##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_1-0-3_to_1-0-4.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2008-09-17															#
 # Updated:		2008-12-02															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2008 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		1.0.3 to 1.0.4														#
 #----------------------------------------------------------------------------------##

# Add `db_id` and `track_id` fields, redundantly (already in `assets`), to `tracks`
ALTER TABLE `mm_admin`.`tracks`
 ADD COLUMN `db_id` integer DEFAULT NULL AFTER `asset_id`,
 ADD COLUMN `track_id` INT DEFAULT NULL AFTER `db_id`,
 DROP INDEX `folder_index`,
 ADD INDEX `playlist_index`(`playlist_id`),
 ADD INDEX `db_index`(`db_id`);
UPDATE `mm_admin`.`tracks`, `mm_admin`.`assets` SET `tracks`.`db_id` = `assets`.`db_id`, `tracks`.`track_id` = `assets`.`track_id` WHERE `tracks`.`asset_id` = `assets`.`id`;

# Add `db_id` and `track_id` fields, redundantly (already in `assets`), to `log_downloads`
ALTER TABLE `mm_admin`.`log_downloads`
 ADD COLUMN `db_id` integer NOT NULL AFTER `asset_id`,
 ADD COLUMN `track_id` INT NOT NULL AFTER `db_id`;
UPDATE `mm_admin`.`log_downloads`, `mm_admin`.`assets` SET `log_downloads`.`db_id` = `assets`.`db_id`, `log_downloads`.`track_id` = `assets`.`track_id` WHERE `log_downloads`.`asset_id` = `assets`.`id`;

# Fill in `tracks`.`ndx` fields, repairs effects of ndx misnumbering bug in prior versions
SET @play = -1;
UPDATE `mm_admin`.`tracks` SET `ndx` = IF(@play!=`playlist_id`,0*(@play:=`playlist_id`)+(@ndx:=0),(@ndx:=@ndx+1)) ORDER BY `playlist_id`, `ndx` ASC;

# Delete any invalid tracks with `asset_id` = 0
UPDATE `mm_admin`.`tracks` SET `playlist_id` = NULL, `path` = '', `updated_by` = '0', `updated` = NOW() WHERE `asset_id` = '0';

# Update version fields
UPDATE `mm_admin`.`schema` SET `db_ver` = '1.0.4';