Table Position

SQLite supports ROWID as the table position. The default position is ROWID.

Get Definition

For SQLite, if the primary key is a single column that auto-increments, the Get Definition utility will not bring the key into the table structure. This is because there is not a separate index using the column. The index internally refers to ROWID.

Hints

Hints are not supported and will be disregarded.

Isolation level

Isolation level cannot be changed. Any changes in the DBMS will be disregarded.

Joins

Inner and Outer joins should be implemented using the same syntax as in the SQL Server.

Transactions

Physical /deferred transactions are supported.

Array fetching

SQLite does not support array fetching. Therefore, the array size setting will be disregarded and the records will be fetched one record at a time.

Parallel execution

A single Magic xpa engine, or multiple engines on the same machine, may access SQLite via parallel programs. In such a case, only one thread/EXE can open a physical transaction, since the SQLite file is exclusively locked.

APG

Using the Automatic Program Generator (APG) in DSQL SELECT statements will result in the Null Allowed column property being set to Yes.

Identity

Identity should be defined as Type INTEGER PRIMARY KEY.

SQLite does not allow more than one INTEGER PRIMARY KEY fields.

Identity fields are stored as INT64 data types in SQLite; therefore, you should create a Numeric field to hold 8 bytes of data.

Encryption

The SQLite database supports encryption (Since version: 3.0). The encryption password is defined in the User Password database property.

It is not possible to migrate an existing non-encrypted SQLite database to an encrypted database. If you have an existing application with an SQLite database and you want to use encryption, you should define a different database with encryption and synchronize your data to this database.