Changes between Version 9 and Version 10 of DatabaseProgramming

Timestamp:

05/23/26 22:01:05 (2 days ago)

Author:

231028

Comment:

--

DatabaseProgramming

@@ -399 +399 @@
 $$;
 }}}
+
+{{{
+create or replace procedure public_schema.finish_shift(
+    in driver_id int4
+)
+    language plpgsql as
+$$
+declare
+    num_of_shifts int4;
+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;
+
+    num_of_shifts := (select count(*) from driver_vehicle where id_driver = driver_id and time_to is null);
+
+    if (num_of_shifts != 1) then
+        raise exception 'Driver has not started a shift';
+    end if;
+
+    update driver_vehicle
+    set time_to=now()
+    where id_driver = driver_id
+      and time_to is null;
+    commit;
+end;
+$$;
+}}}
+
+{{{
+create or replace procedure create_offer(
+    request_id int4,
+    dispatcher_user_id int4,
+    driver_user_id int4,
+    price numeric(19, 2),
+    currency_catalog_id int4,
+    eta timestamp,
+    customer_user_id int4
+)
+    language plpgsql
+AS
+$$
+begin
+    if not exists(select * from request where request.id = request_id and request.status = 'pending') then
+        raise exception 'Request with id % and pending status does not exist', request_id;
+    end if;
+    if price <= 0 then
+        raise exception 'Price has to be greater than 0';
+    end if;
+    if eta <= now() then
+        raise exception 'ETA cannot be lower than the time of creation';
+    end if;
+
+    if (select company_id from employmenthistory where employee_user_id = dispatcher_user_id and end_date is null) !=
+       (select company_id from employmenthistory where employee_user_id = driver_user_id and end_date is null) then
+        raise exception 'Dispatcher and driver company mismatch';
+    end if;
+
+    insert into offer(status, created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id,
+                      eta, customer_user_id)
+    values ('pending', now(), create_offer.request_id, create_offer.dispatcher_user_id, create_offer.driver_user_id,
+            create_offer.price, create_offer.currency_catalog_id, create_offer.eta, create_offer.customer_user_id);
+    commit;
+end;
+$$;
+}}}
+
+{{{
+create or replace procedure accept_offer(offer_id int4)
+    language plpgsql
+as
+$$
+declare
+    request_id_proc int4;
+begin
+    if not exists(select * from offer where id = offer_id and status = 'pending') then
+        raise exception 'Offer with id = % and pending status does not exist', offer_id;
+    end if;
+    request_id_proc := (select request_id from offer where id = offer_id);
+    if not exists(select * from request where id = request_id_proc and status = 'pending') then
+        raise exception 'The request for the given offer is not in pending status';
+    end if;
+    update offer set status = 'accepted' where id = offer_id;
+    update offer set status = 'rejected' where id != offer_id and offer.request_id = request_id_proc;
+    update request set status = 'accepted' where id = request_id_proc;
+    commit;
+end;
+$$;
+}}}
+create or replace procedure public_schema.finish_shift(
+    in driver_id int4
+)
+    language plpgsql as
+$$
+declare
+    num_of_shifts int4;
+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;
+
+    num_of_shifts := (select count(*) from driver_vehicle where id_driver = driver_id and time_to is null);
+
+    if (num_of_shifts != 1) then
+        raise exception 'Driver has not started a shift';
+    end if;
+
+    update driver_vehicle
+    set time_to=now()
+    where id_driver = driver_id
+      and time_to is null;
+    commit;
+end;
+$$;
+{{{
 == Функции и тригери
+
+***Функција и тригер за проверка на достапност на возач***
+
+Функцијата ride_assign враќа тригер кој фрла исклучок во случај возачот да е зафатен со друго возење во моментот на доделување на возач. Тригерот се извршува пред внес на запис во табелата Ride.
+
+{{{
+create or replace function ride_assign() returns trigger as
+$ride_assign$
+begin
+    if exists(select * from ride where ride.status = 'in_progress' and ride.driver_user_id = NEW.driver_user_id) then
+        raise exception 'Driver not free';
+    end if;
+    return NEW;
+end;
+$ride_assign$ language plpgsql;
+
+create trigger ride_assign before insert on ride
+    for each row execute function ride_assign();
+}}}
 
 {{{
@@ -429 +563 @@
     for each row
 execute function check_active_shift();
+}}}
+
+{{{
+create or replace function prevent_price_manipulation()
+    returns trigger as
+$prevent_price_manipulation$
+begin
+    if OLD.status in ('accepted', 'completed') and NEW.price <> OLD.price then
+        raise exception 'Price cannot be modified once the offer is accepted or completed. Current status: %', OLD.status;
+    end if;
+
+    if NEW.price <= 0 then
+        raise exception 'Price must be a positive value';
+    end if;
+
+    return NEW;
+end;
+$prevent_price_manipulation$ language plpgsql;
 }}}
 
@@ -531 +683 @@
 execute function prevent_completed_offer_downgrade();
 }}}
-
+{{{
+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 enforce_offer_validity()
+    returns trigger as
+$enforce_offer_validity$
+begin
+    if OLD.status = 'pending' and NEW.status = 'accepted' then
+      if (NOW() - OLD.created_at) > interval '5 minutes' then
+            NEW.status := 'expired';
+            raise notice 'Offer % has expired and cannot be accepted.', OLD.id;
+            return NEW;
+        end if;
+    end if;
+
+    return NEW;
+end;
+$enforce_offer_validity$ language plpgsql;
+
+create trigger trg_enforce_offer_validity
+    before update on offer
+    for each row
+    execute function enforce_offer_validity();
+}}}