Context Navigation

source: sql/advanced.sql

Last change on this file was 8ee90bd, checked in by Stefan-Saveski <stefansaveski19@…>, 5 hours ago
Add advanced database development script for Phase 7 normalization
Property mode set to `100644`
File size: 24.0 KB

Line
1	-- ============================================================
2	-- IKnow / FINKI - Phase 7: advanced database development
3	--
4	-- Run after schema_creation.sql and data_load.sql:
5	-- schema_creation.sql -> tables, enums
6	-- data_load.sql -> sample data
7	-- advanced.sql -> this file
8	--
9	-- Re-runnable: every object is created with OR REPLACE or dropped first.
10	-- ============================================================
11
12	SET search_path TO project;
13
14	-- ============================================================
15	-- 1. CUSTOM DOMAINS
16	-- One place to define what a valid value looks like, reused by
17	-- every column of that kind.
18	-- ============================================================
19
20	DROP DOMAIN IF EXISTS email_address CASCADE;
21	CREATE DOMAIN email_address AS VARCHAR(150)
22	CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
23
24	DROP DOMAIN IF EXISTS embg_number CASCADE;
25	CREATE DOMAIN embg_number AS VARCHAR(13)
26	CHECK (VALUE ~ '^[0-9]{13}$');
27
28	DROP DOMAIN IF EXISTS student_index CASCADE;
29	CREATE DOMAIN student_index AS VARCHAR(20)
30	CHECK (VALUE ~ '^[0-9]{6}$');
31
32	DROP DOMAIN IF EXISTS phone_number CASCADE;
33	CREATE DOMAIN phone_number AS VARCHAR(20)
34	CHECK (VALUE ~ '^[0-9+][0-9 /-]{5,19}$');
35
36	DROP DOMAIN IF EXISTS money_amount CASCADE;
37	CREATE DOMAIN money_amount AS INTEGER
38	CHECK (VALUE >= 0);
39
40	DROP DOMAIN IF EXISTS gpa_value CASCADE;
41	CREATE DOMAIN gpa_value AS REAL
42	CHECK (VALUE >= 2.0 AND VALUE <= 5.0);
43
44	DROP DOMAIN IF EXISTS credit_points CASCADE;
45	CREATE DOMAIN credit_points AS INTEGER
46	CHECK (VALUE > 0 AND VALUE <= 30);
47
48	ALTER TABLE users ALTER COLUMN email TYPE email_address;
49	ALTER TABLE users ALTER COLUMN embg TYPE embg_number;
50	ALTER TABLE users ALTER COLUMN "index" TYPE student_index;
51	ALTER TABLE contact ALTER COLUMN microsoft_email TYPE email_address;
52	ALTER TABLE contact ALTER COLUMN number TYPE phone_number;
53	ALTER TABLE payment ALTER COLUMN amount TYPE money_amount;
54	ALTER TABLE documents ALTER COLUMN cost TYPE money_amount;
55	ALTER TABLE high_school ALTER COLUMN gpa TYPE gpa_value;
56	ALTER TABLE subjects ALTER COLUMN awarded_credits TYPE credit_points;
57
58	-- ============================================================
59	-- 2. MULTI-TENANCY: several faculties in one database
60	-- ============================================================
61
62	CREATE TABLE IF NOT EXISTS faculty (
63	id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
64	short_name VARCHAR(20) NOT NULL UNIQUE,
65	full_name VARCHAR(200) NOT NULL,
66	university VARCHAR(200) NOT NULL
67	);
68
69	INSERT INTO faculty (id, short_name, full_name, university)
70	VALUES (1, 'FINKI', 'Факултет за информатички науки и компјутерско инженерство',
71	'Универзитет „Св. Кирил и Методиј“ - Скопје')
72	ON CONFLICT (short_name) DO NOTHING;
73
74	-- The tenant key goes on the root entities. Everything else inherits its
75	-- faculty through them, so the column is not repeated on every table.
76	ALTER TABLE users ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
77	ALTER TABLE major ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
78	ALTER TABLE subjects ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
79	ALTER TABLE active_semesters ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
80	ALTER TABLE documents ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
81
82	-- subjects.code and subjects.name were globally unique; with several
83	-- faculties they only have to be unique inside one faculty.
84	ALTER TABLE subjects DROP CONSTRAINT IF EXISTS subjects_code_key;
85	ALTER TABLE subjects DROP CONSTRAINT IF EXISTS subjects_name_key;
86	DROP INDEX IF EXISTS subjects_faculty_code_key;
87	DROP INDEX IF EXISTS subjects_faculty_name_key;
88	CREATE UNIQUE INDEX subjects_faculty_code_key ON subjects (faculty_id, code);
89	CREATE UNIQUE INDEX subjects_faculty_name_key ON subjects (faculty_id, name);
90
91	-- The faculty of the current session. Returns NULL when nothing is set,
92	-- which the policies below read as "no tenant filter".
93	CREATE OR REPLACE FUNCTION current_faculty()
94	RETURNS INTEGER
95	LANGUAGE plpgsql STABLE
96	AS $$
97	BEGIN
98	RETURN nullif(current_setting('app.current_faculty', TRUE), '')::INTEGER;
99	EXCEPTION
100	WHEN others THEN RETURN NULL;
101	END;
102	$$;
103
104	CREATE OR REPLACE PROCEDURE set_current_faculty(p_faculty INTEGER)
105	LANGUAGE plpgsql
106	AS $$
107	BEGIN
108	IF p_faculty IS NOT NULL AND NOT EXISTS (SELECT 1 FROM faculty WHERE id = p_faculty) THEN
109	RAISE EXCEPTION 'Faculty % does not exist', p_faculty;
110	END IF;
111	PERFORM set_config('app.current_faculty', coalesce(p_faculty::TEXT, ''), FALSE);
112	END;
113	$$;
114
115	-- Row level security. The table owner bypasses these policies unless
116	-- FORCE ROW LEVEL SECURITY is used, so in production the application
117	-- connects with a separate, non-owner role.
118	ALTER TABLE users ENABLE ROW LEVEL SECURITY;
119	ALTER TABLE major ENABLE ROW LEVEL SECURITY;
120	ALTER TABLE subjects ENABLE ROW LEVEL SECURITY;
121	ALTER TABLE active_semesters ENABLE ROW LEVEL SECURITY;
122	ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
123
124	DROP POLICY IF EXISTS tenant_isolation ON users;
125	DROP POLICY IF EXISTS tenant_isolation ON major;
126	DROP POLICY IF EXISTS tenant_isolation ON subjects;
127	DROP POLICY IF EXISTS tenant_isolation ON active_semesters;
128	DROP POLICY IF EXISTS tenant_isolation ON documents;
129
130	CREATE POLICY tenant_isolation ON users
131	USING (current_faculty() IS NULL OR faculty_id = current_faculty());
132	CREATE POLICY tenant_isolation ON major
133	USING (current_faculty() IS NULL OR faculty_id = current_faculty());
134	CREATE POLICY tenant_isolation ON subjects
135	USING (current_faculty() IS NULL OR faculty_id = current_faculty());
136	CREATE POLICY tenant_isolation ON active_semesters
137	USING (current_faculty() IS NULL OR faculty_id = current_faculty());
138	CREATE POLICY tenant_isolation ON documents
139	USING (current_faculty() IS NULL OR faculty_id = current_faculty());
140
141	-- A foreign key cannot express "these three rows must belong to the same
142	-- faculty", so it is enforced with a trigger.
143	CREATE OR REPLACE FUNCTION check_enrolment_same_faculty()
144	RETURNS TRIGGER
145	LANGUAGE plpgsql
146	AS $$
147	DECLARE
148	f_student INTEGER;
149	f_major INTEGER;
150	f_semester INTEGER;
151	BEGIN
152	SELECT faculty_id INTO f_student FROM users WHERE id = NEW.user_id;
153	SELECT faculty_id INTO f_major FROM major WHERE id = NEW.major_id;
154	SELECT faculty_id INTO f_semester FROM active_semesters WHERE id = NEW.semester_id;
155
156	IF f_student IS DISTINCT FROM f_major OR f_student IS DISTINCT FROM f_semester THEN
157	RAISE EXCEPTION
158	'Cross-faculty enrolment: student belongs to faculty %, major to %, semester to %',
159	f_student, f_major, f_semester;
160	END IF;
161	RETURN NEW;
162	END;
163	$$;
164
165	DROP TRIGGER IF EXISTS trg_enrolment_same_faculty ON enrolled_semesters;
166	CREATE TRIGGER trg_enrolment_same_faculty
167	BEFORE INSERT OR UPDATE ON enrolled_semesters
168	FOR EACH ROW EXECUTE FUNCTION check_enrolment_same_faculty();
169
170	-- ============================================================
171	-- 3. NOTIFICATIONS
172	-- ============================================================
173
174	CREATE TABLE IF NOT EXISTS notification (
175	id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
176	user_id INTEGER NOT NULL REFERENCES users (id),
177	kind VARCHAR(40) NOT NULL,
178	message TEXT NOT NULL,
179	is_read BOOLEAN NOT NULL DEFAULT FALSE,
180	created_at TIMESTAMP NOT NULL DEFAULT now()
181	);
182
183	CREATE INDEX IF NOT EXISTS notification_user_unread_idx
184	ON notification (user_id) WHERE is_read = FALSE;
185
186	-- ============================================================
187	-- 4. ENROLMENT RULES
188	-- ============================================================
189
190	-- 4a. A subject must be offered by the study programme of the enrolment.
191	CREATE OR REPLACE FUNCTION check_subject_in_major()
192	RETURNS TRIGGER
193	LANGUAGE plpgsql
194	AS $$
195	DECLARE
196	v_major INTEGER;
197	BEGIN
198	SELECT major_id INTO v_major FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id;
199
200	IF NOT EXISTS (SELECT 1 FROM major_subjects
201	WHERE major_id = v_major AND subject_id = NEW.subjects_id) THEN
202	RAISE EXCEPTION 'Subject % is not offered by study programme %',
203	NEW.subjects_id, v_major;
204	END IF;
205	RETURN NEW;
206	END;
207	$$;
208
209	DROP TRIGGER IF EXISTS trg_subject_in_major ON semesters_subjects;
210	CREATE TRIGGER trg_subject_in_major
211	BEFORE INSERT OR UPDATE ON semesters_subjects
212	FOR EACH ROW EXECUTE FUNCTION check_subject_in_major();
213
214	-- 4b. The professor must actually teach that subject in that semester.
215	CREATE OR REPLACE FUNCTION check_professor_teaches()
216	RETURNS TRIGGER
217	LANGUAGE plpgsql
218	AS $$
219	DECLARE
220	v_semester INTEGER;
221	v_role user_role;
222	BEGIN
223	SELECT semester_id INTO v_semester FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id;
224	SELECT role INTO v_role FROM users WHERE id = NEW.professor_id;
225
226	IF v_role <> 'prof' THEN
227	RAISE EXCEPTION 'User % is not a professor', NEW.professor_id;
228	END IF;
229
230	IF NOT EXISTS (SELECT 1 FROM professour_subjects
231	WHERE prof_id = NEW.professor_id
232	AND subject_id = NEW.subjects_id
233	AND active_semester_id = v_semester) THEN
234	RAISE EXCEPTION 'Professor % does not teach subject % in semester %',
235	NEW.professor_id, NEW.subjects_id, v_semester;
236	END IF;
237	RETURN NEW;
238	END;
239	$$;
240
241	DROP TRIGGER IF EXISTS trg_professor_teaches ON semesters_subjects;
242	CREATE TRIGGER trg_professor_teaches
243	BEFORE INSERT OR UPDATE ON semesters_subjects
244	FOR EACH ROW EXECUTE FUNCTION check_professor_teaches();
245
246	-- 4c. Prerequisites must already be passed.
247	CREATE OR REPLACE FUNCTION check_prerequisites_passed()
248	RETURNS TRIGGER
249	LANGUAGE plpgsql
250	AS $$
251	DECLARE
252	v_user INTEGER;
253	v_missing TEXT;
254	BEGIN
255	SELECT user_id INTO v_user FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id;
256
257	SELECT string_agg(s.code, ', ' ORDER BY s.code) INTO v_missing
258	FROM dependency_subject d
259	JOIN subjects s ON s.id = d.dependency_id
260	WHERE d.subject_id = NEW.subjects_id
261	AND NOT EXISTS (
262	SELECT 1
263	FROM passed_subjects ps
264	JOIN semesters_subjects ss ON ss.id = ps.enrolled_id
265	JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
266	WHERE es.user_id = v_user AND ss.subjects_id = d.dependency_id);
267
268	IF v_missing IS NOT NULL THEN
269	RAISE EXCEPTION 'Prerequisite(s) not passed for subject %: %',
270	NEW.subjects_id, v_missing;
271	END IF;
272	RETURN NEW;
273	END;
274	$$;
275
276	DROP TRIGGER IF EXISTS trg_prerequisites_passed ON semesters_subjects;
277	CREATE TRIGGER trg_prerequisites_passed
278	BEFORE INSERT ON semesters_subjects
279	FOR EACH ROW EXECUTE FUNCTION check_prerequisites_passed();
280
281	-- 4d. At most 5 subjects per enrolment, and at most 30 credits.
282	-- Deferred to commit, so a transaction may insert the five rows in any
283	-- order, or swap one subject for another, without tripping the rule
284	-- half-way through.
285	CREATE OR REPLACE FUNCTION check_enrolment_size()
286	RETURNS TRIGGER
287	LANGUAGE plpgsql
288	AS $$
289	DECLARE
290	v_enrolment INTEGER := coalesce(NEW.enrolled_semesters_id, OLD.enrolled_semesters_id);
291	v_count INTEGER;
292	v_credits INTEGER;
293	BEGIN
294	SELECT count(*), coalesce(sum(s.awarded_credits), 0)
295	INTO v_count, v_credits
296	FROM semesters_subjects ss
297	JOIN subjects s ON s.id = ss.subjects_id
298	WHERE ss.enrolled_semesters_id = v_enrolment;
299
300	IF v_count > 5 THEN
301	RAISE EXCEPTION 'Enrolment % has % subjects; the maximum is 5', v_enrolment, v_count;
302	END IF;
303
304	IF v_credits > 30 THEN
305	RAISE EXCEPTION 'Enrolment % has % credits; the maximum is 30', v_enrolment, v_credits;
306	END IF;
307
308	RETURN NULL;
309	END;
310	$$;
311
312	DROP TRIGGER IF EXISTS trg_enrolment_size ON semesters_subjects;
313	CREATE CONSTRAINT TRIGGER trg_enrolment_size
314	AFTER INSERT OR UPDATE OR DELETE ON semesters_subjects
315	DEFERRABLE INITIALLY DEFERRED
316	FOR EACH ROW EXECUTE FUNCTION check_enrolment_size();
317
318	-- ============================================================
319	-- 5. GRADING RULES
320	-- ============================================================
321
322	CREATE OR REPLACE FUNCTION check_grade_rules()
323	RETURNS TRIGGER
324	LANGUAGE plpgsql
325	AS $$
326	DECLARE
327	v_professor INTEGER;
328	v_actor INTEGER;
329	BEGIN
330	IF NEW.date_passed > now() THEN
331	RAISE EXCEPTION 'A grade cannot be dated in the future (%)', NEW.date_passed;
332	END IF;
333
334	SELECT professor_id INTO v_professor
335	FROM semesters_subjects WHERE id = NEW.enrolled_id;
336
337	-- Enforced only when the application tells the database who is acting,
338	-- so scripts and migrations are not blocked.
339	v_actor := nullif(current_setting('app.current_user_id', TRUE), '')::INTEGER;
340	IF v_actor IS NOT NULL AND v_actor <> v_professor THEN
341	RAISE EXCEPTION 'User % may not grade this subject; it is taught by %',
342	v_actor, v_professor;
343	END IF;
344
345	-- Passing a subject implies the professor signed it off.
346	UPDATE semesters_subjects SET signature = TRUE WHERE id = NEW.enrolled_id;
347
348	RETURN NEW;
349	END;
350	$$;
351
352	DROP TRIGGER IF EXISTS trg_grade_rules ON passed_subjects;
353	CREATE TRIGGER trg_grade_rules
354	BEFORE INSERT OR UPDATE ON passed_subjects
355	FOR EACH ROW EXECUTE FUNCTION check_grade_rules();
356
357	-- Tell the student, in their own language of record, that they were graded.
358	CREATE OR REPLACE FUNCTION notify_student_graded()
359	RETURNS TRIGGER
360	LANGUAGE plpgsql
361	AS $$
362	DECLARE
363	v_user INTEGER;
364	v_subject TEXT;
365	BEGIN
366	SELECT es.user_id, s.name
367	INTO v_user, v_subject
368	FROM semesters_subjects ss
369	JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
370	JOIN subjects s ON s.id = ss.subjects_id
371	WHERE ss.id = NEW.enrolled_id;
372
373	INSERT INTO notification (user_id, kind, message)
374	VALUES (v_user, 'grade',
375	format('Добивте оценка %s по предметот %s.', NEW.grade, v_subject));
376	RETURN NEW;
377	END;
378	$$;
379
380	DROP TRIGGER IF EXISTS trg_notify_graded ON passed_subjects;
381	CREATE TRIGGER trg_notify_graded
382	AFTER INSERT ON passed_subjects
383	FOR EACH ROW EXECUTE FUNCTION notify_student_graded();
384
385	-- ============================================================
386	-- 6. VIEWS
387	-- ============================================================
388
389	CREATE OR REPLACE VIEW v_student_transcript AS
390	SELECT es.user_id,
391	u."index" AS student_index,
392	u.name \|\| ' ' \|\| u.surname AS student,
393	s.code AS subject_code,
394	s.name AS subject,
395	s.awarded_credits,
396	ps.grade,
397	ps.date_passed,
398	p.name \|\| ' ' \|\| p.surname AS professor,
399	a.year,
400	a.type AS semester_type
401	FROM passed_subjects ps
402	JOIN semesters_subjects ss ON ss.id = ps.enrolled_id
403	JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
404	JOIN users u ON u.id = es.user_id
405	JOIN subjects s ON s.id = ss.subjects_id
406	JOIN users p ON p.id = ss.professor_id
407	JOIN active_semesters a ON a.id = es.semester_id;
408
409	CREATE OR REPLACE VIEW v_student_standing AS
410	SELECT u.id AS user_id,
411	u."index" AS student_index,
412	u.name \|\| ' ' \|\| u.surname AS student,
413	count(ps.id) AS passed_subjects,
414	coalesce(sum(s.awarded_credits), 0) AS credits,
415	round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) AS average_grade
416	FROM users u
417	LEFT JOIN enrolled_semesters es ON es.user_id = u.id
418	LEFT JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id
419	LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
420	LEFT JOIN subjects s ON s.id = ss.subjects_id AND ps.id IS NOT NULL
421	WHERE u.role = 'student'
422	GROUP BY u.id, u."index", u.name, u.surname;
423
424	CREATE OR REPLACE VIEW v_professor_gradebook AS
425	SELECT ss.professor_id,
426	p.name \|\| ' ' \|\| p.surname AS professor,
427	s.code AS subject_code,
428	s.name AS subject,
429	a.year,
430	a.type AS semester_type,
431	u.id AS student_id,
432	u."index" AS student_index,
433	u.name \|\| ' ' \|\| u.surname AS student,
434	ss.signature,
435	ps.grade
436	FROM semesters_subjects ss
437	JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
438	JOIN users u ON u.id = es.user_id
439	JOIN users p ON p.id = ss.professor_id
440	JOIN subjects s ON s.id = ss.subjects_id
441	JOIN active_semesters a ON a.id = es.semester_id
442	LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id;
443
444	-- Which subjects nobody teaches in a given semester. Students cannot enrol
445	-- in these, so this is the list the administrator has to clear.
446	CREATE OR REPLACE VIEW v_semester_coverage AS
447	SELECT a.id AS semester_id,
448	a.year,
449	a.type AS semester_type,
450	s.id AS subject_id,
451	s.code AS subject_code,
452	s.name AS subject
453	FROM active_semesters a
454	CROSS JOIN subjects s
455	WHERE NOT EXISTS (SELECT 1 FROM professour_subjects ps
456	WHERE ps.active_semester_id = a.id AND ps.subject_id = s.id);
457
458	-- ============================================================
459	-- 7. MATERIALIZED VIEW: subject statistics
460	-- ============================================================
461
462	DROP MATERIALIZED VIEW IF EXISTS mv_subject_statistics;
463	CREATE MATERIALIZED VIEW mv_subject_statistics AS
464	SELECT s.id AS subject_id,
465	s.code AS subject_code,
466	s.name AS subject,
467	count(ss.id) AS enrolled_count,
468	count(ps.id) AS passed_count,
469	round(100.0 * count(ps.id) / nullif(count(ss.id), 0), 1) AS pass_rate,
470	round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) AS average_grade
471	FROM subjects s
472	LEFT JOIN semesters_subjects ss ON ss.subjects_id = s.id
473	LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
474	GROUP BY s.id, s.code, s.name;
475
476	CREATE UNIQUE INDEX mv_subject_statistics_pk ON mv_subject_statistics (subject_id);
477
478	-- ============================================================
479	-- 8. BACKGROUND JOBS
480	-- ============================================================
481
482	-- Marks an enrolment finished once every subject in it is graded.
483	CREATE OR REPLACE PROCEDURE close_completed_enrolments()
484	LANGUAGE plpgsql
485	AS $$
486	DECLARE
487	v_closed INTEGER;
488	BEGIN
489	WITH finished AS (
490	SELECT es.id
491	FROM enrolled_semesters es
492	JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id
493	LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
494	WHERE es.completed IS NULL
495	GROUP BY es.id
496	HAVING count(ss.id) > 0 AND count(ss.id) = count(ps.id)
497	)
498	UPDATE enrolled_semesters es
499	SET completed = now(),
500	last_change = now()
501	FROM finished f
502	WHERE es.id = f.id;
503
504	GET DIAGNOSTICS v_closed = ROW_COUNT;
505	RAISE NOTICE 'close_completed_enrolments: closed % enrolment(s)', v_closed;
506	END;
507	$$;
508
509	-- Invalidates refresh tokens past their expiry.
510	CREATE OR REPLACE PROCEDURE expire_old_tokens()
511	LANGUAGE plpgsql
512	AS $$
513	DECLARE
514	v_expired INTEGER;
515	BEGIN
516	UPDATE token
517	SET is_valid = FALSE
518	WHERE is_valid = TRUE AND expires_at < now();
519
520	GET DIAGNOSTICS v_expired = ROW_COUNT;
521	RAISE NOTICE 'expire_old_tokens: invalidated % token(s)', v_expired;
522	END;
523	$$;
524
525	-- Reminds students about subjects they are enrolled in without a signature.
526	CREATE OR REPLACE PROCEDURE notify_missing_signatures()
527	LANGUAGE plpgsql
528	AS $$
529	DECLARE
530	v_sent INTEGER;
531	BEGIN
532	INSERT INTO notification (user_id, kind, message)
533	SELECT es.user_id,
534	'signature',
535	format('Немате потпис по предметот %s за семестарот %s/%s.',
536	s.name, a.year, a.year + 1)
537	FROM semesters_subjects ss
538	JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
539	JOIN subjects s ON s.id = ss.subjects_id
540	JOIN active_semesters a ON a.id = es.semester_id
541	WHERE ss.signature = FALSE
542	AND es.completed IS NULL
543	AND NOT EXISTS (
544	SELECT 1 FROM notification n
545	WHERE n.user_id = es.user_id
546	AND n.kind = 'signature'
547	AND n.message LIKE '%' \|\| s.name \|\| '%');
548
549	GET DIAGNOSTICS v_sent = ROW_COUNT;
550	RAISE NOTICE 'notify_missing_signatures: created % notification(s)', v_sent;
551	END;
552	$$;
553
554	CREATE OR REPLACE PROCEDURE refresh_statistics()
555	LANGUAGE plpgsql
556	AS $$
557	BEGIN
558	REFRESH MATERIALIZED VIEW CONCURRENTLY mv_subject_statistics;
559	RAISE NOTICE 'refresh_statistics: mv_subject_statistics refreshed';
560	END;
561	$$;
562
563	-- One entry point for the scheduler (pg_cron, or the operating system).
564	CREATE OR REPLACE PROCEDURE run_nightly_maintenance()
565	LANGUAGE plpgsql
566	AS $$
567	BEGIN
568	CALL expire_old_tokens();
569	CALL close_completed_enrolments();
570	CALL notify_missing_signatures();
571	CALL refresh_statistics();
572	RAISE NOTICE 'run_nightly_maintenance: done';
573	END;
574	$$;
575
576	-- ============================================================
577	-- 9. REPORTING FUNCTIONS
578	-- ============================================================
579
580	-- Everything the enrolment screen has to know about one student, in one call.
581	CREATE OR REPLACE FUNCTION student_eligible_subjects(p_user_id INTEGER, p_semester_id INTEGER)
582	RETURNS TABLE (
583	subject_id INTEGER,
584	subject_code VARCHAR,
585	subject_name VARCHAR,
586	awarded_credits INTEGER,
587	already_passed BOOLEAN,
588	prerequisites_ok BOOLEAN,
589	has_professor BOOLEAN
590	)
591	LANGUAGE sql STABLE
592	AS $$
593	SELECT s.id,
594	s.code,
595	s.name,
596	s.awarded_credits::INTEGER,
597	EXISTS (SELECT 1
598	FROM passed_subjects ps
599	JOIN semesters_subjects ss2 ON ss2.id = ps.enrolled_id
600	JOIN enrolled_semesters es2 ON es2.id = ss2.enrolled_semesters_id
601	WHERE es2.user_id = p_user_id AND ss2.subjects_id = s.id),
602	NOT EXISTS (SELECT 1
603	FROM dependency_subject d
604	WHERE d.subject_id = s.id
605	AND NOT EXISTS (
606	SELECT 1
607	FROM passed_subjects ps
608	JOIN semesters_subjects ss3 ON ss3.id = ps.enrolled_id
609	JOIN enrolled_semesters es3 ON es3.id = ss3.enrolled_semesters_id
610	WHERE es3.user_id = p_user_id
611	AND ss3.subjects_id = d.dependency_id)),
612	EXISTS (SELECT 1 FROM professour_subjects pr
613	WHERE pr.subject_id = s.id AND pr.active_semester_id = p_semester_id)
614	FROM subjects s
615	WHERE EXISTS (
616	SELECT 1
617	FROM major_subjects ms
618	JOIN enrolled_semesters es ON es.major_id = ms.major_id
619	WHERE ms.subject_id = s.id AND es.user_id = p_user_id)
620	ORDER BY s.code;
621	$$;
622
623	-- Average grade and credits of a student, as one row.
624	CREATE OR REPLACE FUNCTION student_summary(p_user_id INTEGER)
625	RETURNS TABLE (passed_subjects BIGINT, credits BIGINT, average_grade NUMERIC)
626	LANGUAGE sql STABLE
627	AS $$
628	SELECT count(ps.id),
629	coalesce(sum(s.awarded_credits), 0)::BIGINT,
630	round(avg(ps.grade::TEXT::INT)::NUMERIC, 2)
631	FROM passed_subjects ps
632	JOIN semesters_subjects ss ON ss.id = ps.enrolled_id
633	JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
634	JOIN subjects s ON s.id = ss.subjects_id
635	WHERE es.user_id = p_user_id;
636	$$;

Note: See TracBrowser for help on using the repository browser.

Download in other formats: