Enum type

[Former user]

It would be great to have a standard Enum type that used a database's native ENUM type if available, otherwise it could fallback to using a VARCHAR.

[Former user]

Patch to trunk (@r4948) to provide Enum column type

[jek]

Would love to see generic enums w/ Postgres 8.3 ENUM support as well.

[Mike Bayer]

• the "unicode" and "ascii" flags need to become something more coherent and non-MySQL specific, like "encoding=XXXX".
• the Text/Unicode constructors should just be String(length,convert_unicode=True|False). "Text" is going to generate as CLOB on oracle.
• un-it-tests !

[jek]

agreed, i would drop the unicode/ascii; encoding= and collation= would be sufficient & sql standard.

[jek]

a postgres enum implementation would differ significantly; i think that it needs to be ironed out first to ensure that the generic type can work with it.

bumping the severity a bit- my hunch is that PG ENUM + the generic type will require some refactoring of the ddl execution environment.

[Former user]

PGEnum would be very different from MSEnum.

In postgresql, every enum is a datatype for which SQLAlchemy must issue a {{{CREATE TYPE}}} DDL statement.

An equivalent for other databases would be a {{{TypeDecorator}}} that maps enum constants to a {{{SmallInteger}}}.

This equivalent would be useful for MySQL too, for users who want their enum not to act like a string that truncates or remaps data in various ways (NULL, {{{''}}}, case-equivalents, integers) dependent on server version and sql mode connection settings.

[Former user]

I'm interested in doing a patch, first PGEnum then Enum.

The DDL requirement means introducing some coupling between UserDefinedType and MetaData.

A {{{PGType.add_to_metadata()}}} method, and for convenience the constructor, will take a MetaData parameter and register a before-create/after-drop listener.

But to enforce that the type really exists where it is used, it would be nice to have the code that creates the table (presumably I'll grep for get_col_spec) notify the type with the table. Then PGEnum can require that the table's metadata matches one that was registered previously.

Does {{{AbstractType.before_create_col(self, table)}}} sound good?

[Former user]

And here is a patch with PGEnum and generic support for schema-defined types. PGEnum is supported in table colspecs and reflection.

The generic {{{SchemaDefinedType}}} support could be used to implement PGComposite and PGDomain in the future.

I skipped the metadata validation part ({{{exists_in_schema}}}, a sanity check to ensure the type is defined in the table's metadata). It is defined, but I haven't found a good place to call it.

To test, type:

nosetests --db=postgresql test.dialect.test_postgresql:SchemaDefinedTypesTest -v

[Former user]

Oh yes, apply that to @8e551942c0b5fc489ce6411cbbbffc3d4066dc46 .

[Former user]

PGEnum patch

[Former user]

Whoops, did this fall under the cracks?

[Former user]

(through)

[Mike Bayer]

this is SO freaky as I just started working on a generic Enum type, which would build upon #1511. Like, literally in the past 30 minutes. I also wonder why #1511 was created since it seems to be a continuation of this ticket. Anyway if the Enum is built as a schema aware concept, I'm experimenting with using schema events to allow enums on PG and any platform that allows check constraints.

[Former user]

That was the author of #1511 (got my feeds confused), we can continue discussing over there.

[Mike Bayer]

the generic version is committed in aa557982fa2518e6d520ce17894093d5ed03c0eb, implemented for PG and MySQL so far. a VARCHAR version that creates constraints is straightforward.

See #1511 for comments on the PG aspects of the patch.

[Mike Bayer]

Enum type now works using VARCHAR + CHECK constraint for all other platforms in eb9763febe58655ca0f61fa758925c56b94ece9b.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)