Repository: ariga/atlas
Pull request #376 (2022-01-05 19:49:52)

sql/mysql: create options
In MySQL, there's a way (supported also by ent) to append additional options to the `CREATE TABLE` statement. 


Patch:
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 307f0100..ca88428f 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -156,10 +156,10 @@ func (i *inspect) table(ctx context.Context, name string, opts *schema.InspectTa
 	}
 	row := i.QueryRowContext(ctx, query, args...)
 	var (
-		autoinc                              sql.NullInt64
-		tSchema, charset, collation, comment sql.NullString
+		autoinc                                       sql.NullInt64
+		tSchema, charset, collation, comment, options sql.NullString
 	)
-	if err := row.Scan(&tSchema, &charset, &collation, &autoinc, &comment); err != nil {
+	if err := row.Scan(&tSchema, &charset, &collation, &autoinc, &comment, &options); err != nil {
 		if err == sql.ErrNoRows {
 			return nil, &schema.NotExistError{
 				Err: fmt.Errorf("mysql: table %q was not found", name),
@@ -183,6 +183,11 @@ func (i *inspect) table(ctx context.Context, name string, opts *schema.InspectTa
 			Text: comment.String,
 		})
 	}
+	if sqlx.ValidString(options) {
+		t.Attrs = append(t.Attrs, &CreateOptions{
+			V: options.String,
+		})
+	}
 	if autoinc.Valid {
 		t.Attrs = append(t.Attrs, &AutoIncrement{
 			V: autoinc.Int64,
@@ -676,7 +681,8 @@ SELECT
 	t2.CHARACTER_SET_NAME,
 	t1.TABLE_COLLATION,
 	t1.AUTO_INCREMENT,
-	t1.TABLE_COMMENT
+	t1.TABLE_COMMENT,
+	t1.CREATE_OPTIONS
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
@@ -691,7 +697,8 @@ SELECT
 	t2.CHARACTER_SET_NAME,
 	t1.TABLE_COLLATION,
 	t1.AUTO_INCREMENT,
-	t1.TABLE_COMMENT
+	t1.TABLE_COMMENT,
+	t1.CREATE_OPTIONS
 FROM
 	INFORMATION_SCHEMA.TABLES AS t1
 	JOIN INFORMATION_SCHEMA.COLLATIONS AS t2
@@ -754,6 +761,12 @@ type (
 		V int64
 	}
 
+	// CreateOptions attribute for describing extra options used with CREATE TABLE.
+	CreateOptions struct {
+		schema.Attr
+		V string
+	}
+
 	// 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 23103a57..0c160c69 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -440,6 +440,8 @@ func (s *state) fks(b *sqlx.Builder, fks ...*schema.ForeignKey) {
 func (s *state) tableAttr(b *sqlx.Builder, attrs ...schema.Attr) error {
 	for _, a := range attrs {
 		switch a := a.(type) {
+		case *CreateOptions:
+			b.P(a.V)
 		case *AutoIncrement:
 			if a.V == 0 {
 				return fmt.Errorf("missing value for table option AUTO_INCREMENT")


Test patch:
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 8917750b..bf50e03c 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -57,11 +57,11 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(sqltest.Escape(tableQuery)).
 					WithArgs("users").
 					WillReturnRows(sqltest.Rows(`
-+--------------+--------------------+--------------------+----------------+---------------+
-| TABLE_SCHEMA | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT |
-+--------------+--------------------+--------------------+----------------+---------------+
-| test         | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       |
-+--------------+--------------------+--------------------+----------------+---------------+
++--------------+--------------------+--------------------+----------------+---------------+--------------------+
+| TABLE_SCHEMA | CHARACTER_SET_NAME | TABLE_COLLATION    | AUTO_INCREMENT | TABLE_COMMENT | CREATE_OPTIONS     |
++--------------+--------------------+--------------------+----------------+---------------+--------------------+
+| test         | utf8mb4            | utf8mb4_0900_ai_ci | nil            | Comment       | COMPRESSION="ZLIB" |
++--------------+--------------------+--------------------+----------------+---------------+--------------------+
 `))
 				m.ExpectQuery(sqltest.Escape(columnsQuery)).
 					WithArgs("test", "users").
@@ -82,6 +82,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					&schema.Charset{V: "utf8mb4"},
 					&schema.Collation{V: "utf8mb4_0900_ai_ci"},
 					&schema.Comment{Text: "Comment"},
+					&CreateOptions{V: `COMPRESSION="ZLIB"`},
 				}, t.Attrs)
 				require.Len(t.PrimaryKey.Parts, 1)
 				require.True(t.PrimaryKey.Parts[0].C == t.Columns[0])
@@ -889,9 +890,9 @@ func (m mock) tables(schema string, names ...string) {
 }
 
 func (m mock) tableExists(schema, table string, exists bool) {
-	rows := sqlmock.NewRows([]string{"table_schema", "table_collation", "character_set", "auto_increment", "table_comment"})
+	rows := sqlmock.NewRows([]string{"table_schema", "table_collation", "character_set", "auto_increment", "table_comment", "create_options"})
 	if exists {
-		rows.AddRow(schema, nil, nil, nil, nil)
+		rows.AddRow(schema, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(sqltest.Escape(tableQuery)).
 		WithArgs(table).
@@ -899,9 +900,9 @@ func (m mock) tableExists(schema, table string, exists bool) {
 }
 
 func (m mock) tableExistsInSchema(schema, table string, exists bool) {
-	rows := sqlmock.NewRows([]string{"table_schema", "table_collation", "character_set", "auto_increment", "table_comment"})
+	rows := sqlmock.NewRows([]string{"table_schema", "table_collation", "character_set", "auto_increment", "table_comment", "create_options"})
 	if exists {
-		rows.AddRow(schema, nil, nil, nil, nil)
+		rows.AddRow(schema, nil, nil, nil, nil, nil)
 	}
 	m.ExpectQuery(sqltest.Escape(tableSchemaQuery)).
 		WithArgs(table, schema).
diff --git a/sql/mysql/migrate_test.go b/sql/mysql/migrate_test.go
index 44c77cd3..ce46a9f5 100644
--- a/sql/mysql/migrate_test.go
+++ b/sql/mysql/migrate_test.go
@@ -224,6 +224,7 @@ func TestPlanChanges(t *testing.T) {
 							&schema.Collation{V: "utf8mb4_bin"},
 							&schema.Comment{Text: "posts comment"},
 							&schema.Check{Name: "id_nonzero", Expr: "(`id` > 0)"},
+							&CreateOptions{V: `COMPRESSION="ZLIB"`},
 						},
 					}
 					t.PrimaryKey = &schema.Index{Parts: []*schema.IndexPart{{C: t.Columns[0]}}}
@@ -232,7 +233,7 @@ func TestPlanChanges(t *testing.T) {
 			},
 			plan: &migrate.Plan{
 				Reversible: true,
-				Changes:    []*migrate.Change{{Cmd: "CREATE TABLE `posts` (`id` bigint NOT NULL AUTO_INCREMENT, `text` text NULL, PRIMARY KEY (`id`), CONSTRAINT `id_nonzero` CHECK (`id` > 0)) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT \"posts comment\" AUTO_INCREMENT 100", Reverse: "DROP TABLE `posts`"}},
+				Changes:    []*migrate.Change{{Cmd: "CREATE TABLE `posts` (`id` bigint NOT NULL AUTO_INCREMENT, `text` text NULL, PRIMARY KEY (`id`), CONSTRAINT `id_nonzero` CHECK (`id` > 0)) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT \"posts comment\" COMPRESSION=\"ZLIB\" AUTO_INCREMENT 100", Reverse: "DROP TABLE `posts`"}},
 			},
 		},
 		{