Cookbook

Practical, copy-paste recipes for common Trysil tasks. Each recipe is self-contained -- jump to what you need.

Pagination

Load a page of results with offset and limit:

var LBuilder := LContext.CreateFilterBuilder<TPerson>();
try
  var LFilter := LBuilder
    .Where('Active').Equal(True)
    .OrderByAsc('Lastname')
    .Limit(20)
    .Offset(40)   // skip first 2 pages
    .Build;

  LContext.Select<TPerson>(LPersons, LFilter);
finally
  LBuilder.Free;
end;

Count total records for the pager UI:

var LBuilder := LContext.CreateFilterBuilder<TPerson>();
try
  var LFilter := LBuilder
    .Where('Active').Equal(True)
    .Build;

  LTotal := LContext.SelectCount<TPerson>(LFilter);
finally
  LBuilder.Free;
end;

Combining AND / OR Conditions

var LBuilder := LContext.CreateFilterBuilder<TPerson>();
try
  var LFilter := LBuilder
    .Where('Lastname').Equal('Smith')
    .OrWhere('Lastname').Equal('Jones')
    .AndWhere('Active').Equal(True)
    .Build;

  LContext.Select<TPerson>(LPersons, LFilter);
finally
  LBuilder.Free;
end;

Note

Conditions are combined in declaration order, and SQL binds AND tighter than OR. For grouping such as (A or B) and C, use the expression API.

Raw WHERE with Parameters

When the fluent builder is not enough, use TTFilter directly:

var LFilter := TTFilter.Create(
  '(Lastname = :Name OR Firstname = :Name) AND Age >= :MinAge');
LFilter.AddParameter('Name', ftWideString, 'David');
LFilter.AddParameter('MinAge', ftInteger, 18);

LContext.Select<TPerson>(LPersons, LFilter);

Always use named parameters (:ParamName) -- never concatenate values into SQL strings.

Insert and Read Back the ID

var LPerson := LContext.CreateEntity<TPerson>();
try
  LPerson.Firstname := 'David';
  LPerson.Lastname := 'Lastrucci';
  LContext.Insert<TPerson>(LPerson);

  Writeln(Format('New ID: %d', [LPerson.ID]));  // ID is populated after insert
finally
  LContext.FreeEntity<TPerson>(LPerson);
end;

CreateEntity<T> initializes the entity with a sequence-generated ID. Always use it instead of calling TPerson.Create directly.

Save (Insert or Update Automatically)

Save<T> determines whether to insert or update based on the internal new-entity cache:

var LPerson := LContext.CreateEntity<TPerson>();
try
  LPerson.Firstname := 'Alice';
  LContext.Save<TPerson>(LPerson);    // INSERT (new entity)

  LPerson.Lastname := 'Smith';
  LContext.Save<TPerson>(LPerson);    // UPDATE (already persisted)
finally
  LContext.FreeEntity<TPerson>(LPerson);
end;

Batch Operations with ApplyAll

Insert, update, and delete multiple entities in a single transaction:

var
  LInsertList: TTList<TPerson>;
  LUpdateList: TTList<TPerson>;
  LDeleteList: TTList<TPerson>;
begin
  LInsertList := TTList<TPerson>.Create;
  LUpdateList := TTList<TPerson>.Create;
  LDeleteList := TTList<TPerson>.Create;
  try
    // Prepare inserts
    LPerson := LContext.CreateEntity<TPerson>();
    LPerson.Firstname := 'New';
    LInsertList.Add(LPerson);

    // Prepare updates
    LUpdateList.Add(LExistingPerson);
    LExistingPerson.Lastname := 'Updated';

    // Prepare deletes
    LDeleteList.Add(LObsoletePerson);

    // Execute all in one transaction
    LContext.ApplyAll<TPerson>(LInsertList, LUpdateList, LDeleteList);
  finally
    LDeleteList.Free;
    LUpdateList.Free;
    LInsertList.Free;
  end;
end;

Explicit Transactions

Wrap multiple operations in a transaction with automatic commit/rollback:

LContext.RunInTransaction(
  procedure
  begin
    LContext.Insert<TOrder>(LOrder);
    LContext.Insert<TOrderDetail>(LDetail1);
    LContext.Insert<TOrderDetail>(LDetail2);
  end);

It commits on clean exit and rolls back and re-raises on an exception. If a transaction is already active it joins it instead of opening a second one.

When you need to decide commit and rollback yourself:

var LTransaction := LContext.CreateTransaction(
  TTTransactionMode.RollbackOnDestroy);
try
  LContext.Insert<TOrder>(LOrder);
  LContext.Update<TProduct>(LProduct);

  LTransaction.Commit;
finally
  LTransaction.Free;
end;

Leaving the block without reaching Commit rolls back.

Lazy Loading a Related Entity

Define a lazy field on the entity:

type
  [TTable('Orders')]
  [TSequence('OrdersID')]
  TOrder = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('CustomerID')]
    FCustomer: TTLazy<TCustomer>;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;

    function GetCustomer: TCustomer;
    procedure SetCustomer(const AValue: TCustomer);
  public
    property ID: TTPrimaryKey read FID;
    property Customer: TCustomer read GetCustomer write SetCustomer;
  end;

