Index: openacs-4/packages/press/press.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/press.info,v diff -u -r1.6 -r1.7 --- openacs-4/packages/press/press.info 8 Oct 2003 16:05:26 -0000 1.6 +++ openacs-4/packages/press/press.info 31 Oct 2003 16:09:23 -0000 1.7 @@ -9,6 +9,7 @@ oracle + postgresql Sarah Barwig Press allows publishers to display external press coverage of their site. @@ -19,51 +20,6 @@ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Index: openacs-4/packages/press/sql/postgresql/press-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/sql/postgresql/press-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/sql/postgresql/press-create.sql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,458 @@ +-- /packages/press/sql/press-create.sql +-- +-- @author sarah@arsdigita.com +-- @author stefan@arsdigita.com +-- @author ron@arsdigita.com +-- @author michael@steigman.net +-- +-- @created 2000-11-15 +-- +-- $Id: press-create.sql,v 1.1 2003/10/31 16:09:23 michaels Exp $ + +-- +-- Permissions +-- + + +-- the read privilege is by default granted to 'the_public' +-- the site-wide administrator has to change this in /permissions/ +-- if he wants to restrict an instance to a specific party_id only + +-- the press_admin has all privileges, read, create, delete, approve +-- it is a child of 'admin' + +select acs_privilege__create_privilege('press_read',null,null); +select acs_privilege__create_privilege('press_create',null,null); +select acs_privilege__create_privilege('press_delete',null,null); +select acs_privilege__create_privilege('press_approve',null,null); + + -- bind privileges to global names +select acs_privilege__add_child('read','press_read'); +select acs_privilege__add_child('create','press_create'); +select acs_privilege__add_child('delete','press_delete'); +select acs_privilege__add_child('admin','press_approve'); + +-- add this to the press_admin privilege +select acs_privilege__create_privilege('press_admin','Press administrator',null); + +-- press administrator binds to global 'admin', plus inherits press_* permissions +select acs_privilege__add_child('admin','press_admin'); +select acs_privilege__add_child('press_admin','press_approve'); +select acs_privilege__add_child('press_admin','press_create'); +select acs_privilege__add_child('press_admin','press_delete'); + +-- assign permission to defined contexts within ACS by default +-- + +create function inline_0 () +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''); + + -- give the public permission to read by default + perform acs_permission__grant_permission ( + default_context, -- object_id + the_public, -- grantee_id + ''press_read'' -- privilege + ); + + -- outcomment if your site wants to + -- give registered users permission to upload items by default + -- perform acs_permission__grant_permission ( + -- default_context, -- object_id + -- registered_users, -- grantee_id + -- ''press_create'' -- privilege + -- ); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + +-- this table stores the different adp-templates to show a press item + +create table press_templates ( + template_id integer primary key not null, + -- we use this to select the template + template_name varchar(100) not null unique, + -- the adp code fragment + template_adp text not null +); + +-- We use the content repository to store most of the information for +-- press items. +-- +-- See http://cvs.arsdigita.com/acs/packages/acs-content-repository) + +create table cr_press ( + press_id integer + constraint cr_press_id_fk references cr_revisions + constraint cr_press_pk primary key, + -- include package_id to provide support for multiple instances + package_id integer, + -- constraint cr_press_package_id_nn not null, + -- information about the publication where this press item appeared + -- *** The journal name, the journal URL *** + publication_name varchar(100) + constraint cr_press_publication_name_nn not null, + publication_link varchar(200), + -- *** the specific journal issue *** + publication_date timestamptz not null, + publication_date_desc varchar(100), + -- *** the article link, pages *** + article_link varchar(400), + article_pages varchar(40), + article_abstract_html_p varchar(1) + constraint cp_article_abstract_html_p_ck + check (article_abstract_html_p in ('t','f')), + -- *** support for dates when items are displayed or archived *** + -- unapproved release dates are null + release_date timestamptz, + -- unscheduled archiving dates are null + archive_date timestamptz, + -- support for approval, if ApprovalRequiredP == 1 + approval_user integer + constraint cr_press_approval_user_fk + references users, + approval_date timestamptz, + approval_ip varchar(50), + -- *** presentation information *** + -- supply own press-specific templates (see table below) until + -- template system is better organized and documented + template_id integer default 1 + constraint cr_press_templ_id references press_templates +); + + +-- index to avoid lock situation through parent table +-- cr_press_press_id_fk is not created because press_id +-- is already indexed through virtue of being the primary +-- key for the table + +create index cr_press_appuser_fk on cr_press(approval_user); + +-- Initialize with one site-wide Default template +-- Make sure that you pass the following variables to the template +-- @publication_link@ (optional) +-- @publication_name@ +-- @article_link@ (optional) +-- @article_title@ +-- @pretty_publication_date@ +-- @html_p@ +-- @article_abstract@ + +insert into press_templates + (template_id, + template_name, + template_adp) +values + (1, + 'Default', + '@publication_name@ - @article_title@
@publication_date@ : "@article_abstract@"'); + +select content_type__create_type ( + 'press', -- content_type + 'content_revision', -- supertype + 'Press Item', -- pretty_name + 'Press Items', -- pretty_plural + 'cr_press', -- table_name + 'press_id', -- id_column + 'news__name' -- name_method +); + +-- create attributes for widget generation later + +-- publication in which the press article appeared + +select content_type__create_attribute ( + 'press', -- content_type + 'publication_name', -- attribute_name + 'text', -- datatype + 'Publication', -- pretty_name + 'Publications', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar(100)' -- column_spec +); + +-- URL link to this publication + +select content_type__create_attribute ( + 'press', -- content_type + 'publication_link', -- attribute_name + 'text', -- datatype + 'Publication URL', -- pretty_name + 'Publication URL', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar(200)' -- column_spec +); + +-- issue date + +select content_type__create_attribute ( + 'press', -- content_type + 'publication_date', -- attribute_name + 'date', -- datatype + 'Publication Date', -- pretty_name + 'Publication Dates',-- pretty_plural + null, -- sort_order + null, -- default_value + 'timestamptz' -- column_spec +); + +-- issue date in words (optional) + +select content_type__create_attribute ( + 'press', -- content_type + 'publication_date_desc', -- attribute_name + 'text', -- datatype + 'Publication Date Description', -- pretty_name + 'Publication Date Description', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar(100)' -- column_spec +); + +-- URL link to the article + +select content_type__create_attribute ( + 'press', -- content_type + 'article_link', -- attribute_name + 'text', -- datatype + 'Article Link', -- pretty_name + 'Article Links', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar(400)' -- column_spec +); + +-- article page range, e.g. 'pp 83-100' + +select content_type__create_attribute ( + 'press', -- content_type + 'article_pages', -- attribute_name + 'text', -- datatype + 'Article Pages', -- pretty_name + 'Articles Pages', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar(40)' -- column_spec +); + +-- a flag that tells if the article abstract is in HTML or not + +select content_type__create_attribute ( + 'press', -- content_type + 'article_abstract_html_p', -- attribute_name + 'text', -- datatype + 'Article Abstract HTML Flag', -- pretty_name + 'Article Abstract HTML Flag', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar(1)' -- column_spec +); + +-- release date of press release + +select content_type__create_attribute ( + 'press', -- content_type + 'release_date', -- attribute_name + 'timestamp', -- datatype + 'Release Date', -- pretty_name + 'Release Dates', -- pretty_plural + null, -- sort_order + current_date::varchar, -- default_value + 'timestamptz' -- column_spec +); + +-- archive date of press release + +select content_type__create_attribute ( + 'press', -- content_type + 'archive_date', -- attribute_name + 'timestamp', -- datatype + 'Archival Date', -- pretty_name + 'Archival Dates', -- pretty_plural + null, -- sort_order + null, -- default_value + 'timestamptz' -- column_spec +); + +-- assignement to an authorized user for approval + +select content_type__create_attribute ( + 'press', -- content_type + 'approval_user', -- attribute_name + 'integer', -- datatype + 'Approval User', -- pretty_name + 'Approval Users', -- pretty_plural + null, -- sort_order + null, -- default_value + 'integer' -- column_spec +); + +-- approval date + +select content_type__create_attribute ( + 'press', -- content_type + 'approval_date', -- attribute_name + 'timestamp', -- datatype + 'Approval Date', -- pretty_name + 'Approval Dates', -- pretty_plural + null, -- sort_order + current_date::varchar, -- default_value + 'timestamptz' -- column_spec +); + +-- approval IP address + +select content_type__create_attribute ( + 'press', -- content_type + 'approval_ip', -- attribute_name + 'text', -- datatype + 'Approval IP', -- pretty_name + 'Approval IPs', -- pretty_plural + null, -- sort_order + null, -- default_value + 'varchar' -- column_spec +); + +-- CREATE THE PRESS FOLDER as our CONTAINER *** + +-- create 1 press folder; different instances are filtered by package_id +-- associate content types with press folder + +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; +begin + v_folder_id := content_folder__new ( + ''press'', -- name + ''press'', -- label + ''Press Item Root Folder, all press items go in here'', -- description + null -- parent_id + ); + perform content_folder__register_content_type ( + v_folder_id, -- folder_id + ''press'', -- content_type + ''t'' -- include_subtypes + ); + perform content_folder__register_content_type ( + v_folder_id, -- folder_id + ''content_revision'', -- content_type + ''t'' -- include_subtypes + ); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + +-- +-- views on cr_press that combine information from cr_press, cr_items, cr_revisions +-- + +-- view of all press items in the system + +create or replace view press_items +as +select ci.item_id as item_id, + press_id, + package_id, + publication_name, + publication_link, + publication_date, + publication_date_desc, + cr.title as article_title, + cr.content as article_abstract, + cr.description as revision_note, + article_link, + article_pages, + article_abstract_html_p, + release_date, + archive_date, + live_revision, + press__status(press_id) as status + from cr_items ci, cr_revisions cr, cr_press cp + where ( ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cp.press_id) + or ( ci.live_revision is null + and ci.item_id = cr.item_id + and cr.revision_id = content_item__get_latest_revision(ci.item_id) + and cr.revision_id = cp.press_id); + +-- view on all approved press items (active revision only) + +create or replace view press_items_approved +as +select ci.item_id as item_id, + package_id, + publication_name, + publication_link, + publication_date, + publication_date_desc, + cr.title as article_title, + cr.content as article_abstract, + cr.description as revision_note, + article_link, + article_pages, + article_abstract_html_p as html_p, + release_date, + archive_date, + pt.template_id, + pt.template_name, + pt.template_adp + from cr_items ci, cr_revisions cr, cr_press cp, press_templates pt + where ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cp.press_id + and pt.template_id = cp.template_id; + +-- view of all revisions of a press item + +create or replace view press_item_revisions +as +select revision_id, + cr.item_id as item_id, + package_id, + publication_name, + publication_link, + publication_date, + publication_date_desc, + cr.title as article_title, + cr.content as article_abstract, + cr.description as revision_note, + article_link, + article_pages, + article_abstract_html_p as html_p, + release_date, + archive_date, + cp.template_id, + template_name, + template_adp, + creation_date, + press__status(press_id) as status, + first_names || ' ' || last_name as item_creator, + creation_ip, + ci.name as item_name + from cr_revisions cr, cr_press cp, press_templates pt, cr_items ci, acs_objects ao, persons + where cr.revision_id = ao.object_id + and cr.revision_id = cp.press_id + and cp.template_id = pt.template_id + and ci.item_id = cr.item_id + and ao.creation_user = persons.person_id; + +\i press-package-create.sql Index: openacs-4/packages/press/sql/postgresql/press-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/sql/postgresql/press-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/sql/postgresql/press-drop.sql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,196 @@ +-- /packages/press/sql/press-drop.sql +-- +-- @author sarah@arsdigita.com +-- @author stefan@arsdigita.com +-- @author ron@arsdigita.com +-- @author michael@steigman.net +-- +-- @created 2000-11-27 +-- +-- $Id: press-drop.sql,v 1.1 2003/10/31 16:09:23 michaels Exp $ + +-- delete press views + +drop view press_items_approved; +drop view press_item_revisions; +drop view press_items; + +-- drop plpgsql + +\i press-package-drop.sql + +-- unregister content_types from folder + +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; + v_item_cursor record; +begin + v_folder_id := content_item__get_id(''press'',null,''f''); + + -- delete all contents of press folder + for v_item_cursor in + select item_id + from cr_items + where parent_id = v_folder_id + loop + perform content_item__delete(press_rec.item_id); + end loop; + + -- cheesy sub for cursor that doesnt work. need to fix. + -- update cr_items set parent_id=0 where parent_id = v_folder_id; + -- unregister_content_types + + perform content_folder__unregister_content_type ( + v_folder_id, -- folder_id + ''content_revision'', -- content_type + ''t'' -- include_subtypes + ); + + perform content_folder__unregister_content_type ( + v_folder_id, -- folder_id + ''press'', -- content_type + ''t'' -- include_subtypes + ); + + -- delete press folder + + perform content_folder__delete(v_folder_id); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + +-- drop attributes + +-- the Journal in which the press article appeared +select content_type__drop_attribute ( + 'press', -- content_type + 'publication_name', -- attribute_name + 'f' -- drop_column +); +-- the URL link to this Journal +select content_type__drop_attribute ( + 'press', -- content_type + 'publication_link', -- attribute_name + 'f' -- drop_column +); +-- the journal issue date +select content_type__drop_attribute ( + 'press', -- content_type + 'publication_date', -- attribute_name + 'f' -- drop_column +); +-- the journal issue date in words +select content_type__drop_attribute ( + 'press', -- content_type + 'publication_date_desc', -- attribute_name + 'f' -- drop_column +); +-- the URL link to the article +select content_type__drop_attribute ( + 'press', -- content_type + 'article_link', -- attribute_name + 'f' -- drop_column +); +-- the article page range, e.g. 'pp 83-100' +select content_type__drop_attribute ( + 'press', -- content_type + 'article_pages', -- attribute_name + 'f' -- drop_column +); +-- a flag that tells if the article abstract is in HTML or not +select content_type__drop_attribute ( + 'press', -- content_type + 'article_abstract_html_p', -- attribute_name + 'f' -- drop_column +); +-- website release date of press release +select content_type__drop_attribute ( + 'press', -- content_type + 'release_date', -- attribute_name + 'f' -- drop_column +); +-- website archive date of press release +select content_type__drop_attribute ( + 'press', -- content_type + 'archive_date', -- attribute_name + 'f' -- drop_column +); +-- assignement to an authorized user for approval +select content_type__drop_attribute ( + 'press', -- content_type + 'approval_user', -- attribute_name + 'f' -- drop_column +); +-- approval date +select content_type__drop_attribute ( + 'press', -- content_type + 'approval_date', -- attribute_name + 'f' -- drop_column +); +-- approval IP address +select content_type__drop_attribute ( + 'press', -- content_type + 'approval_ip', -- attribute_name + 'f' -- drop_column +); +-- delete content_type 'press' +select acs_object_type__drop_type ( + 'press', -- object_type + 't' -- cascade_p +); + +-- drop indices to avoid lock situation through parent table + +drop index cr_press_appuser_fk; + +-- delete pertinent info from cr_press + +drop table cr_press cascade; +drop table press_templates cascade; + +-- delete privileges; + +create function inline_0 () +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 + perform acs_privilege__remove_child(''press_admin'',''press_approve''); + perform acs_privilege__remove_child(''press_admin'',''press_create''); + perform acs_privilege__remove_child(''press_admin'',''press_delete''); + perform acs_privilege__remove_child(''press_admin'',''press_read''); + perform acs_privilege__remove_child(''read'',''press_read''); + perform acs_privilege__remove_child(''create'',''press_create''); + perform acs_privilege__remove_child(''delete'',''press_delete''); + perform acs_privilege__remove_child(''admin'',''press_approve''); + perform acs_privilege__remove_child(''admin'',''press_admin''); + perform acs_privilege__drop_privilege(''press_admin''); + + default_context := acs__magic_object_id(''default_context''); + the_public := acs__magic_object_id(''the_public''); + + perform acs_permission__revoke_permission ( + default_context, -- object_id + the_public, -- grantee_id + ''press_read'' -- privilege + ); + + perform acs_privilege__drop_privilege(''press_read''); + perform acs_privilege__drop_privilege(''press_create''); + perform acs_privilege__drop_privilege(''press_delete''); + perform acs_privilege__drop_privilege(''press_approve''); + + return 0; +end; +' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); Index: openacs-4/packages/press/sql/postgresql/press-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/sql/postgresql/press-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/sql/postgresql/press-package-create.sql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,433 @@ +-- /packages/press/sql/press-package-create.sql +-- +-- @author sarah@arsdigita.com +-- @author stefan@arsdigita.com +-- @author ron@arsdigita.com +-- @author michael@steigman.net +-- +-- @created 2003-10-23 +-- +-- $Id: press-package-create.sql,v 1.1 2003/10/31 16:09:23 michaels Exp $ + +-- *** PACKAGE PRESS, plsql to create content_item *** + +create function press__new (varchar,varchar,varchar,timestamptz,varchar,varchar,varchar,varchar,integer,timestamptz,varchar,timestamptz,timestamptz,integer,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,integer,varchar,varchar,varchar,varchar,timestamptz,varchar,integer) +returns integer as ' +declare + p_name alias for $1; + p_publication_name alias for $2; + p_publication_link alias for $3; + p_publication_date alias for $4; + p_publication_date_desc alias for $5; + p_article_link alias for $6; + p_article_pages alias for $7; + p_article_abstract_html_p alias for $8; + p_approval_user alias for $9; + p_approval_date alias for $10; + p_approval_ip alias for $11; + p_release_date alias for $12; + p_archive_date alias for $13; + p_package_id alias for $14; + p_parent_id alias for $15; + p_item_id alias for $16; + p_locale alias for $17; + p_item_subtype alias for $18; + p_content_type alias for $19; + p_title alias for $20; + p_description alias for $21; + p_mime_type alias for $22; + p_template_id alias for $23; + p_nls_language alias for $24; + p_text alias for $25; + p_relation_tag alias for $26; + p_is_live_p alias for $27; + p_creation_date alias for $28; + p_creation_ip alias for $29; + p_creation_user alias for $30; + v_press_id integer; + v_item_id integer; + v_revision_id integer; + v_revision_log varchar; +begin + + v_revision_log := ''initial submission''; + v_item_id := content_item__new ( + p_name, -- name + p_parent_id, -- parent_id + p_item_id, -- item_id + p_locale, -- locale + current_timestamp,-- creation_date + p_creation_user, -- creation_user + null, -- context_id + p_creation_ip, -- creation_ip + p_item_subtype, -- item_subtype + p_content_type, -- content_type + null, -- title + null, -- description + p_mime_type, -- mime_type + p_nls_language, -- nls_language + null, -- data + ''text'' -- storage_type + ); + + v_revision_id := content_revision__new ( + p_title, -- title + v_revision_log, -- description + p_release_date, -- publish_date + p_mime_type, -- mime_type + p_nls_language, -- nls_language + p_description, -- content + v_item_id, -- item_id + null, -- revision_id + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + + insert into cr_press + (press_id, + package_id, + publication_name, + publication_date, + publication_date_desc, + publication_link, + article_link, + article_abstract_html_p, + article_pages, + template_id, + approval_user, + approval_date, + approval_ip, + release_date, + archive_date) + values + (v_revision_id, + p_package_id, + p_publication_name, + p_publication_date, + p_publication_date_desc, + p_publication_link, + p_article_link, + p_article_abstract_html_p, + p_article_pages, + p_template_id, + p_approval_user, + p_approval_date, + p_approval_ip, + p_release_date, + p_archive_date); + + -- make this revision live when immediately approved + + if p_is_live_p = ''t'' then + perform content_item__set_live_revision (v_revision_id); + end if; + + return v_revision_id; + +end;' language 'plpgsql'; + + -- deletes a press item along with all its revisions + +create function press__delete (integer) +returns integer as ' +declare + p_item_id alias for $1; +begin + delete + from cr_press + where press_id in (select revision_id + from cr_revisions + where item_id = p_item_id); + + perform content_item__delete(p_item_id); + + return 0; + +end;' language 'plpgsql'; + +-- make a press item permanent by nulling the archive_date +-- this only applies to the currently active revision + +create function press__make_permanent (integer) +returns integer as ' +declare + p_item_id alias for $1; +begin + update cr_press + set archive_date = null + where press_id = content_item__get_live_revision(p_item_id); + + return 0; + +end;' language 'plpgsql'; + +-- archive a press item by setting cr_press.release_date to sysdate +-- this only applies to the currently active revision + +create function press__archive (integer,timestamptz) +returns integer as ' +declare + p_item_id alias for $1; + p_archive_date alias for $2; +begin + update cr_press + set archive_date = p_archive_date + where press_id = content_item__get_live_revision(p_item_id); + + return 0; + +end;' language 'plpgsql'; + +-- approve/unapprove currently active revision + +create function press__approve (integer,varchar) +returns integer as ' +declare + p_press_id alias for $1; + p_approve_p alias for $2; + v_item_id cr_items.item_id%TYPE; +begin + + -- get item_id for revision that is being unapproved + select item_id into v_item_id + from cr_revisions + where revision_id = p_press_id; + + if p_approve_p = ''t'' then + -- approve revision + perform content_item__set_live_revision (p_revision_id); + + -- set approval_date for revision + update cr_press + set approval_date = current_timestamp + where press_id = p_press_id; + else + -- unapprove revision + -- does not mean to knock out active revision + -- perform content_item__unset_live_revision (v_item_id); + + -- null approval_date for revision + update cr_press + set approval_date = null, + release_date = null + where press_id = p_press_id; + end if; + + return 0; + +end;' language 'plpgsql'; + +create function press__approve_release (integer,timestamptz,timestamptz) +returns integer as ' +declare + p_revision_id alias for $1; + p_release_date alias for $2; + p_archive_date alias for $3; +begin + update cr_press + set release_date = p_release_date, + archive_date = p_archive_date + where press_id = p_revision_id; + + return 0; + +end;' language 'plpgsql'; + +create function press__set_active_revision (integer) +returns integer as ' +declare + p_revision_id alias for $1; + v_press_item_p char; + -- could be used to check if really a press item +begin + perform content_item__set_live_revision (p_revision_id); + + return 0; + +end;' language 'plpgsql'; + +create function press__is_live (integer) +returns varchar as ' +declare + p_press_id alias for $1; + v_item_id cr_items.item_id%TYPE; +begin + select item_id into v_item_id + from cr_revisions + where revision_id = p_press_id; + -- use get_live_revision + if content_item__get_live_revision(v_item_id) = p_press_id then + return ''t''; + else + return ''f''; + end if; + +end;' language 'plpgsql'; + +-- the status function returns information on the pulish or archive status +-- it does not make any checks on the order of release_date and archive_date + +create function press__status (integer) +returns varchar as ' +declare + p_press_id alias for $1; + v_archive_date date; + v_release_date date; +begin + -- populate variables + select archive_date, release_date + into v_archive_date, v_release_date + from cr_press + where press_id = p_press_id; + + -- if release_date is not null the item is approved, otherwise it is not + -- archive_date can be null + if v_release_date is not null then + if v_release_date > current_timestamp then + -- to be published (2 cases) + if v_archive_date is null then + return ''going live in '' + || text(round(cast(extract(days from (v_release_date - current_timestamp)) + + extract(hours from (v_release_date - current_timestamp))/24 as numeric),1)) + || '' days''; + else + return ''going live in '' + || text(round(cast(extract(days from (v_release_date - current_timestamp)) + + extract(hours from (v_release_date - current_timestamp))/24 as numeric),1)) + || '' days'' || '', archived in '' + || text(round(cast(extract(days from (v_archive_date - current_timestamp)) + + extract(hours from (v_archive_date - current_timestamp))/24 as numeric),1)) + || '' days''; + end if; + else + -- already released or even archived (3 cases) + if v_archive_date is null then + return ''live, permanent''; + else + if v_archive_date > current_timestamp then + return ''live, archived in '' + || text(round(cast(extract(days from (v_archive_date - current_timestamp)) + + extract(hours from (v_archive_date - current_timestamp))/24 as numeric),1)) + || '' days''; + else + return ''archived''; + end if; + end if; + end if; + else + return ''unapproved''; + end if; + +end;' language 'plpgsql'; + +-- *** PACKAGE PRESS_REVISION, plsql to update press items + +-- press_revision: the basic idea here is to create a new press_revision +-- in both, cr_revision and cr_press for each edit, i.e. you can't edit a press revision after +-- upload, so that all changes are audited. +-- +-- plsql to create revisions of press items + +create function press_revision__new (varchar,varchar,varchar,varchar,integer,integer,varchar,integer,varchar,varchar,timestamptz,varchar,varchar,varchar,varchar,integer,timestamptz,varchar,timestamptz,timestamptz,integer,varchar,varchar) +returns integer as ' +declare + p_title alias for $1; + p_description alias for $2; + p_mime_type alias for $3; + p_text alias for $4; + p_item_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + p_package_id alias for $8; + p_publication_name alias for $9; + p_publication_link alias for $10; + p_publication_date alias for $11; + p_publication_date_desc alias for $12; + p_article_link alias for $13; + p_article_pages alias for $14; + p_article_abstract_html_p alias for $15; + p_approval_user alias for $16; + p_approval_date alias for $17; + p_approval_ip alias for $18; + p_release_date alias for $19; + p_archive_date alias for $20; + p_template_id alias for $21; + p_make_active_revision_p alias for $22; + p_revision_note alias for $23; + v_revision_id integer; +begin + + -- create revision + v_revision_id := content_revision__new ( + p_title, -- title + p_revision_note, -- description + p_release_date, -- publish_date + p_mime_type, -- mime_type + null, -- nls_language + p_description, -- content + p_item_id, -- item_id + null, -- revision_id + current_timestamp,-- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + + -- create new press entry to go with new revision + + insert into cr_press + (press_id, + package_id, + publication_name, + publication_date, + template_id, + publication_date_desc, + publication_link, + article_link, + article_pages, + article_abstract_html_p, + approval_user, + approval_date, + approval_ip, + release_date, + archive_date) + values + (v_revision_id, + p_package_id, + p_publication_name, + p_publication_date, + p_template_id, + p_publication_date_desc, + p_publication_link, + p_article_link, + p_article_pages, + p_article_abstract_html_p, + p_approval_user, + p_approval_date, + p_approval_ip, + p_release_date, + p_archive_date); + + -- make active revision if indicated + + if p_make_active_revision_p = ''t'' then + perform press__set_active_revision(v_revision_id); + end if; + + return v_revision_id; + +end;' language 'plpgsql'; + +create function press_revision__delete (integer) +returns varchar as ' +declare + p_revision_id alias for $1; +begin + + delete from cr_press where press_id = p_revision_id; + + perform content_revision__delete(p_revision_id); + + return 0; + +end;' language 'plpgsql'; Index: openacs-4/packages/press/sql/postgresql/press-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/sql/postgresql/press-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/sql/postgresql/press-package-drop.sql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,19 @@ +-- /packages/press/sql/press-package-drop.sql +-- +-- @author michael@steigman.net +-- +-- @created 2003-10-23 +-- +-- $Id: press-package-drop.sql,v 1.1 2003/10/31 16:09:23 michaels Exp $ + +drop function press__new (varchar,varchar,varchar,timestamptz,varchar,varchar,varchar,varchar,integer,timestamptz,varchar,timestamptz,timestamptz,integer,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,integer,varchar,varchar,varchar,varchar,timestamptz,varchar,integer); +drop function press__delete (integer); +drop function press__make_permanent (integer); +drop function press__archive (integer,timestamptz); +drop function press__approve (integer,varchar); +drop function press__approve_release (integer,timestamptz,timestamptz); +drop function press__set_active_revision (integer); +drop function press__is_live (integer); +drop function press__status (integer); +drop function press_revision__new (varchar,varchar,varchar,varchar,integer,integer,varchar,integer,varchar,varchar,timestamptz,varchar,varchar,varchar,varchar,integer,timestamptz,varchar,timestamptz,timestamptz,integer,varchar,varchar); +drop function press_revision__delete (integer); Index: openacs-4/packages/press/tcl/press-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/tcl/press-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/tcl/press-procs-oracle.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,48 @@ + + + + oracle8.1.6 + + + + select sysdate from dual + + + + + + select next_day(sysdate,'Monday') from dual + + + + + + select add_months(sysdate,1) from dual + + + + + + begin + press.archive(item_id => :id, archive_date => :archive_date); + end; + + + + + + begin + press.make_permanent(item_id => :id); + end; + + + + + + begin + press.del(item_id => :id); + end; + + + + Index: openacs-4/packages/press/tcl/press-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/tcl/press-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/tcl/press-procs-postgresql.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,64 @@ + + + + postgresql7.1 + + + + select current_timestamp + + + + + + select ( + case when EXTRACT(DOW FROM TIMESTAMP 'today') = 0 + then + to_char('now'::date + '1 day'::interval,'YYYY-MM-DD HH:MM:SS') + when EXTRACT(DOW FROM TIMESTAMP 'today') = 1 + then + to_char('now'::date,'YYYY-MM-DD') + when EXTRACT(DOW FROM TIMESTAMP 'today') = 2 + then + to_char('now'::date + '6 days'::interval,'YYYY-MM-DD HH:MM:SS') + when EXTRACT(DOW FROM TIMESTAMP 'today') = 3 + then + to_char('now'::date + '5 days'::interval,'YYYY-MM-DD HH:MM:SS') + when EXTRACT(DOW FROM TIMESTAMP 'today') = 4 + then + to_char('now'::date + '4 days'::interval,'YYYY-MM-DD HH:MM:SS') + when EXTRACT(DOW FROM TIMESTAMP 'today') = 5 + then + to_char('now'::date + '3 days'::interval,'YYYY-MM-DD HH:MM:SS') + when EXTRACT(DOW FROM TIMESTAMP 'today') = 6 + then + to_char('now'::date + '2 days'::interval,'YYYY-MM-DD HH:MM:SS') + end) as next_week + + + + + + select to_char('now'::date + '1 month'::interval,'YYYY-MM-DD HH:MM:SS') + + + + + + select press__archive(:id,:archive_date) + + + + + + select press__make_permanent(:id) + + + + + + select press__delete(:id) + + + + Index: openacs-4/packages/press/tcl/press-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/tcl/press-procs.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/press/tcl/press-procs.tcl 30 Sep 2003 12:10:09 -0000 1.2 +++ openacs-4/packages/press/tcl/press-procs.tcl 31 Oct 2003 16:09:23 -0000 1.3 @@ -5,6 +5,7 @@ @author stefan@arsdigita.com @author sarah@arsdigita.com + @author michael@steigman.net @creation-date 2000-12-1 @cvs-id $Id$ @@ -21,22 +22,18 @@ switch [join $when] { "now" { - set archive_date [db_string release_now "select sysdate from dual"] + set archive_date [db_string now {}] } "next week" { - set archive_date [db_string release_now "select next_day(sysdate,'Monday') from dual"] + set archive_date [db_string next_week {}] } "next month" { - set archive_date [db_string release_now "select add_months(sysdate,1) from dual"] + set archive_date [db_string next_month {}] } } foreach id $id_list { - db_exec_plsql press_item_archive { - begin - press.archive(item_id => :id, archive_date => :archive_date); - end; - } + db_exec_plsql press_item_archive {} } } @@ -48,11 +45,7 @@ set package_id [ad_conn package_id] ad_require_permission $package_id press_admin foreach id $id_list { - db_exec_plsql press_item_make_permanent { - begin - press.make_permanent(item_id => :id); - end; - } + db_exec_plsql press_item_make_permanent {} } } @@ -63,11 +56,7 @@ set package_id [ad_conn package_id] ad_require_permission $package_id press_delete foreach id $id_list { - db_exec_plsql press_item_delete { - begin - press.del(item_id => :id); - end; - } + db_exec_plsql press_item_delete {} } } @@ -180,11 +169,7 @@ } { set template_select " + + + oracle8.1.6 + + + + begin + press.set_active_revision (:revision_id); + end; + + + + Index: openacs-4/packages/press/www/admin/one-item-revision-update-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/one-item-revision-update-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/one-item-revision-update-postgresql.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,12 @@ + + + + postgresql7.1 + + + + select press__set_active_revision (:revision_id) + + + + Index: openacs-4/packages/press/www/admin/one-item-revision-update.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/one-item-revision-update.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/press/www/admin/one-item-revision-update.tcl 18 Sep 2002 12:07:28 -0000 1.2 +++ openacs-4/packages/press/www/admin/one-item-revision-update.tcl 31 Oct 2003 16:09:23 -0000 1.3 @@ -14,10 +14,6 @@ revision_id:integer,notnull } -db_exec_plsql update_forum { - begin - press.set_active_revision (:revision_id); - end; -} +db_exec_plsql update_press_revision {} ad_returnredirect "one-item-admin?item_id=$item_id" Index: openacs-4/packages/press/www/admin/one-item-revoke-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/one-item-revoke-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/one-item-revoke-oracle.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + begin + press.approve( + press_id => :revision_id, + approve_p => :approve_p + ); + end; + + + + Index: openacs-4/packages/press/www/admin/one-item-revoke-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/one-item-revoke-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/one-item-revoke-postgresql.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,15 @@ + + + + postgresql7.1 + + + + select press__approve ( + :revision_id, -- press_id + :approve_p -- approve_p + ) + + + + Index: openacs-4/packages/press/www/admin/one-item-revoke.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/one-item-revoke.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/press/www/admin/one-item-revoke.tcl 20 Apr 2001 20:51:22 -0000 1.1 +++ openacs-4/packages/press/www/admin/one-item-revoke.tcl 31 Oct 2003 16:09:23 -0000 1.2 @@ -22,25 +22,12 @@ set package_id [ad_conn package_id] # commented out because of admin directory # ad_require_permission $package_id press_admin -set revoke_p "f" +set approve_p "f" -db_exec_plsql press_item_make_permanent { - begin - press.approve( - press_id => :revision_id, - approve_p => :revoke_p - ); - end; -} +db_exec_plsql press_item_revoke_release {} -set item_id [db_string revision_root " -select -item_id -from -cr_revisions -where revision_id = :revision_id"] +set item_id [db_string revision_root {}] - # return to one-item-admin.tcl page ad_returnredirect one-item-admin?item_id=$item_id Index: openacs-4/packages/press/www/admin/one-item-revoke.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/one-item-revoke.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/one-item-revoke.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + + select item_id + from cr_revisions + where revision_id = :revision_id + + + + Index: openacs-4/packages/press/www/admin/process.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/process.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/press/www/admin/process.tcl 5 Sep 2002 13:12:13 -0000 1.2 +++ openacs-4/packages/press/www/admin/process.tcl 31 Oct 2003 16:09:23 -0000 1.3 @@ -44,30 +44,8 @@ # template items for display -db_foreach press_items " -select item_id, - package_id, - publication_name, - publication_link, - publication_date, - publication_date_desc, - template_id, - template_adp, - article_title, - article_abstract, - article_link, - article_pages, - html_p, - release_date, - archive_date, - template_adp -from press_items_approved -where item_id in ([join $pr_items ,]) -and package_id = :package_id -order by publication_date desc" { +db_foreach press_items {} { - - press_item_format lappend templated_list "

$template_value

" Index: openacs-4/packages/press/www/admin/process.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/process.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/process.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + select item_id, + package_id, + publication_name, + publication_link, + publication_date, + publication_date_desc, + template_id, + template_adp, + article_title, + article_abstract, + article_link, + article_pages, + html_p, + release_date, + archive_date, + template_adp + from press_items_approved + where item_id in ([join $pr_items ,]) + and package_id = :package_id + order by publication_date desc + + + + Index: openacs-4/packages/press/www/admin/template-admin-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-admin-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/template-admin-oracle.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + select t.template_id, + t.template_name, + t.template_adp, + count(p.template_id) as template_usage + from press_templates t, + press_items_approved p + where t.template_id = p.template_id(+) + group by t.template_id, t.template_name, t.template_adp + order by t.template_name + + + + Index: openacs-4/packages/press/www/admin/template-admin-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-admin-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/template-admin-postgresql.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + select t.template_id, + t.template_name, + t.template_adp, + count(p.template_id) as template_usage + from press_templates t left outer join press_items_approved p using (template_id) + group by t.template_id, t.template_name, t.template_adp + order by t.template_name + + + + Index: openacs-4/packages/press/www/admin/template-admin.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-admin.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/press/www/admin/template-admin.tcl 5 Sep 2002 13:12:13 -0000 1.2 +++ openacs-4/packages/press/www/admin/template-admin.tcl 31 Oct 2003 16:09:23 -0000 1.3 @@ -23,17 +23,7 @@ # Grab the sample information -db_foreach press_templates { - select t.template_id, - t.template_name, - t.template_adp, - count(p.template_id) as template_usage - from press_templates t, - press_items_approved p - where t.template_id = p.template_id(+) - group by t.template_id, t.template_name, t.template_adp - order by t.template_name -} { +db_foreach press_templates {} { set template_item "
  • $template_name (used $template_usage time[expr {$template_usage != 1 ? "s" : ""}]) Index: openacs-4/packages/press/www/admin/template-ae-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-ae-3.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/press/www/admin/template-ae-3.tcl 18 Sep 2002 12:07:28 -0000 1.2 +++ openacs-4/packages/press/www/admin/template-ae-3.tcl 31 Oct 2003 16:09:23 -0000 1.3 @@ -20,19 +20,11 @@ switch $action { edit { - db_dml insert_template " - update press_templates - set template_name = :template_name, - template_adp = :template_adp - where template_id = :template_id" + db_dml edit_template {} } create { - db_dml insert_template " - insert into press_templates - (template_id, template_name, template_adp) - values - (acs_object_id_seq.nextval, :template_name, :template_adp)" + db_dml insert_template {} } default { Index: openacs-4/packages/press/www/admin/template-ae-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-ae-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/template-ae-3.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + + update press_templates + set template_name = :template_name, + template_adp = :template_adp + where template_id = :template_id + + + + + + insert into press_templates + (template_id, template_name, template_adp) + values + (acs_object_id_seq.nextval, :template_name, :template_adp) + + + + Index: openacs-4/packages/press/www/admin/template-delete-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-delete-2.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/packages/press/www/admin/template-delete-2.tcl 20 Apr 2001 20:51:22 -0000 1.1 +++ openacs-4/packages/press/www/admin/template-delete-2.tcl 31 Oct 2003 16:09:23 -0000 1.2 @@ -20,13 +20,8 @@ # delete template -db_dml reset_press_templates " - update cr_press - set template_id = (select template_id from press_templates where template_name='Default') - where template_id = :template_id" +db_dml reset_press_templates {} -db_dml delete_template " - delete from press_templates - where template_id = :template_id" +db_dml delete_template {} ad_returnredirect template-admin Index: openacs-4/packages/press/www/admin/template-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/template-delete-2.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,19 @@ + + + + + + update cr_press + set template_id = (select template_id from press_templates where template_name='Default') + where template_id = :template_id + + + + + + delete from press_templates + where template_id = :template_id + + + + Index: openacs-4/packages/press/www/admin/template-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-delete.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/press/www/admin/template-delete.tcl 5 Sep 2002 13:12:13 -0000 1.2 +++ openacs-4/packages/press/www/admin/template-delete.tcl 31 Oct 2003 16:09:23 -0000 1.3 @@ -27,12 +27,7 @@ set package_id [ad_conn package_id] -db_1row press_item_info { - select template_name, - template_adp - from press_templates - where template_id = :template_id -} +db_1row template_select {} # Generate the preview Index: openacs-4/packages/press/www/admin/template-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/press/www/admin/template-delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/press/www/admin/template-delete.xql 31 Oct 2003 16:09:23 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + + select template_name, template_adp + from press_templates + where template_id = :template_id + + + +