It’s very usual to use encrypted SQLite databases in our Xojo projects where we expect to get the maximum read speed from them. But the truth is that encrypting the data in these databases can introduce a penalty in our queries, both from read and writing/updating data to them. How can we improve this? One technique is the creation of a new in-memory based SQLite database, where we will be able to copy the table (or tables) we are interested in getting the maximum speed possible with. Continue reading to see how to do this.
You can further modify this technique so you can work with your databases in a mixed way; that is, where we want to simultaneously execute a query involving both an encrypted database table and an in-memory table database.
But for this example code we are only focused on opening an encrypted SQLite database (based on disk) as a new SQLite class instance, and using next a SQL sentence to create a new in-memory database (named RAMDB in the example), creating the same table on the RAM database next and then copying the data from the source table during the process.
From that time on, you will be able to use the new table on RAM at light speed!
Another SQL command you might find of interest, and that you can use in all your SQLite databases where you expect to get a gain in the read speed, is to use the exclusive mode, using for that PRAGMA LOCKING_MODE = Exclusive.
However, and focusing on the tip itself, this is the snippet of code you can use:
Var db As SQLiteDatabase = New SQLiteDatabase
db.DatabaseFile = // Assign here a FolderItem to the SQLite database file on disk
db.EncryptionKey = "aes256:" + // Add here a String with the same password that the one used to encrypt the data
If db.Connect = False Then
Var re As New RuntimeException
re.message = "Error trying to connect to the database"
Raise re
End If
db.ExecuteSQL("PRAGMA LOCKING_MODE = Exclusive") // Improves the read access to the database
// We create a in-memory database, 'attaching' it with the disk based SQLite database
db.ExecuteSQL("attach database ':memory:' as 'RAMDB'")
// Copying all the contents from the source table
// to the newly created table on the RAM SQLite database
db.ExecuteSQL("create table RAMDB.product_table as select * from main.product_table")
As you can see, the second line of code expects a valid FolderItem instance pointing to the SQLite database on disk. In addition, you need to provide the String to the password used originally to encrypt the data on the source database. This example assumes that this has been done using the AES 256 option, stronger than the one using a length of 128 bits and the only option available before Xojo 2018r1.
Finally, in the last sentence db.SQLExecute you’ll have to change the table name you want to copy from the source database to the new RAM based one (product_table in the example).
Javier Rodriguez has been the Xojo Spanish Evangelist since 2008, he’s also a Developer, Consultant and Trainer who has be using Xojo since 1998. He manages AprendeXojo.com and is the developer behind the GuancheMOS plug-in for Xojo Developers, Markdown Parser for Xojo, HTMLColorizer for Xojo and the Snippery app, among others.