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 5

By Andy Leonard, (first published: 2009/08/20)

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. In Part 3, I demonstrated refactoring and automating existence tests. In Part 4, we looked at database deployment and deployment artifacts. In this article, I examine database versioning.

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. Once open, click ViewàSolution Explorer to display the solution artifacts:

Weather Data Project in Solution Explorer

More Evidence

There are several other ways to store evidence of database deployments. We’ll look at three here:

  • A table in the database;
  • Extended properties;
  • A script to detect the version.

Exhibit A: The Table

I like to create a table in the dbo schema of the database and name it something obvious like dbo.DBVersion. It usually looks something like this:

Use WeatherData
go
If Not Exists(Select name
               From sys.tables
               Where name= 'DBVersion')
  begin
   Print ' > Creating DBVersion table'
   Create Table DBVersion
    (
     ID int Identity(1,1)Not Null Constraint PK_DBVersion Primary Key Clustered(ID)
    ,DeploymentDateTime datetime Constraint DF_DBVersion_DeploymentDateTime Default(GetDate())
    ,Version varchar(20)
    ,VersionDescription varchar(255)
    ,DeploymentStatus char(1)
    )
   Print ' > DBVersion table created'
  end
 Else
  Print ' > DBVersion table exists.'
go
If Exists(Select name
           From sys.procedures
           Where name = 'AddNewVersion')
  begin
   Print ' > Dropping AddNewVersion stored procedure'
   Drop Procedure AddNewVersion
  end
  Print ' > Creating AddNewVersion stored procedure'
go
Create Procedure AddNewVersion
   @Version varchar(20)
   ,@VersionDescription varchar(255)= Null
   ,@DeploymentStatus char(1)= 'R'
  As
   begin
    Insert Into DBVersion
    (Version
    ,VersionDescription
    ,DeploymentStatus)
    Values
    (@Version
    ,@VersionDescription
    ,@DeploymentStatus)
   end
go
  Print ' > AddNewVersion stored procedure created'
go
If Exists(Select name
           From sys.procedures
           Where name = 'UpdateLastVersion')
  begin
   Print ' > Dropping UpdateLastVersion stored procedure'
   Drop Procedure UpdateLastVersion
  end
  Print ' > Creating UpdateLastVersion stored procedure'
go
Create Procedure UpdateLastVersion
   @DeploymentStatus char(1)= 'S'
  As
   begin
    declare @LastVersionID int
    set @LastVersionID = (SelectMax(ID)
                         From DBVersion)
    Update DBVersion
    Set DeploymentStatus = @DeploymentStatus
    Where ID = @LastVersionID
   end
go
  Print ' > UpdateLastVersion stored procedure created'
go

The easiest way to interact with this script is to create a couple stored procedures to interact with the table.

I added this script to the WeatherData project as Create_DBVersion.table.sql. Next, let’s modify the Deploy_V1.1 script, adding a call to this new Create script near the top:

Code in SSMS

In Deploy_V1, however, there is no database until the Create_WeatherData.db.sql script is executed (that creates the database), so the Create_DBVersion script has to run after there’s a database. There’s a little more to it than that, and this is a good time to talk about it.

“:r” is a command in SqlCmd. SqlCmd commands are executed immediately. So you read this and think “Cool, all will be fine so long as I modify the script as shown here:”

Code in SSMS

And you’re almost right.

While commands are executed immediately (and they are – I promise), this particular command is the Parse command. Parse accepts a filename as an argument, and adds the contents of that file to the statement cache. The statement cache is executed when the script hits a batch terminator, and “Go” is a batch terminator.

If we execute the script as shown above, it will fail. To make it succeed, we need to terminate the batch after the database is created but before the Create_DBVersion.table.sql script:

Code with PARSE command.

This script will now execute successfully.

One other thing: By tying this snippet to the deployment script I am intentionally coupling a database version (and its label) to an instance of script execution. That may not sound very important, but it can be if you’re attempting to trace why one instance of your database is performing differently from the others; or if you’re in an environment that requires intense auditing.

Next, add a call to AddNewVersion to each deployment script. For Deploy_V1.ver.sql, I’ll add the following statements just after the call to the Create_DBVersion.table.sql script:

Declare @Version varchar(20)
Set @Version = '1.0'
Exec AddNewVersion @Version

For Deploy_V1.1.ver.sql, I modify the Set statement, setting the value of @Version to '1.1'.

At the bottom of the Deploy_V1.ver.sql and Deploy_V1.1.ver.sql scripts – just before the last of the Print statements – I add the following call:

Exec WeatherData.dbo.UpdateLastVersion

To test this new functionality, execute the script MasterDeploy_V1.ver.sql. Then execute the following script to view the results:

select * from WeatherData.dbo.DBVersion

My results look like this:

Deployment results

Exhibit B: Extended Properties

Extended properties rock. They allow you to annotate database objects in any number of ways. I’ve seen bunches of cool uses for extended properties, including a great presentation by Kevin Hazzard to the Richmond SQL Server Users Group a few months ago.

Extended properties are extremely flexible. To add an extended property, we use the sp_addextendedproperty system stored procedure. To update the values in an extended property, we use the sp_updateextendedproperty system stored procedure and we use sp_dropextendedproperty to delete the extended property. You can list extended properties and their values by querying fn_listextendedproperty.

Extended properties include a three-level hierarchy described as levels. According to Books Online (http://msdn.microsoft.com/en-us/library/ms190243.aspx), valid values for Level 0 “are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, and NULL.” Valid values for Level 1 “are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.” Valid values for Level 2 “are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.”

You can learn more about extended properties in Books Online. To apply an extended property to the database, simply pass in Nulls for the as shown here:

exec sp_addextendedproperty'Version', '1.0'

To view the values of extended properties on the database, query the function:

select *
 from fn_listextendedproperty(Default
                            ,Default
                            ,Default
                            ,Default
                            ,Default
                           ,Default
,Default)

The results look like this:

Extended Properties Results

You can update the value of an extended property:

exec sp_updateextendedproperty 'Version','1.1'

You can remove an extended property:

exec sp_dropextendedproperty 'Version'

Open the script Deploy_V1.ver.sql and modify the 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.
 */
 Print '------------------------------------------------'
 Print '  Deployment Log:  WeatherData'
 Print '          Script:  Deploy_V1.ver.sql (V1)'
 Print '         Version:  1.0'
 Print '  Start DateTime:  '+ convert(varchar,GetDate(), 101) + ' '+ convert(varchar,GetDate(), 108)
 Print '          Server:  '+ @@ServerName
 Print '     Executed By:  ' + Original_Login()
 Print '------------------------------------------------'
 Print ''
 Print ' [V1] Deploying WeatherData v1.'
 Print ''
 Print ' [V1] Calling Create_WeatherData.test.sql...'
:r $(ScriptPath)Create_WeatherData.test.sql
 Print ' [V1] Create_WeatherData.test.sql called.'
 Print ''
 Print ' [V1] Calling Create_WeatherData.db.sql...'
:r $(ScriptPath)Create_WeatherData.db.sql
 Print ' [V1] Create_WeatherData.db.sql called.'
go
 Print ''
 Print ' [V1] Calling Create_DBVersion.table.sql...'
:r $(ScriptPath)Create_DBVersion.table.sql
 Print ' [V1] Create_DBVersion.table.sql called.'
 Declare @Version varchar(20)
 Declare@VersionDescription varchar(255)
 Set @Version = '1.0'
 Set@VersionDescription = 'Deployed WeatherData database'
 ExecAddNewVersion @Version, @VersionDescription
 Print ' > Adding extended property'
 Exec sp_addextendedproperty'Version', '1.0'
 Print ''
 Print ' [V1] Calling Create_WeatherData.test.sql...'
:r $(ScriptPath)Create_WeatherData.test.sql
 Print ' [V1] Create_WeatherData.test.sql called.'
 ExecWeatherData.dbo.UpdateLastVersion
 Print ''
 Print '------------------------------------------------'
 Print ' [V1] WeatherData v1 deployed.'
 Print '------------------------------------------------'

As you can see, extended properties allow us to label (or “stripe”) the database with version information. We can also label every object in the database. The database is the only object we deploy in version 1.0. In the deployment script for version 1.1, we deploy the Raw schema, StageTemperature table, and GetLatestAvgTemp stored procedure.

Open the Create_Raw.schema.sql and modify the script to read:

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)
   exec sp_addextendedproperty 'Version','1.1', 'SCHEMA','Raw'
  end
 Else
 Print ' > Raw schema exists.'

Make similar modifications to Create_StageTemperature.table.sql and Create_GetLatestAvgTemp.proc.sql; adding the following lines of code, respectively:

exec sp_addextendedproperty 'Version','1.1', 'SCHEMA','Raw', 'TABLE','StageTemperature'
execsp_addextendedproperty'Version', '1.1','SCHEMA', 'Raw','PROCEDURE', 'GetLatestAvgTemp'

Let’s test it! Drop the database and then re-execute MasterDeploy_V1.ver.sql. To query the values, execute the following queries:

select *
 from fn_listextendedproperty(Default
                            ,'SCHEMA'
                            ,'raw'
                            ,Default
                            ,Default
                            ,Default
,Default) select * from fn_listextendedproperty(Default ,'SCHEMA' ,'raw' ,'TABLE' ,'StageTemperature' ,Default
,Default) select * from fn_listextendedproperty(Default ,'SCHEMA' ,'raw' ,'PROCEDURE' ,'GetLatestAvgTemp' ,Default
,Default)

Exhibit C: A Script

One last method we’ll examine is using a script to detect the version. This is the least invasive method – you simply check the database to see if the objects from the latest change exist in the database. Depending on the change type, the detection scripts can get interesting. Also, as the total number of changes increases over time, you may need to modify portions of the script to detect earlier versions. In the worst-case, it may simply be impossible to detect certain changes (for instance, when all changes in a particular revision have been backed out of a future version).

Here’s a script to detect our current database version:

Print '------------------------------------------------'
Print '  Version Test:  WeatherData'
Print '        Script:  VersionTests.sql'
Print '       Version:  1.0, 1.1'
Print 'Start DateTime:  '+ convert(varchar,GetDate(), 101) + ' '+ convert(varchar,GetDate(), 108)
Print '        Server:  '+ @@ServerName
Print '   Executed By:  ' + Original_Login()
Print '------------------------------------------------'
Print ''
Use WeatherData
go
 If 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 ' > Version 1.1 changes detected [V1.1]'
 Use master
go
If Exists(Select name
           From sys.databases
         Where name ='WeatherData')
Print' > Version 1.0 changes detected [V1]'

Conclusion

In this article, we’ve looked at three ways to create, collect, and maintain database version information. I use all three in practice. My first choice is to use a DBVersion table, my second choice is a script to detect changes. Most of the time I use these together. Although they work well, I use extended properties the least. I have no real reason for this, it’s just a preference.

:{> Andy

The Series

The articles in this series:

Resources:

WeatherDataProject.zip
Total article views: 10155 | Views in the last 30 days: 17
 
Related Articles
FORUM

database diagram print on one page

database diagram print

FORUM

Versioning database release and permission scripts

How do you version permission scripts

BLOG

A Script A Day - Day 6 - Drop and Create Database Snapshots

Today's Script will drop all database snapshots and create a database snapshot for all online read w...

FORUM

script to create indexes existing in a database

script to create indexes existing in a database

FORUM

Create Database

Create database taking forever

 
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