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 

One of the reasons I like Blackfish is that its just so easy to deploy as part of a ASP.Net hosted solution. I had been exploring BlogEngine.Net as an alternative to Community Server and discovered that it comes with a couple of data providers, xml and Sql Server. Since it comes with source code I was able to determine that it should be possible to write my own data provider talking to Blackfish.

I thought it would be a really interesting project to try and get BlogEngine.Net running against Blackfish. This blog post will document how I built the data provider and how you can get  BlogEngine.Net running against it.

The first step was to download the web application  from here. If your just interested in deployment "BlogEngine.NET 1.3 (website)" is sufficient.

I'm running with version 1.3

I'm also a fan of Eco so I decided I would implement my data access layer using that. Once downloaded I got the blog running using the Ms Sql data provider, I was then able to reverse engineer the resulting database into a model.

The resulting model is shown below.

The  source code I will provide is broken down into three projects:-

EcoBlogEngineProvider: This is the BlogEngine data provider.

EcoBlogEngineProvider.MetaData : Rather than create a sql script to generate the Blackfish database, I used the DbxExpress metadata  API to code the database schema creation in Delphi.

Moshine.Eco.Persistance: This assembly contains a component . Since the Eco model is reverse engineered, Eco needs an xml file which maps the model to the database. TResourceMappingProvider is a component I created using a similiar component to one that ships with ECO, this allows me to embed the xml mapping information in the assembly that contains the Eco model.

A word of warning, if you do decide to download the code your going to need the Architect edition of RAD Studio 2007.

Once downloaded you need to build the projects, I have supplied a projectgroup for that. EcoBlogEngineProvider is dependant on BlogEngine.Core, this assembly can be found in the BlogEngine.Web bin directory which comes as part of the BlogEngine.Net download. Your probably going to have to modify the assembly reference in the EcoBlogEngineProvider project before you can build.

Inside the project EcoBlogEngineProvider.MetaData is a file called Target1.target. This is a MSBuild file which allows you to create Blackfish databases populated with the BlogEngine schema.

I have a custom MSBuild task which I run from the IDE which uses the classes inside  EcoBlogEngineProvider.MetaData to create the database schema. It used some code I uploaded to codecentral. You can read my blog post about it here

Assuming that you want to use the CodeGear ASP.Net 2.0 providers for login and membership services, the best way to start the database creation process is to create a new ASP.Net Web Application. Before running the application I would modify the Membership provider settings in web.config to look this 

 

       <membership defaultProvider="AspNetAdoMembershipProvider">
            <providers>
                <remove name="AspNetAdoMembershipProvider"/>
                <add name="AspNetAdoMembershipProvider"
                     type="Borland.Web.Security.AdoMembershipProvider, Borland.Web.Provider, Version=11.0.5000.0, Culture=neutral, PublicKeyToken=91d62ebb5b0d1b1b"
                     connectionStringName="BlackfishSQLAspNet"
                     applicationName="/"
                     enablePasswordRetrieval="false"
                     enablePasswordReset="true"
                     requiresQuestionAndAnswer="false"
                     requiresUniqueEmail="false"
                     passwordFormat="Hashed"
                     maxInvalidPasswordAttempts="50"
                     minRequiredPasswordLength="8"
                     minRequiredNonalphanumericCharacters="0"
                     passwordAttemptWindow="10"
                     passwordStrengthRegularExpression=""/>
            </providers>
        </membership>

 

I turned off requiresQuestionAndAnswer and set  minRequiredNonalphanumericCharacters to 0. You can now run ASP.Net configuration from the project manager.

I used the configuration manager to create 2 roles Adminstrator and Editor . I then created a user that was a member of the Adminstrator role.

Following that I closed the project in the IDE and went to the App_Data directory in the project directory. This contains three files which is my Blackfish database and these are

bsql_aspnetdb.jds
bsql_aspnetdb_LOGA_ANCHOR
bsql_aspnetdb_LOGA_0000000000

I renamed them to

BlogEngine.jds
BlogEngine_LOGA_ANCHOR
BlogEngine_LOGA_0000000000

and saved them in a safe place for later use.

So lets recap. If I downloaded the CodeCentral project to a directory called c:\develop\BlogEngine it would contain the following directories.

EcoBlogEngineProvider
EcoBlogEngineProvider.MetaData
lib
Moshine.Eco.Persistence

If you attempted to build the projectgroup you will notice that a BlogEngine.Web directory is created. This is where you can copy your BlogEngine.Net installation. The final step to deploy your blog is to copy the BlogEngine.Web directory to your hosting service.

If you go into the bin subdirectory of BlogEngine.Web you should find EcoBlogEngineProvider.dll

The next step is to copy your Blackfish database into the App_Data sudirectory of BlogEngine.Web.

You are now going to create the BlogEngine schema in that database. The first thing you need to do is to open  app.config in the EcoBlogEngineProvider.MetaData directory.

You need to modify the database connection string to point to your database in the App_Data directory. For example

 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

    <connectionStrings>
        <add name="SomeConnectionString" connectionString="database=C:\develop\BlogEngine\BlogEngine.Web\App_Data\BlogEngine.jds;user=SYSDBA;password=masterkey;host=localhost;protocol=TCP;create=false" providerName="Borland.Data.BlackfishSQL.RemoteClient"/>
    </connectionStrings>


</configuration>

You can then save the file and rebuild the project.  You can now use the IDE to create the database schema by right clicking on Targets1.target and executing the migration task as can be seen in the picture below.

 

  Assuming everything is correct this should result in a successful build with 0 errors. If you use the IDE data explorer on that database it should look like the following picture.

  We should now have a empty database for our blog.

The final step before running the Blog web application is to configure web.config to use the Blackfish database, the Eco BlogEngine Provider and the CodeGear ASP.Net Providers.

web.config can be found in c:\develop\blogengine\blogengine.web\web.config 

The relevant parts are as follows:-

1) Configure the application to use the EcoBlogProvider

  <BlogEngine>
    <blogProvider defaultProvider="EcoBlogProvider">
      <providers>
        <add name="XmlBlogProvider" type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core"/>
        <add name="MSSQLBlogProvider" type="BlogEngine.Core.Providers.MSSQLBlogProvider, BlogEngine.Core"/>

        <add name="EcoBlogProvider" type="Moshine.BlogProvider.TEcoBlogProvider,EcoBlogEngineProvider"/>

      </providers>
    </blogProvider>
  </BlogEngine>

2) Add the Blackfish database connection string

  <connectionStrings>
    <clear/>

    <add name="BlackfishBlogEngine" connectionString="database=|DataDirectory|BlogEngine.jds;user=SYSDBA;password=masterkey;" providerName="Borland.Data.BlackfishSQL.LocalClient"/>
</connectionstring>

The EcoBlogEngineProvider is using the inprocess LocalClient so you need to specify the provider name as Borland.Data.BlackfishSQL.LocalClient. Its probably best to change the user and password for deployment. The connectionname must be BlackfishBlogEngine because that is the one the data provider is configured to use.

3) Comment out the membership and roleManager sections and replace them with the CodeGear ones from the web.config of the application you used to create the initial Blackfish database.

    <roleManager enabled="true" cacheRolesInCookie="true" defaultProvider="AspNetAdoRoleProvider" createPersistentCookie="true">
        <providers>
            <remove name="AspNetAdoRoleProvider" />
            <add name="AspNetAdoRoleProvider" connectionStringName="BlackfishBlogEngine" applicationName="/" type="Borland.Web.Security.AdoRoleProvider, Borland.Web.Provider, Version=11.0.5000.0, Culture=neutral, PublicKeyToken=91d62ebb5b0d1b1b"/>
        </providers>
    </roleManager>

    <membership defaultProvider="AspNetAdoMembershipProvider">
      <providers>
        <remove name="AspNetAdoMembershipProvider" />
        <add name="AspNetAdoMembershipProvider" type="Borland.Web.Security.AdoMembershipProvider, Borland.Web.Provider, Version=11.0.5000.0, Culture=neutral, PublicKeyToken=91d62ebb5b0d1b1b" connectionStringName="BlackfishBlogEngine" enablePasswordRetrieval="false"
            enablePasswordReset="true"
            requiresQuestionAndAnswer="true"
            applicationName="/"
            requiresUniqueEmail="false"
            passwordFormat="Hashed"
            maxInvalidPasswordAttempts="50"
            minRequiredPasswordLength="2"
            minRequiredNonalphanumericCharacters="0"
            passwordAttemptWindow="10"
            passwordStrengthRegularExpression=""/>
      </providers>
    </membership>
 

4) Unfortunately Blackfish and the CodeGear providers do not run under medium trust so its very important to comment out

<!--   
    <trust level="High" />
--> 

the above section.

 

Now that the database is in place and the web.config is configured its time to verify that everything is in place. I use the Cassini webserver for this and it can be found in the RAD Studio bin directory. From the RAD Studio command prompt change to the bin directory.

 i.e C:\Program Files\CodeGear\RAD Studio\5.0\bin

You can now run Cassini using this commandline

cassiniwebserver.exe g:\develop\blogengine\blogengine.web 8080

 As soon as Cassini is up and running you can run a browser and browse to this url http://localhost:8080/default.aspx. The web page should look something like this

 

You should also be able to login using the user you setup at the begining.

 

In part 2 I aim to talk the code inside the data provider, how I imported my existing blog into BlogEngine and deploying BlogEngine to a hosting provider.

 

I hope this was useful. I have more than likely glossed over something and probably missed stuff out. Any feedback is welcome.

 

The source code for the article can be found here.