Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

47.6 KB
Raw Blame
package oceanbase

import (
	"bytes"
	"context"
	"database/sql"
	"encoding/hex"
	"fmt"
	"log/slog"
	"regexp"
	"slices"
	"strconv"
	"strings"
	"unicode"
	"unicode/utf8"

	metadatapb "github.com/bytebase/omni/metadata"
	"golang.org/x/text/encoding/charmap"
	"golang.org/x/text/transform"
	"google.golang.org/protobuf/proto"

	"github.com/blang/semver/v4"
	"github.com/pkg/errors"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/common/log"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/db"
	"github.com/bytebase/bytebase/backend/plugin/db/util"
)

const (
	autoIncrementSymbol    = "AUTO_INCREMENT"
	autoRandSymbol         = "AUTO_RANDOM"
	pkAutoRandomBitsSymbol = "PK_AUTO_RANDOM_BITS"
	virtualGenerated       = "VIRTUAL GENERATED"
	storedGenerated        = "STORED GENERATED"
)

var (
	systemDatabases = map[string]bool{
		"information_schema": true,
		"mysql":              true,
		"performance_schema": true,
		"sys":                true,
		// OceanBase only
		"oceanbase":  true,
		"SYS":        true,
		"LBACSYS":    true,
		"ORAAUDITOR": true,
		"__public":   true,
	}
	systemDatabaseClause = func() string {
		var l []string
		for k := range systemDatabases {
			l = append(l, fmt.Sprintf("'%s'", k))
		}
		return strings.Join(l, ", ")
	}()

	// Matches SHOW CREATE VIEW output: optional `db`. qualifier on the view name and an
	// optional explicit column list, which MySQL prints comma-separated: (`a`,`b`,`c`).
	// Column-list identifiers are rendered with embedded backticks doubled (`a``b`), so
	// each list segment matches doubling-aware atoms rather than [^`]+ — an identifier
	// containing a backtick (or ", ") must not mis-split the list.
	viewDefMatcher = regexp.MustCompile("CREATE ALGORITHM=(UNDEFINED|MERGE|TEMPTABLE) DEFINER=`([^`]+)`@`([^`]+)` SQL SECURITY (DEFINER|INVOKER) VIEW `([^`]+)`(?:\\.`([^`]+)`)?( \\(`(?:[^`]|``)+`(?:, ?`(?:[^`]|``)+`)*\\))? AS (?P<def>.+)")
)

// isStockMySQL reports whether the version suffix returned by getVersion (the text
// after the numeric version, e.g. "-MariaDB", "-OceanBase-v4.2", "-TiDB-v7.5") denotes
// a stock MySQL server. MySQL-compatible engines registered as MYSQL report MySQL-like
// version numbers but do not implement every stock information_schema feature, so any
// stock-only query or decode (SRS_ID column select, binary default encodings, column
// INVISIBLE capture) must be gated on this single predicate.
func isStockMySQL(versionSuffix string) bool {
	return !strings.Contains(versionSuffix, "MariaDB") &&
		!strings.Contains(versionSuffix, "OceanBase") &&
		!strings.Contains(versionSuffix, "TiDB")
}

// SyncInstance syncs the instance.
func (d *Driver) SyncInstance(ctx context.Context) (*db.InstanceMetadata, error) {
	version, _, err := d.getVersion(ctx)
	if err != nil {
		return nil, err
	}

	var lowerCaseTableNames int32
	lowerCaseTableNamesText, err := d.getServerVariable(ctx, "lower_case_table_names")
	if err != nil {
		slog.Debug("failed to get lower_case_table_names variable", log.BBError(err))
	} else {
		v, err := strconv.ParseInt(lowerCaseTableNamesText, 10, 32)
		if err != nil {
			slog.Debug("failed to parse lower_case_table_names variable", log.BBError(err))
		} else {
			lowerCaseTableNames = int32(v)
		}
	}

	instanceRoles := d.getInstanceRoles(ctx)

	// Query db info
	where := fmt.Sprintf("LOWER(SCHEMA_NAME) NOT IN (%s)", systemDatabaseClause)
	query := `
		SELECT
			SCHEMA_NAME,
			DEFAULT_CHARACTER_SET_NAME,
			DEFAULT_COLLATION_NAME
		FROM information_schema.SCHEMATA
		WHERE ` + where
	rows, err := d.db.QueryContext(ctx, query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	var databases []*metadatapb.DatabaseSchemaMetadata
	for rows.Next() {
		database := &metadatapb.DatabaseSchemaMetadata{}
		if err := rows.Scan(
			&database.Name,
			&database.CharacterSet,
			&database.Collation,
		); err != nil {
			return nil, err
		}
		databases = append(databases, database)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return &db.InstanceMetadata{
		Version:   version,
		Databases: databases,
		Metadata: &storepb.Instance{
			MysqlLowerCaseTableNames: lowerCaseTableNames,
			Roles:                    instanceRoles,
		},
	}, nil
}

func (d *Driver) getServerVariable(ctx context.Context, varName string) (string, error) {
	db := d.GetDB()
	query := fmt.Sprintf("SHOW VARIABLES LIKE '%s'", varName)
	var varNameFound, value string
	if err := db.QueryRowContext(ctx, query).Scan(&varNameFound, &value); err != nil {
		if err == sql.ErrNoRows {
			return "", common.FormatDBErrorEmptyRowWithQuery(query)
		}
		return "", util.FormatErrorWithQuery(err, query)
	}
	if varName != varNameFound {
		return "", errors.Errorf("expecting variable %s, but got %s", varName, varNameFound)
	}
	return value, nil
}

func containsInvisibleChars(data []byte) bool {
	// Iterate over the byte slice as runes
	for len(data) > 0 {
		r, size := utf8.DecodeRune(data)
		if r == utf8.RuneError && size == 1 {
			// If the byte slice contains invalid UTF-8 characters, treat it as invisible
			return true
		}
		// Check if the rune is not printable
		if !unicode.IsPrint(r) {
			return true
		}
		// Move to the next rune
		data = data[size:]
	}
	return false
}

func utf8ToISO88591(utf8Str string) (string, error) {
	// Create a transformer that encodes UTF-8 to ISO-8859-1
	encoder := charmap.ISO8859_1.NewEncoder()

	// Transform the input UTF-8 string into ISO-8859-1
	isoBytes, _, err := transform.String(encoder, utf8Str)
	if err != nil {
		return "", err
	}

	return isoBytes, nil
}

// SyncDBSchema syncs a single database schema.
func (d *Driver) SyncDBSchema(ctx context.Context) (*metadatapb.DatabaseSchemaMetadata, error) {
	schemaMetadata := &metadatapb.SchemaMetadata{
		Name: "",
	}

	// Query MySQL version
	version, rest, err := d.getVersion(ctx)
	if err != nil {
		return nil, err
	}
	semVersion, err := semver.Make(version)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to parse MySQL version %s to semantic version", version)
	}
	atLeast800 := semVersion.GE(semver.MustParse("8.0.0"))
	atLeast803 := semVersion.GE(semver.MustParse("8.0.3"))
	atLeast8_0_13 := semVersion.GE(semver.MustParse("8.0.13"))
	atLeast8_0_16 := semVersion.GE(semver.MustParse("8.0.16"))
	atLeast5_7_0 := semVersion.GE(semver.MustParse("5.7.0"))
	isMariaDB := strings.Contains(rest, "MariaDB")
	// Some information_schema features exist only on stock MySQL: compatible engines
	// (MariaDB, OceanBase, TiDB) registered as MYSQL report MySQL-like versions but
	// lack them, so every stock-only query/decode must share this one predicate.
	stockMySQL := isStockMySQL(rest)
	// Binary-family (binary charset) literal defaults are reported by
	// information_schema.COLUMNS in version-specific encodings that must be decoded to
	// the canonical dump form (see canonicalBinaryDefault). The conventions are verified
	// for stock MySQL only; MariaDB, OceanBase, and TiDB keep the legacy verbatim path.
	binaryDefaultFmt := binaryDefaultVerbatim
	if stockMySQL {
		if atLeast800 {
			binaryDefaultFmt = binaryDefaultHexNotation
		} else {
			binaryDefaultFmt = binaryDefaultRawBytes
		}
	}

	// Query index info.
	indexMap := make(map[db.TableKey]map[string]*metadatapb.IndexMetadata)
	indexQuery := `
		SELECT
			TABLE_NAME,
			INDEX_NAME,
			COLUMN_NAME,
			COLLATION,
			IFNULL(SUB_PART, -1),
			'',
			SEQ_IN_INDEX,
			INDEX_TYPE,
			NON_UNIQUE,
			1,
			INDEX_COMMENT
		FROM information_schema.STATISTICS
		WHERE TABLE_SCHEMA = ?
		ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX`
	// MySQL 8.0.13 introduced the EXPRESSION column in the INFORMATION_SCHEMA.STATISTICS table.
	// https://dev.mysql.com/doc/refman/8.0/en/information-schema-statistics-table.html
	// MariaDB doesn't have the EXPRESSION column.
	// https://mariadb.com/docs/server/ref/mdb/information-schema/STATISTICS
	if atLeast8_0_13 && !isMariaDB {
		indexQuery = `
			SELECT
				TABLE_NAME,
				INDEX_NAME,
				COLUMN_NAME,
				COLLATION,
				IFNULL(SUB_PART, -1),
				EXPRESSION,
				SEQ_IN_INDEX,
				INDEX_TYPE,
				NON_UNIQUE,
				CASE IS_VISIBLE WHEN 'YES' THEN 1 ELSE 0 END,
				INDEX_COMMENT
			FROM information_schema.STATISTICS
			WHERE TABLE_SCHEMA = ?
			ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX`
	}
	indexRows, err := d.db.QueryContext(ctx, indexQuery, d.databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, indexQuery)
	}
	defer indexRows.Close()
	for indexRows.Next() {
		var tableName, indexName, indexType, comment, expression string
		var columnName sql.NullString
		var expressionName sql.NullString
		var collation sql.NullString
		var position int
		var subPart int64
		var nonUnique, visible bool
		if err := indexRows.Scan(
			&tableName,
			&indexName,
			&columnName,
			&collation,
			&subPart,
			&expressionName,
			&position,
			&indexType,
			&nonUnique,
			&visible,
			&comment,
		); err != nil {
			return nil, err
		}
		if columnName.Valid {
			expression = columnName.String
		} else if expressionName.Valid {
			// It's a bit late or not necessary to differentiate the column name or expression.
			// We add parentheses around expression.
			expression = fmt.Sprintf("(%s)", expressionName.String)
		}

		desc := false
		if collation.Valid && collation.String == "D" {
			desc = true
		}

		key := db.TableKey{Schema: "", Table: tableName}
		if _, ok := indexMap[key]; !ok {
			indexMap[key] = make(map[string]*metadatapb.IndexMetadata)
		}
		if _, ok := indexMap[key][indexName]; !ok {
			indexMap[key][indexName] = &metadatapb.IndexMetadata{
				Name:    indexName,
				Type:    indexType,
				Unique:  !nonUnique,
				Primary: indexName == "PRIMARY",
				Visible: visible,
				Comment: comment,
			}
		}
		indexMap[key][indexName].Expressions = append(indexMap[key][indexName].Expressions, expression)
		indexMap[key][indexName].KeyLength = append(indexMap[key][indexName].KeyLength, subPart)
		indexMap[key][indexName].Descending = append(indexMap[key][indexName].Descending, desc)
	}
	if err := indexRows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, indexQuery)
	}

	// Query column info.
	// SRS_ID (spatial reference id) was added to information_schema.COLUMNS in stock
	// MySQL 8.0.3 (the same version encoded by the /*!80003 SRID */ renderer). Stock
	// 8.0.0–8.0.2 lack it, as do 5.7/5.6, MariaDB, OceanBase, and TiDB (which may report
	// an 8.x MySQL-compat version), so selecting it there would fail the whole sync —
	// they select NULL instead.
	columnMap := make(map[db.TableKey][]*metadatapb.ColumnMetadata)
	sridSelect := "NULL"
	if atLeast803 && stockMySQL {
		sridSelect = "SRS_ID"
	}
	columnQuery := fmt.Sprintf(`
		SELECT
			TABLE_NAME,
			IFNULL(COLUMN_NAME, ''),
			ORDINAL_POSITION,
			CASE WHEN COLUMN_DEFAULT is NULL THEN NULL ELSE QUOTE(COLUMN_DEFAULT) END,
			IS_NULLABLE,
			COLUMN_TYPE,
			IFNULL(CHARACTER_SET_NAME, ''),
			IFNULL(COLLATION_NAME, ''),
			QUOTE(COLUMN_COMMENT),
			convert(GENERATION_EXPRESSION using BINARY),
			EXTRA,
			%s
		FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA = ?
		ORDER BY TABLE_NAME, ORDINAL_POSITION`, sridSelect)
	if !atLeast5_7_0 {
		// GENERATION_EXPRESSION does not exist in MySQL 5.6.
		columnQuery = fmt.Sprintf(`
		SELECT
			TABLE_NAME,
			IFNULL(COLUMN_NAME, ''),
			ORDINAL_POSITION,
			CASE WHEN COLUMN_DEFAULT is NULL THEN NULL ELSE QUOTE(COLUMN_DEFAULT) END,
			IS_NULLABLE,
			COLUMN_TYPE,
			IFNULL(CHARACTER_SET_NAME, ''),
			IFNULL(COLLATION_NAME, ''),
			QUOTE(COLUMN_COMMENT),
			NULL,
			EXTRA,
			%s
		FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA = ?
		ORDER BY TABLE_NAME, ORDINAL_POSITION`, sridSelect)
	}
	columnRows, err := d.db.QueryContext(ctx, columnQuery, d.databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, columnQuery)
	}
	defer columnRows.Close()
	for columnRows.Next() {
		column := &metadatapb.ColumnMetadata{}
		var tableName, nullable, extra, tp string
		var defaultStr sql.NullString
		var generationExpr []byte
		var srid sql.NullInt64
		if err := columnRows.Scan(
			&tableName,
			&column.Name,
			&column.Position,
			&defaultStr,
			&nullable,
			&tp,
			&column.CharacterSet,
			&column.Collation,
			&column.Comment,
			&generationExpr,
			&extra,
			&srid,
		); err != nil {
			return nil, err
		}
		// Quoted string has a single quote around it and is escaped by QUOTE().
		column.Comment = stripSingleQuote(column.Comment)
		nullableBool, err := util.ConvertYesNo(nullable)
		if err != nil {
			return nil, err
		}
		column.Type = GetColumnTypeCanonicalSynonym(tp)
		column.Nullable = nullableBool
		setColumnMetadataDefault(column, defaultStr, nullableBool, extra, binaryDefaultFmt)
		// SRID presence tracks SRS_ID being non-NULL: spatial columns with an explicit
		// SRID (MySQL 8.0), INCLUDING the valid SRID 0. SRS_IDs are unsigned 32-bit, so
		// the value must not be squeezed through int32 (custom SRSs may be >= 2^31).
		if srid.Valid {
			v := uint32(srid.Int64)
			column.Srid = &v
		}
		// A user-declared INVISIBLE column surfaces as the INVISIBLE token in EXTRA
		// (MySQL 8.0.23+). Captured on stock MySQL only: MariaDB 10.3+ also reports
		// INVISIBLE in EXTRA, but the writers emit the MySQL-versioned
		// /*!80023 INVISIBLE */ form, which MariaDB ignores — capturing there would
		// yield a permanently non-converging diff. On stock MySQL the dumper re-emits
		// the attribute and the declarative diff stays a no-op.
		if stockMySQL && isInvisibleColumnExtra(extra) {
			column.IsInvisible = true
		}
		key := db.TableKey{Schema: "", Table: tableName}
		columnMap[key] = append(columnMap[key], column)
		invisible := containsInvisibleChars(generationExpr)
		iso88591Text, convertedErr := utf8ToISO88591(string(generationExpr))
		text := string(generationExpr)
		if invisible && convertedErr == nil {
			text = iso88591Text
		}
		// MySQL server returns the generation expression with an escaped single quote.
		// I have no idea why it does that. But we need to unescape it. -_-
		text = strings.ReplaceAll(text, `\'`, `'`)
		if extra != "" && strings.Contains(strings.ToUpper(extra), virtualGenerated) && len(generationExpr) != 0 {
			column.Generation = &metadatapb.GenerationMetadata{
				Type:       metadatapb.GenerationMetadata_TYPE_VIRTUAL,
				Expression: text,
			}
		} else if extra != "" && strings.Contains(strings.ToUpper(extra), storedGenerated) && len(generationExpr) != 0 {
			column.Generation = &metadatapb.GenerationMetadata{
				Type:       metadatapb.GenerationMetadata_TYPE_STORED,
				Expression: text,
			}
		}
	}
	if err := columnRows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, columnQuery)
	}

	// Check constraints info.
	checkMap := make(map[db.TableKey][]*metadatapb.CheckConstraintMetadata)
	if atLeast8_0_16 {
		checkQuery := getCheckConstraintQuery(isMariaDB)
		checkRows, err := d.db.QueryContext(ctx, checkQuery, d.databaseName)
		if err != nil {
			return nil, util.FormatErrorWithQuery(err, checkQuery)
		}
		defer checkRows.Close()
		for checkRows.Next() {
			check := &metadatapb.CheckConstraintMetadata{}
			var tableName string
			if err := checkRows.Scan(
				&tableName,
				&check.Name,
				&check.Expression,
			); err != nil {
				return nil, err
			}
			check.Expression = unescapeCheckClause(check.Expression)
			key := db.TableKey{Schema: "", Table: tableName}
			checkMap[key] = append(checkMap[key], check)
		}
		if err := checkRows.Err(); err != nil {
			return nil, util.FormatErrorWithQuery(err, checkQuery)
		}
	}

	// Query view info.
	viewMap := make(map[db.TableKey]*metadatapb.ViewMetadata)
	viewQuery := `
		SELECT
			TABLE_NAME,
			VIEW_DEFINITION
		FROM information_schema.VIEWS
		WHERE TABLE_SCHEMA = ?`
	viewRows, err := d.db.QueryContext(ctx, viewQuery, d.databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, viewQuery)
	}
	defer viewRows.Close()
	for viewRows.Next() {
		view := &metadatapb.ViewMetadata{}
		if err := viewRows.Scan(
			&view.Name,
			&view.Definition,
		); err != nil {
			return nil, err
		}
		// Note: MySQL's information_schema.VIEWS doesn't have a comment column
		// View comments are not supported in MySQL
		view.Comment = ""
		key := db.TableKey{Schema: "", Table: view.Name}
		view.Columns = columnMap[key]
		viewMap[key] = view
	}
	if err := viewRows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, viewQuery)
	}
	for key := range viewMap {
		def, err := d.reconcileViewDefinition(ctx, d.databaseName, key.Table)
		if err != nil {
			return nil, err
		}
		if def != "" {
			viewMap[key].Definition = def
		}
	}

	// Query triggers.
	triggerMap, err := d.getTriggerList(ctx, d.databaseName)
	if err != nil {
		return nil, err
	}

	// Query events.
	eventList, err := d.getEventList(ctx, d.databaseName)
	if err != nil {
		return nil, err
	}
	schemaMetadata.Events = eventList

	// Query foreign key info.
	foreignKeysMap, err := d.getForeignKeyList(ctx, d.databaseName)
	if err != nil {
		return nil, err
	}

	// OceanBase's information_schema.PARTITIONS does not describe its partitions in the
	// shape SHOW CREATE TABLE prints, so the sync claims none.
	partitionTables := make(map[db.TableKey][]*metadatapb.TablePartitionMetadata)

	functions, procedures, err := d.syncRoutines(ctx, d.databaseName)
	if err != nil {
		return nil, err
	}
	schemaMetadata.Functions = functions
	schemaMetadata.Procedures = procedures

	// Query table info.
	tableQuery := `
		SELECT
			TABLES.TABLE_NAME,
			TABLES.TABLE_TYPE,
			IFNULL(TABLES.ENGINE, ''),
			IFNULL(TABLES.TABLE_COLLATION, ''),
			IFNULL(TABLES.TABLE_ROWS, 0),
			IFNULL(TABLES.DATA_LENGTH, 0),
			IFNULL(TABLES.INDEX_LENGTH, 0),
			IFNULL(TABLES.DATA_FREE, 0),
			IFNULL(TABLES.CREATE_OPTIONS, ''),
			QUOTE(IFNULL(TABLES.TABLE_COMMENT, '')),
			IFNULL(CCSA.CHARACTER_SET_NAME, '')
		FROM information_schema.TABLES TABLES
		LEFT JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
		ON TABLES.TABLE_COLLATION = CCSA.COLLATION_NAME
		WHERE TABLE_SCHEMA = ?
		ORDER BY TABLE_NAME`
	tableRows, err := d.db.QueryContext(ctx, tableQuery, d.databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, tableQuery)
	}
	defer tableRows.Close()
	for tableRows.Next() {
		var tableName, tableType, engine, collation, createOptions, comment, charset string
		var rowCount, dataSize, indexSize, dataFree int64
		// Workaround TiDB bug https://github.com/pingcap/tidb/issues/27970
		var tableCollation sql.NullString
		if err := tableRows.Scan(
			&tableName,
			&tableType,
			&engine,
			&collation,
			&rowCount,
			&dataSize,
			&indexSize,
			&dataFree,
			&createOptions,
			&comment,
			&charset,
		); err != nil {
			return nil, err
		}
		// Quoted string has a single quote around it and is escaped by QUOTE().
		comment = stripSingleQuote(comment)

		// Note: We should NOT skip partitioned tables here.
		// The CREATE_OPTIONS might contain 'partitioned' for partitioned tables,
		// but we still need to include them in the table list.
		// The partition details are fetched separately by listPartitionTables.

		key := db.TableKey{Schema: "", Table: tableName}
		switch tableType {
		case baseTableType:
			columns := columnMap[key]
			tableMetadata := &metadatapb.TableMetadata{
				Name:             tableName,
				Columns:          columns,
				ForeignKeys:      foreignKeysMap[key],
				Engine:           engine,
				Collation:        collation,
				RowCount:         rowCount,
				DataSize:         dataSize,
				IndexSize:        indexSize,
				DataFree:         dataFree,
				CreateOptions:    createOptions,
				Comment:          comment,
				Partitions:       partitionTables[key],
				CheckConstraints: checkMap[key],
				Charset:          charset,
				Triggers:         triggerMap[key],
			}
			if tableCollation.Valid {
				tableMetadata.Collation = tableCollation.String
			}
			var indexNames []string
			if indexes, ok := indexMap[key]; ok {
				for indexName := range indexes {
					indexNames = append(indexNames, indexName)
				}
				slices.Sort(indexNames)
				for _, indexName := range indexNames {
					tableMetadata.Indexes = append(tableMetadata.Indexes, indexes[indexName])
				}
			}

			schemaMetadata.Tables = append(schemaMetadata.Tables, tableMetadata)
		case viewTableType:
			if view, ok := viewMap[key]; ok {
				schemaMetadata.Views = append(schemaMetadata.Views, view)
			}
		case externalTableType:
			schemaMetadata.ExternalTables = append(schemaMetadata.ExternalTables, &metadatapb.ExternalTableMetadata{
				Name:    tableName,
				Columns: columnMap[key],
			})
		default:
			// Unknown table type, skip
		}
	}
	if err := tableRows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, tableQuery)
	}
	materializedViews, err := d.listMaterializedViews(ctx, d.databaseName, schemaMetadata.Tables)
	if err != nil {
		return nil, err
	}
	schemaMetadata.MaterializedViews = materializedViews

	databaseMetadata := &metadatapb.DatabaseSchemaMetadata{
		Name:    d.databaseName,
		Schemas: []*metadatapb.SchemaMetadata{schemaMetadata},
	}
	// Query db info.
	databaseQuery := `
		SELECT
			DEFAULT_CHARACTER_SET_NAME,
			DEFAULT_COLLATION_NAME
		FROM information_schema.SCHEMATA
		WHERE SCHEMA_NAME = ?`
	if err := d.db.QueryRowContext(ctx, databaseQuery, d.databaseName).Scan(
		&databaseMetadata.CharacterSet,
		&databaseMetadata.Collation,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, common.Errorf(common.NotFound, "database %q not found", d.databaseName)
		}
		return nil, err
	}

	return databaseMetadata, err
}

