1.3. Разработка модели классов
Модель классов для ВИ Работа с классификатором:
Рис. 3. Модель классов для ВИ Ведение классификатора
Описание компонентов диаграммы: структура классификации описывается с использованием метакласса Класс изделия. Зависимости между классами моделируются ассоциацией Потомки. В качестве атрибутов метакласса выделены Имя и Обозначение. Введено несколько операций метакласса.
1.4. Разработка модели хранения в среде субд данных классификатора изделий
1. Разработка ERD
Первоначально опишем модель хранения в виде ERD.
Рис. 4. ERD для задачи Работа с классификатором изделий
Сущность CHEM_CLASS позволяет хранить данные о структуре классификатора. Поле BASE_EI позволяет указать единицу измерения количества изделия. Поле MAIN_CLASS позволяет описать дерево классификации изделий. Поле CODE позволяет добавить условный код единицы измерения.
Сущность PROD позволяет вести каталог изделий. Поле ID_CLASS позволяет указать терминальный класс изделия. Поля SHORT_NAME, NAME позволяют указать обозначение и имя изделия.
2. Создание базы данных
Рис. 5. Создание БД в DataGrip
3. Подготовка скриптов метаданных
Для каждой сущности создаем таблицы с описанием полей:
/*Единицы измерения*/
CREATE TABLE EI
(
EI_ID SERIAL primary key,
SHORT_NAME varchar(10),
NAME varchar(50),
CODE varchar(15)
);
/*Классы изделий*/
create table CHEM_CLASS
(
SHORT_NAME VARCHAR(10),
ID_CLASS SERIAL PRIMARY KEY,
NAME VARCHAR(50),
MAIN_CLASS INTEGER,
BASE_EI INTEGER,
FOREIGN KEY (BASE_EI) REFERENCES EI (EI_ID),
FOREIGN KEY (MAIN_CLASS) REFERENCES CHEM_CLASS (ID_CLASS)
);
/*Продукция*/
CREATE TABLE PROD
(
ID_PROD SERIAL PRIMARY KEY ,
SHORT_NAME VARCHAR(50),
NAME VARCHAR(250),
ID_CL INTEGER,
FOREIGN KEY (ID_CL) REFERENCES CHEM_CLASS (ID_CLASS)
);
1.5. Разработка основных процедур
/*Добавление новой ЕИ в таблицу*/
create or replace function ins_ei(CODE integer, SHORT_NAME varchar(10), NAME varchar(50))
returns INTEGER
as
'
insert into EI(SHORT_NAME, NAME, CODE)
values (CODE, SHORT_NAME, NAME);
select 1
'
LANGUAGE sql;
/*Операция добавления нового класса в классификатор изделий*/
CREATE OR REPLACE FUNCTION ins_class(main_class INTEGER, short_name VARCHAR(10), name VARCHAR(50), base_ei INTEGER)
returns INTEGER
as
'
begin
if (exists(
select BASE_EI
from EI
where EI.EI_ID = base_ei)
) then
insert into CHEM_CLASS(MAIN_CLASS, SHORT_NAME, NAME, BASE_EI)
values (main_class, short_name, name, base_ei);
return 1;
end if;
insert into CHEM_CLASS(MAIN_CLASS, SHORT_NAME, NAME, BASE_EI)
values (NULL, short_name, name, NULL);
return 1;
end;
'
LANGUAGE plpgsql;
/*Поиск всех подклассов класса*/
CREATE OR REPLACE FUNCTION find_gr_gr(class INTEGER)
returns TABLE(ID integer, MAIN INTEGER, NAME VARCHAR(50), BASE INTEGER)
as
$$
begin
return QUERY
SELECT CHEM_CLASS.ID_CLASS, CHEM_CLASS.MAIN_CLASS,CHEM_CLASS.NAME, CHEM_CLASS.BASE_EI
FROM CHEM_CLASS
WHERE ID_CLASS = class
union
(WITH RECURSIVE r AS (
SELECT CHEM_CLASS.ID_CLASS, CHEM_CLASS.MAIN_CLASS,CHEM_CLASS.NAME, CHEM_CLASS.BASE_EI
FROM CHEM_CLASS
WHERE MAIN_CLASS = class
UNION
SELECT CHEM_CLASS.ID_CLASS, CHEM_CLASS.MAIN_CLASS, CHEM_CLASS.NAME, CHEM_CLASS.BASE_EI
FROM CHEM_CLASS
JOIN r
ON CHEM_CLASS.MAIN_CLASS = r.ID_CLASS
)
SELECT * from r)
order by id_class;
end;
$$
LANGUAGE plpgsql;
/*Поиск всех родителей класса*/
CREATE OR REPLACE FUNCTION find_parent(class INTEGER)
returns TABLE(ID integer, MAIN INTEGER, NAME VARCHAR(50), BASE INTEGER)
as
$$
begin
return QUERY
SELECT CHEM_CLASS.ID_CLASS, CHEM_CLASS.MAIN_CLASS, CHEM_CLASS.NAME, CHEM_CLASS.BASE_EI
FROM CHEM_CLASS
WHERE ID_CLASS = class
union
(WITH RECURSIVE r AS (
with t as (SELECT MAIN_CLASS AS parent FROM CHEM_CLASS
WHERE ID_CLASS = class)
SELECT CHEM_CLASS.ID_CLASS, CHEM_CLASS.MAIN_CLASS,CHEM_CLASS.NAME, CHEM_CLASS.BASE_EI
FROM CHEM_CLASS, t
WHERE ID_CLASS = t.parent
UNION
SELECT CHEM_CLASS.ID_CLASS, CHEM_CLASS.MAIN_CLASS, CHEM_CLASS.NAME, CHEM_CLASS.BASE_EI
FROM CHEM_CLASS
JOIN r
ON CHEM_CLASS.ID_CLASS = r.MAIN_CLASS
)
SELECT * from r)
order by id_class DESC;
end;
$$
LANGUAGE plpgsql;
/*Добавление продукции в таблицу*/
create or replace function ins_prod(ID_CL integer, SHORT_NAME varchar(10), NAME varchar(50))
returns INTEGER
as
'
begin
if (exists(
select ID_CLASS
from chem_class
where chem_class.ID_CLASS = ID_CL)
) then
insert into PROD(ID_CL, short_name, NAME)
values (ID_CL, SHORT_NAME, NAME);
return 1;
end if;
insert into PROD(ID_CL, short_name, NAME)
values (NULL, SHORT_NAME, NAME);
return 1;
end;
'
LANGUAGE plpgsql;
/*Вывести список всех продукции указанного класса*/
create or replace function FIND_LIST(class integer)
returns TABLE(ID integer, prod_name VARCHAR(50), shotr_NAME VARCHAR(10), ei_name varchar(10), class_id INTEGER, class_name varchar(50))
as
$$
begin
return QUERY
with t as (select * from find_gr_gr(class))
select ID_PROD, PROD.NAME, PROD.SHORT_NAME, ei.name, prod.id_cl, CHEM_CLASS.name from prod
inner join CHEM_CLASS on CHEM_CLASS.id_class = prod.id_cl
inner join EI on EI.ei_id = CHEM_CLASS.base_ei
where prod.id_cl = any(select t.id from t);
end;
$$
LANGUAGE plpgsql;