Skip to content

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

24.2 KB
Raw Blame
package mysql

import (
	"strings"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	_ "github.com/go-sql-driver/mysql"
	"github.com/stretchr/testify/require"

	"os"

	"gopkg.in/yaml.v3"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/common/yamltest"
	"github.com/bytebase/bytebase/backend/plugin/schema"
)

func TestGetTableDefinitionWithCheckConstraint(t *testing.T) {
	table := &metadatapb.TableMetadata{
		Name: "t1",
		Columns: []*metadatapb.ColumnMetadata{
			{Name: "type", Type: "varchar(10)", Nullable: true, Default: "NULL"},
			{Name: "amount", Type: "decimal(10,2)", Nullable: true, Default: "NULL"},
		},
		CheckConstraints: []*metadatapb.CheckConstraintMetadata{
			{
				Name: "c1",
				// The expression as synced from MySQL after unescaping the
				// information_schema escaped form ((`type` = _utf8mb4\'A\')...).
				Expression: "(((`type` = _utf8mb4'A') and (`amount` is not null)))",
			},
		},
		Engine:    "InnoDB",
		Charset:   "utf8mb4",
		Collation: "utf8mb4_general_ci",
	}

	got, err := GetTableDefinition("", table, nil)
	require.NoError(t, err)

	want := "--\n" +
		"-- Table structure for `t1`\n" +
		"--\n" +
		"CREATE TABLE `t1` (\n" +
		"  `type` varchar(10) DEFAULT NULL,\n" +
		"  `amount` decimal(10,2) DEFAULT NULL,\n" +
		"  CONSTRAINT `c1` CHECK (((`type` = _utf8mb4'A') and (`amount` is not null)))\n" +
		") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;\n\n"
	require.Equal(t, want, got)
	require.NotContains(t, got, `\'`)
}

func TestGetTableDefinitionWithSystemVersioning(t *testing.T) {
	table := &metadatapb.TableMetadata{
		Name: "explicit_period",
		Columns: []*metadatapb.ColumnMetadata{
			{Name: "id", Type: "int", Nullable: true, Default: "NULL"},
			{Name: "valid_from", Type: "timestamp(6)", Generation: &metadatapb.GenerationMetadata{Type: metadatapb.GenerationMetadata_TYPE_STORED, Expression: "ROW START"}},
			{Name: "valid_to", Type: "timestamp(6)", Generation: &metadatapb.GenerationMetadata{Type: metadatapb.GenerationMetadata_TYPE_STORED, Expression: "ROW END"}},
		},
		Engine:        "InnoDB",
		Charset:       "utf8mb4",
		Collation:     "utf8mb4_general_ci",
		Comment:       "versioned",
		CreateOptions: "WITH SYSTEM VERSIONING",
	}

	got, err := GetTableDefinition("", table, nil)
	require.NoError(t, err)

	want := "--\n" +
		"-- Table structure for `explicit_period`\n" +
		"--\n" +
		"CREATE TABLE `explicit_period` (\n" +
		"  `id` int DEFAULT NULL,\n" +
		"  `valid_from` timestamp(6) GENERATED ALWAYS AS ROW START NOT NULL,\n" +
		"  `valid_to` timestamp(6) GENERATED ALWAYS AS ROW END NOT NULL,\n" +
		"  PERIOD FOR SYSTEM_TIME (`valid_from`, `valid_to`)\n" +
		") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='versioned' WITH SYSTEM VERSIONING;\n\n"
	require.Equal(t, want, got)

	// The schema-text diff path loads a dump with the omni MySQL loader. Loading the
	// table unversioned would let the diff recreate it without history, so it must fail.
	_, err = GetDatabaseMetadata(got)
	require.Error(t, err)
}

func TestGetFunctionDefinitionWithMultilineParameters(t *testing.T) {
	function := &metadatapb.FunctionMetadata{
		Name: "f1",
		// The definition as synced from SHOW CREATE FUNCTION: the parameter
		// list keeps its original multi-line formatting, and the CHARSET and
		// COLLATE attributes of the RETURNS clause have been stripped.
		Definition: "CREATE FUNCTION `f1`(\n" +
			"    p_a BIGINT,\n" +
			"    p_b VARCHAR(36)\n" +
			") RETURNS char(36)\n" +
			"    NO SQL\n" +
			"    DETERMINISTIC\n" +
			"BEGIN\n" +
			"    RETURN p_b;\n" +
			"END",
		CharacterSetClient:  "utf8mb4",
		CollationConnection: "utf8mb4_unicode_ci",
		SqlMode:             "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES",
	}

	got, err := GetFunctionDefinition("", function)
	require.NoError(t, err)

	want := "--\n" +
		"-- Function structure for `f1`\n" +
		"--\n" +
		"SET character_set_client = utf8mb4;\n" +
		"SET character_set_results = utf8mb4;\n" +
		"SET collation_connection = utf8mb4_unicode_ci;\n" +
		"SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';\n" +
		"CREATE FUNCTION `f1`(\n" +
		"    p_a BIGINT,\n" +
		"    p_b VARCHAR(36)\n" +
		") RETURNS char(36)\n" +
		"    NO SQL\n" +
		"    DETERMINISTIC\n" +
		"BEGIN\n" +
		"    RETURN p_b;\n" +
		"END;;\n" +
		"DELIMITER ;\n\n"
	require.Equal(t, want, got)
}

func TestGetDatabaseDefinitionSkipsEventWithoutDefinition(t *testing.T) {
	metadata := &metadatapb.DatabaseSchemaMetadata{
		Name:    "db",
		Schemas: []*metadatapb.SchemaMetadata{{Events: []*metadatapb.EventMetadata{{Name: "e"}}}},
	}
	for _, sdl := range []bool{false, true} {
		definition, err := GetDatabaseDefinition(schema.GetDefinitionContext{SDLFormat: sdl}, metadata)
		require.NoError(t, err)
		require.Empty(t, definition, "SDLFormat=%t", sdl)
	}
}

func TestRenderColumnDefaultBitLiteral(t *testing.T) {
	testCases := []struct {
		name       string
		columnType string
		// def is the value stored in ColumnMetadata.Default. For a BIT column the MySQL
		// sync stores the bit literal QUOTE()-escaped, e.g. b'0' becomes 'b\'0\''.
		def  string
		want string
	}{
		{name: "bit1 quoted-escaped", columnType: "bit(1)", def: `'b\'0\''`, want: "b'0'"},
		{name: "bit8 quoted-escaped", columnType: "bit(8)", def: `'b\'101\''`, want: "b'101'"},
		{name: "bit no width", columnType: "bit", def: `'b\'1\''`, want: "b'1'"},
		{name: "bit already bare literal", columnType: "bit(4)", def: "b'1010'", want: "b'1010'"},
		{name: "bit hex 0x form", columnType: "bit(8)", def: "0xFF", want: "0xFF"},
		{name: "bit hex x'' quoted-escaped", columnType: "bit(8)", def: `'x\'1f\''`, want: "x'1f'"},
		// Non-bit columns and non-literal defaults are emitted verbatim.
		{name: "varchar string default untouched", columnType: "varchar(10)", def: "'hello'", want: "'hello'"},
		{name: "varchar string that looks bit-ish but not bit column", columnType: "varchar(10)", def: `'b\'0\''`, want: `'b\'0\''`},
		{name: "int numeric default untouched", columnType: "int", def: "42", want: "42"},
		// A BIT column whose default is not a bit/hex literal (defensive) is left verbatim.
		{name: "bit with non-literal default", columnType: "bit(8)", def: "'abc'", want: "'abc'"},
	}
	for _, tc := range testCases {
		t.Run(tc.name, func(t *testing.T) {
			got := renderColumnDefault(&metadatapb.ColumnMetadata{Type: tc.columnType, Default: tc.def})
			require.Equal(t, tc.want, got)
		})
	}
}

