Getting Started with DataConstructor

A Tutorial for DataConstructor

Overview

What is in this tutorial?

In this tutorial, you will learn the basics of using t= he DataConstructor component. First you will learn ho= w to access DataConstructor programmatically. Then you will learn how to write XML scripts to configure DataConstr= uctor. Finally, you will learn about some of t= he specific features of DataConstructor.

Where is everything?

Overview.. 1<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Table of Contents. 1<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

The Unique Problem.. 2<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Code Evolves. 3<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Data Stores Metamorphose. 3<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Modifying Data is Risky. 3<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

The Known Path. 3<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Key Players. 4<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Factory. 4<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

StructureBuilder. 4<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Structure. 4<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

FeatureBuilder. 4<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Getting Started. 4<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Getting an Instance of Factory. 5<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Getting an Instance of StructureBuilder. 5<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Preparing your StructureBuilder. 5<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Configuring your StructureBuilder. 5<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Getting an Instance of Structure. 6<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Upgrading your Data Store. 7<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Encapsulate Everything. 7<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

An Example. 7<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Using XML to Define a Database. 8<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Basic Database XML. 9<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Getting a StructureBuilder Using a Factory and X= ML. 9<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Using Versions. 9<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Adding Multiple SQL Statements to Versions. 11<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Using Versioning. 12<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Another Simple Example. 12<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Advanced XML. 13<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Parameterization. 13<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Variables are for Deferring Commitment. 14<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Using Variables in XML. 14<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Binding Values to Variables. 15<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Variables are Transient. 16<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Placeholders. 16<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Defining a Placeholder in XML. 16<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Binding Behavior to a Placeholder. 16<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

The Catch: Working with Transactions. 17<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

The Other Catch: Flexibility with a Chance of Flimsiness. 18<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Conclusion. 18<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

Legal 19<= span style=3D'mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:m= inor-fareast; mso-no-proof:yes'>

The Unique Problem

Why do I need DataConstructor?

The problem, simply put, is that data stores – databas= es, read/write XML data and the like – are resistant to change. It is not the programmers, it is the me= dia themselves. As a consequence, it’s= been difficult to bring Agility to the data storage world. The root cause of the problem appears t= o be a difference in what is valued: it is easy to simply replace old binaries with newer ones, or to install an upgrade but it would be unthinkable to drop a deployed database with any significant amount of data then roll out a new o= ne.

…so we try to apply changes to a live database. These changes are often risky and the database world becomes a sort of “Wild West,” wherein workers must stay lat= e to ensure that a database deployment runs smoothly. In the case of XML data stores (as migh= t be used by an application), there is a lot of after-the-fact testing done by hand. Upgrades from version to ver= sion are often poorly tested and only certain migration paths are considered.

DataConstructor brings to the live data world the same= kind of certainty that compilers brought to the application world. It gives you a repeatable, testable, an= d well defined means of building and growing your database or XML file.

Code Evolves

In an Agile environment, when you install new code, you rarely go to great lengths to respect the old code. The version two binaries of an applicat= ion usually replace the version one binaries. There’s a good chance you would even make your customer uninstall previous versions of your product before installing the latest and greatest. Of course, there are some exceptions like public interfaces and web services but, by and large, the modus operandi is “out with the ol= d, in with the new.”

So why are we so willing to throw away what we have in= favor of the next big thing? The answer = seems pretty obvious: because the value of what is sitting on our hard-drives is pretty low. The value provided by = the application may be high, but the value of an installation is low. The easiest way to demonstrate this is = to think about the last time you had a problem with a corrupted install? What did you do? You probably uninstalled – maybe even g= oing through the registry and file system to ensure everything was gone – and th= en reinstalled?

…so code and binaries evolve. When a superior solution is introduced = into the environment, the inferior versions are slowly driven to extinction.

Data Stores Metamorphose

The data world works a little differently. Odds are, the bits sitting on hard driv= es, tapes, or optical platters which have been entered by your customers are pretty valuable. Those data are probably = a lot more valuable to your customer than a new feature. In fact, depending on the amount of inf= ormation involved, your customers’ existing data may be more valuable than any numbe= r of new features. The more data they h= ave (and hence the more value there is in their data) the harder it becomes to switch from an old format to a new format.

The reality is that where code can simply evolve, data= sets have to metamorphose. Evolution pr= oceeds by the death of one generation and the birth of another (individuals don’t evolve, species do). In contrast: = metamorphosis can occur within a single living organism (butterflies, for instance). A data store can change but continuity = must be maintained; it cannot simply be replaced. Thus: an Agile data store must undergo metamorphosis rather than evolution.

Modifying Data is Risky

Our solution has been to try and “confine” a waterfall process and/or thinking to the database world and then, when we are faced w= ith change, we use “cowboy” tactics such as staying late to make sure things “go smoothly,” in order to mitigate the inherent risks involved in upgrading da= ta. It’s nobody’s fault. We’ve all done it… there just wasn’t re= ally a better option.

The Known Path

DataConstructor introduces the concept of a known upgr= ade path. Using this, the structure of= every data store will grow in exactly the same way. The migration from one version to the next can be thoroughly tested.= If some part of the path does need to v= ary, you will be afforded strict control over when and where that variance is introduced and what effect it can have.

If you use DataConstructor in a test-first development environment – and it’s highly recommended that you do so– it brings certain= ty into play by letting you define all of the behavior and know when you’re done. Thus DataConstructor gives y= ou confidence that your data upgrade scripts will actually do what it’s suppos= ed to do. If you go one step further,= and use a test-driven development technique, it will also help you keep the qua= lity of your database high.

Key Players

What are the fundamental types in DataConstructor?

There are four types in the DataConstructor component = with which you must be familiar in order to use the product effectively. Those three types are Factory, Platform= , StructureBuilder, and Structure. In addition there is another type that is so central to the operation of DataConstructor that it will be difficult for you to avoid: FeatureBuilder. All of these types are in the HexagonSo= ftware.DataConstructor.Contracts namespace.

Factory

As with virtually all Hexagon Software components, the= re is a Factory object which must be used in order to access the component. This particular factory offers the abil= ity to instantiate a large variety of objects and we will only discuss a small sub= set of those in this tutorial (primarily: the four other types called out in th= is section).

StructureBuilder

Implementers of the StructureBuilder interface are Builders<= /a> that know how construct instances of the Structure type. The construction process for most implementations of Structure is very complex so it is highly recommended th= at you use an implementation of StructureBuilder rather than trying to do so u= sing only the Factory class described above. To be clear: StructureBuilder is not required but constructing insta= nces of Structure is a complex task that should be encapsulated. If StructureBuilder does not meet your = needs, please consider writing your own equivalent.

Structure

An implementation of Structure is, essentially, a description of how a database should look as of a certain version. It can be treated as a Strategy= for safely upgrading a database from some version to a target version.

FeatureBuilder

Implementations of the FeatureBuilder type are crucial= to the construction of a Structure object. <= /span>StructureBuilder objects use FeatureBuilder objects to describe the Structure object they wi= ll eventually produce.

Getting Started

How do I access DataConstructor?

The first thing you have to do is get an instance of Factory. Once you have a factory, = you will use it to get an instance of StructureBuilder. You will then add an instance of Featur= eBuilder to that StructureBuilder object describing some change you want made to a database. Next, you will use the S= tructureBuilder to get an instance of Structure. T= he Structure object can, in turn, be used to upgrade a database into a target state. Ideally, you will have encapsulated everything you’ve done.

Getting an Instance of Factory

The first thing you need to do is get an instance of Factory. This can be done using the FactoryLocator class (again: in the HexagonSoftware.DataConstructor.Contrac= ts namespace). The FactoryLocator cla= ss has a static method named “GetFactory” which accepts one argument: your license key.

So to get a factory, = you’ll want to do something like this:

Listing 1 – Getting an instance of Facto= ry using FactoryLocator

<= o:p>

string licenseXml =3D LicenseUtilities.= GetLicenseText();

Factory<= /span> factory =3D FactoryLocator.GetFa= ctory(licenseXml);

Now you have a factory that can be used to instantiate= DataConstructor objects.

Getting an Instance of StructureBuilder
Once you have your Factory, you’ll want to use it to g= et an instance of StructureBuilder. This= can be accomplished using the “CreateStructureBuilder” method. One of the overloads for CreateStructur= eBuilder takes no parameters and returns an un-configured instance of StructureBuild= er. To get your StructureBuilder, you’ll wa= nt to do something like this:

Listing 2 – Using your Factory to instan= tiate StructureBuilder

<= o:p>

StructureBuilder structureBuil= der =3D factory.CreateStructureBuilder();

=

Having done this, you= should have an instance of StructureBuilder which can be used to instantiate implementations of the Structure interface.

Preparing your StructureBuilder

StructureBuilder objects are, initially, completely unconfigured. When they are born, = they cannot even produce a Null strategy. This is because every StructureBuilder must have a Platform. There are two pre-defined platforms: Databases and XML. For the purpose= of this tutorial, we will choose the Database platform.

Listing 3 – Setting a StructureBuilder’s Platform

structureBuilder.Platf= orm =3D factory.Platforms.Database();

Configuring your StructureBuilder

Now you’ve got a StructureBuilder. The next step is to configure it. We do this by adding “features” to our = StructureBuilder. “Features” are described by implementat= ions of FeatureBuilder, the most common form of which also implements the Script= FeatureBuilder interface. The first step is to us= e our factory to get an instance of ScriptFeatureBuilder as shown in Listing 4.

Listing 4 – Using your factory to instan= tiate SqlScriptFeatureBuilder

<= o:p>

ScriptFe= atureBuilder featureBuilder =3D factory.FeatureBuilders.Script();

Once you have your feature builder, the next thing to = do is to assign its identity as in Listing 5.

Listing 5 – Assigning an identity to a F= eatureBuilder

<= o:p>

f= eatureBuilder.Identity =3D new Ver= sion(1, 0);

In order to impact a database, a Feature must ultimate= ly resolve down to SQL commands which can be executed against a database if th= ey need to be implemented. Therefore,= you will need to add at least one SQL command to our FeatureBuilder. We accomplish this by building an insta= nce of SqlCommand and adding it to our ScriptFeatureBuilder object’s list of comma= nds as demonstrated in Listing 6.

Listing 6 – Using “AddSqlStatement” to a= dd a simple SQL statement to a SqlScriptFeatureBuilder

<= o:p>

const string SQL =3D

@"C= REATE TABLE CUSTOMERS(

[Id] UNIQUEIDENTIFIER,

[FirstName] NVARCHAR(254),

[LastName] NVARCHAR(254))"<= span style=3D'mso-bidi-font-size:8.0pt;mso-bidi-font-family:"Courier New";mso-no= -proof: yes'>;

SqlComma= ndBuilder sqlCommand =3D factory.SqlCommands.CreateSqlCommandBuilder();

sqlCommand.Sql =3D SQL= ;

featureBuilder.AddComm= and(sqlCommand);

Of course, the FeatureBuilder object does you little g= ood when it’s just drifting through the Ether. You need to connect it to your StructureBuilder object and to do so = you should use the “AddFeatureBuilder” method. This is demonstrated in Listing 7.

Listing 7 – Programmatically adding a Fe= atureBuilder to a StructureBuilder

<= o:p>

structureBuilder.AddFe= atureBuilder(featureBuilder);

Now you have an instance of StructureBuilder which has= been configured to construct a meaningful Structure object.

Getting an Instance of Structure

As previously mentioned, implementations of the Struct= ureBuilder interface are just Builders… abstractions of the complex creation process f= or instances of Structure. It is objects that implement the Structure interface which do the actual work to upgrade a data store. To get an instance of Struc= ture, use the “GetStructure” method. The= “GetStructure” method takes an array of arguments each of which must be the identity of one target Feature. In other words, yo= u need to tell the StructureBuilder which parts of the database you want to see in= the finished product. There is an exam= ple of this in Listing 8.

Listing 8 – Using a StructureBuilder to = get an instance of Structure

Structur= e Structure =3D StructureBuilder.GetStructure(new Version(1, 0));

You should, now, have an instance of Structure that kn= ows how to upgrade a database to “Version 1.0.”

Upgrading your Data Store

Now it’s time to execute any required upgrades on your database. Naturally, you will need= a database connection; it’s assumed that you know how to get one of these. Once you have an instance of System.Data.IDbConnection in the “Open” state, you can upgrade the database= on the other end of the connection to version 1.0 by using your Structure obje= ct’s “Build” method as shown in .

Listing 9 – Using a Structure to upgrade a database to Version 1.0

IDbConne= ction connection =3D new SqlConnection("...");

connection.Open();

Structure.Build(connec= tion);

You should now have a database that has been upgraded = to version 1.0. Note that if you were= to call “Build” again on the same database, nothing would happen – the database already has version 1.0 implemented.

Encapsulate Everything

We recommend that you encapsulate everything, includin= g our own products. While we feel that o= ur interfaces are a simple enough point of encapsulation that, should you choo= se to, you could easily replace our product by writing your own implementation= s of those interfaces, there are extra benefits to adding your own layer of insulation. It is unlikely that yo= u need all of the features provided by DataConstructor in any one situation. By creating a Façade= over our products you can both ensure that you minimize the coupling to an outsi= de product (which is always a must) an= d simplify the interface upon which you depend.

An Example

Here is an example of a very simple Façade built on to= p of DataConstructor. It encapsulates the structure of a data= base with a Customers table.

Listing 10 – A very simple example of how to use= the DataConstructor component

using System;

using System.Collections.Generic;

using System.Data;

using System.Xml;

using HexagonSoftware.DataConstructor.Contracts;

namespace HexagonSoftware.DataConstructor.Examples.Tutorial

{

public class BasicDatabaseStructure

{

public static void UpgradeDatabase(= IDbConnection connection, Version identity)

{

string licenseXml =3D= LicenseUtilities.GetLicenseText();

Factory factory = =3D FactoryLocator.GetFactory(licenseXml);<= /o:p>

StructureBuilder structureBuilder =3D factory.CreateStructureBuilder();

structureBuilder.Platform =3D factory.Platforms.Databas= e();

ScriptFeatureBuilder featureBuilder =3D factory.FeatureBuilders.Script();

featureBuilder.Identity =3D = new Version(1, 0);

const string SQL =3D

@"C= REATE TABLE CUSTOMERS(

[Id] UNIQUEIDENTIFIER,

[FirstName] NVARCHAR(254),

[LastName] NVARCHAR(254))"<= span style=3D'mso-bidi-font-size:8.0pt;mso-bidi-font-family:"Courier New";mso-no= -proof: yes'>;

SqlCommandBuilder sqlCommand =3D factory.SqlCommands.CreateSqlCommandBuilder();

sqlCommand.Sql =3D SQL;

featureBuilder.AddCommand(sqlCommand);

structureBuilder.AddFeatureBuilder(featureBuilder);

Structure databaseStructure =3D

structureBuilder.GetStructure(identity);=

databaseStructure.Build(connection);<= /p>

}

Using this, you could start building on an interface b= ehind which you can extend the database, as needed.

Using XML to Define a Database

Is there an easier way to define my database?

Listing 10 demonstrates the most basic way of using DataConstructor. By “most basic” we mean “easiest to dig= est.” Programmatic definition often requires = an over-articulation of your needs and, therefore, imposes upon you an unneces= sary burden. There is a simpler way: Da= taConstructor supports a number of XML formats that provide a more compact and readable format with which you can define your database. The format we will focus on in this section of the tutorial is called “versions,” it is the simplest and best loved of the schemas supported by D= ataConstructor.

Basic Database XML

The simplest database definition would be one that does absolutely nothing. It’s not very = useful to us but, for demonstration purposes, it would look something like Listing 11.

Listing 11 – A minimal Database Structure script

<?xml versio= n=3D"1.0= " encoding=3D"utf-8" ?>

<= DatabaseWithVersions xmlns=3D"http://schemas.hexsw.com/Components/DataConstructor.xs= d" />

We now have the minimum amount of XML required to instantiate a StructureBuilder object.

Getting a StructureBuilder Using a Factory an= d XML

Now that we have our database defined as XML, we need = a way to make it into an instance of StructureBuilder. This can be accomplished with a factory= . The Factory interface has two methods n= amed “CreateStructureBuilder.” One of t= hem, we’ve already been introduced to: the overload which takes no parameters. The other method with that name takes o= ne argument: an instance of System.Xml.XmlElement containing a description of = our database’s structure.

You’ll probably find that it is easiest to embed your = XML script in the same assembly that houses your abstraction over the DataConst= ructor component. Assuming that you’ve ma= de your XML file available as a resource named “DatabaseBuild” in a resource f= ile named “Scripts,” all you need to do is change how you construct your Struct= ureBuilder from what is delineated in Listing 2 to something more like the following.

Listing 12 – Loading an XML resour= ce and using it to create a StructureBuilder object

<= o:p>

XmlDocument document =3D new XmlDocument();

d= ocument.LoadXml(Scripts.DatabaseBuild);

<= o:p>

StructureBuilder StructureBuil= der =3D

<= span style=3D'mso-tab-count:1'> factory.CreateStructureBuilder(document= .DocumentElement);

Now you should have the means to produce an instance o= f StructureBuilder which is a reflection of an XML file defining your database.

Using Versions

Now it’s time to add some meat to our XML. The easiest way to define a database us= ing DataConstructor is using versioning. Versioning is= a simplified form of the “feature-driven” mechanism we saw in the previous section (Getting Started).

Version-driven DataConstructors are more linear and, therefore, more predictable than feature-driven ones. When you are defining your database in = terms of features, you have the option of setting up your dependencies however you like. This means that you have more control over how your database evolves and, with that control, comes added = risk and responsibility. When you defin= e a database in terms of versions, each version builds on its predecessor. In essence, it’s like each version is a feature that with one dependency: its predecessor.

The XML expression of Database Versions is very simple= . The first thing you do is let DataConst= ructor know that your XML contains versions. This is done using the DatabaseWithVersions element.

Listing 13 – A database XML file t= hat operates in versioned mode

<= o:p>

<?xml versio= n=3D"1.0= " encoding=3D"utf-8" ?>

<= DatabaseWithVersions xmlns=3D"http://schemas.hexsw.com/Components/DataConstructor.xs= d">

</DatabaseWithVersions><= o:p>

Now you have an XML file that DataConstructor knows wi= ll contain “versions” and not “features.” Next, let’s translate the example spread out between Listing 4 through Listing 8 into our new XML. The first thing = you do is add a Version element to your document. Version has one required attribute named “Id” which is must contain a valid .Net version (major.minor[.build[.revision]]).= In order for a version to do anything i= t’s also going to need a Sql element, which will contain the SQL command that implements that version. Listing 14 shows what your XML file will probably look like when you are done. NOTE: the CDATA section is not necessar= y but it keeps Microsoft® Visual Studio® from messing with your formatting, so it= ’s recommended for readability’s sake.

Listing 14 – XML that defines version 1.0= of a database as having a Customers table

<?xml versio= n=3D"1.0= " encoding=3D"utf-8" ?>

<= DatabaseWithVersions xmlns=3D"http://schemas.hexsw.com/Components/DataConstructor.xs= d">

<Ve= rsion Id=3D"1.0">

<Sq= l>

<![CDATA[

CREATE TABLE CUSTOMERS(

[Id] UNIQUEIDENTIFIER PRIMARY KEY,=

[FirstName] NVARCHAR(254),

[LastName] NVARCHAR(254))]]>

</Sq= l>

</Ve= rsion>

</DatabaseWithVersions><= o:p>

With that, you can drop the programmatic definition of= your Database.Structure in favor of a mechanism that is completely driven by your XML. When you are done, it should = look something along the lines of the code in Listing 15.

Listing 15 – A resource-driven encapsulat= ion of the DataConstructor component

using System;

using HexagonSoftware.DataConstructor.Contracts;

using System.Data;

using System.Xml;

using System.Collections.Generic;

namespace HexagonSoftware.DataConstructor.Examples.Tutorial

{

public class ResourceDrivenDatabaseStructure

{

public static void UpgradeDatabase(= IDbConnection connection, Version identity)

{

string licenseXml =3D= LicenseUtilities.GetLicenseText();

Factory factory = =3D FactoryLocator.GetFactory(licenseXml);<= /o:p>

XmlDocument docume= nt =3D new XmlDocument();

document.LoadXml(Scripts<= /span>.DatabaseBuild);

StructureBuilder databaseStructureBuilder =3D

factory.CreateStructureBuilder(document.DocumentEle= ment);

Structure databaseStructure =3D

databaseStructureBuilder.GetStructure(identity);

databaseStructure.Build(dbConnection);

}

Note how much smaller it is… and it’s a little more te= stable, too. We now have a small programma= tic driver for our XML database definition.

Adding Multiple SQL Statements to Versions

Certain platforms, such as Microsoft’s “Jet” database engine, prohibit batches from containing more than one SQL statement. Even on industrial strength platforms s= uch as Microsoft’s SQL Server, you’ll find certain restrictions on which statement= s can be bundled into a batch. DataConst= ructor allows you to define versions that run multiple batches in a prescribed sequence. We do this by simply add= ing another Sql element to our version.

For example, let’s say you want to create a table and = then add a column to another table that is a foreign key into the new table. You might want to do that in multiple S= QL statements, even if only for clarity. Listing 16 shows how this might be done.

Listing 16 – A database version that requ= ires multiple SQL statements to be executed

<= o:p>

<= Version Id=3D"1.1">

<Sq= l>

<![CDATA[

CREATE TABLE Orders(

[Id] UNIQUEIDENTIFIER PRIMARY KEY,=

[CustomerId] UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES [Customers]([Id]),

[OrderDescription] TEXT)]]>

</Sq= l>

<Sq= l>

<![CDATA[

ALTER TABLE Customers ADD

LastOrderId UNIQUEIDENTIFIER NULL FOREI= GN KEY REFERENCES [Orders]([Id])]]>=

</Sq= l>

</Version>

We now have a version that executes multiple SQL statements. Please note that DataC= onstructor does not impose any rules as to how you batch your SQL statements. Only the underlying database technology= will enforce any such rules. That said,= we recommend splitting your versions = up into multiple SQL statements for the sake of readability and portability. <= /p>

Versioning

Versioning is the primary vehicle by which DataConstru= ctor delivers value. It lets you have confidence in your database upgrade path; because each version depends upon= its predecessor, you know that all inst= ances of your database – test and production alike – follow the exact same upgrade path.

Most users incorporate DataConstructor into their acce= ptance tests so that they know that the database in production will be built or upgraded exactly the same way as it was when it was tested. Some go as far as to specifically write business-visible acceptance tests specifically covering a database transiti= on – probably not necessary every time but it certainly won’t hurt. As an aside: such a practice can lead t= o good conversations regarding any potential changes to your domain model.

Because you have the ability to upgrade a database to = any version you like, and know that you’re moving along the exact same path, ev= ery time, you can easily write tests that specifically target the riskiest part= of database development: the transition from one version to the next. This is in addition to, rather than ins= tead of, the sorts of tests you would ordinarily write to ensure proper functionality and design. Targetin= g the quality of a transition to a version, in addition to the quality of that version itself, gives you the same kind of control over how your database design emerges as you are probably accustomed to having over how your code evolves.

There is one caveat: you have to play by the rules. This probably goes without saying but o= nce a transition has gone to production, that’s it. It is forever a part of that database’s history and should be left intact. If you need to change the = nature of the database it is best to add a new version that transitions away from = that point to another point. The same i= s true of “injecting” versions between two versions that have already been applied= to a production system: don’t.

Another Simple Example

Now it’s time to put it all together. Take version 1.1 (as shown in Listing 16) and add it to your database script. The end-result should look something like Listing 17.

Listing 17 – A database script with more = than one version

<= o:p>

<?xml versio= n=3D"1.0= " encoding=3D"utf-8" ?>

<= DatabaseWithVersions xmlns=3D"http://schemas.hexsw.com/Components/DataConstructor.xs= d">

<Ve= rsion Id=3D"1.0">

<Sq= l>

<![CDATA[

CREATE TABLE CUSTOMERS(

[Id] UNIQUEIDENTIFIER PRIMARY KEY,=

[FirstName] NVARCHAR(254),

[LastName] NVARCHAR(254))]]>

</Sq= l>

</Ve= rsion>

<Ve= rsion Id=3D"1.1">

<Sq= l>

<![CDATA[

CREATE TABLE Orders(

[Id] UNIQUEIDENTIFIER PRIMARY KEY,=

[CustomerId] UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES [Customers]([Id]),

[OrderDescription] TEXT)]]>

</Sq= l>

<Sq= l>

<![CDATA[

ALTER TABLE Customers ADD

LastOrderId UNIQUEIDENTIFIER NULL FOREI= GN KEY REFERENCES [Orders]([Id])]]>=

</Sq= l>

</Ve= rsion>

</DatabaseWithVersions><= o:p>

We now have a database definition that can do three th= ings:

1.&n= bsp; Upgrade an empty database to Version 1.0

2.&n= bsp; Upgrade an empty database to Version 1.1 (via 1.0)

3.&n= bsp; Upgrade a database that is already at Version 1.0 to Version 1.1

Again, please note how the database upgrade path is linear. You can choose to move for= ward to any point on the “timeline” (the sequence of versions) from any prior po= int but you must pass through all of the intermediary points to get there.

Advanced XML

Is there a way to embed custom behavior in an XML-de= fined database?

So far, you’ve developed a relatively simple database.= Nothing has varied from one database bu= ild to the next (aside from the target database, itself) and there are no features which require complex operations to which SQL is not well suited. In this section we explore two features= that help you handle more complicated problems. The first is parameterization, which lets you handle the case where = data varies from one build to the next. The second is placeholders, = which allow you to plug custom behavior in where SQL just won’t cut it. Both features still operate within the harness of DataConstructor and their use can be tested to the point of confidence. Both of these features= are delineated in the following two sections.

Parameterization

Is there a way to a= void hard-coding values?

Sometimes there are parts of your database upgrade pat= h that you specifically want to vary.<= span style=3D'mso-spacerun:yes'> Often this has to do with domain data.<= span style=3D'mso-spacerun:yes'> For instance, you may want to be able to decide the starting value for a counter in a database right when you build = that database. Another example would be= if a database needs to contain some self-identifying data that cannot safely be assumed until the moment that data needs to be inserted. These are cases for using the Variables feature.

Variables allow certain data in your database to fluct= uate at upgrade-time in a controlled way. In some cases, it’s fine to hard-code domain data. On the other hand, there are cases, such as when you are preparing an existing database to accept data from a company you just purchased, hard-co= ding won’t do. One might argue that no = values should be hard-coded. It’s your prerogative: there are costs and benefits to be weighed whenever you make something parameterized.

There are 3 steps required to parameterize your databa= se upgrade script.

1.&n= bsp; Declare a parameter in one of your SQL comma= nds and bind it to a Variable.

2.&n= bsp; Use the declared parameter in that command.<= /p>

3.&n= bsp; “Punch a hole” in your encapsulation of your build script so that a value can be specified for the Variable mentioned in step 1.

Step 1 may be a little confusing to you. That’s okay. The distinction between a variable and a parameter is not obvious. Variables are global to a database build. They are defined when you u= pgrade your database and evaporate as soon as the process is complete. Parameters have a much, much, shorter lifespan. They are only relevant w= hen a command is being executed. If a ve= rsion has been applied or will not be applied then they never get used.

Variables are for Deferring Commitment
Since Variables were designed with time-sensitive, vol= atile, initial values in mind, they only apply at the moment they are used, so onc= e a parameterized version has been applied to a database, the arguments used are lost forever. DataConstructor make= s no effort to track the values you specified; the only traces will be the impact your variables had on the database itself. Variables are not considered to be part of the identity of a version= . That is: you cannot cause a version tha= t has already been imposed on a database to be reapplied by specifying a different value for one of the variables that version uses.

Using Variables in XML

In order to leverage a variable you will first need to= use an element named “Command,” in one of your Versions. The Sql element is actually a simplifie= d form of Command which cannot have any parameters. The Command element has a required child named “Sql” which contains = the SQL script to be applied. Listing 18 shows an example Command which does not use any parameters.

Listing 18 – A Command element with no parameters

<= o:p>

<= Command>

<Sq= l>

<![CDATA[

INSERT INTO= [Counters]([Name], [Value]) VALUES('Global.MyCounter', 100000) ]]>= ;

</Sq= l>

</Command>

This will certainly populate a table with a counter va= lue but does not meet the requirement of allowing that starting value to vary.<= span style=3D'mso-spacerun:yes'> In order to be able to adjust part of y= our SQL statement, you’ll need to introduce a Parameter element to your command. Listing 19 shows an example of binding a parameter to a command using this element.

Listing 19 – A Command element with one parameter (SQL Server’s “T-SQL” Syntax)

<= o:p>

<= Command>

<Pa= rameter>

<Na= me>@CounterValue<= ;/Name>

<Va= riable>Global.MyCounter.StartValue</Variable>

</Pa= rameter>

<Sq= l>

<![CDATA[

INSERT INTO [Counters]([Name], [Value]) VALUES('Global.MyCounter', @CounterValue) ]]>

</Sq= l>

</Command>

Now you should have a database upgrade script capable = of inserting an initial counter-value into your database but that allows you t= o commit to a specific value at the last responsible moment. You may have noticed that, while your database build script knows about this variable, nothing else in your progr= am does. There is no way to actually specify a value for this variable in anything we’ve written, thus far. That’s the next step.

Binding Values to Variables

Earlier you built an abstraction that passes an instan= ce of System.Data.IDbConnection into the Structure.Build() method. That overload is just a simplified wrap= per for the real “Build” method, which accepts an instance of DataConstructor’s= DataBuildParameters interface. The DataBuildParameters interface has two properties: “Connection,” which holds a reference to the target database, and “Arguments,” which stores any variables you require.

To start using the more comprehensive overload of Stru= cture.Build, you’ll need to take six steps:

1.&n= bsp; Use factory.CreateDataBuildParameters, to instantiate the parameters object

2.&n= bsp; Use factory.CreateDatabaseConnection, to instantiate an instance of DatabaseConnection

3.&n= bsp; Bind your DatabaseConnection into the DataBuildParameters object created in step 1

4.&n= bsp; Bind your ADO.NET connection into the DatabaseConnection object created in step 2

5.&n= bsp; Apply any parameter-bindings to the DataBuil= dParameters.Arguments property

6.&n= bsp; Call Structure.Build(DataBuildParameters) instead of Structure.Build(IDbConnection)

Listing 20 demonstrates how you might do this.

Listing 20 – Binding a value to a paramet= er

DataBuil= dParameters buildParameters =3D factory.CreateDataBuildParameters();

Database= Connection dbConnection =3D factory.CreateDatabaseConnection();

dbConnection.DatabaseC= onnection =3D connection;

buildParameters.Connec= tion =3D dbConnection;

buildParameters.Variab= les["Global.MyCounter.StartValue"] =3D= 25000;

databaseStructure.Buil= d(buildParameters);

Doubtless, you will probably derive a more elegant sol= ution than hard-coding the value of the variable in your encapsulation of DataCon= structor but this conveys the basic mechanism.

Variables are Transient

Please remember that variables have a very short lifes= pan; if you want to capture what variable was used to implement a certain versio= n, you will need to devise a mechanism to do this yourself. Variables are also completely unconstra= ined. It is assumed that the value you bind i= n to DataConstructor is acceptable. If you need additio= nal validation, such as ensuring that a counter value is non-negative, you will need to either do that in your database XML as part of the version that use= s a variable, in your encapsulation of DataConstructor, or both.

Placeholders

Is there a way to plug in custom bits of behavior?

Not everything can be expressed as SQL. Certain database upgrade behaviors are = going to need to be defined in terms of code. This is where the Placeholders feature can come in handy. In a DataConstructor XML script you can= leave an “empty spot” wherever a Version or a Command should be, in the form of a Placeholder. The placeholders pres= ent themselves as actual commands but their behavior can be defined dynamically, after a StructureBuilder has been instantiated but before it produces a Str= ucture object.

Defining a Placeholder in XML

Placeholder versions will not be covered in this docum= ent because you can simulate them by wrapping a similar Placeholder statement i= n a version and using placeholder statements is both safer and more straightforward. To define a place= holder statement within a version, you simply use the Placeholder element where you would have put a Sql or Command element. The content of the Placeholder element is treated as the name of the placeholder, which will come in handy once it’s time to bind in behavior.

Listing 21 – XML defining a placeh= older

<= Placeholder>= ConfirmThatItIsDone</Placeholder>

Binding Behavior to a Placeholder

Once you obtain a StructureBuilder object, it’s time t= o bind behaviors in to the placeholders. = To do this you need to obtain the Version hosting the placeholder, use that to ob= tain the placeholder, and bind a “command builder” to the placeholder. The “command builder” must, in turn, be= able to produce an instance of “Command,” which is what will perform the custom action, if needed. That probably s= ounds like a lot of work but Listing 22 shows that it is not that bad.

Listing 22 – Binding a placeholder “Comma= ndBuilder” into an incomplete version

ScriptFe= atureBuilder incompleteFeatureBuilder =3D

(ScriptFeatureBuilder)dat= abaseStructureBuilder.GetFeatureBuilder(new Version(1, 2));

Placehol= derCommandBuilder placeholderCommandBuilder =3D

incompleteFeatureBuilder.GetPlaceholder("ConfirmThatItIsDone");

placeholderCommandBuil= der.ActualCommandBuilder =3D

ConfirmCompletionCommandBuilder<= /span>.GetInstance();

= ;

// ...

<= o:p>

internal class ConfirmCompletionCommandBuilder <= /o:p>

<= span style=3D'mso-tab-count:1'> : Command= Builder

<= span style=3D'mso-tab-count:1'> public Command GetCommand()

<= span style=3D'mso-tab-count:1'> {

<= span style=3D'mso-tab-count:2'> return ConfirmCompletionCommand.GetInstance();=