СУБД/Практические занятия №2(2 семестр) — различия между версиями

CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_ins_tr` BEFORE INSERT ON `ns_tree`
 FOR EACH ROW
BEGIN
 SET @left_key := 0;
 SET @level := 0;
-- Если мы указали родителя:
 IF NEW.parent_id IS NOT NULL AND NEW.parent_id > 0 THEN
 SELECT right_key, `level` + 1 INTO @left_key, @level
 FROM ns_tree
 WHERE id = NEW.parent_id AND tree = NEW.tree;
 END IF;
-- Если мы указали левый ключ:
 IF NEW.left_key IS NOT NULL AND NEW.left_key > 0 AND 
 (@left_key IS NULL OR @left_key = 0) THEN
 SELECT id, left_key, right_key, `level`, parent_id 
 INTO @tmp_id, @tmp_left_key, @tmp_right_key, @tmp_level, @tmp_parent_id
 FROM ns_tree
 WHERE tree = NEW.tree AND (left_key = NEW.left_key OR right_key = NEW.left_key);
 IF @tmp_left_key IS NOT NULL AND @tmp_left_key > 0 AND NEW.left_key = @tmp_left_key THEN
 SET NEW.parent_id := @tmp_parent_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level;
 ELSEIF @tmp_left_key IS NOT NULL AND @tmp_left_key > 0 AND NEW.left_key = @tmp_right_key THEN
 SET NEW.parent_id := @tmp_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level + 1;
 END IF;
 END IF;
-- Если родитель или левый ключ не указан, или мы ничего не нашли
 IF @left_key IS NULL OR @left_key = 0 THEN
 SELECT MAX(right_key) + 1 INTO @left_key
 FROM ns_tree
 WHERE tree = NEW.tree;
 IF @left_key IS NULL OR @left_key = 0 THEN
 SET @left_key := 1;
 END IF;
 SET @level := 0;
 SET NEW.parent_id := 0; 
 END IF;
-- Устанавливаем новые значения ключей
 SET NEW.left_key := @left_key;
 SET NEW.right_key := @left_key + 1;
 SET NEW.`level` := @level;
-- Формируем разрыв в дереве
 UPDATE _ns_tree
 SET left_key = CASE WHEN left_key >= @left_key 
 THEN left_key + 2 
 ELSE left_key + 0 
 END,
 right_key = right_key + 2
 WHERE tree = NEW.tree AND right_key >= @left_key;
END;

SQL код (4)CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_upd_tr` BEFORE UPDATE ON `ns_tree`
 FOR EACH ROW
BEGIN
-- Запрещаем изменять поля, или присылать гадости
 SET NEW.tree := OLD.tree;
 SET NEW.right_key := OLD.right_key;
 SET NEW.`level` := OLD.`level`;
 SET @return_flag := 0;
 IF NEW.parent_id IS NULL THEN SET NEW.parent_id := 0; END IF;
-- Проверяем, а есть ли изменения связанные со структурой дерева
 IF NEW.parent_id <> OLD.parent_id OR NEW.left_key <> OLD.left_key THEN
-- Дерево таки перестраиваем, что ж, приступим:
 SET @left_key := 0;
 SET @level := 0;
 SET @skew_tree := OLD.right_key - OLD.left_key + 1;
-- Определяем куда мы его переносим:
-- Если сменен parent_id:
 IF NEW.parent_id <> OLD.parent_id THEN
-- Если в подчинение другому злу:
 IF NEW.parent_id > 0 THEN
 SELECT right_key, level + 1
 INTO @left_key, @level
 FROM ns_tree
 WHERE id = NEW.parent_id AND tree = NEW.tree;
-- Иначе в корень дерева переносим:
 ELSE
 SELECT MAX(right_key) + 1 
 INTO @left_key
 FROM ns_tree
 WHERE tree = NEW.tree;
 SET @level := 0;
 END IF;
-- Если вдруг родитель в диапазоне перемещаемого узла, проверка:
 IF @left_key IS NOT NULL AND 
 @left_key > 0 AND
 @left_key > OLD.left_key AND
 @left_key <= OLD.right_key THEN
 SET NEW.parent_id := OLD.parent_id;
 SET NEW.left_key := OLD.left_key;
 SET @return_flag := 1;
 END IF;
 END IF;
-- Если не parent_id, то изменен left_key, или если изменение parent_id ничего не дало
 IF @left_key IS NULL OR @left_key = 0 THEN
 SELECT id, left_key, right_key, `level`, parent_id 
 INTO @tmp_id, @tmp_left_key, @tmp_right_key, @tmp_level, @tmp_parent_id
 FROM ns_tree
 WHERE tree = NEW.tree AND (right_key = NEW.left_key OR right_key = NEW.left_key - 1)
 LIMIT 1;
 IF @tmp_left_key IS NOT NULL AND 
 @tmp_left_key > 0 AND 
 NEW.left_key - 1 = @tmp_right_key THEN
 SET NEW.parent_id := @tmp_parent_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level;
 ELSEIF @tmp_left_key IS NOT NULL AND 
 @tmp_left_key > 0 AND 
 NEW.left_key = @tmp_right_key THEN
 SET NEW.parent_id := @tmp_id;
 SET @left_key := NEW.left_key;
 SET @level := @tmp_level + 1;
 ELSEIF NEW.left_key = 1 THEN
 SET NEW.parent_id := 0;
 SET @left_key := NEW.left_key;
 SET @level := 0;
 ELSE
 SET NEW.parent_id := OLD.parent_id;
 SET NEW.left_key := OLD.left_key;
 SET @return_flag = 1;
 END IF;
 END IF;
-- Теперь мы знаем куда мы перемещаем дерево
-- Проверяем а стоит ли это делать
 IF @return_flag IS NULL OR @return_flag = 0 THEN
 SET @skew_level := @level - OLD.`level`;
 IF @left_key > OLD.left_key THEN
-- Перемещение вверх по дереву
 SET @skew_edit := @left_key - OLD.left_key - @skew_tree;
 UPDATE _ns_tree
 SET left_key = CASE WHEN right_key <= OLD.right_key
 THEN left_key + @skew_edit
 ELSE CASE WHEN left_key > OLD.right_key
 THEN left_key - @skew_tree
 ELSE left_key
 END
 END,
 `level` = CASE WHEN right_key <= OLD.right_key 
 THEN `level` + @skew_level
 ELSE `level`
 END,
 right_key = CASE WHEN right_key <= OLD.right_key 
 THEN right_key + @skew_edit
 ELSE CASE WHEN right_key < @left_key
 THEN right_key - @skew_tree
 ELSE right_key
 END
 END
 WHERE tree = OLD.tree AND
 right_key > OLD.left_key AND
 left_key < @left_key AND
 id <> OLD.id;
 SET @left_key := @left_key - @skew_tree;
 ELSE
-- Перемещение вниз по дереву:
 SET @skew_edit := @left_key - OLD.left_key;
 UPDATE _ns_tree 
 SET
 right_key = CASE WHEN left_key >= OLD.left_key
 THEN right_key + @skew_edit
 ELSE CASE WHEN right_key < OLD.left_key
 THEN right_key + @skew_tree
 ELSE right_key
 END
 END,
 `level` = CASE WHEN left_key >= OLD.left_key
 THEN `level` + @skew_level
 ELSE `level`
 END,
 left_key = CASE WHEN left_key >= OLD.left_key
 THEN left_key + @skew_edit
 ELSE CASE WHEN left_key >= @left_key
 THEN left_key + @skew_tree
 ELSE left_key
 END
 END
 WHERE tree = OLD.tree AND
 right_key >= @left_key AND
 left_key < OLD.right_key AND
 id <> OLD.id;
 END IF;
-- Дерево перестроили, остался только наш текущий узел
 SET NEW.left_key := @left_key;
 SET NEW.`level` := @level;
 SET NEW.right_key := @left_key + @skew_tree - 1;
 END IF;
 END IF;
END;

CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_del_tr` AFTER DELETE ON `ns_tree`
 FOR EACH ROW
BEGIN
-- Удаляем дочерние узлы:
 DELETE FROM _ns_tree
 WHERE 
 tree = OLD.tree AND
 left_key > OLD.left_key AND
 right_key < OLD.right_key;
-- Убираем разрыв в ключах:
 SET @skew_tree := OLD.right_key - OLD.left_key + 1;
 UPDATE _ns_tree
 SET left_key = CASE WHEN left_key > OLD.left_key
 THEN left_key - @skew_tree
 ELSE left_key
 END,
 right_key = right_key - @skew_tree
 WHERE right_key > OLD.right_key AND
 tree = OLD.tree AND
 id <> OLD.id;
END;

CREATE DEFINER = 'user'@'localhost' TRIGGER `ns_tree_before_del_tr` AFTER DELETE ON `ns_tree`
 FOR EACH ROW
BEGIN
-- Убираем разрыв в ключах:
 UPDATE _ns_tree
 SET left_key = CASE WHEN left_key < OLD.left_key
 THEN left_key
 ELSE CASE WHEN right_key < OLD.right_key
 THEN left_key - 1 
 ELSE left_key - 2
 END
 END,
 parent_id = CASE WHEN right_key < OLD.right_key AND `level` = OLD.level + 1
 THEN OLD.parent_id
 ELSE parent_id
 END,
 `level` = CASE WHEN right_key < OLD.right_key
 THEN `level` - 1 
 ELSE `level`
 END,
 right_key = CASE WHEN right_key < OLD.right_key
 THEN right_key - 1 
 ELSE right_key - 2
 END
 WHERE (right_key > OLD.right_key OR
 (left_key > OLD.left_key AND right_key < OLD.right_key)) AND
 tree = OLD.tree;
END;