Runs scripts against a database using DbUp

Invoke-DbUp.ps1

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