Re: Nested Sets and Delete/Exchange?

Oliver Berse wrote:
> Hi all,
>
> I use a simple nested set to repesent a hierarchical menu in SQL. I've
> read Joe Celkos article about trees at
>
> http://www.intelligententerprise.com/001020/celko.shtml
>
> and my simple menu table (in MySql) contains the fields id(bigint),
> menuname(varchar), left(int), right(int). So it's easy to build up a
> structure like this:
>
> A(1,8)
> | \
> B(2,3) C(4,7)
> |
> D(5,6)
>
> Unfortunately the reorganization of the tree is more complex and I can
> not find answers to my two questions on the net:
>
> 1. How to renumber the values for left and right after the deletion of
> an element? When I delete element C(4,7) in the tree, D and A have to
> change to D(4,5) and A(1,6). How to formulate this in SQL?
>
> 2. How to exchange two elements and their subtrees? After swapping
> elements B and C (their menu names) the tree should be structured like
> this:
>
> A(1,8)
> | \
> C(2,5) B(6,7)
> |
> D(3,4)
>
> The problem are the subtrees (D) which can hold a different number of
> elements. I tried to save the subtrees in a temporary table, delete them
> from the tree and then reinsert them under their swapped parents, but
> the copy/delete/reinsert takes a lot of SQL code. Is there any easier
> way to swap elements?

I think, you can rather easily perform the delete of one single element if you look at it as two actions:
1 Move the subtrees out from the element as siblings of the element to delete.
2 Delete the element and all of its subtrees (none left due to 1)

I'm sure that this is doable in one step, too. But you might need both actions anyway. Therefore you can just program those and combine them.

As to swapping: Here it might also be easier to do it as two actions: 1 Copy the first element incl. subtrees after the second (insert it!) 2 Delete the first element

Otherwise, you'll have to store the width of one of the elements in order to do two updates.
By the way, step 2 is identical to step 2 of the first task.

If you need actual SQL, just post what you've tries so far.

Regards,
Heinz Received on Wed Sep 24 2003 - 10:23:54 CEST