SPUnit Cookbook

Introdution

A short cookbook in the spirit of the xUnit frameworks. A cookbook is a traditional part of these libraries. It presents an example to get you up and running.

Simple Test

You write a stored procedure. You need to figure out if it does what you want it to. How do you accomplish this? The simplest way is to simply execute the script, and manually verify that the database gets changed as you expect. Often, you will have return values and error codes that you want to ensure are returning to the caller correctly. Most people write sample programs that call the procedure, and print out the results. What about those intermediate values? What about the calculation you do while iterating through a Cursor? The ever handy PRINT statement is the usual solution. All of these methods have drawbacks. Visual inspection of the database is a good idea, but it is time consuming, and if the system is in production (or even if its not) you don't necessarily want to actually make changes to the database just to veryify that you can drop a table. This inspection is necessary every time you make changes to the procedure. PRINT'ing information is also fine, but then you have to litter your code with them, and they still require you to manually inspect the information. In many languages, an interactive debugger is available. Unfortunately, if you are working with SQL Server and the Query Analyzer, this is not the case. Even if one were available, however it is again tedious and error prone to step through each line of code to verify that it behaves as expected.

Tests in SPUnit can be run automatically.Once you set them up, a simple command will give you confidence that the new feature you added works, and you didn't break anything else while adding it.

Here is how to make a simple test:

Create a new sql script. Add a new stored procedure. The convention is to name the procedure after the procedure you wish to test, and prepend "Test" to the name:

IF object_id('TestDeleteOrder')IS NOT Null
    DROP PROCEDURE TestDeleteOrder
GO

CREATE PROCEDURE TestDeleteOrder
AS
...

The IF statement checks to see if the Test Procedure exists, and if so removes it. This makes it easy to recreate your tests. In the body of the test procedure, make calls to the procedure you wish to test. Use the framework Stored procedure: SPUnit.dbo.Assert() to compare values.

For example, to test the DeleteOrder stored procedure add the following as the body of the procedure:

...
    DECLARE @orderID int
    SET @orderID = 42
    
    EXEC DeleteOrder @orderID
    DECLARE @count int
    SET @count = (SELECT count(*) FROM Orders WHERE orderID = @orderID)

    EXEC SPUnit.dbo.Assert @count, 0
GO

That was a very simple test. Just about any Insert, Update, Delete, and parameterized queries can be tested this way. Often these procedures compliment one another, working on the same tables, or data. It is nice if you don't have to set up this information in each test. It would be much easier to put this information in one place, and have it available for the test to work against. Rather than trying to setup the database before hand, or adding unecessary calls to each test, use a fixture.

Fixture

A fixture is a known set of data (or commands to setup that data) that provides the environment for a set of tests. Fixtures are really nice when you have a bunch of tests that work on similar data. They can greatly reduce the complexity of your testing environment. In our above example, how can we know that the database even has an order numbered 42? As far as our test is concerned, DeleteOrder could be an empty procedure, and the test would pass. To enhance the test, change it like this:

...
    DECLARE @orderID int
    SET @orderID = 42

    INSERT INTO Orders (orderID, item, itemCount) VALUES (42, 'widget', 2)

    EXEC DeleteOrder @orderID
...

Now if DeleteOrder is empty it will fail. This is test First programming in action. If I were actually developing DeleteOrder it would have been empty until I enhanced the test thus causing it to fail. Now I can go in, and add the DELETE query to the procedure. Enhancing the test further so that the procedure returns an error if the order is not found is left as an excersise to the reader.

Now suppose you wanted to add a procedure named UpdateOrder. So, you write a test:

IF object_id('TestUpdateOrder')IS NOT Null
    DROP PROCEDURE TestUpdateOrder
GO
    CREATE PROCEDURE TestUpdateOrder
AS
    DECLARE @orderID int
    SET  @orderID = 42

    EXEC UpdateOrder @orderID, @newItem = 'foo'
    DECLARE @count int

    SET  @count = (SELECT count(*) FROM Orders WHERE orderID = @orderID)
    DECLARE @item varchar(50)
    SET  @item = (SELECT item FROM Orders WHERE orderID = @orderID)

    EXEC SPUnit.dbo.Assert @count, 1
    EXEC SPUnit.dbo.Assert @item, 'foo'
GO

For this to work, you will again need an order in the database with order number 42. You could copy the insert from the Delete test, but that is repetition. Repetition is a "bad thing." If you change your insert constraints, you have to remember to change both tests. I am forgetful, so I like to do things Once And Only Once. Instead lets create a fixture.

Fixtures are implemented in SPUnit by simply adding another stored procedure. It can have any name, but by convention it is usually some derivitive of setup. Let's name ours TestSetup:

IF object_id('TestSetup')IS NOT Null
       DROP PROCEDURE TestSetup
GO

CREATE PROCEDURE TestSetup
AS
    INSERT INTO Orders (orderID, item, itemCount) VALUES (42, 'widget', 2)
GO
Now we need to register this procedure as the fixture for our tests. This is done in the Suite Procedure. The suite procedure is covered below. For now just note that this fixture will be applied before every test. It will also be rolled back after every test. You can put any valid SQL statements, or stored procedure calls in the fixture. This is ideal for complicated setups where you have several tables, that need changed. Keep in mind this will be rolled back so no changes are made to the database. Now we can go ahead and remove the INSERT statement from TestDeleteOrder.

There is also a provision for a Teardown action that occurs after the test. In other languages this is important for things like dynamic memory cleanup. In SPUnit this is largely unecessary as everything for a test is in the context of a transaction, and later rolled back. If you do want something done in the fixture setup, that won't be rolled back automatically, simply create a stored procedure to do so, and register it as the teardown procedure (see 'Suite' below).

Test Case

Now that we have these tests, and a fixture, how do we tie this all together. Since TSQL is not an OO lanaguage, the techniques used in the other xUnit frameworks won't work. So we can do this by convention. A Test Case is defined simply by placing all of your tests and fixture procedure into a script. This limits us to one TestCase per database. Future versions of this framework will possibly have provisions to increase this capability if it proves too limiting.

Our Test Case so far looks like listing one.

Suite

Ok, you've written tests, you've created a fixture and you have grouped this code into a single script to create the procedures, how do actually arrange them so they can be all run together. If you haven't guessed from the topic heading, we do this by creating a Suite. As you can see, our efforts so far have been defining procedures, not running them. The Suite procedure makes them available to the outside world. Add a procedure called "Suite" to the script file with two OUTPUT parameters:

IF object_id('Suite')IS NOT Null
    DROP PROCEDURE Suite
GO

CREATE PROCEDURE Suite
    @setup varchar(80) = Null OUTPUT,
    @teardown varchar(80) = Null OUTPUT
AS
...

This is the only assumption that SPUnit makes about your tests -- that the database will have a Suite procedure and that Procedure has two optional OUTPUT parameters anme setup and teardown each varchar(80). Here is how to implement the Suite so it will run our tests with their fixture:

...
    EXEC SPUnit.dbo.AddTest 'SPSampleDB.dbo.TestDeleteOrder'
    EXEC SPUnit.dbo.AddTest 'SPSampleDB.dbo.TestUpdateOrder'

    SET @setup = 'SPSampleDB.dbo.TestSetup'
GO
There are two things to note here. The first is the registration of your tests. This introduces the second procedure from the SPUnit framework, the AddTest Procedure. This registers your tests with SPUnit so that the Run procedure can locate your tests. Note that you must prepend the name of the Database, and the owner of the test since SPUnit is a separate database.

The second thing to note here is our fixture being registered. This is done by setting the fully qualified name of the setup procedure to the OUTPUT parameter @setup.

Test Runner

Ok, we have written some tests, organized them into a suite, and provided a fixture. You are probably wondering how we are going to run them -- the answer? We are going to call the framework procedure Run.

Because its just another stored proc, Run can be called from any language that allows stored procedure calls. Among the items provided with the framework are two runners. One is a Windows Scripting Host sript that uses ADO, the other is a simple sql script. Let's take a look at the sql runner.

Here is the entire script:

USE SPUnit
GO

DECLARE @testCount int
DECLARE @failureCount int
DECLARE @passCount intEXEC Run 'SPSampleDB', @testCount OUTPUT, @failureCount OUTPUT

SET @passCount = @testCount - @failureCount

PRINT 'run: ' + CONVERT( varchar(4), @testCount ) +
      ' passed: ' + CONVERT(varchar(4), @passCount ) +
      ' failed: ' + CONVERT( varchar(4), @failureCount )
In order to run our tests, execute the test case script you have built so far. This will create all of the test procedures, including the Suite, and Fixture procs. Once you have done that you are all set to see if your tests pass. Execute the runner script. The Framework's Run procedure takes the name of the database you want to test (in our case SPSampleDB) and returns two OUTPUT parameters, the number of tests executed, and the number of failures. Go ahead and execute the Runner script. You should get an empty record set back, and the following message:

run: 2 passed: 2 failed: 0

Failures are returned in a recordset showing the name of the test, and the assertion. For example, add this test to your test case:

IF object_id('TestUpdateOrderFailure')IS NOT Null
    DROP PROCEDURE TestUpdateOrderFailure
GO

CREATE PROCEDURE TestUpdateOrderFailure
AS
    DECLARE @orderID int    SET @orderID = 52 -- This order does not exist

    EXEC UpdateOrder @orderID, @newItem = 'foo'

    DECLARE @count int    DECLARE @item varchar(50)

    SET @count = (SELECT count(*) FROM Orders WHERE orderID = @orderID)
    SET @item = (SELECT item FROM Orders WHERE orderID = @orderID)

    EXEC SPUnit.dbo.Assert @count, 1
    EXEC SPUnit.dbo.Assert @item, 'foo'
GO
This should fail when it asserts that the record count is 1 because we no orders exist with this orderID. Now run the runner script again. This time you should get the following message:

run: 3 passed: 2 failed: 1

And a recordset like this: 

          
                    

      testName
      asserted
    

    

      --------------------------------------------------
      ----------------------
    

    

      SPSampleDB.dbo.TestUpdateOrderFailure
      Actual: 0 Expected: 1

Notice that you only get one error message back for TestUpdateOrderFailure even though both assertions should fail. Currently SPUnit only reports the first failure from each test. This is OK, because if an assertion fails, any further assumptions made about the procedure are likely to be inaccurate.

If you would like to use the WSH runner, modify the accompanying data link file accordingly, and run the sp.bat batch file. It will give similar results to the sql script runner.

There is more information about the framework, in the Readme.htm file. Thanks for giving SPUnit a try.

Supporting web standards -- this file created with: W3C-Amaya