Repository: ariga/atlas
Pull request #1650 (2023-05-12 10:01:30)

sql/mysql: support inspecting table storage engines


Patch:
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 73986822..eb62a816 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -161,10 +161,10 @@ func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.
 	defer rows.Close()
 	for rows.Next() {
 		var (
-			autoinc                                             sql.NullInt64
-			tSchema, name, charset, collation, comment, options sql.NullString
+			autoinc                                                     sql.NullInt64
+			tSchema, name, charset, collation, comment, options, engine sql.NullString
 		)
-		if err := rows.Scan(&tSchema, &name, &charset, &collation, &autoinc, &comment, &options); err != nil {
+		if err := rows.Scan(&tSchema, &name, &charset, &collation, &autoinc, &comment, &options, &engine); err != nil {
 			return fmt.Errorf("scan table information: %w", err)
 		}
 		if !sqlx.ValidString(tSchema) || !sqlx.ValidString(name) {
@@ -196,6 +196,11 @@ func (i *inspect) tables(ctx context.Context, realm *schema.Realm, opts *schema.
 				V: options.String,
 			})
 		}
+		if sqlx.ValidString(engine) {
+			t.Attrs = append(t.Attrs, &Engine{
+				V: engine.String,
+			})
+		}
 		if autoinc.Valid {
 			t.Attrs = append(t.Attrs, &AutoIncrement{
 				V: autoinc.Int64,
@@ -701,7 +706,8 @@ SELECT
 	t1.TABLE_COLLATION,
 	t1.AUTO_INCREMENT,
 	t1.TABLE_COMMENT,
-	t1.CREATE_OPTIONS
+	t1.CREATE_OPTIONS,
+	t1.ENGINE
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	LEFT JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
@@ -721,7 +727,8 @@ SELECT
 	t1.TABLE_COLLATION,
 	t1.AUTO_INCREMENT,
 	t1.TABLE_COMMENT,
-	t1.CREATE_OPTIONS
+	t1.CREATE_OPTIONS,
+	t1.ENGINE
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
@@ -800,6 +807,12 @@ type (
 		S string
 	}
 
+	// Engine attribute describes the storage engine used to create a table.
+	Engine struct {
+		schema.Attr
+		V string // InnoDB, MyISAM, etc.
+	}
+
 	// OnUpdate attribute for columns with "ON UPDATE CURRENT_TIMESTAMP" as a default.
 	OnUpdate struct {
 		schema.Attr


Test patch:
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index d5ac4021..5d7c7f14 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -917,6 +917,7 @@ create table atlas_types_sanity
 				Attrs: []schema.Attr{
 					&schema.Charset{V: "latin1"},
 					&schema.Collation{V: "latin1_swedish_ci"},
+					&mysql.Engine{V: "InnoDB"},
 				},
 				Schema: realm.Schemas[0],
 				Columns: []*schema.Column{
@@ -1267,12 +1268,14 @@ create table atlas_types_sanity
 						return []schema.Attr{
 							&schema.Charset{V: "latin1"},
 							&schema.Collation{V: "latin1_swedish_ci"},
+							&mysql.Engine{V: "InnoDB"},
 							&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"},
 					}
 				}(),
 				Schema: realm.Schemas[0],
diff --git a/internal/integration/tidb_test.go b/internal/integration/tidb_test.go
index 8ae46c92..981cada5 100644
--- a/internal/integration/tidb_test.go
+++ b/internal/integration/tidb_test.go
@@ -704,9 +704,8 @@ create table atlas_types_sanity
 				Name: n,
 				Attrs: []schema.Attr{
 					&schema.Charset{V: "latin1"},
-					&schema.Collation{
-						V: "latin1_bin",
-					},
+					&schema.Collation{V: "latin1_bin"},
+					&mysql.Engine{V: "InnoDB"},
 				},
 				Schema: realm.Schemas[0],
 				Columns: []*schema.Column{
@@ -971,6 +970,7 @@ create table atlas_types_sanity
 					return []schema.Attr{
 						&schema.Charset{V: "latin1"},
 						&schema.Collation{V: "latin1_bin"},
+						&mysql.Engine{V: "InnoDB"},
 					}
 				}(),
 				Schema: realm.Schemas[0],
@@ -1008,6 +1008,7 @@ create table atlas_types_sanity
 					return []schema.Attr{
 						&schema.Collation{V: "utf8mb4_bin"},
 						&schema.Charset{V: "utf8mb4"},
+						&mysql.Engine{V: "InnoDB"},
 					}
 				}(),
 				Columns: []*schema.Column{
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 7f9b3d4f..d79c234b 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     |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
-| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
++--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+
+| 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     |
++--------------+--------------+--------------------+--------------------+----------------+---------------+-------------------+------------------+
 `))
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
@@ -84,6 +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"},
 					&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)
@@ -100,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     |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
-| public       | users        | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |
-+--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+
+| 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     |
++--------------+--------------+--------------------+--------------------+----------------+---------------+--------------------+------------------+
 `))
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
@@ -1069,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"})
+	rows := sqlmock.NewRows([]string{"table_schema", "table_name", "table_collation", "character_set", "auto_increment", "table_comment", "create_options", "engine"})
 	if exists {
-		rows.AddRow(schema, table, nil, nil, nil, nil, nil)
+		rows.AddRow(schema, table, nil, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(queryTable).
 		WithArgs(schema).
@@ -1079,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"})
+	rows := sqlmock.NewRows([]string{"schema", "table", "charset", "collate", "inc", "comment", "options", "engine"})
 	for _, t := range tables {
-		rows.AddRow(schema, t, nil, nil, nil, nil, nil)
+		rows.AddRow(schema, t, nil, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(queryTable).
 		WithArgs(schema).