Recently I've been maintaining my database schema changes using DBXExpress, more about the process I follow can be found here

I'm currently moving an application from MS Sql Server to Blackfish and unfortunately all I really have is a bunch of MS Sql specific scripts. I wished I had a set of migrations that I could use as the basis for my Blackfish database, so I decided to dive into DBExpress again and I have extended the MSBuild tasks I created to generate Delphi code that I could use as a starting point.

In the screenshot below can be seen the tables of the SQL Server version of the "NorthWind" database and on the right a Blackfish version.

An updated version of the original code can be found here.

As with the previous version having downloaded the code from codecentral, the first thing that needs to be done is to open up the "MigrationProjectGroup.groupproj" projectgroup

in the root directory. In the new version I have added a new package called Moshine.SchemaGenerationTest. In the download it contains a schema generation which are .pas files used to create the Northwind tables using dbxexpress. If you wish to re-generate, you can remove the .pas files from this package so that it would look like the screenshot below.

 

 

The important part of the project are the 2 msbuild target files and an app.config

The app.config contains two connection strings, one for MSSQL Server and the other for the Blackfish database that will be created. You are quite likely going to have to modify those so that they match your environment.

Generation.targets contains the specification for the schema generation and a copy can be seen below

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

    <UsingTask TaskName="Moshine.Migration.MSBuild.TMigrationSchemaGenerationTask"
            AssemblyFile="..\Moshine.MSBuildMigration\bin\Moshine.MSBuildMigration.dll" />

    <PropertyGroup>
        <MigrationAssembly>bin\Moshine.SchemaGenerationTest.dll</MigrationAssembly>
        <ConnectionName>NorthwindConnection</ConnectionName>
        <DestinationDirectory>d:\develop\moshine\Migration\Moshine.SchemaGenerationTest</DestinationDirectory>
        <Namespace>Moshine.SchemaGenerationTest</Namespace>
        <UpperCaseTableNames>true</UpperCaseTableNames>
    </PropertyGroup>

    <Target Name="SchemaGeneration">
        <TMigrationSchemaGenerationTask  UpperCaseTableNames="$(UpperCaseTableNames)" Namespace="$(Namespace)" DestinationDirectory="$(DestinationDirectory)" MigrationAssembly="$(MigrationAssembly)" ConnectionName="$(ConnectionName)" />
    </Target>

</Project>

DestinationDirectory contains the target directorty for the schema generation, your most likely going to have to change this.

Once the projectgroup has been built you should be able to move to a RAD Studio command prompt from which you can generate the schema.

The commandline from the Moshine.SchemaGenerateTest directory is MSBUILD generation.targets -t:SchemaGeneration 

You can also execute the MSBuild task from the IDE by right clicking on the  Generation.targets file in the project manager and performing a  targets/schemageneration

The actual code that performs the code generation is in the Moshine.Migration.Framework project as Moshine.Migration.Framework.MigrationGenerator.pas. I have tested against the MSSQL Server database Northwind that is used in quite a few Microsoft demos and against the database I'm migrating. In theory it should work against other MS SQL Server databases and any other database supported by DBXExpress. I haven't supported every datatype so you might get an error like this.

In which case you will have to modify the generator code, which shouldn't be too difficult :)

Assuming the schema generation went ok you should have .pas files in the  generation directory. Its 1 file per database table.

The screenshot below shows the files for Northwind

You can then add the files to the SchemaGenerationTest project and rebuild. The resulting assembly is going to be used to build the Blackfish database.

The generated code looks like the sample below

 

procedure TCreateTableShippersMigration.Up;
var
tableMetaData: TDBXMetaDataTable;
newTDBXWideVarCharColumn: TDBXWideVarCharColumn;
newTDBXInt32Column: TDBXInt32Column;
begin
tableMetaData := TDBXMetaDataTable.Create();
tableMetaData.TableName:='SHIPPERS';
newTDBXInt32Column:= TDBXInt32Column.Create('ShipperID');
newTDBXInt32Column.AutoIncrement:=true;
if(not self.Provider.CheckColumnSupported(newTDBXInt32Column))then
begin
newTDBXInt32Column.AutoIncrement:=false;
end;
newTDBXInt32Column.Nullable:=false;
tableMetaData.AddColumn(newTDBXInt32Column);
newTDBXWideVarCharColumn:= TDBXWideVarCharColumn.Create('CompanyName',40);
newTDBXWideVarCharColumn.Nullable:=false;
tableMetaData.AddColumn(newTDBXWideVarCharColumn);
newTDBXWideVarCharColumn:= TDBXWideVarCharColumn.Create('Phone',24);
tableMetaData.AddColumn(newTDBXWideVarCharColumn);
provider.CreateTable(tableMetaData);
self.AddPrimaryKey('ShipperID','SHIPPERS');
end;

