Repository: schemahero/schemahero (go)

Issue:
Add support for parametrized datetime column in MySQL
Using the following spec:

``` yaml
apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: test-table
spec:
  database: mydb
  name: test_table
  schema:
    mysql:
      columns:
      - name: created_at
        type: datetime (3)
        constraints:
          notNull: true
```

`schemahero plan` produces the following error:

```
Error: failed to build column statements: failed to create alter column statement: unknown column type. cannot validate column type "datetime (3)"
failed to build column statements: failed to create alter column statement: unknown column type. cannot validate column type "datetime (3)"
```

Pull request:
Support for fractional seconds precision in datetime and timestamp types for MySQL
Fixes #864

Patch:
diff --git a/pkg/database/mysql/parameterized.go b/pkg/database/mysql/parameterized.go
index e0617699..44ed8742 100644
--- a/pkg/database/mysql/parameterized.go
+++ b/pkg/database/mysql/parameterized.go
@@ -67,6 +67,40 @@ func maybeParseParameterizedColumnType(requestedType string) (string, error) {
 			}
 			columnType = fmt.Sprintf("char (%d)", max)
 		}
+	} else if strings.HasPrefix(requestedType, "datetime") {
+		r := regexp.MustCompile(`datetime\s*\((?P<max>\d*)\)`)
+
+		matchGroups := r.FindStringSubmatch(requestedType)
+		if len(matchGroups) == 0 {
+			return "", fmt.Errorf("invalid datetime precision")
+		} else {
+			maxStr := matchGroups[1]
+			max, err := strconv.Atoi(maxStr)
+			if err != nil {
+				return "", err
+			}
+			if max < 0 || max > 6 {
+				return "", fmt.Errorf("invalid datetime precision %d", max)
+			}
+			columnType = fmt.Sprintf("datetime (%d)", max)
+		}
+	} else if strings.HasPrefix(requestedType, "timestamp") {
+		r := regexp.MustCompile(`timestamp\s*\((?P<max>\d*)\)`)
+
+		matchGroups := r.FindStringSubmatch(requestedType)
+		if len(matchGroups) == 0 {
+			return "", fmt.Errorf("invalid timestamp precision")
+		} else {
+			maxStr := matchGroups[1]
+			max, err := strconv.Atoi(maxStr)
+			if err != nil {
+				return "", err
+			}
+			if max < 0 || max > 6 {
+				return "", fmt.Errorf("invalid timestamp precision %d", max)
+			}
+			columnType = fmt.Sprintf("timestamp (%d)", max)
+		}
 	} else if strings.HasPrefix(requestedType, "tinyint") {
 		r := regexp.MustCompile(`tinyint\s*\((?P<max>\d*)\)`)
 


Test patch:
diff --git a/integration/tests/timescaledb/column-set-default/expect.sql b/integration/tests/timescaledb/column-set-default/expect.sql
index 6f8c7d09..ede1bb39 100644
--- a/integration/tests/timescaledb/column-set-default/expect.sql
+++ b/integration/tests/timescaledb/column-set-default/expect.sql
@@ -1,2 +1,2 @@
-alter table "users" alter column "num_seats" set default '5';
 alter table "users" alter column "account_type" set default 'trial';
+alter table "users" alter column "num_seats" set default '5';
diff --git a/integration/tests/timescaledb/column-unset-default/expect.sql b/integration/tests/timescaledb/column-unset-default/expect.sql
index eef20102..7520cdbc 100644
--- a/integration/tests/timescaledb/column-unset-default/expect.sql
+++ b/integration/tests/timescaledb/column-unset-default/expect.sql
@@ -1,2 +1,2 @@
-alter table "users" alter column "num_seats" drop default, alter column "num_seats" drop not null;
 alter table "users" alter column "account_type" drop default, alter column "account_type" drop not null;
+alter table "users" alter column "num_seats" drop default, alter column "num_seats" drop not null;
diff --git a/pkg/database/mysql/parameterized_test.go b/pkg/database/mysql/parameterized_test.go
index a638d1d0..2f78d37c 100644
--- a/pkg/database/mysql/parameterized_test.go
+++ b/pkg/database/mysql/parameterized_test.go
@@ -1,6 +1,7 @@
 package mysql
 
 import (
+	"strings"
 	"testing"
 
 	"github.com/stretchr/testify/assert"
@@ -9,9 +10,10 @@ import (
 
 func Test_maybeParseParameterizedColumnType(t *testing.T) {
 	tests := []struct {
-		name               string
-		requestedType      string
-		expectedColumnType string
+		name                string
+		requestedType       string
+		expectedColumnType  string
+		expectedErrorSubstr string
 	}{
 		{
 			name:               "fake",
@@ -158,13 +160,51 @@ func Test_maybeParseParameterizedColumnType(t *testing.T) {
 			requestedType:      "char (36)",
 			expectedColumnType: "char (36)",
 		},
+		{
+			name:               "datetime (0)",
+			requestedType:      "datetime (0)",
+			expectedColumnType: "datetime (0)",
+		},
+		{
+			name:               "datetime (6)",
+			requestedType:      "datetime (6)",
+			expectedColumnType: "datetime (6)",
+		},
+		{
+			name:                "datetime (7)",
+			requestedType:       "datetime (7)",
+			expectedColumnType:  "",
+			expectedErrorSubstr: "invalid datetime precision 7",
+		},
+		{
+			name:               "timestamp (0)",
+			requestedType:      "timestamp (0)",
+			expectedColumnType: "timestamp (0)",
+		},
+		{
+			name:               "timestamp (6)",
+			requestedType:      "timestamp (6)",
+			expectedColumnType: "timestamp (6)",
+		},
+		{
+			name:                "timestamp (7)",
+			requestedType:       "timestamp (7)",
+			expectedColumnType:  "",
+			expectedErrorSubstr: "invalid timestamp precision 7",
+		},
 	}
 
 	for _, test := range tests {
 		t.Run(test.name, func(t *testing.T) {
-			columnType, err := maybeParseParameterizedColumnType(test.requestedType)
 			req := require.New(t)
-			req.NoError(err)
+			columnType, err := maybeParseParameterizedColumnType(test.requestedType)
+
+			if test.expectedErrorSubstr == "" {
+				req.NoError(err)
+			} else {
+				req.Error(err)
+				assert.True(t, strings.Contains(err.Error(), test.expectedErrorSubstr))
+			}
 			assert.Equal(t, test.expectedColumnType, columnType)
 		})
 	}