tSQLt Age Calculator kata

Prior to writing the blog post below I did what I thought was a fairly thorough search of the internet to see if anyone had already produced an "Age Calculator kata".  I have since searched again and was rather embarrassed to find a fairly similar blog post on tSQLt (although not actually a kata) that discusses an age calculator in relation to discovering test cases .  Further more, I believe the post was created by one of the authors of tSQLt.  Although the kata below was inspired by the tests one of my colleagues wrote to calculate a person's age in a production project it is very similar to the existing solution presented here.


I recently did a whole lot of work to put together a tSQLt training course.  The course consisted of a number of exercises to demonstrate how, where and why you might use tSQLt tests. I also wanted to give the developers doing the training course some problems to solve that would help them understand some of the principles of TDD (and more specifically test first development).

In C# there are a number of katas that you can find online to help demonstrate TDD.  There aren't nearly so many for database unit testing. There are one or two places where you might find tSQLt katas online.  I think the best examples can be found at http://datacentricity.net/tag/kata/.

As there are so few tSQLt katas out there I thought I would try my hand at putting together a kata.  The kata I have created is an extension of the "Age Calculator kata", a kata that is great for demonstrating boundary conditions.

Here is my kata adapted for tSQLt.

User Story

Create a simple report that will return all people that have reached the legal age requirement to obtain their driver’s license. 

Business Rules

• The report results should be based on a given current date (e.g. @CurrentDate).
• The report should show the following columns: FullName (e.g. ‘Smith, John’), IDNumber and Age.

Possible Tests

• Write a test to check for the existence of a scalar-valued function called ‘CalculateCurrentAge’.
• Write tests against ‘CalculateCurrentAge’ to verify that the function correctly calculates the current age of a person given the person’s date of birth and the current date.  Make sure you consider the boundary conditions.  E.g. What about leap years? What if the current date is before the date of birth.
• Write a test to check for the existence of a table-valued function called ‘LegalDrivingAgeReport’.
• Write a test to confirm that only people who have reached the legal driving age appear in the report.
• Write a test to verify that the FullName and IDNumber are returned.  The test should prove that the FullName is correctly formatted.

The Code

Use the code below to create the initial database schema.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='Person')

BEGIN

       DROP TABLE [Person]

END

GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='Address')

BEGIN

       DROP TABLE [Address]

END

GO

CREATE TABLE [Address]

(

       AddressID int identity(1,1) not null,

       AddressLine1 varchar(200) not null,

       AddressLine2 varchar(200) null,

       City varchar(200) null,

       PostCode varchar(20) not null,

       Country varchar(100) not null,

       CONSTRAINT PK_Address PRIMARY KEY (AddressID)

)

GO

CREATE TABLE Person

(

       PersonID int identity(1,1) not null,

       FirstName varchar(100) not null,

       Surname varchar(100) not null,

       IDNumber varchar(20) not null,

       Email varchar(200) not null,

       DateOfBirth datetime not null,

       ResidentialAddressID int not null,

       PostalAddressID int not null,

       CONSTRAINT PK_Person PRIMARY KEY (PersonID),

       CONSTRAINT FK_ResidentialAddress FOREIGN KEY (ResidentialAddressID) REFERENCES [Address](AddressID),

       CONSTRAINT FK_PostalAddress FOREIGN KEY (PostalAddressID) REFERENCES [Address](AddressID)

)