Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

An Example of Test-Driven Development, Part 2

By Andy Leonard, (first published: 2009/06/16)

Introduction

In this Part 1 of this article I discussed the principles of test-driven database development (TD3) along with Try/Catch and SqlCmd functionality. The article continues by demonstrating more development of the WeatherData database.

The WeatherData database is real – I built it to hold data collected by my weather station.

Open the Existing Solution

Our existing Database Solution is named WeatherData and it’s a Database Project we created in SQL Server Management Studio. You can download the WeatherData project files here. Double-click WeatherData.ssmssln to open the database solution. Once open, click ViewàSolution Explorer to display the solution artifacts:

Solution Explorer

I can hear you thinking “Just what is a solution, Andy?” Excellent question!

The Solution / Project / Artifact paradigm has been around for a while. Visual Studio has used this model for years. As in the diagram below, Database Solutions contain one or more Database Projects. In turn, Database Projects contain database project artifacts. The project artifacts for a database project are Connections and Queries. Database projects can also hold other artifacts – these will go in the Misc folder.

Let’s Create a Table!

But first… this is test-driven development. So let’s create a test.

The first question is “What are we going to test?” In this case, our table is going to be in a new schema named “raw.” Before we can create the table, we’ll need to create the schema. So we’re going to first write a test for the schema. In Solution Explorer, right-click the Queries logical folder and click New Query:

New Query

Rename the query Test_CreateRaw.schema.sql. I like this as a test:

Use WeatherData
go 
If Not Exists(Select name
             From sys.schemas
             Where name= 'raw')
 Print 'Failure: The Raw schema does not exist.'
Else
 Print 'Success: The Raw schema exists.'

Running this script produces the following result:

Failure: The Raw schema does not exist.

Remember this is good. We want it to fail first. Next let’s create the script that builds the raw schema (I named this one CreateRaw.schema.sql):

Use WeatherData
go
If Not Exists(Select name
              From sys.schemas
              Where name= 'raw')
 begin
  Print ' > Creating Raw schema.'
  Declare @Sql varchar(50)
  Set @Sql = 'Create Schema raw'
  Exec(@Sql)
 end
Else
 Print ' > Raw schema exists.'

Ok, Now Let’s Create a Table!

This table is going to hold raw data imported from CSV files. My Oregon-Scientific WMR100NA weather station updates CSV files every hour on the hour. I’ll eventually use SSIS to pump the data from the CSV files into the WeatherData database. When I do, I’ll land it in a staging table.

First the test query named Test_CreateStageTemperature.table.sql:

UseWeatherData
go
If Not Exists(Select s.name + '.' + t.name
              From sys.tables t
              Inner Joinsys.schemas s On s.Schema_Id= t.Schema_Id
              Where s.name = 'raw'
                And t.name = 'StageTemperature')
  Print 'Failure: Raw.StageTemperature does not exist.'
Else
  Print 'Success: Raw.StageTemperature exists.'

And the table script (CreateStageTemperature.table.sql) is:

Use WeatherData
go
If Not Exists(Select s.name + '.' + t.name
              From sys.tables t
              Inner Joinsys.schemas s On s.Schema_Id= t.Schema_Id
              Where s.name = 'raw'
                And t.name = 'StageTemperature')
 begin
  Print ' > Creating raw.StageTemperature.'
  Create Table raw.StageTemperature
  (
    ID int Identity(1,1)Not Null Constraint PK_StageTemperature PrimaryKey Clustered (ID)
   ,MeasDate datetime Null
   ,MeasTime datetime Null
   ,MinT real Null
   ,MaxT real Null
   ,AvgT real Null
   ,MinH smallint Null
   ,MaxH smallint Null
   ,AvgH smallint Null
   ,ComfortZone smallint Null
   ,MinDP real Null
   ,MaxDP real Null
   ,AvgDP real Null
   ,MinHI varchar(7)Null
   ,MaxHI varchar(7)Null
   ,AvgHI varchar(7)Null
   ,LoadDate datetime Null
  )
 end
Else
Print' > Raw.StageTemperature exists.'

Things Get Interesting: A Stored Procedure

This stored procedure will return the latest average temperature (AvgT) and measurement datetime (MeasDateTime) stored in the raw.StageTemperature table. The temperature is stored in Celsius (Centirgrade) degrees, so I’ll also return a converted value for Fahrenheit degrees.

The test query is named Test_CreateGetLatestAvgTemp.proc.sql:

use WeatherData
go
If Not Exists(Select s.name + '.' + p.name
              From sys.procedures p
              Inner Joinsys.schemas s On s.Schema_Id= p.Schema_Id
              Where s.name = 'raw'
                And p.name = 'GetLatestAvgTemp')
 Print 'Failure: GetLatestAvgTemp does not exist.'
Else
 Print 'Success: GetLatestAvgTemp exists.'

As you can see from the test, this stored procedure is also in the raw schema. Next let’s create CreateGetLatestAvgTemp.proc.sql:

Use WeatherData
go
If Exists(Select s.name +'.' + p.name
          Fromsys.procedures p
          InnerJoin sys.schemas s On s.Schema_Id= p.Schema_Id
          Where s.name = 'raw'
            And p.name = 'GetLatestAvgTemp')
 begin
  Print ' > Dropping Procedure raw.GetLatestAvgTemp.'
  Drop Procedure raw.GetLatestAvgTemp
 end
go
Print ' > Creating Procedure raw.GetLatestAvgTemp.'
go
Create Procedure raw.GetLatestAvgTemp
As
 begin
  Select top 1
  MeasDateTime
  ,AvgT
  ,((AvgT * 9/5)+ 32) As AvgTF
  from raw.StageTemperature
  order by ID desc
 end
go
Print ' > raw.GetLatestAvgTemp Procedure created.'

Note I drop and recreate the stored procedure every time, but not the database, schema, or table. Why? One reason is it doesn’t matter whether I execute an Alter Procedure statement or a Drop and Create – the changes to the Procedure are accomplished regardless. One benefit of dropping and recreating is I can tell by glancing at the Object Explorer Details for Stored Procedures when the Procedure was last deployed. The Created column shows the last deployment date, as shown below:

Object Explorer - Stored Procedures

Deploying Version 1.1

There are a couple ways to approach deploying the second version of a database project:

  1. Add to the existing script.
  2. Add scripts

I prefer the second approach. I make two more scripts. The first is a script to deploy version 1.1, and it looks like this:

 /*
Notes:
 1. Turn on SQLCMD mode.
 2. If you receive an error like "Incorrect syntax near ':'",
    see Note 1.
 3. Correct the file paths, replacing the default
    with the full path to your files.
*/
Print ''
Print 'Deploying WeatherData v1.1.'
Print ''
Print 'Calling Test_CreateRaw.schema.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateRaw.schema.sql"
Print 'Test_CreateRaw.schema.sql called.'
Print ''
Print 'Calling CreateRaw.schema.sql...'
:r "C:\Projects\WeatherData\WeatherData\CreateRaw.schema.sql"
Print 'CreateRaw.schema.sql called.'
Print ''
Print 'Calling Test_CreateRaw.schema.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateRaw.schema.sql"
Print 'Test_CreateRaw.schema.sql called.'
Print ''
Print 'Calling Test_CreateStageTemperature.table.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateStageTemperature.table.sql"
Print 'Test_CreateStageTemperature.table.sql called.'
Print ''
Print 'Calling CreateStageTemperature.table.sql...'
:r "C:\Projects\WeatherData\WeatherData\CreateStageTemperature.table.sql"
Print 'CreateStageTemperature.table.sql called.'
Print ''
Print 'Calling Test_CreateStageTemperature.table.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateStageTemperature.table.sql"
Print 'Test_CreateStageTemperature.table.sql called.'
Print ''
Print 'Calling Test_CreateGetLatestAvgTemp.proc.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateGetLatestAvgTemp.proc.sql"
Print 'Test_CreateGetLatestAvgTemp.proc.sql called.'
Print ''
Print 'Calling CreateGetLatestAvgTemp.proc.sql...'
:r "C:\Projects\WeatherData\WeatherData\CreateGetLatestAvgTemp.proc.sql"
Print 'CreateGetLatestAvgTemp.proc.sql called.'
Print ''
Print 'Calling Test_CreateGetLatestAvgTemp.proc.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateGetLatestAvgTemp.proc.sql"
Print 'Test_CreateGetLatestAvgTemp.proc.sql called.'
Print ''
Print 'WeatherData v1.1 deployed.'

This script calls all the previous scripts in order of dependence: schema, table, procedure. The second script marries the two version deployment scripts together, and it looks like this:

/*
Notes:
 1. Turn on SQLCMD mode.
 2. If you receive an error like "Incorrect syntax near ':'",
    see Note 1.
 3. Correct the file paths, replacing the default
    with the full path to your files.
*/
Print ''
Print 'Building WeatherData Version 1.'
Print ''
Print 'Calling DeployV1.ver.sql...'
:r "C:\Projects\WeatherData\WeatherData\DeployV1.ver.sql"
Print 'DeployV1.ver.sql called.'
Print ''
Print 'Calling DeployV1.1.ver.sql...'
:r "C:\Projects\WeatherData\WeatherData\DeployV1.1.ver.sql"
Print 'DeployV1.1.ver.sql called.'
Print ''
Print 'WeatherData Version 1 built.'

Executing this script produces the following output Results to Text:

Building WeatherData Version 1.

Calling DeployV1.ver.sql...

Deploying WeatherData v1.

Calling Test_CreateWeatherData.db.sql...

Success: WeatherData exists.

Test_CreateWeatherData.db.sql called.

Calling CreateWeatherData.db.sql...

Create WeatherData database

> WeatherData database exists.

CreateWeatherData.db.sql called.

Calling Test_CreateWeatherData.db.sql...

Success: WeatherData exists.

Test_CreateWeatherData.db.sql called.

WeatherData v1 deployed.

DeployV1.ver.sql called.

Calling DeployV1.1.ver.sql...

Deploying WeatherData v1.1.

Calling Test_CreateRaw.schema.sql...

Failure: The Raw schema does not exist.

Test_CreateRaw.schema.sql called.

Calling CreateRaw.schema.sql...

> Creating Raw schema.

CreateRaw.schema.sql called.

Calling Test_CreateRaw.schema.sql...

Success: The Raw schema exists.

Test_CreateRaw.schema.sql called.

Calling Test_CreateStageTemperature.table.sql...

Failure: Raw.StageTemperature does not exist.

Test_CreateStageTemperature.table.sql called.

Calling CreateStageTemperature.table.sql...

> Creating raw.StageTemperature.

CreateStageTemperature.table.sql called.

Calling Test_CreateStageTemperature.table.sql...

Success: Raw.StageTemperature exists.

Test_CreateStageTemperature.table.sql called.

Calling Test_CreateGetLatestAvgTemp.proc.sql...

Failure: GetLatestAvgTemp does not exist.

Test_CreateGetLatestAvgTemp.proc.sql called.

Calling CreateGetLatestAvgTemp.proc.sql...

> Creating Procedure raw.GetLatestAvgTemp.

> raw.GetLatestAvgTemp Procedure created.

CreateGetLatestAvgTemp.proc.sql called.

Calling Test_CreateGetLatestAvgTemp.proc.sql...

Success: GetLatestAvgTemp exists.

Test_CreateGetLatestAvgTemp.proc.sql called.

WeatherData v1.1 deployed.

DeployV1.1.ver.sql called.

WeatherData Version 1 built.

Re-executing the build produces the following output:

Building WeatherData Version 1.

Calling DeployV1.ver.sql...

Deploying WeatherData v1.

Calling Test_CreateWeatherData.db.sql...

Success: WeatherData exists.

Test_CreateWeatherData.db.sql called.

Calling CreateWeatherData.db.sql...

Create WeatherData database

> WeatherData database exists.

CreateWeatherData.db.sql called.

Calling Test_CreateWeatherData.db.sql...

Success: WeatherData exists.

Test_CreateWeatherData.db.sql called.

WeatherData v1 deployed.

DeployV1.ver.sql called.

Calling DeployV1.1.ver.sql...

Deploying WeatherData v1.1.

Calling Test_CreateRaw.schema.sql...

Success: The Raw schema exists.

Test_CreateRaw.schema.sql called.

Calling CreateRaw.schema.sql...

> Raw schema exists.

CreateRaw.schema.sql called.

Calling Test_CreateRaw.schema.sql...

Success: The Raw schema exists.

Test_CreateRaw.schema.sql called.

Calling Test_CreateStageTemperature.table.sql...

Success: Raw.StageTemperature exists.

Test_CreateStageTemperature.table.sql called.

Calling CreateStageTemperature.table.sql...

> Raw.StageTemperature exists.

CreateStageTemperature.table.sql called.

Calling Test_CreateStageTemperature.table.sql...

Success: Raw.StageTemperature exists.

Test_CreateStageTemperature.table.sql called.

Calling Test_CreateGetLatestAvgTemp.proc.sql...

Success: GetLatestAvgTemp exists.

Test_CreateGetLatestAvgTemp.proc.sql called.

Calling CreateGetLatestAvgTemp.proc.sql...

> Dropping Procedure raw.GetLatestAvgTemp.

> Creating Procedure raw.GetLatestAvgTemp.

> raw.GetLatestAvgTemp Procedure created.

CreateGetLatestAvgTemp.proc.sql called.

Calling Test_CreateGetLatestAvgTemp.proc.sql...

Success: GetLatestAvgTemp exists.

Test_CreateGetLatestAvgTemp.proc.sql called.

WeatherData v1.1 deployed.

DeployV1.1.ver.sql called.

WeatherData Version 1 built.

This output is beginning to look like a useful artifact too – a deployment log.

What’s really cool about all this is the fact that it’s re-executable. You can run the same script against a new instance of SQL Server where WeatherData has never been deployed, or you can run it where only version 1.0 has been deployed. I like that a lot.

Finally! Something More Than Existence Tests!

Up until now, we’ve been writing existence tests. We’ve tested for the existence of the database, schema, table, and a stored procedure. Let’s do some unit testing!

Depending on whom you talk to and when, you’ll get different definitions of “unit test.” I first heard the phrase when studying electronics engineering. In electronics engineering, a unit test is sometimes referred to as a “smoke test” (my former lab partners can explain why…) and consisted of powering up a circuit and observing the results. To power it up, we usually constructed a “harness” – a collection of wires tied together with wire ties and maybe terminated with some NEMA, ANSI, or IEC connectors to transfer power from the power supply (or supplies) to the circuit (does any of this language sound familiar?). We weren’t looking for everything to work perfectly – at best it was a way to generate first-pass results. But seriously, we were really checking to see if the circuit survived a power-up. If it did, these results were used to validate both calculations and assumptions.

This idea of unit testing, applied to software in general, tests functions or methods or even code snippets to see if they too survive “power-up.” In the strictest sense, a unit test determines if the object under test performs or smokes during normal use.

That said, many people mean something very different when they say “unit test.” Most folks are looking for valid results from a unit test. Testing for valid results requires different types of testing, ranging from functional testing to exception testing to performance testing. Lots of people lump all these test types together under an umbrella called unit testing. Is it all semantics? It depends on who you’re talking to.

Before we write a test, let’s write setup and teardown scripts. The setup script will insert a row of data into the raw.StageTemperature table and will be a script named Test_GetLatestAvgTempSuccess.setup.sql:

Use WeatherData
go
If Not Exists(Select MeasDate
              From raw.StageTemperature
              Where MeasDate = '1/1/2001')
 Insert Into raw.StageTemperature
 (MeasDate
 ,AvgT)
 Values
 ('1/1/2001' 
  ,37)

Similarly, we’ll need a script to tear down our test data when the test is done. This script will be named Test_GetLatestAvgTemp.teardown.sql:

Use WeatherData
go
If Exists(Select MeasDate
          From raw.StageTemperature
          Where MeasDate = '1/1/2001')
 Delete raw.StageTemperature
 Where MeasDate = '1/1/2001'

Why tear down? It’s not mandatory. I’ve seen lots of production database with test data in them. Some manage it with negative integers in the artificial primary keys, I’ve seen a couple with a bit field that exists solely for the purpose of marking the row as test data. It’s a preference thing for me. I don’t like test data in my production database.

So now the testing script. This will be a functional test. I’m going to look for the normal human body temperature as my test. To conduct the test I’ll run the setup, execute a test script (yet to be written), and then the tear down.

Let’s write the test script. We’ll call it Test_GetLatestAvgTemp.proc.sql and it’ll look like this:

use WeatherData
go
set nocount On
declare @ret real
declare @tbl table
 (Mdate datetime
 ,DegC real
 ,DegF real)
Insert Into @tbl
Exec raw.GetLatestAvgTemp
Set @ret = (Select DegC
            From @tbl)
If (@ret Is Null)
 Print 'Failure: raw.GetLatestAvgTemp returns null'
Else
 If (@ret <> 37)
  Print 'Failure: raw.GetLatestAvgTemp returns '+ Convert(varchar,@ret)
 Else
Print 'Success: raw.GetLatestAvgTemp returns ' + Convert(varchar,@ret)

Finally, we need to put it all together in a test harness we’ll name TestGetLatestAvgTemp.harness.sql:

/*
Notes:
 1. Turn on SQLCMD mode.
 2. If you receive an error like "Incorrect syntax near ':'",
    see Note 1.
 3. Correct the file paths, replacing the default
    with the full path to your files.
*/
UseWeatherData
go
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTempSuccess.setup.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.proc.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.teardown.sql"

Running this script produces the following result:

Success: raw.GetLatestAvgTemp returns 37

I’d really like to expand this test. Right now it merely checks to see that everything goes as expected. That’s good, but most database (and application) developers check for the expected result. This is an important part of testing but it’s not all of it.

Let’s add some stuff. First, we’ll change the harness script to execute a test prior to any setup data. To do thig, modify the harness script to read:

/*
Notes:
 1. Turn on SQLCMD mode.
 2. If you receive an error like "Incorrect syntax near ':'",
    see Note 1.
 3. Correct the file paths, replacing the default
    with the full path to your files.
*/
Use WeatherData
go
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.proc.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTempSuccess.setup.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.proc.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.teardown.sql"

Executing this script returns:

Failure: raw.GetLatestAvgTemp returns null
Success: raw.GetLatestAvgTemp returns 37

Better, but I can do better still. Let’s create another setup script that inserts the incorrect temperature into the table. Name it Test_GetLatestAvgTempFailure.setup.sql and enter the following T-Sql:

Use WeatherData
go
If Not Exists(Select MeasDate
              From raw.StageTemperature
              Where MeasDate = '1/1/2001')
 Insert Into raw.StageTemperature
 (MeasDate
 ,AvgT)
 Values
 ('1/1/2001'
,37.1)

Finally, modify the harness script to read:

/*
Notes:
 1. Turn on SQLCMD mode.
 2. If you receive an error like "Incorrect syntax near ':'",
    see Note 1.
 3. Correct the file paths, replacing the default
    with the full path to your files.
*/
UseWeatherData
go
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.proc.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTempFailure.setup.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.proc.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.teardown.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTempSuccess.setup.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.proc.sql"
:r "C:\Projects\WeatherData\WeatherData\Test_GetLatestAvgTemp.teardown.sql"

Executing this script returns the following results:

Failure: raw.GetLatestAvgTemp returns null

Failure: raw.GetLatestAvgTemp returns 37.1
Success: raw.GetLatestAvgTemp returns 37

Now that’s a test.

This article has been long but we covered a lot of good ground. We talked about scaling deployment. We talked about lots of types of testing and demonstrated three types: unit, functional, and null tests. We used these tests to describe test setup and tear down scripts and test harnesses.

:{> Andy

The Series

The articles in this series:

Total article views: 16133 | Views in the last 30 days: 15
 
Related Articles
FORUM

SSIS Script component, Script Task and project References

How to reference a project in ssis Script component ?

FORUM

Handling NULL in existing project

In a existing project with lot of data, most of the columns are allow NULL, so for handling in the c...

FORUM

To insert data only if it doesnot exist if exist show the table info and print a message

how can i check to see if data exist and insert it only if it doesnot exist and print table info if ...

FORUM

script task refrence a project that reference an externed dll

script task refrence a project that reference an externed dll

SCRIPT

Print The Calendar 2

This script will print the calendar from the given date.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones