MySQL 在嵌套集中移动节点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/889527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Move node in nested set
提问by Ivar
I'd need a MySQL query that moves a node and all its children within a nested set. I found thissite, but that function just seems so illogical - there's no universeidor treeidin a nested set model, and the code itself is just longer than what feels required. The only extra column I've got in the table is parent.
我需要一个 MySQL 查询来移动嵌套集中的节点及其所有子节点。我找到了这个网站，但该功能似乎很不合逻辑 - 没有universeid或treeid在嵌套集模型中，并且代码本身比感觉所需的要长。我在表中唯一的额外列是parent.
I couldn't just remove and add the node again since it will loose its ID.
我不能只是删除并再次添加节点，因为它会丢失其 ID。
回答by Arturas Smorgun
I see, that this topic is quite old, but anyway it's still unanswered. I got here from Google, and found no direct answer to this question.
我明白了，这个话题已经很老了，但无论如何它仍然没有答案。我从谷歌来到这里，并没有找到这个问题的直接答案。
So, after a little research I found quite easy solution.
所以，经过一些研究，我发现了很简单的解决方案。
Everything, what we gonna need to move our node is: node left and right positions, new parent node right position. The node to the new position then can be moved in four easy steps:
一切，我们需要移动我们的节点是：节点左右位置，新父节点右侧位置。然后可以通过四个简单的步骤将节点移动到新位置：
- Change positions of node and all it's sub nodes into negative values, which are equal to current ones by module.
- Move all positions "up", which are more, that pos_right of current node.
- Move all positions "down", which are more, that pos_right of new parent node.
- Change positions of current node and all it's subnodes, so that it's now will be exactly "after" (or "down") of new parent node.
- 将节点及其所有子节点的位置更改为负值，按模块等于当前值。
- 将所有位置“向上”移动，更多的是当前节点的 pos_right。
- 将所有位置“向下”移动，更多的是新父节点的 pos_right。
- 更改当前节点及其所有子节点的位置，使其现在正好位于新父节点的“之后”（或“向下”）。
That's theory, now - this algorithm realization in MySQL (example using PHP):
这是理论，现在 - 这个算法在 MySQL 中的实现（使用 PHP 的例子）：
-- step 0: Initialize parameters.
SELECT
@node_id := 1, --put there id of moving node
@node_pos_left := 0, --put there left position of moving node
@node_pos_right := 1, --put there right position of moving node
@parent_id := 2, --put there id of new parent node (there moving node should be moved)
@parent_pos_right := 4; --put there right position of new parent node (there moving node should be moved)
SELECT
@node_size := @node_pos_right - @node_pos_left + 1; -- 'size' of moving node (including all it's sub nodes)
-- step 1: temporary "remove" moving node
UPDATE `list_items`
SET `pos_left` = 0-(`pos_left`), `pos_right` = 0-(`pos_right`)
WHERE `pos_left` >= @node_pos_left AND `pos_right` <= @node_pos_right;
-- step 2: decrease left and/or right position values of currently 'lower' items (and parents)
UPDATE `list_items`
SET `pos_left` = `pos_left` - @node_size
WHERE `pos_left` > @node_pos_right;
UPDATE `list_items`
SET `pos_right` = `pos_right` - @node_size
WHERE `pos_right` > @node_pos_right;
-- step 3: increase left and/or right position values of future 'lower' items (and parents)
UPDATE `list_items`
SET `pos_left` = `pos_left` + @node_size
WHERE `pos_left` >= IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_size, @parent_pos_right);
UPDATE `list_items`
SET `pos_right` = `pos_right` + @node_size
WHERE `pos_right` >= IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_size, @parent_pos_right);
-- step 4: move node (ant it's subnodes) and update it's parent item id
UPDATE `list_items`
SET
`pos_left` = 0-(`pos_left`)+IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_pos_right - 1, @parent_pos_right - @node_pos_right - 1 + @node_size),
`pos_right` = 0-(`pos_right`)+IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_pos_right - 1, @parent_pos_right - @node_pos_right - 1 + @node_size)
WHERE `pos_left` <= 0-@node_pos_left AND `pos_right` >= 0-@node_pos_right;
UPDATE `list_items`
SET `parent_item_id` = @parent_id
WHERE `item_id` = @node_id;
Please beware - there still may be some syntax errors in SQL code, because I actually use this algorithm in PHP like this:
请注意 - SQL 代码中仍然可能存在一些语法错误，因为我实际上在 PHP 中使用这种算法是这样的：
$iItemId = 1;
$iItemPosLeft = 0;
$iItemPosRight = 1;
$iParentId = 2;
$iParentPosRight = 4;
$iSize = $iPosRight - $iPosLeft + 1;
$sql = array(
// step 1: temporary "remove" moving node
'UPDATE `list_items`
SET `pos_left` = 0-(`pos_left`), `pos_right` = 0-(`pos_right`)
WHERE `pos_left` >= "'.$iItemPosLeft.'" AND `pos_right` <= "'.$iItemPosRight.'"',
// step 2: decrease left and/or right position values of currently 'lower' items (and parents)
'UPDATE `list_items`
SET `pos_left` = `pos_left` - '.$iSize.'
WHERE `pos_left` > "'.$iItemPosRight.'"',
'UPDATE `list_items`
SET `pos_right` = `pos_right` - '.$iSize.'
WHERE `pos_right` > "'.$iItemPosRight.'"',
// step 3: increase left and/or right position values of future 'lower' items (and parents)
'UPDATE `list_items`
SET `pos_left` = `pos_left` + '.$iSize.'
WHERE `pos_left` >= "'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iSize : $iParentPosRight).'"',
'UPDATE `list_items`
SET `pos_right` = `pos_right` + '.$iSize.'
WHERE `pos_right` >= "'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iSize : $iParentPosRight).'"',
// step 4: move node (ant it's subnodes) and update it's parent item id
'UPDATE `list_items`
SET
`pos_left` = 0-(`pos_left`)+'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iItemPosRight - 1 : $iParentPosRight - $iItemPosRight - 1 + $iSize).',
`pos_right` = 0-(`pos_right`)+'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iItemPosRight - 1 : $iParentPosRight - $iItemPosRight - 1 + $iSize).'
WHERE `pos_left` <= "'.(0-$iItemPosLeft).'" AND i.`pos_right` >= "'.(0-$iItemPosRight).'"',
'UPDATE `list_items`
SET `parent_item_id` = "'.$iParentItemId.'"
WHERE `item_id`="'.$iItemId.'"'
);
foreach($sql as $sqlQuery){
mysql_query($sqlQuery);
}
Please note also, that code may be optimized, but I going to leave it like that for better readability. Also consider table locking if you are using nested sets in multi-user systems.
另请注意，该代码可能已优化，但为了更好的可读性，我将保留它。如果您在多用户系统中使用嵌套集，还要考虑表锁定。
Hope that my message will help to anyone, who will search for a solution after me. Any comments and corrections are also welcome.
希望我的信息对任何会在我之后寻找解决方案的人有所帮助。也欢迎任何评论和更正。
回答by Roger Keays
Here is a solution that lets you move a node to any position in the tree, either as a sibling or a child with just a single input parameter - the new left position (newlpos) of the node.
这是一个解决方案，可让您将节点移动到树中的任何位置，作为兄弟节点或子节点，只需一个输入参数 - 节点的新左位置 (newlpos)。
Fundamentally there are three steps:
基本上分为三个步骤：
- Create new space for the subtree.
- Move the subtree into this space.
- Remove the old space vacated by the subtree.
- 为子树创建新空间。
- 将子树移动到这个空间。
- 删除子树腾出的旧空间。
In psuedo-sql, it looks like this:
在 psuedo-sql 中，它看起来像这样：
//
* -- create new space for subtree
* UPDATE tags SET lpos = lpos + :width WHERE lpos >= :newlpos
* UPDATE tags SET rpos = rpos + :width WHERE rpos >= :newlpos
*
* -- move subtree into new space
* UPDATE tags SET lpos = lpos + :distance, rpos = rpos + :distance
* WHERE lpos >= :tmppos AND rpos < :tmppos + :width
*
* -- remove old space vacated by subtree
* UPDATE tags SET lpos = lpos - :width WHERE lpos > :oldrpos
* UPDATE tags SET rpos = rpos - :width WHERE rpos > :oldrpos
*/
The :distance variable is the distance between the new and old positions, the :width is the size of the subtree, and :tmppos is used to keep track of the subtree being moved during the updates. These variables are defined as:
:distance 变量是新旧位置之间的距离， :width 是子树的大小， :tmppos 用于跟踪更新期间移动的子树。这些变量定义为：
// calculate position adjustment variables
int width = node.getRpos() - node.getLpos() + 1;
int distance = newlpos - node.getLpos();
int tmppos = node.getLpos();
// backwards movement must account for new space
if (distance < 0) {
distance -= width;
tmppos += width;
}
For a complete code example, see my blog at
有关完整的代码示例，请参阅我的博客
http://www.ninthavenue.com.au/how-to-move-a-node-in-nested-sets-with-sql
http://www.ninthavenue.com.au/how-to-move-a-node-in-nested-sets-with-sql
If you like this solution, please up-vote.
如果你喜欢这个解决方案，请投票。
回答by ETFairfax
I know this is an old question, but I've just used the answer myself but for SQL Server. Should anyone want it, here is the code for a SQL Server Stored Proc based on the accepted answer.
我知道这是一个老问题，但我自己只是使用了 SQL Server 的答案。如果有人想要它，这里是基于接受的答案的 SQL Server 存储过程的代码。
CREATE PROCEDURE [dbo].[Item_Move]
@id uniqueidentifier,
@destinationId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
declare @moverLeft int,
@moverRight int,
@destinationRight int,
@node_size int
-- step 0: Initialize parameters.
SELECT
@moverLeft = leftExtent,
@moverRight = rightExtent
FROM
Item
WHERE
id = @id
SELECT
@destinationRight = rightExtent
FROM
Item
WHERE
id = @destinationId
SELECT
@node_size = @moverRight - @moverLeft + 1; -- 'size' of moving node (including all it's sub nodes)
-- step 1: temporary "remove" moving node
UPDATE Item
SET leftExtent = 0-(leftExtent), rightExtent = 0-(rightExtent), updatedDate = GETDATE()
WHERE leftExtent >= @moverLeft AND rightExtent <= @moverRight;
-- step 2: decrease left and/or right position values of currently 'lower' items (and parents)
UPDATE Item
SET leftExtent = leftExtent - @node_size, updatedDate = GETDATE()
WHERE leftExtent > @moverRight;
UPDATE Item
SET rightExtent = rightExtent - @node_size, updatedDate = GETDATE()
WHERE rightExtent > @moverRight;
-- step 3: increase left and/or right position values of future 'lower' items (and parents)
UPDATE Item
SET leftExtent = leftExtent + @node_size, updatedDate = GETDATE()
WHERE leftExtent >= CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @node_size ELSE @destinationRight END;
UPDATE Item
SET rightExtent = rightExtent + @node_size, updatedDate = GETDATE()
WHERE rightExtent >= CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @node_size ELSE @destinationRight END;
-- step 4: move node (and it's subnodes) and update it's parent item id
UPDATE Item
SET
leftExtent = 0-(leftExtent) + CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @moverRight - 1 ELSE @destinationRight - @moverRight - 1 + @node_size END,
rightExtent = 0-(rightExtent) + CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @moverRight - 1 ELSE @destinationRight - @moverRight - 1 + @node_size END,
updatedDate = GETDATE()
WHERE leftExtent <= 0-@moverLeft AND rightExtent >= 0-@moverRight;
UPDATE Item
SET parentId = @destinationId, updatedDate = GETDATE()
WHERE id = @id;
END
回答by Bill Karwin
Moving subtrees around is very expensive and complex in the Nested Sets design.
在嵌套集设计中，移动子树非常昂贵且复杂。
You should consider a different design for representing trees.
您应该考虑采用不同的设计来表示树木。
For example, if you use the Path Enumeration design, you store the list of direct ancestors of each node as a concatenated string.
例如，如果使用路径枚举设计，则将每个节点的直接祖先列表存储为串联字符串。
id path
1 1/
2 1/2/
3 1/3/
4 1/3/4/
5 1/3/5/
Then moving a subtree (say node 3 moves to be a child of node 2):
然后移动子树（假设节点 3 移动为节点 2 的子节点）：
UPDATE Tree t
JOIN Tree node2 ON (node2.id = 2)
JOIN Tree node3 ON (node3.id = 3)
SET t.path = CONCAT(node2.path, REPLACE(t.path, node3.path, node2.path))
WHERE t.path LIKE CONCAT(node3.path, '%');
回答by Quassnoi
See the article in my blog for storing and using hierarchical data in MySQL:
请参阅我博客中有关在以下位置存储和使用分层数据的文章MySQL：
To move a whole branch in such a table, you'll just need to update the root's parent(a single row)
要在这样的表中移动整个分支，您只需要更新根parent（单行）
You'll need to create a function:
您需要创建一个函数：
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE _id INT;
DECLARE _parent INT;
DECLARE _next INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
SET _parent = @id;
SET _id = -1;
IF @id IS NULL THEN
RETURN NULL;
END IF;
LOOP
SELECT MIN(id)
INTO @id
FROM t_hierarchy
WHERE parent = _parent
AND id > _id;
IF @id IS NOT NULL OR _parent = @start_with THEN
SET @level = @level + 1;
RETURN @id;
END IF;
SET @level := @level - 1;
SELECT id, parent
INTO _id, _parent
FROM t_hierarchy
WHERE id = _parent;
END LOOP;
END
and use it in a query:
并在查询中使用它：
SELECT CONCAT(REPEAT(' ', level - 1), CAST(hi.id AS CHAR)) AS treeitem, parent, level
FROM (
SELECT hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
FROM (
SELECT @start_with := 0,
@id := @start_with,
@level := 0
) vars, t_hierarchy
WHERE @id IS NOT NULL
) ho
JOIN t_hierarchy hi
ON hi.id = ho.id
回答by postfuturist
I have a stored procedure that moves a node in a nested set to a new parent node. I am using a table called "category" in a MySQL / InnoDB database called "somedb". Of course, if the destination is a subcategory of the category you want to move this procedure will screw things up, so make sure that you aren't trying to embed a node inside of itself. I will leave it as an exercise to the reader to make this procedure safe for that case.
我有一个存储过程，可以将嵌套集中的节点移动到新的父节点。我在名为“somedb”的 MySQL/InnoDB 数据库中使用名为“category”的表。当然，如果目标是您要移动的类别的子类别，则此过程会将事情搞砸，因此请确保您没有尝试在其内部嵌入节点。我将把它留给读者作为练习，以确保此过程在这种情况下是安全的。
CREATE PROCEDURE `somedb`.`moveCatParent` (IN cat_a VARCHAR(45), IN cat_b VARCHAR(45))
BEGIN
START TRANSACTION;
/* cat_b.lft + 1 is the destination. */
SELECT @destination := (lft + 1)
FROM category
WHERE name = cat_b;
SELECT @cat_a_width := ((rgt - lft) + 1)
FROM category
WHERE name = cat_a;
/* Rip this table a new cat_a sized hole inside cat_b. */
UPDATE category SET rgt = rgt + @cat_a_width WHERE rgt >= @destination;
UPDATE category SET lft = lft + @cat_a_width WHERE lft >= @destination;
SELECT @cat_a_lft := lft, @cat_a_rgt := rgt
FROM category
WHERE name = cat_a;
SELECT @diff := @destination - @cat_a_lft;
/* Move cat_a and all inhabitants to new hole */
UPDATE category SET rgt = rgt + @diff WHERE rgt BETWEEN @cat_a_lft AND @cat_a_rgt;
UPDATE category SET lft = lft + @diff WHERE lft BETWEEN @cat_a_lft AND @cat_a_rgt;
/* Close the gap created when we moved cat_a. */
UPDATE category SET rgt = rgt - @cat_a_width WHERE rgt >= @cat_a_lft;
UPDATE category SET lft = lft - @cat_a_width WHERE lft >= @cat_a_lft;
COMMIT;
END
回答by user1401768
It is quite simple, first define a stored procedure:
很简单，先定义一个存储过程：
CREATE DEFINER=`root`@`localhost` PROCEDURE `move_item`(
IN itemId BIGINT, IN kind SMALLINT,
IN newSiblingId BIGINT UNSIGNED, IN newSiblingKind SMALLINT, IN newParentId BIGINT UNSIGNED,
IN jobId BIGINT UNSIGNED, IN companyId BIGINT UNSIGNED,
OUT outSucess SMALLINT UNSIGNED)
proc_label:BEGIN
next we need some local variables:
接下来我们需要一些局部变量：
DECLARE oldLeft, oldRight, newLeft, newRight, itemWidth, moveBy INT UNSIGNED DEFAULT 0;
set outSucess =0;
Now get our old left & right and get the width
现在得到我们旧的左右并得到宽度
SELECT `LFT`, `RGT` into oldLeft, oldRight from `nodes` where `ID`=itemId LIMIT 1;
SET itemWidth = oldRight - oldLeft + 1;
Now take them "out of the tree" by multiplying by -1
现在通过乘以 -1 将它们“从树中”取出
UPDATE `nodes` SET `RGT`=`RGT`* -1, `LFT`=`LFT`* -1 WHERE ``LFT` BETWEEN oldLeft and oldRight;
The next part is not necessary as the tree will work without it, but it is neat; close the old gap:
下一部分不是必需的，因为没有它树也能工作，但它很整洁；缩小旧差距：
-- Update right
UPDATE `nodes` SET `RGT` = `RGT` - itemWidth WHERE `RGT` > oldRight;
-- Update left
UPDATE `nodes` SET `LFT` = `LFT` - itemWidth WHERE `LFT` > oldRight;
Now find the new location:
现在找到新位置：
SELECT (`RGT`+1) into newLeft from `nodes` where `ID`=newSiblingId LIMIT 1;
-- No sibling, so make it last in parent
IF (newLeft = 0) AND (newParentId != 0) THEN
SELECT `RGT` into newLeft from `nodes` WHERE `ID`=newParentId LIMIT 1;
END IF;
-- If no previous sibling or parent, set to first item in tree
IF (newLeft=0) OR (newLeft=NULL) THEN SET newLeft=1;
END IF;
Now make some space:
现在腾出一些空间：
-- Update right
UPDATE `nodes` SET `RGT` = `RGT` + itemWidth WHERE `RGT` >= newLeft;
-- Update left
UPDATE `nodes` SET `LFT` = `LFT` + itemWidth WHERE `LFT` >= newLeft;
Finally move the nodes that where shifted out of the tree back in by * -1, and while you are at it, move them to the correct location as well:
最后将移出树的节点按 * -1 移回，当你在那里时，也将它们移到正确的位置：
SET moveBy = OldLeft - NewLeft;
UPDATE `nodes` SET `RGT`=(`RGT`* -1)-moveBy, `LFT`=(`LFT`* -1)-moveBy WHERE `LFT` < 0;
set outSucess =1;
Not tested, pasted and adjusted and simplified from a working routine.
未经测试、粘贴和调整，并从工作程序中简化。
回答by Klidi Spiro
I know this post is old but im posting this solution for every one else that will get here to see a solution.I found this @ sedna-soft.de . I tested id and works perfectly
我知道这篇文章很旧，但我将这个解决方案发布给每个到这里来查看解决方案的其他人。我找到了这个@sedna-soft.de。我测试了 id 并且完美运行
-- moves a subtree before the specified position
-- if the position is the rgt of a node, the subtree will be its last child
-- if the position is the lft of a node, the subtree will be inserted before
-- @param l the lft of the subtree to move
-- @param r the rgt of the subtree to move
-- @param p the position to move the subtree before
SET @r: , @l: , @p:
update tree
set
lft = lft + if (@p > @r,
if (@r < lft and lft < @p,
@l - @r - 1,
if (@l <= lft and lft < @r,
@p - @r - 1,
0
)
),
if (@p <= lft and lft < @l,
@r - @l + 1,
if (@l <= lft and lft < @r,
@p - @l,
0
)
)
),
rgt = rgt + if (@p > @r,
if (@r < rgt and rgt < @p,
@l - @r - 1,
if (@l < rgt and rgt <= @r,
@p - @r - 1,
0
)
),
if (@p <= rgt and rgt < @l,
@r - @l + 1,
if (@l < rgt and rgt <= @r,
@p - @l,
0
)
)
)
where @r < @p or @p < @l;
回答by Hisham Javed
# Get Left and Right offsets of both source node (Drag Node) and target node (Drop off Node).
SELECT lft,rgt INTO @sourceNodeLft,@sourceNodeRgt FROM treetest WHERE id=_sourceNodeId;
SELECT lft,rgt INTO @targetNodeLft,@targetNodeRgt FROM treetest WHERE id=_targetNodeId;
# Determine node order direction
SET @direction := IF(@targetNodeLft<@sourceNodeLft,'UP','DOWN');
# Determine with of source node (Drag Node)
SET @width := @sourceNodeRgt - @sourceNodeLft + 1;
# Mark all displaced nodes with negative lft and rgt
UPDATE treetest SET lft = 0-lft, rgt = 0-rgt
WHERE lft >= @targetNodeLft AND rgt <= @targetNodeRgt;
UPDATE treetest SET lft = 0-lft, rgt = 0-rgt
WHERE lft >= @sourceNodeLft AND rgt <= @sourceNodeRgt;
# Update left and right offsets of inner nodes between source (Drag Node)and target (Drop off) node
UPDATE treetest SET lft = (lft + IF(@direction = 'UP',@width,0-@width)),
rgt = (rgt + IF(@direction = 'UP',@width,0-@width))
WHERE lft > IF(@direction = 'UP', @targetNodeLft, @sourceNodeLft)
AND rgt < IF(@direction = 'UP', @sourceNodeLft, @targetNodeLft);
# Update source (Drag) Node and its children offsets
SET @sourceOffset := IF(@direction = 'UP',@targetNodeLft - @sourceNodeLft, @targetNodeRgt - @width - @sourceNodeLft+1);
UPDATE treetest SET lft = 0 - lft + @sourceOffset,
rgt = 0 - rgt + @sourceOffset
WHERE (0-lft) >= @sourceNodeLft AND (0-rgt) <= @sourceNodeRgt;
# Update target (Drop off) node and its children offsets
SET @targetOffset := IF(@direction = 'UP', 0 - @width,@width);
UPDATE treetest SET lft = 0 - (lft + @targetOffset),
rgt = 0 - (rgt + @targetOffset)
WHERE (0-lft) >= @targetNodeLft AND (0-rgt) <= @targetNodeRgt;
回答by Ivan Volpin
There are many answers already, but I feel like mine can be useful for someone. Based on answer of Roger Keays (thank you very much!), I wrote stored procedures for mySQL database:
已经有很多答案了，但我觉得我的对某人有用。根据 Roger Keays 的回答（非常感谢！），我为 mySQL 数据库编写了存储过程：
-- to move target before specified node
CREATE DEFINER=`root`@`%` PROCEDURE `move_before`(IN target_id int, before_id int)
BEGIN
SELECT @new_pos := lft FROM dirs WHERE id = before_id;
CALL move(target_id, @new_pos);
END
-- to move target after specified node
CREATE DEFINER=`root`@`%` PROCEDURE `move_after`(IN target_id int, after_id int)
BEGIN
SELECT @new_pos := rgt + 1 FROM dirs WHERE id = after_id;
CALL move(target_id, @new_pos);
END
-- to move target to the specified node
CREATE DEFINER=`root`@`%` PROCEDURE `move_in`(IN target_id int, parent_id int)
BEGIN
SELECT @new_pos := rgt FROM dirs WHERE id = parent_id;
CALL move(target_id, @new_pos);
END
--main procedure to move target before position
CREATE DEFINER=`root`@`%` PROCEDURE `move`(in target_id int, in new_pos int)
BEGIN
SELECT @oldlft := lft, @oldrgt := rgt
FROM dirs
WHERE target_id = id;
SET @width := @oldrgt - @oldlft +1;
SET @distance := new_pos - @oldlft;
SET @tmppos := @oldlft;
IF (@distance <0)
THEN
SELECT @distance := @distance - @width;
SELECT @tmppos := @tmppos + @width;
END IF;
-- create new space for subtree
UPDATE dirs SET lft = lft + @width WHERE lft >= new_pos;
UPDATE dirs SET rgt = rgt + @width WHERE rgt >= new_pos;
-- move subtree into new space
UPDATE dirs SET lft = lft + @distance, rgt = rgt + @distance
WHERE lft >= @tmppos AND rgt < @tmppos + @width;
-- remove old space vacated by subtree
UPDATE dirs SET lft = lft - @width WHERE lft > @oldrgt;
UPDATE dirs SET rgt = rgt - @width WHERE rgt > @oldrgt;
END
