# SQL Generation

> SQL generation reference for SimpleORM — how TSimpleSQL builds queries, pagination by dialect, soft delete behavior.

- Skill: `academiadocodigo/sql-generation` (Agent Skill)
- Install (CLI): `npx skillmds@latest add academiadocodigo/sql-generation`
- Raw SKILL.md: https://api.skillmd.com/api/skills/academiadocodigo/sql-generation/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: academiadocodigo (https://skillmd.com/u/academiadocodigo)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/academiadocodigo/sql-generation

---


# SQL Generation in SimpleORM

> **Rules are in `.claude/rules/sql-safety.md`** — this skill provides generation flow reference and examples.

## Generation Flow

```
Entity (RTTI attributes)
  → TSimpleRTTI<T> extracts: TableName, Fields, PK, Where, SoftDeleteField, FieldsInsert, Param
    → TSimpleSQL<T> generates: SELECT, INSERT, UPDATE, DELETE
      → TSimpleDAO<T> fills Params via DictionaryFields
        → iSimpleQuery executes
```

## TSimpleSQL<T> Methods

| Method | Generates |
|--------|-----------|
| `Select(var aSQL)` | `SELECT fields FROM table WHERE ...` |
| `SelectId(var aSQL)` | `SELECT fields FROM table WHERE pk = :pk` |
| `Insert(var aSQL)` | `INSERT INTO table (fields) VALUES (:params)` |
| `Update(var aSQL)` | `UPDATE table SET field=:field WHERE pk=:pk` |
| `Delete(var aSQL)` | `DELETE FROM` or `UPDATE SET soft=1` |
| `LastID(var aSQL)` | `SELECT FIRST(1) pk FROM table ORDER BY pk DESC` |
| `LastRecord(var aSQL)` | `SELECT FIRST(1) fields FROM table ORDER BY pk DESC` |

## Pagination by Database Dialect

| Database | Syntax | Position |
|----------|--------|----------|
| Firebird | `SELECT FIRST n SKIP n fields FROM ...` | After SELECT |
| MySQL | `... LIMIT n OFFSET n` | End of query |
| SQLite | `... LIMIT n OFFSET n` | End of query |
| Oracle | `... OFFSET n ROWS FETCH NEXT n ROWS ONLY` | End of query |

## Soft Delete Behavior

- **SELECT**: Auto-appends `WHERE soft_field = 0`
- **DELETE**: Becomes `UPDATE table SET soft_field = 1 WHERE pk = :pk`
- **ForceDelete**: Ignores soft delete, uses real `DELETE FROM`

## Fluent SQL Building

```pascal
LDAO.SQL
  .Fields('ID, NAME')
  .Where('STATUS = 1')
  .OrderBy('NAME')
  .GroupBy('CATEGORY')
  .Join('INNER JOIN OTHER ON ...')
  .Skip(10)
  .Take(20)
  .&End
  .Find(LList);
```

## Known Limitations

- `LastID`/`LastRecord` hardcoded to Firebird syntax (`SELECT FIRST(1)`) — not dialect-aware