Index: openacs-4/packages/news/sql/oracle/upgrade/upgrade-5.5.1d1-5.6.0d1.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/upgrade/upgrade-5.5.1d1-5.6.0d1.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/news/sql/oracle/upgrade/upgrade-5.5.1d1-5.6.0d1.sql	23 Dec 2009 20:53:22 -0000	1.1
@@ -0,0 +1,205 @@
+--
+--  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,
+    cr.content as publish_body,
+    cr.mime_type as publish_format,
+    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,
+    cr.content as publish_body,
+    cr.mime_type as publish_format,
+    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,
+    cr.content as publish_body,
+    cr.publish_date,
+    cn.archive_date,
+    cr.description as log_entry,
+    cr.mime_type as publish_format,
+    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,
+    cr.content as publish_body,
+    cr.mime_type as publish_format,
+    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/postgresql/upgrade/upgrade-5.5.1d1-5.6.0d1.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.5.1d1-5.6.0d1.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.5.1d1-5.6.0d1.sql	23 Dec 2009 20:53:22 -0000	1.1
@@ -0,0 +1,207 @@
+drop view news_items_approved;
+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,
+    cr.mime_type as publish_format,
+    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
+--
+drop view news_items_live_or_submitted;
+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,
+    cr.mime_type as publish_format,
+    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 
+drop view news_items_unapproved;
+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
+--
+drop view news_item_revisions;
+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,
+    cr.mime_type as publish_format,
+    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
+drop view news_item_unapproved;
+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
+--
+drop view news_item_full_active;
+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,
+    cr.mime_type as publish_format,
+    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;