Repository: ariga/atlas
Pull request #287 (2021-12-06 20:22:05)

sql/mysql: normalize longtext columns to json on mariadb


Patch:
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index fba1920f..58f512bd 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -516,11 +516,23 @@ func (d *Driver) checks(ctx context.Context, t *schema.Table) error {
 		if err := rows.Scan(&name, &clause, &enforced); err != nil {
 			return fmt.Errorf("mysql: %w", err)
 		}
-		t.Attrs = append(t.Attrs, &Check{
+		check := &Check{
 			Name:     name.String,
 			Clause:   unescape(clause.String),
 			Enforced: clause.String != "NO",
-		})
+		}
+		t.Attrs = append(t.Attrs, check)
+		// In MariaDB, JSON is an alias to LONGTEXT, and the JSON_VALID
+		// CHECK constraint is automatically created for the column for
+		// versions >= 10.4.3. However, we expect tools like Atlas and
+		// Ent to manually add this CHECK for older versions of MariaDB.
+		if d.mariadb() {
+			c, ok := t.Column(check.Name)
+			if ok && c.Type.Raw == tLongText && check.Clause == fmt.Sprintf("json_valid(`%s`)", c.Name) {
+				c.Type.Raw = tJSON
+				c.Type.Type = &schema.JSONType{T: tJSON}
+			}
+		}
 
 	}
 	return rows.Err()
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 3f58d564..5f014411 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -199,6 +199,13 @@ func (m *migrate) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) {
 		}
 		b.P("DEFAULT", v)
 	}
+	// Add manually the JSON_VALID constraint for older
+	// versions < 10.4.3. See Driver.checks for full info.
+	if _, ok := c.Type.Type.(*schema.JSONType); ok && m.mariadb() && m.compareV("10.4.3") == -1 && !sqlx.Has(c.Attrs, &Check{}) {
+		b.P("CHECK").Wrap(func(b *sqlx.Builder) {
+			b.WriteString(fmt.Sprintf("json_valid(`%s`)", c.Name))
+		})
+	}
 	for _, a := range c.Attrs {
 		switch a := a.(type) {
 		case *schema.Collation:


Test patch:
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index d9d4460d..5d084446 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -452,11 +452,6 @@ func TestMySQL_ForeignKey(t *testing.T) {
 
 func TestMySQL_Ent(t *testing.T) {
 	myRun(t, func(t *myTest) {
-		// Temporary skip Maria until
-		// JSON will be supported.
-		if t.mariadb() {
-			t.Skip()
-		}
 		testEntIntegration(t, dialect.MySQL, t.db)
 	})
 }
@@ -886,7 +881,7 @@ create table atlas_types_sanity
 					{
 						Name: "tJSON",
 						Type: func() *schema.ColumnType {
-							if t.mariadb() {
+							if t.version == "Maria102" || t.version == "Maria103" {
 								return &schema.ColumnType{Type: &schema.StringType{T: "longtext"},
 									Raw: "longtext", Null: true}
 							}
diff --git a/sql/mysql/driver_test.go b/sql/mysql/driver_test.go
index 98d26f27..0fad1979 100644
--- a/sql/mysql/driver_test.go
+++ b/sql/mysql/driver_test.go
@@ -156,6 +156,8 @@ func TestDriver_InspectTable(t *testing.T) {
 +---------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
 | id            | bigint(20)                   |                      | NO          | PRI        | NULL           | auto_increment | NULL               | NULL           |
 | tiny_int      | tinyint(1)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| longtext      | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
+| jsonc         | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
 +---------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
 `))
 				m.ExpectQuery(sqltest.Escape(indexesQuery)).
@@ -163,7 +165,13 @@ func TestDriver_InspectTable(t *testing.T) {
 				m.noFKs()
 				m.ExpectQuery(sqltest.Escape(marChecksQuery)).
 					WithArgs("public", "users").
-					WillReturnRows(sqlmock.NewRows([]string{"CONSTRAINT_NAME", "CHECK_CLAUSE", "ENFORCED"}))
+					WillReturnRows(sqltest.Rows(`
++-------------------+-------------------------------------------+------------+
+| CONSTRAINT_NAME   | CHECK_CLAUSE                              |  ENFORCED  |
++-------------------+-------------------------------------------+------------+
+| jsonc             | json_valid(` + "`jsonc`" + `)             |  YES       |
++-------------------+-------------------------------------------+------------+
+`))
 			},
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
@@ -173,6 +181,8 @@ func TestDriver_InspectTable(t *testing.T) {
 				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: "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"}}},
 				}, t.Columns)
 			},
 		},