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.18 -r1.18.14.1 --- openacs-4/packages/news/sql/postgresql/news-create.sql 24 Feb 2005 18:03:04 -0000 1.18 +++ openacs-4/packages/news/sql/postgresql/news-create.sql 2 Nov 2013 16:08:07 -0000 1.18.14.1 @@ -43,37 +43,44 @@ -- assign permission to defined contexts within ACS by default -- -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE default_context acs_objects.object_id%TYPE; registered_users acs_objects.object_id%TYPE; the_public acs_objects.object_id%TYPE; -begin - default_context := acs__magic_object_id(''default_context''); - registered_users := acs__magic_object_id(''registered_users''); - the_public := acs__magic_object_id(''the_public''); +BEGIN + default_context := acs__magic_object_id('default_context'); + registered_users := acs__magic_object_id('registered_users'); + the_public := acs__magic_object_id('the_public'); -- give the public permission to read by default PERFORM acs_permission__grant_permission ( default_context, -- object_id the_public, -- grantee_id - ''news_read'' -- privilege + 'news_read' -- privilege ); -- give registered users permission to upload items by default -- However, they must await approval by users with news_admin privilege PERFORM acs_permission__grant_permission ( default_context, -- object_id registered_users, -- grantee_id - ''news_create'' -- privilege + 'news_create' -- privilege ); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0 (); drop function inline_0 (); @@ -188,33 +195,40 @@ -- *** CREATE THE NEWS FOLDER as our CONTAINER *** -- create 1 news folder; different instances are filtered by package_id -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE v_folder_id cr_folders.folder_id%TYPE; -begin +BEGIN v_folder_id := content_folder__new( - ''news'', -- name - ''news'', -- label - ''News Item Root Folder, all news items go in here'', -- description + 'news', -- name + 'news', -- label + 'News Item Root Folder, all news items go in here', -- description null -- parent_id ); -- associate content types with news folder PERFORM content_folder__register_content_type ( v_folder_id, -- folder_id - ''news'', -- content_type - ''t'' -- include_subtypes + 'news', -- content_type + 't' -- include_subtypes ); PERFORM content_folder__register_content_type ( v_folder_id, -- folder_id - ''content_revision'', -- content_type - ''t'' -- include_subtypes + 'content_revision', -- content_type + 't' -- include_subtypes ); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0 (); drop function inline_0 (); 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.9 -r1.9.14.1 --- openacs-4/packages/news/sql/postgresql/news-drop.sql 24 Feb 2005 18:03:04 -0000 1.9 +++ openacs-4/packages/news/sql/postgresql/news-drop.sql 2 Nov 2013 16:08:07 -0000 1.9.14.1 @@ -5,18 +5,24 @@ -- $Id$ -- unregister content_types from folder -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE v_folder_id cr_folders.folder_id%TYPE; v_item_id cr_items.item_id%TYPE; -- RAL: commented out, not used. GC should be probably dealt with in -- news__delete anyways. -- v_gc_id general_comments.comment_id%TYPE; -- v_gc_msg_id acs_messages.message_id%TYPE; v_item_cursor RECORD; -begin - select content_item__get_id(''news'', null, ''f'') into v_folder_id from dual; +BEGIN + select content_item__get_id('news', null, 'f') into v_folder_id from dual; -- delete all contents of news folder FOR v_item_cursor IN @@ -31,25 +37,26 @@ -- unregister_content_types PERFORM content_folder__unregister_content_type ( v_folder_id, -- folder_id - ''content_revision'', -- content_type - ''t'' -- include_subtypes + 'content_revision', -- content_type + 't' -- include_subtypes ); PERFORM content_folder__unregister_content_type ( v_folder_id, -- folder_id - ''news'', -- content_type - ''t'' -- include_subtypes + 'news', -- content_type + 't' -- include_subtypes ); - -- this table must not hold reference to ''news'' type - delete from cr_folder_type_map where content_type = ''news''; + -- this table must not hold reference to 'news' type + delete from cr_folder_type_map where content_type = 'news'; -- delete news folder PERFORM content_folder__delete(v_folder_id); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0 (); drop function inline_0 (); @@ -79,50 +86,57 @@ -- delete privileges; -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE default_context acs_objects.object_id%TYPE; registered_users acs_objects.object_id%TYPE; the_public acs_objects.object_id%TYPE; -begin - PERFORM acs_privilege__remove_child(''news_admin'',''news_approve''); - PERFORM acs_privilege__remove_child(''news_admin'',''news_create''); - PERFORM acs_privilege__remove_child(''news_admin'',''news_delete''); - PERFORM acs_privilege__remove_child(''news_admin'',''news_read''); +BEGIN + PERFORM acs_privilege__remove_child('news_admin','news_approve'); + PERFORM acs_privilege__remove_child('news_admin','news_create'); + PERFORM acs_privilege__remove_child('news_admin','news_delete'); + PERFORM acs_privilege__remove_child('news_admin','news_read'); - PERFORM acs_privilege__remove_child(''read'',''news_read''); - PERFORM acs_privilege__remove_child(''create'',''news_create''); - PERFORM acs_privilege__remove_child(''delete'',''news_delete''); - PERFORM acs_privilege__remove_child(''admin'',''news_approve''); + PERFORM acs_privilege__remove_child('read','news_read'); + PERFORM acs_privilege__remove_child('create','news_create'); + PERFORM acs_privilege__remove_child('delete','news_delete'); + PERFORM acs_privilege__remove_child('admin','news_approve'); - PERFORM acs_privilege__remove_child(''admin'',''news_admin''); + PERFORM acs_privilege__remove_child('admin','news_admin'); - default_context := acs__magic_object_id(''default_context''); - registered_users := acs__magic_object_id(''registered_users''); - the_public := acs__magic_object_id(''the_public''); + default_context := acs__magic_object_id('default_context'); + registered_users := acs__magic_object_id('registered_users'); + the_public := acs__magic_object_id('the_public'); PERFORM acs_permission__revoke_permission ( default_context, -- object_id registered_users, -- grantee_id - ''news_create'' -- privilege + 'news_create' -- privilege ); PERFORM acs_permission__revoke_permission ( default_context, -- object_id the_public, -- grantee_id - ''news_read'' -- privilege + 'news_read' -- privilege ); - PERFORM acs_privilege__drop_privilege(''news_approve''); - PERFORM acs_privilege__drop_privilege(''news_create''); - PERFORM acs_privilege__drop_privilege(''news_delete''); - PERFORM acs_privilege__drop_privilege(''news_read''); - PERFORM acs_privilege__drop_privilege(''news_admin''); + PERFORM acs_privilege__drop_privilege('news_approve'); + PERFORM acs_privilege__drop_privilege('news_create'); + PERFORM acs_privilege__drop_privilege('news_delete'); + PERFORM acs_privilege__drop_privilege('news_read'); + PERFORM acs_privilege__drop_privilege('news_admin'); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0 (); drop function inline_0 (); 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 -r1.5 -r1.5.2.1 --- openacs-4/packages/news/sql/postgresql/news-package-create.sql 8 Nov 2010 07:24:44 -0000 1.5 +++ openacs-4/packages/news/sql/postgresql/news-package-create.sql 2 Nov 2013 16:08:07 -0000 1.5.2.1 @@ -7,64 +7,62 @@ -- OpenACS Port: Robert Locke (rlocke@infiniteinfo.com) -- *** PACKAGE NEWS, plsql to create content_item *** -select 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'); -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; +-- 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'); + + + +-- +-- 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 + +) 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'') +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 nextval(''t_acs_object_id_seq'') + select nextval('t_acs_object_id_seq') into v_id from dual; else v_id := p_item_id; end if; -- - v_name := ''news-'' || to_char(current_timestamp,''YYYYMMDD'') || ''-'' || v_id; + v_name := 'news-' || to_char(current_timestamp,'YYYYMMDD') || '-' || v_id; -- - v_log_string := ''initial submission''; + v_log_string := 'initial submission'; -- v_item_id := content_item__new( v_name, -- name @@ -75,8 +73,8 @@ 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 p_mime_type, -- mime_type @@ -85,7 +83,7 @@ null, -- data null, -- relation_tag p_is_live_p, -- live_p - ''text'', -- storage_type + 'text', -- storage_type p_package_id -- package_id ); @@ -120,33 +118,42 @@ p_approval_date, p_approval_ip); -- make this revision live when immediately approved - if p_is_live_p = ''t'' then + if p_is_live_p = 't' then update cr_items set live_revision = v_revision_id, - publish_status = ''ready'' + publish_status = 'ready' where item_id = v_item_id; end if; v_news_id := v_revision_id; return v_news_id; -end; -' language 'plpgsql'; +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; + + +-- added +select define_function_args('news__delete','item_id'); + +-- +-- procedure news__delete/1 +-- +CREATE OR REPLACE FUNCTION news__delete( + p_item_id integer +) RETURNS integer AS $$ +DECLARE v_item_id cr_items.item_id%TYPE; v_cm RECORD; -begin +BEGIN v_item_id := p_item_id; - -- dbms_output.put_line(''Deleting associated comments...''); + -- dbms_output.put_line('Deleting associated comments...'); -- delete acs_messages, images, comments to news item FOR v_cm IN @@ -169,75 +176,108 @@ where item_id = v_item_id); PERFORM content_item__delete(v_item_id); return 0; -end; -' language 'plpgsql'; +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 + + +-- added +select define_function_args('news__make_permanent','item_id'); + +-- +-- procedure news__make_permanent/1 +-- +CREATE OR REPLACE FUNCTION news__make_permanent( + p_item_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN update cr_news set archive_date = null where news_id = content_item__get_live_revision(p_item_id); return 0; -end; -' language 'plpgsql'; +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 + + +-- 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 + +) RETURNS integer AS $$ +DECLARE +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'; +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 + + +-- +-- 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'; +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'' + + +-- added +select define_function_args('news__set_approve','revision_id,approve_p;t,publish_date;null,archive_date;null,approval_user;null,approval_date;current_timestamp,approval_ip;null,live_revision_p;t'); + +-- +-- procedure news__set_approve/8 +-- +CREATE OR REPLACE FUNCTION news__set_approve( + p_revision_id integer, + p_approve_p varchar, -- default 't' + p_publish_date timestamptz, -- default null + p_archive_date timestamptz, -- default null + p_approval_user integer, -- default null + p_approval_date timestamptz, -- default current_timestamp + p_approval_ip varchar, -- default null + p_live_revision_p boolean -- default 't' + +) RETURNS integer AS $$ +DECLARE v_item_id cr_items.item_id%TYPE; -begin +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 + if p_approve_p = 'f' then update cr_news set approval_date = null, approval_user = null, @@ -262,104 +302,123 @@ 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 + if p_live_revision_p = 't' then update cr_items set live_revision = p_revision_id, - publish_status = ''ready'' + publish_status = 'ready' where item_id = v_item_id; end if; -- end if; return 0; -end; -' language 'plpgsql'; +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 + + +-- added +select define_function_args('news__status','publish_date,archive_date'); + +-- +-- procedure news__status/2 +-- +CREATE OR REPLACE FUNCTION news__status( + p_publish_date timestamptz, + p_archive_date timestamptz +) RETURNS varchar AS $$ +DECLARE +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''; + return 'going_live_no_archive'; else - return ''going_live_with_archive''; + return 'going_live_with_archive'; end if; else -- Published in the past if p_archive_date is null then - return ''published_no_archive''; + return 'published_no_archive'; else if p_archive_date > current_timestamp then - return ''published_with_archive''; + return 'published_with_archive'; else - return ''archived''; + return 'archived'; end if; end if; end if; else -- publish_date null - return ''unapproved''; + return 'unapproved'; end if; -end; -' language 'plpgsql'; +END; -create or replace function news__name (integer) -returns varchar as ' -declare - p_news_id alias for $1; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('news__name','news_id'); + +-- +-- procedure news__name/1 +-- +CREATE OR REPLACE FUNCTION news__name( + p_news_id integer +) RETURNS varchar AS $$ +DECLARE v_news_title cr_revisions.title%TYPE; -begin +BEGIN select title into v_news_title from cr_revisions where revision_id = p_news_id; return v_news_title; -end; -' language 'plpgsql'; +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; + +-- 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 v_revision_id integer; -begin +BEGIN -- create revision v_revision_id := content_revision__new( p_title, -- title @@ -392,31 +451,40 @@ p_approval_date, p_approval_ip); -- make active revision if indicated - if p_make_active_revision_p = ''t'' then + 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'; +END; +$$ LANGUAGE plpgsql; -create or replace function news__revision_set_active (integer) -returns integer as ' -declare - p_revision_id alias for $1; + + + +-- added +select define_function_args('news__revision_set_active','revision_id'); + +-- +-- procedure news__revision_set_active/1 +-- +CREATE OR REPLACE FUNCTION news__revision_set_active( + p_revision_id integer +) RETURNS integer AS $$ +DECLARE v_news_item_p boolean; v_item_id cr_items.item_id%TYPE; v_title acs_objects.title%TYPE; - -- could be used to check if really a ''news'' item -begin + -- could be used to check if really a 'news' item +BEGIN select item_id, title into v_item_id, v_title from cr_revisions where revision_id = p_revision_id; update cr_items set live_revision = p_revision_id, - publish_status = ''ready'' + publish_status = 'ready' where item_id = v_item_id; -- We update the acs_objects title as well. @@ -425,20 +493,30 @@ where object_id = v_item_id and (title != v_title or title is null); return 0; -end; -' language 'plpgsql'; +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_old_package_id alias for $1; --default null, - p_new_package_id alias for $2; --default null + + +-- added +select define_function_args('news__clone','old_package_id,new_package_id'); + +-- +-- procedure news__clone/2 +-- +CREATE OR REPLACE FUNCTION news__clone( + p_old_package_id integer, --default null, + p_new_package_id integer --default null + +) RETURNS integer AS $$ +DECLARE one_news record; -begin +BEGIN for one_news in select publish_date, cr.content as text, @@ -497,16 +575,25 @@ end loop; return 0; -end; -' language 'plpgsql'; +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 + + +-- added +select define_function_args('news__revision_delete','revision_id'); + +-- +-- procedure news__revision_delete/1 +-- +CREATE OR REPLACE FUNCTION news__revision_delete( + p_revision_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN -- delete from cr_news table delete from cr_news where news_id = p_revision_id; @@ -517,8 +604,9 @@ ); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + Index: openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.1.0b-4.6.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.1.0b-4.6.sql,v diff -u -r1.2 -r1.2.20.1 --- openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.1.0b-4.6.sql 30 Nov 2002 17:38:58 -0000 1.2 +++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.1.0b-4.6.sql 2 Nov 2013 16:08:07 -0000 1.2.20.1 @@ -4,13 +4,22 @@ -- new function news__clone -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 + + +-- added +select define_function_args('news__clone','new_package_id,old_package_id'); + +-- +-- procedure news__clone/2 +-- +CREATE OR REPLACE FUNCTION news__clone( + p_new_package_id integer, --default null, + p_old_package_id integer --default null + +) RETURNS integer AS $$ +DECLARE one_news record; -begin +BEGIN for one_news in select publish_date, cr.content as text, @@ -60,5 +69,6 @@ end loop; return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; 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 -r1.1 -r1.1.20.1 --- openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.6-5.0d1.sql 6 Oct 2003 12:11:23 -0000 1.1 +++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-4.6-5.0d1.sql 2 Nov 2013 16:08:07 -0000 1.1.20.1 @@ -6,39 +6,48 @@ --- **** 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 + + +-- added +select define_function_args('news__status','publish_date,archive_date'); + +-- +-- procedure news__status/2 +-- +CREATE OR REPLACE FUNCTION news__status( + p_publish_date timestamptz, + p_archive_date timestamptz +) RETURNS varchar AS $$ +DECLARE +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''; + return 'going_live_no_archive'; else - return ''going_live_with_archive''; + return 'going_live_with_archive'; end if; else -- Published in the past if p_archive_date is null then - return ''published_no_archive''; + return 'published_no_archive'; else if p_archive_date > current_timestamp then - return ''published_with_archive''; + return 'published_with_archive'; else - return ''archived''; + return 'archived'; end if; end if; end if; else -- publish_date null - return ''unapproved''; + return 'unapproved'; end if; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + -- **** Recreate views with calls to new status function create view news_items_live_or_submitted as 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 -r1.2 -r1.2.10.1 --- openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d1-5.2.0d2.sql 8 Aug 2006 21:27:04 -0000 1.2 +++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d1-5.2.0d2.sql 2 Nov 2013 16:08:07 -0000 1.2.10.1 @@ -29,37 +29,45 @@ -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 + + +-- 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 -- - 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; @@ -68,8 +76,8 @@ v_parent_id integer; v_name varchar; v_log_string varchar; -begin - select content_item__get_id(''news'',null,''f'') +BEGIN + select content_item__get_id('news',null,'f') into v_parent_id from dual; -- @@ -82,11 +90,11 @@ v_id := p_item_id; end if; -- - select ''news'' || to_char(current_timestamp,''YYYYMMDD'') || v_id + select 'news' || to_char(current_timestamp,'YYYYMMDD') || v_id into v_name from dual; -- - v_log_string := ''initial submission''; + v_log_string := 'initial submission'; -- v_item_id := content_item__new( v_name, -- name @@ -97,14 +105,14 @@ 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 null, -- title null, -- description p_mime_type, -- mime_type p_nls_language, -- nls_language null, -- data - ''text'' -- storage_type + 'text' -- storage_type -- relation tag is not used by any callers or any -- implementations of content_item__new ); @@ -138,51 +146,59 @@ p_approval_date, p_approval_ip); -- make this revision live when immediately approved - if p_is_live_p = ''t'' then + if p_is_live_p = 't' then update cr_items set live_revision = v_revision_id, - publish_status = ''ready'' + publish_status = 'ready' where item_id = v_item_id; end if; v_news_id := v_revision_id; return v_news_id; -end; -' language 'plpgsql'; +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; + + + +-- 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 -- - 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 +BEGIN -- create revision v_revision_id := content_revision__new( p_title, -- title @@ -215,14 +231,15 @@ p_approval_date, p_approval_ip); -- make active revision if indicated - if p_make_active_revision_p = ''t'' then + 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'; +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; Index: openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d2-5.2.0d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d2-5.2.0d3.sql,v diff -u -r1.3 -r1.3.10.1 --- openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d2-5.2.0d3.sql 8 Aug 2006 21:27:04 -0000 1.3 +++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d2-5.2.0d3.sql 2 Nov 2013 16:08:07 -0000 1.3.10.1 @@ -1,36 +1,41 @@ -select 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'); -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 +-- 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'); + + + + +-- +-- 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 + +) RETURNS integer AS $$ +DECLARE -- - 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; v_news_id integer; v_item_id integer; @@ -39,8 +44,8 @@ v_parent_id integer; v_name varchar; v_log_string varchar; -begin - select content_item__get_id(''news'',null,''f'') +BEGIN + select content_item__get_id('news',null,'f') into v_parent_id from dual; -- @@ -53,9 +58,9 @@ v_id := p_item_id; end if; -- - v_name := ''news-'' || to_char(current_timestamp,''YYYYMMDD'') || ''-'' || v_id; + v_name := 'news-' || to_char(current_timestamp,'YYYYMMDD') || '-' || v_id; -- - v_log_string := ''initial submission''; + v_log_string := 'initial submission'; -- v_item_id := content_item__new( v_name, -- name @@ -66,8 +71,8 @@ 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 p_mime_type, -- mime_type @@ -76,7 +81,7 @@ null, -- data null, -- relation_tag p_is_live_p, -- live_p - ''text'', -- storage_type + 'text', -- storage_type p_package_id -- package_id ); @@ -111,38 +116,47 @@ p_approval_date, p_approval_ip); -- make this revision live when immediately approved - if p_is_live_p = ''t'' then + if p_is_live_p = 't' then update cr_items set live_revision = v_revision_id, - publish_status = ''ready'' + publish_status = 'ready' where item_id = v_item_id; end if; v_news_id := v_revision_id; return v_news_id; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function news__revision_set_active (integer) -returns integer as ' -declare - p_revision_id alias for $1; + + + +-- added +select define_function_args('news__revision_set_active','revision_id'); + +-- +-- procedure news__revision_set_active/1 +-- +CREATE OR REPLACE FUNCTION news__revision_set_active( + p_revision_id integer +) RETURNS integer AS $$ +DECLARE v_news_item_p boolean; v_item_id cr_items.item_id%TYPE; v_title acs_objects.title%TYPE; - -- could be used to check if really a ''news'' item -begin + -- could be used to check if really a 'news' item +BEGIN select item_id, title into v_item_id, v_title from cr_revisions where revision_id = p_revision_id; update cr_items set live_revision = p_revision_id, - publish_status = ''ready'' + publish_status = 'ready' where item_id = v_item_id; -- We update the acs_objects title as well. @@ -151,5 +165,6 @@ where object_id = v_item_id and (title != v_title or title is null); return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d5-5.2.0d6.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d5-5.2.0d6.sql,v diff -u -r1.2 -r1.2.10.1 --- openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d5-5.2.0d6.sql 8 Aug 2006 21:27:04 -0000 1.2 +++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.2.0d5-5.2.0d6.sql 2 Nov 2013 16:08:07 -0000 1.2.10.1 @@ -7,13 +7,22 @@ -- @cvs-id $Id$ -- -create or replace function news__clone (integer, integer) -returns integer as ' -declare - p_old_package_id alias for $1; --default null, - p_new_package_id alias for $2; --default null + + +-- added +select define_function_args('news__clone','old_package_id,new_package_id'); + +-- +-- procedure news__clone/2 +-- +CREATE OR REPLACE FUNCTION news__clone( + p_old_package_id integer, --default null, + p_new_package_id integer --default null + +) RETURNS integer AS $$ +DECLARE one_news record; -begin +BEGIN for one_news in select publish_date, cr.content as text, @@ -72,5 +81,6 @@ end loop; return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.6.0d1-5.6.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.6.0d1-5.6.0d2.sql,v diff -u -r1.1 -r1.1.2.1 --- openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.6.0d1-5.6.0d2.sql 8 Nov 2010 07:24:44 -0000 1.1 +++ openacs-4/packages/news/sql/postgresql/upgrade/upgrade-5.6.0d1-5.6.0d2.sql 2 Nov 2013 16:08:07 -0000 1.1.2.1 @@ -8,37 +8,45 @@ -- PG 9.x - changing usage of sequences -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 + + +-- 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') +-- 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 + +) RETURNS integer AS $$ +DECLARE -- - 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; v_news_id integer; v_item_id integer; @@ -47,23 +55,23 @@ v_parent_id integer; v_name varchar; v_log_string varchar; -begin - select content_item__get_id(''news'',null,''f'') +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 nextval(''t_acs_object_id_seq'') + select nextval('t_acs_object_id_seq') into v_id from dual; else v_id := p_item_id; end if; -- - v_name := ''news-'' || to_char(current_timestamp,''YYYYMMDD'') || ''-'' || v_id; + v_name := 'news-' || to_char(current_timestamp,'YYYYMMDD') || '-' || v_id; -- - v_log_string := ''initial submission''; + v_log_string := 'initial submission'; -- v_item_id := content_item__new( v_name, -- name @@ -74,8 +82,8 @@ 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 p_mime_type, -- mime_type @@ -84,7 +92,7 @@ null, -- data null, -- relation_tag p_is_live_p, -- live_p - ''text'', -- storage_type + 'text', -- storage_type p_package_id -- package_id ); @@ -119,17 +127,18 @@ p_approval_date, p_approval_ip); -- make this revision live when immediately approved - if p_is_live_p = ''t'' then + if p_is_live_p = 't' then update cr_items set live_revision = v_revision_id, - publish_status = ''ready'' + publish_status = 'ready' where item_id = v_item_id; end if; v_news_id := v_revision_id; return v_news_id; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql;