Thursday 19 December 2013

TDD - So what's this test first thing anyway?

I have been doing TDD for a few years now.  When I first started TDD I already had a number of years of development experience behind me. I took a fair bit of time to get comfortable with the whole TDD process, after all I had managed fine for many years without it.  The hardest part for me was the mindset shift to writing tests before writing the code. Now I find it difficult to write any code without having a test in place first.

These days I spend a fair amount of time introducing developers to TDD and I have seen how developers new to TDD grapple to get their heads around exactly what it's all about.  In the rest of this post I will try to explain how the "test first" process of TDD works using a very simple example.  The example is based on the FizzBuzz kata.  I am not going to repeat the kata text here, but for anyone reading this article who is not familiar with the kata, I would suggest following this link to the kata and read through the problem description.

Uncle Bob has a few simple rules that you should follow when doing TDD.

  • You are not allowed to write any production code unless it is to make a failing unit test pass.
  • You are not allowed to write any more of a unit test than is sufficient to fail; and compilation failures are failures.
  • You are not allowed to write any more production code than is sufficient to pass the one failing unit test.

So to get started we are going to need to write a failing test.


using System;
using NUnit.Framework;

namespace FizzBuzz
{
    [TestFixture]
    public class TestFizzBuzz
    {
        [Test]
        public void GetFizzBuzz_WhenInputIs_1_ShouldReturn_1()
        {
            //---------------Set up test pack-------------------
            const string expected = "1";
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            var actual = GetFizzBuzz(1);
            //---------------Test Result -----------------------
            Assert.AreEqual(expected, actual);
        } 

        private string GetFizzBuzz(int input)
        {
            return "0";
        }
}
The name of the test above should be self explanatory. Notice in the GetFizzBuzz method I am simply returning "0" which will cause the first test to fail. To fix the failing test is pretty straight forward.
	private string GetFizzBuzz(int input)
	{
		return "1";
	}

I think that most people will agree that the first test is pretty straight forward and to get the test to pass is as easy as changing a single character in the System Under Test (SUT). The System Under Test in this case is the GetFizzBuzz method. In my next test I want to make the smallest change I can to start evolving the algorithm of the GetFizzBuzz method.
	[Test]
	public void GetFizzBuzz_WhenInputIs_2_ShouldReturn_2()
	{
		//---------------Set up test pack-------------------
		const string expected = "2";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(2);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	}

We once again have a failing test that we need to fix.
	private string GetFizzBuzz(int input)
	{
		if (input==1) return "1";
		return "2";
	}

Run the tests and they will now both pass. On to test 3.
	[Test]
	public void GetFizzBuzz_WhenInputIs_3_ShouldReturn_Fizz()
	{
		//---------------Set up test pack-------------------
		const string expected = "Fizz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(3);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	}

And to fix the failing test.
	private string GetFizzBuzz(int input)
	{
		if (input==1) return "1";
		if (input == 2) return "2";
		return "Fizz";
	}

We are green again. On to the next test.
	[Test]
	public void GetFizzBuzz_WhenInputIs_4_ShouldReturn_4()
	{
		//---------------Set up test pack-------------------
		const string expected = "4";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(4);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	} 

And to make this test pass.
	private string GetFizzBuzz(int input)
	{
		if (input==1) return "1";
		if (input == 2) return "2";

		if (input == 4) return "4";
		return "Fizz";
	}


Once again we are green. By this point it should be pretty apparent that a pattern is emerging and we can refactor to remove the code duplication. So lets refactor.
	private string GetFizzBuzz(int input)
	{
		if (input == 3) return "Fizz";
		return Convert.ToString(input);            
	}

That's better. The duplication is gone and the code is much more readable. Lets continue.
	[Test]
	public void GetFizzBuzz_WhenInputIs_5_ShouldReturn_Buzz()
	{
		//---------------Set up test pack-------------------
		const string expected = "Buzz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(5);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	}

Another failing test. And the fix...
	private string GetFizzBuzz(int input)
	{
		if (input == 5) return "Buzz";
		if (input == 3) return "Fizz";
		return Convert.ToString(input);            
	}

We're green again. On to 6.
	[Test]
	public void GetFizzBuzz_WhenInputIs_6_ShouldReturn_Fizz()
	{
		//---------------Set up test pack-------------------
		const string expected = "Fizz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(6);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	}

Getting this test to pass reveals another pattern starting to emerge.
	private string GetFizzBuzz(int input)
	{
		if (input == 5) return "Buzz";
		if (input == 3 || input == 6) return "Fizz";
		return Convert.ToString(input);            
	}

Not time to refactor just yet. It's generally accepted that we should apply 'the rule of three' when it comes to refactoring. At this point we aren't going to get any value writing tests for 7 and 8 (they should just pass) so lets move on to a test for 9.
	[Test]
	public void GetFizzBuzz_WhenInputIs_9_ShouldReturn_Fizz()
	{
		//---------------Set up test pack-------------------
		const string expected = "Fizz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(9);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	}


And to get the test to pass.
	private string GetFizzBuzz(int input)
	{
		if (input == 5) return "Buzz";
		if (input == 3 || input == 6 || input == 9) return "Fizz";
		return Convert.ToString(input);            
	}

Green again. Notice the repeated code (times 3). Time to refactor.
	private string GetFizzBuzz(int input)
	{
		if (input == 5) return "Buzz";
		if (input % 3 == 0) return "Fizz";
		return Convert.ToString(input);            
	}

Run the tests and we are still green. That's better. At this point it should be pretty obvious the same pattern should emerge for 5. To speed things along I am going to use NUnit's TestCase to write tests for 3 examples that are divisible by 5.
	[TestCase(5)]
	[TestCase(10)]
	[TestCase(15)]
	public void GetFizzBuzz_WhenInputIsMultipleOf_5_ShouldReturn_Buzz(int input)
	{
		//---------------Set up test pack-------------------
		const string expected = "Buzz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(input);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	} 

Notice the test name. I have generalised it a little so that it makes sense for all the test case examples. When using tests cases you need to be aware that there is a balance in terms of how general you allow your tests to become, the test name should help you here. If you make the tests to general, you will lose the intent of the test and down the line other developers looking at your tests will find it much harder to work out what your code is doing. e.g.
	[TestCase(1)]
	[TestCase(2)]
	[TestCase(3)]
	[TestCase(4)]
	[TestCase(5)]
	public void GetFizzBuzz_GivenANumericInput_ShouldReturnAStringResult(int input)
	{

                // THIS IS A BAD TEST.  IT IS WAY TO GENERALISED

		//---------------Set up test pack-------------------
		const string expected = "Buzz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(input);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	} 

DON'T WRITE TESTS LIKE THE ONE ABOVE. JUST DON'T!
Ok. Let's get our tests passing.
	private string GetFizzBuzz(int input)
	{
		if (input % 5 == 0) return "Buzz";
		if (input % 3 == 0) return "Fizz";
		return Convert.ToString(input);            
	}

Moving on. We need to deal with the case where a number is divisible by 3 and 5.
	[TestCase(15)]
	public void GetFizzBuzz_WhenInputIsMultipleOf_3_And_5_ShouldReturn_FizzBuzz(int input)
	{
		//---------------Set up test pack-------------------
		const string expected = "FizzBuzz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(input);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	} 

And to get the test to pass.
	private string GetFizzBuzz(int input)
	{
		if (input == 3*5) return "FizzBuzz";
		if (input % 5 == 0) return "Buzz";
		if (input % 3 == 0) return "Fizz";
		return Convert.ToString(input);            
	}  

My new test passes, but I have a failure. What's gone wrong? Turns out that making the big step with the test cases for 5 I didn't consider numbers that are divisible by 3 and 5. I need to fix the tests case for 15. I'll change it to 20.
	[TestCase(5)]
	[TestCase(10)]
	[TestCase(20)]
	public void GetFizzBuzz_WhenInputIsMultipleOf_5_ShouldReturn_Buzz(int input)
	{
		//---------------Set up test pack-------------------
		const string expected = "Buzz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(input);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	} 
    
Ok. The tests are all passing again. I'll add the necessary test cases for FizzBuzz (divisible by 3 and 5).
	[TestCase(15)]
	[TestCase(30)]
	[TestCase(45)]
	public void GetFizzBuzz_WhenInputIsMultipleOf_3_And_5_ShouldReturn_FizzBuzz(int input)
	{
		//---------------Set up test pack-------------------
		const string expected = "FizzBuzz";
		//---------------Assert Precondition----------------

		//---------------Execute Test ----------------------
		var actual = GetFizzBuzz(input);
		//---------------Test Result -----------------------
		Assert.AreEqual(expected, actual);
	}
    
And change the code to get all the tests passing.
	private string GetFizzBuzz(int input)
	{
		if (input % (3*5) == 0) return "FizzBuzz";
		if (input % 5 == 0) return "Buzz";
		if (input % 3 == 0) return "Fizz";
		return Convert.ToString(input);            
	} 
    
That's it the GetFizzBuzz method should now work for any integer value greater than 0. I should possibly include a test to ensure that the input is always greater than 0, but I'll leave that up to you. Just to finish the kata off quickly, i'll include one further test to test GetFizzBuzz list that accepts a "count" and returns a list of FizzBuzz results that has "count" items.
	[Test]
	public void GetFizzList_Given_100_ShouldReturn_First100FizzBuzzresults()
	{
		//---------------Set up test pack-------------------
		
		//---------------Assert Precondition----------------
		//---------------Execute Test ----------------------
		List results = GetFizzBuzzList(100);
		//---------------Test Result -----------------------
		foreach (var result in results)
		{
			Console.WriteLine(result);
		}

		Assert.AreEqual(100, results.Count);
		Assert.AreEqual("1", results[0]);
		Assert.AreEqual("Fizz", results[2]);
		Assert.AreEqual("Buzz", results[4]);
		Assert.AreEqual("FizzBuzz", results[14]);
		Assert.AreEqual("FizzBuzz", results[89]);
		Assert.AreEqual("Fizz", results[98]);
		Assert.AreEqual("Buzz", results[99]);
	}
   
And to get the test to pass.
	private List GetFizzBuzzList(int count)
	{
		var results = new List();
		for (int i = 0; i < count; i++)
		{
			string result = GetFizzBuzz(i+1);
			results.Add(result);
		}
		return results;
	}
    
Green again. That's it.

Wednesday 2 October 2013

tSQLt demo workflow using Visual Studio

Recently, I have worked alongside one of our company directors in presenting a series of  "TDD with tSQLt" training courses.  A frequent request from the developers attending these courses is "How do we implement tSQLt in our production projects?".

To answer the request, I have put together a small tSQLt demo workflow project and made it available on github: https://github.com/chilli-andrew/tsqlt-demo-workflow

Please feel free to try it out and let me know what you think.

Friday 23 August 2013

T-SQL: Reminders 1

For the last few month I have spent most of my development time working with T-SQL.  This is just down to the nature of the current project I am working on.  On other projects I will spend far less time on T-SQL tasks and more time on front-end and middle-tier development.  Over time, especially when I am working on projects that don't rely heavily on SQL, I tend to forget some of the useful commands and tricks that are available in T-SQL.

This post is just as a reminder to myself about some of those commands that I have seen in the past and forgotten about until recently, and some new ones I would like to remember (or find here) in the future.

These first two I have seen before:

SET STATISTICS TIME ON

This will turn on time statistics which in turn will produce time info in the messages pane after a query is run.

Example:
SET STATISTICS TIME ON

WAITFOR DELAY '00:00:02.5'


GO

Output:



SET STATISTICS IO ON

This will turn on IO statistics which in turn will produce IO info in the messages pane after a query is run.

Example:
SET STATISTICS IO ON

SELECT * FROM Person p
INNER JOIN Address a ON p.PostalAddressID=a.AddressID
WHERE IDNumber=456
GO

Output:










Once you no longer want time or IO statistics you can turn the statistics off using:
SET STATISTICS TIME OFF

GO

or
SET STATISTICS IO OFF

GO


And these ones I haven't:

REPLICATE

This function takes 2 parameters: REPLICATE(string_expressing, integer_expression). Whatever is contained in the string expression will be replicated the number of times specified in the integer_expression.

Example:
SELECT REPLICATE('abc', 3) AS replicate_example

Output:




BATCH INSERT TEST DATA
This trick reminds me of the strange word function that allows you to create random text in a word document.  Next time you have word open try typing (followed by Enter): =rand(9)

To batch insert into a table:

Example:
CREATE TABLE MyTest (Id int identity(1,1) primary key, Name varchar(100) not null)
GO

INSERT MyTest (Name) VALUES (CAST(NEWID() AS varchar(36)))
GO 5



SELECT * FROM MyTest

Output:




Wednesday 21 August 2013

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)
)