Database Programming

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

Процедури

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

Процедурата проверува дали 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;
$$;

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

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

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