PostgreSQL : Support Enum persisted as datastore enum

[andyjefferson]

Support persisting of Java enums as database enums.

PostgreSQL: http://www.postgresql.org/docs/9.3/static/datatype-enum.html MySQL: https://dev.mysql.com/doc/refman/5.0/en/enum.html

Not supported by all DB's though. Firebird, H2, SQLServer, Oracle for example do not have enums.

The preferred handling is to use a CHECK constraint on a VARCHAR column, as per https://stackoverflow.com/a/9366855/8558216 and DataNucleus already supports those via the following extension "enum-check-constraint" specified on the ColumnMetaData.

[andyjefferson]

For PostgreSQL this would be CREATE TYPE my_gender AS ENUM ('Male','Female') Create a table using this type, like this CREATE TABLE PERSON (ID INT NOT NULL, GENDER my_gender NOT NULL); Insert into the table INSERT INTO PERSON (ID, GENDER) VALUES (1, 'Male');

To implement this we need to be able to annotate an Enum with something like

@EnumNative("my_gender")
public enum Gender
{
    MALE,
    FEMALE
}

and then at schema generation time it would need to look at the Enum and see the type name and issue CREATE TYPE my_gender AS ENUM ('MALE', 'FEMALE') and subsequently any usage of that Enum needs to use 'my_gender' SQL type.

For the sake of completeness, for MySQL this would be

CREATE TABLE MY_TABLE (
    ...
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

and insert would be INSERT INTO MY_TABLE (... , size) VALUES (... ,'large'), (... ,'medium'), (... ,'small'); BUT that is effectively the same as using ANSI standard "CHECK IN (...)" so we will not support it there, just for PostgreSQL.