Skip to content

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

20.7 KB
Raw Blame
package mariadb

import (
	"context"
	"fmt"
	"log/slog"
	"slices"
	"strings"

	"github.com/pkg/errors"

	"github.com/bytebase/omni/mariadb/ast"
	mariadbparser "github.com/bytebase/omni/mariadb/parser"

	"github.com/bytebase/bytebase/backend/common"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/parser/base"
	"github.com/bytebase/bytebase/backend/store/model"
)

func init() {
	base.RegisterTransformDMLToSelect(storepb.Engine_MARIADB, TransformDMLToSelect)
}

const (
	maxTableNameLength = 64
)

type StatementType int

const (
	StatementTypeUnknown StatementType = iota
	StatementTypeUpdate
	StatementTypeInsert
	StatementTypeDelete
)

type TableReference struct {
	Database      string
	Table         string
	Alias         string
	StatementType StatementType
}

type StatementInfo struct {
	Offset        int
	Statement     string
	Node          ast.Node
	Table         *TableReference
	StartPosition *storepb.Position
	EndPosition   *storepb.Position
	FullSQL       string
}

func TransformDMLToSelect(ctx context.Context, tCtx base.TransformContext, statement string, sourceDatabase string, targetDatabase string, tablePrefix string) ([]base.BackupStatement, error) {
	var dbMetadata *model.DatabaseMetadata
	if tCtx.GetDatabaseMetadataFunc != nil {
		_, metadata, err := tCtx.GetDatabaseMetadataFunc(ctx, tCtx.InstanceID, sourceDatabase)
		if err == nil {
			dbMetadata = metadata
		}
	}

	statementInfoList, err := prepareTransformation(sourceDatabase, statement, dbMetadata)
	if err != nil {
		return nil, errors.Wrap(err, "failed to prepare transformation")
	}

	return generateSQL(ctx, tCtx, statementInfoList, targetDatabase, tablePrefix)
}

func prepareTransformation(databaseName, statement string, dbMetadata *model.DatabaseMetadata) ([]StatementInfo, error) {
	list, err := SplitSQL(statement)
	if err != nil {
		return nil, errors.Wrap(err, "failed to split sql")
	}

	var result []StatementInfo
	for i, item := range list {
		if len(item.Text) == 0 || item.Empty {
			continue
		}

		parsed, err := ParseMariaDB(item.Text)
		if err != nil {
			return nil, errors.Wrap(err, "failed to parse sql")
		}
		if parsed == nil || len(parsed.Items) == 0 {
			continue
		}

		for _, node := range parsed.Items {
			tables, err := ExtractTables(databaseName, node, item.Text, i, dbMetadata)
			if err != nil {
				return nil, errors.Wrap(err, "failed to extract tables")
			}
			for _, table := range tables {
				result = append(result, StatementInfo{
					Offset:        table.Offset,
					Statement:     table.Statement,
					Node:          table.Node,
					Table:         table.Table,
					StartPosition: item.Start,
					EndPosition:   item.End,
					FullSQL:       item.Text,
				})
			}
		}
	}

	return result, nil
}

// ExtractTables extracts table references from a DML statement.
func ExtractTables(databaseName string, node ast.Node, fullSQL string, offset int, dbMetadata *model.DatabaseMetadata) ([]StatementInfo, error) {
	switch n := node.(type) {
	case *ast.DeleteStmt:
		return extractTablesFromDelete(databaseName, n, fullSQL, offset, dbMetadata)
	case *ast.UpdateStmt:
		return extractTablesFromUpdate(databaseName, n, fullSQL, offset, dbMetadata)
	default:
		return nil, nil
	}
}