This is DBXExpress Delphi code to create the SHIPPERS database table. Each class corresponds to 1 step in a migration. 

Having successfully built the assembly the second targets file Migration.Targets in the SchemaGenerationTest project can be used. You can right click on the

Migration.Targets file in the project manager and perform Targets/migration. 

From a CodeGear command prompt this would be MSBUILD migration.targets-t:migration. Assuming that the migration was performed you should have a Blackfish database

with the tables created. As I mentioned before you be able to perform a code generation and migration against any database that DBXExpress supports.

The generated code only creates the tables from the source schema but it should be a good starting point from which you can add your own code to further extend the migration and the database schema.

Feedback is welcome and the source can be found in CodeCentral here 

Overview

I recently spent some time looking at Ruby On Rails. One of the many things about Rails that caught my interest was the way that schema changes to databases are performed using Ruby. I think it was this post by Steve Shaughnessy about the new dbExpress 4.0 MetaData where it occurred to me perhaps I could use the DBExpress MetaData to do my something similar in Delphi.

I had several goals in mind:-

• Produce a repeatable series of steps to upgrade database schemas.
• Stay away from having to remember SQL syntax.
• Automate the upgrade process
• Target multiple db servers

Before presenting my solution, I would like to thank Leonel Togniolli, CodeGear R&D for helping me with how to go about calling the DBExpress MetaData API.

Project Layout

I have bundled everything together in a projectgroup which contains five projects.
Moshine.Migration.Framework.dll - Contains most of the core code
Moshine.Migration.Framework.Tests.dll - Contains some unit tests
Moshine.Migration.Test.dll - is an example of a database migration
Moshine.MigrationRunner - Is a VCL.Net application to execute migrations
Moshine.MSBuildMigration.dll - Contains a collection of MSBuild tasks that can be used from the IDE or standalone from the RAD Studio Command Line

Example

A database schema generation or migration is composed of multiple steps. A step is the equivalent to a Delphi class.  A step can perform such actions as creating tables, adding columns, adding indexes, adding foreign keys, and executing sql. Sql could be used to add stored procedures and insert domain data into tables.

So if you take a look at Moshine.MigrationTest.dll, this contains seven units which each contain a Delphi Class. So after running the migration the database would be at version 7. For example this is the declaration for the first step

[Migration(1)]
TBookMarksFirstMigration=class(TMigration)
public
    procedure Up();override;
    procedure Down(); override;
end;

Migrations can go backwards and forwards so I have Up and Down methods. The idea is that to create a step you create a new class derived from TMigration and decorate with an attribute which describes which step this is. Migrations must start at 1 and be sequential.

If you take a close look at the Up method in TBookMarksFirstMigration, its creating a table called Bookmarks using the DBXExpress metadata API. After table creation the column ID is set as the primary key.

The corresponding Down method for this class would drop the table.

TMigration


All migration steps must be derived from TMigration. This base class is in Moshine.Migration.Framework in Moshine.Migration.Framework.MigrationBase.pas. I have provided a number of protected methods that Migration developers can call

procedure AddColumn(tableName:string;columnName:string;dataType:integer);
procedure RemoveColumn(tableName,columnName:string); procedure AddIndex(tableName, columnName: string; indexName: String);
procedure RemoveIndex(tableName, columnName: string; indexName: String);
procedure AddPrimaryKey(columnName,TableName:string); procedure AddForeignKey(columnName,tableName,primaryTableName,primaryColumn:string);
procedure RemoveForeignKey(columnName,tableName,primaryTableName,primaryColumn:string);

There are also 2 properties

property Provider:TAdoMetaDataProvider read FProvider write FProvider;
property Connection:DbConnection read FConnection write FConnection;

Connection is a database connection but Provider is the most important. Its a TAdoMetaDataProvider object and is essentially the DBXExpress metadata. The protected methods I supply are basically calling methods in the metadata API. Most of the time I would expect that you would call the helper methods but you can use use the Provider or Connection to execute raw sql.

MSBuild Setup


I have been using MSBuild target files to define targets that call the various tasks. The Moshine.MigrationTest.dll project contains one such targets file called TesTargets.Target

The MSBuild tasks are driven by two properties, a connection and assembly that contains your migration.

Moshine.MigrationTest is a sample migration. The project contains an app.config

