Repository: ariga/atlas
Pull request #1410 (2023-01-19 11:02:27)

sql/mysql: respect the lower_case_table_names sysvar on table search


Patch:
diff --git a/sql/internal/sqlx/diff.go b/sql/internal/sqlx/diff.go
index 8281f552..b53d9362 100644
--- a/sql/internal/sqlx/diff.go
+++ b/sql/internal/sqlx/diff.go
@@ -66,6 +66,12 @@ type (
 	Normalizer interface {
 		Normalize(from, to *schema.Table) error
 	}
+
+	// TableFinder wraps the FindTable method, providing more
+	// control to the DiffDriver on how tables are matched.
+	TableFinder interface {
+		FindTable(*schema.Schema, string) (*schema.Table, error)
+	}
 )
 
 // RealmDiff implements the schema.Differ for Realm objects and returns a list of changes
@@ -115,26 +121,31 @@ func (d *Diff) SchemaDiff(from, to *schema.Schema) ([]schema.Change, error) {
 
 	// Drop or modify tables.
 	for _, t1 := range from.Tables {
-		t2, ok := to.Table(t1.Name)
-		if !ok {
+		switch t2, err := d.findTable(to, t1.Name); {
+		case schema.IsNotExistError(err):
 			changes = append(changes, &schema.DropTable{T: t1})
-			continue
-		}
-		change, err := d.TableDiff(t1, t2)
-		if err != nil {
+		case err != nil:
 			return nil, err
-		}
-		if len(change) > 0 {
-			changes = append(changes, &schema.ModifyTable{
-				T:       t2,
-				Changes: change,
-			})
+		default:
+			change, err := d.tableDiff(t1, t2)
+			if err != nil {
+				return nil, err
+			}
+			if len(change) > 0 {
+				changes = append(changes, &schema.ModifyTable{
+					T:       t2,
+					Changes: change,
+				})
+			}
 		}
 	}
 	// Add tables.
 	for _, t1 := range to.Tables {
-		if _, ok := from.Table(t1.Name); !ok {
+		switch _, err := d.findTable(from, t1.Name); {
+		case schema.IsNotExistError(err):
 			changes = append(changes, &schema.AddTable{T: t1})
+		case err != nil:
+			return nil, err
 		}
 	}
 	return changes, nil
@@ -146,6 +157,11 @@ func (d *Diff) TableDiff(from, to *schema.Table) ([]schema.Change, error) {
 	if from.Name != to.Name {
 		return nil, fmt.Errorf("mismatched table names: %q != %q", from.Name, to.Name)
 	}
+	return d.tableDiff(from, to)
+}
+
+// tableDiff implements the table diffing but skips the table name check.
+func (d *Diff) tableDiff(from, to *schema.Table) ([]schema.Change, error) {
 	// Normalizing tables before starting the diff process.
 	if n, ok := d.DiffDriver.(Normalizer); ok {
 		if err := n.Normalize(from, to); err != nil {
@@ -153,10 +169,6 @@ func (d *Diff) TableDiff(from, to *schema.Table) ([]schema.Change, error) {
 		}
 	}
 	var changes []schema.Change
-	if from.Name != to.Name {
-		return nil, fmt.Errorf("mismatched table names: %q != %q", from.Name, to.Name)
-	}
-
 	// Drop or modify attributes (collations, checks, etc).
 	change, err := d.TableAttrDiff(from, to)
 	if err != nil {
@@ -372,6 +384,17 @@ func (d *Diff) similarUnnamedIndex(t *schema.Table, idx1 *schema.Index) (*schema
 	return nil, false
 }
 
+func (d *Diff) findTable(s *schema.Schema, name string) (*schema.Table, error) {
+	if f, ok := d.DiffDriver.(TableFinder); ok {
+		return f.FindTable(s, name)
+	}
+	t, ok := s.Table(name)
+	if !ok {
+		return nil, &schema.NotExistError{Err: fmt.Errorf("table %q was not found", name)}
+	}
+	return t, nil
+}
+
 // CommentChange reports if the element comment was changed.
 func CommentChange(from, to []schema.Attr) schema.ChangeKind {
 	var c1, c2 schema.Comment
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index cc9a4fb3..ea3d558d 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -211,6 +211,41 @@ func (d *diff) Normalize(from, to *schema.Table) error {
 	return d.defaultCharset(&to.Attrs)
 }
 
+// FindTable implements the DiffDriver.TableFinder method in order to provide
+// tables lookup that respect the "lower_case_table_names" system variable.
+func (d *diff) FindTable(s *schema.Schema, name string) (*schema.Table, error) {
+	switch d.lcnames {
+	// In mode 0: tables are stored as specified, and comparisons are case-sensitive.
+	case 0:
+		t, ok := s.Table(name)
+		if !ok {
+			return nil, &schema.NotExistError{Err: fmt.Errorf("table %q was not found", name)}
+		}
+		return t, nil
+	// In mode 1: the table are stored in lowercase, but they are still
+	// returned on inspection, because comparisons are not case-sensitive.
+	// In mode 2: the tables are stored as given but compared in lowercase.
+	// This option is not supported by Linux-based systems.
+	case 1, 2:
+		var matches []*schema.Table
+		for _, t := range s.Tables {
+			if strings.ToLower(name) == strings.ToLower(t.Name) {
+				matches = append(matches, t)
+			}
+		}
+		switch n := len(matches); n {
+		case 0:
+			return nil, &schema.NotExistError{Err: fmt.Errorf("table %q was not found", name)}
+		case 1:
+			return matches[0], nil
+		default:
+			return nil, fmt.Errorf("%d matches found for table %q", n, name)
+		}
+	default:
+		return nil, fmt.Errorf("unsupported 'lower_case_table_names' mode: %d", d.lcnames)
+	}
+}
+
 // collationChange returns the schema change for migrating the collation if
 // it was changed, and it is not the default attribute inherited from its parent.
 func (*diff) collationChange(from, top, to []schema.Attr) schema.Change {
diff --git a/sql/mysql/driver.go b/sql/mysql/driver.go
index 1967bcef..b8e73866 100644
--- a/sql/mysql/driver.go
+++ b/sql/mysql/driver.go
@@ -35,6 +35,7 @@ type (
 		mysqlversion.V
 		collate string
 		charset string
+		lcnames int
 	}
 )
 
@@ -58,7 +59,7 @@ func Open(db schema.ExecQuerier) (migrate.Driver, error) {
 	if err != nil {
 		return nil, fmt.Errorf("mysql: query system variables: %w", err)
 	}
-	if err := sqlx.ScanOne(rows, &c.V, &c.collate, &c.charset); err != nil {
+	if err := sqlx.ScanOne(rows, &c.V, &c.collate, &c.charset, &c.lcnames); err != nil {
 		return nil, fmt.Errorf("mysql: scan system variables: %w", err)
 	}
 	if c.TiDB() {
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 38ac2fbb..44c4d700 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -676,7 +676,7 @@ func nArgs(n int) string { return strings.Repeat("?, ", n-1) + "?" }
 
 const (
 	// Query to list system variables.
-	variablesQuery = "SELECT @@version, @@collation_server, @@character_set_server"
+	variablesQuery = "SELECT @@version, @@collation_server, @@character_set_server, @@lower_case_table_names"
 
 	// Query to list database schemas.
 	schemasQuery = "SELECT `SCHEMA_NAME`, `DEFAULT_CHARACTER_SET_NAME`, `DEFAULT_COLLATION_NAME` from `INFORMATION_SCHEMA`.`SCHEMATA` WHERE `SCHEMA_NAME` NOT IN ('information_schema','innodb','mysql','performance_schema','sys') ORDER BY `SCHEMA_NAME`"


Test patch:
diff --git a/sql/mysql/diff_test.go b/sql/mysql/diff_test.go
index 495ff385..6c6d8b87 100644
--- a/sql/mysql/diff_test.go
+++ b/sql/mysql/diff_test.go
@@ -604,6 +604,54 @@ func TestDiff_SchemaDiff(t *testing.T) {
 	}, changes)
 }
 
+func TestDiff_LowerCaseMode(t *testing.T) {
+	db, m, err := sqlmock.New()
+	require.NoError(t, err)
+	mock{m}.lcmode("8.0.19", "1")
+	drv, err := Open(db)
+	require.NoError(t, err)
+	changes, err := drv.SchemaDiff(
+		schema.New("public").AddTables(schema.NewTable("t"), schema.NewTable("S")),
+		schema.New("public").AddTables(schema.NewTable("T"), schema.NewTable("s")),
+	)
+	require.NoError(t, err)
+	require.Nil(t, changes)
+
+	changes, err = drv.SchemaDiff(
+		schema.New("public").AddTables(schema.NewTable("t")),
+		schema.New("public").AddTables(schema.NewTable("T"), schema.NewTable("t")),
+	)
+	require.EqualError(t, err, `2 matches found for table "t"`)
+	require.Nil(t, changes)
+
+	changes, err = drv.SchemaDiff(
+		schema.New("public").AddTables(schema.NewTable("t")),
+		schema.New("public").AddTables(schema.NewTable("s"), schema.NewTable("S")),
+	)
+	require.NoError(t, err)
+	require.Len(t, changes, 3)
+
+	mock{m}.lcmode("8.0.19", "2")
+	drv, err = Open(db)
+	require.NoError(t, err)
+	changes, err = drv.SchemaDiff(
+		schema.New("public").AddTables(schema.NewTable("t"), schema.NewTable("S")),
+		schema.New("public").AddTables(schema.NewTable("T"), schema.NewTable("s")),
+	)
+	require.NoError(t, err)
+	require.Nil(t, changes)
+
+	mock{m}.lcmode("8.0.19", "3")
+	drv, err = Open(db)
+	require.NoError(t, err)
+	changes, err = drv.SchemaDiff(
+		schema.New("public").AddTables(schema.NewTable("t"), schema.NewTable("S")),
+		schema.New("public").AddTables(schema.NewTable("T"), schema.NewTable("s")),
+	)
+	require.EqualError(t, err, `unsupported 'lower_case_table_names' mode: 3`)
+	require.Nil(t, changes)
+}
+
 func TestDiff_RealmDiff(t *testing.T) {
 	db, m, err := sqlmock.New()
 	require.NoError(t, err)
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index 6e5748f5..7f9b3d4f 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -1035,13 +1035,26 @@ type mock struct {
 }
 
 func (m mock) version(version string) {
+
+	m.ExpectQuery(sqltest.Escape(variablesQuery)).
+		WillReturnRows(sqltest.Rows(`
++-----------------+--------------------+------------------------+--------------------------+ 
+| @@version       | @@collation_server | @@character_set_server | @@lower_case_table_names | 
++-----------------+--------------------+------------------------+--------------------------+ 
+| ` + version + ` | utf8_general_ci    | utf8                   | 0                        | 
++-----------------+--------------------+------------------------+--------------------------+ 
+`))
+}
+
+func (m mock) lcmode(version, mode string) {
+
 	m.ExpectQuery(sqltest.Escape(variablesQuery)).
 		WillReturnRows(sqltest.Rows(`
-+-----------------+--------------------+------------------------+
-| @@version       | @@collation_server | @@character_set_server |
-+-----------------+--------------------+------------------------+
-| ` + version + ` | utf8_general_ci    | utf8                   |
-+-----------------+--------------------+------------------------+
++-----------------+--------------------+------------------------+--------------------------+ 
+| @@version       | @@collation_server | @@character_set_server | @@lower_case_table_names | 
++-----------------+--------------------+------------------------+--------------------------+ 
+| ` + version + ` | utf8_general_ci    | utf8                   | ` + mode + `             |
++-----------------+--------------------+------------------------+--------------------------+ 
 `))
 }