MySQL Enum redeclarations

[oderwat]

I am evaluating sqlc for one of our future projects. Currently, I am using sqlc to write a small migration tool for converting a legacy MySQL database to PostgreSQL.

At the very first step I ran into a problem with the generated code for this schema fragment:

CREATE TABLE `users` (
  `id` int(14) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('active','inactive','blocked') NOT NULL DEFAULT 'active',
  `testuser` enum('N','Y') NOT NULL DEFAULT 'N',
  `readonly` enum('N',Y') NOT NULL DEFAULT 'N',
...

The problem is that those enums cause redeclaration errors as it creates constants with the raw Enum name.

type TestuserType string

const (
	N TestuserType = "N"
	Y TestuserType = "Y"
)

type ReadonlyType string

const (
	N ReadonlyType = "N"
	Y ReadonlyType = "Y"
)

The real users schema includes 12 enums of the type enum('N','Y') and a lot of the other tables share the same enum names too.

I wonder that it seems that I can not use enums with the same names in different columns or even different tables.

Is this because MySQL support is being experimental or am I doing something wrong here and there is a way to solve this problem?

[kyleconroy]

I wonder that it seems that I can not use enums with the same names in different columns or even different tables.

Right now sqlc will generate a new type for every column with an enum.

Is this because MySQL support is being experimental or am I doing something wrong here and there is a way to solve this problem?

You aren't doing anything wrong; MySQL support is experimental and this is one (of many) edge cases.

I won't be fixing any MySQL bugs in the short term; I'm working on a major architecture change that unifies the SQL processing for all database engines. It should land by the next release.

[oderwat]

The documentation for enums clearly shows that the variable names for the values get the same name as the enum type as a prefix. So I guess that this edge case is not really an edge case but a wrong implementation of what is supposed to happen for Enums?

I guess this can easily be fixed by changing
https://github.com/kyleconroy/sqlc/blob/fd329cbd13f1cfa31623b888e356960db5cbec8e/internal/mysql/gen.go#L39

I think I am going to do this in a dev version just to be able to continue with what I wanted to do. Would you accept a PR for this or is the new version really close anyway?

[kyleconroy]

@oderwat Sorry that discussion of this dropped off. As I said in a previous comment, the MySQL package has been deprecated and replaced. The new package, code-named Dolphin, now has enum support (#676). However, that implementation has a different strategy for enum naming.

Given your MySQL table:

CREATE TABLE `users` (
  `id` int(14) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('active','inactive','blocked') NOT NULL DEFAULT 'active',
  `testuser` enum('N','Y') NOT NULL DEFAULT 'N',
  `readonly` enum('N','Y') NOT NULL DEFAULT 'N',
)

The old MySQL package generated the following Go:

type TestuserType string

const (
	N TestuserType = "N"
	Y TestuserType = "Y"
)

type ReadonlyType string

const (
	N ReadonlyType = "N"
	Y ReadonlyType = "Y"
)

This was obviously wrong, as N and Y were declared multiple times, causing compilation to fail.

The new MySQL package generates the following:

type Readonly string

const (
	ReadonlyN Readonly = "N"
	ReadonlyY Readonly = "Y"
)

type Status string

const (
	StatusActive   Status = "active"
	StatusInactive Status = "inactive"
	StatusBlocked  Status = "blocked"
)

type Testuser string

const (
	TestuserN Testuser = "N"
	TestuserY Testuser = "Y"
)

This will compile, but using the column name as the type name may cause conflicts if the name is reused elsewhere.

[oderwat]

I am very pleased about your coming back to this!

The new way seems to be pretty nice. Just a thought: Wouldn't it possible to bring this into a kind of "enum" package? That way it would be enum.Status and enum.StatusActive and make it impossible to clash with something in the "db" package.