Skip to content

Instantly share code, notes, and snippets.

@inammathe
Created September 19, 2018 16:13
Show Gist options
  • Save inammathe/ba10f325cd595eb1c11c64a954c2a001 to your computer and use it in GitHub Desktop.
Save inammathe/ba10f325cd595eb1c11c64a954c2a001 to your computer and use it in GitHub Desktop.
Runs scripts against a database using DbUp
param(
$TargetDatabase,
$PackagePath,
$TargetServer,
$JournalToSqlTable,
$ConnectionTimeout,
$ExecutionTimeout,
$Static_ScriptsDir,
$Sandpit_ScriptsDir,
$TestEnv_ScriptsDir,
$Finalisation_ScriptsDir
)
function Set-Console {
$Global:StringBuilder = [System.Text.StringBuilder]::new()
$Global:StringWriter = [System.IO.StringWriter]::new($Global:StringBuilder)
[System.Console]::SetOut($Global:StringWriter)
}
function Close-Console {
$Global:StringBuilder.Clear() | Out-Null
$Global:StringWriter.Close() | Out-Null
}
function Write-Console {
$Global:StringBuilder.ToString() | Write-Host
$Global:StringBuilder.Clear() | Out-Null
}
function Invoke-DbUpScripts {
param(
$ScriptPath,
$ScriptType,
$TargetServer,
$TargetDatabase,
$JournalToSqlTable,
$ConnectionTimeout,
$ExecutionTimeout,
$RawScript
)
$dbUp = @()
$dbUp = [DbUp.DeployChanges]::To
# Build the Sql connection
$dbUp = [SqlServerExtensions]::SqlDatabase($dbUp, "server=$TargetServer;database=$TargetDatabase;Trusted_Connection=True;Connection Timeout=$ConnectionTimeout")
if($RawScript)
{
$dbUp = [StandardExtensions]::JournalTo($dbUp, [DbUp.Helpers.NullJournal]::new())
$dbUp = [StandardExtensions]::WithScript($dbUp, $RawScript.name, $RawScript.script)
}
else
{
# Specify the DbUp journal table if supplied
if ($JournalToSqlTable) {
$dbUp = [DbUp.Contoso.ContosoSqlServerExtensions]::JournalToContosoSqlTable($dbUp, 'dbo', $JournalToSqlTable, $ScriptPath, $ScriptType)
$dbUp = [DbUp.Contoso.ContosoSqlServerExtensions]::WithContosoFilter($dbUp, 'dbo', $JournalToSqlTable)
}
else
{
$dbUp = [StandardExtensions]::JournalTo($dbUp, [DbUp.Helpers.NullJournal]::new())
}
# Run scripts within a directory or a single script file depending on what was supplied
if((Get-Item $scriptPath).PSIsContainer) {
$dbUp = [StandardExtensions]::WithScriptsFromFileSystem($dbUp, $scriptPath)
}
else
{
$dbUp = [StandardExtensions]::WithScript($dbUp, (Get-Item $scriptPath | Select-Object -ExpandProperty Name), (Get-Content $scriptPath | Out-String))
}
}
$dbUp = [StandardExtensions]::LogToConsole($dbUp)
$dbUp = [StandardExtensions]::WithTransactionPerScript($dbUp)
$dbUp = [StandardExtensions]::WithExecutionTimeout($dbUp, $ExecutionTimeout)
$UpgradeResult = $dbUp.Build().PerformUpgrade()
Write-Console
Write-Output $UpgradeResult
}
function Write-Results {
param(
[Parameter(Mandatory, ValueFromPipelineByPropertyName, ValueFromPipeline)]
[ValidateNotNullorEmpty()]
[Dbup.Engine.DatabaseUpgradeResult]
$UpgradeResult
)
process
{
# Display the upgrade results - Does some nice formatting and highlighting if running via OctopusDeploy
[DbUp.OctopusDeployExtensions]::WriteExecutedScriptsToOctopusTaskSummary($UpgradeResult)
Write-Console
Write-Host "`tSuccess: $($UpgradeResult.Successful)"
if($UpgradeResult.Error)
{
Write-Host "`n`tErrors:"
Throw $UpgradeResult.Error
}
}
}
# Add assemblies
Add-Type -Path ("$PackagePath\dbUp-core.dll")
Add-Type -Path ("$PackagePath\dbUp-sqlserver.dll")
Add-Type -Path ("$PackagePath\dbUp-Contoso.dll")
# Set console settings
Set-Console
# Iterate over a ordered hash table containing the paths to scripts we need to run
([ordered]@{
Static = $Static_ScriptsDir
Sandpit = $Sandpit_ScriptsDir
TestEnv = $TestEnv_ScriptsDir
Finalisation = $Finalisation_ScriptsDir
}).GetEnumerator() | ForEach-Object {
Write-Host "`nRunning $($_.key) Scripts"
Write-Verbose "Script directory: $($_.value)"
if($_.key -eq 'Static')
{
# Run these scripts everytime
Invoke-DbUpScripts `
-ScriptPath $_.value `
-ScriptType $_.key `
-TargetServer $TargetServer `
-TargetDatabase $TargetDatabase `
-ConnectionTimeout $ConnectionTimeout `
-ExecutionTimeout $ExecutionTimeout |
Write-Results
}
else
{
# Only run once
Invoke-DbUpScripts `
-ScriptPath $_.value `
-ScriptType $_.key `
-TargetServer $TargetServer `
-TargetDatabase $TargetDatabase `
-JournalToSqlTable $JournalToSqlTable `
-ConnectionTimeout $ConnectionTimeout `
-ExecutionTimeout $ExecutionTimeout |
Write-Results
}
}
Close-Console
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment