For a long time now I've named my genre with prefixes so that I could
keep the list in chronological order: e.g.
a_Medieval
b_Renaissance
etc.
A recent fix to 6.5.3 and to the trunk by Triode which used an sql
query as part of the db scan optimization phase got me thinking that I
could write a query to "fixup" my genre names after a scan. This is
the query I'm using now:
Code:
--------------------
ALTER TABLE `slimdatatrunk`.`genres` MODIFY COLUMN `name` TEXT DEFAULT NULL;
UPDATE genres
set name = CASE when name = 'A_Medieval' then 'Medieval'
when name = 'B_Renaissance' then 'Renaissance'
when name = 'C_Early_Baroque' then 'Early Baroque'
when name = 'D_English_Italian_Baroque' then concat('Italian ', char(38), '
English Baroque')
when name = 'F_French_Baroque' then 'French Baroque'
when name = 'E_Spanish_Latin_American_Baroque' then concat('Spanish ',
char(38), ' Latin-American Baroque')
when name = 'G_German_Baroque' then 'German Baroque'
when name = 'H_Classical' then 'Classical'
when name = 'I_Romantic' then 'Romantic'
when name = 'J_Post-Romantic' then 'Post-Romantic'
when name = 'K_Post-Romantic-Nationalist' then 'Post-Romantic Nationalist'
when name = 'L_Modern_Central_European' then 'Central-European Modernism'
when name = 'M_Modern_German' then 'German Modernism'
when name = 'N_Modern_French' then French Modernism'
when name = 'O_Modern_Russian' then 'Russian Modernism'
when name = 'P_Modern_Scandinavian' then 'Scandinavian Modernism'
when name = 'Q_Modern_Italian-Spanish' then concat('Italian ', char(38), '
Spanish Modernism')
when name = 'R_Modern_Latin-American' then 'Latin-American Modernism'
when name = 'S_Modern_American-English' then concat('English ', char(38), '
American Modernism')
when name = 'T_Post Modern Contemporary' then 'Contemporary Post-Modernism'
when name = 'U_Post Modern Aleatoric' then 'Aleatoric Post-Modernism'
when name = 'V_Post Modern Minimalist' then 'Minimalist Post-Modernism'
when name = 'W_World_Classical' then 'World Classical'
Else name
End ;
ALTER TABLE `slimdatatrunk`.`genres` MODIFY COLUMN `name` BLOB DEFAULT NULL;
--------------------
The order that the genres get displayed on the slimserver web and
squeezebox interfaces doesn't change because that's controlled by the
'namesort' column. This is the batch file I fire off as part of my
wipe & rescan routine:
Code:
--------------------
cd "C:\Program Files\SlimServerTrunk\server\SQL\mysql"
rem fixup Genre names
"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe" --user=slimserver
--password=slimserver --database=slimdatatrunk --execute="\. fix_genre.sql"
--------------------
I'm running the trunk svn code on a windows xp box as a service with a
separate instance of mysql. I would think that it wouldn't be too hard
to modify this technique to work with 6.5.2 with the bundled mysql.
Perhaps you are happy with your genre names but don't like the order in
which they get displayed...then you could modify the query so that it
works on the 'namesort' field.
This is one of the things I was looking forward to doing when
Slimserver first went to SQLLite and then MySQL. I'm surprised it's
taken me this long to implement it for myself. I'd be interested in
hearing if other folks are doing any "post-processing" of the slimsever
db.
--
gharris999
------------------------------------------------------------------------
gharris999's Profile: http://forums.slimdevices.com/member.php?userid=115
View this thread: http://forums.slimdevices.com/showthread.php?t=35876
_______________________________________________
ripping mailing list
[email protected]
http://lists.slimdevices.com/lists/listinfo/ripping
