Функции, процедури и тригери (DatabaseProgramming)

Апликациската логика е имплементирана во PL/pgSQL: 11 процедури (procedures.sql​), 18 функции (functions.sql​) и 18 тригери со 10 тригер-функции (triggers.sql​). Правилата се поделени во три слоја. Ограничувањата од шемата ги гарантираат правилата за секоја редица. Процедурите ги проверуваат истите правила пред запишувањето, за апликацијата да добие описна порака наместо грешка на ограничување; тие проверки се издвоени во функции. Правилата што читаат други табели ги гарантираат тригерите за секој пат на запишување; процедурите не ги повторуваат, туку се потпираат на тригерот.

Содржина

1. Процедури
   1. Процедура 1: Регистрација на корисник (sp_register_user)
   2. Процедура 2: Активирање на корисник (sp_activate_user)
   3. Процедура 3: Деактивирање на корисник (sp_deactivate_user)
   4. Процедура 4: Договор со прв проект (sp_create_contract_with_project)
   5. Процедура 5: Промена на статус на проект (sp_update_project_status)
   6. Процедура 6: Промена на буџет на проект (sp_update_project_budget)
   7. Процедура 7: Поднесување рецензија (sp_submit_review)
   8. Процедура 8: Поднесување спор (sp_file_dispute)
   9. Процедура 9: Решавање на спор (sp_resolve_dispute)
   10. Процедура 10: Објавување рецензија (sp_publish_review)
   11. Процедура 11: Обновување на претплата (sp_renew_vendor_subscription)
2. Функции
   1. Функција 1: Целосно име на корисник (fn_get_full_name)
   2. Функција 2: Софтверска агенција на проект (fn_get_vendor_id_for_project)
   3. Функција 3: Клиент на проект (fn_get_client_id_for_project)
   4. Функција 4: Корисник на софтверската агенција на проектот (fn_is_vendor_user_of_project)
   5. Функција 5: Корисник на клиентот на проектот (fn_is_client_user_of_project)
   6. Функција 6: Проверка за непразен текст (fn_assert_not_blank)
   7. Функција 7: Проверка за позитивен износ (fn_assert_positive)
   8. Функции 8–18: Проверки за постоење (fn_assert_*_exists)
3. Тригери
   1. Тригери 1–3: Подтип на корисник (trg_enforce_user_subtype)
   2. Тригер 4: Непроменлив тип на корисник (trg_prevent_user_type_change)
   3. Тригер 5: Договорна цена на претплата (trg_enforce_negotiated_price)
   4. Тригери 6–12: Време на последна промена (trg_set_updated_at)
   5. Тригер 13: Ревизија на буџетот (trg_capture_budget_change)
   6. Тригер 14: Актер во историјата на статуси (trg_validate_status_history_actor)
   7. Тригер 15: Преклопување на претплати (trg_prevent_subscription_overlap)
   8. Тригер 16: Решавање на спор (trg_validate_dispute_resolution)
   9. Тригер 17: Објавување на оспорена рецензија (trg_prevent_publishing_disputed_review)
   10. Тригер 18: Рецензија само за завршен проект (trg_require_finished_project)

---

Процедури

Процедура 1: Регистрација на корисник (sp_register_user)

Регистрација на сметка: во една трансакција запишува нов корисник во "User" и соодветната редица во табелата на неговиот подтип (Client_User, Vendor_User или Management_User). Типот определува кој идентификатор е задолжителен (клиент, софтверска агенција или улога) и тој мора да постои; текстуалните полиња не смеат да бидат празни. Корисникот се креира неактивен и се активира по потврда.