This contains your connection specifications. For example mine contains

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="SomeConnectionString" connectionString="database=C:\develop\moshine\Migration\Moshine.MigrationRunner\App_Data\migration.jds;user=SYSDBA;password=masterkey;host=localhost;protocol=TCP;create=true" providerName="Borland.Data.BlackfishSQL.RemoteClient"/>
    </connectionStrings>
</configuration>

a single Blackfish connection called "SomeConnectionString"

The other item that must be added to a project are the MSBuild tasks. I have added TestTarget.targets to Moshine.MigrationTests.dll

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

    <UsingTask TaskName="Moshine.Migration.MSBuild.TMigrateDownTask"
            AssemblyFile="C:\develop\moshine\Migration\Moshine.MSBuildMigration\bin\Moshine.MSBuildMigration.dll" />
    <UsingTask TaskName="Moshine.Migration.MSBuild.TMigrateUpTask"
            AssemblyFile="C:\develop\moshine\Migration\Moshine.MSBuildMigration\bin\Moshine.MSBuildMigration.dll" />
    <UsingTask TaskName="Moshine.Migration.MSBuild.TMigrationTask"
            AssemblyFile="C:\develop\moshine\Migration\Moshine.MSBuildMigration\bin\Moshine.MSBuildMigration.dll" />
    <UsingTask TaskName="Moshine.Migration.MSBuild.TMigrationSchemaVersionTask"
            AssemblyFile="C:\develop\moshine\Migration\Moshine.MSBuildMigration\bin\Moshine.MSBuildMigration.dll" />

    <PropertyGroup>
        <MigrationAssembly>C:\develop\moshine\Migration\Moshine.MigrationTest\bin\Moshine.MigrationTest.dll</MigrationAssembly>
        <ConnectionName>SomeConnectionString</ConnectionName>
    </PropertyGroup>

    <Target Name="Migration">
        <TMigrationTask  MigrationAssembly="$(MigrationAssembly)" ConnectionName="$(ConnectionName)" />
    </Target>

    <Target Name="Up">
        <TMigrateUpTask  MigrationAssembly="$(MigrationAssembly)" ConnectionName="$(ConnectionName)" />
    </Target>

    <Target Name="Down">
        <TMigrateDownTask  MigrationAssembly="$(MigrationAssembly)" ConnectionName="$(ConnectionName)" />
    </Target>

    <Target Name="SchemaVersion">
        <TMigrationSchemaVersionTask  MigrationAssembly="$(MigrationAssembly)" ConnectionName="$(ConnectionName)" />
    </Target>

</Project>

The contents is above. I have setup two properties and four targets. The two properties define the Connection to use and the assembly that contains the migrations that will be run by the MSBuild targets.

You should now be able to open a RAD Studio Command Prompt. It you change to the Moshine.MSBuildMigration directory you add run the migrations. For example

msbuild TestTarget.targets -t:up
msbuild TestTarget.targets -t:down
msbuild TestTarget.targets -t:SchemaVersion
msbuild TestTarget.targets -t:Migration

You can also run the migrations from the IDE project menu as well. In the project manager select TestTarget.targets in the Moshine.MigrationTest project, right click and select the targets menu item. Listed as submenu items of the targets menu will be the same four targets as above, as can be seen below.

The SchemaVersion targets displays the database schema version.
Migration executes all steps defined in the assembly.
Up and down step up and down the migration.

So say I have my database at database schema version 4 and I execute the migration target. Steps 5, 6 and 7 will be performed.


Running a Migration



The Migration Framework looks for a database table called Migration_Schema to determine the database version and then looks at the steps provided in the metadata assembly to work out the steps to run.

If you issue the Migration target and the number of steps doesn't match the schema version the appropriate steps will be run.

You can also use the Up and down targets step up and down the migrations. MigrationSchemaVersion outputs the database schema version.

Moshine.MigrationRunner

I have also provide a VCL.Net application which duplicates the MSBuild functionality. This is configured with app.config. An example of which can be seen below.

Note(s)

To be able to build the projectgroup you will need the December 2007 Rad Studio Update installed.

The Moshine.MSBuildMigration.dll assembly relies on Moshine.Migration.Framework.dll, so make sure that both assemblies are located wherever you set the using task.

i.e

<UsingTask TaskName="Moshine.Migration.MSBuild.TMigrateDownTask"
            AssemblyFile="C:\develop\moshine\Migration\Moshine.MSBuildMigration\bin\Moshine.MSBuildMigration.dll" />


In theory you should be able to perform migrations on any database that CodeGear provides MetaData for. I have done some testing with AdoDbx against MSSQL, Interbase and MySql. Most of the time I have been using the Blackfish native providers.

If this interests you, I have placed the code in codecentral at this location. Absolutely any feedback is welcome.