##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_1.2.0.sql									#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2008-11-19															#
 # Updated:		2009-11-10															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2008-2009 Soundminer Inc.									#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		1.1.9.9 to 1.2.0													#
 #----------------------------------------------------------------------------------##

#-----------------------------------------------------------------------------
# Migrate `users`.`state` column
#-----------------------------------------------------------------------------

# Add state flags and `expires` to `users` table
ALTER TABLE `mm_admin`.`users`
 ADD COLUMN `deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag - user account has been deleted.' AFTER `type`,
 ADD COLUMN `enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Flag - user account is enabled.' AFTER `deleted`,
 ADD COLUMN `registering` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag - user is in the process of registering.' AFTER `enabled`,
 ADD COLUMN `expires` DATE DEFAULT NULL COMMENT 'Date account expires and becomes disabled. NULL = never.' AFTER `enabled`;

# Populate new state flags from old `state` field
UPDATE `mm_admin`.`users` SET `deleted` = 1 WHERE `state` = 'deleted';
UPDATE `mm_admin`.`users` SET `enabled` = 0 WHERE `state` = 'disabled';
UPDATE `mm_admin`.`users` SET `registering` = 1 WHERE `state` = 'registering';

# Drop `state` column
ALTER TABLE `mm_admin`.`users` DROP COLUMN `state`;

#-----------------------------------------------------------------------------
# Settings Paradigm
#-----------------------------------------------------------------------------

# `users` Migrate `perm_id` columns to new `settings_id` columns
ALTER TABLE `mm_admin`.`users` ADD COLUMN `settings_id` INT NOT NULL COMMENT 'User\'s settings.' AFTER `role_id`;
UPDATE `mm_admin`.`users` SET `settings_id` = `perm_id`+1;
ALTER TABLE `mm_admin`.`users` DROP COLUMN `perm_id`;

# `groups` Migrate `perm_id` columns to new `settings_id` columns
ALTER TABLE `mm_admin`.`groups` ADD COLUMN `settings_id` INT NOT NULL COMMENT 'Group\'s settings.' AFTER `notes`;
UPDATE `mm_admin`.`groups` SET `settings_id` = `perm_id`+1;
ALTER TABLE `mm_admin`.`groups` DROP COLUMN `perm_id`;

# `roles` Migrate `perm_id` columns to new `settings_id` columns
ALTER TABLE `mm_admin`.`roles` ADD COLUMN `settings_id` INT NOT NULL COMMENT 'Role\'s settings.' AFTER `notes`;
UPDATE `mm_admin`.`roles` SET `settings_id` = `perm_id`+1;
ALTER TABLE `mm_admin`.`roles` DROP COLUMN `perm_id`;

# Create `settings` table to manage all inheritable/overridable settings
#SET @@SESSION.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE `mm_admin`.`settings` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `p_admin_users` BOOL DEFAULT NULL COMMENT 'Permit administration of users.',
 `p_admin_reports` BOOL DEFAULT NULL COMMENT 'Permit administration of reports.',
 `p_admin_site` BOOL DEFAULT NULL COMMENT 'Permit administration of the site\'s design, style, and options.',
 `p_admin_library` BOOL DEFAULT NULL COMMENT 'Permit administration of databases, assets, and metadata.',
 `p_admin_system` BOOL DEFAULT NULL COMMENT 'Permit administration of the system.',
 `p_assets` LONGTEXT DEFAULT NULL COMMENT 'JSON structure of asset permissions.',
 `p_columns` LONGTEXT DEFAULT NULL COMMENT 'JSON structure of column permissions.',
 `p_proxies` LONGTEXT DEFAULT NULL COMMENT 'JSON structure of proxy permissions.',
 `u_sort_tracks` BOOL DEFAULT NULL COMMENT 'User can change sort order of track results.',
 `u_sort_albums` BOOL DEFAULT NULL COMMENT 'User can change sort order of ablums.',
 `u_src_type` BOOL DEFAULT NULL COMMENT 'User can change source type.',
 `u_src_id` BOOL DEFAULT NULL COMMENT 'User can change soure ID.',
 `u_result_vista` BOOL DEFAULT NULL COMMENT 'User can change the results view.',
 `u_result_nondown` BOOL DEFAULT NULL COMMENT 'User can change hide non-downloadable results.',
 `u_track_columns` BOOL DEFAULT NULL COMMENT 'User can change track result columns.',
 `u_track_order` BOOL DEFAULT NULL COMMENT 'User can change track result order.',
 `u_track_limit` BOOL DEFAULT NULL COMMENT 'User can change track result page limit.',
 `u_track_grouping` BOOL DEFAULT NULL COMMENT 'User can change track result grouping.',
 `u_track_view` BOOL DEFAULT NULL COMMENT 'User can change track result view.',
 `u_browse_show` BOOL DEFAULT NULL COMMENT 'User can change metabrowser visibility.',
 `u_browse_columns` BOOL DEFAULT NULL COMMENT 'User can change metabrowser columns.',
 `u_project_show` BOOL DEFAULT NULL COMMENT 'User can change project panel visibility.',
 `u_project_width` BOOL DEFAULT NULL COMMENT 'User can change project panel width.',
 `u_download_zip` BOOL DEFAULT NULL COMMENT 'User can change download method setting.',
 `u_download_flatten` BOOL DEFAULT NULL COMMENT 'User can change download flat setting.',
 `u_wdgts` BOOL DEFAULT NULL COMMENT 'User can change widgets structure.',
 `u_emailable` BOOL DEFAULT NULL COMMENT 'User email playlists.',
 `u_home` BOOL DEFAULT NULL COMMENT 'User can see home screen.',
 `s_sort_tracks` LONGTEXT DEFAULT NULL COMMENT 'Sort order of track results.',
 `s_sort_albums` LONGTEXT DEFAULT NULL COMMENT 'Sort order of album results.',
 `s_src_type` ENUM('db','special','folder','playlist','smart','cuesheet') DEFAULT NULL COMMENT 'Source type.',
 `s_src_id` INT DEFAULT NULL COMMENT 'Source ID: 0: all, >0:  db_id, <0: -folder_id',
 `s_result_vista` ENUM('albums','mixed','tracks') DEFAULT NULL COMMENT 'Results layout.',
 `s_result_nondown` BOOL DEFAULT NULL COMMENT 'Hide non-downloadable results.',
 `s_track_columns` TEXT DEFAULT NULL COMMENT 'Track result columns.',
 `s_track_order` VARCHAR(255) DEFAULT NULL COMMENT 'Track result order.',
 `s_track_limit` INT DEFAULT NULL COMMENT 'Track result page limit.',
 `s_track_grouping` ENUM('off','on') DEFAULT NULL COMMENT 'Track result grouping.',
 `s_track_view` ENUM('auto','fixed') DEFAULT NULL COMMENT 'Track result view.',
 `s_browse_show` ENUM('hide','show') DEFAULT NULL COMMENT 'Metabrowser visibility.',
 `s_browse_columns` TEXT DEFAULT NULL COMMENT 'Metabrowser columns.',
 `s_project_show` ENUM('hide','show') DEFAULT NULL COMMENT 'Project panel visibility.',
 `s_project_width` INT DEFAULT NULL COMMENT 'Project panel width.',
 `s_project_exts` BOOL DEFAULT NULL COMMENT 'Show file extensions in project track names.',
 `s_download_zip` ENUM('never','folders','multiple','always','push') DEFAULT NULL COMMENT 'Zip up multiple track downloads into a single zip file.',
 `s_download_flatten` BOOL DEFAULT NULL COMMENT 'Flatten folder downloads by collapsing the folder structure.',
 `s_wdgts` TEXT DEFAULT NULL COMMENT 'Widgets structure.',
 `hash` text default NULL,
 `orig_id` int(10) unsigned default NULL COMMENT 'Transiet field used when doing complex copy operations.',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