// listMaterializedViews reads the database's materialized views. OceanBase lists one
// in neither information_schema.TABLES nor VIEWS, only its storage table __mv_container_<id>,
// which carries the view's comment and indexes and stays synced as a table. A failed read, such
// as one without SELECT on the oceanbase database, leaves the list empty; a caller can tell,
// because a container table is then synced without its materialized view.
func (d *Driver) listMaterializedViews(ctx context.Context, databaseName string, tables []*metadatapb.TableMetadata) ([]*metadatapb.MaterializedViewMetadata, error) {
	query := `
		SELECT MVIEW_NAME, CONTAINER_NAME, QUERY
		FROM oceanbase.DBA_MVIEWS
		WHERE OWNER = ?
		ORDER BY MVIEW_NAME`
	rows, err := d.db.QueryContext(ctx, query, databaseName)
	if err != nil {
		if ctx.Err() != nil {
			return nil, ctx.Err()
		}
		slog.Warn("failed to list OceanBase materialized views", slog.String("database", databaseName), log.BBError(err))
		return nil, nil
	}
	defer rows.Close()
	containers := make(map[string]*metadatapb.TableMetadata, len(tables))
	for _, table := range tables {
		containers[table.Name] = table
	}
	var views []*metadatapb.MaterializedViewMetadata
	for rows.Next() {
		var name, containerName, definition string
		if err := rows.Scan(&name, &containerName, &definition); err != nil {
			return nil, err
		}
		view := &metadatapb.MaterializedViewMetadata{
			Name:       name,
			Definition: definition,
			// No MySQL-family DDL writer renders OceanBase's CREATE MATERIALIZED VIEW.
			SkipDump: true,
		}
		if container, ok := containers[containerName]; ok {
			view.Comment = container.Comment
			for _, index := range container.Indexes {
				view.Indexes = append(view.Indexes, proto.CloneOf(index))
			}
		}
		views = append(views, view)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	return views, nil
}

// getEventList lists the database's events by name only. OceanBase rejects SHOW CREATE
// EVENT, and information_schema.EVENTS cannot rebuild it: it shows the schedule in the session
// time zone, flattens a BEGIN...END body, and does not record the creating session's time zone,
// SQL mode, or character set. The definition stays empty, so the DDL writers in
// backend/plugin/schema/mysql skip the event.
//
// A database created under a dropped one's name also lists the dropped one's events, which
// information_schema cannot tell from its own; DISTINCT lists a name both have once.
func (d *Driver) getEventList(ctx context.Context, databaseName string) ([]*metadatapb.EventMetadata, error) {
	query := `
		SELECT DISTINCT EVENT_NAME
		FROM information_schema.EVENTS
		WHERE EVENT_SCHEMA = ?
		ORDER BY EVENT_NAME`
	rows, err := d.db.QueryContext(ctx, query, databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()
	var events []*metadatapb.EventMetadata
	for rows.Next() {
		event := &metadatapb.EventMetadata{}
		if err := rows.Scan(&event.Name); err != nil {
			return nil, err
		}
		events = append(events, event)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	return events, nil
}

func (d *Driver) getTriggerList(ctx context.Context, databaseName string) (map[db.TableKey][]*metadatapb.TriggerMetadata, error) {
	// Prune to a single database on MariaDB: filter by EVENT_OBJECT_SCHEMA (the
	// trigger's table schema), not TRIGGER_SCHEMA, which scans every database's
	// table metadata. A trigger lives in its table's schema, so they are equivalent.
	triggersQuery := `
	SELECT 
		TRIGGER_NAME,
		EVENT_OBJECT_TABLE,
		EVENT_MANIPULATION,
		ACTION_TIMING,
		ACTION_STATEMENT,
		SQL_MODE,
		CHARACTER_SET_CLIENT,
		COLLATION_CONNECTION
	FROM INFORMATION_SCHEMA.TRIGGERS
	WHERE EVENT_OBJECT_SCHEMA = ?
	ORDER BY EVENT_OBJECT_TABLE ASC, EVENT_MANIPULATION ASC, ACTION_TIMING ASC, ACTION_ORDER ASC;
	`
	triggerRows, err := d.db.QueryContext(ctx, triggersQuery, databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, triggersQuery)
	}
	defer triggerRows.Close()
	triggerMap := make(map[db.TableKey][]*metadatapb.TriggerMetadata)
	for triggerRows.Next() {
		var name, table, event, timing, statement, sqlMode, charsetClient, collationConnection string
		if err := triggerRows.Scan(
			&name,
			&table,
			&event,
			&timing,
			&statement,
			&sqlMode,
			&charsetClient,
			&collationConnection,
		); err != nil {
			return nil, err
		}
		trigger := &metadatapb.TriggerMetadata{
			Name:                name,
			Event:               event,
			Timing:              timing,
			Body:                statement,
			SqlMode:             sqlMode,
			CharacterSetClient:  charsetClient,
			CollationConnection: collationConnection,
			Comment:             "", // MySQL doesn't support trigger comments via information_schema
		}
		tableKey := db.TableKey{Schema: "", Table: table}
		triggerMap[tableKey] = append(triggerMap[tableKey], trigger)
	}
	if err := triggerRows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, triggersQuery)
	}
	return triggerMap, nil
}

func (d *Driver) syncRoutines(ctx context.Context, databaseName string) ([]*metadatapb.FunctionMetadata, []*metadatapb.ProcedureMetadata, error) {
	// Query functions and procedure info.
	routinesQuery := `
		SELECT
			ROUTINE_NAME,
			ROUTINE_TYPE,
			SQL_MODE,
			CHARACTER_SET_CLIENT,
			COLLATION_CONNECTION,
			DATABASE_COLLATION,
			IFNULL(ROUTINE_COMMENT, '')
		FROM
			INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_SCHEMA = ? AND ROUTINE_TYPE IN ('FUNCTION', 'PROCEDURE')
		ORDER BY ROUTINE_TYPE, ROUTINE_NAME;
	`
	routineRows, err := d.db.QueryContext(ctx, routinesQuery, d.databaseName)
	if err != nil {
		return nil, nil, util.FormatErrorWithQuery(err, routinesQuery)
	}
	defer routineRows.Close()
	var functions []*metadatapb.FunctionMetadata
	var procedures []*metadatapb.ProcedureMetadata
	for routineRows.Next() {
		var name, routineType, routineComment string
		var sqlMode, charsetClient, collationConnection, databaseCollation sql.NullString
		if err := routineRows.Scan(
			&name,
			&routineType,
			&sqlMode,
			&charsetClient,
			&collationConnection,
			&databaseCollation,
			&routineComment,
		); err != nil {
			return nil, nil, err
		}
		if strings.EqualFold(routineType, "PROCEDURE") {
			procedureDef, err := d.getCreateProcedureStmt(ctx, databaseName, name)
			if err != nil {
				return nil, nil, err
			}
			procedures = append(procedures, &metadatapb.ProcedureMetadata{
				Name:                name,
				Definition:          procedureDef,
				SqlMode:             sqlMode.String,
				CharacterSetClient:  charsetClient.String,
				CollationConnection: collationConnection.String,
				DatabaseCollation:   databaseCollation.String,
				Comment:             routineComment,
			})
		} else {
			functionDef, err := d.getCreateFunctionStmt(ctx, databaseName, name)
			if err != nil {
				return nil, nil, err
			}
			functions = append(functions, &metadatapb.FunctionMetadata{
				Name:                name,
				Definition:          functionDef,
				SqlMode:             sqlMode.String,
				CharacterSetClient:  charsetClient.String,
				CollationConnection: collationConnection.String,
				DatabaseCollation:   databaseCollation.String,
				Comment:             routineComment,
			})
		}
	}
	if err := routineRows.Err(); err != nil {
		return nil, nil, util.FormatErrorWithQuery(err, routinesQuery)
	}

	return functions, procedures, nil
}

func (d *Driver) getCreateFunctionStmt(ctx context.Context, databaseName, functionName string) (string, error) {
	query := fmt.Sprintf("SHOW CREATE FUNCTION `%s`.`%s`", databaseName, functionName)
	rows, err := d.db.QueryContext(ctx, query)
	if err != nil {
		return "", util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	var createFunction sql.NullString

	columns, err := rows.Columns()
	if err != nil {
		return "", err
	}
	defIdx := -1
	for i, column := range columns {
		if strings.EqualFold(column, "Create Function") {
			defIdx = i
			break
		}
	}

	if defIdx == -1 {
		return "", errors.Errorf("failed to find column Create Function")
	}

	for rows.Next() {
		dests := make([]any, len(columns))
		for i := 0; i < len(columns); i++ {
			if i == defIdx {
				dests[i] = &createFunction
				continue
			}
			dests[i] = new(string)
		}

		if err := rows.Scan(dests...); err != nil {
			return "", err
		}
	}

	if err := rows.Err(); err != nil {
		return "", err
	}

	if createFunction.Valid {
		f := createFunction.String

		functionSymbolIdx := strings.Index(f, " FUNCTION ")
		if functionSymbolIdx >= 0 {
			f = fmt.Sprintf("CREATE%s", f[functionSymbolIdx:])
		}

		f = stripReturnsCharset(f)

		return f, nil
	}
	return "", nil
}

// unescapeCheckClause unescapes the check constraint clause stored in
// information_schema.CHECK_CONSTRAINTS.CHECK_CLAUSE, which escapes single
// quotes as \' (e.g. (`type` = _utf8mb4\'A\')). The escaped form is not valid
// SQL and must be unescaped before being written back into DDL.
func unescapeCheckClause(clause string) string {
	return strings.ReplaceAll(clause, `\'`, `'`)
}

func getCheckConstraintQuery(isMariaDB bool) string {
	if isMariaDB {
		// MariaDB includes TABLE_NAME in CHECK_CONSTRAINTS. Joining TABLE_CONSTRAINTS
		// by constraint name/schema is both slower and incorrect because MariaDB check
		// names are scoped to a table, not the whole schema.
		return `
			SELECT
				TABLE_NAME,
				CONSTRAINT_NAME,
				CHECK_CLAUSE
			FROM information_schema.CHECK_CONSTRAINTS
			WHERE CONSTRAINT_SCHEMA = ?
		`
	}

	return `
			SELECT
				tc.TABLE_NAME,
				cc.CONSTRAINT_NAME,
				cc.CHECK_CLAUSE
			FROM information_schema.CHECK_CONSTRAINTS cc
				JOIN information_schema.TABLE_CONSTRAINTS tc
					ON cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
					AND cc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
			WHERE tc.CONSTRAINT_TYPE = 'CHECK' AND tc.TABLE_SCHEMA = ?
		`
}

// stripReturnsCharset removes the " CHARSET ... [COLLATE ...]" attributes that
// SHOW CREATE FUNCTION appends to the RETURNS clause. The search is anchored to
// the RETURNS line because the parameter list is preserved verbatim from the
// original definition: it can span multiple lines and may itself contain
// CHARSET attributes.
func stripReturnsCharset(def string) string {
	returnsIdx := strings.Index(def, " RETURNS ")
	if returnsIdx == -1 {
		return def
	}
	lineEnd := strings.Index(def[returnsIdx:], "\n")
	if lineEnd == -1 {
		return def
	}
	charsetIdx := strings.Index(def[returnsIdx:returnsIdx+lineEnd], " CHARSET ")
	if charsetIdx == -1 {
		return def
	}
	return def[:returnsIdx+charsetIdx] + def[returnsIdx+lineEnd:]
}

func (d *Driver) getCreateProcedureStmt(ctx context.Context, databaseName, functionName string) (string, error) {
	query := fmt.Sprintf("SHOW CREATE PROCEDURE `%s`.`%s`", databaseName, functionName)
	rows, err := d.db.QueryContext(ctx, query)
	if err != nil {
		return "", util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	var createProcedure sql.NullString

	columns, err := rows.Columns()
	if err != nil {
		return "", err
	}
	defIdx := -1
	for i, column := range columns {
		if strings.EqualFold(column, "Create Procedure") {
			defIdx = i
			break
		}
	}

	if defIdx == -1 {
		return "", errors.Errorf("failed to find column Create Procedure")
	}

	for rows.Next() {
		dests := make([]any, len(columns))
		for i := 0; i < len(columns); i++ {
			if i == defIdx {
				dests[i] = &createProcedure
				continue
			}
			dests[i] = new(string)
		}

		if err := rows.Scan(dests...); err != nil {
			return "", err
		}
	}

	if err := rows.Err(); err != nil {
		return "", err
	}

	if createProcedure.Valid {
		p := createProcedure.String

		procedureSymbolIdx := strings.Index(p, " PROCEDURE ")
		if procedureSymbolIdx >= 0 {
			p = fmt.Sprintf("CREATE%s", p[procedureSymbolIdx:])
		}

		return p, nil
	}
	return "", nil
}

// binaryDefaultFormat says how the connected server reports binary-family (binary
// charset) literal column defaults in information_schema.COLUMNS.COLUMN_DEFAULT.
// Verified live against MySQL 5.7.25 and 8.0.32; SHOW CREATE TABLE on both versions
// prints the full raw value as a quoted string, but information_schema does not.
type binaryDefaultFormat int

const (
	// binaryDefaultVerbatim keeps the legacy verbatim static-default path. Used for
	// MariaDB and OceanBase, whose information_schema conventions are not verified.
	binaryDefaultVerbatim binaryDefaultFormat = iota
	// binaryDefaultRawBytes: MySQL < 8.0 reports the raw value bytes — padded with
	// trailing NULs to the declared length for binary(N), and truncated at the first
	// byte that does not convert to the information_schema character set (e.g. 0xFF).
	binaryDefaultRawBytes
	// binaryDefaultHexNotation: MySQL >= 8.0 reports hexadecimal notation text
	// ("0xABCD"), built from the value truncated at its first NUL byte (which also
	// strips binary(N) padding).
	binaryDefaultHexNotation
)

func setColumnMetadataDefault(column *metadatapb.ColumnMetadata, defaultStr sql.NullString, nullableBool bool, extra string, binaryFormat binaryDefaultFormat) {
	if defaultStr.Valid {
		// MySQL handles these defaults differently:
		// 1. CURRENT_TIMESTAMP functions - stored as function names, need QUOTE() unescaping only
		// 2. Expression defaults - stored as escaped expression strings, need double unescaping
		// 3. Binary-family literal defaults - version-specific information_schema encodings,
		//    decoded to one canonical form (never CURRENT_TIMESTAMP: binary types do not
		//    support it, so raw bytes that merely spell it stay a literal)
		// 4. Static defaults - stored as literal values, keep quoted for mysqldump compatibility

		// First, remove QUOTE() escaping that was added by our SQL query
		unquotedDefault := UnquoteMySQLString(defaultStr.String)
		isBinaryDefault := binaryFormat != binaryDefaultVerbatim && isBinaryFamilyColumnType(column.Type)
		switch {
		case IsCurrentTimestampLike(unquotedDefault) && !isBinaryDefault:
			// CURRENT_TIMESTAMP, CURRENT_DATE, etc. - these are function names, not expressions
			// MySQL stores them without quotes in the catalog, so we just need QUOTE() unescaping
			column.Default = unquotedDefault
		case strings.Contains(extra, "DEFAULT_GENERATED"):
			// Expression defaults like DEFAULT (UUID()) or DEFAULT (JSON_OBJECT('key', value))
			// MySQL stores the expression as an escaped string in the catalog, then QUOTE() adds another layer
			// We need both QUOTE() unescaping and catalog storage unescaping
			unescapedDefault := UnescapeExpressionDefault(unquotedDefault)
			column.Default = fmt.Sprintf("(%s)", unescapedDefault)
		case isBinaryDefault:
			if canonical, ok := canonicalBinaryDefault(unquotedDefault, column.Type, binaryFormat); ok {
				column.Default = canonical
			} else {
				// Unexpected shape for this server version — keep the legacy verbatim form.
				column.Default = defaultStr.String
			}
		default:
			// Static defaults like DEFAULT 'hello' or DEFAULT 42
			// MySQL evaluates these at DDL time and stores them as literal values
			// Preserve quotes for mysqldump compatibility (mysqldump expects quoted literals)
			column.Default = defaultStr.String
		}
	} else if strings.Contains(strings.ToUpper(extra), autoIncrementSymbol) {
		// TODO(zp): refactor column default value.
		// Use the upper case to consistent with MySQL Dump.
		column.Default = autoIncrementSymbol
	} else if nullableBool {
		// This is NULL if the column has an explicit default of NULL,
		// or if the column definition includes no DEFAULT clause.
		// https://dev.mysql.com/doc/refman/8.0/en/information-schema-columns-table.html
		column.Default = "NULL"
	}

	if strings.Contains(extra, "on update CURRENT_TIMESTAMP") {
		re := regexp.MustCompile(`CURRENT_TIMESTAMP\((\d+)\)`)
		match := re.FindStringSubmatch(extra)
		if len(match) > 0 {
			digits := match[1]
			column.OnUpdate = fmt.Sprintf("CURRENT_TIMESTAMP(%s)", digits)
		} else {
			column.OnUpdate = "CURRENT_TIMESTAMP"
		}
	}
}

// isBinaryFamilyColumnType reports whether an information_schema COLUMN_TYPE spelling
// denotes a binary-charset string type (binary / varbinary / blob family), whose
// literal defaults information_schema reports in version-specific encodings.
func isBinaryFamilyColumnType(columnType string) bool {
	lower := strings.ToLower(strings.TrimSpace(columnType))
	switch {
	case lower == "binary" || strings.HasPrefix(lower, "binary("):
		return true
	case lower == "varbinary" || strings.HasPrefix(lower, "varbinary("):
		return true
	case lower == "tinyblob" || lower == "mediumblob" || lower == "longblob":
		return true
	case lower == "blob" || strings.HasPrefix(lower, "blob("):
		return true
	default:
		return false
	}
}

// isFixedBinaryColumnType reports whether COLUMN_TYPE is the fixed-length binary(N)
// type, whose values (including the stored default on 5.7) are right-padded with NUL
// bytes to the declared length. The padding is not part of the declared default.
func isFixedBinaryColumnType(columnType string) bool {
	lower := strings.ToLower(strings.TrimSpace(columnType))
	return lower == "binary" || strings.HasPrefix(lower, "binary(")
}

// canonicalBinaryDefault decodes a binary-family literal column default from the
// version-specific information_schema encoding (see binaryDefaultFormat) and renders
// it in the one canonical dump form shared by every version:
//
//   - an empty string literal (two single quotes) for the empty value (SHOW CREATE
//     prints binary(N) defaults NUL-padded; the padding is dropped because it is
//     reapplied on write and NUL bytes cannot live in the UTF-8 metadata/SDL text),
//   - the SHOW CREATE-style quoted string ('ab', with QUOTE()-style backslash escaping
//     of \ and ') when the bytes are clean printable text,
//   - an unquoted hex literal (0x610062) otherwise — value-equivalent input syntax that
//     both versions accept and that stays representable in UTF-8.
//
// Without this, 8.0's hex NOTATION text was re-quoted as a string (DEFAULT '0x6162'),
// which never converges: cross-version diffs phantom-MODIFY every binary default and
// re-applying a dump double-encodes it ('0x6162' -> '0x307836313632').
//
// The ok result is false when the text does not match the declared encoding (not hex
// notation on a >= 8.0 server); the caller falls back to the legacy verbatim form.
func canonicalBinaryDefault(unquoted, columnType string, format binaryDefaultFormat) (string, bool) {
	var value []byte
	switch {
	case format == binaryDefaultHexNotation && unquoted == "":
		// varbinary DEFAULT '' is reported as an empty string, not as "0x".
		value = nil
	case format == binaryDefaultHexNotation:
		if len(unquoted) < 2 || unquoted[0] != '0' || (unquoted[1] != 'x' && unquoted[1] != 'X') {
			return "", false
		}
		decoded, err := hex.DecodeString(unquoted[2:])
		if err != nil {
			return "", false
		}
		value = decoded
	default:
		value = []byte(unquoted)
	}
	if isFixedBinaryColumnType(columnType) {
		value = bytes.TrimRight(value, "\x00")
	}
	if len(value) == 0 {
		return "''", true
	}
	if isCleanDefaultText(value) {
		escaped := strings.ReplaceAll(string(value), `\`, `\\`)
		escaped = strings.ReplaceAll(escaped, `'`, `\'`)
		return "'" + escaped + "'", true
	}
	return "0x" + hex.EncodeToString(value), true
}

// isCleanDefaultText reports whether value can be embedded as a plain quoted string in
// UTF-8 SDL text: valid UTF-8 with no control bytes (NUL bytes in particular — the
// information_schema encodings differ on them and SDL must stay NUL-free).
func isCleanDefaultText(value []byte) bool {
	if !utf8.Valid(value) {
		return false
	}
	for _, b := range value {
		if b < 0x20 || b == 0x7F {
			return false
		}
	}
	return true
}

// isInvisibleColumnExtra reports whether the information_schema.COLUMNS.EXTRA value
// carries the standalone INVISIBLE token (MySQL 8.0.23+; MariaDB 10.3+ reports the
// same token, but the capture site gates on isStockMySQL — see SyncDBSchema). EXTRA
// can hold several space-separated tokens (e.g. "VIRTUAL GENERATED INVISIBLE" or
// "DEFAULT_GENERATED on update CURRENT_TIMESTAMP INVISIBLE"), so the token is matched
// as a whole field rather than a substring.
func isInvisibleColumnExtra(extra string) bool {
	for _, tok := range strings.Fields(extra) {
		if strings.EqualFold(tok, "INVISIBLE") {
			return true
		}
	}
	return false
}

// UnescapeExpressionDefault unescapes the default expression for MySQL.
// This handles the second layer of escaping that MySQL applies when storing
// expression defaults (like DEFAULT (UUID())) in system catalogs.
// MySQL stores expressions as escaped strings internally, separate from QUOTE() escaping.
func UnescapeExpressionDefault(s string) string {
	s = strings.ReplaceAll(s, `\'`, `'`) // unescape single quote
	s = strings.ReplaceAll(s, `\\`, `\`) // unescape backslash
	return s
}

func IsCurrentTimestampLike(s string) bool {
	upper := strings.ToUpper(s)
	if strings.HasPrefix(upper, "CURRENT_TIMESTAMP") {
		return true
	}
	if strings.HasPrefix(upper, "CURRENT_DATE") {
		return true
	}
	return false
}

func (d *Driver) reconcileViewDefinition(ctx context.Context, databaseName, viewName string) (string, error) {
	query := fmt.Sprintf("SHOW CREATE VIEW `%s`.`%s`", databaseName, viewName)
	var createStmt, unused string
	if err := d.db.QueryRowContext(ctx, query).Scan(&unused, &createStmt, &unused, &unused); err != nil {
		if noRows := errors.Is(err, sql.ErrNoRows); noRows {
			slog.Warn("no rows return for query show create view", slog.String("viewName", viewName), slog.String("databaseName", databaseName))
			return "", nil
		}
		return "", errors.Wrapf(err, "failed to scan row for query: %s", query)
	}

	def, err := getViewDefFromCreateView(createStmt)
	if err != nil {
		slog.Warn("failed to get view definition", slog.String("viewName", viewName), slog.String("databaseName", databaseName), log.BBError(err))
		return "", nil
	}

	return def, nil
}

func getViewDefFromCreateView(createView string) (string, error) {
	viewDefMatching := viewDefMatcher.FindStringSubmatch(createView)
	if len(viewDefMatching) == 0 {
		return "", errors.Errorf("failed to match view definition, %s", createView)
	}
	for i, name := range viewDefMatcher.SubexpNames() {
		if name == "def" && i < len(viewDefMatching) {
			return viewDefMatching[i], nil
		}
	}
	return "", errors.Errorf("failed to match view definition, %s", createView)
}

func (d *Driver) getForeignKeyList(ctx context.Context, databaseName string) (map[db.TableKey][]*metadatapb.ForeignKeyMetadata, error) {
	// Prune to a single database on MariaDB instead of opening every database's
	// table metadata (Error 1969 on large instances). REFERENTIAL_CONSTRAINTS prunes
	// on CONSTRAINT_SCHEMA; KEY_COLUMN_USAGE prunes only on TABLE_SCHEMA. A foreign
	// key lives in its table's schema, so the predicates are equivalent. Never wrap
	// the column in LOWER() -- it defeats the pruning.
	fkQuery := `
		SELECT
			TABLE_NAME,
			CONSTRAINT_NAME,
			REFERENCED_TABLE_NAME,
			DELETE_RULE,
			UPDATE_RULE,
			MATCH_OPTION
		FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
		WHERE CONSTRAINT_SCHEMA = ?;
	`

	kcuQuery := `
		SELECT
			TABLE_NAME,
			CONSTRAINT_NAME,
			COLUMN_NAME,
			REFERENCED_COLUMN_NAME
		FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
		WHERE POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL AND TABLE_SCHEMA = ?
		ORDER BY TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION;
	`

	fkRows, err := d.db.QueryContext(ctx, fkQuery, databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, fkQuery)
	}
	defer fkRows.Close()
	fkMap := make(map[db.IndexKey]*metadatapb.ForeignKeyMetadata)
	for fkRows.Next() {
		var tableName string
		var fk metadatapb.ForeignKeyMetadata
		if err := fkRows.Scan(
			&tableName,
			&fk.Name,
			&fk.ReferencedTable,
			&fk.OnDelete,
			&fk.OnUpdate,
			&fk.MatchType,
		); err != nil {
			return nil, err
		}
		key := db.IndexKey{Schema: "", Table: tableName, Index: fk.Name}
		fkMap[key] = &fk
	}
	if err := fkRows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, fkQuery)
	}

	kcuQueryRows, err := d.db.QueryContext(ctx, kcuQuery, databaseName)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, kcuQuery)
	}
	defer kcuQueryRows.Close()
	for kcuQueryRows.Next() {
		var tableName, fkName, column, referencedColumn string
		if err := kcuQueryRows.Scan(
			&tableName,
			&fkName,
			&column,
			&referencedColumn,
		); err != nil {
			return nil, err
		}
		key := db.IndexKey{Schema: "", Table: tableName, Index: fkName}
		if fk, ok := fkMap[key]; ok {
			fk.Columns = append(fk.Columns, column)
			fk.ReferencedColumns = append(fk.ReferencedColumns, referencedColumn)
		}
	}
	if err := kcuQueryRows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, kcuQuery)
	}
	unordered := make(map[db.TableKey][]*metadatapb.ForeignKeyMetadata)
	for key, fk := range fkMap {
		tableKey := db.TableKey{Schema: "", Table: key.Table}
		unordered[tableKey] = append(unordered[tableKey], fk)
	}

	orderedResult := make(map[db.TableKey][]*metadatapb.ForeignKeyMetadata)
	for key, fks := range unordered {
		slices.SortFunc(fks, func(x, y *metadatapb.ForeignKeyMetadata) int {
			if x.Name < y.Name {
				return -1
			} else if x.Name > y.Name {
				return 1
			}
			return 0
		})
		orderedResult[key] = fks
	}
	return orderedResult, nil
}

func stripSingleQuote(s string) string {
	if len(s) >= 2 && s[0] == '\'' && s[len(s)-1] == '\'' {
		return s[1 : len(s)-1]
	}
	return s
}

// UnquoteMySQLString unescapes a string that was escaped by MySQL's QUOTE() function.
// MySQL's QUOTE() function escapes:
// - \ → \\
// - ' → \'
// - ASCII NUL (0x00) → \0
// - Control-Z (0x1A) → \Z
func UnquoteMySQLString(s string) string {
	if len(s) == 0 {
		return s
	}

	// First remove surrounding quotes if present
	s = stripSingleQuote(s)

	// Now unescape the content
	result := make([]byte, 0, len(s))
	for i := 0; i < len(s); i++ {
		if s[i] == '\\' && i+1 < len(s) {
			switch s[i+1] {
			case '\\':
				result = append(result, '\\')
				i++ // skip next character
			case '\'':
				result = append(result, '\'')
				i++ // skip next character
			case '0':
				result = append(result, 0) // ASCII NUL
				i++                        // skip next character
			case 'Z':
				result = append(result, 0x1A) // Control-Z
				i++                           // skip next character
			case 'n':
				result = append(result, '\n') // newline
				i++                           // skip next character
			case 't':
				result = append(result, '\t') // tab
				i++                           // skip next character
			case 'r':
				result = append(result, '\r') // carriage return
				i++                           // skip next character
			case 'b':
				result = append(result, '\b') // backspace
				i++                           // skip next character
			default:
				// Unknown escape sequence, keep the backslash
				result = append(result, s[i])
			}
		} else {
			result = append(result, s[i])
		}
	}
	return string(result)
}