Documentation - Use Case - Transition testing

Use Case: Transition testing

Abstract

For database development to be truly agile, deployment to production must have absolutely no manual intervention. This means that you have to have complete confidence that it will either work or have no effect at all. You can cover some ground by making your transitions defend against broken database changes. The rest is handled with transition testing, which you use to verify that a database moves from one version to another as expected.

Body

There is no way to introduce frequent, rapid, pain-free change when your change process involves manual intervention. If the process of changing a production database instance's schema has any manual steps (other than pushing the "do it" button), all hope of agility is lost. You can go part of the way to eliminating manual interaction from the database upgrade process by making transitions as transactional as possible. You can go further by making them watch for errors and implementing custom rollback steps for anything that cannot be wrapped up in a transaction.

However, none of that actually allows for fully unattended upgrades of database designs. That is a role filled by transition testing. Transition testing is a form of test driven development in which you specify the behavior of a transition from one version of a database class to another. It works pretty much like any other kind of TDD activity: every test must have at least one set up, trigger, and assertion.

During the process of upgrading a database is the wrong time to be verifying (or worse: writing) parts of an upgrade script. What transition testing gives you is a proper time to do all of the database change verification/validation steps that a DBA would ordinarily do during the process of upgrading a production database from one version to another.

Transition testing does not replace unit or acceptance testing - it's job is not to prove that a database class's design is good nor is it to verify that a database meets a customer's needs. A transition test only confirms that a transition script properly transforms a database of one version into a database of another version, whatever that other version is.

So, assuming we've already written failing acceptance and unit tests that cover a change to a database, let's start building our transition logic. Let's look at the current state of our database class:

You can click on keywords and concepts in blue.

database TheDatabase
{
  types str ("nvarchar") as string, int as integer;
  stereotypes type_definitions;
}

TheDatabase.dbc

You can click on keywords and concepts in blue.

