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-то на возачот е валидно, доколку не постои фрла соодветен исклучок.

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

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

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

Секој корисник смее да остави максимум една оценка за секое возење. Функцијата враќа тригер кој доколку во табелата 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();

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