SQLize Online / PHPize Online  /  SQLtest Online

CREATE schema LECTORIUM; CREATE table LECTORIUM.EMPLOYEE( employee_id integer primary key , name_nm varchar(255) , cameraman_flg boolean , videoeditor_flg boolean ); CREATE table LECTORIUM.TEACHER( teacher_id integer primary key , teacher_nm varchar(255) , agree_flg boolean ); CREATE table LECTORIUM.CAMERA( camera_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.MICROPHONE( microphone_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.CAPTURE_CARD( capture_card_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.COMPUTER( computer_id integer primary key , model_nm varchar(255) , state_flg boolean ); CREATE table LECTORIUM.ROOM( room_id integer primary key , room_nm varchar(255) , camera_id integer references LECTORIUM.CAMERA(camera_id) , microphone_id integer references LECTORIUM.MICROPHONE(microphone_id) , capture_card_id integer references LECTORIUM.CAPTURE_CARD(capture_card_id) , computer_id integer references LECTORIUM.COMPUTER(computer_id) ); CREATE table LECTORIUM.SUBJECT( subject_id integer primary key , subject_nm varchar(255) , course_no integer , room_id integer references LECTORIUM.ROOM(room_id) , teacher_id integer references LECTORIUM.TEACHER(teacher_id) ); CREATE table LECTORIUM.LECTURE( lecture_id integer primary key , subject_id integer references LECTORIUM.SUBJECT(subject_id) , room_id integer references LECTORIUM.ROOM(room_id) , shooting_dt date , cameraman_nm varchar(255) , videoeditor_nm varchar(255) , ready_flg boolean ); CREATE table LECTORIUM.LECTURE_X_EMPLOYEE( employee_id integer references LECTORIUM.EMPLOYEE(employee_id) , lecture_id integer references LECTORIUM.LECTURE(lecture_id) , role_nm varchar(255) , start_dttm timestamp , end_dttm timestamp ); INSERT INTO LECTORIUM.EMPLOYEE VALUES (1, 'Иванов Максим Игоревич', TRUE, TRUE) , (2, 'Петров Иван Ильич', TRUE, FALSE) , (3, 'Павлов Андрей Алексеевич', FALSE, TRUE) , (4, 'Дмитриев Дмитрий Дмитревич', TRUE, TRUE) , (5, 'Ульянова Мария Евгеньевна', TRUE, FALSE) , (6, 'Бурундуков Михаил Алексеевич', TRUE, TRUE) , (7, 'Губкин Алексей Ильич', TRUE, TRUE) , (8, 'Шаронов Арон Аронович', FALSE, TRUE) , (9, 'Златопольский Григорий Евгеньевич', TRUE, FALSE) , (10, 'Родригес Марио Леонович', FALSE, TRUE); INSERT INTO LECTORIUM.TEACHER VALUES (1, 'Андреев Борис Викторович', TRUE) , (2, 'Гаврилин Дмитрий Евгеньевич', TRUE) , (3, 'Ёршик Жанна Зиновьевна', TRUE) , (4, 'Иод Иоахим Кириллович', TRUE) , (5, 'Лампель Максим Николаевич', TRUE) , (6, 'Остроградский Павел Романович', TRUE) , (7, 'Савельева Татьяна Устиновна', TRUE) , (8, 'Франкенштейн Харитон Харитонович', TRUE) , (9, 'Целков Роман Романович', TRUE) , (10, 'Ястреб Эмиль Юрьевич', TRUE); INSERT INTO LECTORIUM.CAMERA VALUES (1, 'Pony HD100500', TRUE) , (2, 'Pony HD100600', TRUE) , (3, 'Monosonic Ultra4', TRUE) , (4, 'Monosonic Ultra5', TRUE) , (5, 'Nikonor 666HD', TRUE) , (6, 'Nikonor 666HD', TRUE) , (7, 'Pony HD100500', TRUE) , (8, 'Pony HD100600', FALSE) , (9, 'Zenith 2', TRUE) , (10, 'Zenith 2', FALSE); INSERT INTO LECTORIUM.MICROPHONE VALUES (1, 'Hahaheiser 333', TRUE) , (2, 'Hahaheiser 343', TRUE) , (3, 'Hahaheiser 333', TRUE) , (4, 'Hahaheiser 343', TRUE) , (5, 'Hahaheiser 666', TRUE) , (6, 'Hahaheiser 343', TRUE) , (7, 'Hahaheiser 666', TRUE) , (8, 'Hahaheiser 333', FALSE) , (9, 'Hahaheiser 343', TRUE) , (10, 'Hahaheiser 343', FALSE); INSERT INTO LECTORIUM.CAPTURE_CARD VALUES (1, 'AreYouMedia Portable 1', TRUE) , (2, 'AreYouMedia Portable 1', TRUE) , (3, 'AreYouMedia Portable 4', TRUE) , (4, 'AreYouMedia Portable 4', TRUE) , (5, 'AreYouMedia Portable 1', TRUE) , (6, 'AreYouMedia Portable 4', TRUE) , (7, 'AreYouMedia Portable 1', TRUE) , (8, 'AreYouMedia Portable 3', FALSE) , (9, 'AreYouMedia Portable 1', TRUE) , (10, 'AreYouMedia Portable 2', TRUE); INSERT INTO LECTORIUM.COMPUTER VALUES (1, 'Lenoldo 640', TRUE) , (2, 'Lenoldo 950', TRUE) , (3, 'Lenoldo 960', TRUE) , (4, 'Lenoldo 640', TRUE) , (5, 'Lenoldo 330', TRUE) , (6, 'Lenoldo 110', TRUE) , (7, 'Lenoldo 640', TRUE) , (8, 'Lenoldo 114', TRUE) , (9, 'Lenoldo 252', TRUE) , (10, 'Lenoldo 252', TRUE); INSERT INTO LECTORIUM.ROOM VALUES (1, 'Арктика 225', 1, 2, 3, 4) , (2, 'Арктика 227', 5, 6, 7, 8) , (3, 'Антарктика 325', 9, 10, 1, 2) , (4, 'Антарктика 327', 3, 4, 5, 6) , (5, 'Африка 108', 7, 8, 9, 10) , (6, 'Африка 109', 2, 3, 4, 5) , (7, 'Азия 222', 6, 7, 8, 9) , (8, 'Европа 666', 10, 1, 2, 3) , (9, 'Австралия 112', 4, 5, 6, 7) , (10, 'Америка 911', 8, 9, 10, 1); INSERT INTO LECTORIUM.SUBJECT VALUES (1, 'Алгебра и геометрия', 1, 2, 3) , (2, 'Алгоритмы и структуры данных', 1, 6, 7) , (3, 'Математический анализ', 1, 10, 1) , (4, 'Теория вероятностей', 2, 4, 5) , (5, 'История компьютерных технологий', 2, 8, 9) , (6, 'Многопоточная синхронизация', 2, 3, 4) , (7, 'Машинное обучение', 3, 7, 8) , (8, 'Математическая статистика', 3, 1, 2) , (9, 'Компьютерная графика', 4, 5, 6) , (10, 'Философия', 4, 9, 10); INSERT INTO LECTORIUM.LECTURE VALUES (1, 1, 2, '2022-02-02', 'Петров Иван Ильич', 'Иванов Максим Игоревич', TRUE) , (2, 1, 2, '2022-02-09', 'Петров Иван Ильич', 'Иванов Максим Игоревич', FALSE) , (3, 2, 6, '2022-02-02', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (4, 2, 6, '2022-02-09', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (5, 3, 10, '2022-02-02', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (6, 3, 10, '2022-02-04', 'Дмитриев Дмитрий Дмитревич', 'Павлов Андрей Алексеевич', TRUE) , (7, 4, 4, '2022-02-03', 'Бурундуков Михаил Алексеевич', 'Бурундуков Михаил Алексеевич', TRUE) , (8, 4, 4, '2022-02-10', 'Бурундуков Михаил Алексеевич', 'Бурундуков Михаил Алексеевич', TRUE) , (9, 5, 8, '2022-02-03', 'Ульянова Мария Евгеньевна', 'Бурундуков Михаил Алексеевич', TRUE) , (10, 5, 8, '2022-02-10', 'Ульянова Мария Евгеньевна', 'Бурундуков Михаил Алексеевич', TRUE); INSERT INTO LECTORIUM.LECTURE_X_EMPLOYEE VALUES (2, 1, 'Оператор', '2022-02-02 12:20:00', '2022-02-02 13:45:00') , (2, 2, 'Оператор', '2022-02-09 12:20:00', '2022-02-09 13:45:00') , (1, 1, 'Монтажёр', '2022-02-02 22:00:00', '2022-02-02 23:00:00') , (1, 2, 'Монтажёр', NULL, NULL) , (4, 3, 'Оператор', '2022-02-02 13:55:00', '2022-02-02 15:20:00') , (4, 5, 'Оператор', '2022-02-02 15:30:00', '2022-02-02 16:55:00') , (4, 6, 'Оператор', '2022-02-04 13:55:00', '2022-02-04 15:20:00') , (4, 4, 'Оператор', '2022-02-09 13:55:00', '2022-02-09 15:20:00') , (3, 3, 'Монтажёр', '2022-02-02 22:55:00', '2022-02-02 23:45:00') , (3, 4, 'Монтажёр', '2022-02-09 22:50:00', '2022-02-09 23:40:00') , (3, 5, 'Монтажёр', '2022-02-10 22:50:00', '2022-02-10 23:40:00') , (3, 6, 'Монтажёр', '2022-02-11 22:50:00', '2022-02-11 23:40:00') , (6, 7, 'Оператор', '2022-02-03 09:00:00', '2022-02-03 10:25:00') , (6, 8, 'Оператор', '2022-02-10 09:00:00', '2022-02-10 10:25:00') , (6, 7, 'Монтажёр', '2022-02-11 09:00:00', '2022-02-11 10:25:00') , (6, 8, 'Монтажёр', '2022-02-11 11:00:00', '2022-02-11 12:25:00') , (6, 9, 'Монтажёр', '2022-02-11 13:00:00', '2022-02-11 14:25:00') , (6, 10, 'Монтажёр', '2022-02-11 15:00:00', '2022-02-11 16:25:00') , (5, 9, 'Оператор', '2022-02-03 10:45:00', '2022-02-03 12:10:00') , (5, 10, 'Оператор', '2022-02-10 10:45:00', '2022-02-10 12:10:00'); -- Indices -- All our tables contain IDs and that makes indexation much easier CREATE INDEX ON LECTORIUM.EMPLOYEE(employee_id); CREATE INDEX ON LECTORIUM.TEACHER(teacher_id); CREATE INDEX ON LECTORIUM.CAMERA(camera_id); CREATE INDEX ON LECTORIUM.MICROPHONE(microphone_id); CREATE INDEX ON LECTORIUM.CAPTURE_CARD(capture_card_id); CREATE INDEX ON LECTORIUM.COMPUTER(computer_id); CREATE INDEX ON LECTORIUM.ROOM(room_id); CREATE INDEX ON LECTORIUM.SUBJECT(subject_id); CREATE INDEX ON LECTORIUM.LECTURE(lecture_id); CREATE INDEX ON LECTORIUM.LECTURE_X_EMPLOYEE(employee_id, lecture_id); -- Stored procedures -- 1: updates lecture state if a videoeditor finished his/her work on a given lecture CREATE PROCEDURE update_lecture_state() RETURNS TRIGGER AS $$ BEGIN IF (OLD.end_dttm = NULL AND NEW.start_dttm <> NULL AND NEW.end_dttm <> NULL AND OLD.role_nm = 'Монтажёр' AND NEW.role_nm = 'Монтажёр') THEN UPDATE LECTORIUM.LECTURE AS l SET ready_flg = TRUE WHERE NEW.lecture_id = l.lecture_id; END IF; END; $$ LANGUAGE plpgsql; -- 2: helps to insert lecture info CREATE PROCEDURE insert_lecture_info() RETURNS TRIGGER AS $$ DECLARE cur_flag boolean; cur_line record; temp_employee_id integer; temp_id integer; temp_role_nm varchar(255); temp_start_dttm timestamp; temp_end_dttm timestamp; BEGIN -- Check if our cameraman is valid FOR cur_flag IN SELECT cameraman_flg FROM LECTORIUM.EMPLOYEE e WHERE e.name_nm = NEW.cameraman_nm LOOP IF (cur_flag = FALSE) THEN RETURN NULL; END IF; END LOOP; -- Check if our videoeditor is valid FOR cur_flag IN SELECT videoeditor_flg FROM LECTORIUM.EMPLOYEE e WHERE e.name_nm = NEW.videoeditor_nm LOOP IF (cur_flag = FALSE) THEN RETURN NULL; END IF; END LOOP; -- Check if the room is valid FOR cur_line IN SELECT subject_id, room_id FROM LECTORIUM.SUBJECT s WHERE s.subject_id = NEW.subject_id LOOP IF (cur_line.room_id <> NEW.room_id) THEN RETURN NULL; END IF; END LOOP; -- Inserting cameraman value FOR temp_id IN SELECT employee_id FROM LECTORIUM.EMPLOYEE e WHERE e.name_nm = NEW.cameraman_nm LOOP temp_employee_id := temp_id; END LOOP; temp_role_nm := 'Оператор'; temp_start_dttm := NEW.shooting_dt + interval '1 minute'; temp_end_dttm := NEW.shooting_dt + interval '2 minute'; INSERT INTO LECTORIUM.LECTURE_X_EMPLOYEE VALUES (temp_employee_id , NEW.lecture_id , temp_role_nm , temp_start_dttm , temp_end_dttm); -- Inserting videoeditor value FOR temp_id IN SELECT employee_id FROM LECTORIUM.EMPLOYEE e WHERE e.name_nm = NEW.cameraman_nm LOOP temp_employee_id := temp_id; END LOOP; temp_role_nm := 'Монтажёр'; IF (NEW.ready_flg = TRUE) THEN temp_start_dttm := current_timestamp + interval '1 minute'; temp_end_dttm := current_timestamp + interval '2 minute'; ELSIF (NEW.ready_flg = FALSE) THEN temp_start_dttm := NULL; temp_end_dttm := NULL; END IF; INSERT INTO LECTORIUM.LECTURE_X_EMPLOYEE VALUES (temp_employee_id , NEW.lecture_id , temp_role_nm , temp_start_dttm , temp_end_dttm); RETURN NEW; END; $$ LANGUAGE plpgsql; -- Triggers -- 1 CREATE TRIGGER update_lecture_state AFTER UPDATE ON LECTORIUM.LECTURE_X_EMPLOYEE FOR EACH STATEMENT EXECUTE PROCEDURE update_lecture_state(); -- 2 CREATE TRIGGER insert_lecture_info BEFORE INSERT ON LECTORIUM.LECTURE FOR EACH STATEMENT EXECUTE PROCEDURE insert_lecture_info();