Build IntegrationAs 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 ExecutionIntegrating 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.
The code generator requires the following prerequisites:
- T4 toolbox visual studio extension which can be downloaded from the following links:
- Visual Studio 2012: http://visualstudiogallery.msdn.microsoft.com/7f9bd62f-2505-4aa4-9378-ee7830371684.
- Visual Studio 2013: http://visualstudiogallery.msdn.microsoft.com/791817a4-eb9a-4000-9c85-972cc60fd5aa.
- 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.
To create the empty MS-Test, to copy the output of the code generator into, use the following steps:
- Add a new C# MS-Test project to the solution
- 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.
- Delete the newly added SQL server unit test file.
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.
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.