Repository: ariga/atlas
Pull request #642 (2022-03-09 20:39:43)

sql/mysql: add support for index prefixes
See: https://dev.mysql.com/doc/refman/8.0/en/column-indexes.html\#column-indexes-prefix


Patch:
diff --git a/sql/internal/specutil/convert.go b/sql/internal/specutil/convert.go
index fcb5ebdf..4c616aa9 100644
--- a/sql/internal/specutil/convert.go
+++ b/sql/internal/specutil/convert.go
@@ -151,8 +151,9 @@ func Column(spec *sqlspec.Column, conv ConvertTypeFunc) (*schema.Column, error)
 	return out, err
 }
 
-// Index converts a sqlspec.Index to a schema.Index.
-func Index(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, error) {
+// Index converts a sqlspec.Index to a schema.Index. The optional arguments allow
+// passing functions for mutating the created index-part (e.g. add attributes).
+func Index(spec *sqlspec.Index, parent *schema.Table, partFns ...func(*sqlspec.IndexPart, *schema.IndexPart) error) (*schema.Index, error) {
 	parts := make([]*schema.IndexPart, 0, len(spec.Columns)+len(spec.Parts))
 	switch n, m := len(spec.Columns), len(spec.Parts); {
 	case n == 0 && m == 0:
@@ -187,6 +188,11 @@ func Index(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, error) {
 				}
 				part.C = c
 			}
+			for _, f := range partFns {
+				if err := f(p, part); err != nil {
+					return nil, err
+				}
+			}
 			parts = append(parts, part)
 		}
 	}
@@ -418,7 +424,7 @@ func normalizeQuotes(s string) (string, error) {
 }
 
 // FromIndex converts schema.Index to sqlspec.Index.
-func FromIndex(idx *schema.Index) (*sqlspec.Index, error) {
+func FromIndex(idx *schema.Index, partFns ...func(*schema.IndexPart, *sqlspec.IndexPart)) (*sqlspec.Index, error) {
 	spec := &sqlspec.Index{Name: idx.Name, Unique: idx.Unique}
 	convertCommentFromSchema(idx.Attrs, &spec.Extra.Attrs)
 	if parts, ok := columnsOnly(idx); ok {
@@ -442,6 +448,9 @@ func FromIndex(idx *schema.Index) (*sqlspec.Index, error) {
 			}
 			part.Expr = x.X
 		}
+		for _, f := range partFns {
+			f(p, part)
+		}
 		spec.Parts[i] = part
 	}
 	return spec, nil
diff --git a/sql/internal/specutil/spec.go b/sql/internal/specutil/spec.go
index a6c21c02..0018e4f4 100644
--- a/sql/internal/specutil/spec.go
+++ b/sql/internal/specutil/spec.go
@@ -25,7 +25,12 @@ func BoolAttr(k string, v bool) *schemaspec.Attr {
 	}
 }
 
