Some of the weirder bug reports

For a long while I've been getting weird bug reports like, 'last upgrade deleted all my movies' and 'last upgrade made it so I can only enter games', or my favorite 'last uprade force-closes' and other such items.

I've been banging my head against the wall because none of my upgrades did ANYTHING that would delete all the movies or would just force-close.

About 20 releases back, I moved the database upgrade portion of the upgrade process to a background thread and I threw up a waiting dialog saying 'please wait while your database is verified. This can take up to 5 minutes after an upgrade'

It appears that some users got impatient and killed the process and the upgrade was half finished. This isn't a problem for upgrades that alter data, but for the upgrades where I alter the database schema, this causes all kind of nastyness...

Now that I know what is happening, I'm going to try to build restart logic in to the upgrade process but it's not going to be pretty. I really need the upgrade to complete once it starts and determining where it was left off is going to be a real challenge.

I could create a table where I insert the queries to run for an upgrade. The upgrade process will get a query, run it, and then delete it. That way I know which ones I've run but I would have to insert those records and the user could kill that process.

I could inspect the database schema and only run the alters I need (probably the best way). I'll need to get familiar with the metadata commands for sqlite to make this a reality.

Right now, I store all the commands I need to perform on the database in a static array:

private static String[] DATABASE_UPGRADE_FROM_9_TO_10 = new String[] {"ALTER TABLE movies ADD COLUMN author NULL", "ALTER TABLE movies ADD COLUMN artist NULL"};

for(int i = 0; i < DATABASE_UPGRADE_FROM_9_TO_10.length; i++) {
    db.executeRawSql(DATABASE_UPGRADE_FROM_9_TO_10[i]);
}

I run these strings in a for loop. I think what I'll need to do is create an object to describe the alter and then create a helper class to check if the update is needed. I'll create an interface or abstract class and methods like Alter, Update, Insert, and Delete and describe the changes I want. The end-result would look something like this:

private static DbOpInt[] DATABASE_UPGRADE_FROM_9_TO_10 = new DbOpInt[] {
  new Alter("movies", "add", "column", "author", true)
  ,new Alter("movies", "add", "column", "artist", true)
  ,new Update("movies", new String[] {"subtype"}, new String[]{"DVD"}, new String[]{"type"}, new String[]{"DVD"})
  ,new Update("movies", new String[] {"type"}, new String[]{"Movie"}, new String[]{"type"}, new String[]{"DVD"})
};

for(int i = 0; i < DATABASE_UPGRADE_FROM_9_TO_10.length; i++) {
    if(upgradeHelper.needed(DATABASE_UPGRADE_FROM_9_TO_10[i], db) {
        db.executeRawSql(DATABASE_UPGRADE_FROM_9_TO_10[i].toString());
    }
}