Changes between Version 29 and Version 30 of DatabaseProgramming


Ignore:
Timestamp:
06/16/26 01:57:29 (26 hours ago)
Author:
231119
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v29 v30  
    1111{{{
    1212create or replace procedure start_shift(
    13     in driver_id int4,
    14     in vin_vehicle varchar(17)
     13    in v_driver_id int4,
     14    in v_vin_vehicle varchar(17)
    1515)
    1616    language plpgsql as
    1717$$
    1818begin
    19     if not exists(select user_id from driver where user_id = driver_id) then
    20         raise exception 'Driver with id % does not exist', driver_id;
    21     end if;
    22 
    23     if not exists(select vin from vehicle where vin = vin_vehicle) then
    24         raise exception 'Vehicle with VIN % does not exist', vin_vehicle;
     19    if not exists(select user_id from driver where user_id = v_driver_id) then
     20        raise exception 'Driver with id % does not exist', v_driver_id;
     21    end if;
     22
     23    if not exists(select vin from vehicle where vin = v_vin_vehicle) then
     24        raise exception 'Vehicle with VIN % does not exist', v_vin_vehicle;
    2525    end if;
    2626
    2727    insert into driver_vehicle(vin_vehicle, id_driver, time_from, time_to)
    28     values (vin_vehicle, driver_id, now(), null);
     28    values (v_vin_vehicle, v_driver_id, now(), null);
    2929    commit;
    3030end;
    … …  
    3838{{{
    3939create or replace procedure make_request(
    40     out request_id int4,
    41     in customer_id int4,
    42     in start_latitude double precision,
    43     in start_longitude double precision,
    44     in end_latitude double precision,
    45     in end_longitude double precision,
    46     in number_of_adult_passengers int4 default 1,
    47     in number_of_children int4 default 0,
    48     in female_driver boolean default false,
    49     in luggage boolean default false,
    50     in luggage_count int4 default 0,
    51     in baby_seat_count int4 default 0
     40    in v_customer_id int4,
     41    in v_start_latitude double precision,
     42    in v_start_longitude double precision,
     43    in v_end_latitude double precision,
     44    in v_end_longitude double precision,
     45    in v_number_of_adult_passengers int4 default 1,
     46    in v_number_of_children int4 default 0,
     47    in v_female_driver boolean default false,
     48    in v_luggage boolean default false,
     49    in v_luggage_count int4 default 0,
     50    in v_baby_seat_count int4 default 0
    5251)
    5352    language plpgsql as
    … …  
    5655    if not exists(select user_id
    5756                  from customer c
    58                   where c.user_id = customer_id)
     57                  where c.user_id = v_customer_id)
    5958    then
    60         raise exception 'Customer with id % does not exist', customer_id;
    61     end if;
    62 
    63     if start_latitude < -90.0 or start_latitude > 90.0
    64         or start_longitude < -180.0 or start_longitude > 180.0
    65         or end_latitude < -90.0 or end_latitude > 90.0
    66         or end_longitude < -180.0 or end_longitude > 180.0
     59        raise exception 'Customer with id % does not exist', v_customer_id;
     60    end if;
     61
     62    if v_start_latitude < -90.0 or v_start_latitude > 90.0
     63        or v_start_longitude < -180.0 or v_start_longitude > 180.0
     64        or v_end_latitude < -90.0 or v_end_latitude > 90.0
     65        or v_end_longitude < -180.0 or v_end_longitude > 180.0
    6766    then
    6867        raise exception 'Invalid location values';
    6968    end if;
    7069
    71     if number_of_adult_passengers < 0 then
     70    if v_number_of_adult_passengers < 0 then
    7271        raise exception 'Number of adult passengers cannot be negative';
    7372    end if;
    7473
    75     if number_of_children < 0 then
     74    if v_number_of_children < 0 then
    7675        raise exception 'Number of children cannot be negative';
    7776    end if;
    7877
    79     if number_of_adult_passengers + number_of_children < 0 then
     78    if v_number_of_adult_passengers + v_number_of_children < 0 then
    8079        raise exception 'There must be at least 1 passenger';
    8180    end if;
    8281
    83     if baby_seat_count < 0 then
     82    if v_baby_seat_count < 0 then
    8483        raise exception 'Baby seat count cannot be negative';
    8584    end if;
    8685
    87     if luggage = false and luggage_count > 0 then
     86    if v_luggage = false and v_luggage_count > 0 then
    8887        raise exception 'Luggage count cannot be greater than zero if the luggage flag is set to false.';
    8988    end if;
    9089
    91     if luggage = true and luggage_count <= 0 then
     90    if v_luggage = true and v_luggage_count <= 0 then
    9291        raise exception 'Invalid luggage count parameters';
    9392    end if;
    9493
    95     if start_latitude = end_latitude and start_longitude = end_longitude then
     94    if v_start_latitude = v_end_latitude and v_start_longitude = v_end_longitude then
    9695        raise exception 'Start and destination location cannot be the same';
    9796    end if;
    9897
    9998    insert into request(customer_user_id,
    100                                       start_latitude,
    101                                       start_longitude,
    102                                       end_latitude,
    103                                       end_longitude,
    104                                       timestamp,
    105                                       number_of_adult_passengers,
    106                                       number_of_children,
    107                                       status,
    108                                       female_driver,
    109                                       luggage,
    110                                       luggage_count,
    111                                       baby_seat_count)
    112     values (customer_id,
    113             make_request.start_latitude,
    114             make_request.start_longitude,
    115             make_request.end_latitude,
    116             make_request.end_longitude,
     99                        start_latitude,
     100                        start_longitude,
     101                        end_latitude,
     102                        end_longitude,
     103                        timestamp,
     104                        number_of_adult_passengers,
     105                        number_of_children,
     106                        status,
     107                        female_driver,
     108                        luggage,
     109                        luggage_count,
     110                        baby_seat_count)
     111    values (v_customer_id,
     112            v_start_latitude,
     113            v_start_longitude,
     114            v_end_latitude,
     115            v_end_longitude,
    117116            now(),
    118             make_request.number_of_adult_passengers,
    119             make_request.number_of_children,
     117            v_number_of_adult_passengers,
     118            v_number_of_children,
    120119            'pending',
    121             make_request.female_driver,
    122             make_request.luggage,
    123             make_request.luggage_count,
    124             make_request.baby_seat_count)
    125     returning id into request_id;
     120            v_female_driver,
     121            v_luggage,
     122            v_luggage_count,
     123            v_baby_seat_count);
    126124    commit;
    127125end;
    … …  
    135133{{{
    136134create or replace procedure add_waypoint(
    137     in request_id int4,
    138     in latitude int4,
    139     in longitude int4
     135    in v_request_id int4,
     136    in v_latitude int4,
     137    in v_longitude int4
    140138)
    141139    language plpgsql as
    142140$$
    143141declare
    144     prev_seqno int4;
    145 begin
    146     if not exists(select id from request where id = request_id) then
    147         raise exception 'Request with id % does not exist', request_id;
    148     end if;
    149 
    150     if latitude < -90.0 or latitude > 90.0
    151         or longitude < -180.0 or longitude > 180.0 then
     142    v_prev_seqno int4;
     143begin
     144    if not exists(select id from request where id = v_request_id) then
     145        raise exception 'Request with id % does not exist', v_request_id;
     146    end if;
     147
     148    if v_latitude < -90.0 or v_latitude > 90.0
     149        or v_longitude < -180.0 or v_longitude > 180.0 then
    152150        raise exception 'Invalid waypoint coordinates';
    153151    end if;
    … …  
    155153    if exists(select id
    156154              from waypoints w
    157               where w.latitude = add_waypoint.latitude
    158                 and w.longitude = add_waypoint.longitude
    159                 and w.request_id = add_waypoint.request_id) then
     155              where w.latitude = v_latitude
     156                and w.longitude = v_longitude
     157                and w.request_id = v_request_id) then
    160158        raise exception 'Waypoint already added';
    161159    end if;
    162160
    163161    select count(*)
    164     into prev_seqno
     162    into v_prev_seqno
    165163    from waypoints w
    166     where w.request_id = add_waypoint.request_id;
     164    where w.request_id = v_request_id;
    167165
    168166    insert into waypoints(latitude, longitude, seqno, request_id)
    169     values (add_waypoint.latitude, add_waypoint.longitude, prev_seqno + 1, add_waypoint.request_id);
     167    values (v_latitude, v_longitude, v_prev_seqno + 1, v_request_id);
    170168    commit;
    171169end;
    … …  
    179177{{{
    180178create or replace procedure start_ride(
    181     in offer_id int4,
    182     out ride_id int4
     179    in v_offer_id int4,
     180    out v_ride_id int4
    183181)
    184182    language plpgsql as
    185183$$
    186184declare
    187     v_request_id      int4;
    188     start_latitude  double precision;
    189     start_longitude double precision;
    190     driver_id       int4;
    191     v_vehicle_vin     varchar(17);
    192 --     v_status          ride_status;
    193 begin
    194     if not exists(select id from offer where id = offer_id) then
    195         raise exception 'Offer with id % does not exist', offer_id;
     185    v_request_id  int4;
     186    v_vehicle_vin varchar(17);
     187    v_driver_id   int4;
     188begin
     189    if not exists(select id from offer where id = v_offer_id) then
     190        raise exception 'Offer with id % does not exist', v_offer_id;
    196191    end if;
    197192
    198193    select o.request_id,
    199            r.start_latitude,
    200            r.start_longitude
     194           o.driver_user_id
    201195    into
    202         request_id,
    203         start_latitude,
    204         start_longitude
     196        v_request_id,
     197        v_driver_id
    205198    from offer o
    206199             join request r on o.request_id = r.id
    207     where o.id = offer_id;
    208 
    209     select d.user_id, dc.vin_vehicle
    210     into driver_id,
    211         v_vehicle_vin
    212     from driver d
    213              join driver_vehicle dc on d.user_id = dc.id_driver
    214     where dc.time_to is null
    215       and d.latitude is not null
    216       and d.longitude is not null
    217     order by power(start_latitude - d.latitude, 2) + power(start_longitude - d.longitude, 2)
    218     limit 1;
    219 
    220     if driver_id is null then
    221         raise exception 'No active drivers available';
    222     end if;
     200    where o.id = v_offer_id;
     201
     202    v_vehicle_vin := (select vin_vehicle from driver_vehicle where id_driver = v_driver_id);
    223203
    224204    insert into ride(start_time,
    … …  
    234214            0,
    235215            v_vehicle_vin,
    236             driver_id,
     216            v_driver_id,
    237217            v_request_id,
    238218            'in_progress',
    239             start_ride.offer_id);
     219            v_offer_id);
    240220
    241221    update offer
    242222    set status='accepted'
    243     where id = offer_id;
     223    where id = v_offer_id;
    244224
    245225    update request
    … …  
    450430    request_id int4,
    451431    dispatcher_user_id int4,
    452     driver_user_id int4,
    453432    price numeric(19, 2),
    454433    currency_catalog_id int4,
    … …  
    459438AS
    460439$$
     440declare
     441    v_driver_user_id  int4;
     442    v_start_latitude  double precision;
     443    v_start_longitude double precision;
    461444begin
    462445    if not exists(select * from request where request.id = request_id and request.status = 'pending') then
    … …  
    470453    end if;
    471454
     455    select start_latitude, start_longitude
     456    into v_start_latitude, v_start_longitude
     457    from request
     458    where id = request_id;
     459
     460    select d.user_id, dc.vin_vehicle
     461    into v_driver_user_id
     462    from driver d
     463             join driver_vehicle dc on d.user_id = dc.id_driver
     464    where dc.time_to is null
     465      and d.latitude is not null
     466      and d.longitude is not null
     467    order by power(v_start_latitude - d.latitude, 2) + power(v_start_longitude - d.longitude, 2)
     468    limit 1;
     469
     470    if (select company_id from employmenthistory where employee_user_id = dispatcher_user_id and end_date is null) !=
     471       (select company_id from employmenthistory where employee_user_id = v_driver_user_id and end_date is null) then
     472        raise exception 'Dispatcher and driver company mismatch';
     473    end if;
     474
    472475    insert into offer(status, created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id,
    473476                      eta, customer_user_id)
    474     values ('pending', now(), create_offer.request_id, create_offer.dispatcher_user_id, create_offer.driver_user_id,
     477    values ('pending', now(), create_offer.request_id, create_offer.dispatcher_user_id, v_driver_user_id,
    475478            create_offer.price, create_offer.currency_catalog_id, create_offer.eta, create_offer.customer_user_id);
    476479    commit;