Tuesday, April 22, 2008

Database versioning part 2

In part 1, I discussed database versioning with MS SQL Server. SQL Server has robust tools available that simplifies the process of differencing and creating upgrade scripts. DBISAM doesn't have these tools, but using datamodules and table components makes versioning close to painless.

Situation 2
Delphi app
DBISAM database
1 developer

Rental Property Manager uses a DBISAM database (DBISAM v3 in RPM 1, DBISAM v4 in RPM 2). In the 4 years since it was released, it has been through about 20 changes.

Unlike sql server, there is no database comparison utility to generate upgrade scripts. That means things have to be done a bit differently.

I still use the same general process as for sql server:
  1. Record the database version number in the database
  2. Define the expected db version in the application
  3. Keep a master db for comparison
  4. Only make one set of changes at a time
  5. Automate differencing as much as possible
  6. Unit test, test and test again. The tests should fail the moment a table is modified.
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.
In code this 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;

Unit tests included:
  • Make sure the current version is correct
  • Make sure that every table and every field exists
  • Create a new blank database (for a number of different versions) and work though the upgrade process to make sure the final database is correct.
  • Restore an existing older database with data and upgrade to the latest version
With this process, altering the database structure is trivial for most changes. Adding fields and table usually requires no more work than updating the table components and generating a new creation script .

The current implementation does have a couple of restrictions in that it won't remove tables or fields. However if that is required, it won't take long to add.

Thursday, April 17, 2008

Database versioning part 1

Versioning databases is one of those ongoing problems that has no one-size-fits-all solution. There are 2 solutions I have developed and used successfully.

The general process I use each time is:
  1. Record the database version number in the database
  2. Define the expected db version the application
  3. Keep a master db for comparison
  4. Only make one set of changes at a time
  5. Automate differencing as much as possible
  6. Unit test, test and test again. The tests should fail the moment a table is modified.

Situation 1
MSDE (= SQL server) database
8 developers (pair programming)
C# application

Every developer had 2 databases, Unit Test db and Application Test DB (UnitDb and AppDb from now on). With msde (now sql express) there are no licensing costs to worry about.
There was also a Master database stored on a central server that served as the canonical reference.

The database version number was stored in a view ('select xx as VersionNumber).

To make database changes, the following process was used:
  1. Check out the latest version of the app
  2. Increase the version number in the application
  3. Make and test DB changes. Usually this was done in UnitDB. AppDb was kept synchronised using SQL Compare
  4. Update the version number in UnitDB
  5. Generate an update script using Sql Compare. Scripts were named UpgradeXXX.sql where XXX is the version that was being upgraded from.
  6. Generate a CreateDatabase.sql script (for shipping with the app) and a CreateDatabaseXXX.sql (for unit tests only) script. In this case XXX is the version that will be created. The 2 scripts are the same except for the name.
  7. If necessary (rarely) append further queries to the scripts. E.g. to set the values of new fields, or to add new data rows.
  8. Update unit tests to suit the new db
  9. Check in changes
When the application is run, it goes through the following process
  1. If no database is found, then run CreateDatabase.sql
  2. Get the current version number from the database
  3. If it is less than the expected version number then
    run UpgradeXXX.sql and go to 2
When the unit tests are run, the first step is to upgrade UnitDb to the current version using the same process as the main application. That means that other peoples changes are automatically applied. The unit tests included:
  • Make sure the current version is correct
  • Make sure that every table and every field exist (this doesn't always need to be explicit as the persistence unit tests should pick up any problems here).
  • Create a new blank database (for a number of different versions) and work though the upgrade process to make sure the final database is correct.
The secret weapon in all this was sql compare which makes generating the scripts quite straight forward.
Also the upgrade scripts can do more than one set of version changes. ie Upgrade001.sql could upgrade the version to v10 so that scripts 002 - 009 don't need to be run.

Situation 2 (delphi and dbisam) will follow in a later post.

Links
Sql Compare