Unit Testing Databases with tSQLt Part 3 – testing table constraints

by Greg M Lucas on 29 October 2011

In Part 2, we looked at the first couple of tests for a new table; that it exists and with the correct structure (columns, data types etc). In this part we are going to write the rest of the tests we need to cover this table effectively. We will be validating the primary key, the IDENTITY column and the default constraints that we will be adding to this table. I will assume that you reading this post after Part 2 so you’ll know that we are still writing tests for a table to store SQL Server exceptions as part of the Log4TSql framework.

The last test we created, “[ExceptionTableTests].[test_Exception_metadata]” validated most of the bare Exception table – with the exception of the IDENTITY column. Now, you might argue that there’s not a lot a point testing something like this, aren’t we safe to assume that this works? Well yes, and no. When defining the IDENTITY property, you still have to specify seed and increment values and if the these matter to your application, then you need to test them – you might want odd numbers only for example.

Here is a quick reminder of what our Exception table looks like

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]

We have already created a class/schema to hold our tests so we don’t need to do it again but here’s the command anyway as a reminder

EXEC tSQLt.NewTestClass 'ExceptionTableTests';

Testing the IDENTITY property on a column

So to test that ExceptionId auto-increments as expected, we need to insert two rows, capture both new IDENTITY values and compare them. You might notice that in this test procedure I am only populating the NOT NULL columns – for this test I’m not interested in what goes in to the other columns. You might be thinking that it would make more sense to populate the other columns with some value or other just in case we make any of the remaining columns NOT NULL. I’m certainly guilty of that “just in case…, at some unknown point in the dim and distant future…, what if…” thinking but that’s not very agile. Think: Simplicity – the art of maximizing the amount of work not done. Even if I ever do make one of those columns NOT NULL, there is also the possibility that I might also introduce a CHECK constraint which any default value I supply in this test might still fail. No, I think that the best way is to write the minimum code that works now and refactor the test in the future if I ever need to.

CREATE PROCEDURE [ExceptionTableTests].[test_Exception_ExceptionId_auto_increments]
AS
BEGIN
    --! Setup
    DECLARE @firstId int, @actualId int , @expectedId int;

    --! Exercise
    BEGIN TRY
        INSERT [log4].[Exception]
        (
          [SystemDate]
        , [UtcDate]
        )
        VALUES
        (
          '20110101'
        , '20110101'
        );

        SET @firstId = SCOPE_IDENTITY();

        INSERT [log4].[Exception]
        (
          [SystemDate]
        , [UtcDate]
        )
        VALUES
        (
          '20110101'
        , '20110101'
        );

        SET @actualId = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @errorMessage nvarchar(1000) = ERROR_MESSAGE();
        --! If the column has IDENTITY it's probably also NOT NULL so we'll get @@ERROR 515
        --! before we can do the Assert so hide that error since we're only interested in
        --! testing for auto-increment
        IF ERROR_NUMBER() != 515
            RAISERROR(@errorMessage, 16, 1);
    END CATCH

    --! We expect the ID to increment by 1
    SET @expectedId = @firstId + 2;

    --! Assert
    IF @firstId IS NULL OR @actualId IS NULL
        EXEC tSQLt.Fail 'ExceptionId auto increment not implemented (values not populated)';
    ELSE
        EXEC tSQLt.AssertEquals
              @expectedId
            , @actualId
            , 'ExceptionId auto increment not implemented (values do not increment as expected)';
END

There are several other points I want to make about this test. It is most likely that a column with the IDENTITY property defined has probably also been declared as NOT NULL. In which case, both INSERTs will fail with @@ERROR 515. However, I just want to test whether this column auto increments so I hide that error within the catch block so that I can report a useful reason for failure. Obviously, if I get any other error I want to bubble that back up. Another point here is that we have two assertions, the first checks that we have something for each ID – it is this assertion that will fail if the column is NOT NULL and has no IDENTITY. The assertion is that the second ID has incremented correctly.p>

You’ll notice that if the first assertion fails, we call tSQLt.Fail – this generates  a test failure (rather than any error) and can be used to mark a test as failed if there is no suitable tSQLt.Assert… method.  When using tSQLt.Fail, it is important to supply a useful failure message so anyone looking at the test results can understand why the test failed.

Since I’ve already defined the IDENTITY column properly on the Exception table, I’ve written this deliberately to fail in the first instance just to demonstrate use of the optional failure message third parameter (SET @expectedId = @firstId + 2 should be ... + 1).

As a general rule, a single test should not contain more than one assertion but if you do have more, specifying a failure message for each assertion can help you identify the exact point of failure in a multi-assertion test failed. This is how the the Official tSQLt User Guide describes the method signature for AssertEquals .

Syntax
tSQLt.AssertEquals [@expected = ] ‘expected value’
, [@actual = ] ‘actual value’
[, [@message = ] ‘message’ ]
Arguments
[@expected = ] ‘expected value’ – The expected value for the test. @expected is SQL_VARIANT with no default.
[@actual = ] ‘actual value’ – The actual value resulting from processing during the test. @actual is SQL_VARIANT with no default.
[@message = ] ‘message’ – Optional. String containing an additional failure message to be used if the expected and actual values are not equal. @message is NVARCHAR(MAX) with no default.

So, we are now ready to run our tests using the following command to get the results below:

EXEC tSQLt.RunTestClass 'ExceptionTableTests';
[ExceptionTableTests].[test_Exception_ExceptionId_auto_increments] failed: ExceptionId auto increment not implemented (values do not increment as expected) Expected: <4> but was: <3>

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

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

Notice how the the “[ExceptionTableTests].[test_Exception_ExceptionId_auto_increments] failed:...“ failure message uses the custom failure message we specified in the test procedure so we know which assertion actually failed.

Testing a PRIMARY KEY

The next thing we want to do is to ensure that the uniqueness of ExceptionId is enforced. We don’t care whether this is done via a PRIMARY KEY, UNIQUE constraint or an index declared as unique. They all create a unique index under the hood anyway. What we’re interested in here is that ExceptionId is guaranteed unique, not how that is implemented. Uniqueness could be enforced by a trigger for all we care – although I can’t for the life of me think why you’d want to do that :-)

In this test, as the ExceptionId column has the IDENTITY property defined we need to turn IDENTITY_INSERT on for the duration of the test. We’re expecting to get an exception so make sure that we turn IDENTITY_INSERT off again outside the scope of the failing code.

CREATE PROCEDURE [ExceptionTableTests].[test_Exception_ExceptionId_is_unique]
AS
BEGIN
    DECLARE @expectedError int = 2627;
    DECLARE @actualError int = 0;
    DECLARE @newId int = COALESCE((SELECT MAX(ExceptionId) FROM [log4].[Exception]) + 99, 999);

    SET IDENTITY_INSERT [log4].[Exception] ON;

    BEGIN TRY
        INSERT [log4].[Exception]
        (
          [ExceptionId]
        , [UtcDate]
        , [SystemDate]
        )
        VALUES
        (
          @newId
        , '20110101'
        , '20110101'
        )
        ;

        INSERT [log4].[Exception]
        (
          [ExceptionId]
        , [UtcDate]
        , [SystemDate]
        )
        VALUES
        (
          @newId
        , '20110101'
        , '20110101'
        )
        ;

    END TRY
    BEGIN CATCH
        SET @actualError = ERROR_NUMBER();
    END CATCH;

    SET IDENTITY_INSERT [log4].[Exception] OFF;

    EXEC tSQLt.assertEquals
              @expectedError
            , @actualError
            , '[log4].[Exception] table should not allow duplicate ExceptionId';

    IF (SELECT COUNT(1) FROM [log4].[Exception] WHERE ExceptionId = @newId) > 1
        EXEC tSQLt.Fail '[log4].[Exception] table should not allow more than one row with the same ExceptionId';
END

What we want to happen is that we get @@ERROR 2627 Violation of PRIMARY KEY constraint 'PK_Exception'. Cannot insert duplicate key in object 'log4.Exception' but we also assert that there is no possibility of ending up with duplicate rows. This test yields the following failure message

[ExceptionTableTests].[test_Exception_ExceptionId_is_unique] failed: [log4].[Exception] table should not allow duplicate ExceptionId Expected: <2627> but was: <0>

Adding a PRIMARY KEY to the Exception table like this will naturally result in this test passing

ALTER TABLE [log4].[Exception]
ADD CONSTRAINT [PK_Exception] PRIMARY KEY NONCLUSTERED ([ExceptionId])
+---------------------+
|Test Execution Sumary|
+---------------------+

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

Testing a DEFAULT constraint

