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 -N -r1.6 -r1.6.2.1 --- openacs-4/packages/news/sql/postgresql/news-package-create.sql 27 Oct 2014 16:41:47 -0000 1.6 +++ openacs-4/packages/news/sql/postgresql/news-package-create.sql 25 Sep 2015 08:25:57 -0000 1.6.2.1 @@ -8,13 +8,10 @@ -- *** PACKAGE NEWS, plsql to create content_item *** --- old define_function_args ('news__new','item_id,locale,publish_date,text,nls_language,title,mime_type;text/plain,package_id,archive_date,approval_user,approval_date,approval_ip,relation_tag,creation_ip,creation_user,is_live_p;f,lead') --- 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'); +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 -- @@ -42,7 +39,6 @@ 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; @@ -59,11 +55,10 @@ else v_id := p_item_id; end if; - -- + v_name := 'news-' || to_char(current_timestamp,'YYYYMMDD') || '-' || v_id; - -- v_log_string := 'initial submission'; - -- + v_item_id := content_item__new( v_name, -- name v_parent_id, -- parent_id @@ -73,34 +68,32 @@ p_creation_user, -- creation_user p_package_id, -- context_id p_creation_ip, -- creation_ip - 'content_item', -- item_subtype - 'news', -- content_type + 'content_item', -- item_subtype + 'news', -- content_type p_title, -- title - null, -- description + v_log_string, -- description p_mime_type, -- mime_type p_nls_language, -- nls_language null, -- text - null, -- data + p_text, -- data null, -- relation_tag p_is_live_p, -- live_p - 'text', -- storage_type + 'text', -- storage_type p_package_id -- package_id ); - 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 - ); + -- + -- get the newly created revision_id as news_id + -- + v_news_id := content_item__get_live_revision(v_item_id); + -- + -- setting publish_date to the provided p_publish_date + -- + update cr_revisions + set publish_date = p_publish_date + where revision_id = v_news_id; + insert into cr_news (news_id, lead, @@ -110,36 +103,22 @@ approval_date, approval_ip) values - (v_revision_id, + (v_news_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 - --- added select define_function_args('news__delete','item_id'); -- @@ -164,15 +143,15 @@ -- images delete from images where image_id in (select latest_revision - from cr_items + 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 + 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; @@ -184,8 +163,6 @@ -- (re)-publish a news item out of the archive by nulling the archive_date -- this only applies to the currently active revision - --- added select define_function_args('news__make_permanent','item_id'); -- @@ -209,46 +186,28 @@ -- archive a news item -- this only applies to the currently active revision - --- added select define_function_args('news__archive','item_id,archive_date;current_timestamp'); -- -- procedure news__archive/2 -- CREATE OR REPLACE FUNCTION news__archive( p_item_id integer, - p_archive_date timestamptz -- default current_timestamp + p_archive_date timestamptz DEFAULT current_timestamp ) RETURNS integer AS $$ DECLARE BEGIN - update cr_news + 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 --- --- procedure news__archive/1 --- -CREATE OR REPLACE FUNCTION news__archive( - p_item_id integer -) RETURNS integer AS $$ -DECLARE -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 @@ -274,11 +233,11 @@ v_item_id cr_items.item_id%TYPE; BEGIN select item_id into v_item_id - from cr_revisions + 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 + update cr_news set approval_date = null, approval_user = null, approval_ip = null, @@ -293,14 +252,14 @@ update cr_revisions set publish_date = p_publish_date where revision_id = p_revision_id; - -- - update cr_news + -- + 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 @@ -336,19 +295,19 @@ 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 + if p_archive_date is null then return 'going_live_no_archive'; - else + else return 'going_live_with_archive'; - end if; + 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 + else return 'archived'; end if; end if; @@ -375,7 +334,7 @@ DECLARE v_news_title cr_revisions.title%TYPE; BEGIN - select title + select title into v_news_title from cr_revisions where revision_id = p_news_id; @@ -386,9 +345,9 @@ $$ LANGUAGE plpgsql; --- +-- -- API for Revision management --- +-- -- added @@ -435,19 +394,19 @@ ); -- create new news entry with new revision insert into cr_news - (news_id, + (news_id, lead, package_id, - archive_date, - approval_user, - approval_date, + archive_date, + approval_user, + approval_date, approval_ip) values - (v_revision_id, + (v_revision_id, p_lead, p_package_id, - p_archive_date, - p_approval_user, + p_archive_date, + p_approval_user, p_approval_date, p_approval_ip); -- make active revision if indicated @@ -515,7 +474,7 @@ ) RETURNS integer AS $$ DECLARE - one_news record; + one_news record; BEGIN for one_news in select publish_date, @@ -535,18 +494,18 @@ ci.live_revision, cr.revision_id, cn.lead - from - cr_items ci, + from + cr_items ci, cr_revisions cr, cr_news cn, acs_objects ao where cn.package_id = p_old_package_id and ((ci.item_id = cr.item_id - and ci.live_revision = cr.revision_id - and cr.revision_id = cn.news_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 + 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 @@ -609,4 +568,3 @@ $$ LANGUAGE plpgsql; -