[MOD-2589] Android: Optimize encrypted db open() when migration from older version not needed
GitHub Issue | n/a |
---|---|
Type | Improvement |
Priority | Medium |
Status | Closed |
Resolution | Fixed |
Resolution Date | 2020-04-22T17:37:30.000+0000 |
Affected Version/s | n/a |
Fix Version/s | appcelerator.encrypteddatabase 3.3.0 |
Components | Encrypted SQLite DB |
Labels | android, database, encrypted_database, open, performance, sqlite |
Reporter | Joshua Quick |
Assignee | Joshua Quick |
Created | 2020-03-27T00:25:55.000+0000 |
Updated | 2020-04-22T17:37:30.000+0000 |
Description
*Summary:*
An Android encrypted database file created via "appcelerator.encrypteddatabase" module version Set up "tiapp.xml" to use module "appcelerator.encrypteddatabase" version
In the log, look up the "DB open duration" time. It should be around
3.0.2
or older needs to be "migrated" when opened with module version 3.0.3
and higher or else it will fail to open. As of module version 3.1.0
, the module will automatically migrate the database when you call the open()
method. This migration operation adds about 500ms
to the open time every time you open the database, even if it has already been migrated.
We should only migrate an encrypted database once and subsequent opens should not execute the migration operation.
*Test:*
Create a Classic Titanium project using the below code for your "app.js".
Set up "tiapp.xml" to use module "appcelerator.encrypteddatabase" version 3.0.2
.
Build and run on Android.
Set up "tiapp.xml" to use newest version of module "appcelerator.encrypteddatabase".
Build and run on Android.
In the log, look up the "DB open duration" time. It should be around 900ms
.
Press the Android back button.
Relaunch the app.
In the log, notice the "DB open duration" time is just as long. (This is what we want to optimize.)
var database = require("appcelerator.encrypteddatabase");
database.setPassword("password");
var openStartTime = new Date();
var dbConnection = database.open("test_encrypted.db");
Ti.API.info("@@@ DB open duration: " + (new Date() - openStartTime) + " ms");
dbConnection.execute("DROP TABLE IF EXISTS test;");
dbConnection.execute("CREATE TABLE test(id integer PRIMARY KEY, name TEXT);");
dbConnection.close();
*Recommended Solution:*
When we successfully open/create a database, we should store the sqlcipher version to a SharedPreference
. This way the next time we open the database, we will know what version it was last opened with.
We can obtain the sqlcipher version via the follow Java constant.
[SQLiteDatabase.SQLCIPHER_ANDROID_VERSION](https://github.com/sqlcipher/android-database-sqlcipher/blob/23325436094c08f39dc704a851d2c989bfb6783e/android-database-sqlcipher/src/main/java/net/sqlcipher/database/SQLiteDatabase.java#L95)
Our code should do something similar to as follows...
// The preferences file should use the module's name to avoid file collision.
final String MODULE_PREFERENCES_NAME = "appcelerator.encrypteddatabase";
SharedPreferences preferencesReader = TiApplication.getInstance().getSharedPreferences(
MODULE_PREFERENCES_NAME, Context.MODE_PRIVATE);
// After opening the database, use the db file path as the key and store the db library version to it.
SharedPreferences.Editor preferencesWriter = preferencesReader.edit();
preferencesWriter.putString(dbFilePath, SQLiteDatabase.SQLCIPHER_ANDROID_VERSION);
In our module, we have a migrationHook
that gets invoked after the database has been open.
[EncrypteddatabaseModule.java - migrationHook ](https://github.com/appcelerator-modules/appcelerator.encrypteddatabase/blob/ff743b74058d662014f53164355e4c8de3c308f2/android/src/appcelerator/encrypteddatabase/EncrypteddatabaseModule.java#L80)
We should only execute PRAGMA cipher_migrate;
if the major version component of SQLiteDatabase.SQLCIPHER_ANDROID_VERSION
is higher than what is stored to the above shared preference. If the shared preference cannot be found for the database file, then we should assume the worse and do the migration anyways. This is in case the database file is old and has been copied to a different directory.
-PR:- https://github.com/appcelerator-modules/appcelerator.encrypteddatabase/pull/46 _(Closed in favor of below PR.)_
PR: https://github.com/appcelerator-modules/appcelerator.encrypteddatabase/pull/48
Here is a back-port which will work with Titanium 7.0.0+. (Not built with gradle.) PR (android)(3_3_X): https://github.com/appcelerator-modules/appcelerator.encrypteddatabase/pull/50
Backport PR Merged.
Fix works as expected. Closing.