Unit Testing Databases with tSQLt Part 8 – testing string searches in the WHERE clause

by Greg M Lucas on 16 February 2012

In Part 7, we looked at how to write tests to confirm that parameterised start and end dates were correctly applied in a WHERE clause. In this post we will extend that SELECT procedure further by writing more tests for the WHERE clause, specifically searching the contents of a string, and validating how default values are applied to those search predicates in the event of not being supplied by the caller. We will also be checking that results are as expected when multiple filter conditions are supplied together.

Just to recap, we have an [Exception] table which is designed to capture the details of any SQL Server errors caught within a TRY… CATCH… block. The table looks like this and and we’ve already written and tested a basic ExceptionReader procedure which returns a date-filtered selection of these columns in the correct order (See Part 7 for the current version).

CREATE TABLE [log4].[Exception]
(
  [ExceptionId]        int                  NOT NULL    IDENTITY( 1,1 )
, [UtcDate]            datetime             NOT NULL
, [SystemDate]         datetime             NOT NULL
-----------------------------------------------------------------------
, [ErrorContext]       nvarchar    ( 512 )  NOT NULL
, [ErrorNumber]        int                  NOT NULL
, [ErrorSeverity]      int                  NOT NULL
, [ErrorState]         int                  NOT NULL
, [ErrorProcedure]     nvarchar    ( 128 )  NOT NULL
, [ErrorLine]          int                  NOT NULL
, [ErrorMessage]       nvarchar    ( max )  NOT NULL
-----------------------------------------------------------------------
, [SessionId]          int                  NOT NULL
, [ServerName]         nvarchar    ( 128 )  NOT NULL
, [DatabaseName]       nvarchar    ( 128 )  NOT NULL
, [HostName]           nvarchar    ( 128 )  NOT NULL
, [ProgramName]        nvarchar    ( 128 )  NOT NULL
, [NTDomain]           nvarchar    ( 128 )  NOT NULL
, [NTUsername]         nvarchar    ( 128 )  NOT NULL
, [LoginName]          nvarchar    ( 128 )  NOT NULL
, [OriginalLoginName]  nvarchar    ( 128 )  NOT NULL
, [SessionLoginTime]   datetime             NULL
)

In an ideal world, this table should be more or less empty but on a less well tested system, or during development or testing activities this table may contain a lot of rows and although we already have the facility to limit results by date and time, there is also a requirement to offer additional filter options. The user story we are going to focus on in this post is “As a database tester, I want to be able to filter results based on the full or partial name of the procedure that generated the error.

As the user in this story is a “database tester”, we can safely assume a basic familiarity with T-SQL and the use of the “%” wild card. Otherwise we might have built some sort of “begins with”, “ends with”, “contains” or “exact match” search functionality into the procedure itself. This requirement suggests that [ExceptionReader] will need a new parameter to search error procedure like this:

CREATE PROCEDURE [log4].[ExceptionReader]
(
  @StartDate                 datetime            = NULL
, @EndDate                   datetime            = NULL
, @TimeZoneOffset            smallint            = NULL
, @ErrorProcedure            nvarchar   ( 128 )  = NULL
)

The interesting thing here is how we go about testing this, or more specifically, what do we test. We know that “begins with”, “ends with”, “contains” and “exact match” form our possible search conditions so we’re most likely to be using a “LIKE” in the WHERE clause. There is an argument in TDD that suggests we shouldn’t waste time testing how “LIKE” itself works – we should be safe to assume that Microsoft has got that covered. However, what we may need to test is what, if anything, happens to a supplied input before it is actually used as a predicate. As long as we’re not changing the input value, we only really need to test one LIKE condition – “contains” in this case.

Our first (or only) test

In this test (below), we’re going to add some rows to [Exception] each with a different procedure name string against which we can search. We’re not interested in testing dates and times here so we just need to ensure that they are all within a known range – in fact we can even use the same date/time for each exception row.

