February 19, 2015

Android: onDowngrade... the dark path on databases



So I've been developing an app which has a really big database (for an Android app) and while still in development it has already been upgraded quite a few times.

My team and I have been using the onUpgrade method without any problems for the whole time, but because we use git with one branch per feature and code reviews sometimes the app version that we have installed on our device before when doing a code review is bigger that the one of the next code review.

That isn't of course a problem, we could just uninstall it, as it will never happen in production, but for my knowledge sake, I tried to implement a simple downgrade.. just drop all tables and create them again.

So I've went to read the javadoc:

public void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion)

Added in API level 11
Called when the database needs to be downgraded. This is strictly similar to onUpgrade(SQLiteDatabase, int, int) method, but is called whenever current version is newer than requested one. However, this method is not abstract, so it is not mandatory for a customer to implement it. If not overridden, default implementation will reject downgrade and throws SQLiteException
This method executes within a transaction. If an exception is thrown, all changes will automatically be rolled back.
Parameters
dbThe database.
oldVersionThe old database version.
newVersionThe new database version.



Ok, so I've had to override it to avoid an exception. No problem...


... I thought.


In fact the app crashed when I tried to drop all tables and (re)create them on that method... it seemed strange, as it was a lock on the database problem...

(I'll later add here the stack trace)


So after quite a bit of googling/stackoverflowing I've found a bizarre, but working answer.

Which made me class (after stripped of all my app-business-logic) look like this:

public abstract class DatabaseHelper extends SQLiteOpenHelper {

    public DatabaseHelper(Context context, String name, CursorFactory factory, int version,            DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    public DatabaseHelper(Context context, String name, CursorFactory factory, int version) {        super(context, name, factory, version);
    }

    @Override
    public synchronized SQLiteDatabase getWritableDatabase() {
        try {
            return super.getWritableDatabase();
        } catch (SQLiteDowngradeFailedException e) {
            // do some magic...            dropAllTables();
            createTables();
        }


        // now return a freshly created database
        return super.getWritableDatabase();
    }

    @Override
    public final void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // throwing a custom Exception to catch it in getWritableDatabase
        throw new SQLiteDowngradeFailedException();
    }

    // that's the exception
    static class SQLiteDowngradeFailedException extends SQLiteException {
        public SQLiteDowngradeFailedException() {}

        public SQLiteDowngradeFailedException(String error) {
            super(error);
        }
    }
}

I fell a bit like Thor (in a bad way) hitting the code with a hammer until it works... but I haven't found a more elegant solution.

I really think that the Android team should change this behavior as it doesn't seems to make sense if you really need to downgrade a database... but until then, if anyone needs a solution, here it is.

If anyone has solved this with at least a "smaller hammer", please share I'd really appreciate!

If not.. if you need this at least you have found a solution.