wiki:DatabaseProgramming

Context Navigation

Version 2 (modified by 231088, 15 hours ago) ( diff )
--

Функции, Процедури и Тригери

Во оваа фаза беа имплементирани функции, процедури и тригери кои ја поддржуваат основната бизнис логика на системот за booking на артисти и бендови. Секоја компонента е одговорна за одредена операција — од пресметување статистики, преку управување со bookings, до автоматска заштита од грешки.

Функции

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

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;

Процедури

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

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

Тригери

Тригерите се специјални функции кои се извршуваат автоматски при одредени настани во базата — без потреба апликацијата да ги повикува рачно. Во системот се имплементирани три тригери кои обезбедуваат конзистентност и интегритет на податоците.

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

Attachments (1)

faza4.sql (5.0 KB ) - added by 231088 14 hours ago.

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text