Database versioning in installed applications using Delphi

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 开发者_JS百科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.

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);
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;

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.

What I do is store a version number in the database, and a version number in the application. Every time I need to change the database structure, I create some code update the structure of the database, and increase the version number in the application. When the application starts, it compares, numbers, and if need be runs some code to update the database structure AND update the database's version number. Thus the database is now up to date with the application. My code is something like

if DBVersion < AppVersion then
begin
  for i := DBVersion+1 to AppVersion do
    UpdateStructure(i);
end
else
  if DBVersion > AppVersion then
    raise EWrongVersion.Create('Wrong application for this database');

UpdateStructure just runs the necessary code something like :

procedure UpdateStructure(const aVersion : Integer);
begin
  case aVersion of
    1 : //some db code
    2 : //some more db code
    ...
    ...
  end;
  UpdateDatabaseVersion(aVersion);
end;  

You can actually use the same code to create the database from scratch

CreateDatabase;
for i := 1 to AppVersion do
  UpdateStructure(i);