Index: openacs-4/packages/acs-kernel/acs-kernel.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v
diff -u -r1.49 -r1.49.2.1
--- openacs-4/packages/acs-kernel/acs-kernel.info 10 Nov 2003 14:28:55 -0000 1.49
+++ openacs-4/packages/acs-kernel/acs-kernel.info 20 Nov 2003 16:32:31 -0000 1.49.2.1
@@ -7,13 +7,13 @@
t
t
-
+
Don Baccus
Routines and data models providing the foundation for OpenACS-based Web services.
2003-11-07
OpenACS
-
+
Index: openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql,v
diff -u -r1.11 -r1.11.2.1
--- openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 30 Sep 2003 12:10:02 -0000 1.11
+++ openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 20 Nov 2003 16:32:31 -0000 1.11.2.1
@@ -251,9 +251,10 @@
declare
security_context_root acs_objects.object_id%TYPE;
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;
end if;
@@ -263,7 +264,7 @@
security_context_root := -4;
-- Remove my old ancestors from my descendants.
- for pair in ( select object_id from acs_object_contexts where
+ for pair in ( select object_id from acs_object_contexts where
ancestor_id = :old.object_id) loop
delete from acs_object_context_index
where object_id = pair.object_id
Index: openacs-4/packages/acs-kernel/sql/oracle/groups-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/groups-create.sql,v
diff -u -r1.7 -r1.7.2.1
--- openacs-4/packages/acs-kernel/sql/oracle/groups-create.sql 30 Sep 2003 12:10:02 -0000 1.7
+++ openacs-4/packages/acs-kernel/sql/oracle/groups-create.sql 20 Nov 2003 16:32:31 -0000 1.7.2.1
@@ -257,9 +257,14 @@
create index group_elem_idx_group_idx on group_element_index (group_id);
create index group_elem_idx_element_idx on group_element_index (element_id);
create index group_elem_idx_rel_id_idx on group_element_index (rel_id);
-create index group_elem_idx_container_idx on group_element_index (container_id);
create index group_elem_idx_rel_type_idx on group_element_index (rel_type);
+-- The index on container_id is not very good
+-- and in some cases can be quite detrimental
+-- see http://openacs.org/forums/message-view?message_id=142769
+-- create index group_elem_idx_container_idx on group_element_index (container_id);
+
+
comment on table group_element_index is '
This table is for internal use by the parties system. It as an auxiliary
table, a denormalization of data, that is used to improve performance.
Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0.0b1-5.0.0b2.sql'.
Fisheye: No comparison available. Pass `N' to diff?
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 -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
Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql,v
diff -u -r1.24 -r1.24.2.1
--- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 28 Aug 2003 09:41:38 -0000 1.24
+++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 20 Nov 2003 16:32:31 -0000 1.24.2.1
@@ -3,7 +3,7 @@
--
-- @author rhs@mit.edu
-- @creation-date 2000-08-22
--- @cvs-id groups-body-create.sql,v 1.1.4.1 2001/01/12 22:58:33 mbryzek Exp
+-- @cvs-id $Id$
--
--------------
Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql,v
diff -u -r1.16 -r1.16.2.1
--- openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 28 Aug 2003 09:41:38 -0000 1.16
+++ openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 20 Nov 2003 16:32:31 -0000 1.16.2.1
@@ -293,9 +293,14 @@
create index group_elem_idx_group_idx on group_element_index (group_id);
create index group_elem_idx_element_idx on group_element_index (element_id);
create index group_elem_idx_rel_id_idx on group_element_index (rel_id);
-create index group_elem_idx_container_idx on group_element_index (container_id);
create index group_elem_idx_rel_type_idx on group_element_index (rel_type);
+-- The index on container_id is not very good
+-- and in some cases can be quite detrimental
+-- see http://openacs.org/forums/message-view?message_id=142769
+-- create index group_elem_idx_container_idx on group_element_index (container_id);
+
+
comment on table group_element_index is '
This table is for internal use by the parties system. It as an auxiliary
table, a denormalization of data, that is used to improve performance.
Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b1-5.0.0b2.sql'.
Fisheye: No comparison available. Pass `N' to diff?