Repository: ariga/atlas
Pull request #563 (2022-02-10 21:14:40)

sql/mysql: support index types
Also, renamed `index.using` to `index.type`. 


Patch:
diff --git a/schema/schemaspec/schemahcl/opts.go b/schema/schemaspec/schemahcl/opts.go
index e778748f..96db0a96 100644
--- a/schema/schemaspec/schemahcl/opts.go
+++ b/schema/schemaspec/schemahcl/opts.go
@@ -45,13 +45,13 @@ func New(opts ...Option) *state {
 // option allows setting HASH or BTREE to the "using" attribute in
 // "index" block.
 //
-//	WithScopedEnums("table.index.using", "HASH", "BTREE")
+//	WithScopedEnums("table.index.type", "HASH", "BTREE")
 //
 //	table "t" {
 //		...
 //		index "i" {
-//			using = HASH	// Allowed.
-//			using = INVALID	// Not Allowed.
+//			type = HASH     // Allowed.
+//			type = INVALID  // Not Allowed.
 //		}
 //	}
 //
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index 35ca90de..b140727a 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -243,7 +243,7 @@ func (*diff) autoIncChange(from, to []schema.Attr) schema.Change {
 // indexType returns the index type from its attribute.
 // The default type is BTREE if no type was specified.
 func indexType(attr []schema.Attr) *IndexType {
-	t := &IndexType{T: "BTREE"}
+	t := &IndexType{T: IndexTypeBTree}
 	if sqlx.Has(attr, t) {
 		t.T = strings.ToUpper(t.T)
 	}
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index ce7ed624..90d33006 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -188,6 +188,11 @@ const (
 
 // Additional common constants in MySQL.
 const (
+	IndexTypeBTree    = "BTREE"
+	IndexTypeHash     = "HASH"
+	IndexTypeFullText = "FULLTEXT"
+	IndexTypeSpatial  = "SPATIAL"
+
 	currentTS     = "current_timestamp"
 	defaultGen    = "default_generated"
 	autoIncrement = "auto_increment"
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 605dce4b..f90da736 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -28,6 +28,9 @@ func (i *inspect) InspectRealm(ctx context.Context, opts *schema.InspectRealmOpt
 		return nil, err
 	}
 	r := schema.NewRealm(schemas...).SetCharset(i.charset).SetCollation(i.collate)
+	if len(schemas) == 0 {
+		return r, nil
+	}
 	if err := i.inspectTables(ctx, r, nil); err != nil {
 		return nil, err
 	}
@@ -772,7 +775,7 @@ type (
 	// IndexType represents an index type.
 	IndexType struct {
 		schema.Attr
-		T string // BTREE, FULLTEXT, HASH, RTREE
+		T string // BTREE, HASH, FULLTEXT, SPATIAL, RTREE
 	}
 
 	// BitType represents a bit type.
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 26342cc9..4377edff 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -213,20 +213,14 @@ func (s *state) addTable(add *schema.AddTable) error {
 		})
 		if pk := add.T.PrimaryKey; pk != nil {
 			b.Comma().P("PRIMARY KEY")
-			s.indexParts(b, pk.Parts)
-			s.attr(b, pk.Attrs...)
+			indexParts(b, pk.Parts)
 		}
 		if len(add.T.Indexes) > 0 {
 			b.Comma()
 		}
 		b.MapComma(add.T.Indexes, func(i int, b *sqlx.Builder) {
 			idx := add.T.Indexes[i]
-			if idx.Unique {
-				b.P("UNIQUE")
-			}
-			b.P("INDEX").Ident(idx.Name)
-			s.indexParts(b, idx.Parts)
-			s.attr(b, idx.Attrs...)
+			index(b, idx)
 		})
 		if len(add.T.ForeignKeys) > 0 {
 			b.Comma()
@@ -352,22 +346,12 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 			reversible = false
 		case *schema.AddIndex:
 			b.P("ADD")
-			if change.I.Unique {
-				b.P("UNIQUE")
-			}
-			b.P("INDEX").Ident(change.I.Name)
-			s.indexParts(b, change.I.Parts)
-			s.attr(b, change.I.Attrs...)
+			index(b, change.I)
 			reverse.Comma().P("DROP INDEX").Ident(change.I.Name)
 		case *schema.DropIndex:
 			b.P("DROP INDEX").Ident(change.I.Name)
 			reverse.Comma().P("ADD")
-			if change.I.Unique {
-				reverse.P("UNIQUE")
-			}
-			reverse.P("INDEX").Ident(change.I.Name)
-			s.indexParts(reverse, change.I.Parts)
-			s.attr(reverse, change.I.Attrs...)
+			index(reverse, change.I)
 			reversible = true
 		case *schema.AddForeignKey:
 			b.P("ADD")
@@ -498,7 +482,29 @@ func (s *state) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) error
 	return nil
 }
 
-func (s *state) indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
+func index(b *sqlx.Builder, idx *schema.Index) {
+	var t IndexType
+	if sqlx.Has(idx.Attrs, &t) {
+		t.T = strings.ToUpper(t.T)
+	}
+	switch {
+	case idx.Unique:
+		b.P("UNIQUE")
+	case t.T == IndexTypeFullText || t.T == IndexTypeSpatial:
+		b.P(t.T)
+	}
+	b.P("INDEX").Ident(idx.Name)
+	// Skip BTREE as it is the default type.
+	if t.T == IndexTypeHash {
+		b.P("USING", t.T)
+	}
+	indexParts(b, idx.Parts)
+	if c := (schema.Comment{}); sqlx.Has(idx.Attrs, &c) {
+		b.P("COMMENT", quote(c.Text))
+	}
+}
+
+func indexParts(b *sqlx.Builder, parts []*schema.IndexPart) {
 	b.Wrap(func(b *sqlx.Builder) {
 		b.MapComma(parts, func(i int, b *sqlx.Builder) {
 			switch part := parts[i]; {
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index 4e594245..bcdafacc 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -3,6 +3,7 @@ package mysql
 import (
 	"fmt"
 	"reflect"
+	"strings"
 
 	"ariga.io/atlas/schema/schemaspec"
 	"ariga.io/atlas/schema/schemaspec/schemahcl"
@@ -63,7 +64,10 @@ func MarshalSpec(v interface{}, marshaler schemaspec.Marshaler) ([]byte, error)
 }
 
 var (
-	hclState = schemahcl.New(schemahcl.WithTypes(TypeRegistry.Specs()))
+	hclState = schemahcl.New(
+		schemahcl.WithTypes(TypeRegistry.Specs()),
+		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeFullText, IndexTypeSpatial),
+	)
 	// UnmarshalHCL unmarshals an Atlas HCL DDL document into v.
 	UnmarshalHCL = schemaspec.UnmarshalerFunc(func(bytes []byte, i interface{}) error {
 		return UnmarshalSpec(bytes, hclState, i)
@@ -78,7 +82,7 @@ var (
 // ForeignKeySpecs into ForeignKeys, as the target tables do not necessarily exist in the schema
 // at this point. Instead, the linking is done by the convertSchema function.
 func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, error) {
-	t, err := specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, specutil.Index, convertCheck)
+	t, err := specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, convertIndex, convertCheck)
 	if err != nil {
 		return nil, err
 	}
@@ -88,6 +92,22 @@ func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, er
 	return t, err
 }
 
+// convertIndex converts a sqlspec.Index into a schema.Index.
+func convertIndex(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, error) {
+	idx, err := specutil.Index(spec, parent)
+	if err != nil {
+		return nil, err
+	}
+	if attr, ok := spec.Attr("type"); ok {
+		t, err := attr.String()
+		if err != nil {
+			return nil, err
+		}
+		idx.Attrs = append(idx.Attrs, &IndexType{T: t})
+	}
+	return idx, nil
+}
+
 // convertCheck converts a sqlspec.Check into a schema.Check.
 func convertCheck(spec *sqlspec.Check) (*schema.Check, error) {
 	c, err := specutil.Check(spec)
@@ -149,7 +169,7 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 		t,
 		columnSpec,
 		specutil.FromPrimaryKey,
-		specutil.FromIndex,
+		indexSpec,
 		specutil.FromForeignKey,
 		checkSpec,
 	)
@@ -165,6 +185,17 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 	return ts, nil
 }
 
+func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
+	spec, err := specutil.FromIndex(idx)
+	if err != nil {
+		return nil, err
+	}
+	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) {
+		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
+	}
+	return spec, nil
+}
+
 // columnSpec converts from a concrete MySQL schema.Column into a sqlspec.Column.
 func columnSpec(c *schema.Column, t *schema.Table) (*sqlspec.Column, error) {
 	col, err := specutil.FromColumn(c, columnTypeSpec)
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 4ae199d8..8e37d9d2 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -641,7 +641,7 @@ func (s *state) partAttrs(b *sqlx.Builder, p *schema.IndexPart) {
 func (s *state) index(b *sqlx.Builder, idx *schema.Index) {
 	// Avoid appending the default method.
 	if t := (IndexType{}); sqlx.Has(idx.Attrs, &t) && strings.ToUpper(t.T) != IndexTypeBTree {
-		b.P("USING").P(t.T)
+		b.P("USING", t.T)
 	}
 	s.indexParts(b, idx.Parts)
 	if p := (IndexPredicate{}); sqlx.Has(idx.Attrs, &p) {
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index 5c68702e..43c589e6 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -93,7 +93,7 @@ func MarshalSpec(v interface{}, marshaler schemaspec.Marshaler) ([]byte, error)
 var (
 	hclState = schemahcl.New(
 		schemahcl.WithTypes(TypeRegistry.Specs()),
-		schemahcl.WithScopedEnums("table.index.using", IndexTypeBTree, IndexTypeHash, IndexTypeGIN, IndexTypeGiST),
+		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeHash, IndexTypeGIN, IndexTypeGiST),
 	)
 	// UnmarshalHCL unmarshals an Atlas HCL DDL document into v.
 	UnmarshalHCL = schemaspec.UnmarshalerFunc(func(bytes []byte, i interface{}) error {
@@ -205,7 +205,7 @@ func convertIndex(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, err
 	if err != nil {
 		return nil, err
 	}
-	if attr, ok := spec.Attr("using"); ok {
+	if attr, ok := spec.Attr("type"); ok {
 		t, err := attr.String()
 		if err != nil {
 			return nil, err
@@ -331,7 +331,7 @@ func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 		return nil, err
 	}
 	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) {
-		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("using", strings.ToUpper(i.T)))
+		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
 	}
 	return spec, nil
 }


Test patch:
diff --git a/internal/integration/testdata/mysql/index-type.txt b/internal/integration/testdata/mysql/index-type.txt
new file mode 100644
index 00000000..c18e6bf0
--- /dev/null
+++ b/internal/integration/testdata/mysql/index-type.txt
@@ -0,0 +1,44 @@
+apply 1.hcl
+cmpshow users 1.sql
+
+# Drop an index.
+apply 2.hcl
+cmpshow users 2.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "text" {
+    null = false
+    type = text
+  }
+  index "users_text" {
+    type = FULLTEXT
+    columns = [column.text]
+  }
+}
+
+-- 1.sql --
+CREATE TABLE `users` (
+  `text` text NOT NULL,
+  FULLTEXT KEY `users_text` (`text`)
+)
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "text" {
+    null = false
+    type = text
+  }
+}
+
+-- 2.sql --
+CREATE TABLE `users` (
+  `text` text NOT NULL
+)
+
diff --git a/internal/integration/testdata/postgres/index-type.txt b/internal/integration/testdata/postgres/index-type.txt
index 838e139a..ff036122 100644
--- a/internal/integration/testdata/postgres/index-type.txt
+++ b/internal/integration/testdata/postgres/index-type.txt
@@ -19,11 +19,11 @@ table "users" {
     type = jsonb
   }
   index "users_name" {
-    using = HASH
+    type = HASH
     columns = [column.name]
   }
   index "users_data" {
-    using = GIN
+    type = GIN
     columns = [column.data]
   }
 }
@@ -56,7 +56,7 @@ table "users" {
     # Index without "using" defaults to BTREE.
   }
   index "users_data" {
-    using = BTREE
+    type = BTREE
     columns = [column.data]
   }
 }
diff --git a/sql/mysql/migrate_test.go b/sql/mysql/migrate_test.go
index a33778dd..b24db365 100644
--- a/sql/mysql/migrate_test.go
+++ b/sql/mysql/migrate_test.go
@@ -441,6 +441,7 @@ func TestPlanChanges(t *testing.T) {
 									},
 									Attrs: []schema.Attr{
 										&schema.Comment{Text: "comment"},
+										&IndexType{T: IndexTypeHash},
 									},
 								},
 							},
@@ -463,7 +464,7 @@ func TestPlanChanges(t *testing.T) {
 				Reversible: true,
 				Changes: []*migrate.Change{
 					{
-						Cmd:     "ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL, ADD INDEX `id_key` (`id`) COMMENT \"comment\", ADD CONSTRAINT `id_nonzero` CHECK (id > 0) ENFORCED, AUTO_INCREMENT 1000",
+						Cmd:     "ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL, ADD INDEX `id_key` USING HASH (`id`) COMMENT \"comment\", ADD CONSTRAINT `id_nonzero` CHECK (id > 0) ENFORCED, AUTO_INCREMENT 1000",
 						Reverse: "ALTER TABLE `users` DROP COLUMN `name`, DROP INDEX `id_key`, DROP CONSTRAINT `id_nonzero`, AUTO_INCREMENT 1",
 					},
 				},
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index 2773e509..d31538e7 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -50,7 +50,7 @@ table "table" {
 		columns = [table.table.column.col]
 	}
 	index "index" {
-		using = HASH
+		type = HASH
 		unique = true
 		columns = [
 			table.table.column.col,
@@ -265,7 +265,7 @@ table "t" {
 		type = int
 	}
 	index "i" {
-		using = %s
+		type = %s
 		columns = [column.c]
 	}
 }