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;