Upgrading to sqlcipher 4 and split databases

SQLCipher
4

Hello,

I’ve tried option 2 with isolate demo, and it indeed work.
For anyone who meets similar requirement as mine, I’d like to share some tips in this post.

Before implement this requirement, we must list the stage we might meet:
Assume that our app database goes through below udpates:

  1. App version 1, old.db version 1
  2. App version 2, old.db version 2
  3. App version 3, old.db version 2(deprecated) and 3 new.db version1
  4. App version 4, old.db version 2(deprecated) and 3 new.db version 2(maybe not all new.db updates to new version)

So as my understanding, there should be below 3 cases:

  1. User install App version 1 with old.db version 1, then update to App version 3 or version 4 directly.
    For this case, we must update old.db to version 2 firstly, then perform the upgrade and split actions.
  2. User install App version 2 with old.db version2, then update to App version 3 or version 4 directly.
    For this case, we only need to perform the upgrade and split actions.
  3. User install app version 3 or version 4, which means it is fresh install
    For this case, we only need to create new.db and don’t need any upgrad or split actions.

Check the content in the main post, actually my requirement has 3 key actions:

  1. Perform upgrade to sqlcipher 4.0.1 if old.db exists and migrate has not been taken before, more details check:
    upgrading-to-sqlcipher-4
    I chose option 1, the simplest way:
    PRAGMA cipher_migrate

  2. Perform create new.db databases

  3. Perform split actions, i.e. migrate old.db data to new.db, note this action must be performed after action 2), and this action should be perfromed only once.

For action 1), although it is OK that we always exec “PRAGMA cipher_migrate”:
offical_samplle
But I added some code logic before creating SQLiteDatabaseHook:

  • If database doesn’t exist, do not create SQLiteDatabaseHook
    Check context.getDatabasePath(dbFileName) with File#exists
  • If database exist, but upgrade has taken before(recorded by SharedPreference), then do not create SQLiteDatabaseHook
  • Otherwise, create SQLiteDatabaseHook

With above actions, it prevents trigger “PRAGMA cipher_migrate” multi times.

For action 2), we only need to follow the typicall Android SQLiteOpenHelper/ContentProvider design.

For action 3), we can follow below reference to attach database and copy tables:

INSERT INTO new.TABLE SELECT * FROM old_db.TABLE;
Copying data from one SQLite database to another - Stack Overflow
SQLite - How to copy data from one database to another? | TablePlus
http://sqlite.1065341.n5.nabble.com/Best-way-to-quot-split-quot-a-large-DB-into-two-DBs-td91297.html

And this action is implemented with old.db ContentProvider:

  • In ContentProvider#onCreate, we only create SQLiteOpenHelper when old.db file exists
    and the split action(migration) hasn’t been taken(recorded by SharedPreference) yet.
    In other word, in some cases SQLiteOpenHelper(with old.db) won’t be created.
  • Do not support ContentProvider#query, ContentProvider#insert, ContentProvider#delete for old.db:
    For old.db we should remove all code logic to support ContentProvider#query, ContentProvider#insert, ContentProvider#delete, as we won’t use it any more, e.g. just throw exception as below:
    @Override
    public Cursor query(Uri uri, String projection, String selection, String selectionArgs,
    String sortOrder) {
    throw new UnsupportedOperationException("Cannot query URL: " + uri);
    }
  • Then in ContentProvider#update we implement the migration action:
    if SQLiteOpenHelper is null, which means this is fresh install application or migration has already taken before.
    The background is, if database file doesn’t exist or migration has already taken before,we won’t create the mOpenHelper in ContentProvider#onCreate.
    As there is no need to create it for mirgation, and it won’t occupy system resource.

Some Notes:

  • Note 1: old.db must update to latest version(SQLiteOpenHelper#onUpgrade need to be triggered if database is not latest version).
    To ensure this, we only need to call SQLiteOpenHelper#getWritableDatabase
  • Note 3: new.db must be created(SQLiteOpenHelper#onCreate)
    To ensure this, we only need to call SQLiteOpenHelper#getWritableDatabase
  • Note 4: I tried to view the database via sqlitebrowser after migration, but failed.
    Finally I found that my sqlitebrowser is out of date, to view sqlcipher 4.x db file, we must use DB Browser for SQLite 3.11.1+
    https://sqlitebrowser.org/dl/
/**
 * Copy the table contents from source to target with only 1 transactions.
 * Before calling this function, ensure below conditions:
 * 1) target database file exists`
 * 2) target database SQLiteOpenHelper#onCreate has called
 * 3) target database table struct is exactly same as source database
 *
 * @param context             The context to access resource
 * @param srcWritableDatabase The legacy SQLiteDatabase, i.e. old.db
 * @param targetDbFileName    The target database file name, i.e. new.db
 * @param tableNames          The tables to be copied
 * @return boolean value whether migration success
 */
private static boolean migrateTables(Context context, SQLiteDatabase
        srcWritableDatabase, String targetDbFileName, String... tableNames) {
    if (context == null) {
        Log.d(TAG, "migrateTables: invalid context");
        return false;
    }
    if (srcWritableDatabase == null) {
        Log.d(TAG, "migrateTables: invalid srcWritableDatabase");
        return false;
    }
    if (TextUtils.isEmpty(targetDbFileName)) {
        Log.d(TAG, "migrateTables: invalid targetDbFileName");
        return false;
    }
    if (tableNames == null || tableNames.length == 0) {
        Log.d(TAG, "migrateTables: invalid tableNames");
        return false;
    }
    File targetDbFile = context.getDatabasePath(targetDbFileName);
    if (targetDbFile == null || !targetDbFile.exists()) {
        Log.d(TAG, "migrateTables: target db file doesn't exist:" +
                targetDbFileName);
        return false;
    }

    Log.d(TAG, "migrateTables: targetDbFileName=" + targetDbFileName);

    boolean success = true;
    try {
        Log.d(TAG, "migrateTables: attach database");
        srcWritableDatabase.execSQL("ATTACH DATABASE '" + targetDbFile.getPath() + "' AS " +
                "target KEY '" + <your_sql_password> + "'");
    } catch (SQLException e) {
        success = false;
        Log.e(TAG, "migrateTables: exception=" + e);
    }
    srcWritableDatabase.beginTransaction();
    try {
        Log.d(TAG, "migrateTables: start copy tables");
        for (String tableName : tableNames) {
            Log.d(TAG, "migrateTables: start copy table:" + tableName);
            // INSERT INTO target.TABLE SELECT * FROM src.TABLE;
            srcWritableDatabase.execSQL("INSERT INTO target." + tableName
                    + " SELECT * FROM " + tableName);
        }

        srcWritableDatabase.setTransactionSuccessful();
        Log.d(TAG, "migrateTables: end copy tables");
    } catch (Exception e) {
        Log.e(TAG, "migrateTables: exception=" + e);
        success = false;
    } finally {
        srcWritableDatabase.endTransaction();
        srcWritableDatabase.execSQL("DETACH DATABASE target");
        Log.d(TAG, "migrateTables: detach database");
    }
    Log.d(TAG, "migrateTables: success=" + success);
    return success;
}

Thanks