Here’s where the process really starts to pay for itself in spades. The previous soliloquy led you toward a conclusion that was left unsaid only because it is in the domain of theory: Emergent design can safely be achieved in a database with transition testing in place.

The transition tests for this version (1.2) do all of the things demonstrated in the previous sections:

Item 3 is the meat of this whole process. If all you ever had to do was add to a database, rolling out new versions would be pretty simple. In fact, if that were the case, I probably wouldn’t even see a point in writing this short cut. Unfortunately,^[15] things seldom work that way.

Certainly, you will have to change existing structures if you want to allow a design to emerge. You will also probably have to change those structures at some point even if you are trying to suppress design changes. These points lead to one of the major reasons for testing even identity transformations: to ensure we have a stable platform from which to test those transformations that are nontrivial.

The new structure that has no dependencies on anything else is the Roles table, so we’ll start there. The first thing that I did was build a unit test suite for it. Listing 22 presents the entire test suite for the Roles table as of the end of iteration 3.

 [TestFixture]
public class RolesTableTests
{
  private const string IDENTITY =
    "{42666B38-C864-49d9-ABC1-13F55BA3AD83} ";
  private const string NAME = "ROLE";

  private ExceptionUtilities _exceptionUtilities;
  private DatabaseUtilities _databaseUtilities;
  private DatabaseDefinition _database;

  private DataServices.Factory _factory;
  private DataServices.Store _store;
  private DataServices.Table _rolesTable;

  private Guid? _identity;
  private string _name;

  [SetUp]
  public void SetUp()
  {
    _database = DatabaseDefinition.GetInstance();
    _exceptionUtilities = ExceptionUtilities.GetInstance();
    _databaseUtilities = DatabaseUtilities.GetInstance();

    RebuildDatabase();

    _factory = DataServices.FactoryLocator.GetFactory();
    _store = _factory.GetStore();
    _rolesTable = _store.GetTable("Roles");

    _identity = new Guid(IDENTITY);
    _name = NAME;
  }

  [Test]
  public void DataCanBeSelectedFromTable()
  {
    _rolesTable.Select(null);
  }

  [Test]
  public void CanInsertDataIntoRolesTable()
  {
    InsertRecord();
  }

  [Test]
  public void CannotInsertDataIntoRolesTableWithoutAnIdentityField()
  {
    _identity = null;

    _exceptionUtilities.ExpectException<Exception>(
      () => InsertRecord());
  }

  [Test]
  public void CannotInsertDataIntoRolesTableWithoutATechnicalNameField()
  {
    _name = null;

    _exceptionUtilities.ExpectException<Exception>(
      () => InsertRecord());
  }

  [Test]
  public void OwnerRoleExistsInRolesTable()
  {
    var findOwnerCriterion = _factory.CreatePropertyCriterion(
      "TechnicalName", BusinessServices.RoleName.OWNER);

    var rows = _rolesTable.Select(findOwnerCriterion);

    Assert.AreEqual(1, rows.Count());
  }
  [Test]
  public void ReviewerRoleExistsInRolesTable()
  {
    var findReviewerCriterion = _factory.CreatePropertyCriterion(
      "TechnicalName", BusinessServices.RoleName.REVIEWER);

    var rows = _rolesTable.Select(findReviewerCriterion);

    Assert.AreEqual(1, rows.Count());
  }

  private void RebuildDatabase()
  {
    _databaseUtilities.DestroyDatabase(_database);
    _database.UpgradeDatabaseToCurrentVersion();
  }

  private void InsertRecord()
  {
    Record record = _factory.CreateRecord();

    record.SetProperty("Identity", ConvertNullToDBNull(_identity));
    record.SetProperty("TechnicalName", ConvertNullToDBNull(_name));

    _rolesTable.Insert(record);
  }
  private object ConvertNullToDBNull(object toConvert)
  {
    return toConvert ?? DBNull.Value;
  }
}

With the exception of testing that certain data are inserted in the Roles table when it is born, you’ve already seen examples similar to a lot of these tests. Some people might question the necessity of writing unit tests for domain data. I am doing so because those data cannot be changed through the application itself. As a result it is the responsibility of the developer to make sure those data are there. That which we must do, we must unit test.

The unit test suite for the Roles table ensures that the table has two rows, one each for the Owner and Reviewer roles. Every time those tests pass, I will know that the database meets one of the requirements mentioned earlier (that the two roles in the system are described in the Roles table). The tests failed initially, so I knew that I needed to make some changes to my database, which in turn meant more transition tests were required.

I won’t go through all of the transition tests for the Roles table here; I completed all of the necessary tasks in previous sections:

I repeated this process until I had an empty Roles table with the correct schema. Listing 23 shows what the builder script looked like at that point. The script creates a new Roles table with an Identity primary key column and a TechnicalName non-null string column.

<DatabaseWithVersions>
  <Version Id="1.0">
    <!-- Snipped. -->
  </Version>
  <Version Id="1.1">
    <!-- Snipped. -->
  </Version>
  <Version Id="1.2">
    <Sql>
CREATE TABLE [Roles](
  [Identity] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  [TechnicalName] CHAR(4) NOT NULL)
    </Sql>
  </Version>
</DatabaseWithVersions>

Let’s look at the transition tests that ensure the domain rows exist (see Listing 24). Each of the tests in Listing 24 executes a SQL SELECT command with a WHERE clause against the Roles table to ensure it has one of the required rows in it. There are two roles, each of which has a “technical name”—that is, a four-letter code that the application can use to reliably find the role in the database.

private DatabaseUtilities _databaseUtilities;

private DatabaseDefinition _database;

private readonly Version _originalVersion = new Version(1, 1);
private readonly Version _newVersion = new Version(1, 2);

[TestFixtureSetUp]
public void SetUp()
{
  _databaseUtilities = DatabaseUtilities.GetInstance();
  _database = DatabaseDefinition.GetInstance();

  _databaseUtilities.DestroyDatabase(_database);

  _database.UpgradeDatabaseToSpecificVersion(_originalVersion);

  PopulateDatabaseWithSampleData();

  _database.UpgradeDatabaseToSpecificVersion(_newVersion);
}

[Test]
public void OwnerRoleExistsInRolesTable()
{
  var rows = _databaseUtilities.ExecuteSql(
    _database, "SELECT * FROM [Roles] WHERE [TechnicalName] = 'OWNR'");

  Assert.AreEqual(1, rows.Count());
}

[Test]
public void ReviewerRoleExistsInRolesTable()
{
  var rows = _databaseUtilities.ExecuteSql(
    _database, "SELECT * FROM [Roles] WHERE [TechnicalName] = 'RVWR'");

  Assert.AreEqual(1, rows.Count());
}

private void PopulateDatabaseWithSampleData()
{
  // snipped out to maintain relevance
}

Once those transition tests failed, I changed the database builder script to insert the required rows as shown in Listing 25. Before the changes, the upgrade script for version 1.2 just built an empty Roles table. Now it builds an empty Roles table and then inserts the two required rows into it.

<DatabaseWithVersions>
  <Version Id="1.0">
    <!-- Snipped. -->
  </Version>
  <Version Id="1.1">
    <!-- Snipped. -->
  </Version>
  <Version Id="1.2">
    <Sql>
CREATE TABLE [Roles](
  [Identity] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  [TechnicalName] CHAR(4) NOT NULL)
    </Sql>
    <Sql>
INSERT INTO [Roles]([Identity], [TechnicalName]) VALUES(newid(), 'OWNR')
    </Sql>
    <Sql>
INSERT INTO [Roles]([Identity], [TechnicalName]) VALUES(newid(), 'RVWR')
    </Sql>
  </Version>
</DatabaseWithVersions>

I continued to use this process, carefully skirting around the Works table, until I had built out the rest of the required structure. Eventually, I had Roles, AccessToWorks, and RequestsForAccessToWork tables as well as a WorksSearch view (see Listing 26 for the database build script).

