WL#744: CREATE CHARACTER SET and CREATE COLLATION

Affects: Server-7.1   —   Status: Assigned

Extend MySQL to understand collation names from other databases:
oracle, ms sql, etc. MySQL may not provide exactly the same sorting
order, but it should be able at least to understand names and
map them into the most close collations.

We need this to:
1. Understand other databases dumps
2. Produce dump other databases can understand.

This can be done by implementing standard SQL feauture:
CREATE CHARACTER SET and CREATE COLLATION, which, among
other features, can be used for creating aliases.

It's easy with MS SQL. It does use COLLATE syntax.
Oracle has a different system: NLS

Related to dev-private discussion "Identifier equivalence and events":
"Identifiers are equivalent if their upper-case forms are
equal according to a binary collation with space padding."
And BUG#22034.

Note: See WL#3574 "Online backup: SHOW commands for various metadata"
and WL#3582 "Online Backup: Add SHOW commands for needed metadata".
When implementing these statements, remember to also implement
SHOW CREATE CHARACTER SET and SHOW CREATE COLLATION.

Perhaps CREATE COLLATION could have the same clauses as WEIGHT_STRING(),
or CASE SENSITIVE / CASE INSENSITIVE and ACCENT SENSITIVE / ACCENT INSENSITIVE
clauses, similar to Firebird.

Perhaps CREATE COLLATION could have a way to specify its STRENGTH,
i.e. how many levels to use for comparison. The default strength
should be 1. But some languages need strength 2 (e.g. Hungarian).

Perhaps CREATE COLLATION could have a way to specify how to treat
variable weighting characters:
- Blanked
- Non-ignorable
- Shifted
http://www.unicode.org/reports/tr10/#Variable_Weighting

Perhaps CREATE COLLATION could specify which level should case
difference be on: either on the third level, or mixed with the
second level weights. The second approach makes comparison
and sorting faster. This is what MySQL does with cp1250_czech_cs:
   http://www.collation-charts.org/mysql60/mysql604.cp1250_czech_cs.html
This is what Oracle does in what it calls "monolingual" sorts:
   http://www.collation-charts.org/oracle10g/ora10g.EE8MSWIN1250.XCZECH.html