Index: openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-create.sql	2 Nov 2001 02:34:07 -0000	1.1
+++ openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-create.sql	16 Nov 2001 03:11:08 -0000	1.2
@@ -2,106 +2,98 @@
 -- @author Luke Pond (dlpond@pobox.com)
 -- @creation-date 2001-05-31
 --
-alter table cr_folders
-add package_id integer references apm_packages;
+-- Ported to Oracle by Jon Griffin and Don Baccus
 
-create sequence t_etp_auto_page_number_seq;
---create view etp_auto_page_number_seq as
---select nextval('t_etp_auto_page_number_seq') as nextval;
---- package decs
-create or replace package body etp
+create sequence etp_auto_page_number_seq;
+
+create or replace package etp
 as 
     function get_attribute_value ( 
-    returns varchar as '
-        p_object_id    in integer
-        p_attribute_id in integer
-        v_value varchar
+      p_object_id    in acs_objects.object_id%TYPE,
+      p_attribute_id in acs_attribute_values.attribute_id%TYPE
     ) return varchar;
 
     function create_page (
-        p_package_id in integer
-        p_name       in varchar
-        p_title      in varchar
-        p_content_type in varchar default null -- -> use content_revision
+      p_package_id in apm_packages.package_id%TYPE,
+      p_name       in varchar,
+      p_title      in varchar,
+      p_content_type in varchar default 'content_revision'
     ) return integer;
 
     function create_extlink (
-      p_package_id  in integer
-      p_url         in varchar
-      p_title       in varchar
-      p_description in varchar 
+      p_package_id in apm_packages.package_id%TYPE,
+      p_url         in varchar,
+      p_title       in varchar,
+      p_description in varchar
     ) return integer;
 
     function create_symlink (
-      p_package_id in integer
+      p_package_id in apm_packages.package_id%TYPE,
       p_target_id  in integer
     ) return integer;
 
     function create_new_revision (
-      p_package_id in integer
-      p_name alias in varchar
-      p_user_id    in integer
+      p_package_id in apm_packages.package_id%TYPE,
+      p_name in varchar,
+      p_user_id    in users.user_id%TYPE
     ) return integer;
 
     function get_folder_id (
-      p_package_id in integer
+      p_package_id in apm_packages.package_id%TYPE
     ) return integer;
 
     function get_relative_url (
-      p_item_id in integer
+      p_item_id in cr_items.item_id%TYPE,
       p_name    in varchar
     ) return varchar;
 
     function get_title (
-      p_item_id        in integer
+      p_item_id in cr_items.item_id%TYPE,
       p_revision_title in varchar
     ) return varchar;
 
     function get_description (
-      p_item_id              in integer
+      p_item_id in cr_items.item_id%TYPE,
       p_revision_description in varchar
     ) return varchar;
 
 end etp;
 /
 show errors
 
+
 -- package bodies
 create or replace package body etp
 as 
     function get_attribute_value ( 
-    returns varchar as '
-        p_object_id    in integer
-        p_attribute_id in integer
-        v_value varchar
-    ) return varchar
-    is
+      p_object_id    in acs_objects.object_id%TYPE,
+      p_attribute_id in acs_attribute_values.attribute_id%TYPE
+    ) return varchar is
+      v_value acs_attribute_values.attr_value%TYPE;
     begin
         select attr_value
         into v_value
         from acs_attribute_values
         where object_id = p_object_id
         and attribute_id = p_attribute_id;
 
-        if not found then
-            v_value := '';
-        end if;
+        exception when no_data_found then v_value := '';
 
         return v_value;
     end get_attribute_value;
 
 
     function create_page (
-        p_package_id in integer
-        p_name       in varchar
-        p_title      in varchar
-        p_content_type in varchar default null -- -> use content_revision
+      p_package_id in apm_packages.package_id%TYPE,
+      p_name       in varchar,
+      p_title      in varchar,
+      p_content_type in varchar default 'content_revision'
     ) return integer 
     is
-        v_item_id integer;
-        v_revision_id integer;
-        v_content_type varchar;
-        v_folder_id integer;
+        v_item_id cr_items.item_id%TYPE;
+        v_revision_id cr_revisions.revision_id%TYPE;
+        v_content_type acs_objects.object_type%TYPE;
+        v_folder_id cr_folders.folder_id%TYPE;
     begin
        v_item_id := acs_object.new (
            null, 
@@ -128,7 +120,7 @@
 
       insert into cr_revisions (revision_id, item_id, title, 
                             publish_date, mime_type) 
-      values (v_revision_id, v_item_id, p_title, now(), 'text/html');
+      values (v_revision_id, v_item_id, p_title, sysdate, 'text/html');
 
       update cr_items 
           set live_revision = v_revision_id
@@ -138,21 +130,21 @@
     end create_page;
 
     function create_extlink (
-      p_package_id  in integer
-      p_url         in varchar
-      p_title       in varchar
-      p_description in varchar 
+      p_package_id in apm_packages.package_id%TYPE,
+      p_url         in varchar,
+      p_title       in varchar,
+      p_description in varchar
     ) return integer
     is
-      v_item_id integer;
-      v_folder_id integer;
+      v_item_id cr_items.item_id%TYPE;
+      v_folder_id cr_folders.folder_id%TYPE;
     begin
       v_item_id := acs_object.new (
           null, 
           'content_extlink'
       );
       
-      v_folder_id := etp_get_folder_id(p_package_id);
+      v_folder_id := etp.get_folder_id(p_package_id);
 
       insert into cr_items 
         (item_id, parent_id, name, content_type) 
@@ -170,12 +162,12 @@
     end create_extlink;
 
     function create_symlink (
-      p_package_id in integer
+      p_package_id in apm_packages.package_id%TYPE,
       p_target_id  in integer
     ) return integer
     is
-      v_item_id integer;
-      v_folder_id integer;
+      v_item_id cr_items.item_id%TYPE;
+      v_folder_id cr_folders.folder_id%TYPE;
     begin 
       v_item_id := acs_object.new(null, 'content_symlink');
       v_folder_id := etp.get_folder_id(p_package_id);
@@ -196,21 +188,21 @@
     end create_symlink;
 
     function create_new_revision (
-      p_package_id in integer
-      p_name alias in varchar
-      p_user_id    in integer
+      p_package_id in apm_packages.package_id%TYPE,
+      p_name in varchar,
+      p_user_id    in users.user_id%TYPE
     ) return integer
     is
-      v_revision_id integer;
-      v_new_revision_id integer;
-      v_content_type varchar;
+      v_revision_id cr_revisions.revision_id%TYPE;
+      v_new_revision_id cr_revisions.revision_id%TYPE;
+      v_content_type acs_objects.object_type%TYPE;
     begin
 
       select max(r.revision_id)
       into v_revision_id
       from cr_revisions r, cr_items i
       where i.name = p_name
-      and i.parent_id = etp_get_folder_id(p_package_id)
+      and i.parent_id = etp.get_folder_id(p_package_id)
       and r.item_id = i.item_id;
 
       select object_type
@@ -227,7 +219,7 @@
       insert into acs_objects 
         ( object_id, object_type, creation_date, creation_user)
       values 
-        (v_new_revision_id, v_content_type, now(), p_user_id);
+        (v_new_revision_id, v_content_type, sysdate, p_user_id);
 
       insert into cr_revisions 
         (revision_id, item_id, title, description, content, mime_type) 
@@ -249,30 +241,37 @@
     end create_new_revision;
 
     function get_folder_id (
-      p_package_id in integer
+      p_package_id in apm_packages.package_id%TYPE
     ) return integer
     is
-      v_folder_id integer;
+      v_folder_id cr_folders.folder_id%TYPE;
     begin
       select folder_id into v_folder_id
       from cr_folders
       where package_id = p_package_id;
     
-      if not found then 
-        v_folder_id := content_item_globals.c_root_folder_id;
-      end if;
+      exception when no_data_found then v_folder_id := content_item.c_root_folder_id;
 
       return v_folder_id;
     end get_folder_id;
 
     function get_relative_url (
-      p_item_id in integer
+      p_item_id in cr_items.item_id%TYPE,
       p_name    in varchar
     ) return varchar
     is
-      v_url varchar(400);
-      v_object_type varchar;
-      v_link_rec record;
+      v_url cr_extlinks.url%TYPE;
+      v_object_type acs_objects.object_type%TYPE;
+      v_package_id apm_packages.package_id%TYPE;
+      v_name cr_items.name%TYPE;
+      v_item_id cr_items.item_id%TYPE;
+
+      cursor v_link_rec is
+        select f.package_id, i.name
+        from cr_items i, cr_folders f
+        where i.item_id = v_item_id
+        and i.parent_id = f.folder_id;
+
     begin
 
       select object_type into v_object_type
@@ -296,21 +295,19 @@
       end if;
 
       if v_object_type = 'content_symlink' then
-        select target_id into p_item_id
+        select target_id into v_item_id
         from cr_symlinks
         where symlink_id = p_item_id;
 
-        select f.package_id, i.name
-        into v_link_rec
-        from cr_items i, cr_folders f
-        where i.item_id = p_item_id
-        and i.parent_id = f.folder_id;
+        open v_link_rec;
+        fetch v_link_rec into v_package_id, v_name;
+        close v_link_rec;
 
         select site_node.url(s.node_id) into v_url
         from site_nodes s
-        where s.object_id = v_link_rec.package_id;
+        where s.object_id = v_package_id;
 
-        return v_url || v_link_rec.name;
+        return v_url || v_name;
 
       end if;
 
@@ -320,19 +317,20 @@
 
 
     function get_title(
-      p_item_id        in integer
+      p_item_id in cr_items.item_id%TYPE,
       p_revision_title in varchar
     ) return varchar
     is
-      v_title varchar(400);
-      v_object_type varchar;
+      v_title cr_revisions.title%TYPE;
+      v_object_type acs_objects.object_type%TYPE;
+      v_item_id cr_items.item_id%TYPE;
     begin
       if p_revision_title is not null then
         return p_revision_title;
       end if;
 
-      select object_type 
-      from acs_objects into v_object_type
+      select object_type into v_object_type
+      from acs_objects
       where object_id = p_item_id;
 
       if v_object_type = 'content_folder' then
@@ -353,10 +351,10 @@
       end if;
 
       if v_object_type = 'content_symlink' then
-        select target_id into p_item_id
+        select target_id into v_item_id
         from cr_symlinks
         where symlink_id = p_item_id;
-        return etp_get_title(p_item_id, null);
+        return etp.get_title(p_item_id, null);
       end if;
 
       if v_object_type = 'content_item' then
@@ -372,19 +370,20 @@
     end get_title;
 
     function get_description(
-      p_item_id              in integer
+      p_item_id in cr_items.item_id%TYPE,
       p_revision_description in varchar
     ) return varchar
     is 
-      v_description varchar(400);
-      v_object_type varchar;
+      v_description cr_revisions.description%TYPE;
+      v_object_type acs_objects.object_type%TYPE;
+      v_item_id cr_items.item_id%TYPE;
     begin
       if p_revision_description is not null then
         return p_revision_description;
       end if;
 
-      select object_type 
-      from acs_objects into v_object_type
+      select object_type into v_object_type
+      from acs_objects
       where object_id = p_item_id;
 
       if v_object_type = 'content_folder' then
@@ -406,10 +405,10 @@
       end if;
 
       if v_object_type = 'content_symlink' then
-        select target_id into p_item_id
+        select target_id into v_item_id
         from cr_symlinks
         where symlink_id = p_item_id;
-        return etp_get_description(p_item_id, null);
+        return etp.get_description(p_item_id, null);
       end if;
 
       if v_object_type = 'content_item' then
@@ -427,100 +426,54 @@
 /
 show errors
 
--- this is a workaround for a bug in postgresql 7.1
--- that causes the cr_revision__delete function to 
--- trigger a "data change violation" as a result of
--- a row being inserted and then deleted from the
--- cr_item_publish_audit table in the same transaction.
--- see http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0001x3&topic_id=12&topic=OpenACS%204%2e0%20Design
-
--- this effectively drops all constraints (foreign key and otherwise)
--- from the audit table.
-
-create table cr_audit_temp as select * from cr_item_publish_audit;
-drop table cr_item_publish_audit;
-create table cr_item_publish_audit as select * from cr_audit_temp;
-drop table cr_audit_temp;
-
-
-
 -- add the ETP parameters to the acs-subsite package so that
 -- we can serve the site's home page and top level pages.
 
-create function inline_0 () 
-returns integer as '
+-- DRB: this was trying to set the parameter values to their default value but they
+-- already (quite logically) are set by apm.register_parameter ...
+
 declare
-  ss_package_id integer;
-  cur_val record;
+  v_parameter_id apm_parameters.parameter_id%TYPE;
+
 begin
-  perform apm__register_parameter(
-       NULL,
-       ''acs-subsite'',
-       ''application'',
-       ''Name of the ETP application to use (default, faq, wiki, or create your own with the etp::define_applicaton procedure)'',
-       ''string'',
-       ''default'',
-       ''EditThisPage'',
-       ''1'',
-       ''1''
-      );
-  perform apm__register_parameter(
-       NULL,
-       ''acs-subsite'',
-       ''subtopic_application'',
-       ''Name of the ETP application to use when creating a subtopic'',
-       ''string'',
-       ''default'',
-       ''EditThisPage'',
-       ''1'',
-       ''1''
-      );
+  v_parameter_id := apm.register_parameter(
+       package_key         => 'acs-subsite',
+       parameter_name      => 'application',
+       description         => 'Name of the ETP application to use (default, faq, wiki, or create your own with the etp::define_applicaton procedure)',
+       datatype            => 'string',
+       default_value       => 'default',
+       section_name        => 'EditThisPage',
+       min_n_values        => 1,
+       max_n_values        => 1
+  );
 
-  select package_id into ss_package_id
-    from apm_packages
-   where package_key = ''acs-subsite'';
+  v_parameter_id := apm.register_parameter(
+       package_key         => 'acs-subsite',
+       parameter_name      => 'subtopic_application',
+       description         => 'Name of the ETP application to use when creating a subtopic',
+       datatype            => 'string',
+       default_value       => 'default',
+       section_name        => 'EditThisPage',
+       min_n_values        => 1,
+       max_n_values        => 1
+  );
 
-  for cur_val in select parameter_id, default_value
-     from apm_parameters
-     where package_key = ''acs-subsite''
-     and section_name = ''EditThisPage''
-  loop
-      perform apm_parameter_value__new(
-        null,
-        ss_package_id,
-        cur_val.parameter_id,
-        cur_val.default_value
-      ); 
-  end loop;   
-
-  return 0;
 end;
-' language 'plpgsql';
+/
+show errors;
 
-select inline_0 ();
-drop function inline_0 ();
-
-
 -- create a folder with magic folder_id of -400 where we
 -- will put all deleted content items so they'll be recoverable.
 
-create function inline_1 () 
-returns integer as '
+declare
+  v_folder_id cr_folders.folder_id%TYPE;
 begin
-perform content_folder__new (
-    ''trash'',
-    ''Trash'', 
-    ''Deleted content items get put here'',
-    0,
-    null,
-    -400,
-    now(),
-    null,
-    null
+  v_folder_id := content_folder.new (
+           name           => 'trash',
+           label          => 'Trash', 
+           description    => 'Deleted content items get put here',
+           folder_id      => -400
   );
-return 0;
 end;
-' language 'plpgsql';
-
-select inline_1 ();
-drop function inline_1 ();
+/
+show errors;
