Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v diff -u -r1.49 -r1.49.2.1 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 23 Feb 2005 11:52:20 -0000 1.49 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 3 Oct 2005 18:17:10 -0000 1.49.2.1 @@ -220,7 +220,8 @@ constraint cr_items_storage_type check (storage_type in ('lob','text','file')), storage_area_key varchar(100) default 'CR_FILES' not null, - tree_sortkey varbit + tree_sortkey varbit not null, + max_child_sortkey varbit ); create index cr_items_by_locale on cr_items(locale); @@ -230,8 +231,8 @@ create unique index cr_items_unique_name on cr_items(parent_id, name); create unique index cr_items_unique_id on cr_items(parent_id, item_id); create index cr_items_by_parent_id on cr_items(parent_id); -create index cr_sortkey_idx on cr_items(tree_sortkey); create index cr_items_name on cr_items(name); +create unique index cr_items_tree_sortkey_un on cr_items(tree_sortkey); -- content-create.sql patch -- @@ -272,36 +273,39 @@ create function cr_items_tree_insert_tr () returns opaque as ' declare - v_parent_sk varbit default null; - v_max_value integer; - v_parent_id integer; + v_parent_sk varbit default null; + v_max_child_sortkey varbit; + v_parent_id integer default null; begin - -- Lars: If the parent is not a cr_item, we treat it as if it was null. select item_id into v_parent_id from cr_items where item_id = new.parent_id; - if v_parent_id is null then + if new.parent_id = 0 then + + new.tree_sortkey := int_to_tree_key(new.item_id+1000); - -- Lars: Treat all items with a non-cr_item parent as one big pool wrt tree_sortkeys - -- The old algorithm had tree_sortkeys start from zero for each different parent + elsif v_parent_id is null then - select max(tree_leaf_key_to_int(child.tree_sortkey)) into v_max_value - from cr_items child - where not exists (select 1 from cr_items where child.parent_id = item_id); - else - select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value - from cr_items - where parent_id = new.parent_id; + new.tree_sortkey := int_to_tree_key(new.parent_id+1000) || int_to_tree_key(new.item_id+1000); - select tree_sortkey into v_parent_sk - from cr_items - where item_id = new.parent_id; - end if; + else - new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value); + SELECT tree_sortkey, tree_increment_key(max_child_sortkey) + INTO v_parent_sk, v_max_child_sortkey + FROM cr_items + WHERE item_id = new.parent_id + FOR UPDATE; + UPDATE cr_items + SET max_child_sortkey = v_max_child_sortkey + WHERE item_id = new.parent_id; + + new.tree_sortkey := v_parent_sk || v_max_child_sortkey; + + end if; + return new; end;' language 'plpgsql'; @@ -311,11 +315,10 @@ create function cr_items_tree_update_tr () returns opaque as ' declare - v_parent_sk varbit default null; - v_max_value integer; - p_id integer; - v_rec record; - clr_keys_p boolean default ''t''; + v_parent_sk varbit default null; + v_max_child_sortkey varbit; + v_parent_id integer default null; + v_old_parent_length integer; begin if new.item_id = old.item_id and ((new.parent_id = old.parent_id) or @@ -325,49 +328,37 @@ end if; - for v_rec in select item_id - from cr_items - where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey) - order by tree_sortkey - LOOP - if clr_keys_p then - update cr_items set tree_sortkey = null - where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey); - clr_keys_p := ''f''; - end if; - - -- Lars: If the parent is not a cr_item, we treat it as if it was null. - select parent.item_id - into p_id - from cr_items parent, - cr_items child - where child.item_id = v_rec.item_id - and parent.item_id = child.parent_id; + select item_id + into v_parent_id + from cr_items + where item_id = new.parent_id; - if p_id is null then + -- the tree sortkey is going to change so get the new one and update it and all its + -- children to have the new prefix... + v_old_parent_length := length(new.tree_sortkey) + 1; - -- Lars: Treat all items with a non-cr_item parent as one big pool wrt tree_sortkeys - -- The old algorithm had tree_sortkeys start from zero for each different parent + if new.parent_id = 0 then + v_parent_sk := int_to_tree_key(new.item_id+1000); + elsif v_parent_id is null then + v_parent_sk := int_to_tree_key(new.parent_id+1000) || int_to_tree_key(new.item_id+1000); + else + SELECT tree_sortkey, tree_increment_key(max_child_sortkey) + INTO v_parent_sk, v_max_child_sortkey + FROM cr_items + WHERE item_id = new.parent_id + FOR UPDATE; - select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value - from cr_items child - where not exists (select 1 from cr_items where child.parent_id = item_id); - else - select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value - from cr_items - where parent_id = p_id; + UPDATE cr_items + SET max_child_sortkey = v_max_child_sortkey + WHERE item_id = new.parent_id; - select tree_sortkey into v_parent_sk - from cr_items - where item_id = p_id; - end if; + v_parent_sk := v_parent_sk || v_max_child_sortkey; + end if; - update cr_items - set tree_sortkey = tree_next_key(v_parent_sk, v_max_value) - where item_id = v_rec.item_id; + UPDATE cr_items + SET tree_sortkey = v_parent_sk || substring(tree_sortkey, v_old_parent_length) + WHERE tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey); - end LOOP; - return new; end;' language 'plpgsql'; Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0b2-5.2.0b3.sql'. Fisheye: No comparison available. Pass `N' to diff?