Rivet

0.9.1

Extras/Convert-Migration.ps1

<#
.SYNOPSIS
Demonstrates how to use the Rivet object model to convert migrations to standalone SQL scripts.
 
.DESCRIPTION
Sometimes you can't run your migration scripts directly against a database. In these situations, it is useful to be able to grab the SQL from your migrations and convert them into a different form. This script demonstates how to do that by outputing your migrations into four different files per database: one for schema changes, one for code object changes, one for data, and one for unknown kinds of changes.
 
.LINK
Merge-Migration
 
.EXAMPLE
Convert-Migration.ps1 -OutputPath 'F:\BuildOutput\DBScripts'
 
Demonstrates how to run `Convert-Migration.ps1`.
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [string]
    # The directory where the scripts should be output.
    $OutputPath,

    [Parameter()]
    [string]
    # The path to the rivet.json file to use. By default, it will look in the current directory.
    $ConfigFilePath,

    [Parameter()]
    [Hashtable]
    # Mapping of migration base name (e.g. `20130115142433_CreateTable`) to the person's name who created it.
    $Author = @{ },

    [string[]]
    # A list of migrations to include. Only migrations that match are returned. Wildcards permitted.
    $Include,

    [string[]]
    # Any migrations/files to exclude. Wildcards accepted.
    $Exclude,

    [DateTime]
    # Only get migrations before this date/time.
    $Before,

    [DateTime]
    # Only get migrations after this date/time.
    $After
)

Set-StrictMode -Version 'Latest'

& (Join-Path -Path $PSScriptRoot -ChildPath '..\Import-Rivet.ps1' -Resolve)

if( -not (Test-Path -Path $OutputPath -PathType Container) )
{
    $null = New-Item -ItemType 'Directory' -Path $OutputPath -Force
}
else
{
    Get-ChildItem -Path $OutputPath -File | Remove-Item
}

$getMigrationParams = @{ }
@( 'ConfigFilePath', 'Exclude', 'Include', 'Before', 'After' ) |
    Where-Object { $PSBoundParameters.ContainsKey( $_ ) } |
    ForEach-Object { $getMigrationParams.$_ = Get-Variable -Name $_ -ValueOnly }

$operations = New-Object 'Collections.ArrayList'
$newTables = New-Object 'Collections.Generic.HashSet[string]'
$opIdx = @{ }

Get-Migration @getMigrationParams |
    Merge-Migration |
    ForEach-Object {
        $migration = $_
        $migration.PushOperations | 
            Where-Object { $_ } | 
            ForEach-Object {

                $op = $_

                $schemasScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Schemas.sql' -f $migration.Database)
                $schemaScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Schema.sql' -f $migration.Database)
                $dependentObjectScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.DependentObject.sql' -f $migration.Database)
                $extendedPropertyScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.ExtendedProperty.sql' -f $migration.Database)
                $codeObjectScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.CodeObject.sql' -f $migration.Database)
                $dataScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Data.sql' -f $migration.Database)
                $unknownScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Unknown.sql' -f $migration.Database)
                $triggerScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Trigger.sql' -f $migration.Database)
                $constraintScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Constraint.sql' -f $migration.Database)
                $foreignKeyScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.ForeignKey.sql' -f $migration.Database)
                $typeScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Type.sql' -f $migration.Database)

                $header = $op.Source | ForEach-Object {
                    $name = $_.FullName
                    $by = ''
                    if( $Author -and $Author.ContainsKey( $name ) )
                    {
                        $by = ': {0}' -f $Author[$name]
                    }
                    '-- {0}{1}' -f $name,$by
                } 
                $header = $header -join ([Environment]::NewLine)

                if( $op -is [Rivet.Operations.AddTableOperation] )
                {
                    $newTables.Add( $op.ObjectName ) | Out-Null
                }
        
                $op = $_
                $path = switch -Regex ( $op.GetType() )
                {
                    '(Add|Remove|Update)ExtendedProperty'
                    {
                        $extendedPropertyScriptPath
                        break
                    }

                    '(Add|Remove|Update)Schema'
                    {
                        $schemasScriptPath
                        break
                    }

                    '(Add|Remove|Update)Table'
                    {
                        $schemaScriptPath
                        break
                    }

                    '(Add|Remove|Update)Trigger'
                    {
                        $triggerScriptPath
                        break
                    }

                    '(Add|Remove|Update)(Index|PrimaryKey|UniqueKey)'
                    {
                        $tableName = '{0}.{1}' -f $op.SchemaName,$op.TableName
                        if( $newTables.Contains( $tableName ) )
                        {
                            $schemaScriptPath
                        }
                        else
                        {
                            $dependentObjectScriptPath
                        }
                        break
                    }

                    '(Add|Remove)(CheckConstraint|DefaultConstraint)'
                    {
                        $constraintScriptPath
                        break
                    }

                    '(Enable|Disable)Constraint'
                    {
                        $constraintScriptPath
                        break
                    }

                    '(Add|Remove|Disable|Enable)ForeignKey'
                    {
                        $foreignKeyScriptPath
                        break
                    }

                    '(Add|Remove|Update)(DataType|Synonym)'
                    {
                        $typeScriptPath
                        break
                    }

                    'Rename(Column|Constraint|Index)?Operation'
                    {
                        $schemaScriptPath
                    }

                    '(Add|Remove|Update)(CodeObjectMetadata|StoredProcedure|UserDefinedFunction|View)'
                    {
                        $codeObjectScriptPath
                        break
                    }

                    '(Add|Remove|Update)Row'
                    {
                        $dataScriptPath
                        break
                    }

                    'RawDdl|ScriptFile'
                    {
                        Write-Warning ('Generic migration operation found in ''{0}''.' -f $migration.Path)
                        $unknownScriptPath
                        break
                    }

                    default
                    {
                        Write-Error ('Unknown migration operation ''{0}'' in ''{1}''.' -f $op.GetType(),$migration.Path)
                        return
                    }
                }

                if( -not (Test-Path -Path $path -PathType Leaf) )
                {
                    $null = New-Item -Path $path -ItemType 'File' -Force
                }
                $header | Add-Content -Path $path
                $op.ToIdempotentQuery() | Add-Content -Path $path
                ("GO{0}" -f [Environment]::NewLine) | Add-Content -Path $path
            }
    }