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

Wednesday 03 October 2007 11:55:26 pm - 3 replies

Modified on Wednesday 03 October 2007 11:56:11 pm by Piotrek Karaś

3 replies

Author	Message
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

Author

Message

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

You must be logged in to post messages in this topic!