wiki:DatabaseProgramming

Version 1 (modified by 231138, 6 days ago) ( diff )

--

Напредни бази на податоци

Фаза 5 — Функции, процедури и тригери

Проект: DriveNet

Александар Милошевски 231138 Исидора Кузмановска 231052 Андон Михајлов 231016

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

ФУНКЦИИ

1. calculate_booking_fare

Опис:

Ја пресметува вкупната цена за одредена резервација врз основа на сегментите кои патникот ги поминува и бројот на патници на секој сегмент. Цената се дели рамномерно меѓу сите патници на секој сегмент — доколку на еден сегмент патуваат 3 патници, секој плаќа 1/3 од цената на тој сегмент.

Сигнатура:

calculate_booking_fare(p_booking_id INT) RETURNS DECIMAL(10,2)

Влезни параметри:

Параметар Тип Опис
p_booking_id INT ID на резервацијата

Логика:

  1. Го наоѓа ride_id и price_per_km преку bookings JOIN rides
  2. За секој сегмент во passenger_segments каде is_present = TRUE ја пресметува цената: (distance_km × price_per_km) / passengers_on_segment
  3. Ги сумира сите сегменти и го враќа вкупниот износ

Исклучоци:

  • RAISE EXCEPTION — доколку booking_id не постои

Имплементација:

CREATE OR REPLACE FUNCTION calculate_booking_fare(p_booking_id INT)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total        DECIMAL(10,2) := 0;
    v_ride_id      INT;
    v_price_per_km DECIMAL(8,2);
    rec            RECORD;
BEGIN
    SELECT b.ride_id, r.price_per_km
    INTO v_ride_id, v_price_per_km
    FROM bookings b
    JOIN rides r ON r.id = b.ride_id
    WHERE b.id = p_booking_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Booking % not found', p_booking_id;
    END IF;

    FOR rec IN
        SELECT rs.distance_km, ps.passengers_on_segment
        FROM passenger_segments ps
        JOIN route_segments rs ON rs.id = ps.segment_id
        WHERE ps.booking_id = p_booking_id
          AND ps.is_present = TRUE
    LOOP
        v_total := v_total + ROUND(
            (rec.distance_km * v_price_per_km) / rec.passengers_on_segment,
            2
        );
    END LOOP;

    RETURN v_total;
END;
$$;

Пример за употреба:

SELECT calculate_booking_fare(1);

2. is_driver_available

Опис:

Проверува дали возачот веќе има закажано возење кое временски се преклопува со бараниот термин. Се користи пред креирање на ново возење за да се спречат конфликти во распоредот. Буферот е ±3 часа околу бараното време на поаѓање.

Сигнатура:

is_driver_available(p_driver_id INT, p_departure TIMESTAMP) RETURNS BOOLEAN

Влезни параметри:

Параметар Тип Опис
p_driver_id INT ID на возачот
p_departure TIMESTAMP Бараното време на поаѓање

Логика:

  1. Брои колку возења со статус scheduled или in_progress има возачот во прозорецот [departure - 3h, departure + 3h]
  2. Доколку бројот е 0 враќа TRUE (слободен), инаку враќа FALSE (зафатен)

Имплементација:

CREATE OR REPLACE FUNCTION is_driver_available(
    p_driver_id INT,
    p_departure TIMESTAMP
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM rides
    WHERE driver_id = p_driver_id
      AND status IN ('scheduled', 'in_progress')
      AND departure_time BETWEEN (p_departure - INTERVAL '3 hours')
                             AND (p_departure + INTERVAL '3 hours');

    RETURN v_count = 0;
END;
$$;

Пример за употреба:

-- Провери дали возачот е слободен на 15.06.2025 во 10:00
SELECT is_driver_available(1, '2025-06-15 10:00:00');

3. get_available_seats

Опис:

Го враќа тековниот број на слободни места за одредено возење. Се користи пред резервација за да се потврди дека има место за нов патник.

Сигнатура:

get_available_seats(p_ride_id INT) RETURNS INT

Влезни параметри:

Параметар Тип Опис
p_ride_id INT ID на возењето

Логика:

  1. Го чита полето seats_available директно од табелата rides
  2. Доколку возењето не постои фрла исклучок

Исклучоци:

  • RAISE EXCEPTION — доколку ride_id не постои

Имплементација:

CREATE OR REPLACE FUNCTION get_available_seats(p_ride_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_seats INT;
BEGIN
    SELECT seats_available
    INTO v_seats
    FROM rides
    WHERE id = p_ride_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Ride % not found', p_ride_id;
    END IF;

    RETURN v_seats;
END;
$$;

Пример за употреба:

SELECT get_available_seats(1);

ПРОЦЕДУРИ

4. book_ride

Опис:

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

Сигнатура:

book_ride(p_passenger_id INT, p_ride_id INT,
          p_pickup_stop_id INT, p_dropoff_stop_id INT)

Влезни параметри:

Параметар Тип Опис
p_passenger_id INT ID на патникот
p_ride_id INT ID на возењето
p_pickup_stop_id INT ID на постојката за качување
p_dropoff_stop_id INT ID на постојката за слегување

Логика:

  1. Ги заклучува редовите со FOR UPDATE за да се спречат состојби на трка
  2. Проверува дали возењето постои и дали е со статус scheduled
  3. Проверува дали seats_available > 0
  4. Проверува дали патникот веќе има резервација за ова возење
  5. Вметнува нов ред во bookings со статус confirmed
  6. Го намалува seats_available за 1
  7. Вметнува запис во booking_status_history

Исклучоци:

  • RAISE EXCEPTION — возењето не постои
  • RAISE EXCEPTION — возењето не е со статус scheduled
  • RAISE EXCEPTION — нема слободни места
  • RAISE EXCEPTION — патникот веќе има резервација за ова возење

Имплементација:

CREATE OR REPLACE PROCEDURE book_ride(
    p_passenger_id    INT,
    p_ride_id         INT,
    p_pickup_stop_id  INT,
    p_dropoff_stop_id INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_booking_id  INT;
    v_seats       INT;
    v_ride_status VARCHAR(20);
BEGIN
    SELECT status, seats_available
    INTO v_ride_status, v_seats
    FROM rides
    WHERE id = p_ride_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Ride % not found', p_ride_id;
    END IF;

    IF v_ride_status != 'scheduled' THEN
        RAISE EXCEPTION 'Ride % is not available for booking (status: %)',
            p_ride_id, v_ride_status;
    END IF;

    IF v_seats <= 0 THEN
        RAISE EXCEPTION 'No available seats for ride %', p_ride_id;
    END IF;

    IF EXISTS (
        SELECT 1 FROM bookings
        WHERE ride_id = p_ride_id
          AND passenger_id = p_passenger_id
    ) THEN
        RAISE EXCEPTION 'Passenger % already has a booking for ride %',
            p_passenger_id, p_ride_id;
    END IF;

    INSERT INTO bookings (
        ride_id, passenger_id,
        pickup_stop_id, dropoff_stop_id,
        status, created_at
    )
    VALUES (
        p_ride_id, p_passenger_id,
        p_pickup_stop_id, p_dropoff_stop_id,
        'confirmed', NOW()
    )
    RETURNING id INTO v_booking_id;

    UPDATE rides
    SET seats_available = seats_available - 1
    WHERE id = p_ride_id;

    INSERT INTO booking_status_history (booking_id, status, changed_at)
    VALUES (v_booking_id, 'confirmed', NOW());

    RAISE NOTICE 'Booking % created for passenger % on ride %',
        v_booking_id, p_passenger_id, p_ride_id;
END;
$$;

Пример за употреба:

CALL book_ride(1, 1, 1, 4);

5. cancel_booking

Опис:

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

Сигнатура:

cancel_booking(p_booking_id INT, p_reason VARCHAR(300) DEFAULT NULL)

Влезни параметри:

Параметар Тип Опис
p_booking_id INT ID на резервацијата
p_reason VARCHAR(300) Причина за откажување (опционално)

Логика:

  1. Ги зема деталите за резервацијата и поврзаното возење со FOR UPDATE
  2. Проверува дали статусот е pending или confirmed
  3. Проверува дали до поаѓањето има повеќе од 1 час
  4. Го менува статусот на cancelled
  5. Го зголемува seats_available за 1
  6. Вметнува запис во booking_status_history со причината

Исклучоци:

  • RAISE EXCEPTION — резервацијата не постои
  • RAISE EXCEPTION — статусот не дозволува откажување
  • RAISE EXCEPTION — помалку од 1 час до поаѓање

Имплементација:

CREATE OR REPLACE PROCEDURE cancel_booking(
    p_booking_id INT,
    p_reason     VARCHAR(300) DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_ride_id   INT;
    v_status    VARCHAR(20);
    v_departure TIMESTAMP;
BEGIN
    SELECT b.ride_id, b.status, r.departure_time
    INTO v_ride_id, v_status, v_departure
    FROM bookings b
    JOIN rides r ON r.id = b.ride_id
    WHERE b.id = p_booking_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Booking % not found', p_booking_id;
    END IF;

    IF v_status NOT IN ('pending', 'confirmed') THEN
        RAISE EXCEPTION 'Cannot cancel booking % with status %',
            p_booking_id, v_status;
    END IF;

    IF v_departure <= NOW() + INTERVAL '1 hour' THEN
        RAISE EXCEPTION 'Cannot cancel booking % — less than 1 hour before departure',
            p_booking_id;
    END IF;

    UPDATE bookings
    SET status = 'cancelled'
    WHERE id = p_booking_id;

    UPDATE rides
    SET seats_available = seats_available + 1
    WHERE id = v_ride_id;

    INSERT INTO booking_status_history (booking_id, status, changed_at, reason)
    VALUES (p_booking_id, 'cancelled', NOW(), p_reason);

    RAISE NOTICE 'Booking % cancelled successfully', p_booking_id;
END;
$$;

Пример за употреба:

CALL cancel_booking(1, 'Патникот не може да патува');

6. complete_ride

Опис:

Го завршува возењето и автоматски ги завршува сите активни резервации. За секоја резервација се пресметува и зачувува финалната тарифа преку функцијата calculate_booking_fare.

Сигнатура:

complete_ride(p_ride_id INT)

Влезни параметри:

Параметар Тип Опис
p_ride_id INT ID на возењето

Логика:

  1. Проверува дали возењето постои и дали е со статус in_progress
  2. Го менува статусот на возењето во completed
  3. За секоја резервација со статус confirmed или picked_up:
    • Го менува статусот во completed
    • Го поставува dropoff_confirmed_at = NOW()
    • Ја пресметува финалната тарифа преку calculate_booking_fare
    • Ја зачувува тарифата во booking_final_fare (INSERT или UPDATE)
    • Вметнува запис во booking_status_history

Исклучоци:

  • RAISE EXCEPTION — возењето не постои
  • RAISE EXCEPTION — возењето не е со статус in_progress

Имплементација:

CREATE OR REPLACE PROCEDURE complete_ride(p_ride_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR(20);
    rec      RECORD;
    v_fare   DECIMAL(10,2);
BEGIN
    SELECT status INTO v_status
    FROM rides
    WHERE id = p_ride_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Ride % not found', p_ride_id;
    END IF;

    IF v_status != 'in_progress' THEN
        RAISE EXCEPTION 'Ride % cannot be completed (status: %)',
            p_ride_id, v_status;
    END IF;

    UPDATE rides
    SET status = 'completed'
    WHERE id = p_ride_id;

    FOR rec IN
        SELECT id FROM bookings
        WHERE ride_id = p_ride_id
          AND status IN ('confirmed', 'picked_up')
    LOOP
        UPDATE bookings
        SET status = 'completed',
            dropoff_confirmed_at = NOW()
        WHERE id = rec.id;

        v_fare := calculate_booking_fare(rec.id);

        INSERT INTO booking_final_fare (booking_id, total_amount, calculated_at)
        VALUES (rec.id, v_fare, NOW())
        ON CONFLICT (booking_id)
        DO UPDATE SET
            total_amount  = EXCLUDED.total_amount,
            calculated_at = EXCLUDED.calculated_at;

        INSERT INTO booking_status_history (booking_id, status, changed_at)
        VALUES (rec.id, 'completed', NOW());
    END LOOP;

    RAISE NOTICE 'Ride % completed successfully', p_ride_id;
END;
$$;

Пример за употреба:

CALL complete_ride(1);

ТРИГЕРИ

7. trg_booking_status_history

Опис:

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

Тип: AFTER UPDATE на табела bookings

Логика:

  1. Се активира по секој UPDATE на табелата bookings
  2. Проверува дали OLD.status IS DISTINCT FROM NEW.status
  3. Доколку статусот се сменил, вметнува нов ред во booking_status_history

Имплементација:

CREATE OR REPLACE FUNCTION fn_booking_status_history()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO booking_status_history (booking_id, status, changed_at)
        VALUES (NEW.id, NEW.status, NOW());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_booking_status_history
AFTER UPDATE ON bookings
FOR EACH ROW
EXECUTE FUNCTION fn_booking_status_history();

Пример — активирање:

-- Овој UPDATE автоматски ќе вметне ред во booking_status_history
UPDATE bookings SET status = 'picked_up' WHERE id = 1;

8. trg_ride_status_history

Опис:

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

Тип: AFTER UPDATE на табела rides

Логика:

  1. Се активира по секој UPDATE на табелата rides
  2. Проверува дали OLD.status IS DISTINCT FROM NEW.status
  3. Доколку статусот се сменил, вметнува нов ред во ride_status_history

Имплементација:

CREATE OR REPLACE FUNCTION fn_ride_status_history()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO ride_status_history (ride_id, status, changed_at)
        VALUES (NEW.id, NEW.status, NOW());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_ride_status_history
AFTER UPDATE ON rides
FOR EACH ROW
EXECUTE FUNCTION fn_ride_status_history();

Пример — активирање:

-- Овој UPDATE автоматски ќе вметне ред во ride_status_history
UPDATE rides SET status = 'in_progress' WHERE id = 1;

9. trg_audit_log

Опис:

Автоматски запишува во табелата audit_logs за сите INSERT, UPDATE и DELETE операции на критичните табели: rides, bookings и users. Го чува и старото и новото состојба на редот во JSON формат.

Тип: AFTER INSERT OR UPDATE OR DELETE на табели rides, bookings, users

Логика:

  1. За INSERT — зачувува new_data = row_to_json(NEW), old_data = NULL
  2. За UPDATE — зачувува old_data = row_to_json(OLD) и new_data = row_to_json(NEW)
  3. За DELETE — зачувува old_data = row_to_json(OLD), new_data = NULL

Имплементација:

CREATE OR REPLACE FUNCTION fn_audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs (
            table_name, record_id, operation,
            old_data, new_data, changed_at
        )
        VALUES (
            TG_TABLE_NAME, NEW.id, 'INSERT',
            NULL, row_to_json(NEW)::TEXT, NOW()
        );
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs (
            table_name, record_id, operation,
            old_data, new_data, changed_at
        )
        VALUES (
            TG_TABLE_NAME, NEW.id, 'UPDATE',
            row_to_json(OLD)::TEXT, row_to_json(NEW)::TEXT, NOW()
        );
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs (
            table_name, record_id, operation,
            old_data, new_data, changed_at
        )
        VALUES (
            TG_TABLE_NAME, OLD.id, 'DELETE',
            row_to_json(OLD)::TEXT, NULL, NOW()
        );
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER trg_audit_log_rides
AFTER INSERT OR UPDATE OR DELETE ON rides
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_log_bookings
AFTER INSERT OR UPDATE OR DELETE ON bookings
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_log_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

Пример — активирање:

-- Секој од овие автоматски ќе вметне ред во audit_logs
INSERT INTO rides (...) VALUES (...);
UPDATE bookings SET status = 'cancelled' WHERE id = 1;
DELETE FROM users WHERE id = 999;

Резиме

# Име Тип Опис
1 calculate_booking_fare Функција Пресметува вкупна цена за резервација
2 is_driver_available Функција Проверува конфликт на термин за возач
3 get_available_seats Функција Враќа број на слободни места за возење
4 book_ride Процедура Целосен процес на резервација
5 cancel_booking Процедура Откажување на резервација со проверки
6 complete_ride Процедура Завршување на возење и пресметка на тарифи
7 trg_booking_status_history Тригер Автоматски лог на промени на статус на резервација
8 trg_ride_status_history Тригер Автоматски лог на промени на статус на возење
9 trg_audit_log Тригер Audit лог за rides, bookings и users
Note: See TracWiki for help on using the wiki.