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:
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:
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:
Deploying Version 1.1
There are a couple ways to approach deploying the second version of a database project:
- Add to the existing script.
- 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:
