Using SQLite with FireDAC

This reference article has several sections:

Introduction to SQLite: reviews SQLite features, missed features, possible applications, and applications not for SQLite.
Using SQLite Database: explains how to create, connect to, and manage the SQLite database in Delphi application.
SQLite Encrypted Database: the database encryption is one of the important SQLite features. The topic explains how it works and how to control it.
SQLite Data Types: SQLite has a unique data type system. Without understanding how it works, it is difficult to effectively store and retrieve the data in Delphi applications.
SQLite SQL Commands: main aspects of the SQLite SQL dialect for Delphi application developers.
SQLite Transactions, Locking, and Cursors: explains how to work with transactions in an SQLite environment.
Extending SQLite Engine: as an embedded DBMS, the SQLite engine can be extended by the Delphi application code.
Advanced SQLite Techniques: finally we introduce some advanced SQLite concepts, such as updates logging and SQL authorization.

This article requires knowledge of the FireDAC basics and of the main library APIs. For beginners, we suggest starting from the Getting Started article and looking into the FireDAC\Samples\Getting Started\SQLite demo.

Introduction to SQLite

SQLite Database

The SQLite is an embedded SQL database engine, developed by SQLite Consortium. It is the DBMS most widely deployed database in the world with a rough estimate of 500M installations. You can find it on all iOS and Android mobile devices and on Mac OS desktops. It is used by Firefox, Skype, and McAfee anti-virus.

SQLite Features

The source lists:

Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
Zero-configuration - no setup or administration needed.
Implements most of SQL92. Supports table triggers and views.
A complete database is stored in a single cross-platform disk file.
Supports terabyte-sized databases and gigabyte-sized strings and blobs.
Faster than popular client/server database engines for most common operations.
Self-contained: no external dependencies.
Multi-device: Windows, macOS, iOS, and Android are supported out of the box.
The sources are in the public domain. The use for any purpose is free.
Very powerful API, allowing you to extend the engine in practically all areas.
SQLite achieves one of the best data access performances among other embedded, file-server, and client-server database engines used by the Delphi applications. We are aware of many successful applications with multi-gigabyte databases. For example, Silwood Technology Saphir is built with SQLite, Delphi, and FireDAC.

Missed SQLite Features

According to our experience, developers often look for the following features, which are not presented in SQLite:

Stored procedures -- FireDAC offers custom functions API.
Reach set of build-in functions -- FireDAC pre-installs about 50 standard functions.
Security system, including a user concept and a right concept -- FireDAC offers a password-protected, encrypted database and special callbacks to filter user actions.
Collations (only ASCII and binary) -- FireDAC allows you to define custom collations.

SQLite Applications

The source lists:

Application File Format -- SQLite has been used with great success as the on-disk file format for desktop platforms.
Embedded devices and applications -- Because the SQLite database requires little or no administration, SQLite is a good choice for devices or services that work unattended and without human support.
Websites -- SQLite usually works well as the database engine for low to medium traffic websites (that is, 99.9% of all websites).
Replacement for ad hoc disk files -- SQLite works particularly well as a replacement for these ad hoc data files.
Internal or temporary databases -- For programs, sifting and sorting the data in various ways can be simpler by loading data into the in-memory SQLite database and using the full scale SQL command.
Stand-in for an enterprise database during demos or testing.
Database Pedagogy.

Applications NOT for SQLite

The same source lists:

High Concurrency -- SQLite uses reader/writer locks on the entire database file. This means that if any process is reading from any part of the database, all the other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all the other processes are prevented from reading any other part of the database.
Client/Server Applications -- If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite works over a network file system, but because of the latency associated with most network file systems, the performance is not ideal.
Very large datasets (N tb).

Using SQLite Database

Connecting to the SQLite Database from a Delphi Application

To link the FireDAC SQLite driver into the Delphi application, add TFDPhysSQLiteDriverLink to a form or to a data module. To connect to an SQLite database, specify SQLite driver parameters, at least:

DriverID=SQLite
Database=<path to SQLite database>

By default, all SQLite driver settings are set for the high-performance single connection access to a database in a stable environment. The PRAGMA command allows you to configure SQLite. Many FireDAC SQLite driver parameters correspond to the pragmas. Additionally, SQLiteAdvanced allows you to specify multiple pragmas separated by ';' as a single connection parameter.

The additional SQLite use cases are:

No	Application specifics	Description
1	Reading large DB.	Set CacheSize to a higher number of pages, which will be used to cache the DB data. The total cache size will be CacheSize * <db page size>.
2	Exclusive updating of DB.	Consider to set JournalMode to WAL (more).
3	Long updating transactions.	Set CacheSize to a higher number of pages, that will allow you to run transactions with many updates without overloading the memory cache with dirty pages.
4	A few concurrent updating processes.	Set LockingMode to Normal to enable shared DB access. Set Synchronous to Normal or Full to make committed data visible to others. Set UpdateOptions.LockWait to True to enable waiting for locks. Increase BusyTimeout to raise a lock waiting time. Consider to set JournalMode to WAL.
5	A few concurrent updating threads.	See (4). Also set SharedCache to False to minimize locking conflicts.
6	A few concurrent updating transactions.	See (4) or (5). Also set TxOptions.Isolation to xiSnapshot or xiSerializible to avoid possible transaction deadlocks.
7	High safety.	Set Synchronous to Full to protect the DB from the committed data losses. Also see (3). Consider encrypting the database to provide integrity.
8	High confidentiality.	Encrypt database to provide confidentiality and integrity.
9	Development time.	Set LockingMode to Normal to enable simultaneous use of the SQLite DB in the IDE and a debugged program.

Creating a SQLite Database from a Delphi Application

By default, the SQLite database is created at a connection establishment if it does not exist. For explicit control, the Delphi application may specify:

OpenMode=CreateUTF8 | CreateUTF16 | ReadWrite | ReadOnly

The first two values used for creation differ by the encoding that will be used for the new database. Also, we recommend you to set page_size to 4096 or more for databases with multi-row tables. This can be done by specifying the following at creation time:

SQLiteAdvanced=page_size=4096

Consider to specify parameters using SQLiteAdvanced:

Note that after the database file is created, it has the zero size. As a result, the database encoding, page size, and other persistent parameters are not recorded in the database. To make such parameters persistent, the application should create at least one table.

Using an SQLite In-memory Database in a Delphi Application

The next SQLite unique feature is the ability to work with pure in-memory databases. This means that no files are created to store database objects and everything is kept in the memory. In this way, you obtain more security, more performance, less requirements to the environment rights for a Delphi application.

To create and open an SQLite in-memory database use the following parameters:

DriverID=SQLite
Database=:memory:

Or just leave the Database parameter empty:

DriverID=SQLite

One FireDAC customer had an SQLite database on a shared network resource. The database is a read-only products catalog with many stored product attributes. To radically improve performance, the customer used TFDSQLiteBackup to move the whole database into the in-memory database. The sample code:

FDConnection1.DriverName := 'SQLite';
FDConnection1.Open;

FDSQLiteBackup1.Database := '\\srv\db\data.sdb';
FDSQLiteBackup1.DestDatabaseObj := FDConnection1.CliObj;
FDSQLiteBackup1.DestMode := smCreate;
FDSQLiteBackup1.Backup;

Working with Unicode and SQLite Databases

FireDAC supports Unicode of Delphi (starting with Delphi 2009) in full. For SQLite, this means that:

FireDAC automatically sets up an SQLite database to exchange all metadata in UTF-16 encoding, when Delphi 2009 or later is used. In Delphi 2007 or less, the metadata is ANSI encoded.
The data will be defined and exchanged as described in the chapter "Mapping SQLite to FireDAC Data Types".

Using Multiple SQLite databases in a Delphi Application

SQLite allows you to use multiple databases in a single connection. A DB specified by the Database parameter is the main database. To attach additional databases, the Delphi application should use the ATTACH command. For example:

FDConnection1.ExecSQL('ATTACH ''c:\hr.sdb'' AS hr');
FDConnection1.ExecSQL('ATTACH ''c:\cust.sdb'' AS cust');
FDQuery1.Open('select * from "Orders" o ' +
  'left join hr."Employees" e on o.EmployeeID = e.EmployeeID ' +
  'left join cust."Customers" c on o.CustomerID = c.CustomerID');

Note: FireDAC interprets a database name as a catalog name.

Manage an SQLite Database from a Delphi Application

A good Delphi (and not only) SQLite database application must be aware of the following facts:

The SQLite database may become fragmented and non-optimal after many "hard" record updates or deletes. The TFDSQLiteValidate.Sweep method call optimizes the database. This method corresponds to the VACUUM command and PRAGMA auto_vacuum. Example:

FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.Sweep;

The SQLite query optimizer builds a better query execution plan when it has an up-to-date database statistic. SQLite does not automatically update the statistic. The TFDSQLiteValidate.Analyze method call collects it. This method utilizes the ANALYZE command. An application may collect statistic for the full database:

FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.Analyze;

The SQLite database may become corrupted or malformed. To verify its integrity, use the TFDSQLiteValidate.CheckOnly method. Note that in order to repair a broken SQLite database, the Delphi application needs to restore it from a backup. The CheckOnly method uses the OnProgress event handler to notify about issues. This method performs the PRAGMA integrity_check command.

procedure TForm1.FDSQLiteValidate1Progress(ASender: TFDPhysDriverService; const AMessage: String);
begin
  Memo1.Lines.Add(AMessage);
end;

FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.OnProgress := Form1Progress;
FDSQLiteValidate1.CheckOnly;

The SQLite database must be backed up regularly to preserve the data loss. The TFDSQLiteBackup component performs the database backup copy. This is how the simplest backup code looks:

FDSQLiteBackup1.Database := 'c:\db.sdb';
FDSQLiteBackup1.DestDatabase := 'c:\db.backup';
FDSQLiteBackup1.DestMode := smCreate;
FDSQLiteBackup1.Backup;

SQLite Encrypted Database

Approach

One of the distinctive SQLite features is the high-speed strong database encryption. It allows you to make database file content confidential and enforce integrity control on the database file.

The encrypted database format is not compatible with other similar SQLite encryption extensions. This means that you cannot use an encrypted database, encrypted with non-FireDAC libraries. If you need to do this, then you have to decrypt a database with an original tool and encrypt it with FireDAC.

The encryption is provided through the officially supported SQLite approach - custom codec code and compilation with SQLITE_HAS_CODEC defined. All encryption routines are implemented on Delphi and embedded into the sqlite3 code. As a result, the encryption is correctly handled for:

ATTACH.
VACUUM.
database recovery.
database backup.

Encryption Modes

Mode	Description	Usage
AES-NNN	The aes-NNN algorithms are generic compositions of AES-CTR and AES-CBC-MAC. This composition guarantees both Confidentiality and Integrity, meaning that only entities with access to the correct password are able to read and modify the pages of the encrypted database. These algorithms add a linear overhead of 32 bytes per page to your encrypted database. This algorithm helps you detect the most malicious attempts to inject data into the database, but it does not prevent such attempts and it does not help you undo such modifications. It is basically a complement to frequent backups, but it is much better than most other database encryption schemes at alerting you when you have been subject to an attack and it is time to restore your database from the backup. Please note that the aes-NNN algorithm, by itself, neither detects deletion of entire pages at the end of the database (but it does detect deletions in the middle of the database), nor does it detect attacks that consist in reverting the database to an older version encrypted using the same password.	The AES-NNN provides top strong Confidentiality and Integrity. However, this happens at the price of a certain performance reduction, noticeable across other encryption modes.
AES-CTR-NNN	The aes-ctr-NNN algorithms are AES-CTR only. They do not detect modifications to the database, but they provide Confidentiality against passive attacks. That is, as long as the attacker does not have access to your password and does not attempt to modify the database to see how your application reacts to the modifications, your data remains as secret as your application allows. Needless to say that the algorithms only protect your database file against attackers that are not able to tap into your FireDAC application using, for example, a debugger and extract the password this way. In a similar way, if you store your password in a configuration file or as a constant in the software itself, it will be trivial for any not even moderately skilled attacker to find it and compromise your security.	The AES-CTR-NNN provides top strong Confidentiality, but not Integrity. For this, you obtain better performance.
AEC-ECB-NNN	The aes-ecb-NNN algorithms are AES-ECB only. They do not detect modifications to the database, and do not provide strong Confidentiality against passive attacks, in contrast to AES-NNN and AES-CTR-NNN.	The AES-ECB-NNN provides comparably weak Confidentiality and no Integrity. However, it has the best performance across other encryption modes.

The NNN is the key size, which may be 128, 192, or 256 bits.

Setting up Encryption

The encryption can be controlled:

through the connection definition parameters Encrypt, NewPassword, and Password.
through the TFDSQLiteSecurity service component.

The password connection definition parameters can have the following form:

[aes-128 | aes-192 | aes-256 | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 |
 aes-ecb-128 | aes-ecb-192 | aes-ecb-256:] password

