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:
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 script to Create_WeatherData.test.sql:
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:
When 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:
This 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:
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”:
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:
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:
