/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES cp1251 */;
SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE IF EXISTS `recurs`;
CREATE DATABASE `recurs`
CHARACTER SET 'cp1251'
COLLATE 'cp1251_general_ci';
USE `recurs`;
#
# Structure for the `t_s_komplektnaya_edinitsa` table :
#
DROP TABLE IF EXISTS `t_s_komplektnaya_edinitsa`;
CREATE TABLE `t_s_komplektnaya_edinitsa` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`opisanie_reliza` char(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
#
# Data for the `t_s_komplektnaya_edinitsa` table (LIMIT 0,500)
#
INSERT INTO `t_s_komplektnaya_edinitsa` (`id`, `opisanie_reliza`) VALUES
(1,'1'),
(2,'2'),
(3,'3'),
(4,'4'),
(5,'5'),
(6,'5'),
(7,'7'),
(8,'8'),
(9,'9'),
(10,'10'),
(11,'11'),
(12,'12'),
(13,'13'),
(14,'14'),
(15,'15'),
(16,'16'),
(17,'17'),
(18,'18');
COMMIT;
#
# Structure for the `t_s_komplektatsiya_sborok` table :
#
DROP TABLE IF EXISTS `t_s_komplektatsiya_sborok`;
CREATE TABLE `t_s_komplektatsiya_sborok` (
`id` bigint(15) UNSIGNED NOT NULL AUTO_INCREMENT,
`sborka` int(11) UNSIGNED DEFAULT NULL,
`sub_sborka_detal` int(11) UNSIGNED DEFAULT NULL,
`k_vo` tinyint(1) UNSIGNED NOT NULL,
`tst` int(11) DEFAULT NULL,
`tst1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uissd` (`sborka`,`sub_sborka_detal`),
KEY `sborka` (`sborka`),
KEY `sub_sborka_detal` (`sub_sborka_detal`),
CONSTRAINT `komplektatsiya_sborok_fk` FOREIGN KEY (`sborka`) REFERENCES `t_s_komplektnaya_edinitsa` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `komplektatsiya_sborok_fk1` FOREIGN KEY (`sub_sborka_detal`) REFERENCES `t_s_komplektnaya_edinitsa` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
#
# Data for the `t_s_komplektatsiya_sborok` table (LIMIT 0,500)
#
INSERT INTO `t_s_komplektatsiya_sborok` (`id`, `sborka`, `sub_sborka_detal`, `k_vo`, `tst`, `tst1`) VALUES
(1,7,11,1,0,0),
(2,7,3,2,NULL,NULL),
(3,3,9,8,NULL,NULL),
(4,3,10,7,NULL,NULL),
(5,6,3,3,NULL,NULL),
(6,6,12,4,NULL,NULL),
(7,6,13,1,NULL,NULL),
(8,6,16,3,NULL,NULL),
(9,16,14,2,NULL,NULL),
(10,16,15,1,NULL,NULL),
(11,3,5,1,NULL,NULL),
(12,5,1,2,NULL,NULL),
(13,5,2,2,NULL,NULL),
(14,3,17,1,NULL,NULL),
(15,6,17,1,0,0);
COMMIT;
#
# Definition for the `find_in_all_tree` procedure :
#
DROP PROCEDURE IF EXISTS `find_in_all_tree`;
CREATE PROCEDURE `find_in_all_tree`(OUT pa INTEGER(11), OUT ch INTEGER(11), rooten BIGINT(20), testing BIGINT(20))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'Проверка на число вхождений'
BEGIN
DECLARE buf INTEGER(11);
SET pa:=-1;
SET ch:=-1;
IF -- проверка параметров на правильность
(`rooten`<1) OR (`testing`<1)
OR (rooten = testing)
OR (IFNULL(rooten, 0) = 0)
OR (IFNULL(testing , 0) = 0)
THEN -- если ошибка
SET pa:=-1;
SET ch:=-1;
ELSE -- выполняем поиск
CALL `getSubtree`(`rooten` ,'1','child');
SELECT COUNT(*) INTO pa FROM test02 WHERE i_am = rooten;
DELETE FROM test02;
CALL `getSubtree`(`testing` ,'1','parent');
SELECT COUNT(*) INTO ch FROM test02 WHERE child = testing;
DROP TABLE IF EXISTS test02;
END IF;
END;
#
# Definition for the `find_in_tree` procedure :
#
DROP PROCEDURE IF EXISTS `find_in_tree`;
CREATE PROCEDURE `find_in_tree`(OUT find INTEGER(11), rooten INTEGER(11), testing INTEGER(11), type_analyze ENUM('parent','child'))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'Проверка на число вхождений'
BEGIN
/* ОПИСАНИЕ
Эта процедура нужна для проверки члена testing
на связь с rooten . Это нужно для избежания зацикливания дерева.
В триггере перед вставкой с помощью нее можно проверить
не будет ли вводимое значение зацикливать дерево.
Возвращает количество вхождений по линии потомков или родителей.
Если вернет 0 , то вставка допустима
(в этом случае элемент нигде не состоит или состоит в этом же направлении).
Если не 0, то это зависит от условий.
Если testing планируется вставлять в rooten как потомок,
то testing не должен числиться в родителях и наоборот.
переменная type_analyze влияет на это.
*/
DECLARE buf INTEGER(11);
IF -- проверка параметров на правильность
(IFNULL(type_analyze,1) = 1) OR ((type_analyze != 'child') AND (type_analyze != 'parent'))
OR (`rooten`<1) OR (`testing`<1)
OR (IFNULL(rooten ,1) = 1)
OR (IFNULL(testing,1) = 1)
THEN -- если ошибка
SET find:=-1;
SELECT 'НЕВЕРНЫЕ ПАРАМЕТРЫ!' AS `ОШИБКА`;
ELSE -- выполняем поиск
CALL `getSubtree`(`rooten` ,'1',`type_analyze`);
IF type_analyze = 'parent' THEN
SELECT COUNT(i_am) INTO buf FROM test02 WHERE i_am = testing;
ELSE
SELECT COUNT(child) INTO buf FROM test02 WHERE child = testing;
END IF;
DROP TABLE IF EXISTS test02;
SET find:=buf;
END IF;
END;
#
# Definition for the `getAllTree` procedure :
#
DROP PROCEDURE IF EXISTS `getAllTree`;
CREATE PROCEDURE `getAllTree`(INOUT `comp_num` INTEGER(11), INOUT `type_analyze` ENUM('parent','child'))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Выводит анализ(статистику) дерева деталей'
BEGIN
/*
Возвращает статистику по выбраному дереву(поддереву)
или элементу. Находит всех родителей или детей компонента.
Пользуется базовой процедурой `getSubtree`
Первая колонка содержит номер объекта
Вторая число таких объектов в дереве
Третья строка - применяемость, входимость
Показывает сколько отдельных применений имеет этот элемент.
Или перефразируя, во сколько различных(независимых) поддеревьев
входит элемент с этим идентификатором
*/
DECLARE cbuf,bef, aft , que ,cbuf1 , tmp_table_name VARCHAR(50);
DECLARE ibuf, i INTEGER(11);
DECLARE t BIGINT(20);
-- СТУПЕНЬ ПРОВЕРКИ ПАРАМЕТРОВ
IF
(IFNULL(type_analyze,1) = 1) OR ((type_analyze != 'child') AND (type_analyze != 'parent'))
OR (comp_num<1) OR (IFNULL(comp_num ,0) = 0)
THEN
SELECT 'УКАЗАНЫ НЕДОПУСТИМЫЕ ПАРАМЕТРЫ' AS `ОШИБКА_ПЕРЕДАНЫХ_ПАРАМЕТРОВ`;
ELSE
-- рекурсия , где все происходит
CALL `getSubtree`( `comp_num` , '1', `type_analyze`);
-- выборка по результатам действия процедуры
IF (type_analyze = 'parent') THEN
SELECT i_am AS `komponent`,
SUM(k_vo) AS `count`,
COUNT(i_am) AS `entering`
FROM test02 GROUP BY (i_am);
ELSE
SELECT child AS `komponent`,
SUM(k_vo) AS `count`,
COUNT(child) AS `entering`
FROM test02 GROUP BY (child);
END IF;
DROP TABLE test02;
END IF;
END;
#
# Definition for the `getSubtree` procedure :
#
DROP PROCEDURE IF EXISTS `getSubtree`;
CREATE PROCEDURE `getSubtree`(comp_num INTEGER(11), count_comp INTEGER(11), type_analyze ENUM('parent','child'))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Базовая процедура'
BEGIN
/*
Используя эту процедуру, можно написать любую функцию или процедуру
Рекурсивная составляющая getAllTree.
Анализ всего дерева происходит именно в этой процедуре.
В теле проход по всем веточкам текущего узла.
При рекурсивном вызове происходит переход к следующему узлу.
В качестве параметров получает
Номер узла(comp_num) по чему проводить анализ,
Счет узла(count_num) для подсчета точного числа компонентов,
тип анализа(type_analyze) - режим анализирования.
*/
DECLARE `iter`, `cnt1` , `cnt2` INT(4) UNSIGNED; -- счетчик итераций для каждой рекурсии
DECLARE `buf` INTEGER(11);
DECLARE ibuf, i INTEGER(11);
DECLARE t BIGINT(20);
-- Это курсоры для рекурсий
DECLARE `curCNT2` CURSOR FOR SELECT COUNT(*)
FROM `t_s_komplektatsiya_sborok`
WHERE( `t_s_komplektatsiya_sborok`.`sub_sborka_detal` = `comp_num`);
DECLARE `curCNT1` CURSOR FOR SELECT COUNT(*)
FROM `t_s_komplektatsiya_sborok`
WHERE( `t_s_komplektatsiya_sborok`.`sborka` = comp_num);
DECLARE `cur2` CURSOR FOR
SELECT `t_s_komplektatsiya_sborok`.`sborka`
FROM `t_s_komplektatsiya_sborok`
WHERE( `t_s_komplektatsiya_sborok`.`sub_sborka_detal` = `comp_num`);
DECLARE `cur1` CURSOR FOR
SELECT t_s_komplektatsiya_sborok.`sub_sborka_detal`
FROM `t_s_komplektatsiya_sborok`
WHERE( `t_s_komplektatsiya_sborok`.`sborka` = comp_num);
-- Действие
-- Проверка входящих данных на корректность.
-- ПРОВЕРКИА ПАРАМЕТРОВ
IF
(IFNULL(type_analyze,1) = 1) OR ((type_analyze != 'child') AND (type_analyze != 'parent'))
OR (comp_num<1) OR (IFNULL(comp_num ,0) = 0)
THEN
SET t=-9;
-- SELECT 'УКАЗАНЫ НЕДОПУСТИМЫЕ ПАРАМЕТРЫ +' AS `ОШИБКА_ПЕРЕДАНЫХ_ПАРАМЕТРОВ`;
ELSE
CREATE TEMPORARY TABLE IF NOT EXISTS test02
(-- создание с проверкой временной таблицы
i_am INTEGER(11) UNSIGNED NOT NULL,
child INTEGER(11) UNSIGNED NOT NULL,
k_vo INTEGER(11) UNSIGNED NOT NULL
);
IF type_analyze = 'parent' THEN -- ее заполнение
INSERT INTO test02 -- по предкам
SELECT
t_s_komplektatsiya_sborok.sborka,
t_s_komplektatsiya_sborok.sub_sborka_detal,
t_s_komplektatsiya_sborok.k_vo * count_comp
FROM t_s_komplektatsiya_sborok
WHERE t_s_komplektatsiya_sborok.sub_sborka_detal = comp_num;
ELSE
INSERT INTO test02 -- по потомкам
SELECT
t_s_komplektatsiya_sborok.sborka,
t_s_komplektatsiya_sborok.sub_sborka_detal,
t_s_komplektatsiya_sborok.k_vo * count_comp
FROM t_s_komplektatsiya_sborok
WHERE t_s_komplektatsiya_sborok.sborka = comp_num;
END IF;
-- Ищем путем перебора по курсору все подуровни
-- при нахождении оного самовызов с новым сборочным элементом
-- остальные параметры без изменений
SET @iter := 1;
SET @sbuf1 := `type_analyze`;
IF(`type_analyze` = 'child') THEN -- анализируем сборку
-- находим число детей
OPEN `curCNT1`;
FETCH `curCNT1` INTO `cnt1`;
CLOSE `curCNT1`;
IF `cnt1` >0 THEN -- если есть дети
OPEN `cur1`;
SET iter := 0;
WHILE iter < cnt1 DO
FETCH cur1 INTO buf; -- получаем номер очередного элемента
SET @i:= count_comp*`count_this_component`(comp_num, buf);
CALL `getSubtree`(buf , @i, @sbuf1);
SET iter := iter + 1;
END WHILE;
CLOSE `cur1`;
END IF;
ELSE -- анализируем деталь
-- находим число родителей
OPEN `curCNT2`;
FETCH `curCNT2` INTO cnt2;
CLOSE `curCNT2`;
OPEN `cur2`;
SET iter := 0;
WHILE iter < cnt2 DO
-- Ищем по родителям (сборки)
FETCH `cur2` INTO `buf`; -- получаем номер очередного элемента
-- вызываем сами себя для анализа родительской ветви
SET @i:= count_comp * `count_this_component`(buf,comp_num);
CALL `getSubtree`(buf, @i , @sbuf1);
SET iter := iter + 1;
END WHILE;
CLOSE `cur2`;
END IF;
END IF;
END;
#
# Definition for the `count_this_component` function :
#
DROP FUNCTION IF EXISTS `count_this_component`;
CREATE FUNCTION `count_this_component`(iam INTEGER(11), child INTEGER(11))
RETURNS tinyint(4)
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Определяет вхождение заданного компонента'
BEGIN
-- Служебная функция
DECLARE i INT(1);
DECLARE CREC CURSOR FOR SELECT COUNT(*) FROM `t_s_komplektatsiya_sborok`
WHERE `t_s_komplektatsiya_sborok`.`sborka`=iam
AND `t_s_komplektatsiya_sborok`.`sub_sborka_detal` = child;
DECLARE CNT CURSOR FOR SELECT `t_s_komplektatsiya_sborok`.`k_vo`
FROM `t_s_komplektatsiya_sborok`
WHERE `t_s_komplektatsiya_sborok`.`sborka`=iam
AND `t_s_komplektatsiya_sborok`.`sub_sborka_detal` = child;
OPEN CREC;
FETCH CREC INTO i;
CLOSE CREC;
IF i!=1 THEN
RETURN 0;
END IF;
OPEN CNT;
FETCH CNT INTO i;
CLOSE CNT;
RETURN i;
END;
CREATE TRIGGER `t_s_komplektatsiya_sborok_before_ins_tr` BEFORE INSERT ON `t_s_komplektatsiya_sborok`
FOR EACH ROW
BEGIN
DECLARE buf, pa, ch INTEGER(11);
SET pa:=-1;
SET ch:=-1;
IF NEW.sborka = NEW.sub_sborka_detal THEN
SET NEW.sborka = 0;
SET NEW.sub_sborka_detal = 0;
ELSE
CREATE TEMPORARY TABLE IF NOT EXISTS test02
(
i_am INTEGER(11) UNSIGNED NOT NULL,
child INTEGER(11) UNSIGNED NOT NULL,
k_vo INTEGER(11) UNSIGNED NOT NULL
);
CALL `getSubtree`(NEW.sborka ,'1','parent');
SELECT COUNT(*) INTO pa FROM test02 WHERE i_am = NEW.sub_sborka_detal;
DELETE FROM test02;
CALL `getSubtree`(NEW.sub_sborka_detal ,'1','child');
SELECT COUNT(*) INTO ch FROM test02 WHERE child = NEW.sborka;
DROP TEMPORARY TABLE IF EXISTS test02;
IF ch != 0 AND pa!=0 THEN
SET NEW.sborka = 0;
SET NEW.sub_sborka_detal = 0;
END IF;
END IF;
END;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;