SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE `karaokedb`
    CHARACTER SET 'utf8'
    COLLATE 'utf8_general_ci';

USE `karaokedb`;

#
# Structure for the `parameter` table : 
#

CREATE TABLE `parameter` (
  `name` tinytext character set latin1 NOT NULL,
  `value` tinytext character set latin1,
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`name`(1)),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Structure for the `producer` table : 
#

CREATE TABLE `producer` (
  `name` text,
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 9216 kB';

#
# Structure for the `song` table : 
#

CREATE TABLE `song` (
  `name` text NOT NULL,
  `remark` text,
  `update_date` date default NULL,
  `id` int(11) NOT NULL auto_increment,
  `producer_id` int(11) NOT NULL,
  `status` tinytext character set latin1 NOT NULL,
  `create_date` date default NULL,
  `word_count` int(11) default NULL,
  `stroke_count` int(11) default NULL,
  `hit_count` int(11) default NULL,
  `language` tinytext character set latin1 NOT NULL,
  `type` tinytext character set latin1 NOT NULL,
  `media_location` text NOT NULL,
  `media_type` tinytext character set latin1 NOT NULL,
  `audio_balance` tinytext character set latin1,
  `publish_date` date default NULL,
  PRIMARY KEY  (`id`),
  KEY `word_count` (`word_count`),
  KEY `stroke_count` (`stroke_count`),
  KEY `hit_count` (`hit_count`),
  KEY `language_id` (`language`(1)),
  KEY `song_producer_fk` (`producer_id`),
  CONSTRAINT `song_producer_fk` FOREIGN KEY (`producer_id`) REFERENCES `producer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 9216 kB; (`producer_id`) REFER `karaokedb/produ';

#
# Structure for the `playing_queue` table : 
#

CREATE TABLE `playing_queue` (
  `song_id` int(11) NOT NULL,
  `selection_order` int(11) default '0',
  `status` tinytext character set latin1,
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `song_id` (`song_id`),
  CONSTRAINT `playing_queue_song_fk` FOREIGN KEY (`song_id`) REFERENCES `song` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 9216 kB; (`song_id`) REFER `karaokedb/song`(`id';

#
# Structure for the `singer` table : 
#

CREATE TABLE `singer` (
  `id` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  `type` text NOT NULL COMMENT 'the type of the singer, such as single, pair or band',
  `picture` mediumblob,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 9216 kB';

#
# Structure for the `song_singer_relation` table : 
#

CREATE TABLE `song_singer_relation` (
  `song_id` int(11) NOT NULL,
  `singer_id` int(11) NOT NULL,
  `type` tinytext character set latin1 NOT NULL,
  PRIMARY KEY  (`song_id`,`singer_id`),
  KEY `song_id` (`song_id`),
  KEY `singer_id` (`singer_id`),
  CONSTRAINT `song_singer_relation_singer_fk` FOREIGN KEY (`singer_id`) REFERENCES `singer` (`id`),
  CONSTRAINT `song_singer_relation_song_fk` FOREIGN KEY (`song_id`) REFERENCES `song` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 9216 kB; (`singer_id`) REFER `karaokedb/singer`';

#
# Data for the `parameter` table  (LIMIT 0,500)
#

INSERT INTO `parameter` (`name`, `value`, `id`) VALUES 
  ('isEnabledAccompanySound','false',1),
  ('karaokeSongRepository','file:/g:/KaraokeRepository',2);

COMMIT;