44 lines
1.4 KiB
SQL
44 lines
1.4 KiB
SQL
-- migrate:up
|
|
CREATE TABLE courses (
|
|
id SERIAL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
-- e.g. CS-477
|
|
code TEXT,
|
|
-- At least one teacher, checked by the backend
|
|
teachers TEXT[] NOT NULL DEFAULT '{}',
|
|
-- Main page of the course (usually moodle)
|
|
main_page_url TEXT
|
|
);
|
|
|
|
-- Kind of a time slot (lecture, exercises, lab, ...). A table rather than an enum so
|
|
-- new kinds can be added without a migration of the slots.
|
|
CREATE TABLE slot_tags (
|
|
id SERIAL PRIMARY KEY,
|
|
"name" JSONB NOT NULL,
|
|
-- Hex color used to display the slots of this kind, e.g. #2563eb
|
|
color TEXT NOT NULL
|
|
);
|
|
|
|
INSERT INTO slot_tags ("name", color) VALUES
|
|
('{"fr": "Cours", "en": "Lecture"}', '#2563eb'),
|
|
('{"fr": "Exercices", "en": "Exercises"}', '#16a34a'),
|
|
('{"fr": "Lab", "en": "Lab"}', '#ea580c');
|
|
|
|
-- Weekly recurring slot of a course. Slots of different courses may overlap.
|
|
CREATE TABLE time_slots (
|
|
id SERIAL PRIMARY KEY,
|
|
course_id INTEGER NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
|
|
tag_id INTEGER NOT NULL REFERENCES slot_tags (id),
|
|
-- ISO weekday: 1 = monday ... 7 = sunday
|
|
weekday SMALLINT NOT NULL CHECK (weekday BETWEEN 1 AND 7),
|
|
start_time TIME NOT NULL,
|
|
end_time TIME NOT NULL,
|
|
CHECK (start_time < end_time)
|
|
);
|
|
|
|
CREATE INDEX time_slots_course_id_idx ON time_slots (course_id);
|
|
|
|
-- migrate:down
|
|
DROP TABLE IF EXISTS time_slots;
|
|
DROP TABLE IF EXISTS slot_tags;
|
|
DROP TABLE IF EXISTS courses;
|