# Setup global default settings (SID_DEF = 1)
INSERT INTO `mm_admin`.`settings` (
	`id`, `p_admin_users`, `p_admin_reports`, `p_admin_site`, `p_admin_library`, `p_admin_system`, `p_assets`, `p_columns`, `p_proxies`,
	`u_sort_tracks`, `u_sort_albums`, `u_src_type`, `u_src_id`, `u_result_vista`, `u_result_nondown`, `u_track_columns`, `u_track_order`, `u_track_limit`, `u_track_grouping`, `u_track_view`, `u_browse_show`, `u_browse_columns`, `u_project_show`, `u_project_width`, `u_download_zip`, `u_download_flatten`, `u_wdgts`, `u_emailable`, `u_home`,
	`s_sort_tracks`, `s_sort_albums`, `s_src_type`, `s_src_id`, `s_result_vista`, `s_result_nondown`,
	`s_track_columns`,
	`s_track_order`, `s_track_limit`, `s_track_grouping`, `s_track_view`, `s_browse_show`, `s_browse_columns`, `s_project_show`, `s_project_width`, `s_project_exts`, `s_download_zip`, `s_download_flatten`,
	`s_wdgts`,
	`hash`, `orig_id`)
 VALUES(
	1, 0, 0, 0, 0, 0, '{"column":"Database","new":0,"items":{}}', '{"new":0,"items":{}}', '{"new":1,"def":[],"0":[],"1":[]}',
	1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
	'{"Relevance":"descend","Library":"ascend","Volume":"descend","Track":"ascend"}', '{"Relevance":"descend","Library":"ascend","Volume":"descend"}', 'db', 0, 'tracks', 0,
	'[[\"Artwork\",1,1,67],[\"Waveform\",0,0,50],[\"Library\",1,1,102],[\"TrackTitle\",1,1,110],[\"Description\",1,0,304],[\"CDTitle\",0,0,50],[\"Composer\",1,0,50],[\"Notes\",0,0,50],[\"Filename\",0,0,50],[\"Source\",0,0,50],[\"Publisher\",1,0,50],[\"SampleRate\",0,0,50],[\"Channels\",0,0,50],[\"Show\",0,0,50],[\"FileType\",0,0,50],[\"CDDescription\",0,0,50],[\"Keywords\",0,0,50],[\"ModificationDate\",0,0,50],[\"Arranger\",0,0,50],[\"Category\",1,0,60],[\"Duration\",1,1,58],[\"Relevance\",1,1,50]]',
	'Relevance', 15, 'off', 'auto', 'hide', '[\"Library\",\"Category\",\"Composer\",\"CDTitle\"]', 'show', 210, 1, 'multiple', 0,
	'{"wid":"1","wdgts":{"1":{"wid":"1","type":"layout","style":"collapsible","title":"Layout","layout":"layout-3","head":[],"col1":["3"],"col2":["2"],"col3":["4"],"foot":[]},"3":{"wid":"3","type":"summary","style":"collapsible","title":"Library","column":"Library","sort":"ascend"},"2":{"wid":"2","type":"html","style":"naked","title":"Html","html":"<h2 style=\\"text-align: center; font-weight: bold;\\">Welcome!<\\/h2>"},"4":{"wid":"4","type":"summary","style":"collapsible","title":"Category","column":"Category","sort":"ascend"}}}',
	'{}', NULL);

# Migrate settings from `perms` table to `settings` table
INSERT INTO `mm_admin`.`settings` (`id`, `p_admin_users`, `p_admin_reports`, `p_admin_site`, `p_admin_library`, `p_admin_system`, `p_assets`, `p_columns`, `u_emailable`, `s_sort_tracks`, `s_sort_albums`, `s_project_exts` ) SELECT `id`+1, `admin_users`, `admin_reports`, `admin_site`, `admin_databases`, `admin_databases`, `assets`, `columns`, `email_playlists`, `sort_tracks`, `sort_albums`, `file_exts` FROM `mm_admin`.`perms`;

# Migrate settings from `users` table to `settings` table
UPDATE `mm_admin`.`settings`, `mm_admin`.`users` SET
 `settings`.`s_src_type` = `users`.`src_type`,
 `settings`.`s_src_id` = `users`.`src_id`,
 `settings`.`s_result_vista` = `users`.`prf_vista`,
 `settings`.`s_result_nondown` = `users`.`hide_nondown`,
 `settings`.`s_track_columns` = `users`.`columns`,
 `settings`.`s_track_order` = `users`.`order`,
 `settings`.`s_track_limit` = `users`.`limit`,
 `settings`.`s_track_grouping` = `users`.`prf_grouping`,
 `settings`.`s_track_view` = `users`.`prf_view`,
 `settings`.`s_browse_show` = CASE `users`.`prf_browse` WHEN 0 THEN 'hide' WHEN 1 THEN 'show' ELSE NULL END,
 `settings`.`s_browse_columns` = `users`.`browses`,
 `settings`.`s_project_show` = `users`.`prf_project`,
 `settings`.`s_project_width` = `users`.`project_width`,
 `settings`.`s_download_zip` = `users`.`download_zip`,
 `settings`.`s_download_flatten` = `users`.`download_flatten`,
 `settings`.`hash` = `users`.`hash`
 WHERE `settings`.`id` = `users`.`settings_id`;

# Drop `users` columns that aren't actually used
# Drop migrated columns from `users` table
ALTER TABLE `mm_admin`.`users`
 DROP COLUMN `mode`,
 DROP COLUMN `weights`,
 DROP COLUMN `weights_order`,
 DROP COLUMN `prf_columns`,
 DROP COLUMN `prf_history`,

 DROP COLUMN `src_type`,
 DROP COLUMN `src_id`,
 DROP COLUMN `limit`,
 DROP COLUMN `prf_grouping`,
 DROP COLUMN `prf_browse`,
 DROP COLUMN `browses`,
 DROP COLUMN `columns`,
 DROP COLUMN `order`,
 DROP COLUMN `project_width`,
 DROP COLUMN `hide_nondown`,
 DROP COLUMN `prf_view`,
 DROP COLUMN `prf_project`,
 DROP COLUMN `prf_vista`,
 DROP COLUMN `download_zip`,
 DROP COLUMN `download_flatten`,
 DROP COLUMN `hash`;

# Alter `history` table to match `settings` table columns
ALTER TABLE `mm_admin`.`history`
 CHANGE COLUMN `src_type` `s_src_type` ENUM('db','special','folder','playlist','smart','cuesheet') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 CHANGE COLUMN `src_id` `s_src_id` INTEGER NOT NULL,
 CHANGE COLUMN `columns` `s_track_columns` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 CHANGE COLUMN `browse` `s_browse_show` TINYINT(1) NOT NULL,
 CHANGE COLUMN `browses` `s_browse_columns` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 CHANGE COLUMN `order` `s_track_order` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 DROP COLUMN `prf_grouping`,
 CHANGE COLUMN `index` `s_track_index` INTEGER NOT NULL,
 CHANGE COLUMN `limit` `s_track_limit` INTEGER NOT NULL,
 DROP COLUMN `browse_state`,
 ADD COLUMN `s_result_vista` ENUM('albums','mixed','tracks') NOT NULL AFTER `s_src_id`,
 ADD COLUMN `s_result_nondown` BOOL NOT NULL AFTER `s_result_vista`,
 ADD COLUMN `s_track_grouping` ENUM('off','on') NOT NULL AFTER `s_track_limit`,
 ADD COLUMN `s_track_view` ENUM('auto','fixed') NOT NULL AFTER `s_track_grouping`;

# Create `fields` table to store custom user field settings

# Drop `perms` table
DROP TABLE `mm_admin`.`perms`;

#-----------------------------------------------------------------------------
# Registration Settings
#-----------------------------------------------------------------------------

# Alter `site` table to accomodate new registration and licensing settings
ALTER TABLE `mm_admin`.`site`
 MODIFY COLUMN `registration` ENUM('none','instant','email','alert') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'none' COMMENT 'Auto registration options.',
 ADD COLUMN `registration_email` TEXT NOT NULL COMMENT 'Json structure for the email to send new registering users.' AFTER `registration_alert`,
 ADD COLUMN `registration_fields` TEXT NOT NULL COMMENT 'Json array of fields to include in the registration form.' AFTER `registration_email`,
 ADD COLUMN `lic_id` VARCHAR(40) NOT NULL COMMENT 'License ID.' AFTER `help_url`,
 ADD COLUMN `lic_data` TEXT NOT NULL COMMENT 'License data.' AFTER `lic_id`,
 ADD COLUMN `lic_key` TEXT NOT NULL AFTER `lic_data`;

# Create default registration email
UPDATE `mm_admin`.`site` SET `registration_email` = '{"cc":"","bcc":"","subject":"[%site_name%] Registration Confirmation","message":"Hi %user%,\\n\\nYour registration for %site_name% is almost complete.\\n\\nCOMPLETE REGISTRATION:\\n\\nTo complete your registration and gain instant access click this link:\\n%link_confirm%\\n\\nCANCEL REGISTRATION:\\n\\nTo cancel your registration follow this link:\\n%link_cancel%\\n\\n%site_name%"}';

#-----------------------------------------------------------------------------
# Users / Groups
#-----------------------------------------------------------------------------

CREATE TABLE `mm_admin`.`users_groups` (
  `user_id` INT NOT NULL COMMENT 'User ID.',
  `group_id` INT NOT NULL COMMENT 'Group ID.',
  `role_id` INT DEFAULT NULL COMMENT 'User\'s role within the group.',
  PRIMARY KEY(`user_id`, `group_id`)
)
ENGINE = MYISAM
CHARACTER SET utf8;

INSERT INTO `mm_admin`.`users_groups` (`user_id`, `group_id`, `role_id`) SELECT `id`, `group_id`, `role_id` FROM `mm_admin`.`users` WHERE `group_id` IS NOT NULL;

#-----------------------------------------------------------------------------
# Project Sharing
#-----------------------------------------------------------------------------

# Fix up any dangling folder paths and remove past deleted folders (which will now be handled with a `deleted` flag)
UPDATE `mm_admin`.`folders` SET `path` = '/' WHERE `folder_id` = 0;
UPDATE `mm_admin`.`folders` SET `updated_by` = 0;
DELETE FROM `mm_admin`.`folders` WHERE `folder_id` IS NULL;
DELETE FROM `mm_admin`.`playlists` WHERE `folder_id` IS NULL;
DELETE FROM `mm_admin`.`tracks` WHERE `playlist_id` IS NULL;
DELETE FROM `mm_admin`.`searches` WHERE `folder_id` IS NULL;

# Alter `folders`, `playlists`, `tracks`, `searches` tables to accommodate new "root" owner and access settings
ALTER TABLE `mm_admin`.`folders` MODIFY COLUMN `folder_id` INTEGER NOT NULL DEFAULT 0 COMMENT 'Parent folder containing this folder.',
 ADD COLUMN `owner_id` INT DEFAULT NULL COMMENT 'Owner ID: > 0 = user_id, < 0 = group_id.' AFTER `id`,
 ADD COLUMN `type` ENUM('private','inbox','published','shared') DEFAULT NULL COMMENT 'Internal flag which determines project tree placement and handling.' AFTER `owner_id`,
 ADD COLUMN `o_access` SET('read','add','modify','delete','root') DEFAULT NULL COMMENT 'Bit flags that control owner\'s access to this content.' AFTER `type`,
 ADD COLUMN `s_access` SET('read','add','modify','delete','root') DEFAULT NULL COMMENT 'Bit flags that control other user\'s access to shared content.' AFTER `o_access`,
 ADD COLUMN `root_id` INT NOT NULL COMMENT 'Root folder with owner and access settings.' AFTER `s_access`,
 ADD COLUMN `deleted` BOOL NOT NULL DEFAULT 0 COMMENT 'Deleted flag.' AFTER `updated`;

ALTER TABLE `mm_admin`.`playlists` MODIFY COLUMN `folder_id` INTEGER NOT NULL DEFAULT 0 COMMENT 'Parent folder containing this playlist. NULL = deleted.',
 ADD COLUMN `root_id` INT NOT NULL COMMENT 'Root folder with owner and access settings.' AFTER `id`,
 ADD COLUMN `deleted` BOOL NOT NULL DEFAULT 0 COMMENT 'Deleted flag.' AFTER `updated`;

