Hi Glenn,
I think the answer is "No". The rationale is that collations/charsets are handled really different by every RDBMS and, although the issue has raised various times in the past, I think our "agnostic" position is the correct, avoiding to enter in that minefield completely.
So I think that you'll have to perform any change in that area following the documentation for your RDBMS. Moodle will, simply apply the defaults always.
Hope it helps, ciao
Hang on Eloy, the whole point of XMLDB is to hide the evil inconsistencies between what you need to do to define database tables in all the different DBs.
And, if you recall MDL-29332, you will remember that is is pretty much inevitable that will will have to tackle collations at some point.
The nub of the problem is that if you want locale-senstive sorting in MySQL, then you have to specify a case-insensitive collation. And, if you specify a _ci collation, then MySQL thinks that 'x' = 'X' which is a horrible problems for columns like idnumber, username, role.shortname, calculated question type variable names, ...
In the discussion we had (some time ago) I though we had agreed this approach:
100% agree, what else can I say.
But that does not include to "set or update the collation of a specific 'text' column with the XMLDB format", that was the Q above.
Personally I'm using binary collations everywhere since ages ago (yes I know it breaks some ordering here and there). But any collation breaks ordering, for people using other languages, so no big difference for me (English/Spanish).
And yes, I think that the sql_orderby() (or whatever we call it) is something to implement and apply soon (2.3, yay!?) to courses/users lists. I'm sure there are not thousands of uses really requiring it.
Also, don't forget that there was some problem for PostgreSQL, so the collation only could be used for ordering starting with 9.x (if I'm not wrong).
Anyway, in summary, yes, there are plans to provide better (and dynamic) collation-cross-db compatible sorting. But, no, there are no plans to allow to specify/change any collation from the XMLDB definitions/stuff.
Thanks for reviving the thing Tim, ciao
Good. sql_order_by in 2.3 would be great.
To clarify your penultimate sentence, what we are proposing is that, on install, as part of the environment checks, Moodle will check that the whole database is set to a sane (case-sensitive) collation.
Thanks Eloy. For what its worth, that tracker ticket Tim referenced is the reason I was looking for the information. I just placed a $DB->execute_sql() after the create_table call in the upgrade script. Not very pretty but it seems to have done the trick for us.
Thanks again.
