Changes between Version 6 and Version 7 of DatabaseProgramming

Timestamp:

05/23/26 21:14:33 (2 days ago)

Author:

231028

Comment:

--

DatabaseProgramming

@@ -5 +5 @@
 == Процедури
 
+{{{
+create or replace procedure start_shift(
+    in driver_id int4,
+    in vin_vehicle varchar(17)
+)
+    language plpgsql as
+$$
+begin
+    if not exists(select user_id from driver where user_id = driver_id) then
+        raise exception 'Driver with id % does not exist', driver_id;
+    end if;
+
+    if not exists(select vin from vehicle where vin = vin_vehicle) then
+        raise exception 'Vehicle with VIN % does not exist', vin_vehicle;
+    end if;
+
+    insert into driver_vehicle(vin_vehicle, id_driver, time_from, time_to)
+    values (vin_vehicle, driver_id, now(), null);
+    commit;
+end;
+$$;
+}}}
 **Процедура за вработување на возач во компанија**
 
@@ -165 +187 @@
 $$;
 }}}
-
+{{{
+create or replace procedure make_request(
+    out request_id int4,
+    in customer_id int4,
+    in start_latitude double precision,
+    in start_longitude double precision,
+    in end_latitude double precision,
+    in end_longitude double precision,
+    in number_of_adult_passengers int4 default 1,
+    in number_of_children int4 default 0,
+    in female_driver boolean default false,
+    in luggage boolean default false,
+    in luggage_count int4 default 0,
+    in baby_seat_count int4 default 0
+)
+    language plpgsql as
+$$
+begin
+    if not exists(select user_id
+                  from customer c
+                  where c.user_id = customer_id)
+    then
+        raise exception 'Customer with id % does not exist', customer_id;
+    end if;
+
+    if start_latitude < -90.0 or start_latitude > 90.0
+        or start_longitude < -180.0 or start_longitude > 180.0
+        or end_latitude < -90.0 or end_latitude > 90.0
+        or end_longitude < -180.0 or end_longitude > 180.0
+    then
+        raise exception 'Invalid location values';
+    end if;
+
+    if number_of_adult_passengers < 0 then
+        raise exception 'Number of adult passengers cannot be negative';
+    end if;
+
+    if number_of_children < 0 then
+        raise exception 'Number of children cannot be negative';
+    end if;
+
+    if number_of_adult_passengers + number_of_children < 0 then
+        raise exception 'There must be at least 1 passenger';
+    end if;
+
+    if baby_seat_count < 0 then
+        raise exception 'Baby seat count cannot be negative';
+    end if;
+
+    if luggage = false and luggage_count > 0 then
+        raise exception 'Luggage count cannot be greater than zero if the luggage flag is set to false.';
+    end if;
+
+    if luggage = true and luggage_count <= 0 then
+        raise exception 'Invalid luggage count parameters';
+    end if;
+
+    if start_latitude = end_latitude and start_longitude = end_longitude then
+        raise exception 'Start and destination location cannot be the same';
+    end if;
+
+    insert into request(customer_user_id,
+                                      start_latitude,
+                                      start_longitude,
+                                      end_latitude,
+                                      end_longitude,
+                                      timestamp,
+                                      number_of_adult_passengers,
+                                      number_of_children,
+                                      status,
+                                      female_driver,
+                                      luggage,
+                                      luggage_count,
+                                      baby_seat_count)
+    values (customer_id,
+            make_request.start_latitude,
+            make_request.start_longitude,
+            make_request.end_latitude,
+            make_request.end_longitude,
+            now(),
+            make_request.number_of_adult_passengers,
+            make_request.number_of_children,
+            'pending',
+            make_request.female_driver,
+            make_request.luggage,
+            make_request.luggage_count,
+            make_request.baby_seat_count)
+    returning id into request_id;
+    commit;
+end;
+$$;
+}}}
+
+{{{
+create or replace procedure add_waypoint(
+    in request_id int4,
+    in latitude int4,
+    in longitude int4
+)
+    language plpgsql as
+$$
+declare
+    prev_seqno int4;
+begin
+    if not exists(select id from request where id = request_id) then
+        raise exception 'Request with id % does not exist', request_id;
+    end if;
+
+    if latitude < -90.0 or latitude > 90.0
+        or longitude < -180.0 or longitude > 180.0 then
+        raise exception 'Invalid waypoint coordinates';
+    end if;
+
+    if exists(select id
+              from waypoints w
+              where w.latitude = add_waypoint.latitude
+                and w.longitude = add_waypoint.longitude
+                and w.request_id = add_waypoint.request_id) then
+        raise exception 'Waypoint already added';
+    end if;
+
+    select count(*)
+    into prev_seqno
+    from waypoints w
+    where w.request_id = add_waypoint.request_id;
+
+    insert into waypoints(latitude, longitude, seqno, request_id)
+    values (add_waypoint.latitude, add_waypoint.longitude, prev_seqno + 1, add_waypoint.request_id);
+    commit;
+end;
+$$;
+}}}
+
+{{{
+create or replace procedure start_ride(
+    in offer_id int4,
+    out ride_id int4
+)
+    language plpgsql as
+$$
+declare
+    v_request_id      int4;
+    start_latitude  double precision;
+    start_longitude double precision;
+    driver_id       int4;
+    v_vehicle_vin     varchar(17);
+--     v_status          ride_status;
+begin
+    if not exists(select id from offer where id = offer_id) then
+        raise exception 'Offer with id % does not exist', offer_id;
+    end if;
+
+    select o.request_id,
+           r.start_latitude,
+           r.start_longitude
+    into
+        request_id,
+        start_latitude,
+        start_longitude
+    from offer o
+             join request r on o.request_id = r.id
+    where o.id = offer_id;
+
+    select d.user_id, dc.vin_vehicle
+    into driver_id,
+        v_vehicle_vin
+    from driver d
+             join driver_vehicle dc on d.user_id = dc.id_driver
+    where dc.time_to is null
+      and d.latitude is not null
+      and d.longitude is not null
+    order by power(start_latitude - d.latitude, 2) + power(start_longitude - d.longitude, 2)
+    limit 1;
+
+    if driver_id is null then
+        raise exception 'No active drivers available';
+    end if;
+
+    insert into ride(start_time,
+                     end_time,
+                     distance_traveled,
+                     vehicle_vin,
+                     driver_user_id,
+                     request_id,
+                     status,
+                     offer_id)
+    values (now(),
+            null,
+            0,
+            v_vehicle_vin,
+            driver_id,
+            v_request_id,
+            'in_progress',
+            start_ride.offer_id);
+
+    update offer
+    set status='accepted'
+    where id = offer_id;
+
+    update request
+    set status='in_progress'
+    where id = v_request_id;
+
+    commit;
+end;
+$$;
+}}}
 == Функции и тригери
