SQL Server Unit Testing Framework

• Download source files - 28.4 KB

Background

Database schema are subject to change. Fields are added, deleted or renamed and new constraints are introduced. However, since there is no "compiler" as such for SQL Server, these changes can be breaking changes for the code that depends on these databases and therefore a set of "unit tests" are a valuable safety net.

This unit test framework has no dependencies beyond SQL server and does not rely on the common language runtime. The tables and functions in the downloadable source files (.sql) should ideally be created in a separate database on the server that hosts the databases being tested. 

The process will be familiar to anyone who has used the unit test frameworks such a NUnit, MSTest, etc. First, you set up the prerequisites for the test (pre test setup) then you perform the assertion (unit test) then you clean up anything created by the unit test (tear down).

The Requirement

The requirement is for an overnight or scheduled job to run and perform a standard set of unit tests against all the databases on the development server to provide a 'smoke test' and unit test capability to alert us if any development changes break any of the existing database functionality. The unit test framework has to be hosted entirely within SQL Server and has to utilize standard T-SQL. The test framework has to allow for the possibility that different types of database (aka data models) are stored on the same server and that tests will only be run against the relevant data model.

All test validations are recorded so that any breaking changes can quickly be caught and can easily be identified.

1. The Unit Test

Running a single unit test requires the following process: run prerequisites to get the database items required for the test, run the test step(s) and run the validation(s) pertaining to each test step and finally run the teardown code to restore the database to the state it was in before the test was run. This last part is important to ensure that any unit tests are rerunnable.

Each unit test is uniquely named and also a description of the intent of that test is recorded in the table [Unit test definition]:

CREATE TABLE [Unit Test Definition] (
    [Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Data Model] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS _
    NOT NULL CONSTRAINT [DF_Unit Test Definition_Data Model] _
    DEFAULT ([dbo].[DefaultReferenceDataItem]('Data Model')),
    [Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Date Updated] [datetime] NOT NULL CONSTRAINT [DF_Unit Test Definition_Date Updated] _
    DEFAULT (getdate()),
    [Username] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS _
    NOT NULL CONSTRAINT [DF_Unit Test Definition_Username] DEFAULT (suser_sname()),
    CONSTRAINT [PK_Unit Test Definition] PRIMARY KEY  CLUSTERED 
    (
        [Test Name]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

The Process

1.1 Test Prerequisites

The test prerequisites are a collection of SQL commands that are executed on the target database before the test is run. They are executed in step number order as read form the [Unit Test Initialise] table:

CREATE TABLE [Unit Test Initialise] (
    [Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Initialise Command Step Number] [int] NOT NULL ,
    [Command] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    CONSTRAINT [PK_Unit Test Initialise] PRIMARY KEY  CLUSTERED 
    (
        [Test Name],
        [Initialise Command Step Number]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

These are executed by the stored procedure BeforeRunUnittest:

CREATE PROCEDURE dbo.BeforeRunUnitTest (@TestName char(20), @TargetDatabase varchar(220)) AS
BEGIN

declare @Step_Number [int]  ,
        @Command varchar(2000),
         @tmpErr int, @tmpRows int,
        @test_master_db varchar(240) -- The name of the database that 
                                     -- the test framework resides on

   select @test_master_db =  DB_NAME()

   DECLARE BeforeUnitTestCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
     SELECT [Initialise Command Step Number]  ,
        [Command] 
       FROM [Unit test Initialise]
      WHERE [Test name] = @TestName
      ORDER BY [Initialise Command Step Number]

   OPEN BeforeUnitTestCursor
  
   FETCH NEXT FROM BeforeUnitTestCursor INTO @Step_Number, @Command

  WHILE @@FETCH_STATUS = 0
      BEGIN
         exec SetUnitTestStepVariables @TestName , @TargetDatabase  , _
              @Step_Number, 1, 'Initialise'
         set @command = dbo.ReplaceUnitTestvariablesWithValues_
             (@TestName, @TargetDatabase, @command)
         exec dbo.RunDatabaseCommand @command, @TargetDatabase, @tmpErr , @tmpRows
         exec SetUnitTestStepVariables @TestName , @TargetDatabase  , _
              @Step_Number, 0, 'Initialise'

         -- Get the next pending command to execute
         FETCH NEXT FROM BeforeUnitTestCursor INTO @Step_Number, @Command
      END

CLEAN_EXIT:
  -- Always close and deallocate the cursor, if in error or not
  CLOSE BeforeUnitTestCursor
  DEALLOCATE BeforeUnitTestCursor

END  

1.2 Variables

You will notice that there are two commands that set the unit test variables and substitute the unit test variables in the command with their current values. This is required so that you can write unit test steps whose T-SQL definition is only set at test run time (for example, to get the id of the most recent record in a table that has an identity field you would need to create a unit test variable and populate it with the T-SQL select IDENT_CURRENT('tablename').

The variables that are defined for each unit test are held in the table [Unit Test Variable Definition]:

CREATE TABLE [Unit Test Variable Definitions] (
    [Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Variable Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Description] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Unit Test Variable Definitions] PRIMARY KEY  CLUSTERED 
    (
        [Test Name],
        [Variable Name]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

And when a test is running, the current value of those variables for a given named database are stored in the table [Unit Test Variables]:

CREATE TABLE [Unit Test Variables] (
    [Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Database Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Variable Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Value] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Unit Test Variables] PRIMARY KEY  CLUSTERED 
    (
        [Test Name],
        [Database Name],
        [Variable Name]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

The T-SQL used to set each variable is held in the table [Unit test Variable Assignment]:

CREATE TABLE [Unit Test Variable Assignment] (
    [Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Step Number] [int] NOT NULL ,
    [Step Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Variable Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Run Before Step] [bit] NOT NULL ,
    [Assignment Command] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    CONSTRAINT [PK_Unit Test Variable Assignment] PRIMARY KEY  CLUSTERED 
    (
        [Test Name],
        [Step Number],
        [Variable Name],
        [Run Before Step]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

You will notice that this table includes the step number as well, because the same variable may be assigned a different value after each step of the unit test (for instance, if you are keeping a running total).

The setting of the unit test variables is performed by the stored procedure SetUnittestvariables:

CREATE PROCEDURE dbo.SetUnitTestStepVariables _
(@TestName char(20), @TargetDatabase varchar(220) , @Step_Number int , _
@PreTest bit, @Step_Type varchar(20)) AS
BEGIN

DECLARE @command varchar(3000),
        @variable_name varchar(100),
        @local_variable_name varchar(101), 
        @local_command nvarchar(3100),
        @params_def nvarchar(200),
        @value varchar(220),
        @errnum int

DECLARE UnitTestStepVariableAssignment CURSOR READ_ONLY LOCAL FOR
  SELECT [Variable Name],
         [Assignment Command]
    FROM [dbo].[Unit test Variable Assignment]
   WHERE [Test name] = @Testname
     AND [Step Number] = @step_number
     AND [Run Before Step] = @Pretest
     AND [Step Type] = @Step_Type

OPEN UnitTestStepVariableAssignment

FETCH UnitTestStepVariableAssignment INTO @variable_name, @command
WHILE @@fetch_status = 0
  BEGIN

    -- Make a valid variable name for use in an SQL statement 
    SET @local_variable_name = '@output_value'

    SET @local_command = 'SET ' + @local_variable_name + _
        ' = ( ' + replace(@command, '[database]','[' + @TargetDatabase + ']') + ')'    

    SET @params_def =  @local_variable_name + ' VARCHAR(100) OUTPUT'

    -- execute the command to populate the @value field
    EXEC sp_executesql @stmt = @local_command,
                  @params = @params_def,
                  @output_value = @value output

    -- Get the error number if any
    SELECT @ErrNum = ISNULL(@@Error, 0)

    -- If we got the value without any error, update the holding table
    IF (@value IS NOT NULL) AND (@ErrNum = 0)
       UPDATE [dbo].[Unit Test variables]
          SET [Value] = @Value
        WHERE [Test Name] = @testname
          AND [Database Name] = @TargetDatabase
          AND [Variable name] = @variable_name

    FETCH UnitTestStepVariableAssignment INTO @variable_name, @command
  END

CLOSE UnitTestStepVariableAssignment

DEALLOCATE UnitTestStepVariableAssignment

END

GO

The unit test variables are denoted in the actual SQL of a command by having '@@' before and after them. Then at run time, the variable names are replaced by the variable values by the scalar function ReplaceUnitTestvariablesWithValues:

-- [ RepaceUnitTestvariablesWithValues ]---------------------------------------
-- Replaces and @@variable@@ with the value looked up from the 
-- [Unit test Variables] table for the give test on the given database
-------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ReplaceUnitTestvariablesWithValues] 
(
  @test_name varchar(20),
  @database_name varchar(220),
  @command_in varchar(3000)
)
RETURNS varchar(3000)
AS
BEGIN
  declare @command_out varchar(3000),
          @value varchar(220),
          @variable_name varchar(100)

  set @command_out = @command_in

  DECLARE variables_substitution CURSOR READ_ONLY LOCAL FOR
    SELECT ltrim(rtrim([Variable Name])), 
           ISNULL([Value], 'NULL')
    FROM [dbo].[Unit Test Variables]
   WHERE [test name] = @test_name
     AND [database name] = @database_name

  OPEN variables_substitution

  FETCH variables_substitution INTO @variable_name, @value

  WHILE @@fetch_status = 0
    BEGIN

      SET @command_out = replace(@command_out, '@@' + @variable_name + '@@', @value)

      FETCH variables_substitution INTO @variable_name, @value
    END

  CLOSE variables_substitution

  DEALLOCATE variables_substitution
 
  return @command_out

END

GO

1.3 Running a Single T-SQL Command

To run a single command, we first replace [database] with the database name passed in, then use the exec command to run it:

CREATE PROCEDURE dbo.RunDatabaseCommand (@Command varchar(2000), _
    @TargetDatabase varchar(220), @error int output, @rows int output) AS
BEGIN

    select @command = replace(@command, '[database]', '[' + @targetDatabase +']')
    exec (@command)
    select @error = isnull(@@error,0), @rows = isnull(@@rowcount,0)

END

GO

The @@error and @@rowcount system variables are passed back out to record how the command went.

1.4 Test Step Validations

After each test step, you might want to test the state of the database after the step has run. This is a test step validation and the definitions of the validations are stored in the table [Unit Test Step Validation]:

CREATE TABLE [Unit Test Step Validation] (
    [Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Step Number] [int] NOT NULL ,
    [Validation Number] [int] NOT NULL ,
    [Data Table Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Field Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Test Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS _
     NOT NULL CONSTRAINT [DF_Unit Test Step Validation_test Type] DEFAULT _
     ([dbo].[DefaultReferenceDataItem]('Unit Test Comparison')),
    [Field Comparison] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Where clause] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Username] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS _
    NOT NULL CONSTRAINT [DF_Unit Test Step Validation_Username] DEFAULT (suser_sname()),
    CONSTRAINT [PK_Unit Test Step Validation] PRIMARY KEY  CLUSTERED 
    (
        [Test Name],
        [Step Number],
        [Validation Number]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

The types of validation that can be performed are:

• 'Exists' - There is one or more records in the data table that has the where clause
• 'Not exists' - There are no records in the named data table that match the where clause
• 'Equals' - The field in the data table row that matches the where clause equals the field comparison value
• 'Less Than' - as above but less than the comparison
• 'More Than' - as above but greater than the comparison
• 'Not Equals' - as above but does not equal the comparison

The section of the unit test process that performs these valuation tests is as follows:

              DECLARE @ValidationNumber int,
                      @DataTableName [varchar] (220) ,
                      @FieldName [varchar] (220),
                      @TestType [varchar] (50)  ,
                      @FieldComparison [varchar] (2000),
                      @WhereClause [varchar] (200),
                      @ValidationCommand [nvarchar] (3000) ,
                      @xtype tinyint 

              -- Otherwise execute all the post-step tests in turn
              DECLARE UnitTestValidationCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
              SELECT [Validation Number],
                     [Data Table Name],
                     [Field Name] ,
                     [Test Type],
                     [Field Comparison],
                     [Where clause]
                FROM [Unit Test Step Validation]
               WHERE [Test name] = @TestName
                 AND [Step Number] = @Step_Number
               ORDER BY [Validation Number]


              OPEN UnitTestValidationCursor

              FETCH NEXT FROM UnitTestValidationCursor INTO @ValidationNumber ,
                      @DataTableName ,
                      @FieldName ,
                      @TestType,  
                      @FieldComparison ,
                      @WhereClause

              WHILE @@FETCH_STATUS = 0
                 BEGIN

                   -- Create the SQL to run the test
                   SET @ValidationCommand = 'SELECT @rows = _
                   count(1) FROM [' + @targetdatabase +  ']..' + @DataTableName + _
                            ' WHERE ' + @Whereclause

                   IF (@TestType != 'Not Exists' AND  @TestType != 'Exists') 
                      BEGIN
                         -- Get the data type of the field to compare to...
                         SELECT @xtype = ISNULL(xtype,0)
                           FROM syscolumns
                          WHERE OBJECT_NAME([ID]) = @DataTablename
                            AND [name] = @Fieldname

                         SET @ValidationCommand = @ValidationCommand + _
                             ' AND '  + @fieldname + '  '

                         IF @TestType = 'Equals'
                            SET @ValidationCommand = @ValidationCommand + ' = '

                         IF @TestType = 'Greater Than'
                            SET @ValidationCommand = @ValidationCommand + ' > '

                         IF @TestType = 'Less Than'
                            SET @ValidationCommand = @ValidationCommand + ' < '

                         IF @TestType = 'Like'
                            SET @ValidationCommand = @ValidationCommand + ' LIKE '

                         IF @TestType = 'Not Equals'
                             SET @ValidationCommand = @ValidationCommand + ' != '
                        
                         --characters (TYPE = 231, 175) NEED QUOTES...
                         if @xtype in (231,175)
                             SET @ValidationCommand = @ValidationCommand + _
                                 dbo.quotestring(@fieldcomparison)
                         else if @xtype = 61
                            SET @ValidationCommand = @ValidationCommand + _
                                dbo.quotedate(@fieldcomparison)
                         else
                            SET @ValidationCommand = @ValidationCommand + @fieldcomparison 
                          
                      END

                   -- Execute the test
                   IF @ValidationCommand != ''
                     begin
                       set @validationcommand = _
                       dbo.ReplaceUnitTestvariablesWithValues_
                           (@TestName, @TargetDatabase, @validationcommand)

                       EXEC sp_executesql  @validationcommand, 
                                      N'@rows int output',
                                      @rows = @tmprows OUTPUT

                       select @tmpErr = isnull(@@error,0) 
                     end

2. The Data Model

The data model is an additional property of each database that specifies which type of database it is. For example, if a company has one database type that is used to record airline schedules and another that records staff details, the tests that make sense in the former will not run sensibly in the latter. Therefore, a table exists ([Production databases]) which maps each database to the data model that the database is an instance of:

CREATE TABLE [Production Databases] (
    [Server name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Database name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Model Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS _
     NOT NULL CONSTRAINT [DF_Production Databases_Model name] _
     DEFAULT ([dbo].[DefaultReferenceDataitem]('Database Model')),
    CONSTRAINT [PK_Production Databases] PRIMARY KEY  CLUSTERED 
    (
        [Server name],
        [Database name]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

Example

The following is a really simple example to get the idea across:

In our model database, we have a table called Fund which has two fields: PriceRound and UnitRound which are defined as INT and we want to add a test row that has a unit round of 4 and a price round of 2.

Script to Set Up the Unit Test

------------------------------------------------------------------
-- fund class rounding unit test
-- Purpose:
-- Comments:
-- Author:
------------------------------------------------------------------
declare @test_name char(20)

select @test_name = 'FundClassRounding'

delete [Unit test Definition]
where [test name] = @test_name

insert into [Unit test Definition] ([test name], [description]) _
             values (@test_name, 'FundClass table rounding test')

-- Remove any existing initialisation for this test
delete [Unit test Initialise]
where [Test Name] = @test_name

-- Add steps to delete any funds called "Duncan%"
insert into [Unit Test Initialise] values _
(@test_name, 2, 'delete [dbo].[Fund] where fund LIKE ' + dbo.quotestring('Duncan%'))
insert into [Unit Test Initialise] values _
(@test_name, 1, 'delete [dbo].[FundClass] where fundclass LIKE ' + dbo.quotestring('Duncan%'))

insert into [Unit Test Initialise] values (@test_name, 3, 'insert into [dbo].[Fund] _
(FundName, Fund, Parent) VALUES (' + dbo.quotestring('Duncan') + ',' + _
dbo.quotestring('Duncan') + ',' + dbo.quotestring('Duncan') +  ')' ) 
insert into [Unit Test Initialise] values (@test_name, 4, 'insert into _
[dbo].[FundClass] (FundClass, Fund) VALUES (' + dbo.quotestring('Duncan') + ',' + _
dbo.quotestring('Duncan') + ')' ) 

-- Add the individual test steps...
delete [Unit test step]
where [Test Name] = @test_name

insert into [Unit Test Step] values (@test_name, 1, _
              'Update [dbo].[FundClass] set UnitRound = 2, _
PriceRound = 4 where FundClass = ' + dbo.quotestring('Duncan') , 1, suser_sname() )

-- Add the post step tests
delete [Unit Test Step Validation]
where [Test Name] = @test_name

insert into [Unit Test Step Validation] values (@test_name, 1, 1, '[dbo].[FundClass]', _
'[UnitRound]', 'Equals', '2', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())
insert into [Unit Test Step Validation] values (@test_name, 1, 2, '[dbo].[FundClass]', _
'[PriceRound]', 'Greater Than', '3', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())
insert into [Unit Test Step Validation] values (@test_name, 1, 3, '[dbo].[FundClass]', _
'[PriceRound]', 'Less Than', '5', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())
insert into [Unit Test Step Validation] values (@test_name, 1, 4, '[dbo].[FundClass]', _
'[PriceRound]', 'Less Than', '2', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())

-- Remove any existsing cleanup steps for this test
delete [Unit test Teardown]
where [Test Name] = @test_name

-- Add steps to delete any funds called "Duncan%"
insert into [Unit Test Teardown] values (@test_name, 1, _
'delete [dbo].[FundClass] where fundclass LIKE ' + dbo.quotestring('Duncan%'))
insert into [Unit Test Teardown] values (@test_name, 2, _
'delete [dbo].[Fund] where fund LIKE ' + dbo.quotestring('Duncan%'))

Example Results

Test name	Step Number	Validation Number	Run Start	Run End	Error Number	Version	Result	Username
FundClassRounding	1	0	2007-01-22 12:00:02	2007-01-22 12:00:03	0	4.36.0	The query executed successfully	jonesde
FundClassRounding	1	1	2007-01-22 12:00:03	2007-01-22 12:00:03	0	4.36.0	Validation success : Equals	jonesde
FundClassRounding	1	2	2007-01-22 12:00:03	2007-01-22 12:00:03	0	4.36.0	Validation success : Greater Than	jonesde
FundClassRounding	1	3	2007-01-22 12:00:03	2007-01-22 12:00:04	0	4.36.0	Validation success : Less Than	jonesde
FundClassRounding	1	4	2007-01-22 12:00:04	2007-01-22 12:00:04	0	4.36.0	Validation failure: Less Than	jonesde

Further Development or Comments?

The full source code for the current implementation of the unit test framework is attached to this article. I'd be interested to hear if you have any suggestions for further development or improvements.

History

• 25^th September, 2014: Initial version