Repository: ariga/atlas
Pull request #903 (2022-07-02 14:24:29)

sql/mysql: unescape mysql expression strings
Closed https://github.com/ariga/atlas/issues/803.


Patch:
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index f9213403..eea2b448 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -257,8 +257,11 @@ func (i *inspect) addColumn(s *schema.Schema, rows *sql.Rows) error {
 	if attr.onUpdate != "" {
 		c.Attrs = append(c.Attrs, &OnUpdate{A: attr.onUpdate})
 	}
-	if expr.String != "" {
-		c.SetGeneratedExpr(&schema.GeneratedExpr{Expr: expr.String, Type: attr.generatedType})
+	if x := expr.String; x != "" {
+		if !i.mariadb() {
+			x = unescape(x)
+		}
+		c.SetGeneratedExpr(&schema.GeneratedExpr{Expr: x, Type: attr.generatedType})
 	}
 	if defaults.Valid {
 		if i.mariadb() {
@@ -555,7 +558,7 @@ func (i *inspect) myDefaultExpr(c *schema.Column, x string, attr *extraAttr) sch
 		if _, ok := c.Type.Type.(*schema.TimeType); ok && reCurrTimestamp.MatchString(x) {
 			return &schema.RawExpr{X: x}
 		}
-		return &schema.RawExpr{X: sqlx.MayWrap(x)}
+		return &schema.RawExpr{X: sqlx.MayWrap(unescape(x))}
 	}
 	switch c.Type.Type.(type) {
 	case *schema.BinaryType:


Test patch:
diff --git a/internal/integration/testdata/mysql/column-default-expr.txt b/internal/integration/testdata/mysql/column-default-expr.txt
new file mode 100644
index 00000000..815bbac0
--- /dev/null
+++ b/internal/integration/testdata/mysql/column-default-expr.txt
@@ -0,0 +1,46 @@
+# Run tests only on MySQL 8.
+only mysql8
+
+# Apply schema "1.hcl" on fresh database.
+apply 1.hcl
+
+# Compare the result of "SHOW TABLE users" with the content of a file named '1.sql'.
+cmpshow users 1.sql
+cmphcl 1.hcl
+
+-- 1.hcl --
+table "users" {
+  schema = schema.script_column_default_expr
+  column "a" {
+    null    = false
+    type    = varchar(255)
+    default = ""
+  }
+  column "b" {
+    null    = false
+    type    = varchar(255)
+    default = sql("(concat(_utf8mb4'a',`a`,_utf8mb4'\\'s',_utf8mb4'b'))")
+  }
+  column "c" {
+    null    = false
+    type    = varchar(255)
+    default = "a'b"
+  }
+  column "d" {
+    null    = false
+    type    = varchar(255)
+    default = sql("(_utf8mb4'a\\'b')")
+  }
+}
+schema "script_column_default_expr" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
+}
+
+-- 1.sql --
+CREATE TABLE `users` (
+  `a` varchar(255) NOT NULL DEFAULT '',
+  `b` varchar(255) NOT NULL DEFAULT (concat(_utf8mb4'a',`a`,_utf8mb4'\'s',_utf8mb4'b')),
+  `c` varchar(255) NOT NULL DEFAULT 'a''b',
+  `d` varchar(255) NOT NULL DEFAULT (_utf8mb4'a\'b')
+)
\ No newline at end of file
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 15d119d3..d3a37d23 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -302,16 +302,17 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
-| table_name | column_name | column_type   | column_comment | is_nullable | column_key | column_default | extra | character_set_name | collation_name | generation_expression     |
-+------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
-| users      | c1          | char(20)      |                | NO          |            | char           |       | NULL               | NULL           | NULL                      |
-| users      | c2          | varchar(30)   |                | NO          |            | NULL           |       | NULL               | NULL           | NULL                      |
-| users      | c3          | tinytext      |                | NO          |            | NULL           |       | NULL               | NULL           | NULL                      |
-| users      | c4          | mediumtext    |                | NO          |            | NULL           |       | NULL               | NULL           | NULL                      |
-| users      | c5          | text          |                | NO          |            | NULL           |       | NULL               | NULL           | NULL                      |
-| users      | c6          | longtext      |                | NO          |            | NULL           |       | NULL               | NULL           | NULL                      |
-+------------+-------------+---------------+----------------+-------------+------------+----------------+-------+--------------------+----------------+---------------------------+
++------------+-------------+---------------+----------------+-------------+------------+--------------------------------------------+-------------------+--------------------+----------------+---------------------------+
+| table_name | column_name | column_type   | column_comment | is_nullable | column_key | column_default                             | extra             | character_set_name | collation_name | generation_expression     |
++------------+-------------+---------------+----------------+-------------+------------+--------------------------------------------+-------------------+--------------------+----------------+---------------------------+
+| users      | c1          | char(20)      |                | NO          |            | char                                       |                   | NULL               | NULL           | NULL                      |
+| users      | c2          | varchar(30)   |                | NO          |            | NULL                                       |                   | NULL               | NULL           | NULL                      |
+| users      | c3          | tinytext      |                | NO          |            | NULL                                       |                   | NULL               | NULL           | NULL                      |
+| users      | c4          | mediumtext    |                | NO          |            | NULL                                       |                   | NULL               | NULL           | NULL                      |
+| users      | c5          | text          |                | NO          |            | NULL                                       |                   | NULL               | NULL           | NULL                      |
+| users      | c6          | longtext      |                | NO          |            | NULL                                       |                   | NULL               | NULL           | NULL                      |
+| users      | c7          | varchar(20)   |                | NO          |            | concat(_latin1\'Hello \',` + "`name`" + `) | DEFAULT_GENERATED | NULL               | NULL           | NULL                      |
++------------+-------------+---------------+----------------+-------------+------------+--------------------------------------------+-------------------+--------------------+----------------+---------------------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -326,6 +327,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c4", Type: &schema.ColumnType{Raw: "mediumtext", Type: &schema.StringType{T: "mediumtext"}}},
 					{Name: "c5", Type: &schema.ColumnType{Raw: "text", Type: &schema.StringType{T: "text"}}},
 					{Name: "c6", Type: &schema.ColumnType{Raw: "longtext", Type: &schema.StringType{T: "longtext"}}},
+					{Name: "c7", Type: &schema.ColumnType{Raw: "varchar(20)", Type: &schema.StringType{T: "varchar", Size: 20}}, Default: &schema.RawExpr{X: "(concat(_latin1'Hello ',`name`))"}},
 				}, t.Columns)
 			},
 		},
@@ -463,13 +465,14 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.ExpectQuery(queryColumns).
 					WithArgs("public", "users").
 					WillReturnRows(sqltest.Rows(`
-+------------+-------------+-------------+----------------+-------------+------------+----------------+-------------------+--------------------+----------------+-----------------------+
-| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA             | CHARACTER_SET_NAME | COLLATION_NAME | GENERATION_EXPRESSION |
-+------------+-------------+-------------+----------------+-------------+------------+----------------+-------------------+--------------------+----------------+-----------------------+
-| users      | c1          | int         |                | NO          |            | NULL           |                   | NULL               | NULL           |                       |
-| users      | c2          | int         |                | NO          |            | NULL           | VIRTUAL GENERATED | NULL               | NULL           | ` + "(`c1` * `c1`)" + `   |
-| users      | c3          | int         |                | NO          |            | NULL           | STORED GENERATED  | NULL               | NULL           | ` + "(`c1` + `c2`)" + `   |
-+------------+-------------+-------------+----------------+-------------+------------+----------------+-------------------+--------------------+----------------+-----------------------+
++------------+-------------+-------------+----------------+-------------+------------+----------------+-------------------+--------------------+----------------+--------------------------------------+
+| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA             | CHARACTER_SET_NAME | COLLATION_NAME | GENERATION_EXPRESSION                |
++------------+-------------+-------------+----------------+-------------+------------+----------------+-------------------+--------------------+----------------+--------------------------------------+
+| users      | c1          | int         |                | NO          |            | NULL           |                   | NULL               | NULL           |                                      |
+| users      | c2          | int         |                | NO          |            | NULL           | VIRTUAL GENERATED | NULL               | NULL           | ` + "(`c1` * `c1`)" + `              |
+| users      | c3          | int         |                | NO          |            | NULL           | STORED GENERATED  | NULL               | NULL           | ` + "(`c1` + `c2`)" + `              |
+| users      | c4          | varchar(20) |                | NO          |            | NULL           | STORED GENERATED  | NULL               | NULL           | concat(_latin1\'\\\'\',_latin1\'"\') |
++------------+-------------+-------------+----------------+-------------+------------+----------------+-------------------+--------------------+----------------+--------------------------------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -481,6 +484,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c1", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 					{Name: "c2", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&schema.GeneratedExpr{Expr: "(`c1` * `c1`)", Type: "VIRTUAL"}}},
 					{Name: "c3", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&schema.GeneratedExpr{Expr: "(`c1` + `c2`)", Type: "STORED"}}},
+					{Name: "c4", Type: &schema.ColumnType{Raw: "varchar(20)", Type: &schema.StringType{T: "varchar", Size: 20}}, Attrs: []schema.Attr{&schema.GeneratedExpr{Expr: "concat(_latin1'\\'',_latin1'\"')", Type: "STORED"}}},
 				}, t.Columns)
 			},
 		},