Forem: David Lastrucci

Entity mapping in depth: attributes, types, and nullable fields

David Lastrucci — Sun, 12 Apr 2026 12:27:02 +0000

In the previous article we created a simple entity and performed CRUD operations. Now let's look at how entity mapping actually works under the hood, and how to handle more realistic scenarios like nullable fields and optimistic locking.

The mapping attributes

Every Trysil entity needs at least two class-level attributes:

[TTable('Contacts')]      // which table
[TSequence('ContactsID')]   // how the PK is generated
TTContact = class

And every mapped field needs a [TColumn] attribute:

[TColumn('Firstname')]
FFirstname: String;

Here is the full set of mapping attributes:

Attribute	Level	Purpose
`TTable`	Class	Maps the class to a database table
`TSequence`	Class	Names the sequence for the PK
`TPrimaryKey`	Field	Marks the primary key field
`TColumn`	Field	Maps a field to a column
`TVersionColumn`	Field	Enables optimistic locking
`TRelation`	Class	Declares a parent-child relationship (covered in a later article)

Core types

Trysil defines three foundational types in Trysil.Types:

TTPrimaryKey

[TPrimaryKey]
[TColumn('ID')]
FID: TTPrimaryKey;

TTPrimaryKey is an alias for Int32. Every entity must have exactly one primary key field of this type. The value is auto-generated by the database.

The ID property is read-only — you never set it manually:

property ID: TTPrimaryKey read FID;

TTVersion

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

TTVersion is also an Int32. When you mark a field with [TVersionColumn], Trysil includes it in the WHERE clause of UPDATE and DELETE statements:

UPDATE Contacts SET ... WHERE ID = :ID AND VersionID = :VersionID

If the version in the database does not match the version in your object, the update affects zero rows and Trysil raises an exception. This is optimistic locking — it prevents two users from silently overwriting each other's changes.

After a successful update, Trysil increments the version automatically.

TTNullable<T>

Database columns are often nullable, but Delphi value types (Integer, TDateTime, Double) cannot be null. Trysil solves this with a generic record:

[TColumn('BirthDate')]
FBirthDate: TTNullable<TDateTime>;

[TColumn('Score')]
FScore: TTNullable<Integer>;

TTNullable<T> has no default constructor. When you declare a field of this type and do not assign a value, it is null:

var
  LDate: TTNullable<TDateTime>;
begin
  // LDate is null here

  if LDate.IsNull then
    WriteLn('No date set');

  // Assign a value
  LDate := TTNullable<TDateTime>.Create(Now);
  WriteLn(DateTimeToStr(LDate.Value));
end;

In the database, a null TTNullable<T> field is stored as SQL NULL. When reading, SQL NULL maps back to the null state.

A more realistic entity

Let's build a TTProduct entity that uses all of these types:

unit Product.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  System.SysUtils,
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes;

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

    [TRequired]
    [TMaxLength(100)]
    [TColumn('Description')]
    FDescription: String;

    [TGreater(0.00)]
    [TColumn('Price')]
    FPrice: Double;

    [TColumn('DiscountedPrice')]
    FDiscountedPrice: TTNullable<Double>;

    [TColumn('AvailableFrom')]
    FAvailableFrom: TTNullable<TDateTime>;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property Description: String read FDescription write FDescription;
    property Price: Double read FPrice write FPrice;
    property DiscountedPrice: TTNullable<Double>
      read FDiscountedPrice write FDiscountedPrice;
    property AvailableFrom: TTNullable<TDateTime>
      read FAvailableFrom write FAvailableFrom;
    property VersionID: TTVersion read FVersionID;
  end;

The matching SQL table:

CREATE TABLE Products (
  ID INTEGER PRIMARY KEY,
  Description TEXT NOT NULL,
  Price REAL NOT NULL,
  DiscountedPrice REAL,
  AvailableFrom TEXT,
  VersionID INTEGER NOT NULL DEFAULT 1
);

Note how DiscountedPrice and AvailableFrom are nullable in both the Delphi entity and the SQL schema.

Working with nullable fields

var
  LProduct: TTProduct;
begin
  LProduct := LContext.CreateEntity<TTProduct>();
  LProduct.Description := 'Mechanical keyboard';
  LProduct.Price := 149.99;
  // DiscountedPrice and AvailableFrom are null — we simply don't set them

  LContext.Insert<TTProduct>(LProduct);

  // Later, set a discounted price
  LProduct.DiscountedPrice := TTNullable<Double>.Create(119.99);
  LContext.Update<TTProduct>(LProduct);
end;

How the mapping cache works

The first time you use an entity type with TTContext, Trysil reads its attributes via RTTI and builds a TTTableMap — an internal representation of the table name, columns, primary key, version column, and relationships.

This mapping is cached globally in TTMapper.Instance (a singleton). Subsequent operations on the same entity type skip the RTTI scan entirely. This means the reflection cost is paid only once per entity type for the lifetime of your application.

Compile-time safety

Always add this directive at the top of your model units:

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

Without it, a typo like [TColum('Name')] compiles silently and the field is never mapped. With the directive, the compiler flags it as an error immediately.

What is next

We now know how to map classes to tables, handle nullable fields, and rely on optimistic locking for safe concurrent updates. In the next article we will explore validation — how Trysil checks your data before it hits the database.

Trysil is open-source and available on GitHub. Feedback and contributions are welcome!

Meet Trysil: a lightweight ORM for Delphi

David Lastrucci — Thu, 09 Apr 2026 20:21:17 +0000

If you have ever written a Delphi application that talks to a database, you know the routine: write SQL by hand, manage parameters, loop through datasets, and copy values into objects field by field. It works, but it is tedious and error-prone.

Trysil is an open-source ORM for Delphi that eliminates that boilerplate. You decorate your classes with attributes, and the framework handles the rest — mapping, querying, inserting, updating, deleting. It is lightweight, attribute-driven, and built on top of FireDAC, so it works with SQLite, PostgreSQL, SQL Server, and Firebird out of the box.

In this first article we will go from zero to a working CRUD application with SQLite.

Installation

You can install Trysil in three ways:

GetIt — search for "Trysil" in the Embarcadero GetIt Package Manager
Boss — boss install davidlastrucci/Trysil
Manual — clone the GitHub repo, open Packages/<ver>/Trysil.groupproj, and build all

After installation, point your project's Search Path to the compiled output directory.

Your first entity

An entity in Trysil is a plain Delphi class with attributes. No base class to inherit, no interface to implement.

unit Contact.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes;

type
  [TTable('Contacts')]
  [TSequence('ContactsID')]
  TTContact = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TMaxLength(50)]
    [TColumn('Firstname')]
    FFirstname: String;

    [TRequired]
    [TMaxLength(50)]
    [TColumn('Lastname')]
    FLastname: String;

    [TMaxLength(255)]
    [TEmail]
    [TColumn('Email')]
    FEmail: String;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property Firstname: String read FFirstname write FFirstname;
    property Lastname: String read FLastname write FLastname;
    property Email: String read FEmail write FEmail;
    property VersionID: TTVersion read FVersionID;
  end;

A few things to note:

[TTable('Contacts')] maps the class to the Contacts table.
[TSequence('ContactsID')] tells Trysil how the primary key is generated.
[TPrimaryKey] and [TColumn('...')] mark the primary key and map each field to a column.
[TVersionColumn] enables optimistic locking — Trysil will check this value on every update to prevent lost writes.
[TRequired], [TMaxLength], [TEmail] are validation attributes — we will cover them in depth in a later article.
{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR} turns typos in attribute names into compile-time errors. Always add this.

Fields are strict private with public properties. The ORM accesses them through RTTI.

Connecting to SQLite

uses
  Trysil.Data,
  Trysil.Data.FireDAC.SQLite,
  Trysil.Data.FireDAC.ConnectionPool,
  Trysil.Context;

