Database schema updates

Question

I'm working on an AIR application that uses a local SQLite database and was wondering how I could manage database schema updates when I distribute new versions of the application. Also considering updates that skip some versions. E.g. instead of going from 1.0 to 1.1, going from 1.0 to 1.5.

What technique would you recommend?

Answer 1

In the case of SQLite, you can make use of the user_version pragma to track the version of the database. To get the version:

PRAGMA user_version

To set the version:

PRAGMA user_version = 5

I then keep each group of updates in an SQL file (that's embedded in the app) and run the updates needed to get up to the most recent version:

Select Case currentUserVersion
Case 1
  // Upgrade to version 2
Case 2
  // Upgrade to version 3
Case etc...
End Select

This allows the app to update itself to the most recent version regardless of the current version of the DB.

Answer 2

We script every DDL change to the DB and when we make a "release" we concatenate them into a single "upgrade" script, together with any Stored Procedures which have changed "since last time"

We have a table that stores the version number of the latest patch applied - so upgrade tools can apply any newer patches.

Every Stored Procedure is in a separate file. Each starts with an "insert" statement to a logging table that stores Name of SProc, Version and "now". (Actually an SProc is executed to store this, its not a raw insert statement).

Sometimes during deployment we manually change an SProc, or rollout odds & ends from DEV, and comparing the log on client's TEST and PRODUCTION databases enables us to check that everything is at the same version.

We also have a "release" master-database, to which we apply the updates, and we use a restored backup of that for new installations (saves the time of running the scripts, which obviously increase over time). We update that as & when, because obviously if it is a bit stale the later patch scripts can be applied.

Our Release database also contains sanitised starter data (which is deleted, or sometimes adopted & modified, before a new installation goes live - so this is not included in any update scripts)

SQL Server has a toolbar button to script a change - so you can use the GUI tools to make all the changes, but rather than saving them generate a script instead. (actually, there is a checkbox to always generate a script, so if you forget and just press SAVE it still gives you the script it used after-the-fact, which can be saved as the patch file)