===== Cantata 2 Database =====

^Database Name|quizkidn_cantata2|
^User Name|quizkidn_can2|

  * create database quizkidn_cantata2;
  * create user 'quizkidn_can2'@'%' identified by 'cantatapass2';
  * grant all on quizkidn_cantata2.* to 'quizkidn_can2'@'%';
  * FLUSH PRIVILEGES;
  * select user from mysql.user;

^SONG_META|id|VARCHAR(50)|
^:::|TITLE|VARCHAR(100)|

^FILE_META|id|VARCHAR(50)|
^:::|SONG_META_ID|VARCHAR(50)|
^:::|FILE_NAME|VARCHAR(128)|Will hold URL if TYPE=URL|
^:::|LAST_UPDATED|DATETIME|
^:::|MUSIC_KEY|VARCHAR(16)|
^:::|COMMENTS|VARCHAR(1024)|
^:::|TYPE|VARCHAR(12)|One of 'Chart', 'Audio', or 'URL'|

^KEYWORD|id|VARCHAR(50)|
^:::|NAME|VARCHAR(64)|
^:::|DEFAULT_VALUE|VARCHAR(8)|

^KEYWORD_SONG_META_XREF|id|VARCHAR(50)|
^:::|KEYWORD_ID|VARCHAR(50)|
^:::|SONG_META_ID|VARCHAR(50)|

^LIST_META|id|VARCHAR(50)|
^:::|LIST_DATE|DATETIME|
^:::|TYPE|VARCHAR(64)|
^:::|COMMENTS|VARCHAR(1024)|

^LIST_ITEM|id|VARCHAR(50)|
^:::|FILE_META_ID|VARCHAR(50)|
^:::|DISPLAY_TITLE|VARCHAR(100)|
^:::|SONG_ORDER|INT|

==== SQL ====
<code>
create table KEYWORD (ID VARCHAR(50) PRIMARY KEY, NAME varchar(64), DEFAULT_VALUE varchar(8)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
create table SONG_META (ID VARCHAR(50) PRIMARY KEY, TITLE varchar(100)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
create table FILE_META (ID VARCHAR(50) PRIMARY KEY, SONG_META_ID VARCHAR(50) NOT NULL, FILE_NAME varchar(64), LAST_UPDATED datetime, MUSIC_KEY varchar(16), 
COMMENTS varchar(1024), TYPE varchar(12),
FOREIGN KEY (SONG_META_ID) REFERENCES SONG_META(ID)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table KEYWORD_SONG_META_XREF (ID VARCHAR(50) PRIMARY KEY, 
KEYWORD_ID VARCHAR(50) NOT NULL, 
SONG_META_ID VARCHAR(50) NOT NULL,
FOREIGN KEY (KEYWORD_ID) REFERENCES KEYWORD (ID),
FOREIGN KEY (SONG_META_ID) REFERENCES SONG_META (ID)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table LIST_META (ID VARCHAR(50) PRIMARY KEY, LIST_DATE DATETIME, 
TYPE VARCHAR(64), COMMENTS VARCHAR(1024)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table LIST_ITEM (ID VARCHAR(50) PRIMARY KEY, LIST_META_ID VARCHAR(50), FILE_META_ID VARCHAR(50), 
DISPLAY_TITLE VARCHAR(100), SONG_ORDER int,
FOREIGN KEY (LIST_META_ID) REFERENCES LIST_META(ID),
FOREIGN KEY (FILE_META_ID) REFERENCES FILE_META(ID)) ENGINE=InnoDB DEFAULT CHARSET=latin1;;

</code>