Additional
Developing Delphi applications with SQLite and AnyDAC
Posted by Support [Nov 09, 2010]
The SQLite driver of AnyDAC for Delphi rounds off the DA-SOFT offering of the AnyDAC by adding an in-house solution for the embedded Delphi database applications, in contrast to other supported DBMS's, which may require purchasing, downloading, installing and configuring.

Introduction

This is the first article in the set of our articles, explaining how to combine and use AnyDAC for Delphi and some specific DBMS. We decided to start from SQLite. If you are AnyDAC beginner, then please read first the Getting Started series of articles.

 

This articles has few sections:

 

  • Benefits of Combining AnyDAC and SQLite: while each of the products is a state of the art solution for the specific tasks, together they allow to solve these tasks more effectively.
  • Using SQLite Database: explains how to create, connect to and manage SQLite database in Delphi application.
  • SQLite Data Types: SQLite has an unique data type system. Without understanding of how it works, will be hard to effectively store and retrieve the data in Delphi applications.
  • SQLite SQL Commands: main aspects of SQLite SQL dialect for the Delphi application developers.
  • SQLite Transactions, Locking and Cursors: explains how to work with transactions in SQLite environment.
  • Extending SQLite Engine from Delphi Application: as an embedded DBMS, SQLite engine may be extended by the Delphi application code.
  • Advanced SQLite Techniques: finally we want to introduce some advanced SQLite concepts, like updates logging and SQL authorization.

Benefits of Combining AnyDAC and SQLite

SQLite Database

SQLite Logo

The SQLite is an embedded SQL database engine, most widely deployed in the world. SQLite may be used to build the complete single user database Delphi applications or provide database facilities to other Delphi application kinds. Read more about appropriate uses for SQLite.

 

The SQLite features are:

 

  • 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.
  • 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.
  • Cross-platform: Unix (Linux and Mac OS X), OS/2, and Windows (Win32 and WinCE) are supported out of the box. Easy to port to other systems.
  • Sources are in the public domain. Use for any purpose.

AnyDAC

AnyDAC Logo

 

While AnyDAC allows you to connect to the different DBMSs using the same API, it offers a wide range of the features unified across all supported DBMSs. But it is not a generic "lowest-common-denominator" DAC, but, in fact, hand-tuned to provide the best possible data access speed to the database types it supports. In a way it's more like a combination of DACs, such as InterBase Objects (IBO), Devart ODAC and SDAC, etc. This approach simplifies as a product learning, as it allows you to build a flexible and highly effective solution without digging into the low-level details of the different DBMS APIs.

Together

AnyDAC SQLite driver provides superb integration with SQLite, giving great performance and feature set for Delphi applications. The AnyDAC SQLite driver rounds off the DA-SOFT Technologies offering of the AnyDAC by adding an in-house solution for the embedded database applications, in contrast to other supported DBMS's, which may require purchasing, downloading, installing and configuring. And it does that without giving up the SQLite flexibility and feature set. The combined product benefits are:

 

  • Simplicity:
    • SQLite engine is fully embedded into Delphi application and does not require any external files. AnyDAC provides precompiled sqlite3.obj with most optimal settings for most Delphi applications, which will be linked into Delphi application.
    • SQLite requires minimal administration. Still the application may need to verify the database, compute statistics, perform DB backup.
  • Performance:
    • 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 AnyDAC.
    • AnyDAC SQLite driver is very well optimized, giving good performance gain other similar products, while the CPU load is largely reduced.
  • Features:
    • SQLite provides complete confidentiality and integrity with encryption facility for Delphi database applications.
    • SQLite may be used as a pure in-memory database.
    • AnyDAC SQLite driver offers support practically for all SQLite features, which may be actual for a Delphi database application.
  • Support:
    • The AnyDAC Team provides support for SQLite driver and SQLite engine together as for a single solution.
    • We pack tested and compiled SQLite binaries with AnyDAC. So, always up-to-date builds of the AnyDAC and SQLite.
  • Price:
    • You need to buy only AnyDAC, no other expenses to add embedded database engine to your application. As no SQLite runtime license is required.

 

But note, SQLite does not allow to scale an Delphi application to multi-user or client-server mode. If you need an embedded DBMS, which later may be scaled up, then consider to use Firebird Embedded (freeware), Interbase ToGo (commercial) or MySQL Embedded (commercial).

Using SQLite Database

Connecting to SQLite database from Delphi application

To link the AnyDAC SQLite driver to the Delphi application add TADPhysSQLiteDriverLink to a form or data module. To connect to SQLite database, Delphi application should specify SQLite driver parameters, at least:

DriverID=SQLite
Database=[path to your SQLite database file]

By default, all SQLite driver settings are set to enable high-performance single user and single connection access to a database in a stable environment. Depending on the Delphi application needs, we recommend to change the SQLite connection parameters:

 

 NApplication specificsDescription
 1Reading 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 pages size).
 2Exclusive updating of DB.Consider to set JournalMode to WAL.
 3Long updating transactions.Set CacheSize to a higher number of pages, that will allow to run transactions with many updates without overloading the memory cache with dirty pages.
 4Multiple 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.
 5Multiple concurrent updating threads.See (4). Also set SharedCache to False to minimize locking conflicts.
 6Multiple concurrent updating transactions.See (4) or (5). Also set TxOptions.Isolation to xiSnapshot or xiSerializible to avoid possible transaction deadlocks.
 7High safety.Set Synchronous to Full to protect DB from the committed data losts. Also see (3). Consider to encrypt database to provide integrity.
 8High confidentiality.Encrypt database to provide confidentiality and integrity.

 

More about controlling SQLite connection you can find:

 

 

Note, that in time of v 3.7.0 the WAL mode is not yet stable in the multi-threading environment. The WAL usage may lead to dead-locks and other issues.

Creating SQLite database from Delphi application

By default a SQLite database will be created if it does not exist at a connection establishment time. To make that more explicit the Delphi application may specify:

OpenMode=CreateUTF8 | CreateUTF16

The difference in SQLite parameter value is an encoding, that will be used for the database. For the new database we strongly recommend to set page_size to 4096 or even more, if your database will have tables with many rows. That may be done by specifying at creation time:

SQLiteAdvanced=page_size=4096

Also consider to specify parameters using SQLiteAdvanced:

 

 

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

Using encrypted SQLite database in Delphi application

One of the distinctive SQLite features among other supported by AnyDAC embedded DBMS is the database AES / DES based encryption. It allows to make database file content really confidential and enforce integrity control on the database file. Read this article for details.

Using SQLite in-memory database in Delphi application

Next SQLite unique feature is the ability to work with pure in-memory databases. That means, no files will be created to store database objects and all will be kept in the memory. So, more security, more performance, less requirements to the environment rights for a Delphi application.

To create and open a SQLite in-memory database the Delphi application should use parameters:

DriverID=SQLite
DATABASE=:memory:

One AnyDAC customer has SQLite database on a shared network resource. The database is a read/only products catalogue with many stored product attributes. To radically improve performance, the customer used TADSQLiteBackup to move the database in whole into the in-memory database. The sample code:

ADSQLiteBackup1.Database := '\\srv\db\data.sdb';
ADSQLiteBackup1.DestDatabase := ':memory:';
ADSQLiteBackup1.DestMode := smCreate;
ADSQLiteBackup1.Backup;
 
ADConnection1.DriverName := 'SQLite';
ADConnection1.Params.Text := 'Database=:memory:';
ADConnection1.Open;

Working with Unicode and SQLite database from Delphi application

AnyDAC supports Unicode Delphi's (starting with Delphi 2009) in full. For SQLite that means:

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

Using multiple SQLite databases in Delphi application

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

ADConnection1.ExecSQL('ATTACH ''c:\hr.sdb'' AS hr');
ADConnection1.ExecSQL('ATTACH ''c:\products.sdb'' AS prod');
ADQuery1.Open('select * from orders o left join hr.employees e on o.EmployeeID = E.ID ' +
              '                       left join prod.products p on o.ProductID = p.ID');

Note, that AnyDAC interprets a database name as a catalog name.

Manage SQLite database from Delphi application

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

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

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

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

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

  • SQLite database may become corrupted or malformed. To verify it integrity use the TADSQLiteValidate.CheckOnly method. Note, to repair a broken SQLite database, Delphi application needs to restore it from a backup. CheckOnly method will use OnProgress event handler to notify about issues. This method performs PRAGMA integrity_check command.

procedure TForm1.ADSQLiteValidate1Progress(ASender: TADPhysDriverService;
  const AMessage: String);
begin
  Memo1.Lines.Add(AMessage);
end;
 
ADSQLiteValidate1.Database := 'c:\db.sdb';
ADSQLiteValidate1.OnProgress := Form1Progress;
ADSQLiteValidate1.CheckOnly;

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

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

SQLite Data Types

Mapping SQLite to AnyDAC Data Types

SQLite has "type less" data type system. Practically that 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 SQLite approach more compatible with other DBMS's and Delphi, and more comfortable for the Delphi application developers, AnyDAC recognizes data type names as described in the table:

Type nameDescription
ROWID | _ROWID_ | OIDdtInt64, Attrs = [caSearchable, caAllowNull, caROWID]
BIT | BOOL | BOOLEAN | LOGICAL | YESNOdtBoolean
TINYINT | SHORTINT | INT8 [UNSIGNED]dtSByte / dtByte
BYTE | UINT8dtByte
SMALLINT | INT16 [UNSIGNED]dtInt16 / dtUInt16
WORD | UINT16 | YEARdtUInt16
MEDIUMINT | INTEGER | INT | INT32 [UNSIGNED]dtInt32 / dtUInt32
LONGWORD | UINT32dtUInt32
BIGINT | INT64 | COUNTER | AUTOINCREMENT |
IDENTITY [UNSIGNED]
dtInt64 / dtUInt64
LONGLONGWORD | UINT64dtUInt64
FLOAT | REAL | DOUBLE | SINGLE [PRECISION] [(P, S)]dtDouble / 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 | SMALLDATEdtDate
DATETIME | SMALLDATETIMEdtDateTime
TIMESTAMPdtDateTimeStamp
TIMEdtTime
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
TEXT | MEDIUMTEXT | LONGTEXT | CLOB | MEMO | NOTE |
LONG | LONG TEXT | LONGCHAR | LONGVARCHAR | TINYTEXT
dtMemo
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

Special SQLite Data Types

To add an auto incrementing column to a table, you have to define column as INTEGER PRIMARY KEY AUTOINCREMENT. The column type will be mapped to dtInt32, Attrs = [caAutoInc].
The columns with ROWID, _ROWID_ or OID type names are considered as identifying row columns. The column type will be mapped to dtInt64, Attrs = [caSearchable, caAllowNull, caROWID]. SQLite ROWID is the fastest way to get an access to a specific row:

SELECT * FROM Orders WHERE ROWID = :RID

Adjusting AnyDAC Mapping

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

 

ParameterDescription
StringFormat = Choose | UnicodeWhen Unicode, then all dtAnsiString and dtMemo will be represented to a client as dtWideString and dtWideMemo. 
GUIDFormat = String | Binary When Binary, then dtGUID will be stored in a database as a TGUID binary representation. When String, then - as a string in {xxxxxxx} format. Binary requires less space in DB, String is more readable. 
DateTimeFormat = String | BinaryWhen Binary, then dtDate, dtTime, dtDateTime will be stored in a database using Joolean date format as a double value. When String, then as a character string. Binary requires less space in DB, String is more readable.

For expressions in a SELECT list, SQLite does not provide any type name information. So, for Delphi application AnyDAC will describe those columns by default as dtWideString. To specify expression data type, you should append :: to the column alias:

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

In some cases an Delphi application may need to get a mapping more close to the native SQLite data representation. For example, map TEXT columns to dtAnsiString and INT columns to dtInt64. Then you may use AnyDAC mapping rules:

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

SQLite SQL Commands

SQL Dialect

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

 

SQLite SQL Command Batches

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

with ADQuery1.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;
ADQuery1.Open;
// process here the DBMS list
ADQuery1.NextRecordSet;
// process here the programming languages list 

SQLite SQL Parameters Binding

SQLite supports named parameters. So, for each unique named parameter in a SQL command, AnyDAC will create single TADParam in a Delphi application. For example:

ADQuery1.SQL.Text := 'select * from Orders where OrderDate >= :OD or ShipDate >= :OD';
// ADQuery1.Params -> has single OD parameter 

That is different from SQL Server, for example. Where for each parameter entry will be created a single TADParam.

SQL script dialect 

AnyDAC TADScript does not support SQLite syntax, where script control commands are starting from '.'. DA-SOFT Technologies is considering to implement this feature.

SQLite Transactions, Locking, Threads and Cursors

Locking and concurrent updates

We will suggest to read the following original SQLite articles:

 

 

Note, that SQLite likes to lock the database tables at updates. The following settings affect on the concurrent access:

 

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

Supported isolation modes

SQLite supports transactions in full, including check points. SQLite as a rather file-server DBMS likes to lock a database. Further is a list of isolation modes supported by SQLite:

 

ModeCorresponds to 
xiDirtyReadPRAGMA read_uncommitted = 1
xiReadCommitted BEGIN TRANSACTION DEFERRED
xiRepeatableReadThe same as xiReadCommitted.
xiSnapshotBEGIN TRANSACTION IMMEDIATE
xiSerializibleBEGIN TRANSACTION EXCLUSIVE

 

Transactions and Array DML 

Surrounding writing commands into a transaction will radically improve SQLite performance. At first that is notable at large data inserts. So, using AnyDAC Array DML feature, surround it into a transaction to get the best performance:

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

Transactions and cursors 

SQLite does not allow to perform a transaction rollback, when there are queries with not yet fetched result sets. To workaround that, AnyDAC will fetch all remaining records from a result set on a Rollback method call. See the FetchOptions.AutoFetchAll.

Extending SQLite Engine from Delphi Application

Custom Functions

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

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

procedure TForm1.ADSQLiteFunction1Calculate(AFunc: TSQLiteFunction;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsInteger := AInputs[0].AsInteger * AInputs[1].AsInteger;
end;
 
ADSQLiteFunction1.DriverLink := ADPhysSQLiteDriverLink1;
ADSQLiteFunction1.FunctionName := 'XmY';
ADSQLiteFunction1.ArgumentsCount := 2;
ADSQLiteFunction1.OnCalculate := ADSQLiteFunction1Calculate;
ADSQLiteFunction1.Active := True;

This function may be used as:

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

A function may also call AnyDAC method to query a database.

The above and other function samples you can find in AnyDAC\Samples\DBMS Specific\SQLite\UserFunc folder. AnyDAC implements and installs to each SQLite connection about 50 functions, which are standard de-facto for many DBMS and are implemented by the AnyDAC local expressions engine.

 

And the video provided to us by Ron Grove:

 

Custom Collations

SQLite stores and handles all character data either in UTF8, either in UTF16, depending on the OpenMode connection parameter. But when a client is asking SQLite to compare or sort a character data, then SQLite must know what rules to use for that. The rules are known as a collation.

SQLite has few build-in collations. None of them will produce a correct sorting of German, Cyrillic, Arabian, etc phrases. For that you have to use TADSQLiteCollation component. To build your own collation, set CollationName, Flags and Locale. Then set Active to True to register the collation with SQLite engine. For example:

ADSQLiteCollation1.DriverLink := ADPhysSQLiteDriverLink1;
ADSQLiteCollation1.CollationName := 'UTF16NoCase';
ADSQLiteCollation1.Flags := [sfIgnoreCase];
ADSQLiteCollation1.Active := True;

Note, that above component setup with CollationKind=scCompareString implements a standard Win32 Unicode collation. As an option, the application may implement completely custom collations setting CollationKind=scCustomUTF16 or scCustomUTF8 and implementing OnCompare event handler.

And how to use this collation:

SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase

To specify default collation for a column you can do:

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

Note, that there is no ability to specify default collation for a connection, a database or a table. The above collation samples you can find in AnyDAC\Samples\DBMS Specific\SQLite\UserCollation folder.

The Database Events

The SQLite does not have procedural programming language. But it has triggers ! Funny, but everything there must be performed using the regular SQL statements. Internet has many examples for them. AnyDAC adds ability to notify an Delphi application from a DB trigger about some events, like a data is changed.

For that AnyDAC uses similar to Firebird approach and registers POST_EVENT custom function. To call it from a trigger do:

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

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

ADEventAlerter1.Names.Text := 'Orders';
ADEventAlerter1.Options.Synchronize := True;
ADEventAlerter1.OnAlter := DoAlert;
ADEventAlerter1.Active := True;
 
procedure TForm1.DoAlert(ASender: TADCustomEventAlerter;
  const AEventName: String; const AArgument: Variant);
begin
  if CompareText(AEventName, 'Orders') = 0 then
    qryOrders.Refresh;
end;

Advanced SQLite Techniques

Hooking Database Updates

SQLite provides an unique API allowing to monitor all updates to a database. This feature may be used, for example, to log all updates to a DB. To work with this API, a Delphi application should set OnUpdate event handler of the TSQLiteDatabase object, which is a database connection wrapping object. The right time to hook this event is after a database connection will be 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;
 
ADConnection1.Connected := True;
TSQLiteDatabase(ADConnection1.ConnectionIntf.CliObj).OnUpdate := DoUpdate;

This sample you can find in AnyDAC\Samples\DBMS Specific\SQLite\OnUpdate folder.

Controlling Database Access Rights

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

 

  • An application may restrict rights depending on a end-user license. Demo license - less possibilities, full license - all possibilities.
  • Multi-tier data access frameworks may use their own user concept and to control data access rights using some generic approach.

 

And again, SQLite provides an unique feature allowing to authorize or not SQL commands. To work with this API, a Delphi application should set OnAutorize event handler of the TSQLiteDatabase object, which is a database connection wrapping object. The right time to hook this event is after a database connection will be 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;
 
ADConnection1.Connected := True;
TSQLiteDatabase(ADConnection1.ConnectionIntf.CliObj).OnAutorize := DoAuthorize;

This sample you can find in AnyDAC\Samples\DBMS Specific\SQLite\OnAuthorize folder.

Summary

This article has provided the benefits of combining SQLite and AnyDAC to get premier embedded DBMS solution for Embarcadero Delphi and C++ Builder. The articles describes all aspects of the AnyDAC for Delphi SQLite driver usage.

 

If you like to provide your feedback - feel free to post your comments here or send them directly to This e-mail address is being protected from spam bots, you need JavaScript enabled to view it .

 

Many thanks to Ron Grove (Evanoah I/T Services) for his video.


LIST OF COMMENTS


1/2. Virtual table
Posted by burdi - Dec 06, 2010
Website
It would be nice to read here about the use of virtual table, especially how to define virtual table with TDataSet as source :)

2/2. RE: Virtual Table
Posted by Support - Dec 26, 2010
Website
We will, when this feature support will be ready.

Add Comments