For real applications, retrieving objects per ID is not enough.
Your project may have the need to retrieve objects by a textual field, e.g. a
name or identifier.
In this case, you can specify a published property of the
TSQLRecord as stored false, and it will be
defined as an unique column in
the underlying database.
For instance, in the
latest version of our performance benchmark sample code, you can define the
UNIK conditional to define both LastName and
FirstName properties as unique:
type
TSQLRecordSample = class(TSQLRecord)
private
fFirstName: RawUTF8;
fLastName: RawUTF8;
fAmount: currency;
fBirthDate: TDateTime;
fLastChange: TModTime;
fCreatedAt: TCreateTime;
published
property FirstName: RawUTF8 index 40 read fFirstName write fFirstName
{$ifdef UNIK}stored false{$endif};
property LastName: RawUTF8 index 40 read fLastName write fLastName
{$ifdef UNIK}stored false{$endif};
property Amount: currency read fAmount write fAmount;
property BirthDate: TDateTime read fBirthDate write fBirthDate;
property LastChange: TModTime read fLastChange;
property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
end;
During insertion or update of records, the database will have to check for
uniqueness of those column values. It will have an additional performance cost,
since a search of the new value is to be performed among existing values.
In order to speed-up the process, a so-called index is created at the
database level.
As a consequence, further lookup using this property will benefit for this
index, and will be much faster than a classic loop throughout all
data.
In the mORMot core, we just made some modifications related to this feature:
- External tables are now able to create properly UNIQUE fields at database level, as expected;
- A hashed-based
index feature has been added to the
fast
TSQLRestServerStaticInMemoryinternal engine, and insertion will have no speed penalty any more.