The getter and setter delegate to FCustomer.Entity:

function TOrder.GetCustomer: TCustomer;
begin
  Result := FCustomer.Entity;
end;

procedure TOrder.SetCustomer(const AValue: TCustomer);
begin
  FCustomer.Entity := AValue;
end;

Use it -- the related entity is loaded on first access:

LContext.SelectAll<TOrder>(LOrders);
for LOrder in LOrders do
  Writeln(LOrder.Customer.Name);  // loads TCustomer on first call

Warning

Each TTLazy<T> access triggers a separate SELECT. Loading a lazy field inside a loop causes N+1 queries. For bulk operations, consider loading related entities upfront with a separate SelectAll.

Lazy Loading a Detail List

type
  [TTable('Orders')]
  [TSequence('OrdersID')]
  [TRelation('OrderDetails', 'OrderID', True)]
  TOrder = class
  strict private
    // ...
    [TDetailColumn('ID', 'OrderID')]
    FDetails: TTLazyList<TOrderDetail>;

    function GetDetails: TTList<TOrderDetail>;
  public
    property Details: TTList<TOrderDetail> read GetDetails;
  end;

function TOrder.GetDetails: TTList<TOrderDetail>;
begin
  Result := FDetails.List;
end;

LOrder := LContext.Get<TOrder>(LOrderID);
for LDetail in LOrder.Details do
  Writeln(Format('  %s x%d', [LDetail.ProductName, LDetail.Quantity]));

Nullable Fields

Use TTNullable<T> for columns that can be NULL:

type
  [TTable('Persons')]
  TPerson = class
  strict private
    [TColumn('Email')]
    FEmail: TTNullable<String>;
  public
    property Email: TTNullable<String> read FEmail write FEmail;
  end;

// Set a value
LPerson.Email := TTNullable<String>.Create('david@example.com');

// Check and read
if not LPerson.Email.IsNull then
  Writeln(LPerson.Email.Value);

// Set to null (default state)
LPerson.Email := Default(TTNullable<String>);

Validation with Attributes

type
  [TTable('Products')]
  TProduct = class
  strict private
    [TRequired]
    [TMaxLength(100)]
    [TColumn('Name')]
    FName: String;

    [TRequired]
    [TRange(0.01, 99999.99)]
    [TColumn('Price')]
    FPrice: Currency;

    [TEmail]
    [TColumn('ContactEmail')]
    FContactEmail: String;

    [TRegex('^[A-Z]{2}-\d{4}$')]
    [TColumn('Code')]
    FCode: String;
  end;

Validation runs automatically on Insert, Update and Undelete. To validate manually:

try
  LContext.Validate<TProduct>(LProduct);
except
  on E: ETValidationException do
    Writeln(E.Message);
end;

Custom Validation with Event Methods

type
  [TTable('Orders')]
  TOrder = class
  strict private
    FTotal: Currency;
    FDiscount: Currency;
  public
    [TBeforeInsertEvent]
    [TBeforeUpdateEvent]
    procedure ValidateDiscount;
  end;

procedure TOrder.ValidateDiscount;
begin
  if FDiscount > FTotal then
    raise ETValidationException.Create('Discount cannot exceed total');
end;

JOIN Query

Define a join entity and query it:

type
  [TTable('Orders')]
  [TSequence('OrdersID')]
  [TJoin(TJoinKind.Inner, 'Customers', 'CustomerID', 'ID')]
  TOrderReport = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('OrderDate')]
    FOrderDate: TDateTime;

    [TColumn('Customers', 'CompanyName')]
    FCustomerName: String;

    [TVersionColumn]
    [TColumn('VersionID')]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property OrderDate: TDateTime read FOrderDate;
    property CustomerName: String read FCustomerName;
  end;

LOrders := LContext.CreateEntityList<TOrderReport>();
try
  LContext.SelectAll<TOrderReport>(LOrders);
  for LOrder in LOrders do
    WriteLn(Format('Order %d: %s (%s)', [
      LOrder.ID,
      LOrder.CustomerName,
      DateToStr(LOrder.OrderDate)]));
finally
  LOrders.Free;
end;

Join entities are read-only. See JOIN Queries for all overloads and details.

Raw Select with GROUP BY

Map aggregation results to a DTO class:

type
  TOrderSummary = class
  strict private
    [TColumn('CustomerName')]
    FCustomerName: String;

    [TColumn('OrderCount')]
    FOrderCount: Integer;

    [TColumn('Total')]
    FTotal: Currency;
  public
    property CustomerName: String read FCustomerName;
    property OrderCount: Integer read FOrderCount;
    property Total: Currency read FTotal;
  end;

LResult := TTObjectList<TOrderSummary>.Create;
try
  LContext.RawSelect<TOrderSummary>(
    'SELECT c.CompanyName AS CustomerName, ' +
    '       COUNT(*) AS OrderCount, ' +
    '       SUM(o.Amount) AS Total ' +
    'FROM Orders o ' +
    'JOIN Customers c ON o.CustomerID = c.ID ' +
    'GROUP BY c.CompanyName',
    LResult);

  for LItem in LResult do
    WriteLn(Format('%s: %d orders, total %.2f', [
      LItem.CustomerName, LItem.OrderCount, LItem.Total]));
finally
  LResult.Free;
end;

DTO classes only need [TColumn] attributes -- no [TTable], [TPrimaryKey], or [TSequence] required. See Raw Select.

JSON Round-Trip

Serialize an entity to JSON and back:

var
  LJSonContext: TTJSonContext;
  LConfig: TTJSonSerializerConfig;
  LJson: String;
  LPerson: TPerson;
begin
  LJSonContext := TTJSonContext.Create(LConnection);
  try
    LConfig := TTJSonSerializerConfig.Create(-1, False);

    // Entity to JSON
    LJson := LJSonContext.EntityToJSon<TPerson>(LPerson, LConfig);

    // JSON to Entity
    LPerson := LJSonContext.EntityFromJSon<TPerson>(LJson);
  finally
    LJSonContext.Free;
  end;
end;

Serialize a list:

LJson := LJSonContext.ListToJSon<TPerson>(LPersons, LConfig);

Exclude Fields from JSON

type
  [TTable('Users')]
  TUser = class
  strict private
    [TColumn('Username')]
    FUsername: String;

    [TJSonIgnore]
    [TColumn('PasswordHash')]
    FPasswordHash: String;
  end;

Fields decorated with [TJSonIgnore] are excluded from serialization.

SQLite In-Memory Database for Testing

TTSQLiteConnection.RegisterConnection('Test', ':memory:');
LConnection := TTSQLiteConnection.Create('Test');
try
  // Create schema
  LConnection.Execute(
    'CREATE TABLE Persons (' +
    '  ID INTEGER PRIMARY KEY AUTOINCREMENT,' +
    '  Firstname TEXT,' +
    '  Lastname TEXT,' +
    '  VersionID INTEGER DEFAULT 0)');

  LContext := TTContext.Create(LConnection);
  try
    // ... run tests against in-memory database
  finally
    LContext.Free;
  end;
finally
  LConnection.Free;
end;

This is the recommended approach for integration tests -- fast, isolated, no cleanup needed.

Structured Logging

Register a custom logger to capture SQL activity:

A logger is a thread: extend TTLoggerThread and override its seven abstract methods, one per event. The framework calls them on the logger's own thread, so the writing never sits on the path of the query. What is still queued when the logger is freed is written by the thread that frees it, once the logger's own thread has stopped.

type
  TConsoleLoggerThread = class(TTLoggerThread)
  strict protected
    procedure LogStartTransaction(const AID: TTLoggerItemID); override;
    procedure LogCommit(const AID: TTLoggerItemID); override;
    procedure LogRollback(const AID: TTLoggerItemID); override;
    procedure LogParameter(
      const AID: TTLoggerItemID;
      const AName: String;
      const AValue: String); override;
    procedure LogSyntax(
      const AID: TTLoggerItemID; const ASyntax: String); override;
    procedure LogCommand(
      const AID: TTLoggerItemID; const ASyntax: String); override;
    procedure LogError(
      const AID: TTLoggerItemID; const AMessage: String); override;
  end;

procedure TConsoleLoggerThread.LogSyntax(
  const AID: TTLoggerItemID; const ASyntax: String);
begin
  Writeln(Format('[SQL] %s', [ASyntax]));
end;

procedure TConsoleLoggerThread.LogParameter(
  const AID: TTLoggerItemID;
  const AName: String;
  const AValue: String);
begin
  Writeln(Format('[PARAM] %s = %s', [AName, AValue]));
end;

procedure TConsoleLoggerThread.LogError(
  const AID: TTLoggerItemID; const AMessage: String);
begin
  Writeln(Format('[ERR] %s', [AMessage]));
end;

The other four - LogStartTransaction, LogCommit, LogRollback and LogCommand - have the same shape and must be overridden too, because they are abstract.

Register the class, not an instance: the framework constructs the thread.

TTLogger.Instance.RegisterLogger<TConsoleLoggerThread>();

The overload with a pool size distributes the events over several threads, round-robin:

TTLogger.Instance.RegisterLogger<TConsoleLoggerThread>(4);

Every method receives a TTLoggerItemID (connection ID + thread ID), which is what lets you correlate all the SQL of one connection across threads. See Logging for the full contract.

Optimistic Locking

Add a [TVersionColumn] field to your entity:

type
  [TTable('Products')]
  TProduct = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('Name')]
    FName: String;

    [TVersionColumn]
    [TColumn('VersionID')]
    FVersionID: TTVersion;
  end;

Trysil automatically increments VersionID on each update and includes it in the WHERE clause. If another user has modified the record since it was loaded, the update raises ETConcurrentUpdateException.

try
  LContext.Update<TProduct>(LProduct);
except
  on E: ETConcurrentUpdateException do
    ShowMessage('Record modified by another user. Please reload and try again.');
end;