Real enum support

[oojacoboo]

We've been avoiding enums in Propel, and sticking with varchar types in our schema, due to the inadequacies of Propel and enums. Basically, Propel assumes you'll store your enums in the database as integers. We've never done that, nor would we. Enums are more powerful as strings, at least if you ever need to read a database yourself. Unfortunately, Propel doesn't support this currently. Additionally, it doesn't generate an actual enum in the model, as it should. Or, it should accept a pre-defined enum, and use it.

[kasparsatke]

That is unfortunate indeed & I would like to have real ENUMs as well. I assume the reason is maximum compatibility of Propel's internal data types with the vendor ones.

E.g., SQLite doesn't have an ENUM type. @mringler How did you approach it with UUID as SQLite doesn't have this type as well?

And furthermore, how should we deal with different vendor (type) compatibilities in the future?

[mringler]

Yes, I agree, the handling of ENUM and SET types is indeed very unfortunate. Kaspar is probably correct, the initial idea was to be independent of vendor, but it creates more problems than it solves. For me, the big issues with the current approach is that what happens is neither expected nor apparent, and even worse, I think the mapping between numeric values used in DB and the corresponding string values used on PHP side is generated from schema.xml without updating data, so changes to the list of values can royally screw up the data you get from ORM.

You can work around the whole thing by using the sqlType attribute:

<column name="status" sqlType="SET('granted','denied','in_submission','deleted')" />

but of course you have to be aware of the issue in the first place and it feels wonky that you have to "unlock" the expected behavior through an override.

With UUIDs, the vendor-specific platform classes will determine the actual column data type and whether manual transformation between binary and string format is necessary. Same approach would make sense for ENUM/SET too. It is possible to automatically create migration code from INTEGER based ENUM/SET columns to native type columns to make the change pretty much backwards compatible. Quite a bit of work though...

What would you say is the best way to change ENUM/SET behavior in regards to backwards compatibility?

[oojacoboo]

The only thing that comes to mind, is a setting or a new column attribute.

There is also this behavior FWIW:
https://github.com/Effenti/propel-real-enum-behavior

[mringler]

The cleanest approach probably adds two new column types, ENUM_VIRTUAL and ENUM_NATIVE (same for SET), where the virtual type uses the current approach and the native one uses DB column type (if available, otherwise defaults to the virtual type).

At that point, the current ENUM type is basically an alias for ENUM_VIRTUAL, which I think would still be a problem, as it is still not what I would expect, still easy to miss, and still possibly dangerous. I think I would want to change that, too.

Do you think users could handle that?
Also, is there a better name than _VIRTUAL and _NATIVE?

[oojacoboo]

Those are fine. Could also use ENUM_SYNTHETIC.

ENUM type would just be deprecated, then. And in the build process for models, if it's use is detected, a notice is thrown. Then, after a couple versions, you can move to a hard deprecation, which removes support for the ENUM type. After, further down the road, can eventually add ENUM back, which defaults to ENUM_NATIVE.

I'd also like to express the desire for phpType to support an existing enum, that the models would use, instead of generating one.

[kasparsatke]

@mringler
That is actually a nice idea to play it safe - because in the last hours I had some second thoughts about compatibility and how to solve them. I have been concerned about that:

1. more vendors don't really have enums like MSSQL
2. even if we achieve enum-like behaviour through workarounds like constraints or even lookup tables in unsupported vendors
3. the actual advantages of enums (speed, data (size) sparsity & enum definition integrity) would not be present with those hacks
4. reversing a database to scheme might be even more error prone

So although your two-fold approach cannot solve the actual problems of the different vendor implementations, it still enables both reliable behavior and expected compatibility behavior on the one hand as well as progressive enhancement for MySQL/MariaDB and PostgreSQL on the other hand.

As for the current Propel ENUM type, we could also give it aliases like

ENUM_SAFE
ENUM_COMPATIBLE
ENUM_MAPPED

[kasparsatke]

As for the fallback, I would combine your two approaches but actually from backwards.

I believe normal users expect the framework to do the magic behind the scenes and don't complicate it upfront but only afterwards if necessary. So I would implement it like this:

So I would just keep the name ENUM for now as it is but actually make it an alias of the actual current type which should an 'internal' type and renamed to _virtual, _synthethic, _compatible, _safe, _old or what ever. Then on the other hand, there is the new native type.

Although both internal types old and new can be used directly in the schema, the normal user would still use ENUM which is a mapped alias.

Mapping decision can (both) be made:

• via a config switch (nice for future deprecation of the old type)
• via the vendor - which we already have via the propel.yaml and sometimes even the scheme.xml

So we would aim both for maximum magic for normal users and maximum possibilities for experienced users 🚀