SQLite Database
From Xojo Documentation
Contents
- 1 The Database Property
- 2 Creating a Database
- 3 Creating a Table
- 4 Connecting to a Database
- 5 Retrieving, Adding, Changing and Deleting Data
- 6 Transactions
- 7 Auto-Incrementing Primary Keys
- 8 Encryption
- 9 Multiple User Support
- 10 Large Objects (BLOB)
- 11 Attaching Other SQLite Databases
- 12 Determining the SQLite Version
- 13 Example Projects
- 14 Videos
- 15 See Also
SQLite is the built-in database engine. You access it using the SQLiteDatabase class. SQLite is an open-source, public-domain embedded database that is used by all types of software. It is lightweight, fast and easy to use. It works great for desktop, web and iOS apps.
Unlike most databases, SQLite does not have a strictly typed columns. You can put data of any type in any column, regardless of the type the column has defined. You can learn more about SQLite by visiting their web site: www.SQLite.org
The Database Property
When working with a SQLite database you will want to have a database property available so you can easily use it throughout your app. For desktop and iOS projects you'll likely want this property to be on the App object or in a module. For web projects you usually want it to be on the Session object.
If that is on the App object you'd refer to it like this:
In a module you'd access it like this:
DB // if property is global
And on the Session object in a web project like this:
Creating a Database
SQLite databases are single files that typically exist on the same computer as the running app. For desktop apps, the SQLite database is usually in the Application Data folder for the operating system. For web apps, the database often resides alongside the web app itself on the server. In iOS apps, the database is often placed in the Documentation or Caches folder. To work with SQLite databases you use the SQLiteDatabase in desktop, web and console projects and use the iOSSQLiteDatabase class for iOS.
This desktop code creates a new SQLite database in the Application Data folder and could be on the App.Open event:
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")
// DB As SQLiteDatabase is a property on the App object
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
If App.DB.CreateDatabaseFile Then
// Use the database
End If
If the database already exists, then CreateDatabaseFile will connect to the existing database instead.
Do not place your database file alongside the desktop app itself. The app location is often read-only (especially for non-admin users) which will prevent you from being able to save any changes to the database.
Creating a Table
As covered in Database Concepts, tables are used to store data in the database. The following SQL command creates the Team table in the SQLite database:
CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));
SQL commands such as CREATE TABLE are sent to SQLite using the SQLExecute method of the SQLiteDatabase class. This code can be used to send the above SQL to SQLite:
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
If App.DB.CreateDatabaseFile Then
// Create the table
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
App.DB.SQLExecute(sql)
If App.DB.Error Then
MessageBox("Error: " + App.DB.ErrorMessage)
End If
End If
Connecting to a Database
If you are connecting to a database that already exists, you can instead call the Connect method:
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")
If dbFile.Exists Then
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
If App.DB.Connect Then
// Use the database
End If
End if
Retrieving, Adding, Changing and Deleting Data
For information on how to retrieve, add, change or delete data, refer to the UserGuide:Database Operations topic.
Transactions
As covered in UserGuide:Database Transactions, a Transaction is a block of processing. With SQLite, by default each command operates as its own implicit transaction. There may be times when you instead want to group many commands into a single transaction. This can be for data integrity purposes, but it also has the benefit of improving performance as well.
For example, if you update 1,000 rows each of which are in their own transaction then the database has to do more work as it has to create and commit a transaction for each row. Instead you can manually start a single transaction, do your 1,000 row updates and then commit once at the end. This is much faster as the database only has to create and update the single transaction. To start a transaction in SQLite, you use the "BEGIN TRANSACTION" SQL command, which you can send using the SQLExecute method:
Then when you are done, call the Commit method:
Auto-Incrementing Primary Keys
With SQLite, if a table has a single column specified as the INTEGER primary key, then that column auto-increments when a row is added to the table. This column is said to map to the internal rowid column that is on all SQLite tables.
However, just because SQLite has an internal rowid column, you should not rely on it as your primary key. Rowid values can be changed behind the scenes by SQLite and this could possibly corrupt any relationships in your database. Always create a separate primary key for your tables. When you INSERT data into a table with a primary key, you omit the primary key from the INSERT SQL:
INSERT INTO Team (Name)
VALUES ('Seagulls');
The above SQL is sent to SQLite using this code:
App.DB.SQLExecute("INSERT INTO Team (Name) VALUES ('Seagulls');"
After adding a row to the database, you can get the value of the last primary key value by calling the LastRowID method:
Encryption
SQLite databases can be encrypted. An encrypted database cannot be viewed at all unless you know the encryption key.
Encrypting a Database
To encrypt a new database, specify a value for the EncryptionKey property before you call CreateDatabaseFile or before you call Connect. This creates a new encrypted database:
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
App.DB.EncryptionKey = "MySecretKey123!"
If App.DB.CreateDatabaseFile Then
// Use the database
End If
To encrypt an existing database, call the Encrypt method, supplying the encryption key as a parameter:
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
If App.DB.CreateDatabaseFile Then
// Encrypt the database
App.DB.Encrypt("MySecretKey123!")
End If
You can also use the Encrypt method to change the encryption key of an encrypted database.
Decrypting a Database
Remember, you do not need to decrypt a database in order to use it -- just connect using the encryption key as shown above.
To decrypt an encrypted database, call the Decrypt method after you have connected to the database:
dbFile = SpecialFolder.ApplicationData.Child("MyDatabase.sqlite")
App.DB = New SQLiteDatabase
App.DB.DatabaseFile = dbFile
App.DB.EncryptionKey = "MySecretKey123!"
If App.DB.CreateDatabaseFile Then
App.DB.Decrypt // Decrypt the database
End If
Once you have decrypted the database, you no longer need to use the encryption key to access it.
Multiple User Support
SQLite is not technically a multiple user database. But by enabling a feature called Write-Ahead Logging (WAL), you can improve performance when multiple users are accessing the database from the same app.
This is most useful with web apps because they can easily have multiple users connected to the web app, each of which may be connecting to the database.
The SQLite developers do not recommend that you use SQLite on a shared network drive with separate apps sharing it. This can result in data corruption.
Large Objects (BLOB)
Large objects in a database are also called BLOBs (Binary Large Objects). You can add large objects to a database using the DatabaseRecord class, but there is a limitation on the amount of available memory.
If you need to store large objects in a database, but want to be able to read the data from the database sequentially, you use the SQLiteBlob class in conjunction with the CreateBlob and OpenBlob methods on the SQLiteDatabase class:
blob = App.DB.OpenBlob("Team", "Logo", 1, True)
If blob <> Nil Then
// Read BLOB
Var data As String
While Not blob.EOF
// Read 1000 bytes at a time
data = blob.Read(1000)
Wend
// Do something with the data
End If
Attaching Other SQLite Databases
Normally when you connect to a SQLite database, you are connecting to a single file. With SQLite it is possible to connect to multiple SQLite database files using one connection. You do this by "attaching" the additional databases.
The AttachDatabase method attaches the specified database file and lets you assign a prefix to use for all the tables in the attached database. The DetachDatabase method is to remove the attached database.
If App.DB.AttachDatabase(dbFile, "extra") Then
Var rs As RecordSet
rs = App.DB.SQLSelect("SELECT * FROM extra.Table")
// Process results...
End If
Determining the SQLite Version
It can sometimes be helpful to know exactly which version of SQLite is being used by your app. You can check this using the LibraryVersion property.
Example Projects
These projects demonstrate various SQLite features:
- Examples/Database/SQLite/DatabasePictureTest
- Examples/Database/SQLite/FTS5Example
- Examples/Database/SQLite/FTSExample
- Examples/Database/SQLite/SQLiteBackup
- Examples/Database/SQLite/SQLiteBlob
- Examples/Database/SQLite/SQLiteExample
- Examples/Database/SQLite/WebSQLiteExample
- Examples/iOS/Database/SQLiteExample
- Examples/iOS/Database/SQLiteInMemory
- Examples/iOS/Database/SQLiteVersion
- Examples/iOS/Controls/Table/TableDataSourceDatabase/TableDataSourceDatabase
- Examples/Sample Applications/EddiesElectronics
Videos
These videos also cover SQLite and its features:
See Also
SQLiteDatabase, iOSSQLiteDatabase, SQLiteBlob classes; UserGuide:Framework, UserGuide:Database Overview topic