Index: openacs-4/packages/edit-this-page/sql/oracle/etp-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/edit-this-page/sql/oracle/Attic/etp-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/edit-this-page/sql/oracle/etp-create.sql 31 Oct 2001 04:04:33 -0000 1.1 @@ -0,0 +1,526 @@ +-- etp-create.sql +-- @author Luke Pond (dlpond@pobox.com) +-- @creation-date 2001-05-31 +-- +alter table cr_folders +add package_id integer references apm_packages; + +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 +as + function get_attribute_value ( + returns varchar as ' + p_object_id in integer + p_attribute_id in integer + v_value varchar + ) 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 + ) return integer; + + function create_extlink ( + p_package_id in integer + p_url in varchar + p_title in varchar + p_description in varchar + ) return integer; + + function create_symlink ( + p_package_id in integer + 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 + ) return integer; + + function get_folder_id ( + p_package_id in integer + ) return integer; + + function get_relative_url ( + p_item_id in integer + p_name in varchar + ) return varchar; + + function get_title ( + p_item_id in integer + p_revision_title in varchar + ) return varchar; + + function get_description ( + p_item_id in integer + 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 + 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; + + 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 + ) return integer + is + v_item_id integer; + v_revision_id integer; + v_content_type varchar; + v_folder_id integer; + begin + v_item_id := acs_object.new ( + null, + 'content_item', + sysdate(), + null, + null, + p_package_id + ); + + v_folder_id := etp.get_folder_id(p_package_id); + + insert into cr_items + (item_id, parent_id, name, content_type) + values + (v_item_id, v_folder_id, p_name, v_content_type); + + -- would like to use p_content_type here, but since there''s + -- no table that corresponds to it, we get an error from + -- the dynamic sql in acs_object__delete. so just use content_revision. + + v_content_type := 'content_revision'; + v_revision_id := acs_object.new(null, v_content_type); + + 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'); + + update cr_items + set live_revision = v_revision_id + where item_id = v_item_id; + + return 1; + end create_page; + + function create_extlink ( + p_package_id in integer + p_url in varchar + p_title in varchar + p_description in varchar + ) return integer + is + v_item_id integer; + v_folder_id integer; + begin + v_item_id := acs_object.new ( + null, + 'content_extlink' + ); + + v_folder_id := etp_get_folder_id(p_package_id); + + insert into cr_items + (item_id, parent_id, name, content_type) + values + (v_item_id, v_folder_id, + 'extlink ' || etp_auto_page_number_seq.nextval, + 'content_extlink'); + + insert into cr_extlinks + (extlink_id, url, label, description) + values + (v_item_id, p_url, p_title, p_description); + + return 1; + end create_extlink; + + function create_symlink ( + p_package_id in integer + p_target_id in integer + ) return integer + is + v_item_id integer; + v_folder_id integer; + begin + v_item_id := acs_object.new(null, 'content_symlink'); + v_folder_id := etp.get_folder_id(p_package_id); + + insert into cr_items + ( item_id, parent_id, name, content_type) + values + ( v_item_id, v_folder_id, + 'symlink ' || etp_auto_page_number_seq.nextval, + 'content_symlink'); + + insert into cr_symlinks + (symlink_id, target_id) + values + (v_item_id, p_target_id); + + return 1; + end create_symlink; + + function create_new_revision ( + p_package_id in integer + p_name alias in varchar + p_user_id in integer + ) return integer + is + v_revision_id integer; + v_new_revision_id integer; + v_content_type varchar; + 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 r.item_id = i.item_id; + + select object_type + into v_content_type + from acs_objects + where object_id = v_revision_id; + + -- cannot use acs_object__new because it creates attributes with their + -- default values, which is not what we want. + + select acs_object_id_seq.nextval + into v_new_revision_id from dual; + + insert into acs_objects + ( object_id, object_type, creation_date, creation_user) + values + (v_new_revision_id, v_content_type, now(), p_user_id); + + insert into cr_revisions + (revision_id, item_id, title, description, content, mime_type) + select v_new_revision_id, + item_id, title, + description, content, mime_type + from cr_revisions r + where r.revision_id = v_revision_id; + + -- copy extended attributes to the new revision, if there are any + insert into acs_attribute_values + (object_id, attribute_id, attr_value) + select v_new_revision_id as object_id, + attribute_id, attr_value + from acs_attribute_values + where object_id = v_revision_id; + + return 1; + end create_new_revision; + + function get_folder_id ( + p_package_id in integer + ) return integer + is + v_folder_id integer; + 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; + + return v_folder_id; + end get_folder_id; + + function get_relative_url ( + p_item_id in integer + p_name in varchar + ) return varchar + is + v_url varchar(400); + v_object_type varchar; + v_link_rec record; + begin + + select object_type into v_object_type + from acs_objects + where object_id = p_item_id; + + if v_object_type = 'content_item' then + return p_name; + end if; + + -- is this portable? wouldn't seperator be better + if v_object_type = 'content_folder' then + return p_name || '/'; + end if; + + if v_object_type = 'content_extlink' then + select url into v_url + from cr_extlinks + where extlink_id = p_item_id; + return v_url; + end if; + + if v_object_type = 'content_symlink' then + select target_id into p_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; + + select site_node.url(s.node_id) into v_url + from site_nodes s + where s.object_id = v_link_rec.package_id; + + return v_url || v_link_rec.name; + + end if; + + return null; + + end get_relative_url; + + + function get_title( + p_item_id in integer + p_revision_title in varchar + ) return varchar + is + v_title varchar(400); + v_object_type varchar; + 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 + where object_id = p_item_id; + + if v_object_type = 'content_folder' then + select r.title + into v_title + from cr_items i, cr_revisions r + where i.parent_id = p_item_id + and i.name = 'index' + and i.live_revision = r.revision_id; + return v_title; + end if; + + if v_object_type = 'content_extlink' then + select label into v_title + from cr_extlinks + where extlink_id = p_item_id; + return v_title; + end if; + + if v_object_type = 'content_symlink' then + select target_id into p_item_id + from cr_symlinks + where symlink_id = p_item_id; + return etp_get_title(p_item_id, null); + end if; + + if v_object_type = 'content_item' then + select r.title into v_title + from cr_items i, cr_revisions r + where i.item_id = p_item_id + and i.live_revision = r.revision_id; + return v_title; + end if; + + return null; + + end get_title; + + function get_description( + p_item_id in integer + p_revision_description in varchar + ) return varchar + is + v_description varchar(400); + v_object_type varchar; + 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 + where object_id = p_item_id; + + if v_object_type = 'content_folder' then + select r.description + into v_description + from cr_items i, cr_revisions r + where i.parent_id = p_item_id + and i.name = 'index' + and i.live_revision = r.revision_id + and i.item_id = r.item_id; + return v_description; + end if; + + if v_object_type = 'content_extlink' then + select description into v_description + from cr_extlinks + where extlink_id = p_item_id; + return v_description; + end if; + + if v_object_type = 'content_symlink' then + select target_id into p_item_id + from cr_symlinks + where symlink_id = p_item_id; + return etp_get_description(p_item_id, null); + end if; + + if v_object_type = 'content_item' then + select r.description into v_description + from cr_items i, cr_revisions r + where i.item_id = p_item_id + and i.live_revision = r.revision_id; + return v_description; + end if; + + return null; + + end get_description; +end etp; +/ +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 ' +declare + ss_package_id integer; + cur_val record; +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'' + ); + + 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'; + +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 ' +begin +perform content_folder__new ( + ''trash'', + ''Trash'', + ''Deleted content items get put here'', + 0, + null, + -400, + now(), + null, + null + ); +return 0; +end; +' language 'plpgsql'; + +select inline_1 (); +drop function inline_1 ();