Практика №3 - Администрирование.md

Практика №3 - Администрирование

Работы в рамках дисциплины:
Клиент-серверные системы управления базами данных
Предыдущая работа:
Практика №2 - Доработка базы данных и оптимизация
Следующая работа:
Практика №4 - Работа с транзакциями
Полный SQL код:
practic3.sql

---

Задание

В реализованную по выбору базу данных добавить:

1. 2 уникальные схемы
2. 2 уникальные функции
3. 2 уникальные процедуры
4. 2 уникальных триггера

Создание схем

CREATE SCHEMA personal_file_staff;
CREATE SCHEMA personal_file_prisoner;
CREATE SCHEMA Nutrition;

CREATE TABLE nutrition.food_intake(
food_intake_id BIGSERIAL NOT NULL PRIMARY KEY,
type INT NOT NULL,
data DATE NOT NULL,
food_sets_id INT NOT NULL
);

CREATE TABLE nutrition.food_sets(
food_sets_id BIGSERIAL NOT NULL PRIMARY KEY,
menu TEXT NOT NULL
);

ALTER TABLE nutrition.food_intake
ADD FOREIGN KEY (food_sets_id) REFERENCES nutrition.food_sets(food_sets_id);

CREATE USER boss PASSWORD '123';
ALTER ROLE boss WITH createrole createdb superuser;

Создание функций

Функция, которая уменьшает срок на указанное количество дней

CREATE FUNCTION amnistia(prisoner bigint, days integer) RETURNS date AS $$
	UPDATE prison_sentence
		SET end_date = end_date - days
		WHERE prisoner = amnistia.prisoner;
	SELECT end_date FROM prison_sentence WHERE prisoner = amnistia.prisoner;
$$ LANGUAGE SQL;

--проверка
SELECT amnistia(1, 1);

Функция, которая пересаживает в другую камеру

CREATE FUNCTION move(prisoner int, camera int) RETURNS bigint AS $$
	UPDATE prisoner
		SET camera = move.camera
		WHERE id = move.prisoner;
	SELECT camera FROM prisoner WHERE prisoner.id = move.prisoner;
$$ LANGUAGE SQL;

SELECT move(1, 1);

Создание своего типа данных

CREATE TYPE inventory_item AS (
    name            varchar(30),
    surname    		  varchar(30),
    camera          bigint
);

Создание процедур

Процедура, которая удаляет заключенного, который отсидел срок

CREATE PROCEDURE redemption()
LANGUAGE SQL
AS $$
DELETE FROM prison_sentence
WHERE prison_sentence.end_date <= clock_timestamp();
DELETE FROM prisoner USING prison_sentence
WHERE prisoner.id NOT IN (SELECT prisoner FROM prison_sentence);
$$
;

Процедура, которая индексирует зарплату сотрудникам на определенный процент

CREATE PROCEDURE index(a real)
LANGUAGE SQL
AS $$
UPDATE posts SET salary = salary*a
$$
;

Создание триггеров

Триггер, который не даёт добавить заключённого в переполненную камеру

--сначала нужна функция
CREATE OR REPLACE FUNCTION error()
	RETURNS trigger AS
	$$BEGIN
	IF (SELECT TRUE FROM prisoner
	INNER JOIN camera ON camera.id = prisoner.camera
	GROUP BY camera.id HAVING camera.num_seat < COUNT(prisoner.id)) then
	RAISE NOTICE 'camera is full';
	ELSE
	RETURN NEW;
	END IF;
	END;
	$$
LANGUAGE 'plpgsql';

--сам триггер
CREATE TRIGGER chek_count
	AFTER INSERT ON prisoner
	FOR EACH ROW
	EXECUTE FUNCTION error();

Триггер, который не позволяет регистрировать больных на работы

CREATE OR REPLACE FUNCTION error2()
	RETURNS trigger AS
	$$BEGIN
	IF (SELECT TRUE FROM prisoner
	INNER JOIN helth ON prisoner.id = helth.prisoner_id
	INNER JOIN work_prisoner ON prisoner.id = work_prisoner.prisoner_id
	WHERE NOT helth.helth)
	THEN 
	RAISE NOTICE 'helth is low';
	ELSE
	RETURN NEW;
	END IF;
	END;
	$$
LANGUAGE 'plpgsql';

CREATE TRIGGER chek_helth
	AFTER INSERT ON work_prisoner
	FOR EACH ROW
	EXECUTE FUNCTION error2();