Database versioning in installed applications using Delphi

Question

I'm working on a number of Delphi applications that will need to upgrade their own database structures in the field when new versions are released and when users choose to install additional modules. The applications are using a variety of embedded databases (DBISAM and Jet currently, but this may change).

In the past I've done this with DBISAM using the user version numbers than can be stored with each table. I shipped an extra, empty set of database files and, at start-up, compared the version numbers of each table using the FieldDefs to update the installed table if necessary. While this worked I found it clumsy to have to ship a spare copy of the database and newer versions of DBISAM have changed the table restructuring methodology so that I'll need to rewrite this anyway.

I can see two ways of implementing this: storing a version number with the database and using DDL scripts to get from older versions to newer versions or storing a reference version of the database structure inside the application, comparing the reference to the database on start-up, and having the application generate DDL commands to upgrade the database.

I think that I'll probably have to implement parts of both. I won't want the application to diff the database against the reference structure every time the application starts (too slow), so I'll need a database structure version number to detect whether the user is using an outdated structure. However, I'm not sure I can trust pre-written scripts to do the structural upgrade when the database could have been partially updated in the past or when the user may have themselves changed the database structure, so I'm inclined to use a reference diff for the actual update.

Researching the question I've found a couple of database versioning tools but they all seem targeted towards SQL Server and are implemented outside the actual application. I'm looking for a process that would be tightly integrated into my application and that could be adapted to different database requirements (I know that I'll have to write adapters, custom descendant classes, or event code to handle differences in DDL for various databases, that doesn't bother me).

Does anyone know of anything off the shelf that does this or failing that, does anyone have any thoughts on:

1. The best way to store a reference version of a generic relational database structure inside an application.

2. The best way to diff the reference against the actual database.

3. The best way to generate DDL to update the database.

Answer 1

Similar story here. We store a DB version number in a 'system' table and check that on startup. (If the table/field/value doesn't exist then we know it's version 0 where we forgot to add that bit in!)

During development as and when we need to upgrade the database we write a DDL script to do the work and once happy that it's working OK it gets added as a text resource to the app.

When the app determines that it needs to upgrade it loads the appropriate resource(s) and runs it/them. If it needs to upgrade several versions it must run each script in order. Turns out to be only a few lines of code in the end.

The main point being that instead of using the GUI based tools to modify tables in an ad-hoc or 'random' manner we actually write the DDL straight away. This makes it far easier, when the time comes, to build the full upgrade script. And structure diff'ing isn't required.

Answer 2

I have a blog post here about how I do dbisam database versioning and sql server.

The important parts are:

Because dbisam doesn't support views, the version number is stored (along with a bunch of other info) in an ini file in the database directory.

I have a datamodule, TdmodCheckDatabase. This has a TdbisamTable component for every table in the database. The table component contains all fields in the table and is updated whenever the table is changed.

To make database changes, the following process was used:

1. Increase the version number in the application
2. Make and test DB changes.
3. Update the affected tables in TdmodCheckDatabase
4. If necessary (rarely) add further upgrade queries to TdmodCheckDatabase. E.g. to set the values of new fields, or to add new data rows.
5. Generate a CreateDatabase unit script using the supplied database tools.
6. Update unit tests to suit the new db

When the application is run, it goes through the following process

1. If no database is found, then run CreateDatabase unit and then do step 3
2. Get the current version number from the database ini file
3. If it is less than the expected version number then Run CreateDatabase (to create any new tables) Check every table component in TdmodCheckDatabase Apply any table changes run any manual upgrade scripts
4. Update the version number in the database ini file

A code sample is

class procedure TdmodCheckDatabase.UpgradeDatabase(databasePath: string; currentVersion, newVersion: integer);
var
module: TdmodCheckDatabase;
f: integer;
begin
module:= TdmodCheckDatabase.create(nil);
try
  module.OpenDatabase( databasePath );

  for f:= 0 to module.ComponentCount -1  do
  begin
    if module.Components[f] is TDBISAMTable then
    begin
      try
        // if we need to upgrade table to dbisam 4
        if currentVersion <= DB_VERSION_FOR_DBISAM4 then
          TDBISAMTable(module.Components[f]).UpgradeTable;

        module.UpgradeTable(TDBISAMTable(module.Components[f]));
      except
       // logging and error stuff removed
      end;
    end;
  end;

  for f:= currentVersion + 1 to newVersion do
    module.RunUpgradeScripts(f);

  module.sqlMakeIndexes.ExecSQL; // have to create additional indexes manually
 finally
  module.DBISAMDatabase1.Close;
  module.free;
end;
end;


procedure TdmodCheckDatabase.UpgradeTable(table: TDBISAMTable);
var
 fieldIndex: integer;
 needsRestructure: boolean;
 canonical: TField;
begin
 needsRestructure:= false;

 table.FieldDefs.Update;

 // add any new fields to the FieldDefs
 if table.FieldDefs.Count < table.FieldCount then
 begin
   for fieldIndex := table.FieldDefs.Count to table.Fields.Count -1 do
   begin
     table.FieldDefs.Add(fieldIndex + 1, table.Fields[fieldIndex].FieldName, table.Fields[fieldIndex].DataType, table.Fields[fieldIndex].Size, table.Fields[fieldIndex].Required);
   end;
   needsRestructure:= true;
 end;

 // make sure we have correct size for string fields
 for fieldIndex := 0 to table.FieldDefs.Count -1 do
 begin
   if (table.FieldDefs[fieldIndex].DataType = ftString) then
   begin
     canonical:= table.FindField(table.FieldDefs[fieldIndex].Name);
     if assigned(canonical) and (table.FieldDefs[fieldIndex].Size <> canonical.Size) then
   begin
     // field size has changed
     needsRestructure:= true;
     table.FieldDefs[fieldIndex].Size:= canonical.Size;
   end;
   end;
 end;

 if needsRestructure then
   table.AlterTable(); // upgrades table using the new FieldDef values
end;

procedure TdmodCheckDatabase.RunUpgradeScripts(newVersion: integer);
begin
 case newVersion of
   3: sqlVersion3.ExecSQL;
   9: sqlVersion9.ExecSQL;
   11: begin  // change to DBISAM 4
         sqlVersion11a.ExecSQL;
         sqlVersion11b.ExecSQL;
         sqlVersion11c.ExecSQL;
         sqlVersion11d.ExecSQL;
         sqlVersion11e.ExecSQL;
       end;
   19: sqlVersion19.ExecSQL;
   20: sqlVersion20.ExecSQL;
 end;
end;