Windows phone Linq to SQL: First blood

Windows phone mango introduces SQL CE database support through Linq to SQL. In my last post I called this Linq to SQL phone ORM: MiniLinq.
MiniLinq does not support the DBML mapping file, you add the attributes to your DTOs by hand, the database is created based on your model when the app is loaded.

   1:  using (MangoDb mangoDb = new MangoDb())

   2:  {

   3:      if (!mangoDb.DatabaseExists())

   4:      {

   5:          mangoDb.CreateDatabase();

   6:      }

   7:  }

In seven lines of code the initial schema of the MangoDb Data Context is created. When you update the model, add new Properties or Entities, you have to call into the new DatabaseSchemaUpdater api introduced especially for the phone. This can be created through an extension method on DataContext and can be used by importing the namespace Microsoft.Phone.Data.Linq.

   1:  DatabaseSchemaUpdater updater = mangoDb.CreateDatabaseSchemaUpdater();

This class provides a property and five methods. Typically a database is updated more than once in an applications lifetime. We use the DatabaseSchemaVersion for this purpose to infer what has been included in the database before and only add what is not there. Adding an object that already exists will throw a SqlCeException when the Execute method is called on database schema updater. The Execute method commits a batch update operation, this allows everything to be completed in a transaction and protects the database from being left in an indeterminate state if an error occurs.

   1:  int version = updater.DatabaseSchemaVersion;

   2:   

   3:  updater.AddTable<T>();

   4:  updater.AddColumn<T>("columnPropertyName");

   5:  updater.AddIndex<T>("indexName");

   6:  updater.AddAssociation<T>("associationPropertyName");

   7:   

   8:  updater.Execute();

All four add methods take a generic, which is the model you are updating. This means the updater will read the meta data of the Mapping attributes on you model, keeping the method for creating, reading and updating the database exactly the same.

Databases are not kept on a server somewhere, they are stored in Isolated Storage on the users phone and this means you have many databases to look after. This brings with it an interesting conceptual problem when trying to update the database. The problem is due to the DatabaseSchemaVersion property, because this property is initialised when the database is first created to 0 and there is no user override.

I will discuss the problem with a tale of three versions and two users. You release the first version of your application. Bob decides to download your new data driven application and runs the application this creates the database with a version 0.



You make changes to your app and release v2 to the marketplace. Bob updates his app and the Db is now version 1. Also, a new user Panda downloads the app at this time this creates version 0 on her device.



You makes changes again for version 3 of your app. Both Bob and Panda update, Bobs update sails through, you are using logic like this:

   1:  using (MangoDb mangoDb = new MangoDb())

   2:  {

   3:      if (mangoDb.DatabaseExists())

   4:      {

   5:          DatabaseSchemaUpdater updater = mangoDb.CreateDatabaseSchemaUpdater();

   6:         

   7:          int version = updater.DatabaseSchemaVersion;

   8:   

   9:          if (version < 1)

  10:          {

  11:              UpdateDatabaseForAppVersion2();

  12:          }

  13:   

  14:          if (version < 2)

  15:          {

  16:              UpdateDatabaseForAppVersion3();

  17:          }

  18:   

  19:          updater.Execute();      

  20:      }

  21:      else

  22:      {

  23:          mangoDb.CreateDatabase();

  24:      }

  25:  }

However Panda’s update does not bode so well, The application fails to start when a hidden SqlCeException is thrown, because because the logic will try to update Panda’s application as a version 1 application, because the database is version 0. However, Panda initially installed the app at version 2. This makes Panda very sad..

You can resolve this by not using the DatabaseSchemaVersion at all. Use an application version number instead to upgrade your database.

This is not the only problem I have with database updates. The second problem is the upgrade occurs when the user launches the application, not when the application is installed/updated. This, to me, is a poor user experience and can lead to slow start times, more than the magic 10seconds, maybe!

To resolve this, Microsoft can you please allow an UpdateTask class in the Windows phone api? This will allow database upgrades and other local storage related operations to run when the user is installing the application, not when the application launches.

You saw in this post how to use the DatabaseSchemaUpdater and some of the issues around this new api in Windows phone. The SDK is still beta, so I expect the DatabaseSchemaVersion property will be dropped in a refresh of the tools and hopefully we can have a mechanism to run operations when the user is installing/updating an application to lead to better user experiences.