DatabaseProgramming

Опис

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

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

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

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

• functions.sql​
• procedures.sql​
• triggers.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-от поврзан со даден проект се користи:

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 функции. На пример:

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 табела.

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 овозможува со една операција да се креира договор и неговиот почетен проект:

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 го менува тековниот статус и истовремено додава запис во историјата:

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 низа:

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 табели.

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 функција:

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

На пример:

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

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

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

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

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:

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 не е поставен, автоматски се поставува тековното време.

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:

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 базата. $$$