A. Pagaltzis wrote:
* Jef Driesen <[EMAIL PROTECTED]> [2007-04-11 16:10]:
I managed to solve this problem now. I had to write my solution
(see below) in pseudo sql code (e.g some extra non-sql code was
required) because sqlite does not support stored procedures. It
think it is doable to incorporate the IF/THEN/ELSE inside the
sql query, but I didn't try to do that.
thanks for posting that. I rewrote your code a little because
single-letter variable names make code hard to read; this version
should make it more obvious what’s actually being computed. Also,
I made the conditionals more restrictive, so that the code will
not silently mangle data if you ask it to move a node onto itself
or under one of its own descendants.
IF @src_lft < @dst_lft AND @src_lft < @dst_rgt THEN
direction = 1;
affected_lft = @src_lft;
displaced_lft = @src_rgt + 1;
displaced_rgt = @dst_rgt - 1;
affected_rgt = @dst_rgt - 1;
ELSIF @src_lft > @dst_lft THEN
direction = -1;
affected_lft = @dst_rgt;
displaced_lft = @dst_rgt;
displaced_rgt = @src_lft - 1;
affected_rgt = @src_rgt;
ELSE
THROW "Illegal move"
END IF;
src_move_offset = @direction * (@displaced_rgt - @displaced_lft + 1);
displace_width = [EMAIL PROTECTED] * (@src_rgt - @src_lft + 1);
UPDATE tree SET lft = CASE
WHEN lft BETWEEN @src_lft AND @src_rgt THEN
lft + @src_move_offset
ELSE
lft + @displace_width
END
WHERE lft BETWEEN @affected_lft AND @affected_rgt;
UPDATE tree SET rgt = CASE
WHEN rgt BETWEEN @src_lft AND @src_rgt THEN
rgt + @src_move_offset
ELSE
rgt + @displace_width
END
WHERE rgt BETWEEN @affected_lft AND @affected_rgt;
Your version is indeed easier to understand then mine. (I started mine
from a drawing on paper. And at that time, I had no idea about the
meaning of the final variables, so I used a letter for each new
variable.) Now that we are talking about names, affected_lft/rgt and
displaced_lft/rgt do not always correspond to lft and rgt values. A
better choice would be 'first' and 'last'. But what's in a name ;-)
Anyway that's not the reason why I'm writing this.
I think your conditionals are incorrect (and mine were incorrect too).
It is best explained with an example image [1]. Moving "Plasma" (lft=7,
rgt=8) under "Televisions" (lft=2, rgt=9) should do nothing, since it is
already in place, but your code attempts to move it in the wrong direction!
[1] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
If I didn't make a mistake, there are 3 special cases:
A. dst is inside src (dst_rgt between src_lft and src_rgt): move is illegal
B. src and dst are equal (src_lft = dst_lft and src_rgt = dst_rgt): move
is not strictly illegal, but more like a null operation.
C. src is a direct child of dst: technically the src is already in place
and this is also a null operation. But this situation is difficult to
detect properly (without additional queries). And performing the move
anyway has the nice effect that src is moved to the end of the children
of dst. That makes the behavior consistent with the idea of a delete
followed by an insert. The only exception is when src is already at the
end (src_rgt = dst_rgt-1), which is easy to detect.
This means I now have:
IF @dst= @src THEN
RETURN;
END IF;
IF @dst_rgt = @src_rgt + 1 THEN
RETURN;
END IF;
IF @dst_rgt BETWEEN @src_lft AND @src_rgt THEN
THROW "Illegal move";
END IF;
IF @src_rgt < @dst_rgt THEN
direction = 1;
affected_lft = @src_lft;
displaced_lft = @src_rgt + 1;
displaced_rgt = @dst_rgt - 1;
affected_rgt = @dst_rgt - 1;
ELSE
direction = -1;
affected_lft = @dst_rgt;
displaced_lft = @dst_rgt;
displaced_rgt = @src_lft - 1;
affected_rgt = @src_rgt;
END IF;
And the rest remains the same.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
