Changes between Version 27 and Version 28 of DatabaseProgramming


Ignore:
Timestamp:
06/29/26 21:48:21 (6 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v27 v28  
    11= Програмирање на базата на податоци
    22
     3
    34== Функции
    45
    56=== `get_current_price`
    67
    7 Оваа функција ја пресметува моменталната цена на билетот земајќи ги предвид активните промотивни периоди или поскапувања во реално време. Таа проверува дали за денешниот датум постои дефиниран период за промена на цената и соодветно ја зголемува или намалува основната цена.
     8Оваа функција ја пресметува моменталната цена на билетот со примена на активниот процент на попуст дефиниран за тековниот временски период. Таа проверува дали денешниот датум се наоѓа во некој од опсезите на `Event_Period` и линеарно ја намалува основната цена на билетот.
    89
    910{{{
    1011
    1112CREATE OR REPLACE FUNCTION get_current_price(p_ticket_id BIGINT)
    12 RETURNS FLOAT4 AS $$
    13 DECLARE
    14     v_base_price FLOAT4;
     13RETURNS DECIMAL(10,2)
     14LANGUAGE plpgsql
     15AS $$
     16DECLARE
     17    v_base_price DECIMAL(10,2);
    1518    v_happening_id BIGINT;
    16     v_percent INT;
    17     v_increase BOOLEAN;
    18     v_final_price FLOAT4;
     19    v_discount_percent INT;
     20    v_final_price DECIMAL(10,2);
    1921BEGIN
    2022    SELECT base_price, event_happening_id INTO v_base_price, v_happening_id
    2123    FROM "Ticket" WHERE ticket_id = p_ticket_id;
    2224
    23     SELECT price_change_percent, increase_decrease INTO v_percent, v_increase
     25    SELECT price_discount_percent INTO v_discount_percent
    2426    FROM "Event_Period"
    2527    WHERE event_happening_id = v_happening_id
    … …  
    2830
    2931    IF FOUND THEN
    30         IF v_increase THEN
    31            v_final_price := v_base_price * (1 + (v_percent / 100.0));
    32         ELSE
    33            v_final_price := v_base_price * (1 - (v_percent / 100.0));
    34         END IF;
     32        v_final_price := v_base_price * (1 - (v_discount_percent / 100.0));
    3533    ELSE
    3634        v_final_price := v_base_price;
    … …  
    3937    RETURN v_final_price;
    4038END;
    41 $$ LANGUAGE plpgsql;
    42 
    43 }}}
     39$$;
     40
     41}}}
     42
    4443
    4544=== `calculate_refund_amount`
    4645
    47 Оваа функција ја пресметува сумата за рефундација со примена на 15% задршка од оригиналната уплата. Таа го идентификува плаќањето преку неговиот уникатен ID и враќа 85% од износот како финален износ за поврат на средства.
    48 
    49 {{{
    50 
    51 CREATE OR REPLACE FUNCTION calculate_refund_amount(p_purchase_id BIGINT)
    52 RETURNS FLOAT4 AS $$
    53 DECLARE
    54     v_original_price FLOAT4;
    55     v_refund_amount FLOAT4;
    56 BEGIN
    57     SELECT purchase_amount INTO v_original_price
    58     FROM "Ticket_Purchase"
    59     WHERE purchase_id = p_purchase_id;
     46Оваа функција ја калкулира сумата за поврат на средства при откажување на конкретна ставка од нарачката со задржување на 15% административна такса. Таа го идентификува билетот преку неговиот уникатен ID во трансакцискиот дел и враќа чисто 85% од оригинално платената цена.
     47
     48{{{
     49
     50CREATE OR REPLACE FUNCTION calculate_refund_amount(p_order_item_id BIGINT)
     51RETURNS DECIMAL(10,2)
     52LANGUAGE plpgsql
     53AS $$
     54DECLARE
     55    v_original_price DECIMAL(10,2);
     56    v_refund_amount DECIMAL(10,2);
     57BEGIN
     58    SELECT item_price INTO v_original_price
     59    FROM "Ticket_Order_Item"
     60    WHERE order_item_id = p_order_item_id;
    6061
    6162    IF NOT FOUND THEN
    62         RAISE EXCEPTION 'Purchase record not found.';
     63        RAISE EXCEPTION 'Order item record not found.';
    6364    END IF;
    6465
    … …  
    6768    RETURN v_refund_amount;
    6869END;
    69 $$ LANGUAGE plpgsql;
    70 
    71 }}}
     70$$;
     71
     72}}}
     73
    7274
    7375== Процедури
    … …  
    7577=== `buy_ticket`
    7678
    77 Оваа процедура го извршува целиот процес на купување билет, вклучувајќи пресметка на цена, генерирање на уникатен QR код и креирање на запис за трансакцијата. По успешното купување, процедурата автоматски го менува статусот на билетот во недостапен за да се спречи двојна продажба.
     79Оваа процедура го автоматизира процесот на купување билет преку симултано генерирање на главна нарачка и поединечна ставка со уникатен QR-код. По успешното запишување на трансакцијата, процедурата инстантни го менува статусот на билетот во недостапен за да спречи паралелна продажба на истото седиште.
    7880
    7981{{{
    … …  
    8385    p_ticket_id BIGINT
    8486)
    85 AS $$
    86 DECLARE
    87     v_price FLOAT4;
     87LANGUAGE plpgsql
     88AS $$
     89DECLARE
     90    v_price DECIMAL(10,2);
    8891    v_qr_code TEXT;
     92    v_order_id BIGINT;
    8993BEGIN
    9094    v_price := get_current_price(p_ticket_id);
    9195    v_qr_code := 'QR-' || p_ticket_id || '-' || p_user_id || '-' || EXTRACT(EPOCH FROM NOW());
    9296
    93     INSERT INTO "Ticket_Purchase" (ticket_id, user_id, qr_code, purchase_amount)
    94     VALUES (p_ticket_id, p_user_id, v_qr_code, v_price);
     97    INSERT INTO "Ticket_Order" (user_id, order_amount)
     98    VALUES (p_user_id, v_price)
     99    RETURNING order_id INTO v_order_id;
     100
     101    INSERT INTO "Ticket_Order_Item" (order_id, ticket_id, qr_code, item_price)
     102    VALUES (v_order_id, p_ticket_id, v_qr_code, v_price);
    95103
    96104    UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = p_ticket_id;
    97    
     105
    98106    COMMIT;
    99107END;
    100 $$ LANGUAGE plpgsql;
    101 
    102 }}}
     108$$;
     109
     110}}}
     111
    103112
    104113=== `cancel_ticket`
    105114
    106 Оваа процедура го менаџира процесот на откажување на купен билет и иницирање рефундација на средствата. Таа автоматски ја пресметува сумата за поврат (со вклучени пенали), го евидентира откажувањето во базата и го ослободува седиштето за нов купувач.
     115Оваа процедура менаџира делумно или целосно откажување на купени ставки преку евидентирање на рефундацијата и поврат на средствата со пресметани пенали. По завршување на финансискиот запис во релационите табели, таа автоматски го враќа билетот во статус на достапен за повторна продажба на пазарот.
    107116
    108117{{{
    109118
    110119CREATE OR REPLACE PROCEDURE cancel_ticket(
    111     p_purchase_id BIGINT,
     120    p_order_item_id BIGINT,
    112121    p_reason TEXT
    113122)
    114 AS $$
    115 DECLARE
    116     v_refund_amount FLOAT4;
     123LANGUAGE plpgsql
     124AS $$
     125DECLARE
     126    v_refund_amount DECIMAL(10,2);
    117127    v_ticket_id BIGINT;
    118 BEGIN
    119     v_refund_amount := calculate_refund_amount(p_purchase_id);
    120 
    121     SELECT ticket_id INTO v_ticket_id
    122     FROM "Ticket_Purchase"
    123     WHERE purchase_id = p_purchase_id;
    124 
    125     INSERT INTO "Ticket_Refund" (purchase_id, refund_amount, reason)
    126     VALUES (p_purchase_id, v_refund_amount, p_reason);
     128    v_order_id BIGINT;
     129    v_refund_id BIGINT;
     130BEGIN
     131    v_refund_amount := calculate_refund_amount(p_order_item_id);
     132
     133    SELECT order_id, ticket_id INTO v_order_id, v_ticket_id
     134    FROM "Ticket_Order_Item"
     135    WHERE order_item_id = p_order_item_id;
     136
     137    INSERT INTO "Ticket_Refund" (order_id, refund_amount, reasons)
     138    VALUES (v_order_id, v_refund_amount, p_reason)
     139    RETURNING refund_id INTO v_refund_id;
     140
     141    INSERT INTO "Ticket_Refund_Item" (refund_id, order_item_id, item_price)
     142    VALUES (v_refund_id, p_order_item_id, v_refund_amount);
    127143
    128144    UPDATE "Ticket" SET is_available = TRUE WHERE ticket_id = v_ticket_id;
    129145
    130     RAISE NOTICE 'Ticket cancelled. Refund amount: %', v_refund_amount;
    131 END;
    132 $$ LANGUAGE plpgsql;
    133 
    134 }}}
     146    RAISE NOTICE 'Ticket item cancelled successfully. Refund amount: %', v_refund_amount;
     147END;
     148$$;
     149
     150}}}
     151
    135152
    136153=== `schedule_new_happening`
    137154
    138 Оваа процедура се користи за автоматизирано закажување на нов настан и генерирање на инвентар од билети за сите достапни седишта во објектот. Со еден повик, таа го креира настанот и веднаш ги пополнува соодветните табели со достапни билети за секоја секција во салата.
     155Оваа процедура овозможува авторизиран администратор да закаже нов термин за настан и автоматски да го генерира почетниот инвентар на билети. Системот динамички ги презема сите достапни седишта од дефинираната сала и ги мапира како слободни влезници со почетна базна цена.
    139156
    140157{{{
    141158
    142159CREATE OR REPLACE PROCEDURE schedule_new_happening(
     160    p_admin_id BIGINT,
    143161    p_event_id BIGINT,
    144162    p_venue_id BIGINT,
    145163    p_time TIMESTAMP,
    146     p_duration INT,
    147     p_base_price FLOAT4
     164    p_duration_minutes INT,
     165    p_base_price DECIMAL(10,2)
    148166)
     167LANGUAGE plpgsql
    149168AS $$
    150169DECLARE
    151170    v_happening_id BIGINT;
    152171BEGIN
    153     INSERT INTO "Event_Happening" (event_id, event_time, venue_id, duration)
    154     VALUES (p_event_id, p_time, p_venue_id, p_duration)
     172    IF NOT EXISTS (
     173        SELECT 1 FROM "Admin" WHERE user_id = p_admin_id
     174    ) THEN
     175        RAISE EXCEPTION 'Access Denied: Only administrators can schedule a new event happening.';
     176    END IF;
     177
     178    INSERT INTO "Event_Happening" (event_id, event_time, venue_id, duration_minutes, organizers)
     179    VALUES (p_event_id, p_time, p_venue_id, p_duration_minutes, 'System Generated Organizer')
    155180    RETURNING event_happening_id INTO v_happening_id;
    156181
    157     INSERT INTO "Ticket" (ticket_type, base_price, is_available, event_happening_id, seat_id)
     182    INSERT INTO "Ticket" (base_price, is_available, event_happening_id, seat_id)
    158183    SELECT
    159         'Standard',
    160184        p_base_price,
    161185        TRUE,
    … …  
    166190    WHERE sec.venue_id = p_venue_id;
    167191
    168     RAISE NOTICE 'Event scheduled and tickets generated.';
    169 END;
    170 $$ LANGUAGE plpgsql;
    171 
    172 }}}
     192    RAISE NOTICE 'Event happening successfully scheduled by admin % and tickets generated.', p_admin_id;
     193END;
     194$$;
     195
     196}}}
     197
    173198
    174199=== `create_rating`
    175200
    176 Оваа процедура овозможува внесување оценка само доколку корисникот поседува валиден и нерефундиран билет за настанот. Со ова се спречува лажно оценување и се гарантира веродостојност на повратните информации.
     201Оваа процедура ја гарантира веродостојноста на рецензиите преку строга проверка на историјата на нарачки на корисникот. Спуштањето на оцена и коментар е дозволено исклучиво доколку корисникот реално поседува валиден, купен и воедно нерефундиран билет за конкретниот термин на настанот.
    177202
    178203{{{
    … …  
    184209    p_comment TEXT
    185210)
     211LANGUAGE plpgsql
    186212AS $$
    187213BEGIN
    188214    IF NOT EXISTS (
    189         SELECT 1
    190         FROM "Ticket_Purchase" tp
    191         JOIN "Ticket" t ON tp.ticket_id = t.ticket_id
    192         LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id
    193         WHERE tp.user_id = p_user_id
     215        SELECT 1
     216        FROM "Ticket_Order" o
     217        JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id
     218        JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
     219        LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
     220        WHERE o.user_id = p_user_id
    194221          AND t.event_happening_id = p_event_happening_id
    195           AND tr.refund_id IS NULL
     222          AND tri.refund_item_id IS NULL
    196223    ) THEN
    197224        RAISE EXCEPTION 'Access Denied: You can only rate events you attended and did not refund.';
    198225    END IF;
    199226
    200     INSERT INTO "Event_Happening_Rating" (rating, comment, event_happening_id, user_id)
    201     VALUES (p_rating, p_comment, p_event_happening_id, p_user_id);
     227    INSERT INTO "Event_Happening_Rating" (rating, comment, event_happening_id, user_id, timestamp)
     228    VALUES (p_rating, p_comment, p_event_happening_id, p_user_id, CURRENT_TIMESTAMP);
    202229
    203230    RAISE NOTICE 'Rating successfully submitted.';
    204231END;
    205 $$ LANGUAGE plpgsql;
    206 
    207 }}}
     232$$;
     233
     234}}}
     235
    208236
    209237== Тригери
    … …  
    211239=== `trg_check_user_age`
    212240
    213 Овој тригер врши автоматска проверка на старосната граница на корисникот пред да му дозволи да купи билет за одреден настан. Доколку настанот има ограничување (на пр. 18+), тригерот ја пресметува возраста на корисникот и го блокира внесувањето во табелата за нарачки ако условот не е исполнет.
     241Овој бизнис тригер врши автоматска валидација на старосната граница пред секое вметнување ставка во кошничката. Системот ја пресметува тековната возраст на купувачот преку неговиот датум на раѓање и инстантно го блокира процесот доколку настанот содржи рестриктивно ограничување за малолетници.
    214242
    215243{{{
    216244
    217245CREATE OR REPLACE FUNCTION trg_check_user_age()
    218 RETURNS TRIGGER AS $$
     246RETURNS TRIGGER
     247LANGUAGE plpgsql
     248AS $$
    219249DECLARE
    220250    v_user_age INT;
    221251    v_min_age INT;
    222 BEGIN
     252    v_user_id BIGINT;
     253BEGIN
     254    SELECT user_id INTO v_user_id
     255    FROM "Ticket_Order" WHERE order_id = NEW.order_id;
     256
    223257    SELECT DATE_PART('year', AGE(date_of_birth)) INTO v_user_age
    224     FROM "User" WHERE user_id = NEW.user_id;
     258    FROM "Regular_User" WHERE user_id = v_user_id;
    225259
    226260    SELECT e.min_age INTO v_min_age
    … …  
    231265
    232266    IF v_user_age < v_min_age THEN
    233         RAISE EXCEPTION 'Access Denied: User is too young.';
     267        RAISE EXCEPTION 'Access Denied: User does not meet the minimum age restriction for this event.';
    234268    END IF;
    235269
    236270    RETURN NEW;
    237271END;
    238 $$ LANGUAGE plpgsql;
    239 
    240 CREATE TRIGGER check_age_before_purchase
    241 BEFORE INSERT ON "Ticket_Purchase"
     272$$;
     273
     274CREATE TRIGGER check_age_before_item_insert
     275BEFORE INSERT ON "Ticket_Order_Item"
    242276FOR EACH ROW EXECUTE FUNCTION trg_check_user_age();
    243277
    244278}}}
    245279
    246 === `trg_check_venue_schedule`
    247 
    248 Овој тригер спречува преклопување на два настани во ист објект и во исто време. Пред секој внес или измена во распоредот, базата проверува дали терминот е веќе резервиран за таа локација.
    249 
    250 {{{
    251 
    252 CREATE OR REPLACE FUNCTION trg_check_venue_schedule()
    253 RETURNS TRIGGER AS $$
     280
     281=== `trg_sync_order_amount`
     282
     283Овој тригер одржува конзистентност на податоците преку автоматска синхронизација на финансиската сума во главната нарачка при секоја измена на нејзините ставки. Тој динамички го прекалкулира вкупниот износ земајќи ги предвид само активните и нерефундираните билети по секој '''INSERT''', '''UPDATE''' или '''DELETE'''.
     284
     285{{{
     286
     287CREATE OR REPLACE FUNCTION trg_sync_order_amount()
     288RETURNS TRIGGER
     289LANGUAGE plpgsql
     290AS $$
     291DECLARE
     292    v_target_order_id BIGINT;
     293    v_new_total DECIMAL(10,2);
     294BEGIN
     295    IF TG_OP = 'DELETE' THEN
     296        v_target_order_id := OLD.order_id;
     297    ELSE
     298        v_target_order_id := NEW.order_id;
     299    END IF;
     300
     301    SELECT COALESCE(SUM(toi.item_price), 0.00) INTO v_new_total
     302    FROM "Ticket_Order_Item" toi
     303    LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
     304    WHERE toi.order_id = v_target_order_id AND tri.refund_item_id IS NULL;
     305
     306    UPDATE "Ticket_Order"
     307    SET order_amount = v_new_total
     308    WHERE order_id = v_target_order_id;
     309
     310    RETURN NEW;
     311END;
     312$$;
     313
     314CREATE TRIGGER sync_order_total_on_change
     315AFTER INSERT OR UPDATE OR DELETE ON "Ticket_Order_Item"
     316FOR EACH ROW EXECUTE FUNCTION trg_sync_order_amount();
     317
     318}}}
     319
     320
     321=== `trg_prevent_double_booking`
     322
     323Овој тригер го штити интегритетот на распоредот на локациите преку спречување на временско преклопување на настани во иста сала. Тој применува напредна OVERLAPS логика која вклучува времетраење на перформансот и задолжителен технички бафер од 3 часа за подготовка на сцената пред почеток на следниот настан.
     324
     325{{{
     326
     327CREATE OR REPLACE FUNCTION trg_prevent_double_booking()
     328RETURNS TRIGGER
     329LANGUAGE plpgsql
     330AS $$
    254331BEGIN
    255332    IF EXISTS (
    256333        SELECT 1 FROM "Event_Happening"
    257334        WHERE venue_id = NEW.venue_id
    258           AND event_time = NEW.event_time
    259335          AND event_happening_id <> NEW.event_happening_id
     336          AND (NEW.event_time, NEW.event_time + (NEW.duration_minutes || ' minutes')::INTERVAL + INTERVAL '3 hours') OVERLAPS
     337              (event_time, event_time + (duration_minutes || ' minutes')::INTERVAL + INTERVAL '3 hours')
    260338    ) THEN
    261         RAISE EXCEPTION 'Venue is already occupied at this time.';
     339        RAISE EXCEPTION 'Access Denied: Venue is already occupied or currently in technical preparation (3-hour buffer required).';
    262340    END IF;
    263341    RETURN NEW;
    264342END;
    265 $$ LANGUAGE plpgsql;
    266 
    267 CREATE TRIGGER check_venue_before_insert_update
     343$$;
     344
     345CREATE TRIGGER check_venue_integrity
    268346BEFORE INSERT OR UPDATE ON "Event_Happening"
    269 FOR EACH ROW EXECUTE FUNCTION trg_check_venue_schedule();
    270 
    271 }}}
    272 
    273 === `trg_check_performer_availability`
    274 
    275 Овој тригер гарантира дека еден изведувач не може да биде на две места истовремено. Со поврзување на табелите за изведувачи и настани, системот автоматски го блокира секој обид за дуплирање на ангажманот во ист термин.
    276 
    277 {{{
    278 
    279 CREATE OR REPLACE FUNCTION trg_check_performer_availability()
    280 RETURNS TRIGGER AS $$
    281 DECLARE
    282     v_new_event_time TIMESTAMP;
    283 BEGIN
    284     SELECT event_time INTO v_new_event_time
    285     FROM "Event_Happening"
    286     WHERE event_happening_id = NEW.event_happening_id;
    287 
    288     IF EXISTS (
    289         SELECT 1
    290         FROM "Event_Happening_Performer" ehp
    291         JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id
    292         WHERE ehp.performer_id = NEW.performer_id
    293           AND eh.event_time = v_new_event_time
    294           AND ehp.event_happening_id <> NEW.event_happening_id
    295     ) THEN
    296         RAISE EXCEPTION 'Performer is already booked for another event at this time.';
    297     END IF;
    298     RETURN NEW;
    299 END;
    300 $$ LANGUAGE plpgsql;
    301 
    302 CREATE TRIGGER check_performer_before_assign
    303 BEFORE INSERT OR UPDATE ON "Event_Happening_Performer"
    304 FOR EACH ROW EXECUTE FUNCTION trg_check_performer_availability();
    305 
    306 }}}
     347FOR EACH ROW EXECUTE FUNCTION trg_prevent_double_booking();
     348
     349}}}