##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-5-4_to_0-5-5.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2007-05-10															#
 # Updated:		2007-06-15															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.5.4 to 0.5.5														#
 #----------------------------------------------------------------------------------##

CREATE TABLE `mm_admin`.`assets` (
  `id` INT NOT NULL auto_increment,
  `db_id` INT NOT NULL,
  `track_id` INT NOT NULL,
  `Filename` VARCHAR(255) NOT NULL,
  PRIMARY KEY(`id`),
  INDEX `db_id_track_id_index`(`db_id`, `track_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `mm_logs`.`ids` ADD COLUMN `time` datetime NOT NULL AFTER `id`;

ALTER TABLE `mm_logs`.`auditions` DROP COLUMN `time`,
 MODIFY COLUMN `file_path` VARCHAR(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 MODIFY COLUMN `file_type` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 ADD COLUMN `group_id` INT AFTER `user_id`,
 ADD COLUMN `role_id` INT AFTER `group_id`;

ALTER TABLE `mm_logs`.`downloads` DROP COLUMN `time`,
 MODIFY COLUMN `file_path` VARCHAR(3000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 MODIFY COLUMN `file_type` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 ADD COLUMN `group_id` INT AFTER `user_id`,
 ADD COLUMN `role_id` INT AFTER `group_id`;

TRUNCATE TABLE `mm_logs`.`ids`;
TRUNCATE TABLE `mm_logs`.`auditions`;
TRUNCATE TABLE `mm_logs`.`downloads`;
TRUNCATE TABLE `mm_logs`.`events`;
TRUNCATE TABLE `mm_logs`.`hosts`;
TRUNCATE TABLE `mm_logs`.`logins`;
TRUNCATE TABLE `mm_logs`.`queries`;
TRUNCATE TABLE `mm_logs`.`searches`;
TRUNCATE TABLE `mm_logs`.`terms`;

UPDATE `mm_admin`.`schema` SET `db_ver` = '0.5.5', `mm_ver` = '0.5.5';
UPDATE `mm_cache`.`schema` SET `mm_ver` = '0.5.5';
UPDATE `mm_logs`.`schema` SET `db_ver` = '0.5.5', `mm_ver` = '0.5.5';