Repository: schemahero/schemahero
Pull request #222 (2020-07-02 01:19:37)

Fixing mysql types
Fix fixtures for tinytext, mediumtext and longtext in mysql.

Don't quote current_timestamp for datetime columns


Patch:
diff --git a/pkg/database/mysql/alias.go b/pkg/database/mysql/alias.go
index 0cff4e88..d6832161 100644
--- a/pkg/database/mysql/alias.go
+++ b/pkg/database/mysql/alias.go
@@ -14,6 +14,16 @@ func unaliasUnparameterizedColumnType(requestedType string) string {
 		return "tinyint (1)"
 	}
 
+	// mysql gives us the length of these text types, but it won't
+	// accept them as create table arguments...
+	if requestedType == "tinytext (255)" {
+		requestedType = "tinytext"
+	} else if requestedType == "mediumtext (16777215)" {
+		requestedType = "mediumtext"
+	} else if requestedType == "longtext (4294967295)" {
+		requestedType = "longtext"
+	}
+
 	for _, unparameterizedColumnType := range unparameterizedColumnTypes {
 		if unparameterizedColumnType == requestedType {
 			return requestedType
@@ -46,6 +56,10 @@ func unaliasParameterizedColumnType(requestedType string) string {
 		return fmt.Sprintf("int (%s)", matchGroups[1])
 	}
 	if strings.HasPrefix(requestedType, "dec") {
+		if strings.Contains(requestedType, "decimal") {
+			requestedType = strings.Replace(requestedType, "decimal", "dec", -1)
+		}
+
 		precisionAndScale := regexp.MustCompile(`dec\s*\(\s*(?P<precision>\d*),\s*(?P<scale>\d*)\s*\)`)
 		precisionOnly := regexp.MustCompile(`dec\s*\(\s*(?P<precision>\d*)\s*\)`)
 
@@ -64,7 +78,6 @@ func unaliasParameterizedColumnType(requestedType string) string {
 		precisionAndScale := regexp.MustCompile(`double precision\s*\(\s*(?P<precision>\d*),\s*(?P<scale>\d*)\s*\)`)
 		precisionAndScaleMatchGroups := precisionAndScale.FindStringSubmatch(requestedType)
 
-		fmt.Printf("%#v\n", precisionAndScaleMatchGroups)
 		if len(precisionAndScaleMatchGroups) == 3 {
 			return fmt.Sprintf("double (%s, %s)", precisionAndScaleMatchGroups[1], precisionAndScaleMatchGroups[2])
 		}
diff --git a/pkg/database/mysql/column.go b/pkg/database/mysql/column.go
index 68b3c9cf..a23d7751 100644
--- a/pkg/database/mysql/column.go
+++ b/pkg/database/mysql/column.go
@@ -65,7 +65,19 @@ func mysqlColumnAsInsert(column *schemasv1alpha4.SQLTableColumn) (string, error)
 	}
 
 	if mysqlColumn.ColumnDefault != nil {
-		formatted = fmt.Sprintf("%s default '%s'", formatted, *mysqlColumn.ColumnDefault)
+		quoteDefaultValue := true
+
+		if mysqlColumn.DataType == "datetime" || mysqlColumn.DataType == "timestamp" {
+			if *mysqlColumn.ColumnDefault == "CURRENT_TIMESTAMP" {
+				quoteDefaultValue = false
+			}
+		}
+
+		if quoteDefaultValue {
+			formatted = fmt.Sprintf("%s default '%s'", formatted, *mysqlColumn.ColumnDefault)
+		} else {
+			formatted = fmt.Sprintf("%s default %s", formatted, *mysqlColumn.ColumnDefault)
+		}
 	}
 
 	return formatted, nil
diff --git a/pkg/database/mysql/parameterized.go b/pkg/database/mysql/parameterized.go
index 2c2adb31..24d40ab3 100644
--- a/pkg/database/mysql/parameterized.go
+++ b/pkg/database/mysql/parameterized.go
@@ -12,6 +12,7 @@ var unparameterizedColumnTypes = []string{
 	"datetime",
 	"timestamp",
 	"tinyblob",
+	"tinytext",
 	"mediumblob",
 	"mediumtext",
 	"longblob",
@@ -50,9 +51,14 @@ func maybeParseParameterizedColumnType(requestedType string) (string, error) {
 	} else if strings.HasPrefix(requestedType, "char") {
 		columnType = "char"
 
+		if strings.Contains(requestedType, "character") {
+			requestedType = strings.Replace(requestedType, "character", "char", -1)
+		}
+
 		r := regexp.MustCompile(`char\s*\((?P<max>\d*)\)`)
 
 		matchGroups := r.FindStringSubmatch(requestedType)
+
 		if len(matchGroups) == 0 {
 			columnType = "char (11)"
 		} else {
@@ -159,54 +165,6 @@ func maybeParseParameterizedColumnType(requestedType string) (string, error) {
 			}
 			columnType = fmt.Sprintf("bigint (%d)", max)
 		}
-	} else if strings.HasPrefix(requestedType, "tinytext") {
-		columnType = "tinytext"
-
-		r := regexp.MustCompile(`tinytext\s*\((?P<max>\d*)\)`)
-
-		matchGroups := r.FindStringSubmatch(requestedType)
-		if len(matchGroups) == 0 {
-			columnType = "tinytext (255)"
-		} else {
-			maxStr := matchGroups[1]
-			max, err := strconv.Atoi(maxStr)
-			if err != nil {
-				return "", err
-			}
-			columnType = fmt.Sprintf("tinytext (%d)", max)
-		}
-	} else if strings.HasPrefix(requestedType, "mediumtext") {
-		columnType = "mediumtext"
-
-		r := regexp.MustCompile(`mediumtext\s*\((?P<max>\d*)\)`)
-
-		matchGroups := r.FindStringSubmatch(requestedType)
-		if len(matchGroups) == 0 {
-			columnType = "mediumtext (16777215)"
-		} else {
-			maxStr := matchGroups[1]
-			max, err := strconv.Atoi(maxStr)
-			if err != nil {
-				return "", err
-			}
-			columnType = fmt.Sprintf("mediumtext (%d)", max)
-		}
-	} else if strings.HasPrefix(requestedType, "longtext") {
-		columnType = "longtext"
-
-		r := regexp.MustCompile(`longtext\s*\((?P<max>\d*)\)`)
-
-		matchGroups := r.FindStringSubmatch(requestedType)
-		if len(matchGroups) == 0 {
-			columnType = "longtext (4294967295)"
-		} else {
-			maxStr := matchGroups[1]
-			max, err := strconv.Atoi(maxStr)
-			if err != nil {
-				return "", err
-			}
-			columnType = fmt.Sprintf("longtext (%d)", max)
-		}
 	} else if strings.HasPrefix(requestedType, "decimal") {
 		columnType = "decimal"
 
diff --git a/pkg/database/mysql/tables.go b/pkg/database/mysql/tables.go
index d4e1bf72..3bd45417 100644
--- a/pkg/database/mysql/tables.go
+++ b/pkg/database/mysql/tables.go
@@ -165,7 +165,10 @@ order by c.ORDINAL_POSITION`
 }
 
 func (m *MysqlConnection) GetTableSchema(tableName string) ([]*types.Column, error) {
-	query := `select COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from information_schema.COLUMNS where TABLE_NAME = ? order by ORDINAL_POSITION`
+	query := `select COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE 
+from information_schema.COLUMNS 
+where TABLE_NAME = ? 
+order by ORDINAL_POSITION`
 	rows, err := m.db.Query(query, tableName)
 	if err != nil {
 		return nil, err
@@ -179,8 +182,10 @@ func (m *MysqlConnection) GetTableSchema(tableName string) ([]*types.Column, err
 		var maxLength sql.NullInt64
 		var isNullable string
 		var columnDefault sql.NullString
+		var numericPrecision sql.NullInt64
+		var numericScale sql.NullInt64
 
-		if err := rows.Scan(&column.Name, &columnDefault, &isNullable, &column.DataType, &maxLength); err != nil {
+		if err := rows.Scan(&column.Name, &columnDefault, &isNullable, &column.DataType, &maxLength, &numericPrecision, &numericScale); err != nil {
 			return nil, err
 		}
 
@@ -201,6 +206,11 @@ func (m *MysqlConnection) GetTableSchema(tableName string) ([]*types.Column, err
 		if maxLength.Valid {
 			column.DataType = fmt.Sprintf("%s (%d)", column.DataType, maxLength.Int64)
 		}
+
+		if numericPrecision.Valid && numericScale.Valid {
+			column.DataType = fmt.Sprintf("%s (%d, %d)", column.DataType, numericPrecision.Int64, numericScale.Int64)
+		}
+
 		columns = append(columns, &column)
 	}
 


Test patch:
diff --git a/pkg/database/mysql/alias_test.go b/pkg/database/mysql/alias_test.go
index 7aeabe3e..799a0528 100644
--- a/pkg/database/mysql/alias_test.go
+++ b/pkg/database/mysql/alias_test.go
@@ -41,6 +41,36 @@ func Test_unaliasUnparameterizedColumnType(t *testing.T) {
 			requestedType:         "boolean",
 			expectedUnaliasedType: "tinyint (1)",
 		},
+		{
+			name:                  "tinytext",
+			requestedType:         "tinytext",
+			expectedUnaliasedType: "tinytext",
+		},
+		{
+			name:                  "tinytext",
+			requestedType:         "tinytext (255)",
+			expectedUnaliasedType: "tinytext",
+		},
+		{
+			name:                  "mediumtext",
+			requestedType:         "mediumtext",
+			expectedUnaliasedType: "mediumtext",
+		},
+		{
+			name:                  "mediumtext",
+			requestedType:         "mediumtext (16777215)",
+			expectedUnaliasedType: "mediumtext",
+		},
+		{
+			name:                  "longtext",
+			requestedType:         "longtext",
+			expectedUnaliasedType: "longtext",
+		},
+		{
+			name:                  "longtext (4294967295)",
+			requestedType:         "longtext (4294967295)",
+			expectedUnaliasedType: "longtext",
+		},
 	}
 
 	for _, test := range tests {
diff --git a/pkg/database/mysql/create_test.go b/pkg/database/mysql/create_test.go
index 6fa491ad..2fe2b85b 100644
--- a/pkg/database/mysql/create_test.go
+++ b/pkg/database/mysql/create_test.go
@@ -32,6 +32,27 @@ func Test_CreateTableStatement(t *testing.T) {
 			tableName:         "simple",
 			expectedStatement: "create table `simple` (`id` int (11), primary key (`id`))",
 		},
+		{
+			name: "varchar composite primary key",
+			tableSchema: &schemasv1alpha4.SQLTableSchema{
+				PrimaryKey: []string{
+					"col_a",
+					"col_b",
+				},
+				Columns: []*schemasv1alpha4.SQLTableColumn{
+					&schemasv1alpha4.SQLTableColumn{
+						Name: "col_a",
+						Type: "char (36)",
+					},
+					&schemasv1alpha4.SQLTableColumn{
+						Name: "col_b",
+						Type: "varchar (255)",
+					},
+				},
+			},
+			tableName:         "table_b",
+			expectedStatement: "create table `table_b` (`col_a` char (36), `col_b` varchar (255), primary key (`col_a`, `col_b`))",
+		},
 		{
 			name: "composite primary key",
 			tableSchema: &schemasv1alpha4.SQLTableSchema{
@@ -57,6 +78,26 @@ func Test_CreateTableStatement(t *testing.T) {
 			tableName:         "composite_primary_key",
 			expectedStatement: "create table `composite_primary_key` (`one` int (11), `two` int (11), `three` varchar (255), primary key (`one`, `two`))",
 		},
+		{
+			name: "decimal (8, 2) column",
+			tableSchema: &schemasv1alpha4.SQLTableSchema{
+				PrimaryKey: []string{
+					"one",
+				},
+				Columns: []*schemasv1alpha4.SQLTableColumn{
+					&schemasv1alpha4.SQLTableColumn{
+						Name: "one",
+						Type: "integer",
+					},
+					&schemasv1alpha4.SQLTableColumn{
+						Name: "bee",
+						Type: "decimal (8, 2)",
+					},
+				},
+			},
+			tableName:         "decimal_8_2",
+			expectedStatement: "create table `decimal_8_2` (`one` int (11), `bee` decimal (8, 2), primary key (`one`))",
+		},
 	}
 
 	for _, test := range tests {
diff --git a/pkg/database/mysql/parameterized_test.go b/pkg/database/mysql/parameterized_test.go
index 5263fcff..a638d1d0 100644
--- a/pkg/database/mysql/parameterized_test.go
+++ b/pkg/database/mysql/parameterized_test.go
@@ -153,6 +153,11 @@ func Test_maybeParseParameterizedColumnType(t *testing.T) {
 			requestedType:      "blob",
 			expectedColumnType: "blob",
 		},
+		{
+			name:               "char (36)",
+			requestedType:      "char (36)",
+			expectedColumnType: "char (36)",
+		},
 	}
 
 	for _, test := range tests {