Програмирање на базата на податоци

Функции

fn_calculate_price

Оваа функција ја пресметува крајната цена на билетот во реално време преку проверка на сите активни периоди за попуст или поскапување. Таа овозможува системот да биде флексибилен со цените без да се менуваат оригиналните записи во табелата со билети.

CREATE OR REPLACE FUNCTION fn_calculate_price(p_ticket_id BIGINT) RETURNS FLOAT4 AS $$
DECLARE v_base FLOAT4; v_perc INT; v_inc BOOLEAN;
BEGIN
    SELECT base_price INTO v_base FROM "Ticket" WHERE ticket_id = p_ticket_id;
    SELECT price_change_percent, increase_decrease INTO v_perc, v_inc FROM "Event_Period" 
    WHERE event_happening_id = (SELECT event_happening_id FROM "Ticket" WHERE ticket_id = p_ticket_id)
    AND CURRENT_DATE BETWEEN start_date AND end_date LIMIT 1;
    IF v_perc IS NOT NULL THEN
        IF v_inc THEN v_base := v_base + (v_base * v_perc / 100.0);
        ELSE v_base := v_base - (v_base * v_perc / 100.0); END IF;
    END IF;
    RETURN v_base;
END; $$ LANGUAGE plpgsql;

fn_check_resource_availability

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

CREATE OR REPLACE FUNCTION fn_check_resource_availability(p_type TEXT, p_id BIGINT, p_start TIMESTAMP, p_dur INT) RETURNS BOOLEAN AS $$
BEGIN
    IF p_type = 'VENUE' THEN
        RETURN NOT EXISTS (SELECT 1 FROM "Event_Happening" WHERE venue_id = p_id AND (p_start, (p_start + (p_dur || ' minutes')::interval)) OVERLAPS (event_time, (event_time + (duration || ' minutes')::interval)));
    ELSIF p_type = 'PERFORMER' THEN
        RETURN NOT EXISTS (SELECT 1 FROM "Event_Happening_Performer" ehp JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id 
        WHERE ehp.performer_id = p_id AND (p_start, (p_start + (p_dur || ' minutes')::interval)) OVERLAPS (eh.event_time, (eh.event_time + (eh.duration || ' minutes')::interval)));
    END IF;
    RETURN TRUE;
END; $$ LANGUAGE plpgsql;

fn_validate_hierarchy_capacity

Оваа функција врши строга контрола врз хиерархиската структура на објектот (Venue -> Section -> Seat). Гарантира дека вкупниот број на седишта во секциите не ја надминува границата на салата, ниту пак бројот на реални седишта го надминува капацитетот на секцијата.

CREATE OR REPLACE FUNCTION fn_validate_hierarchy_capacity(p_parent_id BIGINT, p_new_amount INT, p_type TEXT) RETURNS BOOLEAN AS $$
DECLARE v_limit INT; v_current INT;
BEGIN
    IF p_type = 'VENUE_LIMIT' THEN
        SELECT number_of_seats INTO v_limit FROM "Venue" WHERE venue_id = p_parent_id;
        SELECT COALESCE(SUM(number_of_seats), 0) INTO v_current FROM "Section" WHERE venue_id = p_parent_id;
    ELSIF p_type = 'SECTION_LIMIT' THEN
        SELECT number_of_seats INTO v_limit FROM "Section" WHERE section_id = p_parent_id;
        SELECT COUNT(*) INTO v_current FROM "Seat" WHERE section_id = p_parent_id;
    END IF;
    RETURN (v_current + p_new_amount) <= v_limit;
END; $$ LANGUAGE plpgsql;

Процедури

proc_execute_purchase

Оваа процедура го менаџира целиот процес на продажба на билет во една сигурна трансакција. Ја пресметува точната цена, го евидентира купувачот и автоматски го заклучува билетот за да спречи негова повторна продажба.

CREATE OR REPLACE PROCEDURE proc_execute_purchase(p_uid BIGINT, p_tid BIGINT, p_qr VARCHAR) AS $$
BEGIN
    IF (SELECT is_available FROM "Ticket" WHERE ticket_id = p_tid) = FALSE THEN RAISE EXCEPTION 'Ticket is already sold'; END IF;
    INSERT INTO "Ticket_Purchase" (ticket_id, user_id, qr_code, purchase_amount) 
    VALUES (p_tid, p_uid, p_qr, fn_calculate_price(p_tid));
    UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = p_tid;
END; $$ LANGUAGE plpgsql;

proc_execute_refund

Ове е автоматизирана процедура за рефундирање која пресметува 85% поврат на средствата за корисникот. По успешното запишување на рефундацијата, таа веднаш го враќа билетот во продажба како „слободен“.

CREATE OR REPLACE PROCEDURE proc_execute_refund(p_purchase_id BIGINT) AS $$
DECLARE v_paid FLOAT4; v_tid BIGINT;
BEGIN
    SELECT purchase_amount, ticket_id INTO v_paid, v_tid FROM "Ticket_Purchase" WHERE purchase_id = p_purchase_id;
    INSERT INTO "Ticket_Refund" (purchase_id, refund_amount, refund_time) VALUES (p_purchase_id, v_paid * 0.85, CURRENT_TIMESTAMP);
    UPDATE "Ticket" SET is_available = TRUE WHERE ticket_id = v_tid;
END; $$ LANGUAGE plpgsql;

proc_create_full_event

Оваа процедура овозможува креирање на комплетен настан заедно со неговиот термин и доделен изведувач со една команда. Процедурата ја користи логиката за проверка на достапност за да гарантира дека новиот настан не се поклопува со веќе постоечките.

CREATE OR REPLACE PROCEDURE proc_create_full_event(p_name VARCHAR, p_age INT, p_ven_id BIGINT, p_perf_id BIGINT, p_time TIMESTAMP, p_dur INT) AS $$
DECLARE v_eid BIGINT; v_hid BIGINT;
BEGIN
    IF NOT fn_check_resource_availability('VENUE', p_ven_id, p_time, p_dur) THEN 
        RAISE EXCEPTION 'Venue is already booked for this time'; 
    END IF;

    INSERT INTO "Event" (name, description, min_age) 
    VALUES (p_name, 'No description provided', p_age) 
    RETURNING event_id INTO v_eid;

    INSERT INTO "Event_Happening" (event_id, venue_id, event_time, duration) 
    VALUES (v_eid, p_ven_id, p_time, p_dur) 
    RETURNING event_happening_id INTO v_hid;

    INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id) 
    VALUES (v_hid, p_perf_id);
END; $$ LANGUAGE plpgsql;

Тригери

trg_buy_age

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

CREATE OR REPLACE FUNCTION func_age_gate() RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT EXTRACT(YEAR FROM AGE(date_of_birth)) FROM "User" WHERE user_id = NEW.user_id) < 
       (SELECT e.min_age FROM "Event" e JOIN "Event_Happening" eh ON e.event_id = eh.event_id JOIN "Ticket" t ON eh.event_happening_id = t.event_happening_id WHERE t.ticket_id = NEW.ticket_id)
    THEN RAISE EXCEPTION 'Access Denied: User age is below the minimum required for this event'; END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_buy_age BEFORE INSERT ON "Ticket_Purchase" FOR EACH ROW EXECUTE FUNCTION func_age_gate();

trg_rate_check

Овој тригер обезбедува кредибилитет на рејтингот на настаните преку строга контрола на корисниците. Дозволува оставање на оценка само доколку корисникот има купено билет и истиот не е рефундиран.

CREATE OR REPLACE FUNCTION func_rating_logic() RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM "Ticket_Purchase" tp JOIN "Ticket" t ON tp.ticket_id = t.ticket_id 
       WHERE tp.user_id = NEW.user_id AND t.event_happening_id = NEW.event_happening_id 
       AND NOT EXISTS (SELECT 1 FROM "Ticket_Refund" tr WHERE tr.purchase_id = tp.purchase_id))
    THEN RAISE EXCEPTION 'You must have a valid ticket and have attended the event to rate it'; END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_rate_check BEFORE INSERT ON "Event_Happening_Rating" FOR EACH ROW EXECUTE FUNCTION func_rating_logic();

trg_period_check

Овој тригер спречува логички грешки при дефинирањето на промотивните периоди. Оневозможува поставување на датуми за попуст кои траат откако самиот настан веќе завршил.

CREATE OR REPLACE FUNCTION func_period_val() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.end_date > (SELECT event_time::DATE FROM "Event_Happening" WHERE event_happening_id = NEW.event_happening_id)
    THEN RAISE EXCEPTION 'Invalid Period: End date cannot be after the event occurrence date'; END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_period_check BEFORE INSERT OR UPDATE ON "Event_Period" FOR EACH ROW EXECUTE FUNCTION func_period_val();

trg_venue_sync

Овој тригер ја одржува синхронизацијата на капацитетите преку повикување на хиерархиската функција. Ги блокира обидите за создавање секции кои би го надминале вкупниот број на седишта планиран за целиот објект.

CREATE OR REPLACE FUNCTION func_sync_cap() RETURNS TRIGGER AS $$
BEGIN
    IF NOT fn_validate_hierarchy_capacity(NEW.venue_id, NEW.number_of_seats, 'VENUE_LIMIT') 
    THEN RAISE EXCEPTION 'Capacity Error: New section exceeds total venue seat limit'; END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_venue_sync BEFORE INSERT OR UPDATE ON "Section" FOR EACH ROW EXECUTE FUNCTION func_sync_cap();

trg_ticket_gate

Ова е тригер кој претставува бариера која спречува „overbooking“ на настаните. Оневозможува генерирање на повеќе билети од вкупниот физички капацитет на салата за секој поединечен термин.

CREATE OR REPLACE FUNCTION func_ticket_cap() RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM "Ticket" WHERE event_happening_id = NEW.event_happening_id) >= 
       (SELECT number_of_seats FROM "Venue" v JOIN "Event_Happening" eh ON v.venue_id = eh.venue_id WHERE eh.event_happening_id = NEW.event_happening_id)
    THEN RAISE EXCEPTION 'Sold Out: Number of tickets cannot exceed the venue seat capacity'; END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_ticket_gate BEFORE INSERT ON "Ticket" FOR EACH ROW EXECUTE FUNCTION func_ticket_cap();

Тестирање на proc_execute_purchase, fn_calculate_price, proc_execute_refund, trg_buy_age, trg_ticket_gate

-- 1. make purchase
CALL proc_execute_purchase(1, 100, 'QR-TEST-CODE-001');

-- 2. check if ticket is not available (is_available should be false)
SELECT is_available FROM "Ticket" WHERE ticket_id = 100;

-- 3. check if price is calculated correctly in Ticket_Purchase
SELECT * FROM "Ticket_Purchase" WHERE ticket_id = 100;

-- 4. make refund to the purchase (purchase_id from before)
CALL proc_execute_refund(16000001);

-- 5. check if the ticket is available (is_available should be true)
SELECT is_available FROM "Ticket" WHERE ticket_id = 100;

-- 6. check if there is a corresponding record in Ticket_Refund
SELECT * FROM "Ticket_Refund" WHERE purchase_id = 16000001;

Тестирање на trg_buy_age, func_age_gate, proc_execute_purchase

-- get the user_id of a user who is under 18
SELECT user_id FROM "User" WHERE date_of_birth > '2010-01-01' LIMIT 1;  -- 1081

-- get the event_id of an event whose min_age is 18
SELECT event_id FROM "Event" WHERE min_age = 18 LIMIT 1;    -- 5

-- try to make the user under 18 buy a ticket for a happening of the event whose min entrance age is 18
CALL proc_execute_purchase(1081, 5, 'QR-FAIL');