From 2ee608c5f27db376cf4fa6a306413b149dfcc74b Mon Sep 17 00:00:00 2001 From: Pierre-Louis GONON Date: Fri, 24 Oct 2025 11:27:52 +0200 Subject: [PATCH 1/4] mysql: Replace if statement with switch in audit --- internal/mysql/audit.go | 16 +++++++++------- 1 file changed, 9 insertions(+), 7 deletions(-) diff --git a/internal/mysql/audit.go b/internal/mysql/audit.go index a4d4dc0fb..ad0fa36b5 100644 --- a/internal/mysql/audit.go +++ b/internal/mysql/audit.go @@ -120,18 +120,20 @@ func (mdl *model) auditTable(table *catalog.Table) { c.Annotations = nil audit.Identifier(c, c.Name) audit.Expression(c, c.Default) - if c.Type.Name == "enum" { //nolint:staticcheck + + switch c.Type.Name { + case "enum": c.Annotate("type enum", scores.ColumnTypeEnum) - } else if c.Type.Name == "set" { + case "set": c.Annotate("type set", scores.ColumnTypeSet) } - if c.Generated == "VIRTUAL" { + + switch c.Generated { + case "VIRTUAL": c.Annotate("virtual column", scores.VirtualColumn) - } - if c.Generated == "ALWAYS AS IDENTITY" { + case "ALWAYS AS IDENTITY": c.Score += scores.Sequence - } - if c.Generated == "ON UPDATE CURRENT_TIMESTAMP" { + case "ON UPDATE CURRENT_TIMESTAMP": c.Annotate("on update current timestamp", scores.ColumnOnUpdateCurrentTimestamp) } } -- GitLab From 207005b7534fc8e3437eb9f8df4d023eb4ab06fe Mon Sep 17 00:00:00 2001 From: Pierre-Louis GONON Date: Fri, 24 Oct 2025 11:30:50 +0200 Subject: [PATCH 2/4] mysql: Convert default set value MariaDB returns the quoted default value for enum and set, whereas MySQL does not. --- internal/mysql/convert.go | 30 +++- internal/mysql/convert_test.go | 218 +++++++++++++++++++++++++++++ internal/mysql/sql/columns.sql | 8 +- test/fixtures/mariadb/00-extra.sql | 1 + test/fixtures/mysql/00-extra.sql | 1 + 5 files changed, 253 insertions(+), 5 deletions(-) create mode 100644 internal/mysql/convert_test.go diff --git a/internal/mysql/convert.go b/internal/mysql/convert.go index 4af0c513d..d02c35ee5 100644 --- a/internal/mysql/convert.go +++ b/internal/mysql/convert.go @@ -58,13 +58,35 @@ func convertEnumSet(t catalog.Table) catalog.Table { slog.Debug("Setting default enum value.", "value", values[0], "path", c.ObjectPath) break } - c.Default = lexer.QuoteString(c.Default) - if slices.Contains(values, c.Default) { - slog.Debug("Converting default enum value.", "value", c.Default, "path", c.ObjectPath) - t.Columns[i].Default = c.Default + quoted := lexer.QuoteString(c.Default) + if slices.Contains(values, quoted) { + t.Columns[i].Default = quoted + slog.Debug("Quoting default enum value.", "value", quoted, "path", c.ObjectPath) } case "set": check.Expression = fmt.Sprintf("%s <@ ARRAY[%s]", c.Name, items) + if (!c.Nullable && c.Default == "") || c.Default == "''" { + t.Columns[i].Default = "'{}'" + slog.Debug("Setting default set value.", "value", "'{}'", "path", c.ObjectPath) + break + } + if c.Default != "" { + // MariaDB returns the quoted default value, whereas MySQL does not. + unquoted := lexer.UnquoteString(c.Default) + defaultValues := strings.Split(unquoted, ",") + contains := true + for _, d := range defaultValues { + if !slices.Contains(values, lexer.QuoteString(strings.TrimSpace(d))) { + contains = false + break + } + } + if contains { + value := fmt.Sprintf("'{%s}'", unquoted) + t.Columns[i].Default = value + slog.Debug("Setting default set value.", "value", value, "path", c.ObjectPath) + } + } default: slog.Warn("Unexpected type with values.", "type", c.Type, "path", c.ObjectPath) continue diff --git a/internal/mysql/convert_test.go b/internal/mysql/convert_test.go new file mode 100644 index 000000000..b796e5a8b --- /dev/null +++ b/internal/mysql/convert_test.go @@ -0,0 +1,218 @@ +package mysql + +import ( + "testing" + + "github.com/stretchr/testify/require" + "gitlab.com/dalibo/pg_migrate/internal/catalog" +) + +func TestConvertEnumNotNullable(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "enum", + Columns: []catalog.Column{ + { + Name: "myEnumColumn", + Type: catalog.DataType{ + Name: "enum", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: false, + }, + }, + } + table = convertEnumSet(table) + r.Equal("'tuut'", table.Columns[0].Default) + r.Equal("myEnumColumn in ('tuut', 'pouet', 'bip')", table.Checks[0].Expression) +} + +func TestConvertEnumNotNullableMySQL(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "enum", + Columns: []catalog.Column{ + { + Name: "myEnumColumn", + Type: catalog.DataType{ + Name: "enum", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: false, + Default: "bip", + }, + }, + } + table = convertEnumSet(table) + r.Equal("'bip'", table.Columns[0].Default) + r.Equal("myEnumColumn in ('tuut', 'pouet', 'bip')", table.Checks[0].Expression) +} + +func TestConvertEnumNotNullableMaria(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "enum", + Columns: []catalog.Column{ + { + Name: "myEnumColumn", + Type: catalog.DataType{ + Name: "enum", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: false, + Default: "'pouet'", + }, + }, + } + table = convertEnumSet(table) + r.Equal("'pouet'", table.Columns[0].Default) + r.Equal("myEnumColumn in ('tuut', 'pouet', 'bip')", table.Checks[0].Expression) +} + +func TestConvertEnumNonLitteral(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "enum", + Columns: []catalog.Column{ + { + Name: "myEnumColumn", + Type: catalog.DataType{ + Name: "enum", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: true, + Default: "maFonction(mondIdentifiant)", + }, + }, + } + table = convertEnumSet(table) + r.Equal("maFonction(mondIdentifiant)", table.Columns[0].Default) + r.Equal("myEnumColumn in ('tuut', 'pouet', 'bip')", table.Checks[0].Expression) +} + +func TestConvertSetNotNullable(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "set", + Columns: []catalog.Column{ + { + Name: "mySetColumn", + Type: catalog.DataType{ + Name: "set", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: false, + }, + }, + } + table = convertEnumSet(table) + r.Equal("'{}'", table.Columns[0].Default) + r.Equal("mySetColumn <@ ARRAY['tuut', 'pouet', 'bip']", table.Checks[0].Expression) +} + +func TestConvertSetNotNullableMaria(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "set", + Columns: []catalog.Column{ + { + Name: "mySetColumn", + Type: catalog.DataType{ + Name: "set", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: false, + Default: "pouet, bip", + }, + }, + } + table = convertEnumSet(table) + r.Equal("'{pouet, bip}'", table.Columns[0].Default) + r.Equal("mySetColumn <@ ARRAY['tuut', 'pouet', 'bip']", table.Checks[0].Expression) +} + +func TestConvertSetemptyString(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "set", + Columns: []catalog.Column{ + { + Name: "mySetColumn", + Type: catalog.DataType{ + Name: "set", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: false, + Default: "''", + }, + }, + } + table = convertEnumSet(table) + r.Equal("'{}'", table.Columns[0].Default) + r.Equal("mySetColumn <@ ARRAY['tuut', 'pouet', 'bip']", table.Checks[0].Expression) +} + +func TestConvertEnumSetNullable(t *testing.T) { + r := require.New(t) + table := catalog.Table{ + Name: "set", + Columns: []catalog.Column{ + { + Name: "myEnumColumn", + Type: catalog.DataType{ + Name: "enum", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: true, + }, + { + Name: "mySetColumn", + Type: catalog.DataType{ + Name: "set", + Params: []string{ + "'tuut'", + "'pouet'", + "'bip'", + }, + }, + Nullable: true, + }, + }, + } + + table = convertEnumSet(table) + r.Equal("", table.Columns[0].Default) + r.Equal("", table.Columns[1].Default) +} diff --git a/internal/mysql/sql/columns.sql b/internal/mysql/sql/columns.sql index 1496b1f29..a79b10769 100644 --- a/internal/mysql/sql/columns.sql +++ b/internal/mysql/sql/columns.sql @@ -4,7 +4,13 @@ SELECT c.table_schema, c.table_name, column_name, column_comment, NULL AS "precision", NULL AS "scale", is_nullable = 'YES' AS nullable, - COALESCE(NULLIF(generation_expression,''), column_default), + COALESCE( + NULLIF(generation_expression,''), + CASE + WHEN column_default = '' THEN '''''' + ELSE column_default + END + ), CASE extra WHEN 'STORED GENERATED' THEN 'STORED' WHEN 'VIRTUAL GENERATED' THEN 'VIRTUAL' diff --git a/test/fixtures/mariadb/00-extra.sql b/test/fixtures/mariadb/00-extra.sql index c77f48038..2d9fd8ec5 100644 --- a/test/fixtures/mariadb/00-extra.sql +++ b/test/fixtures/mariadb/00-extra.sql @@ -3,6 +3,7 @@ CREATE TABLE x_table ( name VARCHAR(50) NOT NULL, upper_name VARCHAR(50) GENERATED ALWAYS AS (UPPER(name)) VIRTUAL, lower_name VARCHAR(50) GENERATED ALWAYS AS (LOWER(name)) STORED, + category SET('cat0','cat1','cat2') NOT NULL DEFAULT '', CONSTRAINT CHK_MixedCase_id CHECK (id BETWEEN 1 AND 10), CONSTRAINT x_table_uk UNIQUE (id, name) ); diff --git a/test/fixtures/mysql/00-extra.sql b/test/fixtures/mysql/00-extra.sql index 8e4c9ef92..9b66d8c14 100644 --- a/test/fixtures/mysql/00-extra.sql +++ b/test/fixtures/mysql/00-extra.sql @@ -3,6 +3,7 @@ CREATE TABLE x_table ( name VARCHAR(50) NOT NULL, upper_name VARCHAR(50) GENERATED ALWAYS AS (UPPER(name)) VIRTUAL, lower_name VARCHAR(50) GENERATED ALWAYS AS (LOWER(name)) STORED, + category SET('cat0','cat1','cat2') NOT NULL DEFAULT '', CONSTRAINT CHK_MixedCase_id CHECK (id BETWEEN 1 AND 10), CONSTRAINT x_table_uk UNIQUE (id, name) ); -- GitLab From cb286f72877341746e39bd38ef0df0f771794cb5 Mon Sep 17 00:00:00 2001 From: Pierre-Louis GONON Date: Mon, 27 Oct 2025 14:45:53 +0100 Subject: [PATCH 3/4] mysql: Use the default port in dev mode MariaDB runs on the default port in development mode, whereas MySQL runs on port 13306 in CI. --- docker-compose.yml | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/docker-compose.yml b/docker-compose.yml index b63bd5518..6ea3b8c6c 100644 --- a/docker-compose.yml +++ b/docker-compose.yml @@ -85,7 +85,7 @@ services: volumes: - .:/pg_migrate/ - ./test/fixtures/mysql/:/docker-entrypoint-initdb.d/ - ports: ["3306:3306"] + ports: ["13306:3306"] environment: MYSQL_ROOT_PASSWORD: N0tSecret MYSQL_USER: sakila @@ -124,7 +124,7 @@ services: volumes: - .:/pg_migrate/ - ./test/fixtures/mariadb/:/docker-entrypoint-initdb.d/ - ports: ["3308:3306"] + ports: ["3306:3306"] environment: MARIADB_ROOT_PASSWORD: N0tSecret MARIADB_USER: sakila -- GitLab From 64c5f4967a6457986144cbedac074c24ff212547 Mon Sep 17 00:00:00 2001 From: Pierre-Louis GONON Date: Tue, 28 Oct 2025 11:57:43 +0100 Subject: [PATCH 4/4] mysql: Annotate non literal default values for set and enum --- internal/mysql/audit.go | 11 +++++++++++ internal/mysql/convert.go | 10 +--------- internal/mysql/tables.go | 17 +++++++++++++++++ internal/project/scores.go | 2 ++ 4 files changed, 31 insertions(+), 9 deletions(-) diff --git a/internal/mysql/audit.go b/internal/mysql/audit.go index ad0fa36b5..e6db3b548 100644 --- a/internal/mysql/audit.go +++ b/internal/mysql/audit.go @@ -10,6 +10,7 @@ import ( "gitlab.com/dalibo/pg_migrate/internal/catalog" "gitlab.com/dalibo/pg_migrate/internal/project" "gitlab.com/dalibo/transqlate/ast" + "gitlab.com/dalibo/transqlate/lexer" ) var scores project.Scores @@ -124,8 +125,18 @@ func (mdl *model) auditTable(table *catalog.Table) { switch c.Type.Name { case "enum": c.Annotate("type enum", scores.ColumnTypeEnum) + unquoted := lexer.UnquoteString(c.Default) + if unquoted != "" && !slices.Contains(c.Type.Values(), lexer.QuoteString(unquoted)) { + c.Annotate("non literral default value", scores.ColumnTypeEnumSetDefault) + } case "set": c.Annotate("type set", scores.ColumnTypeSet) + if c.Default != "" && lexer.UnquoteString(c.Default) != "" { + unquoted := lexer.UnquoteString(c.Default) + if !IsValidDefaultSet(unquoted, c.Type.Values()) { + c.Annotate("non literral default value", scores.ColumnTypeEnumSetDefault) + } + } } switch c.Generated { diff --git a/internal/mysql/convert.go b/internal/mysql/convert.go index d02c35ee5..31fdb821f 100644 --- a/internal/mysql/convert.go +++ b/internal/mysql/convert.go @@ -73,15 +73,7 @@ func convertEnumSet(t catalog.Table) catalog.Table { if c.Default != "" { // MariaDB returns the quoted default value, whereas MySQL does not. unquoted := lexer.UnquoteString(c.Default) - defaultValues := strings.Split(unquoted, ",") - contains := true - for _, d := range defaultValues { - if !slices.Contains(values, lexer.QuoteString(strings.TrimSpace(d))) { - contains = false - break - } - } - if contains { + if IsValidDefaultSet(unquoted, values) { value := fmt.Sprintf("'{%s}'", unquoted) t.Columns[i].Default = value slog.Debug("Setting default set value.", "value", value, "path", c.ObjectPath) diff --git a/internal/mysql/tables.go b/internal/mysql/tables.go index b44d54369..d4eef0cb4 100644 --- a/internal/mysql/tables.go +++ b/internal/mysql/tables.go @@ -4,12 +4,14 @@ import ( "context" "database/sql" "log/slog" + "slices" "strings" "gitlab.com/dalibo/pg_migrate/internal/catalog" "gitlab.com/dalibo/pg_migrate/internal/database" "gitlab.com/dalibo/pg_migrate/internal/errorlist" "gitlab.com/dalibo/pg_migrate/internal/fetch" + "gitlab.com/dalibo/transqlate/lexer" ) func (mdl *model) inspectTables(ctx context.Context) error { @@ -212,3 +214,18 @@ func rowToSubPartition(rows *sql.Rows) (a catalog.Association, err error) { a.Component = p return a, err } + +// IsValidDefaultSet returns true if defaultValue is a valid default set value +// +// defaultValue must be unquoted, values contains possible set values +func IsValidDefaultSet(defaultValue string, values []string) bool { + defaultValues := strings.Split(defaultValue, ",") + contains := true + for _, d := range defaultValues { + if !slices.Contains(values, lexer.QuoteString(strings.TrimSpace(d))) { + contains = false + break + } + } + return contains +} diff --git a/internal/project/scores.go b/internal/project/scores.go index 6b1cf5dd2..5baccca4c 100644 --- a/internal/project/scores.go +++ b/internal/project/scores.go @@ -6,6 +6,7 @@ type Scores struct { Column float32 ColumnTypeEnum float32 ColumnTypeSet float32 + ColumnTypeEnumSetDefault float32 ColumnTypeLarge float32 ColumnMissingPrecision float32 ColumnNegativeScale float32 @@ -58,6 +59,7 @@ func newScores() Scores { Column: 0.1, ColumnTypeEnum: 0.5, ColumnTypeSet: 2, + ColumnTypeEnumSetDefault: 0.1, ColumnTypeLarge: 1, ColumnMissingPrecision: 1, ColumnNegativeScale: 1, -- GitLab