Testing From Requirements

Testing From Requirements
I have folks say to me frequently that they cannot begin testing until the coding has been completed. I agree that you cannot complete testing until the code is completed. However, you can begin testing even before the code has been started. In fact, all you really need to begin testing are the requirements.

I’m going to take a different approach on the newsletter this week. Instead of bringing up a topic and asking lots of questions, I’m going to walk you through an example of how you can test before code is written.

I’m going to use the AdventureWorks database for this example. I have a request for a report that will list the number of hires each month for the last twelve months. The report should break out all 12 months by department, and display the count for each month. Here’s a short example with two departments:

We will demonstrate writing tests in TSQL to start with, and perhaps move into NUnit at a later time. We’ll see how things go.

Before I can start getting hiring information from the HumanResources.Employee table I need to get 12 monthly time segments with the start and end date for each month. I clarified that the current month is to be excluded from the report. So, I need to return a table with twelve rows, a start date and an end date for each row.

If I were to return a table based on today’s date “12/16/2012” I could expect the following table using DateTime data type having the smallest precision of 3 Milliseconds:

I can create a Common Table Expression (CTE) returning the desired data as follows:

WITH Segments (SegmentStart, SegmentEnd)
AS
(
SELECT '2011-12-01 00:00:00.000' AS SegmentStart
, '2011-12-31 23:59:59.997' AS SegmentEnd
UNION ALL SELECT '2012-01-01 00:00:00.000', '2012-01-31 23:59:59.997'
UNION ALL SELECT '2012-02-01 00:00:00.000', '2012-02-29 23:59:59.997'
UNION ALL SELECT '2012-03-01 00:00:00.000', '2012-03-31 23:59:59.997'
UNION ALL SELECT '2012-04-01 00:00:00.000', '2012-04-30 23:59:59.997'
UNION ALL SELECT '2012-05-01 00:00:00.000', '2012-05-31 23:59:59.997'
UNION ALL SELECT '2012-06-01 00:00:00.000', '2012-06-30 23:59:59.997'
UNION ALL SELECT '2012-07-01 00:00:00.000', '2012-07-31 23:59:59.997'
UNION ALL SELECT '2012-08-01 00:00:00.000', '2012-08-31 23:59:59.997'
UNION ALL SELECT '2012-09-01 00:00:00.000', '2012-09-30 23:59:59.997'
UNION ALL SELECT '2012-10-01 00:00:00.000', '2012-10-31 23:59:59.997'
UNION ALL SELECT '2012-11-01 00:00:00.000', '2012-11-30 23:59:59.997'
)

I now have my test figured out before having any code generated. Now I have communicated with my developer and agreed on a Table value Function to return this data in order to implement the report. We have agreed on a Function Name, Column Names, Column Data Types, and the formula for generating the values for each time segment.

The following code for a table function “TwelveMonthSegments_tfn” returns twelve monthly segments. It utilizes a segment table having a list of numeric sequences starting at 0 and incrementing by 1.

In order to test my assumption I can write a select statement from the table value function passing any date in from the beginning of December 2012 to the end of December 2012.

DECLARE @PassRowCount INT = 0
DECLARE @Period DATETIME = '12/1/2012 1:24:32'

;WITH Segments (SegmentStart, SegmentEnd)
AS
(
SELECT '2011-12-01 00:00:00.000' AS SegmentStart
, '2011-12-31 23:59:59.997' AS SegmentEnd
UNION ALL SELECT '2012-01-01 00:00:00.000', '2012-01-31 23:59:59.997'
UNION ALL SELECT '2012-02-01 00:00:00.000', '2012-02-29 23:59:59.997'
UNION ALL SELECT '2012-03-01 00:00:00.000', '2012-03-31 23:59:59.997'
UNION ALL SELECT '2012-04-01 00:00:00.000', '2012-04-30 23:59:59.997'
UNION ALL SELECT '2012-05-01 00:00:00.000', '2012-05-31 23:59:59.997'
UNION ALL SELECT '2012-06-01 00:00:00.000', '2012-06-30 23:59:59.997'
UNION ALL SELECT '2012-07-01 00:00:00.000', '2012-07-31 23:59:59.997'
UNION ALL SELECT '2012-08-01 00:00:00.000', '2012-08-31 23:59:59.997'
UNION ALL SELECT '2012-09-01 00:00:00.000', '2012-09-30 23:59:59.997'
UNION ALL SELECT '2012-10-01 00:00:00.000', '2012-10-31 23:59:59.997'
UNION ALL SELECT '2012-11-01 00:00:00.000', '2012-11-30 23:59:59.997'
)

SELECT @PassRowCount = SUM(1)
FROM Segments base
JOIN TwelveMonthSegments_tfn(@Period) test
ON base.SegmentStart = test.SegmentStart
AND base.SegmentEnd = test.SegmentEnd

IF @PassRowCount <> 12
RAISERROR('Test failed...one or more segments don''t match.', 12, 12)

SELECT @PassRowCount = SUM(1)
FROM TwelveMonthSegments_tfn(@Period) test

IF @PassRowCount <> 12
RAISERROR(‘Test failed…The Table Value Function must return 12 rows only.’, 12, 12)

If the data from the table value function TwelveMonthSequence_tfn does not match the values in the CTE Segments, an error will be raised because the query won’t result in 12 rows. If the function TwelveMonthSequence_tfn returns anything other than 12 rows, an error will also be raised.

I now have a unit test completed, and no code has been written at this point. Once the function is written the test may be executed and validate that the function meets the expected results.

If you would like to comment on this newsletter you can share it by writing in to btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Determining High-Load SQL Statements with ADDM
High-Load SQL Statements may cause serious performance problem, reducing significantly throughput of your database system. So, it�s a good thing to monitor your database, identifying high-load SQL statements. To simplify this task, Oracle Database offers Automatic Database Diagnostic Monitor (ADDM) that you can use to automate the task of finding potential high-load SQL statements and receiving recommendations on how to fix the problem identified.XML Support and SQL Server (Part 2) – XML Schemas
In my last article, I gave an overview of XML technology. In this article I will talk about XML schemas.

Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Simplify encryption and key management on your SQL Server. Data thieves are targeting SMB companies because of their inadequa… (read more)