Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql,v
diff -u -r1.41.2.1 -r1.41.2.2
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql	17 Nov 2003 21:05:29 -0000	1.41.2.1
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql	24 Nov 2003 17:25:15 -0000	1.41.2.2
@@ -13,7 +13,7 @@
 create view content_item_globals as 
 select -100 as c_root_folder_id;
 
-create function content_item__get_root_folder (integer)
+create or replace function content_item__get_root_folder (integer)
 returns integer as '
 declare
   get_root_folder__item_id                alias for $1;  -- default null 
@@ -39,7 +39,7 @@
 
   return v_folder_id;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 -- new 19 param version of content_item__new
 create or replace function content_item__new (
@@ -236,7 +236,7 @@
 end;' language 'plpgsql';
 
 --
-create function content_item__new (varchar,integer,integer,varchar,timestamptz,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar)
+create or replace function content_item__new (varchar,integer,integer,varchar,timestamptz,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar)
 returns integer as '
 declare
   new__name                   alias for $1;  
@@ -392,7 +392,7 @@
  
 end;' language 'plpgsql';
 
-create function content_item__new (varchar,integer,integer,varchar,timestamptz,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer)
+create or replace function content_item__new (varchar,integer,integer,varchar,timestamptz,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer)
 returns integer as '
 declare
   new__name                   alias for $1;  
@@ -551,7 +551,7 @@
  
 end;' language 'plpgsql';
 
-create function content_item__new(varchar,integer,varchar,text,text) 
+create or replace function content_item__new(varchar,integer,varchar,text,text) 
 returns integer as '
 declare
         new__name               alias for $1;
@@ -580,7 +580,7 @@
                                  
 end;' language 'plpgsql';
 
-create function content_item__new(varchar,integer) returns integer as '
+create or replace function content_item__new(varchar,integer) returns integer as '
 declare
         new__name       alias for $1;
         new__parent_id  alias for $2;
@@ -594,7 +594,7 @@
 
 -- function new -- sets security_inherit_p to FALSE -DaveB
 
-create function content_item__new ( integer, varchar, integer, varchar, timestamptz, integer, integer, varchar, boolean, varchar, text, varchar, boolean, varchar,varchar,varchar)
+create or replace function content_item__new ( integer, varchar, integer, varchar, timestamptz, integer, integer, varchar, boolean, varchar, text, varchar, boolean, varchar,varchar,varchar)
 
 returns integer as '
 declare
@@ -755,7 +755,7 @@
 
 end;' language 'plpgsql';
 
-create function content_item__is_published (integer)
+create or replace function content_item__is_published (integer)
 returns boolean as '
 declare
   is_published__item_id                alias for $1;  
@@ -772,29 +772,37 @@
   and
     item_id = is_published__item_id;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
-create function content_item__is_publishable (integer)
+create or replace function content_item__is_publishable (integer)
 returns boolean as '
 declare
   is_publishable__item_id                alias for $1;  
   v_child_count                          integer;       
   v_rel_count                            integer;       
+  v_content_type			 varchar;
   v_template_id                          cr_templates.template_id%TYPE;
   v_child_type                           record;
   v_rel_type                             record;
   -- v_pub_wf                               record;
 begin
+  -- check valid item_id
+  select content_item__get_content_type(is_publishable__item_id) into v_content_type;
 
+  if v_content_type is null then 
+	raise exception ''content_item__is_publishable item_id % invalid'',is_publishable__item_id;
+  end if;
+
   -- validate children
   -- make sure the # of children of each type fall between min_n and max_n
   for v_child_type in select
                         child_type, min_n, max_n
                       from
                         cr_type_children
                       where
-                        parent_type = content_item__get_content_type(is_publishable__item_id) 
+                        parent_type = v_content_type 
+	                and (min_n is not null or max_n is not null)
   LOOP
     select
       count(rel_id) into v_child_count
@@ -819,13 +827,14 @@
 
   -- validate relations
   -- make sure the # of ext links of each type fall between min_n and max_n
+  -- only check if one of min_n max_n not null
   for v_rel_type in select
                       target_type, min_n, max_n
                     from
                       cr_type_relations
                     where
-                      content_type = content_item__get_content_type(is_publishable__item_id)
-
+                      content_type = v_content_type
+		      and (max_n is not null or min_n is not null)
   LOOP
     select
       count(rel_id) into v_rel_count
@@ -879,9 +888,9 @@
 
   return ''t'';
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
--- new verson of is_valid_child that checks relation_tag
+-- new verson of is_valid_child that checks relation_tag 
 
 create or replace function content_item__is_valid_child (integer,varchar,varchar)
 returns boolean as '
@@ -942,9 +951,9 @@
 
   return v_is_valid_child;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 --
-create function content_item__is_valid_child (integer,varchar)
+create or replace function content_item__is_valid_child (integer,varchar)
 returns boolean as '
 declare
   is_valid_child__item_id                alias for $1;  
@@ -1007,7 +1016,7 @@
  6) delete keyword associations
  7) delete all associated comments */
 
-create function content_item__delete (integer)
+create or replace function content_item__delete (integer)
 returns integer as '
 declare
   delete__item_id                alias for $1;  
@@ -1132,7 +1141,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__rename (integer,varchar)
+create or replace function content_item__rename (integer,varchar)
 returns integer as '
 declare
   rename__item_id                alias for $1;  
@@ -1167,7 +1176,7 @@
   return 0; 
 end;' language 'plpgsql';
 
-create function content_item__get_id (varchar,integer,boolean)
+create or replace function content_item__get_id (varchar,integer,boolean)
 returns integer as '
 declare
   get_id__item_path              alias for $1;  
@@ -1248,7 +1257,7 @@
 
   return child_id;
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 create sequence content_item_gp_session_id;
 
@@ -1276,7 +1285,7 @@
        primary key (sid,pos)
 );
 
-create function content_item__create_rel_cursor(integer,integer) 
+create or replace function content_item__create_rel_cursor(integer,integer) 
 returns integer as '
 declare
         v_item_id       alias for $1;
@@ -1305,7 +1314,7 @@
         return null;
 end;' language 'plpgsql';
 
-create function content_item__create_abs_cursor(integer,integer) 
+create or replace function content_item__create_abs_cursor(integer,integer) 
 returns integer as '
 declare
         v_item_id       alias for $1;
@@ -1334,7 +1343,7 @@
         return null;
 end;' language 'plpgsql';
 
-create function content_item__abs_cursor_next_pos() returns integer as '
+create or replace function content_item__abs_cursor_next_pos() returns integer as '
 declare 
         v_pos   integer;
 begin
@@ -1344,7 +1353,7 @@
         return v_pos;        
 end;' language 'plpgsql';
 
-create function content_item__rel_cursor_next_pos() returns integer as '
+create or replace function content_item__rel_cursor_next_pos() returns integer as '
 declare 
         v_pos   integer;
 begin
@@ -1354,17 +1363,18 @@
         return v_pos;        
 end;' language 'plpgsql';
 
-create function content_item__cleanup_cursors(integer) returns integer as '
+-- if called with null its a noop and returns null so strict.
+create or replace function content_item__cleanup_cursors(integer) returns integer as '
 declare
         v_sid   alias for $1;
 begin
         delete from get_path_abs_cursor where sid = v_sid;
         delete from get_path_rel_cursor where sid = v_sid;
         
         return null;
-end;' language 'plpgsql';
+end;' language 'plpgsql' strict;
 
-create function content_item__get_path (integer,integer)
+create or replace function content_item__get_path (integer,integer)
 returns varchar as '
 declare
   get_path__item_id                alias for $1;  
@@ -1515,7 +1525,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__get_virtual_path (integer,integer)
+create or replace function content_item__get_virtual_path (integer,integer)
 returns varchar as '
 declare
   get_virtual_path__item_id               alias for $1;  
@@ -1525,6 +1535,7 @@
   v_is_folder                             boolean;       
   v_index                                 cr_items.item_id%TYPE;
 begin
+  -- XXX possible bug: root_folder_id arg is ignored.
 
   -- first resolve the item
   v_item_id := content_symlink__resolve(get_virtual_path__item_id);
@@ -1544,7 +1555,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__write_to_file (integer,varchar)
+create or replace function content_item__write_to_file (integer,varchar)
 returns integer as '
 declare
   item_id                alias for $1;  
@@ -1571,7 +1582,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__register_template (integer,integer,varchar)
+create or replace function content_item__register_template (integer,integer,varchar)
 returns integer as '
 declare
   register_template__item_id                alias for $1;  
@@ -1603,7 +1614,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__unregister_template (integer,integer,varchar)
+create or replace function content_item__unregister_template (integer,integer,varchar)
 returns integer as '
 declare
   unregister_template__item_id                alias for $1;  
@@ -1643,7 +1654,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__get_template (integer,varchar)
+create or replace function content_item__get_template (integer,varchar)
 returns integer as '
 declare
   get_template__item_id                alias for $1;  
@@ -1687,10 +1698,10 @@
 
   return v_template_id;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable strict;
 
 
-create function content_item__get_content_type (integer)
+create or replace function content_item__get_content_type (integer)
 returns varchar as '
 declare
   get_content_type__item_id                alias for $1;  
@@ -1704,16 +1715,12 @@
   where 
     item_id = get_content_type__item_id;  
 
-  if NOT FOUND then 
-     return null;
-  end if;
-
   return v_content_type;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable strict;
 
 
-create function content_item__get_live_revision (integer)
+create or replace function content_item__get_live_revision (integer)
 returns integer as '
 declare
   get_live_revision__item_id                alias for $1;  
@@ -1727,16 +1734,12 @@
   where
     item_id = get_live_revision__item_id;
 
-  if NOT FOUND then 
-     return null;
-  else 
-     return v_revision_id;
-  end if;
+  return v_revision_id;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable strict;
 
 
-create function content_item__set_live_revision (integer) returns integer as '
+create or replace function content_item__set_live_revision (integer) returns integer as '
 declare
   set_live_revision__revision_id    alias for $1;  
   set_live_revision__publish_status cr_items.publish_status%TYPE default ''ready'';
@@ -1765,7 +1768,7 @@
   return 0; 
 end;' language 'plpgsql';
 
-create function content_item__set_live_revision (integer,varchar)
+create or replace function content_item__set_live_revision (integer,varchar)
 returns integer as '
 declare
   set_live_revision__revision_id    alias for $1;  
@@ -1796,7 +1799,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__unset_live_revision (integer)
+create or replace function content_item__unset_live_revision (integer)
 returns integer as '
 declare
   unset_live_revision__item_id                alias for $1;  
@@ -1822,7 +1825,7 @@
   return 0; 
 end;' language 'plpgsql';
 
-create function content_item__set_release_period (integer, timestamptz, timestamptz)
+create or replace function content_item__set_release_period (integer, timestamptz, timestamptz)
 returns integer as '
 declare
   set_release_period__item_id                alias for $1;  
@@ -1854,7 +1857,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__get_revision_count (integer)
+create or replace function content_item__get_revision_count (integer)
 returns integer as '
 declare
   get_revision_count__item_id   alias for $1;  
@@ -1870,10 +1873,10 @@
 
   return v_count;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
-create function content_item__get_context (integer)
+create or replace function content_item__get_context (integer)
 returns integer as '
 declare
   get_context__item_id                alias for $1;  
@@ -1890,12 +1893,12 @@
     object_id = get_context__item_id;
 
   if NOT FOUND then 
-     raise EXCEPTION ''-20000: Content item % does not exist in content_item.get_context'', item_id;
+     raise EXCEPTION ''-20000: Content item % does not exist in content_item.get_context'', get_context__item_id;
   end if;
 
   return v_context_id;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
 -- 1) make sure we are not moving the item to an invalid location:
@@ -1904,13 +1907,17 @@
 --   to the target folder
 -- 3) update the parent_id for the item
 
-create function content_item__move (integer,integer)
+create or replace function content_item__move (integer,integer)
 returns integer as '
 declare
   move__item_id                alias for $1;  
   move__target_folder_id       alias for $2;  
 begin
 
+  if move__target_folder_id is null then 
+	raise exception ''attempt to move item_id % to null folder_id'', move__item_id;
+  end if;
+
   if content_folder__is_folder(move__item_id) = ''t'' then
 
     PERFORM content_folder__move(move__item_id, move__target_folder_id);
@@ -1936,7 +1943,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__copy (integer,integer,integer,varchar)
+create or replace function content_item__copy (integer,integer,integer,varchar)
 returns integer as '
 declare
   item_id                alias for $1;  
@@ -1960,7 +1967,7 @@
 -- 3) create a new item with no revisions in the target folder
 -- 4) copy the latest revision from the original item to the new item (if any)
 
-create function content_item__copy2 (integer,integer,integer,varchar)
+create or replace function content_item__copy2 (integer,integer,integer,varchar)
 returns integer as '
 declare
   copy2__item_id                alias for $1;  
@@ -2086,11 +2093,11 @@
 end;' language 'plpgsql';
 
 
-create function content_item__get_latest_revision (integer)
+create or replace function content_item__get_latest_revision (integer)
 returns integer as '
 declare
-  get_latest_revision__item_id                alias for $1;  
-  v_revision_id                               integer;       
+  get_latest_revision__item_id                alias for $1;
+  v_revision_id                               integer;
   v_rec                                       record;
 begin
   for v_rec in 
@@ -2105,20 +2112,16 @@
   order by 
     o.creation_date desc
   LOOP
-        v_revision_id := v_rec.revision_id;
-        exit;
+      v_revision_id := v_rec.revision_id;
+      exit;
   end LOOP;
 
-  if NOT FOUND then
-     return null;
-  end if;
-
   return v_revision_id;
  
-end;' language 'plpgsql' with (isstrict);
+end;' language 'plpgsql' strict stable;
 
 
-create function content_item__get_best_revision (integer)
+create or replace function content_item__get_best_revision (integer)
 returns integer as '
 declare
   get_best_revision__item_id                alias for $1;  
@@ -2134,16 +2137,12 @@
   where
     item_id = get_best_revision__item_id;
 
-  if NOT FOUND then 
-     return null;
-  end if;
-
   return v_revision_id;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable strict;
 
 
-create function content_item__get_title (integer,boolean)
+create or replace function content_item__get_title (integer,boolean)
 returns varchar as '
 declare
   get_title__item_id                alias for $1;  
@@ -2185,21 +2184,21 @@
 
   return v_title;
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
-create function content_item__get_title (integer)
+create or replace function content_item__get_title (integer)
 returns varchar as '
 declare
   get_title__item_id                alias for $1;  
 begin
   
   return content_item__get_title(get_title__item_id, ''f'');
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable strict;
 
 
-create function content_item__get_publish_date (integer,boolean)
+create or replace function content_item__get_publish_date (integer,boolean)
 returns timestamptz as '
 declare
   get_publish_date__item_id                alias for $1;  
@@ -2228,43 +2227,32 @@
       r.revision_id = i.latest_revision;
   end if;
 
-  if NOT FOUND then 
-     return null;
-  end if;
-
   return v_publish_date;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
-create function content_item__is_subclass (varchar,varchar)
+create or replace function content_item__is_subclass (varchar,varchar)
 returns boolean as '
 declare
   is_subclass__object_type            alias for $1;  
   is_subclass__supertype              alias for $2;  
   v_subclass_p                        boolean;      
   v_inherit_val                       record;
 begin
+  select count(*) > 0 into v_subclass_p where exists (
+	select 1
+          from acs_object_types o, acs_object_types o2
+         where o2.object_type = is_subclass__supertype
+           and o.object_type = is_subclass__object_type
+           and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey));
 
-  v_subclass_p := ''f'';
-
-  for v_inherit_val in select o.object_type
-                       from acs_object_types o, acs_object_types o2
-                       where o2.object_type = is_subclass__supertype
-                         and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey)
-                       order by o.tree_sortkey
-  LOOP
-    if v_inherit_val.object_type = is_subclass__object_type then
-         v_subclass_p := ''t'';
-    end if;
-  end loop;
-
   return v_subclass_p;
 
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
 
-create function content_item__relate (integer,integer,varchar,integer,varchar)
+create or replace function content_item__relate (integer,integer,varchar,integer,varchar)
 returns integer as '
 declare
   relate__item_id                alias for $1;  
@@ -2359,7 +2347,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__unrelate (integer)
+create or replace function content_item__unrelate (integer)
 returns integer as '
 declare
   unrelate__rel_id      alias for $1;  
@@ -2375,7 +2363,7 @@
 end;' language 'plpgsql';
 
 
-create function content_item__is_index_page (integer,integer)
+create or replace function content_item__is_index_page (integer,integer)
 returns boolean as '
 declare
   is_index_page__item_id                alias for $1;  
@@ -2387,18 +2375,18 @@
     return ''f'';
   end if;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable;
 
-
-create function content_item__get_parent_folder (integer)
+create or replace function content_item__get_parent_folder (integer)
 returns integer as '
 declare
   get_parent_folder__item_id               alias for $1;  
   v_folder_id                              cr_folders.folder_id%TYPE;
   v_parent_folder_p                        boolean default ''f'';       
 begin
+  v_folder_id := get_parent_folder__item_id;
 
-  while NOT v_parent_folder_p LOOP
+  while NOT v_parent_folder_p and v_folder_id is not null LOOP
 
     select
       parent_id, content_folder__is_folder(parent_id) 
@@ -2407,17 +2395,13 @@
     from
       cr_items
     where
-      item_id = get_parent_folder__item_id;
+      item_id = v_folder_id;
 
-    if NOT FOUND then
-       return null;
-    end if;
-
   end loop; 
 
   return v_folder_id;
  
-end;' language 'plpgsql';
+end;' language 'plpgsql' stable strict;
 
 
 
