МиСПрИС_Задание2_3_Петрова_Романова_Заболотников_9373
.pdfelse --если параметр - перечисление
select id_enum from enum_value where iIdEnumVal = id_value into vValEnum; --найдем значение перечисления для вставляемого значения
select id_enum from parameters where id_param = iIdParam into vEnumPar; --найдем значение перечисления для параметра
if(vValEnum = vEnumPar or iIdEnumVal is null)
then
update prod_param set id_enum_value = iIdEnumVal where iIdProd = id_prod and iIdParam = id_param; --Обновим значение в записи
oRes = 1; return;
else
raise exception 'id_enum_value is not correct! Please, look at table parameters.';
oRes = 0; return;
end if; end if;
else
raise exception 'You have to update typeParameters. You can not update another parameters.';
oRes = 0; return;
end if; end if;
end $$ LANGUAGE plpgsql;
--Удаление пары prod_param
CREATE OR REPLACE function delete_prod_param (
IdToDelete integer) RETURNS VOID
as $$
/*функция: Удаление элемента из таблицы prod_param
вход: IdToDelete - ид. пары, котоую нужно удалить выход: 1 - БД обновлена, 0 - Ошибка эффекты: 1. Проверка на существование удаляемой записи
*/
41
begin
if(select count(*) from prod_param where IdToDelete = id_pair) is not null then
DELETE FROM prod_param WHERE IdToDelete = id_pair;
else
raise exception 'Pair with this id does not exist. DB wasnt
updated.'; end if;
end $$ LANGUAGE plpgsql;
CREATE TABLE Type_Param--Таблица Типов Параметров
(
id_paramType serial NOT NULL PRIMARY KEY, --Идентификатор Типа Параметра
name_paramType text NOT NULL --Наименование Типа Параметра
)
-- Перечисление таблица
CREATE TABLE enumeration
(
id_enum serial NOT null PRIMARY KEY, -- ID перечисления name_enum text NOT NULL --Имя перечисления
)
-- Значение перечисления строковое таблица
CREATE TABLE enum_value
(
id_value serial NOT null PRIMARY KEY, -- ID значения
num integer NOT NULL, -- Порядковый номмер значения в перечислении name_value text NOT NULL, --Имя значения
id_enum integer NOT NULL, --ID перечисления
CONSTRAINT id_enum FOREIGN KEY (id_enum)
REFERENCES enumeration(id_enum) MATCH simple -- Ограничение для ссылки только на существующее id
ON UPDATE cascade --На обновление таблицы перечисления нет
реакции
ON DELETE cascade -- Если удаляется объект перечисления, то удалятся и все значения, которые на него ссылались
)
42
create unique index union_num_iden on enum_value(num, id_enum); --
Создадим индекс для уникальности пар (num, id_enum)
create unique index union_name_iden on enum_value(name_value, id_enum); --Создадим индекс для уникальности пар (name_value, id_enum)
CREATE TABLE Parameters--Таблица Параметров
(
id_param serial NOT NULL PRIMARY KEY, --Идентификатор Класса name_param text NOT NULL, --Наименование параметров
flagConf bool not null, --Флаг конфигурации
id_paramType integer not null, --Ссылка на тип парраметра id_enum integer, -- Cссылка на перечисление
id_uom integer not null, -- Ссылка на ЕИ
CONSTRAINT id_uom FOREIGN KEY (id_uom)
REFERENCES Unit_Of_Measure (id_uom) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
alter table parameters drop column flagConf;
alter table parameters alter column id_uom drop not null;
ALTER TABLE Parameters ADD CONSTRAINT idParaType FOREIGN KEY (id_paramType)
REFERENCES public.Type_Param (id_paramType) MATCH SIMPLE ON UPDATE NO ACTION
ON DELETE cascade;
ALTER TABLE Parameters ADD CONSTRAINT id_enum FOREIGN KEY (id_enum) REFERENCES public.enumeration (id_enum) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE cascade;
CREATE TABLE Prod_Param--Таблица Параметров
(
id_pair serial NOT NULL PRIMARY KEY, --Идентификатор пары (Продут и Параметр)
43
id_prod integer NOT null, -- Ссылка на продукт id_param integer not null, -- Ссылка на параметр
id_enum_value integer, --ссылка на значение перечисления max_value integer not null,
min_value integer not null, value double precision,
CONSTRAINT id_enum_value FOREIGN KEY (id_enum_value) REFERENCES enum_value (id_value) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
ALTER TABLE Prod_Param ADD CONSTRAINT id_param FOREIGN KEY (id_param) REFERENCES public.Parameters (id_param) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE cascade;
ALTER TABLE Prod_Param ADD CONSTRAINT id_prod FOREIGN KEY (id_prod) REFERENCES public.Product (id_product) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE cascade;
create unique index prodParamUnique on Prod_Param(id_prod, id_param); --Создадим индекс для уникальности пар (num, id_enum)
CREATE TABLE Class_Param--Таблица Параметров
(
id_pair serial NOT NULL PRIMARY KEY, --Идентификатор пары (Класс и Параметр)
id_class integer NOT null, -- Ссылка на Класс id_param integer not null, -- Ссылка на параметр max_value integer, --максимально допустимое значчение min_value integer, --минимально допустимое значчение
CONSTRAINT id_class FOREIGN KEY (id_class)
REFERENCES classificator (id_class) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
44
ALTER TABLE Class_Param ADD CONSTRAINT id_param FOREIGN KEY (id_param) REFERENCES public.Parameters (id_param) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE cascade;
create unique index classPar on Class_Param(id_class, id_param); --
Создадим индекс для уникальности пар (id_class, id_param)
CREATE TABLE SHD--Таблица Параметров
(
id_shd serial NOT NULL PRIMARY KEY, --Идентификатор СХД nameShd text NOT null -- Наименование СХД
)
CREATE TABLE ORDERR--Таблица Заказов
(
id_order serial NOT NULL PRIMARY KEY, -- ид Заказа id_shd integer NOT null, -- Ссылка на заказчика date_reg date not null, --Дата регистрации заказа
CONSTRAINT id_shd FOREIGN KEY (id_shd)
REFERENCES SHD (id_shd) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
CREATE TABLE Pos_Order--Таблица позиций в заказах
(
id_pair serial NOT NULL PRIMARY KEY, --Идентификатор пары (Заказ и Продукт)
id_order integer NOT null, -- Ссылка на Заказ id_prod integer not null, -- Ссылка на Продукт num integer not null, --Номер позиции
q integer not null, -- Колличество
CONSTRAINT id_order FOREIGN KEY (id_order)
REFERENCES ORDERR (id_order) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
ALTER TABLE Pos_Order ADD CONSTRAINT id_prod FOREIGN KEY (id_prod)
45
REFERENCES public.Product (id_product) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE cascade;
create unique index PosOrder on Pos_Order(id_order, num); --Создадим индекс для уникальности пар (id_order, num)
CREATE TABLE TypeProd_Param--Таблица позиций в заказах
(
id_pair serial NOT NULL PRIMARY KEY, --Идентификатор пары (Заказ и Продукт)
id_prod integer NOT null, -- Ссылка на Заказ id_param integer not null, -- Ссылка на Продукт max_val integer, --Наибольшее возможное значение min_val integer , --наименьшее возможное значение
CONSTRAINT id_param FOREIGN KEY (id_param)
REFERENCES Parameters (id_param) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
ALTER TABLE TypeProd_Param ADD CONSTRAINT id_prod FOREIGN KEY (id_prod)
REFERENCES public.Product (id_product) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE cascade;
create unique index TypeProdParam on TypeProd_Param(id_prod, id_param);
--------------------------------------------------------СОПРОВОЖДАЮЩИЕ ФУНКЦИИ--------------------------------------------------------
--Создание перечисления
CREATE OR REPLACE function add_enum ( new_enum_name text)
RETURNS int
/*функция: Создание перечисления вход: new_enum_name - Имя перечисления выход: 1 - БД обновлена, 0 - Ошибка
46
эффекты: 1. Если уже существует перечисление с таким именем, то перечисление не создаётся.
*/
as $$
declare ind int default 0; begin
select id_enum from enumeration where new_enum_name = name_enum into ind;
if ind is not null then
raise notice 'Enumeration with this name already exists. Db was not updated';
ind:=0; return ind;
else
INSERT INTO enumeration(name_enum) VALUES ($1); ind:= 1;
return ind; end if;
end $$ LANGUAGE plpgsql;
--Удаление перечисления
CREATE OR REPLACE function delete_enum ( delete_id integer) RETURNS int
/*функция: Удаление перечисления
вход: delete_id - Id перечисления, которое надо удалить выход: 1 - БД обновлена, 0 - Ошибка
эффекты: 1. Если перечисления с таким id нет, то БД не обновится и будет выведена ошибка
*/ as $$
declare ind int default 0; begin
select id_enum from enumeration where delete_id = id_enum into
ind;
if ind is not null then
DELETE FROM enumeration WHERE id_enum = delete_id; ind:= 1;
47
return ind;
else
raise notice 'Enumeration with this id does not exist. Db was not updated';
ind:= 0; return ind;
end if;
end $$ LANGUAGE plpgsql;
-- Добавление значения в перечисление create or replace function add_value (
new_value_name text, new_id_enum integer
)
returns int
/*функция: Добавление значения в перечисление
вход: new_value_name - имя значения, которое будет добавлено new_id_enum - id перечисления, к которому добавлеяется
значение выход: 1 - БД обновлена, 0 - Ошибка
эффекты: 1. Если перечисления с таким id нет, то БД не обновится и будет выведена ошибка
2. Если такое значение уже есть в перечислении, то оно добавлено не будет
*/ as $$
declare new_num integer; declare ind_id int default 0; declare ind_rep int default 0; begin
select id_enum from enumeration where new_id_enum = id_enum into ind_id;
select id_value from enum_value where name_value = new_value_name and id_enum = new_id_enum into ind_rep;
if ind_id is null then
raise notice 'This enumerator does not exist. DB was not
updated.';
return 0;
48
else
if ind_rep is not null then
raise notice 'This value already exists in this enumeration.DB was not updated.';
return 0;
else
if ((select COUNT(*) from enum_value where id_enum = $2) = 0) then--Если в перечислении ещё нет значений,
--то порядковый номер значения будет 1 new_num:= 1;
else
select MAX(num)+1 from enum_value where id_enum = $2
into new_num;
end if;
insert into enum_value(num, name_value, id_enum) VALUES (new_num, $1, $2);
return 1; end if;
end if;
end $$ LANGUAGE plpgsql;
-- Удаление значения из перечисления
CREATE OR REPLACE function delete_enum_value ( delete_id integer) RETURNS int
/*функция: Удаления значения из перечисления
вход: delete_id - id значения, которое будет удалено выход: 1 - БД обновлена, 0 - Ошибка
эффекты: 1. Если значения с таким id нет, то БД не обновится и будет выведена ошибка
*/ as $$
declare ind int default 0; declare idEnum int default 0; declare numd int default 0; begin
select id_value, num, id_enum from enum_value where id_value = delete_id into ind, numd, idEnum;
if ind is not null then
49
update enum_value set num = num-1 where num> numd and id_enum = idEnum;
DELETE FROM enum_value WHERE id_value = delete_id;
return 1;
else
raise notice 'The value with this id does not exist.DB was not updated.';
return 0; end if;
end $$ LANGUAGE plpgsql;
-- Добавление позиции заказа
create or replace function add_pos_order ( oIdOrder integer,
oIdProduct integer, oQ integer
)
returns int
/*функция: Добавление позиции заказа вход: oIdOrder - ид. Заказа
oIdProduct - ид. продукта oQ - количество продукта
выход: 1 - БД обновлена, 0 - Ошибка
эффекты: 1. Если заказа с таким id нет, то БД не обновится и будет выведена ошибка
2. Если такое значение уже есть в перечислении, то оно добавлено не будет
*/ as $$
declare new_num integer; declare ind_id int default 0; declare ind_rep int default 0; begin
select id_order from orderr where oIdOrder = id_order into ind_id; --Проверка на наличие заданного заказа
50