Coding Thoughts

Something to watch for with DatabaseSchemaUpdater

Local databases on Windows Phone 7.5 are a wonderful thing. They allow you to write beautiful code that takes full advantage of LINQ, and who doesn’t like LINQ right?!? (answer: crazy people). But, using databases doesn’t come without it’s headaches, and a common one is how you update it. 

Fortunately several clever people have already come up with a way of updating your database schema that involve using the DatabaseSchemaUpdater. In a nutshell that process involves something like this:

if (dbSchemaUpdater.DatabaseSchemaVersion < 1) {
     dbSchemaUpdater.AddColumn<MyTable1>(“NewColumn1”)
}
if (dbSchemaUpdater.DatabaseSchemaVersion < 2) { 
     dbSchemaUpdater.AddColumn<MyTable1>(“NewColumn2”)
}

dbSchemaUpdater.DatabaseSchemaVersion = 2;
dbSchemaUpdater.Execute();  

This works really well for most circumstances. However, there is a circumstance where by you can trip up as a developer. Consider that we release our app and it has a DatabaseSchemaVersion of 2. It’s great, everyone loves it, so we start work on an update with extra awesome in it. We need a new table so we add the following:

if (dbSchemaUpdater.DatabaseSchemaVersion < 3) {
     dbSchemaUpdater.AddTable<NewFeaturesTable>();
}

dbSchemaUpdater.DatabaseSchemaVersion = 3;
dbSchemaUpdater.Execute(); 

We run the app, it works. We’re in business, and off we start coding! Now all of a sudden we want to add a new column on that table so we revisit our database updating code and we add the following:

if (dbSchemaUpdater.DatabaseSchemaVersion < 4) {
     dbSchemaUpdater.AddColumn<NewFeaturesTable>(“MoreStuff”);
}

dbSchemaUpdater.DatabaseSchemaVersion = 4;
dbSchemaUpdater.Execute();  

We run it again on our test environment, GREAT! Still working. We even delete our app and reinstall it, still working. This is awesome! So we code our features up and we release it to the marketplace…. BUT it’s crashing for the user!!! WHY DOES IT CRASH! Well, this stumped me for a while, but lets take a look at what the code above is doing.

First of all, the user is coming in at schema version 2. Then, the db updater executes the following code:

1. It adds a NewFeaturesTable
2. It adds the column MoreStuff to the NewFeaturesTable

But that’s what we wanted wasn’t it? I mean, it worked fine in development so why isn’t it working now? Well, not quite… When we added the NewFeaturesTable to our schema originally it didnt have the MoreStuff column, so it didn’t exist, and therefore adding the column was fine.

However now, when the code executes on the user’s device, the NewFeaturesTable table that gets created will already have the MoreStuff column on it, and therefore when your code tries to add the column again, it will fail!

If anyone remembers the disaster that was Mehdoh 1.5 upgrade, then that was the cause. It’s REALLY easy to get carried away with just updating the DB Schema. You might add a new table, add another, update a few other tables, and then add another column to your first new table - it’s easily done.

You should always keep track of what the user will have and test their upgrade scenarios (not just yours!). Honestly, I can’t recommend enough that you do the following to help you avoid the above:

1. Keep copies of the XAP files you submit to the App Store.
2. Before you release any updates, install the previously submitted XAP onto your device and test your upgrade process.  
3. If you’ve got time, test a few other versions too! 

Hopefully someone can learn from my mistake here.