main.sql

CREATE TABLE Cues (
	CueID BIGINT NOT NULL UNIQUE,
	CueName varchar(255) NOT NULL,
	TrackID BIGINT NOT NULL,
	PlayTime DATETIME NOT NULL,
	Repeats BOOLEAN NOT NULL,
	RepeatMonday BOOLEAN NOT NULL,
	RepeatTuesday BOOLEAN NOT NULL,
	RepeatWednesday BOOLEAN NOT NULL,
	RepeatThursday BOOLEAN NOT NULL,
	RepeatFriday BOOLEAN NOT NULL,
	RepeatSaturday BOOLEAN NOT NULL,
	RepeatSunday BOOLEAN NOT NULL,
	Enabled BOOLEAN NOT NULL,
    Created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	Modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (CueID),
    CONSTRAINT fk_Cues_Tracks_TrackID FOREIGN KEY (TrackID) REFERENCES Tracks(TrackID) ON DELETE CASCADE
);

CREATE TABLE Grids (
	GridID BIGINT NOT NULL UNIQUE,
	GridName varchar(255) NOT NULL,
	Layout varchar(10240) DEFAULT "{}",
    Created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	Modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (GridID)
);

CREATE TABLE GridItems (
	GridItemID BIGINT NOT NULL UNIQUE,
	GridItemName varchar(255) NOT NULL,
	GridItemColour varchar(255) NOT NULL,
	TrackID BIGINT NOT NULL,
	GridID BIGINT NOT NULL,
    Created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	Modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (GridItemID),
    CONSTRAINT fk_Grids_Grids_GridID FOREIGN KEY (GridID) REFERENCES Grids(GridID) ON DELETE CASCADE,
	CONSTRAINT fk_Grids_Tracks_TrackID FOREIGN KEY (TrackID) REFERENCES Tracks(TrackID) ON DELETE CASCADE
);

CREATE TABLE Tracks (
	TrackID BIGINT NOT NULL UNIQUE,
    TrackName varchar(255) NOT NULL,
	TrackType varchar(255) DEFAULT "music" NOT NULL,
	Created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	Modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (TrackID)
);

CREATE TABLE Users (
	UserID BIGINT NOT NULL UNIQUE,
	Email varchar(255) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
	DisplayName varchar(255) NOT NULL,
	Password varchar(2048) NOT NULL,
    Access int(1) NOT NULL,
	Enabled BOOLEAN NOT NULL DEFAULT True,
	Created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	Modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (UserID)
);

CREATE TABLE Settings (
	Feild varchar(255) UNIQUE,
    Data varchar(1024) NOT NULL,
	Created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	Modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (Feild)
);