Changes between Version 28 and Version 29 of DatabaseProgramming


Ignore:
Timestamp:
06/30/26 22:04:06 (5 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v28 v29  
    1111
    1212CREATE OR REPLACE FUNCTION get_current_price(p_ticket_id BIGINT)
    13 RETURNS DECIMAL(10,2)
    14 LANGUAGE plpgsql
    15 AS $$
    16 DECLARE
    17     v_base_price DECIMAL(10,2);
    18     v_happening_id BIGINT;
     13RETURNS DECIMAL(10,2) LANGUAGE plpgsql AS $$
     14DECLARE
     15    v_base_price     DECIMAL(10,2);
     16    v_happening_id   BIGINT;
    1917    v_discount_percent INT;
    20     v_final_price DECIMAL(10,2);
    21 BEGIN
    22     SELECT base_price, event_happening_id INTO v_base_price, v_happening_id
    23     FROM "Ticket" WHERE ticket_id = p_ticket_id;
    24 
    25     SELECT price_discount_percent INTO v_discount_percent
     18    v_final_price    DECIMAL(10,2);
     19BEGIN
     20    SELECT base_price, event_happening_id
     21    INTO v_base_price, v_happening_id
     22    FROM "Ticket"
     23    WHERE ticket_id = p_ticket_id;
     24
     25    SELECT price_discount_percent
     26    INTO v_discount_percent
    2627    FROM "Event_Period"
    2728    WHERE event_happening_id = v_happening_id
    … …  
    2930    LIMIT 1;
    3031
    31     IF FOUND THEN
    32         v_final_price := v_base_price * (1 - (v_discount_percent / 100.0));
    33     ELSE
    34         v_final_price := v_base_price;
    35     END IF;
     32    v_final_price := COALESCE(v_base_price * (1 - (v_discount_percent / 100.0)), v_base_price);
    3633
    3734    RETURN v_final_price;
    … …  
    4946
    5047CREATE OR REPLACE FUNCTION calculate_refund_amount(p_order_item_id BIGINT)
    51 RETURNS DECIMAL(10,2)
    52 LANGUAGE plpgsql
    53 AS $$
     48RETURNS DECIMAL(10,2) LANGUAGE plpgsql AS $$
    5449DECLARE
    5550    v_original_price DECIMAL(10,2);
    56     v_refund_amount DECIMAL(10,2);
    57 BEGIN
    58     SELECT item_price INTO v_original_price
     51BEGIN
     52    SELECT item_price
     53    INTO v_original_price
    5954    FROM "Ticket_Order_Item"
    6055    WHERE order_item_id = p_order_item_id;
    … …  
    6459    END IF;
    6560
    66     v_refund_amount := v_original_price * 0.85;
    67 
    68     RETURN v_refund_amount;
     61    RETURN v_original_price * 0.85;
    6962END;
    7063$$;
    … …  
    8174{{{
    8275
    83 CREATE OR REPLACE PROCEDURE buy_ticket(
    84     p_user_id BIGINT,
    85     p_ticket_id BIGINT
    86 )
    87 LANGUAGE plpgsql
    88 AS $$
    89 DECLARE
    90     v_price DECIMAL(10,2);
    91     v_qr_code TEXT;
    92     v_order_id BIGINT;
    93 BEGIN
     76CREATE OR REPLACE PROCEDURE buy_ticket(p_user_id BIGINT, p_ticket_id BIGINT)
     77LANGUAGE plpgsql AS $$
     78DECLARE
     79    v_price     DECIMAL(10,2);
     80    v_qr_code   TEXT;
     81    v_order_id  BIGINT;
     82    v_active    BOOLEAN;
     83BEGIN
     84    SELECT is_active INTO v_active FROM "User" WHERE user_id = p_user_id;
     85    IF v_active IS NOT TRUE THEN
     86        RAISE EXCEPTION 'Access Denied: User is inactive.';
     87    END IF;
     88
    9489    v_price := get_current_price(p_ticket_id);
    9590    v_qr_code := 'QR-' || p_ticket_id || '-' || p_user_id || '-' || EXTRACT(EPOCH FROM NOW());
    … …  
    10398
    10499    UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = p_ticket_id;
    105 
    106     COMMIT;
    107100END;
    108101$$;
    … …  
    117110{{{
    118111
    119 CREATE OR REPLACE PROCEDURE cancel_ticket(
    120     p_order_item_id BIGINT,
    121     p_reason TEXT
    122 )
    123 LANGUAGE plpgsql
    124 AS $$
     112CREATE OR REPLACE PROCEDURE cancel_ticket(p_order_item_id BIGINT, p_reason TEXT)
     113LANGUAGE plpgsql AS $$
    125114DECLARE
    126115    v_refund_amount DECIMAL(10,2);
    127     v_ticket_id BIGINT;
    128     v_order_id BIGINT;
    129     v_refund_id BIGINT;
     116    v_ticket_id     BIGINT;
     117    v_order_id      BIGINT;
     118    v_refund_id     BIGINT;
    130119BEGIN
    131120    v_refund_amount := calculate_refund_amount(p_order_item_id);
    132121
    133     SELECT order_id, ticket_id INTO v_order_id, v_ticket_id
     122    SELECT order_id, ticket_id
     123    INTO v_order_id, v_ticket_id
    134124    FROM "Ticket_Order_Item"
    135125    WHERE order_item_id = p_order_item_id;
    … …  
    143133
    144134    UPDATE "Ticket" SET is_available = TRUE WHERE ticket_id = v_ticket_id;
    145 
    146     RAISE NOTICE 'Ticket item cancelled successfully. Refund amount: %', v_refund_amount;
    147135END;
    148136$$;
    … …  
    165153    p_base_price DECIMAL(10,2)
    166154)
    167 LANGUAGE plpgsql
    168 AS $$
     155LANGUAGE plpgsql AS $$
    169156DECLARE
    170157    v_happening_id BIGINT;
    171 BEGIN
    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.';
     158    v_active       BOOLEAN;
     159BEGIN
     160    IF NOT EXISTS (SELECT 1 FROM "Admin" WHERE user_id = p_admin_id) THEN
     161        RAISE EXCEPTION 'Access Denied: Not an admin.';
     162    END IF;
     163
     164    SELECT is_active INTO v_active FROM "Event" WHERE event_id = p_event_id;
     165    IF v_active IS NOT TRUE THEN
     166        RAISE EXCEPTION 'Cannot schedule for an inactive event.';
    176167    END IF;
    177168
    178169    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')
     170    VALUES (p_event_id, p_time, p_venue_id, p_duration_minutes, 'System Generated')
    180171    RETURNING event_happening_id INTO v_happening_id;
    181172
    182173    INSERT INTO "Ticket" (base_price, is_available, event_happening_id, seat_id)
    183     SELECT
    184         p_base_price,
    185         TRUE,
    186         v_happening_id,
    187         s.seat_id
     174    SELECT p_base_price, TRUE, v_happening_id, s.seat_id
    188175    FROM "Seat" s
    189176    JOIN "Section" sec ON s.section_id = sec.section_id
    190177    WHERE sec.venue_id = p_venue_id;
    191 
    192     RAISE NOTICE 'Event happening successfully scheduled by admin % and tickets generated.', p_admin_id;
    193178END;
    194179$$;
    … …  
    203188{{{
    204189
    205 CREATE OR REPLACE PROCEDURE create_rating(
    206     p_user_id BIGINT,
    207     p_event_happening_id BIGINT,
    208     p_rating INT,
    209     p_comment TEXT
    210 )
    211 LANGUAGE plpgsql
    212 AS $$
     190CREATE OR REPLACE PROCEDURE create_rating(p_user_id BIGINT, p_event_happening_id BIGINT, p_rating INT, p_comment TEXT)
     191LANGUAGE plpgsql AS $$
    213192BEGIN
    214193    IF NOT EXISTS (
    … …  
    222201          AND tri.refund_item_id IS NULL
    223202    ) THEN
    224         RAISE EXCEPTION 'Access Denied: You can only rate events you attended and did not refund.';
     203        RAISE EXCEPTION 'Access Denied: Cannot rate.';
    225204    END IF;
    226205
    227206    INSERT INTO "Event_Happening_Rating" (rating, comment, event_happening_id, user_id, timestamp)
    228207    VALUES (p_rating, p_comment, p_event_happening_id, p_user_id, CURRENT_TIMESTAMP);
    229 
    230     RAISE NOTICE 'Rating successfully submitted.';
    231208END;
    232209$$;
    … …  
    244221
    245222CREATE OR REPLACE FUNCTION trg_check_user_age()
    246 RETURNS TRIGGER
    247 LANGUAGE plpgsql
    248 AS $$
    249 DECLARE
    250     v_user_age INT;
    251     v_min_age INT;
    252     v_user_id BIGINT;
    253 BEGIN
    254     SELECT user_id INTO v_user_id
    255     FROM "Ticket_Order" WHERE order_id = NEW.order_id;
    256 
    257     SELECT DATE_PART('year', AGE(date_of_birth)) INTO v_user_age
    258     FROM "Regular_User" WHERE user_id = v_user_id;
    259 
    260     SELECT e.min_age INTO v_min_age
     223RETURNS TRIGGER LANGUAGE plpgsql AS $$
     224DECLARE
     225    v_active    BOOLEAN;
     226    v_user_age  INT;
     227    v_min_age   INT;
     228    v_user_id   BIGINT;
     229BEGIN
     230    SELECT user_id INTO v_user_id FROM "Ticket_Order" WHERE order_id = NEW.order_id;
     231    SELECT is_active INTO v_active FROM "User" WHERE user_id = v_user_id;
     232
     233    IF v_active IS NOT TRUE THEN
     234        RAISE EXCEPTION 'User is inactive.';
     235    END IF;
     236
     237    SELECT DATE_PART('year', AGE(date_of_birth))
     238    INTO v_user_age
     239    FROM "Regular_User"
     240    WHERE user_id = v_user_id;
     241
     242    SELECT e.min_age
     243    INTO v_min_age
    261244    FROM "Ticket" t
    262245    JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
    … …  
    265248
    266249    IF v_user_age < v_min_age THEN
    267         RAISE EXCEPTION 'Access Denied: User does not meet the minimum age restriction for this event.';
     250        RAISE EXCEPTION 'Age restriction.';
    268251    END IF;
    269252
    … …  
    279262
    280263
    281 === `trg_sync_order_amount`
    282 
    283 Овој тригер одржува конзистентност на податоците преку автоматска синхронизација на финансиската сума во главната нарачка при секоја измена на нејзините ставки. Тој динамички го прекалкулира вкупниот износ земајќи ги предвид само активните и нерефундираните билети по секој '''INSERT''', '''UPDATE''' или '''DELETE'''.
    284 
    285 {{{
    286 
    287 CREATE OR REPLACE FUNCTION trg_sync_order_amount()
    288 RETURNS TRIGGER
    289 LANGUAGE plpgsql
    290 AS $$
    291 DECLARE
    292     v_target_order_id BIGINT;
    293     v_new_total DECIMAL(10,2);
    294 BEGIN
    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
     264=== `trg_limit_tickets_per_happening`
     265
     266...
     267
     268{{{
     269
     270CREATE OR REPLACE FUNCTION trg_limit_tickets_per_happening()
     271RETURNS TRIGGER LANGUAGE plpgsql AS $$
     272DECLARE
     273    v_happening_id BIGINT;
     274    v_count        INT;
     275BEGIN
     276    SELECT event_happening_id INTO v_happening_id FROM "Ticket" WHERE ticket_id = NEW.ticket_id;
     277
     278    SELECT COUNT(*) INTO v_count
    302279    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;
     280    JOIN "Ticket_Order" o ON toi.order_id = o.order_id
     281    JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
     282    WHERE o.user_id = (SELECT user_id FROM "Ticket_Order" WHERE order_id = NEW.order_id)
     283      AND t.event_happening_id = v_happening_id;
     284
     285    IF v_count >= 4 THEN
     286        RAISE EXCEPTION 'Purchase limit reached: You cannot own more than 4 tickets for this event.';
     287    END IF;
    309288
    310289    RETURN NEW;
    … …  
    312291$$;
    313292
    314 CREATE TRIGGER sync_order_total_on_change
    315 AFTER INSERT OR UPDATE OR DELETE ON "Ticket_Order_Item"
    316 FOR EACH ROW EXECUTE FUNCTION trg_sync_order_amount();
     293CREATE TRIGGER trg_limit_user_happening_tickets
     294BEFORE INSERT ON "Ticket_Order_Item"
     295FOR EACH ROW EXECUTE FUNCTION trg_limit_tickets_per_happening();
    317296
    318297}}}
    … …  
    326305
    327306CREATE OR REPLACE FUNCTION trg_prevent_double_booking()
    328 RETURNS TRIGGER
    329 LANGUAGE plpgsql
    330 AS $$
     307RETURNS TRIGGER LANGUAGE plpgsql AS $$
    331308BEGIN
    332309    IF EXISTS (
    … …  
    334311        WHERE venue_id = NEW.venue_id
    335312          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')
     313          AND (NEW.event_time, NEW.event_time + (NEW.duration_minutes || ' minutes')::INTERVAL + INTERVAL '3 hours')
     314              OVERLAPS (event_time, event_time + (duration_minutes || ' minutes')::INTERVAL + INTERVAL '3 hours')
    338315    ) THEN
    339         RAISE EXCEPTION 'Access Denied: Venue is already occupied or currently in technical preparation (3-hour buffer required).';
    340     END IF;
     316        RAISE EXCEPTION 'Venue occupied.';
     317    END IF;
     318
    341319    RETURN NEW;
    342320END;