func extractTablesFromDelete(databaseName string, n *ast.DeleteStmt, fullSQL string, offset int, _ *model.DatabaseMetadata) ([]StatementInfo, error) {
	cteNames := collectCTENames(fullSQL, n.Loc)
	stmtText := extractStatementText(fullSQL, n.Loc)

	// Collect all single table refs from the Tables list.
	singleTables := collectSingleTables(databaseName, n.Tables)

	if len(n.Using) == 0 {
		// Single-table DELETE: DELETE FROM t WHERE ...
		if len(singleTables) == 0 {
			return nil, nil
		}
		for _, ref := range singleTables {
			ref.StatementType = StatementTypeDelete
		}
		first := singleTables[firstKey(singleTables)]
		return []StatementInfo{{
			Offset:    offset,
			Statement: stmtText,
			Node:      n,
			Table:     first,
			FullSQL:   fullSQL,
		}}, nil
	}

	// Multi-table DELETE: DELETE t1, t2 FROM t1 JOIN t2 ... WHERE ...
	// Tables = targets to delete from, Using = referenced table list.
	refTables := collectSingleTables(databaseName, n.Using)

	var result []StatementInfo
	for _, target := range singleTables {
		name := target.Table
		if target.Alias != "" {
			name = target.Alias
		}
		if cteNames[name] {
			continue
		}
		ref, ok := refTables[name]
		if !ok {
			return nil, errors.Errorf("cannot extract reference table: no matched table %q in referenced table list", name)
		}
		ref.StatementType = StatementTypeDelete
		result = append(result, StatementInfo{
			Offset:    offset,
			Statement: stmtText,
			Node:      n,
			Table:     ref,
			FullSQL:   fullSQL,
		})
	}
	return result, nil
}

func extractTablesFromUpdate(databaseName string, n *ast.UpdateStmt, fullSQL string, offset int, dbMetadata *model.DatabaseMetadata) ([]StatementInfo, error) {
	cteNames := collectCTENames(fullSQL, n.Loc)
	stmtText := extractStatementText(fullSQL, n.Loc)
	singleTables := collectSingleTables(databaseName, n.Tables)

	// Determine which tables are updated via SET clause column prefixes.
	updatedTables := make(map[string]bool)
	var unqualifiedColumns []string
	for _, assign := range n.SetList {
		if assign.Column != nil {
			table := assign.Column.Table
			if !cteNames[table] {
				updatedTables[table] = true
				if table == "" {
					unqualifiedColumns = append(unqualifiedColumns, assign.Column.Column)
				}
			}
		}
	}

	// Resolve unqualified columns using metadata.
	if updatedTables[""] {
		delete(updatedTables, "")
		resolved := resolveUnqualifiedColumns(unqualifiedColumns, singleTables, dbMetadata)
		for t := range resolved {
			updatedTables[t] = true
		}
	}

	// Single-table UPDATE (only one table, no explicit qualification needed).
	if len(updatedTables) == 0 && len(singleTables) == 1 {
		for _, ref := range singleTables {
			ref.StatementType = StatementTypeUpdate
			return []StatementInfo{{
				Offset:    offset,
				Statement: stmtText,
				Node:      n,
				Table:     ref,
				FullSQL:   fullSQL,
			}}, nil
		}
	}

	var result []StatementInfo
	for table := range updatedTables {
		ref, ok := singleTables[table]
		if !ok {
			return nil, errors.Errorf("cannot extract reference table: no matched updated table %q in referenced table list", table)
		}
		ref.StatementType = StatementTypeUpdate
		result = append(result, StatementInfo{
			Offset:    offset,
			Statement: stmtText,
			Node:      n,
			Table:     ref,
			FullSQL:   fullSQL,
		})
	}
	return result, nil
}

// collectSingleTables walks TableExpr slices and collects TableRef entries keyed by alias or name.
func collectSingleTables(databaseName string, exprs []ast.TableExpr) map[string]*TableReference {
	result := make(map[string]*TableReference)
	for _, expr := range exprs {
		collectSingleTablesFromExpr(databaseName, expr, result)
	}
	return result
}

func collectSingleTablesFromExpr(databaseName string, expr ast.TableExpr, out map[string]*TableReference) {
	switch e := expr.(type) {
	case *ast.TableRef:
		db := e.Schema
		if db == "" {
			db = databaseName
		}
		ref := &TableReference{
			Database: db,
			Table:    e.Name,
			Alias:    e.Alias,
		}
		key := e.Name
		if e.Alias != "" {
			key = e.Alias
		}
		out[key] = ref
	case *ast.JoinClause:
		if e.Left != nil {
			collectSingleTablesFromExpr(databaseName, e.Left, out)
		}
		if e.Right != nil {
			collectSingleTablesFromExpr(databaseName, e.Right, out)
		}
	default:
	}
}

