since Moodle 1.7 (when the new Moodle DB layer was introduced, adding unified support for MySQL, PostgreSQL, MSSQL and Oracle) there is one (tiny) part which support has been far from perfect.
I'm talking about enums (those check constraints allowing to specify a list of valid values in one column of the DB).
Problems have been various and solutions to handle / workaround them, just that, workarounds.
This weekend I've been spending some hours reviewing the problems, as last chance to check if there is some viable solution to implement them properly under the new, improved, Development:DB layer 2.0 that will arrive with Moodle 2.0. Here there are the main problems:
- Different implementations on each DB: While MySQL implements them as "enum fields" the rest of DB implement them as "column check constraints".
- Complexity to retrieve them from DB Dictionary. It's easy to get the current values from one enum in MySQL, but far complex to do the same under other DBs (complex queries returning some SQL expressions that need to be parsed - regexp - to get the list of values being the enum).
- Added complexity for any DDL operation in those fields. If we try to perform any modification in those fields (cahnge name, length... whatever, we need to search against DB dictionary for any potential enum, drop it, perform the change in the field and recreate the enum). Same if we want to change the allowed values in the enum.
With all this, the needed code to handle those enums properly would be really complex (and potentially unestable, as long as internal representations of those enums can change).
Also, we are only using enums in 7 fields in all the DB, and never relaying in DB returning errors for those enums / check constraints. Everything is checked at the application (PHP) level, so check in DB can be considered redundant.
So, summarising, this discussion is about to propose to drop completely enums support from Moodle DB Layer (for Moodle 2.0), by:
1) Transforming all (7) current enums to "normal" varchar fields (upgrade step in 1.9 => 2.0).
2) Drop enums from all the install.xml files (installation)
3) Drop enum support from XMLDB and Dictionary Moodle DB functions.
4) Document it properly (in 2.0 release Docs, in DB Layer 2.0 Docs and in contrib-related Docs.
And that's all. I really think it's the best solution for upcoming Moodle releases and one less thing (a tiny one but causing a lot of problems) to be worried about.
Ciao