database TheDatabase
{
  current version 1.0 : initialized
  {
    design
    {
      public type_definitions Types with
        public meat_name = type(str(50)),
        public topping_name = type(str(20) null);
      
      // we don't want clients to see the Hamburgers table
      protected table Hamburgers
      {
        // but we do want anything that can see the table to see its columns
        public column Identity ("ID") with DataType = type(int);
        public column Patty with DataType = Types.meat_name;
        public column Lettuce with DataType = Types.topping_name;
        public column Cheese with DataType = Types.topping_name;
        public column Condiment1 with DataType = Types.topping_name;
        public column Condiment2 with DataType = Types.topping_name;
      }
      
      public procedure AddHamburger ("Hamburgers_add")
      {
        public parameter patty ("@patty") : Hamburgers.Patty;
        public parameter lettuce ("@lettuce") : Hamburgers.Lettuce;
        public parameter cheese ("@cheese") : Hamburgers.Cheese;
        public parameter condiment1 ("@condiment1") : Hamburgers.Condiment1;
        public parameter condiment2 ("@condiment2") : Hamburgers.Condiment2;
        
        public parameter identity ("@identity") : Hamburgers.Identity with IsOutput = true;
      }
      
      public procedure GetHamburger ("Hamburgers_get")
      {
        public parameter identity ("@identity") : Hamburgers.Identity;
        public result RetrievedHamburger : Hamburgers with IsPlural = false;
      }
    }
    
    construction
    {
      in context Hamburgers
      {
        step sql
        {
CREATE TABLE $[Hamburgers](
  $[Identity.Declaration] PRIMARY KEY IDENTITY(1, 1),
  $[Patty.Declaration],
  $[Lettuce.Declaration],
  $[Cheese.Declaration],
  $[Condiment1.Declaration]
  $[Condiment2.Declaration]);
        }
    
      in context AddHamburger
      {
        step sql
        {
CREATE PROCEDURE $[AddHamburger]
  $[patty.Declaration], $[lettuce.Declaration], $[cheese.Declaration], $[condiment1.Declaration],   ↲
↳   $[condiment2.Declaration], $[identity.Declaration]
AS
  INSERT INTO $[Hamburgers]($[Hamburgers.Patty], $[Hamburgers.Lettuce], $[Hamgurgers.Cheese],       ↲
↳   $[Hamburgers.Condiment1], $[Hamburgers.Condiment2])
    VALUES($[patty], $[lettuce], $[cheese], $[condiment1], $[condiment2]);
    
  SET $[identity] = @@ROW_IDENTITY();
        }
      }
    
      in context GetHamburger
      {
        step sql
        {
CREATE PROCEDURE $[GetHamburger]
  $[identity.Declaration]
AS
  SELECT * FROM $[Hamburgers] WHERE $[Hamburgers.Identity] = $[identity];
        }
      }
    }
  }
}

TheDatabase-1_0.dbc

As you can see, this database already has two methods (stored procedures): Create a hamburger and get a hamburger by identity. This database design also keeps the details of the Hamburgers table private.

In version 1.1 of TheDatabase, we want to change the structure of the Hamburgers table so that all of the toppings are stored in a Toppings table and the Hamburgers table references the Toppings table. We're doing this for performance and quality reasons and there are no corresponding client changes so we don't want to change the interface to TheDatabase or its behavior, we just want to change how it works underneath the hood.

For the same reason, there is no need for a new suite of unit tests or acceptance tests so we can jump straight into writing the transition tests. We have a lot of options as to how this can be done. One option would be to test exactly how a table's content is transformed. That's a valid thing to do but we are going to focus on preserving information a client can retrieve. To do that, we put some data into a database using version 1.0 methods, transition it to version 1.1, then get the information back out of the database and make sure it survived the transformation.

// hooking in to your favorite test framework is an exercise for the reader
private static void EnsureHamburgerEntitySurvivesTransition(IDbConnection connection)
   sourceDatabase,
  TheDatabase.Design._1_1.Proxy targetDatabase)
{
  // setup: populate the database
  var patty = "All beef, 1/3 lbs";
  var lettuce = "Iceberg";
  var cheese = "Bleu";
  var condiment1 = "Barbecue sauce";
  var condiment2 = null;

  var identity = TheDatabase.Design._1_0.Proxy.GetInstance(connection)
    .AddHamburger(patty, lettuce, cheese, condiment1, condiment2).Identity;
  
  // trigger: upgrade the database to v1.1
  TheDatabase.GetInstance().BuildToVersion(connection, TheDatabase.Design_1_1.GetVersionNumber());
  
  // assertion: check the content of the transformed database
  var hamburger = TheDatabase.Design._1_1.Proxy.GetInstance(connection).GetHamburger(identity);
  
  Assert.That(hamburger.Patty, Is.EqualTo(patty));
  Assert.That(hamburger.Lettuce, Is.EqualTo(lettuce));
  Assert.That(hamburger.Cheese, Is.EqualTo(cheese));
  Assert.That(hamburger.Condiment1, Is.EqualTo(condiment1));
  Assert.That(hamburger.Condiment2, Is.EqualTo(condiment2));
}

TheDatbase-1_1-TransitionTest.cs

As is always preferable with any flavor of test-driven development: that will fail to compile. The resulting compiler errors mandate the existence of a new version (1.1). So, to get the test to compile, we'll create a new version of TheDatabase.

You can click on keywords and concepts in blue.

database TheDatabase
{
  version 1.1 : 1.0
  {
  }
}

TheDatabase-1_1.dbc

Adding that file gets our test to compile and, because we have changed nothing, it also passes. A lot of people like to see their tests fail once before relying on them too heavily, so we will goad the test into failure by making the constructor logic for version 1.1 do the wrong thing.

You can click on keywords and concepts in blue.

database TheDatabase
{
  version 1.1 : 1.0
  {
    construction
    {
      step sql { DELETE $[Hamburgers]; }
    }
  }
}

TheDatabase-1_1.dbc

Now upgrading to version 1.1 will delete whatever 1.0 content was in a database. Since the transition test always adds content to a version 1.0 database, upgrades to version 1.1, and then tests the content of the upgraded database, it will fail. We want our tests to be green before we engage in any refactoring task so the next step is to back out the breaking change.

You can click on keywords and concepts in blue.

database TheDatabase
{
  version 1.1 : 1.0
  {
  }
}

TheDatabase-1_1.dbc

Having a green transition test lets us know it is safe to start refactoring the database. We can move in small increments, continually re-running the transition test to make sure we haven't broken anything. If we do that, as soon as the transition test fails, we will know what we did to break it and can back out that change.

Related Concepts

Related Keywords

Other Actions

documentation | all examples | use cases | concepts | keywords

DataClass

Use Case: Transition testing

Abstract

Body

Related Concepts

Related Keywords

Other Actions