Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql,v diff -u -N -r1.44 -r1.44.2.1 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 22 Sep 2003 11:52:14 -0000 1.44 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 20 Nov 2003 16:32:31 -0000 1.44.2.1 @@ -187,18 +187,19 @@ last_modified timestamptz default current_timestamp not null, modifying_user integer, modifying_ip varchar(50), - tree_sortkey varbit, + tree_sortkey varbit + constraint acs_objects_tree_sortkey_un unique + constraint acs_objects_tree_sortkey_nn not null, + max_child_sortkey varbit, constraint acs_objects_context_object_un unique (context_id, object_id) ); -create index acs_objects_context_object_idx on - acs_objects (context_id, object_id); +-- The unique constriant about will force create of this index... +-- create index acs_objects_context_object_idx onacs_objects (context_id, object_id); +-- The unique constraint should generate an index automatically so this is not needed +-- create index acs_objs_tree_skey_idx on acs_objects (tree_sortkey); -create index acs_objs_tree_skey_idx on acs_objects (tree_sortkey); - --- alter table acs_objects modify constraint acs_objects_context_object_un enable; - create index acs_objects_creation_user_idx on acs_objects (creation_user); create index acs_objects_modify_user_idx on acs_objects (modifying_user); @@ -230,106 +231,94 @@ -- tree query support for acs_objects -create function acs_objects_get_tree_sortkey(integer) returns varbit as ' +create or replace function acs_objects_get_tree_sortkey(integer) returns varbit as ' declare p_object_id alias for $1; begin return tree_sortkey from acs_objects where object_id = p_object_id; -end;' language 'plpgsql'; +end;' language 'plpgsql' with (isstrict, iscachable); -create function acs_objects_insert_tr () returns opaque as ' +create function acs_objects_insert_tr() returns opaque as ' declare - v_parent_sk varbit default null; - v_max_value integer; + v_parent_sk varbit default null; + v_max_child_sortkey varbit; begin - if new.context_id is null then - select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value - from acs_objects - where context_id is null; - else - select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value - from acs_objects - where context_id = new.context_id; + if new.context_id is null then + new.tree_sortkey := int_to_tree_key(new.object_id+1000); + else + SELECT tree_sortkey, tree_increment_key(max_child_sortkey) + INTO v_parent_sk, v_max_child_sortkey + FROM acs_objects + WHERE object_id = new.context_id + FOR UPDATE; - select tree_sortkey into v_parent_sk - from acs_objects - where object_id = new.context_id; + UPDATE acs_objects + SET max_child_sortkey = v_max_child_sortkey + WHERE object_id = new.context_id; + + new.tree_sortkey := v_parent_sk || v_max_child_sortkey; end if; - - new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value); - + new.max_child_sortkey := null; return new; - end;' language 'plpgsql'; create trigger acs_objects_insert_tr before insert on acs_objects for each row execute procedure acs_objects_insert_tr (); + create function acs_objects_update_tr () returns opaque as ' declare v_parent_sk varbit default null; - v_max_value integer; - ctx_id integer; - v_rec record; - clr_keys_p boolean default ''t''; + v_max_child_sortkey varbit; + v_old_parent_length integer; begin - if new.object_id = old.object_id and - ((new.context_id = old.context_id) or - (new.context_id is null and old.context_id is null)) then + if new.object_id = old.object_id + and ((new.context_id = old.context_id) + or (new.context_id is null + and old.context_id is null)) then return new; end if; - for v_rec in select object_id - from acs_objects - where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey) - order by tree_sortkey - LOOP - if clr_keys_p then - update acs_objects set tree_sortkey = null - where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey); - clr_keys_p := ''f''; - end if; - - select context_id into ctx_id - from acs_objects - where object_id = v_rec.object_id; + -- 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; - if ctx_id is null then - select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value - from acs_objects - where context_id is null; - else - select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value - from acs_objects - where context_id = ctx_id; + if new.context_id is null then + v_parent_sk := int_to_tree_key(new.object_id+1000); + else + SELECT tree_sortkey, tree_increment_key(max_child_sortkey) + INTO v_parent_sk, v_max_child_sortkey + FROM acs_objects + WHERE object_id = new.context_id + FOR UPDATE; - select tree_sortkey into v_parent_sk - from acs_objects - where object_id = ctx_id; - end if; + UPDATE acs_objects + SET max_child_sortkey = v_max_child_sortkey + WHERE object_id = new.context_id; - update acs_objects - set tree_sortkey = tree_next_key(v_parent_sk, v_max_value) - where object_id = v_rec.object_id; + v_parent_sk := v_parent_sk || v_max_child_sortkey; + end if; - end LOOP; + UPDATE acs_objects + 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); return new; - end;' language 'plpgsql'; -create trigger acs_objects_update_tr after update +create trigger acs_objects_update_tr after update on acs_objects -for each row +for each row execute procedure acs_objects_update_tr (); -- show errors comment on table acs_objects is ' +The root table for the acs object heirarchy. It all starts here folks. '; comment on column acs_objects.context_id is ' @@ -422,9 +411,10 @@ inner record; security_context_root integer; begin - if new.object_id = old.object_id and - new.context_id = old.context_id and - new.security_inherit_p = old.security_inherit_p then + if new.object_id = old.object_id + and ((new.context_id = old.context_id) + or (new.context_id is null and old.context_id is null)) + and new.security_inherit_p = old.security_inherit_p then return new; end if; @@ -592,7 +582,7 @@ -- function new -create function acs_object__new (integer,varchar,timestamptz,integer,varchar,integer,boolean) +create or replace function acs_object__new (integer,varchar,timestamptz,integer,varchar,integer,boolean) returns integer as ' declare new__object_id alias for $1; -- default null @@ -633,7 +623,7 @@ end;' language 'plpgsql'; -- function new -create function acs_object__new (integer,varchar,timestamptz,integer,varchar,integer) +create or replace function acs_object__new (integer,varchar,timestamptz,integer,varchar,integer) returns integer as ' declare new__object_id alias for $1; -- default null