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 KEYWORDS (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 (ID INT PRIMARY KEY, LIST_DATE DATETIME, 
NAME VARCHAR(100), TYPE VARCHAR(64), COMMENTS VARCHAR(1024), created_at datetime, updated_at datetime) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table LIST_ITEM (ID INT PRIMARY KEY, LIST_ID INT, FILE_META_ID INT, 
DISPLAY_TITLE VARCHAR(100), SONG_ORDER int, created_at datetime, updated_at datetime, 
FOREIGN KEY (LIST_ID) REFERENCES LIST(ID),
FOREIGN KEY (FILE_META_ID) REFERENCES FILE_META(ID)) ENGINE=InnoDB DEFAULT CHARSET=latin1;;