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 oracle
import (
"cmp"
"fmt"
"slices"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
"google.golang.org/protobuf/proto"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
parserbase "github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/plugin/schema"
"github.com/bytebase/bytebase/backend/store/model"
)
func init() {
schema.RegisterGenerateMigration(storepb.Engine_ORACLE, generateMigration)
}
func generateMigration(diff *schema.MetadataDiff) (string, error) {
var buf strings.Builder
// Safe order for migrations:
// 1. Drop dependent objects first (in reverse dependency order)
// - Use topological sort to drop in safe order
// 2. Create/Alter objects (in dependency order)
// - Use topological sort to create in safe order
// Phase 1: Drop dependent objects using topological sort
dropObjectsInOrder(diff, &buf)
// Only add blank line if we have drops AND we're about to create something
dropPhaseHasContent := buf.Len() > 0
createPhaseWillHaveContent := hasCreateOrAlterObjects(diff)
if dropPhaseHasContent && createPhaseWillHaveContent {
_, _ = buf.WriteString("\n")
}
// Phase 2: Create/Alter objects using topological sort
createObjectsInOrder(diff, &buf)
return buf.String(), nil
}
// dropObjectsInOrder drops all objects in reverse topological order (most dependent first)
func dropObjectsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) {
// Build dependency graph for all objects being dropped or altered
graph := parserbase.NewGraph()
// Maps to store different object types
viewMap := make(map[string]*schema.ViewDiff)
materializedViewMap := make(map[string]*schema.MaterializedViewDiff)
tableMap := make(map[string]*schema.TableDiff)
functionMap := make(map[string]*schema.FunctionDiff)
procedureMap := make(map[string]*schema.ProcedureDiff)
sequenceMap := make(map[string]*schema.SequenceDiff)
// Track all object IDs for dependency resolution
allObjects := make(map[string]bool)
// Collect schemas being dropped to ensure their objects are dropped first
schemasBeingDropped := make(map[string]bool)
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionDrop {
schemasBeingDropped[schemaDiff.SchemaName] = true
}
}
// Add views to graph
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop || viewDiff.Action == schema.MetadataDiffActionAlter {
viewID := getMigrationObjectID(viewDiff.SchemaName, viewDiff.ViewName)
graph.AddNode(viewID)
viewMap[viewID] = viewDiff
allObjects[viewID] = true
}
}
// Add materialized views to graph
for _, mvDiff := range diff.MaterializedViewChanges {
if mvDiff.Action == schema.MetadataDiffActionDrop || mvDiff.Action == schema.MetadataDiffActionAlter {
mvID := getMigrationObjectID(mvDiff.SchemaName, mvDiff.MaterializedViewName)
graph.AddNode(mvID)
materializedViewMap[mvID] = mvDiff
allObjects[mvID] = true
}
}
// Add functions to graph
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionDrop {
funcID := getMigrationObjectID(funcDiff.SchemaName, funcDiff.FunctionName)
graph.AddNode(funcID)
functionMap[funcID] = funcDiff
allObjects[funcID] = true
}
}
// Add procedures to graph
for _, procDiff := range diff.ProcedureChanges {
if procDiff.Action == schema.MetadataDiffActionDrop {
procID := getMigrationObjectID(procDiff.SchemaName, procDiff.ProcedureName)
graph.AddNode(procID)
procedureMap[procID] = procDiff
allObjects[procID] = true
}
}
// Add tables to graph
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop {
tableID := getMigrationObjectID(tableDiff.SchemaName, tableDiff.TableName)
graph.AddNode(tableID)
tableMap[tableID] = tableDiff
allObjects[tableID] = true
}
}
// Add sequences to graph
for _, seqDiff := range diff.SequenceChanges {
if seqDiff.Action == schema.MetadataDiffActionDrop {
// Skip system-generated sequences that cannot be manually dropped
if isSystemGeneratedSequence(seqDiff.SequenceName) {
continue
}
seqID := getMigrationObjectID(seqDiff.SchemaName, seqDiff.SequenceName)
graph.AddNode(seqID)
sequenceMap[seqID] = seqDiff
allObjects[seqID] = true
}
}
// Add dependency edges
// For views depending on tables/views
for viewID, viewDiff := range viewMap {
if viewDiff.OldView != nil {
for _, dep := range viewDiff.OldView.DependencyColumns {
depID := getMigrationObjectID(dep.Schema, dep.Table)
if allObjects[depID] {
// Edge from dependent to dependency (view depends on table/view)
graph.AddEdge(viewID, depID)
}
}
}
}
// For materialized views depending on tables/views
for mvID, mvDiff := range materializedViewMap {
if mvDiff.OldMaterializedView != nil {
for _, dep := range mvDiff.OldMaterializedView.DependencyColumns {
depID := getMigrationObjectID(dep.Schema, dep.Table)
if allObjects[depID] {
// Edge from dependent to dependency
graph.AddEdge(mvID, depID)
}
}
}
}
// For functions depending on tables
for funcID, funcDiff := range functionMap {
if funcDiff.OldFunction != nil {
for _, dep := range funcDiff.OldFunction.DependencyTables {
depID := getMigrationObjectID(dep.Schema, dep.Table)
if allObjects[depID] {
// Edge from function to table
graph.AddEdge(funcID, depID)
}
}
}
}
// For tables with foreign keys depending on other tables
for tableID, tableDiff := range tableMap {
if tableDiff.OldTable != nil {
for _, fk := range tableDiff.OldTable.ForeignKeys {
depID := getMigrationObjectID(fk.ReferencedSchema, fk.ReferencedTable)
if allObjects[depID] && depID != tableID {
// Edge from table with FK to referenced table
graph.AddEdge(tableID, depID)
}
}
}
}
// Get topological order
orderedList, err := graph.TopologicalSort()
if err != nil {
// If there's a cycle, fall back to a safe order
// First drop foreign keys from ALTER operations that might reference tables being dropped
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionDrop {
writeDropForeignKey(buf, tableDiff.SchemaName, tableDiff.TableName, fkDiff.OldForeignKey.Name)
}
}
}
}
// Drop foreign keys from tables being dropped
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop && tableDiff.OldTable != nil {
for _, fk := range tableDiff.OldTable.ForeignKeys {
writeDropForeignKey(buf, tableDiff.SchemaName, tableDiff.TableName, fk.Name)
}
}
}
// Drop views
for _, viewDiff := range viewMap {
writeDropView(buf, viewDiff.SchemaName, viewDiff.ViewName)
}
// Drop materialized views
for _, mvDiff := range materializedViewMap {
writeDropMaterializedView(buf, mvDiff.SchemaName, mvDiff.MaterializedViewName)
}
// Drop functions
for _, funcDiff := range functionMap {
writeDropFunction(buf, funcDiff.SchemaName, funcDiff.FunctionName)
}
// Drop procedures
for _, procDiff := range procedureMap {
writeDropProcedure(buf, procDiff.SchemaName, procDiff.ProcedureName)
}
// Drop tables
for _, tableDiff := range tableMap {
writeDropTable(buf, tableDiff.SchemaName, tableDiff.TableName)
}
// Drop sequences
for _, seqDiff := range sequenceMap {
writeDropSequence(buf, seqDiff.SchemaName, seqDiff.SequenceName)
}
// Handle remaining ALTER table operations (constraints, indexes, columns)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
// Drop check constraints
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionDrop {
writeDropCheckConstraint(buf, tableDiff.SchemaName, tableDiff.TableName, checkDiff.OldCheckConstraint.Name)
}
}
// Drop indexes
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionDrop {
if indexDiff.OldIndex.IsConstraint {
writeDropConstraint(buf, tableDiff.SchemaName, tableDiff.TableName, indexDiff.OldIndex.Name)
} else {
writeDropIndex(buf, tableDiff.SchemaName, indexDiff.OldIndex.Name)
}
}
}
// Drop columns
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionDrop {
writeDropColumn(buf, tableDiff.SchemaName, tableDiff.TableName, colDiff.OldColumn.Name)
}
}
// Drop triggers
for _, triggerDiff := range tableDiff.TriggerChanges {
if triggerDiff.Action == schema.MetadataDiffActionDrop {
writeDropTrigger(buf, triggerDiff.OldTrigger.Name)
}
}
}
}
} else {
// First, handle ALTER table drops (foreign keys that might reference tables being dropped)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
// Drop foreign keys first
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionDrop {
writeDropForeignKey(buf, tableDiff.SchemaName, tableDiff.TableName, fkDiff.OldForeignKey.Name)
}
}
}
}
// Drop in topological order (most dependent first)
for _, objID := range orderedList {
// Drop the object itself
if viewDiff, ok := viewMap[objID]; ok {
writeDropView(buf, viewDiff.SchemaName, viewDiff.ViewName)
} else if mvDiff, ok := materializedViewMap[objID]; ok {
writeDropMaterializedView(buf, mvDiff.SchemaName, mvDiff.MaterializedViewName)
} else if funcDiff, ok := functionMap[objID]; ok {
writeDropFunction(buf, funcDiff.SchemaName, funcDiff.FunctionName)
} else if procDiff, ok := procedureMap[objID]; ok {
writeDropProcedure(buf, procDiff.SchemaName, procDiff.ProcedureName)
} else if tableDiff, ok := tableMap[objID]; ok {
// Drop foreign keys before table
if tableDiff.OldTable != nil {
for _, fk := range tableDiff.OldTable.ForeignKeys {
writeDropForeignKey(buf, tableDiff.SchemaName, tableDiff.TableName, fk.Name)
}
}
writeDropTable(buf, tableDiff.SchemaName, tableDiff.TableName)
} else if seqDiff, ok := sequenceMap[objID]; ok {
writeDropSequence(buf, seqDiff.SchemaName, seqDiff.SequenceName)
}
}
// Handle remaining ALTER table drops (constraints, indexes, columns)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
// Drop check constraints
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionDrop {
writeDropCheckConstraint(buf, tableDiff.SchemaName, tableDiff.TableName, checkDiff.OldCheckConstraint.Name)
}
}
// Drop indexes
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionDrop {
if indexDiff.OldIndex.IsConstraint {
writeDropConstraint(buf, tableDiff.SchemaName, tableDiff.TableName, indexDiff.OldIndex.Name)
} else {
writeDropIndex(buf, tableDiff.SchemaName, indexDiff.OldIndex.Name)
}
}
}
// Drop columns
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionDrop {
writeDropColumn(buf, tableDiff.SchemaName, tableDiff.TableName, colDiff.OldColumn.Name)
}
}
// Drop triggers
for _, triggerDiff := range tableDiff.TriggerChanges {
if triggerDiff.Action == schema.MetadataDiffActionDrop {
writeDropTrigger(buf, triggerDiff.OldTrigger.Name)
}
}
}
}
}
// Drop schemas (must be empty)
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionDrop {
// Before dropping a schema, we need to drop all objects inside it
// This handles cases where the diff doesn't include objects within a schema being dropped
if schemaDiff.OldSchema != nil {
// Drop all objects in the schema in the correct order
// Drop views first (they might depend on tables)
for _, view := range schemaDiff.OldSchema.Views {
writeDropView(buf, schemaDiff.SchemaName, view.Name)
}
// Drop materialized views
for _, mv := range schemaDiff.OldSchema.MaterializedViews {
writeDropMaterializedView(buf, schemaDiff.SchemaName, mv.Name)
}
// Drop functions
for _, fn := range schemaDiff.OldSchema.Functions {
writeDropFunction(buf, schemaDiff.SchemaName, fn.Name)
}
// Drop procedures
for _, proc := range schemaDiff.OldSchema.Procedures {
writeDropProcedure(buf, schemaDiff.SchemaName, proc.Name)
}
// Drop tables (this will handle foreign keys internally)
for _, table := range schemaDiff.OldSchema.Tables {
// Drop the table
writeDropTable(buf, schemaDiff.SchemaName, table.Name)
}
// Drop sequences
for _, seq := range schemaDiff.OldSchema.Sequences {
writeDropSequence(buf, schemaDiff.SchemaName, seq.Name)
}
}
writeDropSchema(buf, schemaDiff.SchemaName)
}
}
}
// createObjectsInOrder creates all objects in topological order (dependencies first)
func createObjectsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) {
// First create schemas (they don't have dependencies)
var schemasToCreate []string
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionCreate {
schemasToCreate = append(schemasToCreate, schemaDiff.SchemaName)
}
}
slices.Sort(schemasToCreate)
for _, schemaName := range schemasToCreate {
writeCreateSchema(buf, schemaName)
}
// Add blank line after schema creation only if we have schemas and more content follows
if len(schemasToCreate) > 0 && (hasCreateOrAlterTables(diff) || hasCreateViewsOrFunctions(diff)) {
_, _ = buf.WriteString("\n")
}
// Create sequences (before tables as they might be used in column defaults)
for _, seqDiff := range diff.SequenceChanges {
if seqDiff.Action == schema.MetadataDiffActionCreate {
// Skip system-generated sequences that are automatically created by Oracle
if isSystemGeneratedSequence(seqDiff.SequenceName) {
continue
}
writeMigrationCreateSequence(buf, seqDiff.SchemaName, seqDiff.NewSequence)
}
}
// Build dependency graph for all objects being created or altered
graph := parserbase.NewGraph()
// Maps to store different object types
viewMap := make(map[string]*schema.ViewDiff)
materializedViewMap := make(map[string]*schema.MaterializedViewDiff)
tableMap := make(map[string]*schema.TableDiff)
functionMap := make(map[string]*schema.FunctionDiff)
procedureMap := make(map[string]*schema.ProcedureDiff)
// Track all object IDs for dependency resolution
allObjects := make(map[string]bool)
// Add tables to graph (both CREATE and ALTER for column additions)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate || tableDiff.Action == schema.MetadataDiffActionAlter {
tableID := getMigrationObjectID(tableDiff.SchemaName, tableDiff.TableName)
graph.AddNode(tableID)
tableMap[tableID] = tableDiff
allObjects[tableID] = true
}
}
// Add views to graph
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.Action == schema.MetadataDiffActionAlter {
viewID := getMigrationObjectID(viewDiff.SchemaName, viewDiff.ViewName)
graph.AddNode(viewID)
viewMap[viewID] = viewDiff
allObjects[viewID] = true
}
}
// Add materialized views to graph
for _, mvDiff := range diff.MaterializedViewChanges {
if mvDiff.Action == schema.MetadataDiffActionCreate || mvDiff.Action == schema.MetadataDiffActionAlter {
mvID := getMigrationObjectID(mvDiff.SchemaName, mvDiff.MaterializedViewName)
graph.AddNode(mvID)
materializedViewMap[mvID] = mvDiff
allObjects[mvID] = true
}
}
// Add functions to graph
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionCreate || funcDiff.Action == schema.MetadataDiffActionAlter {
funcID := getMigrationObjectID(funcDiff.SchemaName, funcDiff.FunctionName)
graph.AddNode(funcID)
functionMap[funcID] = funcDiff
allObjects[funcID] = true
}
}
// Add procedures to graph for creation
for _, procDiff := range diff.ProcedureChanges {
if procDiff.Action == schema.MetadataDiffActionCreate || procDiff.Action == schema.MetadataDiffActionAlter {
procID := getMigrationObjectID(procDiff.SchemaName, procDiff.ProcedureName)
graph.AddNode(procID)
procedureMap[procID] = procDiff
allObjects[procID] = true
}
}
// Add dependency edges
// For tables with foreign keys depending on other tables (only for CREATE operations)
for tableID, tableDiff := range tableMap {
if tableDiff.Action == schema.MetadataDiffActionCreate && tableDiff.NewTable != nil {
for _, fk := range tableDiff.NewTable.ForeignKeys {
depID := getMigrationObjectID(fk.ReferencedSchema, fk.ReferencedTable)
if depID != tableID {
// Edge from dependency to dependent (referenced table to table with FK)
graph.AddEdge(depID, tableID)
}
}
}
}
// For views depending on tables/views
for viewID, viewDiff := range viewMap {
if viewDiff.NewView != nil {
for _, dep := range viewDiff.NewView.DependencyColumns {
depID := getMigrationObjectID(dep.Schema, dep.Table)
// Only add edge if the dependency exists in our objects being created
if allObjects[depID] {
// Edge from dependency to dependent (table/view to view)
graph.AddEdge(depID, viewID)
} else {
// Try with empty schema if the dependency schema doesn't match
depIDNoSchema := getMigrationObjectID("", dep.Table)
if allObjects[depIDNoSchema] {
// Edge from dependency to dependent (table/view to view)
graph.AddEdge(depIDNoSchema, viewID)
}
}
}
}
}
// For materialized views depending on tables/views
for mvID, mvDiff := range materializedViewMap {
if mvDiff.NewMaterializedView != nil {
for _, dep := range mvDiff.NewMaterializedView.DependencyColumns {
depID := getMigrationObjectID(dep.Schema, dep.Table)
// Only add edge if the dependency exists in our objects being created
if allObjects[depID] {
// Edge from dependency to dependent
graph.AddEdge(depID, mvID)
}
}
}
}
// For functions depending on tables
for funcID, funcDiff := range functionMap {
if funcDiff.NewFunction != nil {
for _, dep := range funcDiff.NewFunction.DependencyTables {
depID := getMigrationObjectID(dep.Schema, dep.Table)
// Only add edge if the dependency exists in our objects being created
if allObjects[depID] {
// Edge from table to function
graph.AddEdge(depID, funcID)
}
}
}
}
// Get topological order
orderedList, err := graph.TopologicalSort()
if err != nil {
// If there's a cycle, fall back to a safe order
// Create tables first (without foreign keys)
for _, tableDiff := range tableMap {
if tableDiff.Action == schema.MetadataDiffActionCreate {
createTableSQL, err := generateCreateTable(tableDiff.SchemaName, tableDiff.TableName, tableDiff.NewTable, false)
if err != nil {
return
}
_, _ = buf.WriteString(createTableSQL)
if createTableSQL != "" {
_, _ = buf.WriteString("\n")
}
}
}
// Handle column additions for ALTER operations (only columns in topological order)
for _, tableDiff := range tableMap {
if tableDiff.Action == schema.MetadataDiffActionAlter {
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionCreate {
writeAddColumn(buf, tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn)
}
}
}
}
// Create views
for _, viewDiff := range viewMap {
switch viewDiff.Action {
case schema.MetadataDiffActionCreate, schema.MetadataDiffActionAlter:
writeMigrationView(buf, viewDiff.SchemaName, viewDiff.NewView)
default:
// No action needed
}
}
// Create materialized views
for _, mvDiff := range materializedViewMap {
switch mvDiff.Action {
case schema.MetadataDiffActionCreate, schema.MetadataDiffActionAlter:
writeMigrationMaterializedView(buf, mvDiff.SchemaName, mvDiff.NewMaterializedView)
default:
// No action needed
}
}
// Create functions
for _, funcDiff := range functionMap {
writeFunctionDiff(buf, funcDiff)
}
// Create procedures
for _, procDiff := range procedureMap {
writeProcedureDiff(buf, procDiff)
}
// Add foreign keys (only for CREATE operations)
for _, tableDiff := range tableMap {
if tableDiff.Action == schema.MetadataDiffActionCreate && tableDiff.NewTable != nil {
for _, fk := range tableDiff.NewTable.ForeignKeys {
if !waitsForRebuild(diff, tableDiff.SchemaName, fk) {
writeMigrationForeignKey(buf, tableDiff.SchemaName, tableDiff.TableName, fk)
}
}
}
}
} else {
// Create in topological order (dependencies first)
for _, objID := range orderedList {
if tableDiff, ok := tableMap[objID]; ok {
switch tableDiff.Action {
case schema.MetadataDiffActionCreate:
// Create table without foreign keys
createTableSQL, err := generateCreateTable(tableDiff.SchemaName, tableDiff.TableName, tableDiff.NewTable, false)
if err != nil {
return
}
_, _ = buf.WriteString(createTableSQL)
if createTableSQL != "" {
_, _ = buf.WriteString("\n")
}
case schema.MetadataDiffActionAlter:
// Handle column additions for ALTER operations (only columns in topological order)
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionCreate {
writeAddColumn(buf, tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn)
}
}
default:
// No action needed
}
} else if viewDiff, ok := viewMap[objID]; ok {
switch viewDiff.Action {
case schema.MetadataDiffActionCreate, schema.MetadataDiffActionAlter:
writeMigrationView(buf, viewDiff.SchemaName, viewDiff.NewView)
default:
// No action needed
}
} else if mvDiff, ok := materializedViewMap[objID]; ok {
switch mvDiff.Action {
case schema.MetadataDiffActionCreate, schema.MetadataDiffActionAlter:
writeMigrationMaterializedView(buf, mvDiff.SchemaName, mvDiff.NewMaterializedView)
default:
// No action needed
}
} else if funcDiff, ok := functionMap[objID]; ok {
writeFunctionDiff(buf, funcDiff)
} else if procDiff, ok := procedureMap[objID]; ok {
writeProcedureDiff(buf, procDiff)
}
}
// Add foreign keys after all tables are created (only for CREATE operations)
for _, tableDiff := range tableMap {
if tableDiff.Action == schema.MetadataDiffActionCreate && tableDiff.NewTable != nil {
for _, fk := range tableDiff.NewTable.ForeignKeys {
if !waitsForRebuild(diff, tableDiff.SchemaName, fk) {
writeMigrationForeignKey(buf, tableDiff.SchemaName, tableDiff.TableName, fk)
}
}
}
}
}
// Handle remaining ALTER table operations (not columns, which are handled in topological order)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
// Skip column additions (already handled in topological order)
// Handle other ALTER operations
// Alter columns (type changes, nullability, etc.)
writeAlterColumns(buf, diff, tableDiff)
// Add indexes
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionCreate {
if indexDiff.NewIndex.IsConstraint {
// Add constraint (primary key or unique constraint)
if indexDiff.NewIndex.Primary {
writeMigrationPrimaryKey(buf, tableDiff.SchemaName, tableDiff.TableName, indexDiff.NewIndex)
} else if indexDiff.NewIndex.Unique {
writeMigrationUniqueKey(buf, tableDiff.SchemaName, tableDiff.TableName, indexDiff.NewIndex)
}
} else {
writeMigrationIndex(buf, tableDiff.SchemaName, tableDiff.TableName, indexDiff.NewIndex)
}
}
}
// Add check constraints
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionCreate {
writeAddCheckConstraint(buf, tableDiff.SchemaName, tableDiff.TableName, checkDiff.NewCheckConstraint)
}
}
// Add foreign keys
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionCreate {
writeMigrationForeignKey(buf, tableDiff.SchemaName, tableDiff.TableName, fkDiff.NewForeignKey)
}
}
// Add triggers
for _, triggerDiff := range tableDiff.TriggerChanges {
if triggerDiff.Action == schema.MetadataDiffActionCreate {
writeMigrationTrigger(buf, triggerDiff.NewTrigger)
}
}
// Handle table comment changes
generateTableCommentChanges(buf, tableDiff)
// Handle column comment changes
generateColumnCommentChanges(buf, tableDiff)
// Handle index comment changes
generateIndexCommentChanges(buf, tableDiff)
}
}
// Handle comment changes for other object types
generateViewCommentChanges(buf, diff)
generateMaterializedViewCommentChanges(buf, diff)
generateFunctionCommentChanges(buf, diff)
generateSequenceCommentChanges(buf, diff)
}
func generateCreateTable(schemaName, tableName string, table *metadatapb.TableMetadata, includeForeignKeys bool) (string, error) {
var buf strings.Builder
writeMigrationCreateTable(&buf, schemaName, tableName, table.Columns, table.CheckConstraints)
if _, err := buf.WriteString(";\n"); err != nil {
return "", err
}
// Add constraints (primary key, unique)
for _, index := range table.Indexes {
if index.Primary {
writeMigrationPrimaryKey(&buf, schemaName, tableName, index)
} else if index.Unique && index.IsConstraint {
writeMigrationUniqueKey(&buf, schemaName, tableName, index)
}
}
// Add non-constraint indexes
for _, index := range table.Indexes {
if !index.IsConstraint {
writeMigrationIndex(&buf, schemaName, tableName, index)
}
}
// Add table comment if present
if table.Comment != "" {
writeTableComment(&buf, schemaName, tableName, table.Comment)
}
// Add column comments if present
for _, col := range table.Columns {
if col.Comment != "" {
writeColumnComment(&buf, schemaName, tableName, col.Name, col.Comment)
}
}
// Optionally add foreign keys
if includeForeignKeys && table != nil {
for _, fk := range table.ForeignKeys {
writeMigrationForeignKey(&buf, schemaName, tableName, fk)
}
}
// Add triggers
for _, trigger := range table.Triggers {
writeMigrationTrigger(&buf, trigger)
}
return buf.String(), nil
}
// writeAlterColumns writes one table's column alterations. A rebuilt column
// cannot change while an index or a constraint is on it (ORA-12991, ORA-54022,
// ORA-54023), so those of every rebuilt column are dropped before any column
// changes and made again once all have, each once: a constraint shared by two
// rebuilt columns is checked only against the finished table.
func writeAlterColumns(buf *strings.Builder, diff *schema.MetadataDiff, tableDiff *schema.TableDiff) {
var oldColumns, newColumns []string
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionAlter && rebuildsColumn(colDiff) {
oldColumns = append(oldColumns, colDiff.OldColumn.Name)
newColumns = append(newColumns, colDiff.NewColumn.Name)
}
}
schemaName, tableName := tableDiff.SchemaName, tableDiff.TableName
created := diffObjectNames(tableDiff, schema.MetadataDiffActionCreate)
// Other tables' foreign keys on a rebuilt key go first, or dropping the key
// fails (ORA-02273), and come back after it.
for _, incoming := range findIncomingForeignKeys(diff, diff.OldDatabase, tableDiff, oldColumns, schema.MetadataDiffActionDrop) {
writeDropForeignKey(buf, schemaName, incoming.table, incoming.foreignKey.Name)
}
findColumnDependents(tableDiff.OldTable, oldColumns, diffObjectNames(tableDiff, schema.MetadataDiffActionDrop)).writeDrops(buf, schemaName, tableName)
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action != schema.MetadataDiffActionAlter {
continue
}
// A named NOT NULL went with the dependents. One the diff creates is
// left to it.
notNull := columnNotNull{dropped: notNullConstraintName(tableDiff.OldTable, colDiff.OldColumn.Name) != ""}
if name := notNullConstraintName(tableDiff.NewTable, colDiff.NewColumn.Name); !created[name] {
notNull.name = name
}
_, _ = buf.WriteString(generateAlterColumn(schemaName, tableName, colDiff, notNull))
}
findColumnDependents(tableDiff.NewTable, newColumns, created).writeCreates(buf, schemaName, tableName)
for _, incoming := range findIncomingForeignKeys(diff, diff.NewDatabase, tableDiff, newColumns, schema.MetadataDiffActionCreate) {
writeMigrationForeignKey(buf, schemaName, incoming.table, incoming.foreignKey)
}
}
// incomingForeignKey is another table's foreign key on a rebuilt column.
type incomingForeignKey struct {
table string
foreignKey *metadatapb.ForeignKeyMetadata
}
// findIncomingForeignKeys lists the foreign keys of the database's other tables
// that reference any of the columns of tableDiff's table. It leaves out those
// the diff handles itself: on a table it creates or drops, or that it creates
// or drops, as action says.
func findIncomingForeignKeys(diff *schema.MetadataDiff, database *model.DatabaseMetadata, tableDiff *schema.TableDiff, columns []string, action schema.MetadataDiffAction) []incomingForeignKey {
if len(columns) == 0 || database == nil {
return nil
}
schemaMetadata := database.GetSchemaMetadata(tableDiff.SchemaName)
if schemaMetadata == nil {
return nil
}
tableDiffs := make(map[string]*schema.TableDiff)
for _, other := range diff.TableChanges {
if other.SchemaName == tableDiff.SchemaName {
tableDiffs[other.TableName] = other
}
}
var result []incomingForeignKey
for _, table := range schemaMetadata.GetProto().GetTables() {
if table.Name == tableDiff.TableName {
continue
}
var handled map[string]bool
switch other := tableDiffs[table.Name]; {
case other == nil:
case other.Action == schema.MetadataDiffActionCreate && action == schema.MetadataDiffActionCreate:
// A new table's foreign keys on a rebuilt key waited for it.
case other.Action != schema.MetadataDiffActionAlter:
continue
default:
handled = diffObjectNames(other, action)
}
for _, fk := range table.GetForeignKeys() {
if !handled[fk.Name] && referencesColumns(fk, tableDiff.SchemaName, diff.DatabaseName, tableDiff.TableName, columns) {
result = append(result, incomingForeignKey{table: table.Name, foreignKey: fk})
}
}
}
return result
}
// waitsForRebuild reports whether a new table's foreign key references a column
// the diff rebuilds. writeAlterColumns adds it after the rebuild: added with
// the table, it would hold the key the rebuild drops (ORA-02273).
func waitsForRebuild(diff *schema.MetadataDiff, schemaName string, fk *metadatapb.ForeignKeyMetadata) bool {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action != schema.MetadataDiffActionAlter || tableDiff.SchemaName != schemaName {
continue
}
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionAlter && rebuildsColumn(colDiff) &&
referencesColumns(fk, schemaName, diff.DatabaseName, tableDiff.TableName, []string{colDiff.NewColumn.Name}) {
return true
}
}
}
return false
}
// referencesColumns reports whether the foreign key references any of the
// columns of the table, which the sync names without a schema when it is the
// foreign key's own.
func referencesColumns(fk *metadatapb.ForeignKeyMetadata, schemaName, databaseName, tableName string, columns []string) bool {
if fk.ReferencedTable != tableName || (fk.ReferencedSchema != "" && fk.ReferencedSchema != schemaName && fk.ReferencedSchema != databaseName) {
return false
}
return slices.ContainsFunc(fk.ReferencedColumns, func(column string) bool { return slices.Contains(columns, column) })
}
// rebuildsColumn reports whether Oracle must drop and add the column again, or
// give it a new virtual type or expression.
func rebuildsColumn(colDiff *schema.ColumnDiff) bool {
oldGeneration, newGeneration := colDiff.OldColumn.Generation, colDiff.NewColumn.Generation
if (oldGeneration == nil) != (newGeneration == nil) {
return true
}
return newGeneration != nil && (oldGeneration.Expression != newGeneration.Expression || colDiff.OldColumn.Type != colDiff.NewColumn.Type)
}
// columnNotNull is how a rebuilt column's NOT NULL is handled: dropped means a
// named one was dropped with the dependents, and name is the one to restore.
type columnNotNull struct {
dropped bool
name string
}
func generateAlterColumn(schemaName, tableName string, colDiff *schema.ColumnDiff, notNull columnNotNull) string {
var buf strings.Builder
// Oracle cannot turn a stored column into a virtual one or back, so the
// column is dropped and added again, comment included.
oldGeneration, newGeneration := colDiff.OldColumn.Generation, colDiff.NewColumn.Generation
if (oldGeneration == nil) != (newGeneration == nil) {
writeDropColumn(&buf, schemaName, tableName, colDiff.OldColumn.Name)
if notNull.name == "" {
writeAddColumn(&buf, schemaName, tableName, colDiff.NewColumn)
} else {
// Added nullable, so that NOT NULL comes back under its name.
column := proto.CloneOf(colDiff.NewColumn)
column.Nullable = true
writeAddColumn(&buf, schemaName, tableName, column)
writeSetNotNull(&buf, schemaName, tableName, colDiff.NewColumn, notNull.name)
}
if colDiff.NewColumn.Comment != "" {
writeColumnComment(&buf, schemaName, tableName, colDiff.NewColumn.Name, colDiff.NewColumn.Comment)
}
return buf.String()
}
// Oracle changes a virtual column's type only with its expression
// (ORA-54027), and NOT NULL blocks both (ORA-54023).
virtualChanged := newGeneration != nil && (oldGeneration.Expression != newGeneration.Expression || colDiff.OldColumn.Type != colDiff.NewColumn.Type)
if virtualChanged && !colDiff.OldColumn.Nullable && !notNull.dropped {
writeAlterColumnDropNotNull(&buf, schemaName, tableName, colDiff.OldColumn.Name)
}
// In Oracle, we need to handle different aspects of column changes separately
// If type changed, alter the column type
if !virtualChanged && colDiff.OldColumn.Type != colDiff.NewColumn.Type {
writeAlterColumnType(&buf, schemaName, tableName, colDiff.NewColumn.Name, colDiff.NewColumn.Type)
}
// If nullability changed
if !virtualChanged && colDiff.OldColumn.Nullable != colDiff.NewColumn.Nullable {
if colDiff.NewColumn.Nullable {
writeAlterColumnDropNotNull(&buf, schemaName, tableName, colDiff.NewColumn.Name)
} else {
writeAlterColumnSetNotNull(&buf, schemaName, tableName, colDiff.NewColumn.Name)
}
}
// Handle default value changes
oldHasDefault := hasDefaultValue(colDiff.OldColumn)
newHasDefault := hasDefaultValue(colDiff.NewColumn)
if oldHasDefault || newHasDefault {
if !defaultValuesEqual(colDiff.OldColumn, colDiff.NewColumn) {
// First drop the old default if it exists
if oldHasDefault {
writeAlterColumnDropDefault(&buf, schemaName, tableName, colDiff.OldColumn.Name)
}
// Add new default if needed
if newHasDefault {
defaultExpr := getDefaultExpression(colDiff.NewColumn)
writeAlterColumnSetDefault(&buf, schemaName, tableName, colDiff.NewColumn.Name, defaultExpr)
}
}
}
if virtualChanged {
writeModifyColumn(&buf, schemaName, tableName, colDiff.NewColumn.Name, colDiff.NewColumn.Type+" AS ("+newGeneration.Expression+")")
writeSetNotNull(&buf, schemaName, tableName, colDiff.NewColumn, notNull.name)
}
if colDiff.OldColumn.IsInvisible != colDiff.NewColumn.IsInvisible {
visibility := "VISIBLE"
if colDiff.NewColumn.IsInvisible {
visibility = "INVISIBLE"
}
writeModifyColumn(&buf, schemaName, tableName, colDiff.NewColumn.Name, visibility)
}
return buf.String()
}
// writeSetNotNull restores a rebuilt column's NOT NULL, under its name when it
// had one.
func writeSetNotNull(out *strings.Builder, schemaName, tableName string, column *metadatapb.ColumnMetadata, name string) {
switch {
case column.Nullable:
case name != "":
writeModifyColumn(out, schemaName, tableName, column.Name, `CONSTRAINT "`+name+`" NOT NULL`)
default:
writeAlterColumnSetNotNull(out, schemaName, tableName, column.Name)
}
}
// notNullConstraintName returns the name of the column's named NOT NULL
// constraint, which the sync lists as a check on "<column>" IS NOT NULL.
func notNullConstraintName(table *metadatapb.TableMetadata, column string) string {
want := `"` + column + `" IS NOT NULL`
for _, check := range table.GetCheckConstraints() {
if strings.Join(strings.Fields(check.Expression), " ") == want {
return check.Name
}
}
return ""
}
// columnDependents are a table's indexes and constraints on some of its
// columns. notNulls are named NOT NULL constraints: they are dropped with the
// rest, but restored by the column's own MODIFY, since a CHECK does not make
// a column NOT NULL.
type columnDependents struct {
indexes []*metadatapb.IndexMetadata
checks []*metadatapb.CheckConstraintMetadata
notNulls []*metadatapb.CheckConstraintMetadata
foreignKeys []*metadatapb.ForeignKeyMetadata
}
// findColumnDependents lists the table's indexes and constraints on any of the
// columns, each once, less those named in skip.
func findColumnDependents(table *metadatapb.TableMetadata, columns []string, skip map[string]bool) columnDependents {
var dependents columnDependents
if len(columns) == 0 {
return dependents
}
references := func(expression string) bool {
return slices.ContainsFunc(columns, func(column string) bool { return expressionReferencesColumn(expression, column) })
}
// A primary or unique key lists column names as Oracle stores them, not
// SQL, so they compare as they are.
isColumn := func(name string) bool { return slices.Contains(columns, strings.Trim(name, `"`)) }
for _, index := range table.GetIndexes() {
matches := references
if index.IsConstraint {
matches = isColumn
}
if !skip[index.Name] && slices.ContainsFunc(index.Expressions, matches) {
dependents.indexes = append(dependents.indexes, index)
}
}
for _, check := range table.GetCheckConstraints() {
switch {
case skip[check.Name] || !references(check.Expression):
case slices.ContainsFunc(columns, func(column string) bool { return notNullConstraintName(table, column) == check.Name }):
dependents.notNulls = append(dependents.notNulls, check)
default:
dependents.checks = append(dependents.checks, check)
}
}
for _, fk := range table.GetForeignKeys() {
onColumn := slices.ContainsFunc(fk.Columns, func(column string) bool { return slices.Contains(columns, column) })
// A foreign key of the table on its own rebuilt key goes with the key.
if !skip[fk.Name] && (onColumn || referencesColumns(fk, "", "", table.Name, columns)) {
dependents.foreignKeys = append(dependents.foreignKeys, fk)
}
}
return dependents
}
// diffObjectNames returns the names of the indexes and constraints the diff
// drops, or creates, on the table itself.
func diffObjectNames(tableDiff *schema.TableDiff, action schema.MetadataDiffAction) map[string]bool {
names := make(map[string]bool)
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == action {
names[cmp.Or(indexDiff.NewIndex, indexDiff.OldIndex).GetName()] = true
}
}
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == action {
names[cmp.Or(checkDiff.NewCheckConstraint, checkDiff.OldCheckConstraint).GetName()] = true
}
}
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == action {
names[cmp.Or(fkDiff.NewForeignKey, fkDiff.OldForeignKey).GetName()] = true
}
}
return names
}
func (d columnDependents) writeDrops(out *strings.Builder, schemaName, tableName string) {
for _, fk := range d.foreignKeys {
writeDropForeignKey(out, schemaName, tableName, fk.Name)
}
for _, check := range slices.Concat(d.checks, d.notNulls) {
writeDropCheckConstraint(out, schemaName, tableName, check.Name)
}
for _, index := range d.indexes {
if index.IsConstraint {
writeDropConstraint(out, schemaName, tableName, index.Name)
} else {
writeDropIndex(out, schemaName, index.Name)
}
}
}
func (d columnDependents) writeCreates(out *strings.Builder, schemaName, tableName string) {
for _, index := range d.indexes {
switch {
case index.Primary:
writeMigrationPrimaryKey(out, schemaName, tableName, index)
case index.IsConstraint:
writeMigrationUniqueKey(out, schemaName, tableName, index)
default:
writeMigrationIndex(out, schemaName, tableName, index)
}
}
for _, check := range d.checks {
writeAddCheckConstraint(out, schemaName, tableName, check)
}
for _, fk := range d.foreignKeys {
writeMigrationForeignKey(out, schemaName, tableName, fk)
}
}
// expressionReferencesColumn reports whether an index or check expression
// names the column, quoted or as an unquoted identifier. String literals are
// skipped.
func expressionReferencesColumn(expression, column string) bool {
isIdentifierChar := func(c byte) bool {
return c == '_' || c == '$' || c == '#' || ('0' <= c && c <= '9') || ('A' <= c && c <= 'Z') || ('a' <= c && c <= 'z')
}
for i := 0; i < len(expression); {
switch c := expression[i]; {
case c == '\'' || c == '"':
end := strings.IndexByte(expression[i+1:], c)
if end < 0 {
return false
}
if c == '"' && expression[i+1:i+1+end] == column {
return true
}
i += end + 2
case isIdentifierChar(c):
j := i + 1
for j < len(expression) && isIdentifierChar(expression[j]) {
j++
}
// A run that starts with a digit is a number, such as 1E5.
if (c < '0' || c > '9') && strings.ToUpper(expression[i:j]) == column {
return true
}
i = j
default:
i++
}
}
return false
}
// hasDefaultValue checks if a column has any default value
func hasDefaultValue(column *metadatapb.ColumnMetadata) bool {
if column == nil {
return false
}
return column.GetDefault() != ""
}
// defaultValuesEqual checks if two columns have the same default value
func defaultValuesEqual(col1, col2 *metadatapb.ColumnMetadata) bool {
if col1 == nil || col2 == nil {
return col1 == col2
}
// Check default value
def1 := col1.GetDefault()
def2 := col2.GetDefault()
return def1 == def2
}
// getDefaultExpression returns the SQL expression for a column's default value
func getDefaultExpression(column *metadatapb.ColumnMetadata) string {
if column == nil {
return ""
}
if column.Default != "" {
return column.Default
}
return ""
}
// Write functions for various DDL statements
func writeDropForeignKey(out *strings.Builder, schema, table, constraint string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" DROP CONSTRAINT "`)
_, _ = out.WriteString(constraint)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropFunction(out *strings.Builder, schema, function string) {
_, _ = out.WriteString(`DROP FUNCTION `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(function)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropProcedure(out *strings.Builder, schema, procedure string) {
_, _ = out.WriteString(`DROP PROCEDURE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(procedure)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropView(out *strings.Builder, schema, view string) {
_, _ = out.WriteString(`DROP VIEW `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(view)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropMaterializedView(out *strings.Builder, schema, view string) {
_, _ = out.WriteString(`DROP MATERIALIZED VIEW `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(view)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropCheckConstraint(out *strings.Builder, schema, table, constraint string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" DROP CONSTRAINT "`)
_, _ = out.WriteString(constraint)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropConstraint(out *strings.Builder, schema, table, constraint string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" DROP CONSTRAINT "`)
_, _ = out.WriteString(constraint)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropIndex(out *strings.Builder, schema, index string) {
_, _ = out.WriteString(`DROP INDEX `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(index)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropColumn(out *strings.Builder, schema, table, column string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" DROP COLUMN "`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropTable(out *strings.Builder, schema, table string) {
_, _ = out.WriteString(`DROP TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropSequence(out *strings.Builder, schema, sequence string) {
_, _ = out.WriteString(`DROP SEQUENCE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(sequence)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeDropSchema(out *strings.Builder, schema string) {
_, _ = out.WriteString(`DROP USER "`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`" CASCADE;`)
_, _ = out.WriteString("\n")
}
func writeCreateSchema(out *strings.Builder, schema string) {
_, _ = out.WriteString(`CREATE USER "`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}
func writeAddColumn(out *strings.Builder, schema, table string, column *metadatapb.ColumnMetadata) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" ADD "`)
_, _ = out.WriteString(column.Name)
_, _ = out.WriteString(`" `)
_, _ = out.WriteString(column.Type)
writeColumnAttributes(out, column)
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeColumnAttributes writes what follows a column's type in CREATE TABLE
// and ADD: visibility, then the virtual expression or the default, then NOT NULL.
func writeColumnAttributes(out *strings.Builder, column *metadatapb.ColumnMetadata) {
if column.IsInvisible {
_, _ = out.WriteString(` INVISIBLE`)
}
if column.Generation != nil {
_, _ = out.WriteString(virtualColumnClause(column.Generation))
} else if defaultExpr := getDefaultExpression(column); defaultExpr != "" {
_, _ = out.WriteString(` DEFAULT `)
_, _ = out.WriteString(defaultExpr)
}
if !column.Nullable {
_, _ = out.WriteString(` NOT NULL`)
}
}
// writeModifyColumn writes ALTER TABLE ... MODIFY ("column" clause).
func writeModifyColumn(out *strings.Builder, schema, table, column, clause string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" MODIFY ("`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`" `)
_, _ = out.WriteString(clause)
_, _ = out.WriteString(`);`)
_, _ = out.WriteString("\n")
}
func writeAlterColumnType(out *strings.Builder, schema, table, column, newType string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" MODIFY "`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`" `)
_, _ = out.WriteString(newType)
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
func writeAlterColumnSetNotNull(out *strings.Builder, schema, table, column string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" MODIFY "`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`" NOT NULL;`)
_, _ = out.WriteString("\n")
}
func writeAlterColumnDropNotNull(out *strings.Builder, schema, table, column string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" MODIFY "`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`" NULL;`)
_, _ = out.WriteString("\n")
}
func writeAlterColumnSetDefault(out *strings.Builder, schema, table, column, defaultExpr string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" MODIFY "`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`" DEFAULT `)
_, _ = out.WriteString(defaultExpr)
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
func writeAlterColumnDropDefault(out *strings.Builder, schema, table, column string) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" MODIFY "`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`" DEFAULT NULL;`)
_, _ = out.WriteString("\n")
}
func writeAddCheckConstraint(out *strings.Builder, schema, table string, check *metadatapb.CheckConstraintMetadata) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" ADD CONSTRAINT "`)
_, _ = out.WriteString(check.Name)
_, _ = out.WriteString(`" CHECK (`)
_, _ = out.WriteString(check.Expression)
_, _ = out.WriteString(`);`)
_, _ = out.WriteString("\n")
}
func writeFunctionDiff(out *strings.Builder, funcDiff *schema.FunctionDiff) {
switch funcDiff.Action {
case schema.MetadataDiffActionCreate:
definition := funcDiff.NewFunction.Definition
// If the definition doesn't start with CREATE, add the CREATE OR REPLACE prefix
if !strings.HasPrefix(strings.ToUpper(strings.TrimSpace(definition)), "CREATE") {
_, _ = out.WriteString("CREATE OR REPLACE ")
}
_, _ = out.WriteString(definition)
if !strings.HasSuffix(strings.TrimSpace(definition), ";") {
_, _ = out.WriteString(";")
}
_, _ = out.WriteString("\n")
// Add comment if present
if funcDiff.NewFunction.Comment != "" {
writeFunctionComment(out, funcDiff.SchemaName, funcDiff.FunctionName, funcDiff.NewFunction.Comment)
}
_, _ = out.WriteString("\n")
case schema.MetadataDiffActionAlter:
// Oracle requires CREATE OR REPLACE for functions
definition := funcDiff.NewFunction.Definition
// If the definition doesn't start with CREATE, add the CREATE OR REPLACE prefix
if !strings.HasPrefix(strings.ToUpper(strings.TrimSpace(definition)), "CREATE") {
_, _ = out.WriteString("CREATE OR REPLACE ")
}
_, _ = out.WriteString(definition)
if !strings.HasSuffix(strings.TrimSpace(definition), ";") {
_, _ = out.WriteString(";")
}
_, _ = out.WriteString("\n")
// Add comment if present
if funcDiff.NewFunction.Comment != "" {
writeFunctionComment(out, funcDiff.SchemaName, funcDiff.FunctionName, funcDiff.NewFunction.Comment)
}
_, _ = out.WriteString("\n")
default:
// Handle other actions if needed
}
}
// Helper functions to check if diff contains certain types of changes
func hasCreateOrAlterObjects(diff *schema.MetadataDiff) bool {
return hasCreateOrAlterTables(diff) || hasCreateViewsOrFunctions(diff)
}
func hasCreateOrAlterTables(diff *schema.MetadataDiff) bool {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate || tableDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
for _, seqDiff := range diff.SequenceChanges {
if seqDiff.Action == schema.MetadataDiffActionCreate {
// Skip system-generated sequences
if !isSystemGeneratedSequence(seqDiff.SequenceName) {
return true
}
}
}
return false
}
func hasCreateViewsOrFunctions(diff *schema.MetadataDiff) bool {
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
for _, mvDiff := range diff.MaterializedViewChanges {
if mvDiff.Action == schema.MetadataDiffActionCreate || mvDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionCreate || funcDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
for _, procDiff := range diff.ProcedureChanges {
if procDiff.Action == schema.MetadataDiffActionCreate || procDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
return false
}
// getObjectID generates a unique identifier for database objects
func getMigrationObjectID(schema, name string) string {
return fmt.Sprintf("%s.%s", schema, name)
}
// isSystemGeneratedSequence checks if a sequence is system-generated and should not be included in migration DDL
func isSystemGeneratedSequence(sequenceName string) bool {
// Oracle automatically creates sequences for IDENTITY columns with names like ISEQ$$_*
return strings.HasPrefix(sequenceName, "ISEQ$$_")
}
// writeCreateSequence writes a CREATE SEQUENCE statement
func writeMigrationCreateSequence(out *strings.Builder, schema string, seq *metadatapb.SequenceMetadata) {
_, _ = out.WriteString(`CREATE SEQUENCE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(seq.Name)
_, _ = out.WriteString(`"`)
if seq.Start != "" {
_, _ = out.WriteString(` START WITH `)
_, _ = out.WriteString(seq.Start)
}
if seq.Increment != "" {
_, _ = out.WriteString(` INCREMENT BY `)
_, _ = out.WriteString(seq.Increment)
}
if seq.MinValue != "" {
_, _ = out.WriteString(` MINVALUE `)
_, _ = out.WriteString(seq.MinValue)
}
if seq.MaxValue != "" {
_, _ = out.WriteString(` MAXVALUE `)
_, _ = out.WriteString(seq.MaxValue)
}
if seq.CacheSize != "" {
_, _ = out.WriteString(` CACHE `)
_, _ = out.WriteString(seq.CacheSize)
}
if seq.Cycle {
_, _ = out.WriteString(` CYCLE`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
// Add comment if present
if seq.Comment != "" {
writeSequenceComment(out, schema, seq.Name, seq.Comment)
}
}
func writeProcedureDiff(out *strings.Builder, procDiff *schema.ProcedureDiff) {
switch procDiff.Action {
case schema.MetadataDiffActionCreate:
definition := procDiff.NewProcedure.Definition
// If the definition doesn't start with CREATE, add the CREATE OR REPLACE prefix
if !strings.HasPrefix(strings.ToUpper(strings.TrimSpace(definition)), "CREATE") {
_, _ = out.WriteString("CREATE OR REPLACE ")
}
_, _ = out.WriteString(definition)
if !strings.HasSuffix(strings.TrimSpace(definition), ";") {
_, _ = out.WriteString(";")
}
_, _ = out.WriteString("\n")
// Note: ProcedureMetadata doesn't have a comment field in the protobuf,
// so we don't add procedure comments here
_, _ = out.WriteString("\n")
case schema.MetadataDiffActionAlter:
// Oracle requires CREATE OR REPLACE for procedures
definition := procDiff.NewProcedure.Definition
// If the definition doesn't start with CREATE, add the CREATE OR REPLACE prefix
if !strings.HasPrefix(strings.ToUpper(strings.TrimSpace(definition)), "CREATE") {
_, _ = out.WriteString("CREATE OR REPLACE ")
}
_, _ = out.WriteString(definition)
if !strings.HasSuffix(strings.TrimSpace(definition), ";") {
_, _ = out.WriteString(";")
}
_, _ = out.WriteString("\n")
// Note: ProcedureMetadata doesn't have a comment field in the protobuf,
// so we don't add procedure comments here
_, _ = out.WriteString("\n")
default:
// Handle other actions if needed
}
}
// writeView writes a CREATE VIEW statement
func writeMigrationView(out *strings.Builder, schema string, view *metadatapb.ViewMetadata) {
_, _ = out.WriteString(`CREATE VIEW `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(view.Name)
_, _ = out.WriteString(`" AS `)
_, _ = out.WriteString(view.Definition)
if !strings.HasSuffix(strings.TrimSpace(view.Definition), ";") {
_, _ = out.WriteString(`;`)
}
_, _ = out.WriteString("\n")
// Add comment if present
if view.Comment != "" {
writeViewComment(out, schema, view.Name, view.Comment)
}
}
// writeMaterializedView writes a CREATE MATERIALIZED VIEW statement
func writeMigrationMaterializedView(out *strings.Builder, schema string, view *metadatapb.MaterializedViewMetadata) {
_, _ = out.WriteString(`CREATE MATERIALIZED VIEW `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(view.Name)
_, _ = out.WriteString(`" AS `)
_, _ = out.WriteString(view.Definition)
if !strings.HasSuffix(strings.TrimSpace(view.Definition), ";") {
_, _ = out.WriteString(`;`)
}
_, _ = out.WriteString("\n")
// Add comment if present
if view.Comment != "" {
writeMaterializedViewComment(out, schema, view.Name, view.Comment)
}
}
// writeForeignKey writes an ALTER TABLE ADD CONSTRAINT statement for a foreign key
func writeMigrationForeignKey(out *strings.Builder, schema, table string, fk *metadatapb.ForeignKeyMetadata) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" ADD CONSTRAINT "`)
_, _ = out.WriteString(fk.Name)
_, _ = out.WriteString(`" FOREIGN KEY (`)
for i, col := range fk.Columns {
if i > 0 {
_, _ = out.WriteString(`, `)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(col)
_, _ = out.WriteString(`"`)
}
_, _ = out.WriteString(`) REFERENCES `)
if fk.ReferencedSchema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(fk.ReferencedSchema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(fk.ReferencedTable)
_, _ = out.WriteString(`" (`)
for i, col := range fk.ReferencedColumns {
if i > 0 {
_, _ = out.WriteString(`, `)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(col)
_, _ = out.WriteString(`"`)
}
_, _ = out.WriteString(`)`)
if fk.OnUpdate != "" {
_, _ = out.WriteString(` ON UPDATE `)
_, _ = out.WriteString(fk.OnUpdate)
}
if fk.OnDelete != "" {
_, _ = out.WriteString(` ON DELETE `)
_, _ = out.WriteString(fk.OnDelete)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeCreateTable writes a CREATE TABLE statement
func writeMigrationCreateTable(out *strings.Builder, schema, table string, columns []*metadatapb.ColumnMetadata, checks []*metadatapb.CheckConstraintMetadata) {
_, _ = out.WriteString(`CREATE TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" (`)
_, _ = out.WriteString("\n")
// Write columns
for i, col := range columns {
if i > 0 {
_, _ = out.WriteString(",\n")
}
_, _ = out.WriteString(" \"")
_, _ = out.WriteString(col.Name)
_, _ = out.WriteString("\" ")
_, _ = out.WriteString(col.Type)
writeColumnAttributes(out, col)
}
// Write check constraints
for _, check := range checks {
_, _ = out.WriteString(",\n")
_, _ = out.WriteString(" CONSTRAINT \"")
_, _ = out.WriteString(check.Name)
_, _ = out.WriteString("\" CHECK (")
_, _ = out.WriteString(check.Expression)
_, _ = out.WriteString(")")
}
_, _ = out.WriteString("\n)")
}
// writePrimaryKey writes an ALTER TABLE ADD PRIMARY KEY statement
func writeMigrationPrimaryKey(out *strings.Builder, schema, table string, index *metadatapb.IndexMetadata) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" ADD CONSTRAINT "`)
_, _ = out.WriteString(index.Name)
_, _ = out.WriteString(`" PRIMARY KEY (`)
for i, expr := range index.Expressions {
if i > 0 {
_, _ = out.WriteString(`, `)
}
_, _ = out.WriteString(expr)
}
_, _ = out.WriteString(`);`)
_, _ = out.WriteString("\n")
}
// writeUniqueKey writes an ALTER TABLE ADD UNIQUE statement
func writeMigrationUniqueKey(out *strings.Builder, schema, table string, index *metadatapb.IndexMetadata) {
_, _ = out.WriteString(`ALTER TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" ADD CONSTRAINT "`)
_, _ = out.WriteString(index.Name)
_, _ = out.WriteString(`" UNIQUE (`)
for i, expr := range index.Expressions {
if i > 0 {
_, _ = out.WriteString(`, `)
}
_, _ = out.WriteString(expr)
}
_, _ = out.WriteString(`);`)
_, _ = out.WriteString("\n")
}
// writeIndex writes a CREATE INDEX statement
func writeMigrationIndex(out *strings.Builder, schema, table string, index *metadatapb.IndexMetadata) {
_, _ = out.WriteString(`CREATE `)
if index.Unique {
_, _ = out.WriteString(`UNIQUE `)
}
_, _ = out.WriteString(`INDEX "`)
_, _ = out.WriteString(index.Name)
_, _ = out.WriteString(`" ON `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" (`)
// Handle FUNCTION-BASED indexes differently from normal indexes
if strings.Contains(index.Type, "FUNCTION-BASED") {
for i, expression := range index.Expressions {
if i > 0 {
_, _ = out.WriteString(`, `)
}
_, _ = out.WriteString(expression)
// For function-based indexes, always add explicit ASC/DESC
if i < len(index.Descending) && index.Descending[i] {
_, _ = out.WriteString(` DESC`)
} else {
_, _ = out.WriteString(` ASC`)
}
}
} else {
for i, column := range index.Expressions {
if i > 0 {
_, _ = out.WriteString(`, `)
}
// Remove quotes if they already exist to avoid double quoting
cleanColumn := column
if strings.HasPrefix(column, `"`) && strings.HasSuffix(column, `"`) {
cleanColumn = column[1 : len(column)-1]
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(cleanColumn)
_, _ = out.WriteString(`"`)
// For normal indexes, add explicit ASC/DESC
if i < len(index.Descending) && index.Descending[i] {
_, _ = out.WriteString(` DESC`)
} else {
_, _ = out.WriteString(` ASC`)
}
}
}
_, _ = out.WriteString(`)`)
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
// Add comment if present
if index.Comment != "" {
writeIndexComment(out, schema, index.Name, index.Comment)
}
}
// Comment writing helper functions
// writeViewComment writes a COMMENT ON VIEW statement
func writeViewComment(out *strings.Builder, schema, viewName, comment string) {
_, _ = out.WriteString(`COMMENT ON VIEW `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(viewName)
_, _ = out.WriteString(`" IS `)
if comment == "" {
_, _ = out.WriteString(`''`)
} else {
_, _ = out.WriteString(`'`)
// Escape single quotes in the comment
escapedComment := strings.ReplaceAll(comment, "'", "''")
_, _ = out.WriteString(escapedComment)
_, _ = out.WriteString(`'`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeMaterializedViewComment writes a COMMENT ON MATERIALIZED VIEW statement
func writeMaterializedViewComment(out *strings.Builder, schema, viewName, comment string) {
_, _ = out.WriteString(`COMMENT ON MATERIALIZED VIEW `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(viewName)
_, _ = out.WriteString(`" IS `)
if comment == "" {
_, _ = out.WriteString(`''`)
} else {
_, _ = out.WriteString(`'`)
// Escape single quotes in the comment
escapedComment := strings.ReplaceAll(comment, "'", "''")
_, _ = out.WriteString(escapedComment)
_, _ = out.WriteString(`'`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeFunctionComment writes a COMMENT ON FUNCTION statement
func writeFunctionComment(out *strings.Builder, schema, functionName, comment string) {
_, _ = out.WriteString(`COMMENT ON FUNCTION `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(functionName)
_, _ = out.WriteString(`" IS `)
if comment == "" {
_, _ = out.WriteString(`''`)
} else {
_, _ = out.WriteString(`'`)
// Escape single quotes in the comment
escapedComment := strings.ReplaceAll(comment, "'", "''")
_, _ = out.WriteString(escapedComment)
_, _ = out.WriteString(`'`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeSequenceComment writes a COMMENT ON SEQUENCE statement
func writeSequenceComment(out *strings.Builder, schema, sequenceName, comment string) {
_, _ = out.WriteString(`COMMENT ON SEQUENCE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(sequenceName)
_, _ = out.WriteString(`" IS `)
if comment == "" {
_, _ = out.WriteString(`''`)
} else {
_, _ = out.WriteString(`'`)
// Escape single quotes in the comment
escapedComment := strings.ReplaceAll(comment, "'", "''")
_, _ = out.WriteString(escapedComment)
_, _ = out.WriteString(`'`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeIndexComment writes a COMMENT ON INDEX statement
func writeIndexComment(out *strings.Builder, schema, indexName, comment string) {
_, _ = out.WriteString(`COMMENT ON INDEX `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(indexName)
_, _ = out.WriteString(`" IS `)
if comment == "" {
_, _ = out.WriteString(`''`)
} else {
_, _ = out.WriteString(`'`)
// Escape single quotes in the comment
escapedComment := strings.ReplaceAll(comment, "'", "''")
_, _ = out.WriteString(escapedComment)
_, _ = out.WriteString(`'`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// Comment generation functions for Oracle migration DDL
// generateTableCommentChanges generates COMMENT ON TABLE statements for table comment changes
func generateTableCommentChanges(buf *strings.Builder, tableDiff *schema.TableDiff) {
if tableDiff.OldTable == nil || tableDiff.NewTable == nil {
return
}
oldComment := tableDiff.OldTable.Comment
newComment := tableDiff.NewTable.Comment
// If comments are different, generate COMMENT ON TABLE statement
if oldComment != newComment {
writeTableComment(buf, tableDiff.SchemaName, tableDiff.TableName, newComment)
}
}
// generateColumnCommentChanges generates COMMENT ON COLUMN statements for column comment changes
func generateColumnCommentChanges(buf *strings.Builder, tableDiff *schema.TableDiff) {
if tableDiff.OldTable == nil || tableDiff.NewTable == nil {
return
}
// Build maps for efficient lookup
oldColumnMap := make(map[string]*metadatapb.ColumnMetadata)
for _, col := range tableDiff.OldTable.Columns {
oldColumnMap[col.Name] = col
}
newColumnMap := make(map[string]*metadatapb.ColumnMetadata)
for _, col := range tableDiff.NewTable.Columns {
newColumnMap[col.Name] = col
}
// Check for columns that exist in both old and new (column modifications/comment changes)
for _, newCol := range tableDiff.NewTable.Columns {
if oldCol, exists := oldColumnMap[newCol.Name]; exists {
// Column exists in both, check if comment changed
if oldCol.Comment != newCol.Comment {
writeColumnComment(buf, tableDiff.SchemaName, tableDiff.TableName, newCol.Name, newCol.Comment)
}
} else {
// New column, add comment if it has one
if newCol.Comment != "" {
writeColumnComment(buf, tableDiff.SchemaName, tableDiff.TableName, newCol.Name, newCol.Comment)
}
}
}
// Handle explicit column changes in the diff
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionCreate && colDiff.NewColumn != nil && colDiff.NewColumn.Comment != "" {
writeColumnComment(buf, tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn.Name, colDiff.NewColumn.Comment)
} else if colDiff.Action == schema.MetadataDiffActionAlter && colDiff.OldColumn != nil && colDiff.NewColumn != nil {
if colDiff.OldColumn.Comment != colDiff.NewColumn.Comment {
writeColumnComment(buf, tableDiff.SchemaName, tableDiff.TableName, colDiff.NewColumn.Name, colDiff.NewColumn.Comment)
}
}
}
}
// generateIndexCommentChanges generates COMMENT ON INDEX statements for index comment changes within table diffs
func generateIndexCommentChanges(buf *strings.Builder, tableDiff *schema.TableDiff) {
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionAlter {
if indexDiff.OldIndex == nil || indexDiff.NewIndex == nil {
continue
}
oldComment := indexDiff.OldIndex.Comment
newComment := indexDiff.NewIndex.Comment
// If comments are different, generate COMMENT ON INDEX statement
if oldComment != newComment {
writeIndexComment(buf, tableDiff.SchemaName, indexDiff.NewIndex.Name, newComment)
}
} else if indexDiff.Action == schema.MetadataDiffActionCreate && indexDiff.NewIndex != nil && indexDiff.NewIndex.Comment != "" {
// New index with comment
writeIndexComment(buf, tableDiff.SchemaName, indexDiff.NewIndex.Name, indexDiff.NewIndex.Comment)
}
}
}
// generateViewCommentChanges generates COMMENT ON VIEW statements for view comment changes
func generateViewCommentChanges(buf *strings.Builder, diff *schema.MetadataDiff) {
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionAlter {
if viewDiff.OldView == nil || viewDiff.NewView == nil {
continue
}
oldComment := viewDiff.OldView.Comment
newComment := viewDiff.NewView.Comment
// If comments are different, generate COMMENT ON VIEW statement
if oldComment != newComment {
writeViewComment(buf, viewDiff.SchemaName, viewDiff.ViewName, newComment)
}
} else if viewDiff.Action == schema.MetadataDiffActionCreate && viewDiff.NewView != nil && viewDiff.NewView.Comment != "" {
// New view with comment
writeViewComment(buf, viewDiff.SchemaName, viewDiff.ViewName, viewDiff.NewView.Comment)
}
}
}
// generateMaterializedViewCommentChanges generates COMMENT ON MATERIALIZED VIEW statements
func generateMaterializedViewCommentChanges(buf *strings.Builder, diff *schema.MetadataDiff) {
for _, mvDiff := range diff.MaterializedViewChanges {
if mvDiff.Action == schema.MetadataDiffActionAlter {
if mvDiff.OldMaterializedView == nil || mvDiff.NewMaterializedView == nil {
continue
}
oldComment := mvDiff.OldMaterializedView.Comment
newComment := mvDiff.NewMaterializedView.Comment
// If comments are different, generate COMMENT ON MATERIALIZED VIEW statement
if oldComment != newComment {
writeMaterializedViewComment(buf, mvDiff.SchemaName, mvDiff.MaterializedViewName, newComment)
}
} else if mvDiff.Action == schema.MetadataDiffActionCreate && mvDiff.NewMaterializedView != nil && mvDiff.NewMaterializedView.Comment != "" {
// New materialized view with comment
writeMaterializedViewComment(buf, mvDiff.SchemaName, mvDiff.MaterializedViewName, mvDiff.NewMaterializedView.Comment)
}
}
}
// generateFunctionCommentChanges generates COMMENT ON FUNCTION statements for function comment changes
func generateFunctionCommentChanges(buf *strings.Builder, diff *schema.MetadataDiff) {
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionAlter {
if funcDiff.OldFunction == nil || funcDiff.NewFunction == nil {
continue
}
oldComment := funcDiff.OldFunction.Comment
newComment := funcDiff.NewFunction.Comment
// If comments are different, generate COMMENT ON FUNCTION statement
if oldComment != newComment {
writeFunctionComment(buf, funcDiff.SchemaName, funcDiff.FunctionName, newComment)
}
} else if funcDiff.Action == schema.MetadataDiffActionCreate && funcDiff.NewFunction != nil && funcDiff.NewFunction.Comment != "" {
// New function with comment
writeFunctionComment(buf, funcDiff.SchemaName, funcDiff.FunctionName, funcDiff.NewFunction.Comment)
}
}
}
// generateSequenceCommentChanges generates COMMENT ON SEQUENCE statements for sequence comment changes
func generateSequenceCommentChanges(buf *strings.Builder, diff *schema.MetadataDiff) {
for _, seqDiff := range diff.SequenceChanges {
if seqDiff.Action == schema.MetadataDiffActionAlter {
if seqDiff.OldSequence == nil || seqDiff.NewSequence == nil {
continue
}
oldComment := seqDiff.OldSequence.Comment
newComment := seqDiff.NewSequence.Comment
// If comments are different, generate COMMENT ON SEQUENCE statement
if oldComment != newComment {
writeSequenceComment(buf, seqDiff.SchemaName, seqDiff.SequenceName, newComment)
}
} else if seqDiff.Action == schema.MetadataDiffActionCreate && seqDiff.NewSequence != nil && seqDiff.NewSequence.Comment != "" {
// New sequence with comment
writeSequenceComment(buf, seqDiff.SchemaName, seqDiff.SequenceName, seqDiff.NewSequence.Comment)
}
}
}
// Helper functions to write comment statements for different object types
// writeTableComment writes a COMMENT ON TABLE statement
func writeTableComment(out *strings.Builder, schema, table, comment string) {
_, _ = out.WriteString(`COMMENT ON TABLE `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`" IS `)
if comment == "" {
_, _ = out.WriteString(`''`)
} else {
_, _ = out.WriteString(`'`)
// Escape single quotes in the comment
escapedComment := strings.ReplaceAll(comment, "'", "''")
_, _ = out.WriteString(escapedComment)
_, _ = out.WriteString(`'`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeColumnComment writes a COMMENT ON COLUMN statement
func writeColumnComment(out *strings.Builder, schema, table, column, comment string) {
_, _ = out.WriteString(`COMMENT ON COLUMN `)
if schema != "" {
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(schema)
_, _ = out.WriteString(`".`)
}
_, _ = out.WriteString(`"`)
_, _ = out.WriteString(table)
_, _ = out.WriteString(`"."`)
_, _ = out.WriteString(column)
_, _ = out.WriteString(`" IS `)
if comment == "" {
_, _ = out.WriteString(`''`)
} else {
_, _ = out.WriteString(`'`)
// Escape single quotes in the comment
escapedComment := strings.ReplaceAll(comment, "'", "''")
_, _ = out.WriteString(escapedComment)
_, _ = out.WriteString(`'`)
}
_, _ = out.WriteString(`;`)
_, _ = out.WriteString("\n")
}
// writeMigrationTrigger writes a CREATE TRIGGER statement for migration
func writeMigrationTrigger(out *strings.Builder, trigger *metadatapb.TriggerMetadata) {
if trigger == nil {
return
}
_, _ = out.WriteString(trigger.Body)
if !strings.HasSuffix(strings.TrimSpace(trigger.Body), ";") {
_, _ = out.WriteString(";")
}
_, _ = out.WriteString("\n")
}
// writeDropTrigger writes a DROP TRIGGER statement
func writeDropTrigger(out *strings.Builder, triggerName string) {
_, _ = out.WriteString(`DROP TRIGGER "`)
_, _ = out.WriteString(triggerName)
_, _ = out.WriteString(`";`)
_, _ = out.WriteString("\n")
}