Changes between Version 1 and Version 2 of DatabaseProgramming

Timestamp:

05/26/26 23:48:39 (15 hours ago)

Author:

231088

Comment:

--

DatabaseProgramming

@@ +1 @@
+= Функции, Процедури и Тригери =
+Во оваа фаза беа имплементирани функции, процедури и тригери кои ја поддржуваат основната бизнис логика на системот за booking на артисти и бендови. Секоја компонента е одговорна за одредена операција — од пресметување статистики, преку управување со bookings, до автоматска заштита од грешки.
+
 == Функции ==
-
-=== '''fn_artist_average_rating(p_bookable_id INT)''' ===
-Оваа функција ја пресметува просечната оцена за даден артист/бенд (bookable) така што ги зема сите рецензии (''Review'') за неговите букинзи и ја враќа средната вредност заокружена на две децимали.
-
-Со тоа се имплементира бизнис логиката за прикажување на рејтинг на артистите во апликацијата и за статистика/сортирање по оцена.
-
-=== '''fn_artist_total_earnings(p_bookable_id INT)''' ===
-Функцијата ја собира сумата на сите плаќања со статус ''PAID'' за сите букинзи поврзани со даден артист или бенд.
-
-Така се имплементира бизнис логиката за пресметка на вкупната заработка на артистот, која може да се прикаже во неговиот профил или во администраторскиот дел.
-
-=== '''fn_check_artist_availability(p_bookable_id INT, p_date DATE)''' ===
-Оваа функција проверува дали артистот има барем еден запис во ''AvailabilitySlot'' со статус ''AVAILABLE'' на дадениот датум и враќа TRUE/FALSE.
-
-Со тоа се имплементира логиката за проверка на достапност при креирање понуда или букинг – апликацијата прво проверува дали артистот е слободен на конкретниот датум.
-
-=== '''fn_total_client_bookings(p_client_id INT)''' ===
-Функцијата го брои бројот на букинзи за даден клиент преку поврзување на ''BookingRequest'', ''Offer'' и ''Booking'' и враќа цел број.
-
-Ова ја покрива бизнис логиката за статистика за клиентот (на пример колку настани има букирано), што може да се користи во неговиот профил или за аналитика.
+Функциите се користат за пресметување и враќање на вредности кои се потребни на повеќе места во апликацијата. Секоја функција прима параметри, извршува логика врз базата и враќа резултат.
+
+=== 1. fn_artist_average_rating ===
+Функцијата {{{fn_artist_average_rating}}} го пресметува просечниот рејтинг на артист или бенд врз основа на сите reviews оставени за нивните завршени bookings.
+
+Ги поврзува табелите {{{Review}}}, {{{Booking}}} и {{{Offer}}} за да го пронајде просекот на рејтинзите за конкретниот артист. Ако артистот сè уште нема reviews, функцијата враќа {{{0}}} наместо {{{NULL}}} преку употреба на {{{COALESCE}}}.
+{{{
+CREATE OR REPLACE FUNCTION fn_artist_average_rating(
+    p_bookable_id INT
+)
+RETURNS NUMERIC(3,2)
+AS
+$$
+DECLARE
+    v_average_rating NUMERIC(3,2);
+BEGIN
+
+    SELECT
+        ROUND(AVG(r.rating), 2)
+    INTO v_average_rating
+    FROM Review r
+    JOIN Booking bk
+        ON r.booking_id = bk.booking_id
+    JOIN Offer o
+        ON bk.offer_id = o.offer_id
+    WHERE o.bookable_id = p_bookable_id;
+
+    RETURN COALESCE(v_average_rating, 0);
+
+END;
+$$
+LANGUAGE plpgsql;
+}}}
+
+=== 2. fn_artist_total_earnings ===
+Функцијата {{{fn_artist_total_earnings}}} ја пресметува вкупната заработка на артист или бенд земајќи ги предвид само успешно завршените плаќања со статус {{{PAID}}}.
+
+Ги поврзува табелите {{{Payment}}}, {{{Booking}}} и {{{Offer}}} и го сумира износот на сите потврдени плаќања. Ако артистот сè уште нема реализирани плаќања, функцијата враќа {{{0}}} наместо {{{NULL}}}.
+{{{
+CREATE OR REPLACE FUNCTION fn_artist_total_earnings(
+    p_bookable_id INT
+)
+RETURNS NUMERIC(10,2)
+AS
+$$
+DECLARE
+    v_total NUMERIC(10,2);
+BEGIN
+
+    SELECT
+        SUM(p.amount)
+    INTO v_total
+    FROM Payment p
+    JOIN Booking bk
+        ON p.booking_id = bk.booking_id
+    JOIN Offer o
+        ON bk.offer_id = o.offer_id
+    WHERE o.bookable_id = p_bookable_id
+      AND p.payment_status = 'PAID';
+
+    RETURN COALESCE(v_total, 0);
+
+END;
+$$
+LANGUAGE plpgsql;
+}}}
+
+=== 3. fn_check_artist_availability ===
+Функцијата {{{fn_check_artist_availability}}} проверува дали артист или бенд има слободен термин на одреден датум и враќа {{{TRUE}}} или {{{FALSE}}}.
+
+Наместо {{{COUNT(*)}}} кој ги брои сите редици, функцијата користи {{{EXISTS}}} кој застанува веднаш при првиот пронајден слободен слот — со што е значително поефикасна.
+{{{
+CREATE OR REPLACE FUNCTION fn_check_artist_availability(
+    p_bookable_id INT,
+    p_date        DATE
+)
+RETURNS BOOLEAN
+AS
+$$
+DECLARE
+    v_exists BOOLEAN;
+BEGIN
+
+    SELECT EXISTS(
+        SELECT 1
+        FROM AvailabilitySlot
+        WHERE bookable_id = p_bookable_id
+          AND DATE(start_datetime) = p_date
+          AND status = 'AVAILABLE'
+    )
+    INTO v_exists;
+
+    RETURN v_exists;
+
+END;
+$$
+LANGUAGE plpgsql;
+}}}
+
+=== 4. fn_total_client_bookings ===
+Функцијата {{{fn_total_client_bookings}}} го враќа вкупниот број на bookings направени од одреден клиент.
+
+Ги поврзува табелите {{{BookingRequest}}}, {{{Offer}}} и {{{Booking}}} за да го пресмета бројот на сите bookings поврзани со клиентот, без разлика на нивниот статус.
+{{{
+CREATE OR REPLACE FUNCTION fn_total_client_bookings(
+    p_client_id INT
+)
+RETURNS INT
+AS
+$$
+DECLARE
+    v_total INT;
+BEGIN
+
+    SELECT COUNT(*)
+    INTO v_total
+    FROM BookingRequest br
+    JOIN Offer o
+        ON br.request_id = o.request_id
+    JOIN Booking bk
+        ON bk.offer_id = o.offer_id
+    WHERE br.client_id = p_client_id;
+
+    RETURN v_total;
+
+END;
+$$
+LANGUAGE plpgsql;
+}}}
 
 == Процедури ==
-
-=== '''sp_create_booking_request(p_client_id, p_duration_id, p_event_type, p_event_date, p_location_id)''' ===
-Процедурата креира нов запис во ''BookingRequest'' со основните информации за настанот (клиент, траење, тип, датум, локација).
-
-На тој начин ја инкапсулира бизнис операцијата „клиентот испраќа барање за букинг“ и осигурува дека секогаш се пополнуваат потребните полиња на централизирано место.
-
-=== '''sp_cancel_booking(p_booking_id INT)''' ===
-Оваа процедура го ажурира полето ''booking_status'' на избраниот букинг во вредност ''CANCELLED''.
-
-На тој начин се имплементира логиката за откажување на букинг од страна на клиент или администратор, а преку тригерот за историја автоматски се логира и промената на статусот.
-
-=== '''sp_create_payment(p_booking_id INT, p_amount NUMERIC)''' ===
-Процедурата додава нов запис во ''Payment'' за одреден букинг, со зададена сума и статус ''PAID''.
-
-Така се имплементира делот од бизнис логиката за евиденција на успешно извршени плаќања, што потоа се користи и во функцијата за вкупна заработка на артистите.
+Процедурите се користат за извршување на акции врз базата — за разлика од функциите, тие не враќаат вредност туку вршат промени. Секоја процедура содржи валидација пред да изврши каква било промена, со цел да се спречат невалидни состојби во базата.
+
+=== 1. sp_create_booking_request ===
+Процедурата {{{sp_create_booking_request}}} креира нов барање за booking од страна на клиент.
+
+Пред да го изврши вметнувањето, процедурата врши три валидации:
+ * Проверува дали клиентот постои во табелата {{{ClientProfile}}}
+ * Проверува дали локацијата постои во табелата {{{Location}}}
+ * Проверува дали датумот на настанот е во иднина
+
+Ако некоја од валидациите не помине, се фрла исклучок со јасна порака за грешка.
+{{{
+CREATE OR REPLACE PROCEDURE sp_create_booking_request(
+    p_client_id   INT,
+    p_duration_id INT,
+    p_event_type  VARCHAR,
+    p_event_date  DATE,
+    p_location_id INT
+)
+LANGUAGE plpgsql
+AS
+$$
+BEGIN
+
+    IF NOT EXISTS (
+        SELECT 1 FROM ClientProfile WHERE client_id = p_client_id
+    ) THEN
+        RAISE EXCEPTION 'Client with ID % does not exist!', p_client_id;
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1 FROM Location WHERE location_id = p_location_id
+    ) THEN
+        RAISE EXCEPTION 'Location with ID % does not exist!', p_location_id;
+    END IF;
+
+    IF p_event_date <= CURRENT_DATE THEN
+        RAISE EXCEPTION 'Event date must be in the future!';
+    END IF;
+
+    INSERT INTO BookingRequest(
+        client_id,
+        duration_id,
+        event_type,
+        event_date,
+        location_id
+    )
+    VALUES (
+        p_client_id,
+        p_duration_id,
+        p_event_type,
+        p_event_date,
+        p_location_id
+    );
+
+END;
+$$;
+}}}
+
+=== 2. sp_cancel_booking ===
+Процедурата {{{sp_cancel_booking}}} го откажува постоечки booking со менување на неговиот статус во {{{CANCELLED}}}.
+
+Пред да го изврши откажувањето, процедурата врши три проверки:
+ * Проверува дали booking со дадениот ID воопшто постои
+ * Проверува дали booking не е веќе откажан
+ * Проверува дали booking не е веќе завршен — завршените bookings не можат да се откажат
+
+Без овие проверки, повикот со непостоечки ID би поминал тивко без никаква промена или грешка.
+{{{
+CREATE OR REPLACE PROCEDURE sp_cancel_booking(
+    p_booking_id INT
+)
+LANGUAGE plpgsql
+AS
+$$
+DECLARE
+    v_current_status VARCHAR(30);
+BEGIN
+
+    SELECT booking_status
+    INTO v_current_status
+    FROM Booking
+    WHERE booking_id = p_booking_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Booking with ID % does not exist!', p_booking_id;
+    END IF;
+
+    IF v_current_status = 'CANCELLED' THEN
+        RAISE EXCEPTION 'Booking % is already cancelled!', p_booking_id;
+    END IF;
+
+    IF v_current_status = 'COMPLETED' THEN
+        RAISE EXCEPTION 'Booking % is already completed and cannot be cancelled!', p_booking_id;
+    END IF;
+
+    UPDATE Booking
+    SET booking_status = 'CANCELLED'
+    WHERE booking_id = p_booking_id;
+
+END;
+$$;
+}}}
+
+=== 3. sp_create_payment ===
+Процедурата {{{sp_create_payment}}} креира ново плаќање поврзано со одреден booking.
+
+Клучна поправка во однос на оригиналната верзија е дека плаќањето се креира со статус {{{PENDING}}} наместо веднаш {{{PAID}}}. Ова одговара на реалниот тек — плаќањето прво се иницира, па дури потоа се потврдува.
+
+Процедурата врши три валидации:
+ * Проверува дали booking постои
+ * Проверува дали износот е позитивен број
+ * Проверува дали веќе не постои плаќање за истиот booking
+{{{
+CREATE OR REPLACE PROCEDURE sp_create_payment(
+    p_booking_id INT,
+    p_amount     NUMERIC(10,2)
+)
+LANGUAGE plpgsql
+AS
+$$
+BEGIN
+
+    IF NOT EXISTS (
+        SELECT 1 FROM Booking WHERE booking_id = p_booking_id
+    ) THEN
+        RAISE EXCEPTION 'Booking with ID % does not exist!', p_booking_id;
+    END IF;
+
+    IF p_amount <= 0 THEN
+        RAISE EXCEPTION 'Payment amount must be greater than 0!';
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM Payment WHERE booking_id = p_booking_id
+    ) THEN
+        RAISE EXCEPTION 'Payment for booking % already exists!', p_booking_id;
+    END IF;
+
+    INSERT INTO Payment(
+        booking_id,
+        amount,
+        payment_status
+    )
+    VALUES (
+        p_booking_id,
+        p_amount,
+        'PENDING'
+    );
+
+END;
+$$;
+}}}
 
 == Тригери ==
