· 4 years ago · Jun 08, 2021, 11:52 AM
1/* Маршрут */
2CREATE TABLE Route
3(
4 id_route serial PRIMARY KEY, --id маршрута
5 route_distance INT, --дистанция
6 route_route text, --маршрут (пункт отправления-пункт назначения)
7 route_time time, --время в пути
8 route_Log text --кол-во поездов на данном маршруте
9);
10
11/* Информация о водителе */
12CREATE TABLE Driver
13(
14 id_driver serial PRIMARY KEY, --id водителя
15 driver_name text, --имя водителя
16 driver_phone text, --телефон водителя
17 driver_date date, --дата выхода (пункт отправления-пункт назначения)
18 driver_route INT REFERENCES Route (id_route) ON DELETE CASCADE --номер маршрута
19);
20
21/* Информация о поезде */
22CREATE TABLE Train
23(
24 id_train serial PRIMARY KEY, --id поезда
25 train_wagon INT, --количество вагонов
26 train_name text, --название маршрута
27 train_places INT, --кол-во занятых мест в поезде (кол-во пассажиров)
28 train_id_route INT REFERENCES Route (id_route) ON DELETE CASCADE, --маршрут поезда
29 train_driver INT REFERENCES Driver (id_driver) ON DELETE CASCADE
30);
31
32/* Информация о тарифе */
33CREATE TABLE Rate
34(
35 id_rate serial PRIMARY KEY, --id тарифа
36 rate_name text, --название тарифа
37 rate_cost money, --стоимость
38 rate_train INT REFERENCES Train (id_train) ON DELETE CASCADE, --id поезда с данным тарифом (пункт отправления-пункт назначения)
39 rate_food BOOL --номер маршрута
40);
41
42/* Логи маршрутов */
43CREATE TABLE logs
44(
45 log_id serial PRIMARY KEY,
46 log TEXT
47)
48
49ALTER TABLE Route
50ADD CONSTRAINT unique_route UNIQUE (route_route);
51ALTER TABLE Driver
52ADD CONSTRAINT unique_phone UNIQUE (driver_phone);
53
54
55/* (2) a. Вывод количества поездов с маршрутом Москва-Нижний (2)*/
56SELECT route_route, --маршрут
57SUM (CASE
58 WHEN train_id_route = 2
59 THEN
60 1
61 ELSE
62 0
63 END
64) AS route_train
65
66FROM
67 Route, Train
68WHERE
69 train_id_route = id_route AND id_route = 2
70GROUP BY route_route
71
72/* b. Выводит маршруты с количеством поездов */
73CREATE VIEW public.route_train AS
74SELECT DISTINCT
75id_route, route_distance, route_route, route_time, (SELECT COUNT(*) FROM Train WHERE train_id_route = id_route) AS quantity
76FROM Route, Train WHERE id_route = train_id_route;
77/* Сортируем по количеству поездов*/
78SELECT * FROM route_train
79ORDER BY quantity
80
81/* c. Выборка поездов с кол-ом мест > 0.5 с водителями и маршрутом ( Запрос, содержащий коррелированные и некоррелированные
82подзапросы в разделах SELECT, FROM и WHERE (в каждом хотя бы по
83одному))*/
84SELECT id_train, train_places, train_name,
85(SELECT driver_name FROM Driver WHERE id_driver = train_driver),
86(SELECT route_route FROM Route WHERE id_route = train_id_route)
87FROM (SELECT * FROM Train WHERE train_driver != 0) AS needed_driver
88WHERE train_places > (SELECT sum(train_places)/count(train_places) FROM Train);
89
90/* d. Максимальное кол-во мест больше 600 (Многотабличный запрос, содержащий группировку записей,
91агрегативные функции и параметр, используемый в разделе HAVING)*/
92SELECT train_id_route, train_name, max(train_places), route_route FROM Train, Route
93WHERE id_route = train_id_route
94GROUP BY train_id_route, route_route, train_name
95HAVING (max(train_places) <=1000);
96
97/* e. Поезда и водители поездов с маршрутом Рязань-Сочи (Запрос, содержащий предикат ANY(SOME) или ALL)*/
98SELECT train_name, train_driver FROM Train
99WHERE train_id_route = ANY
100(SELECT id_route FROM Route WHERE route_route = 'Рязань-Сочи')
101
102/* (3) индексы для увеличения скорости выполнения запросов*/
103CREATE INDEX i_route_route
104ON Route USING btree(route_route);
105CREATE INDEX i_route_distance
106ON Route USING btree(route_distance);
107CREATE INDEX i_route_time
108ON Route USING btree(route_time);
109
110CREATE INDEX i_train_name
111ON Train USING btree(train_name);
112CREATE INDEX i_train_wagon
113ON Train USING btree(train_wagon);
114CREATE INDEX i_train_places
115ON Train USING btree(train_places);
116CREATE INDEX i_train_id_route
117ON Train USING btree(train_id_route);
118CREATE INDEX i_train_driver
119ON Train USING btree(train_driver);
120
121CREATE INDEX i_driver_name
122ON Driver USING btree(driver_name);
123CREATE INDEX i_driver_date
124ON Driver USING btree(driver_date);
125CREATE INDEX i_driver_route
126ON Driver USING btree(driver_route);
127
128CREATE INDEX i_rate_name
129ON Rate USING btree(rate_name);
130CREATE INDEX i_rate_cost
131ON Rate USING btree(rate_cost);
132CREATE INDEX i_rate_train
133ON Rate USING btree(rate_train);
134CREATE INDEX i_rate_food
135ON Rate USING btree(rate_food);
136
137
138/* (4) Триггеры ( В таблице (в соответствии с вариантом) предусмотреть поле,
139которое заполняется автоматически по срабатыванию триггера
140при добавлении, обновлении и удалении данных, иметь
141возможность продемонстрировать работу триггера при работе
142приложения. Триггеры должны обрабатывать только те записи,
143которые были добавлены, изменены или удалены в ходе текущей
144операции (транзакции). )*/
145/* для добавления маршрута */
146CREATE OR REPLACE FUNCTION route_added()
147RETURNS trigger
148LANGUAGE plpgsql
149AS $function$
150BEGIN
151UPDATE Route SET route_log = (CONCAT(Route.route_log, CONCAT(CONCAT(transaction_timestamp(), ' New route added: '), NEW.route_route)))
152WHERE id_route = NEW.id_route;
153INSERT INTO logs (log) VALUES (CONCAT(transaction_timestamp(), CONCAT(' New route added: ', NEW.route_route)));
154RETURN NEW;
155END;
156$function$;
157CREATE TRIGGER tr_route_added
158AFTER INSERT ON Route
159FOR EACH ROW EXECUTE PROCEDURE route_added();
160
161/* для изменения маршрута route_route*/
162CREATE OR REPLACE FUNCTION route_changed()
163RETURNS TRIGGER
164AS $$
165BEGIN
166UPDATE Route SET route_log = (CONCAT(Route.route_log, CONCAT(CONCAT(transaction_timestamp(), ' The route is chanched. New route:'), NEW.route_route)))
167WHERE id_route = NEW.id_route;
168INSERT INTO logs (log) VALUES (CONCAT(transaction_timestamp(), CONCAT(' The route is chanched. New route: ', NEW.route_route)));
169RETURN NEW;
170END;
171$$ LANGUAGE 'plpgsql';
172CREATE TRIGGER tr_route_changed
173AFTER UPDATE OF route_route ON Route
174FOR EACH ROW EXECUTE PROCEDURE route_changed();
175
176/* для удаления маршрута*/
177CREATE OR REPLACE FUNCTION public.route_deleted()
178RETURNS trigger
179LANGUAGE plpgsql
180AS $function$
181BEGIN
182INSERT INTO logs (log) VALUES (concat(transaction_timestamp(), concat(' The route deleted: ', old.id_route::TEXT)));
183RETURN NEW;
184END;
185$function$;
186CREATE TRIGGER tr_route_deleted
187BEFORE DELETE ON Route
188FOR EACH ROW EXECUTE PROCEDURE route_deleted();
189
190
191/* (5) Операции добавления, удаления и обновления реализовать в виде
192хранимых процедур (с параметрами) хотя бы для одной таблицы;
193для остальных допустимо использовать возможности связывания
194полей ввода в приложении с полями БД. */
195/* добавление маршрута */
196CREATE OR REPLACE FUNCTION public.add_route(_distance int, _route text, _time time)
197RETURNS boolean
198LANGUAGE plpgsql
199AS $function$ DECLARE
200return_val BOOLEAN := true;
201route_count integer;
202BEGIN
203if (_distance IS null)
204THEN
205raise EXCEPTION 'Distance can not be an empty field!';
206return_val := false;
207END if;
208if (_route IS null)
209THEN
210raise EXCEPTION 'Route can not be an empty field!';
211return_val := false;
212END if;
213if (_time IS null)
214THEN
215raise EXCEPTION 'Time can not be an empty field!';
216return_val := false;
217END if;
218
219BEGIN
220SELECT count(*) INTO route_count FROM Route WHERE route_route = _route;
221END;
222if (route_count > 0) THEN
223raise EXCEPTION 'Route with this way already exists!';
224return_val := false;
225END if;
226
227if (return_val = TRUE) THEN
228INSERT INTO Route (route_distance, route_route, route_time)
229VALUES (_distance, _route, _time);
230END if;
231RETURN return_val;
232END;
233$function$;
234
235/* добавление водителя */
236CREATE OR REPLACE FUNCTION public.add_driver(_name text, _date date, _phone text, _route text)
237RETURNS boolean
238LANGUAGE plpgsql
239AS $function$ declare
240return_val boolean := true;
241driver_count integer := 0;
242routeid int = -1;
243route_cursor CURSOR (firstCursor TEXT) FOR SELECT id_route FROM Route
244WHERE route_route = firstCursor;
245
246BEGIN
247OPEN route_cursor (firstCursor := _route);
248FETCH route_cursor INTO routeid;
249if (routeid = -1)
250THEN
251return_val = false;
252end IF;
253
254if (_name IS null)
255THEN
256raise EXCEPTION 'Name can not be an empty field!';
257return_val := false;
258END if;
259if (_date IS null)
260THEN
261raise EXCEPTION 'Date can not be an empty field!';
262return_val := false;
263END if;
264if (_phone IS null)
265THEN
266raise EXCEPTION 'Phone can not be an empty field!';
267return_val := false;
268END if;
269
270
271BEGIN
272SELECT count(*)
273INTO driver_count
274FROM Driver
275WHERE driver_phone = _phone;
276end;
277if (driver_count > 0)
278THEN
279raise EXCEPTION 'Driver with this phone number already exists!';
280return_val := false;
281END if;
282driver_count := 0;
283
284if (return_val = true)
285THEN
286insert INTO Driver (driver_name, driver_date, driver_phone, driver_route) values (_name, _date, _phone, routeid);
287end if;
288return return_val;
289END; $function$
290
291/* Добавление поезда */
292CREATE OR REPLACE FUNCTION public.add_train(_wagon INT, _name text, _places INT, _id_route text, _driver text)
293RETURNS boolean
294LANGUAGE plpgsql
295AS $function$ declare
296return_val boolean := true;
297routeid int = -1;
298driverid int = -1;
299route_cursor CURSOR (firstCursor TEXT) FOR SELECT id_route FROM Route
300WHERE route_route = firstCursor;
301driver_cursor CURSOR (secondCursor TEXT) FOR SELECT id_driver FROM Driver
302WHERE driver_name = secondCursor;
303
304BEGIN
305OPEN route_cursor (firstCursor := _id_route);
306FETCH route_cursor INTO routeid;
307OPEN driver_cursor (secondCursor := _driver);
308FETCH driver_cursor INTO driverid;
309
310if (driverid = -1)
311then
312return_val = false;
313end if;
314if (routeid = -1)
315THEN
316return_val = false;
317end IF;
318
319if (_wagon IS null)
320THEN
321raise EXCEPTION 'Wagon can not be an empty field!';
322return_val := false;
323END if;
324if (_name IS null)
325THEN
326raise EXCEPTION 'Name can not be an empty field!';
327return_val := false;
328END if;
329if (_places IS null)
330THEN
331raise EXCEPTION 'Places can not be an empty field!';
332return_val := false;
333END if;
334
335if (return_val = true)
336THEN
337insert INTO Train (train_wagon, train_name, train_places, train_id_route, train_driver) values (_wagon, _name, _places, routeid, driverid);
338end if;
339return return_val;
340END; $function$;
341
342/* Добавление тарифа */
343CREATE OR REPLACE FUNCTION public.add_rate(_name text, _cost money, _train text, _food BOOL)
344RETURNS boolean
345LANGUAGE plpgsql
346AS $function$ declare
347return_val boolean := true;
348trainid int = -1;
349train_cursor CURSOR (firstCursor TEXT) FOR SELECT id_train FROM Train
350WHERE train_name = firstCursor;
351
352BEGIN
353OPEN train_cursor (firstCursor := _train);
354FETCH train_cursor INTO trainid;
355if (trainid = -1)
356THEN
357return_val = false;
358end IF;
359
360if (_name IS null)
361THEN
362raise EXCEPTION 'Name can not be an empty field!';
363return_val := false;
364END if;
365if (_cost IS null)
366THEN
367raise EXCEPTION 'Cost can not be an empty field!';
368return_val := false;
369END if;
370if (_food IS null)
371THEN
372raise EXCEPTION 'Food can not be an empty field!';
373return_val := false;
374END if;
375
376if (return_val = true)
377THEN
378insert INTO Rate (rate_name, rate_cost, rate_train, rate_food) values (_name, _cost, trainid, _food);
379end if;
380return return_val;
381END; $function$;
382
383/* изменение маршрута */
384CREATE OR REPLACE FUNCTION public.change_route(_oldroute text, _newdistance text, _newroute text, _newtime time)
385RETURNS boolean
386LANGUAGE plpgsql
387AS $function$ declare
388return_val boolean := true;
389route_check int;
390BEGIN
391
392begin
393select count(*)
394into route_check
395from Route
396where route_route = _newroute;
397end;
398
399if (_oldroute = _newroute)
400THEN
401route_check = route_check -1 ;
402end if;
403if (route_check > 0)
404then
405raise exception ' Route with that way already exists!';
406return_val = false;
407end if;
408if (return_val = true)
409THEN
410update Route
411set route_route = _newroute,
412route_distance = _newdistance,
413route_time = _newtime
414where route_route = _oldroute;
415end if;
416return return_val;
417END; $function$
418
419/* изменение водителя */
420CREATE OR REPLACE FUNCTION public.change_driver(_oldphone text, _newname text, _newdate date, _newphone int, _newroute text)
421RETURNS boolean
422LANGUAGE plpgsql
423AS $function$ declare
424return_val boolean := true;
425driver_check int;
426new_routeid int = -1;
427route_cursor CURSOR (firstCursor TEXT) FOR SELECT id_route FROM Route
428WHERE route_route = firstCursor FOR UPDATE;
429BEGIN
430OPEN route_cursor (firstCursor := _newroute);
431FETCH route_cursor INTO new_routeid;
432if (new_routeid = -1)
433THEN
434return_val = false;
435end if;
436
437begin
438select count(*)
439into driver_check
440from Driver
441where driver_phone = _newphone;
442end;
443
444if (_oldphone = _newphone)
445THEN
446driver_check = driver_check -1 ;
447end if;
448if (driver_check > 0)
449then
450raise exception ' Driver with that phone already exists!';
451return_val = false;
452end if;
453if (return_val = true)
454THEN
455UPDATE Driver
456SET driver_name = _newname,
457driver_date = _newdate,
458driver_phone = _newphone,
459driver_route = new_routeid
460WHERE driver_phone = _oldphone;
461END IF;
462RETURN return_val;
463END; $function$
464
465/* изменение тарифа */
466CREATE OR REPLACE FUNCTION public.change_rate(_id integer, _newname text, _newcost money, _newtrain text, _newfood BOOL)
467RETURNS boolean
468LANGUAGE plpgsql
469AS $function$ declare
470return_val boolean := true;
471new_trainid int = -1;
472train_cursor CURSOR (firstCursor TEXT) FOR SELECT id_train FROM Train
473WHERE train_name = firstCursor FOR UPDATE;
474BEGIN
475OPEN train_cursor (firstCursor := _newtrain);
476FETCH train_cursor INTO new_trainid;
477if (new_trainid = -1)
478THEN
479return_val = false;
480end IF;
481if (return_val = true)
482THEN
483update Rate
484set rate_name = _newname,
485rate_cost = _newcost,
486rate_train = new_trainid,
487rate_food = _newfood
488where id_rate = _id;
489end if;
490return return_val;
491END; $function$
492
493/* удаление маршрута */
494CREATE OR REPLACE FUNCTION public.delete_route(_route text)
495RETURNS boolean
496LANGUAGE plpgsql
497AS $function$ declare
498return_val boolean := true;
499route_check int;
500BEGIN
501select count(*)
502into route_check
503from Route
504where route_route = _route;
505if (route_check = 0)
506then
507raise exception ' No route with this way :( ';
508return_val = false;
509end if;
510if (route_check = 1)
511then
512DELETE from Route
513where route_route = _route;
514end if;
515return return_val;
516END; $function$
517
518/* удаление водителя */
519CREATE OR REPLACE FUNCTION public.delete_driver(_phone integer)
520RETURNS boolean
521LANGUAGE plpgsql
522AS $function$ declare
523return_val boolean := true;
524driver_check int;
525BEGIN
526select count(*)
527into driver_check
528from Driver
529where driver_phone = _phone;
530if (driver_check = 0)
531then
532raise exception ' No driver with this phone number!';
533return_val = false;
534end if;
535if (driver_check = 1)
536then
537DELETE from Driver
538where driver_phone = _phone;
539end if;
540return return_val;
541END; $function$
542
543/* удаление поезда */
544CREATE OR REPLACE FUNCTION public.delete_train(_id integer)
545RETURNS boolean
546LANGUAGE plpgsql
547AS $function$ declare
548return_val boolean := true;
549train_check int;
550BEGIN
551select count(*)
552into train_check
553from Train
554where id_train = _id;
555if (train_check = 0)
556then
557raise exception 'No train with this id!';
558return_val = false;
559end if;
560if (train_check = 1)
561then
562DELETE from train
563where id_train = _id;
564end if;
565return return_val;
566END; $function$
567
568/* удаление тарифа */
569CREATE OR REPLACE FUNCTION public.delete_rate(_id integer)
570RETURNS boolean
571LANGUAGE plpgsql
572AS $function$ declare
573return_val boolean := true;
574rate_check int;
575BEGIN
576select count(*)
577into rate_check
578from Rate
579where id_rate = _id;
580if (rate_check = 0)
581then
582raise exception 'No rate with this id!';
583return_val = false;
584end if;
585if (rate_check = 1)
586then
587DELETE from train
588where id_rate = _id;
589end if;
590return return_val;
591END; $function$
592
593
594/* (6) Реализовать отдельную хранимую процедуру, состоящую из
595нескольких отдельных операций в виде единой транзакции,
596которая при определенных условиях может быть зафиксирована
597или откатана. */
598CREATE OR REPLACE PROCEDURE public.commit_rollback_route()
599LANGUAGE plpgsql
600AS $function$ declare
601BEGIN
602DELETE FROM Route WHERE route_route = 'Чебоксары-Санкт-Петербург';
603COMMIT;
604DELETE FROM Route WHERE route_route = 'Чебоксары-Санкт-Петербург';
605ROLLBACK;
606END; $function$
607
608/* (7) В триггере или хранимой процедуре реализовать курсор на
609обновления отдельных данных. */
610CREATE OR REPLACE FUNCTION public.change_train(_id integer, _newname text, _newwagon int, _newplaces int, _newroute text, _newdriver text)
611RETURNS boolean
612LANGUAGE plpgsql
613AS $function$ declare
614return_val boolean := true;
615new_routeid int = -1;
616new_driverid int = -1;
617route_cursor CURSOR (firstCursor TEXT) FOR SELECT id_route FROM Route
618WHERE route_route = firstCursor FOR UPDATE;
619driver_cursor CURSOR (secondCursor TEXT) FOR SELECT id_driver FROM Driver
620WHERE driver_name = secondCursor FOR UPDATE;
621BEGIN
622OPEN route_cursor (firstCursor := _newroute);
623FETCH route_cursor INTO new_routeid;
624OPEN driver_cursor (secondCursor := _newdriver);
625FETCH driver_cursor INTO new_driverid;
626if (new_driverid = -1)
627THEN
628return_val = false;
629end IF;
630if (new_routeid = -1)
631THEN
632return_val = false;
633end if;
634if (return_val = true)
635THEN
636update Train
637set train_wagon = _newwagon,
638train_name = _newname,
639train_places = _newplaces,
640train_id_route = new_routeid,
641train_driver = new_driverid
642where id_route = _id;
643end if;
644return return_val;
645END; $function$
646
647/* (8) В запросе (из пункта 2 или в дополнительном к тому перечню)
648использовать собственную скалярную функцию, а в хранимой
649процедуре – векторную (или табличную) функцию. Функции
650сохранить в базе данных. */
651/* скалярная функция вывода поезда и его маршрута */
652CREATE OR REPLACE FUNCTION public.get_routename(_id integer)
653RETURNS text
654LANGUAGE plpgsql
655AS $function$ DECLARE
656myRoute text;
657BEGIN
658SELECT route_route
659INTO myRoute
660FROM Route
661where id_route = _id;
662RETURN myRoute;
663END; $function$
664SELECT id_train, train_name, get_routename(train_id_route)
665FROM Train;
666
667/* табличная функция вывода маршрутов с дистанцией больше 900 км. */
668CREATE OR REPLACE FUNCTION public.get_900_route()
669RETURNS TABLE(_id integer, _route text, _distance text, _time time)
670LANGUAGE sql
671AS $function$
672SELECT id_route, route_route, route_distance, route_time FROM Route WHERE route_distance >= 900;
673$function$