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 INT PRIMARY KEY, NAME varchar(64), DEFAULT_VALUE varchar(8), created_at datetime, updated_at datetime) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table FILE_META (ID INT PRIMARY KEY, SONG_NAME VARCHAR(128) NOT NULL, FILE_NAME varchar(128), LAST_UPDATED datetime, MUSIC_KEY varchar(16), COMMENTS varchar(1024), TYPE varchar(12), created_at datetime, updated_at datetime)
 ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table KEYWORD_FILE_META_XREF (ID INT PRIMARY KEY, 
KEYWORD_ID INT NOT NULL, 
FILE_META_ID INT NOT NULL, created_at datetime, updated_at datetime,
FOREIGN KEY (KEYWORD_ID) REFERENCES KEYWORD (ID),
FOREIGN KEY (FILE_META_ID) REFERENCES FILE_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;;