Upgrading to sqlcipher 4 and split databases


#1

Hi sqlcipher team,

Firstly, I want to thanks to sqlcipher team for providing the this great library.

I’m an android developer, and I have a special requirement while upgrading to sqlciper 4.
Please check below digram firstly:

As we can see, I want to upgrate exist single 3.x database(with default setting) to 4.x database(with default settings) and split into several databases.
I’ve checked below thread before I raise this topic:
upgrading-to-sqlcipher-4

I have 2 assumption:
Option 1:

  1. Create empty new1.db, new2.db, new3.db

  2. Follow option 3 under upgrading-to-sqlcipher-4, then use sqlcipher_export() to export the whole data to new1.db, new2.db, new3.db

  3. Drop the redundant tables for new1.db, new2.db, new3.db, e.g. for new1.db drop table 4, table 5, table 6, table 7, table 8, table 9

Option 2:

  1. Create empty new1.db, new2.db, new3.db

  2. Follow option 3 under upgrading-to-sqlcipher-4 to attach new database, but follow below suggestion to copy table contents to new tables:
    INSERT INTO new.TABLE SELECT * FROM old_db.TABLE;
    https://stackoverflow.com/questions/2359205/copying-data-from-one-sqlite-database-to-another
    https://tableplus.io/blog/2018/07/sqlite-how-to-copy-table-to-another-database.html
    http://sqlite.1065341.n5.nabble.com/Best-way-to-quot-split-quot-a-large-DB-into-two-DBs-td91297.html

As my personal understanding, option 1 might cause performance issue, as we copy all tables to new database which is unnecessary.

I’m not an expert on sqlite, could you please help to provide suggestion for above 2 options?
Or maybe provide any better suggestion/solution for this special requirement?

Thanks


Upgrading to SQLCipher 4
#2

Hello @hwbest I would recommend option 2 for the performance and deficiency benefits.

With option 1, you would have multiple full copies of the database on permanent storage, which will take up more room. Then (unless you have enabled auto vacuum) you’d also need to vacuum each of the new databases to recover the storage of the dropped tables.

In option 2, you’d be selectively copying only the data that you need into each of the new databases, which will require far fewer operations and could use a lot less storage (depending on how big your tables are).

From a security perspective there really isn’t a substantial difference between the two.


#3

Hi @sjlombardo

Thanks for your suggestion.:grinning:
I’ll follow your suggestion and go with option 2.

Please hold(do not close) this post for a while.
Because I’d like to share the result of option 2 in this post.
Just in case in the future someone has similar requirement as mine, and he can see whether option 2 is ok to go.

Thanks


#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;
https://stackoverflow.com/questions/2359205/copying-data-from-one-sqlite-database-to-another
https://tableplus.io/blog/2018/07/sqlite-how-to-copy-table-to-another-database.html
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


#5

Hello @hwbest - thank you for posting this comprehensive update. I’m sure others will find it useful if they are in a similar situation as you!


#6

Hello @sjlombardo,

I have another question about sqlcipher database version.
Assume that, my Android applicationi use SQLCipher v3.4.2 to create a new databsae, the name is ‘test.db’.

Do we have any sqlcipher api to query the library version that created this database file?
e.g. After executing this api, we can get the version is: v3.4.2, which means this database was created by v3.4.2 library.

Then after I upgrade to 4.0.1, and execute “PRAGMA cipher_migrate”, then we execute this api again, we can get the version is changed from v3.4.2 to 4.0.1?

I have checked official website:
https://www.zetetic.net/sqlcipher/sqlcipher-api/

“PRAGMA cipher_version” can’t meet my requirement, it shows the compiled SQLCipher version instead of the SQLCipher version created the database file.

If there is no such api, is there any way to distinguish whether this database is created by 2.x, 3.x or 4.x library?(Maybe we can try to open this database with complied 2.x, 3.x or 4.x SQLCipher, then check which SQLCipher can open database without any exception, e.g. 3.x opens database without exception while 2.x and 4.x can’t open database, then we can conclude it should be 3.x database?)

Thanks


#7

Hello @hwbest - There isn’t an API available to query the library version that created the database. As you noted, PRAGMA cipher_version returns the current version of the library, not the database version.

One technique that we’ve used to manage migrations is discussed here, and presents a fairly well optimized technique for determining whether a database needs to be migrated:

Would something like that work for you?


#8

Hello @sjlombardo

Thanks for your patience and reply.
I also noticed this solution.
I think it seems to be the best choice as we don’t other better options.

Thanks