Hi Jef,
* 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;
Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
