-- Wimpy Point Data Model for ACS 4.0
--
-- Paul Konigsberg paulk@arsdigita.com 10/22/00
-- original module author Jon Salz jsalz@mit.edu
-- Ported to PostgreSQL: Jack Purswani and Sidharth Widge
-- Notes:
-- Nothing is going into the content repository.
-- Reasons for putting stuff in the repository:
-- You get versioning. You get indexed for intermedia searching.
-- Things that should go into the repository are:
-- 1. Presentation: You should be able to version a presentation. When you
-- do, you should remember the current versions of all the slides in it.
-- 2. Slides.
-- 3. Attachments - since the repository is supposed to be good
-- at handling weird lob data. (i.e. handling the mime type and
-- serving it back.
-- More on content repository: After Jesse Koontz's review, he said:
-- presentation -- subclass from content_items
-- slides -- subclass from content_items
-- Versions: are copied presentation
-- Clobs are mostly not used. Instead varchar(4000) is still being used
-- because working with clobs is tough in plsql. This needs to change.
-- Presentation have no context_id....should they? Answer: Yes their context id should
-- point to the package_id of the instance of this wimpy point.
-- (There could be multiple instances.)
-- Only the basic -1 is implemented right now.
-- Style for presentation. We'll think more about this later if there's time
-- maybe allow ADPs for more flexibility.
insert into cr_mime_types (label, mime_type)
select 'Binary data', 'application/octet-stream'
from dual
where not exists (select 1 from cr_mime_types where mime_type ='application/octet-stream');
create sequence wp_style_seq;
create table wp_styles (
style_id integer
constraint wp_styles_style_id_pk
primary key,
name varchar(400)
constraint wp_styles_name_nn
not null,
css text,
text_color varchar(20) check(text_color like '%,%,%'),
background_color varchar(20) check(background_color like '%,%,%'),
background_image integer default 0,
link_color varchar(20) check(link_color like '%,%,%'),
alink_color varchar(20) check(alink_color like '%,%,%'),
vlink_color varchar(20) check(vlink_color like '%,%,%'),
public_p char(1) default 'f' check(public_p in ('t','f')),
owner integer
constraint wp_styles_to_users
references users (user_id)
);
insert into wp_styles(style_id, name, css)
values(-1, 'Default (Plain)',
'BODY { back-color: white; color: black } P { line-height: 120% } UL { line-height: 140% }');
-- 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);
--jackp: p_create the presentation table
create table cr_wp_presentations (
presentation_id integer
constraint cr_wp_presentations_id_fk
references cr_revisions
constraint cr_wp_presentations_pk
primary key,
-- The title of the presentations, as displayed to the user.
pres_title varchar(400)
constraint cr_wp_presentations_title_nn
not null,
-- A signature on the bottom.
page_signature varchar(200),
-- The copyright notice displayed on all pages.
copyright_notice varchar(400),
-- Style information.
style integer
constraint cr_wp_style_fk
references wp_styles on delete set null,
public_p boolean
constraint cr_wp_public_p_ck
check(public_p in ('t','f')),
-- Show last-modified date for slide?
show_modified_p boolean
constraint cr_wp_show_p_ck
check(show_modified_p in ('t','f')),
show_comments_p char(1) default 'f'
constraint cr_wp_comments_p
check(show_comments_p in ('t','f'))
);
-- Slide belonging to presentations.
create table cr_wp_slides (
slide_id integer
constraint cr_wp_slides_slide_id_fk
references cr_revisions
constraint cr_wp_slides_slide_id_pk
primary key,
original_slide_id integer,
sort_key integer
constraint cr_wp_slides_sort_key_nn
not null,
slide_title varchar(400)
constraint cr_wp_slides_title_nn
not null,
include_in_outline_p boolean
constraint cr_wp_slides_incld_ck
check(include_in_outline_p in ('t','f')),
context_break_after_p boolean
constraint cr_wp_slides_context_ck
check(context_break_after_p in ('t','f')),
style integer
constraint cr_wp_slides_style_fk
references wp_styles
);
create table cr_wp_presentations_aud (
id integer
references cr_revisions,
presentation_id integer
constraint cr_wp_paudience_pid_nn
not null
constraint cr_wp_paudience_pid_fk
references cr_wp_presentations
);
create table cr_wp_presentations_back (
id integer
references cr_revisions,
presentation_id integer
constraint cr_wp_pbackground_pid_nn
not null
constraint cr_wp_pbackground_pid_fk
references cr_wp_presentations
);
--jackp: table to store the preamble
create table cr_wp_slides_preamble (
id integer
references cr_revisions,
slide_id integer
constraint cr_wp_spreamble_sid_nn
not null
constraint cr_wp_spreamble_sid_fk
references cr_wp_slides
);
--jackp: table to store the postamble
create table cr_wp_slides_postamble (
id integer
references cr_revisions,
slide_id integer
constraint cr_wp_spostamble_sid_nn
not null
constraint cr_wp_spostamble_sid_fk
references cr_wp_slides
);
--jackp: table to store the bullet items
create table cr_wp_slides_bullet_items (
id integer
references cr_revisions,
slide_id integer
constraint cr_wp_sbullet_sid_nn
not null
constraint cr_wp_sbullet_sid_fk
references cr_wp_slides
);
--jackp: Need to use inline functions in PostgreSQL.
--jackp: We p_create the main content type here.
--jackp: Need to use PERFORM command line to indicate that the line needs
--jackp: to be run.
create function inline_0 ()
returns integer as'
begin
--jackp: from acs-content-repository/sql/postgresql/content-type.sql
--jackp: The structure for content_type__create_type is as follows:
--jackp: PERFORM content_type__create_type (
--jackp: content_type
--jackp: supertype
--jackp: pretty_name
--jackp: pretty_plural
--jackp: table_name
--jackp: id_column
--jackp: name_method
--jackp: );
PERFORM content_type__create_type (
''cr_wp_presentation_aud'',
''content_revision'',
''Wimpy Point Presentation Audience'',
''Wimpy Point Presentation Audiences'',
''cr_wp_presentations_aud'',
''id'',
null
);
PERFORM content_type__create_type (
''cr_wp_presentation_back'',
''content_revision'',
''Wimpy Point Presentation Background'',
''WimpyPoint Presentation Backgrounds'',
''cr_wp_presentations_back'',
''id'',
null
);
PERFORM content_type__create_type (
''cr_wp_presentation'',
''content_revision'',
''Wimpy Point Presentation'',
''WimpyPoint Presentations'',
''cr_wp_presentations'',
''presentation_id'',
null
);
return 0;
end;' language 'plpgsql';
select inline_0 ();
drop function inline_0 ();
create function inline_1 ()
returns integer as'
begin
PERFORM content_type__create_type (
''cr_wp_slide_preamble'',
''content_revision'',
''wimpy Point Slide Preamble'',
''Wimpy Point Slide Preambles'',
''cr_wp_slides_preamble'',
''id'',
null
);
PERFORM content_type__create_type (
''cr_wp_slide_postamble'',
''content_revision'',
''wimpy Point Slide Postamble'',
''WimpyPoint Slide Postamble'',
''cr_wp_slides_postamble'',
''id'',
null
);
PERFORM content_type__create_type (
''cr_wp_slide_bullet_items'',
''content_revision'',
''wimpy Point Slide Bullet Items'',
''WimpyPoint Slide Bullet Items'',
''cr_wp_slides_bullet_items'',
''id'',
null
);
PERFORM content_type__create_type (
''cr_wp_slide'',
''content_revision'',
''WimpyPoint Slide'',
''wimpy point slide'',
''cr_wp_slides'',
''slide_id'',
null
);
return 0;
end;' language 'plpgsql';
select inline_1 ();
drop function inline_1 ();
--jackp: This section p_creates the attributes that are linked to the
--jackp: different types
create function inline_2 ()
returns integer as'
declare
attr_id acs_attributes.attribute_id%TYPE;
begin
--jackp: from acs-content-repository/sql/postgresql/content-type.sql
--jackp: attr_id := content_type__create_attribute (
--jackp: content_type
--jackp: attribute_name
--jackp: datatype
--jackp: pretty_name
--jackp: pretty_plural
--jackp: sort_order
--jackp: default_value
--jackp: column_spec
--jackp: );
attr_id := content_type__create_attribute (
''cr_wp_presentation'',
''pres_title'',
''text'',
''Presentation Title'',
''Presentation Titles'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_presentation'',
''page_signature'',
''text'',
''Page Signature'',
''Page Signatures'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_presentation'',
''copyright_notice'',
''text'',
''Copyright Notice'',
''Copyright Notices'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_presentation'',
''style'',
''integer'',
''Style'',
''Styles'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_presentation'',
''public_p'',
''boolean'',
''Public Flag'',
''Public Flags'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_presentation'',
''show_modified_p'',
''boolean'',
''Show Modified Flag'',
''Show Modified Flags'',
null,
null,
''text''
);
return 0;
end;' language 'plpgsql';
select inline_2 ();
drop function inline_2 ();
create function inline_3 ()
returns integer as'
declare
attr_id acs_attributes.attribute_id%TYPE;
begin
attr_id := content_type__create_attribute (
''cr_wp_slide'',
''sort_key'',
''integer'',
''Sort Key'',
''Sort Keys'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_slide'',
''slide_title'',
''text'',
''Slide Title'',
''Slide Titles'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_slide'',
''include_in_outline_p'',
''boolean'',
''Include in Outline Flag'',
''Include in Outline Flags'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_slide'',
''context_break_after_p'',
''boolean'',
''Context Break After Flag'',
''Context Break After Flags'',
null,
null,
''text''
);
attr_id := content_type__create_attribute (
''cr_wp_slide'',
''style'',
''integer'',
''Style'',
''Styles'',
null,
null,
''text''
);
return 0;
end;' language 'plpgsql';
select inline_3 ();
drop function inline_3 ();
create function inline_4 ()
returns integer as'
declare
attr_id acs_attributes.attribute_id%TYPE;
begin
attr_id := content_type__create_attribute (
''cr_wp_presentation_aud'',
''presentation_id'',
''integer'',
''Prsentation ID'',
''Presentation IDs'',
null,
null,
''integer''
);
attr_id := content_type__create_attribute (
''cr_wp_presentation_back'',
''presentation_id'',
''integer'',
''Prsentation ID'',
''Presentation IDs'',
null,
null,
''integer''
);
attr_id := content_type__create_attribute (
''cr_wp_slide_preamble'',
''slide_id'',
''integer'',
''Slide ID'',
''Slide IDs'',
null,
null,
''integer''
);
attr_id := content_type__create_attribute (
''cr_wp_slide_postamble'',
''slide_id'',
''integer'',
''Slide ID'',
''Slide IDs'',
null,
null,
''integer''
);
attr_id := content_type__create_attribute (
''cr_wp_slide_bullet_items'',
''slide_id'',
''integer'',
''Slide ID'',
''Slide IDs'',
null,
null,
''integer''
);
return 0;
end;' language 'plpgsql';
select inline_4 ();
drop function inline_4 ();
--jackp: We set the llinks with child types here.
--found in acs-content-repository/sql/postgresql/content-type.sql
create function inline_5 ()
returns integer as'
begin
PERFORM content_type__register_child_type(''cr_wp_presentation'',''cr_wp_presentation_aud'', ''generic'', 0, null);
PERFORM content_type__register_child_type(''cr_wp_presentation'', ''cr_wp_presentation_back'', ''generic'', 0, null);
PERFORM content_type__register_child_type(''cr_wp_presentation'', ''cr_wp_slide'', ''generic'', 0, null);
PERFORM content_type__register_child_type(''cr_wp_slide'', ''cr_wp_slide_preamble'', ''generic'', 0, null);
PERFORM content_type__register_child_type(''cr_wp_slide'', ''cr_wp_slide_postamble'', ''generic'', 0, null);
PERFORM content_type__register_child_type(''cr_wp_slide'', ''cr_wp_slide_bullet_items'', ''generic'', 0, null);
return 0;
end;' language 'plpgsql';
select inline_5 ();
drop function inline_5 ();
--jackp: Register the content types with the acs-objects
--jackp: found in acs-content-repository/sql/postgresql/content-folder.sql
create function inline_6 ()
returns integer as'
begin
PERFORM content_folder__register_content_type(content_item_globals.c_root_folder_id, ''cr_wp_presentation'', ''f'');
PERFORM content_folder__register_content_type(content_item_globals.c_root_folder_id, ''cr_wp_presentation_aud'', ''f'');
PERFORM content_folder__register_content_type(content_item_globals.c_root_folder_id, ''cr_wp_presentation_back'', ''f'');
PERFORM content_folder__register_content_type(content_item_globals.c_root_folder_id, ''cr_wp_slide'', ''f'');
PERFORM content_folder__register_content_type(content_item_globals.c_root_folder_id, ''cr_wp_slide_preamble'', ''f'');
PERFORM content_folder__register_content_type(content_item_globals.c_root_folder_id, ''cr_wp_slide_postamble'', ''f'');
PERFORM content_folder__register_content_type(content_item_globals.c_root_folder_id, ''cr_wp_slide_bullet_items'', ''f'');
return 0;
end;' language 'plpgsql';
select inline_6 ();
drop function inline_6 ();
-- DRB: table for file attachments. Empty but necessary because the object
-- system doesn't allow for simple renaming of types. Nor can two types
-- share an attribute table.
create table cr_wp_file_attachments (
attach_id integer
constraint cr_fattach_attach_id_fk
references cr_revisions
constraint cr_fattach_attach_id_pk
primary key
);
create table cr_wp_image_attachments (
attach_id integer
constraint cr_iattach_attach_id_fk
references cr_revisions
constraint cr_iattach_attach_id_pk
primary key,
display varchar(20)
constraint cr_iattach_display_ck
check(display in (
'preamble',
'bullets',
'postamble',
'top',
'after_preamble',
'after_bullets',
'bottom'))
);
--jackp: p_create the content-type and content-attribute associated with
--jackp: attachments
create function inline_7 ()
returns integer as'
declare
attr_id acs_attributes.attribute_id%TYPE;
begin
PERFORM content_type__create_type (
''cr_wp_image_attachment'',
''image'',
''Wimpy Image Attachment'',
''Wimpy Image Attachments'',
''cr_wp_image_attachments'',
''attach_id'',
null
);
attr_id := content_type__create_attribute (
''cr_wp_image_attachment'',
''display'',
''text'',
''Where to display'',
''Where display this'',
null,
null,
''varchar(20)''
);
PERFORM content_type__create_type (
''cr_wp_file_attachment'',
''content_revision'',
''Wimpy File Attachment'',
''Wimpy File Attachments'',
''cr_wp_file_attachments'',
''attach_id'',
null
);
attr_id := content_type__create_attribute (
''cr_wp_file_attachment'',
''display'',
''text'',
''Where to display'',
''Where display this'',
null,
null,
''varchar(20)''
);
return 0;
end;' language 'plpgsql';
select inline_7 ();
drop function inline_7 ();
--jackp: register the child types for attachments
create function inline_8 ()
returns integer as'
begin
PERFORM content_type__register_child_type(
''cr_wp_slide'',
''cr_wp_image_attachment'',
''generic'',
0,
null
);
PERFORM content_type__register_child_type(
''cr_wp_slide'',
''cr_wp_file_attachment'',
''generic'',
0,
null
);
return 0;
end;' language 'plpgsql';
select inline_8 ();
drop function inline_8 ();
--jackp: register the different content types for attachments
--create function inline_9 ()
--returns integer as'
--begin
-- PERFORM content_folder__register_content_type(
-- content_item_globals.c_root_folder_id,
-- ''cr_wp_attachment'',
-- ''f''
-- );
-- return 0;
--end;' language 'plpgsql';
--select inline_9 ();
--drop function inline_9 ();
commit;
--Define some privileges on the wp_presentation object.
--jackp: found in acs-kernel/sql/postgresql/acs-permissions-create.sql
create function inline_10 ()
returns integer as'
begin
PERFORM acs_privilege__create_privilege(
''wp_admin_presentation'',
null,
null
);
PERFORM acs_privilege__create_privilege(
''wp_create_presentation'',
null,
null
);
PERFORM acs_privilege__create_privilege(
''wp_edit_presentation'',
null,
null
);
PERFORM acs_privilege__create_privilege(
''wp_delete_presentation'',
null,
null
);
PERFORM acs_privilege__create_privilege(
''wp_view_presentation'',
null,
null
);
return 0;
end;' language 'plpgsql';
select inline_10 ();
drop function inline_10 ();
--jackp: set the permissions applicable to the package
create function inline_11 ()
returns integer as'
declare
default_context acs_objects.object_id%TYPE;
registered_users acs_objects.object_id%TYPE;
the_public acs_objects.object_id%TYPE;
begin
default_context := acs__magic_object_id(''default_context'');
registered_users := acs__magic_object_id(''registered_users'');
-- the_public := acs__magic_object_id(''the_public'');
PERFORM acs_permission__grant_permission(
default_context,
registered_users,
''wp_create_presentation''
);
-- this commented out because permission, with this any user could see an slide that has not become public!
-- PERFORM acs_permission__grant_permission(
-- default_context,
-- the_public,
-- ''wp_view_presentation''
-- );
return 0;
end;' language 'plpgsql';
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 with time zone,
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 with time zone,
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 with time zone,
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 with time zone,
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');
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');
\i wp-packages-create.sql
OpenACS – The Toolkit for Online Communities : ACS API Browser : Wimpy Point 5.2.1d : wp-slim-create.sql
Begin main content
