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.45
--- 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	11 Dec 2003 21:39:52 -0000	1.45
@@ -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' stable strict;
 
-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;
 
@@ -549,13 +539,15 @@
 -- ACS_OBJECT PACKAGE --
 ------------------------
 
-create function acs_object__initialize_attributes (integer)
+create or replace function acs_object__initialize_attributes (integer)
 returns integer as '
 declare
   initialize_attributes__object_id              alias for $1;  
   v_object_type                                 acs_objects.object_type%TYPE;
 begin
-   -- XXX This should be fixed to initialize supertypes properly.
+   if  initialize_attributes__object_id is null then 
+	raise exception ''acs_object__initialize_attributes called with null object_id'';
+   end if;
 
    -- Initialize dynamic attributes
    insert into acs_attribute_values
@@ -592,7 +584,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 +625,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
@@ -784,11 +776,11 @@
 
   return object_name;
   
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable strict;
 
 
 -- function default_name
-create function acs_object__default_name (integer)
+create or replace function acs_object__default_name (integer)
 returns varchar as '
 declare
   default_name__object_id   alias for $1;  
@@ -801,12 +793,12 @@
   and o.object_type = ot.object_type;
 
   return object_type_pretty_name || '' '' || default_name__object_id;
-  
-end;' language 'plpgsql';
 
+end;' language 'plpgsql' stable strict;
 
+
 -- procedure get_attribute_storage
-create function acs_object__get_attribute_storage (integer,varchar)
+create or replace function acs_object__get_attribute_storage (integer,varchar)
 returns text as '
 declare
   object_id_in           alias for $1;  
@@ -859,7 +851,7 @@
      a.object_type = t.object_type;
 
    if NOT FOUND then 
-      raise EXCEPTION ''-20000: No such attribute %::% in acs_object.get_attribute_storage.'', v_object_type, attribute_name_in;
+      raise EXCEPTION ''-20000: No such attribute % for object % in acs_object.get_attribute_storage.'', attribute_name_in, object_id_in;
    end if;
 
    -- This should really be done in a trigger on acs_attributes,
@@ -898,10 +890,10 @@
          where 
            object_type = v_object_type;
          if NOT FOUND then 
-            raise EXCEPTION ''-20000: No data found for attribute %::% in acs_object.get_attribute_storage'', v_object_type, attribute_name_in;
+            raise EXCEPTION ''-20000: No data found for attribute %::% object_id % in acs_object.get_attribute_storage'', v_object_type, attribute_name_in, object_id_in;
          end if;
        else
-         raise EXCEPTION ''-20000: No table name specified for storage specific static attribute %::% in acs_object.get_attribute_storage.'',v_object_type, attribute_name_in;
+         raise EXCEPTION ''-20000: No table name specified for storage specific static attribute %::% object_id % in acs_object.get_attribute_storage.'',v_object_type, attribute_name_in, object_id_in;
        end if;
   
      end if;
@@ -913,7 +905,7 @@
        where object_type = v_object_type 
        and table_name = v_table_name;
        if NOT FOUND then 
-          raise EXCEPTION ''-20000: No data found for attribute %::% in acs_object.get_attribute_storage'', v_object_type, attribute_name_in;
+          raise EXCEPTION ''-20000: No data found for attribute %::% object_id % in acs_object.get_attribute_storage'', v_object_type, attribute_name_in, object_id_in;
        end if;
    end if;
 
@@ -937,68 +929,72 @@
 
    return v_column || '','' || v_table_name || '','' || v_key_sql; 
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
-create function acs_object__get_attr_storage_column(text) 
+create or replace function acs_object__get_attr_storage_column(text) 
 returns text as '
 declare
         v_vals  alias for $1;
         v_idx   integer;
 begin
         v_idx := strpos(v_vals,'','');
-        if v_idx = 0 then 
-           raise exception ''invalid storage format: acs_object.get_attr_storage_column'';
+        if v_idx = 0 or v_vals is null then 
+           raise exception ''invalid storage format: acs_object.get_attr_storage_column %'',v_vals;
         end if;
 
         return substr(v_vals,1,v_idx - 1);
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' immutable;
 
-create function acs_object__get_attr_storage_table(text) 
+create or replace function acs_object__get_attr_storage_table(text) 
 returns text as '
 declare
         v_vals  alias for $1;
         v_idx   integer;
         v_tmp   varchar;
 begin
         v_idx := strpos(v_vals,'','');
-        if v_idx = 0 then 
-           raise exception ''invalid storage format: acs_object.get_attr_storage_table'';
+
+        if v_idx = 0 or v_vals is null then 
+           raise exception ''invalid storage format: acs_object.get_attr_storage_table %'',v_vals;
         end if;
+
         v_tmp := substr(v_vals,v_idx + 1);
         v_idx := strpos(v_tmp,'','');
         if v_idx = 0 then 
-           raise exception ''invalid storage format: acs_object.get_attr_storage_table'';
+           raise exception ''invalid storage format: acs_object.get_attr_storage_table %'',v_vals;
         end if;
 
         return substr(v_tmp,1,v_idx - 1);
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' immutable;
 
-create function acs_object__get_attr_storage_sql(text) 
+create or replace function acs_object__get_attr_storage_sql(text) 
 returns text as '
 declare
         v_vals  alias for $1;
         v_idx   integer;
         v_tmp   varchar;
 begin
         v_idx := strpos(v_vals, '','');
-        if v_idx = 0 then 
-           raise exception ''invalid storage format: acs_object.get_attr_storage_sql'';
+
+        if v_idx = 0 or v_vals is null then 
+           raise exception ''invalid storage format: acs_object.get_attr_storage_sql %'',v_vals;
         end if;
+
         v_tmp := substr(v_vals, v_idx + 1);
         v_idx := strpos(v_tmp, '','');
         if v_idx = 0 then 
-           raise exception ''invalid storage format: acs_object.get_attr_storage_sql'';
+           raise exception ''invalid storage format: acs_object.get_attr_storage_sql %'',v_vals;
         end if;
 
         return substr(v_tmp, v_idx + 1);
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' immutable;
 
 -- function get_attribute
-create function acs_object__get_attribute (integer,varchar)
+create or replace function acs_object__get_attribute (integer,varchar)
 returns text as '
 declare
   object_id_in           alias for $1;  
@@ -1028,11 +1024,11 @@
 
    return v_return;
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
 -- procedure set_attribute
-create function acs_object__set_attribute (integer,varchar,varchar)
+create or replace function acs_object__set_attribute (integer,varchar,varchar)
 returns integer as '
 declare
   object_id_in           alias for $1;  
@@ -1044,6 +1040,10 @@
   v_return               text; 
   v_storage              text;
 begin
+   if value_in is null then 
+	-- this will fail more cryptically in the execute so catch now. 
+	raise exception ''acs_object__set_attribute: attempt to set % to null for object_id %'',attribute_name_in, object_id_in;
+   end if;
 
    v_storage := acs_object__get_attribute_storage(object_id_in, attribute_name_in);
 
@@ -1058,7 +1058,7 @@
 
 
 -- function check_context_index
-create function acs_object__check_context_index (integer,integer,integer)
+create or replace function acs_object__check_context_index (integer,integer,integer)
 returns boolean as '
 declare
   check_context_index__object_id              alias for $1;  
@@ -1068,6 +1068,9 @@
   n_gens                                      integer;       
 begin
    -- Verify that this row exists in the index.
+   if check_context_index__object_id is null or check_context_index__ancestor_id is null then
+	raise exception ''object_id or ancestor_id is null in acs_object__check_context_index'';
+   end if;	
    select case when count(*) = 0 then 0 else 1 end into n_rows
    from acs_object_context_index
    where object_id = check_context_index__object_id
@@ -1218,14 +1221,18 @@
 
 
 -- function check_path
-create function acs_object__check_path (integer,integer)
+create or replace function acs_object__check_path (integer,integer)
 returns boolean as '
 declare
   check_path__object_id              alias for $1;  
   check_path__ancestor_id            alias for $2;  
   check_path__context_id             acs_objects.context_id%TYPE;
   check_path__security_inherit_p     acs_objects.security_inherit_p%TYPE;
 begin
+   if check_path__object_id is null or check_path__ancestor_id then
+	raise exception ''acs_object__check_path called with null object_id or ancestor_id'';
+   end if;
+
    if check_path__object_id = check_path__ancestor_id then
      return ''t'';
    end if;
@@ -1250,11 +1257,11 @@
    return acs_object__check_path(check_path__context_id, 
                                  check_path__ancestor_id);
   
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
 -- function check_representation
-create function acs_object__check_representation (integer)
+create or replace function acs_object__check_representation (integer)
 returns boolean as '
 declare
   check_representation__object_id              alias for $1;  
@@ -1264,6 +1271,10 @@
   v_rec                                        record;  
   row                                          record; 
 begin
+   if check_representation__object_id is null then 
+	raise exception ''acs_object__check_representation called for null object_id'';
+   end if;
+
    result := ''t'';
    PERFORM acs_log__notice(''acs_object.check_representation'',
                   ''Running acs_object.check_representation on object_id = '' 
