Windows Phone 8 SQLite database Upgrade

Question

Our Wp8 application using Sqlite for the database. We have a closed beta version that we recently update.

Our problem is, when we modify the database, and the user update the app, they got exception because of the difference. So somehow we need to store the database version, and upgrade that when newer is awailable.

What is the best/ easiest way to do that?

Answer 1

You can Change version in db file name (base1.db, base2.db), when you change structure of db. Or you can store version in IsolatedStorageSettings

Answer 2

In Android, this is handled with the SQLiteOpenHelper class, which stores a version number in the database (using PRAGMA user_version) and has onCreate/onUpdate callbacks for the app to handle these cases.

Look at the source and implement something similar.

Answer 3

As a small thank you to the SO community and to help others, I have provided the main code I use to handle schema changes. Note this will handle database upgrades from any earlier versions to the latest version (even if the user skips versions in between). Database downgrades are not handled. I should probably sprinkle in a little more error checking | try/catch statements.

In my sqlite helper class I have the declarations below (note version is set to 1 when the DB is first created):

private SQLiteConnection _db;

// Increment this number whenever DB schema changes are made
private const int LATEST_DATABASE_VERSION = 3;

Then the main upgrade method is:

private void DoUpgradeDb(int oldVersion, int newVersion)
        {
            for (int vFrom = oldVersion; vFrom < newVersion; vFrom++)
            {
                switch (vFrom)
                {
                    case 1: // Upgrade from v1 to v2
                        UpgradeV1AlterPersonAddColumnAge();
                        break;
                    case 2: // Upgrade from v2 to v3
                        UpgradeV2CreateTableHobbies();
                        break;
                    default:
                        break;
                }
            }
            // Save the new version number to local storage
            App.AppSettingsService.SetDatabaseVersion(newVersion);
        }

As you see above, I like to place individual changes made in each version into its own method, so I could define the UpgradeV2CreateTableHobbies() method as:

    private void UpgradeV2CreateTableHobbies()
    {
        _db.CreateTable<Hobbies>();
    }

Of course you also need to remember to make the changes if the DB is created from scratch (e.g. new install).

When the next set of schema changes are made, you increment the LATEST_DATABASE_VERSION constant. Then I check if a version upgrade is needed each time I instantiate my Sqlite helpder class (since I use a singleton pattern), you could do something like:

    private bool UpgradeDbIfRequired()
    {
        bool wasUpgradeApplied = false;
        // I wrote a GetDatabaseVersion helper method that reads the version (as nullable int) from the app settings.
        int? currentVersion = App.AppSettingsService.GetDatabaseVersion();
        if (currentVersion.HasValue && currentVersion.Value < LATEST_DATABASE_VERSION)
        {
                // Upgrade to latest version
                DoUpgradeDb(currentVersion.Value, LATEST_DATABASE_VERSION);
                wasUpgradeApplied = true;
        }
        else
        {
            // Already on latest version
            return false;
        }

        return wasUpgradeApplied;
    }

Answer 4

This can be implemented using PRAGMA user_version in the database. The source code should have version information as a constant in a header file.

Whenever a database schema file is updated the version info should be updated in the schema file and in the header file.

At power up the DB version is read using PRAGMA user_version from DB and compared with the version in the header file. If it matches then the normal operation continues.

If there is a version mismatch then the DB can be deleted and new DB can be created. The new DB created will have new version number.