func TestPrintColumnClauseBitDefaultUnquoted(t *testing.T) {
	table := &metadatapb.TableMetadata{Name: "t", Charset: "utf8mb4"}
	var buf strings.Builder
	col := &metadatapb.ColumnMetadata{Name: "b1", Type: "bit(1)", Nullable: false, Default: `'b\'0\''`}
	require.NoError(t, printColumnClause(&buf, col, table))
	require.Equal(t, "  `b1` bit(1) NOT NULL DEFAULT b'0'", buf.String())

	// A BIT column with no default (b64 BIT(64)) must stay fine: NULL default emitted as
	// DEFAULT NULL, never as a bit literal.
	var buf2 strings.Builder
	col2 := &metadatapb.ColumnMetadata{Name: "b64", Type: "bit(64)", Nullable: true, Default: "NULL"}
	require.NoError(t, printColumnClause(&buf2, col2, table))
	require.Equal(t, "  `b64` bit(64) DEFAULT NULL", buf2.String())
}

func TestNormalizeFunctionalIndexExpr(t *testing.T) {
	testCases := []struct {
		name string
		expr string
		want string
	}{
		{
			name: "multi-valued json index with utf8mb4 introducer and escaped quotes",
			// The form synced from information_schema.STATISTICS.EXPRESSION.
			expr: "(cast(json_extract(`tags`,_utf8mb4\\'$.ids\\') as unsigned array))",
			want: "(cast(json_extract(`tags`,'$.ids') as unsigned array))",
		},
		{
			name: "latin1 introducer",
			expr: "(json_extract(`c`,_latin1\\'$.a\\'))",
			want: "(json_extract(`c`,'$.a'))",
		},
		{
			name: "no introducer no escaping is unchanged",
			expr: "(json_extract(`tags`,'$.ids'))",
			want: "(json_extract(`tags`,'$.ids'))",
		},
		{
			name: "introducer-like underscore inside string literal is preserved",
			// _utf8mb4 appears INSIDE the literal here; only a real introducer (before the
			// opening quote) must be stripped.
			expr: "(concat(`c`,_utf8mb4\\'_utf8mb4 is text\\'))",
			want: "(concat(`c`,'_utf8mb4 is text'))",
		},
		{
			name: "leading-underscore identifier not before a quote is kept",
			expr: "(`_weird`)",
			want: "(`_weird`)",
		},
		{
			name: "backticked identifier with underscore is untouched",
			expr: "(json_extract(`my_col`,_utf8mb4\\'$.k\\'))",
			want: "(json_extract(`my_col`,'$.k'))",
		},
	}
	for _, tc := range testCases {
		t.Run(tc.name, func(t *testing.T) {
			require.Equal(t, tc.want, normalizeFunctionalIndexExpr(tc.expr))
		})
	}
}

func TestPrintIndexClauseFunctionalExprNormalized(t *testing.T) {
	var buf strings.Builder
	index := &metadatapb.IndexMetadata{
		Name:        "idx_tags",
		Visible:     true,
		Expressions: []string{"(cast(json_extract(`tags`,_utf8mb4\\'$.ids\\') as unsigned array))"},
	}
	require.NoError(t, printIndexClause(&buf, index))
	require.Equal(t, ",\n  KEY `idx_tags` ((cast(json_extract(`tags`,'$.ids') as unsigned array)))", buf.String())
	require.NotContains(t, buf.String(), "_utf8mb4")
	require.NotContains(t, buf.String(), `\'`)
}

// TestStripViewBodyDatabaseQualifier pins the token-scanner rewrite (X6). The failure
// shapes were live-verified: (a) `db`.`db`.`col` (a table named like the database, the
// 5.7 three-part form) was double-stripped down to `col`; (b) a backtick alias
// containing a quote (`it's`) flipped the old scanner's literal state so NO subsequent
// qualifier was stripped; (c) a genuine string literal containing the qualifier bytes
// was corrupted.
func TestStripViewBodyDatabaseQualifier(t *testing.T) {
	cases := []struct {
		name   string
		body   string
		dbName string
		want   string
	}{
		{
			name:   "table_named_like_database_keeps_table_qualifier",
			body:   "select `db`.`db`.`col` AS `col` from `db`.`db`",
			dbName: "db",
			want:   "select `db`.`col` AS `col` from `db`",
		},
		{
			name: "alias_with_quote_does_not_flip_literal_state",
			// 5.7 derived-table shape: all three qualifiers must strip even though the
			// alias `it's` contains a single quote.
			body:   "select `x`.`a` AS `it's` from (select `mydb`.`t`.`a` AS `a` from `mydb`.`t` where (`mydb`.`t`.`a` > 0)) `x`",
			dbName: "mydb",
			want:   "select `x`.`a` AS `it's` from (select `t`.`a` AS `a` from `t` where (`t`.`a` > 0)) `x`",
		},
		{
			name:   "control_alias_without_quote",
			body:   "select `x`.`a` AS `plain` from (select `mydb`.`t`.`a` AS `a` from `mydb`.`t`) `x`",
			dbName: "mydb",
			want:   "select `x`.`a` AS `plain` from (select `t`.`a` AS `a` from `t`) `x`",
		},
		{
			name:   "cross_database_reference_preserved",
			body:   "select `otherdb`.`t`.`c` from `otherdb`.`t` join `mydb`.`u` on (`otherdb`.`t`.`id` = `mydb`.`u`.`id`)",
			dbName: "mydb",
			want:   "select `otherdb`.`t`.`c` from `otherdb`.`t` join `u` on (`otherdb`.`t`.`id` = `u`.`id`)",
		},
		{
			name: "db_named_segment_inside_foreign_reference_preserved",
			// `mydb` here is the TABLE part of a cross-database reference — a
			// continuation after ".", never a qualifier.
			body:   "select `otherdb`.`mydb`.`c` from `otherdb`.`mydb`",
			dbName: "mydb",
			want:   "select `otherdb`.`mydb`.`c` from `otherdb`.`mydb`",
		},
		{
			name: "quote_flip_then_literal_corruption",
			// The live-verified composed corruption: with the old scanner, the quote
			// inside alias `it's` flipped the literal state, so the REAL literal's
			// interior was treated as top-level (its `mydb`. stripped — data change)
			// while the genuine trailing qualifier survived.
			body:   "select `it's` AS a, '`mydb`.`t` and  spaces' AS lit from `mydb`.`t`",
			dbName: "mydb",
			want:   "select `it's` AS a, '`mydb`.`t` and  spaces' AS lit from `t`",
		},
		{
			name:   "string_literal_containing_qualifier_untouched",
			body:   "select '`mydb`.`t`' AS lit, `mydb`.`t`.`c` from `mydb`.`t`",
			dbName: "mydb",
			want:   "select '`mydb`.`t`' AS lit, `t`.`c` from `t`",
		},
		{
			name:   "double_quoted_literal_untouched",
			body:   `select "` + "`mydb`.`t`" + `" AS lit from ` + "`mydb`.`t`",
			dbName: "mydb",
			want:   `select "` + "`mydb`.`t`" + `" AS lit from ` + "`t`",
		},
		{
			name:   "qualifier_not_followed_by_identifier_preserved",
			body:   "select `mydb`.* from `mydb`.`t`",
			dbName: "mydb",
			want:   "select `mydb`.* from `t`",
		},
		{
			name:   "empty_db_name_no_op",
			body:   "select `t`.`c` from `t`",
			dbName: "",
			want:   "select `t`.`c` from `t`",
		},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			require.Equal(t, tc.want, stripViewBodyDatabaseQualifier(tc.body, tc.dbName))
		})
	}
}

// TestStripLeadingDefiner pins the definer-clause parser (X7): quoted accounts may
// legally contain spaces (`my user`@`%`), '@', and doubled/escaped quotes — the old
// cut-at-first-space produced corrupt "CREATE user`@`%` …" output.
func TestStripLeadingDefiner(t *testing.T) {
	cases := []struct {
		name string
		in   string
		want string
	}{
		{
			name: "plain_account",
			in:   "CREATE DEFINER=`root`@`localhost` EVENT `e` ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
			want: "CREATE EVENT `e` ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "quoted_user_with_space",
			in:   "CREATE DEFINER=`my user`@`%` EVENT `e` ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
			want: "CREATE EVENT `e` ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "quoted_host_with_space_function",
			in:   "CREATE DEFINER=`a``b`@`local host` FUNCTION `f`() RETURNS int DETERMINISTIC RETURN 1",
			want: "CREATE FUNCTION `f`() RETURNS int DETERMINISTIC RETURN 1",
		},
		{
			name: "single_quoted_account_procedure",
			in:   "CREATE DEFINER='my user'@'%' PROCEDURE `p`() BEGIN END",
			want: "CREATE PROCEDURE `p`() BEGIN END",
		},
		{
			name: "double_quoted_account",
			in:   `CREATE DEFINER="my user"@"%" EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t`,
			want: "CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "unquoted_account",
			in:   "CREATE DEFINER=root@localhost EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
			want: "CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "current_user",
			in:   "CREATE DEFINER=CURRENT_USER EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
			want: "CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "current_user_parens",
			in:   "CREATE DEFINER=CURRENT_USER() EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
			want: "CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "spaces_around_equals",
			in:   "CREATE DEFINER = `my user`@`%` EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
			want: "CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "no_definer_unchanged",
			in:   "CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
			want: "CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO DELETE FROM t",
		},
		{
			name: "unterminated_quote_left_unchanged",
			in:   "CREATE DEFINER=`broken EVENT e DO SELECT 1",
			want: "CREATE DEFINER=`broken EVENT e DO SELECT 1",
		},
		{
			name: "not_a_create_statement_unchanged",
			in:   "ALTER DEFINER=`root`@`%` EVENT e COMMENT 'x'",
			want: "ALTER DEFINER=`root`@`%` EVENT e COMMENT 'x'",
		},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			require.Equal(t, tc.want, stripLeadingDefiner(tc.in))
		})
	}
}

// TestPrintColumnClauseSRIDPresence pins the presence semantics of the SRID attribute
// (X5): explicit SRID 0 must be emitted (it is a valid spatial reference system,
// distinct from "no SRID"), and an unset SRID emits nothing.
func TestPrintColumnClauseSRIDPresence(t *testing.T) {
	srid := func(v uint32) *uint32 { return &v }
	table := &metadatapb.TableMetadata{Name: "t"}

	render := func(col *metadatapb.ColumnMetadata) string {
		var buf strings.Builder
		require.NoError(t, printColumnClause(&buf, col, table))
		return buf.String()
	}

	require.Equal(t, "  `pt` point NOT NULL /*!80003 SRID 0 */",
		render(&metadatapb.ColumnMetadata{Name: "pt", Type: "point", Nullable: false, Srid: srid(0)}))
	require.Equal(t, "  `pt` point NOT NULL /*!80003 SRID 4326 */",
		render(&metadatapb.ColumnMetadata{Name: "pt", Type: "point", Nullable: false, Srid: srid(4326)}))
	// Custom SRSs above int32 range must render unmangled.
	require.Equal(t, "  `pt` point NOT NULL /*!80003 SRID 3000000000 */",
		render(&metadatapb.ColumnMetadata{Name: "pt", Type: "point", Nullable: false, Srid: srid(3000000000)}))
	require.Equal(t, "  `pt` point NOT NULL",
		render(&metadatapb.ColumnMetadata{Name: "pt", Type: "point", Nullable: false}))
}

// TestPrintColumnClauseInvisibleCommentOrder pins the SDL dumper's INVISIBLE/COMMENT order
// (BYT-9830). MySQL's canonical SHOW CREATE emits INVISIBLE before COMMENT for both regular
// and generated columns (verified against 8.0.32), so the dumped form must match — otherwise
// the dump diverges from SHOW CREATE and from the migration generator's generated-column path.
func TestPrintColumnClauseInvisibleCommentOrder(t *testing.T) {
	srid := func(v uint32) *uint32 { return &v }
	table := &metadatapb.TableMetadata{Name: "t"}

	render := func(col *metadatapb.ColumnMetadata) string {
		var buf strings.Builder
		require.NoError(t, printColumnClause(&buf, col, table))
		return buf.String()
	}

	// Regular INVISIBLE + COMMENT column: INVISIBLE precedes COMMENT.
	require.Equal(t, "  `a` int DEFAULT NULL /*!80023 INVISIBLE */ COMMENT 'c'",
		render(&metadatapb.ColumnMetadata{Name: "a", Type: "int", Nullable: true, Default: "NULL", IsInvisible: true, Comment: "c"}))

	// Generated spatial INVISIBLE + COMMENT column: same canonical INVISIBLE-before-COMMENT order.
	require.Equal(t,
		"  `loc` point GENERATED ALWAYS AS (st_srid(point(`lng`,`lat`),4326)) STORED NOT NULL /*!80003 SRID 4326 */ /*!80023 INVISIBLE */ COMMENT 'geo'",
		render(&metadatapb.ColumnMetadata{Name: "loc", Type: "point", Nullable: false, Srid: srid(4326), IsInvisible: true, Comment: "geo", Generation: &metadatapb.GenerationMetadata{
			Type:       metadatapb.GenerationMetadata_TYPE_STORED,
			Expression: "st_srid(point(`lng`,`lat`),4326)",
		}}))
}

// TestWriteColumnDefinitionBodyGeneratedOrder pins the migration generator's attribute
// order for a generated column (BYT-9830). MySQL's grammar requires the
// `GENERATED ALWAYS AS (...) STORED|VIRTUAL` clause to precede NOT NULL and the SRID
// attribute; emitting SRID/NOT NULL first (the prior order) is rejected with ERROR 1064
// for a generated spatial column. The canonical order — verified against MySQL 8.0.32
// via SHOW CREATE — is:
//
//	type GENERATED ALWAYS AS (expr) STORED|VIRTUAL [NOT NULL] [SRID] [INVISIBLE] [COMMENT]
func TestWriteColumnDefinitionBodyGeneratedOrder(t *testing.T) {
	srid := func(v uint32) *uint32 { return &v }
	stored := &metadatapb.GenerationMetadata{
		Type:       metadatapb.GenerationMetadata_TYPE_STORED,
		Expression: "st_srid(point(`lng`,`lat`),4326)",
	}

	render := func(col *metadatapb.ColumnMetadata) string {
		var buf strings.Builder
		writeColumnDefinitionBody(&buf, col)
		return buf.String()
	}

	// Generated spatial column, NOT NULL + SRID — the failing case. The generation clause
	// must come first, then NOT NULL, then SRID.
	require.Equal(t,
		"point GENERATED ALWAYS AS (st_srid(point(`lng`,`lat`),4326)) STORED NOT NULL /*!80003 SRID 4326 */",
		render(&metadatapb.ColumnMetadata{Name: "loc", Type: "point", Nullable: false, Srid: srid(4326), Generation: stored}))

	// Nullable generated spatial column: no NOT NULL, SRID still after the generation clause.
	require.Equal(t,
		"point GENERATED ALWAYS AS (st_srid(point(`lng`,`lat`),4326)) STORED /*!80003 SRID 0 */",
		render(&metadatapb.ColumnMetadata{Name: "loc", Type: "point", Nullable: true, Srid: srid(0), Generation: stored}))

	// Generated spatial column that is also INVISIBLE and has a COMMENT: INVISIBLE precedes
	// COMMENT, matching SHOW CREATE's canonical order for both regular and generated columns.
	require.Equal(t,
		"point GENERATED ALWAYS AS (st_srid(point(`lng`,`lat`),4326)) STORED NOT NULL /*!80003 SRID 4326 */ /*!80023 INVISIBLE */ COMMENT 'geo'",
		render(&metadatapb.ColumnMetadata{Name: "loc", Type: "point", Nullable: false, Srid: srid(4326), IsInvisible: true, Comment: "geo", Generation: stored}))

	// Regular (non-generated) INVISIBLE column with a COMMENT: INVISIBLE precedes COMMENT,
	// the same canonical order as the generated column above (verified against 8.0.32).
	require.Equal(t,
		"int DEFAULT NULL /*!80023 INVISIBLE */ COMMENT 'c'",
		render(&metadatapb.ColumnMetadata{Name: "a", Type: "int", Nullable: true, Default: "NULL", IsInvisible: true, Comment: "c"}))

	// Plain (non-spatial) VIRTUAL generated column still emits the generation clause and
	// nothing spurious.
	require.Equal(t,
		"int GENERATED ALWAYS AS (`a` + 1) VIRTUAL",
		render(&metadatapb.ColumnMetadata{Name: "b", Type: "int", Nullable: true, Generation: &metadatapb.GenerationMetadata{
			Type:       metadatapb.GenerationMetadata_TYPE_VIRTUAL,
			Expression: "`a` + 1",
		}}))

	// Regression: a non-generated NOT NULL spatial column keeps the regular order
	// (NOT NULL then SRID, no generation clause).
	require.Equal(t,
		"point NOT NULL /*!80003 SRID 4326 */",
		render(&metadatapb.ColumnMetadata{Name: "loc", Type: "point", Nullable: false, Srid: srid(4326)}))
}

