2011-12-03 Database versioning updated

2011-12-03 database-versioning-updated

# Title: Database versioning updated
# Author: Mikael Lundin
# Link: http://blog.mikaellundin.name/2011/12/03/database-versioning-updated.html

Build-Database.ps1

# Will create a new database and add table to manage versions
Function Build-Database ([string]$sqlServer, [string]$databaseName)
{
 # http://sqlblog.com/blogs/allen_white/archive/2008/04/28/create-database-from-powershell.aspx
 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
 $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
 $dbname = $databaseName
  
 # Instantiate the database object and create database
 $db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
 $db.Create()
  
 # Create table and column for handling database version
 $db.ExecuteNonQuery("CREATE TABLE [$databaseName].[dbo].[Settings] ([DatabaseVersion] int NOT NULL)");
 $db.ExecuteNonQuery("INSERT INTO [$databaseName].[dbo].[Settings] ([DatabaseVersion]) VALUES (0)");
}

Drop-Database.ps1

# Will drop the database if it exists
Function Drop-Database ([string]$sql_server, [string]$database_name)
{
 try {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
  $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sql_server
  $s.Refresh();
  $s.KillDatabase($database_name);
  Write-Host "Killed database $databaseName"
 }
 catch {
  Write-Error "Tried to delete database $databaseName but failed, probably because it did not exist"
 }
}

Exists-Database.ps1

# Determine if database exists
Function Exists-Database ([string]$sql_server, [string]$database_name)
{
 $exists = $FALSE
 try {
  # Connect and run a command using SMO
  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
   
  # Get server reference
  $s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sql_server
   
  foreach($db in $s.databases)
  {
   # Database exists?
   if ($db.name -eq $database_name) {
    $exists = $TRUE
   }
  }
 }
 catch {
  Write-Error "Failed to connect to $sql_server"
 }
 
 # Return
 $exists
}

Get-Database-Version.ps1

Function Get-Database-Version ([string]$connectionString)
{
 [System.Data.SqlClient.SqlConnection]::ClearAllPools()
 
 $sql = "SELECT TOP 1 [DatabaseVersion] FROM [Settings]"
 ## Connect to the data source and open it
 $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
 $connection.Open()
 
 $command = New-Object System.Data.SqlClient.SqlCommand $sql,$connection
 $version = $command.ExecuteScalar();
  
 $connection.Close()
 $version
}
 
Function Validate-Connection ([string]$connectionString)
{
 [System.Data.SqlClient.SqlConnection]::ClearAllPools()
  
 try {
  ## Connect to the data source and open it
  $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
  $connection.Open()
  $connection.Close()
  $TRUE
 }
 catch {
  $FALSE
 }
}
 
Function Execute-Sql-Query ([string]$connectionString, [string]$sql)
{
 [System.Data.SqlClient.SqlConnection]::ClearAllPools()
  
 ## Connect to the data source and open it
 $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
 $connection.Open()
 
 $server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
 $server.ConnectionContext.ExecuteNonQuery($sql) | out-null
  
 $connection.Close()
}

Update-Database.ps1

# Will update the database to the most current version in the database directory
Function Update-Database ([string]$connection_string, [string]$database_directory)
{
 
 $databaseVersion = Get-Database-Version $connection_string
  
 # Get all source files that have higher database version number
 $files = Get-ChildItem "$database_directory\*.sql" | Where { [int]::Parse($_.name.Substring(0, 4)) -gt $databaseVersion }
  
 # For each of those files, run query on database
 foreach ($file in $files)
 {
  $fileName = $file.name
  Write-Host "Apply update script: $fileName"
   
  # Get-Content returns a string array of all the lines. We join that into a single string
  $fileContents = Get-Content "$file"
  $sql = [string]::Join([Environment]::NewLine, $fileContents);
  Execute-Sql-Query $connectionString $sql
   
  # Get this version number
  $version = [int]::Parse($fileName.Substring(0, 4))
  
  # Update the settings database with current version number
  Execute-Sql-Query $connectionString "UPDATE [Settings] SET [DatabaseVersion] = $version"
 }
}