SQLiteDatabase

From Xojo Documentation

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 fa-lock-32.png ThreadYieldInterval
Methods
AddDatabase CreateBlob Prepare
AddRow CreateDatabase RemoveDatabase
Backup Decrypt RollbackTransaction
BeginTransaction Encrypt SelectSQL
Close ExecuteSQL TableColumns
CommitTransaction LastRowID TableIndexes
Connect OpenBlob Tables
Constructors

Constructor(dbFile as FolderItem)


Notes

Xojo version SQLite version
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

More specific topics:

A call to SelectSQL returns a dynamic RowSet; you can move forward, backward, or jump to the beginning or end as much as you like.

SQLite supports transactions, both for schema changes and for data changes. A transaction is started automatically when you make any change to the database and is ended by calling either the Commit or Rollback methods of the database class.

All four RowSet navigation methods are supported for SQLite: MoveToFirstRow, MoveToNextRow, MoveToPreviousRow, and MoveToLastRow.

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.

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

fa-info-circle-32.png
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
fa-info-circle-32.png
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 the DatabaseRecord.BlobColumn method or DatabaseField.StringValue 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. DatabaseRow.BooleanColumn and 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 in conjunction with DatabaseRow.CurrencyColumn and DatabaseColumn.CurrencyValue.
Date SQLite does not have a DATE data type. Dates are stored as text using the format YYYY-MM-DD when using DatabaseRecord.DateColumn and DatabaseColumn.DateValue. To store a DateTime value, you should manually save Date.SQLDateTime as a string.
Double Use the REAL data type to store Double values in conjunction with DatabaseRow.DoubleColumn and DatabaseColumn.DoubleValue.
Integer Use the INTEGER data type to store Integer values with DatabaseRow.IntegerColumn and DatabaseColumn.IntegerValue. SQLite always stored 64-bit integer values.
Int64 Use the INTEGER data type to store Int64 values with DatabaseRow.Int64Column and DatabaseColumn.Int64Value. SQLite always stored 64-bit integer values.
Picture Use the BLOB data type to store Pictures with DatabaseRow.PictureColumn and DatabaseColumn.PictureValue.
String Use the TEXT data type to store String values with DatabaseRow.Column and 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 DatabaseRow.Column and 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, just 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:

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

See Also

Database Class, DatabaseRow, PreparedSQLStatement, SQLitePreparedStatement, RowSet, iOSSQLiteDatabase classes.