SQLiteDatabase

Class (inherits from Database)

The SQLiteDatabase class provides access to the SQLite data source, a.k.a., database engine or database back-end. For iOS apps, use iOSSQLiteDatabase.

Properties

DatabaseFile	LoadExtensions	Timeout
EncryptionKey	ShortColumnNames	WriteAheadLogging
LibraryVersion	ThreadYieldInterval

Constructors

Constructor(dbFile as FolderItem)

Notes

Xojo version	SQLite version
2019r3	3.29.0
2019r2	3.28.0
2019r1	3.26.0
2018r4	3.25.3
2018r3	3.24.0
2018r2	3.23.1
2018r1	3.22.0
2017r3	3.20.1
2017r2	3.19.3

SQLite supports a subset of SQL/92 and SQL/99, including queries that involve self-joins, aggregate functions and more.

For complete documentation of SQLite, refer to the official SQLite documentation: http://sqlite.org/docs.html

Network Access

SQLite is not a server database and according the SQLite developers should not be placed on a shared network drive for access by multiple client apps as this can lead to database corruption.

When To Use SQLite

If you require a database that is shared with multiple client apps you can switch to a server database such as PostgreSQL or MySQL. There are also products that wrap a server around a SQLite database, such as CubeSQL. Another common solution is to create a web service that handles all requests to the SQLite database. Your clients communicate with the web service, which is the only app that then communicates with the SQLite database.

Threading

SelectSQL and ExecuteSQL statements do not block when called from within Threads.

Transactions

By default, SQLite does a Commit after each SQL command that changes the database or its data.

To ensure data integrity and to improve performance, you should create a transaction and do your database changes in the transaction. To start a transaction use this command:

db.ExecuteSQL("BEGIN TRANSACTION") // db is an instance of SQLiteDatabase

When you are finished with the database changes, you can close the transaction by calling Commit to make the changes permanent:

db.Commit

If you want to cancel the changes, you can use Rollback:

db.Rollback

Encrypted Databases

You cannot encrypt a database within a transaction. The encryption must be done outside a transaction.

2017r3 and prior: AES-128 encryption is always used.

2018r1 and later defaults to AES-128, but AES-256 can also be used by including the prefix "aes256:" before the rest of the encryption key.

For more information about the encryption, refer to the SQLite Encryption documentation.

To create an encrypted database, specify a value for the EncryptionKey property. The EncryptionKey property must be set before calling either Connect or CreateDatabase. In other words, write something like this to create a new database:

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
Try
db.CreateDatabase
Catch error As IOException
// handle error here
MessageBox(error.Message)
End Try

When you open an encrypted database file, you need to supply the key:

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
Try
db.Connect
Catch error As IOException
// handle error here
MessageBox(error.Message)
End Try

Result Codes

Operations the don't involve files and cause an error will result in SQLiteDatabase raising a DatabaseException. Error information is then available in the DatabaseException.Error and DatabaseException.Message properties.

SQLite error codes:

Error code	Error Message
0	Not an error
1	SQL logic error or missing database
2	Internal SQLite implementation flaw
3	Access permission denied
4	Callback requested query abort
5	Database is locked
6	Database table is locked
7	Out of memory
8	Attempt to write a read/only database
9	Interrupted
10	Disk I/O error
11	Database disk image is malformed
12	Table or record not found
13	Database is full
14	Unable to open database file
15	Database locking protocol failure
16	Table contains no data
17	Database schema has changed
18	Too much data for one table row
19	Constraint failed
20	Datatype mismatch
21	Library routine called out of sequence
22	Kernel lacks large file support
23	Authorization denied
24	Auxiliary database format error
25	Bind or column index out of range
26	File is encrypted or is not a database
200	Not connected

Primary Keys

All SQLite tables have an Integer Primary Key column. If you don't explicitly create such a column, one will be created for you with the name "rowid". If you create your own INTEGER PRIMARY KEY column, then rowid acts as an alias to that column. This means that a query that includes rowid will instead return the column that is the primary key.

This SQL will create an Employees table with "EmployeeID" as the primary key:

 CREATE TABLE Employees (EmployeeID INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT)

You can also use this SQL:

 CREATE TABLE Employees (EmployeeID INTEGER, FirstName TEXT, LastName TEXT, PRIMARY KEY (EmployeeID))

The above syntax is used when creating a table with a multi-part primary key.

Now consider the following queries:

 SELECT * FROM Employees

 SELECT rowid,* FROM Employees

 SELECT rowid, FirstName, LastName FROM Employees

In all of these cases, you will get the EmployeeID column in place of the rowid column.

If you have your own primary key column but still wish to get the rowid, you can do so using the SQL AS keyword. The following example returns the columns rowid, EmployeeID, FirstName, LastName:

 SELECT rowid AS rowid, * FROM tablename

If you don't explicitly define your own INTEGER PRIMARY KEY column, you won't get the 'rowid' column unless you specifically include it in the list of columns to include in your query:

 SELECT rowid, * FROM TableName

You should always specifically define a primary key for your tables and not rely on the rowid column. When there is no specific primary key, the values for rowid are not guaranteed to remain the same which could cause problems if you use the rowid values in your apps or in foreign keys. In particular, the rowid values could be renumbered when data is deleted from a table and when the database is cleaned up using the VACUUM command.

Foreign Keys

