SQLite3BestPractices

MK AP-PH encountered performance problem after deployed sqlcipher (a full database Encryption for SQLite; Sqlcipher & Sqlcipher API: http://sqlcipher.net/sqlcipher-api/), the root case is most likely due to SQLCipher key derivation. SQLCipher using PBKDF2 to perform key derivation (i.e. thousands of SHA1 operations) to defend against brute force and dictionary attacks when opening a database, and this is deferred until the first use of the database, which happens to occur in setLocale, and it’s time costing.

Additional, “SQLcipher uses just-in-time key derivation at the point it is first needed for an operation. This means that the key (and any options) must be set before the first operation on the database. As soon as the database is touched (e.g. SELECT, CREATE TABLE, UPDATE, etc.) and pages need to be read or written, the key is prepared for use.” So you will have to repeat below logic in program:

Local db = sqlite3.open(path)
db: exec(PRAGMA key = ‘xxx’;)
db: exec(your SQL statement)
db: close()

The way to improve performance is to reduce using frequency of “sqlite3.open/close” in the program. Based on Song’s suggestion, since new framework version allow the whole APP share one VM, you may define the global method “getDatabaseByUserID” in initialize.lua(calling environment on app level) to cache the database connection so that it can be used multiple times without having to open and key the database repeatedly. Opening the database once during APP startup, and subsequent access on the same database handle will not trigger key derivation.

Code level example as below:

- initialize.lua
local dbmap = {}
function getDatabaseByUserID(userid) - getDatabaseByUserID方法在APP的全局表上
if not dbmap[userid] then
dbmap[userid] = sqlite3.open(somepathbyuserid)
end
return dbmap[userid]
end

— somepage.lua
local getDatabaseByUserID = getDatabaseByUserID

function someFunction()
— 先取db再用
local db = getDatabaseByUserID(userid)
db:exec(“select * from sometable”)
end

More details, pls refer to: https://github.com/HydraFramework/Hydra/wiki/VM#%E4%B8%80%E4%BA%9B%E4%BC%98%E5%8C%96%E5%BB%BA%E8%AE%AE