Напреден развој на базата

Сите објекти опишани подолу се во скриптата advanced.sql​, која се стартува по schema_creation.sql и data_load.sql. Скриптата е повторлива - секој објект се креира со OR REPLACE или претходно се брише.

Основните ограничувања на ниво на колона и референцијалниот интегритет се документирани во Фаза 2. Овде се опишани само оние правила кои не можат да се изразат со обично ограничување.

1. Повеќе факултети во иста база

Опис на барањето

Системот е замислен да опслужува повеќе факултети од ист универзитет. Секој факултет има свои студенти, професори, предмети, студиски програми и активни семестри. Податоците на еден факултет не смеат да бидат видливи ниту достапни од друг факултет, а притоа сите работат врз иста база и иста шема.

Ова носи три проблеми кои не се решаваат со надворешни клучеви:

• секое барање мора автоматски да биде ограничено на тековниот факултет, без секој SELECT да мора да памети WHERE faculty_id = ...
• шифрата на предметот е уникатна во рамки на факултет, а не глобално - два факултета смеат да имаат предмет со иста шифра
• запишувањето мора да поврзе студент, студиска програма и семестар кои сите припаѓаат на ист факултет - надворешен клуч може да провери дека секој од нив постои, но не и дека се од ист факултет

Имплементација

Табела и колона за наемател (tenant)

CREATE TABLE 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
);

ALTER TABLE users            ADD COLUMN faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
ALTER TABLE major            ADD COLUMN faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
ALTER TABLE subjects         ADD COLUMN faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
ALTER TABLE active_semesters ADD COLUMN faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);
ALTER TABLE documents        ADD COLUMN faculty_id INTEGER NOT NULL DEFAULT 1 REFERENCES faculty (id);

Колоната faculty_id се додава само на кореновите ентитети. Сите останати табели го наследуваат факултетот преку нив, па не се повторува насекаде.

Уникатност во рамки на факултет

ALTER TABLE subjects DROP CONSTRAINT subjects_code_key;
ALTER TABLE subjects DROP CONSTRAINT subjects_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);

Функција и процедура за тековен факултет

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)

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON users
    USING (current_faculty() IS NULL OR faculty_id = current_faculty());

Истата политика се поставува и врз major, subjects, active_semesters и documents. Кога апликацијата ќе повика CALL set_current_faculty(2), сите барања во таа сесија автоматски гледаат само податоци од факултет 2, без ниту една измена во самите SQL барања.

Забелешка: сопственикот на табелата ги заобиколува политиките, освен ако не се употреби FORCE ROW LEVEL SECURITY. Затоа во продукција апликацијата се поврзува со посебна улога која не е сопственик на шемата.

Тригер за интегритет меѓу факултети

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;
$$;

CREATE TRIGGER trg_enrolment_same_faculty
    BEFORE INSERT OR UPDATE ON enrolled_semesters
    FOR EACH ROW EXECUTE FUNCTION check_enrolment_same_faculty();

2. Правила за запишување семестар

Опис на барањето

Запишувањето на семестар има четири правила кои базата треба сама да ги чува, без да зависи од тоа дали апликацијата ќе ги провери:

• предметот мора да биде понуден од студиската програма на запишувањето
• професорот мора навистина да го предава тој предмет во тој семестар
• предусловите на предметот мора да бидат положени
• запишувањето смее да има најмногу 5 предмети и најмногу 30 кредити

Ниту едно од овие не може да се напише како CHECK ограничување, бидејќи сите бараат читање од други табели.

Имплементација - тригери

Предметот мора да е во студиската програма

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;
$$;

CREATE TRIGGER trg_subject_in_major
    BEFORE INSERT OR UPDATE ON semesters_subjects
    FOR EACH ROW EXECUTE FUNCTION check_subject_in_major();

Професорот мора да го предава предметот тој семестар

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;
$$;

CREATE TRIGGER trg_professor_teaches
    BEFORE INSERT OR UPDATE ON semesters_subjects
    FOR EACH ROW EXECUTE FUNCTION check_professor_teaches();

Предусловите мора да се положени

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;
$$;

CREATE TRIGGER trg_prerequisites_passed
    BEFORE INSERT ON semesters_subjects
    FOR EACH ROW EXECUTE FUNCTION check_prerequisites_passed();

Одложен тригер за големина на запишувањето

Ова правило е поставено како одложен (DEFERRABLE INITIALLY DEFERRED) тригер, што значи дека се проверува дури при потврда на трансакцијата, а не по секој ред. Така апликацијата може да ги внесе петте предмети еден по еден, или да замени еден предмет со друг во иста трансакција, без правилото да се прекрши на средина од работата.

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;
$$;

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();

3. Правила за оценување и известувања

Опис на барањето

• оценка не смее да има датум во иднина
• оценка смее да внесе само професорот кој го предава тој предмет на тој студент
• положен предмет автоматски значи дека има потпис
• студентот треба веднаш да добие известување кога ќе биде оценет

Втората точка е проверка на овластување во самата база. Апликацијата и онака ја прави истата проверка, но доколку некој пристапи до базата директно, правилото пак важи.

Имплементација - тригери

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;

    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;

    UPDATE semesters_subjects SET signature = TRUE WHERE id = NEW.enrolled_id;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_grade_rules
    BEFORE INSERT OR UPDATE ON passed_subjects
    FOR EACH ROW EXECUTE FUNCTION check_grade_rules();

Проверката на овластување се активира само кога апликацијата ќе ѝ каже на базата кој работи, преку SET app.current_user_id. Така скриптите за одржување и вчитување податоци не се блокирани, а вистинските барања од апликацијата се проверени и на ниво на база.

Известување на студентот

CREATE TABLE 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 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;
$$;

CREATE TRIGGER trg_notify_graded
    AFTER INSERT ON passed_subjects
    FOR EACH ROW EXECUTE FUNCTION notify_student_graded();

4. Прегледи (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;

Двојното претворање ps.grade::TEXT::INT е потребно бидејќи grade е набројувачки тип, па не може директно да влезе во avg().

Дневник на професорот и покриеност на семестарот

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;

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);

Прегледот v_semester_coverage покажува кои предмети никој не ги предава во даден семестар. Тоа е точно списокот кој администраторот мора да го исчисти, бидејќи студент не може да запише предмет без професор.

5. Статистика по предмети (материјализиран преглед)

Опис на барањето

Статистиката за проодност по предмет се пресметува преку сите запишувања и сите оценки. Тоа е скапо барање кое не се менува од минута во минута, па се чува материјализирано и се освежува еднаш дневно.

Имплементација

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);

Уникатниот индекс не е само оптимизација - тој е услов за да може прегледот да се освежува со REFRESH MATERIALIZED VIEW CONCURRENTLY, односно без да се заклучи за читање додека трае освежувањето.

6. Ноќно одржување (background jobs)

Опис на барањето

Четири работи треба да се случуваат периодично, без некој да ги повика рачно:

• истечените токени за освежување да се поништат
• запишување во кое сите предмети се положени да се затвори
• студентите да добијат потсетник за предмети без потпис
• статистиката да се пресмета одново

Имплементација - процедури

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;
$$;

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;
$$;

Процедурата notify_missing_signatures создава известување за секој предмет без потпис, но само ако таков потсетник сè уште не постои, за да не се праќа истото известување секоја ноќ.

Сите четири се повикуваат од една влезна точка, која потоа ја стартува распоредувачот (pg_cron или закажана задача на оперативниот систем):

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;
$$;

7. Функции за извештаи

Функцијата student_eligible_subjects враќа табела со сите предмети од студиската програма на студентот, и за секој кажува дали е веќе положен, дали предусловите се исполнети и дали некој го предава во бараниот семестар. Екранот за запишување со еден повик добива сè што му треба, наместо да собира три различни барања.

SELECT * FROM student_eligible_subjects(1, 3);

Функцијата student_summary враќа еден ред со бројот на положени предмети, вкупните кредити и просекот на еден студент.

8. Сопствени домени

Опис на барањето

Проверките на формат се повторуваат на повеќе места: е-пошта има две колони (users.email и contact.microsoft_email), износ има две (payment.amount и documents.cost). Наместо истиот CHECK да се пишува на секое место, правилото се дефинира еднаш како домен и потоа се употребува како тип.

Имплементација

CREATE DOMAIN email_address AS VARCHAR(150)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN embg_number AS VARCHAR(13)
    CHECK (VALUE ~ '^[0-9]{13}$');

CREATE DOMAIN student_index AS VARCHAR(20)
    CHECK (VALUE ~ '^[0-9]{6}$');

CREATE DOMAIN phone_number AS VARCHAR(20)
    CHECK (VALUE ~ '^[0-9+][0-9 /-]{5,19}$');

CREATE DOMAIN money_amount AS INTEGER
    CHECK (VALUE >= 0);

CREATE DOMAIN gpa_value AS REAL
    CHECK (VALUE >= 2.0 AND VALUE <= 5.0);

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;

Користење на вештачка интелигенција

AdvancedDatabaseDevelopmentAIUsage

Историјат

Верзија 1 - Прва верзија: домени, повеќе факултети со политики на ниво на редица, тригери за правилата на запишување и оценување, прегледи, материјализиран преглед за статистика и процедури за ноќно одржување.

Статус

Во тек