Enum: use ordinal position instead of value

[marc-mabe]

Hi - nice to see my enumeration impl. here :)

I would expect to store the ordinal position of an enumerator instead of the value because of:

• the enumeration impl. supports all scalar types but database types can't
• the enumeration impl. differs values by strict comparison ("1" !== 1) which can result in issues on convert all values to strings on writing to DB
• less DB resources
• opens possible support for DB type EnumSet

You also can add an optimization by automatically detect the best numeric type for the given enumeration by the number of members.

Special case MySQL:
As of MySQL natively supports enumerations it also possible there to use the ordinal number but you have to know of the the following:

• if enum definition in MySQL contains an empty string the ordinal number starts with 0 else the ordinal number starts with 1 because MySQL auto adds an empty string as first possible value and will use it if you write an invalid value to it
• MySQL only supports up to 256 member as it's using an unsigned tinyint internally

Marc

[dbojdo]

Hi!
Problem with ordinal is you can easily lose a consistency between Enum and a database (just because somebody add a new value between existing one or change an order of constants to alphabetical for example). It's quite dangerous.

Problem of comparison has been fixed (https://github.com/DXI-Ltd/doctrine-extension/blob/master/src/Enum/EnumDBALType.php#L84 - not the best solution - especially when you have lot of values to iterate through - but it works).

I think the good idea is to rely on constants' names as default as we avoid ordinal problem and comparison as well.

I wonder, if we can provide a way to give a developer possibility to decide, what should be used (ordinal, name or value) and create a proper db field according to his choice.

[marc-mabe]

Problem with ordinal is you can easily lose a consistency between Enum and a database (just because somebody add a new value between existing one or change an order of constants to alphabetical for example). It's quite dangerous.

Sorry, but I don't agree in that. If you define an enumeration you define everything of it incl. order and if you change that definition in any case you have to deal with that it can break backward comparison.
So for this case it doesn't matter if you change the order, add a new enumerator, change the name or the values.

Problem of comparison has been fixed (https://github.com/DXI-Ltd/doctrine-extension/blob/master/src/Enum/EnumDBALType.php#L84 - not the best solution - especially when you have lot of values to iterate through - but it works).

This doesn't work: Assume you have the following enumeration:

class MyEnum extends Enum {
    const UNDEFINED = 0;
    const FIRST = 'first';
    const SECOND = 'second';
}

With your comparison you will ever get UNDEFINED from DB as non-strict comparison of 0 == "string" will ever be true.

I already had some special optimized mysql table structures for a extreme high performance application (adserver) - there it was very handy so deal with sets in one table with a defined enumeration of another table (Not a real world example):

rule

id	rule enum(reader, editor)
1	reader
2	editor

user

id	rules set(reader, editor)
1	reader
2	reader,editor

This table definition only works with MySQL but if you store the enumerations as integers based of the ordinal number all databases supports it and you can write SQLs like this to add an enumeration value of the one table to the set of the other table:

UPDATE user
/* JOIN SOMETHING */
SET user.rules = user.rules | rule.rule
WHERE /* condition */

I wonder, if we can provide a way to give a developer possibility to decide, what should be used (ordinal, name or value) and create a proper db field according to his choice.

This would be awesome :)