<DatabaseWithVersions>
  <Version Id="1.0">
    <!-- Snipped. -->
  </Version>
  <Version Id="1.1">
    <!-- Snipped. -->
  </Version>
  <Version Id="1.2">
    <Sql>
CREATE TABLE [Roles](
  [Identity] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  [TechnicalName] CHAR(4) NOT NULL)
    </Sql>
    <Sql>
INSERT INTO [Roles]([Identity], [TechnicalName]) VALUES(newid(), 'OWNR')
    </Sql>
    <Sql>
INSERT INTO [Roles]([Identity], [TechnicalName]) VALUES(newid(), 'RVWR')
    </Sql>
    <Sql>
CREATE TABLE [RequestsForAccessToWork](
  [Identity] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  [AccountIdentity] UNIQUEIDENTIFIER NOT NULL
    CONSTRAINT RequestForAccessToWorkMustReferenceAAccount
    FOREIGN KEY REFERENCES [Accounts]([Identity]),
  [WorkIdentity] UNIQUEIDENTIFIER NOT NULL
    CONSTRAINT RequestForAccessToWorkMustReferenceAWork
    FOREIGN KEY REFERENCES [Works]([Identity]),
  [RoleIdentity] UNIQUEIDENTIFIER NOT NULL
    CONSTRAINT RequestForAccessToWorkMustReferenceARole
    FOREIGN KEY REFERENCES [Roles]([Identity]))
    </Sql>
    <Sql>
CREATE TABLE [AccessToWorks](
  [Identity] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  [AccountIdentity] UNIQUEIDENTIFIER NOT NULL
    CONSTRAINT AccessToWorkMustRelateToAnAccount
    FOREIGN KEY REFERENCES [Accounts]([Identity]),
  [WorkIdentity] UNIQUEIDENTIFIER NOT NULL
    CONSTRAINT AccessToWorkMustRelateToAWork
    FOREIGN KEY REFERENCES [Works]([Identity]),
  [RoleIdentity] UNIQUEIDENTIFIER NOT NULL
    CONSTRAINT AccessToWorkMustRelateToARole
    FOREIGN KEY REFERENCES [Roles]([Identity]))
    </Sql>
    <Sql>
ALTER TABLE [AccessToWorks] ADD CONSTRAINT
  [NoMoreThanOneRoleExistsForAnyAccountAndWorkPair]
  UNIQUE([AccountIdentity], [WorkIdentity])
    </Sql>
    <Sql>
CREATE VIEW [WorksSearch] AS
  SELECT
    [Works].[Identity] AS [Identity],
    [Works].[Title] AS [Title],
    [Works].[Body] AS [Body],
    [Works].[MimeType] AS [MimeType],
    [Works].[TimeStamp] AS [TimeStamp],
    [AccessToWorks].[AccountIdentity] AS [AccountIdentity],
    [AccessToWorks].[RoleIdentity] AS [RoleIdentity]
  FROM
    [Works] INNER JOIN [AccessToWorks]
    ON [Works].[Identity] = [AccessToWorks].[WorkIdentity]
    </Sql>
  </Version>
</DatabaseWithVersions>

Changing the Works table happened in three steps:

Step 1 is a purely transitional concern—it relates solely to the shift from version 1.1 to version 1.2—so there were no unit tests driving it. Although there were unit tests for the AccessToWorks table, they didn’t call for this specific transition.

I did write one transition test, however. This test’s job was to prove that all of the relationships between the Work and Account entities were copied into the new AccessToWorks table.

Before we get into exactly how that was done, I want to introduce another test utility: TableSnapshot. TableSnapshot, as its name implies, creates a “snapshot” of a table held in memory. The TableSnapshot class is useful for a number of things, including storing a database table in memory, restoring the state of a table from something stored in memory, and building up sample data for a table. In addition, instances of TableSnapshot can be compared to one another. (A full listing of this class is not provided here, because its implementation is beyond the scope of this short cut.)

Now we can get to the important question: How did I validate that the relationships were copied properly? The act of copying those data is really just a slightly more complex transformation than the identity transformation, which we’ve already seen how to test. I took the following steps, demonstrated in Listing 27:

private DatabaseUtilities _databaseUtilities;

private DatabaseDefinition _database;

private TableSnapshot _accountsSnapshot;
private TableSnapshot _worksSnapshot;

private readonly Version _originalVersion = new Version(1, 1);
private readonly Version _newVersion = new Version(1, 2);

[TestFixtureSetUp]
public void SetUp()
{
  _databaseUtilities = DatabaseUtilities.GetInstance();
  _database = DatabaseDefinition.GetInstance();
  _databaseUtilities.DestroyDatabase(_database);
  _database.UpgradeDatabaseToSpecificVersion(_originalVersion);

  _accountsSnapshot = TableSnapshot.GetInstance("Accounts");
  _worksSnapshot = TableSnapshot.GetInstance("Works");

  PopulateDatabaseWithSampleData();

  _database.UpgradeDatabaseToSpecificVersion(_newVersion);
}

private void PopulateDatabaseWithSampleData()
{
  PopulateTableWithSampleData(_accountsSnapshot, 2);
  PopulateTableWithSampleData(_worksSnapshot, 20);
}

private void PopulateTableWithSampleData(
  TableSnapshot tableToPopulate, int numberOfRows)
{
  var writer = tableToPopulate.GetWriter();
  writer.AddArbitraryRows(numberOfRows);

  tableSnapshot.Save(_database);
}

[Test]
public void WorksTableAccountIdentityIsUsedToBuildListOfOwnershipRoles()
{
  TableSnapshot expectedAccess =
    TableSnapshot.GetInstance("AccessToWorks");
  TableSnapshot actualAccess =
    TableSnapshot.GetInstance("AccessToWorks");

  Guid ownershipRowIdentity = (Guid)_databaseUtilities.ExecuteScalar(
    _database,
    @"SELECT [Identity] FROM [Roles] WHERE [TechnicalName] = 'OWNR'");

  foreach (var workRow in _worksSnapshot.Rows)
  {
    Dictionary<string, object> expectedRow =
      new Dictionary<string, object>();

    expectedRow["AccountIdentity"] = workRow["AccountIdentity"];
    expectedRow["WorkIdentity"] = workRow["Identity"];
    expectedRow["RoleIdentity"] = ownershipRowIdentity;

    expectedAccess.Rows.Add(expectedRow);
  }

  actualAccess.Load(_database);
  actualAccess.DropColumn("Identity");

  expectedAccess.AssertIsEqualTo(actualAccess);
}

There’s a lot of new stuff in Listing 27 so let’s go over it piece by piece.

I needed to build up some sample data in the Accounts and Works tables. I also needed to store an image of what was in those tables for later use. The following code snippet shows how I accomplished that:

 1.  private void PopulateDatabaseWithSampleData()
 2.  {
 3.    PopulateTableWithSampleData(_accountsSnapshot, 2);
 4.    PopulateTableWithSampleData(_worksSnapshot, 20);
 5.  }
 6.
 7.  private void PopulateTableWithSampleData(
 8.    TableSnapshot tableToPopulate, int numberOfRows)
 9.  {
10.   var writer = tableToPopulate.GetWriter();
11.   writer.AddArbitraryRows(numberOfRows);
12.
13.   tableSnapshot.Save(_database);
14. }

A lot of this code is self-explanatory. The only parts that might need a little additional context are lines 10–13. The TableSnapshot class can issue writer objects that can be used to fill the in-memory copy of a table with either specific or randomly selected data.

In this case, I chose to fill the snapshots for each of the two tables with random data. I created 2 random Account rows and 20 random Work rows. TableSnapshot knows how to create random rows that meet the validation criteria (including foreign keys), so the resulting data set is sufficiently representative of what data might look like in the production database.