var
  LConnection: TTConnection;
  LContext: TTContext;
begin
  // Disable connection pooling (not needed for desktop apps)
  TTFireDACConnectionPool.Instance.Config.Enabled := False;

  // Register and create the connection
  TTSQLiteConnection.RegisterConnection('MyApp', 'contacts.db');
  LConnection := TTSQLiteConnection.Create('MyApp');
  try
    LContext := TTContext.Create(LConnection);
    try
      // ... use the context
    finally
      LContext.Free;
    end;
  finally
    LConnection.Free;
  end;
end;

TTContext is the single entry point for all ORM operations. You create it with a connection, and it gives you methods for reading, writing, and querying entities.

CRUD operations

Create

var
  LContact: TTContact;
begin
  LContact := LContext.CreateEntity<TTContact>();
  LContact.Firstname := 'Ada';
  LContact.Lastname := 'Lovelace';
  LContact.Email := 'ada@example.com';
  LContext.Insert<TTContact>(LContact);
end;

Always use CreateEntity<T> to create new entities — it initializes internal state that the context needs to track the object.

Read

var
  LContacts: TTList<TTContact>;
begin
  LContacts := TTList<TTContact>.Create;
  try
    // Load all contacts
    LContext.SelectAll<TTContact>(LContacts);

    for LContact in LContacts do
      WriteLn(Format('%s %s — %s', [
        LContact.Firstname,
        LContact.Lastname,
        LContact.Email]));
  finally
    LContacts.Free;
  end;
end;

To fetch a single entity by ID:

var
  LContact: TTContact;
begin
  LContact := LContext.Get<TTContact>(42);
  // raises an exception if not found
end;

Update

LContact.Email := 'ada.lovelace@example.com';
LContext.Update<TTContact>(LContact);

Behind the scenes, Trysil generates an UPDATE statement that includes the VersionID in the WHERE clause. If another user has modified the same record in the meantime, the update fails with a concurrency error rather than silently overwriting their changes.

Delete

LContext.Delete<TTContact>(LContact);

The SQL table

For completeness, here is the SQLite schema that matches our entity:

CREATE TABLE Contacts (
  ID INTEGER PRIMARY KEY,
  Firstname TEXT NOT NULL,
  Lastname TEXT NOT NULL,
  Email TEXT,
  VersionID INTEGER NOT NULL DEFAULT 1
);

What is next

In this article we defined an entity, connected to SQLite, and performed full CRUD — all without writing a single line of SQL.

In the next article we will look deeper into entity mapping: nullable fields, custom types, and how Trysil handles the relationship between your Delphi classes and your database schema.

The full source code is available on GitHub.

Trysil is an open-source project. If you find it useful, consider giving it a star on GitHub!

Automatic audit trails and soft delete in Delphi with Trysil

David Lastrucci — Wed, 08 Apr 2026 15:11:14 +0000

Most ORMs handle INSERT, UPDATE, and DELETE well. But when you need to track who changed a record and when, or keep deleted records around instead of erasing them, you're usually on your own — writing triggers, adding boilerplate to every repository method, or bolting on an external audit library.

In Trysil (an open-source ORM for Delphi), I wanted these features to be declarative: add an attribute, and the framework handles the rest. No base class to inherit, no interface to implement, no code to write beyond the attribute itself.

This article shows how it works.

The problem

Consider a typical entity:

[TTable('Invoices')]
[TSequence('InvoicesID')]
TInvoice = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TColumn('InvoiceNumber')]
  FInvoiceNumber: String;

  [TColumn('Amount')]
  FAmount: Double;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property InvoiceNumber: String read FInvoiceNumber write FInvoiceNumber;
  property Amount: Double read FAmount write FAmount;
end;

Now you get a new requirement: "We need to know who created each invoice and when. And when invoices are deleted, don't actually remove them — mark them as deleted so we can audit them later."

In most ORMs this means: add columns to the table, add fields to the entity, then manually set CreatedAt := Now in every insert method, UpdatedAt := Now in every update method, and replace every DELETE FROM with an UPDATE ... SET DeletedAt = Now. And don't forget to add WHERE DeletedAt IS NULL to every single query.

The solution: six attributes

Trysil solves this with six attributes that you place on entity fields:

Attribute	Populated on	Field type
`[TCreatedAt]`	INSERT	`TTNullable<TDateTime>`
`[TCreatedBy]`	INSERT	`String`
`[TUpdatedAt]`	UPDATE	`TTNullable<TDateTime>`
`[TUpdatedBy]`	UPDATE	`String`
`[TDeletedAt]`	DELETE (soft)	`TTNullable<TDateTime>`
`[TDeletedBy]`	DELETE (soft)	`String`

Here's the same invoice entity with full change tracking and soft delete:

[TTable('Invoices')]
[TSequence('InvoicesID')]
TInvoice = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TColumn('InvoiceNumber')]
  FInvoiceNumber: String;

  [TColumn('Amount')]
  FAmount: Double;

  [TCreatedAt]
  [TColumn('CreatedAt')]
  FCreatedAt: TTNullable<TDateTime>;

  [TCreatedBy]
  [TColumn('CreatedBy')]
  FCreatedBy: String;

  [TUpdatedAt]
  [TColumn('UpdatedAt')]
  FUpdatedAt: TTNullable<TDateTime>;

  [TUpdatedBy]
  [TColumn('UpdatedBy')]
  FUpdatedBy: String;

  [TDeletedAt]
  [TColumn('DeletedAt')]
  FDeletedAt: TTNullable<TDateTime>;

  [TDeletedBy]
  [TColumn('DeletedBy')]
  FDeletedBy: String;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property InvoiceNumber: String read FInvoiceNumber write FInvoiceNumber;
  property Amount: Double read FAmount write FAmount;
  property CreatedAt: TTNullable<TDateTime> read FCreatedAt;
  property CreatedBy: String read FCreatedBy;
  property UpdatedAt: TTNullable<TDateTime> read FUpdatedAt;
  property UpdatedBy: String read FUpdatedBy;
  property DeletedAt: TTNullable<TDateTime> read FDeletedAt;
  property DeletedBy: String read FDeletedBy;
end;

That's it. No other code changes. The ORM does the rest.

What happens at runtime

Insert

LInvoice := LContext.CreateEntity<TInvoice>();
LInvoice.InvoiceNumber := 'INV-2026-001';
LInvoice.Amount := 1500.00;
LContext.Insert<TInvoice>(LInvoice);

Before executing the INSERT, the resolver automatically sets CreatedAt to the current timestamp and CreatedBy to the current user. You never touch these fields yourself.

The generated SQL looks like:

INSERT INTO Invoices (InvoiceNumber, Amount, CreatedAt, CreatedBy, VersionID)
VALUES (:p1, :p2, :p3, :p4, 0)

Update

LInvoice.Amount := 1750.00;
LContext.Update<TInvoice>(LInvoice);

The resolver sets UpdatedAt and UpdatedBy automatically:

UPDATE Invoices
SET InvoiceNumber = :p1, Amount = :p2, UpdatedAt = :p3, UpdatedBy = :p4,
    VersionID = VersionID + 1
WHERE ID = :pk AND VersionID = :ver

Note the optimistic locking in the WHERE clause — [TVersionColumn] works alongside change tracking.

Delete (soft)

LContext.Delete<TInvoice>(LInvoice);

Because the entity has a [TDeletedAt] field, this does not execute a DELETE FROM. Instead it runs:

UPDATE Invoices
SET DeletedAt = :p1, DeletedBy = :p2, VersionID = VersionID + 1
WHERE ID = :pk AND VersionID = :ver

The record stays in the database. It's just marked as deleted.

Queries automatically exclude soft-deleted records