CREATE OR REPLACE PROCEDURE sp_register_user(
    OUT p_user_id       int4,
    IN  p_type          text,
    IN  p_first_name    text,
    IN  p_last_name     text,
    IN  p_email         text,
    IN  p_password_hash text,
    IN  p_client_id     int4 DEFAULT NULL,
    IN  p_vendor_id     int4 DEFAULT NULL,
    IN  p_role_id       int4 DEFAULT NULL
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Типот одредува кој идентификатор е задолжителен; секоја гранка го проверува својот
    CASE p_type
        WHEN 'client' THEN
            IF p_client_id IS NULL THEN
                RAISE EXCEPTION 'p_client_id is required when registering a client user';
            END IF;
            PERFORM fn_assert_client_exists(p_client_id);
        WHEN 'vendor' THEN
            IF p_vendor_id IS NULL THEN
                RAISE EXCEPTION 'p_vendor_id is required when registering a vendor user';
            END IF;
            PERFORM fn_assert_vendor_exists(p_vendor_id);
        WHEN 'management' THEN
            IF p_role_id IS NULL THEN
                RAISE EXCEPTION 'p_role_id is required when registering a management user';
            END IF;
            PERFORM fn_assert_role_exists(p_role_id);
        ELSE
            RAISE EXCEPTION 'Invalid user type: %. Must be client, vendor, or management', p_type;
    END CASE;

    -- Задолжителните текстуални полиња не смеат да бидат празни
    PERFORM fn_assert_not_blank(p_first_name, 'first_name');
    PERFORM fn_assert_not_blank(p_last_name, 'last_name');
    PERFORM fn_assert_not_blank(p_email, 'email');
    PERFORM fn_assert_not_blank(p_password_hash, 'password_hash');

    -- Корисникот се креира неактивен (is_active DEFAULT false);
    -- sp_activate_user е чекорот на потврда што го активира
    INSERT INTO "User" (type, first_name, last_name, email, password_hash)
    VALUES (p_type, p_first_name, p_last_name, p_email, p_password_hash)
    RETURNING user_id INTO p_user_id;  -- RETURNING го дава новиот клуч во OUT-параметарот

    -- Редицата на подтипот; тригерот trg_enforce_user_subtype ја проверува според "User".type
    CASE p_type
        WHEN 'client' THEN
            INSERT INTO Client_User (user_id, client_id)
            VALUES (p_user_id, p_client_id);
        WHEN 'vendor' THEN
            INSERT INTO Vendor_User (user_id, vendor_id)
            VALUES (p_user_id, p_vendor_id);
        WHEN 'management' THEN
            INSERT INTO Management_User (user_id, role_id)
            VALUES (p_user_id, p_role_id);
        ELSE
            RAISE EXCEPTION 'Invalid user type: %. Must be client, vendor, or management', p_type;
    END CASE;
END;
$$;

CALL sp_register_user(NULL, 'client', 'Ана', 'Петрова', 'ana.petrova@example.com', 'sha256:...', p_client_id => 17);

---

Процедура 2: Активирање на корисник (sp_activate_user)

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

CREATE OR REPLACE PROCEDURE sp_activate_user(
    IN p_user_id int4
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Состојбата е дел од условот WHERE:
    -- една наредба истовремено проверува и запишува
    UPDATE "User"
    SET    is_active = true
    WHERE  user_id   = p_user_id
      AND  is_active = false;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User % does not exist or is already active', p_user_id;
    END IF;
END;
$$;

CALL sp_activate_user(77001);

---

Процедура 3: Деактивирање на корисник (sp_deactivate_user)

Ја деактивира сметката наместо да ја брише, бидејќи историјата (рецензии, спорови, промени на статус) упатува на корисникот што дејствувал. Ако корисникот е менаџмент корисник, отворените спорови што му се доделени се ослободуваат, за ниту еден спор да не остане кај корисник што заминал.

CREATE OR REPLACE PROCEDURE sp_deactivate_user(
    IN p_user_id int4
)
LANGUAGE plpgsql AS $$
DECLARE
    v_is_management bool;
BEGIN
    -- Состојбата е дел од условот WHERE, како кај sp_activate_user
    UPDATE "User"
    SET    is_active = false
    WHERE  user_id   = p_user_id
      AND  is_active = true;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User % does not exist or is already inactive', p_user_id;
    END IF;

    -- Менаџмент корисник што заминува не смее да остане доделен на отворени спорови
    SELECT EXISTS (
        SELECT 1 FROM Management_User WHERE user_id = p_user_id
    ) INTO v_is_management;

    IF v_is_management THEN
        UPDATE Dispute_Ticket
        SET    assigned_management_user_id = NULL
        WHERE  assigned_management_user_id = p_user_id
          AND  is_resolved                 = false;
    END IF;
END;
$$;

CALL sp_deactivate_user(77001);

---

Процедура 4: Договор со прв проект (sp_create_contract_with_project)

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

CREATE OR REPLACE PROCEDURE sp_create_contract_with_project(
    OUT p_contract_id        int4,
    OUT p_project_id         int4,
    IN  p_client_id          int4,
    IN  p_vendor_id          int4,
    IN  p_contract_title     text,
    IN  p_project_name       text,
    IN  p_status_id          int4,
    IN  p_budget             numeric(10,2),
    IN  p_contract_number    text          DEFAULT NULL,
    IN  p_cvc_start_date     date          DEFAULT CURRENT_DATE,
    IN  p_cvc_end_date       date          DEFAULT NULL,
    IN  p_total_value        numeric(10,2) DEFAULT NULL,
    IN  p_currency_code      text          DEFAULT NULL,
    IN  p_terms_summary      text          DEFAULT NULL,
    IN  p_project_start_date date          DEFAULT CURRENT_DATE,
    IN  p_project_end_date   date          DEFAULT NULL
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Задолжителните текстуални полиња не смеат да бидат празни
    PERFORM fn_assert_not_blank(p_contract_title, 'contract_title');
    PERFORM fn_assert_not_blank(p_project_name, 'project_name');

    -- Буџетот мора да биде даден и позитивен
    PERFORM fn_assert_positive(p_budget, 'Budget');

    -- Референцираните редици мора да постојат пред запишувањето
    PERFORM fn_assert_client_exists(p_client_id);
    PERFORM fn_assert_vendor_exists(p_vendor_id);
    PERFORM fn_assert_status_exists(p_status_id);

    INSERT INTO Client_Vendor_Contract (
        client_id, vendor_id, contract_title, contract_number,
        start_date, end_date, total_value, currency_code, terms_summary
    )
    VALUES (
        p_client_id, p_vendor_id, p_contract_title, p_contract_number,
        p_cvc_start_date, p_cvc_end_date, p_total_value, p_currency_code, p_terms_summary
    )
    RETURNING contract_id INTO p_contract_id;

    -- Името на проектот е уникатно внатре во договорот (uq_project_contract_name);
    -- договорот штотуку е создаден, па првиот проект не може да се судри со друг
    INSERT INTO Project (
        contract_id, status_id, project_name, start_date, end_date, budget
    )
    VALUES (
        p_contract_id, p_status_id, p_project_name,
        p_project_start_date, p_project_end_date, p_budget
    )
    RETURNING project_id INTO p_project_id;
END;
$$;

CALL sp_create_contract_with_project(NULL, NULL, 17, 42, 'Развој на веб-портал', 'Веб-портал, фаза 1', 1, 125000.00,
                                     p_total_value => 500000.00, p_currency_code => 'EUR');

---

Процедура 5: Промена на статус на проект (sp_update_project_status)

Го води животниот циклус на проектот: го менува статусот и запишува редица во историјата на статуси со точно еден актер, корисник на софтверската агенција или менаџмент корисник. Новиот статус мора да постои. Тригерот при запишувањето во историјата проверува дека проектот и актерот постојат и дека корисникот на агенцијата ѝ припаѓа на агенцијата на проектот. Ако проектот веќе е во целниот статус, не се запишува ништо. Проект што веќе има рецензија не може да се врати во отворен статус.

CREATE OR REPLACE PROCEDURE sp_update_project_status(
    IN p_project_id         int4,
    IN p_new_status_id      int4,
    IN p_vendor_user_id     int4 DEFAULT NULL,
    IN p_management_user_id int4 DEFAULT NULL,
    IN p_comment            text DEFAULT NULL
)
LANGUAGE plpgsql AS $$
DECLARE
    v_current_status_id int4;
BEGIN
    -- Точно еден актер: корисник на агенцијата или менаџмент корисник, никогаш двата или ниту еден
    IF (p_vendor_user_id IS NULL) = (p_management_user_id IS NULL) THEN
        RAISE EXCEPTION
            'Provide exactly one actor: p_vendor_user_id or p_management_user_id';
    END IF;

    PERFORM fn_assert_status_exists(p_new_status_id);

    -- Ако проектот веќе е во целниот статус, не се запишува ништо
    SELECT status_id INTO v_current_status_id
    FROM Project
    WHERE project_id = p_project_id;

    IF v_current_status_id = p_new_status_id THEN
        RAISE NOTICE 'Project % is already at status % – no update performed',
            p_project_id, p_new_status_id;
        RETURN;
    END IF;

    -- Рецензиран проект останува завршен: не може да се врати во отворен статус
    IF EXISTS (SELECT 1 FROM Review WHERE project_id = p_project_id)
       AND (SELECT status_name FROM Project_Status WHERE status_id = p_new_status_id)
           NOT IN ('Completed', 'Cancelled') THEN
        RAISE EXCEPTION 'Project % has a review and cannot return to an open status', p_project_id;
    END IF;

    UPDATE Project
    SET    status_id = p_new_status_id
    WHERE  project_id = p_project_id;

    -- проектот, актерот и неговата агенција ги проверува trg_status_history_actor
    INSERT INTO Project_Status_History (
        project_id, status_id, vendor_user_id, management_user_id, comment
    )
    VALUES (
        p_project_id, p_new_status_id, p_vendor_user_id, p_management_user_id, p_comment
    );
END;
$$;

CALL sp_update_project_status(1400001, 3, NULL, 75001, 'Одобрено од менаџмент');

---

Процедура 6: Промена на буџет на проект (sp_update_project_budget)

Го менува буџетот на проектот; новиот буџет мора да биде позитивен, а проектот да постои. Ревизиската редица со стариот и новиот буџет ја запишува тригерот за ревизија на буџетот, така што и промена надвор од процедурата останува забележана.

CREATE OR REPLACE PROCEDURE sp_update_project_budget(
    IN p_project_id int4,
    IN p_new_budget numeric(10,2)
)
LANGUAGE plpgsql AS $$
BEGIN
    PERFORM fn_assert_positive(p_new_budget, 'Budget');

    -- Ревизиската редица ја запишува trg_capture_budget_change, преку тригерот
    -- trg_project_budget_audit и неговата клаузула WHEN (само кога буџетот се менува)
    UPDATE Project
    SET    budget = p_new_budget
    WHERE  project_id = p_project_id;

    -- Ниту една погодена редица значи дека проектот не постои; проверката ја дава пораката
    IF NOT FOUND THEN
        PERFORM fn_assert_project_exists(p_project_id);
    END IF;
END;
$$;

CALL sp_update_project_budget(1400001, 175000.00);

---

Процедура 7: Поднесување рецензија (sp_submit_review)

Поднесување рецензија од клиент по завршен проект: една рецензија и нејзините оценки по димензии, во една трансакција; оценките се предаваат како JSON низа со барем една димензија (не мора да бидат оценети сите десет). Авторот мора да биде корисник на клиентот од договорот на проектот, проектот да биде завршен (Completed или Cancelled, што го гарантира тригерот 18) и да нема веќе рецензија, димензиите да постојат без повторување и оценките да се меѓу 1 и 5. Рецензијата се создава необјавена.

CREATE OR REPLACE PROCEDURE sp_submit_review(
    OUT p_review_id      int4,
    IN  p_project_id     int4,
    IN  p_client_user_id int4,
    IN  p_summary_text   text,
    IN  p_scores         jsonb
)
LANGUAGE plpgsql AS $$
DECLARE
    v_dimension_id int4;
    v_score_value  int4;
BEGIN
    -- Резимето не смее да биде празно
    PERFORM fn_assert_not_blank(p_summary_text, 'summary_text');

    -- Обликот на p_scores: непразна JSON низа од објекти, секој со
    -- нумерички dimension_id и score_value
    -- jsonb_typeof го враќа JSON типот на вредноста ('array', 'object', 'number', ...)
    IF p_scores IS NULL OR jsonb_typeof(p_scores) <> 'array' THEN
        RAISE EXCEPTION 'p_scores must be a JSON array of {dimension_id, score_value} objects';
    END IF;

    IF jsonb_array_length(p_scores) = 0 THEN
        RAISE EXCEPTION 'p_scores must contain at least one dimension score';
    END IF;

    -- jsonb_array_elements ја разложува низата во по една редица за секој елемент (v)
    IF EXISTS (
        SELECT 1
        FROM jsonb_array_elements(p_scores) AS t(v)
        WHERE jsonb_typeof(v) <> 'object'
           OR jsonb_typeof(v->'dimension_id') IS DISTINCT FROM 'number'
           OR jsonb_typeof(v->'score_value') IS DISTINCT FROM 'number'
    ) THEN
        RAISE EXCEPTION
            'Each element of p_scores must be an object with numeric dimension_id and score_value';
    END IF;

    -- Проектот и клиентскиот корисник мора да постојат
    PERFORM fn_assert_project_exists(p_project_id);
    PERFORM fn_assert_client_user_exists(p_client_user_id);

    -- Корисникот мора да припаѓа на клиентот од договорот на проектот;
    -- NULL (непознат корисник или проект) се смета за несовпаѓање
    IF NOT coalesce(fn_is_client_user_of_project(p_client_user_id, p_project_id), false) THEN
        RAISE EXCEPTION
            'Client user % does not belong to the client associated with project %',
            p_client_user_id, p_project_id;
    END IF;

    -- Најмногу една рецензија по проект (UNIQUE врз Review.project_id)
    IF EXISTS (SELECT 1 FROM Review WHERE project_id = p_project_id) THEN
        RAISE EXCEPTION 'A review already exists for project %', p_project_id;
    END IF;

    -- Ниту една димензија не смее да се повторува во низата
    IF EXISTS (
        SELECT 1
        FROM (
            SELECT (v->>'dimension_id')::int4 AS dim_id
            FROM jsonb_array_elements(p_scores) AS t(v)
        ) dims
        GROUP BY dim_id
        HAVING COUNT(*) > 1
    ) THEN
        RAISE EXCEPTION 'Duplicate dimension_id found in p_scores';
    END IF;

    -- Секоја димензија мора да постои: се бара првата што не постои
    -- и проверката fn_assert_dimension_exists ја дава пораката за неа
    SELECT (v->>'dimension_id')::int4
    INTO   v_dimension_id
    FROM   jsonb_array_elements(p_scores) AS t(v)
    WHERE  NOT EXISTS (
        SELECT 1 FROM Rating_Dimension rd
        WHERE rd.dimension_id = (v->>'dimension_id')::int4
    )
    LIMIT  1;

    IF FOUND THEN
        PERFORM fn_assert_dimension_exists(v_dimension_id);
    END IF;

    -- Секоја оценка мора да биде меѓу 1 и 5; ->> го чита полето како текст, ::int4 го претвора во број
    SELECT (v->>'score_value')::int4
    INTO   v_score_value
    FROM   jsonb_array_elements(p_scores) AS t(v)
    WHERE  (v->>'score_value')::int4 NOT BETWEEN 1 AND 5
    LIMIT  1;

    IF FOUND THEN
        RAISE EXCEPTION 'Score value must be between 1 and 5 (received: %)', v_score_value;
    END IF;

    -- завршеноста на проектот (Completed или Cancelled) ја проверува trg_review_finished_project
    INSERT INTO Review (project_id, client_user_id, summary_text)
    VALUES (p_project_id, p_client_user_id, p_summary_text)
    RETURNING review_id INTO p_review_id;

    -- По една редица за секој елемент од низата, со еден INSERT ... SELECT
    INSERT INTO Review_Score (review_id, dimension_id, score_value)
    SELECT p_review_id, (v->>'dimension_id')::int4, (v->>'score_value')::int4
    FROM   jsonb_array_elements(p_scores) AS t(v);
END;
$$;

CALL sp_submit_review(NULL, 1400001, 5001, 'Solid delivery, good communication.',
                      '[{"dimension_id": 1, "score_value": 5}, {"dimension_id": 2, "score_value": 4}]');

---

Процедура 8: Поднесување спор (sp_file_dispute)

Оспорување на рецензија од страна на софтверската агенција: нејзин корисник отвора спор врз рецензијата. Корисникот мора да припаѓа на агенцијата што го испорачала рецензираниот проект и не смее веќе да има отворен спор за истата рецензија. Додека спорот е отворен, рецензијата не може да се објави.

CREATE OR REPLACE PROCEDURE sp_file_dispute(
    OUT p_ticket_id      int4,
    IN  p_review_id      int4,
    IN  p_vendor_user_id int4,
    IN  p_reason         text
)
LANGUAGE plpgsql AS $$
DECLARE
    v_project_id int4;
BEGIN
    -- Причината не смее да биде празна
    PERFORM fn_assert_not_blank(p_reason, 'reason');

    PERFORM fn_assert_review_exists(p_review_id);

    -- Корисникот на агенцијата мора да постои
    PERFORM fn_assert_vendor_user_exists(p_vendor_user_id);

    -- Истиот корисник не смее да има два отворени спора за иста рецензија
    IF EXISTS (
        SELECT 1 FROM Dispute_Ticket
        WHERE review_id      = p_review_id
          AND vendor_user_id = p_vendor_user_id
          AND is_resolved    = false
    ) THEN
        RAISE EXCEPTION
            'Vendor user % already has an unresolved dispute on review %',
            p_vendor_user_id, p_review_id;
    END IF;

    -- Корисникот мора да припаѓа на агенцијата што го испорачала рецензираниот
    -- проект; NULL (непознат корисник или проект) се смета за несовпаѓање
    SELECT project_id
    INTO   v_project_id
    FROM   Review
    WHERE  review_id = p_review_id;

    IF NOT coalesce(fn_is_vendor_user_of_project(p_vendor_user_id, v_project_id), false) THEN
        RAISE EXCEPTION
            'Vendor user % does not belong to the vendor associated with review %',
            p_vendor_user_id, p_review_id;
    END IF;

    INSERT INTO Dispute_Ticket (review_id, vendor_user_id, reason)
    VALUES (p_review_id, p_vendor_user_id, p_reason)
    RETURNING ticket_id INTO p_ticket_id;
END;
$$;

CALL sp_file_dispute(NULL, 1000001, 55001, 'Оценките не го одразуваат договорениот обем на работа.');

---

Процедура 9: Решавање на спор (sp_resolve_dispute)

Менаџмент корисник решава отворен спор: се доделува корисникот, се запишува белешката и спорот се означува како решен; непостоечки или веќе решен спор се одбива. Времето на решавање го пополнува тригерот за решавање на спор.

CREATE OR REPLACE PROCEDURE sp_resolve_dispute(
    IN p_ticket_id                   int4,
    IN p_assigned_management_user_id int4,
    IN p_resolution_note             text
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Белешката за решавање не смее да биде празна
    PERFORM fn_assert_not_blank(p_resolution_note, 'resolution_note');

    -- Постоењето на доделениот менаџмент корисник го проверува тригерот
    -- trg_dispute_ticket_resolve при UPDATE подолу, кој го запишува и
    -- времето на решавање (resolved_at); само отворен спор може да се реши
    UPDATE Dispute_Ticket
    SET    assigned_management_user_id = p_assigned_management_user_id,
           resolution_note             = p_resolution_note,
           is_resolved                 = true
    WHERE  ticket_id   = p_ticket_id
      AND  is_resolved = false;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'Ticket % does not exist or is already resolved', p_ticket_id;
    END IF;
END;
$$;

CALL sp_resolve_dispute(143895, 75001, 'Разгледано: оценките остануваат, рецензијата се објавува.');

---

Процедура 10: Објавување рецензија (sp_publish_review)

Ја објавува рецензијата, по што таа влегува во јавната просечна оценка на софтверската агенција. Непостоечка или веќе објавена рецензија се одбива; рецензија со отворен спор ја одбива тригерот за објавување.

CREATE OR REPLACE PROCEDURE sp_publish_review(
    IN p_review_id int4
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Тригерот trg_review_publish_guard ја одбива промената додека
    -- рецензијата има отворен спор
    UPDATE Review
    SET    is_published = true
    WHERE  review_id    = p_review_id
      AND  is_published = false;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'Review % does not exist or is already published', p_review_id;
    END IF;
END;
$$;

CALL sp_publish_review(1000001);

---

Процедура 11: Обновување на претплата (sp_renew_vendor_subscription)

Обновување на претплатата на софтверската агенција: тековниот активен период се затвора најдоцна на денот кога почнува новиот (порано зададен крај се задржува), а новиот период се отвора со избраното ниво. Агенцијата и нивото мора да постојат, а новиот период мора да почне по почетокот на тековниот; договорната цена ја проверува тригерот за цени на претплата.

CREATE OR REPLACE PROCEDURE sp_renew_vendor_subscription(
    OUT p_new_contract_id      int4,  -- клучот на новиот период; contract_id е примарниот клуч на Vendor_Subscription
    IN  p_vendor_id            int4,
    IN  p_new_tier_id          int4,
    IN  p_new_start_date       date          DEFAULT CURRENT_DATE,
    IN  p_new_end_date         date          DEFAULT NULL,
    IN  p_new_negotiated_price numeric(10,2) DEFAULT NULL
)
LANGUAGE plpgsql AS $$
DECLARE
    v_current_start date;
BEGIN
    PERFORM fn_assert_vendor_exists(p_vendor_id);

    IF p_new_start_date IS NULL THEN
        RAISE EXCEPTION 'start_date is required';
    END IF;

    -- Крајниот датум, ако е даден, мора да биде по почетниот
    IF p_new_end_date IS NOT NULL AND p_new_end_date <= p_new_start_date THEN
        RAISE EXCEPTION
            'end_date (%) must be after start_date (%)', p_new_end_date, p_new_start_date;
    END IF;

    -- Тековниот период се затвора на почетокот на новиот (end_date е
    -- ексклузивен; порано зададен крај се задржува), па новиот период мора да
    -- почне по почетокот на тековниот, инаку затворениот период би бил празен
    SELECT start_date
    INTO   v_current_start
    FROM   Vendor_Subscription
    WHERE  vendor_id = p_vendor_id
      AND  is_active = true;

    IF v_current_start IS NOT NULL AND v_current_start >= p_new_start_date THEN
        RAISE EXCEPTION
            'The current period of vendor % began on %; the new period must start after that',
            p_vendor_id, v_current_start;
    END IF;

    -- LEAST: отворен период добива крај; период што веќе истекол го задржува својот
    UPDATE Vendor_Subscription
    SET    is_active = false,
           end_date  = LEAST(end_date, p_new_start_date)
    WHERE  vendor_id = p_vendor_id
      AND  is_active = true;

    -- нивото и договорената цена ги проверува trg_vendor_subscription_pricing
    INSERT INTO Vendor_Subscription (
        vendor_id, tier_id, negotiated_price, start_date, end_date
    )
    VALUES (
        p_vendor_id, p_new_tier_id, p_new_negotiated_price,
        p_new_start_date, p_new_end_date
    )
    RETURNING contract_id INTO p_new_contract_id;
END;
$$;

CALL sp_renew_vendor_subscription(NULL, 42, 3, CURRENT_DATE, NULL, 1234.50);

---

Функции

Ниту една функција не запишува податоци; ги повикуваат процедурите и тригерите.

Функција 1: Целосно име на корисник (fn_get_full_name)

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

CREATE OR REPLACE FUNCTION fn_get_full_name(p_user_id int4)
RETURNS text LANGUAGE sql STABLE STRICT AS $$
    -- STABLE: не менува податоци и за исти аргументи дава ист резултат во една наредба;
    -- STRICT: за NULL аргумент враќа NULL без извршување
    SELECT first_name || ' ' || last_name
    FROM "User"
    WHERE user_id = p_user_id;
$$;

Функција 2: Софтверска агенција на проект (fn_get_vendor_id_for_project)

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

CREATE OR REPLACE FUNCTION fn_get_vendor_id_for_project(p_project_id int4)
RETURNS int4 LANGUAGE sql STABLE STRICT AS $$
    SELECT cvc.vendor_id
    FROM Project p
    JOIN Client_Vendor_Contract cvc ON cvc.contract_id = p.contract_id
    WHERE p.project_id = p_project_id;
$$;

Функција 3: Клиент на проект (fn_get_client_id_for_project)

Го враќа клиентот на проектот, преку договорот, на ист начин. Двете функции се основата за проверките на припадност подолу.

CREATE OR REPLACE FUNCTION fn_get_client_id_for_project(p_project_id int4)
RETURNS int4 LANGUAGE sql STABLE STRICT AS $$
    SELECT cvc.client_id
    FROM Project p
    JOIN Client_Vendor_Contract cvc ON cvc.contract_id = p.contract_id
    WHERE p.project_id = p_project_id;
$$;

Функција 4: Корисник на софтверската агенција на проектот (fn_is_vendor_user_of_project)

Правилото „корисник на софтверска агенција може да оспори рецензија или да запише промена на статус само за проект на својата агенција“: дали корисникот припаѓа на агенцијата што го извршува проектот. Се користи при поднесување спор и при запишување во историјата на статуси.

CREATE OR REPLACE FUNCTION fn_is_vendor_user_of_project(p_user_id int4, p_project_id int4)
RETURNS bool LANGUAGE sql STABLE STRICT AS $$
    SELECT vu.vendor_id = fn_get_vendor_id_for_project(p_project_id)
    FROM Vendor_User vu
    WHERE vu.user_id = p_user_id;
$$;

Функција 5: Корисник на клиентот на проектот (fn_is_client_user_of_project)

Правилото „само клиентот на проектот може да го рецензира“: дали корисникот припаѓа на клиентот од договорот на проектот. Се користи при поднесување рецензија.

CREATE OR REPLACE FUNCTION fn_is_client_user_of_project(p_user_id int4, p_project_id int4)
RETURNS bool LANGUAGE sql STABLE STRICT AS $$
    SELECT cu.client_id = fn_get_client_id_for_project(p_project_id)
    FROM Client_User cu
    WHERE cu.user_id = p_user_id;
$$;

Функција 6: Проверка за непразен текст (fn_assert_not_blank)

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

CREATE OR REPLACE FUNCTION fn_assert_not_blank(p_value text, p_name text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF btrim(coalesce(p_value, '')) = '' THEN
        RAISE EXCEPTION '% must not be blank', p_name;
    END IF;
END;
$$;

Функција 7: Проверка за позитивен износ (fn_assert_positive)

Одбива износ што не е позитивен, со порака што ја именува вредноста. Ја користат процедурите што запишуваат буџет при склучување договор и при промена на буџет.

CREATE OR REPLACE FUNCTION fn_assert_positive(p_value numeric, p_name text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF p_value IS NULL OR p_value <= 0 THEN
        RAISE EXCEPTION '% must be greater than zero (received: %)', p_name, p_value;
    END IF;
END;
$$;

Функции 8–18: Проверки за постоење (fn_assert_*_exists)

Единаесет функции со иста форма, по една за секоја табела на која упатуваат процедурите и тригерите. Тие го проверуваат истото што и надворешниот клуч, но пред запишувањето и со описна порака. Проверката за проект:

CREATE OR REPLACE FUNCTION fn_assert_project_exists(p_project_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Project WHERE project_id = p_project_id) THEN
        RAISE EXCEPTION 'Project % does not exist', p_project_id;
    END IF;
END;
$$;

Останатите десет се fn_assert_vendor_exists, fn_assert_review_exists, fn_assert_client_exists, fn_assert_status_exists, fn_assert_tier_exists, fn_assert_role_exists, fn_assert_client_user_exists, fn_assert_vendor_user_exists, fn_assert_management_user_exists и fn_assert_dimension_exists.

---

Тригери

Тригери 1–3: Подтип на корисник (trg_enforce_user_subtype)

Редица во Client_User, Vendor_User или Management_User се прифаќа само ако типот на корисникот во "User" е соодветно client, vendor или management, така што ниту еден корисник не може да биде во табела на туѓ подтип. Една функција служи за трите табели.

CREATE OR REPLACE FUNCTION trg_enforce_user_subtype()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_expected text;
  v_actual   text;
BEGIN
  -- TG_TABLE_NAME е името на табелата што го активирала тригерот (со мали букви);
  -- NEW е редицата што се вметнува или менува
  v_expected := CASE TG_TABLE_NAME
                  WHEN 'client_user'     THEN 'client'
                  WHEN 'vendor_user'     THEN 'vendor'
                  WHEN 'management_user' THEN 'management'
                  ELSE NULL
                END;

  IF v_expected IS NULL THEN
    RAISE EXCEPTION 'trg_enforce_user_subtype is not valid on table %', TG_TABLE_NAME;
  END IF;

  SELECT type INTO v_actual FROM "User" WHERE user_id = NEW.user_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'User % does not exist', NEW.user_id;
  END IF;

  IF v_actual <> v_expected THEN
    RAISE EXCEPTION 'User % type must be % (it is %)', NEW.user_id, v_expected, v_actual;
  END IF;

  RETURN NEW;  -- редицата се пропушта; RAISE EXCEPTION погоре ја одбива
END;
$$;

CREATE OR REPLACE TRIGGER trg_client_user_subtype
  BEFORE INSERT OR UPDATE ON Client_User
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();

CREATE OR REPLACE TRIGGER trg_vendor_user_subtype
  BEFORE INSERT OR UPDATE ON Vendor_User
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();

CREATE OR REPLACE TRIGGER trg_management_user_subtype
  BEFORE INSERT OR UPDATE ON Management_User
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();

Тригер 4: Непроменлив тип на корисник (trg_prevent_user_type_change)

Типот на корисникот е фиксен од регистрацијата: промена би ја оставила редицата на стариот подтип во несогласност со новиот тип.

CREATE OR REPLACE FUNCTION trg_prevent_user_type_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- OLD е редицата пред промената; тригерот се повикува само кога типот се менува (WHEN)
  RAISE EXCEPTION 'User % type must stay % and cannot be changed', OLD.user_id, OLD.type;
END;
$$;

CREATE OR REPLACE TRIGGER trg_user_type_immutable
  BEFORE UPDATE OF type ON "User"
  FOR EACH ROW
  WHEN (NEW.type <> OLD.type)
  EXECUTE FUNCTION trg_prevent_user_type_change();

Тригер 5: Договорна цена на претплата (trg_enforce_negotiated_price)

Ценовната политика на нивоата: претплата на ниво со договорна цена мора да има договорена цена, а претплата на ниво со фиксна цена не смее да има.

CREATE OR REPLACE FUNCTION trg_enforce_negotiated_price()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_allows_custom bool;
BEGIN
  -- Нивото мора да постои пред проверката на ценовната политика
  PERFORM fn_assert_tier_exists(NEW.tier_id);

  SELECT allows_custom_pricing
  INTO v_allows_custom
  FROM Subscription_Tier
  WHERE tier_id = NEW.tier_id;

  IF v_allows_custom AND NEW.negotiated_price IS NULL THEN
    RAISE EXCEPTION
      'negotiated_price is required for tiers with custom pricing (contract_id: %)', NEW.contract_id;
  END IF;

  IF NOT v_allows_custom AND NEW.negotiated_price IS NOT NULL THEN
    RAISE EXCEPTION
      'negotiated_price must be NULL for fixed-price tiers (contract_id: %)', NEW.contract_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_vendor_subscription_pricing
  BEFORE INSERT OR UPDATE ON Vendor_Subscription
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_negotiated_price();

Тригери 6–12: Време на последна промена (trg_set_updated_at)

Колоната updated_at се пополнува автоматски при секоја промена на редица во седумте табели што ја имаат, така што апликацијата не мора да ја поставува.

CREATE OR REPLACE FUNCTION trg_set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- BEFORE UPDATE: промената на NEW се запишува во редицата
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_project_updated_at
  BEFORE UPDATE ON Project
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_vendor_subscription_updated_at
  BEFORE UPDATE ON Vendor_Subscription
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_cvc_updated_at
  BEFORE UPDATE ON Client_Vendor_Contract
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_pba_updated_at
  BEFORE UPDATE ON Project_Budget_Audit
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_review_updated_at
  BEFORE UPDATE ON Review
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_dispute_ticket_updated_at
  BEFORE UPDATE ON Dispute_Ticket
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_user_updated_at
  BEFORE UPDATE ON "User"
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

Тригер 13: Ревизија на буџетот (trg_capture_budget_change)

Историјата на буџетот: секоја промена на буџетот на проект запишува редица со стариот и новиот износ во Project_Budget_Audit, без разлика дали доаѓа од процедура или од директна промена.

CREATE OR REPLACE FUNCTION trg_capture_budget_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO Project_Budget_Audit (project_id, old_budget, new_budget)
  VALUES (OLD.project_id, OLD.budget, NEW.budget);
  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_project_budget_audit
  AFTER UPDATE ON Project
  FOR EACH ROW
  -- IS DISTINCT FROM ги споредува и NULL вредностите како обични вредности
  WHEN (OLD.budget IS DISTINCT FROM NEW.budget)
  EXECUTE FUNCTION trg_capture_budget_change();

Тригер 14: Актер во историјата на статуси (trg_validate_status_history_actor)

Промена на статус може да запише само корисник на софтверската агенција што го извршува проектот или менаџмент корисник; корисник на друга агенција не може да менува туѓ проект. Тригерот проверува и дека проектот и актерот постојат, па процедурата за промена на статус не го повторува тоа.

CREATE OR REPLACE FUNCTION trg_validate_status_history_actor()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Проектот мора да постои пред проверката на актерот
  PERFORM fn_assert_project_exists(NEW.project_id);

  -- Актерот од агенцијата мора да постои и да ѝ припаѓа на агенцијата на проектот
  IF NEW.vendor_user_id IS NOT NULL THEN
    PERFORM fn_assert_vendor_user_exists(NEW.vendor_user_id);

    IF NOT coalesce(fn_is_vendor_user_of_project(NEW.vendor_user_id, NEW.project_id), false) THEN
      RAISE EXCEPTION
        'vendor_user % does not belong to the vendor on project %',
        NEW.vendor_user_id, NEW.project_id;
    END IF;
  END IF;

  -- Менаџмент корисникот, ако е актер, мора да постои
  IF NEW.management_user_id IS NOT NULL THEN
    PERFORM fn_assert_management_user_exists(NEW.management_user_id);
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_status_history_actor
  BEFORE INSERT ON Project_Status_History
  FOR EACH ROW EXECUTE FUNCTION trg_validate_status_history_actor();

Тригер 15: Преклопување на претплати (trg_prevent_subscription_overlap)

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

CREATE OR REPLACE FUNCTION trg_prevent_subscription_overlap()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Два периода се преклопуваат ако секој почнува пред крајот на другиот;
  -- NULL end_date е отворен период. contract_id <> NEW.contract_id ја
  -- исклучува самата редица при UPDATE
  IF EXISTS (
    SELECT 1 FROM Vendor_Subscription
     WHERE vendor_id    = NEW.vendor_id
       AND contract_id <> NEW.contract_id
       AND (NEW.end_date IS NULL OR start_date < NEW.end_date)
       AND (end_date    IS NULL OR end_date    > NEW.start_date)
  ) THEN
    RAISE EXCEPTION
      'Vendor % already has a subscription period overlapping % - %',
      NEW.vendor_id, NEW.start_date, coalesce(NEW.end_date::text, 'open');
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_vendor_subscription_overlap
  BEFORE INSERT OR UPDATE ON Vendor_Subscription
  FOR EACH ROW EXECUTE FUNCTION trg_prevent_subscription_overlap();

Тригер 16: Решавање на спор (trg_validate_dispute_resolution)

Спор може да се реши само ако има доделен менаџмент корисник; времето на решавање се пополнува автоматски, а решен спор не може повторно да се отвори.

CREATE OR REPLACE FUNCTION trg_validate_dispute_resolution()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Премин од отворен во решен спор
  IF NEW.is_resolved = true AND OLD.is_resolved = false THEN
    IF NEW.assigned_management_user_id IS NULL THEN
      RAISE EXCEPTION
        'Dispute ticket % must have an assigned management user before it can be resolved', OLD.ticket_id;
    END IF;

    -- Доделениот менаџмент корисник мора да постои
    PERFORM fn_assert_management_user_exists(NEW.assigned_management_user_id);

    IF NEW.resolved_at IS NULL THEN
      NEW.resolved_at := NOW();
    END IF;
  END IF;

  -- Обратниот премин е забранет
  IF OLD.is_resolved = true AND NEW.is_resolved = false THEN
    RAISE EXCEPTION 'Resolved dispute ticket % cannot be re-opened', OLD.ticket_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_dispute_ticket_resolve
  BEFORE UPDATE ON Dispute_Ticket
  FOR EACH ROW EXECUTE FUNCTION trg_validate_dispute_resolution();

Тригер 17: Објавување на оспорена рецензија (trg_prevent_publishing_disputed_review)

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

CREATE OR REPLACE FUNCTION trg_prevent_publishing_disputed_review()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Премин од необјавена во објавена рецензија
  IF NEW.is_published = true AND OLD.is_published = false THEN
    IF EXISTS (
      SELECT 1 FROM Dispute_Ticket
       WHERE review_id   = NEW.review_id
         AND is_resolved = false
    ) THEN
      RAISE EXCEPTION
        'Review % cannot be published while it has unresolved dispute tickets', NEW.review_id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_review_publish_guard
  BEFORE UPDATE ON Review
  FOR EACH ROW EXECUTE FUNCTION trg_prevent_publishing_disputed_review();

Тригер 18: Рецензија само за завршен проект (trg_require_finished_project)

Рецензијата е оценка на завршен ангажман: рецензија може да се запише само за проект со статус Completed или Cancelled, а не за проект во тек. Правилото чита друга табела (Project), па не може да биде CHECK ограничување; тригерот важи и за директно вметнување, а процедурата за поднесување рецензија се потпира на него.

CREATE OR REPLACE FUNCTION trg_require_finished_project()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_status text;
BEGIN
  -- NEW.project_id е проектот на рецензијата што се вметнува
  SELECT ps.status_name
  INTO v_status
  FROM Project p
  JOIN Project_Status ps ON ps.status_id = p.status_id
  WHERE p.project_id = NEW.project_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Project % does not exist', NEW.project_id;
  END IF;

  IF v_status NOT IN ('Completed', 'Cancelled') THEN
    RAISE EXCEPTION 'Project % is %; a review needs a finished project', NEW.project_id, v_status;
  END IF;

  RETURN NEW;
END;
$$;

-- и при промена на project_id на постоечка рецензија
CREATE OR REPLACE TRIGGER trg_review_finished_project
  BEFORE INSERT OR UPDATE OF project_id ON Review
  FOR EACH ROW EXECUTE FUNCTION trg_require_finished_project();