##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-7-0_to_0-7-1.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2007-08-02															#
 # Updated:		2007-08-06															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.7.0 to 0.7.1														#
 #----------------------------------------------------------------------------------##

# Add more fields to `users` table
ALTER TABLE `mm_admin`.`users` ADD COLUMN `rep` VARCHAR(128) NOT NULL COMMENT 'Sales representative.' AFTER `email`;

ALTER TABLE `mm_admin`.`reports` MODIFY COLUMN `users` INT NOT NULL DEFAULT 0 COMMENT 'User or -group ID to include in report.';

# Update reports
UPDATE `mm_admin`.`reports` SET `tables` = '[{\"type\":\"summary\",\"title\":\"Most Popular\",\"events\":[\"search\"],\"limit\":null,\"grand\":true,\"groups\":[[\"Database(s)\",\"s_databases\",null,true,true],[\"Search Query\",\"s_query\",null,true,true]],\"fields\":[[\"# Searches\",\"id\",\"desc\",null,\"count\"],[\"# Users\",\"user\",\"desc\",null,\"dcount\"],[\"Min Found\",\"s_total\",null,null,\"min\"],[\"Max Found\",\"s_total\",null,null,\"max\"],[\"Avg Found\",\"s_total\",null,null,\"avg\"]]},{\"type\":\"summary\",\"title\":\"Fewest Results\",\"events\":[\"search\"],\"limit\":null,\"grand\":true,\"groups\":[[\"Database(s)\",\"s_databases\",null,true,true],[\"Search Query\",\"s_query\",null,true,true]],\"fields\":[[\"# Searches\",\"id\",\"desc\",2,\"count\"],[\"# Users\",\"user\",\"desc\",3,\"dcount\"],[\"Min Found\",\"s_total\",\"asc\",0,\"min\"],[\"Max Found\",\"s_total\",null,null,\"max\"],[\"Avg Found\",\"s_total\",\"asc\",1,\"avg\"]]},{\"type\":\"detail\",\"title\":\"Failed Searches By User\",\"events\":[\"search\"],\"limit\":null,\"grand\":true,\"groups\":[[\"User\",\"user\",\"asc\",false,false]],\"fields\":[[\"Time\",\"datetime\",\"asc\",null,false,false,false,false],[\"Database(s)\",\"s_databases\",\"asc\",null,false,false,false,false],[\"Query\",\"s_query\",\"asc\",null,false,false,false,false],[\"Results\",\"s_total\",\"asc\",2,false,false,false,false]],\"where\":[[\"s_total\",\"eq\",\"0\"]]}]' WHERE `id` = '2';

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