The final thing we’re going to do is assert that the UtcDate and SystemDate columns default to the correct values on INSERT. I suppose, it would be sufficient to confirm that the resulting column value is not NULL but as we have two columns with different DEFAULT date definitions we actually assert that the column value is between the appropriate start and end times. I know that this approach won’t differentiate properly in the UK in winter when GETDATE() and GETUTCDATE() will return the same dates but I can’t off the top of my head think of an easy way round this – suggestions welcome! There is another test like this called test_Exception_UtcDate_has_default.

CREATE PROCEDURE [ExceptionTableTests].[test_Exception_SystemDate_has_default]
AS
BEGIN
	DECLARE @startDate datetime = GETDATE();
	DECLARE @exceptionId int;

	INSERT [log4].[Exception]
	(
	  [UtcDate]
	)
	VALUES
	(
	  '20110101'
	);

	SET @exceptionId = SCOPE_IDENTITY();

	DECLARE @actualDate datetime = (SELECT SystemDate FROM [log4].[Exception] WHERE ExceptionId = @exceptionId)

	DECLARE @endDate datetime = GETDATE();

	--! Assert
	IF @actualDate NOT BETWEEN @startDate AND @endDate
		EXEC tSQLt.Fail 'Default constraint not implemented on SystemDate';
END

So we end up with two failing tests test_Exception_SystemDate_has_default and test_Exception_UtcDate_has_default which the following code will pass as per the results below.

ALTER TABLE [log4].[Exception]
    ADD CONSTRAINT [DF_Exception_SystemDate] DEFAULT GETDATE() FOR [SystemDate];

ALTER TABLE [log4].[Exception]
    ADD CONSTRAINT [DF_Exception_UtcDate] DEFAULT GETUTCDATE() FOR UtcDate;
+---------------------+
|Test Execution Sumary|
+---------------------+

|No|Test Case Name                                                    |Result |
+--+------------------------------------------------------------------+-------+
|1 |[ExceptionTableTests].[test_Exception_ExceptionId_auto_increments]|Success|
|2 |[ExceptionTableTests].[test_Exception_ExceptionId_is_unique]      |Success|
|3 |[ExceptionTableTests].[test_Exception_metadata]                   |Success|
|4 |[ExceptionTableTests].[test_Exception_SystemDate_has_default]     |Success|
|5 |[ExceptionTableTests].[test_Exception_table_exists]               |Success|
|6 |[ExceptionTableTests].[test_Exception_UtcDate_has_default]        |Success|
-----------------------------------------------------------------------------
Test Case Summary: 6 test case(s) executed, 6 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

One last thing before I wrap this session up. As we start to increase the number of tests you are not always going to want to run all the tests every time. My typical approach is to write a failing test, run just that test, write enough code to pass the test, run that one test again to prove it. Only then do I want to re-run all tests (or at least all tests in that class) to prove that the code I’ve just added or altered hasn’t caused any other tests to fail. So far, with only half a dozen tests completed that might not make so much sense but imagine if you have hundred’s of tests, many with lots of data setup – once it takes more than a couple of seconds to run all my tests I start to get impatient. So the ability to run tests selectively – either singly or by class starts to make a lot more sense. The procedure that you can use to run just a single test is tSQLt.Run and you just pass it the schema name and test name in a single string like this:

tSQLt.Run '[ExceptionTableTests].[test_Exception_SystemDate_has_default]';

tSQLt.Run actually has several uses as you can see in this extract from the tSQLt User Guide:

Syntax
tSQLt.Run [ [@testName = ] ‘test name’ ]
Arguments
[@testName = ] ‘test name’
Optional. The name of a test case, including the schema name to which the test case belongs. For example ‘MyTestClass.[test employee has manager]‘. If not provided, the test name provided on the previous call to tSQLt.Run within the current session is used. If no test cases have been run previously in the current session, then no test cases are executed. Optionally, ‘test name’ may be the name of a test class. In which case all tests on that class are executed.

In this post, we learnt about testing a column’s IDENTITY property, PRIMAY or UNIQUE KEY and DEFAULT constraints . In Part 4, we’ll start looking at the first tests for the stored procedure that is responsible for populating our completed Exception table.

Log4TSql is an open source logging framework for SQL Server 2005+ written by Greg M Lucas on behalf of data-centric solutions ltd and licensed for public use under the GNU Lesser General Public License. You can download the latest version of this library along with all the source code and unit tests from sourceforge. Please feel free to use and modify the library as you wish under the terms of the license. I am always interested in hearing how other people or organisations have put this framework to use.

Comments on this entry are closed.

Previous post:

Next post: