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 mysql
import (
"cmp"
"fmt"
"io"
"slices"
"strconv"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/pkg/errors"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/schema"
)
const (
disableUniqueAndForeignKeyCheckStmt = "SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;\nSET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n"
restoreUniqueAndForeignKeyCheckStmt = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\nSET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;\n"
emptyCommentLine = "--\n"
tempViewHeader = "-- Temporary view structure for "
tableHeader = "-- Table structure for "
viewHeader = "-- View structure for "
functionHeader = "-- Function structure for "
procedureHeader = "-- Procedure structure for "
triggerHeader = "-- Trigger structure for "
eventHeader = "-- Event structure for "
setCharacterSetClient = "SET character_set_client = "
setCharacterSetResult = "SET character_set_results = "
setCollation = "SET collation_connection = "
setSQLMode = "SET sql_mode = "
setTimezone = "SET time_zone = "
delimiterDoubleSemi = "DELIMITER ;;\n"
delimiterSemi = "DELIMITER ;\n"
mysqlTypeBlob = "blob"
mysqlTypeTinyBob = "tinyblob"
mysqlTypeMediumBlob = "mediumblob"
mysqlTypeLongBlob = "longblob"
mysqlTypeJSON = "json"
mysqlTypeGeometry = "geometry"
mysqlIndexFullText = "FULLTEXT"
mysqlIndexSpatial = "SPATIAL"
mysqlNoAction = "NO ACTION"
autoIncrementSymbol = "AUTO_INCREMENT"
)
func init() {
schema.RegisterGetDatabaseDefinition(storepb.Engine_MYSQL, GetDatabaseDefinition)
schema.RegisterGetTableDefinition(storepb.Engine_MYSQL, GetTableDefinition)
schema.RegisterGetViewDefinition(storepb.Engine_MYSQL, GetViewDefinition)
schema.RegisterGetFunctionDefinition(storepb.Engine_MYSQL, GetFunctionDefinition)
schema.RegisterGetProcedureDefinition(storepb.Engine_MYSQL, GetProcedureDefinition)
}
func GetDatabaseDefinition(ctx schema.GetDefinitionContext, metadata *metadatapb.DatabaseSchemaMetadata) (string, error) {
if len(metadata.Schemas) == 0 {
return "", nil
}
if ctx.SDLFormat {
return getSDLFormat(metadata)
}
var buf strings.Builder
if ctx.PrintHeader {
if _, err := buf.WriteString(disableUniqueAndForeignKeyCheckStmt); err != nil {
return "", err
}
}
schema := metadata.Schemas[0]
// Construct temporal views.
// Create a temporary view with the same name as the view and with columns of
// the same name in order to satisfy views that depend on this view.
// This temporary view will be removed when the actual view is created.
// The properties of each column, are not preserved in this temporary
// view. They are not necessary because other views only need to reference
// the column name, thus we generate SELECT 1 AS colName1, 1 AS colName2.
// This will not be necessary once we can determine dependencies
// between views and can simply dump them in the appropriate order.
// https://sourcegraph.com/github.com/mysql/mysql-server/-/blob/client/mysqldump.cc?L2781
for _, view := range schema.Views {
if len(view.Columns) == 0 {
if err := writeInvalidTemporaryView(&buf, view); err != nil {
return "", err
}
continue
}
if err := writeTemporaryView(&buf, view); err != nil {
return "", err
}
}
// Construct tables.
for _, table := range schema.Tables {
if err := writeTable(&buf, table); err != nil {
return "", err
}
}
// Construct views.
for _, view := range schema.Views {
if err := writeView(&buf, view); err != nil {
return "", err
}
}
// Construct functions.
for _, function := range schema.Functions {
if err := writeFunction(&buf, function); err != nil {
return "", err
}
}
// Construct procedures.
for _, procedure := range schema.Procedures {
if err := writeProcedure(&buf, procedure); err != nil {
return "", err
}
}
// Construct events.
for _, event := range schema.Events {
if err := writeEvent(&buf, event); err != nil {
return "", err
}
}
// Construct triggers.
for _, table := range schema.Tables {
for _, trigger := range table.Triggers {
if err := writeTrigger(&buf, table.Name, trigger); err != nil {
return "", err
}
}
}
if ctx.PrintHeader {
if _, err := buf.WriteString(restoreUniqueAndForeignKeyCheckStmt); err != nil {
return "", err
}
}
return buf.String(), nil
}
func GetTableDefinition(_ string, table *metadatapb.TableMetadata, _ []*metadatapb.SequenceMetadata) (string, error) {
var buf strings.Builder
if err := writeTable(&buf, table); err != nil {
return "", err
}
return buf.String(), nil
}
func GetViewDefinition(_ string, view *metadatapb.ViewMetadata) (string, error) {
var buf strings.Builder
if err := writeView(&buf, view); err != nil {
return "", err
}
return buf.String(), nil
}
func GetFunctionDefinition(_ string, function *metadatapb.FunctionMetadata) (string, error) {
var buf strings.Builder
if err := writeFunction(&buf, function); err != nil {
return "", err
}
return buf.String(), nil
}
func GetProcedureDefinition(_ string, procedure *metadatapb.ProcedureMetadata) (string, error) {
var buf strings.Builder
if err := writeProcedure(&buf, procedure); err != nil {
return "", err
}
return buf.String(), nil
}
func writeEvent(out io.Writer, event *metadatapb.EventMetadata) error {
// An event synced without its CREATE EVENT, as on OceanBase, has nothing faithful to write.
if event.Definition == "" {
return nil
}
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, eventHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, event.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
// Set charset, collation, sql mode and timezone.
if _, err := io.WriteString(out, setCharacterSetClient); err != nil {
return err
}
if _, err := io.WriteString(out, event.CharacterSetClient); err != nil {
return err
}
if _, err := io.WriteString(out, ";\n"); err != nil {
return err
}
if _, err := io.WriteString(out, setCharacterSetResult); err != nil {
return err
}
if _, err := io.WriteString(out, event.CharacterSetClient); err != nil {
return err
}
if _, err := io.WriteString(out, ";\n"); err != nil {
return err
}
if _, err := io.WriteString(out, setCollation); err != nil {
return err
}
if _, err := io.WriteString(out, event.CollationConnection); err != nil {
return err
}
if _, err := io.WriteString(out, ";\n"); err != nil {
return err
}
if _, err := io.WriteString(out, setSQLMode); err != nil {
return err
}
if _, err := io.WriteString(out, "'"); err != nil {
return err
}
if _, err := io.WriteString(out, event.SqlMode); err != nil {
return err
}
if _, err := io.WriteString(out, "';\n"); err != nil {
return err
}
if _, err := io.WriteString(out, setTimezone); err != nil {
return err
}
if _, err := io.WriteString(out, "'"); err != nil {
return err
}
if _, err := io.WriteString(out, event.TimeZone); err != nil {
return err
}
if _, err := io.WriteString(out, "';\n"); err != nil {
return err
}
if _, err := io.WriteString(out, delimiterDoubleSemi); err != nil {
return err
}
// Definition.
if _, err := io.WriteString(out, event.Definition); err != nil {
return err
}
if _, err := io.WriteString(out, ";;\n"); err != nil {
return err
}
if _, err := io.WriteString(out, delimiterSemi); err != nil {
return err
}
_, err := io.WriteString(out, "\n")
return err
}
func writeTrigger(out io.Writer, tableName string, trigger *metadatapb.TriggerMetadata) error {
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, triggerHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
// Set charset, collation, and sql mode.
if _, err := io.WriteString(out, setCharacterSetClient); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.CharacterSetClient); err != nil {
return err
}
if _, err := io.WriteString(out, ";\n"); err != nil {
return err
}
if _, err := io.WriteString(out, setCharacterSetResult); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.CharacterSetClient); err != nil {
return err
}
if _, err := io.WriteString(out, ";\n"); err != nil {
return err
}
if _, err := io.WriteString(out, setCollation); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.CollationConnection); err != nil {
return err
}
if _, err := io.WriteString(out, ";\n"); err != nil {
return err
}
if _, err := io.WriteString(out, setSQLMode); err != nil {
return err
}
if _, err := io.WriteString(out, "'"); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.SqlMode); err != nil {
return err
}
if _, err := io.WriteString(out, "';\n"); err != nil {
return err
}
if _, err := io.WriteString(out, delimiterDoubleSemi); err != nil {
return err
}
// Definition.
if _, err := io.WriteString(out, "CREATE TRIGGER `"); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.Timing); err != nil {
return err
}
if _, err := io.WriteString(out, " "); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.Event); err != nil {
return err
}
if _, err := io.WriteString(out, " ON `"); err != nil {
return err
}
if _, err := io.WriteString(out, tableName); err != nil {
return err
}
if _, err := io.WriteString(out, "` FOR EACH ROW\n"); err != nil {
return err
}
if _, err := io.WriteString(out, trigger.Body); err != nil {
return err
}
if _, err := io.WriteString(out, ";;\n"); err != nil {
return err
}
if _, err := io.WriteString(out, delimiterSemi); err != nil {
return err
}
_, err := io.WriteString(out, "\n")
return err
}
func writeProcedure(out io.Writer, procedure *metadatapb.ProcedureMetadata) error {
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, procedureHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, procedure.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
// Set charset, collation, and sql mode.
if err := writeAdditionalEventsIfSet(out, procedure.CharacterSetClient, procedure.CharacterSetClient, procedure.CollationConnection, procedure.SqlMode); err != nil {
return err
}
if _, err := io.WriteString(out, delimiterDoubleSemi); err != nil {
return err
}
// Definition.
if _, err := io.WriteString(out, procedure.Definition); err != nil {
return err
}
if _, err := io.WriteString(out, ";;\n"); err != nil {
return err
}
if _, err := io.WriteString(out, delimiterSemi); err != nil {
return err
}
_, err := io.WriteString(out, "\n")
return err
}
func writeFunction(out io.Writer, function *metadatapb.FunctionMetadata) error {
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, functionHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, function.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
// Set charset, collation, and sql mode.
if err := writeAdditionalEventsIfSet(out, function.CharacterSetClient, function.CharacterSetClient, function.CollationConnection, function.SqlMode); err != nil {
return err
}
// Definition.
if _, err := io.WriteString(out, function.Definition); err != nil {
return err
}
if _, err := io.WriteString(out, ";;\n"); err != nil {
return err
}
if _, err := io.WriteString(out, delimiterSemi); err != nil {
return err
}
_, err := io.WriteString(out, "\n")
return err
}
func writeView(out io.Writer, view *metadatapb.ViewMetadata) error {
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, viewHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, view.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
// Definition.
if _, err := io.WriteString(out, "CREATE OR REPLACE VIEW `"); err != nil {
return err
}
if _, err := io.WriteString(out, view.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "` AS "); err != nil {
return err
}
if _, err := io.WriteString(out, view.Definition); err != nil {
return err
}
_, err := io.WriteString(out, ";\n\n")
return err
}
func writeTable(out *strings.Builder, table *metadatapb.TableMetadata) error {
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, tableHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, table.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
// Definition.
if _, err := fmt.Fprintf(out, "CREATE TABLE `%s` (\n", table.Name); err != nil {
return err
}
for i, column := range table.Columns {
if i != 0 {
if _, err := fmt.Fprint(out, ",\n"); err != nil {
return err
}
}
if err := printColumnClause(out, column, table); err != nil {
return err
}
}
writeSystemTimePeriod(out, table)
if err := printPrimaryKeyClause(out, table.Indexes); err != nil {
return err
}
for _, index := range table.Indexes {
if index.Primary {
continue
}
if err := printIndexClause(out, index); err != nil {
return err
}
}
for _, fk := range table.ForeignKeys {
if err := printForeignKeyClause(out, fk); err != nil {
return err
}
}
for _, check := range table.CheckConstraints {
if err := printCheckClause(out, check); err != nil {
return err
}
}
if _, err := out.WriteString("\n)"); err != nil {
return err
}
if table.Engine != "" {
if _, err := fmt.Fprintf(out, " ENGINE=%s", table.Engine); err != nil {
return err
}
}
if table.Charset != "" {
if _, err := fmt.Fprintf(out, " DEFAULT CHARSET=%s", table.Charset); err != nil {
return err
}
}
if table.Collation != "" {
if _, err := fmt.Fprintf(out, " COLLATE=%s", table.Collation); err != nil {
return err
}
}
if table.Comment != "" {
if _, err := fmt.Fprintf(out, " COMMENT='%s'", table.Comment); err != nil {
return err
}
}
writeSystemVersioningOption(out, table)
if len(table.Partitions) > 0 {
if err := printPartitionClause(out, table.Partitions, table.Engine); err != nil {
return err
}
}
_, err := io.WriteString(out, ";\n\n")
return err
}
// Copy the logic from backend/plugin/schema/mysql/state.go.
func printPartitionClause(buf *strings.Builder, partitions []*metadatapb.TablePartitionMetadata, engine string) error {
if len(partitions) == 0 {
return nil
}
vsc := getVersionSpecificComment(partitions)
curComment := vsc
if _, err := fmt.Fprintf(buf, "%s PARTITION BY ", curComment); err != nil {
return err
}
switch partitions[0].Type {
case metadatapb.TablePartitionMetadata_RANGE:
if _, err := fmt.Fprintf(buf, "RANGE (%s)", partitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_RANGE_COLUMNS:
if _, err := fmt.Fprintf(buf, "RANGE COLUMNS (%s)", partitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_LIST:
if _, err := fmt.Fprintf(buf, "LIST (%s)", partitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_LIST_COLUMNS:
if _, err := fmt.Fprintf(buf, "LIST COLUMNS (%s)", partitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_HASH:
if _, err := fmt.Fprintf(buf, "HASH (%s)", partitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_KEY:
if _, err := fmt.Fprintf(buf, "KEY (%s)", partitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_LINEAR_HASH:
if _, err := fmt.Fprintf(buf, "LINEAR HASH (%s)", partitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_LINEAR_KEY:
if _, err := fmt.Fprintf(buf, "LINEAR KEY (%s)", partitions[0].Expression); err != nil {
return err
}
default:
return errors.Errorf("unknown partition type: %v", partitions[0].Type)
}
useDefault := int64(0)
if partitions[0].UseDefault != "" {
var err error
useDefault, err = strconv.ParseInt(partitions[0].UseDefault, 10, 64)
if err != nil {
return err
}
}
if useDefault != 0 {
if _, err := fmt.Fprintf(buf, "\nPARTITIONS %d", useDefault); err != nil {
return err
}
}
if len(partitions[0].Subpartitions) > 0 {
if _, err := fmt.Fprint(buf, "\nSUBPARTITION BY "); err != nil {
return err
}
switch partitions[0].Subpartitions[0].Type {
case metadatapb.TablePartitionMetadata_HASH:
if _, err := fmt.Fprintf(buf, "HASH (%s)", partitions[0].Subpartitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_LINEAR_HASH:
if _, err := fmt.Fprintf(buf, "LINEAR HASH (%s)", partitions[0].Subpartitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_KEY:
if _, err := fmt.Fprintf(buf, "KEY (%s)", partitions[0].Subpartitions[0].Expression); err != nil {
return err
}
case metadatapb.TablePartitionMetadata_LINEAR_KEY:
if _, err := fmt.Fprintf(buf, "LINEAR KEY (%s)", partitions[0].Subpartitions[0].Expression); err != nil {
return err
}
default:
return errors.Errorf("invalid subpartition type: %v", partitions[0].Subpartitions[0].Type)
}
}
subUseDefault := 0
if len(partitions[0].Subpartitions) > 0 && partitions[0].Subpartitions[0].UseDefault != "" {
var err error
subUseDefault, err = strconv.Atoi(partitions[0].Subpartitions[0].UseDefault)
if err != nil {
return err
}
}
if subUseDefault != 0 {
if _, err := fmt.Fprintf(buf, "\nSUBPARTITIONS %d", subUseDefault); err != nil {
return err
}
}
if useDefault == 0 {
if _, err := fmt.Fprint(buf, "\n("); err != nil {
return err
}
preposition, err := getPrepositionByType(partitions[0].Type)
if err != nil {
return err
}
for i, partition := range partitions {
if i != 0 {
if _, err := fmt.Fprint(buf, ",\n "); err != nil {
return err
}
}
if _, err := fmt.Fprintf(buf, "PARTITION %s", mysqlQuoteIdentifier(partition.Name)); err != nil {
return err
}
if preposition != "" {
if partition.Value != "MAXVALUE" {
if _, err := fmt.Fprintf(buf, " VALUES %s (%s)", preposition, partition.Value); err != nil {
return err
}
} else {
if _, err := fmt.Fprintf(buf, " VALUES %s %s", preposition, partition.Value); err != nil {
return err
}
}
}
if subUseDefault == 0 && len(partition.Subpartitions) > 0 {
if _, err := fmt.Fprint(buf, "\n ("); err != nil {
return err
}
for j, subPartition := range partition.Subpartitions {
if _, err := fmt.Fprintf(buf, "SUBPARTITION %s", mysqlQuoteIdentifier(subPartition.Name)); err != nil {
return err
}
if err := writePartitionOptions(buf, engine); err != nil {
return err
}
if j == len(partition.Subpartitions)-1 {
if _, err := fmt.Fprint(buf, ")"); err != nil {
return err
}
} else {
if _, err := fmt.Fprint(buf, ",\n "); err != nil {
return err
}
}
}
} else {
if err := writePartitionOptions(buf, engine); err != nil {
return err
}
}
if i == len(partitions)-1 {
if _, err := fmt.Fprint(buf, ")"); err != nil {
return err
}
}
}
}
if _, err := fmt.Fprint(buf, " */"); err != nil {
return err
}
return nil
}
func getPrepositionByType(tp metadatapb.TablePartitionMetadata_Type) (string, error) {
switch tp {
case metadatapb.TablePartitionMetadata_RANGE:
return "LESS THAN", nil
case metadatapb.TablePartitionMetadata_RANGE_COLUMNS:
return "LESS THAN", nil
case metadatapb.TablePartitionMetadata_LIST:
return "IN", nil
case metadatapb.TablePartitionMetadata_LIST_COLUMNS:
return "IN", nil
case metadatapb.TablePartitionMetadata_HASH, metadatapb.TablePartitionMetadata_KEY, metadatapb.TablePartitionMetadata_LINEAR_HASH, metadatapb.TablePartitionMetadata_LINEAR_KEY:
return "", nil
default:
return "", errors.Errorf("unsupported partition type: %v", tp)
}
}
func writePartitionOptions(buf io.StringWriter, engine string) error {
/*
int err = 0;
err += add_space(fptr);
if (p_elem->tablespace_name) {
err += add_string(fptr, "TABLESPACE = ");
err += add_ident_string(fptr, p_elem->tablespace_name);
err += add_space(fptr);
}
if (p_elem->nodegroup_id != UNDEF_NODEGROUP)
err += add_keyword_int(fptr, "NODEGROUP", (longlong)p_elem->nodegroup_id);
if (p_elem->part_max_rows)
err += add_keyword_int(fptr, "MAX_ROWS", (longlong)p_elem->part_max_rows);
if (p_elem->part_min_rows)
err += add_keyword_int(fptr, "MIN_ROWS", (longlong)p_elem->part_min_rows);
if (!(current_thd->variables.sql_mode & MODE_NO_DIR_IN_CREATE)) {
if (p_elem->data_file_name)
err += add_keyword_path(fptr, "DATA DIRECTORY", p_elem->data_file_name);
if (p_elem->index_file_name)
err += add_keyword_path(fptr, "INDEX DIRECTORY", p_elem->index_file_name);
}
if (p_elem->part_comment)
err += add_keyword_string(fptr, "COMMENT", true, p_elem->part_comment);
return err + add_engine(fptr, p_elem->engine_type);
*/
// TODO(zp): Get all the partition options from the metadata is too complex, just write
// the engine for now. It must be the table's own engine: MySQL rejects a partitioned
// table whose partitions name a different one with "ERROR 1497: The mix of handlers in
// the partitions is not allowed in this version of MySQL", and partitioning a
// non-InnoDB table is still legal on 5.7.
if engine == "" {
engine = "InnoDB"
}
if _, err := buf.WriteString(" ENGINE=" + engine); err != nil {
return err
}
return nil
}
func getVersionSpecificComment(partitions []*metadatapb.TablePartitionMetadata) string {
if len(partitions) == 0 {
return ""
}
partition := partitions[0]
if partition.Type == metadatapb.TablePartitionMetadata_RANGE_COLUMNS || partition.Type == metadatapb.TablePartitionMetadata_LIST_COLUMNS {
// MySQL introduce columns partitioning in 5.5+
return "\n/*!50500"
}
return "\n/*!50100"
}
func printCheckClause(buf *strings.Builder, check *metadatapb.CheckConstraintMetadata) error {
if _, err := fmt.Fprintf(buf, ",\n CONSTRAINT `%s` CHECK %s", check.Name, check.Expression); err != nil {
return err
}
return nil
}
func printForeignKeyClause(buf *strings.Builder, fk *metadatapb.ForeignKeyMetadata) error {
if _, err := fmt.Fprintf(buf, ",\n CONSTRAINT `%s` FOREIGN KEY (", fk.Name); err != nil {
return err
}
for i, column := range fk.Columns {
if i != 0 {
if _, err := fmt.Fprint(buf, ", "); err != nil {
return err
}
}
if _, err := fmt.Fprintf(buf, "`%s`", column); err != nil {
return err
}
}
if _, err := fmt.Fprintf(buf, ") REFERENCES `%s` (", fk.ReferencedTable); err != nil {
return err
}
for i, column := range fk.ReferencedColumns {
if i != 0 {
if _, err := fmt.Fprint(buf, ", "); err != nil {
return err
}
}
if _, err := fmt.Fprintf(buf, "`%s`", column); err != nil {
return err
}
}
if _, err := fmt.Fprint(buf, ")"); err != nil {
return err
}
if fk.OnDelete != "" && !strings.EqualFold(fk.OnDelete, mysqlNoAction) {
if _, err := fmt.Fprintf(buf, " ON DELETE %s", fk.OnDelete); err != nil {
return err
}
}
if fk.OnUpdate != "" && !strings.EqualFold(fk.OnUpdate, mysqlNoAction) {
if _, err := fmt.Fprintf(buf, " ON UPDATE %s", fk.OnUpdate); err != nil {
return err
}
}
return nil
}
func printIndexClause(buf *strings.Builder, index *metadatapb.IndexMetadata) error {
if index.Primary {
return nil
}
if _, err := fmt.Fprint(buf, ",\n "); err != nil {
return err
}
isFullText := strings.EqualFold(index.Type, mysqlIndexFullText)
isSpatial := strings.EqualFold(index.Type, mysqlIndexSpatial)
if index.Unique {
if _, err := fmt.Fprint(buf, "UNIQUE "); err != nil {
return err
}
} else if isFullText {
if _, err := fmt.Fprint(buf, "FULLTEXT "); err != nil {
return err
}
} else if isSpatial {
if _, err := fmt.Fprint(buf, "SPATIAL "); err != nil {
return err
}
}
if _, err := fmt.Fprintf(buf, "KEY `%s` (", index.Name); err != nil {
return err
}
for i, expr := range index.Expressions {
if i != 0 {
if _, err := fmt.Fprint(buf, ", "); err != nil {
return err
}
}
keyLength := int64(-1)
descending := false
if len(index.KeyLength) > i {
keyLength = index.KeyLength[i]
}
if len(index.Descending) > i {
descending = index.Descending[i]
}
// SPATIAL and FULLTEXT index parts do not take a key-part prefix length. The sync
// records SUB_PART for spatial parts (e.g. 32 for a POINT) even though SHOW CREATE
// renders none; emitting `(32)` would diverge from the user form and DROP+ADD the
// index on every no-op. Suppress the prefix for those index types.
if err := printIndexKeyPart(buf, expr, keyLength, descending, isSpatial || isFullText); err != nil {
return err
}
}
if _, err := fmt.Fprint(buf, ")"); err != nil {
return err
}
// INVISIBLE secondary index (MySQL 8.0). The version-gated executable comment is parsed
// back by omni's loader, so the dumped `from` carries invisibility and the diff against
// an INVISIBLE user `to` is a no-op instead of a DROP+ADD.
if !index.Visible {
if _, err := fmt.Fprint(buf, " /*!80000 INVISIBLE */"); err != nil {
return err
}
}
return nil
}
func printIndexKeyPart(buf *strings.Builder, expr string, length int64, descending bool, suppressPrefix bool) error {
if len(expr) == 0 {
return errors.New("index key part expression is empty")
}
if expr[0] == '(' && expr[len(expr)-1] == ')' {
if _, err := buf.WriteString(normalizeFunctionalIndexExpr(expr)); err != nil {
return err
}
} else {
if _, err := buf.WriteString("`"); err != nil {
return err
}
if _, err := buf.WriteString(expr); err != nil {
return err
}
if _, err := buf.WriteString("`"); err != nil {
return err
}
}
if length > 0 && !suppressPrefix {
if _, err := buf.WriteString("("); err != nil {
return err
}
if _, err := buf.WriteString(strconv.FormatInt(length, 10)); err != nil {
return err
}
if _, err := buf.WriteString(")"); err != nil {
return err
}
}
if descending {
if _, err := buf.WriteString(" DESC"); err != nil {
return err
}
}
return nil
}
// normalizeFunctionalIndexExpr rewrites a synced functional (expression) index key part into
// the form the omni SDL loader can parse. The information_schema.STATISTICS.EXPRESSION text
// MySQL records for an `((expr))` key part:
// - backslash-escapes the single quotes around string literals (e.g. json_extract(`tags`,
// _utf8mb4\'$.ids\')), which the omni lexer rejects (`syntax error at or near "\"`); and
// - prefixes string literals with a charset introducer (`_utf8mb4'…'`, `_latin1'…'`, …)
// reflecting the connection charset, which the omni expression parser does not accept in
// this position (`unexpected token`).
//
// Both make LoadSDL fail outright on the dumped source even though omni accepts the canonical
// user spelling (e.g. `((cast(json_extract(`tags`,'$.ids') as unsigned array)))`). This
// unescapes the quotes and strips the introducer so the emitted expression is omni-parseable;
// omni's functional-index normalizer then canonicalizes the dumped and user forms identically,
// yielding an empty no-op. The transform is string-literal aware: the introducer is removed
// only when it immediately precedes a string literal, and literal contents are left untouched.
func normalizeFunctionalIndexExpr(expr string) string {
return stripCharsetIntroducers(unescapeFunctionalIndexQuotes(expr))
}
// unescapeFunctionalIndexQuotes undoes the backslash escaping that
// information_schema.STATISTICS.EXPRESSION applies to single quotes (\' -> ') and backslashes
// (\\ -> \). MySQL stores the expression text escaped; the omni lexer wants the raw SQL.
func unescapeFunctionalIndexQuotes(expr string) string {
if !strings.Contains(expr, `\`) {
return expr
}
var b strings.Builder
b.Grow(len(expr))
for i := 0; i < len(expr); i++ {
if expr[i] == '\\' && i+1 < len(expr) && (expr[i+1] == '\'' || expr[i+1] == '\\') {
b.WriteByte(expr[i+1])
i++
continue
}
b.WriteByte(expr[i])
}
return b.String()
}
// stripCharsetIntroducers removes a MySQL charset introducer token (an underscore-prefixed
// identifier such as `_utf8mb4` or `_latin1`) when it immediately precedes a single-quoted
// string literal, e.g. `_utf8mb4'$.ids'` -> `'$.ids'`. The scan tracks whether it is inside a
// '…'/"…" string literal (honoring backslash escapes and the doubled-quote escape) so that an
// underscore sequence occurring inside literal text is never mistaken for an introducer and
// literal contents are preserved byte for byte. Backtick-quoted identifiers are passed through
// untouched. Only an introducer attached to a string literal is stripped; underscores that are
// part of an ordinary identifier (`my_col`, a leading-underscore name not followed by a quote)
// are left intact.
func stripCharsetIntroducers(expr string) string {
if !strings.Contains(expr, "_") {
return expr
}
var b strings.Builder
b.Grow(len(expr))
var quote byte // open delimiter of the literal currently being copied, or 0 when outside one
for i := 0; i < len(expr); {
c := expr[i]
if quote != 0 {
b.WriteByte(c)
switch {
case c == '\\' && i+1 < len(expr):
b.WriteByte(expr[i+1])
i += 2
continue
case c == quote:
if i+1 < len(expr) && expr[i+1] == quote {
b.WriteByte(expr[i+1])
i += 2
continue
}
quote = 0
default:
}
i++
continue
}
switch c {
case '\'', '"':
quote = c
b.WriteByte(c)
i++
case '`':
// Copy a backtick-quoted identifier verbatim so an underscore inside it is never
// treated as an introducer.
b.WriteByte(c)
i++
for i < len(expr) {
b.WriteByte(expr[i])
if expr[i] == '`' {
i++
break
}
i++
}
case '_':
if n := charsetIntroducerLen(expr[i:]); n > 0 {
// Drop the `_<charset>` introducer; the following string literal is copied on the
// next iterations.
i += n
continue
}
b.WriteByte(c)
i++
default:
b.WriteByte(c)
i++
}
}
return b.String()
}
// charsetIntroducerLen returns the byte length of a leading charset-introducer token in s
// when s begins with `_<charset>` immediately followed by a single-quoted string literal,
// or 0 otherwise. The charset name accepts the ASCII letters/digits MySQL charset names use
// (e.g. utf8mb4, latin1, big5); the terminating quote is required so a bare leading-underscore
// identifier is not stripped.
func charsetIntroducerLen(s string) int {
if len(s) < 2 || s[0] != '_' {
return 0
}
i := 1
for i < len(s) && isCharsetNameByte(s[i]) {
i++
}
if i == 1 || i >= len(s) || s[i] != '\'' {
return 0
}
return i
}
func isCharsetNameByte(c byte) bool {
return c >= 'a' && c <= 'z' || c >= 'A' && c <= 'Z' || c >= '0' && c <= '9'
}
func printPrimaryKeyClause(buf *strings.Builder, indexes []*metadatapb.IndexMetadata) error {
for _, index := range indexes {
if index.Primary {
if _, err := fmt.Fprint(buf, ",\n PRIMARY KEY ("); err != nil {
return err
}
for i, column := range index.Expressions {
if i != 0 {
if _, err := fmt.Fprint(buf, ", "); err != nil {
return err
}
}
keyLength := int64(-1)
descending := false
if len(index.KeyLength) > i {
keyLength = index.KeyLength[i]
}
if len(index.Descending) > i {
descending = index.Descending[i]
}
// Primary keys are never SPATIAL/FULLTEXT, so prefix lengths are valid here.
if err := printIndexKeyPart(buf, column, keyLength, descending, false); err != nil {
return err
}
}
if _, err := fmt.Fprint(buf, ")"); err != nil {
return err
}
return nil
}
}
return nil
}
func isAutoIncrement(column *metadatapb.ColumnMetadata) bool {
return strings.EqualFold(column.GetDefault(), autoIncrementSymbol)
}
func printColumnClause(buf *strings.Builder, column *metadatapb.ColumnMetadata, table *metadatapb.TableMetadata) error {
if _, err := fmt.Fprintf(buf, " `%s` %s", column.Name, normalizeColumnType(column.Type)); err != nil {
return err
}
if column.CharacterSet != "" && column.CharacterSet != table.Charset {
if _, err := fmt.Fprintf(buf, " CHARACTER SET %s", column.CharacterSet); err != nil {
return err
}
}
if column.Collation != "" && column.Collation != table.Collation {
if _, err := fmt.Fprintf(buf, " COLLATE %s", column.Collation); err != nil {
return err
}
}
if isSystemTimeColumn(column) {
writeSystemTimeColumnAttribute(buf, column)
} else if column.Generation != nil && column.Generation.Expression != "" {
if _, err := fmt.Fprintf(buf, " GENERATED ALWAYS AS (%s) ", column.Generation.Expression); err != nil {
return err
}
switch column.Generation.Type {
case metadatapb.GenerationMetadata_TYPE_STORED:
if _, err := fmt.Fprint(buf, "STORED"); err != nil {
return err
}
case metadatapb.GenerationMetadata_TYPE_VIRTUAL:
if _, err := fmt.Fprint(buf, "VIRTUAL"); err != nil {
return err
}
default:
// Unknown generation type
}
}
if !column.Nullable {
if _, err := fmt.Fprint(buf, " NOT NULL"); err != nil {
return err
}
}
// SRID for spatial columns (MySQL 8.0). Emitted after NOT NULL and before DEFAULT to
// match the SHOW CREATE / omni-canonical position; the version-gated executable comment
// is spliced back as live SQL by omni's loader so the dumped `from` carries the SRID and
// the diff against a user `to` with the same SRID is a no-op.
writeColumnSRIDAttribute(buf, column)
if err := printDefaultClause(buf, column); err != nil {
return err
}
// Handle auto_increment.
if isAutoIncrement(column) {
if _, err := fmt.Fprintf(buf, " %s", autoIncrementSymbol); err != nil {
return err
}
}
if column.OnUpdate != "" {
if _, err := fmt.Fprintf(buf, " ON UPDATE %s", column.OnUpdate); err != nil {
return err
}
}
// INVISIBLE column (MySQL 8.0.23+). Emitted before COMMENT to match the SHOW CREATE /
// omni-canonical order (verified against 8.0.32 for both regular and generated columns);
// like SRID the version-gated comment is parsed back by omni's loader so the dumped `from`
// carries invisibility and the diff against an INVISIBLE user `to` is a no-op.
writeColumnInvisibleAttribute(buf, column)
if column.Comment != "" {
if _, err := fmt.Fprintf(buf, " COMMENT '%s'", column.Comment); err != nil {
return err
}
}
return nil
}
// writeColumnSRIDAttribute emits the ` /*!80003 SRID n */` column attribute when the
// column carries an explicit SRID — including the valid SRID 0, which presence (not a
// zero sentinel) distinguishes from "no SRID". Shared by the SDL dumper
// (printColumnClause) and the legacy migration generator (writeAddColumn /
// writeModifyColumn) so both render the same canonical form.
func writeColumnSRIDAttribute(buf *strings.Builder, column *metadatapb.ColumnMetadata) {
if column.Srid != nil {
_, _ = fmt.Fprintf(buf, " /*!80003 SRID %d */", column.GetSrid())
}
}
// writeColumnInvisibleAttribute emits the ` /*!80023 INVISIBLE */` column attribute for
// invisible columns (MySQL 8.0.23+). MySQL's canonical SHOW CREATE order places INVISIBLE
// before COMMENT for both regular and generated columns (verified against 8.0.32), so callers
// emit it before COMMENT. Shared by the SDL dumper and the legacy migration generator.
func writeColumnInvisibleAttribute(buf *strings.Builder, column *metadatapb.ColumnMetadata) {
if column.IsInvisible {
_, _ = buf.WriteString(" /*!80023 INVISIBLE */")
}
}
// normalizeColumnType rewrites engine-specific column-type spellings the omni SDL parser
// does not accept into the canonical spelling it does. MySQL 8.0's SHOW CREATE /
// information_schema render GEOMETRYCOLLECTION as the synonym `geomcollection`, which the
// omni loader rejects with "expected data type"; the canonical `geometrycollection`
// (what 5.7 already dumps) round-trips. The two spellings denote the same type, so this is
// a pure rename with no semantic change.
func normalizeColumnType(columnType string) string {
if strings.EqualFold(columnType, "geomcollection") {
return "geometrycollection"
}
return columnType
}
func printDefaultClause(buf *strings.Builder, column *metadatapb.ColumnMetadata) error {
// Check if column has any default value
hasDefault := column.Default != ""
if !hasDefault {
return nil
}
if column.Default == "NULL" {
if !column.Nullable || !typeSupportsDefaultValue(column.Type) {
// If the column is not nullable, then the default value should not be null.
// For this case, we should not print the default clause.
return nil
}
if column.Generation != nil && column.Generation.Expression != "" {
return nil
}
if _, err := fmt.Fprint(buf, " DEFAULT NULL"); err != nil {
return err
}
return nil
}
if column.Default != "" {
if isAutoIncrement(column) {
// If the default value is auto_increment, then we should not print the default clause.
// We'll handle this in the following AUTO_INCREMENT clause.
return nil
}
if _, err := fmt.Fprintf(buf, " DEFAULT %s", renderColumnDefault(column)); err != nil {
return err
}
}
return nil
}
// renderColumnDefault returns the DEFAULT expression text to emit for a column.
//
// For a BIT column, the synced COLUMN_DEFAULT is the bit-literal text (e.g. b'0'),
// which the sync stores QUOTE()-escaped as a string literal in ColumnMetadata.Default
// (the bit literal wrapped in single quotes with the inner quotes backslash-escaped).
// Emitting that verbatim produces a quoted string default that the omni loader takes as a
// string — never matching the user's BIT literal b'0', so the no-op phantom-re-emits
// MODIFY ... DEFAULT b'0' forever. Real mysqldump renders the bit literal unquoted
// (DEFAULT b'0'); match it by recovering the inner literal and emitting it without the
// surrounding quotes when the column is BIT and the recovered text is a bit/hex literal
// (b'…'/0x…/x'…'). Any other default (including a genuine string) is emitted verbatim.
func renderColumnDefault(column *metadatapb.ColumnMetadata) string {
if isBitColumnType(column.Type) {
if lit, ok := bitLiteralFromQuotedDefault(column.Default); ok {
return lit
}
}
return column.Default
}
// isBitColumnType reports whether a column type spelling is BIT (with or without a
// display width, e.g. "bit" or "bit(8)").
func isBitColumnType(columnType string) bool {
lower := strings.ToLower(strings.TrimSpace(columnType))
return lower == "bit" || strings.HasPrefix(lower, "bit(")
}
// bitLiteralFromQuotedDefault recovers a bit/hex literal from a stored column default and
// reports whether the default denotes one. The default may already be a bare literal
// (b'0', 0x0A, x'1f') or the QUOTE()-escaped string form the MySQL sync stores for static
// defaults (the literal wrapped in single quotes with inner quotes backslash-escaped). It
// strips at most one layer of surrounding single quotes and the QUOTE() backslash escaping,
// then recognizes the MySQL bit/hex literal spellings
// (b'…' / B'…' bit, 0x… hex, x'…' / X'…' hex). On success it returns the canonical literal
// text to emit unquoted; otherwise it returns ("", false) and the caller emits the default
// verbatim.
func bitLiteralFromQuotedDefault(def string) (string, bool) {
if isBitOrHexLiteral(def) {
return def, true
}
if len(def) >= 2 && def[0] == '\'' && def[len(def)-1] == '\'' {
inner := unescapeQuotedDefault(def[1 : len(def)-1])
if isBitOrHexLiteral(inner) {
return inner, true
}
}
return "", false
}
// isBitOrHexLiteral reports whether s is a MySQL bit literal (b'…' / B'…') or hex literal
// (0x… / 0X… / x'…' / X'…') with only valid digits between the delimiters.
func isBitOrHexLiteral(s string) bool {
switch {
case len(s) >= 3 && (s[0] == 'b' || s[0] == 'B') && s[1] == '\'' && s[len(s)-1] == '\'':
return allDigitsIn(s[2:len(s)-1], "01")
case len(s) >= 3 && (s[0] == 'x' || s[0] == 'X') && s[1] == '\'' && s[len(s)-1] == '\'':
return allDigitsIn(s[2:len(s)-1], "0123456789abcdefABCDEF")
case len(s) >= 3 && s[0] == '0' && (s[1] == 'x' || s[1] == 'X'):
return allDigitsIn(s[2:], "0123456789abcdefABCDEF")
default:
return false
}
}
func allDigitsIn(s, allowed string) bool {
if len(s) == 0 {
return false
}
for i := 0; i < len(s); i++ {
if !strings.ContainsRune(allowed, rune(s[i])) {
return false
}
}
return true
}
// unescapeQuotedDefault undoes the QUOTE() backslash escaping (\' -> ', \\ -> \) the MySQL
// sync applies to static defaults. Only these two sequences appear inside a bit/hex literal,
// so the full QUOTE() escape set is intentionally not reproduced here.
func unescapeQuotedDefault(s string) string {
if !strings.Contains(s, `\`) {
return s
}
var b strings.Builder
b.Grow(len(s))
for i := 0; i < len(s); i++ {
if s[i] == '\\' && i+1 < len(s) && (s[i+1] == '\'' || s[i+1] == '\\') {
b.WriteByte(s[i+1])
i++
continue
}
b.WriteByte(s[i])
}
return b.String()
}
func typeSupportsDefaultValue(tp string) bool {
switch strings.ToLower(tp) {
case mysqlTypeBlob, mysqlTypeTinyBob, mysqlTypeMediumBlob, mysqlTypeLongBlob, mysqlTypeJSON, mysqlTypeGeometry:
return false
default:
return true
}
}
func writeInvalidTemporaryView(out io.Writer, view *metadatapb.ViewMetadata) error {
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, tempViewHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, view.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, "-- `"); err != nil {
return err
}
if _, err := io.WriteString(out, view.Name); err != nil {
return err
}
_, err := io.WriteString(out, "` references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them\n\n")
return err
}
func writeTemporaryView(out io.Writer, view *metadatapb.ViewMetadata) error {
// Header.
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
if _, err := io.WriteString(out, tempViewHeader); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
if _, err := io.WriteString(out, view.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`\n"); err != nil {
return err
}
if _, err := io.WriteString(out, emptyCommentLine); err != nil {
return err
}
// Definition.
if _, err := io.WriteString(out, "CREATE VIEW `"); err != nil {
return err
}
if _, err := io.WriteString(out, view.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "` AS SELECT\n "); err != nil {
return err
}
for i, column := range view.Columns {
if i != 0 {
if _, err := io.WriteString(out, ",\n "); err != nil {
return err
}
}
if _, err := io.WriteString(out, "1 AS `"); err != nil {
return err
}
if _, err := io.WriteString(out, column.Name); err != nil {
return err
}
if _, err := io.WriteString(out, "`"); err != nil {
return err
}
}
_, err := io.WriteString(out, ";\n\n")
return err
}
func writeAdditionalEventsIfSet(out io.Writer, characterSetClient, characterSetResult, collationConnection, sqlMode string) error {
events := []struct {
condition bool
prefix string
value string
}{
{characterSetClient != "", setCharacterSetClient, characterSetClient},
{characterSetResult != "", setCharacterSetResult, characterSetResult},
{collationConnection != "", setCollation, collationConnection},
}
for _, event := range events {
if event.condition {
if _, err := io.WriteString(out, event.prefix); err != nil {
return err
}
if _, err := io.WriteString(out, event.value); err != nil {
return err
}
if _, err := io.WriteString(out, ";\n"); err != nil {
return err
}
}
}
if sqlMode != "" {
if _, err := io.WriteString(out, setSQLMode); err != nil {
return err
}
if _, err := io.WriteString(out, "'"); err != nil {
return err
}
if _, err := io.WriteString(out, sqlMode); err != nil {
return err
}
if _, err := io.WriteString(out, "';\n"); err != nil {
return err
}
}
return nil
}
// getSDLFormat emits a canonical, deterministic SDL dump of the database metadata
// such that the omni catalog LoadSDL round-trips it. Unlike the mysqldump-style
// output produced by GetDatabaseDefinition, the SDL dump:
// - contains only the constructive CREATE statements LoadSDL accepts (no SET
// headers, no DELIMITER blocks, no temporary-view scaffolding, no version-gated
// /*!50100 PARTITION */ executable comments);
// - emits objects and intra-table clauses in a stable order (tables and views
// sorted by name; secondary indexes, foreign keys, and check constraints sorted
// by name) so the same metadata always produces byte-identical SDL;
// - strips the volatile AUTO_INCREMENT=N table option (a live counter, never
// schema);
// - emits stored routines, triggers, and events as their canonical CREATE forms
// (DEFINER omitted, no DELIMITER blocks — omni's LoadSDL splitter is BEGIN…END
// aware and the routine/trigger/event differs ignore DEFINER).
//
// Emission order is dependency-safe so every CREATE resolves against objects loaded
// before it: tables → functions → procedures → views → triggers → events. Functions
// precede views/triggers because a view or trigger body may call a stored function;
// triggers and events follow their tables because the omni loader requires a
// trigger's owning table (and any table an event/trigger body touches) to exist.
//
// Type, charset/collation, and default canonicalization are intentionally NOT done
// here: the omni Diff path resolves both the dumped source and the user target
// through the same Normalizer (CanonicalColumn), so faithful emission is sufficient
// for the no-op idempotence property — the canonical comparison happens in omni.
func getSDLFormat(metadata *metadatapb.DatabaseSchemaMetadata) (string, error) {
var buf strings.Builder
schema := metadata.Schemas[0]
tables := make([]*metadatapb.TableMetadata, 0, len(schema.Tables))
for _, table := range schema.Tables {
if table.SkipDump {
continue
}
tables = append(tables, table)
}
slices.SortFunc(tables, func(a, b *metadatapb.TableMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, table := range tables {
if err := writeTableSDL(&buf, table); err != nil {
return "", err
}
}
functions := make([]*metadatapb.FunctionMetadata, 0, len(schema.Functions))
for _, function := range schema.Functions {
if function.SkipDump {
continue
}
functions = append(functions, function)
}
slices.SortFunc(functions, func(a, b *metadatapb.FunctionMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, function := range functions {
if err := writeRoutineSDL(&buf, function.Definition); err != nil {
return "", err
}
}
procedures := make([]*metadatapb.ProcedureMetadata, 0, len(schema.Procedures))
for _, procedure := range schema.Procedures {
if procedure.SkipDump {
continue
}
procedures = append(procedures, procedure)
}
slices.SortFunc(procedures, func(a, b *metadatapb.ProcedureMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, procedure := range procedures {
if err := writeRoutineSDL(&buf, procedure.Definition); err != nil {
return "", err
}
}
views := make([]*metadatapb.ViewMetadata, 0, len(schema.Views))
for _, view := range schema.Views {
if view.SkipDump {
continue
}
views = append(views, view)
}
slices.SortFunc(views, func(a, b *metadatapb.ViewMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, view := range views {
if err := writeViewSDL(&buf, metadata.Name, view); err != nil {
return "", err
}
}
// Triggers hang off tables (TableMetadata.Triggers); emit them after every table
// in deterministic (table, trigger) order.
for _, table := range tables {
triggers := make([]*metadatapb.TriggerMetadata, 0, len(table.Triggers))
for _, trigger := range table.Triggers {
if trigger.SkipDump {
continue
}
triggers = append(triggers, trigger)
}
slices.SortFunc(triggers, func(a, b *metadatapb.TriggerMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, trigger := range triggers {
if err := writeTriggerSDL(&buf, table.Name, trigger); err != nil {
return "", err
}
}
}
events := make([]*metadatapb.EventMetadata, 0, len(schema.Events))
events = append(events, schema.Events...)
slices.SortFunc(events, func(a, b *metadatapb.EventMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, event := range events {
if err := writeEventSDL(&buf, event); err != nil {
return "", err
}
}
return buf.String(), nil
}
// writeTableSDL emits a single CREATE TABLE statement in canonical SDL form with
// deterministic clause ordering. It reuses the same column/index/foreign-key/check
// clause writers as the mysqldump path; only the statement framing and the sorting
// of sub-objects differ.
func writeTableSDL(buf *strings.Builder, table *metadatapb.TableMetadata) error {
if _, err := fmt.Fprintf(buf, "CREATE TABLE `%s` (\n", table.Name); err != nil {
return err
}
for i, column := range table.Columns {
if i != 0 {
if _, err := fmt.Fprint(buf, ",\n"); err != nil {
return err
}
}
if err := printColumnClause(buf, column, table); err != nil {
return err
}
}
writeSystemTimePeriod(buf, table)
if err := printPrimaryKeyClause(buf, table.Indexes); err != nil {
return err
}
secondaryIndexes := make([]*metadatapb.IndexMetadata, 0, len(table.Indexes))
for _, index := range table.Indexes {
if index.Primary {
continue
}
secondaryIndexes = append(secondaryIndexes, index)
}
slices.SortFunc(secondaryIndexes, func(a, b *metadatapb.IndexMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, index := range secondaryIndexes {
if err := printIndexClause(buf, index); err != nil {
return err
}
}
foreignKeys := make([]*metadatapb.ForeignKeyMetadata, len(table.ForeignKeys))
copy(foreignKeys, table.ForeignKeys)
slices.SortFunc(foreignKeys, func(a, b *metadatapb.ForeignKeyMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, fk := range foreignKeys {
if err := printForeignKeyClause(buf, fk); err != nil {
return err
}
}
checks := make([]*metadatapb.CheckConstraintMetadata, len(table.CheckConstraints))
copy(checks, table.CheckConstraints)
slices.SortFunc(checks, func(a, b *metadatapb.CheckConstraintMetadata) int { return cmp.Compare(a.Name, b.Name) })
for _, check := range checks {
if err := printCheckClause(buf, check); err != nil {
return err
}
}
if _, err := buf.WriteString("\n)"); err != nil {
return err
}
if table.Engine != "" {
if _, err := fmt.Fprintf(buf, " ENGINE=%s", table.Engine); err != nil {
return err
}
}
// CREATE_OPTIONS (ROW_FORMAT, KEY_BLOCK_SIZE, COMPRESSION, STATS_PERSISTENT, ...) are
// recorded schema the sync captures into TableMetadata.CreateOptions; emit them in the
// canonical SHOW CREATE position (after ENGINE/AUTO_INCREMENT, before DEFAULT CHARSET) so
// the dumped `from` keeps an explicit ROW_FORMAT and the omni diff against a user `to`
// carrying the same option is a no-op (entry: TestSDLStressTableCreateOptions). The
// synthetic `partitioned` token is dropped — partitioning is surfaced via Partitions, not
// as a literal table option, and emitting it would be invalid DDL.
if opts := filterCreateOptions(table.CreateOptions); opts != "" {
if _, err := fmt.Fprintf(buf, " %s", opts); err != nil {
return err
}
}
if table.Charset != "" {
if _, err := fmt.Fprintf(buf, " DEFAULT CHARSET=%s", table.Charset); err != nil {
return err
}
}
if table.Collation != "" {
if _, err := fmt.Fprintf(buf, " COLLATE=%s", table.Collation); err != nil {
return err
}
}
if table.Comment != "" {
if _, err := fmt.Fprintf(buf, " COMMENT='%s'", table.Comment); err != nil {
return err
}
}
writeSystemVersioningOption(buf, table)
// Partitioning is a table-level clause that follows the table options. The omni
// loader accepts the version-gated /*!50100 PARTITION BY … */ executable comment
// (its lexer splices the inner text back as live SQL), and the omni partition
// differ canonicalizes the expression and per-partition ENGINE, so reusing the
// mysqldump partition writer round-trips as a no-op without an SDL-specific form.
if len(table.Partitions) > 0 {
if err := printPartitionClause(buf, table.Partitions, table.Engine); err != nil {
return err
}
}
_, err := io.WriteString(buf, ";\n\n")
return err
}
// filterCreateOptions returns the table CREATE_OPTIONS string with the synthetic
// `partitioned` token removed. MySQL's information_schema.TABLES.CREATE_OPTIONS folds the
// fact that a table is partitioned into a literal `partitioned` token alongside the real
// declared options (e.g. `partitioned`, or `row_format=DYNAMIC partitioned`). Partitioning
// is emitted separately via the PARTITION BY clause from TableMetadata.Partitions, so the
// token must not be rendered as a table option (it is not valid table-option DDL). The
// remaining tokens (row_format=…, key_block_size=…, …) are already valid `name=value`
// table-option syntax that the omni loader parses case-insensitively, so they are returned
// verbatim and joined by single spaces.
func filterCreateOptions(createOptions string) string {
if createOptions == "" {
return ""
}
kept := make([]string, 0, 4)
// The system-versioning marker is written separately by writeSystemVersioningOption.
for _, tok := range strings.Fields(strings.Replace(createOptions, systemVersioningOption, "", 1)) {
if strings.EqualFold(tok, "partitioned") {
continue
}
kept = append(kept, tok)
}
return strings.Join(kept, " ")
}
// writeViewSDL emits a single CREATE OR REPLACE VIEW statement. LoadSDL accepts
// CREATE OR REPLACE VIEW and is order-tolerant, so no temporary-view scaffolding is
// needed. dbName is the database being dumped; its own-database qualifier is stripped
// from the body so the emitted form matches the user's unqualified spelling (see
// stripViewBodyDatabaseQualifier — required for 5.7 derived-table view idempotence,
// entry TestSDLStressViewDerivedTable57). The stored one-line body is then
// pretty-printed by formatViewBodySDL — a whitespace-only, deterministic rewrite, so
// the omni no-op invariant and the dump-cycle byte stability both hold.
func writeViewSDL(buf *strings.Builder, dbName string, view *metadatapb.ViewMetadata) error {
body := formatViewBodySDL(stripViewBodyDatabaseQualifier(view.Definition, dbName))
if body == "" {
// Defensive: an empty definition keeps the historical inline framing.
if _, err := fmt.Fprintf(buf, "CREATE OR REPLACE VIEW `%s` AS ;\n\n", view.Name); err != nil {
return err
}
return nil
}
if _, err := fmt.Fprintf(buf, "CREATE OR REPLACE VIEW `%s` AS\n%s;\n\n", view.Name, body); err != nil {
return err
}
return nil
}
// stripViewBodyDatabaseQualifier removes the dumped database's own-database qualifier
// (the leading “ `<dbName>`. “ prefix on a table/column reference) from a view body.
//
// MySQL 5.7's SHOW CREATE VIEW / information_schema.VIEWS.VIEW_DEFINITION fully qualifies
// references — including the inner refs of a FROM-clause derived table — with the schema
// name (e.g. “ `mydb`.`assignment` “), whereas 8.0 stores them unqualified and the user
// authors them unqualified. The omni SDL diff loads both inputs under the synthetic
// database bbcatalog and its canonicalViewBody only folds the loaded own-database prefix
// (bbcatalog), so a body still carrying the real synced DB name phantom-diffs against the
// unqualified user body. Stripping the qualifier at dump time yields the db-neutral form
// both 5.7 and 8.0 share.
//
// Only the current database's own qualifier is removed (genuine cross-database references,
// which MySQL views in this single-database scope effectively never carry, are left
// intact). The body is walked with scanViewBodyToken — the same quote/identifier-aware
// scanner formatViewBodySDL uses — so ONE scanner owns the literal rules: a
// “ `<dbName>`. “ sequence inside a '…' or "…" string literal (or inside a backtick
// identifier such as an alias) is NOT a qualifier and is left untouched.
//
// A qualifier is recognized only where one can grammatically start: a backtick
// identifier spelling dbName, immediately followed by "." and another backtick
// identifier, and NOT itself preceded by "." (then it is the table/column part of an
// enclosing dotted reference, e.g. the table part of `otherdb`.`<dbName>`.`c`). After a
// strip, the following identifier is copied verbatim BEFORE re-arming, so a table named
// like the database (`<dbName>`.`<dbName>`.`col`, the 5.7 three-part form) loses only
// the database part.
func stripViewBodyDatabaseQualifier(body, dbName string) string {
if dbName == "" || body == "" {
return body
}
quoted := "`" + strings.ReplaceAll(dbName, "`", "``") + "`"
if !strings.Contains(body, quoted+".") {
return body
}
var out strings.Builder
out.Grow(len(body))
// prevTok is the previous non-whitespace token; "." marks the current identifier as
// a continuation part of a dotted reference, never a qualifier start.
prevTok := ""
for i := 0; i < len(body); {
if isViewBodySpaceByte(body[i]) {
out.WriteByte(body[i])
i++
continue
}
end := scanViewBodyToken(body, i)
tok := body[i:end]
if tok == quoted && prevTok != "." && end < len(body) && body[end] == '.' && end+1 < len(body) && body[end+1] == '`' {
// Own-database qualifier: drop it and the dot, then copy the following
// identifier verbatim so it is not re-tested as a qualifier.
identEnd := scanViewBodyToken(body, end+1)
ident := body[end+1 : identEnd]
out.WriteString(ident)
prevTok = ident
i = identEnd
continue
}
out.WriteString(tok)
prevTok = tok
i = end
}
return out.String()
}
// writeRoutineSDL emits a stored function or procedure. The synced Definition is
// already a complete, canonical CREATE FUNCTION/PROCEDURE statement (the MySQL sync
// rewrites SHOW CREATE to drop the DEFINER and the RETURNS charset attribute), so the
// only framing the SDL form adds is a terminating ";". The body is emitted verbatim:
// omni's loader splitter is BEGIN…END aware, so internal ";" inside a compound body
// do not need DELIMITER wrapping, and omni's routine differ compares the body byte for
// byte after trimming — reformatting would phantom-diff. A leading DEFINER, if one
// somehow survives, is stripped to match omni's DEFINER-agnostic routine identity.
func writeRoutineSDL(buf *strings.Builder, definition string) error {
def := strings.TrimSpace(stripLeadingDefiner(definition))
if def == "" {
return nil
}
if _, err := fmt.Fprintf(buf, "%s;\n\n", def); err != nil {
return err
}
return nil
}
// writeTriggerSDL constructs a canonical CREATE TRIGGER from the trigger's metadata.
// MySQL stores only the action statement (TriggerMetadata.Body) plus the timing,
// event, and owning table, so the CREATE wrapper is rebuilt here exactly as omni's
// loader expects: `CREATE TRIGGER <name> <timing> <event> ON <table> FOR EACH ROW
// <body>`. DEFINER is omitted (omni's trigger differ ignores it) and the body is
// emitted verbatim (compared byte for byte after trimming).
func writeTriggerSDL(buf *strings.Builder, tableName string, trigger *metadatapb.TriggerMetadata) error {
if _, err := fmt.Fprintf(buf, "CREATE TRIGGER `%s` %s %s ON `%s` FOR EACH ROW\n%s;\n\n",
trigger.Name, trigger.Timing, trigger.Event, tableName, strings.TrimSpace(trigger.Body)); err != nil {
return err
}
return nil
}
// writeEventSDL emits a scheduled event. The synced Definition is the full
// SHOW CREATE EVENT text, which (unlike functions/procedures) still carries the
// DEFINER clause, so it is stripped here to match omni's DEFINER-agnostic event
// identity. The schedule's auto-injected STARTS '<create-time>' is left intact: omni's
// event differ normalizes STARTS out of the canonical schedule key, so it does not
// perturb the no-op.
func writeEventSDL(buf *strings.Builder, event *metadatapb.EventMetadata) error {
def := strings.TrimSpace(stripLeadingDefiner(event.Definition))
if def == "" {
return nil
}
if _, err := fmt.Fprintf(buf, "%s;\n\n", def); err != nil {
return err
}
return nil
}
// stripLeadingDefiner removes a leading `CREATE DEFINER=<account> ` clause from a
// CREATE statement, collapsing it back to a bare `CREATE <OBJECT> …`. The account is
// parsed properly — `user`@`host` with each part backtick, single- or double-quoted
// (quoted parts may contain spaces, '@', and doubled/escaped quotes), an unquoted
// user@host, or CURRENT_USER[()] — so a legal quoted account with a space
// (DEFINER=`my user`@`%`) does not get cut at its first space. Statements without a
// DEFINER, or with an account this scan cannot parse, are returned unchanged (omni
// tolerates a DEFINER and never diffs on it, so keeping one is safe while corrupting
// the statement is not). Stripping keeps the dump canonical and matches the
// routine/trigger emission, which carry no DEFINER at all.
func stripLeadingDefiner(definition string) string {
const createKW = "CREATE "
trimmed := strings.TrimLeft(definition, " \t\r\n")
if !strings.HasPrefix(strings.ToUpper(trimmed), createKW) {
return definition
}
rest := strings.TrimLeft(trimmed[len(createKW):], " \t")
const definerKW = "DEFINER"
if !strings.HasPrefix(strings.ToUpper(rest), definerKW) {
return definition
}
pos := len(definerKW)
pos = skipSpacesAndTabs(rest, pos)
if pos >= len(rest) || rest[pos] != '=' {
return definition
}
pos = skipSpacesAndTabs(rest, pos+1)
end, ok := scanDefinerAccount(rest, pos)
if !ok {
return definition
}
// The clause must be followed by whitespace and the object keyword.
afterAccount := skipSpacesAndTabs(rest, end)
if afterAccount == end || afterAccount >= len(rest) {
return definition
}
return createKW + rest[afterAccount:]
}
// skipSpacesAndTabs returns the first offset >= pos in s that is not a space or tab.
func skipSpacesAndTabs(s string, pos int) int {
for pos < len(s) && (s[pos] == ' ' || s[pos] == '\t') {
pos++
}
return pos
}
// scanDefinerAccount scans a MySQL account name starting at pos: CURRENT_USER[()] or
// <part>[@<part>] where each part is backtick/single/double quoted (doubled-delimiter
// and, for '/'"', backslash escapes) or an unquoted run. Returns the offset just past
// the account and whether the scan succeeded.
func scanDefinerAccount(s string, pos int) (int, bool) {
const currentUser = "CURRENT_USER"
if len(s)-pos >= len(currentUser) && strings.EqualFold(s[pos:pos+len(currentUser)], currentUser) {
end := pos + len(currentUser)
if strings.HasPrefix(s[end:], "()") {
end += 2
}
return end, true
}
end, ok := scanAccountPart(s, pos)
if !ok {
return 0, false
}
if end < len(s) && s[end] == '@' {
hostEnd, ok := scanAccountPart(s, end+1)
if !ok {
return 0, false
}
end = hostEnd
}
return end, true
}
// scanAccountPart scans one user or host part of an account name starting at pos.
func scanAccountPart(s string, pos int) (int, bool) {
if pos >= len(s) {
return 0, false
}
switch q := s[pos]; q {
case '`', '\'', '"':
for i := pos + 1; i < len(s); {
switch {
case q != '`' && s[i] == '\\' && i+1 < len(s):
// Backslash escapes apply inside '…' and "…" (not backticks).
i += 2
case s[i] == q:
if i+1 < len(s) && s[i+1] == q {
// Doubled delimiter stays inside the part.
i += 2
continue
}
return i + 1, true
default:
i++
}
}
return 0, false // Unterminated quote.
default:
// Unquoted part: a run of bytes up to the '@' separator or whitespace.
i := pos
for i < len(s) && s[i] != '@' && s[i] != ' ' && s[i] != '\t' && s[i] != '\r' && s[i] != '\n' {
i++
}
if i == pos {
return 0, false
}
return i, true
}
}