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:

CREATETABLE `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:

typeTestuserTypestringconst ( NTestuserType="N"YTestuserType="Y" ) typeReadonlyTypestringconst ( NReadonlyType="N"YReadonlyType="Y" )

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

The new MySQL package generates the following:

typeReadonlystringconst ( ReadonlyNReadonly="N"ReadonlyYReadonly="Y" ) typeStatusstringconst ( StatusActiveStatus="active"StatusInactiveStatus="inactive"StatusBlockedStatus="blocked" ) typeTestuserstringconst ( TestuserNTestuser="N"TestuserYTestuser="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.