Unit testing is something that should be done often and although Red Gate’s SQL Test product (which is a wrapper around the open source tSQLt database unit testing framework) makes unit testing in databases easier it is still a manual task and therefore easy to forget about. Integrating the running of the tests with a continuous integration system is, in my opinion, a major step forward in terms of fully testing an application.
There is a post on Integrating tSQLt with Cruise Control on the tSQLt site but nothing on integrating CruiseControl.NET. Likewise there is a great post Using SQL Test Database Unit Testing with TeamCity Continuous Integration, which is highly recommended and helped me solve the same problem for CCNet, but again it didn’t answer my problem – integrating with CruiseControl.NET. As I am doing mostly the same thing I will skim over some of the groundwork.
In my build configuration I have a number of steps that drop my existing database and then recreate it so each run always uses the latest version. The tests are then run and the results merged to the CCNet build log and then custom Build Reports are included. I spent a few days Googling for xsl files that work with the xml created and transform it for appropriate display in the results but didn’t find anything – so I’m including the versions (links at the end) I came up with in the hope that it saves someone some trouble in future.
This extract from the MyDatabase test project shows these steps, in practice the SQL statements should be stored externally as .sql files and then simply invoked – but for the purposes of showing the code I’ve inlined them here.
<tasks>
<exec executable="$(sqlCmdPath)">
<description>Turn on CLR on this server.</description>
<buildArgs>
-Q
"EXEC sp_configure 'show advanced options',1 ; RECONFIGURE; EXEC sp_configure 'clr enabled',1; RECONFIGURE;"
</buildArgs>
<baseDirectory>\Artifacts\MyDatabase-Database</baseDirectory>
<buildTimeoutSeconds>90</buildTimeoutSeconds>
<successExitCodes>0</successExitCodes>
</exec>
<exec executable="$(sqlCmdPath)">
<description>Close Local MyDatabase Database</description>
<buildArgs>
-Q
"ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE MyDatabase"
</buildArgs>
<baseDirectory>\Artifacts\MyDatabase-Database</baseDirectory>
<buildTimeoutSeconds>90</buildTimeoutSeconds>
<successExitCodes>0</successExitCodes>
</exec>
<exec executable="$(sqlCmdPath)">
<description>Re-Create Local MyDatabase Database</description>
<buildArgs>
-b
-E
-Q
"CREATE DATABASE MyDatabase"
</buildArgs>
<baseDirectory>\Artifacts\MyDatabase-Database</baseDirectory>
<buildTimeoutSeconds>90</buildTimeoutSeconds>
<successExitCodes>0</successExitCodes>
</exec>
<exec executable="$(sqlCmdPath)">
<description>Set database as trusted</description>
<buildArgs>
-b
-E
-Q
"ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;"
</buildArgs>
<baseDirectory>\Artifacts\MyDatabase-Database</baseDirectory>
<buildTimeoutSeconds>90</buildTimeoutSeconds>
<successExitCodes>0</successExitCodes>
</exec>
<exec executable="$(sqlComparePath)">
<description>Build MyDatabase Database</description>
<buildArgs>
/scripts1:/CI_Databases/MyDatabase
/server2:localhost
/Database2=MyDatabase
/synchronise
</buildArgs>
<baseDirectory>\Artifacts\MyDatabase-Database</baseDirectory>
<buildTimeoutSeconds>90</buildTimeoutSeconds>
<successExitCodes>0</successExitCodes>
</exec>
<exec executable="$(sqlCmdPath)">
<description>Run Unit Tests</description>
<buildArgs>-E -b -d MyDatabase –i
"IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[tSQLt].[RunAll]')
AND TYPE IN (N'P',N'PC'))
BEGIN
EXECUTE [tSQLt].[RunAll]
END"</buildArgs>
<baseDirectory>\Artifacts\MyDatabase-Database</baseDirectory>
<buildTimeoutSeconds>3000</buildTimeoutSeconds>
<successExitCodes>0,63</successExitCodes>
</exec>
<exec executable="$(sqlCmdPath)">
<description>Get Unit Tests</description>
<buildArgs>-E -b -d MyDatabase -h-1 -y0 -I
-i ":XML ON
EXEC [tSQLt].[XmlResultFormatter];"
-o "C:\CCNetProjects\Configuration\CI_SQL\Results\TestResults.xml"
</buildArgs>
<baseDirectory>\Artifacts\MyDatabase-Database</baseDirectory>
</exec>
</tasks>
<publishers>
<merge>
<files>
<file>C:\CCNetProjects\Configuration\CI_SQL\Results\TestResults.xml</file>
</files>
</merge>
<statistics />
<modificationHistory onlyLogWhenChangesFound="true" />
<xmllogger logDir="\CCNetProjects\buildlogs"/>
<artifactcleanup cleanUpMethod="KeepLastXBuilds" cleanUpValue="50" />
</publishers>
With all that set up each time the project is run the old database is dropped, then a new instance created and configured for SQL Test. Next SQL Compare is used to sync the new database to the Version Control repository copy. The tests are run and the output stored in TestResults.xml and finally that xml is merged, in the publishers node, with the build logs. What is now required is getting those results to do something in CCNet, because as things stand you would have to check the build log manually to see whether any tests passed or failed and decide what to do about it.
Integrating the results with CCNet
This is the part that caused me the most pain, because I was hoping for a pre-existing xsl file to transform the xml – after all there are already a good number of these included with CCNet – but alas I didn’t find anything, so decided to write my own. First I copied existing xsl’s that were doing what I wanted for both the buildReportBuildPlugins, which shows up on the main build summary and the ReportBuildPlugin that’s listed on the left side (under the link to the build log) and then added them to the dashboard.config
<buildPlugins>
<buildReportBuildPlugin>
<xslFileNames>
<xslFile>xsl\header.xsl</xslFile>
<xslFile>xsl\modifications.xsl</xslFile>
<xslFile>xsl\compile-msbuild.xsl</xslFile>
<xslFile>xsl\compile.xsl</xslFile>
<xslFile>xsl\tSQLtSummary.xsl</xslFile>
</xslFileNames>
</buildReportBuildPlugin>
<xslReportBuildPlugin description="tSQLt Report"
actionName="tSQLtReport"
xslFileName="c:\CCNetProjects\Custom Reports\tSQLtReport.xsl">
<includedProjects>
<projectName>StudentNet-Database</projectName>
</includedProjects>
</xslReportBuildPlugin>
</buildPlugins>
This shows the tSQLtSummary integrated into the Build Report. It summarises the number of tests run, passed and any failures. As I said I based the summary on an existing xsl (NUnit) and I needed to modify a few paths in order to access the results; I’m only including a few lines here but the full version is available as a link at the bottom.
<xsl:variable name="nunit2.result.list" select="/cruisecontrol/build/testsuites/testsuite"/>
<xsl:variable name="nunit2.testcount" select="sum($nunit2.result.list/@tests)"/>
<xsl:variable name="nunit2.failures" select="sum($nunit2.result.list/@failures)"/>
The tSQLtReport.xsl file is also quite lengthy, so again the full version is linked at the bottom. I started with the MsTestReport.xsl and tweaked it for tSQLt in much the same way as for the summary.
<xsl:value-of select="sum(/cruisecontrol/build/testsuites/testsuite/@tests)" />
This gives a nice degree of visibility into the status / health of your database builds.
I know there is a great deal more polish that could be added to these reports, but I’ve done the initial work and am happy enough (for the moment) with the result. If anyone wants to update the files please feel free and share them / ping me so I can update them.
Links to the full files:
76a91d99-5724-4a74-9405-2b22c59d9c8d|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: tSQLt |
Categories: CruiseControl.Net | ContinuousIntegration