Well-written unit tests should succeed only when the module being tested meets the requirements completely.If the test fails, it can do much better than just indicate failure. If the test has managed to provide a clear and comprehensive report of what is wrong, that can save us a lot of time troubleshooting.
We shall see a few examples soon.
This post continues the series on unit testing, the previous posts are
How to Benefit from Unit Testing T-SQL: choosing what not to test
How to Benefit from Unit Testing T-SQL: choosing what to test
How to Benefit from Unit Testing T-SQL. Part One.
How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests
Surely we can come up with many different scenarios, but in my practice we almost always want to do one and the same thing: capture all the result sets and verify everything. "Everything" means this:Column names and types should be matched precisely in every result set - any change in them may break some code. Order of columns must stay the same.
Similarly, all the values in all result sets should match as well.
Of course, we need to be very specific when we say "all the values in all result sets should match". For example, if we are not required to order the results, they can be in any order, and our matching must be able to match unordered result sets.
Another example: if we aggregate floating point numbers, we can get slightly different results, and our matching must tolerate minor differences.
Having said all these reservations, my main point remains the same: if the requirements are specific, our testing must be just as specific. For example, checking row count and nothing else makes sense only if the only requirement is to return a specific number of rows. Similarly, checking that the result set is not empty and nothing else makes sense only if the only requirement is to return a not empty result set.
Saving the full description of a result set in a file
Suppose that our test returns the following result set:
Free Donuts in Kitchen, Hurry up! 2010-10-07 07:12:00.000
East side printer jammed 2010-10-07 09:21:00.000
TPS Reports due in 10 minutes 2010-10-08 11:50:00.000
My testing tool saves column names, types, and all the data returned in this xml file:
<?xml version="1.0" encoding="utf-8"?>
<object name1="result set" name2="">
<column name="subject" value="varchar(50)" />
<column name="sentat" value="datetime" />
<column name="subject" value="Free donuts in kitchen area, hurry up!" />
<column name="sentat" value="10/7/2010 07:12" />
<column name="subject" value="East side printer jammed" />
<column name="sentat" value="10/7/2010 09:21" />
<column name="subject" value="TPS Reports due in 10 minutes" />
<column name="sentat" value="10/8/2010 11:50" />
Exposing Failure in a User-Friendly Way
If at some later time our test fails, we want to see the whole picture of what exactly failed, and what returned as expected. There are quite a few utilities which present differences between text files in an easy-to-interpret way.Let us use one of them.
If the test fails, let us output the actual results into an XML file, and use TortioseMerge, which gives a great visual presentation of the differences. For example, the following screenshot is very easy to interpret: we have correct data in the wrong order:
Note that if we were checking only the row count, we would not detect the discrepancies in this case. If we were checking only some value in the first row only, it is just as easy to come up with a scenario when we would fail to detect the discrepancies.
We really want to verify everything.
If we used NUnit assertions to verify all the values individually, we would definitely detect a failure, but we would get less useful information, only one first discrepancy. Similarly, if we were checking all the values individually, output failures, but continue to check, we would be overwhelmed with too many details in the output looking like this, with every value not matching.
Row 0, Column 0,
Expected: Free donuts in kitchen area, hurry up!
Actual: TPS reports due in 10 minutes
Row 1, Column 0,
Expected: East side printer jammed
Actual: Free donuts in kitchen area, hurry up!
Row 2, Column 0,
Expected: TPS reports due in 10 minutes
Actual: East side printer jammed
Based on such too detailed information, it is very difficult to see the forest behind the trees, to detect the pattern in the failures, to quickly understand what went wrong.
As we have seen, if our unit test fails, it is very important to present all the information about the discrepancies in an easy to understand way.
The next post in this series is: Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation