Index: sql/advanced.sql =================================================================== --- sql/advanced.sql (revision e7bafc08ad74099b6daf91b8414b90e3c4968111) +++ (revision ) @@ -1,636 +1,0 @@ --- ============================================================ --- IKnow / FINKI - Phase 7: advanced database development --- --- Run after schema_creation.sql and data_load.sql: --- schema_creation.sql -> tables, enums --- data_load.sql -> sample data --- advanced.sql -> this file --- --- Re-runnable: every object is created with OR REPLACE or dropped first. --- ============================================================ - -SET search_path TO project; - --- ============================================================ --- 1. CUSTOM DOMAINS --- One place to define what a valid value looks like, reused by --- every column of that kind. --- ============================================================ - -DROP DOMAIN IF EXISTS email_address CASCADE; -CREATE DOMAIN email_address AS VARCHAR(150) - CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); - -DROP DOMAIN IF EXISTS embg_number CASCADE; -CREATE DOMAIN embg_number AS VARCHAR(13) - CHECK (VALUE ~ '^[0-9]{13}$'); - -DROP DOMAIN IF EXISTS student_index CASCADE; -CREATE DOMAIN student_index AS VARCHAR(20) - CHECK (VALUE ~ '^[0-9]{6}$'); - -DROP DOMAIN IF EXISTS phone_number CASCADE; -CREATE DOMAIN phone_number AS VARCHAR(20) - CHECK (VALUE ~ '^[0-9+][0-9 /-]{5,19}$'); - -DROP DOMAIN IF EXISTS money_amount CASCADE; -CREATE DOMAIN money_amount AS INTEGER - CHECK (VALUE >= 0); - -DROP DOMAIN IF EXISTS gpa_value CASCADE; -CREATE DOMAIN gpa_value AS REAL - CHECK (VALUE >= 2.0 AND VALUE <= 5.0); - -DROP DOMAIN IF EXISTS credit_points CASCADE; -CREATE DOMAIN credit_points AS INTEGER - CHECK (VALUE > 0 AND VALUE <= 30); - -ALTER TABLE users ALTER COLUMN email TYPE email_address; -ALTER TABLE users ALTER COLUMN embg TYPE embg_number; -ALTER TABLE users ALTER COLUMN "index" TYPE student_index; -ALTER TABLE contact ALTER COLUMN microsoft_email TYPE email_address; -ALTER TABLE contact ALTER COLUMN number TYPE phone_number; -ALTER TABLE payment ALTER COLUMN amount TYPE money_amount; -ALTER TABLE documents ALTER COLUMN cost TYPE money_amount; -ALTER TABLE high_school ALTER COLUMN gpa TYPE gpa_value; -ALTER TABLE subjects ALTER COLUMN awarded_credits TYPE credit_points; - --- ============================================================ --- 2. MULTI-TENANCY: several faculties in one database --- ============================================================ - -CREATE TABLE IF NOT EXISTS faculty ( - id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, - short_name VARCHAR(20) NOT NULL UNIQUE, - full_name VARCHAR(200) NOT NULL, - university VARCHAR(200) NOT NULL -); - -INSERT INTO faculty (id, short_name, full_name, university) -VALUES (1, 'FINKI', 'Факултет за информатички науки и компјутерско инженерство', - 'Универзитет „Св. Кирил и Методиј“ - Скопје') -ON CONFLICT (short_name) DO NOTHING; - --- The tenant key goes on the root entities. Everything else inherits its --- faculty through them, so the column is not repeated on every table. -ALTER TABLE users ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); -ALTER TABLE major ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); -ALTER TABLE subjects ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); -ALTER TABLE active_semesters ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); -ALTER TABLE documents ADD COLUMN IF NOT EXISTS faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id); - --- subjects.code and subjects.name were globally unique; with several --- faculties they only have to be unique inside one faculty. -ALTER TABLE subjects DROP CONSTRAINT IF EXISTS subjects_code_key; -ALTER TABLE subjects DROP CONSTRAINT IF EXISTS subjects_name_key; -DROP INDEX IF EXISTS subjects_faculty_code_key; -DROP INDEX IF EXISTS subjects_faculty_name_key; -CREATE UNIQUE INDEX subjects_faculty_code_key ON subjects (faculty_id, code); -CREATE UNIQUE INDEX subjects_faculty_name_key ON subjects (faculty_id, name); - --- The faculty of the current session. Returns NULL when nothing is set, --- which the policies below read as "no tenant filter". -CREATE OR REPLACE FUNCTION current_faculty() - RETURNS INTEGER - LANGUAGE plpgsql STABLE -AS $$ -BEGIN - RETURN nullif(current_setting('app.current_faculty', TRUE), '')::INTEGER; -EXCEPTION - WHEN others THEN RETURN NULL; -END; -$$; - -CREATE OR REPLACE PROCEDURE set_current_faculty(p_faculty INTEGER) - LANGUAGE plpgsql -AS $$ -BEGIN - IF p_faculty IS NOT NULL AND NOT EXISTS (SELECT 1 FROM faculty WHERE id = p_faculty) THEN - RAISE EXCEPTION 'Faculty % does not exist', p_faculty; - END IF; - PERFORM set_config('app.current_faculty', coalesce(p_faculty::TEXT, ''), FALSE); -END; -$$; - --- Row level security. The table owner bypasses these policies unless --- FORCE ROW LEVEL SECURITY is used, so in production the application --- connects with a separate, non-owner role. -ALTER TABLE users ENABLE ROW LEVEL SECURITY; -ALTER TABLE major ENABLE ROW LEVEL SECURITY; -ALTER TABLE subjects ENABLE ROW LEVEL SECURITY; -ALTER TABLE active_semesters ENABLE ROW LEVEL SECURITY; -ALTER TABLE documents ENABLE ROW LEVEL SECURITY; - -DROP POLICY IF EXISTS tenant_isolation ON users; -DROP POLICY IF EXISTS tenant_isolation ON major; -DROP POLICY IF EXISTS tenant_isolation ON subjects; -DROP POLICY IF EXISTS tenant_isolation ON active_semesters; -DROP POLICY IF EXISTS tenant_isolation ON documents; - -CREATE POLICY tenant_isolation ON users - USING (current_faculty() IS NULL OR faculty_id = current_faculty()); -CREATE POLICY tenant_isolation ON major - USING (current_faculty() IS NULL OR faculty_id = current_faculty()); -CREATE POLICY tenant_isolation ON subjects - USING (current_faculty() IS NULL OR faculty_id = current_faculty()); -CREATE POLICY tenant_isolation ON active_semesters - USING (current_faculty() IS NULL OR faculty_id = current_faculty()); -CREATE POLICY tenant_isolation ON documents - USING (current_faculty() IS NULL OR faculty_id = current_faculty()); - --- A foreign key cannot express "these three rows must belong to the same --- faculty", so it is enforced with a trigger. -CREATE OR REPLACE FUNCTION check_enrolment_same_faculty() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE - f_student INTEGER; - f_major INTEGER; - f_semester INTEGER; -BEGIN - SELECT faculty_id INTO f_student FROM users WHERE id = NEW.user_id; - SELECT faculty_id INTO f_major FROM major WHERE id = NEW.major_id; - SELECT faculty_id INTO f_semester FROM active_semesters WHERE id = NEW.semester_id; - - IF f_student IS DISTINCT FROM f_major OR f_student IS DISTINCT FROM f_semester THEN - RAISE EXCEPTION - 'Cross-faculty enrolment: student belongs to faculty %, major to %, semester to %', - f_student, f_major, f_semester; - END IF; - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_enrolment_same_faculty ON enrolled_semesters; -CREATE TRIGGER trg_enrolment_same_faculty - BEFORE INSERT OR UPDATE ON enrolled_semesters - FOR EACH ROW EXECUTE FUNCTION check_enrolment_same_faculty(); - --- ============================================================ --- 3. NOTIFICATIONS --- ============================================================ - -CREATE TABLE IF NOT EXISTS notification ( - id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, - user_id INTEGER NOT NULL REFERENCES users (id), - kind VARCHAR(40) NOT NULL, - message TEXT NOT NULL, - is_read BOOLEAN NOT NULL DEFAULT FALSE, - created_at TIMESTAMP NOT NULL DEFAULT now() -); - -CREATE INDEX IF NOT EXISTS notification_user_unread_idx - ON notification (user_id) WHERE is_read = FALSE; - --- ============================================================ --- 4. ENROLMENT RULES --- ============================================================ - --- 4a. A subject must be offered by the study programme of the enrolment. -CREATE OR REPLACE FUNCTION check_subject_in_major() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE - v_major INTEGER; -BEGIN - SELECT major_id INTO v_major FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id; - - IF NOT EXISTS (SELECT 1 FROM major_subjects - WHERE major_id = v_major AND subject_id = NEW.subjects_id) THEN - RAISE EXCEPTION 'Subject % is not offered by study programme %', - NEW.subjects_id, v_major; - END IF; - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_subject_in_major ON semesters_subjects; -CREATE TRIGGER trg_subject_in_major - BEFORE INSERT OR UPDATE ON semesters_subjects - FOR EACH ROW EXECUTE FUNCTION check_subject_in_major(); - --- 4b. The professor must actually teach that subject in that semester. -CREATE OR REPLACE FUNCTION check_professor_teaches() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE - v_semester INTEGER; - v_role user_role; -BEGIN - SELECT semester_id INTO v_semester FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id; - SELECT role INTO v_role FROM users WHERE id = NEW.professor_id; - - IF v_role <> 'prof' THEN - RAISE EXCEPTION 'User % is not a professor', NEW.professor_id; - END IF; - - IF NOT EXISTS (SELECT 1 FROM professour_subjects - WHERE prof_id = NEW.professor_id - AND subject_id = NEW.subjects_id - AND active_semester_id = v_semester) THEN - RAISE EXCEPTION 'Professor % does not teach subject % in semester %', - NEW.professor_id, NEW.subjects_id, v_semester; - END IF; - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_professor_teaches ON semesters_subjects; -CREATE TRIGGER trg_professor_teaches - BEFORE INSERT OR UPDATE ON semesters_subjects - FOR EACH ROW EXECUTE FUNCTION check_professor_teaches(); - --- 4c. Prerequisites must already be passed. -CREATE OR REPLACE FUNCTION check_prerequisites_passed() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE - v_user INTEGER; - v_missing TEXT; -BEGIN - SELECT user_id INTO v_user FROM enrolled_semesters WHERE id = NEW.enrolled_semesters_id; - - SELECT string_agg(s.code, ', ' ORDER BY s.code) INTO v_missing - FROM dependency_subject d - JOIN subjects s ON s.id = d.dependency_id - WHERE d.subject_id = NEW.subjects_id - AND NOT EXISTS ( - SELECT 1 - FROM passed_subjects ps - JOIN semesters_subjects ss ON ss.id = ps.enrolled_id - JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id - WHERE es.user_id = v_user AND ss.subjects_id = d.dependency_id); - - IF v_missing IS NOT NULL THEN - RAISE EXCEPTION 'Prerequisite(s) not passed for subject %: %', - NEW.subjects_id, v_missing; - END IF; - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_prerequisites_passed ON semesters_subjects; -CREATE TRIGGER trg_prerequisites_passed - BEFORE INSERT ON semesters_subjects - FOR EACH ROW EXECUTE FUNCTION check_prerequisites_passed(); - --- 4d. At most 5 subjects per enrolment, and at most 30 credits. --- Deferred to commit, so a transaction may insert the five rows in any --- order, or swap one subject for another, without tripping the rule --- half-way through. -CREATE OR REPLACE FUNCTION check_enrolment_size() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE - v_enrolment INTEGER := coalesce(NEW.enrolled_semesters_id, OLD.enrolled_semesters_id); - v_count INTEGER; - v_credits INTEGER; -BEGIN - SELECT count(*), coalesce(sum(s.awarded_credits), 0) - INTO v_count, v_credits - FROM semesters_subjects ss - JOIN subjects s ON s.id = ss.subjects_id - WHERE ss.enrolled_semesters_id = v_enrolment; - - IF v_count > 5 THEN - RAISE EXCEPTION 'Enrolment % has % subjects; the maximum is 5', v_enrolment, v_count; - END IF; - - IF v_credits > 30 THEN - RAISE EXCEPTION 'Enrolment % has % credits; the maximum is 30', v_enrolment, v_credits; - END IF; - - RETURN NULL; -END; -$$; - -DROP TRIGGER IF EXISTS trg_enrolment_size ON semesters_subjects; -CREATE CONSTRAINT TRIGGER trg_enrolment_size - AFTER INSERT OR UPDATE OR DELETE ON semesters_subjects - DEFERRABLE INITIALLY DEFERRED - FOR EACH ROW EXECUTE FUNCTION check_enrolment_size(); - --- ============================================================ --- 5. GRADING RULES --- ============================================================ - -CREATE OR REPLACE FUNCTION check_grade_rules() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE - v_professor INTEGER; - v_actor INTEGER; -BEGIN - IF NEW.date_passed > now() THEN - RAISE EXCEPTION 'A grade cannot be dated in the future (%)', NEW.date_passed; - END IF; - - SELECT professor_id INTO v_professor - FROM semesters_subjects WHERE id = NEW.enrolled_id; - - -- Enforced only when the application tells the database who is acting, - -- so scripts and migrations are not blocked. - v_actor := nullif(current_setting('app.current_user_id', TRUE), '')::INTEGER; - IF v_actor IS NOT NULL AND v_actor <> v_professor THEN - RAISE EXCEPTION 'User % may not grade this subject; it is taught by %', - v_actor, v_professor; - END IF; - - -- Passing a subject implies the professor signed it off. - UPDATE semesters_subjects SET signature = TRUE WHERE id = NEW.enrolled_id; - - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_grade_rules ON passed_subjects; -CREATE TRIGGER trg_grade_rules - BEFORE INSERT OR UPDATE ON passed_subjects - FOR EACH ROW EXECUTE FUNCTION check_grade_rules(); - --- Tell the student, in their own language of record, that they were graded. -CREATE OR REPLACE FUNCTION notify_student_graded() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE - v_user INTEGER; - v_subject TEXT; -BEGIN - SELECT es.user_id, s.name - INTO v_user, v_subject - FROM semesters_subjects ss - JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id - JOIN subjects s ON s.id = ss.subjects_id - WHERE ss.id = NEW.enrolled_id; - - INSERT INTO notification (user_id, kind, message) - VALUES (v_user, 'grade', - format('Добивте оценка %s по предметот %s.', NEW.grade, v_subject)); - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_notify_graded ON passed_subjects; -CREATE TRIGGER trg_notify_graded - AFTER INSERT ON passed_subjects - FOR EACH ROW EXECUTE FUNCTION notify_student_graded(); - --- ============================================================ --- 6. VIEWS --- ============================================================ - -CREATE OR REPLACE VIEW v_student_transcript AS -SELECT es.user_id, - u."index" AS student_index, - u.name || ' ' || u.surname AS student, - s.code AS subject_code, - s.name AS subject, - s.awarded_credits, - ps.grade, - ps.date_passed, - p.name || ' ' || p.surname AS professor, - a.year, - a.type AS semester_type -FROM passed_subjects ps -JOIN semesters_subjects ss ON ss.id = ps.enrolled_id -JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id -JOIN users u ON u.id = es.user_id -JOIN subjects s ON s.id = ss.subjects_id -JOIN users p ON p.id = ss.professor_id -JOIN active_semesters a ON a.id = es.semester_id; - -CREATE OR REPLACE VIEW v_student_standing AS -SELECT u.id AS user_id, - u."index" AS student_index, - u.name || ' ' || u.surname AS student, - count(ps.id) AS passed_subjects, - coalesce(sum(s.awarded_credits), 0) AS credits, - round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) AS average_grade -FROM users u -LEFT JOIN enrolled_semesters es ON es.user_id = u.id -LEFT JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id -LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id -LEFT JOIN subjects s ON s.id = ss.subjects_id AND ps.id IS NOT NULL -WHERE u.role = 'student' -GROUP BY u.id, u."index", u.name, u.surname; - -CREATE OR REPLACE VIEW v_professor_gradebook AS -SELECT ss.professor_id, - p.name || ' ' || p.surname AS professor, - s.code AS subject_code, - s.name AS subject, - a.year, - a.type AS semester_type, - u.id AS student_id, - u."index" AS student_index, - u.name || ' ' || u.surname AS student, - ss.signature, - ps.grade -FROM semesters_subjects ss -JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id -JOIN users u ON u.id = es.user_id -JOIN users p ON p.id = ss.professor_id -JOIN subjects s ON s.id = ss.subjects_id -JOIN active_semesters a ON a.id = es.semester_id -LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id; - --- Which subjects nobody teaches in a given semester. Students cannot enrol --- in these, so this is the list the administrator has to clear. -CREATE OR REPLACE VIEW v_semester_coverage AS -SELECT a.id AS semester_id, - a.year, - a.type AS semester_type, - s.id AS subject_id, - s.code AS subject_code, - s.name AS subject -FROM active_semesters a -CROSS JOIN subjects s -WHERE NOT EXISTS (SELECT 1 FROM professour_subjects ps - WHERE ps.active_semester_id = a.id AND ps.subject_id = s.id); - --- ============================================================ --- 7. MATERIALIZED VIEW: subject statistics --- ============================================================ - -DROP MATERIALIZED VIEW IF EXISTS mv_subject_statistics; -CREATE MATERIALIZED VIEW mv_subject_statistics AS -SELECT s.id AS subject_id, - s.code AS subject_code, - s.name AS subject, - count(ss.id) AS enrolled_count, - count(ps.id) AS passed_count, - round(100.0 * count(ps.id) / nullif(count(ss.id), 0), 1) AS pass_rate, - round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) AS average_grade -FROM subjects s -LEFT JOIN semesters_subjects ss ON ss.subjects_id = s.id -LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id -GROUP BY s.id, s.code, s.name; - -CREATE UNIQUE INDEX mv_subject_statistics_pk ON mv_subject_statistics (subject_id); - --- ============================================================ --- 8. BACKGROUND JOBS --- ============================================================ - --- Marks an enrolment finished once every subject in it is graded. -CREATE OR REPLACE PROCEDURE close_completed_enrolments() - LANGUAGE plpgsql -AS $$ -DECLARE - v_closed INTEGER; -BEGIN - WITH finished AS ( - SELECT es.id - FROM enrolled_semesters es - JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id - LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id - WHERE es.completed IS NULL - GROUP BY es.id - HAVING count(ss.id) > 0 AND count(ss.id) = count(ps.id) - ) - UPDATE enrolled_semesters es - SET completed = now(), - last_change = now() - FROM finished f - WHERE es.id = f.id; - - GET DIAGNOSTICS v_closed = ROW_COUNT; - RAISE NOTICE 'close_completed_enrolments: closed % enrolment(s)', v_closed; -END; -$$; - --- Invalidates refresh tokens past their expiry. -CREATE OR REPLACE PROCEDURE expire_old_tokens() - LANGUAGE plpgsql -AS $$ -DECLARE - v_expired INTEGER; -BEGIN - UPDATE token - SET is_valid = FALSE - WHERE is_valid = TRUE AND expires_at < now(); - - GET DIAGNOSTICS v_expired = ROW_COUNT; - RAISE NOTICE 'expire_old_tokens: invalidated % token(s)', v_expired; -END; -$$; - --- Reminds students about subjects they are enrolled in without a signature. -CREATE OR REPLACE PROCEDURE notify_missing_signatures() - LANGUAGE plpgsql -AS $$ -DECLARE - v_sent INTEGER; -BEGIN - INSERT INTO notification (user_id, kind, message) - SELECT es.user_id, - 'signature', - format('Немате потпис по предметот %s за семестарот %s/%s.', - s.name, a.year, a.year + 1) - FROM semesters_subjects ss - JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id - JOIN subjects s ON s.id = ss.subjects_id - JOIN active_semesters a ON a.id = es.semester_id - WHERE ss.signature = FALSE - AND es.completed IS NULL - AND NOT EXISTS ( - SELECT 1 FROM notification n - WHERE n.user_id = es.user_id - AND n.kind = 'signature' - AND n.message LIKE '%' || s.name || '%'); - - GET DIAGNOSTICS v_sent = ROW_COUNT; - RAISE NOTICE 'notify_missing_signatures: created % notification(s)', v_sent; -END; -$$; - -CREATE OR REPLACE PROCEDURE refresh_statistics() - LANGUAGE plpgsql -AS $$ -BEGIN - REFRESH MATERIALIZED VIEW CONCURRENTLY mv_subject_statistics; - RAISE NOTICE 'refresh_statistics: mv_subject_statistics refreshed'; -END; -$$; - --- One entry point for the scheduler (pg_cron, or the operating system). -CREATE OR REPLACE PROCEDURE run_nightly_maintenance() - LANGUAGE plpgsql -AS $$ -BEGIN - CALL expire_old_tokens(); - CALL close_completed_enrolments(); - CALL notify_missing_signatures(); - CALL refresh_statistics(); - RAISE NOTICE 'run_nightly_maintenance: done'; -END; -$$; - --- ============================================================ --- 9. REPORTING FUNCTIONS --- ============================================================ - --- Everything the enrolment screen has to know about one student, in one call. -CREATE OR REPLACE FUNCTION student_eligible_subjects(p_user_id INTEGER, p_semester_id INTEGER) - RETURNS TABLE ( - subject_id INTEGER, - subject_code VARCHAR, - subject_name VARCHAR, - awarded_credits INTEGER, - already_passed BOOLEAN, - prerequisites_ok BOOLEAN, - has_professor BOOLEAN - ) - LANGUAGE sql STABLE -AS $$ - SELECT s.id, - s.code, - s.name, - s.awarded_credits::INTEGER, - EXISTS (SELECT 1 - FROM passed_subjects ps - JOIN semesters_subjects ss2 ON ss2.id = ps.enrolled_id - JOIN enrolled_semesters es2 ON es2.id = ss2.enrolled_semesters_id - WHERE es2.user_id = p_user_id AND ss2.subjects_id = s.id), - NOT EXISTS (SELECT 1 - FROM dependency_subject d - WHERE d.subject_id = s.id - AND NOT EXISTS ( - SELECT 1 - FROM passed_subjects ps - JOIN semesters_subjects ss3 ON ss3.id = ps.enrolled_id - JOIN enrolled_semesters es3 ON es3.id = ss3.enrolled_semesters_id - WHERE es3.user_id = p_user_id - AND ss3.subjects_id = d.dependency_id)), - EXISTS (SELECT 1 FROM professour_subjects pr - WHERE pr.subject_id = s.id AND pr.active_semester_id = p_semester_id) - FROM subjects s - WHERE EXISTS ( - SELECT 1 - FROM major_subjects ms - JOIN enrolled_semesters es ON es.major_id = ms.major_id - WHERE ms.subject_id = s.id AND es.user_id = p_user_id) - ORDER BY s.code; -$$; - --- Average grade and credits of a student, as one row. -CREATE OR REPLACE FUNCTION student_summary(p_user_id INTEGER) - RETURNS TABLE (passed_subjects BIGINT, credits BIGINT, average_grade NUMERIC) - LANGUAGE sql STABLE -AS $$ - SELECT count(ps.id), - coalesce(sum(s.awarded_credits), 0)::BIGINT, - round(avg(ps.grade::TEXT::INT)::NUMERIC, 2) - FROM passed_subjects ps - JOIN semesters_subjects ss ON ss.id = ps.enrolled_id - JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id - JOIN subjects s ON s.id = ss.subjects_id - WHERE es.user_id = p_user_id; -$$; Index: sql/data_load.sql =================================================================== --- sql/data_load.sql (revision e7bafc08ad74099b6daf91b8414b90e3c4968111) +++ sql/data_load.sql (revision 002cf5f9fe36565e030e882d63222c760d638995) @@ -115,9 +115,9 @@ (3, 1, 'tok_old789_stefan', '2026-01-01 00:00:00', FALSE); -INSERT INTO payment (id, enrollment_id, amount) VALUES -(1, 1, 200), -(2, 2, 200), -(3, 3, 400), -(4, 4, 200); +INSERT INTO payment (id, user_id, enrollment_id, amount) VALUES +(1, 1, 1, 200), +(2, 1, 2, 200), +(3, 2, 3, 400), +(4, 3, 4, 200); INSERT INTO major_subjects (major_id, subject_id, mandatory_semester) VALUES Index: sql/iknow.dbml =================================================================== --- sql/iknow.dbml (revision e7bafc08ad74099b6daf91b8414b90e3c4968111) +++ sql/iknow.dbml (revision 002cf5f9fe36565e030e882d63222c760d638995) @@ -150,4 +150,5 @@ Table project.payment { id integer [pk, increment] + user_id integer [not null] enrollment_id integer [not null] amount integer [not null] @@ -187,4 +188,5 @@ Ref: project.contact.user_id - project.users.id // has_contact (1:1) Ref: project.token.user_id > project.users.id // has_token +Ref: project.payment.user_id > project.users.id // pays Ref: project.payment.enrollment_id > project.enrolled_semesters.id // for_enrollment Ref: project.enrolled_semesters.user_id > project.users.id // submits Index: sql/reports.sql =================================================================== --- sql/reports.sql (revision e7bafc08ad74099b6daf91b8414b90e3c4968111) +++ (revision ) @@ -1,328 +1,0 @@ --- Phase 6 - advanced reports. --- Runs after schema_creation.sql and data_load.sql; every routine is created --- with OR REPLACE, so the script can be run repeatedly. - -SET search_path TO project; - --- 1. Pass rate per subject and semester, compared to the subject's own average. -CREATE OR REPLACE FUNCTION rep_subject_pass_rate() - RETURNS TABLE ( - subject_code VARCHAR, - subject VARCHAR, - year INTEGER, - semester_type semester_type, - enrolled BIGINT, - signed_count BIGINT, - passed BIGINT, - pass_rate NUMERIC, - average_grade VARCHAR, - deviation VARCHAR - ) - LANGUAGE sql - STABLE - SET search_path = project -AS $$ - WITH enrolled_per_semester AS ( - SELECT ss.subjects_id AS subject_id, - es.semester_id AS semester_id, - COUNT(ss.id) AS enrolled, - COUNT(ps.id) AS passed, - ROUND(AVG(ps.grade::TEXT::INT), 2) AS average_grade - FROM semesters_subjects ss - JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id - LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id - GROUP BY ss.subjects_id, es.semester_id - ), - signed_per_semester AS ( - SELECT ss.subjects_id AS subject_id, - es.semester_id AS semester_id, - COUNT(ss.id) AS signed_count - FROM semesters_subjects ss - JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id - WHERE ss.signature = TRUE - GROUP BY ss.subjects_id, es.semester_id - ), - subject_average AS ( - SELECT ss.subjects_id AS subject_id, - ROUND(AVG(ps.grade::TEXT::INT), 2) AS subject_average - FROM semesters_subjects ss - JOIN passed_subjects ps ON ps.enrolled_id = ss.id - GROUP BY ss.subjects_id - ) - SELECT s.code, - s.name, - a.year, - a.type, - eps.enrolled, - COALESCE(sps.signed_count, 0), - eps.passed, - ROUND(100.0 * eps.passed / eps.enrolled, 1), - COALESCE(CAST(eps.average_grade AS VARCHAR), 'нема оценки'), - COALESCE(CAST(eps.average_grade - sa.subject_average AS VARCHAR), 'n/a') - FROM enrolled_per_semester eps - JOIN subjects s ON s.id = eps.subject_id - JOIN active_semesters a ON a.id = eps.semester_id - LEFT JOIN signed_per_semester sps ON sps.subject_id = eps.subject_id - AND sps.semester_id = eps.semester_id - LEFT JOIN subject_average sa ON sa.subject_id = eps.subject_id - ORDER BY a.year, a.type, ROUND(100.0 * eps.passed / eps.enrolled, 1) DESC, s.code; -$$; - --- 2. Full dossier of a student: studies, money and documents in one row. --- p_user_id IS NULL returns every student. -CREATE OR REPLACE FUNCTION rep_student_dossier(p_user_id INTEGER DEFAULT NULL) - RETURNS TABLE ( - student_index VARCHAR, - student TEXT, - enrolled_semesters BIGINT, - completed_semesters BIGINT, - passed_subjects BIGINT, - pending_subjects BIGINT, - credits BIGINT, - average_grade VARCHAR, - total_paid BIGINT, - num_documents BIGINT, - documents_cost BIGINT - ) - LANGUAGE sql - STABLE - SET search_path = project -AS $$ - WITH passed_stats AS ( - SELECT es.user_id, - COUNT(ps.id) AS passed_subjects, - SUM(s.awarded_credits) AS credits, - ROUND(AVG(ps.grade::TEXT::INT), 2) AS average_grade - FROM enrolled_semesters es - JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id - JOIN passed_subjects ps ON ps.enrolled_id = ss.id - JOIN subjects s ON s.id = ss.subjects_id - GROUP BY es.user_id - ), - semester_stats AS ( - SELECT es.user_id, - COUNT(es.id) AS enrolled_semesters, - COUNT(es.completed) AS completed_semesters - FROM enrolled_semesters es - GROUP BY es.user_id - ), - pending_stats AS ( - SELECT es.user_id, - COUNT(ss.id) AS pending_subjects - FROM enrolled_semesters es - JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id - LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id - WHERE ps.id IS NULL - GROUP BY es.user_id - ), - payment_stats AS ( - SELECT es.user_id, - SUM(p.amount) AS total_paid - FROM payment p - JOIN enrolled_semesters es ON es.id = p.enrollment_id - GROUP BY es.user_id - ), - document_stats AS ( - SELECT ud.user_id, - COUNT(ud.document_id) AS num_documents, - SUM(d.cost) AS documents_cost - FROM user_documents ud - JOIN documents d ON d.id = ud.document_id - GROUP BY ud.user_id - ) - SELECT u."index", - u.name || ' ' || u.surname, - COALESCE(sem.enrolled_semesters, 0), - COALESCE(sem.completed_semesters, 0), - COALESCE(ps.passed_subjects, 0), - COALESCE(pen.pending_subjects, 0), - COALESCE(ps.credits, 0), - COALESCE(CAST(ps.average_grade AS VARCHAR), 'нема оценки'), - COALESCE(pay.total_paid, 0), - COALESCE(doc.num_documents, 0), - COALESCE(doc.documents_cost, 0) - FROM users u - LEFT JOIN passed_stats ps ON ps.user_id = u.id - LEFT JOIN semester_stats sem ON sem.user_id = u.id - LEFT JOIN pending_stats pen ON pen.user_id = u.id - LEFT JOIN payment_stats pay ON pay.user_id = u.id - LEFT JOIN document_stats doc ON doc.user_id = u.id - WHERE u.role = 'student' - AND (p_user_id IS NULL OR u.id = p_user_id) - ORDER BY COALESCE(ps.credits, 0) DESC, COALESCE(ps.average_grade, 0) DESC, u."index"; -$$; - --- 3. Best student of every study programme; ties broken deterministically. -CREATE OR REPLACE FUNCTION rep_top_student_per_major() - RETURNS TABLE ( - major VARCHAR, - student_index VARCHAR, - student TEXT, - passed_subjects BIGINT, - credits BIGINT, - average_grade NUMERIC - ) - LANGUAGE sql - STABLE - SET search_path = project -AS $$ - WITH standing AS ( - SELECT es.user_id, - es.major_id, - COUNT(ps.id) AS passed_subjects, - COALESCE(SUM(s.awarded_credits), 0) AS credits, - COALESCE(ROUND(AVG(ps.grade::TEXT::INT), 2), 0) AS average_grade - FROM enrolled_semesters es - LEFT JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id - LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id - LEFT JOIN subjects s ON s.id = ss.subjects_id AND ps.id IS NOT NULL - GROUP BY es.user_id, es.major_id - ) - SELECT m.name, - u."index", - u.name || ' ' || u.surname, - st.passed_subjects, - st.credits, - st.average_grade - FROM standing st - JOIN users u ON u.id = st.user_id - JOIN major m ON m.id = st.major_id - WHERE NOT EXISTS ( - SELECT 1 - FROM standing st1 - WHERE st1.major_id = st.major_id - AND (st.credits < st1.credits - OR (st.credits = st1.credits AND st.average_grade < st1.average_grade) - OR (st.credits = st1.credits AND st.average_grade = st1.average_grade - AND st.user_id > st1.user_id)) - ) - ORDER BY m.name; -$$; - --- 4. Busiest professor of every active semester, including semesters nobody --- has enrolled in yet. -CREATE OR REPLACE FUNCTION rep_busiest_professor() - RETURNS TABLE ( - year INTEGER, - semester_type semester_type, - professor TEXT, - subjects_taught BIGINT, - enrolled_students BIGINT, - graded_students BIGINT - ) - LANGUAGE sql - STABLE - SET search_path = project -AS $$ - WITH professor_load AS ( - SELECT es.semester_id, - ss.professor_id, - COUNT(ss.id) AS enrolled_students, - COUNT(ps.id) AS graded_students, - COUNT(DISTINCT ss.subjects_id) AS subjects_taught - FROM semesters_subjects ss - JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id - LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id - GROUP BY es.semester_id, ss.professor_id - ), - busiest AS ( - SELECT pl.* - FROM professor_load pl - WHERE NOT EXISTS ( - SELECT 1 - FROM professor_load pl1 - WHERE pl1.semester_id = pl.semester_id - AND (pl.enrolled_students < pl1.enrolled_students - OR (pl.enrolled_students = pl1.enrolled_students - AND pl.graded_students < pl1.graded_students) - OR (pl.enrolled_students = pl1.enrolled_students - AND pl.graded_students = pl1.graded_students - AND pl.professor_id > pl1.professor_id)) - ) - ) - SELECT a.year, - a.type, - COALESCE(u.name || ' ' || u.surname, 'n/a'), - COALESCE(b.subjects_taught, 0), - COALESCE(b.enrolled_students, 0), - COALESCE(b.graded_students, 0) - FROM active_semesters a - LEFT JOIN busiest b ON b.semester_id = a.id - LEFT JOIN users u ON u.id = b.professor_id - ORDER BY a.year, CASE a.type WHEN 'summer' THEN 1 ELSE 2 END; -$$; - --- 5. Change of activity between two consecutive semesters. -CREATE OR REPLACE FUNCTION rep_semester_growth() - RETURNS TABLE ( - semester TEXT, - previous_semester TEXT, - enrolments BIGINT, - subject_enrolments BIGINT, - passed BIGINT, - prev_subject_enrolments VARCHAR, - pct_change VARCHAR - ) - LANGUAGE sql - STABLE - SET search_path = project -AS $$ - WITH ordered_semesters AS ( - SELECT a.id, - a.year, - a.type, - a.year * 10 + CASE a.type WHEN 'summer' THEN 1 ELSE 2 END AS chrono - FROM active_semesters a - ), - semester_stats AS ( - SELECT o.id AS semester_id, - o.year, - o.type, - o.chrono, - COUNT(DISTINCT es.id) AS enrolments, - COUNT(ss.id) AS subject_enrolments, - COUNT(ps.id) AS passed - FROM ordered_semesters o - LEFT JOIN enrolled_semesters es ON es.semester_id = o.id - LEFT JOIN semesters_subjects ss ON ss.enrolled_semesters_id = es.id - LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id - GROUP BY o.id, o.year, o.type, o.chrono - ), - consecutive AS ( - SELECT cur.year, cur.type, cur.chrono, - cur.enrolments, cur.subject_enrolments, cur.passed, - prev.year AS prev_year, - prev.type AS prev_type, - prev.subject_enrolments AS prev_subject_enrolments - FROM semester_stats cur - LEFT JOIN semester_stats prev - ON prev.chrono < cur.chrono - AND NOT EXISTS (SELECT 1 - FROM semester_stats mid - WHERE mid.chrono < cur.chrono - AND mid.chrono > prev.chrono) - ) - SELECT c.year || '-' || c.type, - COALESCE(c.prev_year || '-' || c.prev_type, 'нема претходен'), - c.enrolments, - c.subject_enrolments, - c.passed, - COALESCE(CAST(c.prev_subject_enrolments AS VARCHAR), 'n/a'), - COALESCE(CAST(ROUND(((c.subject_enrolments - c.prev_subject_enrolments) * 100.0) - / NULLIF(c.prev_subject_enrolments, 0)) AS VARCHAR) || '%', 'n/a') - FROM consecutive c - ORDER BY c.chrono; -$$; - --- The same dossier as a stored procedure returning an open cursor, for callers --- that read the report row by row instead of as a result set. -CREATE OR REPLACE PROCEDURE rep_student_dossier_cursor( - IN p_user_id INTEGER, - INOUT p_cursor REFCURSOR DEFAULT 'dossier') - LANGUAGE plpgsql - SET search_path = project -AS $$ -BEGIN - OPEN p_cursor FOR SELECT * FROM rep_student_dossier(p_user_id); -END; -$$; Index: sql/schema.md =================================================================== --- sql/schema.md (revision e7bafc08ad74099b6daf91b8414b90e3c4968111) +++ sql/schema.md (revision 002cf5f9fe36565e030e882d63222c760d638995) @@ -111,4 +111,5 @@ payment { integer id PK + integer user_id FK integer enrollment_id FK integer amount @@ -135,4 +136,5 @@ users ||--o| contact : has_contact users ||--o{ token : has_token + users ||--o{ payment : pays users ||--o{ enrolled_semesters : submits users ||--o{ user_documents : owns @@ -182,5 +184,2 @@ they differ. - `professour_subjects` keeps the spelling used by the table in `schema_creation.sql`. -- `payment` has no `user_id`: the student is reached through `enrollment_id`. - Storing it twice violated BCNF (`enrolled_id -> user_id`) and allowed a - payment to contradict the enrolment it refers to. Removed in Phase 5. Index: sql/schema_creation.sql =================================================================== --- sql/schema_creation.sql (revision e7bafc08ad74099b6daf91b8414b90e3c4968111) +++ sql/schema_creation.sql (revision 002cf5f9fe36565e030e882d63222c760d638995) @@ -115,4 +115,5 @@ CREATE TABLE payment ( id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), enrollment_id INTEGER NOT NULL REFERENCES enrolled_semesters (id), amount INTEGER NOT NULL