26 Aug 2014

Integrating tsqlt with Team Foundation Build Services continuous build

As I've mentioned in a previous blog post I have been integrating a SQL Server unit test framework: tsqlt into my project life-cycle (click here to read more).  Within this post I will share how I got my tsqlt unit tests executing within the continuous integration solution and the results influencing the outcome of the build. The continuous integration platform I am using is Team Foundation Build Services 2012 (TFBS). However my approach will work with any continuous integration platform which handles MS-Build and MS-Test, like Team City.

Build Integration

As I had created my database unit tests within SQL Server Data Tools (SSDTs) integrating them into the continuous integration was a straightforward process.  By extending the build definition to include the unit test project into the list of solution / projects which have to be built.  As I had my own solution, which was listed within the build definition, for all the database projects and other related projects my unit tests were already included.

I also added a very useful MS-Build argument to the build definition: /p:GenerateProjectSpecificOutputFolder=true which puts the outputs of the projects in their own separate folders. This was another way of separating the unit test framework and the unit test from the application database.

Unit Test Execution

Integrating the execution of the database unit tests could be achieved by writing a new unit test adapter to execute the tsqlt unit tests and report the results back to TFBS. However I decided to use an easier approach by making use of the SSDTs extension within MS-Test.  I use MS-Test database unit test to execute the tsqlt unit test and to check the execution result. I have reasons why I don't use MS-Tests to unit test my application database. These can be found in a previous blog post (click here to find out more).

I structured my MS-Test project to mirror the approach taken within tsqlt.  By having a MS-Test class for each tsqlt test schema. Then within each test class have one MS-Test database unit test per tsqlt unit test, using the same names minus spaces and the beginning “test”. This will make it easier seeing the results of the tsqlt unit test within result viewer.

Within the MS-Test database unit test the following SQL pattern was used:

   1:  --Execute the unit tests
   2:  EXECUTE [tSQLt].[Run] @TestName = N'[MyUnitTestClass].[test My Unit Test for My database Application]';
   3:  --Then check the results using the following SQL
   4:  SELECT [tr].[Result]
   5:  , [tr].[Msg]
   6:  FROM [tSQLt].[TestResult] AS tr
   7:  WHERE [tr].[Class] = 'MyUnitTestClass'
   8:  AND [tr].[TestCase] = 'test My Unit Test for My database Application';

Then use the single value assert method to check row 1 column 1 contained the following value: Success.

I have been able to write a T4 code generator which connects to a SQL Server database with tsqlt unit tests and does the following:
  • Create a class file for each of the tsqlt test schema
  • Generate the database unit tests for each tsqlt test found within the schema.
You can download my code generator on the following link: https://onedrive.live.com/redir?resid=E6F77D4B3FF8D47C!385&authkey=!AH-1sMQPJIs_B0c&ithint=file%2c.zip.

The code generator requires the following prerequisites:
To use the code generator update the tsqltUnitTest.tt file with the following:
  • SQL Server: With the name of the SQL Server which contains the unit test database.
  • Database name: The name of the database containing the unit tests.
  • Test name space: With the name of your MS-Test project that output files will be copied to.
Then right mouse click on the tsqltUnitTest.tt and select the option of running custom tool. After the execution of the T4 template there will be a directory, with the same name as your name space setting, containing all the files which need to be copied to your MS-Test project.

To create the empty MS-Test, to copy the output of the code generator into, use the following steps:
  1. Add a new C# MS-Test project to the solution

  2. Add a SQL Server unit tests file. This will add all the necessary references and code to convert the MS-Test project into a SQL server test project.

  3. Delete the newly added SQL server unit test file.
Adding MS-Test Project to solution

To get the unit tests to execute successfully I had to alter the standard build process template. The alteration was to insert an activity, after build and before the MS-Test activities, to run a power shell script. The script would do a deployment of my application database. Then a deployment of the unit test project, which would include the framework, to the localdb.

Standard Build Process Template With Powershll Task

The reason for the extra step was to work around the problem of SQL package only executing Pre/Post deployment files of the dacpack of the referenced source parameter. I needed my application database post deployment script to be executed as this sets up all my reference data which my unit test require.

To integrate the unit tests execution I only had to update my build definition to start executing test based on DLL file pattern match which was: *test*.  And change the setting "failed the build if tests fail" to true.

Finally another useful advantage of using MS-Test is that database developers can deploy and test their code to localdb all within Visual Studio.  So now they can follow the same rules as their .Net counterparts by ensuring that no code is check-in before it passes its unit tests on their local machines.

further reading: