A test-driven design style for database-based applications with VSTS
If you are designing an application test-drive using Microsoft Visual Studio 2005 Team Edition for Testers whose logic includes access to a database, then consider the following mechanism for the database related assertions (also known as specifications: SpecificationByExample):
For a simple example, if you have a business object (also known as processor, unit of work, façade, etc.) —the kind of business objects suggested by Microsoft for application architecture since Windows DNA— called PatchOperation that receives patch installation status messages over the network from one or thousands of clients then an initial and possible TestMethod for it could be like this:
string PCNetworkID="PCx";
string PatchID="Patch123";
//this data structure is what the invocation receives
//both locally or over the network, sample values follow:
InstallStatusInfo status=new InstallStatusInfo();
status.PatchID=PatchID;
status.Status="Installed";
//PatchOperation is the business object,
//the following two lines appears in
//production code exactly like this:
PatchOperation patchoperation=new PatchOperation();
patchoperation.ReceiveInstallStatus(PCNetworkID,status);
//Asserting post-conditions:
DataTable result = Util.SqlQuery("SELECT ... WHERE pc=@PCNetworkID ...");
Assert.AreEqual(1,result.Rows.Count);
Assert.AreEqual("Installed",result.PatchStatus);
// etc.
In order to fully grasp the relative value of the code above, there are a number of assumptions about the perspective from which the observer could be seeing it; I will defer those clarifications for a later post by now check this preamble, by now let's cover the overall database test mechanism.
Chances are that the unit test assumes a particular database state before executing, like particular rows on particular tables, but the same state must be deleted before the next unit test starts. In that case, lets think in terms of a base class for all database-related unit tests:
///
/// Base clase for database-based application design.
/// Intented to be derived.
/// Derived test classes must override InsertTestData method.
/// Derived test classes may override GetIsolationLevel method.
/// Derived test classes must use TransactionalContext in TestContext property.
/// TransactionalContext is under "tx" key in TestContext property.
///
[TestClass]
public abstract class DatabaseTestClass
{
[TestInitialize]
public virtual void Setup()
{
IsolationLevel isolation = GetIsolationLevel();
TransactionalContext tx = TransactionalContext.Create(isolation);
TestContext.Properties["tx"] = tx;
InsertTestData();
}
protected abstract void InsertTestData();
protected virtual IsolationLevel GetIsolationLevel() { return IsolationLevel.Serializable; }
[TestCleanup]
public virtual void Clean()
{
TransactionalContext tx = TestContext.Properties["tx"] as TransactionalContext;
tx.Transaction.Rollback();
tx.Connection.Close();
}
private TestContext testContextInstance;
public TestContext TestContext
{
get { return testContextInstance; }
set { testContextInstance = value; }
}
}
The TransactionalContext class usually is part of a data access assembly or namespace whose types are mainly data access components:
public enum TransactionalContextMode
{
Read,
Write
}
public class TransactionalContext
{
public SqlConnection Connection;
public SqlTransaction Transaction;
public static TransactionalContext Create()
{
return Create(TransactionalContextMode.Write);
}
public static TransactionalContext Create(IsolationLevel isolation)
{
TransactionalContext result = new TransactionalContext();
string dbconn = System.Configuration.ConfigurationManager.AppSettings["conn"];
result.Connection = new SqlConnection(dbconn);
result.Connection.Open();
result.Transaction = result.Connection.BeginTransaction(isolation);
return result;
}
public static TransactionalContext Create(TransactionalContextMode mode)
{
switch (mode)
{
case TransactionalContextMode.Read:
return Create(IsolationLevel.RepeatableRead);
case TransactionalContextMode.Write:
return Create(IsolationLevel.Serializable);
default:
throw new Exception("TransactionalContextMode undefined");
}
}
}
So the initial unit test above could be written like this:
[TestClass]
public class StatusReception : DatabaseTestClass
{
protected override void InsertTestData()
{
Util.SqlExecute(this, "EXECUTE Computer_Insert 'PCx'");
Util.SqlExecute(this, "EXECUTE PCStatus_SetStatus 'PCx',NULL ");
}
[TestMethod]
public void StatusReception_flat()
{
TransactionalContext tx = TestContext.Properties["tx"] as TransactionalContext;
string PCNetworkID="PCx";
string PatchID="Patch123";
InstallStatusInfo status=new InstallStatusInfo();
status.PatchID=PatchID;
status.Status="Installed";
PatchOperation patchoperation=new PatchOperation();
patchoperation.ReceiveInstallStatus(tx,PCNetworkID,status);
DataTable result = Util.SqlQuery("SELECT ... WHERE pc=@PCNetworkID ...");
Assert.AreEqual(1,result.Rows.Count);
Assert.AreEqual("Installed",result.PatchStatus);
// etc.
}
}
At the beginning of each TestMethod, the SetUp method from the base class is invoked and the Transactional context is created and stored as part of the TestContext object, from which the TestMethod can get it in order to propagate that very same Transactional context to the business objects under test.
Later, the Clean method from base class ins invoked and the transactions is rolled back, leaving the database in the same state as of the beginning of the test case.
Comments
Anonymous
July 13, 2006
Thomas Murphy on To Table Design or Not To Table Design.
Rob Caron on stpBA StoryBoarding for Team...Anonymous
February 08, 2008
This is very similar to the methodology we are trying to promote in that it focuses on proving that databases are changed reliably. You might want to abandon the word "patch" as that has connotations of fixing bugs and, with TDD, we want to write bug-proof code from the get-go. There's also a little more that can be done to ensure that transitions are reliable. I'd love to talk. My email is max [at] hexsw [dot] com. Thanks, Max Guernsey, IIIAnonymous
February 09, 2008
Thanks for your comment Max. The code use the word ‘patch’ because at that time I was designing an application dealing with updates installations and services packs to a number of PCs’ operating software. Feel free to keep the talk on this page. Regards.Anonymous
March 07, 2008
Sure. I totally missed the point of the term "patch," and it makes sense in this context. I agree that getting the database into a known state before unit and acceptance tests run is of great importance. There are really two kinds of change that can occur in a database and using a transaction covers one of them. The two kinds of changes that occur in databases are informative changes (adding, removing, and modifying data) and transformative (changing the shape of the database) changes. Rolling back a transaction ensures that informative changes from one test do not effect any others. In an agile environment, we are going to need to roll out numerous transformative changes, too. The transformations need to be heavily tested and fully automated. If transformations are not fully automated and completely tested, you have no guarantee that your test and production databases are similar enough to each other to make your unit and acceptance tests meaningful. I recommend burning your database to the ground and rebuilding it using such a tested mechanism at the start of every test. It usually doesn't take too long and, when it does, you can start to move the structural rebuilds further out (like to fixture setups and, eventually, assembly setups). This has two benefits. Firstly, confidence gained by running unit and acceptance tests against a test database transfers over to production databases. Second, it "dog-foods" your database build process, providing additional validation for it. -- Max