Index: openacs-4/packages/news/news.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/news.info,v diff -u -r1.16 -r1.17 --- openacs-4/packages/news/news.info 24 Feb 2005 13:33:23 -0000 1.16 +++ openacs-4/packages/news/news.info 24 Feb 2005 18:03:04 -0000 1.17 @@ -7,18 +7,18 @@ f f - + OpenACS Used for announcements and news items. 2003-11-10 OpenACS News publication tool. Publication of plain text or html news items with support for scheduled publication and commentability. - - + + - - + + @@ -35,6 +35,8 @@ + + Index: openacs-4/packages/news/catalog/news.en_US.ISO-8859-1.xml =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/catalog/news.en_US.ISO-8859-1.xml,v diff -u -r1.17 -r1.18 --- openacs-4/packages/news/catalog/news.en_US.ISO-8859-1.xml 13 Jan 2005 13:58:26 -0000 1.17 +++ openacs-4/packages/news/catalog/news.en_US.ISO-8859-1.xml 24 Feb 2005 18:03:04 -0000 1.18 @@ -34,6 +34,7 @@ HTML It will go live on It will move into the archive on + Lead Log Entry . When you're done click 'Preview' to see how the news item will appear. Index: openacs-4/packages/news/sql/oracle/news-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/news-create.sql,v diff -u -r1.9 -r1.10 --- openacs-4/packages/news/sql/oracle/news-create.sql 13 Jan 2005 13:58:26 -0000 1.9 +++ openacs-4/packages/news/sql/oracle/news-create.sql 24 Feb 2005 18:03:04 -0000 1.10 @@ -80,7 +80,7 @@ primary key, -- include package_id to provide support for multiple instances package_id integer - constraint cr_news_package_id_nn not null, + constraint cr_news_package_id_nn not null, lead varchar(1000), -- regarding news item -- *** support for dates when items are displayed or archived *** -- unarchived news items have archive_date null @@ -153,6 +153,16 @@ pretty_plural => 'Approval IPs', column_spec => 'varchar2(50)' ); +-- lead +attr_id := content_type.create_attribute ( + content_type => 'news', + attribute_name => 'lead', + datatype => 'text', + pretty_name => 'Lead', + pretty_plural => 'Leads', + column_spec => 'varchar(1000)' +); + end; / show errors @@ -186,772 +196,5 @@ show errors --- *** PACKAGE NEWS, plsql to create content_item *** -create or replace package news -as - function new ( - item_id in cr_items.item_id%TYPE default null, - -- - locale in cr_items.locale%TYPE default null, - -- - publish_date in cr_revisions.publish_date%TYPE default null, - text in varchar2 default null, - nls_language in cr_revisions.nls_language%TYPE default null, - title in cr_revisions.title%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - -- - package_id in cr_news.package_id%TYPE default null, - archive_date in cr_news.archive_date%TYPE default null, - approval_user in cr_news.approval_user%TYPE default null, - approval_date in cr_news.approval_date%TYPE default null, - approval_ip in cr_news.approval_ip%TYPE default null, - -- - relation_tag in cr_child_rels.relation_tag%TYPE - default null, - -- - item_subtype in acs_object_types.object_type%TYPE - default 'content_revision', - content_type in acs_object_types.object_type%TYPE - default 'news', - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_ip in acs_objects.creation_ip%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - -- - is_live_p in varchar2 default 'f' - ) return cr_news.news_id%TYPE; - - procedure del ( - item_id in cr_items.item_id%TYPE - ); - - procedure archive ( - item_id in cr_items.item_id%TYPE, - archive_date in cr_news.archive_date%TYPE default sysdate - ); - - procedure make_permanent ( - item_id in cr_items.item_id%TYPE - ); - - - procedure set_approve ( - revision_id in cr_revisions.revision_id%TYPE, - approve_p in varchar2 default 't', - publish_date in cr_revisions.publish_date%TYPE default null, - archive_date in cr_news.archive_date%TYPE default null, - approval_user in cr_news.approval_user%TYPE default null, - approval_date in cr_news.approval_date%TYPE default sysdate, - approval_ip in cr_news.approval_ip%TYPE default null, - live_revision_p in varchar2 default 't' - ); - - - - function status ( - publish_date in cr_revisions.publish_date%TYPE, - archive_date in cr_news.archive_date%TYPE - ) return varchar2; - - - function name ( - news_id in cr_news.news_id%TYPE - ) return varchar2; - - - -- - -- API for revisions: e.g. when the news admin wants to revise a news item - -- - function revision_new ( - item_id in cr_items.item_id%TYPE, - -- - publish_date in cr_revisions.publish_date%TYPE default null, - text in varchar2 default null, - title in cr_revisions.title%TYPE, - -- - -- here goes the revision log - description in cr_revisions.description%TYPE, - -- - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - package_id in cr_news.package_id%TYPE default null, - archive_date in cr_news.archive_date%TYPE default null, - approval_user in cr_news.approval_user%TYPE default null, - approval_date in cr_news.approval_date%TYPE default null, - approval_ip in cr_news.approval_ip%TYPE default null, - -- - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_ip in acs_objects.creation_ip%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - -- - make_active_revision_p in varchar2 default 'f' - ) return cr_revisions.revision_id%TYPE; - - - procedure revision_delete ( - revision_id in cr_revisions.revision_id%TYPE - ); - - - procedure revision_set_active ( - revision_id in cr_revisions.revision_id%TYPE - ); - - procedure clone ( - new_package_id in cr_news.package_id%TYPE default null, - old_package_id in cr_news.package_id%TYPE default null - ); - -end news; -/ -show errors - - - -create or replace package body news - as - function new ( - item_id in cr_items.item_id%TYPE default null, - -- - locale in cr_items.locale%TYPE default null, - -- - publish_date in cr_revisions.publish_date%TYPE default null, - text in varchar2 default null, - nls_language in cr_revisions.nls_language%TYPE default null, - title in cr_revisions.title%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default - 'text/plain', - -- - package_id in cr_news.package_id%TYPE default null, - archive_date in cr_news.archive_date%TYPE default null, - approval_user in cr_news.approval_user%TYPE default null, - approval_date in cr_news.approval_date%TYPE default null, - approval_ip in cr_news.approval_ip%TYPE default null, - -- - relation_tag in cr_child_rels.relation_tag%TYPE default null, - -- - item_subtype in acs_object_types.object_type%TYPE default - 'content_revision', - content_type in acs_object_types.object_type%TYPE default 'news', - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_ip in acs_objects.creation_ip%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - -- - is_live_p in varchar2 default 'f' - ) return cr_news.news_id%TYPE - is - v_news_id integer; - v_item_id integer; - v_id integer; - v_revision_id integer; - v_parent_id integer; - v_name varchar2(200); - v_log_string varchar2(400); - begin - select content_item.get_id('news') - into v_parent_id - from dual; - -- - -- this will be used for 2xClick protection - if item_id is null then - select acs_object_id_seq.nextval - into v_id - from dual; - else - v_id := item_id; - end if; - -- - select 'news' || to_char(sysdate,'YYYYMMDD') || v_id - into v_name - from dual; - -- - v_log_string := 'initial submission'; - -- - v_item_id := content_item.new( - item_id => v_id, - name => v_name, - parent_id => v_parent_id, - context_id => package_id, - locale => locale, - item_subtype => item_subtype, - content_type => content_type, - mime_type => mime_type, - nls_language => nls_language, - relation_tag => relation_tag, - creation_date => creation_date, - creation_ip => creation_ip, - creation_user => creation_user - ); - v_revision_id := content_revision.new( - title => title, - description => v_log_string, - publish_date => publish_date, - mime_type => mime_type, - nls_language => nls_language, - text => text, - item_id => v_item_id, - creation_date => creation_date, - creation_ip => creation_ip, - creation_user => creation_user - ); - insert into cr_news - (news_id, - package_id, - archive_date, - approval_user, - approval_date, - approval_ip) - values - (v_revision_id, - package_id, - archive_date, - approval_user, - approval_date, - approval_ip); - -- make this revision live when immediately approved - if is_live_p = 't' then - update - cr_items - set - live_revision = v_revision_id, - publish_status = 'ready' - where - item_id = v_item_id; - end if; - v_news_id := v_revision_id; - return v_news_id; - end new; - - - -- deletes a news item along with all its revisions and possibnle attachements - procedure del ( - item_id in cr_items.item_id%TYPE - ) is - v_item_id cr_items.item_id%TYPE; - - cursor comment_cursor IS - select message_id - from acs_messages am, acs_objects ao - where am.message_id = ao.object_id - and ao.context_id = v_item_id; - - begin - v_item_id := news.del.item_id; - dbms_output.put_line('Deleting associated comments...'); - -- delete acs_messages, images, comments to news item - for v_cm in comment_cursor loop - -- images - delete from images - where image_id in (select latest_revision - from cr_items - where parent_id = v_cm.message_id); - acs_message.del(v_cm.message_id); - delete from general_comments - where comment_id = v_cm.message_id; - end loop; - delete from cr_news - where news_id in (select revision_id - from cr_revisions - where item_id = v_item_id); - content_item.del(v_item_id); - end del; - - - -- (re)-publish a news item out of the archive by nulling the archive_date - -- this only applies to the currently active revision - procedure make_permanent ( - item_id in cr_items.item_id%TYPE - ) - is - begin - update cr_news - set archive_date = null - where news_id = content_item.get_live_revision(news.make_permanent.item_id); - end make_permanent; - - - -- archive a news item - -- this only applies to the currently active revision - procedure archive ( - item_id in cr_items.item_id%TYPE, - archive_date in cr_news.archive_date%TYPE default sysdate - ) - is - begin - update cr_news - set archive_date = news.archive.archive_date - where news_id = content_item.get_live_revision(news.archive.item_id); - end archive; - - - -- approve/unapprove a specific revision - -- approving a revision makes it also the active revision - procedure set_approve( - revision_id in cr_revisions.revision_id%TYPE, - approve_p in varchar2 default 't', - publish_date in cr_revisions.publish_date%TYPE default null, - archive_date in cr_news.archive_date%TYPE default null, - approval_user in cr_news.approval_user%TYPE default null, - approval_date in cr_news.approval_date%TYPE default sysdate, - approval_ip in cr_news.approval_ip%TYPE default null, - live_revision_p in varchar2 default 't' - ) - is - v_item_id cr_items.item_id%TYPE; - begin - select item_id into v_item_id - from cr_revisions - where revision_id = news.set_approve.revision_id; - -- unapprove an revision (does not mean to knock out active revision) - if news.set_approve.approve_p = 'f' then - update cr_news - set approval_date = null, - approval_user = null, - approval_ip = null, - archive_date = null - where news_id = news.set_approve.revision_id; - -- - update cr_revisions - set publish_date = null - where revision_id = news.set_approve.revision_id; - else - -- approve a revision - update cr_revisions - set publish_date = news.set_approve.publish_date - where revision_id = news.set_approve.revision_id; - -- - update cr_news - set archive_date = news.set_approve.archive_date, - approval_date = news.set_approve.approval_date, - approval_user = news.set_approve.approval_user, - approval_ip = news.set_approve.approval_ip - where news_id = news.set_approve.revision_id; - -- - -- cannot use content_item.set_live_revision because it sets publish_date to sysdate - if news.set_approve.live_revision_p = 't' then - update cr_items - set live_revision = news.set_approve.revision_id, - publish_status = 'ready' - where item_id = v_item_id; - end if; - -- - end if; - end set_approve; - - - - -- the status function returns information on the puplish or archive status - -- it does not make any checks on the order of publish_date and archive_date - function status ( - publish_date in cr_revisions.publish_date%TYPE, - archive_date in cr_news.archive_date%TYPE - ) return varchar2 - is - begin - - if publish_date is not null then - if publish_date > sysdate then - -- to be published (2 cases) - if archive_date is null then - return 'going_live_no_archive'; - else - return 'going_live_with_archive'; - end if; - else - -- already released or even archived (3 cases) - if archive_date is null then - return 'published_no_archive'; - else - if archive_date - sysdate > 0 then - return 'published_with_archive'; - else - return 'archived'; - end if; - end if; - end if; - else - return 'unapproved'; - end if; - end status; - - - function name ( - news_id in cr_news.news_id%TYPE - ) return varchar2 - is - news_title varchar2(1000); - begin - select title - into news_title - from cr_revisions - where revision_id = news.name.news_id; - - return news_title; - end name; - - - -- - -- API for Revision management - -- - function revision_new ( - item_id in cr_items.item_id%TYPE, - -- - publish_date in cr_revisions.publish_date%TYPE default null, - text in varchar2 default null, - title in cr_revisions.title%TYPE, - -- - -- here goes the revision log - description in cr_revisions.description%TYPE, - -- - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - package_id in cr_news.package_id%TYPE default null, - archive_date in cr_news.archive_date%TYPE default null, - approval_user in cr_news.approval_user%TYPE default null, - approval_date in cr_news.approval_date%TYPE default null, - approval_ip in cr_news.approval_ip%TYPE default null, - -- - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_ip in acs_objects.creation_ip%TYPE default null, - creation_user in acs_objects.creation_user%TYPE default null, - -- - make_active_revision_p in varchar2 default 'f' - ) return cr_revisions.revision_id%TYPE - is - v_revision_id integer; - begin - -- create revision - v_revision_id := content_revision.new( - title => title, - description => description, - publish_date => publish_date, - mime_type => mime_type, - text => text, - item_id => item_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip - ); - -- create new news entry with new revision - insert into cr_news - (news_id, - package_id, - archive_date, - approval_user, - approval_date, - approval_ip) - values - (v_revision_id, - package_id, - archive_date, - approval_user, - approval_date, - approval_ip); - -- make active revision if indicated - if make_active_revision_p = 't' then - news.revision_set_active(v_revision_id); - end if; - return v_revision_id; - end revision_new; - - - - procedure revision_set_active ( - revision_id in cr_revisions.revision_id%TYPE - ) - is - v_news_item_p char; - -- could be used to check if really a 'news' item - begin - update - cr_items - set - live_revision = news.revision_set_active.revision_id, - publish_status = 'ready' - where - item_id = (select - item_id - from - cr_revisions - where - revision_id = news.revision_set_active.revision_id); - end revision_set_active; - - - procedure clone ( - new_package_id in cr_news.package_id%TYPE default null, - old_package_id in cr_news.package_id%TYPE default null - ) - is - new_news_id integer; - begin - for one_news in (select - publish_date, - content.blob_to_string(cr.content) as text, - cr.nls_language, - cr.title as title, - cr.mime_type, - cn.package_id, - archive_date, - approval_user, - approval_date, - approval_ip, - ao.creation_date, - ao.creation_ip, - ao.creation_user - from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao - where - (ci.item_id = cr.item_id - and ci.live_revision = cr.revision_id - and cr.revision_id = cn.news_id - and cr.revision_id = ao.object_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 = cn.news_id - and cr.revision_id = ao.object_id)) - loop - - new_news_id := news.new( - publish_date => one_news.publish_date, - text => one_news.text, - nls_language => one_news.nls_language, - title => one_news.title, - mime_type => one_news.mime_type, - package_id => news.clone.new_package_id, - archive_date => one_news.archive_date, - approval_user => one_news.approval_user, - approval_date => one_news.approval_date, - approval_ip => one_news.approval_ip, - creation_date => one_news.creation_date, - creation_ip => one_news.creation_ip, - creation_user => one_news.creation_user - ); - - end loop; - end clone; - - -- currently not used, because we want to audit revisions - procedure revision_delete ( - revision_id in cr_revisions.revision_id%TYPE - ) - is - begin - -- delete from cr_news table - delete from cr_news - where news_id = news.revision_delete.revision_id; - -- delete revision - content_revision.del( - revision_id => news.revision_delete.revision_id - ); - end revision_delete; - -end news; -/ -show errors - - - - --- --- views on 'news' application that pick from cr_news, cr_items, cr_revisions --- Re-arrange 'joins' for performance tuning --- - --- Views on multiple items - --- View on all released news items in its active revision -create or replace view news_items_approved -as -select - ci.item_id as item_id, - cn.package_id, - cr.title as publish_title, - content.blob_to_string(cr.content) as publish_body, - cr.content as content, - decode(cr.mime_type, 'text/html','t','f') as html_p, - to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date, - cr.publish_date, - ao.creation_user, - ps.first_names || ' ' || ps.last_name as item_creator, - cn.archive_date -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - ci.item_id = cr.item_id -and ci.live_revision = cr.revision_id -and cr.revision_id = cn.news_id -and cr.revision_id = ao.object_id -and ao.creation_user = ps.person_id; - - --- View of all news items in the system -create or replace view news_items_live_or_submitted -as -select - ci.item_id as item_id, - cn.news_id, - cn.package_id, - cr.publish_date, - cn.archive_date, - cr.title as publish_title, - content.blob_to_string(cr.content) as publish_body, - decode(cr.mime_type, 'text/html','t','f') as html_p, - ao.creation_user, - ps.first_names || ' ' || ps.last_name as item_creator, - ao.creation_date, - ci.live_revision, - news.status(cr.publish_date, cn.archive_date) as status -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - (ci.item_id = cr.item_id - and ci.live_revision = cr.revision_id - and cr.revision_id = cn.news_id - and cr.revision_id = ao.object_id - and ao.creation_user = ps.person_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 = cn.news_id - and cr.revision_id = ao.object_id - and ao.creation_user = ps.person_id); - - --- View of unapproved items -create or replace view news_items_unapproved -as -select - ci.item_id as item_id, - cr.title as publish_title, - cn.package_id as package_id, - ao.creation_date as creation_date, - ps.first_names || ' ' || ps.last_name as item_creator -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - cr.revision_id = ao.object_id -and ao.creation_user = ps.person_id -and cr.revision_id = content_item.get_live_revision(ci.item_id) -and cr.revision_id = cn.news_id -and cr.item_id = ci.item_id -and cr.publish_date is null; - - - --- One News Item Views --- - --- View of all revisions of a news item -create or replace view news_item_revisions -as -select - cr.item_id as item_id, - cr.revision_id, - ci.live_revision, - cr.title as publish_title, - content.blob_to_string(cr.content) as publish_body, - cr.publish_date, - cn.archive_date, - cr.description as log_entry, - decode(cr.mime_type,'text/html','t','f') as html_p, - cr.mime_type as mime_type, - cn.package_id, - ao.creation_date as creation_date, - news.status(cr.publish_date, cn.archive_date) as status, - case when exists (select 1 - from cr_revisions cr2 - where cr2.revision_id = cn.news_id - and cr2.publish_date is null - ) then 1 else 0 end - as - approval_needed_p, - ps.first_names || ' ' || ps.last_name as item_creator, - ao.creation_user, - ao.creation_ip, - ci.name as item_name -from - cr_revisions cr, - cr_news cn, - cr_items ci, - acs_objects ao, - persons ps -where - cr.revision_id = ao.object_id -and cr.revision_id = cn.news_id -and ci.item_id = cr.item_id -and ao.creation_user = ps.person_id; - - - --- View of a submitted news item or active revision in unapproved state -create or replace view news_item_unapproved -as -select - cr.revision_id, - ci.name as item_name, - ps.first_names || ' ' || ps.last_name as item_creator, - ao.creation_ip as item_creation_ip, - ao.creation_date -from - cr_revisions cr, - cr_items ci, - acs_objects ao, - persons ps -where - ci.item_id = cr.item_id -and cr.revision_id = ao.object_id -and ao.creation_user = ps.person_id; - - - --- View of a news item as of its active revision -create or replace view news_item_full_active -as -select - ci.item_id as item_id, - cn.package_id as package_id, - revision_id, - cr.title as publish_title, - content.blob_to_string(cr.content) as publish_body, - decode(cr.mime_type,'text/html','t','f') as html_p, - cr.publish_date, - cn.archive_date, - news.status(cr.publish_date, cn.archive_date) as status, - ci.name as item_name, - ps.person_id as creator_id, - ps.first_names || ' ' || ps.last_name as item_creator -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - cr.item_id = ci.item_id -and (cr.revision_id = ci.live_revision - or (ci.live_revision is null - and cr.revision_id = content_item.get_latest_revision(ci.item_id))) -and cr.revision_id = cn.news_id -and ci.item_id = ao.object_id -and ao.creation_user = ps.person_id; - - --- plsql to create keywords for news items --- no additional code necessary for news items right now. - --- plsql for searches: will be covered by site-wide search --- no additional code necessary for news items right now. +@@ news-package-create.sql +@@ news-views-create.sql Index: openacs-4/packages/news/sql/oracle/news-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/news-drop.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/news/sql/oracle/news-drop.sql 30 Sep 2003 12:10:08 -0000 1.2 +++ openacs-4/packages/news/sql/oracle/news-drop.sql 24 Feb 2005 18:03:04 -0000 1.3 @@ -54,22 +54,11 @@ -drop package news; - -- delete news views +@@ drop-news-view.sql -drop view news_items_approved; +drop package news; -drop view news_items_live_or_submitted; - -drop view news_items_unapproved; - -drop view news_item_revisions; - -drop view news_item_unapproved; - -drop view news_item_full_active; - -- drop indices to avoid lock situation through parent table drop index cr_news_appuser_id_fk; Index: openacs-4/packages/news/sql/oracle/news-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/news-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/oracle/news-package-create.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,578 @@ +-- *** PACKAGE NEWS, plsql to create content_item *** +create or replace package news +as + function new ( + item_id in cr_items.item_id%TYPE default null, + -- + locale in cr_items.locale%TYPE default null, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + nls_language in cr_revisions.nls_language%TYPE default null, + title in cr_revisions.title%TYPE default null, + lead in cr_news.lead%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + -- + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + relation_tag in cr_child_rels.relation_tag%TYPE + default null, + -- + item_subtype in acs_object_types.object_type%TYPE + default 'content_revision', + content_type in acs_object_types.object_type%TYPE + default 'news', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + is_live_p in varchar2 default 'f' + ) return cr_news.news_id%TYPE; + + procedure del ( + item_id in cr_items.item_id%TYPE + ); + + procedure archive ( + item_id in cr_items.item_id%TYPE, + archive_date in cr_news.archive_date%TYPE default sysdate + ); + + procedure make_permanent ( + item_id in cr_items.item_id%TYPE + ); + + + procedure set_approve ( + revision_id in cr_revisions.revision_id%TYPE, + approve_p in varchar2 default 't', + publish_date in cr_revisions.publish_date%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default sysdate, + approval_ip in cr_news.approval_ip%TYPE default null, + live_revision_p in varchar2 default 't' + ); + + + + function status ( + publish_date in cr_revisions.publish_date%TYPE, + archive_date in cr_news.archive_date%TYPE + ) return varchar2; + + + function name ( + news_id in cr_news.news_id%TYPE + ) return varchar2; + + + -- + -- API for revisions: e.g. when the news admin wants to revise a news item + -- + function revision_new ( + item_id in cr_items.item_id%TYPE, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + title in cr_revisions.title%TYPE, + lead in cr_news.lead%TYPE default null, + -- + -- here goes the revision log + description in cr_revisions.description%TYPE, + -- + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + make_active_revision_p in varchar2 default 'f' + ) return cr_revisions.revision_id%TYPE; + + + procedure revision_delete ( + revision_id in cr_revisions.revision_id%TYPE + ); + + + procedure revision_set_active ( + revision_id in cr_revisions.revision_id%TYPE + ); + + procedure clone ( + new_package_id in cr_news.package_id%TYPE default null, + old_package_id in cr_news.package_id%TYPE default null + ); + +end news; +/ +show errors + + + +create or replace package body news + as + function new ( + item_id in cr_items.item_id%TYPE default null, + -- + locale in cr_items.locale%TYPE default null, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + nls_language in cr_revisions.nls_language%TYPE default null, + title in cr_revisions.title%TYPE default null, + lead in cr_news.lead%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default + 'text/plain', + -- + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + relation_tag in cr_child_rels.relation_tag%TYPE default null, + -- + item_subtype in acs_object_types.object_type%TYPE default + 'content_revision', + content_type in acs_object_types.object_type%TYPE default 'news', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + is_live_p in varchar2 default 'f' + ) return cr_news.news_id%TYPE + is + v_news_id integer; + v_item_id integer; + v_id integer; + v_revision_id integer; + v_parent_id integer; + v_name varchar2(200); + v_log_string varchar2(400); + begin + select content_item.get_id('news') + into v_parent_id + from dual; + -- + -- this will be used for 2xClick protection + if item_id is null then + select acs_object_id_seq.nextval + into v_id + from dual; + else + v_id := item_id; + end if; + -- + select 'news' || to_char(sysdate,'YYYYMMDD') || v_id + into v_name + from dual; + -- + v_log_string := 'initial submission'; + -- + v_item_id := content_item.new( + item_id => v_id, + name => v_name, + parent_id => v_parent_id, + context_id => package_id, + locale => locale, + item_subtype => item_subtype, + content_type => content_type, + mime_type => mime_type, + nls_language => nls_language, + relation_tag => relation_tag, + creation_date => creation_date, + creation_ip => creation_ip, + creation_user => creation_user + ); + v_revision_id := content_revision.new( + title => title, + description => v_log_string, + publish_date => publish_date, + mime_type => mime_type, + nls_language => nls_language, + text => text, + item_id => v_item_id, + creation_date => creation_date, + creation_ip => creation_ip, + creation_user => creation_user + ); + insert into cr_news + (news_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip); + -- make this revision live when immediately approved + if is_live_p = 't' then + update + cr_items + set + live_revision = v_revision_id, + publish_status = 'ready' + where + item_id = v_item_id; + end if; + v_news_id := v_revision_id; + return v_news_id; + end new; + + + -- deletes a news item along with all its revisions and possibnle attachements + procedure del ( + item_id in cr_items.item_id%TYPE + ) is + v_item_id cr_items.item_id%TYPE; + + cursor comment_cursor IS + select message_id + from acs_messages am, acs_objects ao + where am.message_id = ao.object_id + and ao.context_id = v_item_id; + + begin + v_item_id := news.del.item_id; + dbms_output.put_line('Deleting associated comments...'); + -- delete acs_messages, images, comments to news item + for v_cm in comment_cursor loop + -- images + delete from images + where image_id in (select latest_revision + from cr_items + where parent_id = v_cm.message_id); + acs_message.del(v_cm.message_id); + delete from general_comments + where comment_id = v_cm.message_id; + end loop; + delete from cr_news + where news_id in (select revision_id + from cr_revisions + where item_id = v_item_id); + content_item.del(v_item_id); + end del; + + + -- (re)-publish a news item out of the archive by nulling the archive_date + -- this only applies to the currently active revision + procedure make_permanent ( + item_id in cr_items.item_id%TYPE + ) + is + begin + update cr_news + set archive_date = null + where news_id = content_item.get_live_revision(news.make_permanent.item_id); + end make_permanent; + + + -- archive a news item + -- this only applies to the currently active revision + procedure archive ( + item_id in cr_items.item_id%TYPE, + archive_date in cr_news.archive_date%TYPE default sysdate + ) + is + begin + update cr_news + set archive_date = news.archive.archive_date + where news_id = content_item.get_live_revision(news.archive.item_id); + end archive; + + + -- approve/unapprove a specific revision + -- approving a revision makes it also the active revision + procedure set_approve( + revision_id in cr_revisions.revision_id%TYPE, + approve_p in varchar2 default 't', + publish_date in cr_revisions.publish_date%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default sysdate, + approval_ip in cr_news.approval_ip%TYPE default null, + live_revision_p in varchar2 default 't' + ) + is + v_item_id cr_items.item_id%TYPE; + begin + select item_id into v_item_id + from cr_revisions + where revision_id = news.set_approve.revision_id; + -- unapprove an revision (does not mean to knock out active revision) + if news.set_approve.approve_p = 'f' then + update cr_news + set approval_date = null, + approval_user = null, + approval_ip = null, + archive_date = null + where news_id = news.set_approve.revision_id; + -- + update cr_revisions + set publish_date = null + where revision_id = news.set_approve.revision_id; + else + -- approve a revision + update cr_revisions + set publish_date = news.set_approve.publish_date + where revision_id = news.set_approve.revision_id; + -- + update cr_news + set archive_date = news.set_approve.archive_date, + approval_date = news.set_approve.approval_date, + approval_user = news.set_approve.approval_user, + approval_ip = news.set_approve.approval_ip + where news_id = news.set_approve.revision_id; + -- + -- cannot use content_item.set_live_revision because it sets publish_date to sysdate + if news.set_approve.live_revision_p = 't' then + update cr_items + set live_revision = news.set_approve.revision_id, + publish_status = 'ready' + where item_id = v_item_id; + end if; + -- + end if; + end set_approve; + + + + -- the status function returns information on the puplish or archive status + -- it does not make any checks on the order of publish_date and archive_date + function status ( + publish_date in cr_revisions.publish_date%TYPE, + archive_date in cr_news.archive_date%TYPE + ) return varchar2 + is + begin + + if publish_date is not null then + if publish_date > sysdate then + -- to be published (2 cases) + if archive_date is null then + return 'going_live_no_archive'; + else + return 'going_live_with_archive'; + end if; + else + -- already released or even archived (3 cases) + if archive_date is null then + return 'published_no_archive'; + else + if archive_date - sysdate > 0 then + return 'published_with_archive'; + else + return 'archived'; + end if; + end if; + end if; + else + return 'unapproved'; + end if; + end status; + + + function name ( + news_id in cr_news.news_id%TYPE + ) return varchar2 + is + news_title varchar2(1000); + begin + select title + into news_title + from cr_revisions + where revision_id = news.name.news_id; + + return news_title; + end name; + + + -- + -- API for Revision management + -- + function revision_new ( + item_id in cr_items.item_id%TYPE, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + title in cr_revisions.title%TYPE, + lead in cr_news.lead%TYPE default null, + -- + -- here goes the revision log + description in cr_revisions.description%TYPE, + -- + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + make_active_revision_p in varchar2 default 'f' + ) return cr_revisions.revision_id%TYPE + is + v_revision_id integer; + begin + -- create revision + v_revision_id := content_revision.new( + title => title, + description => description, + publish_date => publish_date, + mime_type => mime_type, + text => text, + item_id => item_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + -- create new news entry with new revision + insert into cr_news + (news_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip); + -- make active revision if indicated + if make_active_revision_p = 't' then + news.revision_set_active(v_revision_id); + end if; + return v_revision_id; + end revision_new; + + + + procedure revision_set_active ( + revision_id in cr_revisions.revision_id%TYPE + ) + is + v_news_item_p char; + -- could be used to check if really a 'news' item + begin + update + cr_items + set + live_revision = news.revision_set_active.revision_id, + publish_status = 'ready' + where + item_id = (select + item_id + from + cr_revisions + where + revision_id = news.revision_set_active.revision_id); + end revision_set_active; + + + procedure clone ( + new_package_id in cr_news.package_id%TYPE default null, + old_package_id in cr_news.package_id%TYPE default null + ) + is + new_news_id integer; + begin + for one_news in (select + publish_date, + content.blob_to_string(cr.content) as text, + cr.nls_language, + cr.title as title, + cn.lead as lead, + cr.mime_type, + cn.package_id, + archive_date, + approval_user, + approval_date, + approval_ip, + ao.creation_date, + ao.creation_ip, + ao.creation_user + from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao + where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_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 = cn.news_id + and cr.revision_id = ao.object_id)) + loop + + new_news_id := news.new( + publish_date => one_news.publish_date, + text => one_news.text, + nls_language => one_news.nls_language, + title => one_news.title, + lead => one_news.lead, + mime_type => one_news.mime_type, + package_id => news.clone.new_package_id, + archive_date => one_news.archive_date, + approval_user => one_news.approval_user, + approval_date => one_news.approval_date, + approval_ip => one_news.approval_ip, + creation_date => one_news.creation_date, + creation_ip => one_news.creation_ip, + creation_user => one_news.creation_user + ); + + end loop; + end clone; + + -- currently not used, because we want to audit revisions + procedure revision_delete ( + revision_id in cr_revisions.revision_id%TYPE + ) + is + begin + -- delete from cr_news table + delete from cr_news + where news_id = news.revision_delete.revision_id; + -- delete revision + content_revision.del( + revision_id => news.revision_delete.revision_id + ); + end revision_delete; + +end news; +/ +show errors + + + + Index: openacs-4/packages/news/sql/oracle/news-views-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/news-views-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/oracle/news-views-create.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,206 @@ +-- +-- views on 'news' application that pick from cr_news, cr_items, cr_revisions +-- Re-arrange 'joins' for performance tuning +-- + +-- Views on multiple items + +-- View on all released news items in its active revision +create or replace view news_items_approved +as +select + ci.item_id as item_id, + cn.package_id, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + cr.content as content, + decode(cr.mime_type, 'text/html','t','f') as html_p, + to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date, + cr.publish_date, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + cn.archive_date +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and ci.live_revision = cr.revision_id +and cr.revision_id = cn.news_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- View of all news items in the system +create or replace view news_items_live_or_submitted +as +select + ci.item_id as item_id, + cn.news_id, + cn.package_id, + cr.publish_date, + cn.archive_date, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + decode(cr.mime_type, 'text/html','t','f') as html_p, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_date, + ci.live_revision, + news.status(cr.publish_date, cn.archive_date) as status +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_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 = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_id); + + +-- View of unapproved items +create or replace view news_items_unapproved +as +select + ci.item_id as item_id, + cr.title as publish_title, + cn.lead as publish_lead, + cn.package_id as package_id, + ao.creation_date as creation_date, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id +and cr.revision_id = content_item.get_live_revision(ci.item_id) +and cr.revision_id = cn.news_id +and cr.item_id = ci.item_id +and cr.publish_date is null; + + + +-- One News Item Views +-- + +-- View of all revisions of a news item +create or replace view news_item_revisions +as +select + cr.item_id as item_id, + cr.revision_id, + ci.live_revision, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + cr.publish_date, + cn.archive_date, + cr.description as log_entry, + decode(cr.mime_type,'text/html','t','f') as html_p, + cr.mime_type as mime_type, + cn.package_id, + ao.creation_date as creation_date, + news.status(cr.publish_date, cn.archive_date) as status, + case when exists (select 1 + from cr_revisions cr2 + where cr2.revision_id = cn.news_id + and cr2.publish_date is null + ) then 1 else 0 end + as + approval_needed_p, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_user, + ao.creation_ip, + ci.name as item_name +from + cr_revisions cr, + cr_news cn, + cr_items ci, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and cr.revision_id = cn.news_id +and ci.item_id = cr.item_id +and ao.creation_user = ps.person_id; + + + +-- View of a submitted news item or active revision in unapproved state +create or replace view news_item_unapproved +as +select + cr.revision_id, + ci.name as item_name, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_ip as item_creation_ip, + ao.creation_date +from + cr_revisions cr, + cr_items ci, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + + +-- View of a news item as of its active revision +create or replace view news_item_full_active +as +select + ci.item_id as item_id, + cn.package_id as package_id, + revision_id, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + decode(cr.mime_type,'text/html','t','f') as html_p, + cr.publish_date, + cn.archive_date, + news.status(cr.publish_date, cn.archive_date) as status, + ci.name as item_name, + ps.person_id as creator_id, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.item_id = ci.item_id +and (cr.revision_id = ci.live_revision + or (ci.live_revision is null + and cr.revision_id = content_item.get_latest_revision(ci.item_id))) +and cr.revision_id = cn.news_id +and ci.item_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- plsql to create keywords for news items +-- no additional code necessary for news items right now. + +-- plsql for searches: will be covered by site-wide search +-- no additional code necessary for news items right now. Index: openacs-4/packages/news/sql/oracle/news-views-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/news-views-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/oracle/news-views-drop.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,8 @@ +-- Drop all the views in reverse order of create. +-- +drop view news_item_full_active; +drop view news_item_unapproved; +drop view news_item_revisions; +drop view news_items_unapproved; +drop view news_items_live_or_submitted; +drop view news_items_approved; Index: openacs-4/packages/news/sql/oracle/upgrade/upgrade-5.2.0d1-5.2.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/upgrade/upgrade-5.2.0d1-5.2.0d2.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/oracle/upgrade/upgrade-5.2.0d1-5.2.0d2.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,796 @@ +ALTER TABLE cr_news ADD COLUMN lead varchar(4000); + +declare + attr_id acs_attributes.attribute_id%TYPE; +begin +-- create attributes for lead +attr_id := content_type.create_attribute ( + content_type => 'news', + attribute_name => 'lead', + datatype => 'text', + pretty_name => 'Lead', + pretty_plural => 'Leads', + column_spec => 'varchar(1000)' +); +end; +/ +show errors + +create or replace package news +as + function new ( + item_id in cr_items.item_id%TYPE default null, + -- + locale in cr_items.locale%TYPE default null, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + nls_language in cr_revisions.nls_language%TYPE default null, + title in cr_revisions.title%TYPE default null, + lead in cr_news.lead%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + -- + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + relation_tag in cr_child_rels.relation_tag%TYPE + default null, + -- + item_subtype in acs_object_types.object_type%TYPE + default 'content_revision', + content_type in acs_object_types.object_type%TYPE + default 'news', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + is_live_p in varchar2 default 'f' + ) return cr_news.news_id%TYPE; + + procedure del ( + item_id in cr_items.item_id%TYPE + ); + + procedure archive ( + item_id in cr_items.item_id%TYPE, + archive_date in cr_news.archive_date%TYPE default sysdate + ); + + procedure make_permanent ( + item_id in cr_items.item_id%TYPE + ); + + + procedure set_approve ( + revision_id in cr_revisions.revision_id%TYPE, + approve_p in varchar2 default 't', + publish_date in cr_revisions.publish_date%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default sysdate, + approval_ip in cr_news.approval_ip%TYPE default null, + live_revision_p in varchar2 default 't' + ); + + + + function status ( + publish_date in cr_revisions.publish_date%TYPE, + archive_date in cr_news.archive_date%TYPE + ) return varchar2; + + + function name ( + news_id in cr_news.news_id%TYPE + ) return varchar2; + + + -- + -- API for revisions: e.g. when the news admin wants to revise a news item + -- + function revision_new ( + item_id in cr_items.item_id%TYPE, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + title in cr_revisions.title%TYPE, + lead in cr_news.lead%TYPE default null, + -- + -- here goes the revision log + description in cr_revisions.description%TYPE, + -- + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + make_active_revision_p in varchar2 default 'f' + ) return cr_revisions.revision_id%TYPE; + + + procedure revision_delete ( + revision_id in cr_revisions.revision_id%TYPE + ); + + + procedure revision_set_active ( + revision_id in cr_revisions.revision_id%TYPE + ); + + procedure clone ( + new_package_id in cr_news.package_id%TYPE default null, + old_package_id in cr_news.package_id%TYPE default null + ); + +end news; +/ +show errors + + + +create or replace package body news + as + function new ( + item_id in cr_items.item_id%TYPE default null, + -- + locale in cr_items.locale%TYPE default null, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + nls_language in cr_revisions.nls_language%TYPE default null, + title in cr_revisions.title%TYPE default null, + lead in cr_news.lead%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default + 'text/plain', + -- + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + relation_tag in cr_child_rels.relation_tag%TYPE default null, + -- + item_subtype in acs_object_types.object_type%TYPE default + 'content_revision', + content_type in acs_object_types.object_type%TYPE default 'news', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + is_live_p in varchar2 default 'f' + ) return cr_news.news_id%TYPE + is + v_news_id integer; + v_item_id integer; + v_id integer; + v_revision_id integer; + v_parent_id integer; + v_name varchar2(200); + v_log_string varchar2(400); + begin + select content_item.get_id('news') + into v_parent_id + from dual; + -- + -- this will be used for 2xClick protection + if item_id is null then + select acs_object_id_seq.nextval + into v_id + from dual; + else + v_id := item_id; + end if; + -- + select 'news' || to_char(sysdate,'YYYYMMDD') || v_id + into v_name + from dual; + -- + v_log_string := 'initial submission'; + -- + v_item_id := content_item.new( + item_id => v_id, + name => v_name, + parent_id => v_parent_id, + context_id => package_id, + locale => locale, + item_subtype => item_subtype, + content_type => content_type, + mime_type => mime_type, + nls_language => nls_language, + relation_tag => relation_tag, + creation_date => creation_date, + creation_ip => creation_ip, + creation_user => creation_user + ); + v_revision_id := content_revision.new( + title => title, + description => v_log_string, + publish_date => publish_date, + mime_type => mime_type, + nls_language => nls_language, + text => text, + item_id => v_item_id, + creation_date => creation_date, + creation_ip => creation_ip, + creation_user => creation_user + ); + insert into cr_news + (news_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip); + -- make this revision live when immediately approved + if is_live_p = 't' then + update + cr_items + set + live_revision = v_revision_id, + publish_status = 'ready' + where + item_id = v_item_id; + end if; + v_news_id := v_revision_id; + return v_news_id; + end new; + + + -- deletes a news item along with all its revisions and possibnle attachements + procedure del ( + item_id in cr_items.item_id%TYPE + ) is + v_item_id cr_items.item_id%TYPE; + + cursor comment_cursor IS + select message_id + from acs_messages am, acs_objects ao + where am.message_id = ao.object_id + and ao.context_id = v_item_id; + + begin + v_item_id := news.del.item_id; + dbms_output.put_line('Deleting associated comments...'); + -- delete acs_messages, images, comments to news item + for v_cm in comment_cursor loop + -- images + delete from images + where image_id in (select latest_revision + from cr_items + where parent_id = v_cm.message_id); + acs_message.del(v_cm.message_id); + delete from general_comments + where comment_id = v_cm.message_id; + end loop; + delete from cr_news + where news_id in (select revision_id + from cr_revisions + where item_id = v_item_id); + content_item.del(v_item_id); + end del; + + + -- (re)-publish a news item out of the archive by nulling the archive_date + -- this only applies to the currently active revision + procedure make_permanent ( + item_id in cr_items.item_id%TYPE + ) + is + begin + update cr_news + set archive_date = null + where news_id = content_item.get_live_revision(news.make_permanent.item_id); + end make_permanent; + + + -- archive a news item + -- this only applies to the currently active revision + procedure archive ( + item_id in cr_items.item_id%TYPE, + archive_date in cr_news.archive_date%TYPE default sysdate + ) + is + begin + update cr_news + set archive_date = news.archive.archive_date + where news_id = content_item.get_live_revision(news.archive.item_id); + end archive; + + + -- approve/unapprove a specific revision + -- approving a revision makes it also the active revision + procedure set_approve( + revision_id in cr_revisions.revision_id%TYPE, + approve_p in varchar2 default 't', + publish_date in cr_revisions.publish_date%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default sysdate, + approval_ip in cr_news.approval_ip%TYPE default null, + live_revision_p in varchar2 default 't' + ) + is + v_item_id cr_items.item_id%TYPE; + begin + select item_id into v_item_id + from cr_revisions + where revision_id = news.set_approve.revision_id; + -- unapprove an revision (does not mean to knock out active revision) + if news.set_approve.approve_p = 'f' then + update cr_news + set approval_date = null, + approval_user = null, + approval_ip = null, + archive_date = null + where news_id = news.set_approve.revision_id; + -- + update cr_revisions + set publish_date = null + where revision_id = news.set_approve.revision_id; + else + -- approve a revision + update cr_revisions + set publish_date = news.set_approve.publish_date + where revision_id = news.set_approve.revision_id; + -- + update cr_news + set archive_date = news.set_approve.archive_date, + approval_date = news.set_approve.approval_date, + approval_user = news.set_approve.approval_user, + approval_ip = news.set_approve.approval_ip + where news_id = news.set_approve.revision_id; + -- + -- cannot use content_item.set_live_revision because it sets publish_date to sysdate + if news.set_approve.live_revision_p = 't' then + update cr_items + set live_revision = news.set_approve.revision_id, + publish_status = 'ready' + where item_id = v_item_id; + end if; + -- + end if; + end set_approve; + + + + -- the status function returns information on the puplish or archive status + -- it does not make any checks on the order of publish_date and archive_date + function status ( + publish_date in cr_revisions.publish_date%TYPE, + archive_date in cr_news.archive_date%TYPE + ) return varchar2 + is + begin + + if publish_date is not null then + if publish_date > sysdate then + -- to be published (2 cases) + if archive_date is null then + return 'going_live_no_archive'; + else + return 'going_live_with_archive'; + end if; + else + -- already released or even archived (3 cases) + if archive_date is null then + return 'published_no_archive'; + else + if archive_date - sysdate > 0 then + return 'published_with_archive'; + else + return 'archived'; + end if; + end if; + end if; + else + return 'unapproved'; + end if; + end status; + + + function name ( + news_id in cr_news.news_id%TYPE + ) return varchar2 + is + news_title varchar2(1000); + begin + select title + into news_title + from cr_revisions + where revision_id = news.name.news_id; + + return news_title; + end name; + + + -- + -- API for Revision management + -- + function revision_new ( + item_id in cr_items.item_id%TYPE, + -- + publish_date in cr_revisions.publish_date%TYPE default null, + text in varchar2 default null, + title in cr_revisions.title%TYPE, + lead in cr_news.lead%TYPE default null, + -- + -- here goes the revision log + description in cr_revisions.description%TYPE, + -- + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + package_id in cr_news.package_id%TYPE default null, + archive_date in cr_news.archive_date%TYPE default null, + approval_user in cr_news.approval_user%TYPE default null, + approval_date in cr_news.approval_date%TYPE default null, + approval_ip in cr_news.approval_ip%TYPE default null, + -- + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + -- + make_active_revision_p in varchar2 default 'f' + ) return cr_revisions.revision_id%TYPE + is + v_revision_id integer; + begin + -- create revision + v_revision_id := content_revision.new( + title => title, + description => description, + publish_date => publish_date, + mime_type => mime_type, + text => text, + item_id => item_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + -- create new news entry with new revision + insert into cr_news + (news_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + package_id, + lead, + archive_date, + approval_user, + approval_date, + approval_ip); + -- make active revision if indicated + if make_active_revision_p = 't' then + news.revision_set_active(v_revision_id); + end if; + return v_revision_id; + end revision_new; + + + + procedure revision_set_active ( + revision_id in cr_revisions.revision_id%TYPE + ) + is + v_news_item_p char; + -- could be used to check if really a 'news' item + begin + update + cr_items + set + live_revision = news.revision_set_active.revision_id, + publish_status = 'ready' + where + item_id = (select + item_id + from + cr_revisions + where + revision_id = news.revision_set_active.revision_id); + end revision_set_active; + + + procedure clone ( + new_package_id in cr_news.package_id%TYPE default null, + old_package_id in cr_news.package_id%TYPE default null + ) + is + new_news_id integer; + begin + for one_news in (select + publish_date, + content.blob_to_string(cr.content) as text, + cr.nls_language, + cr.title as title, + cn.lead as lead, + cr.mime_type, + cn.package_id, + archive_date, + approval_user, + approval_date, + approval_ip, + ao.creation_date, + ao.creation_ip, + ao.creation_user + from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao + where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_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 = cn.news_id + and cr.revision_id = ao.object_id)) + loop + + new_news_id := news.new( + publish_date => one_news.publish_date, + text => one_news.text, + nls_language => one_news.nls_language, + title => one_news.title, + lead => one_news.lead, + mime_type => one_news.mime_type, + package_id => news.clone.new_package_id, + archive_date => one_news.archive_date, + approval_user => one_news.approval_user, + approval_date => one_news.approval_date, + approval_ip => one_news.approval_ip, + creation_date => one_news.creation_date, + creation_ip => one_news.creation_ip, + creation_user => one_news.creation_user + ); + + end loop; + end clone; + + -- currently not used, because we want to audit revisions + procedure revision_delete ( + revision_id in cr_revisions.revision_id%TYPE + ) + is + begin + -- delete from cr_news table + delete from cr_news + where news_id = news.revision_delete.revision_id; + -- delete revision + content_revision.del( + revision_id => news.revision_delete.revision_id + ); + end revision_delete; + +end news; +/ +show errors + + + + +-- +-- views on 'news' application that pick from cr_news, cr_items, cr_revisions +-- Re-arrange 'joins' for performance tuning +-- + +-- Views on multiple items + +-- View on all released news items in its active revision +create or replace view news_items_approved +as +select + ci.item_id as item_id, + cn.package_id, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + cr.content as content, + decode(cr.mime_type, 'text/html','t','f') as html_p, + to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date, + cr.publish_date, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + cn.archive_date +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and ci.live_revision = cr.revision_id +and cr.revision_id = cn.news_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- View of all news items in the system +create or replace view news_items_live_or_submitted +as +select + ci.item_id as item_id, + cn.news_id, + cn.package_id, + cr.publish_date, + cn.archive_date, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + decode(cr.mime_type, 'text/html','t','f') as html_p, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_date, + ci.live_revision, + news.status(cr.publish_date, cn.archive_date) as status +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_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 = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_id); + + +-- View of unapproved items +create or replace view news_items_unapproved +as +select + ci.item_id as item_id, + cr.title as publish_title, + cn.lead as publish_lead, + cn.package_id as package_id, + ao.creation_date as creation_date, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id +and cr.revision_id = content_item.get_live_revision(ci.item_id) +and cr.revision_id = cn.news_id +and cr.item_id = ci.item_id +and cr.publish_date is null; + + + +-- One News Item Views +-- + +-- View of all revisions of a news item +create or replace view news_item_revisions +as +select + cr.item_id as item_id, + cr.revision_id, + ci.live_revision, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + cr.publish_date, + cn.archive_date, + cr.description as log_entry, + decode(cr.mime_type,'text/html','t','f') as html_p, + cr.mime_type as mime_type, + cn.package_id, + ao.creation_date as creation_date, + news.status(cr.publish_date, cn.archive_date) as status, + case when exists (select 1 + from cr_revisions cr2 + where cr2.revision_id = cn.news_id + and cr2.publish_date is null + ) then 1 else 0 end + as + approval_needed_p, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_user, + ao.creation_ip, + ci.name as item_name +from + cr_revisions cr, + cr_news cn, + cr_items ci, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and cr.revision_id = cn.news_id +and ci.item_id = cr.item_id +and ao.creation_user = ps.person_id; + + + +-- View of a submitted news item or active revision in unapproved state +create or replace view news_item_unapproved +as +select + cr.revision_id, + ci.name as item_name, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_ip as item_creation_ip, + ao.creation_date +from + cr_revisions cr, + cr_items ci, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + + +-- View of a news item as of its active revision +create or replace view news_item_full_active +as +select + ci.item_id as item_id, + cn.package_id as package_id, + revision_id, + cr.title as publish_title, + cn.lead as publish_lead, + content.blob_to_string(cr.content) as publish_body, + decode(cr.mime_type,'text/html','t','f') as html_p, + cr.publish_date, + cn.archive_date, + news.status(cr.publish_date, cn.archive_date) as status, + ci.name as item_name, + ps.person_id as creator_id, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.item_id = ci.item_id +and (cr.revision_id = ci.live_revision + or (ci.live_revision is null + and cr.revision_id = content_item.get_latest_revision(ci.item_id))) +and cr.revision_id = cn.news_id +and ci.item_id = ao.object_id +and ao.creation_user = ps.person_id; + + Index: openacs-4/packages/news/sql/postgresql/news-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-create.sql,v diff -u -r1.17 -r1.18 --- openacs-4/packages/news/sql/postgresql/news-create.sql 3 Feb 2005 00:15:27 -0000 1.17 +++ openacs-4/packages/news/sql/postgresql/news-create.sql 24 Feb 2005 18:03:04 -0000 1.18 @@ -86,6 +86,8 @@ references cr_revisions constraint cr_news_pk primary key, + -- article abstract + lead varchar(4000), -- include package_id to provide support for multiple instances package_id integer constraint cr_news_package_id_nn not null, @@ -99,6 +101,7 @@ references users, approval_date timestamptz, approval_ip varchar(50) + ); @@ -123,6 +126,18 @@ begin; -- create attributes for widget generation +-- lead +SELECT content_type__create_attribute ( + 'news', -- content type + 'lead', -- attr name + 'text', -- datatype + 'Lead', -- pretty name + 'Leads', -- pretty plural + null, -- sort order + null, -- default value + 'varchar(4000)' -- column spec +); + -- website archive date of news release select content_type__create_attribute ( 'news', -- content_type @@ -203,723 +218,8 @@ select inline_0 (); drop function inline_0 (); +-- Create views after package since they need news__status --- *** PACKAGE NEWS, plsql to create content_item *** -create function news__new (integer,varchar,timestamptz,text,varchar,varchar, - varchar,integer,timestamptz,integer,timestamptz,varchar,varchar, - varchar,integer,boolean) -returns integer as ' -declare - p_item_id alias for $1; -- default null - -- - p_locale alias for $2; -- default null, - -- - p_publish_date alias for $3; -- default null - p_text alias for $4; -- default null - p_nls_language alias for $5; -- default null - p_title alias for $6; -- default null - p_mime_type alias for $7; -- default ''text/plain'' - -- - p_package_id alias for $8; -- default null, - p_archive_date alias for $9; -- default null - p_approval_user alias for $10; -- default null - p_approval_date alias for $11; -- default null - p_approval_ip alias for $12; -- default null, - -- - p_relation_tag alias for $13; -- default null - -- - -- REMOVED: p_item_subtype alias for $14; -- default ''content_revision'' - -- REMOVED: p_content_type alias for $15; -- default ''news'' - -- REMOVED: p_creation_date alias for $16; -- default current_timestamp - p_creation_ip alias for $14; -- default null - p_creation_user alias for $15; -- default null - -- - p_is_live_p alias for $16; -- default ''f'' +\i news-package-create.sql - v_news_id integer; - v_item_id integer; - v_id integer; - v_revision_id integer; - v_parent_id integer; - v_name varchar; - v_log_string varchar; -begin - select content_item__get_id(''news'',null,''f'') - into v_parent_id - from dual; - -- - -- this will be used for 2xClick protection - if p_item_id is null then - select acs_object_id_seq.nextval - into v_id - from dual; - else - v_id := p_item_id; - end if; - -- - select ''news'' || to_char(current_timestamp,''YYYYMMDD'') || v_id - into v_name - from dual; - -- - v_log_string := ''initial submission''; - -- - v_item_id := content_item__new( - v_name, -- name - v_parent_id, -- parent_id - v_id, -- item_id - p_locale, -- locale - current_timestamp, -- creation_date - p_creation_user, -- creation_user - p_package_id, -- context_id - p_creation_ip, -- creation_ip - ''content_item'', -- item_subtype - ''news'', -- content_type - null, -- title - null, -- description - p_mime_type, -- mime_type - p_nls_language, -- nls_language - null, -- data - ''text'' -- storage_type - -- relation tag is not used by any callers or any - -- implementations of content_item__new - ); - v_revision_id := content_revision__new( - p_title, -- title - v_log_string, -- description - p_publish_date, -- publish_date - p_mime_type, -- mime_type - p_nls_language, -- nls_language - p_text, -- data - v_item_id, -- item_id - null, -- revision_id - current_timestamp, -- creation_date - p_creation_user, -- creation_user - p_creation_ip -- creation_ip - ); - insert into cr_news - (news_id, - package_id, - archive_date, - approval_user, - approval_date, - approval_ip) - values - (v_revision_id, - p_package_id, - p_archive_date, - p_approval_user, - p_approval_date, - p_approval_ip); - -- make this revision live when immediately approved - if p_is_live_p = ''t'' then - update - cr_items - set - live_revision = v_revision_id, - publish_status = ''ready'' - where - item_id = v_item_id; - end if; - v_news_id := v_revision_id; - return v_news_id; -end; -' language 'plpgsql'; - - --- deletes a news item along with all its revisions and possible attachements -create function news__delete (integer) -returns integer as ' -declare - p_item_id alias for $1; - v_item_id cr_items.item_id%TYPE; - v_cm RECORD; -begin - v_item_id := p_item_id; - -- dbms_output.put_line(''Deleting associated comments...''); - -- delete acs_messages, images, comments to news item - - FOR v_cm IN - select message_id from acs_messages am, acs_objects ao - where am.message_id = ao.object_id - and ao.context_id = v_item_id - LOOP - -- images - delete from images - where image_id in (select latest_revision - from cr_items - where parent_id = v_cm.message_id); - PERFORM acs_message__delete(v_cm.message_id); - delete from general_comments - where comment_id = v_cm.message_id; - END LOOP; - delete from cr_news - where news_id in (select revision_id - from cr_revisions - where item_id = v_item_id); - PERFORM content_item__delete(v_item_id); - return 0; -end; -' language 'plpgsql'; - - --- (re)-publish a news item out of the archive by nulling the archive_date --- this only applies to the currently active revision -create function news__make_permanent (integer) -returns integer as ' -declare - p_item_id alias for $1; -begin - update cr_news - set archive_date = null - where news_id = content_item__get_live_revision(p_item_id); - - return 0; -end; -' language 'plpgsql'; - - --- archive a news item --- this only applies to the currently active revision -create function news__archive (integer,timestamptz) -returns integer as ' -declare - p_item_id alias for $1; - p_archive_date alias for $2; -- default current_timestamp -begin - update cr_news - set archive_date = p_archive_date - where news_id = content_item__get_live_revision(p_item_id); - - return 0; -end; -' language 'plpgsql'; - --- RAL: an overloaded version using current_timestamp for archive_date -create function news__archive (integer) -returns integer as ' -declare - p_item_id alias for $1; - -- p_archive_date alias for $2; -- default current_timestamp -begin - return news__archive (p_item_id, current_timestamp); -end; -' language 'plpgsql'; - - --- approve/unapprove a specific revision --- approving a revision makes it also the active revision -create function news__set_approve(integer,varchar,timestamptz, - timestamptz,integer,timestamptz,varchar,boolean) -returns integer as ' -declare - p_revision_id alias for $1; - p_approve_p alias for $2; -- default ''t'' - p_publish_date alias for $3; -- default null - p_archive_date alias for $4; -- default null - p_approval_user alias for $5; -- default null - p_approval_date alias for $6; -- default current_timestamp - p_approval_ip alias for $7; -- default null - p_live_revision_p alias for $8; -- default ''t'' - v_item_id cr_items.item_id%TYPE; -begin - select item_id into v_item_id - from cr_revisions - where revision_id = p_revision_id; - -- unapprove an revision (does not mean to knock out active revision) - if p_approve_p = ''f'' then - update cr_news - set approval_date = null, - approval_user = null, - approval_ip = null, - archive_date = null - where news_id = p_revision_id; - -- - update cr_revisions - set publish_date = null - where revision_id = p_revision_id; - else - -- approve a revision - update cr_revisions - set publish_date = p_publish_date - where revision_id = p_revision_id; - -- - update cr_news - set archive_date = p_archive_date, - approval_date = p_approval_date, - approval_user = p_approval_user, - approval_ip = p_approval_ip - where news_id = p_revision_id; - -- - -- cannot use content_item.set_live_revision because it sets publish_date to sysdate - if p_live_revision_p = ''t'' then - update cr_items - set live_revision = p_revision_id, - publish_status = ''ready'' - where item_id = v_item_id; - end if; - -- - end if; - - return 0; -end; -' language 'plpgsql'; - - --- the status function returns information on the puplish or archive status --- it does not make any checks on the order of publish_date and archive_date -create function news__status (timestamptz, timestamptz) -returns varchar as ' -declare - p_publish_date alias for $1; - p_archive_date alias for $2; -begin - if p_publish_date is not null then - if p_publish_date > current_timestamp then - -- Publishing in the future - if p_archive_date is null then - return ''going_live_no_archive''; - else - return ''going_live_with_archive''; - end if; - else - -- Published in the past - if p_archive_date is null then - return ''published_no_archive''; - else - if p_archive_date > current_timestamp then - return ''published_with_archive''; - else - return ''archived''; - end if; - end if; - end if; - else - -- publish_date null - return ''unapproved''; - end if; -end; -' language 'plpgsql'; - -create function news__name (integer) -returns varchar as ' -declare - p_news_id alias for $1; - v_news_title cr_revisions.title%TYPE; -begin - select title - into v_news_title - from cr_revisions - where revision_id = p_news_id; - - return v_news_title; -end; -' language 'plpgsql'; - - --- --- API for Revision management --- -create function news__revision_new (integer,timestamptz,text,varchar,text, - varchar,integer,timestamptz,integer,timestamptz,varchar,timestamptz,varchar, - integer,boolean) -returns integer as ' -declare - p_item_id alias for $1; - -- - p_publish_date alias for $2; -- default null - p_text alias for $3; -- default null - p_title alias for $4; - -- - -- here goes the revision log - p_description alias for $5; - -- - p_mime_type alias for $6; -- default ''text/plain'' - p_package_id alias for $7; -- default null - p_archive_date alias for $8; -- default null - p_approval_user alias for $9; -- default null - p_approval_date alias for $10; -- default null - p_approval_ip alias for $11; -- default null - -- - p_creation_date alias for $12; -- default current_timestamp - p_creation_ip alias for $13; -- default null - p_creation_user alias for $14; -- default null - -- - p_make_active_revision_p alias for $15; -- default ''f'' - - v_revision_id integer; -begin - -- create revision - v_revision_id := content_revision__new( - p_title, -- title - p_description, -- description - p_publish_date, -- publish_date - p_mime_type, -- mime_type - null, -- nls_language - p_text, -- text - p_item_id, -- item_id - null, -- revision_id - p_creation_date, -- creation_date - p_creation_user, -- creation_user - p_creation_ip -- creation_ip - ); - -- create new news entry with new revision - insert into cr_news - (news_id, - package_id, - archive_date, - approval_user, - approval_date, - approval_ip) - values - (v_revision_id, - p_package_id, - p_archive_date, - p_approval_user, - p_approval_date, - p_approval_ip); - -- make active revision if indicated - if p_make_active_revision_p = ''t'' then - PERFORM news__revision_set_active(v_revision_id); - end if; - return v_revision_id; -end; -' language 'plpgsql'; - - -create function news__revision_set_active (integer) -returns integer as ' -declare - p_revision_id alias for $1; - v_news_item_p boolean; - -- could be used to check if really a ''news'' item -begin - update - cr_items - set - live_revision = p_revision_id, - publish_status = ''ready'' - where - item_id = (select - item_id - from - cr_revisions - where - revision_id = p_revision_id); - - return 0; -end; -' language 'plpgsql'; - - - --- Incomplete for want of blob_to_string() in postgres 16 july 2000 - -create function news__clone (integer, integer) -returns integer as ' -declare - p_new_package_id alias for $1; --default null, - p_old_package_id alias for $2; --default null - one_news record; -begin - for one_news in select - publish_date, - cr.content as text, - cr.nls_language, - cr.title as title, - cr.mime_type, - cn.package_id, - archive_date, - approval_user, - approval_date, - approval_ip, - ao.creation_date, - ao.creation_ip, - ao.creation_user - from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao - where - (ci.item_id = cr.item_id - and ci.live_revision = cr.revision_id - and cr.revision_id = cn.news_id - and cr.revision_id = ao.object_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 = cn.news_id - and cr.revision_id = ao.object_id) - - loop - perform news__new( - one_news.publish_date, - one_news.text, - one_news.nls_language, - one_news.title, - one_news.mime_type, - new_package_id, - one_news.archive_date, - one_news.approval_user, - one_news.approval_date, - one_news.approval_ip, - one_news.creation_date, - one_news.creation_ip, - one_news.creation_user - ); - - end loop; - return 0; -end; -' language 'plpgsql'; - - - - - --- currently not used, because we want to audit revisions -create function news__revision_delete (integer) -returns integer as ' -declare - p_revision_id alias for $1; -begin - -- delete from cr_news table - delete from cr_news - where news_id = p_revision_id; - - -- delete revision - PERFORM content_revision__delete( - p_revision_id -- revision_id - ); - - return 0; -end; -' language 'plpgsql'; - - --- --- views on 'news' application that pick from cr_news, cr_items, cr_revisions --- Re-arrange 'joins' for performance tuning --- RAL: Casted all _date columns to ::date for consistency with Oracle views. --- - --- Views on multiple items - --- View on all released news items in its active revision --- RAL: for now, changed: --- content.blob_to_string(cr.content) as publish_body, --- to --- cr.content as publish_body --- --- RAL: Dropped 'content' column from this view which is redundant and not --- used anywhere. --- -create view news_items_approved -as -select - ci.item_id as item_id, - cn.package_id, - cr.title as publish_title, - cr.content as publish_body, - (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, - to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date, - cr.publish_date, - ao.creation_user, - ps.first_names || ' ' || ps.last_name as item_creator, - cn.archive_date::date as archive_date -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - ci.item_id = cr.item_id -and ci.live_revision = cr.revision_id -and cr.revision_id = cn.news_id -and cr.revision_id = ao.object_id -and ao.creation_user = ps.person_id; - - --- View of all news items in the system --- RAL: for now, changed: --- content.blob_to_string(cr.content) as publish_body, --- to --- cr.content as publish_body --- -create view news_items_live_or_submitted -as -select - ci.item_id as item_id, - cn.news_id, - cn.package_id, - cr.publish_date, - cn.archive_date, - cr.title as publish_title, - cr.content as publish_body, - (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, - ao.creation_user, - ps.first_names || ' ' || ps.last_name as item_creator, - ao.creation_date::date as creation_date, - ci.live_revision, - news__status(cr.publish_date, cn.archive_date) as status -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - (ci.item_id = cr.item_id - and ci.live_revision = cr.revision_id - and cr.revision_id = cn.news_id - and cr.revision_id = ao.object_id - and ao.creation_user = ps.person_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 = cn.news_id - and cr.revision_id = ao.object_id - and ao.creation_user = ps.person_id); - - --- View of unapproved items -create view news_items_unapproved -as -select - ci.item_id as item_id, - cr.title as publish_title, - cn.package_id as package_id, - ao.creation_date::date as creation_date, - ps.first_names || ' ' || ps.last_name as item_creator -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - cr.revision_id = ao.object_id -and ao.creation_user = ps.person_id -and cr.revision_id = content_item__get_live_revision(ci.item_id) -and cr.revision_id = cn.news_id -and cr.item_id = ci.item_id -and cr.publish_date is null; - - --- One News Item Views --- --- View of all revisions of a news item --- RAL: for now, changed: --- content.blob_to_string(cr.content) as publish_body, --- to --- cr.content as publish_body --- -create view news_item_revisions -as -select - cr.item_id as item_id, - cr.revision_id, - ci.live_revision, - cr.title as publish_title, - cr.content as publish_body, - cr.publish_date, - cn.archive_date, - cr.description as log_entry, - (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, - cr.mime_type as mime_type, - cn.package_id, - ao.creation_date::date as creation_date, - news__status(cr.publish_date, cn.archive_date) as status, - case when exists (select 1 - from cr_revisions cr2 - where cr2.revision_id = cn.news_id - and cr2.publish_date is null - ) then 1 else 0 end - as - approval_needed_p, - ps.first_names || ' ' || ps.last_name as item_creator, - ao.creation_user, - ao.creation_ip, - ci.name as item_name -from - cr_revisions cr, - cr_news cn, - cr_items ci, - acs_objects ao, - persons ps -where - cr.revision_id = ao.object_id -and cr.revision_id = cn.news_id -and ci.item_id = cr.item_id -and ao.creation_user = ps.person_id; - - --- View of a submitted news item or active revision in unapproved state -create view news_item_unapproved -as -select - cr.revision_id, - ci.name as item_name, - ps.first_names || ' ' || ps.last_name as item_creator, - ao.creation_ip as item_creation_ip, - ao.creation_date::date as creation_date -from - cr_revisions cr, - cr_items ci, - acs_objects ao, - persons ps -where - ci.item_id = cr.item_id -and cr.revision_id = ao.object_id -and ao.creation_user = ps.person_id; - - --- View of a news item as of its active revision --- RAL: for now, changed: --- content.blob_to_string(cr.content) as publish_body, --- to --- cr.content as publish_body --- -create view news_item_full_active -as -select - ci.item_id as item_id, - cn.package_id as package_id, - revision_id, - cr.title as publish_title, - cr.content as publish_body, - (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, - cr.publish_date, - cn.archive_date, - news__status(cr.publish_date, cn.archive_date) as status, - ci.name as item_name, - ps.person_id as creator_id, - ps.first_names || ' ' || ps.last_name as item_creator -from - cr_items ci, - cr_revisions cr, - cr_news cn, - acs_objects ao, - persons ps -where - cr.item_id = ci.item_id -and (cr.revision_id = ci.live_revision - or (ci.live_revision is null - and cr.revision_id = content_item__get_latest_revision(ci.item_id))) -and cr.revision_id = cn.news_id -and ci.item_id = ao.object_id -and ao.creation_user = ps.person_id; - - --- plsql to create keywords for news items --- no additional code necessary for news items right now. - --- plsql for searches: will be covered by site-wide search --- no additional code necessary for news items right now. - - --- *** Search contract registration *** --- --- JCD: bind in tcl instead. --- \i news-sc-create.sql +\i news-views-create.sql Index: openacs-4/packages/news/sql/postgresql/news-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-drop.sql,v diff -u -r1.8 -r1.9 --- openacs-4/packages/news/sql/postgresql/news-drop.sql 3 Feb 2005 00:15:27 -0000 1.8 +++ openacs-4/packages/news/sql/postgresql/news-drop.sql 24 Feb 2005 18:03:04 -0000 1.9 @@ -4,7 +4,6 @@ -- @created 2000-12-20 -- $Id$ - -- unregister content_types from folder create function inline_0 () returns integer as ' @@ -66,22 +65,11 @@ -- delete pertinent info from cr_news --drop table cr_news; +\i news-views-drop.sql +\i news-package-drop.sql --- drop package news -select drop_package('news'); - --- delete news views -drop view news_items_approved; -drop view news_items_live_or_submitted; -drop view news_items_unapproved; -drop view news_item_revisions; -drop view news_item_unapproved; -drop view news_item_full_active; - -drop function news__status (timestamptz, timestamptz); - -- drop CR content_type select content_type__drop_type( 'news', -- content_type @@ -145,3 +133,4 @@ 'FtsContentProvider', -- impl_contract_name 'news' -- impl_name ); + Index: openacs-4/packages/news/sql/postgresql/news-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/postgresql/news-package-create.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,505 @@ +-- /packages/news/sql/news-package-create.sql +-- +-- @author stefan@arsdigita.com +-- @created 2000-12-13 +-- @cvs-id $Id: news-package-create.sql,v 1.1 2005/02/24 18:03:04 jeffd Exp $ +-- +-- OpenACS Port: Robert Locke (rlocke@infiniteinfo.com) + +-- *** PACKAGE NEWS, plsql to create content_item *** +create or replace function news__new (integer,varchar,timestamptz,text,varchar,varchar, + varchar,integer,timestamptz,integer,timestamptz,varchar,varchar, + varchar,integer,boolean, varchar) +returns integer as ' +declare + p_item_id alias for $1; -- default null + -- + p_locale alias for $2; -- default null, + -- + p_publish_date alias for $3; -- default null + p_text alias for $4; -- default null + p_nls_language alias for $5; -- default null + p_title alias for $6; -- default null + p_mime_type alias for $7; -- default ''text/plain'' + -- + p_package_id alias for $8; -- default null, + p_archive_date alias for $9; -- default null + p_approval_user alias for $10; -- default null + p_approval_date alias for $11; -- default null + p_approval_ip alias for $12; -- default null, + -- + p_relation_tag alias for $13; -- default null + -- + -- REMOVED: p_item_subtype alias for $14; -- default ''content_revision'' + -- REMOVED: p_content_type alias for $15; -- default ''news'' + -- REMOVED: p_creation_date alias for $16; -- default current_timestamp + p_creation_ip alias for $14; -- default null + p_creation_user alias for $15; -- default null + -- + p_is_live_p alias for $16; -- default ''f'' + p_lead alias for $17; -- default ''f'' + + v_news_id integer; + v_item_id integer; + v_id integer; + v_revision_id integer; + v_parent_id integer; + v_name varchar; + v_log_string varchar; +begin + select content_item__get_id(''news'',null,''f'') + into v_parent_id + from dual; + -- + -- this will be used for 2xClick protection + if p_item_id is null then + select acs_object_id_seq.nextval + into v_id + from dual; + else + v_id := p_item_id; + end if; + -- + select ''news'' || to_char(current_timestamp,''YYYYMMDD'') || v_id + into v_name + from dual; + -- + v_log_string := ''initial submission''; + -- + v_item_id := content_item__new( + v_name, -- name + v_parent_id, -- parent_id + v_id, -- item_id + p_locale, -- locale + current_timestamp, -- creation_date + p_creation_user, -- creation_user + p_package_id, -- context_id + p_creation_ip, -- creation_ip + ''content_item'', -- item_subtype + ''news'', -- content_type + null, -- title + null, -- description + p_mime_type, -- mime_type + p_nls_language, -- nls_language + null, -- data + ''text'' -- storage_type + -- relation tag is not used by any callers or any + -- implementations of content_item__new + ); + v_revision_id := content_revision__new( + p_title, -- title + v_log_string, -- description + p_publish_date, -- publish_date + p_mime_type, -- mime_type + p_nls_language, -- nls_language + p_text, -- data + v_item_id, -- item_id + null, -- revision_id + current_timestamp, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + insert into cr_news + (news_id, + lead, + package_id, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + p_lead, + p_package_id, + p_archive_date, + p_approval_user, + p_approval_date, + p_approval_ip); + -- make this revision live when immediately approved + if p_is_live_p = ''t'' then + update + cr_items + set + live_revision = v_revision_id, + publish_status = ''ready'' + where + item_id = v_item_id; + end if; + v_news_id := v_revision_id; + return v_news_id; +end; +' language 'plpgsql'; + + +-- deletes a news item along with all its revisions and possible attachements +create or replace function news__delete (integer) +returns integer as ' +declare + p_item_id alias for $1; + v_item_id cr_items.item_id%TYPE; + v_cm RECORD; +begin + v_item_id := p_item_id; + -- dbms_output.put_line(''Deleting associated comments...''); + -- delete acs_messages, images, comments to news item + + FOR v_cm IN + select message_id from acs_messages am, acs_objects ao + where am.message_id = ao.object_id + and ao.context_id = v_item_id + LOOP + -- images + delete from images + where image_id in (select latest_revision + from cr_items + where parent_id = v_cm.message_id); + PERFORM acs_message__delete(v_cm.message_id); + delete from general_comments + where comment_id = v_cm.message_id; + END LOOP; + delete from cr_news + where news_id in (select revision_id + from cr_revisions + where item_id = v_item_id); + PERFORM content_item__delete(v_item_id); + return 0; +end; +' language 'plpgsql'; + + +-- (re)-publish a news item out of the archive by nulling the archive_date +-- this only applies to the currently active revision +create or replace function news__make_permanent (integer) +returns integer as ' +declare + p_item_id alias for $1; +begin + update cr_news + set archive_date = null + where news_id = content_item__get_live_revision(p_item_id); + + return 0; +end; +' language 'plpgsql'; + + +-- archive a news item +-- this only applies to the currently active revision +create or replace function news__archive (integer,timestamptz) +returns integer as ' +declare + p_item_id alias for $1; + p_archive_date alias for $2; -- default current_timestamp +begin + update cr_news + set archive_date = p_archive_date + where news_id = content_item__get_live_revision(p_item_id); + + return 0; +end; +' language 'plpgsql'; + +-- RAL: an overloaded version using current_timestamp for archive_date +create or replace function news__archive (integer) +returns integer as ' +declare + p_item_id alias for $1; + -- p_archive_date alias for $2; -- default current_timestamp +begin + return news__archive (p_item_id, current_timestamp); +end; +' language 'plpgsql'; + + +-- approve/unapprove a specific revision +-- approving a revision makes it also the active revision +create or replace function news__set_approve(integer,varchar,timestamptz, + timestamptz,integer,timestamptz,varchar,boolean) +returns integer as ' +declare + p_revision_id alias for $1; + p_approve_p alias for $2; -- default ''t'' + p_publish_date alias for $3; -- default null + p_archive_date alias for $4; -- default null + p_approval_user alias for $5; -- default null + p_approval_date alias for $6; -- default current_timestamp + p_approval_ip alias for $7; -- default null + p_live_revision_p alias for $8; -- default ''t'' + v_item_id cr_items.item_id%TYPE; +begin + select item_id into v_item_id + from cr_revisions + where revision_id = p_revision_id; + -- unapprove an revision (does not mean to knock out active revision) + if p_approve_p = ''f'' then + update cr_news + set approval_date = null, + approval_user = null, + approval_ip = null, + archive_date = null + where news_id = p_revision_id; + -- + update cr_revisions + set publish_date = null + where revision_id = p_revision_id; + else + -- approve a revision + update cr_revisions + set publish_date = p_publish_date + where revision_id = p_revision_id; + -- + update cr_news + set archive_date = p_archive_date, + approval_date = p_approval_date, + approval_user = p_approval_user, + approval_ip = p_approval_ip + where news_id = p_revision_id; + -- + -- cannot use content_item.set_live_revision because it sets publish_date to sysdate + if p_live_revision_p = ''t'' then + update cr_items + set live_revision = p_revision_id, + publish_status = ''ready'' + where item_id = v_item_id; + end if; + -- + end if; + + return 0; +end; +' language 'plpgsql'; + + +-- the status function returns information on the puplish or archive status +-- it does not make any checks on the order of publish_date and archive_date +create or replace function news__status (timestamptz, timestamptz) +returns varchar as ' +declare + p_publish_date alias for $1; + p_archive_date alias for $2; +begin + if p_publish_date is not null then + if p_publish_date > current_timestamp then + -- Publishing in the future + if p_archive_date is null then + return ''going_live_no_archive''; + else + return ''going_live_with_archive''; + end if; + else + -- Published in the past + if p_archive_date is null then + return ''published_no_archive''; + else + if p_archive_date > current_timestamp then + return ''published_with_archive''; + else + return ''archived''; + end if; + end if; + end if; + else + -- publish_date null + return ''unapproved''; + end if; +end; +' language 'plpgsql'; + +create or replace function news__name (integer) +returns varchar as ' +declare + p_news_id alias for $1; + v_news_title cr_revisions.title%TYPE; +begin + select title + into v_news_title + from cr_revisions + where revision_id = p_news_id; + + return v_news_title; +end; +' language 'plpgsql'; + + +-- +-- API for Revision management +-- +create or replace function news__revision_new (integer,timestamptz,text,varchar,text, + varchar,integer,timestamptz,integer,timestamptz,varchar,timestamptz,varchar, + integer,boolean, varchar) +returns integer as ' +declare + p_item_id alias for $1; + -- + p_publish_date alias for $2; -- default null + p_text alias for $3; -- default null + p_title alias for $4; + -- + -- here goes the revision log + p_description alias for $5; + -- + p_mime_type alias for $6; -- default ''text/plain'' + p_package_id alias for $7; -- default null + p_archive_date alias for $8; -- default null + p_approval_user alias for $9; -- default null + p_approval_date alias for $10; -- default null + p_approval_ip alias for $11; -- default null + -- + p_creation_date alias for $12; -- default current_timestamp + p_creation_ip alias for $13; -- default null + p_creation_user alias for $14; -- default null + -- + p_make_active_revision_p alias for $15; -- default ''f'' + p_lead alias for $16; + + v_revision_id integer; +begin + -- create revision + v_revision_id := content_revision__new( + p_title, -- title + p_description, -- description + p_publish_date, -- publish_date + p_mime_type, -- mime_type + null, -- nls_language + p_text, -- text + p_item_id, -- item_id + null, -- revision_id + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + -- create new news entry with new revision + insert into cr_news + (news_id, + lead, + package_id, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + p_lead, + p_package_id, + p_archive_date, + p_approval_user, + p_approval_date, + p_approval_ip); + -- make active revision if indicated + if p_make_active_revision_p = ''t'' then + PERFORM news__revision_set_active(v_revision_id); + end if; + return v_revision_id; +end; +' language 'plpgsql'; + + +create or replace function news__revision_set_active (integer) +returns integer as ' +declare + p_revision_id alias for $1; + v_news_item_p boolean; + -- could be used to check if really a ''news'' item +begin + update + cr_items + set + live_revision = p_revision_id, + publish_status = ''ready'' + where + item_id = (select + item_id + from + cr_revisions + where + revision_id = p_revision_id); + + return 0; +end; +' language 'plpgsql'; + + + +-- Incomplete for want of blob_to_string() in postgres 16 july 2000 + +create or replace function news__clone (integer, integer) +returns integer as ' +declare + p_new_package_id alias for $1; --default null, + p_old_package_id alias for $2; --default null + one_news record; +begin + for one_news in select + publish_date, + cr.content as text, + cr.nls_language, + cr.title as title, + cr.mime_type, + cn.package_id, + archive_date, + approval_user, + approval_date, + approval_ip, + ao.creation_date, + ao.creation_ip, + ao.creation_user + from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao + where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_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 = cn.news_id + and cr.revision_id = ao.object_id) + + loop + perform news__new( + one_news.publish_date, + one_news.text, + one_news.nls_language, + one_news.title, + one_news.mime_type, + new_package_id, + one_news.archive_date, + one_news.approval_user, + one_news.approval_date, + one_news.approval_ip, + one_news.creation_date, + one_news.creation_ip, + one_news.creation_user + ); + + end loop; + return 0; +end; +' language 'plpgsql'; + + +-- currently not used, because we want to audit revisions +create or replace function news__revision_delete (integer) +returns integer as ' +declare + p_revision_id alias for $1; +begin + -- delete from cr_news table + delete from cr_news + where news_id = p_revision_id; + + -- delete revision + PERFORM content_revision__delete( + p_revision_id -- revision_id + ); + + return 0; +end; +' language 'plpgsql'; + + + Index: openacs-4/packages/news/sql/postgresql/news-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/postgresql/news-package-drop.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,7 @@ +-- drop package news + +drop function news__status (timestamptz, timestamptz) cascade; +select drop_package('news'); + + + Index: openacs-4/packages/news/sql/postgresql/news-views-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-views-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/postgresql/news-views-create.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,217 @@ +-- views on 'news' application that pick from cr_news, cr_items, cr_revisions +-- Re-arrange 'joins' for performance tuning +-- RAL: Casted all _date columns to ::date for consistency with Oracle views. +-- + +-- Views on multiple items + +-- View on all released news items in its active revision +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +-- RAL: Dropped 'content' column from this view which is redundant and not +-- used anywhere. +-- +create or replace view news_items_approved +as +select + ci.item_id as item_id, + cn.package_id, + cr.title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date, + cr.publish_date, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + cn.archive_date::date as archive_date +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and ci.live_revision = cr.revision_id +and cr.revision_id = cn.news_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- View of all news items in the system +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +create or replace view news_items_live_or_submitted +as +select + ci.item_id as item_id, + cn.news_id, + cn.package_id, + cr.publish_date, + cn.archive_date, + cr.title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_date::date as creation_date, + ci.live_revision, + news__status(cr.publish_date, cn.archive_date) as status +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_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 = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_id); + + +-- View of unapproved items +create or replace view news_items_unapproved +as +select + ci.item_id as item_id, + cr.title as publish_title, + cn.lead as publish_lead, + cn.package_id as package_id, + ao.creation_date::date as creation_date, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id +and cr.revision_id = content_item__get_live_revision(ci.item_id) +and cr.revision_id = cn.news_id +and cr.item_id = ci.item_id +and cr.publish_date is null; + + +-- One News Item Views +-- +-- View of all revisions of a news item +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +create or replace view news_item_revisions +as +select + cr.item_id as item_id, + cr.revision_id, + ci.live_revision, + cr.title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + cr.publish_date, + cn.archive_date, + cr.description as log_entry, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + cr.mime_type as mime_type, + cn.package_id, + ao.creation_date::date as creation_date, + news__status(cr.publish_date, cn.archive_date) as status, + case when exists (select 1 + from cr_revisions cr2 + where cr2.revision_id = cn.news_id + and cr2.publish_date is null + ) then 1 else 0 end + as + approval_needed_p, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_user, + ao.creation_ip, + ci.name as item_name +from + cr_revisions cr, + cr_news cn, + cr_items ci, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and cr.revision_id = cn.news_id +and ci.item_id = cr.item_id +and ao.creation_user = ps.person_id; + + +-- View of a submitted news item or active revision in unapproved state +create or replace view news_item_unapproved +as +select + cr.revision_id, + ci.name as item_name, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_ip as item_creation_ip, + ao.creation_date::date as creation_date +from + cr_revisions cr, + cr_items ci, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + + +-- View of a news item as of its active revision +-- RAL: for now, changed: +-- content.blob_to_string(cr.content) as publish_body, +-- to +-- cr.content as publish_body +-- +create or replace view news_item_full_active +as +select + ci.item_id as item_id, + cn.package_id as package_id, + revision_id, + cr.title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + cr.publish_date, + cn.archive_date, + news__status(cr.publish_date, cn.archive_date) as status, + ci.name as item_name, + ps.person_id as creator_id, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.item_id = ci.item_id +and (cr.revision_id = ci.live_revision + or (ci.live_revision is null + and cr.revision_id = content_item__get_latest_revision(ci.item_id))) +and cr.revision_id = cn.news_id +and ci.item_id = ao.object_id +and ao.creation_user = ps.person_id; Index: openacs-4/packages/news/sql/postgresql/news-views-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-views-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/postgresql/news-views-drop.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,8 @@ +-- Drop all the views in reverse order of create. +-- +drop view news_item_full_active; +drop view news_item_unapproved; +drop view news_item_revisions; +drop view news_items_unapproved; +drop view news_items_live_or_submitted; +drop view news_items_approved; Index: openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d1-5.2.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d1-5.2.0d2.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d1-5.2.0d2.sql 24 Feb 2005 18:03:04 -0000 1.1 @@ -0,0 +1,388 @@ +------------------------------------------------------------------------------ +-- Add a Lead or abstract chunk to news. +-- @author Tom Ayles (tom@beatniq.net) +-- @creation-date 2004-01-08 +-- @cvs-id $Id: upgrade-5.2.0d1-5.2.0d2.sql,v 1.1 2005/02/24 18:03:04 jeffd Exp $ +------------------------------------------------------------------------------ + +------------------------------------------------------------------------------ +-- This first section alters the data model so that a news item can have a +-- lead. The lead is a short text description of the article that is used on +-- the front page of the package. +------------------------------------------------------------------------------ + +ALTER TABLE cr_news ADD COLUMN lead varchar(4000); + +SELECT content_type__create_attribute ( + 'news', -- content type + 'lead', -- attr name + 'text', -- datatype + 'Lead', -- pretty name + 'Leads', -- pretty plural + null, -- sort order + null, -- default value + 'varchar(1000)' -- column spec +); + +-- Beware the number of parameters in this definition +-- means that a default build of PGSQL 7.2 can't use it. + + + +create or replace function news__new (integer,varchar,timestamptz,text,varchar,varchar, + varchar,integer,timestamptz,integer,timestamptz,varchar,varchar, + varchar,integer,boolean, varchar) +returns integer as ' +declare + p_item_id alias for $1; -- default null + -- + p_locale alias for $2; -- default null, + -- + p_publish_date alias for $3; -- default null + p_text alias for $4; -- default null + p_nls_language alias for $5; -- default null + p_title alias for $6; -- default null + p_mime_type alias for $7; -- default ''text/plain'' + -- + p_package_id alias for $8; -- default null, + p_archive_date alias for $9; -- default null + p_approval_user alias for $10; -- default null + p_approval_date alias for $11; -- default null + p_approval_ip alias for $12; -- default null, + -- + p_relation_tag alias for $13; -- default null + -- + -- REMOVED: p_item_subtype alias for $14; -- default ''content_revision'' + -- REMOVED: p_content_type alias for $15; -- default ''news'' + -- REMOVED: p_creation_date alias for $16; -- default current_timestamp + p_creation_ip alias for $14; -- default null + p_creation_user alias for $15; -- default null + -- + p_is_live_p alias for $16; -- default ''f'' + p_lead alias for $17; -- default ''f'' + + v_news_id integer; + v_item_id integer; + v_id integer; + v_revision_id integer; + v_parent_id integer; + v_name varchar; + v_log_string varchar; +begin + select content_item__get_id(''news'',null,''f'') + into v_parent_id + from dual; + -- + -- this will be used for 2xClick protection + if p_item_id is null then + select acs_object_id_seq.nextval + into v_id + from dual; + else + v_id := p_item_id; + end if; + -- + select ''news'' || to_char(current_timestamp,''YYYYMMDD'') || v_id + into v_name + from dual; + -- + v_log_string := ''initial submission''; + -- + v_item_id := content_item__new( + v_name, -- name + v_parent_id, -- parent_id + v_id, -- item_id + p_locale, -- locale + current_timestamp, -- creation_date + p_creation_user, -- creation_user + p_package_id, -- context_id + p_creation_ip, -- creation_ip + ''content_item'', -- item_subtype + ''news'', -- content_type + null, -- title + null, -- description + p_mime_type, -- mime_type + p_nls_language, -- nls_language + null, -- data + ''text'' -- storage_type + -- relation tag is not used by any callers or any + -- implementations of content_item__new + ); + v_revision_id := content_revision__new( + p_title, -- title + v_log_string, -- description + p_publish_date, -- publish_date + p_mime_type, -- mime_type + p_nls_language, -- nls_language + p_text, -- data + v_item_id, -- item_id + null, -- revision_id + current_timestamp, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + insert into cr_news + (news_id, + lead, + package_id, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + p_lead, + p_package_id, + p_archive_date, + p_approval_user, + p_approval_date, + p_approval_ip); + -- make this revision live when immediately approved + if p_is_live_p = ''t'' then + update + cr_items + set + live_revision = v_revision_id, + publish_status = ''ready'' + where + item_id = v_item_id; + end if; + v_news_id := v_revision_id; + return v_news_id; +end; +' language 'plpgsql'; + + +create function news__revision_new (integer,timestamptz,text,varchar,text, + varchar,integer,timestamptz,integer,timestamptz,varchar,timestamptz,varchar, + integer,boolean, varchar) +returns integer as ' +declare + p_item_id alias for $1; + -- + p_publish_date alias for $2; -- default null + p_text alias for $3; -- default null + p_title alias for $4; + -- + -- here goes the revision log + p_description alias for $5; + -- + p_mime_type alias for $6; -- default ''text/plain'' + p_package_id alias for $7; -- default null + p_archive_date alias for $8; -- default null + p_approval_user alias for $9; -- default null + p_approval_date alias for $10; -- default null + p_approval_ip alias for $11; -- default null + -- + p_creation_date alias for $12; -- default current_timestamp + p_creation_ip alias for $13; -- default null + p_creation_user alias for $14; -- default null + -- + p_make_active_revision_p alias for $15; -- default ''f'' + p_lead alias for $16; + + v_revision_id integer; +begin + -- create revision + v_revision_id := content_revision__new( + p_title, -- title + p_description, -- description + p_publish_date, -- publish_date + p_mime_type, -- mime_type + null, -- nls_language + p_text, -- text + p_item_id, -- item_id + null, -- revision_id + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ); + -- create new news entry with new revision + insert into cr_news + (news_id, + lead, + package_id, + archive_date, + approval_user, + approval_date, + approval_ip) + values + (v_revision_id, + p_lead, + p_package_id, + p_archive_date, + p_approval_user, + p_approval_date, + p_approval_ip); + -- make active revision if indicated + if p_make_active_revision_p = ''t'' then + PERFORM news__revision_set_active(v_revision_id); + end if; + return v_revision_id; +end; +' language 'plpgsql'; + + +-- replace views. vaguely back-compatible, as all previous queries should still +-- work (all we've done is add the publish_lead column) +DROP VIEW news_items_approved; +CREATE VIEW news_items_approved +AS +select + ci.item_id as item_id, + cn.package_id, + cr.title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + to_char(cr.publish_date, 'Mon dd, yyyy') as pretty_publish_date, + cr.publish_date, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + cn.archive_date::date as archive_date +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + ci.item_id = cr.item_id +and ci.live_revision = cr.revision_id +and cr.revision_id = cn.news_id +and cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id; + +DROP VIEW news_items_live_or_submitted; +CREATE VIEW news_items_live_or_submitted +AS +select + ci.item_id as item_id, + cn.news_id, + cn.package_id, + cr.publish_date, + cn.archive_date, + cr.title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + ao.creation_user, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_date::date as creation_date, + ci.live_revision, + news__status(cr.publish_date, cn.archive_date) as status +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + (ci.item_id = cr.item_id + and ci.live_revision = cr.revision_id + and cr.revision_id = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_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 = cn.news_id + and cr.revision_id = ao.object_id + and ao.creation_user = ps.person_id); + +DROP VIEW news_items_unapproved; +CREATE VIEW news_items_unapproved +AS +select + ci.item_id as item_id, + cr.title as publish_title, + cn.lead as publish_lead, + cn.package_id as package_id, + ao.creation_date::date as creation_date, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and ao.creation_user = ps.person_id +and cr.revision_id = content_item__get_live_revision(ci.item_id) +and cr.revision_id = cn.news_id +and cr.item_id = ci.item_id +and cr.publish_date is null; + +DROP VIEW news_item_revisions; +CREATE VIEW news_item_revisions +AS +select + cr.item_id as item_id, + cr.revision_id, + ci.live_revision, + cr.title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + cr.publish_date, + cn.archive_date, + cr.description as log_entry, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + cr.mime_type as mime_type, + cn.package_id, + ao.creation_date::date as creation_date, + news__status(cr.publish_date, cn.archive_date) as status, + case when exists (select 1 + from cr_revisions cr2 + where cr2.revision_id = cn.news_id + and cr2.publish_date is null + ) then 1 else 0 end + as + approval_needed_p, + ps.first_names || ' ' || ps.last_name as item_creator, + ao.creation_user, + ao.creation_ip, + ci.name as item_name +from + cr_revisions cr, + cr_news cn, + cr_items ci, + acs_objects ao, + persons ps +where + cr.revision_id = ao.object_id +and cr.revision_id = cn.news_id +and ci.item_id = cr.item_id +and ao.creation_user = ps.person_id; + +DROP VIEW news_item_full_active; +CREATE VIEW news_item_full_active +AS +select + ci.item_id as item_id, + cn.package_id as package_id, + revision_id, + title as publish_title, + cn.lead as publish_lead, + cr.content as publish_body, + (case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p, + cr.publish_date, + cn.archive_date, + news__status(cr.publish_date, cn.archive_date) as status, + ci.name as item_name, + ps.person_id as creator_id, + ps.first_names || ' ' || ps.last_name as item_creator +from + cr_items ci, + cr_revisions cr, + cr_news cn, + acs_objects ao, + persons ps +where + cr.item_id = ci.item_id +and (cr.revision_id = ci.live_revision + or (ci.live_revision is null + and cr.revision_id = content_item__get_latest_revision(ci.item_id))) +and cr.revision_id = cn.news_id +and ci.item_id = ao.object_id +and ao.creation_user = ps.person_id; Index: openacs-4/packages/news/tcl/news-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/tcl/news-procs.tcl,v diff -u -r1.16 -r1.17 --- openacs-4/packages/news/tcl/news-procs.tcl 24 Feb 2005 13:33:23 -0000 1.16 +++ openacs-4/packages/news/tcl/news-procs.tcl 24 Feb 2005 18:03:05 -0000 1.17 @@ -85,7 +85,40 @@ } +ad_proc test_file_type { + imgsrc +} { + Used in form validation to check that the uploaded file type really is + what it's meant to be (invokes 'identify' on the file). + @author Tom Ayles (tom@beatniq.net) +} { + set mime_types [split \ + [parameter::get -parameter ImageUploadTypes] \ + {,}] + if {[catch \ + {array set img_props [ImageMagick::identify $imgsrc]} \ + errmsg]} { + return 0 + } + set mime_type "image/[string tolower $img_props(format)]" + if {[lsearch $mime_types $mime_type] < 0} { + return 0 + } + return 1 +} + + +ad_proc news_get_image_id { + item_id +} { + Retrieves the image associated with the given news item. + + @author Tom Ayles (tom@beatniq.net) +} { + return [db_string img {} -default {}] +} + ad_proc news__datasource { object_id } { Index: openacs-4/packages/news/tcl/news-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/tcl/news-procs.xql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/news/tcl/news-procs.xql 24 Feb 2005 13:33:23 -0000 1.5 +++ openacs-4/packages/news/tcl/news-procs.xql 24 Feb 2005 18:03:05 -0000 1.6 @@ -29,4 +29,10 @@ + +SELECT live_revision AS image_id +FROM cr_items +WHERE content_type = 'image' AND parent_id = :item_id + + Index: openacs-4/packages/news/www/image-choose.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/image-choose.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/image-choose.adp 24 Feb 2005 18:03:05 -0000 1.1 @@ -0,0 +1,17 @@ + +Upload Image + + +

Choose an image to upload and press the 'Upload' button to preview +it. When you're happy, press 'Accept' to use that image in the +article.

+ + + +
+@form_vars;noquote@ + +
+ + + Index: openacs-4/packages/news/www/image-choose.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/image-choose.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/image-choose.tcl 24 Feb 2005 18:03:05 -0000 1.1 @@ -0,0 +1,79 @@ +ad_page_contract { + Allows the content creator to upload an image to be attached to the + news item. All parameters are passed in so that we can link back to + the preview page with all fields still in tact. + + @author Tom Ayles (tom@beatniq.net) + @creation-date 2004-01-12 + @cvs-id $Id: image-choose.tcl,v 1.1 2005/02/24 18:03:05 jeffd Exp $ +} { + action + publish_title + publish_lead + publish_body:allhtml + revision_log:optional + html_p + publish_date_ansi + archive_date_ansi + permanent_p + upload.tmpfile:optional,tmpfile + item_id:optional +} + +# make sure only authorised users can upload images +permission::require_permission \ + -object_id [ad_conn package_id] -privilege news_create + + +set vars {action publish_title publish_lead publish_body html_p + publish_date_ansi archive_date_ansi permanent_p} + +if {[info exists item_id]} { lappend vars item_id revision_log } + +form create img -html {enctype multipart/form-data} \ + -edit_buttons {{{Upload} {ok}}} + +foreach var $vars { + element create img $var -datatype string -widget hidden -optional +} + +element create img upload \ + -datatype file \ + -widget file \ + -label {File} \ + -html [list accept [parameter::get -parameter ImageUploadTypes]] \ + -validate [list img_type \ + {test_file_type [ns_queryget upload.tmpfile]} \ + "Image must be one of the following types: [parameter::get -parameter ImageUploadTypes]" \ + ] + +# clear the upload file value on every request, as it otherwise it displays +# filename, tmppath, mime type which isn't at all useful +element set_value img upload {} + +if { [form is_request img] } { + foreach var $vars { element set_value img $var [set $var] } + if { [info exists item_id] } { + set image_id [news_get_image_id $item_id] + if { ![empty_string_p $image_id] } { set image_url "image/$image_id" } + } +} + +if { [form is_valid img] } { + form get_values img upload + set srcfile [ns_queryget upload.tmpfile] + set imgfile [ImageMagick::tmp_file] + ImageMagick::convert \ + -geometry [parameter::get -parameter ImageGeometry] \ + -output_format [parameter::get -parameter ImageFormat] \ + $srcfile $imgfile + element set_value img upload {} + set imgfile [ImageMagick::shorten_tmp_file $imgfile] + set image_url "image-view-tmpfile/$imgfile" + set form_vars [eval "export_form_vars [join $vars] imgfile"] + set mode preview +} else { + set mode form +} + +ad_return_template Index: openacs-4/packages/news/www/image-view-tmpfile.vuh =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/image-view-tmpfile.vuh,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/image-view-tmpfile.vuh 24 Feb 2005 18:03:05 -0000 1.1 @@ -0,0 +1,6 @@ +# serves a temporary image file +set filename [ad_conn path_info] + +regexp {^/(.*)$} $filename match filename + +ImageMagick::serve_tmp_file $filename Index: openacs-4/packages/news/www/image.vuh =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/image.vuh,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/news/www/image.vuh 24 Feb 2005 18:03:05 -0000 1.1 @@ -0,0 +1,6 @@ +# serves an image from the CR + +if {[regexp {^/([0-9]+)$} [ad_conn path_info] match image_id]} { + permission::require_permission -object_id $image_id -privilege read + cr_write_content -revision_id $image_id +} Index: openacs-4/packages/news/www/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/index.adp,v diff -u -r1.12 -r1.13 --- openacs-4/packages/news/www/index.adp 24 Feb 2005 13:33:23 -0000 1.12 +++ openacs-4/packages/news/www/index.adp 24 Feb 2005 18:03:05 -0000 1.13 @@ -20,11 +20,11 @@ - + +

@news_items.publish_date@: @news_items.publish_title@ +
@news_items.publish_lead@

+ +

#rss-support.Syndication_Feed# Subscribe via RSS

 @@ -33,13 +33,13 @@ Index: openacs-4/packages/news/www/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/index.tcl,v diff -u -r1.12 -r1.13 --- openacs-4/packages/news/www/index.tcl 24 Feb 2005 13:33:23 -0000 1.12 +++ openacs-4/packages/news/www/index.tcl 24 Feb 2005 18:03:05 -0000 1.13 @@ -47,7 +47,7 @@ # view switch in live | archived news if { [string equal "live" $view] } { - set title "[_ news.News]" + set title [apm_instance_name_from_id $package_id] set view_clause [db_map view_clause_live] if { [db_string archived_p " @@ -63,7 +63,7 @@ } else { - set title "[_ news.News_Archive]" + set title [apm_instance_name_from_id $package_id] set view_clause [db_map view_clause_archived] if { [db_string live_p " @@ -104,13 +104,13 @@ if { $count < [expr $start + $max_dspl] } { set next_start "" } else { - set next_start "[_ news.next]" + set next_start "Next" } if { $start == 1 } { set prev_start "" } else { - set prev_start "[_ news.prev]" + set prev_start "Previous" } if { ![empty_string_p $next_start] && ![empty_string_p $prev_start] } { Index: openacs-4/packages/news/www/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/index.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/news/www/index.xql 18 Nov 2002 17:59:10 -0000 1.2 +++ openacs-4/packages/news/www/index.xql 24 Feb 2005 18:03:05 -0000 1.3 @@ -8,6 +8,7 @@ select item_id, package_id, publish_title, + publish_lead, to_char(news_items_approved.publish_date, 'YYYY-MM-DD HH24:MI:SS') as publish_date_ansi from news_items_approved where $view_clause Index: openacs-4/packages/news/www/item-create-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create-3-oracle.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/item-create-3-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 +++ openacs-4/packages/news/www/item-create-3-oracle.xql 24 Feb 2005 18:03:05 -0000 1.2 @@ -9,6 +9,7 @@ begin :1 := news.new( title => :publish_title, +lead => :publish_lead, publish_date => :publish_date_ansi, archive_date => :archive_date_ansi, mime_type => :mime_type, Index: openacs-4/packages/news/www/item-create-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create-3-postgresql.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/item-create-3-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 +++ openacs-4/packages/news/www/item-create-3-postgresql.xql 24 Feb 2005 18:03:05 -0000 1.2 @@ -1,7 +1,9 @@ + + - postgresql7.1 + postgresql7.3 @@ -22,7 +24,8 @@ null, -- p_relation_tag :creation_ip, -- p_creation_ip :user_id, -- p_creation_user - :live_revision_p -- p_is_live_p + :live_revision_p, -- p_is_live_p + :publish_lead -- p_lead ); Index: openacs-4/packages/news/www/item-create-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create-3.tcl,v diff -u -r1.7 -r1.8 --- openacs-4/packages/news/www/item-create-3.tcl 24 Feb 2005 13:33:23 -0000 1.7 +++ openacs-4/packages/news/www/item-create-3.tcl 24 Feb 2005 18:03:05 -0000 1.8 @@ -4,21 +4,29 @@ Final insert into database to create a news item (no double-click protection, see bboard for discussion) + @author stefan@arsdigita.com @creation-date 2000-12-14 @cvs-id $Id$ - } { - publish_title:notnull publish_body:notnull,allhtml,trim + {publish_lead {}} {publish_date_ansi:trim "[db_null]"} {archive_date_ansi:trim "[db_null]"} html_p:notnull permanent_p:notnull - + imgfile:optional +} -validate { + imgfile_valid { + if { [exists_and_not_null imgfile] + && ![ImageMagick::validate_tmp_file $imgfile] } { + ad_complain + } + } +} -errors { + imgfile_valid {Image file invalid} } -properties { - title:onevalue context:onevalue } @@ -72,22 +80,7 @@ # do insert: unfortunately the publish_body cannot be supplied through the PL/SQL function # we therefore have to do this in a second step -set news_id [db_exec_plsql create_news_item " -begin -:1 := news.new( -title => :publish_title, -publish_date => :publish_date_ansi, -archive_date => :archive_date_ansi, -mime_type => :mime_type, -package_id => :package_id, -approval_user => :approval_user, -approval_date => :approval_date, -approval_ip => :approval_ip, -creation_ip => :creation_ip, -creation_user => :user_id, -is_live_p => :live_revision_p -); -end;"] +set news_id [db_exec_plsql create_news_item {}] # # RAL: For postgres, we need NOT store the data in a blob. The @@ -102,6 +95,17 @@ returning content into :1" -blobs [list $publish_body] } +# if an image is specified, we add it here. +if {[exists_and_not_null imgfile]} { + # ImageMagick package will check its tmp directory for the file, so no + # need to expand the path. + db_1row item { + select item_id from cr_revisions where revision_id = :news_id + } + ImageMagick::util::create_image_item -file $imgfile -parent_id $item_id + ImageMagick::delete_tmp_file $imgfile +} + #update RSS if it is enabled if { !$news_admin_p } { Index: openacs-4/packages/news/www/item-create.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create.adp,v diff -u -r1.8 -r1.9 --- openacs-4/packages/news/www/item-create.adp 22 Oct 2003 14:57:35 -0000 1.8 +++ openacs-4/packages/news/www/item-create.adp 24 Feb 2005 18:03:05 -0000 1.9 @@ -3,65 +3,41 @@ @title;noquote@ news.publish_title -

#news.lt_Use_the_following_for# #news.lt_red_fields_are_requir##news.lt_When_youre_done_click# +

Use the following form to define your news item.Note that the fields marked with * are required. +When you're done click 'Preview' to see how the news item will look and to choose an image for the article.

- - - - - - +

*

+

- - - - +

+

- - - - +

*

+


+
 +


 +#news.The_text_is_formatted_as#   + checked>   + checked> +

- - - - - - - - - +

+

@publish_date_select;noquote@

- - - - +

+

@archive_date_select;noquote@
+ checked> #news.show_it_permanently#

+

- - - - -
#news.Title#
#news.Body#
- - - -
#news.or_upload_text_file#
-
#news.The_text_is_formatted_as#   - #news.Plain_text#  - #news.HTML# -
#news.Release_Date#@publish_date_select;noquote@
#news.Archive_Date#@archive_date_select;noquote@
- - #news.never# #news.show_it_permanently#
+

-

- -

+

Index: openacs-4/packages/news/www/item-create.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item-create.tcl,v diff -u -r1.3 -r1.4 --- openacs-4/packages/news/www/item-create.tcl 29 Oct 2002 08:01:14 -0000 1.3 +++ openacs-4/packages/news/www/item-create.tcl 24 Feb 2005 18:03:05 -0000 1.4 @@ -10,14 +10,19 @@ @cvs-id $Id$ } { + {publish_title {}} + {publish_lead {}} + {publish_body:allhtml {}} + {html_p {}} + {publish_date_ansi {now}} + {archive_date_ansi {}} + {permanent_p {}} } -properties { - title:onevalue context:onevalue publish_date_select:onevalue archive_date_select:onevalue immediate_approve_p:onevalue - } # Authorization by news_create @@ -36,9 +41,13 @@ set title "[_ news.Create_News_Item]" set context [list $title] -set proj_archival_date [db_string week "select sysdate + [ad_parameter ActiveDays "news" 14] from dual"] +if { ![empty_string_p $archive_date_ansi] } { + set proj_archival_date $archive_date_ansi +} else { + set proj_archival_date [db_string week "select sysdate + [ad_parameter ActiveDays news 14] from dual"] +} -set publish_date_select [dt_widget_datetime -default now publish_date days] +set publish_date_select [dt_widget_datetime -default $publish_date_ansi publish_date days] set archive_date_select [dt_widget_datetime -default $proj_archival_date archive_date days] ad_return_template Index: openacs-4/packages/news/www/item-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/item-oracle.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/news/www/item-oracle.xql 30 Nov 2002 17:39:19 -0000 1.3 +++ openacs-4/packages/news/www/item-oracle.xql 24 Feb 2005 18:03:05 -0000 1.4 @@ -8,6 +8,7 @@ select item_id, live_revision, publish_title, + publish_lead, html_p, publish_date, '' || item_creator || '' as creator_link Index: openacs-4/packages/news/www/item-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/item-postgresql.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/news/www/item-postgresql.xql 30 Nov 2002 17:39:19 -0000 1.2 +++ openacs-4/packages/news/www/item-postgresql.xql 24 Feb 2005 18:03:05 -0000 1.3 @@ -8,6 +8,7 @@ select item_id, live_revision, publish_title, + publish_lead, html_p, publish_date, publish_body, Index: openacs-4/packages/news/www/item.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item.adp,v diff -u -r1.10 -r1.11 --- openacs-4/packages/news/www/item.adp 2 Feb 2005 23:30:49 -0000 1.10 +++ openacs-4/packages/news/www/item.adp 24 Feb 2005 18:03:05 -0000 1.11 @@ -5,13 +5,15 @@ -

#news.lt_Could_not_find_the_re#

+

#news.lt_Could_not_find_the_re#

 Index: openacs-4/packages/news/www/item.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/item.tcl,v diff -u -r1.11 -r1.12 --- openacs-4/packages/news/www/item.tcl 2 Feb 2005 23:30:49 -0000 1.11 +++ openacs-4/packages/news/www/item.tcl 24 Feb 2005 18:03:05 -0000 1.12 @@ -16,8 +16,10 @@ context:onevalue item_exist_p:onevalue publish_title:onevalue + publish_lead:onevalue publish_date:onevalue publish_body:onevalue + publish_image:onevalue html_p:onevalue creator_link:onevalue comments:onevalue @@ -38,6 +40,7 @@ select item_id, live_revision, publish_title, + publish_lead, html_p, publish_date, '' || item_creator || '' as creator_link @@ -81,6 +84,14 @@ set comments "" } + # get image info, if any + set image_id [news_get_image_id $item_id] + if {![empty_string_p $image_id]} { + set publish_image "image/$image_id" + } else { + set publish_image {} + } + if {[permission::permission_p -object_id $item_id -privilege write] } { set edit_link "Revise" } else { Index: openacs-4/packages/news/www/news.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/news.adp,v diff -u -r1.5 -r1.6 --- openacs-4/packages/news/www/news.adp 2 Feb 2005 23:30:49 -0000 1.5 +++ openacs-4/packages/news/www/news.adp 24 Feb 2005 18:03:05 -0000 1.6 @@ -1,15 +1,15 @@ -<% # This is the default template to render news items in admin/. Comments are not shown here %> -@publish_title@ +This is the default template to render news items in admin/. Comments are not shown here +

