Re: ENUM type

Jim C. Nasby wrote:

>OK, but compare the amount of work you just described to the simplicity
>of using an enum. Enum is much easier and simpler for a developer. Of
>course in most cases the MySQL way of doing it is (as has been
>mentioned) stupid, but done in the normal, normalized way it would
>remove a fair amount of additional work on the part of a developer:
>
>- no need to manually define seperate table
>- no need to define RI
>- no need to manually map between ID and real values (though of course
> we should make it easy to get the ID too)
>
>
>
Again, automating this process is the only way I can see this done in a
normalized way. I think that having type definitions (enum options) in
the table definition is in general a very bad idea. A simple option
would be to have it be a VARCHAR referencing a single column VARCHAR
table with a primary key on the VARCHAR column. Not as
storage-efficient as an int, but better at compatibility.

>
>Hopefully someone on -hackers can shed light on what's required to clean
>up the parsing. One thing worth noting though, is that table definition
>is a relatively small part of doing a migration. Generally, it's
>application code that causes the most issues. Because of this, I think
>there would still be a lot of benefit to an enum type that didn't
>strictly follow the mysql naming/definition convention. In this case, it
>might be much easier to have an enum that doesn't allow you to define
>what can go into it at creation time; ie:
>
>CREATE TABLE ...
> blah ENUM NOT NULL ...
>...
>
>ALTER TABLE SET ENUM blah ALLOWED VALUES(1, 2, 4);
>
>
What about the possibility of using a domain... One could alter the
code using conversion tools....

Something like:
CREATE TABLE table_name (
val_name ENUM(option1, option2, option3) NOT NULL,
);

would be rewritten to:
CREATE DOMAIN table_name_val_name_enum AS VARCHAR DEFAULT NULL CHECK IN
('option1', 'option2', 'option3');
CREATE TABLE table_name (
val_name table_name_val_name_enum NOT NULL,
);

This could be added to the mysql2pg scripts.

Best Wishes,
Chris Travers
Metatron Technology Consulting