Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

09/13/26 21:27:34 (2 weeks ago)

Author:

231075

Comment:

--

DatabaseProgramming

@@ +1 @@
+= 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 базата.
+$$$