We depend on [ExceptionReaderTests].[SetUp] to create our standard [expected] and [actual] tables for this test class and we’re going to re-use our test data builder and “Make” procedures. These patterns were all first introduced in Part 6. In this test, we are going to search for strings containing “ging” so passing “%ging%” to @ErrorProcedure on the call to [ExceptionReader]. With this filter, we would expect only rows B, C, E, F and H to be returned. We call [Make_ExceptionReader_ExceptionRowAndResultRow] for rows B, C, E, F and H (which also calls [ExceptionBuilder] under the hood) to add these rows to both [Exception] and [expected] and we call [ExceptionBuilder] to add the other rows to just the [Exception] table. The rows will be added with the UTC Date that we specify and we don’t care about ExceptionId values for this test.

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_filters_results_on_procedure_contains]
AS
BEGIN
    --!
    --! For this test, we are only interested in validating the procedure
    --! search so make sure that all the dates are within range.
    --!
    DECLARE @UtcDate datetime; SET @UtcDate = '20111121 10:00:01';
    DECLARE @UtcDateStartRange datetime; SET @UtcDateStartRange = '20111121 10:00:00';
    DECLARE @UtcDateEndRange datetime; SET @UtcDateEndRange = '20111121 10:00:02';

    DECLARE @String_A varchar(500); SET @String_A = 'ginseng';
    DECLARE @String_B varchar(500); SET @String_B = 'ginger';
    DECLARE @String_C varchar(500); SET @String_C = 'ging';
    DECLARE @String_D varchar(500); SET @String_D = 'airegin';
    DECLARE @String_E varchar(500); SET @String_E = 'fred and ginger dance';
    DECLARE @String_F varchar(500); SET @String_F = 'fred and ginger sing';
    DECLARE @String_G varchar(500); SET @String_G = 'fred and wilma dance';
    DECLARE @String_H varchar(500); SET @String_H = 'danger gagging ginger';

    EXEC tSQLt.FakeTable 'log4', 'Exception';

    DECLARE @SearchString varchar(500); SET @SearchString = '%ging%';

--/////////////////////////////////////////////////////////////////////////////////////////////////
SetUp:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    --!
    --! Add all the source rows to Exception
    --! We call Make_ExceptionReader_ExceptionRowAndResultRow for rows we are expecting
    --! to actually see and just call ExceptionBuilder for rows we expect to be exluded
    --! from the result set.
    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_A
            , @UtcDate        = @UtcDate;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_B
            , @UtcDate        = @UtcDate;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_C
            , @UtcDate        = @UtcDate;

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_D
            , @UtcDate        = @UtcDate;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_E
            , @UtcDate        = @UtcDate;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_F
            , @UtcDate        = @UtcDate;

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_G
            , @UtcDate        = @UtcDate;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_H
            , @UtcDate        = @UtcDate;

--/////////////////////////////////////////////////////////////////////////////////////////////////
Exercise:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader
          @StartDate                = @UtcDateStartRange
        , @EndDate                  = @UtcDateEndRange
        , @TimeZoneOffset           = 0
        , @ErrorProcedure           = @SearchString
    ;

--/////////////////////////////////////////////////////////////////////////////////////////////////
Assert:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]';
END

Finally, we populate the [actual] table with the results from our [ExceptionReader] call then use [AssertEqualsTable] to compare the expected and actual results.

Oops! We broke it again!

My first attempt (below) to get [ExceptionReader] to pass this test actually caused a number of earlier tests to fail; as I’m sure you can see, this code will only work if a not-null value is supplied for @ErrorProcedure. Remember, one of our requirements is that we are able to call this with as little code as EXEC log4.ExceptionReader; so any NULL parameter values must not be allowed to restrict the number of rows returned by the WHERE clause. Obviously, with the code as it stands, if @ErrorProcedure is supplied as NULL (as it would be for the earlier tests), no rows will be returned as nothing will be “LIKE NULL”. So I need to handle that before I worry about testing the “procedure contains” logic. This was the first (failing) attempt:

    SELECT
          ExceptionId
        , UtcDate
        , SystemDate
        , ErrorNumber
        , ErrorContext
        , ErrorMessage
        , ErrorSeverity
        , ErrorState
        , ErrorProcedure
        , ErrorLine
        , SessionId
        , ProgramName
        , NTDomain
        , NTUsername
        , LoginName
    FROM
        [log4].[Exception]
    WHERE
        UtcDate BETWEEN @StartDate AND @EndDate
    AND
        ErrorProcedure LIKE @ErrorProcedure
    ORDER BY
        ExceptionId DESC

At the risk of banging an old drum, this is why test-driven development is so useful and why it is so important to run all tests regularly during development. The earlier that we can find out that a code change has broken some other piece of functionality, the fresher that change will be in our minds and the easier and quicker it will be for us to fix it. If we weren’t following TDD, it might be weeks before this break was picked up in QA and after spending potentially ages debugging code to identify a fix, without re-runnable unit tests, we would then have to spend even more time manually re-testing all the other behaviours before releasing the fix back into QA or production.

To solve the problem and get all the existing tests to pass again, in [ExceptionHandler] we just default any NULL @ErrorProcedure input to “%” like this:

    SET @ErrorProcedure = COALESCE(@ErrorProcedure, '%');

    SELECT
          ...
        , ...
    FROM
        [log4].[Exception]
    WHERE
        UtcDate BETWEEN @StartDate AND @EndDate
    AND
        ErrorProcedure LIKE @ErrorProcedure
    ORDER BY
        ExceptionId DESC

This change results in [Test_ExceptionReader_filters_results_on_procedure_contains] itself passing along with all the earlier tests passing again too. The fact that we now have code that modifies the value of @ErrorProcedure makes it more important that we test the other likely user inputs. I have supplied all the tests for this filter here:

You might think that all these additional tests are over-the-top and you could be right – in your shop. I’ve tried to show database unit testing “by the book” so that you can then decide how much or how little is right for you to implement on your projects.

Okay, maybe one more test…

So right now we have a number of tests against UTC Date and some separate tests against procedure name. What we haven’t tested is what happens when both criteria are supplied. To do this we’re going to take a copy of our “contains” test and modify it as follows: In the earlier test we were expecting only rows B, C, E, F and H and all rows had the same UTC date. In this test, each row gets a different UTC date (in sequence), and by setting our search time range to between 10:00:03 and 10:00:07 we should only expect to see rows C, E and F returned.

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_filters_results_on_procedure_and_date_range]
AS
BEGIN
    --!
    --! For this test, we want to test procedure and date range combined
    --! search so make sure that only selected matching rows have dates
    --! that are within range.
    --!
    DECLARE @UtcDate_A datetime; SET @UtcDate_A = '20111121 10:00:01';
    DECLARE @UtcDate_B datetime; SET @UtcDate_B = '20111121 10:00:02';
    DECLARE @UtcDate_C datetime; SET @UtcDate_C = '20111121 10:00:03';
    DECLARE @UtcDate_D datetime; SET @UtcDate_D = '20111121 10:00:04';
    DECLARE @UtcDate_E datetime; SET @UtcDate_E = '20111121 10:00:05';
    DECLARE @UtcDate_F datetime; SET @UtcDate_F = '20111121 10:00:06';
    DECLARE @UtcDate_G datetime; SET @UtcDate_G = '20111121 10:00:07';
    DECLARE @UtcDate_H datetime; SET @UtcDate_H = '20111121 10:00:08';

    DECLARE @UtcDateStartRange datetime; SET @UtcDateStartRange = '20111121 10:00:03';
    DECLARE @UtcDateEndRange datetime; SET @UtcDateEndRange = '20111121 10:00:07';

    DECLARE @String_A varchar(500); SET @String_A = 'ginseng';
    DECLARE @String_B varchar(500); SET @String_B = 'ginger';
    DECLARE @String_C varchar(500); SET @String_C = 'ging';
    DECLARE @String_D varchar(500); SET @String_D = 'airegin';
    DECLARE @String_E varchar(500); SET @String_E = 'fred and ginger dance';
    DECLARE @String_F varchar(500); SET @String_F = 'fred and ginger sing';
    DECLARE @String_G varchar(500); SET @String_G = 'fred and wilma dance';
    DECLARE @String_H varchar(500); SET @String_H = 'danger gagging ginger';

    EXEC tSQLt.FakeTable 'log4', 'Exception';

    DECLARE @SearchString varchar(500); SET @SearchString = '%ging%';

--/////////////////////////////////////////////////////////////////////////////////////////////////
SetUp:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    --!
    --! Add all the source rows to Exception
    --! We call Make_ExceptionReader_ExceptionRowAndResultRow for rows we are expecting
    --! to actually see and just call ExceptionBuilder for rows we expect to be exluded
    --! from the result set.
    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_A
            , @UtcDate        = @UtcDate_A;

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_B
            , @UtcDate        = @UtcDate_B;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_C
            , @UtcDate        = @UtcDate_C;

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_D
            , @UtcDate        = @UtcDate_D;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_E
            , @UtcDate        = @UtcDate_E;

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorProcedure = @String_F
            , @UtcDate        = @UtcDate_F;

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_G
            , @UtcDate        = @UtcDate_G;

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ErrorProcedure = @String_H
            , @UtcDate        = @UtcDate_H;

--/////////////////////////////////////////////////////////////////////////////////////////////////
Exercise:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader
          @StartDate                = @UtcDateStartRange
        , @EndDate                  = @UtcDateEndRange
        , @TimeZoneOffset           = 0
        , @ErrorProcedure           = @SearchString
    ;

--/////////////////////////////////////////////////////////////////////////////////////////////////
Assert:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]';
END

The final, fully tested version of [ExceptionReader] now looks like this:

CREATE PROCEDURE [log4].[ExceptionReader]
(
  @StartDate                 datetime            = NULL
, @EndDate                   datetime            = NULL
, @TimeZoneOffset            smallint            = NULL
, @ErrorProcedure            nvarchar   ( 128 )  = NULL
, @ErrorMessage              nvarchar   ( 500 )  = NULL
)

AS

BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;

    --! Working variables
    DECLARE @Error int; SET @Error = 0;

    --! Set defaults in absence of supplied values i.e. last seven days)
    --! If either date is NULL, we're going to work off UTC so set the
    --! timezone offset to zero if it hasn't been specified
    IF @EndDate IS NULL OR @StartDate IS NULL SET @TimeZoneOffset = COALESCE(@TimeZoneOffset, 0);
    IF @EndDate IS NULL SET @EndDate = GETUTCDATE();
    IF @StartDate IS NULL SET @StartDate = DATEADD(day, -7, @EndDate);

    --! If no time zone offset is supplied, use the difference between system time and UTC
    SET @TimeZoneOffset = COALESCE(@TimeZoneOffset,  DATEDIFF(hour, GETDATE(), GETUTCDATE()))
    SET @StartDate      = DATEADD(hour, @TimeZoneOffset * -1, @StartDate)
    SET @EndDate        = DATEADD(hour, @TimeZoneOffset * -1, @EndDate)

    --!
    --! We have to allow any search criteria to be NULL without that restring the
    --! number of matches returned so set default wildcards here.
    --!
    SET @ErrorProcedure = COALESCE(@ErrorProcedure, '%');
    SET @ErrorMessage = COALESCE(@ErrorMessage, '%');

    --!
    --! Return the required results
    --!
    SELECT
          ExceptionId
        , UtcDate
        , SystemDate
        , ErrorNumber
        , ErrorContext
        , ErrorMessage
        , ErrorSeverity
        , ErrorState
        , ErrorProcedure
        , ErrorLine
        , SessionId
        , ProgramName
        , NTDomain
        , NTUsername
        , LoginName
    FROM
        [log4].[Exception]
    WHERE
        UtcDate BETWEEN @StartDate AND @EndDate
    AND
        ErrorProcedure LIKE @ErrorProcedure
    AND
        ErrorMessage LIKE @ErrorMessage
    ORDER BY
        ExceptionId DESC

    SELECT @Error = @@ERROR;

    SET NOCOUNT OFF;

    RETURN(@Error);
END

In this post we looked at how to write tests to assert that a string search parameter is correctly used as a predicate and also testing the effect of combined search criteria. We also had a reminder that following good testing practice helps early identification of bugs in existing code introduced as part of new code changes. In the Part 9, we will look at writing some tests against a foreign key.

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: