SQLite on WP – DB schema management

As I started working with SQLite as a place to store my data, I began thinking about my app’s changing data scheme and I wondered: ‘how do I handle change management in my local database?’ This blog entry outlines my approach to solving this issue, which basically boils down to tracking the data scheme version in the database and doing a wholesale drop and recreate when the schema changes.

Why drop and recreate?

For my app, I use SQLite for data caching of server-side information – providing performance and offline capabilities for the user. Prior to using SQLite, I had been using in-memory serialization to store the object collections in isolated storage, which had a number of performance issues (memory and CPU).

For non-cached user information (account information, preferences, etc.), I don’t use SQLite – I store those items in the isolated storage property bag. As I take my app to the Windows Runtime platform, I’ll bring this information into the roaming data area (for preference information) and credentials locker (for account information).

For the cached app data, I’m now storing this as a SQLite database. My issue is that each version sees continued improvement around the richness of the data (adding review information from a variety of sites, news entries, etc.) – which means scheme modifications. Many of the websites describing using SQLite talk about how to create the database, but leave it to the user to figure out database updates. I played around DDL tracking with complicated UPDATE scripts and decided this was taking way too long for basically volatile cache data…and I went with a simpler approach – ship the app with a current snapshot of the full cache (~7mb) and simply do a drop and recreate each time the schema changes.

Tracking Database schema state

To track the state of the database, I created a class called DBMetadata that I can use for a variety of things in my app, including the schema version (what we’re explaining here), and things like the last time I retrieved a particular dataset (to help balance data pulls that don’t need to be run every time the app runs). The class looks like the following:

[Table("Metadata")]
public class DbMetadata {
 
  [PrimaryKey, AutoIncrement]
  public int Id { get; set; }
 
  public string SettingName { get; set; }
 
  public int SettingValueMajor { get; set; }
 
  public int SettingValueMinor { get; set; }
 
  public string SettingInt { get; set; }
 
  public string SettingString { get; set; }
 
  public DateTime SettingDateTime { get; set; }
 
}

To track the database schema version, I use constants in my LocalData class like the following:

const string DBMETA_SCHEMA_VERSION_NAME = "DbSchemaVersion";
const int DBMETA_SCHEMA_VERSION_MAJOR = 1;
const int DBMETA_SCHEMA_VERSION_MINOR = 2;

With this code in place, I have the following two methods in my LocalData class to interact and check if the database is running the most current:

private async static Task<bool> ValidateIsLatestDbVersion() {
 
  try {
 
    int _table = await LocalData.DbConnection.ExecuteScalarAsync<Int32>("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='Metadata';");
 
    if (_table == 0) return false;
 
    if ((await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).CountAsync()) > 0) {
      var _ret = await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).FirstAsync();
 
      if (_ret != null && _ret.SettingValueMajor > DBMETA_SCHEMA_VERSION_MAJOR) {
        return true;
      } else if (_ret.SettingValueMajor == DBMETA_SCHEMA_VERSION_MAJOR && _ret.SettingValueMinor >= DBMETA_SCHEMA_VERSION_MINOR) {
        return true;
      }
    }
  } catch (Exception _e) {
  }
 
  return false;
}
 
 
private async static Task<bool> MarkDbVersion() {
 
  try {
    if ((await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).CountAsync()) > 0) {
      var _ret = await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).FirstAsync();
      _ret.SettingValueMajor = DBMETA_SCHEMA_VERSION_MAJOR;
      _ret.SettingValueMinor = DBMETA_SCHEMA_VERSION_MINOR;
      await LocalData.DbConnection.UpdateAsync(_ret);
    } else {
      await LocalData.DbConnection.InsertAsync(new DbMetadata() { SettingName = DBMETA_SCHEMA_VERSION_NAME, SettingValueMajor = DBMETA_SCHEMA_VERSION_MAJOR, SettingValueMinor = DBMETA_SCHEMA_VERSION_MINOR });
    }
    return true;
  } catch (Exception _e) {
    return false;
  }
}

With the above in place, it’s pretty easy to now handle the check and drop, as needed.

Open/Create/Delete

So now with the plumbing in place, it’s time to put in place the business logic that does the database creation. I do this when I open the database for the first time in my Windows Phone app.

private async static Task OpenLegoDB() {
 
  string DBPATH = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, DBNAME_LEGO_SETS);
  try {
    await ApplicationData.Current.LocalFolder.GetFileAsync(DBNAME_LEGO_SETS);
    _legoDbConnection = new SQLiteAsyncConnection(DBPATH);
 
    //Check the schema version, and replace the database if it's an old one
    if (!await ValidateIsLatestDbVersion()) {
      _legoDbConnection = null;
      await LocalData.DeleteLegoDB();
    }
 
  } catch (FileNotFoundException) {
  } catch (SQLiteException) {
    _legoDbConnection = null;
  }
  if (_legoDbConnection == null) {
    await LocalData.CreateLegoDB();
    _legoDbConnection = new SQLiteAsyncConnection(DBPATH);
  }
}
 
 
private async static Task DeleteLegoDB() {
  string DBPATH = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, DBNAME_LEGO_SETS);
  try {
    var _file = await ApplicationData.Current.LocalFolder.GetFileAsync(DBNAME_LEGO_SETS);
 
    SQLite.SQLiteConnectionPool.Shared.Reset();
    await _file.DeleteAsync();
  } catch (FileNotFoundException) {
  }
 
}
 
 
private async static Task CreateLegoDB() {
  try {
 
    string DBPATH = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, DBNAME_LEGO_SETS);
 
#if DEBUG
    _legoDbConnection = new SQLiteAsyncConnection(DBPATH);
    await _legoDbConnection.CreateTableAsync<LegoTheme>();
    await _legoDbConnection.CreateTableAsync<LegoSubtheme>();
    await _legoDbConnection.CreateTableAsync<LegoSet>();
 
    await _legoDbConnection.CreateTableAsync<DbMetadata>();
    await LocalData.MarkDbVersion();
    return;
#endif
 
 
    StorageFile dbFile = null;
    try {
      dbFile = await StorageFile.GetFileFromPathAsync(DBPATH);
    } catch (FileNotFoundException) {
      if (dbFile == null) {
        // Copy file from installation folder to local folder.
        IsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();
 
        // Create a stream for the file in the installation folder.
        using (Stream input = System.Windows.Application.GetResourceStream(new Uri("Assets\\LegoSets.Snapshot.sqlite", UriKind.Relative)).Stream) {
          // Create a stream for the new file in the local folder.
          using (IsolatedStorageFileStream output = iso.CreateFile(DBPATH)) {
            // Initialize the buffer.
            byte[] readBuffer = new byte[4096];
            int bytesRead = -1;
 
            // Copy the file from the installation folder to the local folder.
            while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0) {
              output.Write(readBuffer, 0, bytesRead);
            }
          }
        }
      }
    }
 
  } catch (Exception _e) {
    return;
  }
}

There’s a bunch of cool stuff happening in the code above (the delete step alone took me a few hours on StackOverflow to get right). But the basic logic here is as follows:

Open the database file
If the file is there, check the version number
1. Validate that the settings table exists (that we have a version number to check )
2. If the database is the same or newer version, then all is good!
3. If the database has an old version, delete the file and clear the variable
If we have an open database file, proceed; if we don’t, then create a new one
1. If we’re running in DEBUG, then create the file and the tables from scratch
2. If we’re not running in DEBUG, then use the snapshot that shipped with the app

Note that the above applies to an app written for Windows Phone Silverlight. When I do bring this over to the Windows Runtime platform (targeting Windows client, as well as phone), I will likely add some #IF checks to handle where the files are located and the like. But that’s for a future consideration (I have a tablet version of my code, but put it on hold while I get the SQLite and Amazon integration done).

Hope that helps

That about does it for how I manage my SQLite database in my app; I hope this helps!

There are a variety of other things that I’ve adopted over time based on this strategy that may be worth noting (e.g., breaking my SQLite DB into a few different databases to enable part of the cache to be cleared without clearing all of a user’s past traffic). Also, I’m in the process of wiring this up with the Bing Translator, which also warrants a blog post in the coming months.