Index: openacs-4/packages/wp-slim/sql/postgresql/wp-slim-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/wp-slim/sql/postgresql/wp-slim-create.sql,v diff -u -r1.8 -r1.9 --- openacs-4/packages/wp-slim/sql/postgresql/wp-slim-create.sql 28 Aug 2003 09:42:00 -0000 1.8 +++ openacs-4/packages/wp-slim/sql/postgresql/wp-slim-create.sql 22 Sep 2003 18:43:24 -0000 1.9 @@ -741,6 +741,1166 @@ select inline_11 (); drop function inline_11 (); +--jackp: From here on the functions are defined + +--jackp: To p_create each presentation +create function wp_presentation__new ( + timestamp, + integer, + varchar(400), + varchar(400), + varchar(400), + varchar, + integer, + boolean, + boolean, + varchar, + varchar, + integer +) +returns integer as' +declare + p_creation_date alias for $1; + p_creation_user alias for $2; + p_creation_ip alias for $3; + p_pres_title alias for $4; + p_page_signature alias for $5; + p_copyright_notice alias for $6; + p_style alias for $7; + p_public_p alias for $8; + p_show_modified_p alias for $9; + aud alias for $10; + back alias for $11; + p_parent_id alias for $12; + 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 + select coalesce(max(item_id),0) into v_max_id + from cr_items + where content_type = ''cr_wp_presentation'' + and name like p_pres_title || ''%''; + + v_name := p_pres_title || ''_'' || v_max_id; + + v_item_id := content_item__new( + v_name, + p_parent_id, + null, + null, + p_creation_date, + p_creation_user, + null, + p_creation_ip, + ''content_item'', + ''cr_wp_presentation'', + null, + null, + ''text/plain'', + null, + null, + ''text'' + ); + + v_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + null, + v_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM content_item__set_live_revision(v_revision_id); + +--jackp: Actually place the information entered by the user into the table + insert into cr_wp_presentations + ( + presentation_id, + pres_title, + page_signature, + copyright_notice, + style, + public_p, + show_modified_p + ) values ( + v_revision_id, + p_pres_title, + p_page_signature, + p_copyright_notice, + p_style, + p_public_p, + p_show_modified_p + ); + + v_audience_item_id := content_item__new( + aud, + v_item_id, + null, + null, + p_creation_date, + p_creation_user, + null, + p_creation_ip, + ''content_item'', + ''cr_wp_presentation_aud'', + null, + null, + ''text/plain'', + null, + null, + ''text'' + ); + + v_audience_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + aud, + v_audience_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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( + back, + v_item_id, + null, + null, + p_creation_date, + p_creation_user, + null, + p_creation_ip, + ''content_item'', + ''cr_wp_presentation_back'', + null, + null, + ''text/plain'', + null, + null, + ''text'' + ); + + v_background_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + back, + v_background_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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;' language 'plpgsql'; + +create function wp_presentation__delete_audience ( + integer +) +returns integer as' +declare + audience_item_id alias for $1; +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; + + PERFORM content_item__delete(audience_item_id); +return 0; +end;' language 'plpgsql'; + +create function wp_presentation__delete_background ( + integer +) +returns integer as' +declare + background_item_id alias for $1; +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; + + PERFORM content_item__delete(background_item_id); + return 0; +end;' language 'plpgsql'; + +create function wp_presentation__delete ( + integer +) +returns integer as' +declare + pres_item_id alias for $1; + v_audience_item_id cr_items.item_id%TYPE; + v_background_item_id cr_items.item_id%TYPE; + del_rec record; +begin + for del_rec in select item_id as slide_item_id + from cr_items + where content_type = ''cr_wp_slide'' + and parent_id = pres_item_id + loop + PERFORM wp_slide__delete(del_rec.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; + + PERFORM wp_presentation__delete_audience(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; + + PERFORM wp_presentation__delete_background(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); + PERFORM content_item__delete(pres_item_id); +return 0; +end;' language 'plpgsql'; + +-- DRB: All these could've been implemented as a single function with a type argument +-- but I'm not going to rewrite all of wp-slim's queries just to clean this up... + +create function wp_presentation__get_ad_revision (integer) returns text as ' +declare + p_pres_revision_id alias for $1; +begin + return r.content + from cr_revisions r, + cr_wp_presentations_aud pa + where pa.presentation_id = p_pres_revision_id + and r.revision_id = pa.id; +end;' language 'plpgsql'; + +create function wp_presentation__get_audience ( + integer +) returns text as' +declare + p_pres_item_id alias for $1; +begin + return content + from cr_revisions, cr_items + where cr_items.content_type = ''cr_wp_presentation_aud'' + and cr_items.parent_id = p_pres_item_id + and cr_revisions.revision_id = cr_items__live_revision; +end;' language 'plpgsql'; + +create function wp_presentation__get_bg_revision (integer) returns text as ' +declare + p_pres_revision_id alias for $1; +begin + return r.content + from cr_revisions r, + cr_wp_presentations_aud pa + where pa.presentation_id = p_pres_revision_id + and r.revision_id = pa.id; +end;' language 'plpgsql'; + +create function wp_presentation__get_background ( + integer +) returns text as' +declare + pres_item_id alias for $1; +begin + return content + from cr_revisions, cr_items + where cr_items.content_type = ''cr_wp_presentation_bak'' + and cr_items.parent_id = p_pres_item_id + and cr_revisions.revision_id = cr_items__live_revision; +end;' language 'plpgsql'; + + +create function wp_presentation__new_revision ( + timestamp, + integer, + varchar, + integer, + varchar(400), + varchar(200), + varchar(400), + integer, + boolean, + boolean, + varchar, + varchar +) returns integer as' +declare + p_creation_date alias for $1; + p_creation_user alias for $2; + p_creation_ip alias for $3; + p_pres_item_id alias for $4; + p_pres_title alias for $5; + p_page_signature alias for $6; + p_copyright_notice alias for $7; + p_style alias for $8; + p_public_p alias for $9; + p_show_modified_p alias for $10; + p_audience alias for $11; + p_background alias for $12; + 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( + null, + null, + current_timestamp, + ''text/plain'', + null, + null, + p_pres_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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, + p_pres_title, + p_page_signature, + p_copyright_notice, + p_style, + p_public_p, + p_show_modified_p + ); + + select item_id into v_audience_item_id + from cr_items + where parent_id = p_pres_item_id + and content_type = ''cr_wp_presentation_aud''; + + v_audience_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_audience, + v_audience_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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 = p_pres_item_id + and content_type = ''cr_wp_presentation_back''; + + v_background_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_background, + v_background_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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 0; +end;' language 'plpgsql'; + +create function wp_slide__new ( + integer, + timestamp, + integer, + varchar, + varchar, + integer, + integer, + integer, + varchar, + varchar, + varchar, + boolean, + boolean, + integer +) returns integer as' +declare + p_pres_item_id alias for $1; + p_creation_date alias for $2; + p_creation_user alias for $3; + p_creation_ip alias for $4; + p_slide_title alias for $5; + p_style alias for $6; + p_original_slide_id alias for $7; + p_sort_key alias for $8; + p_preamble alias for $9; + p_bullet_items alias for $10; + p_postamble alias for $11; + p_include_in_outline_p alias for $12; + p_context_break_after_p alias for $13; + p_context_id alias for $14; + 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 varchar; +begin + select coalesce(max(item_id),0) into v_max_id + from cr_items + where content_type = ''cr_wp_slide'' + and name like p_slide_title || ''%''; + + v_name := p_slide_title || ''_'' || v_max_id; + + v_item_id := content_item__new( + v_name, + p_pres_item_id, + null, + null, + p_creation_date, + p_creation_user, + null, + p_creation_ip, + ''content_item'', + ''cr_wp_slide'', + null, + null, + ''text/plain'', + null, + null, + ''text'' + ); + + v_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + null, + v_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM content_item__set_live_revision(v_revision_id); + + update cr_wp_slides + set sort_key = sort_key + 1 + where sort_key >= p_sort_key + and exists (select 1 from cr_items, cr_revisions where parent_id = + p_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, + original_slide_id, + sort_key, + slide_title, + include_in_outline_p, + context_break_after_p, + style + ) values ( + v_revision_id, + p_original_slide_id, + p_sort_key, + p_slide_title, + p_include_in_outline_p, + p_context_break_after_p, + p_style + ); + + v_preamble_item_id := content_item__new( + ''preamble'', + v_item_id, + null, + null, + p_creation_date, + p_creation_user, + null, + p_creation_ip, + ''content_item'', + ''cr_wp_slide_preamble'', + null, + null, + ''text/plain'', + null, + null, + ''text'' + ); + + v_preamble_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_preamble, + v_preamble_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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( + ''postamble'', + v_item_id, + null, + null, + p_creation_date, + p_creation_user, + null, + p_creation_ip, + ''content_item'', + ''cr_wp_slide_postamble'', + null, + null, + ''text/plain'', + null, + null, + ''text'' + ); + + + v_postamble_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_postamble, + v_postamble_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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( + ''bullet_items'', + v_item_id, + null, + null, + p_creation_date, + p_creation_user, + null, + p_creation_ip, + ''content_item'', + ''cr_wp_slide_bullet_items'', + null, + null, + null, + ''text/plain'', + null, + ''text'' + ); + + + v_bullet_items_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_bullet_items, + v_bullet_items_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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;' language 'plpgsql'; + +create function wp_slide__delete_preamble ( + integer +) returns integer as' +declare + delete_preamble__preamble_item_id alias for $1; +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 = delete_preamble__preamble_item_id); + + delete from cr_item_publish_audit + where item_id = delete_preamble__preamble_item_id; + + PERFORM content_item__delete(delete_preamble__preamble_item_id); + return 0; +end;' language 'plpgsql'; + +create function wp_slide__delete_postamble( + integer +) returns integer as' +declare + delete_postamble__postamble_item_id alias for $1; +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 = delete_postamble__postamble_item_id); + + delete from cr_item_publish_audit + where item_id = delete_postamble__postamble_item_id; + + PERFORM content_item__delete(delete_postamble__postamble_item_id); + return 0; +end;' language 'plpgsql'; + +create function wp_slide__delete_bullet_items( + integer +) returns integer as' +declare + delete_bullet_items__bullet_items_item_id alias for $1; +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 = delete_bullet_items__bullet_items_item_id); + + delete from cr_item_publish_audit + where item_id = delete_bullet_items__bullet_items_item_id; + + PERFORM content_item__delete(delete_bullet_items__bullet_items_item_id); + return 0; +end;' language 'plpgsql'; + +create function wp_slide__delete( + integer +) returns integer as' +declare + del_rec record; + slide_item_id alias for $1; + 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; +begin + for del_rec in select item_id as attach_item_id + from cr_items + where content_type in (''cr_wp_image_attachment'', ''cr_wp_file_attachment'') + and parent_id = slide_item_id + loop + PERFORM wp_attachment__delete(del_rec.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; + + PERFORM wp_slide__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; + + PERFORM wp_slide__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; + + PERFORM wp_slide__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; + + PERFORM content_item__delete(slide_item_id); + return 0; +end;' language 'plpgsql'; + +create function wp_slide__get_preamble_revision ( + integer +) returns text as ' +declare + p_slide_revision_id alias for $1; +begin + return content + from cr_revisions r, cr_wp_slides_preamble sp + where sp.slide_id = p_slide_revision_id + and r.revision_id = sp.id; +end;' language 'plpgsql'; + +create function wp_slide__get_postamble_revision ( + integer +) returns text as ' +declare + p_slide_revision_id alias for $1; +begin + return content + from cr_revisions r, cr_wp_slides_postamble sp + where sp.slide_id = p_slide_revision_id + and r.revision_id = sp.id; +end;' language 'plpgsql'; + +create function wp_slide__get_bullet_items_revision ( + integer +) returns text as ' +declare + p_slide_revision_id alias for $1; +begin + return content + from cr_revisions r, cr_wp_slides_bullet_items sp + where sp.slide_id = p_slide_revision_id + and r.revision_id = sp.id; +end;' language 'plpgsql'; + +create function wp_slide__get_postamble( + integer +) returns text as ' +declare + p_slide_item_id alias for $1; +begin + return content + from cr_revisions, cr_items + where cr_items.content_type = ''cr_wp_slide_postamble'' + and cr_items.parent_id = p_slide_item_id + and cr_revisions.revision_id = cr_items.live_revision; +end;' language 'plpgsql'; + +create function wp_slide__get_preamble( + integer +) returns text as' +declare + p_slide_item_id alias for $1; +begin + return content + from cr_revisions, cr_items + where cr_items.content_type = ''cr_wp_slide_preamble'' + and cr_items.parent_id = p_slide_item_id + and cr_revisions.revision_id = cr_items.live_revision; +end;' language 'plpgsql'; + +create function wp_slide__get_bullet_items( + integer +) returns text as' +declare + p_slide_item_id alias for $1; +begin + return content + from cr_revisions, cr_items + where cr_items.content_type = ''cr_wp_slide_bullet_items'' + and cr_items.parent_id = p_slide_item_id + and cr_revisions.revision_id = cr_items.live_revision; +end;' language 'plpgsql'; + +create function wp_slide__new_revision( + timestamp, + integer, + varchar, + integer, + varchar, + text, + varchar, + varchar, + integer, + integer, + integer, + boolean, + boolean +) returns integer as' +declare + p_creation_date alias for $1; + p_creation_user alias for $2; + p_creation_ip alias for $3; + p_slide_item_id alias for $4; + p_slide_title alias for $5; + p_preamble alias for $6; + p_bullet_items alias for $7; + p_postamble alias for $8; + p_style alias for $9; + p_original_slide_id alias for $10; + p_sort_key alias for $11; + p_include_in_outline_p alias for $12; + p_context_break_after_p alias for $13; + 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( + null, + null, + current_timestamp, + ''text/plain'', + null, + null, + p_slide_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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, + p_slide_title, + p_style, + p_original_slide_id, + p_sort_key, + p_include_in_outline_p, + p_context_break_after_p + ); + + select item_id into v_preamble_item_id + from cr_items + where parent_id = p_slide_item_id + and content_type = ''cr_wp_slide_preamble''; + + v_preamble_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_preamble, + v_preamble_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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 = p_slide_item_id + and content_type = ''cr_wp_slide_postamble''; + + v_postamble_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_postamble, + v_postamble_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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 = p_slide_item_id + and content_type = ''cr_wp_slide_bullet_items''; + + v_bullet_items_revision_id := content_revision__new( + null, + null, + current_timestamp, + ''text/plain'', + null, + p_bullet_items, + v_bullet_items_item_id, + null, + p_creation_date, + p_creation_user, + p_creation_ip + ); + + PERFORM 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 0; +end;' language 'plpgsql'; + +-- bug fixed, delete first an image then a file, roc@ +create function wp_attachment__delete( + integer +) returns integer as' +declare + p_attach_item_id alias for $1; +begin + + delete from cr_wp_image_attachments + where exists (select 1 from cr_revisions where revision_id + = cr_wp_image_attachments.attach_id + and item_id = p_attach_item_id); + + delete from cr_wp_file_attachments + where exists (select 1 from cr_revisions where revision_id + = cr_wp_file_attachments.attach_id + and item_id = p_attach_item_id); + + delete from cr_item_publish_audit + where item_id = p_attach_item_id; + + PERFORM content_item__delete(p_attach_item_id); + return 0; +end;' language 'plpgsql'; + +create function wp_attachment__new_revision ( + integer +) returns integer as' +declare + p_attach_item_id alias for $1; +begin + return 0; +end; 'language 'plpgsql'; + + + + +create function wp_presentation__set_live_revision(integer) returns integer as ' +declare + p_revision_id alias for $1; + v_revision_id integer; +begin + perform content_item__set_live_revision(p_revision_id); + + select id into v_revision_id + from cr_wp_presentations_aud + where presentation_id = p_revision_id; + + perform content_item__set_live_revision(v_revision_id); + + select id into v_revision_id + from cr_wp_presentations_back + where presentation_id = p_revision_id; + + perform content_item__set_live_revision(v_revision_id); + return 0; +end;' language 'plpgsql'; + + +-- style functions roc@ + +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'; + + -- new permissions roc@ select acs_privilege__add_child('wp_edit_presentation', 'wp_view_presentation'); select acs_privilege__add_child('wp_admin_presentation', 'wp_create_presentation');