UTF-8, collate, fetch & sort with native characters

Author Message

Piotrek Karaś

Wednesday 03 October 2007 11:55:26 pm

Hello everyone after a bit of a break :)

Can anyone give any tips as to how to deal with sorting of the fetched elements, <b>when native characters come into play</b>? Polish language, for instance, uses nine native characters (ąśżźćńłęó). How do I make eZ Publish to sort elements like this:
(...)
o-word
ó-word [o acute]
p-word
(...)
MySQL provides <b>utf8_polish_ci collation</b> - will that do the trick?
What about multi-language installations? Is there a way to force collation based on a siteaccess for example?

Of course, we're talking about UTF-8-based DB.

Any help/suggestions appreciated.
Thanks.

--
Company: mediaSELF Sp. z o.o., http://www.mediaself.pl
eZ references: http://ez.no/partners/worldwide_partners/mediaself
eZ certified developer: http://ez.no/certification/verify/272585
eZ blog: http://ez.ryba.eu

Kristof Coomans

Thursday 04 October 2007 7:32:33 am

Hi Piotrek

As far as I know changing the collation will indeed affect your sorting results. But it's not possible to force collation based on site access at this moment. Maybe executing some SQL on initialization of the database can make it possible, but I didn't investigate this yet.

independent eZ Publish developer and service provider | http://blog.coomanskristof.be | http://ezpedia.org

Piotrek Karaś

Thursday 04 October 2007 9:11:02 am

Yup, you're right. Preinstallation collation setting does the trick - I've just finished several tests on utf8_general_ci against utf8_<i>native</i>_ci for the entire database.

Too bad there's no native eZ Publish ability to relate collations to siteaccesses and/or language settings for MySQL db. Yes, a global setting for the moment of connecting to the database would be very useful and easy to implement - otherwise it seems like most of the queries where collation setting is required would have to be extended.

Thanks.

--
Company: mediaSELF Sp. z o.o., http://www.mediaself.pl
eZ references: http://ez.no/partners/worldwide_partners/mediaself
eZ certified developer: http://ez.no/certification/verify/272585
eZ blog: http://ez.ryba.eu

Piotrek Karaś

Friday 05 October 2007 11:17:28 am

I dug a bit, did some experimenting, unfortunately no results. MySQL seems to unfailingly adhere to the priorities of server->database->table->column default collation override system and I haven't found a way to make it change its behavior by any SQL request upon establishing DB connection. Query rewriting seems inevitable for my purpose and I really hope I am wrong about it.

Anyone with greater MySQL experience?
Thanks

--
Company: mediaSELF Sp. z o.o., http://www.mediaself.pl
eZ references: http://ez.no/partners/worldwide_partners/mediaself
eZ certified developer: http://ez.no/certification/verify/272585
eZ blog: http://ez.ryba.eu

Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.