1.4. Разработка модели хранения в среде субд данных классификатора изделий

1. Разработка er-диаграммы

Рис. 4. ER-диаграмма классификатора.

Сущность «metaclass» хранит данные о структуре классификатора. Поле «parent» позволяет построить дерево классификаций изделий.

Сущность «product» содержит конечные изделия. Поле «s_um» указывает на единицу измерения, а «parent» — на класс изделия.

2. Подготовка скриптов метаданных

Создание таблиц:

/* Единицы измерения*/ CREATE TABLE UM ( s_name VARCHAR(10) UNIQUE, name VARCHAR(50), PRIMARY KEY (name) );

/* Промежуточные вершины (классы) */ CREATE TABLE Metaclass ( name VARCHAR(100), parent VARCHAR(100), PRIMARY KEY (name), FOREIGN KEY (parent) REFERENCES Metaclass(name) ON DELETE CASCADE ); /* Терминальные вершины */ CREATE TABLE Product ( name VARCHAR(100), s_um VARCHAR(10), parent VARCHAR(100) NOT NULL, PRIMARY KEY (name), FOREIGN KEY (parent) REFERENCES Metaclass(name) ON DELETE CASCADE, FOREIGN KEY (s_um) REFERENCES UM(s_name) ON DELETE CASCADE );

Рис. 4. Представление метаданных таблиц в DataGrip.

1.5. Разработка основных процедур

/* Добавление новой единицы измерения */ CREATE OR REPLACE FUNCTION AddUnitOfMeasurement(umName VARCHAR(50), s_umName VARCHAR(10)) RETURNS INTEGER AS $$ DECLARE ALREADY_THERE INTEGER; BEGIN /* Check if name or short name is already in a table */ SELECT COUNT(*) FROM UM WHERE (name = umName OR s_name = s_umName) INTO ALREADY_THERE; /* Main condition */ IF (ALREADY_THERE = 0) THEN /* Success */ INSERT INTO UM VALUES (s_umName, umName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Добавление новой вершины */ CREATE OR REPLACE FUNCTION AddClass(className VARCHAR(100), parentName VARCHAR(100)) RETURNS INTEGER AS $$ DECLARE ALREADY_THERE INTEGER; DECLARE PARENT_FOUND INTEGER; BEGIN /* Check if class is already in a table */ SELECT COUNT(*) FROM MetaClass WHERE name = className INTO ALREADY_THERE; /* Check if parent exists */ SELECT COUNT(*) FROM MetaClass WHERE name = parentName INTO PARENT_FOUND; /* If parent is NULL then it "exists" */ IF (parentName IS NULL) THEN PARENT_FOUND = 1; END IF; /* Main condition */ IF (ALREADY_THERE = 0 AND PARENT_FOUND > 0) THEN /* Success */ INSERT INTO MetaClass VALUES (className, parentName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Добавление новой вершины */ CREATE OR REPLACE FUNCTION AddProduct(prodName VARCHAR(100), s_umName VARCHAR(10), parentName VARCHAR(100)) RETURNS INTEGER AS $$ DECLARE ALREADY_THERE INTEGER; DECLARE UM_FOUND INTEGER; DECLARE PARENT_FOUND INTEGER; BEGIN /* Check if class is already in a table */ SELECT COUNT(*) FROM Product WHERE name = prodName INTO ALREADY_THERE; /* Check if unit of measurement exists */ SELECT COUNT(*) FROM UM WHERE s_name = s_umName INTO UM_FOUND; /* Check if parent exists */ SELECT COUNT(*) FROM MetaClass WHERE name = parentName INTO PARENT_FOUND; /* Main condition */ IF (ALREADY_THERE = 0 AND UM_FOUND > 0 AND PARENT_FOUND > 0) THEN /* Success */ INSERT INTO Product VALUES (prodName, s_umName, parentName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Изменение родителя вершины (промежуточной или терминальной) */ CREATE OR REPLACE FUNCTION ChangeParent(nodeName VARCHAR(100), newParentName VARCHAR(100)) RETURNS INTEGER AS $$ DECLARE NODE_FOUND INTEGER; DECLARE PARENT_FOUND INTEGER; BEGIN /* Check if parent exists */ SELECT COUNT(*) FROM MetaClass WHERE name = newParentName INTO PARENT_FOUND; IF (PARENT_FOUND = 0) THEN /* Error */ RETURN 0; END IF; /* Check if MetaClass exists */ SELECT COUNT(*) FROM MetaClass WHERE name = nodeName INTO NODE_FOUND; IF (NODE_FOUND) THEN /* Success */ UPDATE Metaclass SET parent = newParentName WHERE name = nodeName; RETURN 1; END IF; /* Check if Product exists */ SELECT COUNT(*) FROM Product WHERE name = nodeName INTO NODE_FOUND; IF (NODE_FOUND) THEN /* Success */ UPDATE Product SET parent = newParentName WHERE name = nodeName; RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Изменение порядка размещения потомков в списке вывода */ CREATE OR REPLACE FUNCTION SwapRows(name1 VARCHAR(100), name2 VARCHAR(100)) RETURNS INTEGER AS $$ DECLARE NODES_FOUND INTEGER; BEGIN /* Check if nodes exists */ SELECT COUNT(*) FROM Product WHERE (name = name1 OR name = name2) INTO NODES_FOUND; IF (NODES_FOUND != 2) THEN /* Error */ RETURN 0; END IF; /* Success */ UPDATE Product /* Changing names first to escape primary key problems */ SET name = CASE WHEN name = name1 THEN (SELECT CONCAT(name2,'_')) ELSE (SELECT CONCAT(name1,'_')) END WHERE name IN (name1, name2); /* Finally swapping */ UPDATE Product SET name = name1 WHERE name = (SELECT CONCAT(name1,'_')); UPDATE Product SET name = name2 WHERE name = (SELECT CONCAT(name2,'_')); RETURN 1; END $$ LANGUAGE plpgsql; /* Удаление вершины (промежуточной или терминальной) */ CREATE OR REPLACE FUNCTION RemoveNode(nodeName VARCHAR(100)) RETURNS INTEGER /* Casually deleting node by a name, because foreign key flag "ON DELETE CASCADE" is set */ AS $$ DECLARE NODE_FOUND INTEGER; BEGIN /* Check if MetaClass exists */ SELECT COUNT(*) FROM MetaClass WHERE name = nodeName INTO NODE_FOUND; IF (NODE_FOUND) THEN /* Success */ DELETE FROM Metaclass WHERE name = nodeName; RETURN 1; END IF; /* Check if Product exists */ SELECT COUNT(*) FROM Product WHERE name = nodeName INTO NODE_FOUND; IF (NODE_FOUND) THEN /* Success */ DELETE FROM Product WHERE name = nodeName; RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql;