Changes between Version 5 and Version 6 of DatabaseProgramming

Timestamp:

06/16/26 14:21:23 (14 hours ago)

Author:

231025

Comment:

--

DatabaseProgramming

@@ -3 +3 @@
 
 
-Во оваа фаза се надоградуваме со функции и процедури што ќе помогнат дел од честите операции во базата-отворање записник, додавање прекршок, наплата на казна, додавање категорија на возачка-да се извршуваат преку еден повик, со сите потребни проверки веќе вградени во нив. Исто така искористивме и тригери за работите што треба да се случуваат автоматски, како пресметката на вкупниот износ во уплатата и строга контрола на интегритетот, без рачно ажурирање.[[BR]]
+Во оваа фаза се надоградуваме со функции и процедури што ќе помогнат дел од честите операции во базата-отворање записник, додавање прекршок, наплата на казна, затворање записник по платена казна, додавање категорија на возачка-да се извршуваат преку еден повик, со сите потребни проверки веќе вградени во нив. Исто така искористивме и тригери за работите што треба да се случуваат автоматски, како пресметката на вкупниот износ во уплатата и строга контрола на интегритетот, без рачно ажурирање.[[BR]]
 [attachment:programming.sql programming.sql]
 
@@ -93 +93 @@
 === Тригер 3: trigger_proverka_registracija===
 
-Овој тригер е задолжен за проверка на интегритетот на податоците пред да се изврши вметнување или ажурирање во табелата `Registracija`. Тој прави соодветна валидација за постоење на сопственикот (граѓанинот) и самото возило преку нивните примарни клучеви. Како клучен дел од валидацијата, тригерот користи регуларен израз (REGEX) за да осигура дека регистарската табличка го следи официјалниот македонски стандард (на пр. SK-1234-AA или BT-888-ZZ).
+Овој тригер е задолжен за проверка на интегритетот на податоците пред да се изврши вметнување или ажурирање во табелата `Registracija`. По новата шема, `Registracija` повеќе не ги содржи директно граѓанинот и возилото-таа референцира `Sopstvenost` (преку `id_sopstvenost`) и `Registerska_tablica` (преку `id_tablica`). Затоа тригерот прави валидација дека сопственоста постои и дека, доколку е доделена табличка, таа табличка постои. Како клучен дел од валидацијата, тригерот користи регуларен израз (REGEX) врз составениот запис на табличката (регион-број-код) за да осигура дека таа го следи официјалниот македонски стандард (на пр. SK-1234-AA или BT-888-ZZ).
 
 {{{#!sql
@@ -100 +100 @@
 LANGUAGE plpgsql
 AS $$
-BEGIN
-IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = NEW.EMBG) THEN
-RAISE EXCEPTION 'Граѓанинот со ЕМБГ % не постои во системот!', NEW.EMBG;
-END IF;
-
-IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = NEW.broj_na_sasija) THEN
-    RAISE EXCEPTION 'Возилото со број на шасија % не постои во системот!', NEW.broj_na_sasija;
-END IF;
-
-IF NEW.registerska_tablica IS NOT NULL AND NOT (UPPER(NEW.registerska_tablica) ~ '^[A-Z]{2}-[0-9]{3,4}-[A-Z]{2}$') THEN
-    RAISE EXCEPTION 'Регистарската табличка "%" не е во валиден формат! Очекуван формат: ZZ-0000-ZZ (напр. SK-1234-AA)', NEW.registerska_tablica;
+DECLARE
+v_tablica text;
+BEGIN
+IF NOT EXISTS (SELECT 1 FROM Sopstvenost WHERE id_sopstvenost = NEW.id_sopstvenost) THEN
+RAISE EXCEPTION 'Сопственоста со id % не постои во системот!', NEW.id_sopstvenost;
+END IF;
+
+IF NEW.id_tablica IS NOT NULL THEN
+    SELECT region || '-' || broj || '-' || kod
+    INTO v_tablica
+    FROM Registerska_tablica
+    WHERE id_tablica = NEW.id_tablica;
+
+    IF v_tablica IS NULL THEN
+        RAISE EXCEPTION 'Регистарската табличка со id % не постои!', NEW.id_tablica;
+    END IF;
+
+    IF NOT (UPPER(v_tablica) ~ '^[A-Z]{2,3}-[0-9]{3,4}-[A-Z]{2}$') THEN
+        RAISE EXCEPTION 'Регистарската табличка "%" не е во валиден формат! Очекуван формат: ZZ-0000-ZZ (напр. SK-1234-AA)', v_tablica;
+    END IF;
 END IF;
 
@@ -127 +136 @@
 
 === Процедура 1: kreiraj_zapisnik_so_prekrsok ===
-Отвора нов записник со прв прекршок и веднаш креира празна уплата со статус 'Neplateno'. Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.
+Отвора нов записник со прв прекршок и веднаш креира празна уплата со статус 'Neplateno'. Записникот се отвора со `status_zapisnik = 'Otvoren'` (преку default). Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.
 
 {{{#!sql
@@ -340 +349 @@
 }}}
 
+=== Процедура 5: zatvori_zapisnik_po_uplata ===
+Го затвора записникот (`status_zapisnik` → 'Zatvoren') откако казната за него е платена. Дозволува затворање само ако постои уплата за записникот и нејзиниот статус е 'Plateno'; не дозволува повторно затворање на веќе затворен записник. Вообичаено се повикува веднаш по `plati_kazna`.
+
+{{{#!sql
+CREATE OR REPLACE PROCEDURE zatvori_zapisnik_po_uplata(
+p_id_zapisnik int
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+v_status_uplata    varchar(30);
+v_status_zapisnik  varchar(20);
+BEGIN
+SELECT status_zapisnik INTO v_status_zapisnik
+FROM Zapisnik WHERE id_na_zapisnik = p_id_zapisnik;
+
+IF v_status_zapisnik IS NULL THEN
+    RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
+END IF;
+IF v_status_zapisnik = 'Zatvoren' THEN
+    RAISE EXCEPTION 'Zapisnikot % e vekje zatvoren!', p_id_zapisnik;
+END IF;
+
+SELECT status INTO v_status_uplata
+FROM Uplata WHERE id_zapisnik = p_id_zapisnik;
+
+IF v_status_uplata IS NULL THEN
+    RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
+END IF;
+IF v_status_uplata <> 'Plateno' THEN
+    RAISE EXCEPTION 'Zapisnikot % ne moze da se zatvori bidejki uplatata e so status "%" (mora da bide Plateno)!',
+        p_id_zapisnik, v_status_uplata;
+END IF;
+
+UPDATE Zapisnik
+SET status_zapisnik = 'Zatvoren'
+WHERE id_na_zapisnik = p_id_zapisnik;
+
+RAISE NOTICE 'Zapisnik % e zatvoren po izvrshena uplata.', p_id_zapisnik;
+END;
+$$;
+}}}
+
+Повик:
+{{{#!sql
+CALL plati_kazna(30000005, 'E-bankarstvo');
+CALL zatvori_zapisnik_po_uplata(30000005);
+}}}
+
 [[BR]]
 == 3. Дополнителна функција ==
 Функција што ги обработува задоцнетите неплатени казни: на неплатените постари од 8 дена им го зголемува износот за 50% (само ако сè уште не се зголемени), а на тие постари од 2 месеци им го менува статусот во 'Sudska_postapka'. Враќа порака дека постапката е успешно извршена.
 
-Прво ја прошируваме табелата `Uplata` со новиот статус и со колона за обележување дали казната е веќе зголемена:
+Статусот 'Sudska_postapka' и колоната `kazna_zgolemena` веќе се дел од најновата DDL (Фаза 2). Подолу се повторени `ALTER` наредбите поради идемпотентност (`IF NOT EXISTS` / `DROP ... IF EXISTS`), за функцијата да се изврши и врз постара верзија на базата:
 {{{#!sql
 ALTER TABLE Uplata DROP CONSTRAINT IF EXISTS uplata_status_check;