| 21 | } |
| 22 | |
| 23 | func generateMigration(diff *schema.MetadataDiff) (string, error) { |
| 24 | var buf strings.Builder |
| 25 | |
| 26 | // Collect schemas to create first (needed for checking if create phase will have content) |
| 27 | var schemasToCreate []string |
| 28 | for _, schemaDiff := range diff.SchemaChanges { |
| 29 | if schemaDiff.Action == schema.MetadataDiffActionCreate { |
| 30 | // Skip creating dbo schema as it already exists by default |
| 31 | if strings.ToLower(schemaDiff.SchemaName) != "dbo" { |
| 32 | schemasToCreate = append(schemasToCreate, schemaDiff.SchemaName) |
| 33 | } |
| 34 | } |
| 35 | } |
| 36 | slices.Sort(schemasToCreate) |
| 37 | |
| 38 | // Safe order for migrations: |
| 39 | // 1. Drop dependent objects first (in reverse dependency order) |
| 40 | // - Drop foreign keys |
| 41 | // - Drop indexes |
| 42 | // - Drop views (in reverse topological order) |
| 43 | // - Drop functions/procedures (might depend on tables) |
| 44 | // - Drop tables |
| 45 | // - Drop schemas |
| 46 | // 2. Create/Alter objects (in dependency order) |
| 47 | // - Create schemas |
| 48 | // - Create/Alter tables and columns |
| 49 | // - Create indexes |
| 50 | // - Create foreign keys |
| 51 | // - Create views (in topological order) |
| 52 | // - Create functions/procedures |
| 53 | |
| 54 | // Phase 1: Drop dependent objects |
| 55 | // 1.1 Drop foreign keys first (they depend on tables) |
| 56 | for _, tableDiff := range diff.TableChanges { |
| 57 | switch tableDiff.Action { |
| 58 | case schema.MetadataDiffActionAlter: |
| 59 | for _, fkDiff := range tableDiff.ForeignKeyChanges { |
| 60 | if fkDiff.Action == schema.MetadataDiffActionDrop { |
| 61 | _, _ = buf.WriteString("ALTER TABLE [") |
| 62 | _, _ = buf.WriteString(tableDiff.SchemaName) |
| 63 | _, _ = buf.WriteString("].[") |
| 64 | _, _ = buf.WriteString(tableDiff.TableName) |
| 65 | _, _ = buf.WriteString("] DROP CONSTRAINT [") |
| 66 | _, _ = buf.WriteString(fkDiff.OldForeignKey.Name) |
| 67 | _, _ = buf.WriteString("];\nGO\n") |
| 68 | } |
| 69 | } |
| 70 | case schema.MetadataDiffActionDrop: |
| 71 | // Drop foreign keys before dropping table |
| 72 | for _, fk := range tableDiff.OldTable.ForeignKeys { |
| 73 | _, _ = buf.WriteString("ALTER TABLE [") |
| 74 | _, _ = buf.WriteString(tableDiff.SchemaName) |
| 75 | _, _ = buf.WriteString("].[") |
| 76 | _, _ = buf.WriteString(tableDiff.TableName) |
| 77 | _, _ = buf.WriteString("] DROP CONSTRAINT [") |
| 78 | _, _ = buf.WriteString(fk.Name) |
| 79 | _, _ = buf.WriteString("];\n") |
| 80 | } |