SQLServerCentral Article

An Example of Test-Driven Development, Part 3



In Part 1 of this series I discussed the principles of test-driven database development (TD3) along with Try/Catch and SqlCmd functionality. In Part 2, I discussed Solutions as defined in Microsoft Integrated Development Environments (IDEs), deployment, and different types of tests. Part 2 was long. The series continues with demonstrations of refactoring and automating some of the more urbane tests.

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

In Solution Explorer we see the scripts we’ve created so far. I don’t have the best naming convention going here. I don’t like the inconsistencies. For example, I use a three-part file naming convention. The last part identifies the file type (*.sql) and the middle part identifies the object type. The first part contains two pieces of information: a verb describing what I’m doing – Test, Create, or Deploy so far – followed by the object name. Sometimes there’s an underscore after the verb, sometimes not. Sometimes there are two verbs, like Test_Create. Yuck.

I toss and turn in the middle of the night, mumbling incoherently, thinking about the inconsistencies in this solution. It’s inelegant. Worse, it’s confusing.

So let’s fix it.


Cleaning up code without changing functionality is referred to as refactoring. We’re going to do rename refactoring. There are lots of ways to accomplish rename refactoring. We’ll look at two ways here:

· Manual

· Find and Replace

We’ll start by defining fixes for the stuff I don’t like. First, I’m going to change the verb-object portion of the first part of the object names. I’m going to make them all consistent by using the naming convention Verb_Object. Second, there will be only one verb in the first part of the identifier. I’m going to move “Test” out of the first part and into the second identifier.

Let’s start with the first script. Which script is first? (This is a trick question.) Since this is test-driven development, the first script is a test. One way to work through the scripts is to use the deployment script: DeployV1.ver.sql. The first script in DeployV1.ver.sql is Test_CreateWeatherData.db.sql.

In DeployV1.ver.sql, let’s change the lines:

 Print 'Calling Test_CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateWeatherData.db.sql"
Print 'Test_CreateWeatherData.db.sql called.'

To read:

Print 'Calling Create_WeatherData.test.sql...'
:r "C:\Projects\WeatherData\WeatherData\Create_WeatherData.test.sql"
Print 'Create_WeatherData.test.sql called.'

Next, right-click the Test_CreateWeatherData.db.sql and click Rename:

Rename the file

Rename the script to Create_WeatherData.test.sql:

renamed script

We’ve just done manual rename refactoring. You’ve probably done this before.

There are other references to the old name of the script. To find them and change them to the new script name, we have a couple options:

· Find and Replace in Files

· Find and Replace in the Database Project

To Find and Replace in Files, click the Edit dropdown menu, hover over Find and Replace, and then click Replace in Files:

Find and replaceWhen the Find and Replace window displays, enter the “Find what” and “Replace with” fields, then set the “Look in” folder to the directory containing the script files:find and replace dialogThis isn’t the preferred method. Why? For one thing, there’s no telling what other files are in the directory – files that aren’t in the Database Project / Solution. You can, if you so choose, right-click a file in the Queries virtual folder inside Solution Explorer, and remove a file from the project. You do not have to delete it – you’ll be presented with an option:delete or remove?

This means you can save files in the project directory that are not in the database project. For that reason, I prefer to confine a Find and Replace to the objects in the Database Project. To do this click the Edit dropdown menu, hover over Find and Replace, and then click Replace (or simply hold down Ctrl and press H). When the window displays, fill out the Find and Replace fields. The key field is the “Look in” dropdown – set it to “Current Project”:

find and replace scope

This restricts the Find and Replace to files currently in the project.

Using this second method, I’ve refactored the following:

- Test_GetLatestAvgTempFailure.setup.sqlà Setup_GetLatestAvgTempFailure.data.sql

- Test_GetLatestAvgTempSuccess.setup.sqlà Setup_GetLatestAvgTempSuccess.data.sql

- TestGetLatestAvgTemp.harness.sqlà Run_GetLatestAvgTemp.harness.sql

- CreateGetLatestAvgTemp.proc.sqlà Create_GetLatestAvgTemp.proc.sql

- CreateWeatherData.db.sqlà Create_WeatherData.db.sql

- DeployV1.ver.sqlà Deploy_V1.ver.sql

- CreateRaw.schema.sqlà Create_Raw.schema.sql

- CreateStageTemperature.table.sqlà Create_StageTemperature.table.sql

- Test_GetLatestAvgTemp.teardown.sqlà Teardown_GetLatestAvgTemp.data.sql

- Test_CreateGetLatestAvgTemp.proc.sqlà Create_GetLatestAvgTemp.test.sql

- Test_GetLatestAvgTemp.proc.sqlà Validate_GetLatestAvgTemp.test.sql

- Test_CreateStageTemperature.table.sqlà Create_StageTemperature.test.sql

- Test_CreateRaw.schema.sqlà Create_Raw.test.sql

You don’t have to do it – download the updated project using the link at the end of this article.

Automating Urbane Tests

Let’s face it, some of these tests are begging for automation. So let’s automate them already!

Looking at the existence tests, one can argue the database test (Create_WeatherData.test.sql) doesn’t really lend itself to automation. Why?Well, there’s only one of them. It just doesn’t make sense to do automation if there’s only one object.

Or does it?

On the other hand, we have an opportunity to automate all existence tests, so why not? I say we do it!

I write these automation scripts in a certain style. It’s not a best practice, it’s an Andy practice. If it works for you, awesome. If not and your way works for you, awesome – do it your way.

The Create_WeatherData.test.sql script reads:

Use master
If Exists(Select name
           Wherename = 'WeatherData')
 Print 'Success: WeatherData exists.'
 Print 'Failure: WeatherData does not exist.'

To automate this, I use the following script:

set nocount on
declare @CrLf char(2)
set @CrLf = char(13)+ char(10)
select 'use master' + @CrLf + 'go' + @CrLf + @CrLf
union all
select 'If Exists(Select name' + @CrLf +
'          From sys.databases' + @CrLf +
 '          Where name = ''' + db_name() +''')' + @CrLf +
' Print ''Success: ' + db_name() +' exists.''' + @CrLf +
'Else' + @CrLf +
' Print ''Failure: ' + db_name() +' does not exist.''' + @CrLf

A couple things to note:

· I send the results to Text (the Ctrl-T shortcut from Query Analyzer still works).

· I script the USE statement and use UNION ALL to join it to the remainder of the script.

· I create a Char(2) parameter to hold carriage return and line feed (@CrLf) for text formatting.

Notice the generated test condition doesn’t test for the existence of the WeatherData database. At least, not directly. Instead, it tests for the existence of db_name(). I can hear you thinking “Why Andy?” I’m glad you asked! This is now reusable code. I can change the name of the database in the use statement on Line 3 – the one that currently reads useWeatherData – and instantly generate an existence test for any database available on this instance of SQL Server.

The results of this script are:

use master
If Exists(Select name
           From sys.databases
           Where name = 'WeatherData')
  Print 'Success: WeatherData exists.'
Print 'Failure: WeatherData does not exist.'

Cool – this builds Create_WeatherData.test.sql. Save this query in the WeatherData folder, naming it ExistenceTests.autogen.sql. To add it to the WeatherData project, click File àMove ExistenceTests.autogen.sql into à WeatherData:

move script file

Next, let’s auto-generate the Create_Raw.test.sql script, which reads:

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

To generate this, simply add another select statement to ExistenceTests.autogen.sql:

select 'Use ' + db_name()+ @CrLf + 'go'+ @CrLf + @CrLf
 union all
 select 'If Not Exists(Select name' + @CrLf +
 '              From sys.schemas' + @CrLf +
 '              Where name = ''raw'')' + @CrLf +
 ' Print ''Failure: The Raw schema does not exist.''' + @CrLf +
 'Else' + @CrLf +
' Print ''Success: The Raw schema exists.'''+ @CrLf

When executed, it generates the Create_Raw.test.sql script:

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

Cool. Next is Create_StageTemperature.test.sql, but with a twist. StageTemperature is a table. Let’s write this script to auto-generate code for all tables in the WeatherData database.

Here’s the script:

 select 'Use ' + db_name()+ @CrLf + 'go'+ @CrLf + @CrLf
 union all
 'If Not Exists(Select s.name + ''.'' + t.name' + @CrLf +
 '              From sys.tables t' + @CrLf +
 '              Inner Join sys.schemas s On s.Schema_Id = t.Schema_Id' + @CrLf +
 '              Where s.name = ''' + s.name + ''''+ @CrLf +
 '                And t.name = ''' + t.name + ''')'+ @CrLf +
 '  Print ''Failure: ' + s.name + '.'+ t.name +' does not exist.''' + @CrLf +
 'Else' + @CrLf +
 '  Print ''Success: ' + s.name + '.'+ t.name +' exists.''' + @CrLf + @CrLf
fromsys.tables t
inner join sys.schemas s on s.Schema_Id= t.Schema_Id

For the first time since we started auto-generating, we’re using the catalog views in the sys schema. We join sys.schemas and sys.tables to create an existence test for the raw.StageTemperature table, as shown in the output::

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

But, because we’re leveraging the catalog views, this same script will generate existence tests for every table in every schema in the database. Pretty snappy.

Let’s leverage this to write a final existence test auto-generator for all stored procedures in all schemas in the database:

 select 'Use ' + db_name()+ @CrLf + 'go'+ @CrLf + @CrLf
 union all
 'If Not Exists(Select s.name + ''.'' + p.name' + @CrLf +
 '              From sys.procedures p' + @CrLf +
 '              Inner Join sys.schemas s On s.Schema_Id = p.Schema_Id' + @CrLf +
 '              Where s.name = ''' + IsNull(s.name,'NullSchema')+ '''' + @CrLf +
 '                And p.name = ''' + p.name + ''')'+ @CrLf +
 '  Print ''Failure: ' + s.name + '.'+ p.name +' does not exist.''' + @CrLf +
 'Else' + @CrLf +
 '  Print ''Success: ' + s.name + '.'+ p.name +' exists.''' + @CrLf + @CrLf
 from sys.procedures p
inner join sys.schemas s on s.Schema_Id= p.Schema_Id

The results of this script are:

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


None of this is ground-breaking T-Sql. But it all demonstrates one way to implement a test-driven database development methodology.

:{> Andy

The Series

The articles in this series:


3.78 (9)

You rated this post out of 5. Change rating




3.78 (9)

You rated this post out of 5. Change rating