Database Programming

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

Процедури

Процедура за почеток на смена на возач

Процедурата проверува дали постои id-то на возачот и VIN бројот кај возилото и фрла соодветен исклучот. Доколку возачот и возилото се валидни се додава нов запис за смена во табелата Driver_Vehicle.

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;
$$;

Процедура за завршување на смена на возач

Процедурата проверува дали постои id-то на возачот, доколку не постои фрла соодветен исклучок. Потоа се проверува дали возачот има точно една активна смена која не е завршена. На крај се ажурира записот во табелата Driver_Vehicle на тој начин што се поставува тековното време во колоната time_to.

create or replace procedure 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;
$$;

Процедура за вработување на возач во компанија

Процедурата проверува дали id-то на возачот е валидно, доколку не постои фрла соодветен исклучок.

create or replace procedure employ_driver(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
                   select 1
                   from driver
                   where user_id=id_employee)
               then
                   raise exception 'Driver does not exist';
        else
               insert into
                   employmenthistory (employee_user_id, start_date, end_date, company_id)
                   values (id_employee,current_date, null,id_company);
        end if;
    end;
$$;

Процедура за вработување на диспечер во компанија

Процедурата проверува дали id-то на диспечерот е валидно, доколку не постои фрла соодветен исклучок.

create or replace procedure employ_dispatcher(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
                   select 1
                   from dispatcher
                   where user_id=id_employee)
               then
                   raise notice 'Dispatcher does not exist';
        else
               insert into
                   employmenthistory (employee_user_id, start_date, end_date, company_id)
                   values (id_employee,current_date, null,id_company);
        end if;
    end;
$$;

Процедура за отпуштање/завршување на договор на возач во компанија

Процедурата прави две проверки. Првата проверка ја пребарува табелата Driver за id-то на возачот, доколку истото не постои се фрла соодветен исклучок. Потоа се проверува дали постои соодветен запис во табелата EmploymentHistory кој потврдува дека возачот е тековно вработен во компанијата. На крај се променува записот од табелата EmploymentHistory на тој начин што се става краен датум.

create or replace procedure fire_driver(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
               select 1
               from driver
               where user_id=id_employee)
           then
               raise exception 'Driver does not exist';
       else
           if not exists (
               select 1
               from employmenthistory
               where employee_user_id=id_employee and end_date==null and company_id=id_company)
           then
               raise exception 'Driver does not have an employment';
           else
               update employmenthistory
               set end_date=current_date
               where employee_user_id==id_employee and end_date==null and company_id=id_company;
               raise notice 'Driver is fired';
               commit;
           end if;
       end if;
    end;
$$;

Процедура за отпуштање/завршување на договор на диспечер во компанија

Процедурата прави две проверки. Првата проверка ја пребарува табелата Dispatcher за id-то на диспечарот, доколку истото не постои се фрла соодветен исклучок. Потоа се проверува дали постои соодветен запис во табелата EmploymentHistory кој потврдува дека деспечерот е тековно вработен во компанијата. На крај се променува записот од табелата EmploymentHistory на тој начин што се става краен датум.

create or replace procedure fire_dispatcher(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
               select 1
               from dispatcher
               where user_id=id_employee)
           then
               raise notice 'Dispatcher does not exist';
       else
           if not exists(
               select 1
               from employmenthistory
               where employee_user_id=id_employee and end_date==null and company_id=id_company)
           then
               raise exception 'Dispatcher does not have an employment';
           else
               update employmenthistory
               set end_date=current_date
               where employee_user_id=id_employee and end_date==null and company_id=id_company;
               raise notice 'Dispatcher is fired';
               commit;
           end if;
       end if;
    end;
$$;

Процедура за пишување на оцена

Оваа процедурата се повикува кога корисник испраќа свој коментар и рејтинг за возењето откако ќе пристигне на дестинацијата.

create or replace procedure write_rating(rating numeric, comment text, id_ride int, id_customer int)
language plpgsql
as $$
    begin
        insert into review (rating, comment, ride_id, customer_user_id)
        values (write_rating.rating,write_rating.comment,id_ride,id_customer);
    end;
$$;

Процедура за испраќање порака

Пораките можат да бидат испратени од возачот или патникот и се однесуваат на едно возење.

create or replace procedure write_chat(new_message text, id_ride int, id_user int)
language plpgsql
as $$
    begin
        insert into chatmessage(message, user_id_from, ride_id)
        values (new_message,id_user,id_ride);
    end;
$$;

Процедура за поднесување пријава

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

create or replace procedure write_report(id_ride int, user_id int, new_message text, new_title text,temp_latitude float, temp_long float, new_reason text)
language plpgsql
as $$
    begin
        insert into report(ride_id, customer_user_id, message, title, latitude, longitude, reason)
        values (id_ride,user_id,new_message,new_title,temp_latitude,temp_long,new_reason);
    end;
$$;

Процедура за креирање на барање за превоз

Процедурата make_request се повикува кога корисникот креира ново барање за превоз. Најпрво се проверува дали постои корисникот со даденото customer_id, по што се валидираат координатите за почетната и крајната локација. Дополнително се проверуваат параметрите поврзани со бројот на патници, детски седишта и багаж, при што се фрлаат соодветни исклучоци доколку некоја вредност е невалидна. Исто така, процедурата проверува дали почетната и крајната локација не се исти. Доколку сите проверки се успешни, се додава нов запис во табелата Request со статус pending, а идентификаторот на новокреираното барање се враќа преку излезниот параметар request_id.

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;
$$;

Процедура за додавање на Waypoint на некое барање

Процедурата add_waypoint се повикува кога корисникот сака да додаде попатна точка на постоечко барање за превоз. Најпрво се проверува дали постои барањето со даденото request_id, по што се валидираат координатите на waypoint-от. Дополнително се проверува дали истиот waypoint веќе е додаден за конкретното барање, при што се фрла соодветен исклучок доколку постои дупликат. Потоа се пресметува редниот број (seqno) на новиот waypoint врз основа на бројот на веќе постоечки waypoints за тоа барање. Доколку сите проверки се успешни, се додава нов запис во табелата Waypoints.

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;
$$;

Процедура за зпочнување на возење со такси

Процедурата проверува дали постои id-то на понудата, доколку не постои фрла соодветен исклучок. Потоа се пронаоѓа најблискиот слободен возач кој е на активна смена. На крај се додава нов запис во табелата Ride со статус in_progress, а статусите на понудата и барањето се ажурираат во accepted и in_progress.

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 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;
$$;

Функции и тригери

Функција и тригер за проверка на достапност на возач

Функцијата 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();

Функција и тригер кој спречува два пати доделување на активна смена на ист возач

Функцијата проверува дали постои id-то на возачот и VIN бројот на возилото, при што фрла соодветен исклучок доколку некој од нив не е валиден. Дополнително, се проверува дали возачот е на смена. Тригерот се извршува пред внесување или ажурирање на запис во табелата Driver_Vehicle.

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();

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;

Тригер и функција за проверка на единствена оценка

Секој корисник смее да остави максимум една оценка за секое возење. Функцијата враќа тригер кој доколку во табелата Review постои оценка од корисникот за тоа возење фрла соодветен исклучот. Тригерот се извршува пред внес на запис во табелата Review.

create or replace function check_one_review()
returns trigger
language plpgsql
as $$
    begin
        if 0 < (select count(*)
                from review
                where ride_id=new.ride_id and customer_user_id=new.customer_user_id)
        then
            raise exception 'There is already a review from the same customer for this ride';
        end if;
        return new;
    end;
$$;
create or replace trigger check_valid_review
    before insert on review
    for each row
    execute function check_one_review();

Тригер и функција за проверка на бројот на слободни седишта

Секој корисник при испраќање на барање за превоз напоменува колку патници ќе има (деца, воздрасни и бебиња). Функцијата check_available_seats проверува дали во возилото кое се нуди во понудата има доволно седишта. Функцијата враќа тригер кои се извршува пред внес или промена на запис во табелата Offer.

create or replace function check_available_seats()
returns trigger
language plpgsql
as $$
    begin
        if (select v.passenger_capacity
            from vehicle v
            inner join driver_vehicle dv on v.vin=dv.vin_vehicle
            where dv.id_driver=new.driver_user_id)<(select r.number_of_children+r.number_of_adult_passengers+r.baby_seat_count
                                                    from request r
                                                    inner join offer o on r.id=o.request_id
                                                    where o.id=new.id)
            then
            raise exception 'Not enough seats in the vehicle';
        end if;
        return new;
    end;
$$;
create or replace trigger check_valid_seats
    before insert or update on offer
    for each row
    execute function check_available_seats();

Функција и тригер за спречување на возење со неважечка возачка дозвола

Функцијата го пребарува датумот на истекување на возачката дозвола за соодветниот возач преку поврзување на табелите Driver и DriverLicense. Доколку дозволата е истечена во однос на тековниот датум, се фрла соодветен исклучок. Тригерот се извршува пред внес на нов запис во табелата Ride.

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();

Функција и тригер за спречување на промена на статус на завршена понуда

Функцијата проверува дали претходниот статус на понудата бил 'completed', при што фрла соодветен исклучок доколку се направи обид за негова промена во било кој друг статус. Тригерот се извршува пред ажурирање на запис во табелата Offer.

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();

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();