I just saw this post regarding why you should put your database into source control.
Unfortunately, it doesn’t get into much of the how, a question many project managers would love to point their developers to.
I’ll remedy that for you.
Since 2004, I have been using this practice. Ok, so I was first forced to, but it really helped pave the way for some really good developer habits (even if it meant doing something the company didn’t want to do… more on that later).
The first program I used was a windows application with a very simple screen configuration. It was the details needed to connect to a SQL Server (server, username, password, catalog), and an additional field for an XML configuration file.
This file essentially looked like this:
<configuration>
<Schemas>
<File>Schemas\File01.sql</File>
<File>Schemas\File02.sql</File>
</Schemas>
<Packages>
<File>Packages\File01.sql</File>
<File>Packages\File02.sql</File>
</Packages>
</configuration>
Again, this is a basic concept.
A “Schema” file was to only be run once. So things like table structures, indexes, constraints, etc, would go into this file. After the program ran, it would save the file path into a database table (that it created if it didn’t exist), and would check before running each time so as to not run more than once.
A “Package” file would be run every time. This covers Stored Procedures, Views, Functions, etc. At the beginning of the file, it contained a conditional drop statement if it already existed. This is probably the most controversial part of the program, since the stored procedures will lose it’s compiled run status each time you run a deployment. To that I say: “So?”.
I have taken the concept and written it as a C# windows app, updating it as time went on, and giving it to the companies I worked at. This has drastically changed how development goes. It makes finding differences in updates easier, makes searching for certain table calls faster, and puts you in the right direction for automating your deployments.
The updates I have given it were to rename the “Schemas” to “RunOnce”, “Packages” to “RunAlways”, and given it the concept of folders, in addition to files. This makes one of our multi-tenant projects with our Indian off-shore team development efforts go by much faster. It’s easier to update my software to accomodate their years of developer patterns, than to have them change their entire paradigm to fit my one small feature. I personally think it’s better this way, as the software now fits multiple programming styles, not just the one.
The new XML looks more like this:
<Database>
<RunOnce>
<File>Schemas\File01.sql</File>
<File>Schemas\File02.sql</File>
<!-- OR -->
<Directory>Schemas</Directory>
</RunOnce>
<RunAlways>
<File>Packages\File01.sql</File>
<Directory>Packages\Views</Directory>
<Directory>Packages\Functions</Directory>
<Directory>Packages\StoredProcedures</Directory>
</RunAlways>
</Database>
Anyway, I’ll share some of the pseudo code in how to get this to work properly, so you can get this going in your environment.
function SQL-Create-Connection($connectionstring)
{
# This will return a new System.Data.SqlClient.SqlConnection object based on the connection string
}
function SQL-Get-Connection($server, $catalog, $username, $password, $windows)
{
# This will return a new System.Data.SqlClient.SqlConnection object based on the individual parameters
}
function SQL-Check-Or-Create-DB-Version-Table($connection)
{
# This will create the following database table if it doesn't already exist
# db_version
# filename VARCHAR(512) NOT NULL
}
function SQL-Execute-NonVersion-File($connection, $fullPath)
{
# This will open a file, split the contents based on "GO" statements, and execute the individual parts.
}
function SQL-Execute-Version-File($connection, $path, $fullPath)
{
SQL-Check-Or-Create-DB-Version-Table -connection $connection
# Now find out if the $path (relative to the XML configuration file) already exists
# if ($Record -eq $null)
# {
# try
# {
# SQL-Execute-NonVersion-File -connection $connection -fullPath $fullPath
# # Now insert the file into the version table
# }
# catch [Exception]
# {
# WRITE-WARNING "Problem processing file $fullPath"
# WRITE-WARNING $_.Exception.Message
# Throw
# }
# }
}
function SQL-Execute-Table($connection, $commandText)
{
# This will take the $commandText and create a table result for it
}
function SQL-Execute-NonQuery($connection, $commandText)
{
# This will take the $commandText and just execute it
}
function SQL-Execute-Scalar($connection, $commandText)
{
# This will take the $commandText and return the scalar after execution
}
function DB-Parse-Deployment-XML($XmlDocumentPath, $connection)
{
# Open the XML Document, and get its directory to handle the relative paths
$db_directory = $(Get-Item -path "$XmlDocumentPath").Directory.FullName
$db_directory_regex = $("$($db_directory)\" -replace "\\", "\\")
$db_xml = Get-Content $XmlDocumentPath
try
{
# Loop through all of the RunOnce's...
foreach ($runonce in $db_xml.Database.RunOnce)
{
foreach ($ro_file in $runonce.File)
{
$ro_file = $ro_file -replace "/", "\"
$ro_file_full = "$db_directory\$ro_file"
if (Test-Path $ro_file_full)
{
$results = SQL-Execute-Version-File -connection $connection -path $ro_file -fullPath $ro_file_full
# Write-Host "RunOnce Relative File $ro_file"
# Write-Host "RunOnce Full File $ro_file_full"
}
else
{
Write-Warning "RunOnce file was not found: $ro_file_full"
}
}
foreach ($ro_directory in $runonce.Directory)
{
$ro_directory = $ro_directory -replace "/", "\"
$ro_directory_full = "$db_directory\$ro_directory"
if (Test-Path $ro_directory_full)
{
$scripts = Get-ChildItem "$ro_directory_full\*" -include *.sql -recurse
foreach ($script in $scripts)
{
$Relative_Script = $script -replace $db_directory_regex, ""
$results = SQL-Execute-Version-File -connection $connection -path $Relative_Script -fullPath $script
}
}
else
{
Write-Warning "RunOnce Directory was not found: $ro_directory_full"
}
}
}
# Loop through all of the RunAlways'...
foreach ($runalways in $db_xml.Database.RunAlways)
{
foreach ($ra_file in $runalways.File)
{
$ra_file = $ra_file -replace "/", "\"
$ra_file_full = "$db_directory\$ra_file"
if (Test-Path $ra_file_full)
{
$results = SQL-Execute-NonVersion-File -connection $connection -fullPath $ra_file_full
}
else
{
Write-Warning "RunAlways file was not found: $ra_file_full"
}
}
foreach ($ro_directory in $runalways.Directory)
{
$ro_directory = $ro_directory -replace "/", "\"
$ro_directory_full = "$db_directory\$ro_directory"
if (Test-Path $ro_directory_full)
{
$scripts = Get-ChildItem "$ro_directory_full\*" -include *.sql -recurse
foreach ($script in $scripts)
{
$results = SQL-Execute-NonVersion-File -connection $connection -fullPath $script
}
}
else
{
Write-Warning "RunAlways directory was not found: $ro_directory_full"
}
}
}
}
catch
{
Write-Error "Error in DB-Parse-Deployment-XML..."
}
}
It took me a few hours to figure out what the contents of the first few methods were, but I think some of the fun as a developer is figuring out what those are. Since you have a very basic roadmap, it shouldn’t take too long to get this going all on your own.
Please feel free to let me know what you think in either the comments, or by shooting me an email from my contact page.
Thanks, and happy coding!
RJ
RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.
Leave a Reply