-// Int64Attr is a helper method for constructing *schemaspec.Attr of type int64.
+// IntAttr is a helper method for constructing *schemaspec.Attr with the numeric value of v.
+func IntAttr(k string, v int) *schemaspec.Attr {
+	return Int64Attr(k, int64(v))
+}
+
+// Int64Attr is a helper method for constructing *schemaspec.Attr with the numeric value of v.
 func Int64Attr(k string, v int64) *schemaspec.Attr {
 	return &schemaspec.Attr{
 		K: k,
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index a98e197d..e94e075e 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -1,6 +1,7 @@
 package mysql
 
 import (
+	"errors"
 	"fmt"
 	"reflect"
 	"strings"
@@ -105,7 +106,7 @@ func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, er
 
 // 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)
+	idx, err := specutil.Index(spec, parent, convertPart)
 	if err != nil {
 		return nil, err
 	}
@@ -114,11 +115,25 @@ func convertIndex(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, err
 		if err != nil {
 			return nil, err
 		}
-		idx.Attrs = append(idx.Attrs, &IndexType{T: t})
+		idx.AddAttrs(&IndexType{T: t})
 	}
 	return idx, nil
 }
 
+func convertPart(spec *sqlspec.IndexPart, part *schema.IndexPart) error {
+	if attr, ok := spec.Attr("prefix"); ok {
+		if part.X != nil {
+			return errors.New("attribute 'on.prefix' cannot be used in functional part")
+		}
+		p, err := attr.Int()
+		if err != nil {
+			return err
+		}
+		part.AddAttrs(&SubPart{Len: p})
+	}
+	return nil
+}
+
 // convertCheck converts a sqlspec.Check into a schema.Check.
 func convertCheck(spec *sqlspec.Check) (*schema.Check, error) {
 	c, err := specutil.Check(spec)
@@ -206,7 +221,7 @@ func tableSpec(t *schema.Table) (*sqlspec.Table, error) {
 }
 
 func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
-	spec, err := specutil.FromIndex(idx)
+	spec, err := specutil.FromIndex(idx, partAttr)
 	if err != nil {
 		return nil, err
 	}
@@ -217,6 +232,12 @@ func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 	return spec, nil
 }
 
+func partAttr(part *schema.IndexPart, spec *sqlspec.IndexPart) {
+	if p := (SubPart{}); sqlx.Has(part.Attrs, &p) && p.Len > 0 {
+		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.IntAttr("prefix", p.Len))
+	}
+}
+
 // 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)


Test patch:
diff --git a/internal/integration/testdata/mysql/index-prefix.txt b/internal/integration/testdata/mysql/index-prefix.txt
new file mode 100644
index 00000000..056eb235
--- /dev/null
+++ b/internal/integration/testdata/mysql/index-prefix.txt
@@ -0,0 +1,75 @@
+apply 1.hcl
+cmpshow users 1.sql
+
+# Change prefix.
+apply 2.hcl
+cmpshow users 2.sql
+
+# Drop prefix.
+apply 3.hcl
+cmpshow users 3.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    type = varchar(255)
+  }
+  index "user_name" {
+    on {
+      column = column.name
+      prefix = 64
+    }
+  }
+}
+
+-- 1.sql --
+CREATE TABLE `users` (
+  `name` varchar(255) NOT NULL,
+  KEY `user_name` (`name`(64))
+)
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    type = varchar(255)
+  }
+  index "user_name" {
+    on {
+      column = column.name
+      prefix = 128
+    }
+  }
+}
+
+-- 2.sql --
+CREATE TABLE `users` (
+  `name` varchar(255) NOT NULL,
+  KEY `user_name` (`name`(128))
+)
+
+-- 3.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "name" {
+    type = varchar(128)
+  }
+  index "user_name" {
+    on {
+      column = column.name
+    }
+  }
+}
+
+-- 3.sql --
+CREATE TABLE `users` (
+  `name` varchar(128) NOT NULL,
+  KEY `user_name` (`name`)
+)
\ No newline at end of file
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index 3df3add7..b2fba9cd 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -554,6 +554,7 @@ table "users" {
 		on {
 			column = table.users.column.name
 			desc = true
+			prefix = 10
 		}
 		on {
 			expr = "lower(name)"
@@ -572,7 +573,7 @@ table "users" {
 				AddIndexes(
 					schema.NewIndex("idx").
 						AddParts(
-							schema.NewColumnPart(c).SetDesc(true),
+							schema.NewColumnPart(c).SetDesc(true).AddAttrs(&SubPart{Len: 10}),
 							schema.NewExprPart(&schema.RawExpr{X: "lower(name)"}),
 						),
 				),
@@ -590,7 +591,7 @@ func TestMarshalSpec_IndexParts(t *testing.T) {
 				AddIndexes(
 					schema.NewIndex("idx").
 						AddParts(
-							schema.NewColumnPart(c).SetDesc(true),
+							schema.NewColumnPart(c).SetDesc(true).AddAttrs(&SubPart{Len: 10}),
 							schema.NewExprPart(&schema.RawExpr{X: "lower(name)"}),
 						),
 				),
@@ -607,6 +608,7 @@ func TestMarshalSpec_IndexParts(t *testing.T) {
     on {
       desc   = true
       column = column.name
+      prefix = 10
     }
     on {
       expr = "lower(name)"