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.

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:

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)

)

SQL Server CLR and Authorization

Over the past year I have worked on a few projects that have used SQL CLR procedures and functions.  SQL Server needs to be configured to run these CLRs.  A standard script to get this done would be:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);
GO

This will generally get your CLR up and running on your server if it's the first time you have installed the CLR. What I have noticed though is that if you backup a database and restore it on another SQL Server in a different Domain with different security setting the CLR stops working with an error like this:

"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: "

To get your CLRs working again you need to correct the Authorization on the database with the CLR.  Here is a script that will allow you to do this:

EXEC sp_configure 'clr enabled', 1;
-- To get the owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

-- To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

-- The sid's above should be the same
-- To fix:
/*
ALTER AUTHORIZATION ON Database::XXXXX TO [domain\user]
*/

 

Note the SELECT statements are merely going to show that the SID for the owner of the current database differs to what the master database has recorded as the SID for the current database.  To fix the SID on the current database run the ALTER AUTHORIZATION statement replacing the XXXXX with the current database name and the [domain\user] with the user you want to give authorization to e.g. [sa].  Once the authorization has been altered run the first script above to ensure CLR is enabled and TRUSTWORTHY is ON.

 

 

Tennis Kata - My Solution

Intentional practice is something that is strongly advocated at the company I work for.  What we do as intentional practice changes from time to time, but over the last few months we have spent a fair bit of time solving and resolving various kata's.

Here is what I eventually ended up with after numerous runs of the Tennis Kata.

Firstly, the code:

    
    public class Game
    {
        private readonly Point _servicePoint = new Point();
        private readonly Point _receiverPoint = new Point();

        public string Score
        {
            get
            {
                if (_servicePoint.ToString().Equals(Point.FORTY) 
                   && (_receiverPoint.ToString().Equals(Point.FORTY)))
                {
                    return "DEUCE";
                }
                return string.Format("{0}-{1}", _servicePoint, _receiverPoint);
            }
        }

        public void ScoreToService()
        {
            _servicePoint.Score(_receiverPoint);
        }

        public void ScoreToReceiver()
        {
            _receiverPoint.Score(_servicePoint);
        }
    }

 
    public class Point
    {
        public const string LOVE = "0";
        public const string FIFTEEN = "15";
        public const string THIRTY = "30";
        public const string FORTY = "40";
        public const string GAME = "GAME";
        public const string ADVANTAGE = "ADV";

        private string _score = LOVE;
        private Dictionary> _pointMap;

        public Point()
        {
            BuildPointMap();
        }

        public override string ToString()
        {
            return _score;
        }

        public void Score(Point opponentPoint)
        {
            _pointMap[_score].Invoke(opponentPoint);
        }

        private void BuildPointMap()
        {
            _pointMap = new Dictionary>
            {
               {Point.LOVE, o => { _score = Point.FIFTEEN; }},
               {Point.FIFTEEN, o => { _score = Point.THIRTY; }},
               {Point.THIRTY, o => { _score = Point.FORTY; }},
               {
                  Point.FORTY, o =>
                  {
                     if (o.ToString().Equals(Point.FORTY))
                     {
                        _score = ADVANTAGE;
                     }
                     else
                     {
                        _score = o.ToString().Equals(Point.ADVANTAGE) 
                                                             ? FORTY 
                                                             : GAME;
                     }
                  }
               },
               {Point.ADVANTAGE, o => { _score = GAME; }},
               {Point.GAME, o => 
                 { 
                  throw new ApplicationException("Game already completed!"); 
                 }
               }
            };
        }
    }

And the tests:

    
    [TestFixture]
    public class TestGame
    {
        [Test]
        public void NewGame_ShouldSetScore_0_0()
        {
            //---------------Set up test pack-------------------
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            var game = new Game();

            //---------------Test Result -----------------------
            Assert.IsNotNull(game);
            Assert.AreEqual("0-0", game.Score);
        }

        [Test]
        public void ScoreToService_When_0_0_ShouldSetScore_15_0()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            //---------------Assert Precondition----------------
            Assert.AreEqual("0-0", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("15-0", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_0_0_ShouldSetScore_0_15()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            //---------------Assert Precondition----------------
            Assert.AreEqual("0-0", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("0-15", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_15_15_ShouldSetScore_15_30()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("15-15", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("15-30", game.Score);
        }

        [Test]
        public void ScoreToService_When_40_15_ShouldSetScore_GAME_15()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("40-15", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("GAME-15", game.Score);
        }

        [Test]
        public void ScoreToService_When_30_40_ShouldSetScore_DEUCE()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("30-40", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("DEUCE", game.Score);
        }

        [Test]
        public void ScoreToService_When_DEUCE_ShouldSetScore_ADV_40()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();

            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("DEUCE", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToService();
            //---------------Test Result -----------------------
            Assert.AreEqual("ADV-40", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_40_ADV_ShouldSetScore_40_GAME()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();

            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("40-ADV", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("40-GAME", game.Score);
        }

        [Test]
        public void ScoreToReceiver_When_DEUCE_ShouldSetScore_40_ADV()
        {
            //---------------Set up test pack-------------------
            var game = new Game();
            game.ScoreToService();
            game.ScoreToService();
            game.ScoreToService();

            game.ScoreToReceiver();
            game.ScoreToReceiver();
            game.ScoreToReceiver();
            //---------------Assert Precondition----------------
            Assert.AreEqual("DEUCE", game.Score);

            //---------------Execute Test ----------------------
            game.ScoreToReceiver();
            //---------------Test Result -----------------------
            Assert.AreEqual("40-ADV", game.Score);
        }
    }

 
    [TestFixture]
    public class TestPoint
    {
        // ReSharper disable InconsistentNaming
        [Test]
        public void NewPoint_ShouldSetToString_0()
        {
            //---------------Set up test pack-------------------
            //---------------Assert Precondition----------------
            //---------------Execute Test ----------------------
            var point = new Point();
            //---------------Test Result -----------------------
            Assert.IsNotNull(point);
            Assert.AreEqual(Point.LOVE, point.ToString());
        }

        [Test]
        public void Score_When_0_ShouldSetToString_15()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.LOVE, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.FIFTEEN, point.ToString());
        }

        [Test]
        public void Score_When_15_ShouldSetToString_30()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            point.Score(new Point());
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FIFTEEN, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.THIRTY, point.ToString());
        }

        [Test]
        public void Score_When_30_ShouldSetToString_40()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            point.Score(new Point());
            point.Score(new Point());
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.THIRTY, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.FORTY, point.ToString());
        }

        [Test]
        public void Score_When_40_ShouldSetToString_GAME()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            point.Score(new Point());
            point.Score(new Point());
            point.Score(new Point());
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FORTY, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(new Point());
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.GAME, point.ToString());
        }

        [Test]
        public void Score_When_40_40_ShouldSetToString_ADV()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FORTY, point.ToString(), "PRE CONDITION");
            Assert.AreEqual(Point.FORTY, opponentPoint.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(opponentPoint);
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.ADVANTAGE, point.ToString());
        }

        [Test]
        public void Score_When_40_ADV_ShouldSetToString_40()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.FORTY, point.ToString(), "PRE CONDITION");
            Assert.AreEqual(Point.ADVANTAGE, opponentPoint.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(opponentPoint);
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.FORTY, point.ToString());
        }

        [Test]
        public void Score_When_ADV_40_ShouldSetToString_GAME()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            opponentPoint.Score(point);
            point.Score(opponentPoint);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.ADVANTAGE, point.ToString(), "PRE CONDITION");
            Assert.AreEqual(Point.FORTY, opponentPoint.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            point.Score(opponentPoint);
            //---------------Test Result -----------------------
            Assert.AreEqual(Point.GAME, point.ToString());
        }

        [Test]
        public void Score_When_GAME_0_ShouldError()
        {
            //---------------Set up test pack-------------------
            var point = new Point();
            var opponentPoint = new Point();
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            point.Score(opponentPoint);
            //---------------Assert Precondition----------------
            Assert.AreEqual(Point.GAME, point.ToString(), "PRE CONDITION");
            //---------------Execute Test ----------------------
            var ex = Assert.Throws(() => point.Score(opponentPoint));
            //---------------Test Result -----------------------
            StringAssert.Contains("Game already completed!", ex.Message);
        }
    }

Getting Started with Ruby

In my day job, we use ruby rake files for biulding our .net projects.  The rake files are run by our continuous integration server and build the projects, run unit tests, coverage reports and more.  Over the past year they have evolved and gained more and more in the way of functionality.  I have spent a fair amount of time hacking around in them to add and improve existing functionality picking up bits of ruby as I go along.  I am now at a point where I want to get to know ruby a little better and thought I might document some of my learning.

To get started, if you want to work with ruby you will need to download and install it on your pc.  Ruby can run on a number of operating systems, but in my case I am working on a Windows pc.  To download and install the latest version of ruby go to: www.rubyinstaller.org/downloads/ and select the latest ruby installer download.  Once the download is complete double click the exe to install.

Once ruby is installed you will need to add the path the the ruby.exe to your environment variables path.  Heres how to do it:
Windows XP : http://support.microsoft.com/kb/310519
Windows 7 : http://geekswithblogs.net/renso/archive/2009/10/21/how-to-set-the-windows-path-in-windows-7.aspx

Here is how you might create a hello world ruby script:

• Create a new folder for the scripts c:\rubyscripts
• Add a new text document in the folder called hello_world.rb
• Edit the script in your favourite text editor and add the following

puts "hello world"

• Open a command prompt in the c:\rubyscripts folder
• Run the following c:\rubyscripts\ruby hello_ruby.rb

Strange Web Services Exception

One of the application I work on started playing up recently.  The application communicates with one of our customers systems via web services.  Due to technical reasons on their side they are changing the platform that their web services run on.  This has meant that we have had to make some changes to accomodate their changes and get our application talking to the new web services correctly.  Once released into their testing environment some of the web services call started to throw a System.ServiceModel.CommunicationException:

System.ServiceModel.CommunicationException: There was an error in serializing body of message checkIssueDeliveryRequest: 
'Unable to generate a temporary class (result=1).  error CS2001: Source file '
C:\Windows\TEMP\rjsmbtjy.0.cs' could not be found  error CS2008: No inputs specified  '.  
Please see InnerException for more details. ---> System.InvalidOperationException: Unable to generate a temporary class (result=1).  
error CS2001: Source file 'C:\Windows\TEMP\rjsmbtjy.0.cs' could not be found  error 
CS2008: No inputs specified       
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)   
....
....

It turns out the problem is down to the fact that the User account that the Application Pool associated with the web services website does not have read and write permissions to the c:\windows\temp directory as described here http://support.microsoft.com/kb/322886.  Below is a more detailed step by step approach that I to solve the issue.

• Open IIS Manager
• Click on the web services application node of the problem web services website
• Click on Basic Settings... to see what Application Pool is being used (in my case it was ASP.NET v4.0 Integrated)
• Click the Application Pools node and select the Application Pool associated with your web services website
• Click on Advances Settings... and check the Process > Identity to get the account that is being used
• Open an explorer window and browse to c:\windows\temp
• Right click on the folder and click properties
• Select the Security tab and  click Edit:
• If the account already appears under "Group or user names:", highlight the account by clicking on it and ensure that the read and write checkboxes are checked
• If it is not already listed click Add... to add the account before ensuring that the read and write permission are checked for that account

That's it for now.