Cup of Codenoodles

So you’re interested in doing some Android development? Awesome, let’s get you set up and rolling out some apps. Everything is typically going well until you need to work with the SQLite database, then shit hits the fan. You’ve rolled out 2 or 3 versions of your app and because of the demos you’ve seen from places like the Android samples, changes are impossible to make without slightly breaking your brain.

Wouldn’t it be nice if we could represent our changes in a way that’s sane? We’ve seen this done in numerous frameworks such as Active Record or South.  Now, we don’t have to go this crazy with ORMs and having the code know how everything is related to each-other (though that is pretty awesome).  For now, let’s just focus on representing how our data model changes throughout various versions of our software.  So we could come up with some kind of Domain Specific Language for this problem, but this is still going overboard and may be a bit too complicated, let’s go simpler.  Instead, why don’t we simply use SQL and have a few rules (or conventions) that we need to follow to ensure that our databases migrations get applied properly?

Interestingly enough this is very easy to do and also results in database code that is easier to understand since well, it’s just SQL files!  Another advantage of this is you could simply have the SQLite binary installed on your system and load these files into a local database.  So you’ve also decoupled your database code from your Android application.  Sure, it’s still tied to the SQLite database, but at least you can play around with your database locally instead of in the emulator or on a device which can get a bit hairy if you don’t have the proper permissions (read: root access).

So, first let’s set up our migration files.  We’ll go with the convention of having them stored in assets/migrations. It will be our responsibility to ensure that our migrations are in that folder, and then our code will simply grab all migration files from that directory and load them. Now, we need to ensure some kind of ordering. We could go with the rails way of doing it, which is to add some kind of way to ensure all migrations are unique such as by using a very fine grained timestamp, but again, too complicated. Let’s simply go with a convention like NNNN_my_description_for_my_migration.sql where NNNN is a number between 0001-9999 (hopefully this will be enough for now!).

Awesome, so now we have a place to look and a way to order the migrations properly. Let’s actually figure out how to get these files loaded. I went with the simple approach of having the migrator do all of it’s work in the SQLiteOpenHelper.

First, let’s look at our DatabaseMigrator

public class DatabaseMigrator {
    private Context context;
    private SQLiteDatabase db;
    private AssetManager manager;
    private String[] migrations;

    public DatabaseMigrator(Context context, SQLiteDatabase db) {
        this.context = context;
        this.db = db;
        this.manager = context.getAssets();
        this.migrations = manager.list("migrations");
        Arrays.sort(this.migrations);
    }

    public void migrate(int from, int to) {
        for(String migration : migrations) {
            int version = DatabaseMigrator.version(migration);
            if(version > from && version <= to) {
                String migrationSQL = load(migration);
                apply(migrationSQL);
            }
        }
    }

    public static int currentVersion(Context ctx) {
        String[] migrations = ctx.getAssets().list("migrations");
        return version(migrations[migrations.length - 1]);
    }

    public static int version(String migration) {
        return Integer.parseInt( migration.substring(0, 4) );
    }

    public void apply(String sql) {
        db.execSQL(sql);
    }

    public String load(String migration) {
        ByteArrayOutputStream migrationBytes = new ByteArrayOutputStream();
        InputStream migrationStream = getMigrationStream(migrationStream);
        byte[] buffer = new byte[0x4000];
        int bytesRead = 0;
        while( (bytesRead = migrationStream.read(buffer)) > 0 ) {
            migrationBytes.write(buffer, 0, bytesRead);
        }
        return new String(migrationBytes.toByteArray());
    }

    public InputStream getMigrationStream(String migration) throws IOException {
       return manager.open("migrations/"+migration);
    }
}

Now for our actual database open helper.

public class MyApplicationsDatabaseOpenHelper extends SQLiteOpenHelper {
    private DatabaseMigrator migrator;
    private Context context;

    public MyApplicationsDatabaseOpenHelper(Context ctx) {
        super(ctx, "myAppsDatabase.sqlite3", null, DatabaseMigrator.currentVersion(ctx));
        this.context = ctx;
    }

    public void onCreate(SQLiteDatabase db) {
        DatabaseMigrator migrator = new DatabaseMigrator(context, db);
        migrator.migrate(0, DatabaseMigrator.currentVersion());
    }

    public void onUpdate(SQLiteDatabase db, int oldVersion, int newVersion) {
        DatabaseMigrator migrator = new DatabaseMigrator(context, db);
        migrator.migrate(oldVersion, newVersion);
    }
}

Now we do have a few caveats we should take note of first. The big one is that execSQL only runs the first SQL statement, which can cause some funky issues if we want to have a migration that creates several tables or makes many modifications. All we simply need to do is add something to ensure that our migrations can be cut up properly.

For example:

CREATE TABLE people(firstName, lastName);

/** Separator **/

CREATE TABLE animals(species, genus, class);

Turning this migration file into properly executable statements is left as an exercise for the reader.