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