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 oceanbase
import (
"fmt"
"maps"
"slices"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
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"
)
// tableDrop holds drop operations for deduplication
type tableDrop struct {
checkConstraints map[string]string
indexes map[string]*metadatapb.IndexMetadata
columns map[string]string
}
func init() {
schema.RegisterGenerateMigration(storepb.Engine_OCEANBASE, generateMigration)
}
func generateMigration(diff *schema.MetadataDiff) (string, error) {
var buf strings.Builder
// MySQL doesn't have schemas like PostgreSQL, so we skip schema-level changes
// We'll focus on table-level changes
// Phase 1: Drop dependent objects first
if err := dropObjectsInOrder(diff, &buf); err != nil {
return "", err
}
// 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
if err := createObjectsInOrder(diff, &buf); err != nil {
return "", err
}
return buf.String(), nil
}
// dropObjectsInOrder drops all objects in the correct order
func dropObjectsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) error {
// Drop triggers first (they depend on tables)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop && tableDiff.OldTable != nil {
for _, trigger := range tableDiff.OldTable.Triggers {
if err := writeDropTrigger(buf, trigger.Name); err != nil {
return err
}
}
} else if tableDiff.Action == schema.MetadataDiffActionAlter {
// Drop triggers that are being removed from altered tables
for _, triggerDiff := range tableDiff.TriggerChanges {
if triggerDiff.Action == schema.MetadataDiffActionDrop {
if err := writeDropTrigger(buf, triggerDiff.OldTrigger.Name); err != nil {
return err
}
}
}
}
}
// Drop foreign keys from tables being altered
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionDrop {
if err := writeDropForeignKey(buf, tableDiff.TableName, fkDiff.OldForeignKey.Name); err != nil {
return err
}
}
}
}
}
// Create temporary views for views being dropped to handle dependencies
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop && viewDiff.OldView != nil {
if err := writeTemporaryViewForDrop(buf, viewDiff.ViewName, viewDiff.OldView); err != nil {
return err
}
}
}
// Drop events first (they can reference tables/views)
for _, eventDiff := range diff.EventChanges {
if eventDiff.Action == schema.MetadataDiffActionDrop {
if err := writeDropEvent(buf, eventDiff.EventName); err != nil {
return err
}
}
}
// Drop views
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop {
if err := writeDropView(buf, viewDiff.ViewName); err != nil {
return err
}
}
}
// Drop procedures
for _, procDiff := range diff.ProcedureChanges {
if procDiff.Action == schema.MetadataDiffActionDrop {
if err := writeDropProcedure(buf, procDiff.ProcedureName); err != nil {
return err
}
}
}
// Drop functions
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionDrop {
if err := writeDropFunction(buf, funcDiff.FunctionName); err != nil {
return err
}
}
}
// Drop tables in topologically sorted order (respecting foreign key dependencies)
if err := dropTablesInDependencyOrder(diff, buf); err != nil {
return err
}
// Handle ALTER table drops (constraints, indexes, columns)
// Collect all drop operations by table to avoid duplicates
dropsPerTable := make(map[string]*tableDrop)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
tableName := tableDiff.TableName
if _, exists := dropsPerTable[tableName]; !exists {
dropsPerTable[tableName] = &tableDrop{
checkConstraints: make(map[string]string),
indexes: make(map[string]*metadatapb.IndexMetadata),
columns: make(map[string]string),
}
}
// Collect check constraints to drop
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionDrop {
dropsPerTable[tableName].checkConstraints[checkDiff.OldCheckConstraint.Name] = checkDiff.OldCheckConstraint.Name
}
}
// Collect indexes to drop
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionDrop {
dropsPerTable[tableName].indexes[indexDiff.OldIndex.Name] = indexDiff.OldIndex
}
}
// Collect columns to drop
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionDrop {
dropsPerTable[tableName].columns[colDiff.OldColumn.Name] = colDiff.OldColumn.Name
}
}
}
}
// Execute the deduplicated drop operations. Every map here is walked in name
// order: the same diff has to produce the same DDL, byte for byte, or a
// generated migration is unreviewable and unrepeatable.
for _, tableName := range slices.Sorted(maps.Keys(dropsPerTable)) {
drops := dropsPerTable[tableName]
// Drop check constraints
for _, constraintName := range slices.Sorted(maps.Keys(drops.checkConstraints)) {
if err := writeDropCheckConstraint(buf, tableName, constraintName); err != nil {
return err
}
}
// Drop indexes (handle primary key specially)
for _, indexName := range slices.Sorted(maps.Keys(drops.indexes)) {
index := drops.indexes[indexName]
if index.Primary {
if err := writeDropPrimaryKey(buf, tableName); err != nil {
return err
}
} else {
if err := writeDropIndex(buf, tableName, index.Name); err != nil {
return err
}
}
}
// Drop columns
for _, columnName := range slices.Sorted(maps.Keys(drops.columns)) {
if err := writeDropColumn(buf, tableName, columnName); err != nil {
return err
}
}
}
return nil
}
// dropTablesInDependencyOrder drops tables in the correct order based on foreign key dependencies
func dropTablesInDependencyOrder(diff *schema.MetadataDiff, buf *strings.Builder) error {
// Build a graph of table dependencies
graph := parserbase.NewGraph()
tablesToDrop := make(map[string]*schema.TableDiff)
// Add all tables to be dropped to the graph
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop {
graph.AddNode(tableDiff.TableName)
tablesToDrop[tableDiff.TableName] = tableDiff
}
}
// Add edges for foreign key dependencies
// Edge from table with FK to referenced table (for dropping order)
for _, name := range slices.Sorted(maps.Keys(tablesToDrop)) {
tableDiff := tablesToDrop[name]
if tableDiff.OldTable != nil {
for _, fk := range tableDiff.OldTable.ForeignKeys {
if _, exists := tablesToDrop[fk.ReferencedTable]; exists {
// Add edge from this table to referenced table
// This ensures this table is dropped before referenced table
graph.AddEdge(tableDiff.TableName, fk.ReferencedTable)
}
}
}
}
// Get topological order
orderedTables, err := graph.TopologicalSort()
if err != nil {
// If there's a cycle, fall back to dropping foreign keys first
// Drop all foreign keys from tables being dropped
for _, name := range slices.Sorted(maps.Keys(tablesToDrop)) {
tableDiff := tablesToDrop[name]
if tableDiff.OldTable != nil {
for _, fk := range tableDiff.OldTable.ForeignKeys {
if err := writeDropForeignKey(buf, tableDiff.TableName, fk.Name); err != nil {
return err
}
}
}
}
// Then drop tables in name order; the cycle leaves no better one.
for _, name := range slices.Sorted(maps.Keys(tablesToDrop)) {
if err := writeDropTable(buf, tablesToDrop[name].TableName); err != nil {
return err
}
}
} else {
// Drop tables in topological order
for _, tableName := range orderedTables {
if err := writeDropTable(buf, tableName); err != nil {
return err
}
}
}
return nil
}
// createObjectsInOrder creates all objects in the correct order
func createObjectsInOrder(diff *schema.MetadataDiff, buf *strings.Builder) error {
// Create dummy views first for all new views to handle dependencies
// This must be done before creating tables as triggers might reference views
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate && viewDiff.NewView != nil {
if err := writeCreateTemporaryView(buf, viewDiff.ViewName, viewDiff.NewView); err != nil {
return err
}
}
}
// Create tables (without foreign keys first)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate {
if err := writeCreateTableWithoutForeignKeys(buf, tableDiff.TableName, tableDiff.NewTable); err != nil {
return err
}
}
}
// Add foreign keys after all tables are created
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate && tableDiff.NewTable != nil {
for _, fk := range tableDiff.NewTable.ForeignKeys {
if err := writeAddForeignKey(buf, tableDiff.TableName, fk); err != nil {
return err
}
}
}
}
// Handle ALTER table operations
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
if err := generateAlterTable(tableDiff, buf); err != nil {
return err
}
}
}
// Create or replace views with actual definitions
for _, viewDiff := range diff.ViewChanges {
switch viewDiff.Action {
case schema.MetadataDiffActionCreate:
// Use CREATE OR REPLACE since we already have a dummy view
if err := writeCreateOrReplaceView(buf, viewDiff.ViewName, viewDiff.NewView); err != nil {
return err
}
case schema.MetadataDiffActionAlter:
// MySQL requires CREATE OR REPLACE for views
if err := writeCreateOrReplaceView(buf, viewDiff.ViewName, viewDiff.NewView); err != nil {
return err
}
default:
// No action needed for other diff actions
}
}
// Create functions
for _, funcDiff := range diff.FunctionChanges {
if funcDiff.Action == schema.MetadataDiffActionCreate || funcDiff.Action == schema.MetadataDiffActionAlter {
if err := writeFunctionDiff(buf, funcDiff); err != nil {
return err
}
}
}
// Create procedures
for _, procDiff := range diff.ProcedureChanges {
if procDiff.Action == schema.MetadataDiffActionCreate || procDiff.Action == schema.MetadataDiffActionAlter {
if err := writeProcedureDiff(buf, procDiff); err != nil {
return err
}
}
}
// Create events
for _, eventDiff := range diff.EventChanges {
if eventDiff.Action == schema.MetadataDiffActionCreate || eventDiff.Action == schema.MetadataDiffActionAlter {
if err := writeEventDiff(buf, eventDiff); err != nil {
return err
}
}
}
return nil
}
func generateAlterTable(tableDiff *schema.TableDiff, buf *strings.Builder) error {
// Add columns first
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionCreate {
if err := writeAddColumn(buf, tableDiff.TableName, colDiff.NewColumn); err != nil {
return err
}
}
}
// Modify columns
for _, colDiff := range tableDiff.ColumnChanges {
if colDiff.Action == schema.MetadataDiffActionAlter {
if err := writeModifyColumn(buf, tableDiff.TableName, colDiff.NewColumn); err != nil {
return err
}
}
}
// Add indexes
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionCreate {
if indexDiff.NewIndex.Primary {
if err := writeAddPrimaryKey(buf, tableDiff.TableName, indexDiff.NewIndex); err != nil {
return err
}
} else if indexDiff.NewIndex.Unique {
if err := writeAddUniqueKey(buf, tableDiff.TableName, indexDiff.NewIndex); err != nil {
return err
}
} else {
if err := writeCreateIndex(buf, tableDiff.TableName, indexDiff.NewIndex); err != nil {
return err
}
}
}
}
// Add check constraints
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionCreate {
if err := writeAddCheckConstraint(buf, tableDiff.TableName, checkDiff.NewCheckConstraint); err != nil {
return err
}
}
}
// Add foreign keys last
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionCreate {
if err := writeAddForeignKey(buf, tableDiff.TableName, fkDiff.NewForeignKey); err != nil {
return err
}
}
}
// Add triggers last
for _, triggerDiff := range tableDiff.TriggerChanges {
if triggerDiff.Action == schema.MetadataDiffActionCreate {
if err := writeCreateTrigger(buf, tableDiff.TableName, triggerDiff.NewTrigger); err != nil {
return err
}
}
}
// Handle table comment changes
if tableDiff.OldTable != nil && tableDiff.NewTable != nil &&
tableDiff.OldTable.Comment != tableDiff.NewTable.Comment {
if err := writeAlterTableComment(buf, tableDiff.TableName, tableDiff.NewTable.Comment); err != nil {
return err
}
}
return nil
}
// Write functions for various DDL statements
func writeDropTrigger(buf *strings.Builder, trigger string) error {
_, _ = buf.WriteString("DROP TRIGGER IF EXISTS `")
_, _ = buf.WriteString(trigger)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropForeignKey(buf *strings.Builder, table, constraint string) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` DROP FOREIGN KEY `")
_, _ = buf.WriteString(constraint)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropView(buf *strings.Builder, view string) error {
_, _ = buf.WriteString("DROP VIEW IF EXISTS `")
_, _ = buf.WriteString(view)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropProcedure(buf *strings.Builder, procedure string) error {
_, _ = buf.WriteString("DROP PROCEDURE IF EXISTS `")
_, _ = buf.WriteString(procedure)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropFunction(buf *strings.Builder, function string) error {
_, _ = buf.WriteString("DROP FUNCTION IF EXISTS `")
_, _ = buf.WriteString(function)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropEvent(buf *strings.Builder, event string) error {
_, _ = buf.WriteString("DROP EVENT IF EXISTS `")
_, _ = buf.WriteString(event)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropTable(buf *strings.Builder, table string) error {
_, _ = buf.WriteString("DROP TABLE IF EXISTS `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropCheckConstraint(buf *strings.Builder, table, constraint string) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` DROP CONSTRAINT `")
_, _ = buf.WriteString(constraint)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropIndex(buf *strings.Builder, table, index string) error {
_, _ = buf.WriteString("DROP INDEX `")
_, _ = buf.WriteString(index)
_, _ = buf.WriteString("` ON `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeDropPrimaryKey(buf *strings.Builder, table string) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` DROP PRIMARY KEY;\n\n")
return nil
}
func writeDropColumn(buf *strings.Builder, table, column string) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` DROP COLUMN `")
_, _ = buf.WriteString(column)
_, _ = buf.WriteString("`;\n\n")
return nil
}
func writeCreateTableWithoutForeignKeys(buf *strings.Builder, tableName string, table *metadatapb.TableMetadata) error {
_, _ = buf.WriteString("CREATE TABLE IF NOT EXISTS `")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("` (\n")
// Write columns
for i, col := range table.Columns {
if i > 0 {
_, _ = buf.WriteString(",\n")
}
_, _ = buf.WriteString(" `")
_, _ = buf.WriteString(col.Name)
_, _ = buf.WriteString("` ")
// Reuse the shared column-definition body so the inline CREATE TABLE and the
// ADD/MODIFY COLUMN paths emit the same canonical attribute order (notably the
// generated-column ordering: GENERATED ALWAYS AS (...) precedes NOT NULL/SRID).
writeColumnDefinitionBody(buf, col)
}
writeSystemTimePeriod(buf, table)
// Write primary key constraint inline if exists
for _, index := range table.Indexes {
if index.Primary {
_, _ = buf.WriteString(",\n PRIMARY KEY (")
for i, expr := range index.Expressions {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("`")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("`")
}
_, _ = buf.WriteString(")")
break
}
}
// Write unique constraints inline
for _, index := range table.Indexes {
if index.Unique && !index.Primary {
_, _ = buf.WriteString(",\n UNIQUE KEY `")
_, _ = buf.WriteString(index.Name)
_, _ = buf.WriteString("` (")
for i, expr := range index.Expressions {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("`")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("`")
}
_, _ = buf.WriteString(")")
}
}
// Write check constraints
for _, check := range table.CheckConstraints {
_, _ = buf.WriteString(",\n CONSTRAINT `")
_, _ = buf.WriteString(check.Name)
_, _ = buf.WriteString("` CHECK (")
_, _ = buf.WriteString(check.Expression)
_, _ = buf.WriteString(")")
}
_, _ = buf.WriteString("\n)")
// Write table options
if table.Engine != "" {
_, _ = buf.WriteString(" ENGINE=")
_, _ = buf.WriteString(table.Engine)
}
if table.Charset != "" {
_, _ = buf.WriteString(" DEFAULT CHARSET=")
_, _ = buf.WriteString(table.Charset)
}
if table.Collation != "" {
_, _ = buf.WriteString(" COLLATE=")
_, _ = buf.WriteString(table.Collation)
}
if table.Comment != "" {
_, _ = buf.WriteString(" COMMENT='")
_, _ = buf.WriteString(table.Comment)
_, _ = buf.WriteString("'")
}
writeSystemVersioningOption(buf, table)
// Partitioning is part of the table definition, so it has to be emitted here: MySQL
// cannot partition a table after the fact without rewriting it. printPartitionClause
// writes a self-contained /*!50100 ... */ block, matching the schema dump.
if len(table.Partitions) > 0 {
if err := printPartitionClause(buf, table.Partitions, table.Engine); err != nil {
return err
}
}
_, _ = buf.WriteString(";\n")
// Create non-unique indexes separately
for _, index := range table.Indexes {
if !index.Primary && !index.Unique {
if err := writeCreateIndex(buf, tableName, index); err != nil {
return err
}
}
}
// Add extra newline after table creation for readability
_, _ = buf.WriteString("\n")
// Note: Foreign keys are NOT created here - they will be added separately
return nil
}
// writeColumnDefinitionBody emits the column definition following the leading
// “ `name` “ — the type and every attribute in canonical order. MySQL's grammar puts
// the attributes in a different order for a generated column (the GENERATED ALWAYS AS
// clause precedes NOT NULL and SRID) than for a regular column, so the body branches on
// column.Generation. Both branches emit INVISIBLE before COMMENT, matching MySQL's
// canonical SHOW CREATE order. Shared by writeAddColumn (ADD COLUMN) and writeModifyColumn
// (MODIFY COLUMN), which differ only in the leading clause.
func writeColumnDefinitionBody(buf *strings.Builder, column *metadatapb.ColumnMetadata) {
_, _ = buf.WriteString(column.Type)
if column.CharacterSet != "" {
_, _ = buf.WriteString(" CHARACTER SET ")
_, _ = buf.WriteString(column.CharacterSet)
}
if column.Collation != "" {
_, _ = buf.WriteString(" COLLATE ")
_, _ = buf.WriteString(column.Collation)
}
if column.Generation != nil && column.Generation.Expression != "" {
writeGeneratedColumnAttributes(buf, column)
return
}
if !column.Nullable {
_, _ = buf.WriteString(" NOT NULL")
}
// Spatial SRID (MySQL 8.0): after NOT NULL, before DEFAULT — the SDL dumper's
// canonical position (see printColumnClause).
writeColumnSRIDAttribute(buf, column)
// Handle AUTO_INCREMENT before default value
if hasAutoIncrement(column) {
_, _ = buf.WriteString(" AUTO_INCREMENT")
} else if hasDefaultValue(column) && !hasAutoIncrement(column) {
_, _ = buf.WriteString(" DEFAULT ")
_, _ = buf.WriteString(getDefaultExpression(column))
}
// Handle ON UPDATE
if column.OnUpdate != "" {
_, _ = buf.WriteString(" ON UPDATE ")
_, _ = buf.WriteString(column.OnUpdate)
}
// INVISIBLE column (MySQL 8.0.23+): before COMMENT, matching the SHOW CREATE canonical
// order and the SDL dumper (printColumnClause).
writeColumnInvisibleAttribute(buf, column)
if column.Comment != "" {
_, _ = buf.WriteString(" COMMENT '")
_, _ = buf.WriteString(column.Comment)
_, _ = buf.WriteString("'")
}
}
// writeGeneratedColumnAttributes emits the attributes of a generated column (type and
// charset/collation already written) in MySQL's canonical order:
//
// GENERATED ALWAYS AS (expr) STORED|VIRTUAL [NOT NULL] [SRID] [INVISIBLE] [COMMENT]
//
// The GENERATED ALWAYS AS clause must precede NOT NULL and the SRID attribute — the
// order the current MySQL grammar accepts and that SHOW CREATE emits (verified against
// 8.0.32). INVISIBLE precedes COMMENT, matching MySQL's canonical order for both regular
// and generated columns. Generated columns never carry DEFAULT / AUTO_INCREMENT / ON
// UPDATE, so those clauses are intentionally omitted.
func writeGeneratedColumnAttributes(buf *strings.Builder, column *metadatapb.ColumnMetadata) {
if isSystemTimeColumn(column) {
writeSystemTimeColumnAttribute(buf, column)
} else {
_, _ = buf.WriteString(" GENERATED ALWAYS AS (")
_, _ = buf.WriteString(column.Generation.Expression)
_, _ = buf.WriteString(") ")
switch column.Generation.Type {
case metadatapb.GenerationMetadata_TYPE_STORED:
_, _ = buf.WriteString("STORED")
case metadatapb.GenerationMetadata_TYPE_VIRTUAL:
_, _ = buf.WriteString("VIRTUAL")
default:
// Default to VIRTUAL for unknown types
_, _ = buf.WriteString("VIRTUAL")
}
}
if !column.Nullable {
_, _ = buf.WriteString(" NOT NULL")
}
// Spatial SRID (MySQL 8.0): after NOT NULL for a generated column, matching the
// SHOW CREATE canonical position.
writeColumnSRIDAttribute(buf, column)
// INVISIBLE column (MySQL 8.0.23+): for a generated column this precedes COMMENT,
// matching the SHOW CREATE canonical order.
writeColumnInvisibleAttribute(buf, column)
if column.Comment != "" {
_, _ = buf.WriteString(" COMMENT '")
_, _ = buf.WriteString(column.Comment)
_, _ = buf.WriteString("'")
}
}
func writeAddColumn(buf *strings.Builder, table string, column *metadatapb.ColumnMetadata) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` ADD COLUMN `")
_, _ = buf.WriteString(column.Name)
_, _ = buf.WriteString("` ")
writeColumnDefinitionBody(buf, column)
// TODO: Add column position support (FIRST, AFTER column_name)
_, _ = buf.WriteString(";\n\n")
return nil
}
func writeModifyColumn(buf *strings.Builder, table string, column *metadatapb.ColumnMetadata) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` MODIFY COLUMN `")
_, _ = buf.WriteString(column.Name)
_, _ = buf.WriteString("` ")
writeColumnDefinitionBody(buf, column)
_, _ = buf.WriteString(";\n\n")
return nil
}
func writeCreateIndex(buf *strings.Builder, table string, index *metadatapb.IndexMetadata) error {
_, _ = buf.WriteString("CREATE ")
// Handle special index types
if strings.ToUpper(index.Type) == "FULLTEXT" {
_, _ = buf.WriteString("FULLTEXT ")
} else if strings.ToUpper(index.Type) == "SPATIAL" {
_, _ = buf.WriteString("SPATIAL ")
}
_, _ = buf.WriteString("INDEX `")
_, _ = buf.WriteString(index.Name)
_, _ = buf.WriteString("` ON `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` (")
for i, expr := range index.Expressions {
if i > 0 {
_, _ = buf.WriteString(", ")
}
// Check if this is a functional expression or a column name
// Functional expressions start with ( and end with )
if strings.HasPrefix(expr, "(") && strings.HasSuffix(expr, ")") {
// This is a functional expression, unescape quotes and write it as-is
unescapedExpr := strings.ReplaceAll(expr, "\\'", "'")
_, _ = buf.WriteString(unescapedExpr)
} else {
// This is a column name, wrap it in backticks
_, _ = buf.WriteString("`")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("`")
}
// Handle column length for text/blob columns
// SPATIAL indexes do not support key lengths, and functional expressions don't use key lengths
if i < len(index.KeyLength) && index.KeyLength[i] > 0 && strings.ToUpper(index.Type) != "SPATIAL" &&
(!strings.HasPrefix(expr, "(") || !strings.HasSuffix(expr, ")")) {
_, _ = fmt.Fprintf(buf, "(%d)", index.KeyLength[i])
}
}
_, _ = buf.WriteString(")")
// Add index type if specified and not default
if index.Type != "" && index.Type != "BTREE" &&
strings.ToUpper(index.Type) != "FULLTEXT" &&
strings.ToUpper(index.Type) != "SPATIAL" {
_, _ = buf.WriteString(" USING ")
_, _ = buf.WriteString(index.Type)
}
if index.Comment != "" {
_, _ = buf.WriteString(" COMMENT '")
_, _ = buf.WriteString(index.Comment)
_, _ = buf.WriteString("'")
}
_, _ = buf.WriteString(";\n\n")
return nil
}
func writeAddPrimaryKey(buf *strings.Builder, table string, index *metadatapb.IndexMetadata) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` ADD PRIMARY KEY (")
for i, expr := range index.Expressions {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("`")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("`")
}
_, _ = buf.WriteString(");\n\n")
return nil
}
func writeAddUniqueKey(buf *strings.Builder, table string, index *metadatapb.IndexMetadata) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` ADD UNIQUE KEY `")
_, _ = buf.WriteString(index.Name)
_, _ = buf.WriteString("` (")
for i, expr := range index.Expressions {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("`")
_, _ = buf.WriteString(expr)
_, _ = buf.WriteString("`")
}
_, _ = buf.WriteString(");\n\n")
return nil
}
func writeAddCheckConstraint(buf *strings.Builder, table string, check *metadatapb.CheckConstraintMetadata) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` ADD CONSTRAINT `")
_, _ = buf.WriteString(check.Name)
_, _ = buf.WriteString("` CHECK ")
// The expression already includes parentheses from the parser
_, _ = buf.WriteString(check.Expression)
_, _ = buf.WriteString(";\n\n")
return nil
}
func writeAddForeignKey(buf *strings.Builder, table string, fk *metadatapb.ForeignKeyMetadata) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(table)
_, _ = buf.WriteString("` ADD CONSTRAINT `")
_, _ = buf.WriteString(fk.Name)
_, _ = buf.WriteString("` FOREIGN KEY (")
for i, col := range fk.Columns {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("`")
_, _ = buf.WriteString(col)
_, _ = buf.WriteString("`")
}
_, _ = buf.WriteString(") REFERENCES `")
_, _ = buf.WriteString(fk.ReferencedTable)
_, _ = buf.WriteString("` (")
for i, col := range fk.ReferencedColumns {
if i > 0 {
_, _ = buf.WriteString(", ")
}
_, _ = buf.WriteString("`")
_, _ = buf.WriteString(col)
_, _ = buf.WriteString("`")
}
_, _ = buf.WriteString(")")
if fk.OnUpdate != "" {
_, _ = buf.WriteString(" ON UPDATE ")
_, _ = buf.WriteString(fk.OnUpdate)
}
if fk.OnDelete != "" {
_, _ = buf.WriteString(" ON DELETE ")
_, _ = buf.WriteString(fk.OnDelete)
}
_, _ = buf.WriteString(";\n\n")
return nil
}
func writeCreateOrReplaceView(buf *strings.Builder, viewName string, view *metadatapb.ViewMetadata) error {
_, _ = buf.WriteString("CREATE OR REPLACE VIEW `")
_, _ = buf.WriteString(viewName)
_, _ = buf.WriteString("` AS ")
_, _ = buf.WriteString(view.Definition)
if !strings.HasSuffix(strings.TrimSpace(view.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\n\n")
// Note: MySQL doesn't support adding comments directly to views via DDL
// View comments are stored in information_schema.VIEWS but cannot be set via CREATE VIEW
// The comment field is read-only
return nil
}
func writeFunctionDiff(buf *strings.Builder, funcDiff *schema.FunctionDiff) error {
if funcDiff.Action == schema.MetadataDiffActionCreate || funcDiff.Action == schema.MetadataDiffActionAlter {
// Don't add DELIMITER statements - the definition should already be complete
_, _ = buf.WriteString(funcDiff.NewFunction.Definition)
if !strings.HasSuffix(strings.TrimSpace(funcDiff.NewFunction.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\n\n")
// Note: MySQL doesn't support ALTER FUNCTION ... COMMENT syntax
// Function comments must be set during CREATE FUNCTION statement
// The definition should already include the comment if needed
}
return nil
}
func writeProcedureDiff(buf *strings.Builder, procDiff *schema.ProcedureDiff) error {
if procDiff.Action == schema.MetadataDiffActionCreate || procDiff.Action == schema.MetadataDiffActionAlter {
// Don't add DELIMITER statements - the definition should already be complete
_, _ = buf.WriteString(procDiff.NewProcedure.Definition)
if !strings.HasSuffix(strings.TrimSpace(procDiff.NewProcedure.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\n\n")
// Note: MySQL doesn't support ALTER PROCEDURE ... COMMENT syntax
// Procedure comments must be set during CREATE PROCEDURE statement
// The definition should already include the comment if needed
}
return nil
}
func writeEventDiff(buf *strings.Builder, eventDiff *schema.EventDiff) error {
if eventDiff.Action == schema.MetadataDiffActionCreate || eventDiff.Action == schema.MetadataDiffActionAlter {
// An event synced without its CREATE EVENT, as on OceanBase, has nothing faithful to write.
if eventDiff.NewEvent.Definition == "" {
return nil
}
// Write the event definition
_, _ = buf.WriteString(eventDiff.NewEvent.Definition)
if !strings.HasSuffix(strings.TrimSpace(eventDiff.NewEvent.Definition), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\n\n")
// Note: MySQL doesn't support ALTER EVENT ... COMMENT syntax
// Event comments must be set during CREATE EVENT statement
// The definition should already include the comment if needed
}
return nil
}
func writeAlterTableComment(buf *strings.Builder, tableName, comment string) error {
_, _ = buf.WriteString("ALTER TABLE `")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("` COMMENT = '")
_, _ = buf.WriteString(comment)
_, _ = buf.WriteString("';\n\n")
return nil
}
// Helper functions
func hasCreateOrAlterObjects(diff *schema.MetadataDiff) bool {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate || tableDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.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
}
}
for _, eventDiff := range diff.EventChanges {
if eventDiff.Action == schema.MetadataDiffActionCreate || eventDiff.Action == schema.MetadataDiffActionAlter {
return true
}
}
return false
}
func getDefaultExpression(column *metadatapb.ColumnMetadata) string {
if column == nil {
return ""
}
// Use the unified Default field
if column.Default != "" {
return column.Default
}
return ""
}
func hasDefaultValue(column *metadatapb.ColumnMetadata) bool {
if column == nil {
return false
}
// Don't treat AUTO_INCREMENT as a default value
if strings.EqualFold(column.GetDefault(), "AUTO_INCREMENT") {
return false
}
return column.Default != ""
}
func hasAutoIncrement(column *metadatapb.ColumnMetadata) bool {
if column == nil {
return false
}
// Check if column has AUTO_INCREMENT in default field
return strings.EqualFold(column.GetDefault(), "AUTO_INCREMENT")
}
func writeTemporaryViewForDrop(buf *strings.Builder, viewName string, view *metadatapb.ViewMetadata) error {
// Create a temporary view with SELECT 1 AS column_name structure
// to satisfy other views that depend on this view
_, _ = buf.WriteString("CREATE OR REPLACE VIEW `")
_, _ = buf.WriteString(viewName)
_, _ = buf.WriteString("` AS SELECT")
for i, column := range view.Columns {
if i > 0 {
_, _ = buf.WriteString(",")
}
_, _ = buf.WriteString(" 1 AS `")
_, _ = buf.WriteString(column.Name)
_, _ = buf.WriteString("`")
}
// If no columns, create a dummy view
if len(view.Columns) == 0 {
_, _ = buf.WriteString(" 1")
}
_, _ = buf.WriteString(";\n\n")
return nil
}
func writeCreateTemporaryView(buf *strings.Builder, viewName string, view *metadatapb.ViewMetadata) error {
// Create a temporary view with SELECT 1 AS column_name structure
// to satisfy views that depend on this view before we create the real one
_, _ = buf.WriteString("CREATE VIEW `")
_, _ = buf.WriteString(viewName)
_, _ = buf.WriteString("` AS SELECT")
for i, column := range view.Columns {
if i > 0 {
_, _ = buf.WriteString(",")
}
_, _ = buf.WriteString(" 1 AS `")
_, _ = buf.WriteString(column.Name)
_, _ = buf.WriteString("`")
}
// If no columns, create a dummy view
if len(view.Columns) == 0 {
_, _ = buf.WriteString(" 1")
}
_, _ = buf.WriteString(";\n\n")
return nil
}
func writeCreateTrigger(buf *strings.Builder, tableName string, trigger *metadatapb.TriggerMetadata) error {
// Don't add DELIMITER statements - construct the complete trigger statement
_, _ = buf.WriteString("CREATE TRIGGER `")
_, _ = buf.WriteString(trigger.Name)
_, _ = buf.WriteString("` ")
_, _ = buf.WriteString(trigger.Timing)
_, _ = buf.WriteString(" ")
_, _ = buf.WriteString(trigger.Event)
_, _ = buf.WriteString(" ON `")
_, _ = buf.WriteString(tableName)
_, _ = buf.WriteString("` FOR EACH ROW ")
_, _ = buf.WriteString(trigger.Body)
if !strings.HasSuffix(strings.TrimSpace(trigger.Body), ";") {
_, _ = buf.WriteString(";")
}
_, _ = buf.WriteString("\n\n")
// Note: MySQL doesn't support specifying comments in CREATE TRIGGER syntax
// Trigger comments are retrieved from INFORMATION_SCHEMA.TRIGGERS but cannot be set via DDL
// The comment field is read-only and set by MySQL itself
return nil
}