SQLite supports foreign keys, but they are not enabled by default. To enable them, you use the SQLite PRAGMA command each time you connect to the database:

 PRAGMA foreign_keys = ON;

You can send this PRAGMA command to SQLite from Xojo using SQLExecute:

Var db As New SQLiteDatabase
Try
db.Connect
// Enable foreign keys
db.ExecuteSQL("PRAGMA foreign_keys = ON;")
Catch error As DatabaseException
MessageBox("Database error: " + error.Message)
End Try

Data Types

SQLite handles data types differently than most other database.

In particular, it does not use strong data typing. Although a column and be defined using a data type (called a storage class or affinity with SQLite), you can still put any type of data in any column.

To start, SQLite only supports these data types:

NULL
INTEGER
REAL
TEXT
BLOB

Refer to the official SQLite Data Type page here: http://www.sqlite.org/datatype3.html

The following table describes how to use the SQLite data types to store data from built-in data types with DatabaseRecord or DatabaseField:

FieldType	Description
Blob	BLOB stands for Binary Large OBject. It is a column that contains large string data. This data is not altered in any way (no encodings are applied), so you can use a BLOB to store pictures, files or any other binary data. Use DatabaseColumn.Value to read or save BLOB data. Alternatively you can use the SQLiteBlob class to handle BLOB data incrementally.
Boolean	SQLite does not have a BOOLEAN data type. Booleans are stored using 0 or 1 in an INTEGER column or as "true" or "false" in a TEXT column. DatabaseColumn.BooleanValue will correctly convert the above values to a boolean. Other values in a column are undefined when attempting to convert to a boolean.
Currency	Use the REAL data type to store Currency values with DatabaseColumn.CurrencyValue.
Date / DateTime	SQLite does not have a DATE or DATETIME data type. Dates are stored as text using the format YYYY-MM-DD when using DatabaseColumn.DateTimeValue. To store a DateTime value, you should manually save Date.SQLDateTime as a string.
Double	Use the REAL data type to store Double values using DatabaseColumn.DoubleValue.
Integer	Use the INTEGER data type to store Integer values with DatabaseColumn.IntegerValue. SQLite always stored 64-bit integer values.
Int64	Use the INTEGER data type to store Int64 values with DatabaseColumn.Int64Value. SQLite always stored 64-bit integer values.
Picture	Use the BLOB data type to store Pictures with DatabaseColumn.PictureValue.
String	Use the TEXT data type to store String values with DatabaseColumn.StringValue. SQLite converts all text in TEXT columns to UTF-8 encoding. If you want to preserve the original encoding, use a BLOB column type instead. Because of the way SQLite stores all data, you can use DatabaseColumn.StringValue to get the values of data in any column, even if is not specifically a TEXT column.

In-Memory Database

An "in-memory database" is a SQLite database that exists only in memory; there is no related file on disk. It works exactly as a SQLite database except it is very fast and completely temporary.

To create an in-memory database, create a new SQLiteDatabase instance and connect to it:

Var inMemoryDB As New SQLiteDatabase
Try
inMemoryDB.Connect
// Can now use inMemoryDB
Catch error As DatabaseException
MessageBox("Connection failed: " + error.Message)
End Try

You can use the Backup method to save an in-memory database to a file on disk.

Modified Rows

To find out the number of rows that were modified by an UPDATE or INSERT command, you can call the SQLite changes function:

SELECT changes();

Extensions

Extentions are not loaded by default. Use the LoadExtensions property to enable them as necessary.

Performance Tips

To improve the performance of SQLite DB access, you can turn on Write-Ahead Logging by setting the WriteAheadLogging property to True. Additionally, to increase the user's perception of performance, you may want to place long-running queries within a Thread.

You can also increase the page size that SQLite uses by changing the page_size setting using the PRAGMA command. The default page_size is 4096, but a larger size can help reduce I/O. To change the setting, give it a new value after you create or connect to the database but before you send any other commands that would cause database I/O. The page_size is permanently retained for the database after it is set:

Var db As New SQLiteDatabase
Try
db.Connect
// Set page_size before any other commands are sent
db.ExecuteSQL("PRAGMA page_size = 8192;")
db.ExecuteSQL("CREATE TABLE Foo (c1, c2);")
Catch error As DatabaseException
MessageBox("A database error occurred: " + error.Message)
End Try

For more information about PRAGMA commands, visit the official SQLite documentation:

PRAGMA statements

Periodically use the ANALYZE command to update the internal information that SQLite uses to make better query planning choices. You'll probably want to do this any time you add or remove large amounts of data from a table or when the SQLite database version is updated.

db.ExecuteSQL("ANALYZE")

Sample Code

The following example creates a new SQLite database:

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("MyDB.sqlite")
Try
db.CreateDatabase
// proceed with database operations...
Catch error As DatabaseException
MessageBox("Database not created. Error: " + error.Message)
Return
End Try

The following example opens an existing SQLite database.

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("MyDB.sqlite")
Try
db.Connect
// proceed with database operations here..
Catch error As DatabaseException
MessageBox("The database couldn't be opened. Error: " + error.Message)
Return
End Try

The following example adds a record to a table.

Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem("Employees.sqlite")
Try
db.Connect
db.SQLExecute("BEGIN TRANSACTION;")
db.SQLExecute ("INSERT INTO Employees (Name,Job,YearJoined) VALUES " _
+ "('Dr.Strangelove','Advisor',1962);")
db.CommitTransaction
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
db.RollbackTransaction
End Try