Step 3 – unit testing the functionality of the a new sproc
This is the third blog in a series looking at how to take a TDD approach when creating new sprocs.
It will look at the third step outlined in – Database unit testing patterns – how to test the functionality of a sproc. In this case, how to ensure that the data outputted meets the requirements.
* It is assumed that you have read parts 1 and 2 in this series.
Overview of the overall process
- Identify the requirements – the data that should be returned by the sproc.
- Ensure that appropriate test data has been set up.
- Use the ExportDBDataAsXML tool to create XML files that contain the datasets that should be returned by the sproc.
- Write the data comparison unit tests using DBTestUnit. When first run these will fail.
- Write the implementation SQL script for the sproc.
- Run the tests again. They will pass if the data returned by the sproc matches the ‘expected test data’ in the XML files.
Why test the data outputted by the sproc
The data outputted by a sproc is part of the data contract – the ‘DB API’ – offered by the database to its client. When changes/refactorings are made to the database, having a set of automated unit tests can make it easier to ensure that this DB API is maintained.
Also, from a personal viewpoint, I find that writing tests first – ie defining the requirements – means the implementation is more likely to meet the requirements.
Note
The purpose of this blog is to give an initial introduction into taking a TDD approach when carrying out data comparison type unit testing.
The implementation produced in this blog will not have all of the required query filters to ensure that only ‘current employees are returned’. The next blog in this series will show how to create more ‘granular’ tests – with each query filter explicitly unit tested.
Background scenario
The high level requirement can be outlined as:
“For a given department the sproc should return the details of current employees in that department.”
To test the data that will be outputted there are two areas that need defining:
a) The data elements that the sproc should return. This along with the properties tested in the previous blog effectively make up the ‘DB API’ – the contract offered by the sproc to clients of it.
b) The logic within the sproc that ensures only ‘current employees’ of a department are returned ie the query filters.
The following data elements are required to be returned by the sproc*:
BusinessEntityID (unique ID of a person) FirstName, LastName, JobTitle, StartDate and EndDate (of working for a particular department).
* In reality to get to this point can involve a large degree of analysis/collabaration between different people.
Ensuring that test data exists
The next step is to ensure that appropriate test data exists.
The list of data elements above can be sourced from the existing AdventureWorks database using the following tables:
Person.Person, HumanResources.Employee and HumanResources.EmployeeDepartmentHistory
The following SQL statement can be used to return the data that is expected for the department with a DepartmentID = 2
SELECT e.BusinessEntityID , p.FirstName , p.LastName , e.JobTitle , ed.StartDate , ed.EndDate FROM HumanResources.EmployeeDepartmentHistory as ed INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID WHERE ed.DepartmentID = 2
The image below shows the data that is returned when this query is run against the database:
At this point, the data currently in the sample database is appropriate for carrying out initial testing
The next blog will look at actually creating data to test specific query filters.
Creating the expected test datasets
The ‘expected test data’, that the sproc should return, will be stored in XML/XSD files.
The ExportDBDataAsXML tool can be used to create these. More detail on how to set up/configure this tool can be found at – Using DBTestUnit to test data outputs from SQL objects.
For testing the new sproc the following is added to the ExportDBDataAsXML config file.
<!--********************-->
<!--SECTION FOR SPROCS-->
<!--********************-->
<Sprocs>
<SQLObject>
<FileName>TestData_uspGetCurrentEmployeesForDepartment_Dept2</FileName>
<Execute>Yes</Execute>
<SQLStatement>
SELECT e.BusinessEntityID, p.FirstName, p.LastName, e.JobTitle, ed.StartDate, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = 2
</SQLStatement>
</SQLObject>
When the tool is run it queries the database using the SQL set in the ‘SQLStatement’ element. The output is placed in XML/XSD files – with the names set by the ‘FileName’ element (the directory it is created in is configured in another section.)
The data outputted is shown below for the XML and XSD files respectively:
TestData_uspGetCurrentEmployeesForDepartment_Dept2.xml
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table>
<BusinessEntityID>4</BusinessEntityID>
<FirstName>Rob</FirstName>
<LastName>Walters</LastName>
<JobTitle>Senior Tool Designer</JobTitle>
<StartDate>2004-07-01T00:00:00+01:00</StartDate>
</Table>
<Table>
<BusinessEntityID>11</BusinessEntityID>
<FirstName>Ovidiu</FirstName>
<LastName>Cracium</LastName>
<JobTitle>Senior Tool Designer</JobTitle>
<StartDate>2005-01-05T00:00:00+00:00</StartDate>
</Table>
<Table>
<BusinessEntityID>12</BusinessEntityID>
<FirstName>Thierry</FirstName>
<LastName>D'Hers</LastName>
<JobTitle>Tool Designer</JobTitle>
<StartDate>2002-01-11T00:00:00+00:00</StartDate>
</Table>
<Table>
<BusinessEntityID>13</BusinessEntityID>
<FirstName>Janice</FirstName>
<LastName>Galvin</LastName>
<JobTitle>Tool Designer</JobTitle>
<StartDate>2005-01-23T00:00:00+00:00</StartDate>
</Table>
</NewDataSet>
TestData_uspGetCurrentEmployeesForDepartment_Dept2.xsd
<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="BusinessEntityID" type="xs:int" minOccurs="0" />
<xs:element name="FirstName" type="xs:string" minOccurs="0" />
<xs:element name="LastName" type="xs:string" minOccurs="0" />
<xs:element name="JobTitle" type="xs:string" minOccurs="0" />
<xs:element name="StartDate" type="xs:dateTime" minOccurs="0" />
<xs:element name="EndDate" type="xs:dateTime" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Once these files have been created they should be checked to ensure they are returning the expected data.
Writing a test that defines the requirement/expectations
The next step is to write the data comparison unit tests.
As mentioned in previous blogs DBTestUnit provides a number of sample C# test templates. Therefore, the easiest way to get started is to copy one and change appropriately.
The sample code below shows a data comparison test for the new sproc:
using System;
using MbUnit.Framework;
using DBTestUnit.Util;
namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
[TestFixture]
public class uspGetCurrentEmployeesForDepartment
{
string dbInstance = "AdventureWorks";
string sprocTestFileDir = AppSettings.DirSprocExpectedResults();
public uspGetCurrentEmployeesForDepartment() { }
[RowTest]
[Row("TestData_uspGetCurrentEmployeesForDepartment_Dept2", "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2")]
public void T01_CheckRowsFromExec(string fileName, string sqlText)
{
bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
Assert.IsTrue(areTheSame, "The data returned by the SQL text run in this test is not the same as that in the XML file.");
}
}
}
What happens when the tests are run?
When the test is first run it will fail.
As executing the following SQL statement:
‘EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2’
will not return any data. Obviously this will not be the same when compared to the data contained in the expected test data XML/XSD files created above.
The image below shows the output from the MBUnit console when this test is run
Making the test pass
Next just enough SQL is written to ensure that the test passes.
A script – as shown below – is created and run against the database.
IF EXISTS(SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA + '.' + ROUTINE_NAME = 'HumanResources.uspGetCurrentEmployeesForDepartment') DROP PROC HumanResources.uspGetCurrentEmployeesForDepartment GO CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment @departmentID smallint AS SET NOCOUNT ON SELECT e.BusinessEntityID , p.FirstName , p.LastName , e.JobTitle , ed.StartDate , ed.EndDate FROM HumanResources.EmployeeDepartmentHistory as ed INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID WHERE ed.DepartmentID = @departmentID GO
When the test is run again – it will now pass.
Now, the data returned by the sproc is the same as that contained in the expected test data files.
This is shown in image below:
How does this work?
There are two parts to a data comparison unit test as shown in the sample code below:
[RowTest]
[Row("TestData_uspGetCurrentEmployeesForDepartment_Dept2", "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2")]
public void T01_CheckRowsFromExec(string fileName, string sqlText)
{
bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
Assert.IsTrue(areTheSame, "The data returned by the SQL text run in this test is not the same as that in the XML file.");
}
1. At the top – the ‘Row’ part.
The names of the expected data test file and the actual SQL statement that will be executed are set.
The XML/XSD files named:
‘TestData_uspGetCurrentEmployeesForDepartment_Dept2’
will be compared to the data returned by executing:
‘EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2’
2. The DBTestUnit test method that will carry out the data comparison.
The values set above are passed to the test method – ‘DataSetComparer.Compare’ (part of the DBTestUnit.Util namespace).
This method ‘grabs’ the expected data from the XML/XSD files and transforms it into datasets and then compares this to the dataset returned by running the SQL statement against the database.
If the datasets are the same the test method will return ‘true’ and the test will pass.
If there are any differences it will return ‘false’ and the test will fail.
What happens if ‘unexpected’ changes are made?
There are two types of changes which these types of tests will identify.
1. Schema changes of the dataset returned. If this is changed in anyway eg column names, ordinal position, adding/removing a column and data types*.
2. Logic changes that cause the number of rows returned to change. For a given input there is an expected set of rows to be returned. If, for example, a change is made to a query filter that effects the rows returned – then this type of test will quickly identify this.
* Due to the method currently used by DBTestUnit to compare datasets actually returned by the sproc to those which are expected (XML/XSD comparison) – some types of schema changes on the dataset returned by sproc will not be detected. Looking at the XSD – TestData_uspGetCurrentEmployeesForDepartment_Dept2.xsd shown above – if the underlying SQL column for ‘LastName’ is changed, for example, from nvarchar(50) to nvarchar(40) this type of test will not necessarily identify this. I hope to improve on this in a future version.
Acknowledgements
The idea behind how to carry out this data comparison and the implementation used within the DBTestUnit method – DataSetComparer.Compare – was based on an article written by Alex Kuznetsov and Alex Styler Close those Loopholes – Testing Stored Procedures.
Future release
The next release of DBTestUnit – post version – 0.4.0.428 – will use an updated version of MBUnit (from v2.4 to v3.2).
The latest version of MBUnit makes it easier to work with XML – see MbUnit 3.2 makes it easier to work with Xml
For example it has an inbuilt test method – Assert.Xml.AreEqual. The advantage of using this is the fact that if the assert fails it will display any differences between two XML files. This can save a lot of time troubleshooting failing tests. Writing a test will be very similar to the sample code shown above.
Note the existing method DataSetComparer.Compare will continue to be supported.
What next
This blog has given a quick overview on how to take a TDD approach when testing the expected data that should be outputted by a new sproc.
The next blog in this series will extend this and take a more detailed look at how to explicitly test each ‘query filter’.
Posted by Michael Baylon 
DBTestUnit 