Archive for the ‘databases’ category

Smartly Representing changes to your Data Model

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");

    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);

    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) {

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

    public InputStream getMigrationStream(String migration) throws IOException {

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.

Lessons Learned from Bulk Parsing and Database Insertion

I’m beginning to work on an STO application from an API that was created by Philippe Casgrain.  The data was stored in a pretty big JSON file, and I decided that instead of breaking it into smaller pieces I would simply insert everything into an SQLite database.  Previously the only time I had worked with Android databases had simply been via insertions with ContentValues, which is a lot slower than I had thought at first.

Previously this is what I was doing

void insertData(List objects, SQLiteDatabase db) { 
    ContentValues cvs = new ContentValues();
    for(Foo obj : objects){
        cvs.put("FooStringColumn", obj.fooString());
        cvs.put("FooIntegerColumn", obj.fooInteger()); 
        long result = db.insertWithOnConflict("Foo", null, cvs, SQLiteDatabase.CONFLICT_REPLACE);

This works alright, though when you are generating thousands of entries from your JSON data it results in something that’s so slow it’s painful. At first I thought my problems were stemming from using the org.json tools included in Android so I switched over to using Google GSON instead, which did help reduce a ton of unnecessary garbage collection. Though, everything was still slow.

My database knowledge isn’t extremely deep, and I recall being able to compile PreparedStatements to speed up interaction with the DB. After a bit of searching on the internet I came across an alternative way of doing database insertion using this thing known as an InsertHelper. Using an InsertHelper is pretty straightforward, though it requires writing a bit more code than ContentValues, but it’s way faster.

void insertData(List objects, SQLiteDatabase db){
    InsertHelper helper = new InsertHelper(db, "Foo");
    final int fooStrCol = helper.getColumnIndex("FooStringColumn");
    final int fooIntCol = helper.getColumnIndex("FooIntegerColumn");
    for(Foo obj : objects){
        helper.bind(fooStrCol, obj.fooString());
        helper.bind(fooIntCol, obj.fooInteger());


To also help speed things up, I extracted all of my data first then inserted everything in a transaction. This helped reduce overhead and I was able to get the database initialized in about 10 seconds. Surely I can speed it up further, but for now this seems to solve my problems.



So DROIDHACK was this weekend, and I wasn’t exactly too sure what I wasn’t too sure what I was going to work on. I really wanted to get into the data layer of Mercury, but was really really dreading having to write all the data layer junk that comes with working with SQLite on Android.

Back when I first started working at Shopify I was initially going to be making an Android application. The first thing I knew I needed to do was find some kind of ORM that would make working with the database less painful. I had come across a few options, where the best was a proprietary one which I didn’t think was the best choice. Alternatively, there were a few open source projects that at first glance seemed like a good choice but were using way too much reflection and was kinda difficult to add features to. I really wanted a tool that would make it easy to create a database, but wouldn’t rely on reflection too much.

Another attendee, Don Kelly wasn’t too sure what kind of Android project he wanted to work on either so I bounced the idea off him and then we started hacking up some unit tests. My TDD process is pretty weak, and it was great working with someone who approached the problem from a test driven perspective. What I really liked about this was we were able to make API decisions right away. How do we want to get objects from the database? How do we want to interact with them? How would we create a table? I’ve read Becks’ book on TDD but have always found it tough to apply it when I’m actually working on something.

As for the project we worked on; it’s called velvet and the goal is to make working with databases on Android suck less. We haven’t really got much working with it yet, but the goal will be to have a simple data access layer, migrations and easy access to database cursors for presenting data in listviews and such. I’d have to say working with any kind of structured data with SQLite on Android requires far too much work already, wastes time and is often error prone.

Android SQLite Tutorial 80% Complete

So the majority of the code for my presentation at AndroidTO is up and hosted on Github.

If you are looking for a tutorial on how to interact with SQLite on Android, check out my code. It’s fairly straightforward though almost completely undocumented. I’ll go through in the next few weeks while working on the presentation and add comments to the code both for JavaDocs as well as explaining the nuances of what I was thinking while writing the stuff.

This is probably my fourth or fifth time writing code to interact with databases on Android and I must say that it is a huge pain in the ass. Having to write much of the SQL by hand is error prone, tedious, and downright annoying. It would be nice if the Android SDK came with a built in persistence layer like how iOS comes with CoreData baked into it. It would allow you to focus on the problem at hand instead of wasting that time on the boilerplate copypasta.

I have looked into some ORMs for Android and so far it appears fairly sparse. There are Java libraries you can bring over, though I’m not entire sure how compatible or efficient they are. Hibernate is clearly out of the picture. I have found one project Active Android that seems to be kinda neat, which I am working on using for an app I’m working on. Unfortunately it’s commercially licensed, though it’s rather inexpensive.

If you are looking for an open source tool there is one project called sqlitegen that uses Java annotations and code generation to build to data access classes for you. I haven’t used it personally since I’d prefer to use a solution that doesn’t rely on Eclipse.