Every SELECT generated by Trysil checks for the presence of [TDeletedAt] on the entity. If found, it appends DeletedAt IS NULL to the WHERE clause:

LContext.SelectAll<TInvoice>(LInvoices);
// Generated: SELECT ... FROM Invoices WHERE DeletedAt IS NULL

This happens transparently. You don't add any filter — the ORM knows that if you declared [TDeletedAt], you want soft-deleted records hidden by default.

When you need to see deleted records

Use IncludeDeleted on the filter builder:

LFilter := LContext.CreateFilterBuilder<TInvoice>()
  .IncludeDeleted
  .Build;
LContext.Select<TInvoice>(LInvoices, LFilter);
// Generated: SELECT ... FROM Invoices (no DeletedAt filter)

This is useful for admin panels, audit views, or data recovery.

Providing the current user

The *By attributes need to know who the current user is. Trysil doesn't impose any authentication mechanism — instead, you provide a callback:

LContext.OnGetCurrentUser :=
  function: String
  begin
    result := GetCurrentSessionUser(); // your logic here
  end;

This callback is invoked by the resolver right before executing INSERT, UPDATE, or soft DELETE. If not assigned, an empty string is written — so the *By fields are optional. You can use only the *At attributes if you just need timestamps.

Mix and match

You don't have to use all six attributes. Common combinations:

Timestamps only (no user tracking):

[TCreatedAt]
[TColumn('CreatedAt')]
FCreatedAt: TTNullable<TDateTime>;

[TUpdatedAt]
[TColumn('UpdatedAt')]
FUpdatedAt: TTNullable<TDateTime>;

Soft delete only (no creation/update tracking):

[TDeletedAt]
[TColumn('DeletedAt')]
FDeletedAt: TTNullable<TDateTime>;

Full audit trail — all six, as shown in the invoice example above.

Each entity can have its own combination. There's no global setting to toggle.

How it works internally

The implementation touches three layers:

1. Mapping — When Trysil first encounters an entity class, it scans its RTTI for the six attributes and builds a TTChangeTrackingMap for each phase (created, updated, deleted). Each map holds references to the ChangedAt and ChangedBy column mappings. This scan happens once and is cached.

2. Resolver — Before executing any command, the resolver calls ApplyChangeTracking with the appropriate map. This sets the timestamp via TTNullable<TDateTime>.Create(Now) and the user string via the OnGetCurrentUser callback.

3. SQL generation — When [TDeletedAt] is present, the resolver switches from CreateDeleteCommand (which generates DELETE FROM) to CreateSoftDeleteCommand (which generates an UPDATE targeting only the deleted tracking columns and the version column). On the SELECT side, AddSoftDeleteWhere injects the DeletedAt IS NULL condition unless IncludeDeleted is set.

The soft delete command also skips relation checks (CheckRelations), since the record isn't actually being removed — foreign key constraints don't apply.

Database table

The corresponding table just needs the extra columns. For example, with PostgreSQL:

CREATE TABLE Invoices (
  ID SERIAL PRIMARY KEY,
  InvoiceNumber VARCHAR(50) NOT NULL,
  Amount DOUBLE PRECISION NOT NULL,
  CreatedAt TIMESTAMP,
  CreatedBy VARCHAR(100),
  UpdatedAt TIMESTAMP,
  UpdatedBy VARCHAR(100),
  DeletedAt TIMESTAMP,
  DeletedBy VARCHAR(100),
  VersionID INTEGER NOT NULL DEFAULT 0
);

No triggers, no stored procedures. The ORM handles everything at the application level.

Wrapping up

Adding audit trails and soft delete to a Delphi application usually means writing repetitive code in every data access method. With Trysil's attribute-based approach, you declare the behavior once on the entity and the framework enforces it consistently across all operations.

The feature works with all four supported databases (SQLite, PostgreSQL, SQL Server, Firebird) and composes naturally with the rest of the framework — optimistic locking, identity map, fluent query builder, JSON serialization.

Links:

GitHub: github.com/davidlastrucci/Trysil
Documentation: davidlastrucci.github.io/trysil
Install: available on GetIt and via boss install davidlastrucci/Trysil

If you have questions or feedback, feel free to open an issue on GitHub.

Trysil - API REST made simple

David Lastrucci — Thu, 20 Nov 2025 21:21:00 +0000

If you've ever built REST APIs, you know the drill: define your data model, write your database queries, map results to objects, handle serialization, write controllers... and that's before you even get to the business logic!

What if I told you that with Trysil, a Delphi ORM, you can skip most of that boilerplate and go from your data model to a working API in just a few steps?

Let me show you how.

The Problem with Traditional Approaches

When building REST APIs the traditional way, you often end up writing:

SQL queries (or stored procedures)
Data mapping code
Validation logic
CRUD operations for each entity
Serialization/deserialization code

It works, but it's repetitive and time-consuming. For each new entity, you're basically copying and pasting the same patterns over and over.

Enter Trysil

Trysil is a Delphi ORM that helps you focus on what matters: your business logic. Once you've defined your entity model, Trysil handles the heavy lifting of persistence, queries, and data mapping.

Let's see how simple it can be with a real example.

Step 1: Define Your Entity Model

First, we define our TCustomer entity class. With Trysil, you simply create a plain Delphi class and decorate it with attributes to map it to your database:

[TTable('Customers')]
TCustomer = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TRequired]
  [TMaxLength(100)]
  [TColumn('Firstame')]
  FFirstname: String;

  [TRequired]
  [TMaxLength(100)]
  [TColumn('Lastname')]
  FLastname: String;

  [TMaxLength(255)]
  [TColumn('Email')]
  FEmail: String;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property Firstname: String read FFirstname write FFirstname;
  property Lastname: String read FLastname write FLastname;
  property Email: String read FEmail write FEmail;
  property VersionID: TTVersion read FVersionID;
end;

That's it! Notice how clean and readable it is. The attributes tell Trysil everything it needs to know about the database mapping.

Step 2: Create Your API Controller

Now here's where the magic happens. To expose this entity through a REST API, we create a controller. Thanks to Trysil handling all the persistence logic, our controller can be incredibly simple:

[TUri('/customer')]
TCustomerController = class(TReadWriteController<TCustomer>)
end;

Look at that! With just a few lines of code, we have:

GET to retrieve customers
POST to create a new customer
PUT to update existing one
DELETE to remove a customer

Trysil takes care of:

Database connections
SQL generation
Object-relational mapping
Transactions
Error handling

The Result

What you end up with is a clean, maintainable codebase where:

Your entity model is the single source of truth
Controllers are thin and focused on HTTP concerns
No SQL strings scattered throughout your code
Easy to test and extend

This is the beauty of using an ORM like Trysil: you spend less time on plumbing and more time building features that matter to your users.

Trysil - Multi-tenat API REST

David Lastrucci — Tue, 26 Nov 2024 15:54:28 +0000

Run "Create new Trysil multi-tenant API REST" from the Trysil menu.

Choose the folder, the name of the project, and whether to download the project template from HTTP (the template on the HTTP server is usually more up-to-date).

Enter the settings for your API REST: the base uri, the port, whether the API requires authentication, and whether it should write the HTTP traffic log to the database.

If you have decided to write the HTTP traffic log to a database, you will be asked for the type of database (you can choose between Firebird, Microsoft SQL Server, PostgreSQL or SQLite) and all the other parameters for the connection.

Enter the settings for the Windows service: the service name, name, and description that will appear in the list of services.

Now you need to enter the settings for the root tenant database (localhost) of your API REST.

You're done, your API REST is ready.

Enjoy!

Use Trysil to draw the Entity Model, create the scripts for the database, create the model and controller classes of your new API REST.

To learn more about "Trysil - Delphi ORM" visit the link to the Github project:

https://github.com/davidlastrucci/trysil

Trysil - TWhereClause

