Dan's Wiki

Compared to Cantata2, this Cantata3 database information takes into account the move to Laravel. Also, when setting up the Model/DB Tables, noticed that the Keywords for Cantata2 only applied to the Song Level, but perhaps should have been at the File level as well. This would allow filtering or tagging for various people leading - to make it quicker to select keys, etc.

• create database quizkidn_cantata3;
• create user 'quizkidn_can3'@'%' identified by 'cantatapass3';
• grant all on quizkidn_cantata3.* to 'quizkidn_can3'@'%';
• FLUSH PRIVILEGES;
• select user from mysql.user;

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;;