PDA

View Full Version : Sorting of national characters



strindberg
2007-09-05, 04:13
I am running Slimserver 6.5.4 under Ubuntu 7.04.

I am trying to figure out how to make Slimserver sort national characters according to a specified preference. For instance, in Swedish, the characters å, ä and ö should be sorted at the end of the alphabet. This means that an album called "Årsringar" should come after albums on Z, not as it does now, under A.

Does anyone know how to make Slimserver behave this way under Ubuntu? I tried a very simple solution, and made the file "/etc/default/locale" look like this:

LANG="en_US.UTF-8"
LC_COLLATE="sv_SE.UTF-8"

but with no luck, this does not change anything.

Does anyone have a suggestion?

Jesper

p.s. This is what it says under Server settings:

SlimServer Version: 6.5.4 - 12407 - Debian - EN - iso-8859-1
Server IP address: 192.168.1.8
Perl Version: 5.8.8 i486-linux-gnu-thread-multi
MySQL Version: 5.0.38-Ubuntu_0ubuntu1

strindberg
2007-09-05, 04:28
I should perhaps clarify that what I am interested in is the listing of tags, such as albums or artists, in the right order, and that this problem is not the same as the struggling with unicode file names as some users have reported.

Tuor
2007-09-11, 13:46
The sorting is done by MySQL.
To get proper sorting you have to modify the MySQL database.
Something like this should do the trick for you:

-------------------------------------------------------------
#/> mysql -u slimserver -p
<enter dbpassword found in /etc/slimserver.prefs>

USE slimserver;
ALTER TABLE contributors MODIFY namesort text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE contributors MODIFY namesearch text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE albums MODIFY titlesort text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE albums MODIFY titlesearch text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE tracks MODIFY titlesort text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE tracks MODIFY titlesearch text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE genres MODIFY namesort text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE genres MODIFY namesearch text CHARACTER SET latin1 COLLATE latin1_swedish_ci;
------------------------------------------------------------
If you have a database administration tool like phpMyAdmin you can change these settings using that instead.

It's a pity we don't get to choose the sort order when we install SlimServer.
(It's possible that this actually works out of the box if you have specified a default collation in your MySQL server prior to the SlimServer installation)

strindberg
2007-09-12, 00:05
Great! Thanks for the tip, it works very well. I changed it to specifiy utf8 instead of latin-1:

ALTER TABLE contributors MODIFY namesort text CHARACTER SET utf8 COLLATE utf8_swedish_ci;
ALTER TABLE contributors MODIFY namesearch text CHARACTER SET utf8 COLLATE utf8_swedish_ci;
ALTER TABLE albums MODIFY titlesort text CHARACTER SET utf8 COLLATE utf8_swedish_ci;
ALTER TABLE albums MODIFY titlesearch text CHARACTER SET utf8 COLLATE utf8_swedish_ci;
ALTER TABLE tracks MODIFY titlesort text CHARACTER SET utf8 COLLATE utf8_swedish_ci;
ALTER TABLE tracks MODIFY titlesearch text CHARACTER SET utf8 COLLATE utf8_swedish_ci;
ALTER TABLE genres MODIFY namesort text CHARACTER SET utf8 COLLATE utf8_swedish_ci;
ALTER TABLE genres MODIFY namesearch text CHARACTER SET utf8 COLLATE utf8_swedish_ci;

I will write a bug report / feature request about this. Selling the Squeezebox to my music-interested but not-computer-savvy friends will not be helped by me telling them they need to manually change database tables.

Tuor
2007-09-12, 00:27
You're absolutely right.
UTF-8 should be used instead of latin1.