+
+{{{
+create or replace function check_active_shift()
+    returns trigger as
+$check_active_shift$
+begin
+    if not exists(select user_id from driver where user_id = new.id_driver) then
+        raise exception 'Driver with id % does not exist', new.id_driver;
+    end if;
+
+    if not exists(select vin from vehicle where vin = new.vin_vehicle) then
+        raise exception 'Vehicle with VIN % does not exist', new.vin_vehicle;
+    end if;
+
+    if exists(select id
+              from driver_vehicle
+              where id_driver = new.id_driver
+                and time_to is null) then
+        raise exception 'Driver is already on shift';
+    end if;
+
+    return new;
+end;
+$check_active_shift$ language plpgsql;
+create trigger check_active_shift
+    before insert or update
+    on driver_vehicle
+    for each row
+execute function check_active_shift();
+}}}
 
 **Тригер и функција за проверка на единствена оценка**
@@ -222 +480 @@
 }}}
 
+{{{
+create or replace function prevent_expired_license_ride()
+    returns trigger as
+$prevent_expired_license_ride$
+declare
+    exp_date date;
+begin
+    select dl.expire_date
+    into exp_date
+    from driver d
+             join driverlicense dl on d.driver_license_id = dl.id
+    where d.user_id = new.driver_user_id;
+
+    if exp_date is not null and exp_date < current_date then
+        raise exception 'Driver license expired';
+    end if;
+
+    return new;
+end;
+$prevent_expired_license_ride$ language plpgsql;
+create or replace trigger prevent_expired_license_ride
+    before insert
+    on ride
+    for each row
+execute function prevent_expired_license_ride();
+}}}
+
+{{{
+create or replace function prevent_completed_offer_downgrade()
+    returns trigger as
+$prevent_completed_offer_downgrade$
+begin
+    if old.status = 'completed'
+        and new.status <> 'completed' then
+        raise exception 'Completed offer cannot change status';
+    end if;
+
+    return new;
+end;
+$prevent_completed_offer_downgrade$ language plpgsql;
+create or replace trigger prevent_completed_offer_downgrade
+    before update
+    on offer
+    for each row
+execute function prevent_completed_offer_downgrade();
+}}}
+