// collectCTENames returns the names of the CTEs the statement at stmtLoc opens with.
func collectCTENames(fullSQL string, stmtLoc ast.Loc) map[string]bool {
	result := make(map[string]bool)
	for _, cte := range parseCTEs(withClause(fullSQL, stmtLoc)) {
		result[cte.Name] = true
	}
	return result
}

// extractCTE returns the WITH clause the statement at stmtLoc opens with, or ""
// when it has none or the clause does not parse.
func extractCTE(fullSQL string, stmtLoc ast.Loc) string {
	clause := withClause(fullSQL, stmtLoc)
	if len(parseCTEs(clause)) == 0 {
		return ""
	}
	return clause
}

// withClause returns the WITH clause that opens the UPDATE or DELETE at loc, or "".
func withClause(sql string, loc ast.Loc) string {
	start := dmlKeywordStart(sql, loc)
	if start <= loc.Start {
		return ""
	}
	return strings.TrimSpace(sql[loc.Start:start])
}

// dmlKeywordStart returns the offset of the UPDATE or DELETE keyword of the
// statement at loc. omni starts the node at its WITH clause, if any, and keeps
// no CTE on it, so the clause is the text between the two.
func dmlKeywordStart(sql string, loc ast.Loc) int {
	if loc.Start < 0 || loc.End > len(sql) || loc.Start >= loc.End {
		return loc.Start
	}
	tokens := mariadbparser.Tokenize(sql[loc.Start:loc.End])
	if len(tokens) == 0 || mariadbparser.TokenName(tokens[0].Type) != "WITH" {
		return loc.Start
	}
	depth := 0
	for _, token := range tokens[1:] {
		switch token.Type {
		case '(':
			depth++
		case ')':
			depth--
		default:
			if depth != 0 {
				continue
			}
			if name := mariadbparser.TokenName(token.Type); name == "UPDATE" || name == "DELETE" {
				return loc.Start + token.Loc
			}
		}
	}
	return loc.Start
}

// parseCTEs returns the CTEs of a WITH clause, or nil when it does not parse.
func parseCTEs(clause string) []*ast.CommonTableExpr {
	if clause == "" {
		return nil
	}
	// Wrap the clause with a dummy SELECT to make it parseable.
	parsed, err := ParseMariaDB(clause + " SELECT 1")
	if err != nil || parsed == nil {
		return nil
	}
	for _, item := range parsed.Items {
		if sel, ok := item.(*ast.SelectStmt); ok && len(sel.CTEs) > 0 {
			return sel.CTEs
		}
	}
	return nil
}

func extractStatementText(fullSQL string, loc ast.Loc) string {
	end := loc.End
	if end <= 0 || end > len(fullSQL) {
		end = len(fullSQL)
	}
	start := 0
	if loc.Start > 0 {
		start = loc.Start
	}
	return strings.TrimSpace(fullSQL[start:end])
}

// writeSuffixSelectClause writes the FROM ... WHERE ... suffix of a DML statement.
func writeSuffixSelectClause(buf *strings.Builder, node ast.Node, fullSQL string) error {
	switch n := node.(type) {
	case *ast.UpdateStmt:
		return writeUpdateSuffix(buf, n, fullSQL)
	case *ast.DeleteStmt:
		return writeDeleteSuffix(buf, n, fullSQL)
	}
	return nil
}

func writeUpdateSuffix(buf *strings.Builder, n *ast.UpdateStmt, sql string) error {
	// For UPDATE: write "table_refs WHERE ... ORDER BY ... LIMIT ..."
	// Use the last table's Loc.End as the boundary — everything from first table
	// to last table's end is the table reference list.
	if len(n.Tables) > 0 {
		start := nodeLocStart(n.Tables[0])
		end := nodeLocEnd(n.Tables[len(n.Tables)-1])
		if start >= 0 && end > start && end <= len(sql) {
			if _, err := buf.WriteString(strings.TrimSpace(sql[start:end])); err != nil {
				return err
			}
		}
	}

	// Everything after the last SET assignment is WHERE + ORDER BY + LIMIT.
	if len(n.SetList) > 0 {
		lastAssign := n.SetList[len(n.SetList)-1]
		afterSet := lastAssign.Loc.End
		stmtEnd := n.Loc.End
		if stmtEnd <= 0 || stmtEnd > len(sql) {
			stmtEnd = len(sql)
		}
		suffix := strings.TrimSpace(sql[afterSet:stmtEnd])
		if suffix != "" {
			if _, err := fmt.Fprintf(buf, " %s", suffix); err != nil {
				return err
			}
		}
	}

	return nil
}

func writeDeleteSuffix(buf *strings.Builder, n *ast.DeleteStmt, sql string) error {
	// For single-table DELETE: everything from the table ref to statement end.
	// For multi-table DELETE: everything from the USING/reference table list to end.
	end, err := deleteSuffixEnd(n, sql)
	if err != nil {
		return err
	}
	var start int
	switch {
	case len(n.Using) > 0:
		start = nodeLocStart(n.Using[0])
	case len(n.Tables) > 0:
		start = nodeLocStart(n.Tables[0])
	default:
		return nil
	}
	if start < 0 || end <= start {
		return nil
	}
	_, err = buf.WriteString(strings.TrimSpace(sql[start:end]))
	return err
}

// deleteSuffixEnd returns where the text copied into the backup SELECT must
// stop: short of RETURNING, which is not a SELECT clause.
func deleteSuffixEnd(n *ast.DeleteStmt, sql string) (int, error) {
	return endBeforeReturning(n.Returning, sql, n.Loc.End)
}

func nodeLocStart(expr ast.TableExpr) int {
	switch e := expr.(type) {
	case *ast.TableRef:
		return e.Loc.Start
	case *ast.JoinClause:
		return e.Loc.Start
	}
	return -1
}

func nodeLocEnd(expr ast.TableExpr) int {
	switch e := expr.(type) {
	case *ast.TableRef:
		return e.Loc.End
	case *ast.JoinClause:
		return e.Loc.End
	}
	return -1
}

// resolveUnqualifiedColumns resolves unqualified column names to owning tables using metadata.
func resolveUnqualifiedColumns(columns []string, singleTables map[string]*TableReference, dbMetadata *model.DatabaseMetadata) map[string]bool {
	result := make(map[string]bool)

	if dbMetadata == nil {
		for t := range singleTables {
			result[t] = true
		}
		return result
	}
	schema := dbMetadata.GetSchemaMetadata("")
	if schema == nil {
		for t := range singleTables {
			result[t] = true
		}
		return result
	}

	for _, col := range columns {
		resolved := false
		for aliasOrName, ref := range singleTables {
			tableMetadata := schema.GetTable(ref.Table)
			if tableMetadata == nil {
				continue
			}
			if tableMetadata.GetColumn(col) != nil {
				result[aliasOrName] = true
				resolved = true
			}
		}
		if !resolved {
			for t := range singleTables {
				result[t] = true
			}
			return result
		}
	}
	return result
}

func firstKey(m map[string]*TableReference) string {
	for k := range m {
		return k
	}
	return ""
}

