Forum OpenACS Q&A: Bug in OpenACS 3.2.5 Categories

Posted by Stan Kaufman on
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
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,
returns integer as '
        v_category_id alias for $1;
        v_start_id alias for $2;
        v_level alias for $3;
        v_child_id integer;
        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
             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,
' 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!

Posted by Stan Kaufman on
Well, here's an answer to my own question. Using this procedure

proc find_kids { db node_id kid_list } {

    upvar $kid_list my_kid_list

    set candidate_list [database_to_tcl_list $db "
      select child_category_id 
      from category_hierarchy
      where parent_category_id = '$node_id'"]

    foreach kid_id $candidate_list {
        lappend my_kid_list $kid_id
        find_kids $db $kid_id my_kid_list

called thus:

    #  The original children_list doesn't work correctly
    #  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 this 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"]

# instead use this    
set children_list [list]
find_kids $db $category_id children_list

Calling a select recursively like this is butt-ugly, but for some reason I can't figure out how to write the find_kids procedure correctly in pl/pgsql. If it's simple and obvious how to do that, thanks to anyone who can show me.