Friday, February 27, 2009

It's True (And Not False): Assert Equality in TSQLUnit

When working with xUnit style frameworks like NUnit, it is generally expected to find support for assertions. Asserts are an indispensable tool in any testing process. Unit testing at its core is simply verifying whether something has occurred or not occurred i.e. checking if the state has changed marking it as either true or false. Built-in assert syntax provides programmers a means to perform this level of testing repeatedly and consistently. One of the more common assertions is to compare the values of two items, such as variables or objects, sometimes referred to as equality asserts. As an example, here is C# test code using NUnit to test a method that simply adds two arguments:
// some static class
public static int Add(int firstItem, int secondItem)
return firstItem + secondItem;

public void Verify_sum_of_5_and_3_equals_8()
Assert.AreEqual(Add(5,3), 8);
Imagine my disappointment to discover the absence of equality assert functionality in TSQLUnit. The framework inexplicably does not have any native support for them. (Perhaps this might be because the project has not been active for a very long time.) Instead, you have to construct your own tailor-made assertions outside of the framework itself.

While using TSQLUnit without the aid of asserts, a definite pattern emerged as tsql plumbing code rapidly began to replicate in numerous tests. Here is an example of a unit test, plagued with the same identical code found in multiple places, whose purpose is to verify whether a 'ProductNumber' column has been successfully updated:
create procedure dbo.ut_CanUpdateProductNumber
declare @expectedProductNumber varchar(40),
@actualProductNumber varchar(40),
@currentAccountID varchar(16)

set @expectedProductNumber = '1234567890'

-- find a account to test
select top 1 @currentAccountID=AccountNbr
from dbo.Account

/* RUN TEST */
-- update product number
update dbo.Account
set ProductNumber = @expectedProductNumber
where AccountNbr = @currentAccountID

-- get updated product number
select @actualProductNumber=l.ProductNumber
from dbo.Account l
where l.AccountNbr = @currentAccountID

if @actualProductNumber != @expectedProductNumber
exec tsu_failure 'The product number is not the same.'
print 'Expected: ' + @expectedProductNumber
print 'Actual: ' + @actualProductNumber

The duplicated code is the conditional 'if' block at the end of the stored procedure where the assertion is executed using 'tsu_failure', the obligatory proc call to the TSQLUnit framework that transforms your tsql code into a real live unit test. All test sprocs are built around this critical function. Unfortunately, 'tsu_failure' does not handle actual comparisons between two values, but only after the comparison has been made. It was not designed to recognize when value comparisons are useful or required. Instead, the surrounding test code is responsible for making that evaluation, in this case, using a custom conditional statement not originating from any TSQLUnit function.

In addition, being accustomed to seeing in NUnit test messages that display the detailed results of value comparisons (i.e. expected against actual), print statements were added to the test procs to simulate that same text. For example, the following is what would be shown if the aforementioned test were to fail:
The product number is not the same.
Expected: 1234567890
Actual: 0987654321
Although a welcomed improvement in the feedback provided by the test runner's results, I found myself repeatedly injecting that same structure over and over in numerous other tests.

When this form of repetition occurs, a strategy can be adopted of either (1) continuing copying and pasting code, (2) using code generation, or (3) formulating and developing some reusable code component to manage the duplication. With # 1 and # 2 being obvious maintenance sinkholes draining away any value earned from the test code, implementing # 3 was a more sensible choice.

To fight off test code rot, the 'if' block was refactored and encapsulated into a separate, shareable stored procedure (think Extract Method). Nothing extravagant but quite effective:
create procedure dbo.tsux_AssertAreEqual
/* This is an extension to the TSQLUnit framework. */
@expected varchar(8000),
@actual varchar(8000),
@failMessage varchar(255)
if @actual != @expected
exec tsu_failure @failMessage
print 'Expected: ' + @expected
print 'Actual: ' + @actual
Calling this new proc provides the familiar, sought-after "Assert.AreEqual" functionality found in NUnit and in a lot of other test frameworks. The old 'if' block in the original test was subsequently replaced with the new assert proc:
create procedure dbo.ut_CanUpdateProductNumber
/*...unaltered code...*/

exec dbo.tsux_AssertAreEqual
'The product number is not the same.'

Now that we have a general utility assert sproc for the varchar type, we still have other data types, including int and datetime, that can also benefit from assertions of their own. Since TSQL does not support a flexible language feature like C# type generics for its stored procedures, creating sprocs for each data type is the only clear option to expand this functionality beyond vachar:
I can understand why the creator of TSQLUnit might have not initially built asserts into the framework since it requires building one for each every kind of data type in TSQL. Therefore, in its place, the burden falls on the user (i.e. me) to add additional asserts to the test code base as the need arises.

One kind of assert involving condition testing that might be interesting to implement but I am uncertain if it is remotely doable is this:
set @sqlConditionToEvaluate = (@expectedColor = 'BLUE'
AND @expectedSize = 23 OR StartDate between '1/1/11' and '2/2/22')
exec tsux_AssertIsTrue(@sqlConditionToEvaluate)
or more concisely:
exec tsux_AssertIsTrue(@expectedColor = 'BLUE'
AND @expectedSize = 23 OR StartDate between '1/1/11' and '2/2/22')
Maybe this could be achieved using dynamic sql and with storing each condition to verify within a 'TABLE' data type variable (functioning as an array) that can be looped checking each one to be true or false. However, implementing complex asserts to this extreme extent is a strong indication that TSQLUnit might no longer conceivably be the appropriate tool to write unit tests. It might be preferable to consider alternate unit testing frameworks that operate entirely outside of the database using a language other than TSQL that is better equipped for elaborate conditions and logic flow.

1 comment:

joshilewis said...

I had the same issue, except in my case I want to compare two tables for equal content.

The way I manage to do this: in my test suite setup sproc, I create two identical tables, Expected and Actual. (The transactionality of TSQLUnit will clean em up for me.) Inside the test (as part of the 'Arrange' section) I manually insert data into the Expected table.

In this case I'm testing a UDF, so I select from the UDF into the Actual table. I then call another sproc, (effectively part of my framework), which compares the contents of Expected and Actual. This comparison is done using EXCEPT and UNION ALL clauses. This sproc is called 'AssertTablesCongruent'. This sproc calls tsu_failure directly if need be.

(One has to use 'real' tables for Expected and Actual because you can't pass table variables to a sproc or UDF).

Email me if you want more details.