Hillbilly Soul-Searching

Posted by indomitablehef | Filed under TDD

I always appreciate Kyle’s honesty and transparency. Read his response to Roy Osherove’s controversial post: Do the right thing. Assuming you know what that is.

No more short cuts, god classes or big balls of mud

Posted by indomitablehef | Filed under TDD

Jimmy Bogard on the lifestyle change that is TDD: TDD design trade-offs and junk food

Starting TDD is giving up OO junk food. No more short cuts, god classes or big balls of mud, which seemed so comforting before. No more wallowing in the debugger, taking pride in knowing how to take apart the Gordian knot, or making a mental stack of variables. It hurt at first, seemed a little alien, but led toward leaner, tighter and more cohesive designs.

SQL TDD

Posted by indomitablehef | Filed under TDD

So, it’s been awhile, but here we go again. It’s been a busy summer for me, what with the birth of my daughter, Cecilia. I’ve also been learning a lot, and wishing I had the time to blog about the stuff I’ve been learning. So here goes…

The company I work for does a lot of things, but our bread and butter is data warehousing. This means that we have lots of solutions that are nearly 100% database-centric. There’s a lot of code in stored procedures, and a lot of data moving around. Much of the code is used for data scrubbing, business rule implementation, etc. In our migration towards agile, we’re doing Agile Database development. Automated builds, version control, continuous integration, the whole nine yards. That includes TDD. How? Well, to start, we used tSQLUnit, and added onto it. That worked, but I decided that we needed more. When I couldn’t find anything better, I decided to write my own. It should be ready sometime this fall, but I’m going to go ahead and start sharing it here. My intent is for it to be open-source, and maybe I’ll write some tools to go with it…we’ll see.

To start, I wanted a framework that reported its test failures by raising an exception, not just by dropping a record into a “test result” table. So first, let’s examine how we’re going to get our tests to fail. We’ll need a specific error message

1
2
3
4

EXECUTE sp_addmessage 50100, -- Message id number.
        17, -- Severity.
        N'Assert Failed. %s';
    GO

This next decision is up for debate (as is everything about this, really…please send me any feedback you have, I’d appreciate the input). Readability is important to me, and the use of “tests as documentation”. I normally shy away from over-use of different schemas in my database development, preferring to just stick everything in dbo. But for this case, I decided to create a special schema called “Assert”. This schema will, naturally, hold all my Assert procedures.

1

CREATE SCHEMA Assert

Now we’ll create our Assert.Fail procedure, and raise the error we added earlier:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE PROCEDURE [Assert].[Fail]
    @expected SQL_VARIANT,
    @actual SQL_VARIANT,
    @message NVARCHAR(1000) = '',
    @location NVARCHAR(1000) = ''
AS 
    DECLARE @failureString  NVARCHAR(1000),
			@messageString  NVARCHAR(1000), 
			@locationString NVARCHAR(1000)
 
    SELECT @messageString = CASE WHEN LEN(@message) > 0 THEN 
							' Messsage: [' + @message + ']' 
							ELSE '' END 
 
    SELECT @locationString = CASE WHEN LEN(@location) > 0 THEN 
							' Location: [' + @location + ']' 
							ELSE '' END 
 
    SET @failureString = ' Expected: [' + CONVERT(NVARCHAR, @expected) 
                       + '], Actual: [' + CONVERT(NVARCHAR, @actual) + '].' 
                       + @messageString + @locationString
 
    RAISERROR (50100,17,71,@failureString)

Some things to notice here

1. I’m using SQL_VARIANT- I want to be able to pass in any data type to this “Fail” procedure for @expected and @actual. This, of course, constrains me to SQL2005 and higher, but I was already planning on that.
2. I’m using NVARCHAR - In some of my earlier runs at this, I experimented with using an extended stored procedure as the test running. I may still go that route…not sure. In doing that, I found that to I had to use NVARCHAR in some places. At the time, I started using it everywhere. I’m not 100% sure I want to continue this, but there it is, for now.
3. I’m passing in @location - I intend this to be used to pass in the name of the calling stored procedure (a test). Usually, this parameter will be supplied with the value
   OBJECT_NAME(@@PROCID) - i.e., the name of the calling stored procedure, like so:

1
2
3
4
5
6
7
8

CREATE PROCEDURE MyTestMethod
AS 
	DECLARE @location sysname
	SET @location =  OBJECT_NAME(@@PROCID)
 
	EXEC Assert.Fail @expected = 1, @actual = 2, 
                               @message = N'expected count does not match', 
                               @location = @location

If I had some way of looking up the call stack, to see which stored procedure called Assert.Fail, I wouldn’t need to do this, but as yet I haven’t found any way to do this. When you execute MyTestMethod, you get

1
2

Msg 50100, Level 17, State 71, Procedure Fail, Line 24
Assert Failed. Expected: [1], Actual: [2]. Messsage: [expected count does NOT match] Location: [MyTestMethod]

Of course, it wouldn’t be proper to write a testing framework without using TDD to write it, so here’s what our first self-test looks like.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE PROCEDURE [dbo].[Assert_Fail_Base]
AS
 DECLARE @failMessage NVARCHAR(4000)
 BEGIN TRY
	EXEC Assert.Fail 0,1
    RAISERROR ('Assert.Fail did not raise execption at all.' ,16,72)
 END TRY
 BEGIN CATCH
    DECLARE @number INT, @severity INT, @message NVARCHAR(4000)
    SELECT @number = ERROR_NUMBER(),
		   @severity = ERROR_SEVERITY(),
           @message = ERROR_MESSAGE()
 
    -- is the error number correct 
    IF @number <> 50100
    BEGIN
      SET @failMessage = 'Assert.Fail did not raise assertion exception as expected. Instead, it raised [' + @message + ']'
      RAISERROR (@failMessage ,16,71)
    END
 END CATCH