Forum OpenACS Q&A: Bug in OpenACS 3.2.5 Categories
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!
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.
