Repository: ariga/atlas
Pull request #1655 (2023-05-13 07:57:13)

sql/mysql: print non-default storage engines


Patch:
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index eb76c16d..efc5380d 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -363,6 +363,12 @@ const (
 	IndexTypeFullText = "FULLTEXT"
 	IndexTypeSpatial  = "SPATIAL"
 
+	EngineInnoDB = "InnoDB"
+	EngineMyISAM = "MyISAM"
+	EngineMemory = "Memory"
+	EngineCSV    = "CSV"
+	EngineNDB    = "NDB" // NDBCLUSTER
+
 	currentTS     = "current_timestamp"
 	defaultGen    = "default_generated"
 	autoIncrement = "auto_increment"
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index eb62a816..6f6ecfd3 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -161,10 +161,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
 		)
-		if err := rows.Scan(&tSchema, &name, &charset, &collation, &autoinc, &comment, &options, &engine); err != nil {
+		if err := rows.Scan(&tSchema, &name, &charset, &collation, &autoinc, &comment, &options, &engine, &defaultE); err != nil {
 			return fmt.Errorf("scan table information: %w", err)
 		}
 		if !sqlx.ValidString(tSchema) || !sqlx.ValidString(name) {
@@ -196,9 +197,10 @@ func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.
 				V: options.String,
 			})
 		}
