Wednesday, 9 November 2011

Upgrading Android Databases and ORMLite

I've long been an advocate of ORMLite, its easy to use, light weight and relatively feature full.
One of the biggest headaches I run into when creating Android application is how to nicely implement some sort of upgrade strategy for database migrations.
I require from the strategy the standard approach I would take when writing any and all application regardless of platform or language, usually taking the form of the steps below.
1) Make the change: Alter table, new column etc.
2) Deprecate the old: Remove it from my queries, domain objects, ORM objects, stop using it!
3) Next release, clean up: Old columns/tables removed so in the case a failure, the application can be rolled back successfully.

Android exposes a method which both ORMLite and the standard SQLLiteHelper classes exposes of public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion).


According to the docs:

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.
Parameters
dbThe database.
oldVersionThe old database version.
newVersionThe new database version.
This all seems okay but when you have lots of changes and when you use an ORM framework such as ORMLite it makes it a little more tricky. I am not completely happy with the way I have chosen to deal with migrations, however I feel it fits my purpose and allows me to abstract away some of the raw SQL. I originally started down a builder pattern route but soon realised that although it would fit my purpose I would be writing lots of code and gaining little in readability and maintainability. In adition to this managing a series static column name strings after you have removed the column and the plan to change my domain objects by adding an interface to allow easier migration doesn't seem correct. 
My first approach looked something like this:
Below is a sample utility class which I have created with the aim to try and easy the migration process. I ended up using a series of assets files which contain the migrations for each database version. A little helper classes (UpgradeHelper.java) simply loads the required sql files and keeps the data in a list, stripping out comments, then this is simply executed and if anything fails along the way, I bootstrap the database and re-source everything.
Below is how I have chosen to utilise the UpgradeHelper.java to maintain and load the required database migration files to be ran. This is showing the standard way using onUpgrade to determine if migrations need to be sourced, adding them to a list of migrations then executing them one by one.

Limitations mean that I am restricted to only being able to run SQL files during the migraiton process but improvements could be added to allow for DomainObjects to be loaded and ran. 
Future options/improvements would be: 
  • Include the ability to create/delete/alter tables based using a more fluid human readable syntax i.e. not just executing SQL but creating a interpreter which can be used in conjunction with ORMLite to source/alter domain objects.
  • There are probably more SQL comment types which can be stripped.
  • Abstract away some of the worst attributes of SQLite e.g. the fact you cannot easily perform alter tables without jumping through hoops.
In conclusion this is how I am at present dealing with database migrations inside Android, I'm sure its not the best solution to managing database migrations/upgrades but it fits my purpose and hopefully means in the future the task of adding new migrations is easier. 

Thoughts and comments are always welcome. I will try get a sample Android project with example upgrades and utility classes.