Index: openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql,v diff -u -r1.13 -r1.14 --- openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql 15 Nov 2001 01:47:13 -0000 1.13 +++ openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql 16 Nov 2001 03:11:08 -0000 1.14 @@ -447,9 +447,8 @@ default 'f' constraint cr_folder_symlink_chk check (has_child_symlinks in ('t', 'f')), - package_id integer - constraint cr_fldr_pkg_id_fk - references apm_packages + package_id integer + constraint cr_fldr_pkg_id_fk references apm_packages ); comment on table cr_folders is ' Index: openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql,v diff -u -r1.23 -r1.24 --- openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql 5 Nov 2001 03:55:27 -0000 1.23 +++ openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql 16 Nov 2001 03:11:08 -0000 1.24 @@ -1411,7 +1411,7 @@ from apm_parameters p left outer join apm_parameter_values v using (parameter_id), apm_packages ap where p.package_key = ap.package_key - and v.attr_value = '''' + and v.attr_value = null and p.package_key = register_parameter__package_key loop PERFORM apm__set_value( 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; Index: openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-drop.sql 16 Nov 2001 03:11:08 -0000 1.1 @@ -0,0 +1,52 @@ +-- etp-create.sql +-- @author Luke Pond (dlpond@pobox.com) +-- @creation-date 2001-05-31 +-- +-- Ported to Oracle by Jon Griffin and Don Baccus + +drop sequence 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 + +drop package etp; + + +declare + + cursor subsite_values is + select v.value_id + from apm_parameter_values v, apm_parameters p + where p.package_key = 'acs-subsite' + and p.section_name = 'EditThisPage' + and p.parameter_id = v.parameter_id; + + cursor subsite_parameters is + select parameter_id + from apm_parameters + where package_key = 'acs-subsite' + and section_name = 'EditThisPage'; + +begin + for cur_val in subsite_values loop + apm_parameter_value.delete ( + value_id => cur_val.value_id + ); + end loop; + + for cur_val in subsite_parameters loop + apm.unregister_parameter( + parameter_id => cur_val.parameter_id + ); + end loop; +end; +/ +show errors; + +begin + content_folder.delete ( + folder_id => -400 + ); +end; +/ +show errors; Index: openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-create.sql 8 Nov 2001 14:02:45 -0000 1.4 +++ openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-create.sql 16 Nov 2001 03:11:08 -0000 1.5 @@ -367,6 +367,10 @@ -- add the ETP parameters to the acs-subsite package so that -- we can serve the site's home page and top level pages. +-- DRB: This page was setting the parameter default values explicitly. apm__register_parameter +-- is supposed to do this - there was a mistake in the Oracle->PostgreSQL port of this function. +-- I fixed the bug and removed the code that was here ... + create function inline_0 () returns integer as ' declare @@ -381,8 +385,8 @@ ''string'', ''default'', ''EditThisPage'', - ''1'', - ''1'' + 1, + 1 ); perform apm__register_parameter( NULL, @@ -392,27 +396,10 @@ ''string'', ''default'', ''EditThisPage'', - ''1'', - ''1'' + 1, + 1 ); - select package_id into ss_package_id - from apm_packages - where package_key = ''acs-subsite''; - - 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'; Index: openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-drop.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-drop.sql 7 Nov 2001 03:59:20 -0000 1.2 +++ openacs-4/packages/edit-this-page/sql/postgresql/edit-this-page-drop.sql 16 Nov 2001 03:11:08 -0000 1.3 @@ -15,11 +15,35 @@ drop function etp__get_title(integer, varchar); drop function etp__get_description(integer, varchar); --- --- need to unregister parameters here, but the interface to do so is a pain in the ASS --- +create function inline_0 () +returns integer as ' +declare + v_cur_val record; +begin + for v_cur_val in + select v.value_id + from apm_parameter_values v, apm_parameters p + where p.package_key = ''acs-subsite'' + and p.section_name = ''EditThisPage'' + and p.parameter_id = v.parameter_id + loop + perform apm_parameter_value__delete (v_cur_val.value_id); + end loop; + for v_cur_val in + select parameter_id + from apm_parameters + where package_key = ''acs-subsite'' + and section_name = ''EditThisPage'' + loop + perform apm__unregister_parameter(v_cur_val.parameter_id); + end loop; + return 0; +end;' language 'plpgsql'; +select inline_0 (); +drop function inline_0 (); + -- this will error if any deleted content exists create function inline_1 () @@ -31,6 +55,6 @@ return 0; end; ' language 'plpgsql'; - select inline_1 (); drop function inline_1 (); + Index: openacs-4/packages/edit-this-page/tcl/etp-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/edit-this-page/tcl/etp-procs-oracle.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/edit-this-page/tcl/etp-procs-oracle.xql 31 Oct 2001 05:13:54 -0000 1.2 +++ openacs-4/packages/edit-this-page/tcl/etp-procs-oracle.xql 16 Nov 2001 03:11:08 -0000 1.3 @@ -46,7 +46,7 @@ begin - etp_create_page( + etp.create_page( :package_id, :name, :title, Index: openacs-4/packages/glossary/sql/oracle/glossary-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/glossary/sql/oracle/glossary-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/glossary/sql/oracle/glossary-create.sql 20 Apr 2001 20:51:12 -0000 1.1 +++ openacs-4/packages/glossary/sql/oracle/glossary-create.sql 16 Nov 2001 03:11:08 -0000 1.2 @@ -240,6 +240,7 @@ begin acs_privilege.create_privilege('glossary_admin'); + acs_privilege.add_child('admin','glossary_admin'); acs_privilege.add_child('glossary_admin','glossary_create'); acs_privilege.add_child('glossary_admin','glossary_modify'); acs_privilege.add_child('glossary_admin','glossary_delete'); @@ -262,4 +263,4 @@ / show errors -@@ glossary-workflows.sql \ No newline at end of file +@@ glossary-workflows.sql Index: openacs-4/packages/glossary/sql/postgresql/glossary-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/glossary/sql/postgresql/glossary-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/glossary/sql/postgresql/glossary-create.sql 1 Nov 2001 00:56:57 -0000 1.2 +++ openacs-4/packages/glossary/sql/postgresql/glossary-create.sql 16 Nov 2001 03:11:08 -0000 1.3 @@ -221,6 +221,7 @@ select acs_privilege__create_privilege('glossary_admin', null, null); +select acs_privilege__add_child('admin','glossary_admin'); select acs_privilege__add_child('glossary_admin','glossary_create'); select acs_privilege__add_child('glossary_admin','glossary_modify'); select acs_privilege__add_child('glossary_admin','glossary_delete');