Repository: ariga/atlas
Pull request #345 (2021-12-28 11:40:39)

sql/mysql: support auto_increment attribute in migration


Patch:
diff --git a/go.mod b/go.mod
index bed4d890..6a9916d1 100644
--- a/go.mod
+++ b/go.mod
@@ -22,7 +22,6 @@ require (
 require (
 	github.com/go-openapi/inflect v0.19.0
 	github.com/mitchellh/go-homedir v1.0.0
-	github.com/pelletier/go-toml/v2 v2.0.0-beta.4
 )
 
 require (
diff --git a/go.sum b/go.sum
index 89014137..5dfd7d83 100644
--- a/go.sum
+++ b/go.sum
@@ -223,10 +223,7 @@ github.com/modern-go/concurrent v0.0.0-20180228061459-e0a39a4cb421/go.mod h1:6dJ
 github.com/modern-go/reflect2 v0.0.0-20180701023420-4b7aa43c6742/go.mod h1:bx2lNnkwVCuqBIxFjflWJWanXIb3RllmbCylyMrvgv0=
 github.com/modern-go/reflect2 v1.0.1/go.mod h1:bx2lNnkwVCuqBIxFjflWJWanXIb3RllmbCylyMrvgv0=
 github.com/pascaldekloe/goe v0.0.0-20180627143212-57f6aae5913c/go.mod h1:lzWF7FIEvWOWxwDKqyGYQf6ZUaNfKdP144TG7ZOy1lc=
-github.com/pelletier/go-toml v1.9.3 h1:zeC5b1GviRUyKYd6OJPvBU/mcVDVoL1OhT17FCt5dSQ=
 github.com/pelletier/go-toml v1.9.3/go.mod h1:u1nR/EPcESfeI/szUZKdtJ0xRNbUoANCkoOuaOx1Y+c=
-github.com/pelletier/go-toml/v2 v2.0.0-beta.4 h1:GCs8ebsDtEH3RiO78+BvhHqj65d/I6tjESitJZc07Rc=
-github.com/pelletier/go-toml/v2 v2.0.0-beta.4/go.mod h1:ke6xncR3W76Ba8xnVxkrZG0js6Rd2BsQEAYrfgJ6eQA=
 github.com/pkg/errors v0.8.1/go.mod h1:bwawxfHBFNV+L2hUp1rHADufV3IMtnDRdf1r5NINEl0=
 github.com/pkg/sftp v1.10.1/go.mod h1:lYOWFsE0bwd1+KfKJaKeuokY15vzFx25BLbzYYoAxZI=
 github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
diff --git a/sql/internal/sqlx/sqlx.go b/sql/internal/sqlx/sqlx.go
index 2ed98a9f..676d9de6 100644
--- a/sql/internal/sqlx/sqlx.go
+++ b/sql/internal/sqlx/sqlx.go
@@ -223,6 +223,20 @@ func (b *Builder) MapComma(x interface{}, f func(i int, b *Builder)) *Builder {
 	return b
 }
 
+// MapCommaErr is like MapComma, but returns an error if f returns an error.
+func (b *Builder) MapCommaErr(x interface{}, f func(i int, b *Builder) error) error {
+	s := reflect.ValueOf(x)
+	for i := 0; i < s.Len(); i++ {
+		if i > 0 {
+			b.Comma()
+		}
+		if err := f(i, b); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
 // Wrap wraps the written string with parentheses.
 func (b *Builder) Wrap(f func(b *Builder)) *Builder {
 	b.WriteByte('(')
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 593bf43d..aca2bc32 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -224,7 +224,7 @@ func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
 		return err
 	}
 	c.Type.Type = ct
-	if err := extraAttr(c, extra.String); err != nil {
+	if err := extraAttr(t, c, extra.String); err != nil {
 		return err
 	}
 	if sqlx.ValidString(defaults) {
@@ -564,14 +564,18 @@ func parseColumn(typ string) (parts []string, size int64, unsigned bool, err err
 
 // extraAttr parses the EXTRA column from the INFORMATION_SCHEMA.COLUMNS table
 // and appends its parsed representation to the column.
-func extraAttr(c *schema.Column, extra string) error {
+func extraAttr(t *schema.Table, c *schema.Column, extra string) error {
 	switch extra := strings.ToLower(extra); extra {
 	case "", "null": // ignore.
 	case defaultGen:
 		// The column has an expression default value
 		// and it is handled in Driver.addColumn.
 	case autoIncrement:
-		c.Attrs = append(c.Attrs, &AutoIncrement{A: extra})
+		a := &AutoIncrement{}
+		// Reference to the table attribute if exists, as
+		// there can be only one auto_increment in a table.
+		sqlx.Has(t.Attrs, a)
+		c.Attrs = append(c.Attrs, a)
 	case "default_generated on update current_timestamp", "on update current_timestamp",
 		"on update current_timestamp()" /* MariaDB format. */ :
 		c.Attrs = append(c.Attrs, &OnUpdate{A: extra})
@@ -742,9 +746,9 @@ ORDER BY
 
 type (
 	// AutoIncrement attribute for columns with "AUTO_INCREMENT" as a default.
+	// V represent an optional start value for the counter.
 	AutoIncrement struct {
 		schema.Attr
-		A string
 		V int64
 	}
 
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index f41cc357..ad044575 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -69,7 +69,9 @@ func (s *state) plan(changes []schema.Change) error {
 	for _, c := range planned {
 		switch c := c.(type) {
 		case *schema.AddTable:
-			s.addTable(c)
+			if err := s.addTable(c); err != nil {
+				return err
+			}
 		case *schema.DropTable:
 			s.dropTable(c)
 		case *schema.ModifyTable:
@@ -124,7 +126,7 @@ func (s *state) topLevel(changes []schema.Change) []schema.Change {
 
 // addTable builds and appends the migrate.Change
 // for creating a table in a schema.
-func (s *state) addTable(add *schema.AddTable) {
+func (s *state) addTable(add *schema.AddTable) error {
 	b := Build("CREATE TABLE").Table(add.T)
 	if sqlx.Has(add.Extra, &schema.IfNotExists{}) {
 		b.P("IF NOT EXISTS")
@@ -155,13 +157,16 @@ func (s *state) addTable(add *schema.AddTable) {
 			s.fks(b, add.T.ForeignKeys...)
 		}
 	})
-	s.tableAttr(b, add.T.Attrs...)
+	if err := s.tableAttr(b, add.T.Attrs...); err != nil {
+		return err
+	}
 	s.append(&migrate.Change{
 		Cmd:     b.String(),
 		Source:  add,
 		Reverse: Build("DROP TABLE").Table(add.T).String(),
 		Comment: fmt.Sprintf("create %q table", add.T.Name),
 	})
+	return nil
 }
 
 // dropTable builds and appends the migrate.Change
@@ -223,7 +228,9 @@ func (s *state) modifyTable(modify *schema.ModifyTable) error {
 	}
 	for i := range changes {
 		if len(changes[i]) > 0 {
-			s.alterTable(modify.T, changes[i])
+			if err := s.alterTable(modify.T, changes[i]); err != nil {
+				return err
+			}
 		}
 	}
 	return nil
@@ -231,8 +238,9 @@ func (s *state) modifyTable(modify *schema.ModifyTable) error {
 
 // alterTable modifies the given table by executing on it a list of
 // changes in one SQL statement.
-func (s *state) alterTable(t *schema.Table, changes []schema.Change) {
+func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 	var (
+		errors     []string
 		b          = Build("ALTER TABLE").Table(t)
 		reversible = true
 		reverse    = b.Clone()
@@ -271,14 +279,23 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) {
 			b.P("DROP FOREIGN KEY").Ident(change.F.Symbol)
 			reversible = false
 		case *schema.AddAttr:
-			s.tableAttr(b, change.A)
+			if err := s.tableAttr(b, change.A); err != nil {
+				errors = append(errors, fmt.Sprintf("add attribute: %s", err.Error()))
+			}
 			// Unsupported reverse operation.
 			reversible = false
 		case *schema.ModifyAttr:
-			s.tableAttr(b, change.To)
-			s.tableAttr(reverse, change.From)
+			if err := s.tableAttr(b, change.To); err != nil {
+				errors = append(errors, fmt.Sprintf("modify attribute: %s", err.Error()))
+			}
+			if err := s.tableAttr(reverse, change.From); err != nil {
+				errors = append(errors, fmt.Sprintf("reverse modify attribute: %s", err.Error()))
+			}
 		}
 	})
+	if len(errors) > 0 {
+		return fmt.Errorf("alter table: %s", strings.Join(errors, ", "))
+	}
 	change := &migrate.Change{
 		Cmd: b.String(),
 		Source: &schema.ModifyTable{
@@ -291,6 +308,7 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) {
 		change.Reverse = reverse.String()
 	}
 	s.append(change)
+	return nil
 }
 
 func (s *state) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) {
@@ -319,8 +337,9 @@ func (s *state) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) {
 			b.P("ON UPDATE", a.A)
 		case *AutoIncrement:
 			b.P("AUTO_INCREMENT")
-			if a.V != 0 {
-				b.P(strconv.FormatInt(a.V, 10))
+			// Auto increment with value should be configured on table options.
+			if a.V != 0 && !sqlx.Has(t.Attrs, &AutoIncrement{}) {
+				t.Attrs = append(t.Attrs, a)
 			}
 		default:
 			s.attr(b, a)
@@ -375,20 +394,23 @@ func (s *state) fks(b *sqlx.Builder, fks ...*schema.ForeignKey) {
 
 // tableAttr writes the given table attribute to the SQL
 // statement builder when a table is created or altered.
-func (s *state) tableAttr(b *sqlx.Builder, attrs ...schema.Attr) {
+func (s *state) tableAttr(b *sqlx.Builder, attrs ...schema.Attr) error {
 	for _, a := range attrs {
 		switch a := a.(type) {
 		case *AutoIncrement:
-			b.P("AUTO_INCREMENT")
-			if a.V != 0 {
-				b.P(strconv.FormatInt(a.V, 10))
+			if a.V == 0 {
+				return fmt.Errorf("missing value for table option AUTO_INCREMENT")
 			}
+			b.P("AUTO_INCREMENT", strconv.FormatInt(a.V, 10))
 		case *schema.Charset:
 			b.P("CHARACTER SET", a.V)
-		default:
-			s.attr(b, a)
+		case *schema.Collation:
+			b.P("COLLATE", a.V)
+		case *schema.Comment:
+			b.P("COMMENT", quote(a.Text))
 		}
 	}
+	return nil
 }
 
 // collation returns the table collation from its attributes


Test patch:
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index e0389660..adb5b323 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -86,7 +86,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Len(t.PrimaryKey.Parts, 1)
 				require.True(t.PrimaryKey.Parts[0].C == t.Columns[0])
 				require.EqualValues([]*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 				}, t.Columns)
 			},
 		},
@@ -126,7 +126,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Len(t.PrimaryKey.Parts, 1)
 				require.True(t.PrimaryKey.Parts[0].C == t.Columns[0])
 				require.EqualValues([]*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 					{Name: "v57_tiny", Type: &schema.ColumnType{Raw: "tinyint(1)", Type: &schema.BoolType{T: "tinyint"}}},
 					{Name: "v57_tiny_unsigned", Type: &schema.ColumnType{Raw: "tinyint(4) unsigned", Type: &schema.IntegerType{T: "tinyint", Unsigned: true}}},
 					{Name: "v57_small", Type: &schema.ColumnType{Raw: "smallint(6)", Type: &schema.IntegerType{T: "smallint"}}},
@@ -179,7 +179,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Len(t.PrimaryKey.Parts, 1)
 				require.True(t.PrimaryKey.Parts[0].C == t.Columns[0])
 				require.EqualValues([]*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 					{Name: "tiny_int", Type: &schema.ColumnType{Raw: "tinyint(1)", Type: &schema.BoolType{T: "tinyint"}}},
 					{Name: "longtext", Type: &schema.ColumnType{Raw: "longtext", Type: &schema.StringType{T: "longtext"}}},
 					{Name: "jsonc", Type: &schema.ColumnType{Raw: "json", Type: &schema.JSONType{T: "json"}}},
@@ -482,7 +482,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "unique_index", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "BTREE"}}},
 				}
 				columns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 					{Name: "nickname", Type: &schema.ColumnType{Raw: "varchar(255)", Type: &schema.StringType{T: "varchar", Size: 255}}, Indexes: indexes[0:1], Attrs: []schema.Attr{&schema.Charset{V: "utf8mb4"}, &schema.Collation{V: "utf8mb4_0900_ai_ci"}}},
 					{Name: "oid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Indexes: indexes[2:]},
 					{Name: "uid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Indexes: indexes[2:]},
@@ -547,7 +547,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Symbol: "self_reference", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: t},
 				}
 				columns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}, ForeignKeys: fks[0:1]},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}, ForeignKeys: fks[0:1]},
 					{Name: "oid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, ForeignKeys: fks[0:1]},
 					{Name: "uid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, ForeignKeys: fks[1:2]},
 				}
@@ -590,7 +590,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Equal("users", t.Name)
 				require.Equal("public", t.Schema.Name)
 				columns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 					{Name: "c1", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 				}
 				require.EqualValues(columns, t.Columns)
@@ -727,7 +727,7 @@ func TestDriver_InspectSchema(t *testing.T) {
 					{Symbol: "spouse_id", Table: users, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: users},
 				}
 				userColumns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 					{Name: "spouse_id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}, Null: true}, ForeignKeys: userFKs},
 				}
 				userFKs[0].Columns = userColumns[1:]
@@ -740,7 +740,7 @@ func TestDriver_InspectSchema(t *testing.T) {
 					{Symbol: "owner_id", Table: pets, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: users, RefColumns: userColumns[:1]},
 				}
 				petsColumns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{A: "auto_increment"}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 					{Name: "owner_id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}, Null: true}, ForeignKeys: petsFKs},
 				}
 				petsFKs[0].Columns = petsColumns[1:]
diff --git a/sql/mysql/migrate_test.go b/sql/mysql/migrate_test.go
index 712fa5b6..600b0ac7 100644
--- a/sql/mysql/migrate_test.go
+++ b/sql/mysql/migrate_test.go
@@ -193,19 +193,65 @@ func TestPlanChanges(t *testing.T) {
 		},
 		{
 			changes: []schema.Change{
-				&schema.AddTable{
-					T: &schema.Table{
+				func() *schema.AddTable {
+					t := &schema.Table{
 						Name: "posts",
 						Columns: []*schema.Column{
-							{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}},
+							{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
 							{Name: "text", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Null: true}},
 						},
-					},
-				},
+					}
+					t.PrimaryKey = &schema.Index{Parts: []*schema.IndexPart{{C: t.Columns[0]}}}
+					return &schema.AddTable{T: 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`))", Reverse: "DROP TABLE `posts`"}},
+			},
+		},
+		{
+			changes: []schema.Change{
+				func() *schema.AddTable {
+					t := &schema.Table{
+						Name: "posts",
+						Columns: []*schema.Column{
+							{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{V: 100}}},
+							{Name: "text", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Null: true}},
+						},
+						Attrs: []schema.Attr{
+							&schema.Charset{V: "utf8mb4"},
+							&schema.Collation{V: "utf8mb4_bin"},
+							&schema.Comment{Text: "posts comment"},
+						},
+					}
+					t.PrimaryKey = &schema.Index{Parts: []*schema.IndexPart{{C: t.Columns[0]}}}
+					return &schema.AddTable{T: 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`)) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT \"posts comment\" AUTO_INCREMENT 100", Reverse: "DROP TABLE `posts`"}},
+			},
+		},
+		{
+			changes: []schema.Change{
+				func() *schema.AddTable {
+					t := &schema.Table{
+						Name: "posts",
+						Columns: []*schema.Column{
+							{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+							{Name: "text", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Null: true}},
+						},
+						Attrs: []schema.Attr{&AutoIncrement{V: 10}},
+					}
+					t.PrimaryKey = &schema.Index{Parts: []*schema.IndexPart{{C: t.Columns[0]}}}
+					return &schema.AddTable{T: t}
+				}(),
 			},
 			plan: &migrate.Plan{
 				Reversible: true,
-				Changes:    []*migrate.Change{{Cmd: "CREATE TABLE `posts` (`id` bigint NOT NULL, `text` text NULL)", Reverse: "DROP TABLE `posts`"}},
+				Changes:    []*migrate.Change{{Cmd: "CREATE TABLE `posts` (`id` bigint NOT NULL AUTO_INCREMENT, `text` text NULL, PRIMARY KEY (`id`)) AUTO_INCREMENT 10", Reverse: "DROP TABLE `posts`"}},
 			},
 		},
 		{