= DatabaseProgramming =

== Опис ==

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

Оваа логика се користи за:

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

Кодот е поделен во:

* [attachment:functions.sql functions.sql]
* [attachment:procedures.sql procedures.sql]
* [attachment:triggers.sql triggers.sql]

== Функции ==

Функциите главно се користат како помошни операции кои потоа се повикуваат од процедурите и тригерите.

Едноставен пример е функцијата за добивање на целосното име на корисник:

{{{#!sql
CREATE OR REPLACE FUNCTION fn_get_full_name(p_user_id int4)
RETURNS text LANGUAGE sql STABLE AS $$
SELECT first_name || ' ' || last_name
FROM "User"
WHERE user_id = p_user_id;

$$$;
}}}

За добивање на vendor-от поврзан со даден проект се користи:

{{{#!sql
CREATE OR REPLACE FUNCTION fn_get_vendor_id_for_project(p_project_id int4)
RETURNS int4 LANGUAGE sql STABLE 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;
$$;
}}}

Поголемиот дел од функциите се validation функции. На пример:

{{{#!sql
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;
$$;
}}}

На истиот принцип се имплементирани проверки за `Vendor`, `Client`, `Review`, `Project_Status`, `Subscription_Tier`, корисничките типови и `Rating_Dimension`.

== Процедури ==

Процедурите ги имплементираат главните операции кои менуваат податоци во повеќе поврзани табели.

=== Регистрација на корисник ===

`sp_register_user` прво креира запис во `"User"`, а потоа го додава корисникот во соодветната subtype табела.

{{{#!sql
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;

IF p_type = 'client' THEN
    INSERT INTO Client_User (user_id, client_id)
    VALUES (p_user_id, p_client_id);

ELSIF p_type = 'vendor' THEN
    INSERT INTO Vendor_User (user_id, vendor_id)
    VALUES (p_user_id, p_vendor_id);

ELSIF p_type = 'management' THEN
    INSERT INTO Management_User (user_id, role_id)
    VALUES (p_user_id, p_role_id);
END IF;
}}}

Пред внесувањето се проверуваат типот на корисникот, задолжителните параметри и референците кон останатите табели.

=== Креирање договор и проект ===

`sp_create_contract_with_project` овозможува со една операција да се креира договор и неговиот почетен проект:

{{{#!sql
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;

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

Пред креирањето се проверуваат client, vendor и project status, како и буџетот и потребните текстуални полиња.

=== Промена на статус на проект ===

`sp_update_project_status` го менува тековниот статус и истовремено додава запис во историјата:

{{{#!sql
UPDATE Project
SET status_id = p_new_status_id
WHERE project_id = p_project_id;

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

Со ова секоја промена на статусот останува евидентирана.

=== Внесување review ===

`sp_submit_review` креира review и неговите оценки во една операција.

Оценките се предаваат како JSONB низа:

{{{#!sql
FOR v_score IN
    SELECT * FROM jsonb_array_elements(p_scores)
LOOP
    PERFORM fn_assert_dimension_exists(
        (v_score->>'dimension_id')::int4
    );

    IF ((v_score->>'score_value')::int4 < 1)
       OR ((v_score->>'score_value')::int4 > 5) THEN
        RAISE EXCEPTION
            'Score value must be between 1 and 5';
    END IF;

    INSERT INTO Review_Score (
        review_id,
        dimension_id,
        score_value
    )
    VALUES (
        p_review_id,
        (v_score->>'dimension_id')::int4,
        (v_score->>'score_value')::int4
    );
END LOOP;
}}}

Процедурата дополнително проверува дали корисникот припаѓа на клиентот на проектот и дали за проектот веќе постои review.

Останатите процедури се користат за:

 * деактивирање на корисник
 * промена на project budget
 * отворање dispute ticket
 * разрешување dispute ticket
 * објавување review
 * обновување vendor subscription

== Тригери ==

Тригерите автоматски извршуваат проверки или дополнителни операции при `INSERT` и `UPDATE`.

=== User subtype проверка ===

За `Client_User`, `Vendor_User` и `Management_User` се користи заедничка trigger функција која спречува еден корисник да припаѓа на повеќе subtype табели.

{{{#!sql
CREATE TRIGGER trg_client_user_subtype
  BEFORE INSERT ON Client_User
  FOR EACH ROW
  EXECUTE FUNCTION trg_enforce_user_subtype();

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

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

Дополнително се проверува дали вредноста на `"User".type` одговара на subtype табелата.

=== Автоматско ажурирање на updated_at ===

За табелите кои имаат `updated_at` е дефинирана заедничка trigger функција:

{{{#!sql
CREATE OR REPLACE FUNCTION trg_set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;
}}}

На пример:

{{{#!sql
CREATE TRIGGER trg_project_updated_at
  BEFORE UPDATE ON Project
  FOR EACH ROW
  EXECUTE FUNCTION trg_set_updated_at();
}}}

Истиот механизам се користи и за договори, subscriptions, reviews, dispute tickets и корисници.

=== Историја на промена на буџет ===

При промена на буџетот на проект, автоматски се креира audit запис:

{{{#!sql
CREATE OR REPLACE FUNCTION trg_capture_budget_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.budget IS DISTINCT FROM OLD.budget THEN
    INSERT INTO Project_Budget_Audit (
        project_id,
        old_budget,
        new_budget
    )
    VALUES (
        OLD.project_id,
        OLD.budget,
        NEW.budget
    );
  END IF;

  RETURN NEW;
END;
$$;
}}}

Тригерот се активира после промена на `Project`:

{{{#!sql
CREATE TRIGGER trg_project_budget_audit
  AFTER UPDATE ON Project
  FOR EACH ROW
  EXECUTE FUNCTION trg_capture_budget_change();
}}}

=== Проверка на dispute и review ===

При разрешување на dispute се проверува дали е назначен management корисник. Доколку `resolved_at` не е поставен, автоматски се поставува тековното време.

{{{#!sql
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;

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

Дополнително, review не може да биде објавен додека има неразрешен dispute:

{{{#!sql
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;
}}}

== Заклучок ==

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

Со ова дел од апликациската и validation логиката е имплементирана директно во PostgreSQL базата.
$$$