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 schema
import (
"slices"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/bytebase/bytebase/backend/common"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/store/model"
)
// MetadataDiffAction represents the type of change action.
type MetadataDiffAction string
const (
MetadataDiffActionCreate MetadataDiffAction = "CREATE"
MetadataDiffActionDrop MetadataDiffAction = "DROP"
MetadataDiffActionAlter MetadataDiffAction = "ALTER"
)
// MetadataDiff represents the differences between two database schemas.
type MetadataDiff struct {
// Database level changes
DatabaseName string
// OldDatabase and NewDatabase are the metadata the diff was taken between.
// A generator reads them for objects the diff leaves alone, such as another
// table's foreign key on a key it must rebuild.
OldDatabase *model.DatabaseMetadata
NewDatabase *model.DatabaseMetadata
// Schema changes
SchemaChanges []*SchemaDiff
// Table changes
TableChanges []*TableDiff
// View changes
ViewChanges []*ViewDiff
// MaterializedView changes
MaterializedViewChanges []*MaterializedViewDiff
// Function changes
FunctionChanges []*FunctionDiff
// Procedure changes
ProcedureChanges []*ProcedureDiff
// Sequence changes
SequenceChanges []*SequenceDiff
// Enum type changes
EnumTypeChanges []*EnumTypeDiff
// Composite type changes
CompositeTypeChanges []*CompositeTypeDiff
// Extension changes
ExtensionChanges []*ExtensionDiff
// Event trigger changes
EventTriggerChanges []*EventTriggerDiff
// Event changes
EventChanges []*EventDiff
// Comment changes
CommentChanges []*CommentDiff
}
// nolint
// SchemaDiff represents changes to a schema.
type SchemaDiff struct {
Action MetadataDiffAction
SchemaName string
OldSchema *metadatapb.SchemaMetadata
NewSchema *metadatapb.SchemaMetadata
}
// TableDiff represents changes to a table.
type TableDiff struct {
Action MetadataDiffAction
SchemaName string
TableName string
OldTable *metadatapb.TableMetadata
NewTable *metadatapb.TableMetadata
// Column changes
ColumnChanges []*ColumnDiff
// Index changes (excluding PK and UK which are handled separately)
IndexChanges []*IndexDiff
// Primary key changes
PrimaryKeyChanges []*PrimaryKeyDiff
// Unique constraint changes
UniqueConstraintChanges []*UniqueConstraintDiff
// Foreign key changes
ForeignKeyChanges []*ForeignKeyDiff
// Check constraint changes
CheckConstraintChanges []*CheckConstraintDiff
// EXCLUDE constraint changes (PostgreSQL specific)
ExcludeConstraintChanges []*ExcludeConstraintDiff
// Partition changes
PartitionChanges []*PartitionDiff
// Trigger changes
TriggerChanges []*TriggerDiff
}
// ColumnDiff represents changes to a column.
type ColumnDiff struct {
Action MetadataDiffAction
OldColumn *metadatapb.ColumnMetadata
NewColumn *metadatapb.ColumnMetadata
}
// IndexDiff represents changes to an index.
type IndexDiff struct {
Action MetadataDiffAction
OldIndex *metadatapb.IndexMetadata
NewIndex *metadatapb.IndexMetadata
}
// ForeignKeyDiff represents changes to a foreign key.
type ForeignKeyDiff struct {
Action MetadataDiffAction
OldForeignKey *metadatapb.ForeignKeyMetadata
NewForeignKey *metadatapb.ForeignKeyMetadata
}
// CheckConstraintDiff represents changes to a check constraint.
type CheckConstraintDiff struct {
Action MetadataDiffAction
OldCheckConstraint *metadatapb.CheckConstraintMetadata
NewCheckConstraint *metadatapb.CheckConstraintMetadata
}
// ExcludeConstraintDiff represents changes to an EXCLUDE constraint (PostgreSQL specific).
type ExcludeConstraintDiff struct {
Action MetadataDiffAction
OldExcludeConstraint *metadatapb.ExcludeConstraintMetadata
NewExcludeConstraint *metadatapb.ExcludeConstraintMetadata
}
// PrimaryKeyDiff represents changes to a primary key constraint.
type PrimaryKeyDiff struct {
Action MetadataDiffAction
OldPrimaryKey *metadatapb.IndexMetadata
NewPrimaryKey *metadatapb.IndexMetadata
}
// UniqueConstraintDiff represents changes to a unique constraint.
type UniqueConstraintDiff struct {
Action MetadataDiffAction
OldUniqueConstraint *metadatapb.IndexMetadata
NewUniqueConstraint *metadatapb.IndexMetadata
}
// TriggerDiff represents changes to a trigger.
type TriggerDiff struct {
Action MetadataDiffAction
SchemaName string // Schema name of the table that owns the trigger
TableName string // Table name that owns the trigger
TriggerName string // Trigger name
OldTrigger *metadatapb.TriggerMetadata
NewTrigger *metadatapb.TriggerMetadata
}
// PartitionDiff represents changes to table partitions.
type PartitionDiff struct {
Action MetadataDiffAction
OldPartition *metadatapb.TablePartitionMetadata
NewPartition *metadatapb.TablePartitionMetadata
}
// ViewDiff represents changes to a view.
type ViewDiff struct {
Action MetadataDiffAction
SchemaName string
ViewName string
OldView *metadatapb.ViewMetadata
NewView *metadatapb.ViewMetadata
}
// MaterializedViewDiff represents changes to a materialized view.
type MaterializedViewDiff struct {
Action MetadataDiffAction
SchemaName string
MaterializedViewName string
OldMaterializedView *metadatapb.MaterializedViewMetadata
NewMaterializedView *metadatapb.MaterializedViewMetadata
IndexChanges []*IndexDiff // Index changes on materialized view
}
// FunctionDiff represents changes to a function.
type FunctionDiff struct {
Action MetadataDiffAction
SchemaName string
FunctionName string
OldFunction *metadatapb.FunctionMetadata
NewFunction *metadatapb.FunctionMetadata
// Detailed change information for advanced engines
SignatureChanged bool
BodyChanged bool
AttributesChanged bool
ChangedAttributes []string
CanUseAlterFunction bool
}
// ProcedureDiff represents changes to a procedure.
type ProcedureDiff struct {
Action MetadataDiffAction
SchemaName string
ProcedureName string
OldProcedure *metadatapb.ProcedureMetadata
NewProcedure *metadatapb.ProcedureMetadata
}
// SequenceDiff represents changes to a sequence.
type SequenceDiff struct {
Action MetadataDiffAction
SchemaName string
SequenceName string
OldSequence *metadatapb.SequenceMetadata
NewSequence *metadatapb.SequenceMetadata
}
// EnumTypeDiff represents changes to an enum type.
type EnumTypeDiff struct {
Action MetadataDiffAction
SchemaName string
EnumTypeName string
OldEnumType *metadatapb.EnumTypeMetadata
NewEnumType *metadatapb.EnumTypeMetadata
}
// CompositeTypeDiff represents changes to a composite type.
type CompositeTypeDiff struct {
Action MetadataDiffAction
SchemaName string
CompositeTypeName string
OldCompositeType *metadatapb.CompositeTypeMetadata
NewCompositeType *metadatapb.CompositeTypeMetadata
}
// ExtensionDiff represents changes to an extension.
type ExtensionDiff struct {
Action MetadataDiffAction
ExtensionName string
OldExtension *metadatapb.ExtensionMetadata
NewExtension *metadatapb.ExtensionMetadata
}
// EventTriggerDiff represents changes to an event trigger.
type EventTriggerDiff struct {
Action MetadataDiffAction
EventTriggerName string
OldEventTrigger *metadatapb.EventTriggerMetadata
NewEventTrigger *metadatapb.EventTriggerMetadata
}
// EventDiff represents changes to an event.
type EventDiff struct {
Action MetadataDiffAction
EventName string
OldEvent *metadatapb.EventMetadata
NewEvent *metadatapb.EventMetadata
}
// CommentObjectType represents the type of database object that has a comment.
type CommentObjectType string
const (
CommentObjectTypeSchema CommentObjectType = "SCHEMA"
CommentObjectTypeTable CommentObjectType = "TABLE"
CommentObjectTypeColumn CommentObjectType = "COLUMN"
CommentObjectTypeView CommentObjectType = "VIEW"
CommentObjectTypeMaterializedView CommentObjectType = "MATERIALIZED VIEW"
CommentObjectTypeFunction CommentObjectType = "FUNCTION"
CommentObjectTypeSequence CommentObjectType = "SEQUENCE"
CommentObjectTypeIndex CommentObjectType = "INDEX"
CommentObjectTypeTrigger CommentObjectType = "TRIGGER"
CommentObjectTypeType CommentObjectType = "TYPE"
CommentObjectTypeExtension CommentObjectType = "EXTENSION"
CommentObjectTypeEventTrigger CommentObjectType = "EVENT TRIGGER"
)
// CommentDiff represents changes to database object comments.
// Comments are tracked independently to avoid triggering object recreation when only comments change.
type CommentDiff struct {
Action MetadataDiffAction // CREATE or ALTER (no DROP since object deletion removes comments automatically)
ObjectType CommentObjectType
SchemaName string
TableName string // used for TRIGGER comments (COMMENT ON TRIGGER trigger_name ON table_name)
ObjectName string // table/view/function/sequence/index name
ColumnName string // only used for COLUMN comments
IndexName string // only used for table-level INDEX comments
OldComment string
NewComment string
}
// GetDatabaseSchemaDiff compares two model.DatabaseMetadata instances and returns the differences.
func GetDatabaseSchemaDiff(engine storepb.Engine, oldSchema, newSchema *model.DatabaseMetadata) (*MetadataDiff, error) {
if oldSchema == nil || newSchema == nil {
return nil, nil
}
oldMetadata := oldSchema.GetProto()
newMetadata := newSchema.GetProto()
if oldMetadata == nil || newMetadata == nil {
return nil, nil
}
diff := &MetadataDiff{
DatabaseName: newMetadata.Name,
OldDatabase: oldSchema,
NewDatabase: newSchema,
}
// Use the internal DatabaseMetadata structures for efficient access
oldMeta := oldSchema
newMeta := newSchema
for _, schemaName := range oldMeta.ListSchemaNames() {
if newMeta.GetSchemaMetadata(schemaName) == nil {
oldSchemaMeta := oldMeta.GetSchemaMetadata(schemaName)
if oldSchemaMeta != nil {
diff.SchemaChanges = append(diff.SchemaChanges, &SchemaDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
OldSchema: oldSchemaMeta.GetProto(),
})
}
}
}
for _, schemaName := range newMeta.ListSchemaNames() {
newSchemaMeta := newMeta.GetSchemaMetadata(schemaName)
if newSchemaMeta == nil {
continue
}
if oldMeta.GetSchemaMetadata(schemaName) == nil {
// New schema
diff.SchemaChanges = append(diff.SchemaChanges, &SchemaDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
NewSchema: newSchemaMeta.GetProto(),
})
// Add all objects in the new schema as created
addNewSchemaObjects(diff, schemaName, newSchemaMeta)
} else {
// Compare schema objects
oldSchemaMeta := oldMeta.GetSchemaMetadata(schemaName)
if oldSchemaMeta != nil {
compareSchemaObjects(engine, diff, schemaName, oldSchemaMeta, newSchemaMeta)
}
}
}
// Compare database-level objects (extensions, event triggers)
compareExtensions(diff, oldMetadata, newMetadata)
compareEventTriggers(diff, oldMetadata, newMetadata)
// Sort all diff lists to ensure stable output order
sortDiffLists(diff)
return diff, nil
}
// addNewSchemaObjects adds all objects from a new schema as created.
func addNewSchemaObjects(diff *MetadataDiff, schemaName string, schema *model.SchemaMetadata) {
schemaProto := schema.GetProto()
// Add all tables
for _, tableName := range schema.ListTableNames() {
table := schema.GetRootTable(tableName)
if table != nil && !table.GetProto().GetSkipDump() {
diff.TableChanges = append(diff.TableChanges, &TableDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
TableName: tableName,
NewTable: table.GetProto(),
})
}
}
// Add all views
for _, viewName := range schema.ListViewNames() {
view := schema.GetView(viewName)
if view != nil && !view.SkipDump {
diff.ViewChanges = append(diff.ViewChanges, &ViewDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
ViewName: viewName,
NewView: view,
})
}
}
// Add all materialized views
for _, mvName := range schema.ListMaterializedViewNames() {
mv := schema.GetMaterializedView(mvName)
if mv != nil && !mv.SkipDump {
diff.MaterializedViewChanges = append(diff.MaterializedViewChanges, &MaterializedViewDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
MaterializedViewName: mvName,
NewMaterializedView: mv,
})
}
}
// Add all functions
for _, function := range schema.GetProto().GetFunctions() {
if !function.GetSkipDump() {
// Use signature if available, otherwise fall back to name
functionName := function.Signature
if functionName == "" {
functionName = function.Name
}
diff.FunctionChanges = append(diff.FunctionChanges, &FunctionDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
FunctionName: functionName,
NewFunction: function,
})
}
}
// Add all procedures
for _, procName := range schema.ListProcedureNames() {
proc := schema.GetProcedure(procName)
if proc != nil && !proc.SkipDump {
diff.ProcedureChanges = append(diff.ProcedureChanges, &ProcedureDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
ProcedureName: procName,
NewProcedure: proc,
})
}
}
// Add all sequences
for _, seqProto := range schemaProto.Sequences {
if !seqProto.GetSkipDump() {
diff.SequenceChanges = append(diff.SequenceChanges, &SequenceDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
SequenceName: seqProto.Name,
NewSequence: seqProto,
})
}
}
// Add all enum types
for _, enumProto := range schemaProto.EnumTypes {
if !enumProto.GetSkipDump() {
diff.EnumTypeChanges = append(diff.EnumTypeChanges, &EnumTypeDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
EnumTypeName: enumProto.Name,
NewEnumType: enumProto,
})
}
}
// Add all composite types
for _, compositeProto := range schemaProto.CompositeTypes {
if !compositeProto.GetSkipDump() {
diff.CompositeTypeChanges = append(diff.CompositeTypeChanges, &CompositeTypeDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
CompositeTypeName: compositeProto.Name,
NewCompositeType: compositeProto,
})
}
}
// Add all events
for _, eventProto := range schemaProto.Events {
diff.EventChanges = append(diff.EventChanges, &EventDiff{
Action: MetadataDiffActionCreate,
EventName: eventProto.Name,
NewEvent: eventProto,
})
}
}
// compareSchemaObjects compares objects between two schemas.
func compareSchemaObjects(engine storepb.Engine, diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
// Compare tables
// Check for dropped tables
for _, tableName := range oldSchema.ListTableNames() {
if newSchema.GetRootTable(tableName) == nil {
oldTable := oldSchema.GetRootTable(tableName)
if oldTable != nil && !oldTable.GetProto().GetSkipDump() {
diff.TableChanges = append(diff.TableChanges, &TableDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
TableName: tableName,
OldTable: oldTable.GetProto(),
})
}
}
}
// Check for new and modified tables
for _, tableName := range newSchema.ListTableNames() {
newTable := newSchema.GetRootTable(tableName)
if newTable == nil || newTable.GetProto().GetSkipDump() {
continue
}
if oldSchema.GetRootTable(tableName) == nil {
diff.TableChanges = append(diff.TableChanges, &TableDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
TableName: tableName,
NewTable: newTable.GetProto(),
})
} else {
// Compare table details
oldTable := oldSchema.GetRootTable(tableName)
if oldTable != nil && !oldTable.GetProto().GetSkipDump() {
tableDiff := compareTableDetails(engine, schemaName, tableName, oldTable, newTable)
if tableDiff != nil {
diff.TableChanges = append(diff.TableChanges, tableDiff)
}
}
}
}
// Compare views
compareViews(engine, diff, schemaName, oldSchema, newSchema)
// Compare materialized views
compareMaterializedViews(engine, diff, schemaName, oldSchema, newSchema)
// Compare functions
compareFunctions(engine, diff, schemaName, oldSchema, newSchema)
// Compare procedures
compareProcedures(diff, schemaName, oldSchema, newSchema)
// Compare sequences
compareSequences(diff, schemaName, oldSchema, newSchema)
// Compare enum types
compareEnumTypes(diff, schemaName, oldSchema, newSchema)
// Compare composite types
compareCompositeTypes(diff, schemaName, oldSchema, newSchema)
// Compare events
compareEvents(diff, schemaName, oldSchema, newSchema)
}
// compareTableDetails compares the details of two tables.
func compareTableDetails(engine storepb.Engine, schemaName, tableName string, oldTable, newTable *model.TableMetadata) *TableDiff {
tableDiff := &TableDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
TableName: tableName,
OldTable: oldTable.GetProto(),
NewTable: newTable.GetProto(),
}
hasChanges := false
// Compare columns
columnChanges := compareColumns(engine, oldTable, newTable)
if len(columnChanges) > 0 {
tableDiff.ColumnChanges = columnChanges
hasChanges = true
}
// Compare indexes
indexChanges := compareIndexes(engine, oldTable, newTable)
if len(indexChanges) > 0 {
tableDiff.IndexChanges = indexChanges
hasChanges = true
}
// Compare foreign keys
fkChanges := compareForeignKeys(oldTable.GetProto().ForeignKeys, newTable.GetProto().ForeignKeys)
if len(fkChanges) > 0 {
tableDiff.ForeignKeyChanges = fkChanges
hasChanges = true
}
// Compare check constraints
checkChanges := compareCheckConstraints(engine, oldTable.GetProto().CheckConstraints, newTable.GetProto().CheckConstraints)
if len(checkChanges) > 0 {
tableDiff.CheckConstraintChanges = checkChanges
hasChanges = true
}
// Compare partitions
partitionChanges := comparePartitions(engine, oldTable.GetProto().Partitions, newTable.GetProto().Partitions)
if len(partitionChanges) > 0 {
tableDiff.PartitionChanges = partitionChanges
hasChanges = true
}
// Compare triggers
triggerChanges := compareTriggers(oldTable.GetProto().Triggers, newTable.GetProto().Triggers)
if len(triggerChanges) > 0 {
tableDiff.TriggerChanges = triggerChanges
hasChanges = true
}
// Compare table comments
if oldTable.GetProto().Comment != newTable.GetProto().Comment {
hasChanges = true
}
if !hasChanges {
return nil
}
return tableDiff
}
// compareColumns compares columns between two tables.
func compareColumns(engine storepb.Engine, oldTable, newTable *model.TableMetadata) []*ColumnDiff {
var changes []*ColumnDiff
oldColumns := oldTable.GetProto().GetColumns()
newColumns := newTable.GetProto().GetColumns()
// Check for dropped columns
for _, oldCol := range oldColumns {
if newTable.GetColumn(oldCol.Name) == nil {
changes = append(changes, &ColumnDiff{
Action: MetadataDiffActionDrop,
OldColumn: oldCol,
})
}
}
// Check for new and modified columns
for _, newCol := range newColumns {
oldColWrapper := oldTable.GetColumn(newCol.Name)
if oldColWrapper == nil {
changes = append(changes, &ColumnDiff{
Action: MetadataDiffActionCreate,
NewColumn: newCol,
})
} else {
oldCol := oldColWrapper.GetProto()
if !columnsEqual(engine, oldCol, newCol) {
changes = append(changes, &ColumnDiff{
Action: MetadataDiffActionAlter,
OldColumn: oldCol,
NewColumn: newCol,
})
}
}
}
return changes
}
// columnsEqual checks if two columns are equal.
func columnsEqual(engine storepb.Engine, col1, col2 *metadatapb.ColumnMetadata) bool {
if col1.Type != col2.Type {
return false
}
if col1.Nullable != col2.Nullable {
return false
}
// Compare default values
if !defaultValuesEqual(col1, col2) {
return false
}
if col1.Comment != col2.Comment {
return false
}
// Compare character set and collation
if col1.CharacterSet != col2.CharacterSet {
return false
}
if col1.Collation != col2.Collation {
return false
}
// Compare on update clause
if col1.OnUpdate != col2.OnUpdate {
return false
}
// Compare Oracle specific metadata
if col1.DefaultOnNull != col2.DefaultOnNull {
return false
}
// Compare generated column metadata
if !generationMetadataEqual(engine, col1.Generation, col2.Generation) {
return false
}
// Compare identity column metadata
if col1.IsIdentity != col2.IsIdentity {
return false
}
if col1.IdentityGeneration != col2.IdentityGeneration {
return false
}
if col1.IdentitySeed != col2.IdentitySeed {
return false
}
// Compare spatial SRID (MySQL 8.0): presence + value — SRID 0 is a valid explicit
// SRID, distinct from "no SRID declared".
if (col1.Srid == nil) != (col2.Srid == nil) || col1.GetSrid() != col2.GetSrid() {
return false
}
// Compare column invisibility (MySQL 8.0.23+).
if col1.IsInvisible != col2.IsInvisible {
return false
}
return true
}
// defaultValuesEqual compares default values.
func defaultValuesEqual(col1, col2 *metadatapb.ColumnMetadata) bool {
// Quick check for identical strings
if col1.Default == col2.Default {
return true
}
// Handle PostgreSQL type cast normalization for common cases before expression comparison
// This is needed because the expression comparer may not parse complex type casts correctly
normalized1 := normalizePostgreSQLTypecastInDefault(col1.Default)
normalized2 := normalizePostgreSQLTypecastInDefault(col2.Default)
if normalized1 == normalized2 {
return true
}
// Use semantic expression comparison for PostgreSQL default values
// This handles schema prefixes and other semantic equivalences
return CompareExpressionsSemantically(storepb.Engine_POSTGRES, col1.Default, col2.Default)
}
// normalizePostgreSQLTypecastInDefault normalizes PostgreSQL type casts in default values
func normalizePostgreSQLTypecastInDefault(defaultValue string) string {
if defaultValue == "" {
return ""
}
normalized := strings.TrimSpace(defaultValue)
// Remove PostgreSQL type casts from default values
// Handle patterns like '1 day'::interval -> '1 day'
normalized = strings.ReplaceAll(normalized, "'::interval", "'")
normalized = strings.ReplaceAll(normalized, "'::timestamp", "'")
normalized = strings.ReplaceAll(normalized, "'::date", "'")
normalized = strings.ReplaceAll(normalized, "'::time", "'")
normalized = strings.ReplaceAll(normalized, "'::numeric", "'")
// Handle string type casts
// Handle character varying first (longest match first to avoid partial replacement)
normalized = strings.ReplaceAll(normalized, "'::character varying", "'") // CHARACTER VARYING type (full form of VARCHAR)
normalized = strings.ReplaceAll(normalized, "'::bpchar", "'") // CHAR type
normalized = strings.ReplaceAll(normalized, "'::text", "'") // TEXT type
normalized = strings.ReplaceAll(normalized, "'::varchar", "'") // VARCHAR type
normalized = strings.ReplaceAll(normalized, "'::character", "'") // CHARACTER type
return normalized
}
// generationMetadataEqual compares two generation metadata structs.
func generationMetadataEqual(engine storepb.Engine, gen1, gen2 *metadatapb.GenerationMetadata) bool {
if gen1 == nil && gen2 == nil {
return true
}
if gen1 == nil || gen2 == nil {
return false
}
return gen1.Type == gen2.Type && CompareExpressionsSemantically(engine, gen1.Expression, gen2.Expression)
}
// compareIndexes compares indexes between two tables.
func compareIndexes(engine storepb.Engine, oldTable, newTable *model.TableMetadata) []*IndexDiff {
var changes []*IndexDiff
oldIndexes := oldTable.ListIndexes()
newIndexes := newTable.ListIndexes()
// Check for dropped indexes
for _, oldIdx := range oldIndexes {
if newTable.GetIndex(oldIdx.GetProto().Name) == nil {
changes = append(changes, &IndexDiff{
Action: MetadataDiffActionDrop,
OldIndex: oldIdx.GetProto(),
})
}
}
// Check for new and modified indexes
for _, newIdx := range newIndexes {
oldIdx := oldTable.GetIndex(newIdx.GetProto().Name)
if oldIdx == nil {
changes = append(changes, &IndexDiff{
Action: MetadataDiffActionCreate,
NewIndex: newIdx.GetProto(),
})
} else if !indexesEqual(engine, oldIdx.GetProto(), newIdx.GetProto()) {
// Drop the old index and recreate the new one instead of altering
changes = append(changes, &IndexDiff{
Action: MetadataDiffActionDrop,
OldIndex: oldIdx.GetProto(),
})
changes = append(changes, &IndexDiff{
Action: MetadataDiffActionCreate,
NewIndex: newIdx.GetProto(),
})
}
}
return changes
}
// indexesEqual checks if two indexes are equal.
func indexesEqual(engine storepb.Engine, idx1, idx2 *metadatapb.IndexMetadata) bool {
if idx1.Type != idx2.Type {
return false
}
if idx1.Unique != idx2.Unique {
return false
}
if idx1.Primary != idx2.Primary {
return false
}
if len(idx1.Expressions) != len(idx2.Expressions) {
return false
}
for i, expr := range idx1.Expressions {
if !CompareExpressionsSemantically(engine, expr, idx2.Expressions[i]) {
return false
}
}
// Compare key lengths
if len(idx1.KeyLength) != len(idx2.KeyLength) {
return false
}
for i, keyLen := range idx1.KeyLength {
if keyLen != idx2.KeyLength[i] {
return false
}
}
// Compare descending order - be flexible about empty arrays vs arrays of false
// This handles the case where one has [] and the other has [false, false, ...]
if !descendingArraysEqual(idx1.Descending, idx2.Descending) {
return false
}
// Compare visibility
if idx1.Visible != idx2.Visible {
return false
}
// Compare spatial configuration
if !spatialConfigsEqual(idx1.SpatialConfig, idx2.SpatialConfig) {
return false
}
// Compare WHERE conditions for partial indexes using engine-specific comparer
comparer := GetIndexComparer(engine)
return comparer.CompareIndexWhereConditions(idx1.Definition, idx2.Definition)
}
// descendingArraysEqual compares two descending arrays, considering empty arrays
// and arrays of all false values as equivalent for constraint-based indexes.
func descendingArraysEqual(desc1, desc2 []bool) bool {
// Helper function to check if an array is effectively "all false"
// (either empty or contains only false values)
isEffectivelyAllFalse := func(arr []bool) bool {
if len(arr) == 0 {
return true // Empty array means no descending columns
}
for _, val := range arr {
if val {
return false // Found a true value, so not all false
}
}
return true // All values are false
}
// If both arrays are effectively "all false", they are equal
if isEffectivelyAllFalse(desc1) && isEffectivelyAllFalse(desc2) {
return true
}
// If lengths don't match and neither is empty, do exact comparison
if len(desc1) != len(desc2) {
return false
}
// Do element-by-element comparison
for i, val1 := range desc1 {
if i >= len(desc2) || val1 != desc2[i] {
return false
}
}
return true
}
// spatialConfigsEqual checks if two spatial index configurations are equal.
func spatialConfigsEqual(cfg1, cfg2 *metadatapb.SpatialIndexConfig) bool {
if cfg1 == nil && cfg2 == nil {
return true
}
if cfg1 == nil || cfg2 == nil {
return false
}
// Compare method
if cfg1.Method != cfg2.Method {
return false
}
// Compare tessellation config
if !tessellationConfigsEqual(cfg1.Tessellation, cfg2.Tessellation) {
return false
}
// Compare storage config
if !storageConfigsEqual(cfg1.Storage, cfg2.Storage) {
return false
}
// Compare dimensional config
if !dimensionalConfigsEqual(cfg1.Dimensional, cfg2.Dimensional) {
return false
}
// Compare engine-specific parameters
if len(cfg1.EngineSpecific) != len(cfg2.EngineSpecific) {
return false
}
for key, val1 := range cfg1.EngineSpecific {
val2, exists := cfg2.EngineSpecific[key]
if !exists || val1 != val2 {
return false
}
}
return true
}
// tessellationConfigsEqual checks if two tessellation configurations are equal.
func tessellationConfigsEqual(cfg1, cfg2 *metadatapb.TessellationConfig) bool {
if cfg1 == nil && cfg2 == nil {
return true
}
if cfg1 == nil || cfg2 == nil {
return false
}
if cfg1.Scheme != cfg2.Scheme {
return false
}
if !boundingBoxesEqual(cfg1.BoundingBox, cfg2.BoundingBox) {
return false
}
if !gridLevelsEqual(cfg1.GridLevels, cfg2.GridLevels) {
return false
}
if cfg1.CellsPerObject != cfg2.CellsPerObject {
return false
}
return true
}
// boundingBoxesEqual checks if two bounding boxes are equal.
func boundingBoxesEqual(bb1, bb2 *metadatapb.BoundingBox) bool {
if bb1 == nil && bb2 == nil {
return true
}
if bb1 == nil || bb2 == nil {
return false
}
return bb1.Xmin == bb2.Xmin &&
bb1.Ymin == bb2.Ymin &&
bb1.Xmax == bb2.Xmax &&
bb1.Ymax == bb2.Ymax
}
// gridLevelsEqual checks if two grid level lists are equal.
func gridLevelsEqual(grids1, grids2 []*metadatapb.GridLevel) bool {
if len(grids1) != len(grids2) {
return false
}
for i, grid1 := range grids1 {
grid2 := grids2[i]
if grid1.Level != grid2.Level || grid1.Density != grid2.Density {
return false
}
}
return true
}
// storageConfigsEqual checks if two storage configurations are equal.
func storageConfigsEqual(cfg1, cfg2 *metadatapb.StorageConfig) bool {
if cfg1 == nil && cfg2 == nil {
return true
}
if cfg1 == nil || cfg2 == nil {
return false
}
return cfg1.Fillfactor == cfg2.Fillfactor &&
cfg1.Buffering == cfg2.Buffering &&
cfg1.Tablespace == cfg2.Tablespace &&
cfg1.WorkTablespace == cfg2.WorkTablespace &&
cfg1.SdoLevel == cfg2.SdoLevel &&
cfg1.CommitInterval == cfg2.CommitInterval &&
cfg1.PadIndex == cfg2.PadIndex &&
cfg1.SortInTempdb == cfg2.SortInTempdb &&
cfg1.DropExisting == cfg2.DropExisting &&
cfg1.Online == cfg2.Online &&
cfg1.AllowRowLocks == cfg2.AllowRowLocks &&
cfg1.AllowPageLocks == cfg2.AllowPageLocks &&
cfg1.Maxdop == cfg2.Maxdop &&
cfg1.DataCompression == cfg2.DataCompression
}
// dimensionalConfigsEqual checks if two dimensional configurations are equal.
func dimensionalConfigsEqual(cfg1, cfg2 *metadatapb.DimensionalConfig) bool {
if cfg1 == nil && cfg2 == nil {
return true
}
if cfg1 == nil || cfg2 == nil {
return false
}
return cfg1.Dimensions == cfg2.Dimensions &&
cfg1.DataType == cfg2.DataType &&
cfg1.OperatorClass == cfg2.OperatorClass &&
cfg1.LayerGtype == cfg2.LayerGtype &&
cfg1.ParallelBuild == cfg2.ParallelBuild
}
// compareForeignKeys compares two lists of foreign keys.
func compareForeignKeys(oldFKs, newFKs []*metadatapb.ForeignKeyMetadata) []*ForeignKeyDiff {
var changes []*ForeignKeyDiff
oldFKMap := make(map[string]*metadatapb.ForeignKeyMetadata)
for _, fk := range oldFKs {
oldFKMap[fk.Name] = fk
}
newFKMap := make(map[string]*metadatapb.ForeignKeyMetadata)
for _, fk := range newFKs {
newFKMap[fk.Name] = fk
}
// Check for dropped foreign keys
for fkName, oldFK := range oldFKMap {
if _, exists := newFKMap[fkName]; !exists {
changes = append(changes, &ForeignKeyDiff{
Action: MetadataDiffActionDrop,
OldForeignKey: oldFK,
})
}
}
// Check for new and modified foreign keys
for fkName, newFK := range newFKMap {
oldFK, exists := oldFKMap[fkName]
if !exists {
changes = append(changes, &ForeignKeyDiff{
Action: MetadataDiffActionCreate,
NewForeignKey: newFK,
})
} else if !foreignKeysEqual(oldFK, newFK) {
// Drop the old FK and recreate the new one instead of altering
changes = append(changes, &ForeignKeyDiff{
Action: MetadataDiffActionDrop,
OldForeignKey: oldFK,
})
changes = append(changes, &ForeignKeyDiff{
Action: MetadataDiffActionCreate,
NewForeignKey: newFK,
})
}
}
return changes
}
// foreignKeysEqual checks if two foreign keys are equal.
func foreignKeysEqual(fk1, fk2 *metadatapb.ForeignKeyMetadata) bool {
if fk1.ReferencedSchema != fk2.ReferencedSchema {
return false
}
if fk1.ReferencedTable != fk2.ReferencedTable {
return false
}
if fk1.OnDelete != fk2.OnDelete {
return false
}
if fk1.OnUpdate != fk2.OnUpdate {
return false
}
if fk1.MatchType != fk2.MatchType {
return false
}
if len(fk1.Columns) != len(fk2.Columns) {
return false
}
for i, col := range fk1.Columns {
if col != fk2.Columns[i] {
return false
}
}
if len(fk1.ReferencedColumns) != len(fk2.ReferencedColumns) {
return false
}
for i, col := range fk1.ReferencedColumns {
if col != fk2.ReferencedColumns[i] {
return false
}
}
return true
}
// compareCheckConstraints compares two lists of check constraints.
func compareCheckConstraints(engine storepb.Engine, oldChecks, newChecks []*metadatapb.CheckConstraintMetadata) []*CheckConstraintDiff {
var changes []*CheckConstraintDiff
oldCheckMap := make(map[string]*metadatapb.CheckConstraintMetadata)
for _, check := range oldChecks {
oldCheckMap[check.Name] = check
}
newCheckMap := make(map[string]*metadatapb.CheckConstraintMetadata)
for _, check := range newChecks {
newCheckMap[check.Name] = check
}
// Check for dropped check constraints
for checkName, oldCheck := range oldCheckMap {
if _, exists := newCheckMap[checkName]; !exists {
changes = append(changes, &CheckConstraintDiff{
Action: MetadataDiffActionDrop,
OldCheckConstraint: oldCheck,
})
}
}
// Check for new and modified check constraints
for checkName, newCheck := range newCheckMap {
oldCheck, exists := oldCheckMap[checkName]
if !exists {
changes = append(changes, &CheckConstraintDiff{
Action: MetadataDiffActionCreate,
NewCheckConstraint: newCheck,
})
} else if !checkConstraintsEqual(engine, oldCheck, newCheck) {
// Drop the old constraint and recreate the new one instead of altering
changes = append(changes, &CheckConstraintDiff{
Action: MetadataDiffActionDrop,
OldCheckConstraint: oldCheck,
})
changes = append(changes, &CheckConstraintDiff{
Action: MetadataDiffActionCreate,
NewCheckConstraint: newCheck,
})
}
}
return changes
}
// checkConstraintsEqual checks if two check constraints are equal.
func checkConstraintsEqual(engine storepb.Engine, check1, check2 *metadatapb.CheckConstraintMetadata) bool {
// First try semantic comparison
if CompareExpressionsSemantically(engine, check1.Expression, check2.Expression) {
return true
}
// Handle PostgreSQL-specific normalizations
// PostgreSQL converts "column IN (values)" to "column = ANY (ARRAY[values])"
if isPostgreSQLInAnyEquivalent(check1.Expression, check2.Expression) ||
isPostgreSQLInAnyEquivalent(check2.Expression, check1.Expression) {
return true
}
// Handle PostgreSQL interval syntax differences
// Sync: (order_date >= (CURRENT_DATE - '1 year'::interval))
// Parser: order_date >= CURRENT_DATE - INTERVAL '1 year'
if normalizePostgreSQLCheckConstraint(check1.Expression) == normalizePostgreSQLCheckConstraint(check2.Expression) {
return true
}
return false
}
// isPostgreSQLInAnyEquivalent checks if expr1 contains IN syntax that PostgreSQL would normalize to ANY syntax in expr2
func isPostgreSQLInAnyEquivalent(expr1, expr2 string) bool {
// Clean up expressions for comparison
expr1 = strings.TrimSpace(expr1)
expr2 = strings.TrimSpace(expr2)
// Handle common PostgreSQL transformations:
// "column IN ('A', 'B')" -> "(column = ANY (ARRAY['A'::text, 'B'::text]))"
// Very basic pattern matching for this specific case
// Look for patterns like "column IN(...)" in expr1 and "column = ANY (ARRAY[...])" in expr2
if strings.Contains(expr1, " IN(") || strings.Contains(expr1, " IN (") {
// Extract the column name and values from IN expression
if strings.Contains(expr2, " = ANY (ARRAY[") && strings.Contains(expr2, "::text") {
// This is a simplified check - for a production system, you'd want more robust parsing
// For now, just check if both expressions contain the same column name
inParts := strings.Split(expr1, " IN")
if len(inParts) >= 2 {
column1 := strings.TrimSpace(inParts[0])
// Remove any leading parentheses from column name
column1 = strings.TrimPrefix(column1, "(")
if strings.HasPrefix(expr2, "("+column1+" = ANY") || strings.HasPrefix(expr2, column1+" = ANY") {
// Extract values from both expressions and compare
return compareInVsAnyValues(expr1, expr2)
}
}
}
}
return false
}
// compareInVsAnyValues compares the values in IN syntax vs ANY syntax
func compareInVsAnyValues(inExpr, anyExpr string) bool {
// Extract values from IN expression: "column IN('A', 'B')"
inValues := extractInValues(inExpr)
if inValues == nil {
return false
}
// Extract values from ANY expression: "(column = ANY (ARRAY['A'::text, 'B'::text]))"
anyValues := extractAnyValues(anyExpr)
if anyValues == nil {
return false
}
// Compare the value sets
if len(inValues) != len(anyValues) {
return false
}
// Convert both to maps for comparison (order doesn't matter)
inMap := make(map[string]bool)
for _, v := range inValues {
inMap[v] = true
}
anyMap := make(map[string]bool)
for _, v := range anyValues {
anyMap[v] = true
}
// Check if they contain the same values
for v := range inMap {
if !anyMap[v] {
return false
}
}
return true
}
// extractInValues extracts values from "column IN('A', 'B')" format
func extractInValues(expr string) []string {
// Find the IN part
inIndex := strings.Index(expr, " IN")
if inIndex == -1 {
return nil
}
// Find the opening parenthesis
openParen := strings.Index(expr[inIndex:], "(")
if openParen == -1 {
return nil
}
openParen += inIndex
// Find the closing parenthesis
closeParen := strings.LastIndex(expr, ")")
if closeParen == -1 || closeParen <= openParen {
return nil
}
// Extract the values part
valuesStr := expr[openParen+1 : closeParen]
// Split by comma and clean up
var values []string
parts := strings.Split(valuesStr, ",")
for _, part := range parts {
part = strings.TrimSpace(part)
// Remove quotes
if (part[0] == '\'' && part[len(part)-1] == '\'') ||
(part[0] == '"' && part[len(part)-1] == '"') {
part = part[1 : len(part)-1]
}
values = append(values, part)
}
return values
}
// extractAnyValues extracts values from "(column = ANY (ARRAY['A'::text, 'B'::text]))" format
func extractAnyValues(expr string) []string {
// Find the ARRAY part
arrayIndex := strings.Index(expr, "ARRAY[")
if arrayIndex == -1 {
return nil
}
// Find the closing bracket
closeBracket := strings.Index(expr[arrayIndex:], "]")
if closeBracket == -1 {
return nil
}
closeBracket += arrayIndex
// Extract the values part
valuesStr := expr[arrayIndex+6 : closeBracket] // Skip "ARRAY["
// Split by comma and clean up
var values []string
parts := strings.Split(valuesStr, ",")
for _, part := range parts {
part = strings.TrimSpace(part)
// Remove ::text suffix and quotes
part = strings.TrimSuffix(part, "::text")
if (part[0] == '\'' && part[len(part)-1] == '\'') ||
(part[0] == '"' && part[len(part)-1] == '"') {
part = part[1 : len(part)-1]
}
values = append(values, part)
}
return values
}
// comparePartitions compares two lists of partitions.
func comparePartitions(engine storepb.Engine, oldPartitions, newPartitions []*metadatapb.TablePartitionMetadata) []*PartitionDiff {
var changes []*PartitionDiff
oldPartMap := make(map[string]*metadatapb.TablePartitionMetadata)
for _, part := range oldPartitions {
oldPartMap[part.Name] = part
}
newPartMap := make(map[string]*metadatapb.TablePartitionMetadata)
for _, part := range newPartitions {
newPartMap[part.Name] = part
}
// Check for dropped partitions
for partName, oldPart := range oldPartMap {
if _, exists := newPartMap[partName]; !exists {
changes = append(changes, &PartitionDiff{
Action: MetadataDiffActionDrop,
OldPartition: oldPart,
})
}
}
// Check for new and modified partitions
for partName, newPart := range newPartMap {
oldPart, exists := oldPartMap[partName]
if !exists {
changes = append(changes, &PartitionDiff{
Action: MetadataDiffActionCreate,
NewPartition: newPart,
})
} else if !partitionsEqual(engine, oldPart, newPart) {
// Drop the old partition and recreate the new one instead of altering
changes = append(changes, &PartitionDiff{
Action: MetadataDiffActionDrop,
OldPartition: oldPart,
})
changes = append(changes, &PartitionDiff{
Action: MetadataDiffActionCreate,
NewPartition: newPart,
})
}
}
return changes
}
// compareTriggers compares two lists of triggers.
func compareTriggers(oldTriggers, newTriggers []*metadatapb.TriggerMetadata) []*TriggerDiff {
var changes []*TriggerDiff
oldTriggerMap := make(map[string]*metadatapb.TriggerMetadata)
for _, trigger := range oldTriggers {
oldTriggerMap[trigger.Name] = trigger
}
newTriggerMap := make(map[string]*metadatapb.TriggerMetadata)
for _, trigger := range newTriggers {
newTriggerMap[trigger.Name] = trigger
}
// Check for dropped triggers
for triggerName, oldTrigger := range oldTriggerMap {
if _, exists := newTriggerMap[triggerName]; !exists {
changes = append(changes, &TriggerDiff{
Action: MetadataDiffActionDrop,
TriggerName: triggerName,
OldTrigger: oldTrigger,
})
}
}
// Check for new and modified triggers
for triggerName, newTrigger := range newTriggerMap {
oldTrigger, exists := oldTriggerMap[triggerName]
if !exists {
changes = append(changes, &TriggerDiff{
Action: MetadataDiffActionCreate,
TriggerName: triggerName,
NewTrigger: newTrigger,
})
} else if !triggersEqual(oldTrigger, newTrigger) {
// Drop and recreate the trigger instead of altering
changes = append(changes, &TriggerDiff{
Action: MetadataDiffActionDrop,
TriggerName: triggerName,
OldTrigger: oldTrigger,
})
changes = append(changes, &TriggerDiff{
Action: MetadataDiffActionCreate,
TriggerName: triggerName,
NewTrigger: newTrigger,
})
}
}
return changes
}
// triggersEqual checks if two triggers are equal.
func triggersEqual(t1, t2 *metadatapb.TriggerMetadata) bool {
if t1 == nil || t2 == nil {
return t1 == t2
}
basicEqual := t1.Name == t2.Name &&
t1.Event == t2.Event &&
t1.Timing == t2.Timing
if !basicEqual {
return false
}
// Direct comparison first
if t1.Body == t2.Body {
return true
}
// Normalize both bodies for comparison
norm1 := normalizeTriggerBody(t1.Body)
norm2 := normalizeTriggerBody(t2.Body)
return norm1 == norm2
}
// normalizeTriggerBody normalizes trigger body for comparison
func normalizeTriggerBody(body string) string {
if body == "" {
return ""
}
// Aggressive normalization: remove ALL whitespace for comparison
// This handles cases where ANTLR GetText() strips whitespace
normalized := strings.ReplaceAll(body, " ", "")
normalized = strings.ReplaceAll(normalized, "\r\n", "")
normalized = strings.ReplaceAll(normalized, "\r", "")
normalized = strings.ReplaceAll(normalized, "\n", "")
normalized = strings.ReplaceAll(normalized, "\t", "")
// Convert to uppercase for case-insensitive comparison
normalized = strings.ToUpper(normalized)
return normalized
}
// partitionsEqual checks if two partitions are equal.
func partitionsEqual(engine storepb.Engine, part1, part2 *metadatapb.TablePartitionMetadata) bool {
if part1.Type != part2.Type {
return false
}
if !CompareExpressionsSemantically(engine, part1.Expression, part2.Expression) {
return false
}
if part1.Value != part2.Value {
return false
}
if part1.UseDefault != part2.UseDefault {
return false
}
// Compare subpartitions
if len(part1.Subpartitions) != len(part2.Subpartitions) {
return false
}
// Create maps for subpartition comparison
subPart1Map := make(map[string]*metadatapb.TablePartitionMetadata)
for _, sub := range part1.Subpartitions {
subPart1Map[sub.Name] = sub
}
for _, sub2 := range part2.Subpartitions {
sub1, exists := subPart1Map[sub2.Name]
if !exists || !partitionsEqual(engine, sub1, sub2) {
return false
}
}
return true
}
// compareViews compares views between two schemas.
func compareViews(engine storepb.Engine, diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
// Get the engine-specific view comparer
comparer := GetViewComparer(engine)
// Check for dropped views
for _, viewName := range oldSchema.ListViewNames() {
if newSchema.GetView(viewName) == nil {
oldView := oldSchema.GetView(viewName)
if oldView != nil && !oldView.SkipDump {
diff.ViewChanges = append(diff.ViewChanges, &ViewDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
ViewName: viewName,
OldView: oldView,
})
}
}
}
// Check for new and modified views
for _, viewName := range newSchema.ListViewNames() {
newView := newSchema.GetView(viewName)
if newView == nil || newView.SkipDump {
continue
}
oldView := oldSchema.GetView(viewName)
if oldView == nil {
diff.ViewChanges = append(diff.ViewChanges, &ViewDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
ViewName: viewName,
NewView: newView,
})
} else if !oldView.SkipDump {
// Use engine-specific comparison
changes, err := comparer.CompareView(oldView, newView)
if err != nil {
// Fallback to simple definition comparison on error
if oldView.Definition != newView.Definition {
diff.ViewChanges = append(diff.ViewChanges, &ViewDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
ViewName: viewName,
OldView: oldView,
NewView: newView,
})
}
} else if len(changes) > 0 {
// Check if any change requires recreation
requiresRecreation := false
for _, change := range changes {
if change.RequiresRecreation {
requiresRecreation = true
break
}
}
if requiresRecreation {
diff.ViewChanges = append(diff.ViewChanges, &ViewDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
ViewName: viewName,
OldView: oldView,
NewView: newView,
})
}
// TODO: Handle non-recreating changes like comment updates
}
}
}
}
// compareMaterializedViews compares materialized views between two schemas.
func compareMaterializedViews(engine storepb.Engine, diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
// Get the engine-specific view comparer
comparer := GetViewComparer(engine)
// Check for dropped materialized views
for _, mvName := range oldSchema.ListMaterializedViewNames() {
if newSchema.GetMaterializedView(mvName) == nil {
oldMV := oldSchema.GetMaterializedView(mvName)
if oldMV != nil && !oldMV.SkipDump {
diff.MaterializedViewChanges = append(diff.MaterializedViewChanges, &MaterializedViewDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
MaterializedViewName: mvName,
OldMaterializedView: oldMV,
})
}
}
}
// Check for new and modified materialized views
for _, mvName := range newSchema.ListMaterializedViewNames() {
newMV := newSchema.GetMaterializedView(mvName)
if newMV == nil || newMV.SkipDump {
continue
}
oldMV := oldSchema.GetMaterializedView(mvName)
if oldMV == nil {
diff.MaterializedViewChanges = append(diff.MaterializedViewChanges, &MaterializedViewDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
MaterializedViewName: mvName,
NewMaterializedView: newMV,
})
} else if !oldMV.SkipDump {
// Use engine-specific comparison
changes, err := comparer.CompareMaterializedView(oldMV, newMV)
if err != nil {
// Fallback to simple definition comparison on error
if oldMV.Definition != newMV.Definition {
diff.MaterializedViewChanges = append(diff.MaterializedViewChanges, &MaterializedViewDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
MaterializedViewName: mvName,
OldMaterializedView: oldMV,
NewMaterializedView: newMV,
})
}
} else if len(changes) > 0 {
// Check if any change requires recreation
requiresRecreation := false
for _, change := range changes {
if change.RequiresRecreation {
requiresRecreation = true
break
}
}
if requiresRecreation {
diff.MaterializedViewChanges = append(diff.MaterializedViewChanges, &MaterializedViewDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
MaterializedViewName: mvName,
OldMaterializedView: oldMV,
NewMaterializedView: newMV,
})
}
// TODO: Handle non-recreating changes like comment updates or index-only changes
}
}
}
}
// compareFunctions compares functions between two schemas.
func compareFunctions(engine storepb.Engine, diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
// Get the engine-specific function comparer
comparer := GetFunctionComparer(engine)
// Functions can have overloading, so we need to handle them carefully
// Group functions by signature to properly match overloaded functions
// Build map of old functions by signature
oldFuncsBySignature := make(map[string]*metadatapb.FunctionMetadata)
for _, fn := range oldSchema.GetProto().GetFunctions() {
if !fn.GetSkipDump() {
sig := fn.Signature
if sig == "" {
sig = fn.Name // fallback if no signature
}
oldFuncsBySignature[sig] = fn
}
}
// Build map of new functions by signature
newFuncsBySignature := make(map[string]*metadatapb.FunctionMetadata)
for _, fn := range newSchema.GetProto().GetFunctions() {
if !fn.GetSkipDump() {
sig := fn.Signature
if sig == "" {
sig = fn.Name // fallback if no signature
}
newFuncsBySignature[sig] = fn
}
}
// Check for dropped functions
for sig, oldFunc := range oldFuncsBySignature {
if _, exists := newFuncsBySignature[sig]; !exists {
// Use signature if available, otherwise fall back to name
functionName := oldFunc.Signature
if functionName == "" {
functionName = oldFunc.Name
}
diff.FunctionChanges = append(diff.FunctionChanges, &FunctionDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
FunctionName: functionName,
OldFunction: oldFunc,
})
}
}
// Check for new and modified functions
for sig, newFunc := range newFuncsBySignature {
oldFunc, exists := oldFuncsBySignature[sig]
if !exists {
// Use signature if available, otherwise fall back to name
functionName := newFunc.Signature
if functionName == "" {
functionName = newFunc.Name
}
diff.FunctionChanges = append(diff.FunctionChanges, &FunctionDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
FunctionName: functionName,
NewFunction: newFunc,
})
} else {
// Use detailed comparison to determine migration strategy
comparison, err := comparer.CompareDetailed(oldFunc, newFunc)
if err != nil || comparison == nil {
// Skip if no changes or error
continue
}
// Determine the action based on the comparison result
if comparison.CanUseAlterFunction {
// Use ALTER FUNCTION for body-only changes
// Use signature if available, otherwise fall back to name
functionName := oldFunc.Signature
if functionName == "" {
functionName = oldFunc.Name
}
diff.FunctionChanges = append(diff.FunctionChanges, &FunctionDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
FunctionName: functionName,
OldFunction: oldFunc,
NewFunction: newFunc,
SignatureChanged: comparison.SignatureChanged,
BodyChanged: comparison.BodyChanged,
AttributesChanged: comparison.AttributesChanged,
ChangedAttributes: comparison.ChangedAttributes,
CanUseAlterFunction: true,
})
} else {
// Use DROP and CREATE for signature changes
// Use signature if available, otherwise fall back to name for DROP
oldFunctionName := oldFunc.Signature
if oldFunctionName == "" {
oldFunctionName = oldFunc.Name
}
// Use signature if available, otherwise fall back to name for CREATE
newFunctionName := newFunc.Signature
if newFunctionName == "" {
newFunctionName = newFunc.Name
}
diff.FunctionChanges = append(diff.FunctionChanges, &FunctionDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
FunctionName: oldFunctionName,
OldFunction: oldFunc,
SignatureChanged: comparison.SignatureChanged,
BodyChanged: comparison.BodyChanged,
AttributesChanged: comparison.AttributesChanged,
ChangedAttributes: comparison.ChangedAttributes,
CanUseAlterFunction: false,
})
diff.FunctionChanges = append(diff.FunctionChanges, &FunctionDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
FunctionName: newFunctionName,
NewFunction: newFunc,
SignatureChanged: comparison.SignatureChanged,
BodyChanged: comparison.BodyChanged,
AttributesChanged: comparison.AttributesChanged,
ChangedAttributes: comparison.ChangedAttributes,
CanUseAlterFunction: false,
})
}
}
}
}
// functionsEqual checks if two functions are equal.
func functionsEqual(fn1, fn2 *metadatapb.FunctionMetadata) bool {
if fn1.Definition != fn2.Definition {
// Try normalized comparison for PostgreSQL functions
norm1 := normalizePostgreSQLFunction(fn1.Definition)
norm2 := normalizePostgreSQLFunction(fn2.Definition)
if norm1 != norm2 {
return false
}
}
if fn1.CharacterSetClient != fn2.CharacterSetClient {
return false
}
if fn1.CollationConnection != fn2.CollationConnection {
return false
}
if fn1.DatabaseCollation != fn2.DatabaseCollation {
return false
}
if fn1.SqlMode != fn2.SqlMode {
return false
}
if fn1.Comment != fn2.Comment {
return false
}
return true
}
// compareProcedures compares procedures between two schemas.
func compareProcedures(diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
// Check for dropped procedures
for _, procName := range oldSchema.ListProcedureNames() {
if newSchema.GetProcedure(procName) == nil {
oldProc := oldSchema.GetProcedure(procName)
if oldProc != nil && !oldProc.SkipDump {
diff.ProcedureChanges = append(diff.ProcedureChanges, &ProcedureDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
ProcedureName: procName,
OldProcedure: oldProc,
})
}
}
}
// Check for new and modified procedures
for _, procName := range newSchema.ListProcedureNames() {
newProc := newSchema.GetProcedure(procName)
if newProc == nil || newProc.SkipDump {
continue
}
oldProc := oldSchema.GetProcedure(procName)
if oldProc == nil {
diff.ProcedureChanges = append(diff.ProcedureChanges, &ProcedureDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
ProcedureName: procName,
NewProcedure: newProc,
})
} else if !oldProc.SkipDump && !procedureDefinitionsEqual(oldProc.Definition, newProc.Definition, procName) {
diff.ProcedureChanges = append(diff.ProcedureChanges, &ProcedureDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
ProcedureName: procName,
OldProcedure: oldProc,
NewProcedure: newProc,
})
}
}
}
// compareSequences compares sequences between two schemas.
func compareSequences(diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
// Get sequences from proto since there's no ListSequenceNames method
oldSeqMap := make(map[string]*metadatapb.SequenceMetadata)
for _, seq := range oldSchema.GetProto().Sequences {
if !seq.GetSkipDump() {
oldSeqMap[seq.Name] = seq
}
}
newSeqMap := make(map[string]*metadatapb.SequenceMetadata)
for _, seq := range newSchema.GetProto().Sequences {
if !seq.GetSkipDump() {
newSeqMap[seq.Name] = seq
}
}
// Check for dropped sequences
for seqName, oldSeq := range oldSeqMap {
if _, exists := newSeqMap[seqName]; !exists {
diff.SequenceChanges = append(diff.SequenceChanges, &SequenceDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
SequenceName: seqName,
OldSequence: oldSeq,
})
}
}
// Check for new and modified sequences
for seqName, newSeq := range newSeqMap {
if oldSeq, exists := oldSeqMap[seqName]; !exists {
diff.SequenceChanges = append(diff.SequenceChanges, &SequenceDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
SequenceName: seqName,
NewSequence: newSeq,
})
} else if !sequencesEqual(oldSeq, newSeq) {
diff.SequenceChanges = append(diff.SequenceChanges, &SequenceDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
SequenceName: seqName,
OldSequence: oldSeq,
NewSequence: newSeq,
})
}
}
}
func sequencesEqual(oldSeq, newSeq *metadatapb.SequenceMetadata) bool {
return oldSeq.DataType == newSeq.DataType &&
oldSeq.Start == newSeq.Start &&
oldSeq.MinValue == newSeq.MinValue &&
oldSeq.MaxValue == newSeq.MaxValue &&
oldSeq.Increment == newSeq.Increment &&
oldSeq.Cycle == newSeq.Cycle &&
oldSeq.CacheSize == newSeq.CacheSize &&
oldSeq.OwnerTable == newSeq.OwnerTable &&
oldSeq.OwnerColumn == newSeq.OwnerColumn &&
oldSeq.Comment == newSeq.Comment
}
// compareEnumTypes compares enum types between two schemas.
func compareEnumTypes(diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
oldSchemaProto := oldSchema.GetProto()
newSchemaProto := newSchema.GetProto()
// Build maps of enum types
oldEnumMap := make(map[string]*metadatapb.EnumTypeMetadata)
for _, enum := range oldSchemaProto.EnumTypes {
if !enum.GetSkipDump() {
oldEnumMap[enum.Name] = enum
}
}
newEnumMap := make(map[string]*metadatapb.EnumTypeMetadata)
for _, enum := range newSchemaProto.EnumTypes {
if !enum.GetSkipDump() {
newEnumMap[enum.Name] = enum
}
}
// Check for dropped enum types
for enumName, oldEnum := range oldEnumMap {
if _, exists := newEnumMap[enumName]; !exists {
diff.EnumTypeChanges = append(diff.EnumTypeChanges, &EnumTypeDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
EnumTypeName: enumName,
OldEnumType: oldEnum,
})
}
}
// Check for new and modified enum types
for enumName, newEnum := range newEnumMap {
if oldEnum, exists := oldEnumMap[enumName]; !exists {
diff.EnumTypeChanges = append(diff.EnumTypeChanges, &EnumTypeDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
EnumTypeName: enumName,
NewEnumType: newEnum,
})
} else if !enumValuesEqual(oldEnum.Values, newEnum.Values) {
// Enum values changed - generate ALTER diff
diff.EnumTypeChanges = append(diff.EnumTypeChanges, &EnumTypeDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
EnumTypeName: enumName,
OldEnumType: oldEnum,
NewEnumType: newEnum,
})
}
}
}
func compareCompositeTypes(diff *MetadataDiff, schemaName string, oldSchema, newSchema *model.SchemaMetadata) {
oldSchemaProto := oldSchema.GetProto()
newSchemaProto := newSchema.GetProto()
oldCompositeMap := make(map[string]*metadatapb.CompositeTypeMetadata)
for _, composite := range oldSchemaProto.CompositeTypes {
if !composite.GetSkipDump() {
oldCompositeMap[composite.Name] = composite
}
}
newCompositeMap := make(map[string]*metadatapb.CompositeTypeMetadata)
for _, composite := range newSchemaProto.CompositeTypes {
if !composite.GetSkipDump() {
newCompositeMap[composite.Name] = composite
}
}
// Check for dropped composite types
for compositeName, oldComposite := range oldCompositeMap {
if _, exists := newCompositeMap[compositeName]; !exists {
diff.CompositeTypeChanges = append(diff.CompositeTypeChanges, &CompositeTypeDiff{
Action: MetadataDiffActionDrop,
SchemaName: schemaName,
CompositeTypeName: compositeName,
OldCompositeType: oldComposite,
})
}
}
// Check for new and modified composite types
for compositeName, newComposite := range newCompositeMap {
if oldComposite, exists := oldCompositeMap[compositeName]; !exists {
diff.CompositeTypeChanges = append(diff.CompositeTypeChanges, &CompositeTypeDiff{
Action: MetadataDiffActionCreate,
SchemaName: schemaName,
CompositeTypeName: compositeName,
NewCompositeType: newComposite,
})
} else if !compositeTypesEqual(oldComposite, newComposite) {
diff.CompositeTypeChanges = append(diff.CompositeTypeChanges, &CompositeTypeDiff{
Action: MetadataDiffActionAlter,
SchemaName: schemaName,
CompositeTypeName: compositeName,
OldCompositeType: oldComposite,
NewCompositeType: newComposite,
})
}
}
}
// compositeTypesEqual checks attributes (order-sensitive) and comments.
func compositeTypesEqual(oldComposite, newComposite *metadatapb.CompositeTypeMetadata) bool {
if oldComposite.Comment != newComposite.Comment {
return false
}
if len(oldComposite.Attributes) != len(newComposite.Attributes) {
return false
}
for i, oldAttribute := range oldComposite.Attributes {
newAttribute := newComposite.Attributes[i]
if oldAttribute.Name != newAttribute.Name ||
oldAttribute.Type != newAttribute.Type ||
oldAttribute.Collation != newAttribute.Collation ||
oldAttribute.Comment != newAttribute.Comment {
return false
}
}
return true
}
// enumValuesEqual checks if two enum value slices are equal.
func enumValuesEqual(oldValues, newValues []string) bool {
if len(oldValues) != len(newValues) {
return false
}
for i, v := range oldValues {
if v != newValues[i] {
return false
}
}
return true
}
// compareExtensions compares extensions between old and new database metadata.
func compareExtensions(diff *MetadataDiff, oldMetadata, newMetadata *metadatapb.DatabaseSchemaMetadata) {
// Build maps of extensions
oldExtensionMap := make(map[string]*metadatapb.ExtensionMetadata)
for _, extension := range oldMetadata.Extensions {
oldExtensionMap[extension.Name] = extension
}
newExtensionMap := make(map[string]*metadatapb.ExtensionMetadata)
for _, extension := range newMetadata.Extensions {
newExtensionMap[extension.Name] = extension
}
// Check for dropped extensions
for extensionName, oldExtension := range oldExtensionMap {
if _, exists := newExtensionMap[extensionName]; !exists {
diff.ExtensionChanges = append(diff.ExtensionChanges, &ExtensionDiff{
Action: MetadataDiffActionDrop,
ExtensionName: extensionName,
OldExtension: oldExtension,
})
}
}
// Check for new extensions
for extensionName, newExtension := range newExtensionMap {
if _, exists := oldExtensionMap[extensionName]; !exists {
diff.ExtensionChanges = append(diff.ExtensionChanges, &ExtensionDiff{
Action: MetadataDiffActionCreate,
ExtensionName: extensionName,
NewExtension: newExtension,
})
}
}
// Check for modified extensions
for extensionName, newExtension := range newExtensionMap {
if oldExtension, exists := oldExtensionMap[extensionName]; exists {
// Check if extension has changed (schema, version, or description)
if oldExtension.Schema != newExtension.Schema ||
oldExtension.Version != newExtension.Version ||
oldExtension.Description != newExtension.Description {
// Use DROP + CREATE pattern for modifications
diff.ExtensionChanges = append(diff.ExtensionChanges, &ExtensionDiff{
Action: MetadataDiffActionDrop,
ExtensionName: extensionName,
OldExtension: oldExtension,
})
diff.ExtensionChanges = append(diff.ExtensionChanges, &ExtensionDiff{
Action: MetadataDiffActionCreate,
ExtensionName: extensionName,
NewExtension: newExtension,
})
}
}
}
}
// compareEventTriggers compares event triggers between old and new database metadata.
func compareEventTriggers(diff *MetadataDiff, oldMetadata, newMetadata *metadatapb.DatabaseSchemaMetadata) {
// Build maps of event triggers
oldEventTriggerMap := make(map[string]*metadatapb.EventTriggerMetadata)
for _, eventTrigger := range oldMetadata.EventTriggers {
oldEventTriggerMap[eventTrigger.Name] = eventTrigger
}
newEventTriggerMap := make(map[string]*metadatapb.EventTriggerMetadata)
for _, eventTrigger := range newMetadata.EventTriggers {
newEventTriggerMap[eventTrigger.Name] = eventTrigger
}
// Check for dropped event triggers
for name, oldEventTrigger := range oldEventTriggerMap {
if _, exists := newEventTriggerMap[name]; !exists {
diff.EventTriggerChanges = append(diff.EventTriggerChanges, &EventTriggerDiff{
Action: MetadataDiffActionDrop,
EventTriggerName: name,
OldEventTrigger: oldEventTrigger,
})
}
}
// Check for new event triggers
for name, newEventTrigger := range newEventTriggerMap {
if _, exists := oldEventTriggerMap[name]; !exists {
diff.EventTriggerChanges = append(diff.EventTriggerChanges, &EventTriggerDiff{
Action: MetadataDiffActionCreate,
EventTriggerName: name,
NewEventTrigger: newEventTrigger,
})
}
}
// Check for modified event triggers
for name, newEventTrigger := range newEventTriggerMap {
if oldEventTrigger, exists := oldEventTriggerMap[name]; exists {
// Check if event trigger has changed
if oldEventTrigger.Definition != newEventTrigger.Definition ||
oldEventTrigger.Event != newEventTrigger.Event ||
oldEventTrigger.FunctionSchema != newEventTrigger.FunctionSchema ||
oldEventTrigger.FunctionName != newEventTrigger.FunctionName ||
oldEventTrigger.Enabled != newEventTrigger.Enabled ||
!slices.Equal(oldEventTrigger.Tags, newEventTrigger.Tags) {
// Use DROP + CREATE pattern for modifications (no CREATE OR REPLACE for event triggers)
diff.EventTriggerChanges = append(diff.EventTriggerChanges, &EventTriggerDiff{
Action: MetadataDiffActionDrop,
EventTriggerName: name,
OldEventTrigger: oldEventTrigger,
})
diff.EventTriggerChanges = append(diff.EventTriggerChanges, &EventTriggerDiff{
Action: MetadataDiffActionCreate,
EventTriggerName: name,
NewEventTrigger: newEventTrigger,
})
}
}
}
}
// compareEvents compares events between old and new schemas.
func compareEvents(diff *MetadataDiff, _ string, oldSchema, newSchema *model.SchemaMetadata) {
oldSchemaProto := oldSchema.GetProto()
newSchemaProto := newSchema.GetProto()
// Build maps of events
oldEventMap := make(map[string]*metadatapb.EventMetadata)
for _, event := range oldSchemaProto.Events {
oldEventMap[event.Name] = event
}
newEventMap := make(map[string]*metadatapb.EventMetadata)
for _, event := range newSchemaProto.Events {
newEventMap[event.Name] = event
}
// Check for dropped events
for eventName, oldEvent := range oldEventMap {
if _, exists := newEventMap[eventName]; !exists {
diff.EventChanges = append(diff.EventChanges, &EventDiff{
Action: MetadataDiffActionDrop,
EventName: eventName,
OldEvent: oldEvent,
})
}
}
// Check for new and modified events
for eventName, newEvent := range newEventMap {
oldEvent, exists := oldEventMap[eventName]
if !exists {
diff.EventChanges = append(diff.EventChanges, &EventDiff{
Action: MetadataDiffActionCreate,
EventName: eventName,
NewEvent: newEvent,
})
} else if oldEvent.Definition != newEvent.Definition {
// Check if event has changed
diff.EventChanges = append(diff.EventChanges, &EventDiff{
Action: MetadataDiffActionAlter,
EventName: eventName,
OldEvent: oldEvent,
NewEvent: newEvent,
})
}
}
}
// FilterPostgresArchiveSchema filters out schema diff objects related to bbdataarchive schema.
func FilterPostgresArchiveSchema(diff *MetadataDiff) *MetadataDiff {
if diff == nil {
return nil
}
archiveSchemaName := common.BackupDatabaseNameOfEngine(storepb.Engine_POSTGRES)
// Create a new diff object with filtered changes
filtered := &MetadataDiff{
DatabaseName: diff.DatabaseName,
OldDatabase: diff.OldDatabase,
NewDatabase: diff.NewDatabase,
}
// Filter schema changes
for _, schemaChange := range diff.SchemaChanges {
if schemaChange.SchemaName != archiveSchemaName {
filtered.SchemaChanges = append(filtered.SchemaChanges, schemaChange)
}
}
// Filter table changes
for _, tableChange := range diff.TableChanges {
if tableChange.SchemaName != archiveSchemaName {
filtered.TableChanges = append(filtered.TableChanges, tableChange)
}
}
// Filter view changes
for _, viewChange := range diff.ViewChanges {
if viewChange.SchemaName != archiveSchemaName {
filtered.ViewChanges = append(filtered.ViewChanges, viewChange)
}
}
// Filter materialized view changes
for _, mvChange := range diff.MaterializedViewChanges {
if mvChange.SchemaName != archiveSchemaName {
filtered.MaterializedViewChanges = append(filtered.MaterializedViewChanges, mvChange)
}
}
// Filter function changes
for _, funcChange := range diff.FunctionChanges {
if funcChange.SchemaName != archiveSchemaName {
filtered.FunctionChanges = append(filtered.FunctionChanges, funcChange)
}
}
// Filter procedure changes
for _, procChange := range diff.ProcedureChanges {
if procChange.SchemaName != archiveSchemaName {
filtered.ProcedureChanges = append(filtered.ProcedureChanges, procChange)
}
}
// Filter sequence changes
for _, seqChange := range diff.SequenceChanges {
if seqChange.SchemaName != archiveSchemaName {
filtered.SequenceChanges = append(filtered.SequenceChanges, seqChange)
}
}
// Filter enum type changes
for _, enumChange := range diff.EnumTypeChanges {
if enumChange.SchemaName != archiveSchemaName {
filtered.EnumTypeChanges = append(filtered.EnumTypeChanges, enumChange)
}
}
// Filter composite type changes
for _, compositeChange := range diff.CompositeTypeChanges {
if compositeChange.SchemaName != archiveSchemaName {
filtered.CompositeTypeChanges = append(filtered.CompositeTypeChanges, compositeChange)
}
}
// Filter comment changes
for _, commentChange := range diff.CommentChanges {
if commentChange.SchemaName != archiveSchemaName {
filtered.CommentChanges = append(filtered.CommentChanges, commentChange)
}
}
// Extensions, Event triggers, and Events are database-level objects, not schema-specific, so copy them all
filtered.ExtensionChanges = diff.ExtensionChanges
filtered.EventTriggerChanges = diff.EventTriggerChanges
filtered.EventChanges = diff.EventChanges
return filtered
}
// normalizePostgreSQLFunction normalizes PostgreSQL function definitions for comparison
func normalizePostgreSQLFunction(definition string) string {
if definition == "" {
return ""
}
normalized := strings.ToLower(definition)
// Normalize whitespace
normalized = strings.ReplaceAll(normalized, "\n", " ")
normalized = strings.ReplaceAll(normalized, "\t", " ")
// Remove extra spaces
for strings.Contains(normalized, " ") {
normalized = strings.ReplaceAll(normalized, " ", " ")
}
// Normalize CREATE vs CREATE OR REPLACE
normalized = strings.ReplaceAll(normalized, "create or replace function", "create function")
normalized = strings.ReplaceAll(normalized, "create or replace procedure", "create procedure")
// Remove public schema prefix from function and procedure names
normalized = strings.ReplaceAll(normalized, "function public.", "function ")
normalized = strings.ReplaceAll(normalized, "procedure public.", "procedure ")
// Normalize parameter types
normalized = strings.ReplaceAll(normalized, "character varying", "varchar")
normalized = strings.ReplaceAll(normalized, "returns numeric", "returns decimal")
// Normalize dollar quoting - handle various dollar quote formats
normalized = strings.ReplaceAll(normalized, "$function$", "$$")
normalized = strings.ReplaceAll(normalized, "$procedure$", "$$")
// Normalize language position - move to end
if strings.Contains(normalized, "language plpgsql") && !strings.HasSuffix(strings.TrimSpace(normalized), "language plpgsql") {
withoutLanguage := strings.ReplaceAll(normalized, " language plpgsql", "")
withoutLanguage = strings.ReplaceAll(withoutLanguage, "language plpgsql ", "")
normalized = strings.TrimSpace(withoutLanguage) + " language plpgsql"
}
return strings.TrimSpace(normalized)
}
// normalizePostgreSQLCheckConstraint normalizes PostgreSQL check constraint expressions for comparison
func normalizePostgreSQLCheckConstraint(expression string) string {
// Based on the test output, we need to normalize:
// Sync: (order_date >= (CURRENT_DATE - '1 year'::interval))
// Parser: order_date >= CURRENT_DATE - INTERVAL '1 year'
// Both should normalize to the same canonical form
expression = strings.TrimSpace(expression)
// Step 1: Remove outer parentheses
for strings.HasPrefix(expression, "(") && strings.HasSuffix(expression, ")") {
// Only remove if they are balanced and wrapping the entire expression
inner := expression[1 : len(expression)-1]
parenCount := 0
canRemove := true
for _, ch := range inner {
if ch == '(' {
parenCount++
} else if ch == ')' {
parenCount--
if parenCount < 0 {
canRemove = false
break
}
}
}
if canRemove && parenCount == 0 {
expression = inner
} else {
break
}
}
// Step 2: Normalize interval syntax
// Convert ::interval to INTERVAL prefix
expression = strings.ReplaceAll(expression, "'::interval", "'")
// Step 3: Standardize to INTERVAL syntax
if strings.Contains(expression, "CURRENT_DATE") && strings.Contains(expression, "'") && !strings.Contains(expression, "INTERVAL") {
// Add INTERVAL prefix where needed
expression = strings.ReplaceAll(expression, "CURRENT_DATE - '", "CURRENT_DATE - INTERVAL '")
expression = strings.ReplaceAll(expression, "CURRENT_DATE + '", "CURRENT_DATE + INTERVAL '")
}
// Step 4: Remove extra parentheses around date arithmetic
expression = strings.ReplaceAll(expression, "(CURRENT_DATE - INTERVAL", "CURRENT_DATE - INTERVAL")
expression = strings.ReplaceAll(expression, "(CURRENT_DATE + INTERVAL", "CURRENT_DATE + INTERVAL")
// Step 5: Clean up trailing parentheses after year'
if strings.Contains(expression, "year'") && strings.Contains(expression, "INTERVAL") {
expression = strings.ReplaceAll(expression, "year')", "year'")
}
// Step 6: Normalize timestamp literals and type casts
// '2020-01-01 00:00:00'::timestamp without time zone -> '2020-01-01'::timestamp
expression = strings.ReplaceAll(expression, " 00:00:00'::timestamp without time zone", "'::timestamp")
// Step 7: Normalize numeric type casts
// >= 0::numeric -> >= 0
expression = strings.ReplaceAll(expression, "::numeric", "")
return strings.TrimSpace(expression)
}
// procedureDefinitionsEqual compares procedure definitions with normalization
func procedureDefinitionsEqual(def1, def2, _ string) bool {
if def1 == def2 {
return true
}
// Try PostgreSQL normalization first
norm1 := normalizePostgreSQLFunction(def1)
norm2 := normalizePostgreSQLFunction(def2)
if norm1 == norm2 {
return true
}
// For other engines, fall back to simple comparison
return false
}
// sortDiffLists sorts all diff lists to ensure stable output order
func sortDiffLists(diff *MetadataDiff) {
// Sort schema changes by schema name
slices.SortFunc(diff.SchemaChanges, func(a, b *SchemaDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort table changes by schema name, then table name, then action
slices.SortFunc(diff.TableChanges, func(a, b *TableDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
if a.TableName != b.TableName {
return strings.Compare(a.TableName, b.TableName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort view changes by schema name, then view name, then action
slices.SortFunc(diff.ViewChanges, func(a, b *ViewDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
if a.ViewName != b.ViewName {
return strings.Compare(a.ViewName, b.ViewName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort materialized view changes by schema name, then view name, then action
slices.SortFunc(diff.MaterializedViewChanges, func(a, b *MaterializedViewDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
if a.MaterializedViewName != b.MaterializedViewName {
return strings.Compare(a.MaterializedViewName, b.MaterializedViewName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort function changes by schema name, then function name, then action
slices.SortFunc(diff.FunctionChanges, func(a, b *FunctionDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
if a.FunctionName != b.FunctionName {
return strings.Compare(a.FunctionName, b.FunctionName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort procedure changes by schema name, then procedure name, then action
slices.SortFunc(diff.ProcedureChanges, func(a, b *ProcedureDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
if a.ProcedureName != b.ProcedureName {
return strings.Compare(a.ProcedureName, b.ProcedureName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort sequence changes by schema name, then sequence name, then action
slices.SortFunc(diff.SequenceChanges, func(a, b *SequenceDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
if a.SequenceName != b.SequenceName {
return strings.Compare(a.SequenceName, b.SequenceName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort enum type changes by schema name, then enum name, then action
slices.SortFunc(diff.EnumTypeChanges, func(a, b *EnumTypeDiff) int {
if a.SchemaName != b.SchemaName {
return strings.Compare(a.SchemaName, b.SchemaName)
}
if a.EnumTypeName != b.EnumTypeName {
return strings.Compare(a.EnumTypeName, b.EnumTypeName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort extension changes by extension name, then action
slices.SortFunc(diff.ExtensionChanges, func(a, b *ExtensionDiff) int {
if a.ExtensionName != b.ExtensionName {
return strings.Compare(a.ExtensionName, b.ExtensionName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort event trigger changes by event trigger name, then action
slices.SortFunc(diff.EventTriggerChanges, func(a, b *EventTriggerDiff) int {
if a.EventTriggerName != b.EventTriggerName {
return strings.Compare(a.EventTriggerName, b.EventTriggerName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort event changes by event name, then action
slices.SortFunc(diff.EventChanges, func(a, b *EventDiff) int {
if a.EventName != b.EventName {
return strings.Compare(a.EventName, b.EventName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort sub-object changes within table diffs
for _, tableDiff := range diff.TableChanges {
sortTableSubObjectChanges(tableDiff)
}
}
// sortTableSubObjectChanges sorts the changes within a table diff
func sortTableSubObjectChanges(tableDiff *TableDiff) {
// Sort column changes by column position (for MySQL/Oracle), then by name (for PostgreSQL/MSSQL), then action
// This provides stable sorting across different database engines
slices.SortFunc(tableDiff.ColumnChanges, func(a, b *ColumnDiff) int {
aPos := int32(0)
aName := ""
if a.NewColumn != nil {
aPos = a.NewColumn.Position
aName = a.NewColumn.Name
} else if a.OldColumn != nil {
aPos = a.OldColumn.Position
aName = a.OldColumn.Name
}
bPos := int32(0)
bName := ""
if b.NewColumn != nil {
bPos = b.NewColumn.Position
bName = b.NewColumn.Name
} else if b.OldColumn != nil {
bPos = b.OldColumn.Position
bName = b.OldColumn.Name
}
// First, sort by position if both positions are valid (> 0)
if aPos > 0 && bPos > 0 && aPos != bPos {
return int(aPos - bPos)
}
// If positions are not available or equal, sort by column name
if aName != bName {
return strings.Compare(aName, bName)
}
// Finally, sort by action for stable sorting
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort index changes by index name, then action
slices.SortFunc(tableDiff.IndexChanges, func(a, b *IndexDiff) int {
aName := ""
if a.NewIndex != nil {
aName = a.NewIndex.Name
} else if a.OldIndex != nil {
aName = a.OldIndex.Name
}
bName := ""
if b.NewIndex != nil {
bName = b.NewIndex.Name
} else if b.OldIndex != nil {
bName = b.OldIndex.Name
}
if aName != bName {
return strings.Compare(aName, bName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort foreign key changes by foreign key name, then action
slices.SortFunc(tableDiff.ForeignKeyChanges, func(a, b *ForeignKeyDiff) int {
aName := ""
if a.NewForeignKey != nil {
aName = a.NewForeignKey.Name
} else if a.OldForeignKey != nil {
aName = a.OldForeignKey.Name
}
bName := ""
if b.NewForeignKey != nil {
bName = b.NewForeignKey.Name
} else if b.OldForeignKey != nil {
bName = b.OldForeignKey.Name
}
if aName != bName {
return strings.Compare(aName, bName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort check constraint changes by constraint name, then action
slices.SortFunc(tableDiff.CheckConstraintChanges, func(a, b *CheckConstraintDiff) int {
aName := ""
if a.NewCheckConstraint != nil {
aName = a.NewCheckConstraint.Name
} else if a.OldCheckConstraint != nil {
aName = a.OldCheckConstraint.Name
}
bName := ""
if b.NewCheckConstraint != nil {
bName = b.NewCheckConstraint.Name
} else if b.OldCheckConstraint != nil {
bName = b.OldCheckConstraint.Name
}
if aName != bName {
return strings.Compare(aName, bName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort primary key changes by constraint name, then action
slices.SortFunc(tableDiff.PrimaryKeyChanges, func(a, b *PrimaryKeyDiff) int {
aName := ""
if a.NewPrimaryKey != nil {
aName = a.NewPrimaryKey.Name
} else if a.OldPrimaryKey != nil {
aName = a.OldPrimaryKey.Name
}
bName := ""
if b.NewPrimaryKey != nil {
bName = b.NewPrimaryKey.Name
} else if b.OldPrimaryKey != nil {
bName = b.OldPrimaryKey.Name
}
if aName != bName {
return strings.Compare(aName, bName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort unique constraint changes by constraint name, then action
slices.SortFunc(tableDiff.UniqueConstraintChanges, func(a, b *UniqueConstraintDiff) int {
aName := ""
if a.NewUniqueConstraint != nil {
aName = a.NewUniqueConstraint.Name
} else if a.OldUniqueConstraint != nil {
aName = a.OldUniqueConstraint.Name
}
bName := ""
if b.NewUniqueConstraint != nil {
bName = b.NewUniqueConstraint.Name
} else if b.OldUniqueConstraint != nil {
bName = b.OldUniqueConstraint.Name
}
if aName != bName {
return strings.Compare(aName, bName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort partition changes by partition name, then action
slices.SortFunc(tableDiff.PartitionChanges, func(a, b *PartitionDiff) int {
aName := ""
if a.NewPartition != nil {
aName = a.NewPartition.Name
} else if a.OldPartition != nil {
aName = a.OldPartition.Name
}
bName := ""
if b.NewPartition != nil {
bName = b.NewPartition.Name
} else if b.OldPartition != nil {
bName = b.OldPartition.Name
}
if aName != bName {
return strings.Compare(aName, bName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
// Sort trigger changes by trigger name, then action
slices.SortFunc(tableDiff.TriggerChanges, func(a, b *TriggerDiff) int {
aName := ""
if a.NewTrigger != nil {
aName = a.NewTrigger.Name
} else if a.OldTrigger != nil {
aName = a.OldTrigger.Name
}
bName := ""
if b.NewTrigger != nil {
bName = b.NewTrigger.Name
} else if b.OldTrigger != nil {
bName = b.OldTrigger.Name
}
if aName != bName {
return strings.Compare(aName, bName)
}
return strings.Compare(string(a.Action), string(b.Action))
})
}