Bugzilla/Notes/164

bugzilla:164: Support collation by a certain locale

Proposed solution

 * bugzilla:164, bugzilla:164, bugzilla:164, bugzilla:164, bugzilla:164: use MySQL collation
 * contact MySQL developer team to add specific collation on UTF-8
 * set default collation to utf8_bin
 * send specific collation to MySQL query, mysql> SELECT ... ORDER BY cl_sortkey COLLATE utf8_czech_ci LIMIT 201;

Drawback

 * bugzilla:164, bugzilla:164: The supported collations will depend on the database engine.
 * need MySQL >= 4.1 to support COLLATE keyword in sorting.
 * bugzilla:164: This requires major database alterations and possibly a massive, potentially breaking MySQL upgrade. (This is specific to Wikimedia projects, not to the MediaWiki software).
 * bugzilla:164, bugzilla:164: still slow if it is sorted using the collation that not match the default collation of that table/column (FIXME: need confirmation)

Proposed solution

 * bugzilla:164: use libc's LC_COLLATE on GNU/Linux
 * bugzilla:164: use ICU4C/libicu (FIXME: Is this support sort key generation, so it can be hybrid?)
 * bugzilla:164: use PHP Internationalization extension (Intl), an ICU wrapper for PHP (FIXME: Is this support sort key generation, so it can be hybrid?)

Drawback

 * bugzilla:164: MediaWiki will need to fetch all page title (for that category) from database to do sorting.

Problem

 * bugzilla:164: LC_COLLATE not work in PHP's sort (See )

Hybrid database/PHP level sorting
(bugzilla:164: PHP level for key generation and database level for sorting)

Proposed solution

 * bugzilla:164: languages plug in a filter to generate sort keys from thier text.
 * bugzilla:164, bugzilla:164, bugzilla:164: normalize the input string using Unicode normalization form NFKD ("canonical decomposition")
 * bugzilla:164: This doesn't solve Thai sorting.
 * bugzilla:164, bugzilla:164, bugzilla:164: use Unihan database for Japanese
 * bugzilla:164, bugzilla:164: use Thai sorting algorithm to generate sort key for Thai language

Drawback

 * Need to write code for key generation on each language (FIXME: Not sure if ICU support this key generation. If yes, this drawback will not be the case)

Multilingual sorting
This section will discuss whether, we should implement the "per language collation" or "universal multilingual collation". The latter is to implement the ability to sort the phrase that mix many languages in one phrase, for example,


 * Help Hjælp ヘルプ 帮助 Помощь วิธีใช้
 * Help Hjælp Помощь วิธีใช้ ヘルプ 帮助
 * discussion diskussion ‐ノート 对话 Дискуссия คุย
 * discussion diskussion Дискуссия คุย ‐ノート 对话

With multilingual collation, then no need to specify the collation in any specific language.

Proposed client interface

 * bugzilla:164: add something like, Category-sort-option:en-GB, Category-sort-option:ja, Category-sort-option:th, to category page
 * bugzilla:164: set collation in MediaWiki config, $wgDBcollation
 * bugzilla:164
 * set collation in cgi variable [ http://.../Category:Abc?collation=czech_ci] and Special:Preferences
 * fetch all available collations from, mysql> show collation like 'utf8_%';, putting them in a dropdown list in Special:Preferences.

Appendix A: LC_COLLATE and PHP sort
From experiment with php 5.0.5-2ubuntu1.8 in Ubuntu GNU/Linux, setting LC_ALL or LC_COLLATE doesn't affect php5 sort function.

$ LC_ALL=C php5 -r '$a = array("a", "b", "A", "B"); sort($a, SORT_LOCALE_STRING|SORT_STRING); foreach ($a as $b) { print "$b\n"; }' A B a b $ LC_ALL=en_US php5 -r '$a = array("a", "b", "A", "B"); sort($a, SORT_LOCALE_STRING|SORT_STRING); foreach ($a as $b) { print "$b\n"; }' A B a b $ LC_COLLATE=en_US php5 -r '$a = array("a", "b", "A", "B"); sort($a, SORT_LOCALE_STRING|SORT_STRING); foreach ($a as $b) { print "$b\n"; }' A B a b

While it work in "sort" program,

$ (echo a; echo b; echo A; echo B) | LC_ALL=C sort A B a b $ (echo a; echo b; echo A; echo B) | LC_ALL=en_US sort a A b B $ (echo a; echo b; echo A; echo B) | LC_COLLATE=en_US sort a A b B