The "aes-XXX-NNN:" is an optional prefix, controlling the cipher algorithm to be used. If it is not specified, then the following is used:

an algorithm specified by the Encrypt parameter
aes-256 if nothing is specified

FireDAC supports encryption operations:

Operation	Using parameters	Using TFDSQLiteSecurity
Open encrypted database	Password=xxxx	---
Encrypt unencrypted database	NewPassword=xxxx	FDSQLiteSecurity1.Database := '...'; FDSQLiteSecurity1.Password := 'xxxx'; FDSQLiteSecurity1.SetPassword;
Change encrypted database password	Password=xxxx NewPassword=yyyy	FDSQLiteSecurity1.Database := '...'; FDSQLiteSecurity1.Password := 'xxxx'; FDSQLiteSecurity1.ToPassword := 'yyyy'; FDSQLiteSecurity1.ChangePassword;
Decrypt encrypted database	Password=xxxx NewPassword=	FDSQLiteSecurity1.Database := '...'; FDSQLiteSecurity1.Password := 'xxxx'; FDSQLiteSecurity1.RemovePassword;
Verify encryption database status	--- NewPassword=	FDSQLiteSecurity1.Database := '...'; FDSQLiteSecurity1.Password := 'xxxx'; ShowMessage(FDSQLiteSecurity1.CheckEncryption);

SQL Extension

The ATTACH command has an extension. The full syntax of the ATTACH now is:

ATTACH [DATABASE] 'filename' [AS name] [KEY 'password']

If KEY is omitted, then the password value is inherited from the main database. To specify an empty password in order to attach an unencrypted database, use something similar to the following:

ATTACH 'D:\tmp\test.db' AS tst KEY ''

SQLite Data Types

Mapping SQLite to FireDAC Data Types

SQLite has a "type less" data type system. This practically means that you can use any identifier as a column data type name. For example, "Delphi" will work too and will correspond to the string data type. To make the SQLite approach more compatible with other DBMSs and Delphi and more comfortable for the Delphi application developers, FireDAC recognizes data type names as described in the following table:

Type name	Description
ROWID \| _ROWID_ \| OID	dtInt64, Attrs = [caSearchable, caAllowNull, caROWID]
BIT \| BOOL \| BOOLEAN \| LOGICAL \| YESNO	dtBoolean
TINYINT \| SHORTINT \| INT8 [UNSIGNED]	dtSByte / dtByte
BYTE \| UINT8	dtByte
SMALLINT \| INT16 [UNSIGNED]	dtInt16 / dtUInt16
WORD \| UINT16 \| YEAR	dtUInt16
MEDIUMINT \| INTEGER \| INT \| INT32 [UNSIGNED]	dtInt32 / dtUInt32
LONGWORD \| UINT32	dtUInt32
BIGINT \| INT64 \| COUNTER \| AUTOINCREMENT \| IDENTITY [UNSIGNED]	dtInt64 / dtUInt64
LONGLONGWORD \| UINT64	dtUInt64
REAL \| FLOAT \| DOUBLE	dtDouble
SINGLE [PRECISION] [(P, S)]	dtSingle / dtBCD / dtFmtBCD
DECIMAL \| DEC \| NUMERIC \| NUMBER [UNSIGNED] [(P, S)]	dtSByte / dtInt16 / dtInt32 / dtInt64 dtByte / dtUInt16 / dtUInt32 / dtUInt64 dtBCD / dtFmtBCD
MONEY \| SMALLMONEY \| CURRENCY \| FINANCIAL [(P, S)]	dtCurrency
DATE \| SMALLDATE	dtDate
DATETIME \| SMALLDATETIME	dtDateTime
TIMESTAMP	dtDateTimeStamp
TIME	dtTime
CHAR \| CHARACTER [(L)]	dtAnsiString, Len = L, Attrs = [caFixedLen]
VARCHAR \| VARCHAR2 \| TYNITEXT \| CHARACTER VARYING \| CHAR VARYING [(L)]	dtAnsiString, Len = L
NCHAR \| NATIONAL CHAR \| NATIONAL CHARACTER [(L)]	dtWideString, Len = L, Attrs = [caFixedLen]
NVARCHAR \| NVARCHAR2 \| NATIONAL CHAR VARYING \| STRING [(L)]	dtWideString, Len = L
RAW \| TYNIBLOB \| VARBINARY \| BINARY \| BINARY VARYING [(L)]	dtByteString, Len = L
BLOB \| MEDIUMBLOB \| IMAGE \| LONGBLOB \| LONG BINARY \| LONG RAW \| LONGVARBINARY \| GENERAL \| OLEOBJECT \| TINYBLOB	dtBlob
MEDIUMTEXT \| LONGTEXT \| CLOB \| MEMO \| NOTE \| LONG \| LONG TEXT \| LONGCHAR \| LONGVARCHAR \| TINYTEXT	dtMemo
TEXT \| NTEXT \| WTEXT \| NCLOB \| NMEMO \| LONG NTEXT \| LONG WTEXT \| NATIONAL TEXT \| LONGWCHAR \| LONGWVARCHAR \| HTML	dtWideMemo
XMLDATA \| XMLTYPE \| XML	dtXML
GUID \| UNIQUEIDENTIFIER	dtGUID
Other data types	dtWideString

Note: With SQLite, the FormatOptions.StrsTrim works for all string data types.

Special SQLite Data Types

To add an auto incrementing column to a table, define a column as INTEGER PRIMARY KEY AUTOINCREMENT. This type is mapped to dtInt32, Attrs = [caAutoInc]. For more details about handling auto-incrementing columns, read "Auto-Incremental Fields".

The columns with ROWID, _ROWID_, or OID type names are considered identifying row columns. These types are mapped to dtInt64, Attrs = [caSearchable, caAllowNull, caROWID]. For more details about handling row identifying columns, read "Unique Identifying Fields". SQLite ROWID is the fastest way to get access to a specific row:

SELECT * FROM Orders WHERE ROWID = :RID

Adjusting FireDAC Mapping

Some SQLite driver parameters allow for the Delphi application to adjust the data representation:

Parameter	Description
StringFormat = Choose \| Unicode \| ANSI	If Unicode, then all dtAnsiString and dtMemo are represented to a client as dtWideString and dtWideMemo. If ANSI, then all dtWideString and dtWideMemo are represented to a client as dtAnsiString and dtMemo. If Choose, then a string type will be defined using type name.
GUIDFormat = String \| Binary	If Binary, then dtGUID are stored in a database as a TGUID binary value. If String, then as a string in {xxxxxxx} format. Binary requires less space in the DB, String is more readable.
DateTimeFormat = String \| Binary \| DateTime	If Binary, then dtDate, dtTime, dtDateTime are stored in a database as a double value in Julian date format. If String, then as a character string in 'yyyy-mm-dd hh24:mi:ss.zzz' format. If DateTime, then as a double value in Delphi date / time format. Binary requires less space in the DB, String is more useful in SQLite SQL expressions, DateTime may be compatible with some legacy Delphi applications.

Note: Changing GUIDFormat or DateTimeFormat, when the database is not empty, may lead to errors, as FireDAC may fail to read and parse the stored values.

For an expression in a SELECT list, SQLite avoids type name information. When the result set is not empty, FireDAC uses the value data types from the first record. When empty, FireDAC describes those columns as dtWideString. To explicitly specify the column data type, append ::<type name> to the column alias:

SELECT count(*) as "cnt::INT" FROM mytab

If the Delphi application requires SQLite native data type representation, then use FireDAC mapping rules. For example, map TEXT columns to dtAnsiString and INT columns to dtInt64:

with FDQuery1.FormatOptions do begin
  OwnMapRules := True;
  with MapRules do begin
    SourceDataType := dtMemo;
    TargetDataType := dtAnsiString;
  end;
  with MapRules do begin
    SourceDataType := dtInt32;
    TargetDataType := dtInt64;
  end;
end;

High-Precision Numbers

Due to the SQLite type system, long numbers (>= 20 digits) are recognized as having a REAL type affinity. Therefore, a value is rounded to a REAL (double Delphi type) type with a 15 digits precision. To resolve this issue, an application should use TEXT or a similar data type and a mapping rule casting dtAnsiString to dtFmtBCD or a similar data type. Note that this is not a FireDAC issue and at the moment cannot be resolved differently.

SQLite SQL Commands

SQL Dialect

Although SQLite closely follows ANSI SQL 92, some features and commands are not supported, and some powerful ones are added. You can find more information about the SQLite SQL dialect at:

SQLite SQL Command Batches

The FireDAC SQLite driver supports the SQL command batches. SQL commands must be separated by ';'. SQLite allows you to mix any commands in a batch, including DDL and DML. For example:

with FDQuery1.SQL do begin
  SQL.Clear;
  SQL.Add('create table dbms (id integer, name varchar(20));');
  SQL.Add('insert into tab values (1, ''sqlite'');');
  SQL.Add('insert into tab values (2, ''mysql'');');
  SQL.Add('insert into tab values (3, ''firebird'');');
  SQL.Add('create table langs (id integer, name varchar(20));');
  SQL.Add('insert into tab values (1, ''delphi'');');
  SQL.Add('insert into tab values (2, ''c'');');
  SQL.Add('insert into tab values (3, ''c++'');');
  SQL.Add('select * from dbms;');
  SQL.Add('select * from langs;');
end;
FDQuery1.Open;
// process here the DBMS list
FDQuery1.NextRecordSet;
// process here the programming languages list

SQL Script Dialect

FireDAC TFDScript does not support SQLite syntax, where script control commands start from '.'.

Array DML

Starting with v 3.7.11, SQLite supports the INSERT command with multiple VALUES. FireDAC uses this feature to implement Array DML, when Params.BindMode = pbByNumber. Otherwise, FireDAC emulates Array DML. For example:

// here ADQuery1.Params collection is filled by 3 parameters
FDQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// set "by number" parameter binding mode
FDQuery1.Params.BindMode := pbByNumber;
FDQuery1.Params.ArraySize := 100;
for i := 0 to FDQuery1.Params.ArraySize - 1 do begin
  FDQuery1.Params[0].AsIntegers[i] := i;
  FDQuery1.Params[1].AsStrings[i] := 'qwe';
  FDQuery1.Params[2].Clear(i);
end;
FDQuery1.Execute(FDQuery1.Params.ArraySize);

SQLite Transactions, Locking, Threads and Cursors

Locking and Concurrent Updates

Read the following original SQLite articles:

SQLite, as a file-server DBMS, locks the database tables at updates. The following settings affect the concurrent access:

when multiple threads are updating the same database, set the SharedCache connection parameter to False. This helps you avoid some possible deadlocks.
when multiple processes or threads are updating the same database tables, set LockingMode to Normal to enable concurrent access to the tables. Also set the Synchronous connection parameter to Full or Normal. In this way, SQLite updates a database file right after the transaction is finished and other connections see the updates on the predictable basis.
to avoid locking conflicts between connections, set UpdateOptions.LockWait to True and BusyTimeout to a higher value.
to avoid locking conflicts between long running updating transactions, set TFDConnection.TxOptions.Isolation to xiSnapshot or xiSerializible.

Transactions and Isolation Modes

SQLite supports normal transactions and nested transactions (check points). It does not support multiple transactions. Further is a list of isolation modes supported by SQLite:

Mode	Corresponds to
xiDirtyRead	PRAGMA read_uncommitted = 1
xiReadCommitted	BEGIN TRANSACTION DEFERRED
xiRepeatableRead	The same as xiReadCommitted.
xiSnapshot	BEGIN TRANSACTION IMMEDIATE
xiSerializible	BEGIN TRANSACTION EXCLUSIVE

Transactions and DML Commands

Surrounding writing commands into a transaction may radically improve SQLite performance. This is notable in the case of large data modifications. The same is applicable to the FireDAC Array DML feature. Therefore, surround the data modification code into a transaction to get the best performance:

FDConnection1.StartTransaction;
try
  FDQuery1.SQL.Text := 'insert into tab values (:id, :name)';
  FDQuery1.Params.ArraySize := 10;
  for i := 0 to FDQuery1.Params.ArraySize - 1 do begin
    FDQuery1.Params[0].AsIntegers[i] := i;
    FDQuery1.Params[0].AsStrings[i] := 'name' + IntTostr(i);
  end;
  FDQuery1.Execute(FDQuery1.Params.ArraySize, 0);
  FDConnection1.Commit;
except
  FDConnection1.Rollback;
  raise;
end;

Transactions and Cursors

SQLite does not allow you to rollback a transaction, when there are commands with result sets that have not been fetched yet. To avoid this, FireDAC fetches all remaining records from a result set on a Rollback method call. See the FetchOptions.AutoFetchAll.

Extending SQLite Engine

Custom Functions

