SqlDatabase/SqlDatabase.ps1

#region Copyright & License

# Copyright © 2012 - 2021 François Chabot
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

#endregion

Set-StrictMode -Version Latest

<#
.SYNOPSIS
    Creates the necessary manifest resources to deploy and undeploy a SQL Server database.
.DESCRIPTION
    Creates the necessary manifest resources to deploy and undeploy a SQL Server database. The resources created are as
    follows:
    - a SqlDeploymentScript for the script conventionally named ($Manifest.Properties.Name).Create.$(Name).sql
    - a SqlDeploymentScript for the script conventionally named ($Manifest.Properties.Name).Create.$(Name).Objects.sql
    - a SqlUndeploymentScript for the script conventionally named ($Manifest.Properties.Name).Drop.$(Name).sql
.PARAMETER Name
    The name of the SQL Server database for which to create the necessary deployment and undeployment resources.
.PARAMETER Server
    The name of the SQL Server that will host the database.
.PARAMETER Path
    The folder where the deployment and undeployment SQL scripts are located.
.PARAMETER EnlistInBizTalkBackupJob
.NOTES
    © 2020 be.stateless.
#>

function New-SqlDatabase {
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string[]]
        $Name,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [string]
        $Server,

        [Parameter(Mandatory = $true)]
        [ValidateScript( { $_ | Test-Path -PathType Container } )]
        [psobject]
        $Path,

        [Parameter(Mandatory = $false)]
        [switch]
        $EnlistInBizTalkBackupJob,

        [Parameter(Mandatory = $false)]
        [AllowNull()]
        [hashtable]
        $Variables,

        [Parameter(Mandatory = $false)]
        [ValidateNotNullOrEmpty()]
        [ValidateScript( { $_ -is [bool] -or $_ -is [ScriptBlock] } )]
        [psobject]
        $Condition = $true,

        [Parameter(Mandatory = $false)]
        [switch]
        $PassThru
    )
    Resolve-ActionPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState

    if ($Manifest.Properties.Type -ne 'Application') {
        throw 'A BizTalk Application''s custom SQL database can only be installed in the context of an Application manifest.'
    }

    $arguments = @{
        Server    = $Server
        Condition = $Condition
    }
    if ($null -ne $Variables -and ($Variables.Keys | Test-Any)) { $arguments.Variables = $Variables }

    $Name | ForEach-Object -Process {
        New-SqlDeploymentScript @arguments -Path (Join-Path $Path "$($Manifest.Properties.Name).Create.$_.sql") -PassThru:$PassThru
        New-SqlDeploymentScript @arguments -Path (Join-Path $Path "$($Manifest.Properties.Name).Create.$_.Objects.sql") -PassThru:$PassThru
        New-SqlUndeploymentScript @arguments -Path (Join-Path $Path "$($Manifest.Properties.Name).Drop.$_.sql") -PassThru:$PassThru

        if ($EnlistInBizTalkBackupJob) {
            New-SqlDeploymentScript -Path (Join-Path $env:BTSINSTALLPATH 'Schema\Backup_Setup_All_Tables.sql') -Server $Server -Database $_ -Condition $Condition -PassThru:$PassThru
            New-SqlDeploymentScript -Path (Join-Path $env:BTSINSTALLPATH 'Schema\Backup_Setup_All_Procs.sql') -Server $Server -Database $_ -Condition $Condition -PassThru:$PassThru
            New-SqlDeploymentScript -Path (Join-Path $PSScriptRoot 'IncludeCustomDatabaseInOtherBackupDatabases.sql') -Server $Server -Condition $Condition -PassThru:$PassThru `
                -Variables @{
                CustomDatabaseName = $_
                ServerName         = $Server
                BTSServer          = $env:COMPUTERNAME
            }
            New-SqlUndeploymentScript -Path (Join-Path $PSScriptRoot 'RemoveCustomDatabaseFromOtherBackupDatabases.sql') -Server $Server -Condition $Condition -PassThru:$PassThru `
                -Variables @{ CustomDatabaseName = $_ }
        }
    }
}

Set-Alias -Name SqlDatabase -Value New-SqlDatabase

# SIG # Begin signature block
# MIII0QYJKoZIhvcNAQcCoIIIwjCCCL4CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR
# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUlDuegHdkTO7DpiNIb9lIiBdb
# 2s2gggVMMIIFSDCCAzCgAwIBAgIJAJkr3mJdTBkUMA0GCSqGSIb3DQEBCwUAMEEx
# PzA9BgNVBAMeNgBpAGMAcgBhAGYAdABzAG8AZgB0AHcAYQByAGUAQABzAHQAYQB0
# AGUAbABlAHMAcwAuAGIAZTAeFw0yMTA2MjUxNDEyMjNaFw00MTA2MjAxNDEyMjNa
# MEExPzA9BgNVBAMeNgBpAGMAcgBhAGYAdABzAG8AZgB0AHcAYQByAGUAQABzAHQA
# YQB0AGUAbABlAHMAcwAuAGIAZTCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoC
# ggIBAOeqdUHBv7sxSeX3aj6yPKj7PAvs8izpVXjyEBl5aR8mQneVcXuF53AH7EW1
# 6E5p4+Az5pJPGUD5c3tXhiGMF7vgLhQjO6hlaVBRIqiIYHikNLwMNy6YBMc/QQYM
# rPhqHEFsZ53dkBIIj3M8e3kFcTFA09n25yDtTPDab4nd9yUhc9Qc8+nfpIzfYsoP
# 1pZ3nCzhw6hN2/44v1dkQrG3dRYwt+px65p6NPNZWEJpt4VCJjIFh+lBYJdxm9d4
# X/rAnlHIkbv7liOavWDzgHVabS3hdAWtcDmynm+7+FcZDFqPWNCl3e4SS7xe4s/R
# CKFKA0IsfKkSk9YJlLgeSQIEXUOOWXJAGaLqnRD8xWLZsc4Oi9GZg7XV1mv/S88c
# oztXnwtAN3OOlRKBh2QbomMgxeMO0GvsLE/cq5Q/YKAoz+KGr/7LcZq9jzQ8IPus
# ZvWLeDXmxPiwJjpZc1koLgfGIEX2NStQTT3QmacWr9thrWcKvI+4uBmI4exS9B4a
# R3nV91w5EY+2RoYsHqej9LWwNamO96+jMX9pxprTX+EkLUuMAikw/po8sBC9MUUn
# 5pMWmUv7DCtQOLGGBDDMMMkn4ZcjpCEEdPGHRKfqNnD27ssGtDjiNzfQrsm67toU
# bBwUF+gyJq/YckWquYJhA9ZOFWEADuIwGnsOzsoRvuQyY+p9AgMBAAGjQzBBMA4G
# A1UdDwEB/wQEAwIHgDAWBgNVHSUBAf8EDDAKBggrBgEFBQcDAzAXBgNVHREEEDAO
# ggxzdGF0ZWxlc3MuYmUwDQYJKoZIhvcNAQELBQADggIBACithYM3qckZRc9+Xbfu
# a6gWr3HwjrW+FHKgjfrcOm8ZnLVapb9xFqsqrRQqd3RXWQDINEGrtI2rSfrzyfoK
# UiTgldIfQNP1ZcGY229d++90t3hdo2mlt05hjYlbMENloJHpsEP0vQZmwOcEimCT
# ex1pymYM+P9pj3j8UD1PT1eIZot6or8fBRl63UybyDSrM7L4UOkkAOniKxWy5pW6
# 6duS8SR+SZpr3Bv44NyXPj0Nv+MIpLmsLrd7XPBFmnGxzY01ZO9vzi9KEhM2wT5i
# jPqHDNOvfPiADtAa+EyUBzdJiqy9heCz/TMZQgMWGwtfqJNxWZmsHcha2anW4Qt+
# mzrLO4GojWoVog9uVSAq+l0a+YQsd1u1kUmm4vgZCFyUA+lEp4LkI7ca2VBHkLPD
# w+u2DoDMRiqFPZjO7BCKjGc0jj9B/qGR3JVt+tqDdB621xXf2YGF2oFvxZQ/keGt
# 0ujfJ+JwN3nCulDAA4773q6KUnfykyrvAgITNbRJL6TngeRKtw9VIJBPxzqMzLpV
# 5ggXNituwLaD1CCBJ1oo9DZHpL9gplXp1wGrelJOTiJhh+pdNsPtRH7CrranWa5h
# LFLuigqin0eewQ5giJ1VaiBVEseOmiZog+27UpFIv40aDzgGL3YxB/Mu0ojwrQtp
# WLmqJCmWnR5qxOm0yK+zNWe0MYIC7zCCAusCAQEwTjBBMT8wPQYDVQQDHjYAaQBj
# AHIAYQBmAHQAcwBvAGYAdAB3AGEAcgBlAEAAcwB0AGEAdABlAGwAZQBzAHMALgBi
# AGUCCQCZK95iXUwZFDAJBgUrDgMCGgUAoHgwGAYKKwYBBAGCNwIBDDEKMAigAoAA
# oQKAADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAcBgorBgEEAYI3AgELMQ4w
# DAYKKwYBBAGCNwIBFTAjBgkqhkiG9w0BCQQxFgQUj56tb6hPO1jIafi64Oed47mQ
# kokwDQYJKoZIhvcNAQEBBQAEggIAzn6Sdzf2EHmnHTupdn0KcraXQMlzzonxNpGD
# s4esJO+O7rXd4cA0tqA8tYZE/FJp7QXY0DiNS6pcuyADX1XW5uqu8S5O0t97eL+k
# JE291Zd2NsatkCmUc0Qamwkk/9VMW1P8fKzzM2gkXG4AjNidjcDM0LvZbO+rn1Ge
# VZV0dHRPziYTbr10gSlSQ7xyuhZ8XOWEBX07M5Vb6MPb+iXC/zBcwMfjW0AWYLZ4
# MLdbFxtkiQoTQ9MUBmUzeFrZZglYEW0ZCGLebYce4QllxqlQI5ThwK5EEr7IIi7k
# 6frDXRGZc2pDeCQnd7HsODaD+LxTIz7Nh6gBgN1lClpCmYPVHy8AzfOcCfG0J/dN
# Fyi/8Trkw24CqPpYIdyFDRgztftzvQuApa90eACk/HH/yWeu06N6fVZVyMHz6dWm
# v/tdV8DmiPLpw+6Rvxgpx1VLqQnJAYxTWfWEwNC+IFEOsgsGVSLWo2F/1lFx1u1I
# wgo9ySuNWMNF3wvrMXbE7gyNRguW5yD14rexh3ZYDddirqrkNNhAnjO+JqxlMDPD
# KJVLUDsAz9d9F1+ZaEDKpvL/EdEqObDShj60gY2rdCQ8jNFDdXZDTeqWp5P4a0c/
# vI01bgu2C1q6zM0w4C5ZqpugtLDLTbANNtsAA2eYca96B00JXamvfl5bzI+6Q5ap
# 7JtMPHw=
# SIG # End signature block