Unit testing ‘Select’ stored procedures – part 2

Step 2 – testing the properties of a new sproc

Intro
This blog is the second in this series looking at how to take a TDD approach when creating a new sproc.

The first blog showed how DBTestUnit could be used to test for the existence of a SQL object. This blog will look at the second step – outlined in Database unit testing patterns – how to test the properties of a new sproc.

Why test the properties/schema of a sproc
Sprocs can be used as a way of decoupling external clients from the internal schema of the database. They effectively offer a ‘DB API’ to clients – with the ‘allowable’ inputs and outputs being part of the ‘data contract’ between the database and the external clients.

The benefits of taking a TDD approach include:
1. The sproc developer has to think explicitly about the contract offered to clients.
2. When changing/refactoring the internal schema of a database, having a set of automated tests can help ensure that the ‘DB API’ is maintained .

Background scenario
The overall high level requirement was outlined in the first blog.

“For a given department the sproc should return the details of current employees in that department.”

The new sproc created at the end of the first blog first blog met the basic requirement that it existed. The next step is to define the inputs/outputs required.

From the existing database schema it can be seen that each department can be uniquely identified by the column DepartmentID. From this the following requirements can be stated:

“The sproc should only have 1 input parameter – named @departmentID.”

“It should have 0 output parameters”.

“The input parameter @departmentID should have the same properties as that of the column – HumanResources.Department.DepartmentID”

“The input parameter @departmentID should have the data type – smallint”

Note
The focus of this blogs is on the TDD process – not on the actual sproc being created. Therefore, a simple sproc is being used as an example and it will be implemented using the existing database schema.

The blog series is being written in a serial fashion – i.e. in step 2 (this blog) the inputs/outputs are defined, in step 3 (the next blog) the sprocs functionality is defined. As mentioned previously – Database unit testing patterns – in reality there is often a number of iterations between steps 2 and 3.

Writing a test that defines the requirement/expectations
DBTestUnit provides a number of sample C# sproc test templates. Therefore, the easiest way to get started is to copy one and change the appropriate expected properties for the new sproc.

The sample code below shows the tests for the requirements outlined above:

using System;
using MbUnit.Framework;
using DBTestUnit.InfoSchema;
using DBTestUnit.UnitTestBaseClass.MSSQL;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment : SprocTestBase
    {
        public uspGetCurrentEmployeesForDepartment()
        {
            dbInstance = "AdventureWorks";
            schema = "HumanResources";
            sprocName = "uspGetCurrentEmployeesForDepartment";
            sproc = new Sproc(dbInstance, schema, sprocName);
            expectedInputCount = 1;
            expectedInputList = "@departmentID";
            expectedOutputCount = 0;
            expectedOutputList = "";
        }

        [RowTest]
        [Row("@departmentID", "1,IN,smallint,N/A")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

        [RowTest]
        [Row("@departmentID", "HumanResources.Department", "DepartmentID")]
        public void T06_ParameterIsTheSameDataTypeAndLength(string paramName, string tableName, string columnName)
        {
            bool areTheSame = sproc.ParameterIsSameAsColumn(paramName, tableName, columnName);
            Assert.IsTrue(areTheSame, "Parameter and column are not the same");
        }
    }
}

To make it easier to explain the code above, the explanation notes have been split into three sections.

Section A

            expectedInputCount = 1;
            expectedInputList = "@departmentID";
            expectedOutputCount = 0;
            expectedOutputList = "";

This is declarative and similar in nature to the tests outlined in the first blog in this series.

It tests the requirements:

“The sproc should only have 1 input parameter – named @departmentID.”

expectedInputCount = 1 and expectedInputList = “@departmentID”;

“It should have 0 output parameters”

expectedOutputCount = 0 and expectedOutputList = “”

Section B

        [RowTest]
        [Row("@departmentID", "1,IN,smallint,N/A")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

It tests the requirement:

“The input parameter @departmentID should have the data type – smallint”

Each expected properties for a sproc parameter can be defined in each ‘Row’.

For @departmentID this is set by: [Row(“@departmentID”, “1,IN,smallint,N/A”)]

The variable ‘expectedParameterProperties’ has a value of “1,IN,smallint,N/A” – which breaks down into the following:

  • Ordinal position – 1
  • Parameter type – hence ‘IN’ (output would be ‘INOUT’)
  • Data type – smallint
  • String length – smallint therefore ‘N/A’ (examples for ‘string’ data types e.g. ‘varchar,20’ or ‘nvarchar,256’)

Section C

        [RowTest]
        [Row("@departmentID", "HumanResources.Department", "DepartmentID")]
        public void T06_ParameterIsTheSameDataTypeAndLength(string paramName, string tableName, string columnName)
        {
            bool areTheSame = sproc.ParameterIsSameAsColumn(paramName, tableName, columnName);
            Assert.IsTrue(areTheSame, "Parameter and column are not the same");
        }

It tests the requirement:

“The input parameter @departmentID should have the same properties as that of the column – HumanResources.Department.DepartmentID”

DBTestUnit can check that a parameter has the same data type as that of a table/view column.

Each parameter that should be the same as a table/view column is defined in a ‘Row’

For @departmentID this is set by: [Row(“@departmentID”, “HumanResources.Department”, “DepartmentID”)]

What happens when the tests are run?
The image below shows the output from the MBUnit console when the tests are run.

As expected most fail as the sproc does not yet meet the requirements.

Two of the tests pass, as the sproc, in its current form, does meet the requirement of not having any output parameters.

TDD a new sproc - failing unit tests

TDD a new sproc - failing unit tests

Making all the tests pass
The next step is to write the implementation script/code to make the tests pass.

Just enough script is written to ensure that the tests pass. Later steps will add further functionality.

A very basic SQL script – as shown below – can be 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

GO

The only change from part 1 is the addition of the input parameter ‘@departmentID smallint’

If the tests are run again they will all pass as the sproc now meets the requirements/expectations as specified in the unit tests.

The image below shows the output from the MBUnit console when they are run.

TDD a new sproc - all unit tests pass

TDD a new sproc - all unit tests pass

How does this work?
The tests outlined in part 1 are all declarative in nature.

The tests above are mixed in nature.

Section A are similar to those in part 1

Those in Section B and Section C include some ‘boiler plate’ test code.

All are similar in the fact that they use methods in DBTestUnit test classes that run queries against the various INFORMATION_SCHEMA views to ensure the actual sproc has the same properties as those defined in the tests.

Section A
The sproc test class – ‘uspGetCurrentEmployeesForDepartment’ inherits from a DBTestUnit abstract class named ‘SprocTestBase’ (part of DBTestUnit.UnitTestBaseClass.MSSQL namespace) – as shown in the code sample below:

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment : SprocTestBase
    {

‘SprocTestBase’ has a number of test methods

e.g. for testing parameters – InputCount(), InputCount_Named(), OutputCount(), OutputCount_Named()

When the tests are run – the ‘SprocTestBase’ test methods are called.

The expected values – as shown below – are used by these methods when comparing against the actual values returned from the database.

            expectedInputCount = 1;
            expectedInputList = "@departmentID";
            expectedOutputCount = 0;
            expectedOutputList = "";

DBTestUnit then runs a number of queries against the database being tested.

For example, to test the expected input parameter count the following query is run:

SELECT COUNT(DATA_TYPE) 
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_SCHEMA = 'HumanResources'  AND SPECIFIC_NAME = 'uspGetCurrentEmployeesForDepartment' 
AND PARAMETER_MODE='IN'

If the count returned is the same as that set in ‘expectedInputCount’ i.e. 1 then the test will pass

To test the names of the input parameters the following query is run:

SELECT COUNT(DATA_TYPE) 
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_SCHEMA = 'HumanResources'  AND SPECIFIC_NAME = 'uspGetCurrentEmployeesForDepartment' 
AND PARAMETER_MODE='IN' 
AND PARAMETER_NAME IN ('@departmentID')

To test the expected output parameters similar queries are run but with – PARAMETER_MODE’ set to ‘INOUT’

Section B
The sproc test class – ‘uspGetCurrentEmployeesForDepartment’ instantiates a DBTestUnit object named ‘Sproc’ (part of DBTestUnit.InfoSchema namespace)

This has a number of test methods as shown below:

      
public string ParameterProperties(string parameterName);
public bool ParameterIsSameAsColumn(string parameterName, string entityFullName, string columnName);

To test the properties of the parameters the following requirement was set:

        [RowTest]
        [Row("@departmentID", "1,IN,smallint,N/A")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

When the test ‘T05_ParameterProperties’ is run – the test method ‘sproc.ParameterProperties’ is called.

The following query is then run against the database:

SELECT convert(varchar,ordinal_position) + ',' + parameter_mode + ',' + data_type + ',' + COALESCE(convert(varchar,character_maximum_length),'N/A') 
FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'HumanResources'  AND SPECIFIC_NAME = 'uspGetCurrentEmployeesForDepartment' 
AND PARAMETER_NAME = '@departmentID'

If the returned value is the same as the expected value i.e. “1,IN,smallint,N/A” then the test will pass

Section C
To test that the parameter should be the same as a table/view column – the following requirement was set:

        [RowTest]
        [Row("@departmentID", "HumanResources.Department", "DepartmentID")]
        public void T06_ParameterIsTheSameDataTypeAndLength(string paramName, string tableName, string columnName)
        {
            bool areTheSame = sproc.ParameterIsSameAsColumn(paramName, tableName, columnName);
            Assert.IsTrue(areTheSame, "Parameter and column are not the same");
        }

When the test ‘T06_ParameterIsTheSameDataTypeAndLength’ is run – the test method ‘sproc.ParameterIsSameAsColumn’ is called.

The following query is then run against the database:

T06_ParameterIsTheSameDataTypeAndLength

SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS as c, INFORMATION_SCHEMA.PARAMETERS as p 
WHERE c.DATA_TYPE=p.DATA_TYPE AND COALESCE(c.CHARACTER_MAXIMUM_LENGTH,'')=COALESCE(p.CHARACTER_MAXIMUM_LENGTH,'') 
AND c.TABLE_SCHEMA + '.' + c.TABLE_NAME='HumanResources.Department' AND c.COLUMN_NAME='DepartmentID' 
AND p.SPECIFIC_SCHEMA='HumanResources' AND p.SPECIFIC_NAME='uspGetCurrentEmployeesForDepartment' AND p.PARAMETER_NAME='@departmentID'

If @departmentID has the same data type as HumanResources.Department.DepartmentID – then the query above will return a count of 1 and DBTestUnit will return ‘true’ to the calling test method. If the count is 0 – i.e. the data type is not the same – then DBTestUnit will return ‘false’.

Summary
At first, it might seem quite complicated to carry out this type of test. Especially as it is only testing one input parameter.

It is worth remembering that the developer only needs to concentrate on defining the tests/requirements of the new sproc – as per the section Writing a test that defines the requirement/expectations. DBTestUnit is responsible for carrying out the tests – and to a large degree this detail is hidden from the developer.

As mentioned previously, the DBTestUnit download includes a number of sample tests that makes it relatively easy to get started.

If any change is made to the properties of the sproc without a corresponding change to the tests/requirements- e.g. another parameter is added, the existing one is renamed/changed etc – then these tests will quickly identify this. Therefore, this type of test can help to ensure that the DB API offered to clients is maintained whilst any changes to the internal schema are made.

What next
This blog has shown how to take a TDD approach when testing the expected properties of a new sproc.

The next blog in this series will look at the third step from Database unit testing patterns. How to unit test the expected functionality of the new sproc – in this case the data that is outputted.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: