I've been modifying Categories to add group scoping (so groups can maintain their own categorized General Links etc), and I've run into a bug that I can't find addressed in these forums or in the SDM's BAFs.

The problem is in /admin/categories/add-link-to-parent.tcl. To prevent circular links in the category hierarchies when assigning a new parent to a given category, a list of child nodes is supposed to be generated so that these child nodes can be excluded from candidates for the target node's new parent:

# Find all children, grand-children, etc of category in question and
# store them in a list. The category MUST NOT have parent among any
# element in the list.

set children_list [database_to_tcl_list $db "select
h.child_category_id
from category_hierarchy h 
where category_hierarchy_level($category_id,h.child_category_id,0) is
not null"] 

The trouble is that this list yields not the children of the target node but rather its parental line. When the tree is drawn, the children are given links for new parent assignment--which causes Very Ugly Things to happen (like crashing Postgres when it hits the circular references) if one blithely clicks on a child to turn it into a new parent.

I'm surprised that this hasn't been reported before. Are people not using the Categories mechanism? Is it being completely redone in OpenACS 4?

In any case, I've tried a straigtforward if simpleminded solution by adding a new function instead of category_hierarchy_level. This addition, em_category_rev_hierarcy_level crawls the tree toward the children but only goes down a single branch. Here it is:

create function em_category_rev_hierarchy_level(integer, integer,
integer)
returns integer as '
DECLARE
        v_category_id alias for $1;
        v_start_id alias for $2;
        v_level alias for $3;
        v_child_id integer;
BEGIN
        IF v_category_id = v_start_id
        then return v_level;
        end if;

        select child_category_id into v_child_id from
category_hierarchy where parent_category_id= v_category_id;

        if v_child_id is null
        then 
             if v_start_id= 0
             then return v_level+1;
             else return null;
             end if;
        end if;

        return em_category_rev_hierarchy_level(v_child_id, v_start_id,
1+v_level);
END;
' language 'plpgsql';

Instead, this should generate a list of all the child branches from the target node. I've looked for code examples in /doc/sql/* but find nothing useful. Probably this is a simple CS 101 problem but I can't spot the solution due to my current level of dain bramage (plus I never took CS 101 I'm afraid).

Anyway, many thanks for any suggestions!