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.