Index: openacs-4/packages/wp-slim/sql/oracle/upgrade-4.0b-4.0b1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/wp-slim/sql/oracle/Attic/upgrade-4.0b-4.0b1.sql,v diff -u -N --- openacs-4/packages/wp-slim/sql/oracle/upgrade-4.0b-4.0b1.sql 15 Nov 2001 01:47:13 -0000 1.2 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,1178 +0,0 @@ --- add a (possibly) new mime type -insert into cr_mime_types (mime_type) -select 'application/octet-stream' -from dual -where not exists (select 1 from cr_mime_types where mime_type = 'application/octet-stream'); - - --- users with wp_admin_presentaiton privilege can access --- authorization pages -begin - acs_privilege.create_privilege('wp_admin_presentation'); -end; -/ -show errors - - --- add a parent_id column in all of the auxiliary tables for clobs so that we can keep --- track of all revisions of a specific presentation or slide. -alter table cr_wp_presentations_aud add ( - presentation_id integer - constraint cr_wp_paudience_pid_nn - not null - constraint cr_wp_paudience_pid_fk - references cr_wp_presentations -); - -alter table cr_wp_presentations_back add ( - presentation_id integer - constraint cr_wp_pbackground_pid_nn - not null - constraint cr_wp_pbackground_pid_fk - references cr_wp_presentations -); - -alter table cr_wp_slides_preamble add ( - slide_id integer - constraint cr_wp_spreamble_sid_nn - not null - constraint cr_wp_spreamble_sid_fk - references cr_wp_slides -); - -alter table cr_wp_slides_postamble add ( - slide_id integer - constraint cr_wp_spostamble_sid_nn - not null - constraint cr_wp_spostamble_sid_fk - references cr_wp_slides -); - -alter table cr_wp_slides_bullet_items add ( - slide_id integer - constraint cr_wp_sbullet_sid_nn - not null - constraint cr_wp_sbullet_sid_fk - references cr_wp_slides -); - --- add the correspondent attributes -declare - attr_id acs_attributes.attribute_id%TYPE; -begin - - attr_id := content_type.create_attribute ( - content_type => 'cr_wp_presentation_aud', - attribute_name => 'presentation_id', - datatype => 'integer', - pretty_name => 'Prsentation ID', - pretty_plural => 'Presentation IDs', - column_spec => 'integer' - ); - - attr_id := content_type.create_attribute ( - content_type => 'cr_wp_presentation_back', - attribute_name => 'presentation_id', - datatype => 'integer', - pretty_name => 'Prsentation ID', - pretty_plural => 'Presentation IDs', - column_spec => 'integer' - ); - - attr_id := content_type.create_attribute ( - content_type => 'cr_wp_slide_preamble', - attribute_name => 'slide_id', - datatype => 'integer', - pretty_name => 'Slide ID', - pretty_plural => 'Slide IDs', - column_spec => 'integer' - ); - - attr_id := content_type.create_attribute ( - content_type => 'cr_wp_slide_postamble', - attribute_name => 'slide_id', - datatype => 'integer', - pretty_name => 'Slide ID', - pretty_plural => 'Slide IDs', - column_spec => 'integer' - ); - - attr_id := content_type.create_attribute ( - content_type => 'cr_wp_slide_bullet_items', - attribute_name => 'slide_id', - datatype => 'integer', - pretty_name => 'Slide ID', - pretty_plural => 'Slide IDs', - column_spec => 'integer' - ); -end; -/ -show errors - - - - --- add a public_p column -alter table cr_wp_presentations -add ( - public_p char(1) default 'f' - constraint cr_wp_public_p_ck - check(public_p in ('t','f')) -); - --- create a public_p attribute -declare - attr_id acs_attributes.attribute_id%TYPE; -begin - attr_id := content_type.create_attribute ( - content_type => 'cr_wp_presentation', - attribute_name => 'public_p', - datatype => 'boolean', - pretty_name => 'Public Flag', - pretty_plural => 'Public Flags', - column_spec => 'char(1)' - ); -end; -/ -show errors - --- public_p added. delete_audience and delete_background added. --- get_audience_revision and get_background_revision added. -create or replace package wp_presentation -as - function new ( - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - pres_title in cr_wp_presentations.pres_title%TYPE, - page_signature in cr_wp_presentations.page_signature%TYPE, - copyright_notice in cr_wp_presentations.copyright_notice%TYPE, - style in cr_wp_presentations.style%TYPE default -1, - public_p in cr_wp_presentations.public_p%TYPE default 'f', - show_modified_p in cr_wp_presentations.show_modified_p%TYPE default 'f', - audience in varchar2, - background in varchar2 - ) return cr_items.item_id%TYPE; - - procedure delete_audience ( - audience_item_id in cr_items.item_id%TYPE - ); - - procedure delete_background ( - background_item_id in cr_items.item_id%TYPE - ); - - procedure delete ( - pres_item_id in cr_items.item_id%TYPE - ); - - function get_audience ( - pres_item_id in cr_items.item_id%TYPE - ) return blob; - - function get_audience_revision ( - pres_revision_id in cr_revisions.revision_id%TYPE - ) return blob; - - function get_background ( - pres_item_id in cr_items.item_id%TYPE - ) return blob; - - function get_background_revision ( - pres_revision_id in cr_revisions.revision_id%TYPE - ) return blob; - - procedure new_revision ( - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - pres_item_id in cr_items.item_id%TYPE, - pres_title in cr_wp_presentations.pres_title%TYPE, - page_signature in cr_wp_presentations.page_signature%TYPE, - copyright_notice in cr_wp_presentations.copyright_notice%TYPE, - style in cr_wp_presentations.style%TYPE, - public_p in cr_wp_presentations.public_p%TYPE, - show_modified_p in cr_wp_presentations.show_modified_p%TYPE, - audience in varchar2, - background in varchar2 - ); - -end wp_presentation; -/ -show errors - - --- public_p added. delete_audience and delete_background added. presentation_id in clobs table added. --- get_audience_revision and get_background_revision added. -create or replace package body wp_presentation -as - function new ( - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - pres_title in cr_wp_presentations.pres_title%TYPE, - page_signature in cr_wp_presentations.page_signature%TYPE, - copyright_notice in cr_wp_presentations.copyright_notice%TYPE, - style in cr_wp_presentations.style%TYPE default -1, - public_p in cr_wp_presentations.public_p%TYPE, - show_modified_p in cr_wp_presentations.show_modified_p%TYPE default 'f', - audience in varchar2, - background in varchar2 - ) return cr_items.item_id%TYPE - is - v_item_id cr_items.item_id%TYPE; - v_audience_item_id cr_items.item_id%TYPE; - v_background_item_id cr_items.item_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - v_audience_revision_id cr_revisions.revision_id%TYPE; - v_background_revision_id cr_revisions.revision_id%TYPE; - v_max_id integer; - v_name cr_wp_presentations.pres_title%TYPE; - begin - -- (name, parent_id) must be unique. For type cr_wp_presentation, - -- name has to be unique because parent_id is null. - - select nvl(max(item_id),0) into v_max_id - from cr_items - where content_type = 'cr_wp_presentation' - and name like new.pres_title || '%'; - - v_name := new.pres_title || '_' || v_max_id; - - v_item_id := content_item.new( - name => v_name, - content_type => 'cr_wp_presentation', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - v_revision_id := content_revision.new( - item_id => v_item_id, - title => '', - data => null, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - content_item.set_live_revision(v_revision_id); - - insert into cr_wp_presentations - ( - presentation_id, - pres_title, - page_signature, - copyright_notice, - style, - public_p, - show_modified_p - ) - values - ( - v_revision_id, - new.pres_title, - new.page_signature, - new.copyright_notice, - new.style, - new.public_p, - new.show_modified_p - ); - - v_audience_item_id := content_item.new( - name => 'audience', - parent_id => v_item_id, - content_type => 'cr_wp_presentation_aud', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - v_audience_revision_id := content_revision.new( - item_id => v_audience_item_id, - title => '', - text => audience, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - content_item.set_live_revision(v_audience_revision_id); - - insert into cr_wp_presentations_aud - ( - id, - presentation_id - ) - values - ( - v_audience_revision_id, - v_revision_id - ); - - v_background_item_id := content_item.new( - name => 'background', - parent_id => v_item_id, - content_type => 'cr_wp_presentation_back', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - v_background_revision_id := content_revision.new( - item_id => v_background_item_id, - title => '', - text => background, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - content_item.set_live_revision(v_background_revision_id); - - insert into cr_wp_presentations_back - ( - id, - presentation_id - ) - values - ( - v_background_revision_id, - v_revision_id - ); - - return v_item_id; - end; - - - procedure delete_audience ( - audience_item_id in cr_items.item_id%TYPE - ) - is - begin - delete from cr_wp_presentations_aud - where exists (select 1 from cr_revisions where revision_id = cr_wp_presentations_aud.id and item_id = audience_item_id); - - delete from cr_item_publish_audit - where item_id = audience_item_id; - - content_item.delete(audience_item_id); - end; - - procedure delete_background ( - background_item_id in cr_items.item_id%TYPE - ) - is - begin - delete from cr_wp_presentations_back - where exists (select 1 from cr_revisions where revision_id = cr_wp_presentations_back.id and item_id = background_item_id); - - delete from cr_item_publish_audit - where item_id = background_item_id; - - content_item.delete(background_item_id); - end; - - procedure delete ( - pres_item_id in cr_items.item_id%TYPE - ) - is - v_audience_item_id cr_items.item_id%TYPE; - v_background_item_id cr_items.item_id%TYPE; - cursor v_slide_cursor is - select item_id as slide_item_id - from cr_items - where content_type = 'cr_wp_slide' - and parent_id = pres_item_id; - begin - for c in v_slide_cursor loop - wp_slide.delete(c.slide_item_id); - end loop; - - select item_id into v_audience_item_id - from cr_items - where content_type = 'cr_wp_presentation_aud' - and parent_id = pres_item_id; - - delete_aud(v_audience_item_id); - - select item_id into v_background_item_id - from cr_items - where content_type = 'cr_wp_presentation_back' - and parent_id = pres_item_id; - - delete_aud(v_background_item_id); - - delete from acs_permissions where object_id = pres_item_id; - update acs_objects set context_id=null where context_id = pres_item_id; - delete from cr_wp_presentations where exists (select 1 from cr_revisions where cr_revisions.revision_id = cr_wp_presentations.presentation_id and cr_revisions.item_id = pres_item_id); - content_item.delete(pres_item_id); - end; - - function get_audience ( - pres_item_id in cr_items.item_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions, cr_items - where cr_items.content_type = 'cr_wp_presentation_aud' - and cr_items.parent_id = pres_item_id - and cr_revisions.revision_id = cr_items.live_revision; - return v_blob; - end; - - function get_audience_revision ( - pres_revision_id in cr_revisions.revision_id%TYPE - ) return blob - is - v_blob blob; - begin - select r.content into v_blob - from cr_revisions r, - cr_wp_presentations_aud pa - where pa.presentation_id = pres_revision_id - and r.revision_id = pa.id; - return v_blob; - end; - - function get_background ( - pres_item_id in cr_items.item_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions, cr_items - where cr_items.content_type = 'cr_wp_presentation_back' - and cr_items.parent_id = pres_item_id - and cr_revisions.revision_id = cr_items.live_revision; - return v_blob; - end; - - function get_background_revision ( - pres_revision_id in cr_revisions.revision_id%TYPE - ) return blob - is - v_blob blob; - begin - select r.content into v_blob - from cr_revisions r, - cr_wp_presentations_back pb - where pb.presentation_id = pres_revision_id - and r.revision_id = pb.id; - return v_blob; - end; - - procedure new_revision ( - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - pres_item_id in cr_items.item_id%TYPE, - pres_title in cr_wp_presentations.pres_title%TYPE, - page_signature in cr_wp_presentations.page_signature%TYPE, - copyright_notice in cr_wp_presentations.copyright_notice%TYPE, - style in cr_wp_presentations.style%TYPE, - public_p in cr_wp_presentations.public_p%TYPE, - show_modified_p in cr_wp_presentations.show_modified_p%TYPE, - audience in varchar2, - background in varchar2 - ) - is - v_audience_item_id cr_items.item_id%TYPE; - v_background_item_id cr_items.item_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - v_audience_revision_id cr_revisions.revision_id%TYPE; - v_background_revision_id cr_revisions.revision_id%TYPE; - begin - - v_revision_id := content_revision.new( - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - item_id => pres_item_id, - title => '', - data => null - ); - - content_item.set_live_revision(v_revision_id); - - insert into cr_wp_presentations - ( - presentation_id, - pres_title, - page_signature, - copyright_notice, - style, - public_p, - show_modified_p - ) - values - ( - v_revision_id, - new_revision.pres_title, - new_revision.page_signature, - new_revision.copyright_notice, - new_revision.style, - new_revision.public_p, - new_revision.show_modified_p - ); - - select item_id into v_audience_item_id - from cr_items - where parent_id = pres_item_id - and content_type = 'cr_wp_presentation_aud'; - - v_audience_revision_id := content_revision.new( - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - item_id => v_audience_item_id, - title => '', - text => aud - ); - - content_item.set_live_revision(v_audience_revision_id); - - insert into cr_wp_presentations_aud - ( - id, - presentation_id - ) - values - ( - v_audience_revision_id, - v_revision_id - ); - - select item_id into v_background_item_id - from cr_items - where parent_id = pres_item_id - and content_type = 'cr_wp_presentation_back'; - - v_background_revision_id := content_revision.new( - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - item_id => v_background_item_id, - title => '', - text => back - ); - - content_item.set_live_revision(v_background_revision_id); - - insert into cr_wp_presentations_back - ( - id, - presentation_id - ) - values - ( - v_background_revision_id, - v_revision_id - ); - - end; - -end wp_presentation; -/ -show errors - - --- slide_id in clobs table added. delete_preamble, delete_postamble and delete_bullet_items added. --- get_preamble_revision, get_postamble_revision and get_bullet_items_revision added. -create or replace package wp_slide -as - function new ( - pres_item_id in cr_items.item_id%TYPE, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - slide_title in cr_wp_slides.slide_title%TYPE, - style in cr_wp_slides.style%TYPE default -1, - original_slide_id in cr_wp_slides.original_slide_id%TYPE, - sort_key in cr_wp_slides.sort_key%TYPE, - preamble in varchar2, - bullet_items in varchar2, - postamble in varchar2, - include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', - context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f', - context_id in acs_objects.context_id%TYPE default null - ) return cr_items.item_id%TYPE; - - - procedure delete_preamble ( - preamble_item_id in cr_items.item_id%TYPE - ); - - procedure delete_postamble ( - postamble_item_id in cr_items.item_id%TYPE - ); - - procedure delete_bullet_items ( - bullet_items_item_id in cr_items.item_id%TYPE - ); - - procedure delete ( - slide_item_id in cr_items.item_id%TYPE - ); - - function get_preamble ( - slide_item_id in cr_items.item_id%TYPE - ) return blob; - - function get_preamble_revision ( - slide_revision_id in cr_revisions.revision_id%TYPE - ) return blob; - - function get_postamble ( - slide_item_id in cr_items.item_id%TYPE - ) return blob; - - function get_postamble_revision ( - slide_revision_id in cr_revisions.revision_id%TYPE - ) return blob; - - function get_bullet_items ( - slide_item_id in cr_items.item_id%TYPE - ) return blob; - - function get_bullet_items_revision ( - slide_revision_id in cr_revisions.revision_id%TYPE - ) return blob; - - procedure new_revision ( - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - slide_item_id in cr_items.item_id%TYPE, - slide_title in cr_wp_slides.slide_title%TYPE, - preamble in varchar2, - bullet_items in varchar2, - postamble in varchar2, - style in cr_wp_slides.style%TYPE default -1, - original_slide_id in cr_wp_slides.original_slide_id%TYPE, - sort_key in cr_wp_slides.sort_key%TYPE, - include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', - context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f' - ); - -end wp_slide; -/ -show errors - -create or replace package body wp_slide -as - function new ( - pres_item_id in cr_items.item_id%TYPE, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - slide_title in cr_wp_slides.slide_title%TYPE, - style in cr_wp_slides.style%TYPE default -1, - original_slide_id in cr_wp_slides.original_slide_id%TYPE, - sort_key in cr_wp_slides.sort_key%TYPE, - preamble in varchar2, - bullet_items in varchar2, - postamble in varchar2, - include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', - context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f', - context_id in acs_objects.context_id%TYPE default null - ) return cr_items.item_id%TYPE - is - v_item_id cr_items.item_id%TYPE; - v_preamble_item_id cr_items.item_id%TYPE; - v_postamble_item_id cr_items.item_id%TYPE; - v_bullet_items_item_id cr_items.item_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - v_preamble_revision_id cr_revisions.revision_id%TYPE; - v_postamble_revision_id cr_revisions.revision_id%TYPE; - v_bullet_items_revision_id cr_revisions.revision_id%TYPE; - v_max_id integer; - v_name cr_wp_slides.slide_title%TYPE; - begin - -- (name, parent_id) must be unique. Therefore, slide item - -- name has to be unique within a presentation. - - select nvl(max(item_id),0) into v_max_id - from cr_items - where content_type = 'cr_wp_slide' - and name like new.slide_title || '%'; - - v_name := new.slide_title || '_' || v_max_id; - - v_item_id := content_item.new( - name => v_name, - parent_id => pres_item_id, - content_type => 'cr_wp_slide', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - v_revision_id := content_revision.new( - item_id => v_item_id, - title => '', - data => null, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - content_item.set_live_revision(v_revision_id); - - -- update sort_key - update cr_wp_slides - set sort_key = sort_key + 1 - where sort_key >= new.sort_key - and exists (select 1 from cr_items, cr_revisions where parent_id = pres_item_id and cr_items.item_id = cr_revisions.item_id and cr_revisions.revision_id=cr_wp_slides.slide_id); - - insert into cr_wp_slides - ( - slide_id, - slide_title, - style, - original_slide_id, - sort_key, - include_in_outline_p, - context_break_after_p - ) - values - ( - v_revision_id, - new.slide_title, - new.style, - new.original_slide_id, - new.sort_key, - new.include_in_outline_p, - new.context_break_after_p - ); - - - v_preamble_item_id := content_item.new( - name => 'preamble', - parent_id => v_item_id, - content_type => 'cr_wp_slide_preamble', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - v_preamble_revision_id := content_revision.new( - item_id => v_preamble_item_id, - title => '', - text => preamble, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - content_item.set_live_revision(v_preamble_revision_id); - - insert into cr_wp_slides_preamble - ( - id, - slide_id - ) - values - ( - v_preamble_revision_id, - v_revision_id - ); - - v_postamble_item_id := content_item.new( - name => 'postamble', - parent_id => v_item_id, - content_type => 'cr_wp_slide_postamble', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - v_postamble_revision_id := content_revision.new( - item_id => v_postamble_item_id, - title => '', - text => postamble, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - content_item.set_live_revision(v_postamble_revision_id); - - insert into cr_wp_slides_postamble - ( - id, - slide_id - ) - values - ( - v_postamble_revision_id, - v_revision_id - ); - - v_bullet_items_item_id := content_item.new( - name => 'bullet_items', - parent_id => v_item_id, - content_type => 'cr_wp_slide_bullet_items', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - v_bullet_items_revision_id := content_revision.new( - item_id => v_bullet_items_item_id, - title => '', - text => bullet_items, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - - content_item.set_live_revision(v_bullet_items_revision_id); - - insert into cr_wp_slides_bullet_items - ( - id, - slide_id - ) - values - ( - v_bullet_items_revision_id, - v_revision_id - ); - - return v_item_id; - end; - - procedure delete_preamble ( - preamble_item_id in cr_items.item_id%TYPE - ) - is - begin - delete from cr_wp_slides_preamble - where exists (select 1 from cr_revisions where revision_id = cr_wp_slides_preamble.id and item_id = preamble_item_id); - - delete from cr_item_publish_audit - where item_id = preamble_item_id; - - content_item.delete(preamble_item_id); - end; - - procedure delete_postamble ( - postamble_item_id in cr_items.item_id%TYPE - ) - is - begin - delete from cr_wp_slides_postamble - where exists (select 1 from cr_revisions where revision_id = cr_wp_slides_postamble.id and item_id = postamble_item_id); - - delete from cr_item_publish_audit - where item_id = postamble_item_id; - - content_item.delete(postamble_item_id); - end; - - procedure delete_bullet_items ( - bullet_items_item_id in cr_items.item_id%TYPE - ) - is - begin - delete from cr_wp_slides_bullet_items - where exists (select 1 from cr_revisions where revision_id = cr_wp_slides_bullet_items.id and item_id = bullet_items_item_id); - - delete from cr_item_publish_audit - where item_id = bullet_items_item_id; - - content_item.delete(bullet_items_item_id); - end; - - procedure delete ( - slide_item_id in cr_items.item_id%TYPE - ) - is - v_sort_key cr_wp_slides.sort_key%TYPE; - v_pres_item_id cr_items.item_id%TYPE; - v_preamble_item_id cr_items.item_id%TYPE; - v_postamble_item_id cr_items.item_id%TYPE; - v_bullet_items_item_id cr_items.item_id%TYPE; - cursor v_attach_cursor is - select item_id as attach_item_id - from cr_items - where content_type = 'cr_wp_attachment' - and parent_id = slide_item_id; - begin - for c in v_attach_cursor loop - wp_attachment.delete(c.attach_item_id); - end loop; - - select item_id into v_preamble_item_id - from cr_items - where content_type = 'cr_wp_slide_preamble' - and parent_id = slide_item_id; - - delete_preamble(v_preamble_item_id); - - select item_id into v_postamble_item_id - from cr_items - where content_type = 'cr_wp_slide_postamble' - and parent_id = slide_item_id; - - delete_postamble(v_postamble_item_id); - - select item_id into v_bullet_items_item_id - from cr_items - where content_type = 'cr_wp_slide_bullet_items' - and parent_id = slide_item_id; - - delete_bullet_items(v_bullet_items_item_id); - - -- sort_key of all revisions should be the same - select max(s.sort_key), max(i.parent_id) into v_sort_key, v_pres_item_id - from cr_wp_slides s, cr_revisions r, cr_items i - where r.item_id = slide_item_id - and r.revision_id = s.slide_id - and i.item_id = r.item_id; - delete from cr_wp_slides where exists (select 1 from cr_revisions where cr_revisions.revision_id = cr_wp_slides.slide_id and cr_revisions.item_id = slide_item_id); - update cr_wp_slides set sort_key = sort_key - 1 where sort_key > v_sort_key and exists (select 1 from cr_revisions r, cr_items i where i.parent_id = v_pres_item_id and i.item_id = r.item_id and r.revision_id = cr_wp_slides.slide_id); - update acs_objects set context_id=null where context_id = slide_item_id; - delete from cr_item_publish_audit where item_id = slide_item_id; - content_item.delete(slide_item_id); - end; - - function get_preamble ( - slide_item_id in cr_items.item_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions, cr_items - where cr_items.content_type = 'cr_wp_slide_preamble' - and cr_items.parent_id = slide_item_id - and cr_revisions.revision_id = cr_items.live_revision; - return v_blob; - end; - - function get_preamble_revision ( - slide_revision_id in cr_revisions.revision_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions r, cr_wp_slides_preamble sp - where sp.slide_id = slide_revision_id - and r.revision_id = sp.id; - - return v_blob; - end; - - function get_postamble ( - slide_item_id in cr_items.item_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions, cr_items - where cr_items.content_type = 'cr_wp_slide_postamble' - and cr_items.parent_id = slide_item_id - and cr_revisions.revision_id = cr_items.live_revision; - return v_blob; - end; - - function get_postamble_revision ( - slide_revision_id in cr_revisions.revision_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions r, cr_wp_slides_postamble sp - where sp.slide_id = slide_revision_id - and r.revision_id = sp.id; - - return v_blob; - end; - - function get_bullet_items ( - slide_item_id in cr_items.item_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions, cr_items - where cr_items.content_type = 'cr_wp_slide_bullet_items' - and cr_items.parent_id = slide_item_id - and cr_revisions.revision_id = cr_items.live_revision; - return v_blob; - end; - - function get_bullet_items_revision ( - slide_revision_id in cr_revisions.revision_id%TYPE - ) return blob - is - v_blob blob; - begin - select content into v_blob - from cr_revisions r, cr_wp_slides_bullet_items sb - where sb.slide_id = slide_revision_id - and r.revision_id = sb.id; - - return v_blob; - end; - - procedure new_revision ( - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - slide_item_id in cr_items.item_id%TYPE, - slide_title in cr_wp_slides.slide_title%TYPE, - preamble in varchar2, - bullet_items in varchar2, - postamble in varchar2, - style in cr_wp_slides.style%TYPE default -1, - original_slide_id in cr_wp_slides.original_slide_id%TYPE, - sort_key in cr_wp_slides.sort_key%TYPE, - include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', - context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f' - ) - is - v_preamble_item_id cr_items.item_id%TYPE; - v_postamble_item_id cr_items.item_id%TYPE; - v_bullet_items_item_id cr_items.item_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - v_preamble_revision_id cr_revisions.revision_id%TYPE; - v_postamble_revision_id cr_revisions.revision_id%TYPE; - v_bullet_items_revision_id cr_revisions.revision_id%TYPE; - begin - - v_revision_id := content_revision.new( - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - item_id => slide_item_id, - title => '', - data => null - ); - - content_item.set_live_revision(v_revision_id); - - insert into cr_wp_slides - ( - slide_id, - slide_title, - style, - original_slide_id, - sort_key, - include_in_outline_p, - context_break_after_p - ) - values - ( - v_revision_id, - new_revision.slide_title, - new_revision.style, - new_revision.original_slide_id, - new_revision.sort_key, - new_revision.include_in_outline_p, - new_revision.context_break_after_p - ); - - - select item_id into v_preamble_item_id - from cr_items - where parent_id = slide_item_id - and content_type = 'cr_wp_slide_preamble'; - - v_preamble_revision_id := content_revision.new( - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - item_id => v_preamble_item_id, - title => '', - text => preamble - ); - - content_item.set_live_revision(v_preamble_revision_id); - - insert into cr_wp_slides_preamble - ( - id, - slide_id - ) - values - ( - v_preamble_revision_id, - v_revision_id - ); - - select item_id into v_postamble_item_id - from cr_items - where parent_id = slide_item_id - and content_type = 'cr_wp_slide_postamble'; - - v_postamble_revision_id := content_revision.new( - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - item_id => v_postamble_item_id, - title => '', - text => postamble - ); - - content_item.set_live_revision(v_postamble_revision_id); - - insert into cr_wp_slides_postamble - ( - id, - slide_id - ) - values - ( - v_postamble_revision_id, - v_revision_id - ); - - select item_id into v_bullet_items_item_id - from cr_items - where parent_id = slide_item_id - and content_type = 'cr_wp_slide_bullet_items'; - - v_bullet_items_revision_id := content_revision.new( - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - item_id => v_bullet_items_item_id, - title => '', - text => bullet_items - ); - - content_item.set_live_revision(v_bullet_items_revision_id); - - insert into cr_wp_slides_bullet_items - ( - id, - slide_id - ) - values - ( - v_bullet_items_revision_id, - v_revision_id - ); - end; - -end wp_slide; -/ -show errors Index: openacs-4/packages/wp-slim/sql/oracle/upgrade-4.0b1-4.6.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/wp-slim/sql/oracle/Attic/upgrade-4.0b1-4.6.sql,v diff -u -N --- openacs-4/packages/wp-slim/sql/oracle/upgrade-4.0b1-4.6.sql 21 May 2003 15:12:15 -0000 1.1.2.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,124 +0,0 @@ --- upgrade script --- some extra permissions roc@ -declare - default_context acs_objects.object_id%TYPE; - the_public acs_objects.object_id%TYPE; -Begin - - default_context := acs.magic_object_id('default_context'); - the_public := acs.magic_object_id('the_public'); - - - acs_permission.revoke_permission ( - object_id => default_context, - grantee_id => the_public, - privilege => 'wp_view_presentation' - ); - - - acs_privilege.add_child('wp_edit_presentation', 'wp_view_presentation'); - acs_privilege.add_child('wp_admin_presentation', 'wp_create_presentation'); - acs_privilege.add_child('wp_admin_presentation', 'wp_edit_presentation'); - acs_privilege.add_child('wp_admin_presentation', 'wp_delete_presentation'); - --- lets give site-wide permissions, wp-permissions! - acs_privilege.add_child('admin', 'wp_admin_presentation'); -end; -/ -show errors - - - -alter table wp_styles add public_p char(1) default 'f' check(public_p in ('t','f')); -alter table wp_styles add owner integer constraint wp_styles_to_users references users (user_id); - -create sequence wp_style_seq; - --- this is also a new index! roc@ -create index wp_styles_by_owner on wp_styles(owner); - --- new table for supporting background images! --- Images used for styles. - -create table wp_style_images ( --- this one references to a cr! - wp_style_images_id integer primary key, - style_id integer references wp_styles(style_id) on delete cascade not null, - file_size integer not null, - file_name varchar(200) not null -); - -create index wp_style_images_style_id on wp_style_images(style_id); - -create or replace package wp_style -as - -procedure delete ( - p_style_id in wp_styles.style_id%TYPE -); - -procedure image_delete( - p_revision_id in wp_style_images.wp_style_images_id%TYPE -); - -end wp_style; -/ -show errors - - - -create or replace package body wp_style -as - -procedure delete ( - p_style_id in wp_styles.style_id%TYPE -) -is - p_item_id integer; -begin - - for one_image in ( - select * from wp_style_images - where wp_style_images_id = (select background_image from wp_styles where style_id = wp_style.delete.p_style_id)) - loop - delete from wp_style_images where wp_style_images_id = one_image.wp_style_images_id; - select item_id into p_item_id from cr_revisions where revision_id = one_image.wp_style_images_id; - - content_item.delete(item_id => p_item_id); - end loop; - - update cr_wp_slides set style = -1 where style = wp_style.delete.p_style_id; - update cr_wp_presentations set style = -1 where style = wp_style.delete.p_style_id; - delete from wp_styles where style_id = wp_style.delete.p_style_id; - -end; - - - -procedure image_delete( - p_revision_id in wp_style_images.wp_style_images_id%TYPE -) -is - p_item_id integer; -begin - - update wp_styles set background_image = 0 where background_image = wp_style.image_delete.p_revision_id; - - delete from wp_style_images - where wp_style_images_id = wp_style.image_delete.p_revision_id; - - select item_id into p_item_id from cr_revisions where revision_id = wp_style.image_delete.p_revision_id; - - content_item.delete(item_id => p_item_id); - -end; - - -end wp_style; -/ -show errors - - -alter table cr_wp_presentations add ( - show_comments_p char(1) default 'f' constraint cr_wp_pres_show_comments_p check(show_comments_p in ('t','f')) -); Index: openacs-4/packages/wp-slim/sql/oracle/upgrade/upgrade-4.0b-4.0b1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/wp-slim/sql/oracle/upgrade/upgrade-4.0b-4.0b1.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/wp-slim/sql/oracle/upgrade/upgrade-4.0b-4.0b1.sql 22 May 2003 13:37:34 -0000 1.1.2.1 @@ -0,0 +1,1178 @@ +-- add a (possibly) new mime type +insert into cr_mime_types (mime_type) +select 'application/octet-stream' +from dual +where not exists (select 1 from cr_mime_types where mime_type = 'application/octet-stream'); + + +-- users with wp_admin_presentaiton privilege can access +-- authorization pages +begin + acs_privilege.create_privilege('wp_admin_presentation'); +end; +/ +show errors + + +-- add a parent_id column in all of the auxiliary tables for clobs so that we can keep +-- track of all revisions of a specific presentation or slide. +alter table cr_wp_presentations_aud add ( + presentation_id integer + constraint cr_wp_paudience_pid_nn + not null + constraint cr_wp_paudience_pid_fk + references cr_wp_presentations +); + +alter table cr_wp_presentations_back add ( + presentation_id integer + constraint cr_wp_pbackground_pid_nn + not null + constraint cr_wp_pbackground_pid_fk + references cr_wp_presentations +); + +alter table cr_wp_slides_preamble add ( + slide_id integer + constraint cr_wp_spreamble_sid_nn + not null + constraint cr_wp_spreamble_sid_fk + references cr_wp_slides +); + +alter table cr_wp_slides_postamble add ( + slide_id integer + constraint cr_wp_spostamble_sid_nn + not null + constraint cr_wp_spostamble_sid_fk + references cr_wp_slides +); + +alter table cr_wp_slides_bullet_items add ( + slide_id integer + constraint cr_wp_sbullet_sid_nn + not null + constraint cr_wp_sbullet_sid_fk + references cr_wp_slides +); + +-- add the correspondent attributes +declare + attr_id acs_attributes.attribute_id%TYPE; +begin + + attr_id := content_type.create_attribute ( + content_type => 'cr_wp_presentation_aud', + attribute_name => 'presentation_id', + datatype => 'integer', + pretty_name => 'Prsentation ID', + pretty_plural => 'Presentation IDs', + column_spec => 'integer' + ); + + attr_id := content_type.create_attribute ( + content_type => 'cr_wp_presentation_back', + attribute_name => 'presentation_id', + datatype => 'integer', + pretty_name => 'Prsentation ID', + pretty_plural => 'Presentation IDs', + column_spec => 'integer' + ); + + attr_id := content_type.create_attribute ( + content_type => 'cr_wp_slide_preamble', + attribute_name => 'slide_id', + datatype => 'integer', + pretty_name => 'Slide ID', + pretty_plural => 'Slide IDs', + column_spec => 'integer' + ); + + attr_id := content_type.create_attribute ( + content_type => 'cr_wp_slide_postamble', + attribute_name => 'slide_id', + datatype => 'integer', + pretty_name => 'Slide ID', + pretty_plural => 'Slide IDs', + column_spec => 'integer' + ); + + attr_id := content_type.create_attribute ( + content_type => 'cr_wp_slide_bullet_items', + attribute_name => 'slide_id', + datatype => 'integer', + pretty_name => 'Slide ID', + pretty_plural => 'Slide IDs', + column_spec => 'integer' + ); +end; +/ +show errors + + + + +-- add a public_p column +alter table cr_wp_presentations +add ( + public_p char(1) default 'f' + constraint cr_wp_public_p_ck + check(public_p in ('t','f')) +); + +-- create a public_p attribute +declare + attr_id acs_attributes.attribute_id%TYPE; +begin + attr_id := content_type.create_attribute ( + content_type => 'cr_wp_presentation', + attribute_name => 'public_p', + datatype => 'boolean', + pretty_name => 'Public Flag', + pretty_plural => 'Public Flags', + column_spec => 'char(1)' + ); +end; +/ +show errors + +-- public_p added. delete_audience and delete_background added. +-- get_audience_revision and get_background_revision added. +create or replace package wp_presentation +as + function new ( + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + pres_title in cr_wp_presentations.pres_title%TYPE, + page_signature in cr_wp_presentations.page_signature%TYPE, + copyright_notice in cr_wp_presentations.copyright_notice%TYPE, + style in cr_wp_presentations.style%TYPE default -1, + public_p in cr_wp_presentations.public_p%TYPE default 'f', + show_modified_p in cr_wp_presentations.show_modified_p%TYPE default 'f', + audience in varchar2, + background in varchar2 + ) return cr_items.item_id%TYPE; + + procedure delete_audience ( + audience_item_id in cr_items.item_id%TYPE + ); + + procedure delete_background ( + background_item_id in cr_items.item_id%TYPE + ); + + procedure delete ( + pres_item_id in cr_items.item_id%TYPE + ); + + function get_audience ( + pres_item_id in cr_items.item_id%TYPE + ) return blob; + + function get_audience_revision ( + pres_revision_id in cr_revisions.revision_id%TYPE + ) return blob; + + function get_background ( + pres_item_id in cr_items.item_id%TYPE + ) return blob; + + function get_background_revision ( + pres_revision_id in cr_revisions.revision_id%TYPE + ) return blob; + + procedure new_revision ( + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + pres_item_id in cr_items.item_id%TYPE, + pres_title in cr_wp_presentations.pres_title%TYPE, + page_signature in cr_wp_presentations.page_signature%TYPE, + copyright_notice in cr_wp_presentations.copyright_notice%TYPE, + style in cr_wp_presentations.style%TYPE, + public_p in cr_wp_presentations.public_p%TYPE, + show_modified_p in cr_wp_presentations.show_modified_p%TYPE, + audience in varchar2, + background in varchar2 + ); + +end wp_presentation; +/ +show errors + + +-- public_p added. delete_audience and delete_background added. presentation_id in clobs table added. +-- get_audience_revision and get_background_revision added. +create or replace package body wp_presentation +as + function new ( + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + pres_title in cr_wp_presentations.pres_title%TYPE, + page_signature in cr_wp_presentations.page_signature%TYPE, + copyright_notice in cr_wp_presentations.copyright_notice%TYPE, + style in cr_wp_presentations.style%TYPE default -1, + public_p in cr_wp_presentations.public_p%TYPE, + show_modified_p in cr_wp_presentations.show_modified_p%TYPE default 'f', + audience in varchar2, + background in varchar2 + ) return cr_items.item_id%TYPE + is + v_item_id cr_items.item_id%TYPE; + v_audience_item_id cr_items.item_id%TYPE; + v_background_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_audience_revision_id cr_revisions.revision_id%TYPE; + v_background_revision_id cr_revisions.revision_id%TYPE; + v_max_id integer; + v_name cr_wp_presentations.pres_title%TYPE; + begin + -- (name, parent_id) must be unique. For type cr_wp_presentation, + -- name has to be unique because parent_id is null. + + select nvl(max(item_id),0) into v_max_id + from cr_items + where content_type = 'cr_wp_presentation' + and name like new.pres_title || '%'; + + v_name := new.pres_title || '_' || v_max_id; + + v_item_id := content_item.new( + name => v_name, + content_type => 'cr_wp_presentation', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + v_revision_id := content_revision.new( + item_id => v_item_id, + title => '', + data => null, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + content_item.set_live_revision(v_revision_id); + + insert into cr_wp_presentations + ( + presentation_id, + pres_title, + page_signature, + copyright_notice, + style, + public_p, + show_modified_p + ) + values + ( + v_revision_id, + new.pres_title, + new.page_signature, + new.copyright_notice, + new.style, + new.public_p, + new.show_modified_p + ); + + v_audience_item_id := content_item.new( + name => 'audience', + parent_id => v_item_id, + content_type => 'cr_wp_presentation_aud', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + v_audience_revision_id := content_revision.new( + item_id => v_audience_item_id, + title => '', + text => audience, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + content_item.set_live_revision(v_audience_revision_id); + + insert into cr_wp_presentations_aud + ( + id, + presentation_id + ) + values + ( + v_audience_revision_id, + v_revision_id + ); + + v_background_item_id := content_item.new( + name => 'background', + parent_id => v_item_id, + content_type => 'cr_wp_presentation_back', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + v_background_revision_id := content_revision.new( + item_id => v_background_item_id, + title => '', + text => background, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + content_item.set_live_revision(v_background_revision_id); + + insert into cr_wp_presentations_back + ( + id, + presentation_id + ) + values + ( + v_background_revision_id, + v_revision_id + ); + + return v_item_id; + end; + + + procedure delete_audience ( + audience_item_id in cr_items.item_id%TYPE + ) + is + begin + delete from cr_wp_presentations_aud + where exists (select 1 from cr_revisions where revision_id = cr_wp_presentations_aud.id and item_id = audience_item_id); + + delete from cr_item_publish_audit + where item_id = audience_item_id; + + content_item.delete(audience_item_id); + end; + + procedure delete_background ( + background_item_id in cr_items.item_id%TYPE + ) + is + begin + delete from cr_wp_presentations_back + where exists (select 1 from cr_revisions where revision_id = cr_wp_presentations_back.id and item_id = background_item_id); + + delete from cr_item_publish_audit + where item_id = background_item_id; + + content_item.delete(background_item_id); + end; + + procedure delete ( + pres_item_id in cr_items.item_id%TYPE + ) + is + v_audience_item_id cr_items.item_id%TYPE; + v_background_item_id cr_items.item_id%TYPE; + cursor v_slide_cursor is + select item_id as slide_item_id + from cr_items + where content_type = 'cr_wp_slide' + and parent_id = pres_item_id; + begin + for c in v_slide_cursor loop + wp_slide.delete(c.slide_item_id); + end loop; + + select item_id into v_audience_item_id + from cr_items + where content_type = 'cr_wp_presentation_aud' + and parent_id = pres_item_id; + + delete_aud(v_audience_item_id); + + select item_id into v_background_item_id + from cr_items + where content_type = 'cr_wp_presentation_back' + and parent_id = pres_item_id; + + delete_aud(v_background_item_id); + + delete from acs_permissions where object_id = pres_item_id; + update acs_objects set context_id=null where context_id = pres_item_id; + delete from cr_wp_presentations where exists (select 1 from cr_revisions where cr_revisions.revision_id = cr_wp_presentations.presentation_id and cr_revisions.item_id = pres_item_id); + content_item.delete(pres_item_id); + end; + + function get_audience ( + pres_item_id in cr_items.item_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions, cr_items + where cr_items.content_type = 'cr_wp_presentation_aud' + and cr_items.parent_id = pres_item_id + and cr_revisions.revision_id = cr_items.live_revision; + return v_blob; + end; + + function get_audience_revision ( + pres_revision_id in cr_revisions.revision_id%TYPE + ) return blob + is + v_blob blob; + begin + select r.content into v_blob + from cr_revisions r, + cr_wp_presentations_aud pa + where pa.presentation_id = pres_revision_id + and r.revision_id = pa.id; + return v_blob; + end; + + function get_background ( + pres_item_id in cr_items.item_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions, cr_items + where cr_items.content_type = 'cr_wp_presentation_back' + and cr_items.parent_id = pres_item_id + and cr_revisions.revision_id = cr_items.live_revision; + return v_blob; + end; + + function get_background_revision ( + pres_revision_id in cr_revisions.revision_id%TYPE + ) return blob + is + v_blob blob; + begin + select r.content into v_blob + from cr_revisions r, + cr_wp_presentations_back pb + where pb.presentation_id = pres_revision_id + and r.revision_id = pb.id; + return v_blob; + end; + + procedure new_revision ( + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + pres_item_id in cr_items.item_id%TYPE, + pres_title in cr_wp_presentations.pres_title%TYPE, + page_signature in cr_wp_presentations.page_signature%TYPE, + copyright_notice in cr_wp_presentations.copyright_notice%TYPE, + style in cr_wp_presentations.style%TYPE, + public_p in cr_wp_presentations.public_p%TYPE, + show_modified_p in cr_wp_presentations.show_modified_p%TYPE, + audience in varchar2, + background in varchar2 + ) + is + v_audience_item_id cr_items.item_id%TYPE; + v_background_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_audience_revision_id cr_revisions.revision_id%TYPE; + v_background_revision_id cr_revisions.revision_id%TYPE; + begin + + v_revision_id := content_revision.new( + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_id => pres_item_id, + title => '', + data => null + ); + + content_item.set_live_revision(v_revision_id); + + insert into cr_wp_presentations + ( + presentation_id, + pres_title, + page_signature, + copyright_notice, + style, + public_p, + show_modified_p + ) + values + ( + v_revision_id, + new_revision.pres_title, + new_revision.page_signature, + new_revision.copyright_notice, + new_revision.style, + new_revision.public_p, + new_revision.show_modified_p + ); + + select item_id into v_audience_item_id + from cr_items + where parent_id = pres_item_id + and content_type = 'cr_wp_presentation_aud'; + + v_audience_revision_id := content_revision.new( + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_id => v_audience_item_id, + title => '', + text => aud + ); + + content_item.set_live_revision(v_audience_revision_id); + + insert into cr_wp_presentations_aud + ( + id, + presentation_id + ) + values + ( + v_audience_revision_id, + v_revision_id + ); + + select item_id into v_background_item_id + from cr_items + where parent_id = pres_item_id + and content_type = 'cr_wp_presentation_back'; + + v_background_revision_id := content_revision.new( + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_id => v_background_item_id, + title => '', + text => back + ); + + content_item.set_live_revision(v_background_revision_id); + + insert into cr_wp_presentations_back + ( + id, + presentation_id + ) + values + ( + v_background_revision_id, + v_revision_id + ); + + end; + +end wp_presentation; +/ +show errors + + +-- slide_id in clobs table added. delete_preamble, delete_postamble and delete_bullet_items added. +-- get_preamble_revision, get_postamble_revision and get_bullet_items_revision added. +create or replace package wp_slide +as + function new ( + pres_item_id in cr_items.item_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + slide_title in cr_wp_slides.slide_title%TYPE, + style in cr_wp_slides.style%TYPE default -1, + original_slide_id in cr_wp_slides.original_slide_id%TYPE, + sort_key in cr_wp_slides.sort_key%TYPE, + preamble in varchar2, + bullet_items in varchar2, + postamble in varchar2, + include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', + context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f', + context_id in acs_objects.context_id%TYPE default null + ) return cr_items.item_id%TYPE; + + + procedure delete_preamble ( + preamble_item_id in cr_items.item_id%TYPE + ); + + procedure delete_postamble ( + postamble_item_id in cr_items.item_id%TYPE + ); + + procedure delete_bullet_items ( + bullet_items_item_id in cr_items.item_id%TYPE + ); + + procedure delete ( + slide_item_id in cr_items.item_id%TYPE + ); + + function get_preamble ( + slide_item_id in cr_items.item_id%TYPE + ) return blob; + + function get_preamble_revision ( + slide_revision_id in cr_revisions.revision_id%TYPE + ) return blob; + + function get_postamble ( + slide_item_id in cr_items.item_id%TYPE + ) return blob; + + function get_postamble_revision ( + slide_revision_id in cr_revisions.revision_id%TYPE + ) return blob; + + function get_bullet_items ( + slide_item_id in cr_items.item_id%TYPE + ) return blob; + + function get_bullet_items_revision ( + slide_revision_id in cr_revisions.revision_id%TYPE + ) return blob; + + procedure new_revision ( + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + slide_item_id in cr_items.item_id%TYPE, + slide_title in cr_wp_slides.slide_title%TYPE, + preamble in varchar2, + bullet_items in varchar2, + postamble in varchar2, + style in cr_wp_slides.style%TYPE default -1, + original_slide_id in cr_wp_slides.original_slide_id%TYPE, + sort_key in cr_wp_slides.sort_key%TYPE, + include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', + context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f' + ); + +end wp_slide; +/ +show errors + +create or replace package body wp_slide +as + function new ( + pres_item_id in cr_items.item_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + slide_title in cr_wp_slides.slide_title%TYPE, + style in cr_wp_slides.style%TYPE default -1, + original_slide_id in cr_wp_slides.original_slide_id%TYPE, + sort_key in cr_wp_slides.sort_key%TYPE, + preamble in varchar2, + bullet_items in varchar2, + postamble in varchar2, + include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', + context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f', + context_id in acs_objects.context_id%TYPE default null + ) return cr_items.item_id%TYPE + is + v_item_id cr_items.item_id%TYPE; + v_preamble_item_id cr_items.item_id%TYPE; + v_postamble_item_id cr_items.item_id%TYPE; + v_bullet_items_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_preamble_revision_id cr_revisions.revision_id%TYPE; + v_postamble_revision_id cr_revisions.revision_id%TYPE; + v_bullet_items_revision_id cr_revisions.revision_id%TYPE; + v_max_id integer; + v_name cr_wp_slides.slide_title%TYPE; + begin + -- (name, parent_id) must be unique. Therefore, slide item + -- name has to be unique within a presentation. + + select nvl(max(item_id),0) into v_max_id + from cr_items + where content_type = 'cr_wp_slide' + and name like new.slide_title || '%'; + + v_name := new.slide_title || '_' || v_max_id; + + v_item_id := content_item.new( + name => v_name, + parent_id => pres_item_id, + content_type => 'cr_wp_slide', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + v_revision_id := content_revision.new( + item_id => v_item_id, + title => '', + data => null, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + content_item.set_live_revision(v_revision_id); + + -- update sort_key + update cr_wp_slides + set sort_key = sort_key + 1 + where sort_key >= new.sort_key + and exists (select 1 from cr_items, cr_revisions where parent_id = pres_item_id and cr_items.item_id = cr_revisions.item_id and cr_revisions.revision_id=cr_wp_slides.slide_id); + + insert into cr_wp_slides + ( + slide_id, + slide_title, + style, + original_slide_id, + sort_key, + include_in_outline_p, + context_break_after_p + ) + values + ( + v_revision_id, + new.slide_title, + new.style, + new.original_slide_id, + new.sort_key, + new.include_in_outline_p, + new.context_break_after_p + ); + + + v_preamble_item_id := content_item.new( + name => 'preamble', + parent_id => v_item_id, + content_type => 'cr_wp_slide_preamble', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + v_preamble_revision_id := content_revision.new( + item_id => v_preamble_item_id, + title => '', + text => preamble, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + content_item.set_live_revision(v_preamble_revision_id); + + insert into cr_wp_slides_preamble + ( + id, + slide_id + ) + values + ( + v_preamble_revision_id, + v_revision_id + ); + + v_postamble_item_id := content_item.new( + name => 'postamble', + parent_id => v_item_id, + content_type => 'cr_wp_slide_postamble', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + v_postamble_revision_id := content_revision.new( + item_id => v_postamble_item_id, + title => '', + text => postamble, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + content_item.set_live_revision(v_postamble_revision_id); + + insert into cr_wp_slides_postamble + ( + id, + slide_id + ) + values + ( + v_postamble_revision_id, + v_revision_id + ); + + v_bullet_items_item_id := content_item.new( + name => 'bullet_items', + parent_id => v_item_id, + content_type => 'cr_wp_slide_bullet_items', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + v_bullet_items_revision_id := content_revision.new( + item_id => v_bullet_items_item_id, + title => '', + text => bullet_items, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + content_item.set_live_revision(v_bullet_items_revision_id); + + insert into cr_wp_slides_bullet_items + ( + id, + slide_id + ) + values + ( + v_bullet_items_revision_id, + v_revision_id + ); + + return v_item_id; + end; + + procedure delete_preamble ( + preamble_item_id in cr_items.item_id%TYPE + ) + is + begin + delete from cr_wp_slides_preamble + where exists (select 1 from cr_revisions where revision_id = cr_wp_slides_preamble.id and item_id = preamble_item_id); + + delete from cr_item_publish_audit + where item_id = preamble_item_id; + + content_item.delete(preamble_item_id); + end; + + procedure delete_postamble ( + postamble_item_id in cr_items.item_id%TYPE + ) + is + begin + delete from cr_wp_slides_postamble + where exists (select 1 from cr_revisions where revision_id = cr_wp_slides_postamble.id and item_id = postamble_item_id); + + delete from cr_item_publish_audit + where item_id = postamble_item_id; + + content_item.delete(postamble_item_id); + end; + + procedure delete_bullet_items ( + bullet_items_item_id in cr_items.item_id%TYPE + ) + is + begin + delete from cr_wp_slides_bullet_items + where exists (select 1 from cr_revisions where revision_id = cr_wp_slides_bullet_items.id and item_id = bullet_items_item_id); + + delete from cr_item_publish_audit + where item_id = bullet_items_item_id; + + content_item.delete(bullet_items_item_id); + end; + + procedure delete ( + slide_item_id in cr_items.item_id%TYPE + ) + is + v_sort_key cr_wp_slides.sort_key%TYPE; + v_pres_item_id cr_items.item_id%TYPE; + v_preamble_item_id cr_items.item_id%TYPE; + v_postamble_item_id cr_items.item_id%TYPE; + v_bullet_items_item_id cr_items.item_id%TYPE; + cursor v_attach_cursor is + select item_id as attach_item_id + from cr_items + where content_type = 'cr_wp_attachment' + and parent_id = slide_item_id; + begin + for c in v_attach_cursor loop + wp_attachment.delete(c.attach_item_id); + end loop; + + select item_id into v_preamble_item_id + from cr_items + where content_type = 'cr_wp_slide_preamble' + and parent_id = slide_item_id; + + delete_preamble(v_preamble_item_id); + + select item_id into v_postamble_item_id + from cr_items + where content_type = 'cr_wp_slide_postamble' + and parent_id = slide_item_id; + + delete_postamble(v_postamble_item_id); + + select item_id into v_bullet_items_item_id + from cr_items + where content_type = 'cr_wp_slide_bullet_items' + and parent_id = slide_item_id; + + delete_bullet_items(v_bullet_items_item_id); + + -- sort_key of all revisions should be the same + select max(s.sort_key), max(i.parent_id) into v_sort_key, v_pres_item_id + from cr_wp_slides s, cr_revisions r, cr_items i + where r.item_id = slide_item_id + and r.revision_id = s.slide_id + and i.item_id = r.item_id; + delete from cr_wp_slides where exists (select 1 from cr_revisions where cr_revisions.revision_id = cr_wp_slides.slide_id and cr_revisions.item_id = slide_item_id); + update cr_wp_slides set sort_key = sort_key - 1 where sort_key > v_sort_key and exists (select 1 from cr_revisions r, cr_items i where i.parent_id = v_pres_item_id and i.item_id = r.item_id and r.revision_id = cr_wp_slides.slide_id); + update acs_objects set context_id=null where context_id = slide_item_id; + delete from cr_item_publish_audit where item_id = slide_item_id; + content_item.delete(slide_item_id); + end; + + function get_preamble ( + slide_item_id in cr_items.item_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions, cr_items + where cr_items.content_type = 'cr_wp_slide_preamble' + and cr_items.parent_id = slide_item_id + and cr_revisions.revision_id = cr_items.live_revision; + return v_blob; + end; + + function get_preamble_revision ( + slide_revision_id in cr_revisions.revision_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions r, cr_wp_slides_preamble sp + where sp.slide_id = slide_revision_id + and r.revision_id = sp.id; + + return v_blob; + end; + + function get_postamble ( + slide_item_id in cr_items.item_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions, cr_items + where cr_items.content_type = 'cr_wp_slide_postamble' + and cr_items.parent_id = slide_item_id + and cr_revisions.revision_id = cr_items.live_revision; + return v_blob; + end; + + function get_postamble_revision ( + slide_revision_id in cr_revisions.revision_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions r, cr_wp_slides_postamble sp + where sp.slide_id = slide_revision_id + and r.revision_id = sp.id; + + return v_blob; + end; + + function get_bullet_items ( + slide_item_id in cr_items.item_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions, cr_items + where cr_items.content_type = 'cr_wp_slide_bullet_items' + and cr_items.parent_id = slide_item_id + and cr_revisions.revision_id = cr_items.live_revision; + return v_blob; + end; + + function get_bullet_items_revision ( + slide_revision_id in cr_revisions.revision_id%TYPE + ) return blob + is + v_blob blob; + begin + select content into v_blob + from cr_revisions r, cr_wp_slides_bullet_items sb + where sb.slide_id = slide_revision_id + and r.revision_id = sb.id; + + return v_blob; + end; + + procedure new_revision ( + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + slide_item_id in cr_items.item_id%TYPE, + slide_title in cr_wp_slides.slide_title%TYPE, + preamble in varchar2, + bullet_items in varchar2, + postamble in varchar2, + style in cr_wp_slides.style%TYPE default -1, + original_slide_id in cr_wp_slides.original_slide_id%TYPE, + sort_key in cr_wp_slides.sort_key%TYPE, + include_in_outline_p in cr_wp_slides.include_in_outline_p%TYPE default 't', + context_break_after_p in cr_wp_slides.context_break_after_p%TYPE default 'f' + ) + is + v_preamble_item_id cr_items.item_id%TYPE; + v_postamble_item_id cr_items.item_id%TYPE; + v_bullet_items_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_preamble_revision_id cr_revisions.revision_id%TYPE; + v_postamble_revision_id cr_revisions.revision_id%TYPE; + v_bullet_items_revision_id cr_revisions.revision_id%TYPE; + begin + + v_revision_id := content_revision.new( + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_id => slide_item_id, + title => '', + data => null + ); + + content_item.set_live_revision(v_revision_id); + + insert into cr_wp_slides + ( + slide_id, + slide_title, + style, + original_slide_id, + sort_key, + include_in_outline_p, + context_break_after_p + ) + values + ( + v_revision_id, + new_revision.slide_title, + new_revision.style, + new_revision.original_slide_id, + new_revision.sort_key, + new_revision.include_in_outline_p, + new_revision.context_break_after_p + ); + + + select item_id into v_preamble_item_id + from cr_items + where parent_id = slide_item_id + and content_type = 'cr_wp_slide_preamble'; + + v_preamble_revision_id := content_revision.new( + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_id => v_preamble_item_id, + title => '', + text => preamble + ); + + content_item.set_live_revision(v_preamble_revision_id); + + insert into cr_wp_slides_preamble + ( + id, + slide_id + ) + values + ( + v_preamble_revision_id, + v_revision_id + ); + + select item_id into v_postamble_item_id + from cr_items + where parent_id = slide_item_id + and content_type = 'cr_wp_slide_postamble'; + + v_postamble_revision_id := content_revision.new( + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_id => v_postamble_item_id, + title => '', + text => postamble + ); + + content_item.set_live_revision(v_postamble_revision_id); + + insert into cr_wp_slides_postamble + ( + id, + slide_id + ) + values + ( + v_postamble_revision_id, + v_revision_id + ); + + select item_id into v_bullet_items_item_id + from cr_items + where parent_id = slide_item_id + and content_type = 'cr_wp_slide_bullet_items'; + + v_bullet_items_revision_id := content_revision.new( + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_id => v_bullet_items_item_id, + title => '', + text => bullet_items + ); + + content_item.set_live_revision(v_bullet_items_revision_id); + + insert into cr_wp_slides_bullet_items + ( + id, + slide_id + ) + values + ( + v_bullet_items_revision_id, + v_revision_id + ); + end; + +end wp_slide; +/ +show errors Index: openacs-4/packages/wp-slim/sql/oracle/upgrade/upgrade-4.0b1-4.6.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/wp-slim/sql/oracle/upgrade/upgrade-4.0b1-4.6.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/wp-slim/sql/oracle/upgrade/upgrade-4.0b1-4.6.sql 22 May 2003 13:37:34 -0000 1.1.2.1 @@ -0,0 +1,124 @@ +-- upgrade script +-- some extra permissions roc@ +declare + default_context acs_objects.object_id%TYPE; + the_public acs_objects.object_id%TYPE; +Begin + + default_context := acs.magic_object_id('default_context'); + the_public := acs.magic_object_id('the_public'); + + + acs_permission.revoke_permission ( + object_id => default_context, + grantee_id => the_public, + privilege => 'wp_view_presentation' + ); + + + acs_privilege.add_child('wp_edit_presentation', 'wp_view_presentation'); + acs_privilege.add_child('wp_admin_presentation', 'wp_create_presentation'); + acs_privilege.add_child('wp_admin_presentation', 'wp_edit_presentation'); + acs_privilege.add_child('wp_admin_presentation', 'wp_delete_presentation'); + +-- lets give site-wide permissions, wp-permissions! + acs_privilege.add_child('admin', 'wp_admin_presentation'); +end; +/ +show errors + + + +alter table wp_styles add public_p char(1) default 'f' check(public_p in ('t','f')); +alter table wp_styles add owner integer constraint wp_styles_to_users references users (user_id); + +create sequence wp_style_seq; + +-- this is also a new index! roc@ +create index wp_styles_by_owner on wp_styles(owner); + +-- new table for supporting background images! +-- Images used for styles. + +create table wp_style_images ( +-- this one references to a cr! + wp_style_images_id integer primary key, + style_id integer references wp_styles(style_id) on delete cascade not null, + file_size integer not null, + file_name varchar(200) not null +); + +create index wp_style_images_style_id on wp_style_images(style_id); + +create or replace package wp_style +as + +procedure delete ( + p_style_id in wp_styles.style_id%TYPE +); + +procedure image_delete( + p_revision_id in wp_style_images.wp_style_images_id%TYPE +); + +end wp_style; +/ +show errors + + + +create or replace package body wp_style +as + +procedure delete ( + p_style_id in wp_styles.style_id%TYPE +) +is + p_item_id integer; +begin + + for one_image in ( + select * from wp_style_images + where wp_style_images_id = (select background_image from wp_styles where style_id = wp_style.delete.p_style_id)) + loop + delete from wp_style_images where wp_style_images_id = one_image.wp_style_images_id; + select item_id into p_item_id from cr_revisions where revision_id = one_image.wp_style_images_id; + + content_item.delete(item_id => p_item_id); + end loop; + + update cr_wp_slides set style = -1 where style = wp_style.delete.p_style_id; + update cr_wp_presentations set style = -1 where style = wp_style.delete.p_style_id; + delete from wp_styles where style_id = wp_style.delete.p_style_id; + +end; + + + +procedure image_delete( + p_revision_id in wp_style_images.wp_style_images_id%TYPE +) +is + p_item_id integer; +begin + + update wp_styles set background_image = 0 where background_image = wp_style.image_delete.p_revision_id; + + delete from wp_style_images + where wp_style_images_id = wp_style.image_delete.p_revision_id; + + select item_id into p_item_id from cr_revisions where revision_id = wp_style.image_delete.p_revision_id; + + content_item.delete(item_id => p_item_id); + +end; + + +end wp_style; +/ +show errors + + +alter table cr_wp_presentations add ( + show_comments_p char(1) default 'f' constraint cr_wp_pres_show_comments_p check(show_comments_p in ('t','f')) +); Index: openacs-4/packages/wp-slim/sql/postgresql/upgrade-4.0b1-4.6.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/wp-slim/sql/postgresql/Attic/upgrade-4.0b1-4.6.sql,v diff -u -N --- openacs-4/packages/wp-slim/sql/postgresql/upgrade-4.0b1-4.6.sql 21 May 2003 15:23:01 -0000 1.1.2.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,111 +0,0 @@ --- upgrade script --- extra permissions roc@ - -create function inline_1 () -returns integer as' -declare - default_context acs_objects.object_id%TYPE; - the_public acs_objects.object_id%TYPE; -begin - - default_context := acs__magic_object_id(''default_context''); - the_public := acs__magic_object_id(''the_public''); - - PERFORM acs_permission__revoke_permission ( - default_context, - the_public, - ''wp_view_presentation'' - ); - -return 0; -end;' language 'plpgsql'; -select inline_1 (); -drop function inline_1 (); - - -select acs_privilege__add_child('wp_edit_presentation', 'wp_view_presentation'); -select acs_privilege__add_child('wp_admin_presentation', 'wp_create_presentation'); -select acs_privilege__add_child('wp_admin_presentation', 'wp_edit_presentation'); -select acs_privilege__add_child('wp_admin_presentation', 'wp_delete_presentation'); - --- lets give site-wide permissions, wp-permissions! -select acs_privilege__add_child('admin', 'wp_admin_presentation'); - - - --- styles stuff -alter table wp_styles add public_p char(1) check(public_p in ('t','f')); -alter table wp_styles alter public_p set default 'f'; - -alter table wp_styles add owner integer constraint wp_styles_to_users references users (user_id); - - -create sequence wp_style_seq; - --- this is also a new index! roc@ -create index wp_styles_by_owner on wp_styles(owner); - --- new table for supporting background images! --- Images used for styles. - -create table wp_style_images ( --- this one references to a cr! - wp_style_images_id integer primary key, - style_id integer references wp_styles(style_id) on delete cascade not null, - file_size integer not null, - file_name varchar(200) not null -); - -create index wp_style_images_style_id on wp_style_images(style_id); - -create function wp_style__delete( - integer -) returns integer as' -declare - p_style_id alias for $1; - p_item_id integer; - one_image record; -begin - - for one_image in - select * from wp_style_images - where wp_style_images_id = (select background_image from wp_styles where style_id = p_style_id) - loop - delete from wp_style_images where wp_style_images_id = one_image.wp_style_images_id; - select item_id into p_item_id from cr_revisions where revision_id = one_image.wp_style_images_id; - PERFORM content_item__delete(p_item_id); - end loop; - - update cr_wp_slides set style = -1 where style = p_style_id; - update cr_wp_presentations set style = -1 where style = p_style_id; - delete from wp_styles where style_id = p_style_id; - - return 0; -end;' language 'plpgsql'; - -create function wp_style__image_delete( - integer -) returns integer as' -declare - p_revision_id alias for $1; - p_item_id integer; -begin - - update wp_styles set background_image = 0 where background_image = p_revision_id; - - delete from wp_style_images - where wp_style_images_id = p_revision_id; - - select item_id into p_item_id from cr_revisions where revision_id = p_revision_id; - - PERFORM content_item__delete(p_item_id); - - return 0; -end;' language 'plpgsql'; - - - --- added for showing comments to general users - -alter table cr_wp_presentations add show_comments_p char(1) check(show_comments_p in ('t','f')); -alter table cr_wp_presentations alter show_comments_p set default 'f';