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.7 -r1.8
--- openacs-4/packages/news/sql/oracle/news-create.sql 30 Sep 2003 12:10:08 -0000 1.7
+++ openacs-4/packages/news/sql/oracle/news-create.sql 6 Oct 2003 12:11:23 -0000 1.8
@@ -248,7 +248,8 @@
function status (
- news_id in cr_news.news_id%TYPE
+ publish_date in cr_revisions.publish_date%TYPE,
+ archive_date in cr_news.archive_date%TYPE
) return varchar2;
@@ -540,42 +541,27 @@
-- 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 (
- news_id in cr_news.news_id%TYPE
+ publish_date in cr_revisions.publish_date%TYPE,
+ archive_date in cr_news.archive_date%TYPE
) return varchar2
is
- v_archive_date date;
- v_publish_date date;
begin
- -- populate variables
- select archive_date into v_archive_date
- from cr_news
- where news_id = news.status.news_id;
- --
- select publish_date into v_publish_date
- from cr_revisions
- where revision_id = news.status.news_id;
-
- -- if publish_date is not null the item is approved, otherwise it is not
- if v_publish_date is not null then
- if v_publish_date > sysdate then
+
+ if publish_date is not null then
+ if publish_date > sysdate then
-- to be published (2 cases)
- -- archive date could be null if it has not been decided when to archive
- if v_archive_date is null then
- return 'going live in ' ||
- round(to_char(v_publish_date - sysdate),1) || ' days';
+ if archive_date is null then
+ return 'going_live_no_archive';
else
- return 'going live in ' ||
- round(to_char(v_publish_date - sysdate),1) || ' days' ||
- ', archived in ' || round(to_char(v_archive_date - sysdate),1) || ' days';
+ return 'going_live_with_archive';
end if;
else
-- already released or even archived (3 cases)
- if v_archive_date is null then
- return 'published, not scheduled for archive';
+ if archive_date is null then
+ return 'published_no_archive';
else
- if v_archive_date - sysdate > 0 then
- return 'published, archived in ' ||
- round(to_char(v_archive_date - sysdate),1) || ' days';
+ if archive_date - sysdate > 0 then
+ return 'published_with_archive';
else
return 'archived';
end if;
@@ -820,7 +806,7 @@
ps.first_names || ' ' || ps.last_name as item_creator,
ao.creation_date,
ci.live_revision,
- news.status(cn.news_id) as status
+ news.status(cr.publish_date, cn.archive_date) as status
from
cr_items ci,
cr_revisions cr,
@@ -885,9 +871,12 @@
cr.mime_type as mime_type,
cn.package_id,
ao.creation_date as creation_date,
- news.status(news_id) as status,
- case when exists (select 1 from cr_news where news_id = revision_id
- and news.status(news_id) = 'unapproved') then 1 else 0 end
+ 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,
@@ -941,7 +930,7 @@
decode(cr.mime_type,'text/html','t','f') as html_p,
cr.publish_date,
cn.archive_date,
- news.status(cr.revision_id) as status,
+ 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
Index: openacs-4/packages/news/sql/oracle/upgrade/upgrade-4.6-5.0d1.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/upgrade/upgrade-4.6-5.0d1.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/news/sql/oracle/upgrade/upgrade-4.6-5.0d1.sql 6 Oct 2003 12:11:23 -0000 1.1
@@ -0,0 +1,673 @@
+-- Internationalization of publication and archive status. Code to generate
+-- a human readable publish status has been moved from the news__status plsql
+-- function to a Tcl proc.
+--
+-- @author Peter Marklund
+
+--- **** Recreate package to get the new status function.
+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
+
+-- **** Recreate views with calls to new status function
+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);
+
+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;
+
+create or replace 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,
+ 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.14 -r1.15
--- openacs-4/packages/news/sql/postgresql/news-create.sql 28 Aug 2003 09:41:57 -0000 1.14
+++ openacs-4/packages/news/sql/postgresql/news-create.sql 6 Oct 2003 12:11:23 -0000 1.15
@@ -467,65 +467,39 @@
-- 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 (integer)
+create function news__status (timestamptz, timestamptz)
returns varchar as '
declare
- p_news_id alias for $1;
- v_archive_date timestamptz;
- v_publish_date timestamptz;
+ p_publish_date alias for $1;
+ p_archive_date alias for $2;
begin
- -- populate variables
- select archive_date into v_archive_date
- from cr_news
- where news_id = p_news_id;
- --
- select publish_date into v_publish_date
- from cr_revisions
- where revision_id = p_news_id;
-
- -- if publish_date is not null the item is approved, otherwise it is not
- if v_publish_date is not null then
- if v_publish_date > current_timestamp then
- -- to be published (2 cases)
- -- archive date could be null if it has not been decided when to archive
- -- RAL: the nasty ''extract'' code below was the only way I could figure
- -- to get the same result as Oracle (eg, 2.4 days)
- if v_archive_date is null then
- return ''going live in ''
- || to_char(extract(days from (v_publish_date - current_timestamp))
- + extract(hours from (v_publish_date - current_timestamp))/24,''999D9'')
- || '' days'';
+ 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 in ''
- || to_char(extract(days from (v_publish_date - current_timestamp))
- + extract(hours from (v_publish_date - current_timestamp))/24,''999D9'')
- || '' days'' || '', archived in ''
- || to_char(extract(days from (v_archive_date - current_timestamp))
- + extract(hours from (v_archive_date - current_timestamp))/24,''999D9'')
- || '' days'';
+ return ''going_live_with_archive'';
end if;
else
- -- already released or even archived (3 cases)
- if v_archive_date is null then
- return ''published, not scheduled for archive'';
+ -- Published in the past
+ if p_archive_date is null then
+ return ''published_no_archive'';
else
- if v_archive_date - current_timestamp > 0 then
- return ''published, archived in ''
- || to_char(extract(days from (v_archive_date - current_timestamp))
- + extract(hours from (v_archive_date - current_timestamp))/24,''999D9'')
- || '' days'';
+ if p_archive_date > current_timestamp then
+ return ''published_with_archive'';
else
return ''archived'';
end if;
- end if;
- end if;
- else
+ end if;
+ end if;
+ else
+ -- publish_date null
return ''unapproved'';
end if;
end;
' language 'plpgsql';
-
create function news__name (integer)
returns varchar as '
declare
@@ -788,7 +762,7 @@
ps.first_names || ' ' || ps.last_name as item_creator,
ao.creation_date::date as creation_date,
ci.live_revision,
- news__status(cn.news_id) as status
+ news__status(cr.publish_date, cn.archive_date) as status
from
cr_items ci,
cr_revisions cr,
@@ -856,9 +830,12 @@
cr.mime_type as mime_type,
cn.package_id,
ao.creation_date::date as creation_date,
- news__status(news_id) as status,
- case when exists (select 1 from cr_news where news_id = revision_id
- and news__status(news_id) = 'unapproved') then 1 else 0 end
+ 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,
@@ -915,7 +892,7 @@
(case when cr.mime_type = 'text/html' then 't' else 'f' end) as html_p,
cr.publish_date,
cn.archive_date,
- news__status(cr.revision_id) as status,
+ 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
Index: openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.6-5.0d1.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.6-5.0d1.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.6-5.0d1.sql 6 Oct 2003 12:11:23 -0000 1.1
@@ -0,0 +1,145 @@
+-- Internationalization of publication and archive status. Code to generate
+-- a human readable publish status has been moved from the news__status plsql
+-- function to a Tcl proc.
+--
+-- @author Peter Marklund
+
+--- **** Recreate function. This will drop the views as well.
+drop function news__status (integer) cascade;
+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';
+
+-- **** Recreate views with calls to new status function
+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);
+
+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;
+
+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,
+ 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.11 -r1.12
--- openacs-4/packages/news/tcl/news-procs.tcl 3 Oct 2003 15:45:38 -0000 1.11
+++ openacs-4/packages/news/tcl/news-procs.tcl 6 Oct 2003 12:11:23 -0000 1.12
@@ -122,21 +122,28 @@
return $url
}
-ad_proc news_pretty_status_key {
+ad_proc news_pretty_status {
{-publish_date:required}
{-archive_date:required}
+ {-status:required}
} {
- Given the the publish and archive date of a news item, return
- a human readable and localized string explaining the publish and archive status
- of the item. For example, "Published, scheduled to be archived in 5 days"
+ Given the publish status of a news items return a localization human readable
+ sentence for the status.
- @param publish_date The publish date on ANSI format
- @param archive_date The archive date on ANSI format
+ @param status Publish status short name. Valid values are returned
+ by the plsql function news_status.
- @return The message key (package_key.message_key) for the text.
-
@author Peter Marklund
} {
+ array set news_status_keys {
+ unapproved news.Unapproved
+ going_live_no_archive news.going_live_no_archive
+ going_live_with_archive news.going_live_with_archive
+ published_no_archive news.published_no_archive
+ published_with_archive news.published_scheduled_for_archive
+ archived news.Archived
+ }
+
set now_seconds [clock scan now]
if { ![empty_string_p $archive_date] } {
set archive_date_seconds [clock scan $archive_date]
@@ -155,36 +162,9 @@
# Will be published in the future
set n_days_until_publish [expr ($publish_date_seconds - $now_seconds) / 86400]
-
- if { [empty_string_p $archive_date] } {
- # Not scheduled for archive
- # Message with vars n_days_until_publish
- set status_key news.going_live_no_archive
- } else {
- # Scheduled for archive
- # Message with vars n_days_until_publish, n_days_until_archive
- set status_key news.going_live_with_archive
- }
- } else {
- # Has already been published
-
- if { [empty_string_p $archive_date] } {
- # Not scheduled for archive
- set status_key news.published_no_archive
- } elseif { $archive_date_seconds > $now_seconds } {
- # Scheduled for archive
- # Message with vars n_days_until_archive
- set status_key news.published_scheduled_for_archive
- } else {
- # Already archived
- set status_key news.Archived
- }
}
-
- } else {
- # Item has no publish date - it's unapproved
- set status_key news.Unapproved
}
- return $status_key
+ # Message lookup may use vars n_days_until_archive and n_days_until_publis
+ return [_ $news_status_keys($status)]
}
Index: openacs-4/packages/news/tcl/test/news-test-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/tcl/test/news-test-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/news/tcl/test/news-test-procs-oracle.xql 6 Oct 2003 12:11:23 -0000 1.1
@@ -0,0 +1,12 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select news.status(:publish_date, :archive_date) from dual
+
+
+
+
Index: openacs-4/packages/news/tcl/test/news-test-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/tcl/test/news-test-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/news/tcl/test/news-test-procs-postgresql.xql 6 Oct 2003 12:11:23 -0000 1.1
@@ -0,0 +1,12 @@
+
+
+
+ postgresql7.1
+
+
+
+ select news__status(:publish_date, :archive_date);
+
+
+
+
Index: openacs-4/packages/news/tcl/test/news-test-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/tcl/test/news-test-procs.tcl,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/news/tcl/test/news-test-procs.tcl 3 Oct 2003 15:45:39 -0000 1.1
+++ openacs-4/packages/news/tcl/test/news-test-procs.tcl 6 Oct 2003 12:11:23 -0000 1.2
@@ -11,7 +11,8 @@
namespace eval news::test {}
aa_register_case news_pretty_status_key {
- Test the news_pretty_status_key proc.
+ Test the news_pretty_status_key Tcl proc and
+ the news__status PLSQL function.
@author Peter Marklund
} {
@@ -27,44 +28,57 @@
news::test::assert_status_pretty \
-publish_date $future_date \
-archive_date "" \
- -expect_key news.going_live_no_archive
+ -status going_live_no_archive
# Scheduled for publish and archive
news::test::assert_status_pretty \
-publish_date $future_date \
-archive_date $future_date \
- -expect_key news.going_live_with_archive
+ -status going_live_with_archive
# Published, no archive
news::test::assert_status_pretty \
-publish_date $past_date \
-archive_date "" \
- -expect_key news.published_no_archive
+ -status published_no_archive
# Published scheduled archived
news::test::assert_status_pretty \
-publish_date $past_date \
-archive_date $future_date \
- -expect_key news.published_scheduled_for_archive
+ -status published_with_archive
# Published and archived
news::test::assert_status_pretty \
-publish_date $past_date \
-archive_date $past_date \
- -expect_key news.Archived
+ -status archived
# Not scheduled for publish
news::test::assert_status_pretty \
-publish_date "" \
-archive_date "" \
- -expect_key news.Unapproved
+ -status unapproved
}
ad_proc -private news::test::assert_status_pretty {
{-publish_date:required}
{-archive_date:required}
- {-expect_key:required}
+ {-status:required}
} {
- aa_equals "publish_date \"$publish_date\" archive_date \"$archive_date\"" \
- [news_pretty_status_key -publish_date $publish_date -archive_date $archive_date] $expect_key
+ set pretty_status [news_pretty_status -publish_date $publish_date -archive_date $archive_date -status $status]
+ aa_true "publish_date=\"$publish_date\" archive_date=\"$archive_date\" status=\"$status\" pretty_status=\"$pretty_status\"" \
+ [expr ![empty_string_p $pretty_status]]
+
+ set db_news_status [news::test::get_news_status \
+ -publish_date $publish_date \
+ -archive_date $archive_date]
+ aa_equals "publish_date=\"$publish_date\" archive_date=\"$archive_date\"" $db_news_status $status
}
+
+ad_proc -private news::test::get_news_status {
+ {-publish_date:required}
+ {-archive_date:required}
+} {
+ return [db_string select_status {}]
+}
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.10 -r1.11
--- openacs-4/packages/news/www/admin/index.adp 23 Aug 2003 22:25:11 -0000 1.10
+++ openacs-4/packages/news/www/admin/index.adp 6 Oct 2003 12:11:23 -0000 1.11
@@ -41,7 +41,7 @@
| @news_items.item_creator@ |
@news_items.publish_date@ |
@news_items.archive_date@ |
- @news_items.status@ |
+ @news_items.pretty_status@ |
Index: openacs-4/packages/news/www/admin/index.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/www/admin/index.tcl,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/news/www/admin/index.tcl 18 Nov 2002 17:59:12 -0000 1.5
+++ openacs-4/packages/news/www/admin/index.tcl 6 Oct 2003 12:11:23 -0000 1.6
@@ -9,13 +9,10 @@
@cvs-id $Id$
} {
-
{orderby: "item_id"}
{view: "published"}
{column_names:array ""}
-
} -properties {
-
title:onevalue
context:onevalue
view_link:onevalue
@@ -33,7 +30,7 @@
[list view "[_ news.News_Items]" published [list \
[list published "[_ news.Published]" {where "status like 'published%'"}] \
[list unapproved "[_ news.Unapproved]" {where "status = 'unapproved'"}] \
- [list approved "[_ news.Approved]" {where "status like 'going live%'"}] \
+ [list approved "[_ news.Approved]" {where "status like 'going_live%'"}] \
[list archived "[_ news.Archived]" {where "status = 'archived'"}] \
[list all "[_ news.All]" {} ] \
]]
@@ -62,24 +59,14 @@
# administrator sees all news items
-db_multirow -extend { publish_date archive_date } news_items itemlist {} {
+db_multirow -extend { publish_date archive_date pretty_status } news_items itemlist {} {
set publish_date [lc_time_fmt $publish_date_ansi "%x"]
set archive_date [lc_time_fmt $archive_date_ansi "%x"]
+ set pretty_status [news_pretty_status \
+ -publish_date $publish_date \
+ -archive_date $archive_date \
+ -status $status]
}
ad_return_template
-
-
-
-
-
-
-
-
-
-
-
-
-
-