##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-5-7_to_0-5-8.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2007-05-24															#
 # Updated:		2007-05-28															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.5.7 to 0.5.8														#
 #----------------------------------------------------------------------------------##

# Fix 0.5.7's upgrade script's incorrect assignment of `sort_tracks` and `sort_albums` for all `perms` when it should have just been the base (`id` = '1')
UPDATE `mm_admin`.`perms` SET `sort_tracks` = '{"Relevance":1,"Library":0,"Volume":1,"Track":0}', `sort_albums` = '{"Library":0,"Volume":1}' WHERE `id` = '1';
UPDATE `mm_admin`.`perms` SET `sort_tracks` = NULL, `sort_albums` = NULL WHERE `id` != '1';

# Reset all asset permissions so that they include the now required 'column' entry (should have actually been done in 0.5.7 - fixes resultant bugs)
UPDATE `mm_admin`.`perms` SET `assets` = '{"column":"Database","new":1,"items":[]}' WHERE `id` = '1';
UPDATE `mm_admin`.`perms` SET `assets` = '{"column":"Database","new":2,"items":[]}' WHERE `id` IN ('3', '8');
UPDATE `mm_admin`.`perms` SET `assets` = NULL WHERE `id` NOT IN ('1', '3', '8');

# Now that Artwork and Waveform are two new derived columns, return _PictureLink and _WaveformLink back to normal system columns
UPDATE `mm_admin`.`columns` SET `display` = `id`, `active` = '0', `browse` = '0' WHERE `id` IN ('_PictureLink', '_WaveformLink');
INSERT INTO `mm_admin`.`columns` (`id`, `display`, `active`, `browse`) VALUES ('Artwork', 'Artwork', '1', '0'), ('Waveform', 'Waveform', '1', '0');
UPDATE `mm_admin`.`columns` SET `active` = '1', `browse` = '1' WHERE `id` = 'Volume';
UPDATE `mm_admin`.`columns` SET `active` = '1' WHERE `id` IN ('Track', 'Index');

# Update `users`.`columns` to replace references to _PictureLink and _WaveformLink with the new derived columns Artwork and Waveform
UPDATE `mm_admin`.`users` SET `columns` = REPLACE(REPLACE(`columns`, '_PictureLink', 'Artwork'), '_WaveformLink', 'Waveform');

# Update `perms`.`columns` for Base permissions to include and allow new derived Artwork and Waveform columns and disallow by default old _PictureLink and _WaveformLink columns
UPDATE `mm_admin`.`perms` SET `columns` = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`columns`, ':{"', ':{"Artwork":1,"'), '1}}', '1,"Waveform":1}}'), '0}}', '0,"Waveform":1}}'), '"_PictureLink":1', '"_PictureLink":0'), '"_WaveformLink":1', '"_WaveformLink":0') WHERE `id` = '1';

UPDATE `mm_admin`.`schema` SET `db_ver` = '0.5.8', `mm_ver` = '0.5.8', `sm_ver` = '0.5.7';
UPDATE `mm_cache`.`schema` SET `mm_ver` = '0.5.8';
UPDATE `mm_logs`.`schema` SET `mm_ver` = '0.5.8';