@publish_title@

 +News image +

@publish_lead@

 +
@publish_body;noquote@
+

#news.Contributed_by# @creator_link;noquote@

-
@publish_body;noquote@
-

#news.Contributed_by# @creator_link;noquote@

- - Index: openacs-4/packages/news/www/preview.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/preview.adp,v diff -u -r1.7 -r1.8 --- openacs-4/packages/news/www/preview.adp 22 May 2003 15:03:51 -0000 1.7 +++ openacs-4/packages/news/www/preview.adp 24 Feb 2005 18:03:05 -0000 1.8 @@ -2,12 +2,10 @@ @context;noquote@ @title;noquote@ +

#news.lt_Your_news_item_will_b#

-

    -
  • #news.lt_Your_news_item_will_b# -
  • #news.It_will_go_live_on# @publish_date_pretty@. -
  • + #news.It_will_go_live_on# @publish_date_pretty@. #news.lt_And_be_live_until_rev# @@ -16,25 +14,34 @@ -
  • #news.lt_It_will_go_live_after# + #news.lt_It_will_go_live_after# -
  • #news.lt_To_the_readers_it_wil# -
-

+

+ #news.lt_To_the_readers_it_wil# +

+
+ @image_vars;noquote@ + +
+

@form_action;noquote@ @hidden_vars;noquote@ -

-
+ + @edit_action;noquote@ @image_vars;noquote@ + +

Index: openacs-4/packages/news/www/preview.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/preview.tcl,v diff -u -r1.11 -r1.12 --- openacs-4/packages/news/www/preview.tcl 13 Jan 2005 13:58:27 -0000 1.11 +++ openacs-4/packages/news/www/preview.tcl 24 Feb 2005 18:03:05 -0000 1.12 @@ -1,5 +1,4 @@ # /packages/news/www/preview.tcl - ad_page_contract { This page previews the input from item-create or admin/revision-add @@ -11,6 +10,7 @@ {item_id:integer ""} action:notnull,trim publish_title:notnull,trim + {publish_lead {}} {publish_body:allhtml,trim ""} {revision_log: ""} html_p:notnull,trim @@ -19,11 +19,15 @@ {publish_date:array ""} {archive_date:array ""} {permanent_p: "f"} - + publish_date_ansi:optional + archive_date_ansi:optional + imgfile:optional + } -errors { publish_title:notnull "[_ news.lt_Please_supply_the_tit]" publish_body:notnull "[_ news.lt_Please_supply_the_bod]" + img_file_valid "[_ news.image_file_is_invalid]" } -validate { @@ -36,7 +40,7 @@ } } } - + check_revision_log -requires {action revision_log} { if { ![string match $action "News Item"] && [empty_string_p $revision_log]} { ad_complain "[_ news.lt_You_must_supply_a_rev]" @@ -51,34 +55,40 @@ ad_complain "[_ news.lt_Publish_body_is_missi]" return } elseif { ![empty_string_p $publish_body] && $file_size > 0 } { - ad_complain "[_ news.lt_Cant_upload_a_publica]" + ad_complain "You can either upload a news item or enter text in the box provided, but not both." return - } + } } max_size -requires {text_file.tmpfile text_file} { set b [file size ${text_file.tmpfile}] - + set b_max [expr 1000*[ad_parameter MaxFileSizeKb "news" 1024]] if { $b > $b_max } { ad_complain "[_ news.lt_Your_document_is_larg] ([util_commify_number $b_max] [_ news.bytes])" return } } + img_file_valid -requires {imgfile} { + if {![ImageMagick::validate_tmp_file $imgfile]} { ad_complain } + } } -properties { - + title:onevalue context:onevalue publish_title:onevalue + publish_lead:onevalue publish_body:onevalue publish_location:onevalue hidden_vars:onevalue permanent_p:onevalue html_p:onevalue news_admin_p:onevalue form_action:onevalue + image_url:onevalue + edit_action:onevalue } set user_id [ad_maybe_redirect_for_registration] @@ -96,12 +106,59 @@ } set context [list $title] +# create a new revision of the image if we've come back from the image-choose +# page and we are revising +if {[exists_and_not_null item_id] && [info exists imgfile]} { + # check user has admin privileges (we can only get here from + # admin/revision-add, so all legit users will have admin on package) + permission::require_permission \ + -object_id [ad_conn package_id] -privilege news_admin + + if {[db_0or1row img_item_id {}]} { + # add a revision to the existing image item + ImageMagick::util::revise_image -file $imgfile -item_id $img_item_id + } else { + # create a new image item + ImageMagick::util::create_image_item -file $imgfile -parent_id $item_id + } + # delete the tmpfile + ImageMagick::delete_tmp_file $imgfile +} + + +# set up image path +if {[exists_and_not_null item_id]} { + set image_id [news_get_image_id $item_id] + if { ![empty_string_p $image_id] } { + set publish_image "image/$image_id" + } else { + set publish_image {} + } + set img_file {} +} elseif {[info exists imgfile]} { + set publish_image "image-view-tmpfile/$imgfile" +} else { + set publish_image {} + set imgfile {} +} + +# if we've come back from the image page, set up dates again +if {[info exists publish_date_ansi] && [info exists archive_date_ansi]} { + set exp {([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})} + if { ![regexp $exp $publish_date_ansi match \ + publish_date(year) publish_date(month) publish_date(day)] + || ![regexp $exp $archive_date_ansi match \ + archive_date(year) archive_date(month) archive_date(day)] } { + ad_return_complaint 1 {
  • Publish/archive dates incorrect
    • } + } +} + # deal with Dates, granularity is 'day' # with news_admin privilege fill in publish and archive dates if { $news_admin_p == 1 } { - + if { [info exists publish_date(year)] && [info exists publish_date(month)] && [info exists publish_date(day)] } { set publish_date_ansi "$publish_date(year)-$publish_date(month)-$publish_date(day)" } else { @@ -122,11 +179,12 @@ if { [dt_interval_check $archive_date_ansi $publish_date_ansi] >= 0 } { ad_return_error "[_ news.Scheduling_Error]" \ - "[_ news.lt_The_archive_date_must]" - return - } -} + "[_ news.lt_The_archive_date_must]" + return + } +} + # if uploaded file, read it into publish_body and massage it if {[info exists file_size]} { if { $file_size > 0 } { @@ -135,23 +193,36 @@ # close any open HTML tags in any case set publish_body [util_close_html_tags $publish_body] + + set errors [ad_html_security_check $publish_body] + ns_log Notice "errors: $errors" + if { ![empty_string_p $errors] } { + ad_return_complaint 1 $errors + } } if { [string match $action "News Item"] } { # form variables for confirmation step - set hidden_vars [export_form_vars publish_title publish_body \ - publish_date_ansi archive_date_ansi html_p permanent_p] + set hidden_vars [export_form_vars publish_title publish_lead publish_body \ + publish_date_ansi archive_date_ansi html_p permanent_p imgfile] + set image_vars [export_form_vars publish_title publish_lead publish_body \ + publish_date_ansi archive_date_ansi html_p \ + permanent_p action] set form_action "
    " - + set edit_action "" + } else { - - # Form vars to carry through Confirmation Page - set hidden_vars [export_form_vars item_id revision_log publish_title publish_body\ - publish_date_ansi archive_date_ansi permanent_p html_p] - set form_action "" + # Form vars to carry through Confirmation Page + set hidden_vars [export_form_vars item_id revision_log publish_title publish_lead publish_body \ + publish_date_ansi archive_date_ansi permanent_p html_p imgfile] + set image_vars [export_form_vars publish_title publish_lead publish_body \ + publish_date_ansi archive_date_ansi html_p \ + permanent_p action item_id revision_log] + set form_action "" + set edit_action "" } # creator link Index: openacs-4/packages/news/www/preview.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/Attic/preview.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/preview.xql 15 Oct 2001 13:41:03 -0000 1.1 +++ openacs-4/packages/news/www/preview.xql 24 Feb 2005 18:03:05 -0000 1.2 @@ -10,5 +10,9 @@ + +SELECT item_id AS img_item_id FROM cr_items +WHERE content_type = 'image' AND parent_id = :item_id + Index: openacs-4/packages/news/www/admin/approve.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/approve.adp,v diff -u -r1.7 -r1.8 --- openacs-4/packages/news/www/admin/approve.adp 23 Aug 2003 22:25:11 -0000 1.7 +++ openacs-4/packages/news/www/admin/approve.adp 24 Feb 2005 18:03:05 -0000 1.8 @@ -9,7 +9,13 @@ #news.Author# - @items.publish_title@ + + + + + + + @items.publish_title@ @items.creation_date@ @items.item_creator@ @@ -24,25 +30,18 @@ @hidden_vars;noquote@ - - - - - - - - - -
    #news.Publication_Date#@publish_date_select;noquote@
    #news.Archive_Date#@archive_date_select;noquote@
    - - #news.never# #news.show_it_permanently#
    +

    +

    @publish_date_select;noquote@

    +

    +

    @archive_date_select;noquote@
    + #news.show_it_permanently#

    + +

    -

    -
    - +

    Index: openacs-4/packages/news/www/admin/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/index.adp,v diff -u -r1.13 -r1.14 --- openacs-4/packages/news/www/admin/index.adp 24 Feb 2005 13:33:23 -0000 1.13 +++ openacs-4/packages/news/www/admin/index.adp 24 Feb 2005 18:03:05 -0000 1.14 @@ -33,43 +33,33 @@ - + - + - - @news_items.item_id@ - @news_items.publish_title@ (#news.rev# @news_items.revision_no@) [#news.revise#] + + @news_items.item_id@ + @news_items.publish_title@ (#news.rev# @news_items.revision_no@) #news.revise# @news_items.item_creator@ - @news_items.publish_date_pretty@ - @news_items.archive_date_pretty@ + @news_items.publish_date_pretty@ + @news_items.archive_date_pretty@ @news_items.pretty_status@ - - - - - #news.lt_Click_on_item_ID_to_a#
    - #news.lt_Click_on_revise_to_ed# - - - - + +

    #news.lt_Do_the_following_to_t# +

    - - -
    Index: openacs-4/packages/news/www/admin/item.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/item.adp,v diff -u -r1.6 -r1.7 --- openacs-4/packages/news/www/admin/item.adp 23 Aug 2003 22:25:11 -0000 1.6 +++ openacs-4/packages/news/www/admin/item.adp 24 Feb 2005 18:03:05 -0000 1.7 @@ -2,11 +2,11 @@ @context;noquote@ @title;noquote@ -

    -#news.Add_a_new_revision# - +

    #news.Add_a_new_revision#

    + +
    @@ -18,17 +18,17 @@ - + - + - - - + -
    Revision # #news.Active_Revision#
    <%= [expr @item:rowcount@ - @item.rownum@ +1] %> + #news.active# @@ -38,11 +38,11 @@ + @item.publish_title@@item.item_creator@@item.item_creator@ @item.log_entry@@item.status@ + @item.status@ | #news.approve# Index: openacs-4/packages/news/www/admin/news.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/news.adp,v diff -u -r1.3 -r1.4 --- openacs-4/packages/news/www/admin/news.adp 23 Aug 2003 22:25:11 -0000 1.3 +++ openacs-4/packages/news/www/admin/news.adp 24 Feb 2005 18:03:05 -0000 1.4 @@ -3,6 +3,8 @@

    @publish_title@

    +

    @publish_lead@

     + @publish_body;noquote@

    #news.Contributed_by# @creator_link;noquote@ Index: openacs-4/packages/news/www/admin/process-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/process-2-postgresql.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/process-2-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 +++ openacs-4/packages/news/www/admin/process-2-postgresql.xql 24 Feb 2005 18:03:05 -0000 1.2 @@ -19,7 +19,7 @@ - select (last_day(current_timestamp)+1)::date + select (last_day(current_timestamp)+'1 day')::date Index: openacs-4/packages/news/www/admin/revision-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add-3-oracle.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/revision-add-3-oracle.xql 15 Oct 2001 13:41:03 -0000 1.1 +++ openacs-4/packages/news/www/admin/revision-add-3-oracle.xql 24 Feb 2005 18:03:05 -0000 1.2 @@ -11,6 +11,7 @@ item_id => :item_id, publish_date => :publish_date_ansi, title => :publish_title, + lead => :publish_lead, description => :revision_log, mime_type => :mime_type, package_id => [ad_conn package_id], Index: openacs-4/packages/news/www/admin/revision-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add-3-postgresql.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/news/www/admin/revision-add-3-postgresql.xql 15 Oct 2001 13:41:03 -0000 1.1 +++ openacs-4/packages/news/www/admin/revision-add-3-postgresql.xql 24 Feb 2005 18:03:05 -0000 1.2 @@ -21,7 +21,8 @@ current_timestamp, -- p_creation_date :creation_ip, -- p_creation_ip :creation_user, -- p_creation_user - :active_revision_p -- p_make_active_revision_p + :active_revision_p, -- p_make_active_revision_p + :publish_lead -- p_lead ); Index: openacs-4/packages/news/www/admin/revision-add-3.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add-3.tcl,v diff -u -r1.5 -r1.6 --- openacs-4/packages/news/www/admin/revision-add-3.tcl 29 Oct 2002 08:01:15 -0000 1.5 +++ openacs-4/packages/news/www/admin/revision-add-3.tcl 24 Feb 2005 18:03:05 -0000 1.6 @@ -12,6 +12,7 @@ } { item_id:integer publish_title:notnull + publish_lead publish_body:notnull,allhtml,trim html_p:notnull revision_log:notnull Index: openacs-4/packages/news/www/admin/revision-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/revision-add-oracle.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/news/www/admin/revision-add-oracle.xql 16 Oct 2001 10:10:19 -0000 1.2 +++ openacs-4/packages/news/www/admin/revision-add-oracle.xql 24 Feb 2005 18:03:05 -0000 1.3 @@ -11,6 +11,7 @@ package_id, revision_id, publish_title, + publish_lead, html_p, publish_date, NVL(archive_date, sysdate+[ad_parameter ActiveDays "news" 14]) as archive_date, Index: openacs-4/packages/news/www/admin/revision-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/Attic/revision-add-postgresql.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/news/www/admin/revision-add-postgresql.xql 23 Aug 2003 22:16:24 -0000 1.3 +++ openacs-4/packages/news/www/admin/revision-add-postgresql.xql 24 Feb 2005 18:03:05 -0000 1.4 @@ -11,6 +11,7 @@ package_id, revision_id, publish_title, + publish_lead, html_p, to_char(publish_date, 'YYYY-MM-DD') as publish_date, publish_body, Index: openacs-4/packages/news/www/admin/revision-add.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add.adp,v diff -u -r1.8 -r1.9 --- openacs-4/packages/news/www/admin/revision-add.adp 23 Aug 2003 22:25:11 -0000 1.8 +++ openacs-4/packages/news/www/admin/revision-add.adp 24 Feb 2005 18:03:05 -0000 1.9 @@ -3,73 +3,52 @@ @title;noquote@ -

    #news.lt_Use_the_following_for# #news.lt_red_fields_are_requir##news.lt_When_youre_done_click# -

    +

    Use the following form to define your news item.Note that the fields marked with * are required. +When you're done click 'Preview' to see how the news item will look and to choose an image for the article.

    -
    + @hidden_vars;noquote@ - - - - - - - - - +

    *

    +

    - - - - +

    +

    - - - - +

    - - - - +

    Image

    +

    + [use 'preview' to revise image] +

    - - - - +

    #news.Release_Date#

    +

    @publish_date_select;noquote@

    - - - +

    #news.Archive_Date#

    +

    @archive_date_select;noquote@
    + #news.show_it_permanently#

    +

    *

    +

    - - - - -
    #news.Title#
    #news.Body#
    - - - -
    #news.or_upload_text_file#
    -
    #news.The_text_is_formatted_as# +

    *

    +


    +
     +


     +#news.The_text_is_formatted_as#   - #news.Plain_text#  - #news.HTML# +   + - #news.Plain_text#  - #news.HTML# +   + -

    #news.Release_Date#@publish_date_select;noquote@
    #news.Archive_Date#@archive_date_select;noquote@
    - @never_checkbox;noquote@ - #news.never# #news.show_it_permanently#
    #news.Revision_log#
    -
    - -
    -

    + +

    +

    Index: openacs-4/packages/news/www/admin/revision-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-add.tcl,v diff -u -r1.5 -r1.6 --- openacs-4/packages/news/www/admin/revision-add.tcl 29 Oct 2002 08:01:15 -0000 1.5 +++ openacs-4/packages/news/www/admin/revision-add.tcl 24 Feb 2005 18:03:05 -0000 1.6 @@ -21,6 +21,7 @@ publish_date:onevalue publish_date_desc:onevalue publish_title:onevalue + publish_lead:onevalue publish_body:onevalue html_p:onevalue archive_date:onevalue @@ -38,7 +39,7 @@ where item_id = :item_id } -set title "[_ news.lt_One_Item_-_add_revisi]" +set title "Add revision" set context [list $title] # get active revision of news item @@ -48,6 +49,7 @@ package_id, revision_id, publish_title, + publish_lead, html_p, publish_date, NVL(archive_date, sysdate+[ad_parameter ActiveDays "news" 14]) as archive_date, @@ -83,6 +85,8 @@ set action "[_ news.Revision]" set hidden_vars [export_form_vars item_id action] +set image_id [news_get_image_id $item_id] +if {![empty_string_p $image_id]} { set image_url "../image/$image_id" } ad_return_template Index: openacs-4/packages/news/www/admin/revision-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-oracle.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/news/www/admin/revision-oracle.xql 30 Nov 2002 17:39:28 -0000 1.3 +++ openacs-4/packages/news/www/admin/revision-oracle.xql 24 Feb 2005 18:03:05 -0000 1.4 @@ -10,6 +10,7 @@ revision_id, content_revision.get_number(:revision_id) as revision_no, publish_title, + publish_lead, html_p, publish_date, archive_date, Index: openacs-4/packages/news/www/admin/revision-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision-postgresql.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/news/www/admin/revision-postgresql.xql 30 Nov 2002 17:39:28 -0000 1.2 +++ openacs-4/packages/news/www/admin/revision-postgresql.xql 24 Feb 2005 18:03:05 -0000 1.3 @@ -10,6 +10,7 @@ revision_id, content_revision__get_number(:revision_id) as revision_no, publish_title, + publish_lead, html_p, publish_date, archive_date, Index: openacs-4/packages/news/www/admin/revision.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision.adp,v diff -u -r1.6 -r1.7 --- openacs-4/packages/news/www/admin/revision.adp 23 Aug 2003 22:25:11 -0000 1.6 +++ openacs-4/packages/news/www/admin/revision.adp 24 Feb 2005 18:03:05 -0000 1.7 @@ -4,24 +4,22 @@ -

    #news.lt_Could_not_find_corres#

    +

    #news.lt_Could_not_find_corres#

    -

      -
    • #news.Author#: @creator_link;noquote@ -
    • #news.Revision_number#: @revision_no@ -
    • #news.Creation_Date#: @creation_date@ -
    • #news.Creation_IP#: @creation_ip@ -
    • #news.Release_Date#: @publish_date@ -
    • #news.Archive_Date#: @archive_date@ -
    + #news.Author#: @creator_link;noquote@
    + #news.Revision_number#: @revision_no@
    + #news.Creation_Date#: @creation_date@
    + #news.Creation_IP#: @creation_ip@
    + #news.Release_Date#: @publish_date@
    + #news.Archive_Date#: @archive_date@ +

    -

    -
    - Index: openacs-4/packages/news/www/admin/revision.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/revision.tcl,v diff -u -r1.6 -r1.7 --- openacs-4/packages/news/www/admin/revision.tcl 23 Aug 2003 22:25:11 -0000 1.6 +++ openacs-4/packages/news/www/admin/revision.tcl 24 Feb 2005 18:03:05 -0000 1.7 @@ -19,6 +19,7 @@ news_admin_p:onevalue item_exist_p:onevalue publish_title:onevalue + publish_lead:onevalue publish_body:onevalue html_p:onevalue creator_link:onevalue @@ -34,6 +35,7 @@ revision_id, content_revision.get_number(:revision_id) as revision_no, publish_title, + publish_lead, html_p, publish_date, archive_date, @@ -57,10 +59,17 @@ } # text-only body - if {[info exists html_p] && ![string equal $html_p "t"]} { - set publish_body "

    [ad_quotehtml $publish_body]
    " + # + # replaced this with code from /packages/news/www/item.tcl + # + #if {[info exists html_p] && ![string equal $html_p "t"]} { + # set publish_body "[ad_quotehtml $publish_body]" + #} + if {[info exists html_p] && [string equal $html_p "f"]} { + set publish_body [ad_text_to_html -- $publish_body] } - set title "[_ news.One_Revision]" + + set title "Revision" set context [list [list "item?[export_vars -url item_id]" [_ news.One_Item]] $title] } else {