##----------------------------------------------------------------------------------#
 # MusicMiner Web Server															#
 # Name:		source/app/sql/install_0-9-22.sql									#
 # Author:		Jesse J Morrow	(jesse@jessejmorrow.com)							#
 # Created:		2008-07-18															#
 # Updated:		2009-01-14															#
 # Compat:		MySQL 5																#
 # Copyright:	Copyright 2008-2009 Soundminer Inc.									#
 # Purpose:		Install the MusicMiner databases.									#
 # Version:		0.9.22																#
 #----------------------------------------------------------------------------------##

# MySQL Administrator dump 1.4
# Server version 5.0.27-standard-log



#---------------------------------------------------------------------------------
# mm_admin
#---------------------------------------------------------------------------------

DROP DATABASE IF EXISTS `mm_admin`;
CREATE DATABASE IF NOT EXISTS `mm_admin`;
USE `mm_admin`;

CREATE TABLE  `mm_admin`.`schema` (
  `schema` varchar(30) character set latin1 NOT NULL default '',
  `db_ver` varchar(30) NOT NULL,
  `mm_ver` varchar(30) NOT NULL,
  `sm_ver` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=1;
INSERT INTO `mm_admin`.`schema` (`schema`,`db_ver`,`mm_ver`,`sm_ver`) VALUES ('mm_admin','0.9.22','0.9.22','0.9.22');

CREATE TABLE  `mm_admin`.`assets` (
  `id` int(11) NOT NULL auto_increment,
  `db_id` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `asset_key` char(32) NOT NULL COMMENT 'Random md5 hash used for access security.',
  `Filename` varchar(255) NOT NULL,
  `Pathname` varchar(255) NOT NULL,
  `FilePath` text NOT NULL,
  `Duration` varchar(10) NOT NULL,
  `FileType` varchar(4) NOT NULL,
  `CreationDate` date NOT NULL default '0000-00-00',
  `ModificationDate` date NOT NULL default '0000-00-00',
  `TrackTitle` varchar(255) NULL,
  `Composer` varchar(255) NULL,
  `Publisher` varchar(255) NULL,
  PRIMARY KEY  (`id`),
  KEY `db_id_track_id_index` (`db_id`,`track_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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 AUTO_INCREMENT=2 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');

CREATE TABLE  `mm_admin`.`columns` (
  `id` varchar(255) NOT NULL,
  `display` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL default '0',
  `browse` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Mapping of user columns to database columns.';
INSERT INTO `mm_admin`.`columns` (`id`,`display`,`active`,`browse`) VALUES 
 ('Database','Database',1,1),
 ('Relevance','Relevance',1,0),
 ('RecID','RecID',1,0),
 ('Filename','Filename',1,0),
 ('Pathname','Pathname',1,0),
 ('FilePath','FilePath',1,0),
 ('Description','Description',1,0),
 ('Source','Source',1,0),
 ('Duration','Duration',1,1),
 ('Category','Category',1,1),
 ('SubCategory','SubCategory',1,0),
 ('_FlatCategory','_FlatCategory',0,0),
 ('Manufacturer','Manufacturer',1,0),
 ('Designer','Designer',1,0),
 ('Show','Show',1,0),
 ('Library','Library',1,1),
 ('Notes','Notes',1,0),
 ('RecType','RecType',1,0),
 ('ShortID','ShortID',1,0),
 ('LongID','LongID',1,0),
 ('RecMedium','RecMedium',1,0),
 ('Rating','Rating',1,0),
 ('Location','Location',1,0),
 ('Microphone','Microphone',1,0),
 ('Keywords','Keywords',1,0),
 ('Composer','Composer',1,1),
 ('Publisher','Publisher',1,1),
 ('Performer','Performer',1,0),
 ('Arranger','Arranger',1,0),
 ('Conductor','Conductor',1,0),
 ('TrackTitle','TrackTitle',1,0),
 ('CDTitle','Album',1,1);
INSERT INTO `mm_admin`.`columns` (`id`,`display`,`active`,`browse`) VALUES 
 ('CDDescription','CDDescription',1,0),
 ('FeaturedInstrument','FeaturedInstrument',1,0),
 ('Scene','Scene',1,0),
 ('Take','Take',1,0),
 ('Tape','Tape',1,0),
 ('FileType','FileType',1,0),
 ('CreationDate','CreationDate',1,0),
 ('ModificationDate','ModificationDate',1,0),
 ('TotalFrames','TotalFrames',1,0),
 ('EntryDate','EntryDate',1,0),
 ('Popularity','Popularity',1,0),
 ('Split','Split',1,0),
 ('SplitChannels','SplitChannels',1,0),
 ('SampleRate','SampleRate',1,0),
 ('Channels','Channels',1,0),
 ('_WaveformLink','_WaveformLink',0,0),
 ('_PictureLink','_PictureLink',0,0),
 ('_UMID','_UMID',0,0),
 ('BitDepth','BitDepth',1,0),
 ('ChannelLayout','ChannelLayout',1,0),
 ('_Duration','_Duration',0,0),
 ('BPM','BPM',1,0),
 ('Key','Key',1,0),
 ('BWDescription','BWDescription',1,0),
 ('BWOriginator','BWOriginator',1,0),
 ('BWOriginatorRef','BWOriginatorRef',1,0),
 ('FXName','FXName',0,0),
 ('Time','Time',1,1),
 ('Volume','Volume',1,1),
 ('Track','Track',1,0);
INSERT INTO `mm_admin`.`columns` (`id`,`display`,`active`,`browse`) VALUES 
 ('Index','Index',1,0),
 ('Artwork','Artwork',1,0),
 ('Waveform','Waveform',1,0);

CREATE TABLE  `mm_admin`.`cues` (
  `id` int(11) NOT NULL auto_increment COMMENT 'Primary key.',
  `cuesheet_id` int(11) NOT NULL COMMENT 'Parent cuesheet containing this cue.',
  `ndx` int(11) NOT NULL default '0' COMMENT 'The sequence index of this cue within the cuesheet. cue = ndx + 1',
  `asset_id` int(11) default NULL COMMENT 'Entry in `assets` table that is source track of this song. NULL means this is a hand-entered cue.',
  `title` varchar(255) default NULL COMMENT 'Song title.',
  `composer` varchar(255) default NULL COMMENT 'Composer, with affiliations and percentages, of the song.',
  `publisher` varchar(255) default NULL COMMENT 'Publisher, with affiliations and percentages, of the song.',
  `usage` varchar(32) NOT NULL COMMENT 'Standardized code indicating how the song was used in the work.',
  `time` time NOT NULL COMMENT 'Duration of song usage within the work.',
  `created` datetime NOT NULL COMMENT 'Timestamp of the original creation of row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of the last modification of row.',
  PRIMARY KEY  (`id`),
  KEY `cuesheet_index` (`cuesheet_id`),
  KEY `asset_index` (`asset_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Each row is a cue entry in it''s associated cuesheet.';

CREATE TABLE  `mm_admin`.`cuesheets` (
  `id` int(11) NOT NULL auto_increment COMMENT 'Primary key.',
  `folder_id` int(11) NOT NULL COMMENT 'Parent project folder that contains this cuesheet.',
  `name` varchar(128) NOT NULL COMMENT 'Name of the cuesheet as it appears in parent project folder.',
  `title` varchar(128) default NULL COMMENT 'Film or program title.',
  `episode` varchar(128) default NULL COMMENT 'Episode title if part of a series.',
  `episode_no` varchar(12) default NULL COMMENT 'Episode number if part of a series.',
  `production_no` varchar(12) default NULL COMMENT 'Production number or ID.',
  `airdate` date default NULL COMMENT 'Original or estimated airdate.',
  `length` time default NULL COMMENT 'Normal run time of program or length of film.',
  `program_type` varchar(32) default NULL COMMENT 'Type of program, such as cartoon, game show, soap opera, etc.',
  `version_type` varchar(32) default NULL COMMENT 'Type of broadcast sources for which the cuesheet was created, such as network, cable, etc.',
  `station` varchar(128) default NULL COMMENT 'Network station associated with the program.',
  `producer` varchar(128) default NULL COMMENT 'Producer of the film or program.',
  `distributor` varchar(128) default NULL COMMENT 'Distributor of the film or program.',
  `director` varchar(128) default NULL COMMENT 'Director of the film or program.',
  `contact` varchar(1000) default NULL COMMENT 'Person or company, with address and contact info., who produced the program, prepared, or should be contacted regarding, the cuesheet.',
  `contact_type` enum('contact','prepared','produced') NOT NULL COMMENT 'The entity which the `contact` field refers.',
  `notes` varchar(10000) default NULL COMMENT 'User''s notes attached to this cuesheet.',
  `created` datetime NOT NULL COMMENT 'Timestamp of the original creation of this cuesheet row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of last modification of this cuesheet row.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Holds program info, contact info, and settings for cuesheets';

CREATE TABLE  `mm_admin`.`databases` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `schema` varchar(30) NOT NULL default '',
  `version` tinyint(3) unsigned NOT NULL default '0',
  `version_min` tinyint(3) unsigned NOT NULL default '0',
  `display` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL default '0',
  `active` tinyint(1) NOT NULL default '0',
  `proxy_id` int(11) default '0',
  `perm_columns` varchar(255) NOT NULL default '["Library","CDTitle","Filename"]',
  `group_columns` varchar(255) NOT NULL default '["Library","CDTitle","TrackTitle"]',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='List of known Soundminer library databases.';

CREATE TABLE  `mm_admin`.`folders` (
  `id` int(11) NOT NULL auto_increment,
  `folder_id` int(11) NOT NULL,
  `name` varchar(128) NOT NULL default 'Folder' COMMENT 'Name of this folder as it appears in parent project folder.',
  `created` datetime NOT NULL COMMENT 'Timestamp of the original creation of row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of the last modification of row.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `mm_admin`.`folders` (`id`,`folder_id`,`name`,`created`,`updated`) VALUES 
 (1,0,'Admin','0000-00-00 00:00:00','0000-00-00 00:00:00'),
 (2,0,'Guest','0000-00-00 00:00:00','0000-00-00 00:00:00'),
 (3,0,'Default','0000-00-00 00:00:00','0000-00-00 00:00:00');

CREATE TABLE  `mm_admin`.`folder_states` (
  `user_id` int(11) NOT NULL,
  `type` enum('folder','cuesheet') NOT NULL default 'folder' COMMENT 'Indicates which table, `folders` or `cuesheets`, that `id` is associated with.',
  `id` int(11) NOT NULL,
  `state` enum('open','closed','hidden') NOT NULL,
  PRIMARY KEY  (`user_id`,`type`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Many to one relationship between users and folder state (i.e';
INSERT INTO `mm_admin`.`folder_states` (`user_id`,`type`,`id`,`state`) VALUES 
 (1,'folder',1,'open'),
 (2,'folder',2,'open'),
 (3,'folder',3,'open');

CREATE TABLE  `mm_admin`.`groups` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '0',
  `notes` varchar(255) NOT NULL,
  `perm_id` int(11) default NULL,
  `folder_id` int(11) NOT NULL COMMENT 'Root folder of group''s shared project content.',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`name`),
  KEY `folder_index` (`folder_id`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`help` (
  `id` varchar(32) NOT NULL COMMENT 'Unique help page identifier.',
  `title` varchar(255) NOT NULL COMMENT 'Displayable topic title.',
  `content` text NOT NULL COMMENT 'Help topic content.',
  `file` varchar(255) NOT NULL COMMENT 'File path of HLP file containing help content.',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `search` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`history` (
  `user_id` int(11) NOT NULL default '0',
  `level` int(10) unsigned NOT NULL default '0',
  `kind` enum('home','search','mod') NOT NULL default 'home',
  `lock` tinyint(1) NOT NULL default '0',
  `text` text character set latin1 NOT NULL,
  `ftext` text NOT NULL,
  `db_id` int(11) NOT NULL default '0',
  `columns` text NOT NULL,
  `browse` tinyint(1) NOT NULL default '0',
  `browses` text NOT NULL,
  `order` varchar(255) default NULL,
  `prf_grouping` enum('off','on') NOT NULL default 'off',
  `index` int(11) NOT NULL default '0',
  `limit` int(11) NOT NULL default '0',
  `browse_state` text NOT NULL,
  PRIMARY KEY  (`user_id`,`level`),
  KEY `level_index` (`level`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='History stack of user queries';

CREATE TABLE  `mm_admin`.`hosts` (
  `id` int(11) NOT NULL auto_increment,
  `ip` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index` (`ip`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`log` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique sequence event ID.',
  `time` datetime NOT NULL COMMENT 'Date/time of event occurrence.',
  `event` enum('login','logout','search','audition','download','error') NOT NULL COMMENT 'The event that occurred and indicates which log tables contain further info.',
  `user_id` int(11) default NULL COMMENT 'ID of logged in user causing the event.',
  `group_id` int(11) default NULL COMMENT 'ID of logged in user''s group causing the event.',
  `role_id` int(11) default NULL COMMENT 'ID of logged in user''s role causing the event.',
  `host_id` int(11) NOT NULL COMMENT 'ID of client machine accessing the server during the event.',
  `browser_id` int(11) NOT NULL default '1' COMMENT 'ID of client browser accessing the server during the event.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`log_downloads` (
  `log_id` int(11) NOT NULL,
  `asset_id` int(11) NOT NULL,
  `file_path` varchar(3000) NOT NULL,
  `file_size` int(10) unsigned NOT NULL,
  `file_type` varchar(255) NOT NULL,
  `completed` tinyint(1) NOT NULL,
  PRIMARY KEY  (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`log_logins` (
  `log_id` int(11) NOT NULL default '0',
  `login_id` int(11) default NULL COMMENT 'The matching logout, for logins, or login, for logouts, event ID.',
  `method` enum('user','auto') NOT NULL COMMENT 'The method or reason for the login or logout.',
  PRIMARY KEY  (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`log_searches` (
  `log_id` int(10) unsigned NOT NULL,
  `query_id` int(11) NOT NULL default '0',
  `lock` tinyint(1) NOT NULL,
  `text` text NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY  (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`mimes` (
  `name` varchar(255) NOT NULL default '',
  `type` varchar(30) NOT NULL default '',
  `subtype` varchar(30) NOT NULL,
  `ext` varchar(255) NOT NULL default '',
  `mime` varchar(255) NOT NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `mm_admin`.`mimes` (`name`,`type`,`subtype`,`ext`,`mime`) VALUES 
 ('MPEG Video','video','mpeg','mpg,mpeg,mpe','video/mpeg'),
 ('QuickTime Video','video','quicktime','mov,qt','video/quicktime'),
 ('Microsoft Video','video','x-msvideo','avi','video/x-msvideo'),
 ('WAV Audio','audio','x-wav','wav','audio/x-wav'),
 ('AIFF Audio','audio','x-aiff','aiff','audio/x-aiff'),
 ('MIDI Audio','audio','x-midi','mid','audio/x-midi'),
 ('MP3 Audio','audio','mpeg','mp3','audio/mpeg'),
 ('MP2 Audio','audio','mpeg','mp2','audio/mpeg'),
 ('MP1 Audio','audio','mpeg','mp1','audio/mpeg'),
 ('MP4 Audio','audio','mp4','mp4,mpg4','audio/mp4'),
 ('MP4 Video','video','mp4','mp4,mpg4','video/mp4');

CREATE TABLE  `mm_admin`.`perms` (
  `id` int(11) NOT NULL auto_increment,
  `user_reports` tinyint(1) default NULL,
  `select_db` tinyint(1) default NULL,
  `file_exts` tinyint(1) default NULL,
  `group_add` tinyint(1) default NULL,
  `group_remove` tinyint(1) default NULL,
  `group_reports` tinyint(1) default NULL,
  `group_permissions` tinyint(1) default NULL,
  `admin_home` tinyint(1) default NULL,
  `admin_users` tinyint(1) default NULL,
  `admin_reports` tinyint(1) default NULL,
  `admin_columns` tinyint(1) default NULL,
  `admin_databases` tinyint(1) default NULL,
  `admin_site` tinyint(1) default NULL,
  `assets` longtext,
  `columns` longtext,
  `sort_tracks` longtext,
  `sort_albums` longtext,
  `opt_home` enum('enabled','disabled') default NULL COMMENT 'Enable/disable the home screen functionality and interface.',
  `opt_view` enum('user','auto','fixed') default NULL COMMENT 'Allow user to choose results view or force ''auto'' or ''fixed'' view.',
  `opt_vista` enum('user','albums','mixed','tracks') default NULL COMMENT 'Allow user to choose results vista or force ''albums'', ''mixed'', or ''tracks'' vista.',
  `opt_browse` enum('user','show','hide') default NULL COMMENT 'Allow user to show/hide metabrowser or force ''show'' or ''hide''.',
  `opt_grouping` enum('user','off','on') default NULL COMMENT 'Allow user to turn grouping on/off for result tracks or force ''on'' or ''off''.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `mm_admin`.`perms` (`id`,`user_reports`,`select_db`,`file_exts`,`group_add`,`group_remove`,`group_reports`,`group_permissions`,`admin_home`,`admin_users`,`admin_reports`,`admin_columns`,`admin_databases`,`admin_site`,`assets`,`columns`,`sort_tracks`,`sort_albums`,`opt_home`,`opt_view`,`opt_vista`,`opt_browse`,`opt_grouping`) VALUES 
 (1,0,1,1,0,0,0,0,0,0,0,0,0,0,'{\"column\":\"Database\",\"new\":1,\"items\":[]}','{\"new\":0,\"items\":{\"Artwork\":1,\"Arranger\":1,\"BPM\":null,\"BWDescription\":null,\"BWOriginator\":null,\"BWOriginatorRef\":null,\"BitDepth\":null,\"CDDescription\":1,\"CDTitle\":1,\"Category\":1,\"ChannelLayout\":null,\"Channels\":1,\"Composer\":1,\"Conductor\":null,\"CreationDate\":null,\"Database\":0,\"Description\":1,\"Designer\":null,\"Duration\":1,\"EntryDate\":null,\"FeaturedInstrument\":null,\"FilePath\":null,\"FileType\":1,\"Filename\":1,\"Key\":null,\"Keywords\":1,\"Library\":1,\"Location\":null,\"LongID\":null,\"Manufacturer\":null,\"Microphone\":null,\"ModificationDate\":1,\"Notes\":1,\"Pathname\":null,\"Performer\":null,\"Popularity\":null,\"Publisher\":1,\"Rating\":null,\"RecID\":null,\"RecMedium\":null,\"RecType\":null,\"Relevance\":1,\"SampleRate\":1,\"Scene\":null,\"ShortID\":null,\"Show\":1,\"Source\":1,\"Split\":null,\"SplitChannels\":null,\"SubCategory\":null,\"Take\":null,\"Tape\":null,\"Time\":0,\"TotalFrames\":null,\"TrackTitle\":1,\"_Channels\":0,\"_Duration\":null,\"_FlatCategory\":null,\"_PictureLink\":0,\"_UMID\":null,\"_WaveformLink\":0,\"Waveform\":1}}','{\"Relevance\":\"ascend\",\"Library\":\"ascend\",\"Volume\":\"descend\",\"Track\":\"ascend\"}','{\"Library\":\"ascend\",\"Volume\":\"descend\"}','enabled','user','user','user','user'),
 (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO `mm_admin`.`perms` (`id`,`user_reports`,`select_db`,`file_exts`,`group_add`,`group_remove`,`group_reports`,`group_permissions`,`admin_home`,`admin_users`,`admin_reports`,`admin_columns`,`admin_databases`,`admin_site`,`assets`,`columns`,`sort_tracks`,`sort_albums`,`opt_home`,`opt_view`,`opt_vista`,`opt_browse`,`opt_grouping`) VALUES 
 (3,1,1,1,1,1,1,1,1,1,1,1,1,1,'{\"column\":\"Database\",\"new\":2,\"items\":[]}','{\"new\":1,\"items\":[]}',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (8,1,1,1,1,1,1,1,1,1,1,1,1,1,'{\"column\":\"Database\",\"new\":2,\"items\":[]}','{\"new\":1,\"items\":[]}',NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 (9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO `mm_admin`.`perms` (`id`,`user_reports`,`select_db`,`file_exts`,`group_add`,`group_remove`,`group_reports`,`group_permissions`,`admin_home`,`admin_users`,`admin_reports`,`admin_columns`,`admin_databases`,`admin_site`,`assets`,`columns`,`sort_tracks`,`sort_albums`,`opt_home`,`opt_view`,`opt_vista`,`opt_browse`,`opt_grouping`) VALUES 
 (10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

CREATE TABLE  `mm_admin`.`proxies` (
  `id` int(11) NOT NULL auto_increment,
  `db_id` int(11) NOT NULL,
  `display` varchar(255) NOT NULL,
  `mime_name` varchar(255) NOT NULL default 'From Metadata' COMMENT '''From Metadata'' = use type given in `metadata` table row',
  `asset_path_prefix` varchar(255) NOT NULL,
  `proxy_path_prefix` varchar(255) NOT NULL,
  `asset_file_prefix` varchar(255) NOT NULL,
  `proxy_file_prefix` varchar(255) NOT NULL default '',
  `asset_file_suffix` varchar(255) NOT NULL default '',
  `proxy_file_suffix` varchar(255) NOT NULL default '',
  `is_asset` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`queries` (
  `id` int(11) NOT NULL auto_increment,
  `databases` varchar(512) NOT NULL,
  `query` varchar(16383) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`reports` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(64) NOT NULL default '' COMMENT 'Report title and template name.',
  `description` varchar(255) NOT NULL default '' COMMENT 'Report description.',
  `time` enum('cday','cweek','cmonth','cyear','lday','lweek','lmonth','lyear','all','custom') NOT NULL default 'cday' COMMENT 'Time period covered by report.',
  `begin` datetime default '0000-00-00 00:00:00' COMMENT 'Start of custom time period.',
  `end` datetime default '0000-00-00 00:00:00' COMMENT 'End of custom time period.',
  `users` int(11) NOT NULL default '0' COMMENT 'User or -group ID to include in report.',
  `tables` varchar(10000) NOT NULL default '' COMMENT 'JSON array of structures representing report tables.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `mm_admin`.`reports` (`id`,`title`,`description`,`time`,`begin`,`end`,`users`,`tables`) VALUES 
 (1,'Activity Log','Chronological log of a user\'s activity.','all',NULL,NULL,0,'[{\"type\":\"detail\",\"title\":\"\",\"events\":[\"login\",\"logout\",\"search\",\"audition\",\"download\",\"error\"],\"groups\":[[\"Users\",\"user\",\"asc\",0,0],[null,\"date\",\"asc\",0,0]],\"fields\":[[null,\"time\",\"\",1,0,0,0,0],[null,\"event\",\"\",2,0,0,0,0],[null,\"info\",\"\",3,0,0,0,0]],\"limit\":null,\"grand\":null}]'),
 (2,'Search Summary','','all','0000-00-00 00:00:00','0000-00-00 00:00:00',0,'[{\"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\"]]}]'),
 (3,'Download Summary','','all',NULL,NULL,0,'[{\"type\":\"summary\",\"title\":\"Top Categories\",\"events\":[\"download\"],\"groups\":[[\"Category\",\"a_category\",\"\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"where\":[],\"limit\":null,\"grand\":null},{\"type\":\"summary\",\"title\":\"Top Libraries\",\"events\":[\"download\"],\"groups\":[[\"Library\",\"a_library\",\"\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"where\":[],\"limit\":null,\"grand\":null},{\"type\":\"summary\",\"title\":\"Top Albums\",\"events\":[\"download\"],\"groups\":[[\"Album\",\"a_cdtitle\",\"\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"where\":[],\"limit\":null,\"grand\":null},{\"type\":\"summary\",\"title\":\"Top Users\",\"events\":[\"download\"],\"groups\":[[\"User\",\"user\",\"\",1,1]],\"fields\":[[\"# Downloads\",\"id\",\"desc\",null,\"count\"],[\"# Assets\",\"a_id\",\"desc\",null,\"dcount\"]],\"where\":[],\"limit\":null,\"grand\":null}]');
INSERT INTO `mm_admin`.`reports` (`id`,`title`,`description`,`time`,`begin`,`end`,`users`,`tables`) VALUES 
 (4,'Download Log','Detailed log of downloads grouped by user.','all',NULL,NULL,0,'[{\"type\":\"detail\",\"title\":\"\",\"events\":[\"download\"],\"groups\":[[\"User\",\"user\",\"asc\",0,1]],\"fields\":[[null,\"datetime\",\"asc\",null,0,0,0,0],[null,\"host_ip\",\"\",null,0,0,0,0],[null,\"a_library\",\"\",null,0,0,0,0],[null,\"a_cdtitle\",\"\",null,0,0,0,0],[\"Type\",\"d_filetype\",\"\",null,0,0,0,0],[null,\"d_filename\",\"\",null,0,0,0,0],[\"Size\",\"d_filebytes\",\"\",null,1,0,0,0]],\"limit\":null,\"grand\":null}]'),
 (5,'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}]');

CREATE TABLE  `mm_admin`.`roles` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `notes` varchar(255) NOT NULL,
  `perm_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;
INSERT INTO `mm_admin`.`roles` (`id`,`name`,`notes`,`perm_id`) VALUES 
 (1,'Administrator','Administrators get full access to all administrative functions, download rights to all assets, and the ability to view all metadata pertaining to an asset.',3),
 (2,'Supervisor','',4),
 (3,'Superuser','',5),
 (4,'User','',6),
 (5,'Guest','',7);

CREATE TABLE  `mm_admin`.`searches` (
  `id` int(11) NOT NULL auto_increment,
  `folder_id` int(11) NOT NULL default '0',
  `name` varchar(128) NOT NULL COMMENT 'Name of this search as it appears in parent project folder.',
  `text` text NOT NULL,
  `created` datetime NOT NULL COMMENT 'Timestamp of the original creation of row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of the last modification of row.',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='User''s saved search queries';

CREATE TABLE  `mm_admin`.`sessions` (
  `id` varchar(255) character set latin1 NOT NULL default '',
  `data` text character set latin1 NOT NULL,
  `touched` int(11) NOT NULL,
  `uid` int(11) default NULL,
  `logout` tinyint(1) NOT NULL default '0',
  `reason` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `uid_index` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE  `mm_admin`.`tracks` (
  `id` int(11) NOT NULL auto_increment,
  `folder_id` int(11) NOT NULL default '0',
  `asset_id` int(11) NOT NULL,
  `created` datetime NOT NULL COMMENT 'Timestamp of the original creation of row.',
  `updated` datetime NOT NULL COMMENT 'Timestamp of the last modification of row.',
  PRIMARY KEY  (`id`),
  KEY `folder_index` (`folder_id`),
  KEY `asset_index` (`asset_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#---------------------------------------------------------------------------------
# mm_cache
#---------------------------------------------------------------------------------

DROP DATABASE IF EXISTS `mm_cache`;
CREATE DATABASE IF NOT EXISTS `mm_cache`;
USE `mm_cache`;

CREATE TABLE  `mm_cache`.`schema` (
  `schema` varchar(30) character set latin1 NOT NULL default '',
  `db_ver` varchar(30) NOT NULL,
  `mm_ver` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=1;
INSERT INTO `mm_cache`.`schema` (`schema`,`db_ver`,`mm_ver`) VALUES ('mm_cache','0.9.22','0.9.22');

