DbSmo

1.5.3

New-DbSmoSchema.ps1

<#
 
.SYNOPSIS
 
.DESCRIPTION
 
.PARAMETER
 
.INPUTS
 
.OUTPUTS
 
.EXAMPLE
 
#>

function New-DbSmoSchema {
    [CmdletBinding(DefaultParameterSetName = "ServerInstance")]
    param (
        [Parameter(Mandatory = $true)]
        [System.Data.DataSet] $DataSet,

        [Parameter(Mandatory = $true, ParameterSetName = "ServerInstance")]
        $ServerInstance,
        [Parameter(Mandatory = $true, ParameterSetName = "ServerInstance")]
        $DatabaseName,
        [Parameter(ParameterSetName = "Connection")]
        $Connection,

        $SchemaName,
        [switch] $Script
    )

    $scriptText = New-Object System.Collections.ArrayList

    if ($PSCmdlet.ParameterSetName -ne "Connection") {
        $Connection = New-DbConnection -ServerInstance $ServerInstance -DatabaseName $DatabaseName
    }
    $smo = $Connection | Get-DbSmo

    $sqlDatabase = $smo.Databases[$DatabaseName]
    $newSchema = New-Object Microsoft.SqlServer.Management.Smo.Schema($sqlDatabase, $schemaName)
    $newSchema.Refresh()
    if ($Script) {
        Write-Verbose "Schema scripted"
        [void] $scriptText.Add($newSchema.Script())
    } else {
        if ($newSchema.State -eq "Existing") {
            Write-Verbose "Schema altered"
            $newSchema.Alter()
        } else {
            Write-Verbose "Schema created"
            $newSchema.Create()
        }
    }

    $tables = @{}
    foreach ($table in $DataSet.Tables) {
        try {
            $tableName = $table.TableName
            Write-Verbose "Converting table $tableName"
            $newTable = New-Object Microsoft.SqlServer.Management.Smo.Table($sqlDatabase, $tableName, $SchemaName)
            $newTable.Refresh() # This will fill the schema from the database if it already exists

            # Add temporal table columns if this is SQL 2016 onwards
            if ($newTable.Columns.Count -eq 0 -and $smo.Version.Major -ge 13) {
                Write-Verbose "Adding temporal fields"
                $dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType("DateTime2", 2)
                $fromColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable, "_ValidFrom", $dataType)
                $fromColumn.Nullable = $false # Columns belonging to a system-time period cannot be nullable.
                $fromColumn.IsHidden = $true
                $fromColumn.GeneratedAlwaysType = "AsRowStart"
                $newTable.Columns.Add($fromColumn)

                $toColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable, "_ValidTo", $dataType)
                $toColumn.Nullable = $false # Columns belonging to a system-time period cannot be nullable.
                $toColumn.IsHidden = $true
                $toColumn.GeneratedAlwaysType = "AsRowEnd"
                $newTable.Columns.Add($toColumn)

                $newTable.AddPeriodForSystemTime("_ValidFrom", "_ValidTo", $true) # If you accidentally passed non strings you get a "must provide existing column" error

                $newTable.HistoryTableSchema = $SchemaName
                $newTable.HistoryTableName = "$($tableName)_History"
                $newTable.IsSystemVersioned = $true
            }

            # Iterate columns where the column names aren't already in the table
            $changed = $false
            foreach ($column in ($table.Columns | Where-Object { ($newTable.Columns | Select-Object -ExpandProperty Name) -notcontains $_.ColumnName })) {
                $dataType = switch ($column.DataType.Name) {
                    "Boolean" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::Bit
                    }
                    "Byte[]" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::VarBinary
                    }
                    "Byte" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::TinyInt
                    }
                    "DateTime" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::DateTime2
                    }
                    "Decimal" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::Decimal
                    }
                    "Double" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::Float
                    }
                    "Guid" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::UniqueIdentifier
                    }
                    "Int16" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::SmallInt
                    }
                    "Int32" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::Int
                    }
                    "Int64" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::BigInt
                    }
                    "UInt16" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::SmallInt
                    }
                    "UInt32" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::BigInt # Cluster.RootMemoryReserved; ClusterNode.DrainTarget; ClusterGroup.FailoverThreshold
                    }
                    "UInt64" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::BigInt
                    }
                    "Single" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::Decimal
                    }
                    "String" {
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::NVarChar
                    }
                    default {
                        # This basically defaults everything else so that if we passed something we really
                        # didn't expect then we won't be storing a raw object; it'll be cast to string.
                        [Microsoft.SqlServer.Management.Smo.SqlDataType]::NVarChar
                    }
                }
                Write-Verbose "Adding column $($column.ColumnName) as $dataType"

                if ($dataType -eq "VarBinary" -or $dataType -eq "VarChar" -or $dataType -eq "NVarChar") {
                    if ($column.MaxLength -ne -1) {
                        $dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType($dataType, $column.MaxLength)
                    } else {
                        $dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType("$($dataType)Max")
                    }
                } elseif ($dataType -eq "Decimal") {
                    # The only known uses of Decimal in SMO are for LSN. It defaults to 18, 0 but we need 25, 0.
                    # AvailabilityDatabase.RecoveryLSN
                    # AvailabilityDatabase.TruncationLSN
                    # Database.MirroringFailoverLogSequenceNumber
                    # DatabaseReplicaState.EndOfLogLSN
                    # DatabaseReplicaState.LastCommitLSN
                    # DatabaseReplicaState.LastHardenedLSN
                    # DatabaseReplicaState.LastReceivedLSN
                    # DatabaseReplicaState.LastRedoneLSN
                    # DatabaseReplicaState.LastSentLSN
                    # DatabaseReplicaState.RecoveryLSN
                    # DatabaseReplicaState.TruncationLSN
                    $dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType($dataType, 25, 0)
                } else {
                    $dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType($dataType)
                }

                $newColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable, $column.ColumnName, $dataType)
                $newColumn.Nullable = $column.AllowDbNull
                $newTable.Columns.Add($newColumn)

                $changed = $true
            }
            $tables.Add($tableName, $newTable)

            # If the SMO table has a primary key but the new/existing table doesn't
            if ($table.PrimaryKey) {
                if (!($newTable.Indexes | Where-Object { $_.IndexKeyType -eq "DriPrimaryKey" })) {
                    $primaryKeyName = $table.Constraints | Where-Object { $_ -is [System.Data.UniqueConstraint] -and $_.IsPrimaryKey } | Select-Object -ExpandProperty ConstraintName
                    Write-Verbose "Adding primary key $primaryKeyName"

                    $primaryKey = New-Object Microsoft.SqlServer.Management.Smo.Index($newTable, $primaryKeyName)
                    $primaryKey.IndexType = [Microsoft.SqlServer.Management.Smo.IndexType]::ClusteredIndex
                    $primaryKey.IndexKeyType = [Microsoft.SqlServer.Management.Smo.IndexKeyType]::DriPrimaryKey

                    foreach ($column in $table.PrimaryKey) {
                        $indexColumn = New-Object Microsoft.SqlServer.Management.Smo.IndexedColumn($primaryKey, $column.ColumnName)
                        $primaryKey.IndexedColumns.Add($indexColumn)
                    }

                    $newTable.Indexes.Add($primaryKey)
                }
            } else {
                Write-Warning "$tableName doesn't have a primary key!"
            }

            if ($changed) {
                # You must script out the table, the primary key, and the foreign keys separately
                if ($Script) {
                    Write-Verbose "Table scripted"
                    [void] $scriptText.Add($newTable.Script())
                } else {
                    if ($newTable.State -eq "Existing") {
                        Write-Verbose "Table altered"
                        $newTable.Alter()
                    } else {
                        Write-Verbose "Table created"
                        $newTable.Create()
                    }
                }
            }
        } catch {
            throw
        }
    }

    foreach ($table in $DataSet.Tables) {
        $tableName = $table.TableName
        $newTable = New-Object Microsoft.SqlServer.Management.Smo.Table($sqlDatabase, $tableName, $SchemaName)
        $newTable.Refresh() # This will fill the schema from the database

        foreach ($constraint in ($table.Constraints | Where-Object { $_ -is [System.Data.ForeignKeyConstraint] -and ($newTable.ForeignKeys | Select-Object -ExpandProperty Name) -notcontains $_.ConstraintName })) {
            try {
                $constraintName = $constraint.ConstraintName
                Write-Verbose "Adding foreign key $($tableName).$constraintName"

                $foreignKey = New-Object Microsoft.SqlServer.Management.Smo.ForeignKey($tables[$tableName], $constraintName)
                $foreignKey.ReferencedTable = $constraint.RelatedTable.TableName
                $foreignKey.ReferencedTableSchema = $SchemaName
                $foreignKey.IsChecked = $true
                for ($i = 0; $i -lt $constraint.Columns.Count; $i++) {
                    $foreignKeyColumn = New-Object Microsoft.SqlServer.Management.Smo.ForeignKeyColumn($foreignKey, $constraint.Columns[$i], $constraint.RelatedColumns[$i])
                    $foreignKey.Columns.Add($foreignKeyColumn)
                }

                # SQL 2017 supports on Delete Cascade with Temporal Tables
                if ($smo.Version.Major -ge 14) {
                    $foreignKey.DeleteAction = "Cascade"
                }

                if ($Script) {
                    [void] $scriptText.Add($foreignKey.Script())
                } else {
                    $foreignKey.Create()
                }
            } catch {
                throw
            }
        }
    }

    if ($Script) {
        $scriptText
    }
}