// some static classImagine 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.
public static int Add(int firstItem, int secondItem)
{
return firstItem + secondItem;
}
[Test]
public void Verify_sum_of_5_and_3_equals_8()
{
Assert.AreEqual(Add(5,3), 8);
}
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_CanUpdateProductNumberThe 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.
/* UNIT TEST */
as
begin
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
/* ASSERT TEST */
if @actualProductNumber != @expectedProductNumber
begin
exec tsu_failure 'The product number is not the same.'
print 'Expected: ' + @expectedProductNumber
print 'Actual: ' + @actualProductNumber
end
end
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.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.
Expected: 1234567890
Actual: 0987654321
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_AssertAreEqualCalling 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:
/* This is an extension to the TSQLUnit framework. */
(
@expected varchar(8000),
@actual varchar(8000),
@failMessage varchar(255)
)
as
begin
if @actual != @expected
begin
exec tsu_failure @failMessage
print 'Expected: ' + @expected
print 'Actual: ' + @actual
end
end
create procedure dbo.ut_CanUpdateProductNumberNow 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:
/* UNIT TEST */
as
begin
/*...unaltered code...*/
/* ASSERT TEST */
exec dbo.tsux_AssertAreEqual
@expectedProductNumber,
@actualProductNumber,
'The product number is not the same.'
end
tsux_AssertDatesAreEqualI 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.
tsux_AssertIntsAreEqual
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'or more concisely:
AND @expectedSize = 23 OR StartDate between '1/1/11' and '2/2/22')
exec tsux_AssertIsTrue(@sqlConditionToEvaluate)
exec tsux_AssertIsTrue(@expectedColor = 'BLUE'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.
AND @expectedSize = 23 OR StartDate between '1/1/11' and '2/2/22')
Posts
