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 3

By Andy Leonard, (first published: 2009/07/13)

Introduction

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.

Refactoring

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
go
If Exists(Select name
           Fromsys.databases
           Wherename = 'WeatherData')
 Print 'Success: WeatherData exists.'
Else
 Print 'Failure: WeatherData does not exist.'

To automate this, I use the following script:

set nocount on
useWeatherData
go
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
go
If Exists(Select name
           From sys.databases
           Where name = 'WeatherData')
  Print 'Success: WeatherData exists.'
Else
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
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.'

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
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.'

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
 select
 '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
go
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.'
Else
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
 select
 '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
go
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.'
Else
Print 'Success: raw.GetLatestAvgTemp exists.'

Conclusion

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:

Total article views: 10453 | Views in the last 30 days: 10
 
Related Articles
FORUM

script to create indexes existing in a database

script to create indexes existing in a database

FORUM

Need a script to create a new user based on existing user's permission

create a nre user as copy from existing user

FORUM

How to script existing DB Maintenance Plans

I want to script existing DB Maintenance Plans and avoid creating them manually on new instance of S...

BLOG

Linked Server created using a script is missing Product name and Data source fields !

Today I will discuss about an issue which was observed while creating a linked server via script. T...

FORUM

Using Script Task to check if Folder Exists

Using Script Task to check if Folder Exists

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