func generateSQL(ctx context.Context, tCtx base.TransformContext, statementInfoList []StatementInfo, databaseName string, tablePrefix string) ([]base.BackupStatement, error) {
	groupByTable := make(map[string][]StatementInfo)
	for _, item := range statementInfoList {
		key := fmt.Sprintf("%s.%s", item.Table.Database, item.Table.Table)
		groupByTable[key] = append(groupByTable[key], item)
	}

	for key, list := range groupByTable {
		stmtType := StatementTypeUnknown
		for _, item := range list {
			if stmtType == StatementTypeUnknown {
				stmtType = item.Table.StatementType
			}
			if stmtType != item.Table.StatementType {
				return nil, errors.Errorf("prior backup cannot handle mixed DML statements on the same table %s", key)
			}
		}
	}

	var result []base.BackupStatement
	for key, list := range groupByTable {
		backupStatement, err := generateSQLForTable(ctx, tCtx, list, databaseName, tablePrefix)
		if err != nil {
			return nil, errors.Wrapf(err, "failed to generate SQL for table %s", key)
		}
		result = append(result, *backupStatement)
	}

	slices.SortFunc(result, func(i, j base.BackupStatement) int {
		if i.StartPosition.Line != j.StartPosition.Line {
			if i.StartPosition.Line < j.StartPosition.Line {
				return -1
			}
			return 1
		}
		if i.StartPosition.Column != j.StartPosition.Column {
			if i.StartPosition.Column < j.StartPosition.Column {
				return -1
			}
			return 1
		}
		if i.SourceTableName < j.SourceTableName {
			return -1
		}
		if i.SourceTableName > j.SourceTableName {
			return 1
		}
		return 0
	})

	return result, nil
}

func generateSQLForTable(ctx context.Context, tCtx base.TransformContext, statementInfoList []StatementInfo, databaseName string, tablePrefix string) (*base.BackupStatement, error) {
	table := statementInfoList[0].Table

	generatedColumns, normalColumns, err := classifyColumns(ctx, tCtx.GetDatabaseMetadataFunc, tCtx.ListDatabaseNamesFunc, tCtx.IsCaseSensitive, tCtx.InstanceID, table)
	if err != nil {
		return nil, errors.Wrap(err, "failed to classify columns")
	}

	targetTable := fmt.Sprintf("%s_%s_%s", tablePrefix, table.Table, table.Database)
	targetTable, _ = common.TruncateString(targetTable, maxTableNameLength)
	var buf strings.Builder
	if _, err := fmt.Fprintf(&buf, "CREATE TABLE `%s`.`%s` LIKE `%s`.`%s`;\n", databaseName, targetTable, table.Database, table.Table); err != nil {
		return nil, errors.Wrap(err, "failed to write create table statement")
	}

	if _, err := fmt.Fprintf(&buf, "INSERT INTO `%s`.`%s`", databaseName, targetTable); err != nil {
		return nil, errors.Wrap(err, "failed to write insert into statement")
	}
	if len(generatedColumns) > 0 {
		if _, err := buf.WriteString(" ("); err != nil {
			return nil, errors.Wrap(err, "failed to write insert into statement")
		}
		for i, column := range normalColumns {
			if i > 0 {
				if err := buf.WriteByte(','); err != nil {
					return nil, errors.Wrap(err, "failed to write comma")
				}
			}
			if _, err := fmt.Fprintf(&buf, "`%s`", column); err != nil {
				return nil, errors.Wrap(err, "failed to write column")
			}
		}
		if _, err := buf.WriteString(")"); err != nil {
			return nil, errors.Wrap(err, "failed to write insert into statement")
		}
	}
	for i, item := range statementInfoList {
		if i != 0 {
			if _, err := buf.WriteString("\n  UNION DISTINCT\n"); err != nil {
				return nil, errors.Wrap(err, "failed to write union all statement")
			}
		}
		tableNameOrAlias := item.Table.Table
		if len(item.Table.Alias) > 0 {
			tableNameOrAlias = item.Table.Alias
		}
		if _, err := buf.WriteString("  "); err != nil {
			return nil, errors.Wrap(err, "failed to write space")
		}

		stmtLoc := nodeStmtLoc(item.Node)
		cteString := extractCTE(item.FullSQL, stmtLoc)
		if len(cteString) > 0 {
			if _, err := fmt.Fprintf(&buf, "%s ", cteString); err != nil {
				return nil, errors.Wrap(err, "failed to write cte")
			}
		}

		if len(generatedColumns) == 0 {
			if _, err := fmt.Fprintf(&buf, "SELECT `%s`.* FROM ", tableNameOrAlias); err != nil {
				return nil, errors.Wrap(err, "failed to write select statement")
			}
		} else {
			if _, err := buf.WriteString("SELECT "); err != nil {
				return nil, errors.Wrap(err, "failed to write select statement")
			}
			for j, column := range normalColumns {
				if j > 0 {
					if err := buf.WriteByte(','); err != nil {
						return nil, errors.Wrap(err, "failed to write comma")
					}
				}
				if _, err := fmt.Fprintf(&buf, "`%s`.`%s`", tableNameOrAlias, column); err != nil {
					return nil, errors.Wrap(err, "failed to write column")
				}
			}
			if _, err := buf.WriteString(" FROM "); err != nil {
				return nil, errors.Wrap(err, "failed to write from")
			}
		}
		if err := writeSuffixSelectClause(&buf, item.Node, item.FullSQL); err != nil {
			return nil, errors.Wrap(err, "failed to extract suffix select statement")
		}
	}

	if err := buf.WriteByte(';'); err != nil {
		return nil, errors.Wrap(err, "failed to write semicolon")
	}

	return &base.BackupStatement{
		Statement:       buf.String(),
		SourceTableName: table.Table,
		TargetTableName: targetTable,
		StartPosition:   statementInfoList[0].StartPosition,
		EndPosition:     statementInfoList[len(statementInfoList)-1].EndPosition,
	}, nil
}

func nodeStmtLoc(node ast.Node) ast.Loc {
	switch n := node.(type) {
	case *ast.UpdateStmt:
		return n.Loc
	case *ast.DeleteStmt:
		return n.Loc
	}
	return ast.Loc{}
}

func classifyColumns(ctx context.Context, getDatabaseMetadataFunc base.GetDatabaseMetadataFunc, listDatabaseNamesFunc base.ListDatabaseNamesFunc, isCaseSensitive bool, instanceID string, table *TableReference) ([]string, []string, error) {
	if getDatabaseMetadataFunc == nil {
		return nil, nil, errors.New("GetDatabaseMetadataFunc is not set")
	}

	var dbMetadata *model.DatabaseMetadata
	allDatabaseNames, err := listDatabaseNamesFunc(ctx, instanceID)
	if err != nil {
		return nil, nil, errors.Wrap(err, "failed to list databases names")
	}
	if !isCaseSensitive {
		for _, db := range allDatabaseNames {
			if strings.EqualFold(db, table.Database) {
				_, dbMetadata, err = getDatabaseMetadataFunc(ctx, instanceID, db)
				if err != nil {
					return nil, nil, errors.Wrapf(err, "failed to get database metadata for database %q", db)
				}
				break
			}
		}
	} else {
		for _, db := range allDatabaseNames {
			if db == table.Database {
				_, dbMetadata, err = getDatabaseMetadataFunc(ctx, instanceID, db)
				if err != nil {
					return nil, nil, errors.Wrapf(err, "failed to get database metadata for database %q", db)
				}
				break
			}
		}
	}
	if dbMetadata == nil {
		slog.Debug("failed to get database metadata", slog.String("instanceID", instanceID), slog.String("database", table.Database))
		return nil, nil, errors.Errorf("failed to get database metadata for InstanceID %q, Database %q", instanceID, table.Database)
	}

	schema := dbMetadata.GetSchemaMetadata("")
	if schema == nil {
		return nil, nil, errors.New("failed to get schema metadata")
	}

	var tableSchema *model.TableMetadata
	if !isCaseSensitive {
		for _, tableName := range schema.ListTableNames() {
			if strings.EqualFold(tableName, table.Table) {
				tableSchema = schema.GetTable(tableName)
				break
			}
		}
	} else {
		tableSchema = schema.GetTable(table.Table)
	}
	if tableSchema == nil {
		return nil, nil, errors.Errorf("table %s not found in schema", table.Table)
	}

	var generatedColumns, normalColumns []string
	for _, column := range tableSchema.GetProto().GetColumns() {
		if column.GetGeneration() != nil {
			generatedColumns = append(generatedColumns, column.GetName())
		} else {
			normalColumns = append(normalColumns, column.GetName())
		}
	}

	return generatedColumns, normalColumns, nil
}