Re: Joe Celko Function

Hi Again

I can use your method but Celko gave a better one that look for the gaps it
self using view and loop untill all gaps are closed.

This is a better way as I can run this function after many actions ( like
moving branches ) without giving the GAPS function any variable.
But still I have problems with the while :(

Here is the snip of what i did till now:

CREATE VIEW flatree (visit)
AS SELECT lft from tree
UNION
SELECT rgt FROM tree;
---------------------------------

CREATE VIEW firstvisit (visit)
AS SELECT (visit +1) from flatree
WHERE (visit +1) NOT IN ( SELECT visit FROM flatree )
AND (visit +1) > 0;
---------------------------------

CREATE VIEW lastvisit (visit)
AS SELECT (visit - 1) from flatree
WHERE (visit - 1) NOT IN ( SELECT visit FROM flatree )
AND (visit - 1) < 2 * ( SELECT COUNT (*) FROM tree );
---------------------------------

CREATE VIEW gaps (start, finish, size)
AS SELECT f1.visit, l1.visit, ( ( l1.visit - f1.visit ) + 1 )
FROM firstvisit AS f1, lastvisit AS l1
WHERE l1.visit = ( SELECT MIN ( l2.visit ) FROM lastvisit AS l2 WHERE f1.visit
<= l2.visit );
---------------------------------

BEGIN
WHILE EXISTS ( SELECT * FROM gaps )
LOOP UPDATE frammis -- this frammis is strange as it not mentioned any where
in the chapter, is it the table name ? or special var ?
SET rgt = CASE WHEN rgt > ( SELECT MIN(start) FROM gaps )
THEN rgt - 1 ELSE rgt END,
lft = CASE WHEN lft > ( SELECT MIN(start) FROM gaps )
THEN lft - 1 ELSE lft END;
END WHILE;

> To drop branches, I typically loop through this function.  But it would be
> easy to extend this case to drop an entire branch at once.  You just need
> to know what the offset is.  If you are dropping a whole brach, it's
> actually an easier case, because you don't have to worry about shifting
> lower nodes on the branch (nodes that appear between the lft and rgt of the
> node you dropped). So if the lft is 50 and the rgt is 60, everyone else's
> numbers would just shift down 11. (The former lft 61 should become lft 50,
> etc...).
>
> -Fran