-
-=== '''fn_update_slot_status() / trg_update_slot_status''' ===
-Овој тригер се активира по вметнување (AFTER INSERT) на нов букинг во ''Booking'' и преку функцијата ''fn_update_slot_status'' ги ажурира сите ''AvailabilitySlot'' записи за соодветниот артист на датумот на настанот во статус ''BOOKED''.
-
-Со тоа се имплементира бизнис правилото дека кога ќе се потврди букинг за одреден датум, слободните термини за тој датум повеќе не се достапни во календарот на артистот.
-
-=== '''fn_prevent_double_booking() / trg_prevent_double_booking''' ===
-Тригерот се активира пред вметнување (BEFORE INSERT) на нов букинг и во функцијата ''fn_prevent_double_booking'' проверува дали веќе постои букинг со статус ''CONFIRMED'' за истиот артист (bookable_id) и истиот датум на настан (event_date).
-
-Ако постои, се крева грешка „Artist already booked on that date!“ и се спречува внесот, со што директно се имплементира бизнис правилото дека артист може да има најмногу еден потврден настап во еден ден.
-
-=== '''fn_booking_status_history() / trg_booking_status_history''' ===
-Овој тригер се активира по ажурирање (AFTER UPDATE) на ''Booking'' кога статусот се менува и преку функцијата ''fn_booking_status_history'' внесува ред во ''BookingStatusHistory'' со новиот статус и времето на промена.
-
-Така се имплементира бизнис логиката за водење историја (audit trail) на статусите на букинзите, што е корисно за следење на целиот животен циклус на букинг (''CREATED → CONFIRMED → COMPLETED/CANCELLED'').
+Тригерите се специјални функции кои се извршуваат автоматски при одредени настани во базата — без потреба апликацијата да ги повикува рачно. Во системот се имплементирани три тригери кои обезбедуваат конзистентност и интегритет на податоците.
+
+=== 1. trg_prevent_double_booking ===
+Тригерот {{{trg_prevent_double_booking}}} спречува двоен booking — ситуација каде ист артист или бенд би бил потврден за два различни настани на ист датум.
+
+Се извршува автоматски '''пред''' секој нов запис во табелата {{{Booking}}} ({{{BEFORE INSERT}}}). Доколку за истиот артист веќе постои {{{CONFIRMED}}} booking на истиот датум, тригерот фрла исклучок и го спречува вметнувањето.
+{{{
+CREATE OR REPLACE FUNCTION fn_prevent_double_booking()
+RETURNS TRIGGER
+AS
+$$
+DECLARE
+    v_count      INT;
+    v_bookable   INT;
+    v_event_date DATE;
+BEGIN
+
+    SELECT o.bookable_id, br.event_date
+    INTO v_bookable, v_event_date
+    FROM Offer o
+    JOIN BookingRequest br ON br.request_id = o.request_id
+    WHERE o.offer_id = NEW.offer_id;
+
+    SELECT COUNT(*)
+    INTO v_count
+    FROM Booking b
+    JOIN Offer o
+        ON b.offer_id = o.offer_id
+    JOIN BookingRequest br
+        ON br.request_id = o.request_id
+    WHERE o.bookable_id = v_bookable
+      AND br.event_date = v_event_date
+      AND b.booking_status = 'CONFIRMED';
+
+    IF v_count > 0 THEN
+        RAISE EXCEPTION 'Artist/Band with ID % is already booked on %!',
+            v_bookable, v_event_date;
+    END IF;
+
+    RETURN NEW;
+
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER trg_prevent_double_booking
+BEFORE INSERT ON Booking
+FOR EACH ROW
+EXECUTE FUNCTION fn_prevent_double_booking();
+}}}
+
+=== 2. trg_update_slot_status ===
+Тригерот {{{trg_update_slot_status}}} автоматски го означува слободниот термин на артистот како {{{BOOKED}}} во моментот кога се креира нов booking.
+
+Се извршува автоматски '''по''' секој нов запис во табелата {{{Booking}}} ({{{AFTER INSERT}}}). Го пронаоѓа соодветниот {{{AvailabilitySlot}}} според артистот и датумот на настанот и го менува неговиот статус — со тоа се спречува двојна резервација на ист термин.
+{{{
+CREATE OR REPLACE FUNCTION fn_update_slot_status()
+RETURNS TRIGGER
+AS
+$$
+DECLARE
+    v_bookable   INT;
+    v_event_date DATE;
+BEGIN
+
+    SELECT o.bookable_id, br.event_date
+    INTO v_bookable, v_event_date
+    FROM Offer o
+    JOIN BookingRequest br ON br.request_id = o.request_id
+    WHERE o.offer_id = NEW.offer_id;
+
+    UPDATE AvailabilitySlot
+    SET status = 'BOOKED'
+    WHERE bookable_id = v_bookable
+      AND DATE(start_datetime) = v_event_date
+      AND status = 'AVAILABLE';
+
+    RETURN NEW;
+
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER trg_update_slot_status
+AFTER INSERT ON Booking
+FOR EACH ROW
+EXECUTE FUNCTION fn_update_slot_status();
+}}}
+
+=== 3. trg_booking_status_history ===
+Тригерот {{{trg_booking_status_history}}} автоматски го запишува секој пат кога статусот на booking се менува, формирајќи целосен историјат на промени.
+
+Се извршува автоматски '''по''' секој {{{UPDATE}}} на табелата {{{Booking}}}, но само кога вредноста на {{{booking_status}}} навистина се променила — ова се постигнува со условот {{{WHEN (OLD.booking_status IS DISTINCT FROM NEW.booking_status)}}}. Со тоа се избегнува непотребно запишување при update-и кои не го менуваат статусот.
+{{{
+CREATE OR REPLACE FUNCTION fn_booking_status_history()
+RETURNS TRIGGER
+AS
+$$
+BEGIN
+
+    INSERT INTO BookingStatusHistory(
+        booking_id,
+        new_status,
+        changed_at
+    )
+    VALUES (
+        NEW.booking_id,
+        NEW.booking_status,
+        NOW()
+    );
+
+    RETURN NEW;
+
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER trg_booking_status_history
+AFTER UPDATE ON Booking
+FOR EACH ROW
+WHEN (OLD.booking_status IS DISTINCT FROM NEW.booking_status)
+EXECUTE FUNCTION fn_booking_status_history();
+}}}