Forum OpenACS Development: Reordering tree

Posted by Peter Alberer on 05/27/02 06:33 PM

I started to use the tree_sortkey "connect by" solution for a very generic object type that is something like a "collection" for other objects. I use it to store the structure of books and am quite happy with it.

Now i also want to use it to collect other objects that do not need the hierarchical information and wonder if it is possible to reorder those keys if necessary. There is no problem with performance as the structure is very flat.

I just would like to know what there is to do programmatically (do i have to remove the triggers during such an operation ?) TIA

2: Response to Reordering tree_sortkeys again (response to 1)

Posted by Patrick McNeill on 05/27/02 08:11 PM

I'm using tree_sortkeys as a normal sort key as well, and have written the following function to swap two positions. It swaps the two nodes, as well as any children. I'm not a plpgsql expert (only a couple months experience with it), so YMMV, but here it is:

create function brk_class_dev_nodes__swap(integer, integer)
returns integer as '
declare
p_node_id_1 alias for $1;
p_node_id_2 alias for $2;

v_sortkey_1 varbit;
v_sortkey_2 varbit;
begin
select tree_sortkey into v_sortkey_1
from brk_class_dev_nodes
where node_id = p_node_id_1;

select tree_sortkey into v_sortkey_2
from brk_class_dev_nodes
where node_id = p_node_id_2;

if length(v_sortkey_1) != length(v_sortkey_2) then
raise exception ''cannot reorder nodes at different tree levels'';
end if;

-- swap the initial part of any sortkeys that match the
-- above (hence are the nodes themselves or child nodes
update brk_class_dev_nodes
set tree_sortkey =
case when substring(tree_sortkey from 1 for length(v_sortkey_2)) = v_sortkey_2
then v_sortkey_1 || substring(tree_sortkey from length(v_sortkey_1) + 1 for length(tree_sortkey))
when substring(tree_sortkey from 1 for length(v_sortkey_1)) = v_sortkey_1
then v_sortkey_2 || substring(tree_sortkey from length(v_sortkey_2) + 1 for length(tree_sortkey))
else tree_sortkey
end;

return 0;
end;' language 'plpgsql';

3: Response to Reordering tree_sortkeys again (response to 1)

Posted by Andrei Popov on 05/31/02 05:14 PM

for the benefit of those who come later...

CREATE FUNCTION brk_class_dev_nodes__swap(integer, integer)
RETURNS integer as '
DECLARE
    p_node_id_1 alias for $1;
    p_node_id_2 alias for $2;
    v_sortkey_1 varbit;
    v_sortkey_2 varbit;
BEGI
    SELECT tree_sortkey INTO v_sortkey_1 FROM brk_class_dev_nodes WHERE node_id = p_node_id_1;

    SELECT tree_sortkey INTO v_sortkey_2 FROM brk_class_dev_nodes WHERE node_id = p_node_id_2;

    IF length(v_sortkey_1) != length(v_sortkey_2)
    THEN 
        RAISE EXCEPTION ''cannot reorder nodes at different tree levels'';
    END IF;

    -- swap the initial part of any sortkeys that match the 
    -- above (hence are the nodes themselves or child nodes)

    UPDATE brk_class_dev_nodes
    SET tree_sortkey = 
        CASE WHEN substring(tree_sortkey from 1 for length(v_sortkey_2)) = v_sortkey_2 THEN
                  v_sortkey_1 || substring(tree_sortkey from length(v_sortkey_1) + 1 for length(tree_sortkey))
             WHEN substring(tree_sortkey from 1 for length(v_sortkey_1)) = v_sortkey_1 THEN
                  v_sortkey_2 || substring(tree_sortkey from length(v_sortkey_2) + 1 for length(tree_sortkey)) 
        ELSE tree_sortkey end;

    RETURN 0; 
END;' language 'plpgsql';

Forum OpenACS Development: Reordering tree_sortkeys again