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 -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/edit-this-page/sql/oracle/edit-this-page-create.sql 2 Nov 2001 02:34:07 -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 (); Index: openacs-4/packages/edit-this-page/sql/oracle/edit-this-page.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/edit-this-page/sql/oracle/Attic/edit-this-page.sql,v diff -u -N --- openacs-4/packages/edit-this-page/sql/oracle/edit-this-page.sql 2 Nov 2001 02:31:36 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,526 +0,0 @@ --- 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 ();