SQL database version management in a Windows Phone 7 application

This is the second tips’n’tricks post talking about the stuff I discover while working on the 2Day todo-list application for Windows Phone. In my last blog post I talked about random ArgumentException. This time, I’m talking about the way to handle version management in SQL database in a Windows Phone 7 app.

As soon as you want to push an update to an existing app using a SQL database you will have to deal with version management. The basic idea is that you might want to alter the schema of the database (for examples by adding tables or columns) while not breaking compatibility for your existing users. You must therefore be able to upgrade your database from version N to version N + 1.

On the Windows Phone platform, this is done using the DatabaseSchemaUpdater class. You can get an instance of this type using the CreateDatabaseSchemaUpdater() method of your DatabaseContext. You can then change the database by using the AddTable and AddColumn methods. You can retrieve the version of the database by using the DatabaseSchemaVersion property. ┬áSimple, isn’t it ?

Here are some tips to update your database properly:

  • define all your update operations in a single class – it will be easier for you to manage future updates
  • define the current version of your database using a constant in your class – check this constant agains the DatabaseSchemaVersion property to determine if you must perform an upgrade
  • handle incremental update, when you push an update for version 3, you can still have users with version 1 or 2, so handle upgrade from 1 to 2 and from 2 to 3 incrementally
  • when you add new column to an existing table, the type of this colum must be nullable (bool?, string?, etc.)

Here is what the code looks like in 2Day:

?View Code CSHARP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
using Japf.ToDo.Core.Model;
using Microsoft.Phone.Data.Linq;
 
namespace Japf.ToDo.Core.IO
{
    public class DatabaseVersion
    {
        // database history
        // v0. Feb. 20th 2011 first version
        // v1. May. 7th 2012 add the SpecialFolders table
        // v2. May. 19th 2012 add the Progress column in the Task table
        // v3. Auguest. 12th 2012 add the IsFocused column in the Folders & SpecialFolders table
        public const int Version = 3;
 
        public static void UpgradeIfNeeded(DatabaseContext context)
        {
            DatabaseSchemaUpdater dbUpdater = context.CreateDatabaseSchemaUpdater();
            if (dbUpdater.DatabaseSchemaVersion < Version)
            {
                // handle update for user with version 0
                if (dbUpdater.DatabaseSchemaVersion == 0)
                {
                    From0To1(context);
                    From1To2(context);
                    From2To3(context);
                }
                // handle update for user with version 1
                else if (dbUpdater.DatabaseSchemaVersion == 1)
                {
                    From1To2(context);
                    From2To3(context);
                }
                // handle update for user with version 2
                else if (dbUpdater.DatabaseSchemaVersion == 2)
                {
                    From2To3(context);
                }
            }
        }
 
        private static void From0To1(DatabaseContext context)
        {
            var dbUpdater = context.CreateDatabaseSchemaUpdater();
 
            // add a new table containing where each row is a SpecialFolder
            dbUpdater.AddTable<SpecialFolder>();
 
            // add a column to the existing table Folder named ShowInSpecialFolders
            dbUpdater.AddColumn<Folder>("ShowInSpecialFolders");
 
            // update the version of the schema
            dbUpdater.DatabaseSchemaVersion = 1;
 
            // send the changes to the db
            dbUpdater.Execute();
 
            // do not reuse the DatabaseSchemaUpdater for other update as it still contains
            // instructions to add the new table and the new column
        }
 
        // other methods omitted for clarity...
    }
}

Hope it helps :-)

4 thoughts on “SQL database version management in a Windows Phone 7 application

  1. Do you really need to duplicate each upgrade in every ‘if’ statement? Instead of ‘if-else if’ blocks, couldn’t you just make them independent ‘if’ statements?

    if (dbUpdater.DatabaseSchemaVersion == 0)
    {
    From0To1(context);
    }

    if (dbUpdater.DatabaseSchemaVersion == 1)
    {
    From1To2(context);
    }

    if (dbUpdater.DatabaseSchemaVersion == 2)
    {
    From2To3(context);
    }

    This way, you only have to update one block in the main method each time along with adding the new method.

    But, I also can’t help but wonder whether the Chain of Responsibility pattern might help as well by allowing you to simply add a new class for each migration that handles a specific version. MEF or some other composition technology could find all the IMigrator classes and a loop in the main method could dispatch the calls.

  2. Hi Eddie,

    Thanks for the feedback. You are totally right about the if/else statement :-)

    And sure, with MEF or maybe simply with attributes and reflection there is probably a way to do that automatically.

    Jeremy

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>