Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

06/12/26 20:44:37 (7 days ago)

Author:

231138

Comment:

--

DatabaseProgramming

@@ +1 @@
+= Напредни бази на податоци =
+= Фаза 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 ||