Guest Post: Managing Database Schema Control Over Multiple Versions

Wayne Golding has been a Xojo developer since 2005. He operates two IT companies, a Network/Systems services co & DRNine11 an offsite backup & DR co. Though he primarily uses Xojo for internal management systems and utilities, Wayne’s hobby is robotics where he uses Xojo to communicate with Arduino devices.

When distributing applications with a database component you will undoubtedly upgrade your database schema as your application matures. So how do you manage the schema within your application?

There are three states we need to be concerned with:

1. The application has been updated & the database schema needs to be upgraded too.
2. The application needs to check that the schema is correct.
3. The database schema is too new for the application.

State 1 is obvious, the application needs to recognise the database needs upgrading and performs the task.

State 2 is also obvious and is really an extension of state 1 i.e. make sure the schema version is correct.

State 3 happens when a user installs an old version of the app, or in a networked situation an old version of the app is not upgraded. This can result in corrupt data in the database.

This is how I achieve these requirements.

First I add a parameters table to my database with a schema version column:

db.SQLExecute("CREATE TABLE parameters (id INTEGER NOT NULL PRIMARY KEY, " _
  + "schema_version INTEGER NOT NULL);")
db.SQLExecute("INSERT INTO parameters (id, schema_version) VALUES (1, 0);")

These two lines of code create the parameters table and add a record with a schema version. The connection to the database (represented by the object db) has been instantiated earlier and can be of any supported database type. I usually run these lines in my app when I detect the database hasn’t been created yet (with SQLite the databasefile.exists would be false).

After creating the database and adding the parameters table as above you’ll need to add a method to your app:

Sub CheckSchema
 Dim rstParas As RecordSet
 rstParas = db.SQLSelect("SELECT * FROM parameters;")
  Do
    Select Case rstParas.Field("schema_version").IntegerValue
    Case 0
      db.SQLExecute("CREATE TABLE companies (id INTEGER NOT NULL PRIMARY KEY, coname VARCHAR(75) NOT NULL);")
    Case 1
      Exit Do
    Case Else
      MsgBox "Please upgrade your application"
      Quit
    End Select

    db.SQLExecute("UPDATE parameters SET schema_version = schema_version + 1;")
    rstParas = db.SQLSelect("SELECT * FROM parameters;")
    Loop
End Sub

Let us look at the logic of this method (which is called after we know the database exists). First we get the schema version by selecting the parameters table (which only has one record). We then enter a loop to progressively upgrade the schema to match the program. Assuming the schema version is 0 then the companies table will be created, the schema version will be incremented and the loop will start again, now the version is 1, to the loop will exit. If the version was 1 when starting, the loop would simply exit. If the version greater than 1 then the message box would inform the user of the need to upgrade, and quit the app. To upgrade the schema you simply add lines before the Exit Loop line:

Sub CheckSchema
  Dim rstParas As RecordSet
  rstParas = db.SQLSelect("SELECT * FROM parameters;")
  Do
    Select Case rstParas.Field("schema_version").IntegerValue
    Case 0
      db.SQLExecute("CREATE TABLE companies (id INTEGER NOT NULL PRIMARY KEY, coname VARCHAR(75) NOT NULL);")
    Case 1
      db.SQLExecute("CREATE TABLE addresses (id INTEGER NOT NULL PRIMARY KEY);")
    Case 2
      Exit Do
    Case Else
      MsgBox "Please upgrade your application"
      Quit
    End Select

    db.SQLExecute("UPDATE parameters SET schema_version = schema_version + 1;")
    rstParas = db.SQLSelect("SELECT * FROM parameters;")
   Loop
End Sub

Assuming the schema was 1, the addresses table would be added to the database and the schema would be updated. Don’t forget to add the Case line allow for the schema version update.

The main reason I use this approach is its simplicity – I add my schema updates and a new case line all in one place in my code. A user can install a new version that is several versions ahead of what they had and ithe app will automagically fix the db structure. And if an app is installed in a networked environment the app will detect a newer schema and not (potentially) corrupt the data.