type getDatabaseDefinitionCase struct {
	Description string `yaml:"description"`
	Metadata    string `yaml:"metadata"`
	Expected    string `yaml:"expected"`
}

// TestGetDatabaseDefinition pins the DDL generated for each metadata input,
// and re-parses that DDL to prove it is valid MySQL syntax.
//
// The input is metadata rather than a schema text on purpose: deriving it by
// parsing would cap this test's reach at whatever GetDatabaseMetadata happens to
// extract, so any generator branch the parser cannot feed would go untested.
// unbalancedDelimiterBlocks names the cases whose definition ends a routine with
// ";;" without a DELIMITER directive having made ";;" the terminator. writeEvent,
// writeTrigger and writeProcedure all emit the opening "DELIMITER ;;"; only
// writeFunction leaves it out, so a client restoring such a dump ends the
// function at the first semicolon in its body. The golden records that output;
// giving writeFunction the opener empties this map.
var unbalancedDelimiterBlocks = map[string]bool{
	"Stored procedures and functions":                                               true,
	"Check constraints with string literals and function with multiline parameters": true,
}

// requireDelimiterDiscipline checks what a mysql client relies on to restore a
// dump: a line ending in ";;" terminates a statement only while a DELIMITER
// directive has ";;" active.
func requireDelimiterDiscipline(t *testing.T, definition string) {
	t.Helper()

	active := ";"
	for i, line := range strings.Split(definition, "\n") {
		trimmed := strings.TrimSpace(line)
		if directive, ok := strings.CutPrefix(trimmed, "DELIMITER "); ok {
			active = strings.TrimSpace(directive)
			continue
		}
		if strings.HasSuffix(trimmed, ";;") && active != ";;" {
			t.Fatalf("line %d ends a statement with %q while %q is the active delimiter, so a client cuts it short: %s",
				i+1, ";;", active, trimmed)
		}
	}
}

func TestGetDatabaseDefinition(t *testing.T) {
	const (
		record   = false
		filepath = "testdata/get_database_definition.yaml"
	)

	var tests []getDatabaseDefinitionCase
	content, err := os.ReadFile(filepath)
	require.NoError(t, err)
	require.NoError(t, yaml.Unmarshal(content, &tests))

	for i, tc := range tests {
		t.Run(tc.Description, func(t *testing.T) {
			var metadata metadatapb.DatabaseSchemaMetadata
			require.NoError(t, common.ProtojsonUnmarshaler.Unmarshal([]byte(tc.Metadata), &metadata))

			definition, err := GetDatabaseDefinition(schema.GetDefinitionContext{PrintHeader: true}, &metadata)
			require.NoError(t, err)
			require.NotEmpty(t, definition)

			// Routines are emitted inside DELIMITER blocks, which is mysql-client
			// script syntax the schema parser does not accept, so those
			// definitions are checked for delimiter discipline instead.
			if strings.Contains(definition, "DELIMITER") {
				if !unbalancedDelimiterBlocks[tc.Description] {
					requireDelimiterDiscipline(t, definition)
				}
			} else {
				_, err = GetDatabaseMetadata(definition)
				require.NoError(t, err, "generated definition should parse")
			}

			if record {
				tests[i].Expected = definition
				return
			}
			require.Equal(t, tc.Expected, definition)
		})
	}

	if record {
		yamltest.Record(t, filepath, tests)
	}
}