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

db	The database.
oldVersion	The old database version.
newVersion	The 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. 

First Impression, Tracer Bullet Development

Recently myself and my colleague (@one_hp) have read Ship It - A Practicably Guide to Successful Software Projects and have been looking for a suitable project/chunk of work to trial Tracer Bullet Development, this can be found @ chapter 4, page 105. First impression of firing tracer bullets through your application may feel similar to creating throw away prototypes or simple proof of concept design and implementations but when you get into the swing of it the benefits start to shine through.


The remit of the work we started on was to create some sort of issue raising framework for team at my company. Currently this was done via Outlook, sending emails to various group inbox's which are triaged and worked on accordingly. 


From the business the requires are to replace this system with one which can be reported on easily, will allow money savings by not needing X number of Outlook licenses, not to increase the work load of people who raise issues and also to not greatly impact the current process for triaging and actioning issues which are raised in each of the corresponding teams.       


So after a few days speaking to the various teams, gathering some light weight/loose requirements and performing some analysis on the emails being sent back and forth containing issues and resolutions we got down and started to plan the work which was needed. 


Immediately we follow the usual design process which we tend to follow, get some initial designs down on paper (Created in Pencil, a great tool!), map out the existing work flow processes plus what we imagine the new one may look like. (via Gliffy) This usually may take a few attempts, especially after discussing with team mates/managers/users the various options available. 

Now we have are happy enough to start coding and evolving the idea as we work, we can start firing some bullets though our application and get proposed screen changes up and viewable so we can get some feedback.

So as I understand the idea behind tracer bullet development is to shoot bullets from one side of your application to another but not to spend any time on complex logic/processing, this can be added at a later stage once you are happy your accurate enough at shooting first. 

The application being changed consists of a very standard spring based web stack with Flex 4 for the front end. Standard controllers, services and repository layers with a mixture of JDBC and Hibernate for persistence. 

We took the screen designs and scribbles we made and created an initial schema for what we would think is needed. 

Creating some simple POJO's at this stage is trivial and with the help of eclipse no time needs to be wasted with POJO creation. Next we started adding some initial controller and service methods. These simply called through to the layer below, not performing any logic and in the case of a persistence method you could simply add the objects to a static map or log out the parameters for viewing if you require.We spent some time initially only writing the interfaces making sure we had good method names and correct signatures before proceeding. The devil is in the detail.

So we have a schema, domain objects and we can go from controller to repository without any processing/change, we should now be ready to cock the gun and fire our first bullet....bang...bang.

One of the good things about tracer bullet development as that our bullets consisted of single or lists of pojo's and instinctively we ended up made factory methods for creating these pojo's which is exactly what we tend to do when writing unit tests. Straight away it was highlighted that in reality the unit test can reuse these factory methods and should be in a perfect state to use once we are ready to finally implement the solution. We set out to use tracer bullet development to speed up the feedback process and to get some usable screens we can demonstrate a.s.a.p. Within a-few hours we had a complete end to end implementation which had no logic, persistence or complexity. 

No tests?

You may be wondering where all our tests are, at this point we have no tests and its certainly not test driven. However we have a very light weight and quickly constructed framework and we can concentrate on what would be the time consuming part of this particular task, GUI screen creation and feedback. This also doesn't mean that once the screens have been created and the users are pleased with them we would also not go down the test first route before adding any complex logic to any point to the process.  
 
GUI's

We begin creating our screens, making new components and the best thing about this is immediately we have as much canned data as we want and needed. No messing about with a DB if you find you have a missing field, bad name or wrong data type etc. If something is missing add/remove change a field on you POJO tweak your factory and fire up your instance again. We can easily test small and large amounts of data, obscure looking data and data we know would probably be impossible. Hopefully at the end of this will not only have a feature users are please with and happy to use but a GUI which we work under various scenarios even if obscure. Using our initial Pencil designs we created the screens, and started peppering them with bullets and within a day or so we had all the screens changed and ready to demo with virtually no implementation in place. We had certainly not finished the task but we are ready to get feedback and the users would be able to click about, simulate normal actions and see the outcome as if it was complete.  

Is it any different?

After spending a good 2-3 weeks getting this task to completion I can say I liked the way tracer bullets got us up and running fast and allowed the software to be demo early on even when nothing has really been done. 


We have had the inevitable business changes, feature creep and developer mistakes which have probably forced us to take longer than originally estimated for this task but in the end I am pleased with the software and hopefully on go live the users and business will be pleased with the outcome. 


Tracers seem to have made the process of user feedback a lot easier and simpler when it comes to testing and getting approval for GUI's. It aided us when we came to implementing the solution as virtually all code we had already written could be reused in tests and wasn't thrown away. We have ended up with hopefully a solution to a problem that the business require and a solution and GUI which the users are hopefully pleased with and are well aware of features due to constant feedback.


I would  recommend  using the tracer bullet methodology and recommend reading Ship It - A Practicably Guide to Successful Software Projects as its a great read. 


Any and all comments welcome, James