-		if sqlx.ValidString(engine) {
+		if sqlx.ValidString(engine) && defaultE.Valid {
 			t.Attrs = append(t.Attrs, &Engine{
-				V: engine.String,
+				V:       engine.String,
+				Default: defaultE.Bool,
 			})
 		}
 		if autoinc.Valid {
@@ -707,11 +709,14 @@ SELECT
 	t1.AUTO_INCREMENT,
 	t1.TABLE_COMMENT,
 	t1.CREATE_OPTIONS,
-	t1.ENGINE
+	t1.ENGINE,
+	t3.SUPPORT = 'DEFAULT' AS DEFAULT_ENGINE
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	LEFT JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
 	ON t1.TABLE_COLLATION = t2.COLLATION_NAME
+	LEFT JOIN INFORMATION_SCHEMA.ENGINES AS t3
+	ON t1.ENGINE = t3.ENGINE
 WHERE
 	TABLE_SCHEMA IN (%s)
 	AND TABLE_TYPE = 'BASE TABLE'
@@ -728,11 +733,14 @@ SELECT
 	t1.AUTO_INCREMENT,
 	t1.TABLE_COMMENT,
 	t1.CREATE_OPTIONS,
-	t1.ENGINE
+	t1.ENGINE,
+	t3.SUPPORT = 'DEFAULT' AS DEFAULT_ENGINE
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
 	ON t1.TABLE_COLLATION = t2.COLLATION_NAME
+	LEFT JOIN INFORMATION_SCHEMA.ENGINES AS t3
+	ON t1.ENGINE = t3.ENGINE
 WHERE
 	TABLE_SCHEMA IN (%s)
 	AND TABLE_NAME IN (%s)
@@ -810,7 +818,8 @@ type (
 	// Engine attribute describes the storage engine used to create a table.
 	Engine struct {
 		schema.Attr
-		V string // InnoDB, MyISAM, etc.
+		V       string // InnoDB, MyISAM, etc.
+		Default bool   // The default engine used by the server.
 	}
 
 	// OnUpdate attribute for columns with "ON UPDATE CURRENT_TIMESTAMP" as a default.
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index e36cf6ac..e18de3ad 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -79,6 +79,7 @@ func MarshalSpec(v any, marshaler schemahcl.Marshaler) ([]byte, error) {
 var (
 	hclState = schemahcl.New(
 		schemahcl.WithTypes("table.column.type", TypeRegistry.Specs()),
+		schemahcl.WithScopedEnums("table.engine", EngineInnoDB, EngineMyISAM, EngineMemory, EngineCSV, EngineNDB),
 		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
 		schemahcl.WithScopedEnums("table.primary_key.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
 		schemahcl.WithScopedEnums("table.column.as.type", stored, persistent, virtual),
@@ -255,6 +256,17 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 	if c, ok := hasCollate(t.Attrs, t.Schema.Attrs); ok {
 		ts.Extra.Attrs = append(ts.Extra.Attrs, schemahcl.StringAttr("collate", c))
 	}
+	// Marshal the engine attribute only if it is not InnoDB (default).
+	if e := (&Engine{}); sqlx.Has(t.Attrs, e) && e.V != "" && !e.Default {
+		attr := schemahcl.StringAttr("engine", e.V)
+		for _, e1 := range []string{EngineInnoDB, EngineMyISAM, EngineMemory, EngineCSV, EngineNDB} {
+			if strings.EqualFold(e.V, e1) {
+				attr = specutil.VarAttr("engine", e1)
+				break
+			}
+		}
+		ts.Extra.Attrs = append(ts.Extra.Attrs, attr)
+	}
 	return ts, nil
 }
 


Test patch:
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index 5d7c7f14..66b40604 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -917,7 +917,7 @@ create table atlas_types_sanity
 				Attrs: []schema.Attr{
 					&schema.Charset{V: "latin1"},
 					&schema.Collation{V: "latin1_swedish_ci"},
-					&mysql.Engine{V: "InnoDB"},
+					&mysql.Engine{V: "InnoDB", Default: true},
 				},
 				Schema: realm.Schemas[0],
 				Columns: []*schema.Column{
@@ -1268,14 +1268,14 @@ create table atlas_types_sanity
 						return []schema.Attr{
 							&schema.Charset{V: "latin1"},
 							&schema.Collation{V: "latin1_swedish_ci"},
-							&mysql.Engine{V: "InnoDB"},
+							&mysql.Engine{V: "InnoDB", Default: true},
 							&schema.Check{Name: "tJSON", Expr: "json_valid(`tJSON`)"},
 						}
 					}
 					return []schema.Attr{
 						&schema.Charset{V: "latin1"},
 						&schema.Collation{V: "latin1_swedish_ci"},
-						&mysql.Engine{V: "InnoDB"},
+						&mysql.Engine{V: "InnoDB", Default: true},
 					}
 				}(),
 				Schema: realm.Schemas[0],
diff --git a/internal/integration/tidb_test.go b/internal/integration/tidb_test.go
index 981cada5..c72e4ccb 100644
--- a/internal/integration/tidb_test.go
+++ b/internal/integration/tidb_test.go
@@ -705,7 +705,7 @@ create table atlas_types_sanity
 				Attrs: []schema.Attr{
 					&schema.Charset{V: "latin1"},
 					&schema.Collation{V: "latin1_bin"},
-					&mysql.Engine{V: "InnoDB"},
+					&mysql.Engine{V: "InnoDB", Default: true},
 				},
 				Schema: realm.Schemas[0],
 				Columns: []*schema.Column{
@@ -970,7 +970,7 @@ create table atlas_types_sanity
 					return []schema.Attr{
 						&schema.Charset{V: "latin1"},
 						&schema.Collation{V: "latin1_bin"},
-						&mysql.Engine{V: "InnoDB"},
+						&mysql.Engine{V: "InnoDB", Default: true},
 					}
 				}(),
 				Schema: realm.Schemas[0],
@@ -1008,7 +1008,7 @@ create table atlas_types_sanity
 					return []schema.Attr{
 						&schema.Collation{V: "utf8mb4_bin"},
 						&schema.Charset{V: "utf8mb4"},
-						&mysql.Engine{V: "InnoDB"},
+						&mysql.Engine{V: "InnoDB", Default: true},
 					}
 				}(),
 				Columns: []*schema.Column{
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index d79c234b..fc7cf001 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -42,11 +42,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      |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+
-| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB"|       InnoDB     |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+
++--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+
+| 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          |
++--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+------------------+
 `))
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
@@ -84,7 +84,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					&schema.Collation{V: "utf8mb4_0900_ai_ci"},
 					&schema.Comment{Text: "Comment"},
 					&CreateOptions{V: `COMPRESSION="ZLIB"`},
-					&Engine{V: "InnoDB"},
+					&Engine{V: "InnoDB", Default: true},
 					&CreateStmt{S: "CREATE TABLE users (id bigint NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=55834574848 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin"},
 					&AutoIncrement{V: 55834574848},
 				}, t.Attrs)
@@ -101,11 +101,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      |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+
-| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |       InnoDB     |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+
+| 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          |
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+------------------+
 `))
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
@@ -1070,9 +1070,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"})
+	rows := sqlmock.NewRows([]string{"table_schema", "table_name", "table_collation", "character_set", "auto_increment", "table_comment", "create_options", "engine", "default_engine"})
 	if exists {
-		rows.AddRow(schema, table, nil, nil, nil, nil, nil, nil)
+		rows.AddRow(schema, table, nil, nil, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(queryTable).
 		WithArgs(schema).
@@ -1080,9 +1080,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"})
+	rows := sqlmock.NewRows([]string{"schema", "table", "charset", "collate", "inc", "comment", "options", "engine", "default_engine"})
 	for _, t := range tables {
-		rows.AddRow(schema, t, nil, nil, nil, nil, nil, nil)
+		rows.AddRow(schema, t, nil, nil, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(queryTable).
 		WithArgs(schema).
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index c5effdeb..ca2a9543 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -541,6 +541,62 @@ schema "test" {
 	require.EqualValues(t, expected, string(buf))
 }
 
+func TestMarshalSpec_TableEngine(t *testing.T) {
+	s := schema.New("a8m").
+		AddTables(
+			// InnoDB (default) is not printed.
+			schema.NewTable("repos").AddAttrs(&Engine{V: EngineInnoDB, Default: true}).AddColumns(schema.NewIntColumn("id", TypeBigInt)),
+			schema.NewTable("stars").AddAttrs(&Engine{V: "INNODB", Default: true}).AddColumns(schema.NewIntColumn("id", TypeBigInt)),
+			schema.NewTable("prs").AddAttrs(&Engine{V: EngineMyISAM}).AddColumns(schema.NewIntColumn("id", TypeBigInt)),
+			schema.NewTable("issues").AddAttrs(&Engine{V: "MYISAM"}).AddColumns(schema.NewIntColumn("id", TypeBigInt)),
+			schema.NewTable("commits").AddAttrs(&Engine{V: "MyRocks"}).AddColumns(schema.NewIntColumn("id", TypeBigInt)),
+		)
+	buf, err := MarshalSpec(s, hclState)
+	require.NoError(t, err)
+	const expected = `table "repos" {
+  schema = schema.a8m
+  column "id" {
+    null = false
+    type = bigint
+  }
+}
+table "stars" {
+  schema = schema.a8m
+  column "id" {
+    null = false
+    type = bigint
+  }
+}
+table "prs" {
+  schema = schema.a8m
+  engine = MyISAM
+  column "id" {
+    null = false
+    type = bigint
+  }
+}
+table "issues" {
+  schema = schema.a8m
+  engine = MyISAM
+  column "id" {
+    null = false
+    type = bigint
+  }
+}
+table "commits" {
+  schema = schema.a8m
+  engine = "MyRocks"
+  column "id" {
+    null = false
+    type = bigint
+  }
+}
+schema "a8m" {
+}
+`
+	require.EqualValues(t, expected, string(buf))
+}
+
 func TestUnmarshalSpec_IndexParts(t *testing.T) {
 	var (
 		s schema.Schema