Repository: ariga/atlas
Pull request #2913 (2024-07-02 13:58:50)

sql/mysql: support extending schema with system versioning


Patch:
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index c80ed428..76fb0c3c 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -83,6 +83,9 @@ func (d *diff) TableAttrDiff(from, to *schema.Table) ([]schema.Change, error) {
 	if change := d.engineChange(from.Attrs, to.Attrs); change != noChange {
 		changes = append(changes, change)
 	}
+	if change := d.systemVerChange(from.Attrs, to.Attrs); change != noChange {
+		changes = append(changes, change)
+	}
 	if !d.SupportsCheck() && sqlx.Has(to.Attrs, &schema.Check{}) {
 		return nil, fmt.Errorf("version %q does not support CHECK constraints", d.V)
 	}
@@ -337,6 +340,19 @@ func (*diff) engineChange(from, to []schema.Attr) schema.Change {
 	return noChange
 }
 
+// systemVerChange returns the schema change for migrating the system versioning
+// attributes if it was changed.
+func (d *diff) systemVerChange(from, to []schema.Attr) schema.Change {
+	switch fromHas, toHas := sqlx.Has(from, &SystemVersioned{}), sqlx.Has(to, &SystemVersioned{}); {
+	case fromHas && !toHas:
+		return &schema.DropAttr{A: &SystemVersioned{}}
+	case !fromHas && toHas:
+		return &schema.AddAttr{A: &SystemVersioned{}}
+	default:
+		return noChange
+	}
+}
+
 // charsetChange returns the schema change for migrating the collation if
 // it was changed, and it is not the default attribute inherited from its parent.
 func (*diff) charsetChange(from, top, to []schema.Attr) schema.Change {
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index bf437d16..e9de037d 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -58,7 +58,15 @@ func init() {
 		sqlclient.OpenerFunc(opener),
 		sqlclient.RegisterDriverOpener(Open),
 		sqlclient.RegisterCodec(MarshalHCL, EvalHCL),
-		sqlclient.RegisterFlavours("mysql+unix", "maria", "maria+unix", "mariadb", "mariadb+unix"),
+		sqlclient.RegisterFlavours("mysql+unix"),
+		sqlclient.RegisterURLParser(parser{}),
+	)
+	sqlclient.Register(
+		"mariadb",
+		sqlclient.OpenerFunc(opener),
+		sqlclient.RegisterDriverOpener(Open),
+		sqlclient.RegisterCodec(MarshalHCL, EvalMariaHCL),
+		sqlclient.RegisterFlavours("mariadb+unix", "maria", "maria+unix"),
 		sqlclient.RegisterURLParser(parser{}),
 	)
 }
diff --git a/sql/mysql/driver_oss.go b/sql/mysql/driver_oss.go
index e0125580..59126233 100644
--- a/sql/mysql/driver_oss.go
+++ b/sql/mysql/driver_oss.go
@@ -11,13 +11,14 @@ import (
 
 	"ariga.io/atlas/schemahcl"
 	"ariga.io/atlas/sql/internal/specutil"
+	"ariga.io/atlas/sql/internal/sqlx"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlspec"
 )
 
 var (
-	specOptions []schemahcl.Option
-	specFuncs   = &specutil.SchemaFuncs{
+	specOptions, mariaSpecOptions []schemahcl.Option
+	specFuncs                     = &specutil.SchemaFuncs{
 		Table: tableSpec,
 		View:  viewSpec,
 	}
@@ -31,6 +32,31 @@ func triggersSpec([]*schema.Trigger, *specutil.Doc) ([]*sqlspec.Trigger, error)
 	return nil, nil // unimplemented.
 }
 
+func (*inspect) tablesQuery(context.Context) string {
+	return tablesQuery
+}
+
+func (*inspect) tablesQueryArgs(context.Context) string {
+	return tablesQueryArgs
+}
+
+// newTable creates a new table with the given name and type.
+func (*inspect) newTable(name, _ string) *schema.Table {
+	return schema.NewTable(name)
+}
+
+func (s *state) tableAttr(*sqlx.Builder, schema.Change, schema.Attr) {
+	// unimplemented.
+}
+
+func convertTableAttrs(*sqlspec.Table, *schema.Table) error {
+	return nil // unimplemented.
+}
+
+func tableAttrsSpec(*schema.Table, *sqlspec.Table) {
+	// unimplemented.
+}
+
 func (*inspect) inspectViews(context.Context, *schema.Realm, *schema.InspectOptions) error {
 	return nil // unimplemented.
 }
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index e137a121..07662770 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -180,7 +180,7 @@ func (i *inspect) schemas(ctx context.Context, opts *schema.InspectRealmOption)
 func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.InspectOptions) error {
 	var (
 		args  []any
-		query = fmt.Sprintf(tablesQuery, nArgs(len(realm.Schemas)))
+		query = fmt.Sprintf(i.tablesQuery(ctx), nArgs(len(realm.Schemas)))
 	)
 	for _, s := range realm.Schemas {
 		args = append(args, s.Name)
@@ -189,7 +189,7 @@ func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.
 		for _, t := range opts.Tables {
 			args = append(args, t)
 		}
-		query = fmt.Sprintf(tablesQueryArgs, nArgs(len(realm.Schemas)), nArgs(len(opts.Tables)))
+		query = fmt.Sprintf(i.tablesQueryArgs(ctx), nArgs(len(realm.Schemas)), nArgs(len(opts.Tables)))
 	}
 	rows, err := i.QueryContext(ctx, query, args...)
 	if err != nil {
@@ -198,11 +198,11 @@ func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.
 	defer rows.Close()
 	for rows.Next() {
 		var (
-			defaultE                                                    sql.NullBool
-			autoinc                                                     sql.NullInt64
-			tSchema, name, charset, collation, comment, options, engine sql.NullString
+			defaultE                                                          sql.NullBool
+			autoinc                                                           sql.NullInt64
+			tSchema, name, charset, collation, comment, options, engine, ttyp sql.NullString
 		)
-		if err := rows.Scan(&tSchema, &name, &charset, &collation, &autoinc, &comment, &options, &engine, &defaultE); err != nil {
+		if err := rows.Scan(&tSchema, &name, &charset, &collation, &autoinc, &comment, &options, &engine, &defaultE, &ttyp); err != nil {
 			return fmt.Errorf("scan table information: %w", err)
 		}
 		if !sqlx.ValidString(tSchema) || !sqlx.ValidString(name) {
@@ -212,7 +212,7 @@ func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.
 		if !ok {
 			return fmt.Errorf("schema %q was not found in realm", tSchema.String)
 		}
-		t := &schema.Table{Name: name.String}
+		t := i.newTable(name.String, ttyp.String)
 		s.AddTables(t)
 		if sqlx.ValidString(charset) {
 			t.Attrs = append(t.Attrs, &schema.Charset{
@@ -729,7 +729,8 @@ SELECT
 	t1.TABLE_COMMENT,
 	t1.CREATE_OPTIONS,
 	t1.ENGINE,
-	t3.SUPPORT = 'DEFAULT' AS DEFAULT_ENGINE
+	t3.SUPPORT = 'DEFAULT' AS DEFAULT_ENGINE,
+	t1.TABLE_TYPE
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	LEFT JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
@@ -752,7 +753,8 @@ SELECT
 	t1.TABLE_COMMENT,
 	t1.CREATE_OPTIONS,
 	t1.ENGINE,
-	t3.SUPPORT = 'DEFAULT' AS DEFAULT_ENGINE
+	t3.SUPPORT = 'DEFAULT' AS DEFAULT_ENGINE,
+	t1.TABLE_TYPE
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
@@ -854,6 +856,12 @@ type (
 		Default bool   // The default engine used by the server.
 	}
 
+	// SystemVersioned is an attribute attached to MariaDB tables indicates they are
+	// system versioned. See: https://mariadb.com/kb/en/system-versioned-tables
+	SystemVersioned struct {
+		schema.Attr
+	}
+
 	// OnUpdate attribute for columns with "ON UPDATE CURRENT_TIMESTAMP" as a default.
 	OnUpdate struct {
 		schema.Attr
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 9462e726..96da3734 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -289,7 +289,7 @@ func (s *state) addTable(add *schema.AddTable) error {
 	if len(errs) > 0 {
 		return fmt.Errorf("create table %q: %s", add.T.Name, strings.Join(errs, ", "))
 	}
-	s.tableAttr(b, add, add.T.Attrs...)
+	s.tableAttrs(b, add, add.T.Attrs...)
 	s.append(&migrate.Change{
 		Cmd:     b.String(),
 		Source:  add,
@@ -357,8 +357,6 @@ func (s *state) modifyTable(modify *schema.ModifyTable) error {
 			changes[1] = append(changes[1], &schema.AddIndex{
 				I: change.To,
 			})
-		case *schema.DropAttr:
-			return fmt.Errorf("unsupported change type: %v", change.A)
 		default:
 			changes[1] = append(changes[1], change)
 		}
@@ -459,11 +457,11 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 				b.P("DROP FOREIGN KEY").Ident(change.F.Symbol)
 				reverse = append(reverse, &schema.AddForeignKey{F: change.F})
 			case *schema.AddAttr:
-				s.tableAttr(b, change, change.A)
-				// Unsupported reverse operation.
-				reversible = false
+				s.tableAttrs(b, change, change.A)
+			case *schema.DropAttr:
+				s.tableAttrs(b, change, change.A)
 			case *schema.ModifyAttr:
-				s.tableAttr(b, change, change.To)
+				s.tableAttrs(b, change, change.To)
 				reverse = append(reverse, &schema.ModifyAttr{
 					From: change.To,
 					To:   change.From,
@@ -687,9 +685,9 @@ func (s *state) fks(commaF func(any, func(int, *sqlx.Builder) error) error, fks
 	})
 }
 
-// tableAttr writes the given table attribute to the SQL
+// tableAttrs writes the given table attributes to the SQL
 // statement builder when a table is created or altered.
-func (s *state) tableAttr(b *sqlx.Builder, c schema.Change, attrs ...schema.Attr) {
+func (s *state) tableAttrs(b *sqlx.Builder, c schema.Change, attrs ...schema.Attr) {
 	for _, a := range attrs {
 		switch a := a.(type) {
 		case *CreateOptions:
@@ -713,6 +711,9 @@ func (s *state) tableAttr(b *sqlx.Builder, c schema.Change, attrs ...schema.Attr
 			b.P("COLLATE", a.V)
 		case *schema.Comment:
 			b.P("COMMENT", quote(a.Text))
+		default:
+			// Driver/build specific handling.
+			s.tableAttr(b, c, a)
 		}
 	}
 }
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index 6cc8c51e..1dc7a76a 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -21,11 +21,11 @@ import (
 )
 
 // evalSpec evaluates an Atlas DDL document into v using the input.
-func evalSpec(p *hclparse.Parser, v any, input map[string]cty.Value) error {
+func evalSpec(state *schemahcl.State, p *hclparse.Parser, v any, input map[string]cty.Value) error {
 	switch v := v.(type) {
 	case *schema.Realm:
 		var d specutil.Doc
-		if err := hclState.Eval(p, &d, input); err != nil {
+		if err := state.Eval(p, &d, input); err != nil {
 			return err
 		}
 		if err := specutil.Scan(v,
@@ -45,7 +45,7 @@ func evalSpec(p *hclparse.Parser, v any, input map[string]cty.Value) error {
 		}
 	case *schema.Schema:
 		var d specutil.Doc
-		if err := hclState.Eval(p, &d, input); err != nil {
+		if err := state.Eval(p, &d, input); err != nil {
 			return err
 		}
 		if len(d.Schemas) != 1 {
@@ -65,7 +65,7 @@ func evalSpec(p *hclparse.Parser, v any, input map[string]cty.Value) error {
 	case schema.Schema, schema.Realm:
 		return fmt.Errorf("mysql: Eval expects a pointer: received %[1]T, expected *%[1]T", v)
 	default:
-		return hclState.Eval(p, v, input)
+		return state.Eval(p, v, input)
 	}
 	return nil
 }
@@ -79,19 +79,29 @@ func MarshalSpec(v any, marshaler schemahcl.Marshaler) ([]byte, error) {
 }
 
 var (
+	registrySpecs     = TypeRegistry.Specs()
+	sharedSpecOptions = []schemahcl.Option{
+		schemahcl.WithTypes("table.column.type", registrySpecs),
+		schemahcl.WithTypes("view.column.type", registrySpecs),
+		schemahcl.WithScopedEnums("view.check_option", schema.ViewCheckOptionLocal, schema.ViewCheckOptionCascaded),
+		schemahcl.WithScopedEnums("table.engine", EngineInnoDB, EngineMyISAM, EngineMemory, EngineCSV, EngineNDB),
+		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
+		schemahcl.WithScopedEnums("table.index.parser", IndexParserNGram, IndexParserMeCab),
+		schemahcl.WithScopedEnums("table.primary_key.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
+		schemahcl.WithScopedEnums("table.column.as.type", stored, persistent, virtual),
+		schemahcl.WithScopedEnums("table.foreign_key.on_update", specutil.ReferenceVars...),
+		schemahcl.WithScopedEnums("table.foreign_key.on_delete", specutil.ReferenceVars...),
+	}
 	hclState = schemahcl.New(
 		append(
 			specOptions,
-			schemahcl.WithTypes("table.column.type", TypeRegistry.Specs()),
-			schemahcl.WithTypes("view.column.type", TypeRegistry.Specs()),
-			schemahcl.WithScopedEnums("view.check_option", schema.ViewCheckOptionLocal, schema.ViewCheckOptionCascaded),
-			schemahcl.WithScopedEnums("table.engine", EngineInnoDB, EngineMyISAM, EngineMemory, EngineCSV, EngineNDB),
-			schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
-			schemahcl.WithScopedEnums("table.index.parser", IndexParserNGram, IndexParserMeCab),
-			schemahcl.WithScopedEnums("table.primary_key.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
-			schemahcl.WithScopedEnums("table.column.as.type", stored, persistent, virtual),
-			schemahcl.WithScopedEnums("table.foreign_key.on_update", specutil.ReferenceVars...),
-			schemahcl.WithScopedEnums("table.foreign_key.on_delete", specutil.ReferenceVars...),
+			sharedSpecOptions...,
+		)...,
+	)
+	mariaHCLState = schemahcl.New(
+		append(
+			mariaSpecOptions,
+			sharedSpecOptions...,
 		)...,
 	)
 	// MarshalHCL marshals v into an Atlas HCL DDL document.
@@ -99,11 +109,17 @@ var (
 		return MarshalSpec(v, hclState)
 	})
 	// EvalHCL implements the schemahcl.Evaluator interface.
-	EvalHCL = schemahcl.EvalFunc(evalSpec)
-
-	// EvalHCLBytes is a helper that evaluates an HCL document from a byte slice instead
-	// of from an hclparse.Parser instance.
+	EvalHCL = schemahcl.EvalFunc(func(h *hclparse.Parser, v any, m map[string]cty.Value) error {
+		return evalSpec(hclState, h, v, m)
+	})
+	// EvalHCLBytes is a helper that evaluates an HCL document from a byte slice.
 	EvalHCLBytes = specutil.HCLBytesFunc(EvalHCL)
+	// EvalMariaHCL implements the schemahcl.Evaluator interface for MariaDB flavor.
+	EvalMariaHCL = schemahcl.EvalFunc(func(h *hclparse.Parser, v any, m map[string]cty.Value) error {
+		return evalSpec(mariaHCLState, h, v, m)
+	})
+	// EvalMariaHCLBytes is a helper that evaluates a MariaDB HCL document from a byte slice.
+	EvalMariaHCLBytes = specutil.HCLBytesFunc(EvalMariaHCL)
 )
 
 // convertTable converts a sqlspec.Table to a schema.Table. Table conversion is done without converting
@@ -133,7 +149,10 @@ func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, er
 		}
 		t.AddAttrs(&Engine{V: v})
 	}
-	return t, err
+	if err := convertTableAttrs(spec, t); err != nil {
+		return nil, err
+	}
+	return t, nil
 }
 
 // convertView converts a sqlspec.View to a schema.View.
@@ -316,6 +335,7 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 		}
 		ts.Extra.Attrs = append(ts.Extra.Attrs, attr)
 	}
+	tableAttrsSpec(t, ts)
 	return ts, nil
 }
 


Test patch:
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 957899f8..4a435c92 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -43,11 +43,11 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryTable).
 					WithArgs("public").
 					WillReturnRows(sqltest.Rows(`
-+--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+
-| TABLE_SCHEMA | TABLE_NAME   | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS    |      ENGINE      |  DEFAULT_ENGINE  |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+
-| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB"|       InnoDB     |       1          |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+
++--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+------------------+ 
+| TABLE_SCHEMA | TABLE_NAME   | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS    |      ENGINE      |  DEFAULT_ENGINE  |  TABLE_TYPE      | 
++--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+------------------+ 
+| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB"|       InnoDB     |       1          |                  | 
++--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+------------------+ 
 `))
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
@@ -102,11 +102,11 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryTable).
 					WithArgs("public").
 					WillReturnRows(sqltest.Rows(`
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+
-| TABLE_SCHEMA | TABLE_NAME   | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS     |      ENGINE      |  DEFAULT_ENGINE  |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+
-| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |       InnoDB     |       1          |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+------------------+ 
+| TABLE_SCHEMA | TABLE_NAME   | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS     |      ENGINE      |  DEFAULT_ENGINE  |  TABLE_TYPE      | 
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+------------------+ 
+| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |       InnoDB     |       1          |                  | 
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+------------------+ 
 `))
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
@@ -1088,9 +1088,9 @@ func (m mock) noFKs() {
 }
 
 func (m mock) tableExists(schema, table string, exists bool) {
-	rows := sqlmock.NewRows([]string{"table_schema", "table_name", "table_collation", "character_set", "auto_increment", "table_comment", "create_options", "engine", "default_engine"})
+	rows := sqlmock.NewRows([]string{"table_schema", "table_name", "table_collation", "character_set", "auto_increment", "table_comment", "create_options", "engine", "default_engine", "table_type"})
 	if exists {
-		rows.AddRow(schema, table, nil, nil, nil, nil, nil, nil, nil)
+		rows.AddRow(schema, table, nil, nil, nil, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(queryTable).
 		WithArgs(schema).
@@ -1098,9 +1098,9 @@ func (m mock) tableExists(schema, table string, exists bool) {
 }
 
 func (m mock) tables(schema string, tables ...string) {
-	rows := sqlmock.NewRows([]string{"schema", "table", "charset", "collate", "inc", "comment", "options", "engine", "default_engine"})
+	rows := sqlmock.NewRows([]string{"schema", "table", "charset", "collate", "inc", "comment", "options", "engine", "default_engine", "table_type"})
 	for _, t := range tables {
-		rows.AddRow(schema, t, nil, nil, nil, nil, nil, nil, nil)
+		rows.AddRow(schema, t, nil, nil, nil, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(queryTable).
 		WithArgs(schema).