SQLite does not support the stored procedure or function concept, as it allows you to use the host language environment to extend the engine functionality. SQLite allows you to register host language functions in the SQLite engine and use them in the SQL commands. FireDAC simplifies this by introducing the TFDSQLiteFunction component.

To build a function, the developer has to set FunctionName, ArgumentsCount and create the OnCalculate event handler. Setting Active to True registers the custom function at the SQLite engine. For example:

procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunction;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsInteger := AInputs[0].AsInteger * AInputs[1].AsInteger;
end;

FDSQLiteFunction1.DriverLink := FDPhysSQLiteDriverLink1;
FDSQLiteFunction1.FunctionName := 'XmY';
FDSQLiteFunction1.ArgumentsCount := 2;
FDSQLiteFunction1.OnCalculate := FDSQLiteFunction1Calculate;
FDSQLiteFunction1.Active := True;

This function usage:

FDQuery1.Open('select RegionID, XmY(RegionID, 10) from "Region"');

A function can call the FireDAC methods to query a database. To create a custom function with the default or different number of arguments, you need to specify the same FunctionName and a different number of arguments. This registers an overloaded function inside the SQLite engine.

You can find the above and other function samples in the FireDAC\Samples\DBMS Specific\SQLite\UserFunc folder.

FireDAC implements and installs to a SQLite connection around 50 functions, which are standard de-facto for many DBMS and are implemented by the FireDAC local expressions engine. Note that when you are creating an SQLite connection at run time, you should include the FireDAC.Stan.ExprFuncs unit into the "uses" clause, otherwise an exception is raised:

[FireDAC][Phys][SQLite] ERROR: no such function: UCASE.

To make custom functions accessible at design time, create a custom design-time package with a data module, drop the components on this module, and set up properly. Create the module in the module unit initialization section and destroy it in the finalization section. After this, install your package into the Delphi IDE.

And the Ron Grove movie.

Custom Collations

SQLite stores and handles all character data either in UTF8 or UTF16, depending on the OpenMode connection parameter. When SQLite needs to compare or sort a character data, it has to know what rules to use for this. The rules are known as a collation.

SQLite has several build-in collations. None of them produces a correct sorting for German, Cyrillic, Arabian, and so on phrases. You have to use the TFDSQLiteCollation component to build your own collation. Set CollationName, Flags, LocaleName, then set Active to True to register the collation with the SQLite engine. For example:

FDSQLiteCollation1.DriverLink := FDPhysSQLiteDriverLink1;
FDSQLiteCollation1.CollationName := 'UTF16NoCase';
FDSQLiteCollation1.Flags := [sfIgnoreCase];
FDSQLiteCollation1.Active := True;

The component setup above with default CollationKind=scCompareString implements a standard case-insensitive Unicode collation. The application can implement custom collations using CollationKind=scCustomUTF16 or scCustomUTF8 and implementing the OnCompare event handler. This is how you can use this collation:

SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase

To specify the default collation for a column you can do the following:

CREATE TABLE IF NOT EXISTS test_col (f1 VARCHAR(10) COLLATE UTF16NoCase)

Note: If there is no ability to specify the default collation for a connection, a database, or a table. You can find the above collation samples in the FireDAC\Samples\DBMS Specific\SQLite\UserCollation folder.

If you do not use custom collations, then by default SQLite uses a binary sorting order. For the TFDTable Live Data Window mode, it is important to have the same client side and database sorting orders. To enable client side binary sorting order, set FormatOptions.SortLocale to 0.

The Database Events

FireDAC supports notification of a Delphi application from a SQLite database trigger about some events, such as a data change. For this, FireDAC uses an approach similar to Firebird and registers the POST_EVENT custom function. To call it from a trigger do the following:

CREATE TRIGGER update_orders UPDATE ON "Orders"
BEGIN
  SELECT POST_EVENT('Orders');
END;

To receive an event notification, the Delphi application uses the TFDEventAlerter component. For example:

FDEventAlerter1.Names.Text := 'Orders';
FDEventAlerter1.Options.Synchronize := True;
FDEventAlerter1.OnAlter := DoAlert;
FDEventAlerter1.Active := True;

procedure TForm1.DoAlert(ASender: TFDCustomEventAlerter;
  const AEventName: String; const AArgument: Variant);
begin
  if CompareText(AEventName, 'Orders') = 0 then
    qryOrders.Refresh;
end;

Custom Data Sources

The Local SQL engine allows you to use TDataSet descendants in your SQL queries. FireDAC uses SQLite Virtual Table API to implement Local SQL.

Advanced SQLite Techniques

Hooking Database Updates

SQLite provides an unique API allowing you to monitor all updates to a database. This feature can be used, for example, to log all updates to a DB. To work with this API, a Delphi application should set the OnUpdate event handler of the TSQLiteDatabase object, which is a database connection wrapping object. Hook this event after a database connection is opened. For example:

procedure TForm1.DoUpdate(ADB: TSQLiteDatabase; AOper: Integer; const ADatabase, ATable: String; ARowid: sqlite3_int64);
begin
  Memo1.Lines.Add(Format('%d - %s - %s - %u', [AOper, ADatabase, ATable, ARowid]));
end;

FDConnection1.Connected := True;
TSQLiteDatabase(FDConnection1.ConnectionIntf.CliObj).OnUpdate := DoUpdate;

You can find this sample in the FireDAC\Samples\DBMS Specific\SQLite\OnUpdate folder.

Controlling Database Access Rights

The SQLite is an embedded DBMS. This implies that it is a single user DBMS and does not need such concepts as a user, access rights, and so on. Still, some application can benefit from an access right control, for example:

An application can restrict rights depending on an end-user license. A demo license means less possibilities, a full license provides all possibilities.
Multi-tier data access frameworks can use their own user concept and control data access rights using some generic approach.

Again, SQLite provides a unique feature allowing you to authorize or not SQL commands. To work with this API, a Delphi application should set the OnAutorize event handler of the TSQLiteDatabase object, which is a database connection wrapping object. Hook this event after a database connection is opened. For example:

procedure TForm1.DoAuthorize(ADB: TSQLiteDatabase; ACode: Integer; const AArg1, AArg2, AArg3, AArg4: String; var AResult: Integer);
begin
  Memo1.Lines.Add(Format('%d - %s - %s - %s - %s', [ACode, AArg1, AArg2, AArg3, AArg4]));

  // Deny any delete operation
  if ACode = SQLITE_DELETE then
    AResult := SQLITE_DENY
  else
    AResult := SQLITE_OK;
end;

FDConnection1.Connected := True;
TSQLiteDatabase(FDConnection1.ConnectionIntf.CliObj).OnAutorize := DoAuthorize;

You can find this sample in the FireDAC\Samples\DBMS Specific\SQLite\OnAuthorize folder.

Using SQLite low-level API

If you need to get the maximum SQLite data access performance, then you should consider using FireDAC SQLite API wrapping classes. This is a low-level thin object oriented API, which is used by the FireDAC SQLite driver. This API is not documented and is not officially supported.

The following example shows how to control transactions and fetch records using a parameterized SELECT command:

uses
  FireDAC.Phys.SQLiteWrapper;

procedure TForm1.FormCreate(Sender: TObject);
var
  oDB: TSQLiteDatabase;
  oTran: TSQLiteStatement;
  oStmt: TSQLiteStatement;
  i: Integer;
begin
  FDConnection1.Connected := True;
  oDB := TSQLiteDatabase(FDConnection1.CliObj);

  oTran := TSQLiteStatement.Create(oDB);
  try
    // start transaction
    oTran.Prepare('BEGIN');
    oTran.Execute;

    oStmt := TSQLiteStatement.Create(oDB);
    try
      // prepare statement
      oStmt.Prepare('select * from "Orders" where OrderID > :ID1 and OrderID < :ID2');

      // add bind variables (parameters)
      for i := 1 to oStmt.ParamDefsCount do
        TSQLiteBind.Create(oStmt.Params);

      // add column variables (fields)
      for i := 1 to oStmt.ColumnDefsCount do
        TSQLiteColumn.Create(oStmt.Columns).Index := i - 1;

      // set parameter values and execute
      oStmt.Params[0].AsInteger := 11000;
      oStmt.Params[1].AsInteger := 12000;
      oStmt.Execute;

      // fetch records and read columns
      while oStmt.Fetch do
        Memo1.Lines.Add(Format('OrderID: %d, CustomerID: %s',
          [oStmt.Columns[0].AsInteger, oStmt.Columns[1].AsString]));
    finally
      oStmt.Free;
    end;

    // commit transaction
    oTran.Unprepare;
    oTran.Prepare('COMMIT');
    oTran.Execute;
  finally
    oTran.Free;
  end;
end;