Phil Hendry's Blog

In the first part of this post I discussed my thoughts on how I wanted to test stored procedures. The next step for me was to find a framework to run all this in! This post presents what I’ve found and written.

It was a pretty quick search in Google that brought my attention to the TSqlUnit project on sourceforge. There doesn’t seem much activity on this at the moment but it’s fairly self-contained with full source code available for customisation if necessary. I particularly like this framework because of its simplicity and the fact that it entirely SQL-based. Currently I like to write and test SQL in Microsoft Sql Server Management Studio – it might not have intellisense (unless you install the RedGate utility) but it’s a fast environment to work within and run SQL directly. TSqlUnit allows me to run one sproc to kick off one or more of my tests and maintains the xUnit style.

This is all well and good but I want to be able to integrate those tests with the automated build - so my plan is to call TSqlUnit from Visual Studio-based unit tests which can be triggered by the build. Because I’ve only got Visual Studio 2008 Standard Edition I couldn’t develop this with the VS test framework so I’ve used NUnit instead with Using statements that allow me to swap between the two xUnit frameworks later.

My plan is simple – during compilation of the Unit Test assembly in my project, generate code within it that calls each of the TSqlUnit test sprocs in the database. I could have written just one test method that called the TSqlUnit sproc to run all tests but this isn’t going to look very good when I run the tests in Visual Studio and one fails – I’m not going to be told which tests fails in the normal manner but rather the ‘run all tests’ failed.

Instead I’ve chosen to use the Text Templating built into VS 2008 to generate the code during the compilation. The text template has a .tt file extension and in VS uses a custom tool called TextTemplatingFileGenerator to take the contents of the template and compile it into the target language (C# in my case) placing the source into a code-behind file. The custom tool is triggered when I save the template and the resulting code gets compiled into the assembly during the normal build – it’s at this point where NUnit, which I run in the background already pointing at the assembly, sees the new assembly and automatically loads it and runs all the tests. Here’s a look at how my project appears in Visual Studios solution explorer :

This highlights the text template (SqlUnitTests.tt) and the code-behind which will be generated containing one Microsoft/NUnit test for each test sproc.

I’ve provided the text template code I was using below (sorry, it’s not syntax highlighted because I haven’t found the Windows Live Writer plugin yet ;) ) Currently it has the SQL Server instance and database names embedded in the code so it still needs some work to extract those to a config file. However, this represents very little code that can automatically generate a huge number of test cases with little or no further interaction.

The only other development I had to do was a change to TSqlUnit so that the tsu_runTests sproc could accept an optional test name and returns the error message if the test fails.

This is all very much work-in-progress (time allowing as ever) and I’m currently waiting to dip my toes into the unit testing features of Visual Studio Team Edition for Database Developers now that it’s more accessible and compare its features with my requirements before taking my solution any further. However, I liked the use of the text templates and the usefulness of TSqlUnit that I thought it worthwhile posting now.

<#@ template language="C#" debug="True" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#
   // Use the following to kick off debugger
   //System.Diagnostics.Debugger.Break();
#>

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

using NUnit.Framework;

namespace SqlUnitTestFramework
{
   <#
      Server server = new Server(@"(local)\SQLEXPRESS");
      Database database = new Database(server, "Northwind");
      database.StoredProcedures.Refresh();

      Dictionary<String, List<String>> tests = new Dictionary<string, List<string>>();
      String CategoryName;
      String TestName;

      foreach (StoredProcedure sproc in database.StoredProcedures)
      {
         if (sproc.Name.StartsWith("ut_"))
         {
            Match match = Regex.Match(sproc.Name, "ut_([^_]*)_?(.*)");
            if (match.Groups[2].Value == "")
            {
               CategoryName = "NoCategory";
               TestName = match.Groups[1].Value;
            }
            else
            {
               CategoryName = match.Groups[1].Value;
               TestName = match.Groups[2].Value;
            }
            if (String.Compare(TestName, "setup", true)!=0
                  && String.Compare(TestName, "teardown", true)!=0)
            {
               if (!tests.ContainsKey(CategoryName))
               {
                  tests.Add(CategoryName, new List<String>());
               }
               tests[CategoryName].Add(TestName);
            }
         }
      }
      foreach (string category in tests.Keys)
      {
         PushIndent("\t");
         WriteLine("[TestFixture]");
         WriteLine("public class {0}Tests", category);
         WriteLine("{");
         foreach (string test in tests[category])
         {
            PushIndent("\t");
            WriteLine("[Test]");
            WriteLine("public void {0}()", test);
            WriteLine("{");
               PushIndent("\t");
               WriteLine("Utility.ExecuteNamedTest(\"{0}\");", test);
               PopIndent();
            WriteLine("}");
            PopIndent();
         }
         WriteLine("}");
         PopIndent();
      }
   #>     
   public class Utility
   {
      public static void ExecuteNamedTest(string testName)
      {
         using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString))
         {
            using (SqlCommand cmd = new SqlCommand())
            {
               cmd.Connection = conn;
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.CommandText = "tsu_runTests";

               cmd.Parameters.Add("suite", SqlDbType.NVarChar, 255);
               cmd.Parameters["suite"].Direction = ParameterDirection.InputOutput;

               cmd.Parameters.Add("testName", SqlDbType.NVarChar, 255);
               cmd.Parameters["testName"].Direction = ParameterDirection.InputOutput;
               cmd.Parameters["testName"].Value = testName;

               cmd.Parameters.Add("testError", SqlDbType.NVarChar, 255);
               cmd.Parameters["testError"].Direction = ParameterDirection.Output;

               cmd.Parameters.Add("success", SqlDbType.Bit);
               cmd.Parameters["success"].Direction = ParameterDirection.Output;

               cmd.Parameters.Add("testCount", SqlDbType.Int);
               cmd.Parameters["testCount"].Direction = ParameterDirection.Output;

               cmd.Parameters.Add("failureCount", SqlDbType.Int);
               cmd.Parameters["failureCount"].Direction = ParameterDirection.Output;

               cmd.Parameters.Add("errorCount", SqlDbType.Int);
               cmd.Parameters["errorCount"].Direction = ParameterDirection.Output;

               conn.Open();
               cmd.ExecuteScalar();
               if ((int)cmd.Parameters["failureCount"].Value > 0 || (int)cmd.Parameters["errorCount"].Value > 0)
               {
                  Assert.Fail((string)cmd.Parameters["testError"].Value);
               }
            }
         }
      }
   }
}

Whilst I think unit testing a simple Data Access Layer may not offer many advantages (and isn’t the subject of this post) I do see the value in unit testing T-SQL and Stored Procedures that include some level of business logic.

If you’re using Unit Tests then you should have a Build Server running either Continuous Integration or at least some sort of regular build that triggers all the Unit Tests to run. Creating the database schema on the build server (or other suitable server) during the test run is unavoidable since mocking out the SQL Engine is probably beyond most people :). However, once the schema is created each test will require a set of data appropriate for that test. There are a number of fairly easy options to getting the data inserted into the database but the problem arises trying to maintain one huge set of data that resides to one side from the unit tests that use it. Here are some issues :

• For a simple test that involves only testing an insert into one table there may exist many other related tables involved in foreign key relationships that are required to exist.
• The tests may be very sensitive to schema changes which require tedious code updates for no real benefit.
• One set of data for one particular test may not be able to exist alongside another set of data for a different test.
• Unit tests should be as self-contained as possible and have as few dependencies on other code as possible. Having shared test data  contained in an external location to the tests creates a huge inter-dependency issue between tests. Tweak data for one test and you may break another test.

A couple of these issues could be solved by using the xUnit setup and teardown to insert data appropriate for the tests but there still has to be a huge amount of data added due to the first issue above and we may still end up breaking tests after relatively independent schema or data changes.

Consider the ‘Unit’

A better solution is to consider what we’re trying to achieve with the tests in the first place.  The xUnit mantra encourages us to take the smallest unit of code, isolate it, then test with specific scenarios. This is exactly what should be attempted with SQL but what is the smallest unit?

In most cases we’re only really interested in a sub-set of the columns on any particular table(s) and certainly only on tables that are involved in the query under test. However, there are going to be numerous foreign key relationships and non-nullable column constraints that increase the size of the data required to test a query. This can all be reduced by allowing null fields (including for foreign key columns) and not inserting any data into those columns during the test. The test can now insert data into only the columns that apply to the current test – it’s important to make sure that the insert SQL statements only specify the columns that matter for the test since any presumption on column order or quantity will be fragile.

So the pattern here requires constraints to be dropped and data to be inserted for each test or perhaps during the xUnit setup. Either way, there should be a lot less data to maintain and it exists alongside the tests it’s related to.

To illustrate this idea consider the typical order management system where Customers create an Order which has many Order Lines. There will exist a foreign key relationship from Order Line to Order to Customer but if we’re unit testing the creation of Order Lines and shopping basket type interaction of adding and removing items then we may not be interested at all in testing anything to do with the Customer records. Therefore, before running the tests, we make the Customer foreign key on Order nullable before inserting data to test each case.

Hopefully that provoked some thoughts and you might like to add comments to expand on what I’ve said with your own opinions. In a second part to this blog I’m going to look at how I’ve chosen to combine NUnit, TSqlUnit and some Visual Studio Text Templates to create an integrated test environment in VS.

I really should do this more often… adding external tools to the Tools menu in Visual Studio is such an easy thing to do and can save precious seconds(!)

Here’s the configuration I use to launch NUnit with the current assembly automatically loaded :

I’ve also used the Tools/Customize/Keyboard… option to assign a shortcut key to it. For me NUnit showed up in the list as ‘Tools.External Command 8’ :

Someone has already done the hard work to write an MSBuild task that allows you to call PowerShell scripts a bit like this :

<UsingTask 
      AssemblyFile="PowershellMSBuildTask.dll"
      TaskName="PowerShell" />

<PropertyGroup>
   <ReplaceScript>
      <![CDATA[get-content "..." | % { $_.Replace("something", "something else") } | set-content "..."]]>
   </ReplaceScript>
</PropertyGroup>

<Target Name="AfterGet">
   <Powershell Script="$(ReplaceScript)/>"
</Target>

It was only last week that I was moaning about how expensive it was to do database unit testing with Microsoft tools since Microsoft Studio Team System comes in two flavours of Development Edition and Database Edition – both expensive but with little difference between them IMHO.

Looks like Microsoft may have realised that themselves :

They’ve also published as FAQ.

I thought it was about time that I looked again at some basic information that I’ve known for a while but not necessarily in the level of detailed presented here. Seeing as I don’t have the memory powers of an elephant, I found this exercise quite useful so I hope you find it useful too…

Database Read Inconsistencies

When multiple threads/clients are reading data from a database such as SQL Server, there are a number of inconsistent states data can be in when the same data rows are being read, inserted or updated. These inconsistencies are :

• Dirty read
• Non-repeatable read
• Phantom read

There are a number of transaction isolation levels that are available to avoid one or more of these inconsistencies, but depending on your application, higher isolation levels may be unnecessary.

Dirty Read

The following example shows two transactions with time running from left to right. The top transaction reads some data, updates it but then rolls the change back. The lower transaction is allowed to perform a dirty read after the update was made but is not aware that the data is rolled back

Non-repeatable Read

This next example shows how the lower transaction cannot repeat its original read because it’s been allowed to read the update made by the upper transaction.

Phantom Read

The phantom read is very similar to the non-repeatable read but instead of changing the same row another insert or delete affects the selection criteria of another query. In this example the upper transaction counts the number of occurrences of ‘Phil’ and initially counts 1 but, after the lower transaction inserts and commits a new ‘Phil’, it counts the ‘phantom’  record.

Transaction Isolation Levels

In order to control the effects described above, databases provide a number of different Transaction Isolation Levels. Microsoft SQL Server describes these isolation levels in their documentation and describes a table that lists the isolation levels and the effects they counteract. With the descriptions of the effects above this table should make a lot of sense. The isolation levels become more restrictive toward the bottom of the table and a ‘Yes’ in a cell means that the isolation level allows a particular data inconsistency :

I had a standard class library project in a Visual Studio solution that I’d been happily adding NUnit tests to without a problem. I then used a trick to convert my unit tests between NUnit and Visual Studio tests because ultimately I wanted to run NUnit during my development phase for speed but the VS tests during the TFS Build phase for continuous integration test reports.

However, for some reason my tests wouldn’t appear in the VS Test View. After a bit of digging and experimentation I discovered that I had to edit the .csproj and add a ProjectTypeGuids element (see below) that would tell VS studio to treat the project as a test project.

<ProjectTypeGuids>{3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>

The full list of project type guids can be found in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\Projects although on a day-to-day basis I wouldn’t expect this list to be that useful!!

It appears that the chaps at Microsoft have been hard at work an conjured up two documents detailing guidance and best practices for TFS. The first, TFS Branching Guidance, focuses on just branching and therefore the downloaded pdf is only 37 pages long. The second entitled Team Development With Visual Studio Team Foundation Server (aka TFS Guide) details far more aspects of TFS and to that end the pdf is 496 pages long! There’s certainly a lot to digest but it’s very well structured and because it tries to cater for different scenarios, you quickly narrow down what you actually need to just a few pages if there’s only one aspect of TFS you’re trying to find guidance for.

I’ve not read either in detail yet so if you’ve got any opinions on them please send comments!!

I wanted to fill a column in a table with random numbers so off the top of my head wrote the following sql :

update code set serialNumber = floor(rand() * 30000000000)

Unfortunately this set the serialNumber to exactly the same value since RAND() isn’t being re-executed for each row. I was in a bit of a hurry so instead found the following on google :

update code set serialNumber = abs(cast(cast(newid() as binary(4)) as int)) + 30000000000

Newid() is executed for each row and in this example it’s being used to created (aka ‘bodge’) an int. An interesting solution and did the trick for me.

Here's a quick tip for launching applications with a simple keypress rather than having to hunt it down in the program files menus (although I really like the search facility in the Vista start menu!)

First up, I created a Keyboard Shortcuts folder in my program files folder and added shortcuts for each of the applications I want to launch from a keypress. I've also included the shortcut key I've assigned in the name for simple reference. It's important to be able to track down which keys are assigned to which apps because you can re-assign the key without realising you're overwriting a previous one. Here's my shortcuts in the start menu...

Then it's just a simple matter of assigning the keypress in the properties for each shortcut...

The compatibility tab also lets you run programs automatically as admin if that's required.

The only issues I've noticed when I've done this is I've re-mapped a keypress that already exists in another application. This usually isn't a problem if I choose a shortcut of CTRL + SHIFT + ALT (which is only two key presses on a keyboard - ALT GR and SHIFT) since that's a pretty unusual shortcut for an application. But it just so happened at one point that Visual Studio on my machine had assigned a keypress of CTRL + SHIFT + ALT + E which meant that the one I had defined took priority!