tSQLt test cases can be executed and included in the results of your continuous integration build. This section describes how to integrate tSQLt test results with the popular continuous integration tool, CruiseControl.
CruiseControl executes your build script on a schedule or when code is checked-in to the version control repository. It reports build errors automatically and can include test results. For this discussion, we’ll setup a build in Ant, although other builders can be used.
We want our build to perform the following steps:
1. Create the database which we are testing. It should include the creation of the tables, procedures and other objects.
2. Install tSQLt to the database.
3. Load our tSQLt unit tests into the database.
4. Execute the tests.
5. Get the results from the test cases and store them in a file.
Setting up the build
First lets tell CruiseControl about the build script we will create. After installing CruiseControl, you will find a config.xml in the installation folder. This config file includes your projects (CruiseControl includes a sample project called Connect Four). We’re going to create our own project:
<project name="MyDatabaseProject">
<listeners>
<currentbuildstatuslistener file="logs/${project.name}/status.txt"/>
</listeners>
<modificationset quietperiod="30">
<filesystem folder="C:/projects/${project.name}"/>
</modificationset>
<schedule interval="300">
<ant anthome="apache-ant-1.7.0" buildfile="c:/projects/${project.name}/build/build.xml">
<property name="db.server" value="localhost"/>
<property name="db.name" value="MyUnitTestDatabase"/>
</ant>
</schedule>
</project>
This simple build file executes a build every 5 minutes by calling the Ant script stored at c:/projects/MyDatabaseProject/build/build.xml. We are passing two properties to the Ant script: the server and database name which we will create and use to execute the tests.
The Build Script
Now, we’ll create the build script in c:/projects/MyDatabaseProject/build/build.xml. Here is a sample which completes all of the desired steps:
<project name="tSQLt.sourceforge" default="all">
<target name="all" depends="db.recreate, db.tsqlt.install, db.tests.install, db.tests.run, db.tests.output, db.tests.check.results"/>
<target name="db.recreate">
<exec executable="cmd" failonerror="true">
<arg value="/c"/>
<arg value="sqlcmd -S ${db.server} -E -I -Q "IF EXISTS(SELECT 1 FROM sys.databases WHERE name='${db.name}') DROP DATABASE ${db.name}; CREATE DATABASE ${db.name};""/>
<arg value="sqlcmd -S ${db.server} -E -I -i "CreateSchema.sql""/>
</exec>
</target>
<target name="db.tsqlt.install">
<exec executable="cmd" failonerror="true">
<arg value="/c"/>
<arg value="sqlcmd -S ${db.server} -d ${db.name} -I -i "test/tSQLt.class.sql""/>
</exec>
</target>
<target name="db.tests.install">
<exec executable="cmd" failonerror="true">
<arg value="/c"/>
<arg value="sqlcmd -S ${db.server} -d ${db.name} -I -i "test/TestCases.sql""/>
</exec>
</target>
<target name="db.tests.run">
<exec executable="cmd" failonerror="true">
<arg value="/c"/>
<arg value="sqlcmd -S ${db.server} -d ${db.name} -I -Q "EXEC tSQLt.RunAll;""/>
</exec>
</target>
<target name="db.tests.output">
<exec executable="cmd" failonerror="true">
<arg value="/c"/>
<arg value="sqlcmd -S ${db.server} -d ${db.name} -h-1 -y0 -I -i "test/GetTestResults.sql" -o "test/results/TestResults.xml""/>
</exec>
</target>
<target name="db.tests.check.results">
<exec executable="cmd" failonerror="true">
<arg value="/c"/>
<arg value="sqlcmd -S ${db.server} -d ${db.name} -h-1 -y0 -I -Q ":EXIT(SELECT COUNT(*) FROM tSQLt.TestResult WHERE Result != 'Success')""/>
</exec>
</target>
</project>
Details on each section of the build script
db.recreate: In this section we are dropping and recreating the database. Then we are running the script which populates your database objects, CreateSchema.sql. All file locations are relative to the build folder, however you can enter any paths you wish.
db.tsqlt.install: This section installs tSQLt to your test database.
db.tests.install: Here we put your test cases into the database from the test/TestCases.sql file.
db.tests.run: tSQLt.RunAll executes all of the tests. See Creating and Running Test Cases in tSQLt for other options on running test cases.
db.tests.output: Queries the tSQLt.TestResult table in XML format and stores the results in test/results/TestResults.xml. Here is an example of GetTestResults.sql script which could be used here:
:XML ON EXEC [tSQLt].[XmlResultFormatter];
Note that the :XML ON command used here is a special sqlcmd directive which allows long XML strings to be output. tSQLt.XmlResultFormatter retrieves the test results as an XML document compatible with CruiseControl.
db.tests.check.results: This last step in the build, causes the build to fail if any test cases have failed or caused an error.
Merging the Test Case Results
Finally, we’ll go back to the config.xml in the CruiseControl installation folder. We need to add a log element to our config script. The log element merges the output of the test results with the CruiseControl log. This is our final project configuration in config.xml:
<project name="MyDatabaseProject">
<listeners>
<currentbuildstatuslistener file="logs/${project.name}/status.txt"/>
</listeners>
<modificationset quietperiod="30">
<filesystem folder="C:/projects/${project.name}"/>
</modificationset>
<schedule interval="300">
<ant anthome="apache-ant-1.7.0" buildfile="c:/projects/${project.name}/build/build.xml">
<property name="db.server" value="localhost"/>
<property name="db.name" value="MyUnitTestDatabase"/>
</ant>
</schedule>
<log>
<merge dir="c:/projects/${project.name}/build/test/results"/>
</log>
</project>
Summary
Automating your build process using a tool such as CruiseControl to execute your tests can provide you with constant feedback on your development. This section has demonstrated a very simple way of configuring CruiseControl to work with tSQLt. Your actual build script is likely to also include steps to compile business logic code written in another logic and execute other types of test cases. Having access to your database level tests in your build process is important to maintaining database quality.
Facebook
Twitter
LinkedIn
Reddit
StumbleUpon
Technorati
Print