sql-generation

name	sql-generation
description	SQL generation reference for SimpleORM — how TSimpleSQL builds queries, pagination by dialect, soft delete behavior.
user-invocable	false

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 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

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