Generate ScriptSync wrapped files

psake.ps1

properties {
    $slnDir             = Get-Item ..
    $dbDir              = Join-Path $slnDir db
    $scriptDir              = Join-Path $slnDir scripts
    $surgeDir           = Join-Path $slnDir ../Surge
    $databasePrefix     = "DynamicBuilder"
    $databaseSuffix     = SetDefault $databaseSuffix "_Dev"
    $dbEnvironment      = SetDefault $dbEnvironment $Env:RecreateDatabase_Default_Environment
    $testRunner         = SetDefault $testRunner 'C:\Program Files\Gallio\bin\Gallio.Echo.exe'
    $databaseBackupPath = SetDefault $databaseBackupPath 'C:\Backup\'
    $skipBackup         = SetDefault $skipBackup $false
    $scriptSyncPath     = "$surgeDir/Build/ScriptSync/ScriptSync.exe"
    $migrationsPath     = "$dbDir/Migrations"

    $dbConfiguration = @{
        local = @{
            ServerInstance = "storage";
            Database = $databasePrefix + $databaseSuffix;
        };
        
        local_express = @{
            ServerInstance = ".\SQLEXPRESS";
            Database = $databasePrefix + $databaseSuffix;
        };

        local_full = @{
            ServerInstance = ".\";
            Database = $databasePrefix + $databaseSuffix;
        };

        qa = @{
            ServerInstance = "tcp:50.57.84.227,14044";
            Database = $databasePrefix + $databaseSuffix;
            Username = $Env:DynamicBuilder_Database_Username;
            Password = $Env:DynamicBuilder_Database_Password;
        };

        dennys_qa = @{
            ServerInstance = "tcp:50.57.84.227,14044";
            Database = $databasePrefix + "_Dennys" + $databaseSuffix;
            Username = $Env:DynamicBuilder_Database_Username;
            Password = $Env:DynamicBuilder_Database_Password;
        };

        sears_qa = @{
            ServerInstance = "tcp:50.57.84.227,14044";
            Database = $databasePrefix + "_Sears" + $databaseSuffix;
            Username = $Env:DynamicBuilder_Database_Username;
            Password = $Env:DynamicBuilder_Database_Password;
        };
    }
    
    $sqlServer      = $dbConfiguration[$dbEnvironment]
    $buildType      = SetDefault $buildType "Debug"
    $buildPlatform  = SetDefault $buildPlatform "x64"
    $verbosity      = SetDefault $verbosity "quiet"
    $loggerParams   = SetDefault $loggerParams "ErrorsOnly"
    $publishProfile = SetDefault $publishProfile "db-qa"
    $deployUser     = SetDefault $deployUser $Env:DynamicBuilder_Publish_Username
    $deployPass     = SetDefault $deployPass $Env:DynamicBuilder_Publish_Password
}

Framework "4.0"
# Framework "4.0x64"

task default -depends Build

################################
# Build Tasks
################################
task Clean {
    exec { msbuild $slnDir/DynamicBuilder.sln /t:Clean /v:$verbosity /p:Configuration=$buildType }
}

task Build -depends Clean {
    echo "Performing $buildType - $buildPlatform build..."
    exec { msbuild $slnDir/DynamicBuilder.sln /v:$verbosity /clp:$loggerParams }
    exec { & "$slnDir\..\Surge\Build\Write-HgVersion.ps1" -projectDir $slnDir/DynamicBuilder -repoDir $slnDir }
}

task Publish -depends RegenerateSchema {
    . "$surgeDir/Surge.Platform.Database/Build/SqlServerSnapins.ps1" 
    [System.Data.SqlClient.SqlConnection]::ClearAllPools()    

    echo "Publishing DynamicBuilder using profile: $publishProfile..."
    exec { msbuild $slnDir/DynamicBuilder/DynamicBuilder.csproj /p:Configuration=Release /v:$verbosity /t:WebPublish /p:PublishProfile=$publishProfile /p:AllowUntrustedCertificate=True /p:UserName=$deployUser /p:Password=$deployPass }

    # Back up, and optionally update database
    RunBackup
    if ($wipeData -eq $true) {
        $databaseName = $sqlServer.Get_Item("Database")
        echo "Publishing $databaseName database..."
        exec { & "$dbDir/RecreateDatabase.ps1" -Environment $dbEnvironment } 

        # Map database user
        echo "Running MapDatabaseUser.sql ..."
        $server   = $sqlServer.Get_Item("ServerInstance")
        $username = $sqlServer.Get_Item("Username")
        $password = $sqlServer.Get_Item("Password")
        $query    = [Io.File]::ReadAllText((Resolve-Path "$dbDir/publish/MapDatabaseUser.sql"))
        sql -Query "$query" -Variable "DatabaseName=$databaseName" -ServerInstance $server -Username $username -Password $password -Database "master" 
    }
    if ($runMigrations -eq $true) {
        RunScriptSync
    }

}

################################
# Test Tasks
################################
task Tests -depends Build, StartInDesignServer {
     exec {& $testRunner "$slnDir\DynamicBuilder.Tests\bin\Debug\DynamicBuilder.Tests.exe"}
}

################################
# Database Tasks
################################
task RegenerateSchema -depends Build {
    $assemblyPath = "$slnDir\DynamicBuilder.Domain\bin\Debug\DynamicBuilder.Domain.dll"
    $resultPath   = "$dbDir\1.0\02 - GeneratedSchema.sql"    
    exec {. "$surgeDir\\Build\ddlgen\DdlGenerator.Console.exe" $assemblyPath > $resultPath }
}

task BackupDatabase -precondition {  return ($skipBackup -eq $false)} {
    RunBackup
}

function RunBackup {
    if ($skipBackup -eq $true) {
        return        
    }

    . "$surgeDir/Surge.Platform.Database/Build/SqlServerSnapins.ps1" 
    [System.Data.SqlClient.SqlConnection]::ClearAllPools()

    echo "Backing up $databaseName database..."
    $server   = $sqlServer.Get_Item("ServerInstance")
    $username = $sqlServer.Get_Item("Username")
    $password = $sqlServer.Get_Item("Password")
    $databaseName = $sqlServer.Get_Item("Database")
    $query    = [Io.File]::ReadAllText((Resolve-Path "$dbDir/publish/BackupTarget.sql"))
    if ($username -eq $null) {
        sql -Query "$query" -Variable "DatabaseName='$databaseName'", "DatabaseBackupLocation='$databaseBackupPath'" -ServerInstance $server -Database "master" 
    }
    else {
        sql -Query "$query" -Variable "DatabaseName='$databaseName'", "DatabaseBackupLocation='$databaseBackupPath'" -ServerInstance $server -Username $username -Password $password -Database "master" 
    }
}

task RecreateBaseDatabase -depends BackupDatabase {
    & "$dbDir/RecreateDatabase.ps1" $dbEnvironment
}
task RunMigrations {
    RunScriptSync
     . "$surgeDir/Surge.Platform.Database/Build/SqlServerSnapins.ps1" 
    [System.Data.SqlClient.SqlConnection]::ClearAllPools()    

    sql @sqlServer "SELECT ApplicationDataVersion, Name FROM Surge.DataUpdate"
}

task RecreateDatabase -depends RecreateBaseDatabase, RunMigrations {
}

################################
# Utility Tasks
################################
task UpdatePatchAssemblyVersion {
    "$scriptDir\Update-AssemblyVersion.ps1 -path $slnDir -currentVersion"
    $currentVersion = & "$scriptDir\Update-AssemblyVersion.ps1" -path $slnDir -currentVersion | select -first 1
    "Current version is $currentVersion"
    $vs = $currentVersion.split('.')
    $newVersion = [string]::join('.', ($vs | select -First ($vs.Length-1)) )
    $patchLevel = [int]($vs | select -Last 1)+1
    $newVersion += ".$patchLevel"
    "Updating version to $newVersion"
    exec { & "$scriptDir\Update-AssemblyVersion.ps1"  -path $slnDir -version $newVersion}
}

task KillAllMeddlers {
    kill -name iisexpress -ErrorAction SilentlyContinue
    kill -name cccheck -ErrorAction SilentlyContinue
}

task StartInDesignServer {
    if(Get-Process InDesignServer -ErrorAction SilentlyContinue) {
        return
    }
    Start-Process -FilePath 'C:\Program Files\Adobe\Adobe InDesign CS5.5 Server x64\InDesignServer.com' -ArgumentList @('-port', '5555', '-noconsole' )
}

################################
# Demo Tasks
################################
task DemoCreateSiteSettings -depends RecreateDatabase {
    RunSqlScripts (ls "$dbDir/demo/CreateSiteSettings.sql")
}

task DemoCreateAssets -depends RecreateDatabase, DemoCreateSiteSettings {
    RunSqlScripts (ls "$dbDir/demo/CreateAssets.sql")
}

task DemoCreateTemplate -depends DemoCreateAssets, DemoCreateWorkflows {
    RunSqlScripts (ls "$dbDir/demo/50 - DemoData - CreateTemplate.sql")
}

task DemoCreateWorkflows -depends RecreateDatabase, DemoCreateSiteSettings {
    RunSqlScripts (ls "$dbDir/demo/StubWorkflows.sql")
}


#############################
## Helper Functions
#############################
function SetDefault {
    param(
        [String][AllowNull()]$value, 
        [String]$default
    )
    if ($value) {
        return $value;
    }
    else {
        return $default;
    }
}

function RunSqlScripts($scripts) {
    . "$surgeDir/Surge.Platform.Database/Build/SqlServerSnapins.ps1" 
    [System.Data.SqlClient.SqlConnection]::ClearAllPools()    

    foreach($script in $scripts) {
        echo "Running $script ..."
        sql @sqlServer -InputFile (Resolve-Path $script.FullName) 
    }
}

function RunScriptSync {
    if(-not (Test-Path $migrationsPath)) {
        return
    }
    $server         = $sqlServer.Get_Item("ServerInstance") -replace "tcp:",""
    $databaseName   = $sqlServer.Get_Item("Database")
    $username       = $sqlServer.Get_Item("Username")
    $password       = $sqlServer.Get_Item("Password")

    # If a username is not specified, use trusted connection, otherwise use specified credentials
    $connectionString = $null
    if ($username -eq $null) {
        $connectionString = [string]::format("Data Source={0};Initial Catalog={1};Trusted_Connection=True", $server, $databaseName)
    }
    else {
        $connectionString = [string]::format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", $server, $databaseName, $username, $password)
    }

    # Run ScriptSync
    echo $connectionString
    echo $migrationsPath
    CreateTemplatedMigrations {
        param($dir)
        exec {& $scriptSyncPath $connectionString $dir}
    }
}

function CreateTemplatedMigrations([ScriptBlock]$doThis) {
    $tempDir = join-path ([system.io.path]::GetTempPath()) ([system.guid]::NewGuid())
    mkdir $tempDir
    $scripts = Get-ChildItem $migrationsPath -include *.sql -Recurse 
    foreach($script in $scripts) { 
        $dest = Join-Path $tempDir $script.Name
        $version, $name = $script.Name.Split('-') | % { $_.Trim() } 
        $templateVersion = [string]::join('.', ($version.split('.') | select -first 2))
        $templateVersion += ' - '+($version.split('.') | select -last 1)
        $name = [system.io.path]::getfilenamewithoutextension($name)
        $contents = Get-Content $script
        . "$dbDir\Wrap-InMigrationTemplate.ps1" $name $templateVersion $contents > $dest
    }
    if($doThis) {
        & $doThis $tempDir
    }
    rm $tempDir -recurse -force
}

Wrap-InMigrationTemplate.ps1

param(
    [Parameter(Position=0,Mandatory=1)] [string]$name,
    [Parameter(Position=1,Mandatory=1)] [string]$appVersion,
    [Parameter(Position=2,Mandatory=1)] $script,
    [string]$globalVersion = '1.0 - 01'
)
@"
--Will cause the transaction to abort if any errors are thrown for statements within it
SET XACT_ABORT ON 
GO
-- Update these values for every new script.
 
--Script version. This is incremented by one as scripts are added.
DECLARE @AppVersion NVARCHAR(100) = '$appVersion'
--Global script version requirement. This is the required global schema version for this script to run. 
DECLARE @GlobalVersion NVARCHAR(100) = '$globalVersion'
 --A short description of what this script does
DECLARE @DataUpdateName NVARCHAR(1000) = '$name'
 
--Boilerplate statements to check requirements
IF (SELECT COUNT(*) FROM Surge.DataUpdate WHERE ApplicationDataVersion = @AppVersion) >  0 BEGIN
        RAISERROR('This data update script has already run.', 20, 1) WITH SETERROR, LOG
        RETURN
END
 
IF (SELECT COUNT(*) FROM Surge.DataUpdate WHERE GlobalDataVersion = @GlobalVersion) = 0 BEGIN
        RAISERROR('This data update script depends on a newer global version.', 20, 1) WITH SETERROR, LOG
        RETURN
END
 
--Start a transaction to wrap all script activity
BEGIN TRAN
INSERT INTO Surge.DataUpdate (ApplicationDataVersion, GlobalDataVersion, Name,ExecutionTime,UserName) VALUES(@AppVersion, @GlobalVersion, @DataUpdateName,GETDATE(),suser_sname())
GO
 
-------------------------------------------------------------------
--  Script
-------------------------------------------------------------------
 $script

COMMIT
"@