------------------------------------------------------------------------------ -- 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.3 2014/10/27 16:41:47 victorg 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. -- added -- old define_function_args('news__new','item_id;null,locale;null,publish_date;null,text;null,nls_language;null,title;null,mime_type;text/plain,package_id;null,archive_date;null,approval_user;null,approval_date;null,approval_ip;null,relation_tag;null,creation_ip;null,creation_user;null,is_live_p;f,lead;f') -- new select define_function_args('news__new','item_id;null,locale;null,publish_date;null,text;null,nls_language;null,title;null,mime_type;text/plain,package_id;null,archive_date;null,approval_user;null,approval_date;null,approval_ip;null,relation_tag;null,creation_ip;null,creation_user;null,is_live_p;f,lead'); -- -- procedure news__new/17 -- CREATE OR REPLACE FUNCTION news__new( p_item_id integer, -- default null p_locale varchar, -- default null, p_publish_date timestamptz, -- default null p_text text, -- default null p_nls_language varchar, -- default null p_title varchar, -- default null p_mime_type varchar, -- default 'text/plain' p_package_id integer, -- default null, p_archive_date timestamptz, -- default null p_approval_user integer, -- default null p_approval_date timestamptz, -- default null p_approval_ip varchar, -- default null, p_relation_tag varchar, -- default null p_creation_ip varchar, -- default null p_creation_user integer, -- default null p_is_live_p boolean, -- default 'f' p_lead varchar -- default 'f' ) RETURNS integer AS $$ DECLARE -- -- -- -- -- -- 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; -- added select define_function_args('news__revision_new','item_id,publish_date;null,text;null,title,description,mime_type;text/plain,package_id;null,archive_date;null,approval_user;null,approval_date;null,approval_ip;null,creation_date;current_timestamp,creation_ip;null,creation_user;null,make_active_revision_p;f,lead'); -- -- procedure news__revision_new/16 -- CREATE OR REPLACE FUNCTION news__revision_new( p_item_id integer, p_publish_date timestamptz, -- default null p_text text, -- default null p_title varchar, p_description text, p_mime_type varchar, -- default 'text/plain' p_package_id integer, -- default null p_archive_date timestamptz, -- default null p_approval_user integer, -- default null p_approval_date timestamptz, -- default null p_approval_ip varchar, -- default null p_creation_date timestamptz, -- default current_timestamp p_creation_ip varchar, -- default null p_creation_user integer, -- default null p_make_active_revision_p boolean, -- default 'f' p_lead varchar ) RETURNS integer AS $$ DECLARE -- -- -- here goes the revision log -- -- -- 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, 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;