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), 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 MetaClass WHERE name = className 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; /* If parent is NULL then it "exists" */ IF (parentName IS NULL) THEN PARENT_FOUND = 1; END IF; /* Main condition */ IF (ALREADY_THERE = 0 AND UM_FOUND > 0 AND PARENT_FOUND > 0) THEN /* Success */ INSERT INTO MetaClass VALUES (className, s_umName, parentName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Добавление новой вершины */ CREATE OR REPLACE FUNCTION AddProduct(prodName 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 Product WHERE name = prodName INTO ALREADY_THERE; /* Check if parent exists */ SELECT COUNT(*) FROM MetaClass WHERE name = parentName INTO PARENT_FOUND; /* Main condition */ IF (ALREADY_THERE = 0 AND PARENT_FOUND > 0) THEN /* Success */ INSERT INTO Product VALUES (prodName, parentName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Создание нового перeчисления*/ CREATE OR REPLACE FUNCTION CreateEnum(shortName VARCHAR(15), fullName VARCHAR(100)) RETURNS INTEGER AS $$ DECLARE ALREADY_THERE INTEGER; BEGIN /* Check if name or short name is already in a table */ SELECT COUNT(*) FROM Enum WHERE (name = fullName OR s_name = shortName) INTO ALREADY_THERE; /* Main condition */ IF (ALREADY_THERE = 0) THEN /* Success */ INSERT INTO Enum VALUES (shortName, fullName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Создание нового значения перeчисления */ CREATE OR REPLACE FUNCTION CreateEnumValue(shortEnumName VARCHAR(15), shortValName VARCHAR(15), fullValName VARCHAR(100), val DOUBLE PRECISION) RETURNS INTEGER AS $$ DECLARE ENUM_FOUND INTEGER; DECLARE ALREADY_THERE INTEGER; BEGIN /* Check if short and full names are already in a table */ SELECT COUNT(*) FROM EnumVal WHERE (s_enumName = shortEnumName AND (s_name = shortValName OR name = fullValName)) INTO ALREADY_THERE; /* Check if enum exists */ SELECT COUNT(*) FROM Enum WHERE s_name = shortEnumName INTO ENUM_FOUND; /* Main condition */ IF (ALREADY_THERE = 0 AND ENUM_FOUND > 0) THEN /* Success */ INSERT INTO EnumVal(s_name, name, val, s_enumName) VALUES(shortValName, fullValName, val, shortEnumName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Создание нового параметра */ CREATE OR REPLACE FUNCTION CreateParameter(shortParamName VARCHAR(10), fullParamName VARCHAR(100), valueType VARCHAR(5), shortUnitName VARCHAR(10), shortEnumName VARCHAR(15)) RETURNS INTEGER AS $$ DECLARE ALREADY_THERE INTEGER; DECLARE UM_EXISTS INTEGER; DECLARE ENUM_EXISTS INTEGER; DECLARE NULL_CHECK_OK BOOLEAN; BEGIN /* Check if short and full names are already in a table */ SELECT COUNT(*) FROM Parameters WHERE (s_name = shortParamName OR name = fullParamName) INTO ALREADY_THERE; /* Check if unit of measurement exists */ SELECT COUNT(*) FROM UM WHERE (s_name = shortUnitName) INTO UM_EXISTS; IF (shortUnitName IS NULL) THEN UM_EXISTS = 1; END IF; /* Check if enum exists */ SELECT COUNT(*) FROM EnumVal WHERE (s_name = shortEnumName) INTO ENUM_EXISTS; IF (shortEnumName IS NULL) THEN ENUM_EXISTS = 1; END IF; /* Check if units or enum value are not null */ IF (valueType = 'num') THEN NULL_CHECK_OK = (shortUnitName IS NOT NULL AND shortEnumName IS NULL); ELSIF (valueType = 'enum') THEN NULL_CHECK_OK = (shortUnitName IS NULL AND shortEnumName IS NOT NULL); ELSE /* Error */ RETURN 0; END IF; /* Main condition */ IF (ALREADY_THERE = 0 AND UM_EXISTS > 0 AND ENUM_EXISTS > 0 AND NULL_CHECK_OK = TRUE) THEN /* Success */ INSERT INTO Parameters VALUES(shortParamName, fullParamName, valueType, shortUnitName, shortEnumName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Добавление параметра метаклассу */ CREATE OR REPLACE FUNCTION AddClassParam(fullClassName VARCHAR(10), shortParamName VARCHAR(10), minVal DOUBLE PRECISION, maxVal DOUBLE PRECISION) RETURNS INTEGER AS $$ DECLARE ALREADY_THERE INTEGER; DECLARE PARAM_TYPE VARCHAR(5); DECLARE CLASS_EXISTS INTEGER; DECLARE PARAM_EXISTS INTEGER; BEGIN /* Check if class name and param name are already in a table */ SELECT COUNT(*) FROM ParamRestrictions WHERE (className = fullClassName AND s_paramName = shortParamName) INTO ALREADY_THERE; /* Validate parameters */ SELECT varType FROM Parameters WHERE (s_name = shortParamName) INTO PARAM_TYPE; IF (PARAM_TYPE = 'num') THEN IF (minVal > maxVal) THEN /* Error */ RETURN 0; END IF; ELSIF (minVal IS NOT NULL OR maxVal IS NOT NULL) THEN /* Error */ RETURN 0; END IF; /* Check if class short name exist */ SELECT COUNT(*) FROM MetaClass WHERE (name = fullClassName) INTO CLASS_EXISTS; /* Check if parameter short name exists */ SELECT COUNT(*) FROM Parameters WHERE (s_name = shortParamName) INTO PARAM_EXISTS; /* Main condition */ IF (ALREADY_THERE = 0 AND CLASS_EXISTS > 0 AND PARAM_EXISTS > 0) THEN /* Success */ INSERT INTO ParamRestrictions VALUES (fullClassName, shortParamName, minVal, maxVal); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Добавление значения параметра изделию */ CREATE OR REPLACE FUNCTION AddProductParam(prodName VARCHAR(100), shortParamName VARCHAR(10), val DOUBLE PRECISION, s_enumValName VARCHAR(15)) RETURNS INTEGER AS $$ DECLARE ALREADY_THERE INTEGER; DECLARE PARENT_NAME VARCHAR(100); DECLARE ENUM_VAL_EXISTS INTEGER; DECLARE PARAM_EXISTS INTEGER; DECLARE PARAM_TYPE VARCHAR(5); DECLARE PARAM_ENUM_NAME VARCHAR(15); DECLARE MIN_VALUE DOUBLE PRECISION; DECLARE MAX_VALUE DOUBLE PRECISION; BEGIN /* Check if product name and param name are already in a table */ SELECT COUNT(*) FROM ParamValues WHERE (productName = prodName AND s_paramName = shortParamName) INTO ALREADY_THERE; IF (ALREADY_THERE > 0) THEN /* Error */ RETURN 0; END IF; /* Validate parameter (must have restrictions in a parent class) */ SELECT parent FROM Product WHERE name = prodName INTO PARENT_NAME; /* Check if product exists */ IF (PARENT_NAME IS NULL) THEN /* Error */ RETURN 0; END IF; /* Looping through parents until parameter restrictions are found */ WHILE TRUE LOOP SELECT COUNT(*) FROM ParamRestrictions WHERE (className = PARENT_NAME AND s_paramName = shortParamName) INTO PARAM_EXISTS; IF (PARAM_EXISTS > 0) THEN /* Success */ EXIT; --BREAK; END IF; /* Next iteration */ SELECT parent FROM MetaClass WHERE name = PARENT_NAME INTO PARENT_NAME; IF (PARENT_NAME IS NULL) THEN /* Error */ RETURN 0; END IF; END LOOP; /* Success */ SELECT varType, s_enumName FROM Parameters WHERE s_name = shortParamName INTO PARAM_TYPE, PARAM_ENUM_NAME; SELECT minVal, maxVal FROM ParamRestrictions WHERE (className = PARENT_NAME AND s_paramName = shortParamName) INTO MIN_VALUE, MAX_VALUE; SELECT COUNT(*) FROM EnumVal WHERE (s_enumName = PARAM_ENUM_NAME AND s_name = s_enumValName) INTO ENUM_VAL_EXISTS; IF (PARAM_TYPE = 'num' AND s_enumValName IS NULL AND MIN_VALUE <= val AND val <= MAX_VALUE) THEN INSERT INTO ParamValues VALUES (prodName, shortParamName, val, NULL); RETURN 1; ELSIF (PARAM_TYPE = 'enum' AND val IS NULL AND ENUM_VAL_EXISTS > 0) THEN INSERT INTO ParamValues VALUES (prodName, shortParamName, NULL, s_enumValName); RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Редактирование значения параметра изделия */ CREATE OR REPLACE FUNCTION EditProductParam(prodName VARCHAR(100), shortParamName VARCHAR(10), val DOUBLE PRECISION, s_enumValName VARCHAR(15)) RETURNS INTEGER AS $$ DECLARE PARAM_EXISTS INTEGER; DECLARE VALUE_VALID INTEGER; BEGIN /* Check if given class has parameter */ SELECT COUNT(*) FROM ParamValues WHERE (productName = prodName AND s_paramName = shortParamName) INTO PARAM_EXISTS; /* Main condition */ IF (PARAM_EXISTS > 0) THEN /* Saving current value */ CREATE TABLE TempParamVal AS (SELECT * FROM ParamValues WHERE (productName = prodName AND s_paramName = shortParamName)); PERFORM RemoveProductParam(prodName, shortParamName); SELECT AddProductParam(prodName, shortParamName, val, s_enumValName) INTO VALUE_VALID; IF (VALUE_VALID = 0) THEN /* Error */ INSERT INTO ParamValues SELECT * FROM TempParamVal; DROP TABLE TempParamVal; RETURN 0; END IF; /* Success */ DROP TABLE TempParamVal; RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Отфильтрованный по значениям параметров вывод информации об изделиях */ CREATE OR REPLACE FUNCTION PrintFiltered(fullClassName VARCHAR(10), shortParamName VARCHAR(10), minValu DOUBLE PRECISION, maxValu DOUBLE PRECISION, s_enumValName VARCHAR(10)) RETURNS TABLE(name VARCHAR(100)) AS $$ DECLARE PARAM_EXISTS INTEGER; DECLARE COUNTER INTEGER; DECLARE PARAM_TYPE VARCHAR(5); BEGIN /* Check if given class has parameter */ SELECT COUNT(*) FROM ParamRestrictions WHERE (className = fullClassName AND s_paramName = shortParamName) INTO PARAM_EXISTS; IF (PARAM_EXISTS = 0) THEN RETURN; END IF; /* Validate values */ SELECT varType FROM Parameters WHERE s_name = shortParamName INTO PARAM_TYPE; IF (PARAM_TYPE = 'num' AND (minValu IS NULL OR maxValu IS NULL OR minValu > maxValu OR s_enumValName IS NOT NULL) OR PARAM_TYPE = 'enum' AND (minValu IS NOT NULL OR maxValu IS NOT NULL OR s_enumValName IS NULL)) THEN /* Error */ RETURN; END IF; /* Create table containing all filtered products of a given class */ CREATE TABLE TempChildren(name VARCHAR(100)); /* Create table containing all subclass names of a given class */ CREATE TABLE TempNames(id INTEGER, name VARCHAR(100)); /* Loop through subclasses until leafs are reached */ INSERT INTO TempNames SELECT ROW_NUMBER() OVER(ORDER BY b.name), b.name FROM MetaClass AS b WHERE parent = fullClassName; COUNTER = 0; WHILE TRUE LOOP IF ((SELECT COUNT(*) FROM TempNames WHERE id = COUNTER) = 0) THEN EXIT; -- No metaclasses anymore END IF; INSERT INTO TempChildren SELECT * FROM PrintFiltered((SELECT b.name FROM TempNames AS b WHERE id = COUNTER), shortParamName, minValu, maxValu, s_enumValName); COUNTER = COUNTER + 1; END LOOP; /* Get all filtered products of current metaclass */ INSERT INTO TempChildren SELECT b.name FROM Product AS b WHERE parent = fullClassName AND ( (PARAM_TYPE = 'num' AND minValu <= (SELECT val FROM ParamValues WHERE (s_paramName = shortParamName AND productName = b.name)) AND maxValu >= (SELECT val FROM ParamValues WHERE (s_paramName = shortParamName AND productName = b.name))) OR (PARAM_TYPE = 'enum' AND s_enumValName = (SELECT s_enumVal FROM ParamValues WHERE (s_paramName = shortParamName AND productName = b.name))) ); RETURN QUERY SELECT * FROM TempChildren; /* Deleting tables for later function use */ DROP TABLE TempChildren, TempNames; RETURN; 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; /* Удаление перечеслиния */ CREATE OR REPLACE FUNCTION RemoveEnum(shortName VARCHAR(15)) RETURNS INTEGER /* Casually deleting enum 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 Enum WHERE s_name = shortName INTO NODE_FOUND; IF (NODE_FOUND) THEN /* Success */ DELETE FROM Enum WHERE s_name = shortName; RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Удаление значения перечеслиния */ CREATE OR REPLACE FUNCTION RemoveEnumValue(id INTEGER) RETURNS INTEGER AS $$ DECLARE NODE_FOUND INTEGER; BEGIN /* Check if MetaClass exists */ SELECT COUNT(*) FROM EnumVal WHERE idVal = id INTO NODE_FOUND; IF (NODE_FOUND) THEN /* Success */ DELETE FROM EnumVal WHERE idVal = id; RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql; /* Удаление параметра из класса */ CREATE OR REPLACE FUNCTION RemoveProductParam(prodName VARCHAR(100), shortParamName VARCHAR(10)) RETURNS INTEGER AS $$ DECLARE NODE_FOUND INTEGER; BEGIN /* Check if MetaClass exists */ SELECT COUNT(*) FROM ParamValues WHERE (productName = prodName AND s_paramName = shortParamName) INTO NODE_FOUND; IF (NODE_FOUND) THEN /* Success */ DELETE FROM ParamValues WHERE productName = prodName AND s_paramName = shortParamName; RETURN 1; END IF; /* Error */ RETURN 0; END $$ LANGUAGE plpgsql;