Forum OpenACS Development: Reordering tree_sortkeys again

Collapse
Posted by Peter Alberer on

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

Collapse
Posted by Patrick McNeill on
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';

Collapse
Posted by Andrei Popov on
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';