David Lastrucci — Mon, 19 Feb 2024 09:26:49 +0000

Introduzione
Uno degli attributi disponibili in "Trysil - Delphi ORM" è [TWhereClauseAttribute].

Cerco, in questo articolo, di spiegarne l'utilità.

Database
Prendiamo ad esempio la seguente tabella del database:

CREATE TABLE Users(
  ID int NOT NULL,
  Firstname nvarchar(255) NOT NULL,
  Lastname nvarchar(255) NOT NULL,
  UserType int NOT NULL,
  VersionID int NOT NULL,
  PRIMARY KEY (ID)
);

Stabiliamo che la colonna UserType potrà contenere i valori 0 (Insegnante) e 1 (Studente).

Aggiungiamo un indice alla tabella per ottimizzare i filtri:

CREATE INDEX Users_Index1 ON Users (UserType);

TUser
Definiamo il modello astratto per l'entità TUser:

{ TUser }

  [TTable('Users')]
  [TSequence('UsersID')]
  TUser = class abstract
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('Firstname')]
    FFirstname: String;

    [TColumn('Lastname')]
    FLastname: String;

    [TVersionColumn]
    [TColumn('VersionID')]
    FVersionID: TTVersion;
  strict protected
    [TColumn('UserType')]
    FUserType: Integer;
  public
    property ID: TTPrimaryKey read FID;
    property Firstname: String read FFirstname write FFirstname;
    property Lastname: String read FLastname write FLastname;
    property VersionID: TTVersion read FVersionID;
  end;

TTeacher
Specializziamo l'entità TTeacher:

{ TTeacher }

  [TWhereClause('UserType = 0')]
  TTeacher = class(TUser)
  public
    procedure AfterConstruction; override;
  end;

// ...

procedure TTeacher.AfterConstruction;
begin
  inherited AfterConstruction;
  FUserType := 0;
end;

TStudent
Specializziamo poi l'entità TStudent:

{ TStudent }

  [TWhereClause('UserType = 1')]
  TStudent = class(TUser)
    procedure AfterConstruction; override;
  end;

// ...

procedure TStudent.AfterConstruction;
begin
  inherited AfterConstruction;
  FUserType := 1;
end;

Trysil
Adesso siamo in grado di utilizzare TTeacher e TStudent con Trysil come se le due entità fossero persistite su due tabelle separate, in realtà entrambe andranno ad eseguire tutte le operazioni di CRUD sulla tabelle Users del database.

Per saperne di più su Trysil - Delphi ORM visita il link al progetto:

https://github.com/davidlastrucci/Trysil

Trysil - Delphi ORM

David Lastrucci — Mon, 19 Feb 2024 09:19:34 +0000

Perché continuare a scrivere codice così:

Query.SQL.Text :=
  'SELECT I.ID AS InvoiceID, I.Number, ' +
  'C.ID AS CustomerID, C.Name AS CustomerName, ' +
  'U.ID AS CountryID, U.Name AS CountryName ' +
  'FROM Invoices AS I ' +
  'INNER JOIN Customers AS C ON C.ID = I.CustomerID ' +
  'INNER JOIN Countries AS U ON U.ID = C.CountryID ' +
  'WHERE I.ID = :InvoiceID';
Query.ParamByName('InvoiceID').AsInteger := 1;
Query.Open;

ShowMessage(
  Format('Invoice No: %d, Customer: %s, Country: %s', [
    Query.FieldByName('Number').AsInteger,
    Query.FieldByName('CustomerName').AsString,
    Query.FieldByName('CountryName').AsString]));

Quando c'è la possibilità di scriverlo così?

LInvoice := FContext.Get<TInvoice>(1);
ShowMessage(
  Format('Invoice No: %d, Customer: %s, Country: %s', [
    LInvoice.Number,
    LInvoice.Customer.Name,
    LInvoice.Customer.Country.Name]));

Per maggiori informazioni visitail link al progetto Trysil:

https://github.com/davidlastrucci/Trysil