Unit Testing Databases with tSQLt Part 2 – testing table structure

by Greg M Lucas on 19 October 2011

In Part 1, I worked through setting up the tSQLt unit testing framework in a development database for the first time. In this post, we are going to start writing our first tests for the first piece of functionality to be implemented in Log4TSql which is an open source database logging and exception handling block for SQL2005+.

Test-driven development (TDD) normally means not writing any code until you have a failing test and also that every line of code should be tested. As database code, especially as DDL is declarative it makes less sense to test every line – e.g. testing every column exists and in the required format as individual tests would just take for ever. Fortunately, the clever guys at tSQLt are ahead of me here with a method called AssertResultSetsHaveSameMetaData of which more later. Remember, the cost of writing all the tests should not outweigh the benefits.

My first user story for Log4TSql is “As a developer I want to be able to add the Log4TSql logging block to an existing database with no risk to any existing objects” This will be accomplished by putting everything in a dedicated schema which I’m going to call “log4”, I could write a test to prove that the schema exists but nothing will compile if it doesn’t so I’m going to save myself a few lines of code.

The next story I’m going to think about is “As a developer I want to be to see details of past exceptions so that I can identify where the problem occurred” This means we need a table to store any exceptions that are captured so my first test will need to check for the existence of an Exception table.  But first we need to think about how we are going to group this and any related tests.  tSQLt relies heavily on schemas to manage suites of tests, or “test classes” to use the tSQLt nomenclature. We create a new test class like this:

--!
--! Create a Class to contain these tests
--!
EXEC tSQLt.NewTestClass 'ExceptionTableTests';
GO

We then create our first test in the ExceptionTableTests schema. Any test procedure name must start with “test…” otherwise it will not be executed as a test. This works well because I might later choose to have other procedures in the same schema/class prefixed “setup…” which could be used to set up pre-conditions for one or more tests. So we have a class to put this test in, now let’s write our first test:

CREATE PROCEDURE [ExceptionTableTests].[test_Exception_table_exists]
AS
BEGIN
    EXEC tSQLt.AssertObjectExists 'log4.Exception';
END

In the above test, we are simply telling the test framework to assert that the object “log4.Exception” exists. Note that I talk about object not table specifically. If there was a stored procedure or view called “log4.Exception” the test would still pass. This is, I think, a relatively minor shortcoming in an otherwise excellent framework – I’m sure this will get addressed in a future release.

Now we have our first test, we can run it like this:

--!
--! Run all tests in this class
--!
EXEC tSQLt.RunTestClass 'ExceptionTableTests';
GO

Not unexpectedly, this produces the following test failure output:

[ExceptionTableTests].[test_Exception_table_exists] failed: 'log4.Exception' does not exist

+---------------------+
|Test Execution Sumary|
+---------------------+

|No|Test Case Name |Result |
+--+---------------------------------------------------+-------+
|1 |[ExceptionTableTests].[test_Exception_table_exists]|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored. 
-----------------------------------------------------------------------------

This is classic TDD up to this point, write a failing test then write just enough code to pass the test. This is where the database world has to digress from the OO world because this would be the minimum amount of code required to pass that test.

CREATE TABLE [log4].[Exception] ( [ExceptionId] int)

Unfortunately, as tables go that doesn’t really cut it and I do not want to be writing a test for every column and if I wanted to take this to the extreme an ALTER TABLE…ADD COLUMN block for every column. So time for a tSQLt shortcut. Here is my complete table in it’s first cut.

CREATE TABLE [log4].[Exception]
(
  [ExceptionId]       int                   NOT NULL	IDENTITY( 1,1 )
, [UtcDate]           datetime              NOT NULL
, [SystemDate]        datetime              NOT NULL
, [ErrorContext]      varchar    ( 512 )    NULL
, [ErrorNumber]       int                   NULL
, [Severity]          int                   NULL
, [State]             int                   NULL
, [ErrorProcedure]    varchar    ( 128 )    NULL
, [ErrorLine]         int                   NULL
, [ErrorMessage]      varchar    ( max )    NULL
)
ON [DEFAULT]
TEXTIMAGE_ON [DEFAULT]

Run the above CREATE TABLE statement then the following to re-run our test(s):
--!
--! Run all tests in this class
--!
EXEC tSQLt.RunTestClass 'ExceptionTableTests';
GO

This time we have a passing test.

+---------------------+
|Test Execution Sumary|
+---------------------+

|No|Test Case Name |Result |
+--+---------------------------------------------------+-------+
|1 |[ExceptionTableTests].[test_Exception_table_exists]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Now, although I don’t want to write tests for every column individually, I have just added nine extra columns to the Exception table which have no test coverage at all. tSQLt.AssertResultSetsHaveSameMetaData allows me to test the metadata of two result sets – this includes number of columns, column order, data type and even nullability. This satisfies my need for coverage in a single test – almost a declararive test for declarative code.

Within the stored procedure, we start by creating a temporary table that looks suspiciously like our Exception table. Then we need to put a row of any old data into both the temp table and the target of this test. We then call AssertResultSetsHaveSameMetaData passing two SELECT statements first from the temp table – which will produce the metadata we are expected, and the second SELECT from the table we are trying to test for the actual metadata.

CREATE PROCEDURE [ExceptionTableTests].[test_Exception_metadata]
AS
BEGIN
    IF OBJECT_ID(N'tempdb..#Exception') > 0 DROP TABLE [#Exception];

    CREATE TABLE [#Exception]
    (
      [ExceptionId]       int                   NOT NULL
    , [UtcDate]           datetime              NOT NULL
    , [SystemDate]        datetime              NOT NULL
    , [ErrorContext]      varchar    ( 512 )    NULL
    , [ErrorNumber]       int                   NULL
    , [Severity]          int                   NULL
    , [State]             int                   NULL
    , [ErrorProcedure]    varchar    ( 128 )    NULL
    , [ErrorLine]         int                   NULL
    , [ErrorMessage]      varchar    ( max )    NULL
    );

    --! Give ourselves something to retrieve
    INSERT [log4].[Exception]
    (
      [UtcDate]
    , [SystemDate]
    , [ErrorContext]
    , [ErrorNumber]
    , [Severity]
    , [State]
    , [ErrorProcedure]
    , [ErrorLine]
    , [ErrorMessage]
    )
    VALUES
    (
      GETDATE()
    , GETUTCDATE()
    , 'ErrorContext'
    , 50000
    , 8
    , 10
    , 'ErrorProcedure'
    , 1
    , 'ErrorMessage'
    );

    --! Put something in our comparison table so
    --! that we have a comparison result set
    INSERT [#Exception]
    (
      [ExceptionId]
    , [UtcDate]
    , [SystemDate]
    , [ErrorContext]
    , [ErrorNumber]
    , [Severity]
    , [State]
    , [ErrorProcedure]
    , [ErrorLine]
    , [ErrorMessage]
    )
    VALUES
    (
      999
    , GETDATE()
    , GETUTCDATE()
    , 'matthew'
    , 50001
    , 64
    , 0
    , 'sproc'
    , 99
    , 'msg'
    );

    --! Run the test
    EXEC tSQLt.AssertResultSetsHaveSameMetaData
        'SELECT * FROM #Exception',
        'SELECT * FROM log4.Exception';
END

So we run our tests again with the following results

+---------------------+
|Test Execution Sumary|
+---------------------+

|No|Test Case Name |Result |
+--+---------------------------------------------------+-------+
|1 |[ExceptionTableTests].[test_Exception_metadata] |Success|
|2 |[ExceptionTableTests].[test_Exception_table_exists]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 2 test case(s) executed, 2 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

So at this point, we have the table we want and a couple of tests to ensure that the table (a) exists and (b) is structured the way we want. If in the future, a developer comes along and, for example, makes one of the nullable columns NOT NULL, the metadata test will fail. At which point, the developer will either have to rethink their design or properly evaluate the impact of their change and refactor accordingly

In Part 3 we will add some additional tests for the Exception table to test for a unique key, a working auto-increment and a couple of default constraints.

{ 2 comments }

Christophe Noppeley June 15, 2012 at 11:56

Nice Tutorial, very useful. In the code above , just a little adjustment :
[ExceptionTableTests].[test_Exception_metadata] failed: Cannot insert explicit value for identity column in table ‘#Exception
😉

Greg M Lucas June 15, 2012 at 13:44

Christophe,

Thank you for your feedback.

I’ve corrected the code so no one else encounters the same issue.

Many thanks

Greg

Comments on this entry are closed.

Previous post:

Next post: