Context Navigation

Changes between Version 17 and Version 18 of DatabaseProgramming

Timestamp:: 05/09/26 20:55:16 (2 weeks ago)
Author:: 231027
Comment:: --

Legend:

: Unmodified
: Added
: Removed
: Modified

DatabaseProgramming

-              v17
+              v18
 == Функции
-=== `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` и `func_age_gate`
-Овој тригер се активира при секој обид за купување билет за да ја заштити старосната граница на настанот. Спречува малолетни лица да купуваат билети за настани за кои не ја исполнуваат минималната возраст пропишана од организаторот.
-{{{
-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` и `func_rating_logic`
-Овој тригер обезбедува кредибилитет на рејтингот на настаните преку строга контрола на корисниците. Дозволува оставање на оценка само доколку корисникот има купено билет и истиот не е рефундиран.
-{{{
-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` и `func_period_val`
-Овој тригер спречува логички грешки при дефинирањето на промотивните периоди. Оневозможува поставување на датуми за попуст кои траат откако самиот настан веќе завршил.
-{{{
-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` и `func_sync_cap`
-Овој тригер ја одржува синхронизацијата на капацитетите преку повикување на хиерархиската функција. Ги блокира обидите за создавање секции кои би го надминале вкупниот број на седишта планиран за целиот објект.
-{{{
-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` и `func_ticket_cap`
-Ова е тригер кој претставува бариера која спречува „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`, `func_ticket_cap`
-{{{
--- make purchase
-CALL proc_execute_purchase(1, 100, 'QR-TEST-CODE-001');
--- check if ticket is not available (is_available should be false)
-SELECT is_available FROM "Ticket" WHERE ticket_id = 100;
--- check if price is calculated correctly in Ticket_Purchase
-SELECT * FROM "Ticket_Purchase" WHERE ticket_id = 100;
--- make refund to the purchase (purchase_id from before)
-CALL proc_execute_refund(16000001);
--- check if the ticket is available (is_available should be true)
-SELECT is_available FROM "Ticket" WHERE ticket_id = 100;
--- 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');
-}}}
-== Тестирање на `fn_check_resource_availability`, `proc_create_full_event`
-{{{
--- reset system counter
-SELECT setval(pg_get_serial_sequence('"Event"', 'event_id'), COALESCE(MAX(event_id), 1)) FROM "Event";
--- -- create an event happening from 20:00 to 22:00 in the venue with venue_id 1
-CALL proc_create_full_event('Test Concert 1', 12, 1, 1, '2026-05-20 20:00:00', 120);
--- -- create another event happening from 21:00 to 23:00 in the same venue
-CALL proc_create_full_event('Test Concert 2', 12, 1, 2, '2026-05-20 21:00:00', 120);
-}}}
-== Тестирање на `trg_venue_sync`, `func_sync_cap`, `fn_validate_hierarchy_capacity`
-{{{
--- select a venue (we need the number of seats)
-SELECT * FROM "Venue" LIMIT 1;  -- 1793
--- try to insert a new seat in a section in that venue
-INSERT INTO "Section" (venue_id, name, number_of_seats) VALUES (1, 'TSC', 1);
-}}}
-== Тестирање на `trg_rate_check`, `func_rating_logic`
-{{{
-INSERT INTO "Event_Happening_Rating" (user_id, event_happening_id, rating, comment)
-VALUES (1, 1, 5, 'Test rating');
-}}}
-== Тестирање на `trg_period_check`, `func_period_val`
-{{{
--- get the time of an event happening
-SELECT event_time FROM "Event_Happening" WHERE event_happening_id = 1;  -- 2022-04-29 03:46:32.296118
--- try to add an event period that is after the event happening
-INSERT INTO "Event_Period" (event_happening_id, start_date, end_date, price_change_percent, increase_decrease)
-VALUES (1, '2026-05-10', '2026-05-21', 10, TRUE);
-}}}