51 lines
2.6 KiB
SQL
51 lines
2.6 KiB
SQL
-- migrate:up
|
|
|
|
-- What each tag can label: time slots, tasks and/or course files
|
|
ALTER TABLE tags ADD COLUMN scopes TEXT[] NOT NULL DEFAULT '{}'
|
|
CHECK (scopes <@ ARRAY['slot', 'task', 'file']);
|
|
-- Stable name, for the tags the backend needs to find (e.g. `notes` for the lecture notes)
|
|
ALTER TABLE tags ADD COLUMN slug TEXT UNIQUE;
|
|
UPDATE tags SET scopes = '{slot,task}', slug = lower("name"->>'en')
|
|
WHERE "name"->>'en' IN ('Lecture', 'Exercises', 'Lab');
|
|
UPDATE tags SET scopes = '{task}', slug = lower("name"->>'en')
|
|
WHERE "name"->>'en' IN ('Homework', 'Exam', 'Project');
|
|
INSERT INTO tags ("name", color, scopes, slug) VALUES
|
|
('{"fr": "Slides", "en": "Slides"}', '#0d9488', '{file}', 'slides'),
|
|
('{"fr": "Notes", "en": "Notes"}', '#ca8a04', '{file}', 'notes');
|
|
|
|
-- The lecture notes become the first kind of course files. Uploaded files (slides...)
|
|
-- will be the second one. Ids are kept: they are part of the paths in the storage.
|
|
ALTER TABLE lecture_notes RENAME TO course_files;
|
|
ALTER TABLE course_files RENAME CONSTRAINT lecture_notes_pkey TO course_files_pkey;
|
|
ALTER TABLE course_files RENAME CONSTRAINT lecture_notes_course_id_fkey TO course_files_course_id_fkey;
|
|
ALTER SEQUENCE lecture_notes_id_seq RENAME TO course_files_id_seq;
|
|
ALTER INDEX lecture_notes_course_id_idx RENAME TO course_files_course_id_idx;
|
|
ALTER TABLE course_files RENAME COLUMN title TO "name";
|
|
-- `note`: markdown written in the app and rendered to pdf, `upload`: any file
|
|
ALTER TABLE course_files ADD COLUMN kind TEXT NOT NULL DEFAULT 'note'
|
|
CHECK (kind IN ('note', 'upload'));
|
|
ALTER TABLE course_files ALTER COLUMN kind DROP DEFAULT;
|
|
|
|
CREATE TABLE file_tags (
|
|
file_id INTEGER NOT NULL REFERENCES course_files (id) ON DELETE CASCADE,
|
|
tag_id INTEGER NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
|
|
PRIMARY KEY (file_id, tag_id)
|
|
);
|
|
|
|
INSERT INTO file_tags (file_id, tag_id)
|
|
SELECT f.id, t.id FROM course_files f, tags t WHERE t.slug = 'notes';
|
|
|
|
-- migrate:down
|
|
DROP TABLE IF EXISTS file_tags;
|
|
DELETE FROM course_files WHERE kind <> 'note';
|
|
ALTER TABLE course_files DROP COLUMN kind;
|
|
ALTER TABLE course_files RENAME COLUMN "name" TO title;
|
|
ALTER INDEX course_files_course_id_idx RENAME TO lecture_notes_course_id_idx;
|
|
ALTER SEQUENCE course_files_id_seq RENAME TO lecture_notes_id_seq;
|
|
ALTER TABLE course_files RENAME CONSTRAINT course_files_course_id_fkey TO lecture_notes_course_id_fkey;
|
|
ALTER TABLE course_files RENAME CONSTRAINT course_files_pkey TO lecture_notes_pkey;
|
|
ALTER TABLE course_files RENAME TO lecture_notes;
|
|
|
|
DELETE FROM tags WHERE scopes = '{file}';
|
|
ALTER TABLE tags DROP COLUMN slug;
|
|
ALTER TABLE tags DROP COLUMN scopes;
|