Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package mssql
import (
"context"
"fmt"
"slices"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/bytebase/omni/mssql/ast"
"github.com/pkg/errors"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/plugin/parser/tsql"
"github.com/bytebase/bytebase/backend/plugin/schema"
"github.com/bytebase/bytebase/backend/store/model"
)
func init() {
schema.RegisterGenerateMigration(storepb.Engine_MSSQL, generateMigration)
}
func generateMigration(diff *schema.MetadataDiff) (string, error) {
var buf strings.Builder
// Collect schemas to create first (needed for checking if create phase will have content)
var schemasToCreate []string
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionCreate {
// Skip creating dbo schema as it already exists by default
if strings.ToLower(schemaDiff.SchemaName) != "dbo" {
schemasToCreate = append(schemasToCreate, schemaDiff.SchemaName)
}
}
}
slices.Sort(schemasToCreate)
// Safe order for migrations:
// 1. Drop dependent objects first (in reverse dependency order)
// - Drop foreign keys
// - Drop indexes
// - Drop views (in reverse topological order)
// - Drop functions/procedures (might depend on tables)
// - Drop tables
// - Drop schemas
// 2. Create/Alter objects (in dependency order)
// - Create schemas
// - Create/Alter tables and columns
// - Create indexes
// - Create foreign keys
// - Create views (in topological order)
// - Create functions/procedures
// Phase 1: Drop dependent objects
// 1.1 Drop foreign keys first (they depend on tables)
for _, tableDiff := range diff.TableChanges {
switch tableDiff.Action {
case schema.MetadataDiffActionAlter:
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionDrop {
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] DROP CONSTRAINT [")
_, _ = buf.WriteString(fkDiff.OldForeignKey.Name)
_, _ = buf.WriteString("];\nGO\n")
}
}
case schema.MetadataDiffActionDrop:
// Drop foreign keys before dropping table
for _, fk := range tableDiff.OldTable.ForeignKeys {
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] DROP CONSTRAINT [")
_, _ = buf.WriteString(fk.Name)
_, _ = buf.WriteString("];\n")
}
default:
// Ignore other actions for this phase
}
}
// 1.2 Drop procedures (might depend on tables)
for _, procDiff := range diff.ProcedureChanges {
if procDiff.Action == schema.MetadataDiffActionDrop {
_, _ = buf.WriteString("DROP PROCEDURE [")
_, _ = buf.WriteString(procDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(procDiff.ProcedureName)
_, _ = buf.WriteString("];\nGO\n")
}
}
// 1.3 Drop functions (might depend on tables)
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionDrop {
_, _ = buf.WriteString("DROP FUNCTION [")
_, _ = buf.WriteString(funcDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(funcDiff.FunctionName)
_, _ = buf.WriteString("];\nGO\n")
}
}
// 1.4 Drop views in reverse topological order (dependent views first)
dropViewsInOrder(diff, &buf)
// 1.5 Drop indexes and constraints from tables being altered
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
// Drop check constraints
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionDrop {
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] DROP CONSTRAINT [")
_, _ = buf.WriteString(checkDiff.OldCheckConstraint.Name)
_, _ = buf.WriteString("];\n")
}
}
// Drop indexes
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionDrop {
if indexDiff.OldIndex.IsConstraint {
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] DROP CONSTRAINT [")
_, _ = buf.WriteString(indexDiff.OldIndex.Name)
_, _ = buf.WriteString("];\n")
} else {
_, _ = buf.WriteString("DROP INDEX [")
_, _ = buf.WriteString(indexDiff.OldIndex.Name)
_, _ = buf.WriteString("] ON [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("];\n")
}
}
}
// Drop columns
hasDroppedIndex := false
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionDrop {
hasDroppedIndex = true
break
}
}
for i, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionDrop {
// Add GO before first column drop if we dropped indexes
if i == 0 && hasDroppedIndex {
_, _ = buf.WriteString("GO\n")
}
// If the column has a default constraint, drop it first
if getColumnDefaultValue(colDiff.OldColumn) != "" {
if colDiff.OldColumn.DefaultConstraintName != "" {
// Use the known constraint name directly
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] DROP CONSTRAINT [")
_, _ = buf.WriteString(colDiff.OldColumn.DefaultConstraintName)
_, _ = buf.WriteString("];\n")
}
// Note: If DefaultConstraintName is empty, we cannot drop the constraint automatically
}
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] DROP COLUMN [")
_, _ = buf.WriteString(colDiff.OldColumn.Name)
_, _ = buf.WriteString("];\n")
}
}
}
}
// Add GO after table alterations if we had any and we're about to drop tables
hasTableAlterations := false
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionDrop {
hasTableAlterations = true
break
}
}
}
}
// 1.6 Drop tables
hasTableDrops := false
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop {
if !hasTableDrops && hasTableAlterations {
_, _ = buf.WriteString("GO\n")
}
hasTableDrops = true
_, _ = buf.WriteString("DROP TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("];\n")
}
}
// 1.6.1 Drop all tables in schemas that are being dropped
// This handles cases where the schema differ doesn't detect tables in dropped schemas
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionDrop && schemaDiff.OldSchema != nil {
// Drop all tables in this schema
for _, table := range schemaDiff.OldSchema.Tables {
// Check if this table is already handled by tableDiff
alreadyHandled := false
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop &&
tableDiff.SchemaName == schemaDiff.SchemaName &&
tableDiff.TableName == table.Name {
alreadyHandled = true
break
}
}
if !alreadyHandled {
_, _ = buf.WriteString("DROP TABLE [")
_, _ = buf.WriteString(schemaDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(table.Name)
_, _ = buf.WriteString("];\n")
}
}
}
}
// 1.7 Drop schemas (must be empty)
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionDrop {
// Skip dropping dbo schema as it's a system schema
if strings.ToLower(schemaDiff.SchemaName) == "dbo" {
continue
}
_, _ = buf.WriteString("DROP SCHEMA [")
_, _ = buf.WriteString(schemaDiff.SchemaName)
_, _ = buf.WriteString("];\nGO\n")
}
}
// Only add blank line if we have drops AND we're about to create something
dropPhaseHasContent := buf.Len() > 0
createPhaseWillHaveContent := len(schemasToCreate) > 0 ||
hasCreateOrAlterTables(diff) ||
hasCreateViews(diff) ||
hasCreateFunctions(diff) ||
hasCreateProcedures(diff)
if dropPhaseHasContent && createPhaseWillHaveContent {
_, _ = buf.WriteString("\n")
}
// Phase 2: Create/Alter objects
// 2.1 Create schemas first (already sorted)
for _, schemaName := range schemasToCreate {
_, _ = buf.WriteString("CREATE SCHEMA [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("];\nGO\n")
}
// Add blank line after schema creation only if we have schemas and more content follows
if len(schemasToCreate) > 0 && (hasCreateOrAlterTables(diff) || hasCreateViews(diff) || hasCreateFunctions(diff) || hasCreateProcedures(diff)) {
_, _ = buf.WriteString("\n")
}
// 2.2 Create new tables WITHOUT foreign keys (in topological order based on FK dependencies)
createTablesInOrder(diff, &buf)
// 2.3 Alter existing tables (add columns, alter columns)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
alterTableSQL := generateAlterTable(tableDiff)
_, _ = buf.WriteString(alterTableSQL)
if alterTableSQL != "" {
_, _ = buf.WriteString("\n")
}
}
}
// 2.4 Add foreign keys for newly created tables (after all tables exist)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate && tableDiff.NewTable != nil {
for _, fk := range tableDiff.NewTable.ForeignKeys {
fkSQL := generateAddForeignKey(tableDiff.SchemaName, tableDiff.TableName, fk)
_, _ = buf.WriteString(fkSQL)
_, _ = buf.WriteString(";\n")
}
}
}
// 2.5 Handle table and column comments
hasCommentChanges := false
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate && tableDiff.NewTable != nil {
// Add table comment if present
if tableDiff.NewTable.Comment != "" {
commentSQL := generateTableCommentSQL("ADD", tableDiff.SchemaName, tableDiff.TableName, tableDiff.NewTable.Comment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
}
// Add column comments if present
for _, column := range tableDiff.NewTable.Columns {
if column.Comment != "" {
commentSQL := generateColumnCommentSQL("ADD", tableDiff.SchemaName, tableDiff.TableName, column.Name, column.Comment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
}
}
} else if tableDiff.Action == schema.MetadataDiffActionAlter {
// Handle table comment changes
oldTableComment := ""
newTableComment := ""
if tableDiff.OldTable != nil {
oldTableComment = tableDiff.OldTable.Comment
}
if tableDiff.NewTable != nil {
newTableComment = tableDiff.NewTable.Comment
}
if oldTableComment != newTableComment {
if oldTableComment == "" && newTableComment != "" {
// Add new table comment
commentSQL := generateTableCommentSQL("ADD", tableDiff.SchemaName, tableDiff.TableName, newTableComment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
} else if oldTableComment != "" && newTableComment == "" {
// Drop table comment
commentSQL := generateTableCommentSQL("DROP", tableDiff.SchemaName, tableDiff.TableName, "")
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
} else if oldTableComment != "" && newTableComment != "" {
// Update table comment
commentSQL := generateTableCommentSQL("UPDATE", tableDiff.SchemaName, tableDiff.TableName, newTableComment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
}
}
// Handle column comment changes
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionCreate && colDiff.NewColumn != nil {
if colDiff.NewColumn.Comment != "" {
commentSQL := generateColumnCommentSQL("ADD", tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn.Name, colDiff.NewColumn.Comment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
}
} else if colDiff.Action == schema.MetadataDiffActionAlter {
oldColComment := ""
newColComment := ""
if colDiff.OldColumn != nil {
oldColComment = colDiff.OldColumn.Comment
}
if colDiff.NewColumn != nil {
newColComment = colDiff.NewColumn.Comment
}
if oldColComment != newColComment {
if oldColComment == "" && newColComment != "" {
// Add new column comment
commentSQL := generateColumnCommentSQL("ADD", tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn.Name, newColComment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
} else if oldColComment != "" && newColComment == "" {
// Drop column comment
commentSQL := generateColumnCommentSQL("DROP", tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn.Name, "")
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
} else if oldColComment != "" && newColComment != "" {
// Update column comment
commentSQL := generateColumnCommentSQL("UPDATE", tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn.Name, newColComment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\n")
hasCommentChanges = true
}
}
}
}
}
}
// Add GO after comment changes if we have any
if hasCommentChanges {
_, _ = buf.WriteString("GO\n")
}
// Add a GO statement to separate table creation/alteration from the next phase
// Only add if we have tables/alters and we have views/functions/procedures to follow
if hasCreateOrAlterTables(diff) && (hasCreateViews(diff) || hasCreateFunctions(diff) || hasCreateProcedures(diff)) {
_, _ = buf.WriteString("\nGO\n")
}
// 2.6 Create views in topological order (dependencies first)
createViewsInOrder(diff, &buf)
// 2.7 Create functions
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionCreate {
_, _ = buf.WriteString(funcDiff.NewFunction.Definition)
if !strings.HasSuffix(strings.TrimSpace(funcDiff.NewFunction.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\nGO\n")
// Add function comment if present
if funcDiff.NewFunction.Comment != "" {
commentSQL := generateFunctionCommentSQL("ADD", funcDiff.SchemaName, funcDiff.FunctionName, funcDiff.NewFunction.Comment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\nGO\n")
}
}
}
// 2.8 Create procedures
for _, procDiff := range diff.ProcedureChanges {
switch procDiff.Action {
case schema.MetadataDiffActionCreate:
_, _ = buf.WriteString(procDiff.NewProcedure.Definition)
if !strings.HasSuffix(strings.TrimSpace(procDiff.NewProcedure.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\nGO\n")
case schema.MetadataDiffActionAlter:
// MSSQL requires dropping and recreating procedures
_, _ = buf.WriteString("DROP PROCEDURE [")
_, _ = buf.WriteString(procDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(procDiff.ProcedureName)
_, _ = buf.WriteString("];\nGO\n")
_, _ = buf.WriteString(procDiff.NewProcedure.Definition)
if !strings.HasSuffix(strings.TrimSpace(procDiff.NewProcedure.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\nGO\n")
default:
// Ignore other actions
}
}
// 2.9 Create sequences
for _, sequenceDiff := range diff.SequenceChanges {
if sequenceDiff.Action == schema.MetadataDiffActionCreate {
// Generate CREATE SEQUENCE statement
_, _ = buf.WriteString("CREATE SEQUENCE [")
_, _ = buf.WriteString(sequenceDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(sequenceDiff.SequenceName)
_, _ = buf.WriteString("]")
if sequenceDiff.NewSequence.DataType != "" {
_, _ = buf.WriteString(" AS ")
_, _ = buf.WriteString(sequenceDiff.NewSequence.DataType)
}
_, _ = buf.WriteString(";\nGO\n")
// Add sequence comment if present
if sequenceDiff.NewSequence.Comment != "" {
commentSQL := generateSequenceCommentSQL("ADD", sequenceDiff.SchemaName, sequenceDiff.SequenceName, sequenceDiff.NewSequence.Comment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\nGO\n")
}
}
}
return buf.String(), nil
}
func generateCreateTable(schemaName, tableName string, table *metadatapb.TableMetadata) string {
var buf strings.Builder
_, _ = buf.WriteString("CREATE TABLE [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("] (\n")
// Add columns
for i, column := range table.Columns {
columnDef := generateColumnDefinition(column)
_, _ = buf.WriteString(" ")
_, _ = buf.WriteString(columnDef)
// Add comma if not last column or if there are constraints
if i < len(table.Columns)-1 || hasConstraintsInTable(table) || len(table.CheckConstraints) > 0 {
_, _ = buf.WriteString(",")
}
_, _ = buf.WriteString("\n")
}
// Add constraints (inline with table definition)
constraintAdded := false
for _, idx := range table.Indexes {
if idx.IsConstraint {
if constraintAdded {
_, _ = buf.WriteString(",\n")
}
_, _ = buf.WriteString(" CONSTRAINT [")
_, _ = buf.WriteString(idx.Name)
if idx.Primary {
_, _ = buf.WriteString("] PRIMARY KEY")
} else if idx.Unique {
_, _ = buf.WriteString("] UNIQUE")
} else {
_, _ = buf.WriteString("]")
}
switch idx.Type {
case "CLUSTERED":
_, _ = buf.WriteString(" CLUSTERED")
case "NONCLUSTERED":
_, _ = buf.WriteString(" NONCLUSTERED")
default:
// Default to "" if not specified
}
_, _ = buf.WriteString(" (")
for j, expr := range idx.Expressions {
if j > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("]")
}
_, _ = buf.WriteString(")")
constraintAdded = true
}
}
if constraintAdded && len(table.CheckConstraints) > 0 {
_, _ = buf.WriteString(",\n")
} else if constraintAdded {
_, _ = buf.WriteString("\n")
}
// Add check constraints (inline with table definition)
for i, check := range table.CheckConstraints {
_, _ = buf.WriteString(" CONSTRAINT [")
_, _ = buf.WriteString(check.Name)
_, _ = buf.WriteString("] CHECK (")
_, _ = buf.WriteString(check.Expression)
_, _ = buf.WriteString(")")
if i < len(table.CheckConstraints)-1 {
_, _ = buf.WriteString(",")
}
_, _ = buf.WriteString("\n")
}
_, _ = buf.WriteString(");")
// Add non-primary indexes (after table creation)
for _, idx := range table.Indexes {
if !idx.IsConstraint {
indexSQL := generateCreateIndex(schemaName, tableName, idx)
_, _ = buf.WriteString("\n")
_, _ = buf.WriteString(indexSQL)
_, _ = buf.WriteString(";")
}
}
// Note: Foreign keys are now added in a separate phase after all tables are created
return buf.String()
}
func generateAlterTable(tableDiff *schema.TableDiff) string {
var buf strings.Builder
// Add columns first (other operations might depend on them)
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionCreate {
columnDef := generateColumnDefinition(colDiff.NewColumn)
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] ADD ")
_, _ = buf.WriteString(columnDef)
_, _ = buf.WriteString(";\n")
}
}
// Alter columns
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionAlter {
alterColSQL := generateAlterColumn(tableDiff.SchemaName, tableDiff.TableName, colDiff)
_, _ = buf.WriteString(alterColSQL)
}
}
// Add indexes
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionCreate {
if indexDiff.NewIndex.IsConstraint {
// Add constraint (primary key or unique constraint)
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] ADD CONSTRAINT [")
_, _ = buf.WriteString(indexDiff.NewIndex.Name)
if indexDiff.NewIndex.Primary {
_, _ = buf.WriteString("] PRIMARY KEY")
} else if indexDiff.NewIndex.Unique {
_, _ = buf.WriteString("] UNIQUE")
} else {
_, _ = buf.WriteString("]")
}
switch indexDiff.NewIndex.Type {
case "CLUSTERED":
_, _ = buf.WriteString(" CLUSTERED")
case "NONCLUSTERED":
_, _ = buf.WriteString(" NONCLUSTERED")
default:
// Other index types
}
_, _ = buf.WriteString(" (")
for j, expr := range indexDiff.NewIndex.Expressions {
if j > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("]")
}
_, _ = buf.WriteString(");\n")
} else {
indexSQL := generateCreateIndex(tableDiff.SchemaName, tableDiff.TableName, indexDiff.NewIndex)
_, _ = buf.WriteString(indexSQL)
_, _ = buf.WriteString(";\n")
}
}
}
// Add check constraints
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionCreate {
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(tableDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableDiff.TableName)
_, _ = buf.WriteString("] ADD CONSTRAINT [")
_, _ = buf.WriteString(checkDiff.NewCheckConstraint.Name)
_, _ = buf.WriteString("] CHECK (")
_, _ = buf.WriteString(checkDiff.NewCheckConstraint.Expression)
_, _ = buf.WriteString(");\n")
}
}
// Add foreign keys last (they depend on other tables/columns)
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionCreate {
fkSQL := generateAddForeignKey(tableDiff.SchemaName, tableDiff.TableName, fkDiff.NewForeignKey)
_, _ = buf.WriteString(fkSQL)
_, _ = buf.WriteString(";\n")
}
}
return buf.String()
}
func generateColumnDefinition(column *metadatapb.ColumnMetadata) string {
var buf strings.Builder
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(column.Name)
_, _ = buf.WriteString("] ")
// Check if this is a computed column by examining the type field
isComputedColumn := strings.Contains(column.Type, " AS (") || strings.HasPrefix(column.Type, "AS (")
if isComputedColumn {
// For computed columns, just use the type as-is (it contains the AS expression)
_, _ = buf.WriteString(column.Type)
// Note: Computed columns don't have explicit nullability, IDENTITY, or defaults
// unless they are PERSISTED, which would be included in the type definition
} else {
// Regular column: add type, identity, nullability, and default
_, _ = buf.WriteString(column.Type)
// Add IDENTITY if applicable
if column.IsIdentity {
_, _ = buf.WriteString(" IDENTITY(")
_, _ = fmt.Fprintf(&buf, "%d,%d", column.IdentitySeed, column.IdentityIncrement)
_, _ = buf.WriteString(")")
}
// Add nullability
if column.Nullable {
_, _ = buf.WriteString(" NULL")
} else {
_, _ = buf.WriteString(" NOT NULL")
}
// Add default value if present
if column.GetDefault() != "" {
_, _ = buf.WriteString(" DEFAULT ")
_, _ = buf.WriteString(column.GetDefault())
}
}
return buf.String()
}
func generateAlterColumn(schemaName, tableName string, colDiff *schema.ColumnDiff) string {
var buf strings.Builder
// In MSSQL, we need to handle different aspects of column changes separately
// If type changed, alter the column type
if colDiff.OldColumn.Type != colDiff.NewColumn.Type {
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("] ALTER COLUMN [")
_, _ = buf.WriteString(colDiff.NewColumn.Name)
_, _ = buf.WriteString("] ")
_, _ = buf.WriteString(colDiff.NewColumn.Type)
if colDiff.NewColumn.Nullable {
_, _ = buf.WriteString(" NULL")
} else {
_, _ = buf.WriteString(" NOT NULL")
}
_, _ = buf.WriteString(";\n")
} else if colDiff.OldColumn.Nullable != colDiff.NewColumn.Nullable {
// If only nullability changed
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("] ALTER COLUMN [")
_, _ = buf.WriteString(colDiff.NewColumn.Name)
_, _ = buf.WriteString("] ")
_, _ = buf.WriteString(colDiff.NewColumn.Type)
if colDiff.NewColumn.Nullable {
_, _ = buf.WriteString(" NULL")
} else {
_, _ = buf.WriteString(" NOT NULL")
}
_, _ = buf.WriteString(";\n")
}
// Handle default value changes
oldDefault := getColumnDefaultValue(colDiff.OldColumn)
newDefault := getColumnDefaultValue(colDiff.NewColumn)
if oldDefault != newDefault {
// First, drop the existing default constraint if it exists
if oldDefault != "" {
if colDiff.OldColumn.DefaultConstraintName != "" {
// Use the known constraint name directly (when synced from database)
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("] DROP CONSTRAINT [")
_, _ = buf.WriteString(colDiff.OldColumn.DefaultConstraintName)
_, _ = buf.WriteString("];\n")
}
// Note: If DefaultConstraintName is empty (e.g., when parsed from SQL), we cannot drop the constraint
// as we don't know its name. The user needs to drop it manually or sync from the database first.
}
// Then, add the new default constraint if specified
if newDefault != "" {
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("] ADD CONSTRAINT [DF_")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("_")
_, _ = buf.WriteString(colDiff.NewColumn.Name)
_, _ = buf.WriteString("] DEFAULT ")
_, _ = buf.WriteString(newDefault)
_, _ = buf.WriteString(" FOR [")
_, _ = buf.WriteString(colDiff.NewColumn.Name)
_, _ = buf.WriteString("];\n")
}
}
return buf.String()
}
// getColumnDefaultValue extracts the default value from a column
func getColumnDefaultValue(column *metadatapb.ColumnMetadata) string {
if column == nil {
return ""
}
if column.GetDefault() != "" {
return column.GetDefault()
}
return ""
}
func generateCreateIndex(schemaName, tableName string, index *metadatapb.IndexMetadata) string {
var buf strings.Builder
_, _ = buf.WriteString("CREATE")
// Handle columnstore indexes specially
indexType := strings.ToUpper(index.Type)
switch indexType {
case "CLUSTERED COLUMNSTORE":
_, _ = buf.WriteString(" CLUSTERED COLUMNSTORE INDEX [")
_, _ = buf.WriteString(index.Name)
_, _ = buf.WriteString("] ON [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("]")
// Clustered columnstore indexes don't specify columns
return buf.String()
case "NONCLUSTERED COLUMNSTORE":
_, _ = buf.WriteString(" NONCLUSTERED COLUMNSTORE INDEX [")
_, _ = buf.WriteString(index.Name)
_, _ = buf.WriteString("] ON [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("]")
// Nonclustered columnstore indexes may specify columns
if len(index.Expressions) > 0 {
_, _ = buf.WriteString(" (")
for i, expr := range index.Expressions {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("]")
}
_, _ = buf.WriteString(")")
}
return buf.String()
case "SPATIAL":
return generateSpatialIndexDDL(index, schemaName, tableName)
default:
// Regular indexes
if index.Unique {
_, _ = buf.WriteString(" UNIQUE")
}
switch index.Type {
case "CLUSTERED":
_, _ = buf.WriteString(" CLUSTERED")
case "NONCLUSTERED":
_, _ = buf.WriteString(" NONCLUSTERED")
default:
// Other index types
}
_, _ = buf.WriteString(" INDEX [")
_, _ = buf.WriteString(index.Name)
_, _ = buf.WriteString("] ON [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("] (")
for i, expr := range index.Expressions {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("]")
}
_, _ = buf.WriteString(")")
return buf.String()
}
}
func generateAddForeignKey(schemaName, tableName string, fk *metadatapb.ForeignKeyMetadata) string {
var buf strings.Builder
_, _ = buf.WriteString("ALTER TABLE [")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("] ADD CONSTRAINT [")
_, _ = buf.WriteString(fk.Name)
_, _ = buf.WriteString("] FOREIGN KEY (")
for i, col := range fk.Columns {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(col)
_, _ = buf.WriteString("]")
}
_, _ = buf.WriteString(") REFERENCES ")
if fk.ReferencedSchema != "" {
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(fk.ReferencedSchema)
_, _ = buf.WriteString("].")
}
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(fk.ReferencedTable)
_, _ = buf.WriteString("] (")
for i, col := range fk.ReferencedColumns {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("[")
_, _ = buf.WriteString(col)
_, _ = buf.WriteString("]")
}
_, _ = buf.WriteString(")")
// Add ON DELETE action
if fk.OnDelete != "" {
_, _ = buf.WriteString(" ON DELETE ")
_, _ = buf.WriteString(fk.OnDelete)
}
// Add ON UPDATE action
if fk.OnUpdate != "" {
_, _ = buf.WriteString(" ON UPDATE ")
_, _ = buf.WriteString(fk.OnUpdate)
}
return buf.String()
}
// hasConstraintsInTable checks if the table has any constraints (primary key or unique)
func hasConstraintsInTable(table *metadatapb.TableMetadata) bool {
for _, idx := range table.Indexes {
if idx.IsConstraint {
return true
}
}
return false
}
// generateExtendedPropertySQL generates SQL for adding, updating, or dropping extended properties (comments)
func generateExtendedPropertySQL(action, objectType, schemaName, objectName, comment string) string {
var buf strings.Builder
switch action {
case "ADD":
_, _ = buf.WriteString("EXEC sp_addextendedproperty 'MS_Description', '")
_, _ = buf.WriteString(strings.ReplaceAll(comment, "'", "''")) // Escape single quotes
_, _ = buf.WriteString("', 'SCHEMA', '")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("', '")
_, _ = buf.WriteString(objectType)
_, _ = buf.WriteString("', '")
_, _ = buf.WriteString(objectName)
_, _ = buf.WriteString("'")
case "UPDATE":
_, _ = buf.WriteString("EXEC sp_updateextendedproperty 'MS_Description', '")
_, _ = buf.WriteString(strings.ReplaceAll(comment, "'", "''")) // Escape single quotes
_, _ = buf.WriteString("', 'SCHEMA', '")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("', '")
_, _ = buf.WriteString(objectType)
_, _ = buf.WriteString("', '")
_, _ = buf.WriteString(objectName)
_, _ = buf.WriteString("'")
case "DROP":
_, _ = buf.WriteString("EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', '")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("', '")
_, _ = buf.WriteString(objectType)
_, _ = buf.WriteString("', '")
_, _ = buf.WriteString(objectName)
_, _ = buf.WriteString("'")
default:
// Other actions
}
return buf.String()
}
// generateViewCommentSQL generates SQL for view comment changes
func generateViewCommentSQL(action, schemaName, viewName, comment string) string {
return generateExtendedPropertySQL(action, "VIEW", schemaName, viewName, comment)
}
// generateFunctionCommentSQL generates SQL for function comment changes
func generateFunctionCommentSQL(action, schemaName, functionName, comment string) string {
return generateExtendedPropertySQL(action, "FUNCTION", schemaName, functionName, comment)
}
// generateSequenceCommentSQL generates SQL for sequence comment changes
func generateSequenceCommentSQL(action, schemaName, sequenceName, comment string) string {
return generateExtendedPropertySQL(action, "SEQUENCE", schemaName, sequenceName, comment)
}
// generateTableCommentSQL generates SQL for table comment changes
func generateTableCommentSQL(action, schemaName, tableName, comment string) string {
return generateExtendedPropertySQL(action, "TABLE", schemaName, tableName, comment)
}
// generateColumnCommentSQL generates SQL for column comment changes
func generateColumnCommentSQL(action, schemaName, tableName, columnName, comment string) string {
var buf strings.Builder
switch action {
case "ADD":
_, _ = buf.WriteString("EXEC sp_addextendedproperty 'MS_Description', '")
_, _ = buf.WriteString(strings.ReplaceAll(comment, "'", "''")) // Escape single quotes
_, _ = buf.WriteString("', 'SCHEMA', '")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("', 'TABLE', '")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("', 'COLUMN', '")
_, _ = buf.WriteString(columnName)
_, _ = buf.WriteString("'")
case "UPDATE":
_, _ = buf.WriteString("EXEC sp_updateextendedproperty 'MS_Description', '")
_, _ = buf.WriteString(strings.ReplaceAll(comment, "'", "''")) // Escape single quotes
_, _ = buf.WriteString("', 'SCHEMA', '")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("', 'TABLE', '")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("', 'COLUMN', '")
_, _ = buf.WriteString(columnName)
_, _ = buf.WriteString("'")
case "DROP":
_, _ = buf.WriteString("EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', '")
_, _ = buf.WriteString(schemaName)
_, _ = buf.WriteString("', 'TABLE', '")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("', 'COLUMN', '")
_, _ = buf.WriteString(columnName)
_, _ = buf.WriteString("'")
default:
// Other actions
}
return buf.String()
}
// getViewDependencies extracts the tables that a view depends on
func getViewDependencies(viewDef string, schemaName string) ([]string, error) {
// Parse the CREATE VIEW statement to extract the query properly
// We need to find the AS keyword that's part of CREATE VIEW, not column aliases.
stmts, err := tsql.ParseTSQL(viewDef)
if err != nil {
return nil, errors.Wrapf(err, "failed to parse view definition")
}
if len(stmts) != 1 {
return nil, errors.Errorf("expected exactly 1 statement, got %d", len(stmts))
}
createView, ok := stmts[0].AST.(*ast.CreateViewStmt)
if !ok || createView.Query == nil {
return []string{}, nil
}
selectStmt, ok := createView.Query.(*ast.SelectStmt)
if !ok {
return []string{}, nil
}
// For set operations (UNION/INTERSECT/EXCEPT) the omni parser leaves the
// combined node's Loc.End at -1 because the operator is detected before the
// outer SelectStmt's End is assigned. Walk the Larg/Rarg chain to find the
// real end offset of the entire query expression.
start := selectStmt.Loc.Start
end := selectStmtSpanEnd(selectStmt)
if start < 0 || end < 0 || start >= end || end > len(viewDef) {
return []string{}, nil
}
selectBody := viewDef[start:end]
// Use GetQuerySpan with mock functions to avoid nil pointer dereference
span, err := tsql.GetQuerySpan(
context.Background(),
base.GetQuerySpanContext{
GetDatabaseMetadataFunc: func(_ context.Context, _, databaseName string) (string, *model.DatabaseMetadata, error) {
// Return minimal metadata - we only need table references, not column info
metadata := &metadatapb.DatabaseSchemaMetadata{
Name: databaseName,
Schemas: []*metadatapb.SchemaMetadata{
{
Name: schemaName,
Tables: []*metadatapb.TableMetadata{},
},
},
}
dbMetadata := model.NewDatabaseMetadata(metadata, nil, nil, storepb.Engine_MSSQL, false)
return databaseName, dbMetadata, nil
},
ListDatabaseNamesFunc: func(_ context.Context, _ string) ([]string, error) {
// Return empty list - we don't need actual database names for dependency extraction
return []string{}, nil
},
},
base.Statement{Text: selectBody},
"", // database
schemaName,
false, // case sensitive
)
// If error parsing query span, return empty dependencies to allow migration to proceed
// This is intentional - we prefer to proceed with no dependencies rather than block the migration
if err != nil {
return []string{}, nil // nolint:nilerr
}
// Collect unique dependencies
dependencyMap := make(map[string]bool)
for sourceColumn := range span.SourceColumns {
// Create dependency ID in format: schema.table
depID := getObjectID(sourceColumn.Schema, sourceColumn.Table)
dependencyMap[depID] = true
}
// Convert map to slice
var dependencies []string
for dep := range dependencyMap {
dependencies = append(dependencies, dep)
}
return dependencies, nil
}
// selectStmtSpanEnd returns the maximum byte-offset End across a SelectStmt
// and its Larg/Rarg chain. Set-operation SelectStmt nodes inherit Loc.End=-1
// from their left operand, so the only reliable end is the right-most leaf.
func selectStmtSpanEnd(s *ast.SelectStmt) int {
if s == nil {
return -1
}
end := s.Loc.End
if e := selectStmtSpanEnd(s.Larg); e > end {
end = e
}
if e := selectStmtSpanEnd(s.Rarg); e > end {
end = e
}
return end
}
// getObjectID creates a unique identifier for a database object
func getObjectID(schema, name string) string {
if schema == "" {
return name
}
return fmt.Sprintf("%s.%s", schema, name)
}
// dropViewsInOrder drops views in reverse topological order (dependent views first)
func dropViewsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) {
// Build dependency graph for views being dropped or altered
graph := base.NewGraph()
viewMap := make(map[string]*schema.ViewDiff)
// First pass: Add all views to be dropped or altered to the graph and viewMap
// Sort for deterministic processing order
var viewsToProcess []*schema.ViewDiff
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop || viewDiff.Action == schema.MetadataDiffActionAlter {
viewsToProcess = append(viewsToProcess, viewDiff)
}
}
slices.SortFunc(viewsToProcess, func(i, j *schema.ViewDiff) int {
iFullName := getObjectID(i.SchemaName, i.ViewName)
jFullName := getObjectID(j.SchemaName, j.ViewName)
if iFullName < jFullName {
return -1
}
if iFullName > jFullName {
return 1
}
return 0
})
for _, viewDiff := range viewsToProcess {
viewID := getObjectID(viewDiff.SchemaName, viewDiff.ViewName)
graph.AddNode(viewID)
viewMap[viewID] = viewDiff
}
// Second pass: Add dependency edges now that all views are in viewMap
for _, viewDiff := range viewsToProcess {
viewID := getObjectID(viewDiff.SchemaName, viewDiff.ViewName)
// Get dependencies from the old view definition
if viewDiff.OldView != nil && viewDiff.OldView.Definition != "" {
deps, err := getViewDependencies(viewDiff.OldView.Definition, viewDiff.SchemaName)
if err != nil {
// If we can't parse dependencies, we'll just drop in original order
continue
}
// Add edges from this view to its dependencies
for _, dep := range deps {
// Only add edge if the dependency is also being dropped/altered
if _, exists := viewMap[dep]; exists {
graph.AddEdge(viewID, dep)
}
}
}
}
// Get topological order
orderedList, err := graph.TopologicalSort()
if err != nil {
// If there's a cycle or error, fall back to original order
var fallbackViews []*schema.ViewDiff
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop || viewDiff.Action == schema.MetadataDiffActionAlter {
fallbackViews = append(fallbackViews, viewDiff)
}
}
// Sort alphabetically for deterministic output
slices.SortFunc(fallbackViews, func(i, j *schema.ViewDiff) int {
iFullName := getObjectID(i.SchemaName, i.ViewName)
jFullName := getObjectID(j.SchemaName, j.ViewName)
if iFullName < jFullName {
return -1
}
if iFullName > jFullName {
return 1
}
return 0
})
for _, viewDiff := range fallbackViews {
_, _ = buf.WriteString("DROP VIEW [")
_, _ = buf.WriteString(viewDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(viewDiff.ViewName)
_, _ = buf.WriteString("];\nGO\n")
}
return
}
// Drop views in order (most dependent first due to edge direction)
for _, viewID := range orderedList {
if viewDiff, ok := viewMap[viewID]; ok {
_, _ = buf.WriteString("DROP VIEW [")
_, _ = buf.WriteString(viewDiff.SchemaName)
_, _ = buf.WriteString("].[")
_, _ = buf.WriteString(viewDiff.ViewName)
_, _ = buf.WriteString("];\nGO\n")
}
}
}
// createTablesInOrder creates tables in topological order (dependencies first based on foreign keys)
func createTablesInOrder(diff *schema.MetadataDiff, buf *strings.Builder) {
// Build dependency graph for tables being created
graph := base.NewGraph()
tableMap := make(map[string]*schema.TableDiff)
// First pass: Add all tables to be created to the graph and tableMap
// Sort for deterministic processing order
var tablesToCreate []*schema.TableDiff
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate {
tablesToCreate = append(tablesToCreate, tableDiff)
}
}
slices.SortFunc(tablesToCreate, func(i, j *schema.TableDiff) int {
iFullName := getObjectID(i.SchemaName, i.TableName)
jFullName := getObjectID(j.SchemaName, j.TableName)
if iFullName < jFullName {
return -1
}
if iFullName > jFullName {
return 1
}
return 0
})
for _, tableDiff := range tablesToCreate {
tableID := getObjectID(tableDiff.SchemaName, tableDiff.TableName)
graph.AddNode(tableID)
tableMap[tableID] = tableDiff
}
// Second pass: Add dependency edges based on foreign keys
for _, tableDiff := range tablesToCreate {
tableID := getObjectID(tableDiff.SchemaName, tableDiff.TableName)
// Add edges based on foreign key dependencies
if tableDiff.NewTable != nil {
for _, fk := range tableDiff.NewTable.ForeignKeys {
// Create dependency ID for the referenced table
refTableID := getObjectID(fk.ReferencedSchema, fk.ReferencedTable)
// Only add edge if the referenced table is also being created
if _, exists := tableMap[refTableID]; exists {
// Edge from referenced table to this table (referenced must be created first)
graph.AddEdge(refTableID, tableID)
}
}
}
}
// Get topological order
orderedList, err := graph.TopologicalSort()
if err != nil {
// If there's a cycle or error, fall back to alphabetical order
for _, tableDiff := range tablesToCreate {
createTableSQL := generateCreateTable(tableDiff.SchemaName, tableDiff.TableName, tableDiff.NewTable)
_, _ = buf.WriteString(createTableSQL)
_, _ = buf.WriteString("\n")
}
return
}
// Create tables in order
for _, tableID := range orderedList {
if tableDiff, ok := tableMap[tableID]; ok {
createTableSQL := generateCreateTable(tableDiff.SchemaName, tableDiff.TableName, tableDiff.NewTable)
_, _ = buf.WriteString(createTableSQL)
_, _ = buf.WriteString("\n")
}
}
}
// createViewsInOrder creates views in topological order (dependencies first)
func createViewsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) {
// Build dependency graph for views being created or altered
graph := base.NewGraph()
viewMap := make(map[string]*schema.ViewDiff)
// First pass: Add all views to be created or altered to the graph and viewMap
// Sort for deterministic processing order
var viewsToProcess []*schema.ViewDiff
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.Action == schema.MetadataDiffActionAlter {
viewsToProcess = append(viewsToProcess, viewDiff)
}
}
slices.SortFunc(viewsToProcess, func(i, j *schema.ViewDiff) int {
iFullName := getObjectID(i.SchemaName, i.ViewName)
jFullName := getObjectID(j.SchemaName, j.ViewName)
if iFullName < jFullName {
return -1
}
if iFullName > jFullName {
return 1
}
return 0
})
for _, viewDiff := range viewsToProcess {
viewID := getObjectID(viewDiff.SchemaName, viewDiff.ViewName)
graph.AddNode(viewID)
viewMap[viewID] = viewDiff
}
// Second pass: Add dependency edges now that all views are in viewMap
for _, viewDiff := range viewsToProcess {
viewID := getObjectID(viewDiff.SchemaName, viewDiff.ViewName)
// Get dependencies from the new view definition
if viewDiff.NewView != nil && viewDiff.NewView.Definition != "" {
deps, err := getViewDependencies(viewDiff.NewView.Definition, viewDiff.SchemaName)
if err != nil {
// If we can't parse dependencies, we'll just create in original order
continue
}
// Add edges from dependencies to this view
for _, dep := range deps {
// Only add edge if the dependency is also being created/altered
if _, exists := viewMap[dep]; exists {
graph.AddEdge(dep, viewID)
}
}
}
}
// Get topological order
orderedList, err := graph.TopologicalSort()
if err != nil {
// If there's a cycle or error, fall back to original order
var fallbackViews []*schema.ViewDiff
for _, viewDiff := range diff.ViewChanges {
// Only handle CREATE and ALTER (as create) in this phase
if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.Action == schema.MetadataDiffActionAlter {
fallbackViews = append(fallbackViews, viewDiff)
}
}
// Sort alphabetically for deterministic output
slices.SortFunc(fallbackViews, func(i, j *schema.ViewDiff) int {
iFullName := getObjectID(i.SchemaName, i.ViewName)
jFullName := getObjectID(j.SchemaName, j.ViewName)
if iFullName < jFullName {
return -1
}
if iFullName > jFullName {
return 1
}
return 0
})
for _, viewDiff := range fallbackViews {
_, _ = buf.WriteString(viewDiff.NewView.Definition)
if !strings.HasSuffix(strings.TrimSpace(viewDiff.NewView.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\nGO\n")
// Add view comment if present
if viewDiff.NewView.Comment != "" {
commentSQL := generateViewCommentSQL("ADD", viewDiff.SchemaName, viewDiff.ViewName, viewDiff.NewView.Comment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\nGO\n")
}
}
return
}
// Create views in order
for _, viewID := range orderedList {
if viewDiff, ok := viewMap[viewID]; ok {
// Both CREATE and ALTER actions create the view in this phase
// (ALTER views were already dropped in the drop phase)
_, _ = buf.WriteString(viewDiff.NewView.Definition)
if !strings.HasSuffix(strings.TrimSpace(viewDiff.NewView.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\nGO\n")
// Add view comment if present
if viewDiff.NewView.Comment != "" {
commentSQL := generateViewCommentSQL("ADD", viewDiff.SchemaName, viewDiff.ViewName, viewDiff.NewView.Comment)
_, _ = buf.WriteString(commentSQL)
_, _ = buf.WriteString(";\nGO\n")
}
}
}
}
// Helper functions to check if diff contains certain types of changes
func hasCreateOrAlterTables(diff *schema.MetadataDiff) bool {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate || tableDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
return false
}
func hasCreateViews(diff *schema.MetadataDiff) bool {
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
return false
}
func hasCreateFunctions(diff *schema.MetadataDiff) bool {
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionCreate {
return true
}
}
return false
}
func hasCreateProcedures(diff *schema.MetadataDiff) bool {
for _, procDiff := range diff.ProcedureChanges {
if procDiff.Action == schema.MetadataDiffActionCreate || procDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
return false
}
func generateSpatialIndexDDL(index *metadatapb.IndexMetadata, schemaName, tableName string) string {
var buf strings.Builder
// Build the CREATE SPATIAL INDEX statement
buf.WriteString("CREATE SPATIAL INDEX [")
buf.WriteString(index.Name)
buf.WriteString("] ON [")
buf.WriteString(schemaName)
buf.WriteString("].[")
buf.WriteString(tableName)
buf.WriteString("] (")
// Add column expressions
for i, expr := range index.Expressions {
if i > 0 {
buf.WriteString(", ")
}
buf.WriteString("[")
buf.WriteString(expr)
buf.WriteString("]")
}
buf.WriteString(")")
// Check if spatial configuration exists
if index.SpatialConfig == nil || index.SpatialConfig.Tessellation == nil {
// Add warning comment for incomplete configuration
buf.WriteString("\n/* WARNING: Spatial index configuration may be incomplete. ")
buf.WriteString("Please verify BOUNDING_BOX, GRIDS, and other parameters manually. */")
return buf.String()
}
tessellation := index.SpatialConfig.Tessellation
// Add USING clause for tessellation scheme
if tessellation.Scheme != "" {
buf.WriteString("\nUSING ")
buf.WriteString(tessellation.Scheme)
}
// Build WITH clause parameters
withParams := []string{}
// Add tessellation parameters
withParams = append(withParams, buildMigrationTessellationParams(tessellation)...)
// Add storage parameters
if index.SpatialConfig.Storage != nil {
withParams = append(withParams, buildMigrationStorageParams(index.SpatialConfig.Storage)...)
}
// Add WITH clause if we have parameters
if len(withParams) > 0 {
buf.WriteString("\nWITH (\n ")
buf.WriteString(strings.Join(withParams, ",\n "))
buf.WriteString("\n)")
}
return buf.String()
}
func buildMigrationTessellationParams(tessellation *metadatapb.TessellationConfig) []string {
params := []string{}
// BOUNDING_BOX for GEOMETRY indexes
if tessellation.BoundingBox != nil {
bbox := tessellation.BoundingBox
params = append(params, fmt.Sprintf("BOUNDING_BOX = (%g, %g, %g, %g)",
bbox.Xmin, bbox.Ymin, bbox.Xmax, bbox.Ymax))
}
// GRIDS configuration
if len(tessellation.GridLevels) > 0 {
gridParts := []string{}
for _, level := range tessellation.GridLevels {
gridParts = append(gridParts, fmt.Sprintf("LEVEL_%d = %s", level.Level, level.Density))
}
if len(gridParts) > 0 {
params = append(params, fmt.Sprintf("GRIDS = (%s)", strings.Join(gridParts, ", ")))
}
}
// CELLS_PER_OBJECT
if tessellation.CellsPerObject > 0 {
params = append(params, fmt.Sprintf("CELLS_PER_OBJECT = %d", tessellation.CellsPerObject))
}
return params
}
func buildMigrationStorageParams(storage *metadatapb.StorageConfig) []string {
params := []string{}
// PAD_INDEX (defaults to OFF, so only output when ON)
if storage.PadIndex {
params = append(params, "PAD_INDEX = ON")
}
// FILLFACTOR
if storage.Fillfactor > 0 {
params = append(params, fmt.Sprintf("FILLFACTOR = %d", storage.Fillfactor))
}
// SORT_IN_TEMPDB
if storage.SortInTempdb != "" {
params = append(params, fmt.Sprintf("SORT_IN_TEMPDB = %s", storage.SortInTempdb))
}
// DROP_EXISTING
if storage.DropExisting {
params = append(params, "DROP_EXISTING = ON")
}
// ONLINE
if storage.Online {
params = append(params, "ONLINE = ON")
}
// ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS
// Note: For migration DDL, we output them when they are true
// This differs from definition DDL where we handle defaults differently
if storage.AllowRowLocks {
params = append(params, "ALLOW_ROW_LOCKS = ON")
}
if storage.AllowPageLocks {
params = append(params, "ALLOW_PAGE_LOCKS = ON")
}
// MAXDOP
if storage.Maxdop > 0 {
params = append(params, fmt.Sprintf("MAXDOP = %d", storage.Maxdop))
}
// DATA_COMPRESSION
if storage.DataCompression != "" && storage.DataCompression != "NONE" {
params = append(params, fmt.Sprintf("DATA_COMPRESSION = %s", storage.DataCompression))
}
return params
}