TrilhaEnemDB

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

CREATE SCHEMA studyTrail;
-- Independent tables
CREATE TABLE studyTrail.TrailTopic (
	Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 ModuleId BIGINT NOT NULL,
	Name VARCHAR(40),
 FOREIGN KEY (ModuleId) REFERENCES studyTrail.TrailModule(Id) ON DELETE NO ACTION
);
CREATE TABLE studyTrail.TrailSchedule (
	Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	UserId INT(11) NOT NULL UNIQUE
);
CREATE TABLE studyTrail.TrailScheduleSubject (
	ScheduleId BIGINT NOT NULL,
	SubjectId BIGINT NOT NULL,
	Day SMALLINT NOT NULL,
	PRIMARY KEY (ScheduleId, SubjectId, Day)
);
-- Dependent tables (in order)
CREATE TABLE studyTrail.UserTrailTopic (
	TopicId BIGINT NOT NULL,
	UserId INT(11) NOT NULL,
	Skipped BOOLEAN DEFAULT false,
	CompletionDate DATETIME DEFAULT NULL,
	PRIMARY KEY (TopicId, UserId),
	FOREIGN KEY (TopicId) REFERENCES studyTrail.TrailTopic(Id) ON DELETE NO ACTION
);
CREATE TABLE studyTrail.TrailTopicMaterial (
	Id BIGINT NOT NULL PRIMARY KEY,
	TopicId BIGINT,
	FOREIGN KEY (TopicId) REFERENCES studyTrail.TrailTopic(Id) ON DELETE NO ACTION
);
CREATE TABLE studyTrail.TrailSubject (
	Id BIGINT NOT NULL PRIMARY KEY,
	Name VARCHAR(40) NOT NULL
);
CREATE TABLE studyTrail.TrailModule (
	Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	SubjectId BIGINT,
	FOREIGN KEY (SubjectId) REFERENCES studyTrail.TrailSubject(Id) ON DELETE NO ACTION
);
ALTER TABLE TrailModule
DROP COLUMN TopicId;
ALTER TABLE TrailTopic
ADD COLUMN ModuleId INT(11);
-- Permissions
CREATE USER 'studyTrailApi'@'172.30.%.%' IDENTIFIED BY 'Q3nS5Q399tUl';
GRANT ALL PRIVILEGES ON studyTrail.* TO 'studyTrailApi'@'172.30.%.%';