The pieces discussed so far will populate a v1.1 database with 2 Account and 20 Work records. What about testing whether the data for this relationship are correctly copied? That is the purview of the transition test shown in the following snippet:

 1.  [Test]
 2.  public void  WorksTableAccountIdentityIsUsedToBuildListOfOwnershipRoles()
 3.  {
 4.    // 1
 5.    TableSnapshot expectedAccess =  TableSnapshot.GetInstance("AccessToWorks");
 6.    TableSnapshot actualAccess = TableSnapshot.GetInstance("AccessToWorks");
 7.
 8.    // 2
 9.    Guid ownershipRowIdentity = (Guid)_databaseUtilities.ExecuteScalar(
10.     _database,
11.     @"SELECT [Identity] FROM [Roles] WHERE [TechnicalName] = 'OWNR'");
12.
13.   // 3
14.   foreach (var workRow in _worksSnapshot.Rows)
15.   {
16.     Dictionary<string, object> expectedRow =
17.       new Dictionary<string, object>();
18.
19.     expectedRow["AccountIdentity"] = workRow["AccountIdentity"];
20.     expectedRow["WorkIdentity"] = workRow["Identity"];
21.     expectedRow["RoleIdentity"] = ownershipRowIdentity;
22.
23.     expectedAccess.Rows.Add(expectedRow);
24.   }
25.
26.   // 4
27.   actualAccess.Load(_database);
28.   actualAccess.DropColumn("Identity");
29.
30.   // 5
31.   expectedAccess.AssertIsEqualTo(actualAccess);
32. }

I’ve labeled certain blocks of work to facilitate their description.

The first block of work gets two instances of the TableSnapshot class, both bound to the AccessToWorks table. These two objects will ultimately be compared to each other. As the names imply, expectedAccess is what we expect and actualAccess is what we actually find.

In block 2, the test finds the identity of the Owner role.

In block 3, the test loops through every row in the Works table, building a representation of what the corresponding AccessToWork entity should look like and adding it to the expectedAccess object for each. The test doesn’t build identities because we don’t care which identities are generated. In fact, for the purpose of this test, we don’t even care that identities are generated. We have other tests to handle that issue.

Next, in block 4, the test loads the actual values in the AccessToWork table out of the test database and eliminates the Identity column from the actual values in block 5.^[16]

Finally, the fifth block of work instructs expectedAccess to fail the test if any discrepancies are found between the data and actualAccess, effectively testing whether or not the required copy happened and behaved as expected.

Having that test in place, the next step for me was to see it fail. It did, and exactly as expected: by saying that there should have been 20 AccessToWorks rows with certain values. To make the test pass, I modified the version 1.2 upgrade script as shown in Listing 28.

<DatabaseWithVersions>
  <Version Id="1.0">
    <!-- Snipped: entire v1.0 upgrade. -->
  </Version>
  <Version Id="1.1">
    <!-- Snipped: entire v1.1 upgrade. -->
  </Version>
  <Version Id="1.2">
    <!-- Snipped: building new structures for v1.2. -->
    <Sql>
INSERT INTO [AccessToWorks](
  [Identity],
  [AccountIdentity],
  [WorkIdentity],
  [RoleIdentity])
SELECT
  newid(),
  [Works].[AccountIdentity],
  [Works].[Identity],
  [Roles].[Identity]
FROM
  [Works]
INNER JOIN
  [Roles]
ON
  [Roles].[TechnicalName] = 'OWNR'
      ]]>
    </Sql>
  </Version>
</DatabaseWithVersions>

After I made that change, I reran the transition test. It passed, which indicated that it was safe to start transferring coupling information from the AccountIdentity field to the AccessToWorks table. I couldn’t release the database—or the application for that matter—in midstream, of course, but when is it ever the case that you can release right in the middle of a task?

The process of eradicating coupling to the AccountIdentity field within the AuthorEyes.net application was moderately arduous, to the point of being a story all on its own. You’ve probably already read plenty on refactoring—and if you haven’t, you should. Refactoring code is outside of the scope of this short cut, so just imagine that wavy, shimmery thing they do in the movies or Scooby-Doo cartoons to signify the passage of time; all the coupling to AccountIdentity has been eliminated once everything comes back into focus.

With no coupling to AccountIdentity, the only remaining database task was to get rid of that field altogether. For most of us, this deletion is intuitively the right thing to do. For many of the other readers, this action adheres to a key principle (zero duplication). For those of you who do not see why I might want to go to the extra trouble of eliminating an unnecessary structure, allow me to trot out an argument that Scott Bain made at one of my lectures on the subject: If we leave a vestigial structure in place, we give clients the opportunity to create new coupling to it.

I wrote another test (WorksTableLosesAccountIdentityInTransition). Listing 29 shows what this new test looks like.

[Test]
public void WorksTableLosesAccountIdentityInTransition()
{
  // 1
  TableSnapshot expectedSnapshot =
    TableSnapshot.GetInstance(_worksSnapshot);

  // 2
  TableSnapshot actualSnapshot = TableSnapshot.GetInstance("Works");

  // 3
  actualSnapshot.Load(_database);

  // 4
  expectedSnapshot.DropColumn("AccountIdentity");

  // 5
  expectedSnapshot.AssertIsEqualTo(actualSnapshot);
}

The first line copies the snapshot for the Works table that was captured during the fixture setup. Next, the test gets a new snapshot bound to the Works table. Block 3 loads the state of the Works table into actualSnapshot. Block 4 sets our only expectation—that the AccountIdentity column no longer exists—by eliminating the AccountIdentity column from the expectedSnapshot. Finally, the fifth block of the test ensures that the two table snapshots—expected and actual—contain equivalent rows.

This test failed correctly the first time, showing that the rows actually in the Works table have AccountIdentity fields, whereas the rows that were expected do not. This result once again justified another change to the database builder script, shown in Listing 30.

<DatabaseWithVersions>
  <Version Id="1.0">
    <!-- Snipped: entire v1.0 upgrade. -->
  </Version>
  <Version Id="1.1">
    <!-- Snipped: entire v1.1 upgrade. -->
  </Version>
  <Version Id="1.2">
    <!-- Snipped: building new structures for v1.2. -->
    <Sql>
INSERT INTO [AccessToWorks](
  [Identity],
  [AccountIdentity],
  [WorkIdentity],
  [RoleIdentity])
SELECT
  newid(),
  [Works].[AccountIdentity],
  [Works].[Identity],
  [Roles].[Identity]
FROM
  [Works]
INNER JOIN
  [Roles]
ON
  [Roles].[TechnicalName] = 'OWNR'
    </Sql>
    <Sql>
ALTER TABLE [Works] DROP COLUMN [AccountIdentity]
    </Sql>
  </Version>
</DatabaseWithVersions>

After running that test and seeing it pass, I knew that the relationship between the Accounts table and the Works table had been completely transformed so as to just be another part of the AccessToWorks table. The remaining work in iteration 3 focused on the application logic and is outside of the scope of this document. So, for the purposes of this short cut, iteration 3 was complete.

Well, you’ve stuck with me through the journey, and nowhere along the way did I yield any kind of silver bullet. That’s okay, in my mind, as the title of this short cut is not “ten things you need to know to survive a werewolf attack.”

I hope that it’s okay for you, too.

I suspect it is. I told you there would be no magical solution divulged in this short cut at the very beginning. What I have given you is a regular, old-fashioned nail—something that lets you start to put the pieces together yourself.

I’m a sucker for consistency, so I would regret it for the rest of my life if I didn’t spend at least one paragraph in this section explaining the benefit of following this approach—even though I hope that it is self-evident. In this iteration, we explored the concept of transition testing as it applies to a real project, enabling emergent design in a database. Not only have I built a sturdy platform from which future changes can be made, but I have actually used the process to apply a transformation to living data. A design is starting to emerge in my database.