ALTER TABLE `mm_admin`.`tracks` MODIFY COLUMN `playlist_id` INTEGER NOT NULL DEFAULT 0 COMMENT 'Parent playlist containing this track. NULL = deleted.',
 ADD COLUMN `root_id` INT NOT NULL COMMENT 'Root folder with owner and access settings.' AFTER `id`,
 ADD COLUMN `deleted` BOOL NOT NULL DEFAULT 0 COMMENT 'Deleted flag.' AFTER `updated`;

ALTER TABLE `mm_admin`.`searches` MODIFY COLUMN `folder_id` INTEGER NOT NULL DEFAULT 0 COMMENT 'Parent folder containing this folder. NULL = deleted.',
 ADD COLUMN `root_id` INT NOT NULL COMMENT 'Root folder with owner and access settings.' AFTER `id`,
 ADD COLUMN `deleted` BOOL NOT NULL DEFAULT 0 COMMENT 'Deleted flag.' AFTER `updated`;

# Migrate existing users/groups Playlists to the root structure
UPDATE `mm_admin`.`folders`, `mm_admin`.`users` SET `owner_id` = `users`.`id`, `folders`.`type` = 'private', `o_access` = 'read,add,modify,delete', `s_access` = '', `root_id` = `folders`.`id`, `path` = CONCAT('/', `users`.`user`,':', `users`.`id`, '/'), `folders`.`name` = 'Playlists' WHERE `folders`.`id` = `users`.`folder_id`;
UPDATE `mm_admin`.`folders`, `mm_admin`.`groups` SET `owner_id` = -`groups`.`id`, `folders`.`type` = 'private', `o_access` = 'read,add,modify,delete', `s_access` = '', `root_id` = `folders`.`id`, `path` = CONCAT('/', `groups`.`name`, ':', -`groups`.`id`, '/'), `folders`.`name` = 'Playlists' WHERE `folders`.`id` = `groups`.`folder_id`;

# Create Inbox and Published folders for existing users
INSERT INTO `mm_admin`.`folders` (`owner_id`, `type`, `o_access`, `s_access`, `root_id`, `folder_id`, `path`, `name`, `created`, `updated`) SELECT `id`, 'inbox', 'read,delete', 'add', 0, 0, CONCAT('/', `users`.`user`, ':', `id`, '/'), 'Inbox', NOW(), NOW() FROM `mm_admin`.`users`;
INSERT INTO `mm_admin`.`folders` (`owner_id`, `type`, `o_access`, `s_access`, `root_id`, `folder_id`, `path`, `name`, `created`, `updated`) SELECT `id`, 'published', 'read,add,modify,delete', 'read', 0, 0, CONCAT('/', `users`.`user`, ':', `id`, '/'), 'Published', NOW(), NOW() FROM `mm_admin`.`users`;
UPDATE `mm_admin`.`folders` SET `root_id` = `id` WHERE `folder_id` = 0;

# Fill in `folders`.`path` field for a folder depth of up to 20
UPDATE `mm_admin`.`folders` SET `path` = '' WHERE `folder_id` != 0;
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';
UPDATE `mm_admin`.`folders` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id where a.path = '' AND b.path != '';

# Fill in `playlists`.`path` field
UPDATE `mm_admin`.`playlists` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id;

# Fill in `tracks`.`path` field
UPDATE `mm_admin`.`tracks` a LEFT JOIN `mm_admin`.`playlists` b ON a.playlist_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id;

# Fill in `searches`.`path` field
UPDATE `mm_admin`.`searches` a LEFT JOIN `mm_admin`.`folders` b ON a.folder_id = b.id set a.path = CONCAT(b.path,b.name,':',b.id,'/'), a.root_id = b.root_id;

# Create new `shared` table to link users to other user's shared folders
CREATE TABLE `mm_admin`.`shared` (
  `user_id` INT NOT NULL COMMENT 'Viewing user ID.',
  `owner_id` INT NOT NULL COMMENT 'Sharing user ID.'
)
ENGINE = MYISAM
CHARACTER SET utf8
COMMENT = 'Links users to other user\'s shared folders.';

#-----------------------------------------------------------------------------
# Versions
#-----------------------------------------------------------------------------

# Update version fields
UPDATE `mm_admin`.`schema` SET `db_ver` = '1.2.0', `sm_ver` = '1.2.0';