Database versioning in installed applications using Delphi

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.

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);varmodule: TdmodCheckDatabase;f: integer;beginmodule:= 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 valuesend;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;

I'm Using ADO for my databases. I also use a version number scheme, but only as a sanity check. I have a program I developed which uses the Connection.GetTableNames and Connection.GetFieldNames to identify any discrepancy against an XML document which describes the "master" database. If there is a discrepancy, then I build the appropriate SQL to create the missing fields. I never drop additional ones.

I then have a dbpatch table, which contains a list of patches identified by a unique name. If there are specific patches missing, then they are applied and the appropriate record is added to the dbpatch table. Most often this is new stored procs, or field resizing, or indexes

I also maintain a min-db-version, which is also checked since I allow users to use an older version of the client, I only allow them to use a version that is >= min-db-version and <= cur-db-version.