##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/upgrade_0-7-3_to_0-7-4.sql							#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2007-08-14															#
 # Updated:		2007-08-16															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2007 Soundminer Inc.										#
 # Purpose:		Upgrade the installed MusicMiner databases.							#
 # Version:		0.7.3 to 0.7.4														#
 #----------------------------------------------------------------------------------##

# Create new `browsers` table
CREATE TABLE `mm_admin`.`browsers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_agent` varchar(255) NOT NULL,
  `browser` varchar(64) NOT NULL,
  `version` float NOT NULL,
  `version_maj` int(11) NOT NULL,
  `version_min` int(11) NOT NULL,
  `platform` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index` (`user_agent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Logs details about client browsers.';
INSERT INTO `mm_admin`.`browsers` (`id`, `user_agent`, `browser`, `version`, `version_maj`, `version_min`, `platform`) VALUES ('1', 'Not Recorded', 'Not Recorded', '0', '0', '0', 'Not Recorded');

# Add `browser_id` column to `log` table
ALTER TABLE `mm_admin`.`log` ADD COLUMN `browser_id` INTEGER NOT NULL DEFAULT 1 COMMENT 'ID of client browser accessing the server during the event.' AFTER `host_id`;

# Add Browser Summary report to `reports` table
INSERT INTO `mm_admin`.`reports` (`title`,`description`,`time`,`begin`,`end`,`users`,`tables`) VALUES ('Browser Summary','','all',NULL,NULL,'0','[{\"type\":\"summary\",\"title\":\"Browsers\",\"events\":[\"login\",\"logout\",\"search\",\"audition\",\"download\",\"error\"],\"groups\":[[\"Browser\",\"b_full\",\"\",1,0]],\"fields\":[[\"# Users\",\"user\",\"desc\",null,\"dcount\"]],\"where\":[],\"limit\":null,\"grand\":1},{\"type\":\"summary\",\"title\":\"Platforms\",\"events\":[\"login\",\"logout\",\"search\",\"audition\",\"download\",\"error\"],\"groups\":[[null,\"b_platform\",\"\",1,0]],\"fields\":[[\"# Users\",\"user\",\"desc\",null,\"dcount\"]],\"where\":[],\"limit\":null,\"grand\":1}]');

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