Index: openacs-4/packages/calendar/sql/postgresql/calendar-notifications-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/calendar-notifications-init.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/calendar/sql/postgresql/calendar-notifications-init.sql 10 Jan 2004 18:59:50 -0000 1.1 +++ openacs-4/packages/calendar/sql/postgresql/calendar-notifications-init.sql 12 Aug 2013 09:34:30 -0000 1.2 @@ -1,44 +1,51 @@ -- Calendar integration with Notifications -create function inline_0() returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE impl_id integer; v_notification_id integer; -begin +BEGIN -- the notification type impl impl_id := acs_sc_impl__new ( - ''NotificationType'', - ''calendar_notif_type'', - ''calendars'' + 'NotificationType', + 'calendar_notif_type', + 'calendars' ); v_notification_id := acs_sc_impl_alias__new ( - ''NotificationType'', - ''calendar_notif_type'', - ''GetURL'', - ''calendar::notification::get_url'', - ''TCL'' + 'NotificationType', + 'calendar_notif_type', + 'GetURL', + 'calendar::notification::get_url', + 'TCL' ); v_notification_id := acs_sc_impl_alias__new ( - ''NotificationType'', - ''calendar_notif_type'', - ''ProcessReply'', - ''calendar::notification::process_reply'', - ''TCL'' + 'NotificationType', + 'calendar_notif_type', + 'ProcessReply', + 'calendar::notification::process_reply', + 'TCL' ); PERFORM acs_sc_binding__new ( - ''NotificationType'', - ''calendar_notif_type'' + 'NotificationType', + 'calendar_notif_type' ); v_notification_id:= notification_type__new ( NULL, impl_id, - ''calendar_notif'', - ''Calendar Notification'', - ''Notifications for Entire Calendar Package'', + 'calendar_notif', + 'Calendar Notification', + 'Notifications for Entire Calendar Package', now(), NULL, NULL, @@ -49,16 +56,17 @@ insert into notification_types_intervals (type_id, interval_id) select v_notification_id, interval_id - from notification_intervals where name in (''instant'',''hourly'',''daily''); + from notification_intervals where name in ('instant','hourly','daily'); insert into notification_types_del_methods (type_id, delivery_method_id) select v_notification_id, delivery_method_id - from notification_delivery_methods where short_name in (''email''); + from notification_delivery_methods where short_name in ('email'); return (0); -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0(); drop function inline_0(); \ No newline at end of file Index: openacs-4/packages/faq/sql/postgresql/faq-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/postgresql/faq-create.sql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/faq/sql/postgresql/faq-create.sql 7 Oct 2007 22:37:00 -0000 1.6 +++ openacs-4/packages/faq/sql/postgresql/faq-create.sql 12 Aug 2013 09:34:30 -0000 1.7 @@ -4,24 +4,24 @@ -- -- @cvs-id $Id$ -- -create function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN PERFORM acs_object_type__create_type ( - ''faq'', -- object_type - ''FAQ'', -- pretty_name - ''FAQs'', -- pretty_plural - ''acs_object'', -- supertype - ''FAQS'', -- table_name - ''FAQ_ID'', -- id_column + 'faq', -- object_type + 'FAQ', -- pretty_name + 'FAQs', -- pretty_plural + 'acs_object', -- supertype + 'FAQS', -- table_name + 'FAQ_ID', -- id_column null, -- package_name - ''f'', -- abstract_p + 'f', -- abstract_p null, -- type_extension_table - ''faq__name'' -- name_method + 'faq__name' -- name_method ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); @@ -39,24 +39,24 @@ ); -create function inline_1 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_1 () RETURNS integer AS $$ +BEGIN PERFORM acs_object_type__create_type ( - ''faq_q_and_a'', -- object_type - ''FAQ_Q_and_A'', -- pretty_name - ''FAQ_Q_and_As'', -- pretty_plural - ''acs_object'', -- supertype - ''FAQ_Q_AND_AS'', -- table_name - ''ENTRY_ID'', -- id_column + 'faq_q_and_a', -- object_type + 'FAQ_Q_and_A', -- pretty_name + 'FAQ_Q_and_As', -- pretty_plural + 'acs_object', -- supertype + 'FAQ_Q_AND_AS', -- table_name + 'ENTRY_ID', -- id_column null, -- package_name - ''f'', -- abstract_p + 'f', -- abstract_p null, -- type_extension_table null -- name_method ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_1 (); @@ -110,30 +110,37 @@ select acs_privilege__add_child('admin','faq_admin_faq'); -create function inline_2 () -returns integer as ' -declare + + +-- +-- procedure inline_2/0 +-- +CREATE OR REPLACE FUNCTION inline_2( + +) 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 +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''); + 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 the power to view faqs by default - PERFORM acs_permission__grant_permission (default_context, the_public, ''faq_view_faq''); + PERFORM acs_permission__grant_permission (default_context, the_public, 'faq_view_faq'); -- give the public the power to view q_and_as by default - PERFORM acs_permission__grant_permission ( default_context,the_public, ''faq_view_q_and_a''); + PERFORM acs_permission__grant_permission ( default_context,the_public, 'faq_view_q_and_a'); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_2 (); Index: openacs-4/packages/faq/sql/postgresql/faq-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/postgresql/faq-drop.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/faq/sql/postgresql/faq-drop.sql 7 Oct 2007 22:37:00 -0000 1.4 +++ openacs-4/packages/faq/sql/postgresql/faq-drop.sql 12 Aug 2013 09:34:30 -0000 1.5 @@ -24,24 +24,30 @@ delete from acs_permissions where object_id in (select faq_id from faqs); -create function inline_0() -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE object_rec record; default_context acs_objects.object_id%TYPE; registered_users acs_objects.object_id%TYPE; the_public acs_objects.object_id%TYPE; -begin +BEGIN --drop objects - for object_rec in select object_id from acs_objects where object_type=''faq'' + for object_rec in select object_id from acs_objects where object_type='faq' loop PERFORM acs_object__delete( object_rec.object_id ); end loop; - for object_rec in select object_id from acs_objects where object_type=''faq_q_and_a'' + for object_rec in select object_id from acs_objects where object_type='faq_q_and_a' loop PERFORM acs_object__delete( object_rec.object_id ); end loop; @@ -50,55 +56,56 @@ -- bind privileges to global names - 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'); -- revoke from the public the power to view faqs - PERFORM acs_permission__revoke_permission (default_context, the_public, ''faq_view_faq''); + PERFORM acs_permission__revoke_permission (default_context, the_public, 'faq_view_faq'); -- revoke from the public the power to view q_and_as - PERFORM acs_permission__revoke_permission ( default_context,the_public, ''faq_view_q_and_a''); + PERFORM acs_permission__revoke_permission ( default_context,the_public, 'faq_view_q_and_a'); --drop permissions - PERFORM acs_privilege__remove_child(''create'',''faq_create_faq''); - PERFORM acs_privilege__remove_child(''create'',''faq_create_q_and_a''); - PERFORM acs_privilege__remove_child(''write'',''faq_modify_faq''); - PERFORM acs_privilege__remove_child(''write'',''faq_modify_q_and_a''); - PERFORM acs_privilege__remove_child(''read'',''faq_view_faq''); - PERFORM acs_privilege__remove_child(''read'',''faq_view_q_and_a''); - PERFORM acs_privilege__remove_child(''delete'',''faq_delete_faq''); - PERFORM acs_privilege__remove_child(''delete'',''faq_delete_q_and_a''); - PERFORM acs_privilege__remove_child(''admin'',''faq_admin_faq''); + PERFORM acs_privilege__remove_child('create','faq_create_faq'); + PERFORM acs_privilege__remove_child('create','faq_create_q_and_a'); + PERFORM acs_privilege__remove_child('write','faq_modify_faq'); + PERFORM acs_privilege__remove_child('write','faq_modify_q_and_a'); + PERFORM acs_privilege__remove_child('read','faq_view_faq'); + PERFORM acs_privilege__remove_child('read','faq_view_q_and_a'); + PERFORM acs_privilege__remove_child('delete','faq_delete_faq'); + PERFORM acs_privilege__remove_child('delete','faq_delete_q_and_a'); + PERFORM acs_privilege__remove_child('admin','faq_admin_faq'); - PERFORM acs_privilege__remove_child(''faq_admin_faq'', ''faq_view_faq''); - PERFORM acs_privilege__remove_child(''faq_admin_faq'', ''faq_create_faq''); - PERFORM acs_privilege__remove_child(''faq_admin_faq'', ''faq_delete_faq''); - PERFORM acs_privilege__remove_child(''faq_admin_faq'', ''faq_modify_faq''); - PERFORM acs_privilege__remove_child(''faq_admin_q_and_a'', ''faq_view_q_and_a''); - PERFORM acs_privilege__remove_child(''faq_admin_q_and_a'', ''faq_create_q_and_a''); - PERFORM acs_privilege__remove_child(''faq_admin_q_and_a'', ''faq_delete_q_and_a''); - PERFORM acs_privilege__remove_child(''faq_admin_q_and_a'', ''faq_modify_q_and_a''); - PERFORM acs_privilege__remove_child(''faq_admin_faq'', ''faq_admin_q_and_a''); + PERFORM acs_privilege__remove_child('faq_admin_faq', 'faq_view_faq'); + PERFORM acs_privilege__remove_child('faq_admin_faq', 'faq_create_faq'); + PERFORM acs_privilege__remove_child('faq_admin_faq', 'faq_delete_faq'); + PERFORM acs_privilege__remove_child('faq_admin_faq', 'faq_modify_faq'); + PERFORM acs_privilege__remove_child('faq_admin_q_and_a', 'faq_view_q_and_a'); + PERFORM acs_privilege__remove_child('faq_admin_q_and_a', 'faq_create_q_and_a'); + PERFORM acs_privilege__remove_child('faq_admin_q_and_a', 'faq_delete_q_and_a'); + PERFORM acs_privilege__remove_child('faq_admin_q_and_a', 'faq_modify_q_and_a'); + PERFORM acs_privilege__remove_child('faq_admin_faq', 'faq_admin_q_and_a'); - PERFORM acs_privilege__drop_privilege(''faq_view_faq''); - PERFORM acs_privilege__drop_privilege(''faq_create_faq''); - PERFORM acs_privilege__drop_privilege(''faq_delete_faq''); - PERFORM acs_privilege__drop_privilege(''faq_modify_faq''); - PERFORM acs_privilege__drop_privilege(''faq_view_q_and_a''); - PERFORM acs_privilege__drop_privilege(''faq_create_q_and_a''); - PERFORM acs_privilege__drop_privilege(''faq_delete_q_and_a''); - PERFORM acs_privilege__drop_privilege(''faq_modify_q_and_a''); - PERFORM acs_privilege__drop_privilege(''faq_admin_faq''); - PERFORM acs_privilege__drop_privilege(''faq_admin_q_and_a''); + PERFORM acs_privilege__drop_privilege('faq_view_faq'); + PERFORM acs_privilege__drop_privilege('faq_create_faq'); + PERFORM acs_privilege__drop_privilege('faq_delete_faq'); + PERFORM acs_privilege__drop_privilege('faq_modify_faq'); + PERFORM acs_privilege__drop_privilege('faq_view_q_and_a'); + PERFORM acs_privilege__drop_privilege('faq_create_q_and_a'); + PERFORM acs_privilege__drop_privilege('faq_delete_q_and_a'); + PERFORM acs_privilege__drop_privilege('faq_modify_q_and_a'); + PERFORM acs_privilege__drop_privilege('faq_admin_faq'); + PERFORM acs_privilege__drop_privilege('faq_admin_q_and_a'); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); Index: openacs-4/packages/faq/sql/postgresql/faq-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/postgresql/faq-package-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/faq/sql/postgresql/faq-package-create.sql 1 Apr 2004 22:52:46 -0000 1.1 +++ openacs-4/packages/faq/sql/postgresql/faq-package-create.sql 12 Aug 2013 09:34:30 -0000 1.2 @@ -30,7 +30,7 @@ p_creation_user, p_creation_ip, p_context_id, - ''t'', + 't', p_question, v_package_id ); @@ -40,35 +40,53 @@ (v_entry_id, p_faq_id, p_question, p_answer, p_sort_key); return v_entry_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function faq__delete_q_and_a (integer) -returns integer as ' -declare - p_entry_id alias for $1; -begin + + +-- added +select define_function_args('faq__delete_q_and_a','entry_id'); + +-- +-- procedure faq__delete_q_and_a/1 +-- +CREATE OR REPLACE FUNCTION faq__delete_q_and_a( + p_entry_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN delete from faq_q_and_as where entry_id = p_entry_id; - raise NOTICE ''Deleting FAQ_Q_and_A...''; + raise NOTICE 'Deleting FAQ_Q_and_A...'; PERFORM acs_object__delete(p_entry_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function faq__new_faq (integer, varchar, boolean,varchar,timestamptz,integer,varchar,integer ) -returns integer as ' -declare - p_faq_id alias for $1; - p_faq_name alias for $2; - p_separate_p alias for $3; - p_object_type alias for $4; - p_creation_date alias for $5; - p_creation_user alias for $6; - p_creation_ip alias for $7; - p_context_id alias for $8; + + +-- added +select define_function_args('faq__new_faq','faq_id,faq_name,separate_p,object_type,creation_date,creation_user,creation_ip,context_id'); + +-- +-- procedure faq__new_faq/8 +-- +CREATE OR REPLACE FUNCTION faq__new_faq( + p_faq_id integer, + p_faq_name varchar, + p_separate_p boolean, + p_object_type varchar, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer +) RETURNS integer AS $$ +DECLARE v_faq_id faqs.faq_id%TYPE; -begin +BEGIN v_faq_id := acs_object__new ( p_faq_id, @@ -77,7 +95,7 @@ p_creation_user, p_creation_ip, p_context_id, - ''t'', + 't', p_faq_name, p_context_id ); @@ -88,15 +106,24 @@ return v_faq_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function faq__delete_faq (integer) -returns integer as ' -declare - p_faq_id alias for $1; + + +-- added +select define_function_args('faq__delete_faq','faq_id'); + +-- +-- procedure faq__delete_faq/1 +-- +CREATE OR REPLACE FUNCTION faq__delete_faq( + p_faq_id integer +) RETURNS integer AS $$ +DECLARE del_rec record; -begin +BEGIN -- Because q_and_as are objects, we need to -- loop through a list of them, and call an explicit -- delete function for each one. (i.e. each @@ -113,32 +140,51 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function faq__name(integer) -returns varchar as ' -declare - p_faq_id alias for $1; + + +-- added +select define_function_args('faq__name','faq_id'); + +-- +-- procedure faq__name/1 +-- +CREATE OR REPLACE FUNCTION faq__name( + p_faq_id integer +) RETURNS varchar AS $$ +DECLARE v_faq_name faqs.faq_name%TYPE; -begin +BEGIN select faq_name into v_faq_name from faqs where faq_id = p_faq_id; return v_faq_name; -end; -' language 'plpgsql'; +END; -create or replace function faq__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 +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('faq__clone','new_package_id,old_package_id'); + +-- +-- procedure faq__clone/2 +-- +CREATE OR REPLACE FUNCTION faq__clone( + p_new_package_id integer, --default null, + p_old_package_id integer --default null + +) RETURNS integer AS $$ +DECLARE v_faq_id faqs.faq_id%TYPE; one_faq record; entry record; -begin +BEGIN -- get all the faqs belonging to the old package, -- and create new faqs for the new package for one_faq in select * @@ -166,6 +212,7 @@ end loop; end loop; return 0; - end; -' language 'plpgsql'; + END; +$$ LANGUAGE plpgsql; + Index: openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql,v diff -u -r1.8 -r1.9 --- openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql 8 Aug 2006 21:26:51 -0000 1.8 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql 12 Aug 2013 09:34:30 -0000 1.9 @@ -82,20 +82,21 @@ returns integer as' begin perform acs_object_type__create_type( - ''forums_forum'', - ''Forums Forum'', - ''Forums Forums'', - ''acs_object'', - ''forums_forums'', - ''forum_id'', - ''forums_forum'', - ''f'', + 'forums_forum', + 'Forums Forum', + 'Forums Forums', + 'acs_object', + 'forums_forums', + 'forum_id', + 'forums_forum', + 'f', null, - ''forums_forum__name'' + 'forums_forum__name' ); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); Index: openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql 8 Aug 2006 21:26:51 -0000 1.4 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql 12 Aug 2013 09:34:30 -0000 1.5 @@ -10,23 +10,23 @@ -- privileges -create function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN - delete from acs_permissions where privilege in (''forum_moderate''); + delete from acs_permissions where privilege in ('forum_moderate'); -- remove children - perform acs_privilege__remove_child(''admin'',''forum_moderate''); - perform acs_privilege__remove_child(''forum_moderate'',''create''); - perform acs_privilege__remove_child(''forum_moderate'',''delete''); - perform acs_privilege__remove_child(''forum_moderate'',''read''); - perform acs_privilege__remove_child(''forum_moderate'',''write''); + perform acs_privilege__remove_child('admin','forum_moderate'); + perform acs_privilege__remove_child('forum_moderate','create'); + perform acs_privilege__remove_child('forum_moderate','delete'); + perform acs_privilege__remove_child('forum_moderate','read'); + perform acs_privilege__remove_child('forum_moderate','write'); - perform acs_privilege__drop_privilege(''forum_moderate''); + perform acs_privilege__drop_privilege('forum_moderate'); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0 (); @@ -35,15 +35,15 @@ drop view forums_forums_enabled; drop table forums_forums; -create function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN perform acs_object_type__drop_type ( - ''forums_forum'', ''f'' + 'forums_forum', 'f' ); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0 (); Index: openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql,v diff -u -r1.10 -r1.11 --- openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql 30 Mar 2004 21:00:30 -0000 1.10 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql 12 Aug 2013 09:34:30 -0000 1.11 @@ -13,30 +13,36 @@ select define_function_args('forums_forum__new','forum_id,object_type;forums_forum,name,charter,presentation_type,posting_policy,package_id,creation_date,creation_user,creation_ip,context_id'); -create or replace function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_forum_id alias for $1; - p_object_type alias for $2; - p_name alias for $3; - p_charter alias for $4; - p_presentation_type alias for $5; - p_posting_policy alias for $6; - p_package_id alias for $7; - p_creation_date alias for $8; - p_creation_user alias for $9; - p_creation_ip alias for $10; - p_context_id alias for $11; + + +-- +-- procedure forums_forum__new/11 +-- +CREATE OR REPLACE FUNCTION forums_forum__new( + p_forum_id integer, + p_object_type varchar, -- default 'forums_forum' + p_name varchar, + p_charter varchar, + p_presentation_type varchar, + p_posting_policy varchar, + p_package_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer + +) RETURNS integer AS $$ +DECLARE v_forum_id integer; -begin +BEGIN v_forum_id:= acs_object__new( p_forum_id, p_object_type, p_creation_date, p_creation_user, p_creation_ip, coalesce(p_context_id, p_package_id), - ''t'', + 't', p_name, p_package_id ); @@ -47,28 +53,41 @@ (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id); return v_forum_id; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select define_function_args('forums_forum__name','forum_id'); -create or replace function forums_forum__name(integer) -returns varchar as ' -declare - p_forum_id alias for $1; -begin + + +-- +-- procedure forums_forum__name/1 +-- +CREATE OR REPLACE FUNCTION forums_forum__name( + p_forum_id integer +) RETURNS varchar AS $$ +DECLARE +BEGIN return name from forums_forums where forum_id = p_forum_id; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select define_function_args('forums_forum__delete','forum_id'); -create or replace function forums_forum__delete(integer) -returns integer as ' -declare - p_forum_id alias for $1; -begin + + +-- +-- procedure forums_forum__delete/1 +-- +CREATE OR REPLACE FUNCTION forums_forum__delete( + p_forum_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN perform acs_object__delete(p_forum_id); return 0; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql,v diff -u -r1.13 -r1.14 --- openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 15 Mar 2005 20:06:45 -0000 1.13 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 12 Aug 2013 09:34:30 -0000 1.14 @@ -79,24 +79,24 @@ from forums_messages where state= 'pending'; -create or replace function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN perform acs_object_type__create_type( - ''forums_message'', - ''Forums Message'', - ''Forums Messages'', - ''acs_object'', - ''forums_messages'', - ''message_id'', - ''forums_message'', - ''f'', + 'forums_message', + 'Forums Message', + 'Forums Messages', + 'acs_object', + 'forums_messages', + 'message_id', + 'forums_message', + 'f', null, - ''forums_message__name'' + 'forums_message__name' ); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0 (); Index: openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql 12 Jun 2002 15:24:02 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql 12 Aug 2013 09:34:30 -0000 1.3 @@ -12,15 +12,15 @@ drop view forums_messages_approved; drop table forums_messages; -create function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN perform acs_object_type__drop_type ( - ''forums_message'', ''f'' + 'forums_message', 'f' ); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0 (); Index: openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql,v diff -u -r1.18 -r1.19 --- openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 15 Mar 2005 20:06:45 -0000 1.18 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 12 Aug 2013 09:34:30 -0000 1.19 @@ -16,33 +16,39 @@ -- Get rid of the old version so we'll throw an error if the admin forgets to reboot -- OpenACS after the upgrade (package_instantiate_object caches param lists) -create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_message_id alias for $1; - p_object_type alias for $2; - p_forum_id alias for $3; - p_subject alias for $4; - p_content alias for $5; - p_format alias for $6; - p_user_id alias for $7; - p_state alias for $8; - p_parent_id alias for $9; - p_creation_date alias for $10; - p_creation_user alias for $11; - p_creation_ip alias for $12; - p_context_id alias for $13; + + +-- +-- procedure forums_message__new/13 +-- +CREATE OR REPLACE FUNCTION forums_message__new( + p_message_id integer, + p_object_type varchar, -- default 'forums_message' + p_forum_id integer, + p_subject varchar, + p_content text, + p_format char, + p_user_id integer, + p_state varchar, + p_parent_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer + +) RETURNS integer AS $$ +DECLARE v_message_id integer; v_forum_policy forums_forums.posting_policy%TYPE; v_state forums_messages.state%TYPE; v_posting_date forums_messages.posting_date%TYPE; v_package_id acs_objects.package_id%TYPE; -begin +BEGIN select package_id into v_package_id from forums_forums where forum_id = p_forum_id; if v_package_id is null then - raise exception ''forums_message__new: forum_id % not found'', p_forum_id; + raise exception 'forums_message__new: forum_id % not found', p_forum_id; end if; v_message_id := acs_object__new( @@ -52,7 +58,7 @@ p_creation_user, p_creation_ip, coalesce(p_context_id, p_forum_id), - ''t'', + 't', p_subject, v_package_id ); @@ -63,9 +69,9 @@ from forums_forums where forum_id = p_forum_id; - if v_forum_policy = ''moderated'' - then v_state := ''pending''; - else v_state := ''approved''; + if v_forum_policy = 'moderated' + then v_state := 'pending'; + else v_state := 'approved'; end if; else v_state := p_state; @@ -82,7 +88,7 @@ where forum_id = p_forum_id; if p_parent_id is null then - if v_state = ''approved'' then + if v_state = 'approved' then update forums_forums set thread_count = thread_count + 1, approved_thread_count = approved_thread_count + 1 @@ -93,7 +99,7 @@ where forum_id=p_forum_id; end if; else - if v_state = ''approved'' then + if v_state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count + 1, reply_count = reply_count + 1, @@ -110,18 +116,24 @@ return v_message_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args ('forums_message__root_message_id', 'message_id'); -create or replace function forums_message__root_message_id (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- +-- procedure forums_message__root_message_id/1 +-- +CREATE OR REPLACE FUNCTION forums_message__root_message_id( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_message_id forums_messages.message_id%TYPE; v_forum_id forums_messages.forum_id%TYPE; v_sortkey forums_messages.tree_sortkey%TYPE; -begin +BEGIN select forum_id, tree_sortkey into v_forum_id, v_sortkey from forums_messages @@ -134,94 +146,113 @@ and tree_sortkey = tree_ancestor_key(v_sortkey, 1); return v_message_id; -end; -' language 'plpgsql' stable strict; +END; +$$ LANGUAGE plpgsql stable strict; + select define_function_args ('forums_message__thread_open', 'message_id'); -create or replace function forums_message__thread_open (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- +-- procedure forums_message__thread_open/1 +-- +CREATE OR REPLACE FUNCTION forums_message__thread_open( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_forum_id forums_messages.forum_id%TYPE; v_sortkey forums_messages.tree_sortkey%TYPE; -begin +BEGIN select forum_id, tree_sortkey into v_forum_id, v_sortkey from forums_messages where message_id = p_message_id; update forums_messages - set open_p = ''t'' + set open_p = 't' where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) and forum_id = v_forum_id; update forums_messages - set open_p = ''t'' + set open_p = 't' where message_id = p_message_id; return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select define_function_args ('forums_message__thread_close', 'message_id'); -create or replace function forums_message__thread_close (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- +-- procedure forums_message__thread_close/1 +-- +CREATE OR REPLACE FUNCTION forums_message__thread_close( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_forum_id forums_messages.forum_id%TYPE; v_sortkey forums_messages.tree_sortkey%TYPE; -begin +BEGIN select forum_id, tree_sortkey into v_forum_id, v_sortkey from forums_messages where message_id = p_message_id; update forums_messages - set open_p = ''f'' + set open_p = 'f' where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) and forum_id = v_forum_id; update forums_messages - set open_p = ''f'' + set open_p = 'f' where message_id = p_message_id; return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select define_function_args ('forums_message__set_state', 'message_id,state'); -create or replace function forums_message__set_state(integer,varchar) returns integer as ' -declare - p_message_id alias for $1; - p_state alias for $2; + + +-- +-- procedure forums_message__set_state/2 +-- +CREATE OR REPLACE FUNCTION forums_message__set_state( + p_message_id integer, + p_state varchar +) RETURNS integer AS $$ +DECLARE v_cur record; -begin +BEGIN select into v_cur * from forums_messages where message_id = p_message_id; if v_cur.parent_id is null then - if p_state = ''approved'' and v_cur.state <> ''approved'' then + if p_state = 'approved' and v_cur.state <> 'approved' then update forums_forums set approved_thread_count = approved_thread_count + 1 where forum_id=v_cur.forum_id; - elsif p_state <> ''approved'' and v_cur.state = ''approved'' then + elsif p_state <> 'approved' and v_cur.state = 'approved' then update forums_forums set approved_thread_count = approved_thread_count - 1 where forum_id=v_cur.forum_id; end if; else - if p_state = ''approved'' and v_cur.state <> ''approved'' then + if p_state = 'approved' and v_cur.state <> 'approved' then update forums_messages set approved_reply_count = approved_reply_count + 1, last_poster = (case when v_cur.posting_date > last_child_post then v_cur.user_id else last_poster end), last_child_post = (case when v_cur.posting_date > last_child_post then v_cur.posting_date else last_child_post end) where message_id = forums_message__root_message_id(v_cur.message_id); - elsif p_state <> ''approved'' and v_cur.state = ''approved'' then + elsif p_state <> 'approved' and v_cur.state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count - 1 where message_id = forums_message__root_message_id(v_cur.message_id); @@ -234,16 +265,22 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args ('forums_message__delete', 'message_id'); -create or replace function forums_message__delete (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- +-- procedure forums_message__delete/1 +-- +CREATE OR REPLACE FUNCTION forums_message__delete( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_cur record; -begin +BEGIN -- Maintain the forum thread counts @@ -252,7 +289,7 @@ where message_id = p_message_id; if v_cur.parent_id is null then - if v_cur.state = ''approved'' then + if v_cur.state = 'approved' then update forums_forums set thread_count = thread_count - 1, approved_thread_count = approved_thread_count - 1 @@ -262,7 +299,7 @@ set thread_count = thread_count - 1 where forum_id=v_cur.forum_id; end if; - elsif v_cur.state = ''approved'' then + elsif v_cur.state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count - 1, reply_count = reply_count - 1 @@ -275,18 +312,24 @@ perform acs_object__delete(p_message_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args ('forums_message__delete_thread', 'message_id'); -create or replace function forums_message__delete_thread (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- +-- procedure forums_message__delete_thread/1 +-- +CREATE OR REPLACE FUNCTION forums_message__delete_thread( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_forum_id forums_messages.forum_id%TYPE; v_sortkey forums_messages.tree_sortkey%TYPE; v_message RECORD; -begin +BEGIN select forum_id, tree_sortkey into v_forum_id, v_sortkey from forums_messages @@ -315,16 +358,23 @@ perform forums_message__delete(p_message_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args('forums_message__name','message_id'); -create or replace function forums_message__name (integer) -returns varchar as ' -declare - p_message_id alias for $1; -begin + + +-- +-- procedure forums_message__name/1 +-- +CREATE OR REPLACE FUNCTION forums_message__name( + p_message_id integer +) RETURNS varchar AS $$ +DECLARE +BEGIN return subject from forums_messages where message_id = p_message_id; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/forums-notifications-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/Attic/forums-notifications-init.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/forums/sql/postgresql/forums-notifications-init.sql 1 Jul 2002 04:08:13 -0000 1.3 +++ openacs-4/packages/forums/sql/postgresql/forums-notifications-init.sql 12 Aug 2013 09:34:30 -0000 1.4 @@ -11,45 +11,52 @@ -- the integration with Notifications -create function inline_0() returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE impl_id integer; v_foo integer; -begin +BEGIN -- the notification type impl impl_id := acs_sc_impl__new ( - ''NotificationType'', - ''forums_forum_notif_type'', - ''forums'' + 'NotificationType', + 'forums_forum_notif_type', + 'forums' ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', - ''forums_forum_notif_type'', - ''GetURL'', - ''forum::notification::get_url'', - ''TCL'' + 'NotificationType', + 'forums_forum_notif_type', + 'GetURL', + 'forum::notification::get_url', + 'TCL' ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', - ''forums_forum_notif_type'', - ''ProcessReply'', - ''forum::notification::process_reply'', - ''TCL'' + 'NotificationType', + 'forums_forum_notif_type', + 'ProcessReply', + 'forum::notification::process_reply', + 'TCL' ); PERFORM acs_sc_binding__new ( - ''NotificationType'', - ''forums_forum_notif_type'' + 'NotificationType', + 'forums_forum_notif_type' ); v_foo:= notification_type__new ( NULL, impl_id, - ''forums_forum_notif'', - ''Forum Notification'', - ''Notifications for Entire Forums'', + 'forums_forum_notif', + 'Forum Notification', + 'Notifications for Entire Forums', now(), NULL, NULL, @@ -60,47 +67,47 @@ insert into notification_types_intervals (type_id, interval_id) select v_foo, interval_id - from notification_intervals where name in (''instant'',''hourly'',''daily''); + from notification_intervals where name in ('instant','hourly','daily'); insert into notification_types_del_methods (type_id, delivery_method_id) select v_foo, delivery_method_id - from notification_delivery_methods where short_name in (''email''); + from notification_delivery_methods where short_name in ('email'); -- the notification type impl impl_id := acs_sc_impl__new ( - ''NotificationType'', - ''forums_message_notif_type'', - ''forums'' + 'NotificationType', + 'forums_message_notif_type', + 'forums' ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', - ''forums_message_notif_type'', - ''GetURL'', - ''forum::notification::get_url'', - ''TCL'' + 'NotificationType', + 'forums_message_notif_type', + 'GetURL', + 'forum::notification::get_url', + 'TCL' ); v_foo := acs_sc_impl_alias__new ( - ''NotificationType'', - ''forums_message_notif_type'', - ''ProcessReply'', - ''forum::notification::process_reply'', - ''TCL'' + 'NotificationType', + 'forums_message_notif_type', + 'ProcessReply', + 'forum::notification::process_reply', + 'TCL' ); PERFORM acs_sc_binding__new ( - ''NotificationType'', - ''forums_message_notif_type'' + 'NotificationType', + 'forums_message_notif_type' ); v_foo:= notification_type__new ( NULL, impl_id, - ''forums_message_notif'', - ''Message Notification'', - ''Notifications for Message Thread'', + 'forums_message_notif', + 'Message Notification', + 'Notifications for Message Thread', now(), NULL, NULL, @@ -111,16 +118,17 @@ insert into notification_types_intervals (type_id, interval_id) select v_foo, interval_id - from notification_intervals where name in (''instant'',''hourly'',''daily''); + from notification_intervals where name in ('instant','hourly','daily'); insert into notification_types_del_methods (type_id, delivery_method_id) select v_foo, delivery_method_id - from notification_delivery_methods where short_name in (''email''); + from notification_delivery_methods where short_name in ('email'); return (0); -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0(); drop function inline_0(); \ No newline at end of file Index: openacs-4/packages/forums/sql/postgresql/forums-notifications-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/Attic/forums-notifications-sanitize.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/forums-notifications-sanitize.sql 10 Jul 2002 18:31:26 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/forums-notifications-sanitize.sql 12 Aug 2013 09:34:30 -0000 1.3 @@ -8,20 +8,27 @@ -- lifted from Gilbert's UBB forums. Thanks Orchard Labs. -- -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE row record; -begin +BEGIN for row in select nt.type_id from notification_types nt - where nt.short_name in (''forums_forum_notif'', ''forums_message_notif'') + where nt.short_name in ('forums_forum_notif', 'forums_message_notif') loop perform notification_type__delete(row.type_id); end loop; return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0 (); @@ -30,39 +37,46 @@ -- Service contract drop stuff was missing - Roberto Mello -- -create function inline_0() returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE impl_id integer; v_foo integer; -begin +BEGIN -- the notification type impl impl_id := acs_sc_impl__get_id ( - ''NotificationType'', -- impl_contract_name - ''forums_forum_notif_type'' -- impl_name + 'NotificationType', -- impl_contract_name + 'forums_forum_notif_type' -- impl_name ); PERFORM acs_sc_binding__delete ( - ''NotificationType'', - ''forums_forum_notif_type'' + 'NotificationType', + 'forums_forum_notif_type' ); v_foo := acs_sc_impl_alias__delete ( - ''NotificationType'', -- impl_contract_name - ''forums_forum_notif_type'', -- impl_name - ''GetURL'' -- impl_operation_name + 'NotificationType', -- impl_contract_name + 'forums_forum_notif_type', -- impl_name + 'GetURL' -- impl_operation_name ); v_foo := acs_sc_impl_alias__delete ( - ''NotificationType'', -- impl_contract_name - ''forums_forum_notif_type'', -- impl_name - ''ProcessReply'' -- impl_operation_name + 'NotificationType', -- impl_contract_name + 'forums_forum_notif_type', -- impl_name + 'ProcessReply' -- impl_operation_name ); select into v_foo type_id from notification_types where sc_impl_id = impl_id - and short_name = ''forums_forum_notif''; + and short_name = 'forums_forum_notif'; perform notification_type__delete (v_foo); @@ -71,44 +85,44 @@ and interval_id in ( select interval_id from notification_intervals - where name in (''instant'',''hourly'',''daily'') + where name in ('instant','hourly','daily') ); delete from notification_types_del_methods where type_id = v_foo and delivery_method_id in ( select delivery_method_id from notification_delivery_methods - where short_name in (''email'') + where short_name in ('email') ); -- the notification type impl impl_id := acs_sc_impl__get_id ( - ''NotificationType'', - ''forums_message_notif_type'' + 'NotificationType', + 'forums_message_notif_type' ); PERFORM acs_sc_binding__delete ( - ''NotificationType'', - ''forums_message_notif_type'' + 'NotificationType', + 'forums_message_notif_type' ); v_foo := acs_sc_impl_alias__delete ( - ''NotificationType'', - ''forums_message_notif_type'', - ''GetURL'' + 'NotificationType', + 'forums_message_notif_type', + 'GetURL' ); v_foo := acs_sc_impl_alias__delete ( - ''NotificationType'', - ''forums_message_notif_type'', - ''ProcessReply'' + 'NotificationType', + 'forums_message_notif_type', + 'ProcessReply' ); select into v_foo type_id from notification_types where sc_impl_id = impl_id - and short_name = ''forums_message_notif''; + and short_name = 'forums_message_notif'; perform notification_type__delete (v_foo); @@ -117,20 +131,21 @@ and interval_id in ( select interval_id from notification_intervals - where name in (''instant'',''hourly'',''daily'') + where name in ('instant','hourly','daily') ); delete from notification_types_del_methods where type_id = v_foo and delivery_method_id in ( select delivery_method_id from notification_delivery_methods - where short_name in (''email'') + where short_name in ('email') ); return (0); -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + select inline_0(); drop function inline_0(); Index: openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql 4 Jun 2007 12:43:47 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/forums-reading-info-create.sql 12 Aug 2013 09:34:30 -0000 1.3 @@ -35,13 +35,21 @@ -- remove reading_info for thread (upon new message, upon message deletion, or state change) -create or replace function forums_reading_info__remove_msg (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- added +select define_function_args('forums_reading_info__remove_msg','message_id'); + +-- +-- procedure forums_reading_info__remove_msg/1 +-- +CREATE OR REPLACE FUNCTION forums_reading_info__remove_msg( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_forum_id integer; v_reading RECORD; -begin +BEGIN select forum_id from forums_messages where message_id = p_message_id into v_forum_id; for v_reading in select user_id from forums_reading_info @@ -54,19 +62,28 @@ end loop; return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + -- mark_all_read: -create or replace function forums_reading_info__user_add_forum (integer,integer) -returns integer as ' -declare - p_forum_id alias for $1; - p_user_id alias for $2; + + +-- added +select define_function_args('forums_reading_info__user_add_forum','forum_id,user_id'); + +-- +-- procedure forums_reading_info__user_add_forum/2 +-- +CREATE OR REPLACE FUNCTION forums_reading_info__user_add_forum( + p_forum_id integer, + p_user_id integer +) RETURNS integer AS $$ +DECLARE v_message RECORD; v_read_p RECORD; -begin +BEGIN for v_message in select message_id from forums_messages_approved where forum_id = p_forum_id @@ -84,19 +101,28 @@ delete from forums_reading_info_user where forum_id = p_forum_id and user_id = p_user_id; insert into forums_reading_info_user (forum_id,user_id,threads_read) VALUES (p_forum_id,p_user_id,(select approved_thread_count from forums_forums where forum_id = p_forum_id)); return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + -- mark message read for user -create or replace function forums_reading_info__user_add_msg (integer,integer) -returns integer as ' -declare - p_root_message_id alias for $1; - p_user_id alias for $2; + + +-- added +select define_function_args('forums_reading_info__user_add_msg','root_message_id,user_id'); + +-- +-- procedure forums_reading_info__user_add_msg/2 +-- +CREATE OR REPLACE FUNCTION forums_reading_info__user_add_msg( + p_root_message_id integer, + p_user_id integer +) RETURNS integer AS $$ +DECLARE v_read_p RECORD; v_forum_id integer; v_exists boolean; -begin +BEGIN select forum_id from forums_messages where message_id = p_root_message_id into v_forum_id; select into v_read_p * from forums_reading_info where user_id = p_user_id and root_message_id = p_root_message_id; if NOT FOUND @@ -114,25 +140,34 @@ end if; return 0; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + -- move thread to other forum -create or replace function forums_message__move_update_reading_info (integer,integer,integer) -returns integer as ' -declare - p_message_id alias for $1; - p_old_forum_id alias for $2; - p_new_forum_id alias for $3; + + +-- added +select define_function_args('forums_message__move_update_reading_info','message_id,old_forum_id,new_forum_id'); + +-- +-- procedure forums_message__move_update_reading_info/3 +-- +CREATE OR REPLACE FUNCTION forums_message__move_update_reading_info( + p_message_id integer, + p_old_forum_id integer, + p_new_forum_id integer +) RETURNS integer AS $$ +DECLARE v_message record; v_users record; v_read_p record; v_threads integer; -begin - raise notice ''updating for message %'', p_message_id; +BEGIN + raise notice 'updating for message %', p_message_id; for v_users in select user_id from forums_reading_info where root_message_id = p_message_id loop - raise notice ''updating for user %'', v_users.user_id; + raise notice 'updating for user %', v_users.user_id; -- down the number of threads read in old forum update forums_reading_info_user set threads_read = threads_read - 1 where forum_id = p_old_forum_id and user_id = v_users.user_id; @@ -150,19 +185,28 @@ return 1; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- move thread to other thread -create or replace function forums_message__move_thread_thread_update_reading_info (integer,integer, integer) -returns integer as ' -declare - p_source_message_id alias for $1; - p_source_forum_id alias for $2; - p_target_message_id alias for $3; + + +-- added +select define_function_args('forums_message__move_thread_thread_update_reading_info','source_message_id,source_forum_id,target_message_id'); + +-- +-- procedure forums_message__move_thread_thread_update_reading_info/3 +-- +CREATE OR REPLACE FUNCTION forums_message__move_thread_thread_update_reading_info( + p_source_message_id integer, + p_source_forum_id integer, + p_target_message_id integer +) RETURNS integer AS $$ +DECLARE v_target_forum_id integer; v_users record; -begin +BEGIN select forum_id from forums_messages where message_id = p_target_message_id into v_target_forum_id; -- for all users that have read target, but not the source, remove target_info for v_users in select user_id from forums_reading_info fri where root_message_id = p_target_message_id and not exists(select 1 from forums_reading_info where root_message_id = p_source_message_id and user_id = fri.user_id) @@ -181,20 +225,29 @@ end loop; return 1; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- move message to other thread -create or replace function forums_message__move_thread_update_reading_info (integer,integer,integer) -returns integer as ' -declare - p_source_message_id alias for $1; - p_source_old_root_message_id alias for $2; - p_target_message_id alias for $3; + + +-- added +select define_function_args('forums_message__move_thread_update_reading_info','source_message_id,source_old_root_message_id,target_message_id'); + +-- +-- procedure forums_message__move_thread_update_reading_info/3 +-- +CREATE OR REPLACE FUNCTION forums_message__move_thread_update_reading_info( + p_source_message_id integer, + p_source_old_root_message_id integer, + p_target_message_id integer +) RETURNS integer AS $$ +DECLARE v_target_forum_id integer; v_users record; -begin +BEGIN select forum_id from forums_messages where message_id = p_target_message_id into v_target_forum_id; - raise notice ''v_target_forum_id %'', v_target_forum_id; + raise notice 'v_target_forum_id %', v_target_forum_id; -- for all users that have read target, but not the source, remove target_info for v_users in select user_id from forums_reading_info fri where root_message_id = p_target_message_id and not exists(select 1 from forums_reading_info where root_message_id = p_source_old_root_message_id and user_id = fri.user_id) loop @@ -205,15 +258,22 @@ end loop; return 1; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- recount reading_info_user from reading_info -create or replace function forums_message__repair_reading_info () -returns integer as ' -declare + + +-- +-- procedure forums_message__repair_reading_info/0 +-- +CREATE OR REPLACE FUNCTION forums_message__repair_reading_info( + +) RETURNS integer AS $$ +DECLARE v_users record; -begin +BEGIN delete from forums_reading_info_user; for v_users in select user_id,(select forum_id from forums_messages where message_id = root_message_id) as forum_id, count(root_message_id) as threads_read from forums_reading_info group by forum_id,user_id @@ -223,4 +283,5 @@ (v_users.forum_id,v_users.user_id,v_users.threads_read); end loop; return 1; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql 8 Aug 2006 21:26:51 -0000 1.6 +++ openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql 12 Aug 2013 09:34:30 -0000 1.7 @@ -8,42 +8,49 @@ -- til: only indexing full threads. changes to child messages will be treated as -- change to the thread. -create or replace function forums_message_search__itrg () -returns trigger as ' -begin +CREATE OR REPLACE FUNCTION forums_message_search__itrg () RETURNS trigger AS $$ +BEGIN if new.parent_id is null then - perform search_observer__enqueue(new.message_id,''INSERT''); + perform search_observer__enqueue(new.message_id,'INSERT'); else - perform search_observer__enqueue(forums_message__root_message_id(new.parent_id),''UPDATE''); + perform search_observer__enqueue(forums_message__root_message_id(new.parent_id),'UPDATE'); end if; return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function forums_message_search__dtrg () -returns trigger as ' -declare + + +-- +-- procedure forums_message_search__dtrg/0 +-- +CREATE OR REPLACE FUNCTION forums_message_search__dtrg( + +) RETURNS trigger AS $$ +DECLARE v_root_message_id forums_messages.message_id%TYPE; -begin +BEGIN -- if the deleted msg has a parent then its an UPDATE to a thread, otherwise a DELETE. if old.parent_id is null then - perform search_observer__enqueue(old.message_id,''DELETE''); + perform search_observer__enqueue(old.message_id,'DELETE'); else v_root_message_id := forums_message__root_message_id(old.parent_id); if not v_root_message_id is null then - perform search_observer__enqueue(v_root_message_id,''UPDATE''); + perform search_observer__enqueue(v_root_message_id,'UPDATE'); end if; end if; return old; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function forums_message_search__utrg () -returns trigger as ' -begin - perform search_observer__enqueue(forums_message__root_message_id (old.message_id),''UPDATE''); +CREATE OR REPLACE FUNCTION forums_message_search__utrg () RETURNS trigger AS $$ +BEGIN + perform search_observer__enqueue(forums_message__root_message_id (old.message_id),'UPDATE'); return old; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create trigger forums_message_search__itrg after insert on forums_messages @@ -58,29 +65,29 @@ -- forums_forums indexing trigger -create or replace function forums_forums_search__itrg () -returns trigger as ' -begin - perform search_observer__enqueue(new.forum_id,''INSERT''); +CREATE OR REPLACE FUNCTION forums_forums_search__itrg () RETURNS trigger AS $$ +BEGIN + perform search_observer__enqueue(new.forum_id,'INSERT'); return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function forums_forums_search__utrg () -returns trigger as ' -begin - perform search_observer__enqueue(new.forum_id,''UPDATE''); +CREATE OR REPLACE FUNCTION forums_forums_search__utrg () RETURNS trigger AS $$ +BEGIN + perform search_observer__enqueue(new.forum_id,'UPDATE'); return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function forums_forums_search__dtrg () -returns trigger as ' -begin - perform search_observer__enqueue(old.forum_id,''DELETE''); +CREATE OR REPLACE FUNCTION forums_forums_search__dtrg () RETURNS trigger AS $$ +BEGIN + perform search_observer__enqueue(old.forum_id,'DELETE'); return old; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql 28 Jun 2002 00:14:16 -0000 1.6 +++ openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql 12 Aug 2013 09:34:30 -0000 1.7 @@ -13,17 +13,23 @@ -- This is the sortkey code -- -create function forums_mess_insert_tr () -returns opaque as ' -declare + + +-- +-- procedure forums_mess_insert_tr/0 +-- +CREATE OR REPLACE FUNCTION forums_mess_insert_tr( + +) RETURNS trigger AS $$ +DECLARE v_max_child_sortkey forums_forums.max_child_sortkey%TYPE; v_parent_sortkey forums_messages.tree_sortkey%TYPE; -begin +BEGIN if new.parent_id is null then - select '''', max_child_sortkey + select '', max_child_sortkey into v_parent_sortkey, v_max_child_sortkey from forums_forums where forum_id = new.forum_id @@ -37,7 +43,7 @@ else - select coalesce(tree_sortkey, ''''), max_child_sortkey + select coalesce(tree_sortkey, ''), max_child_sortkey into v_parent_sortkey, v_max_child_sortkey from forums_messages where message_id = new.parent_id @@ -54,7 +60,8 @@ new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey; return new; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create trigger forums_mess_insert_tr before insert on forums_messages Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql 3 Oct 2003 19:38:15 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql 12 Aug 2013 09:34:30 -0000 1.2 @@ -1,11 +1,18 @@ alter table forums_forums add column last_post timestamptz; alter table forums_messages add column last_child_post timestamptz; -create or replace function t () returns integer as ' -declare + + +-- +-- procedure t/0 +-- +CREATE OR REPLACE FUNCTION t( + +) RETURNS integer AS $$ +DECLARE v_record record; v_timestamp timestamptz; -begin +BEGIN for v_record in select forum_id from forums_forums @@ -43,7 +50,8 @@ return 1; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select t(); @@ -68,28 +76,36 @@ from forums_forums where enabled_p = 't'; -create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_message_id alias for $1; - p_object_type alias for $2; - p_forum_id alias for $3; - p_subject alias for $4; - p_content alias for $5; - p_html_p alias for $6; - p_user_id alias for $7; - p_posting_date alias for $8; - p_state alias for $9; - p_parent_id alias for $10; - p_creation_date alias for $11; - p_creation_user alias for $12; - p_creation_ip alias for $13; - p_context_id alias for $14; + + +-- added +select define_function_args('forums_message__new','message_id,object_type,forum_id,subject,content,html_p,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); + +-- +-- procedure forums_message__new/14 +-- +CREATE OR REPLACE FUNCTION forums_message__new( + p_message_id integer, + p_object_type varchar, + p_forum_id integer, + p_subject varchar, + p_content text, + p_html_p char, + p_user_id integer, + p_posting_date timestamptz, + p_state varchar, + p_parent_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer +) RETURNS integer AS $$ +DECLARE v_message_id integer; v_forum_policy forums_forums.posting_policy%TYPE; v_state forums_messages.state%TYPE; v_posting_date forums_messages.posting_date%TYPE; -begin +BEGIN v_message_id := acs_object__new( p_message_id, p_object_type, @@ -105,9 +121,9 @@ from forums_forums where forum_id = p_forum_id; - if v_forum_policy = ''moderated'' - then v_state := ''pending''; - else v_state := ''approved''; + if v_forum_policy = 'moderated' + then v_state := 'pending'; + else v_state := 'approved'; end if; else v_state := p_state; @@ -134,25 +150,34 @@ return v_message_id; -end; -' language 'plpgsql'; +END; -create or replace function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_forum_id alias for $1; - p_object_type alias for $2; - p_name alias for $3; - p_charter alias for $4; - p_presentation_type alias for $5; - p_posting_policy alias for $6; - p_package_id alias for $7; - p_creation_date alias for $8; - p_creation_user alias for $9; - p_creation_ip alias for $10; - p_context_id alias for $11; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('forums_forum__new','forum_id,object_type,name,charter,presentation_type,posting_policy,package_id,creation_date,creation_user,creation_ip,context_id'); + +-- +-- procedure forums_forum__new/11 +-- +CREATE OR REPLACE FUNCTION forums_forum__new( + p_forum_id integer, + p_object_type varchar, + p_name varchar, + p_charter varchar, + p_presentation_type varchar, + p_posting_policy varchar, + p_package_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer +) RETURNS integer AS $$ +DECLARE v_forum_id integer; -begin +BEGIN v_forum_id:= acs_object__new( p_forum_id, p_object_type, @@ -168,6 +193,7 @@ (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id); return v_forum_id; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; + Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.2d-0.3d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.2d-0.3d.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.2d-0.3d.sql 3 Oct 2003 19:38:15 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.2d-0.3d.sql 12 Aug 2013 09:34:30 -0000 1.2 @@ -4,14 +4,22 @@ create unique index forums_mess_forum_sk_un on forums_messages(forum_id, tree_sortkey); -create or replace function forums_message__root_message_id (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- added +select define_function_args('forums_message__root_message_id','message_id'); + +-- +-- procedure forums_message__root_message_id/1 +-- +CREATE OR REPLACE FUNCTION forums_message__root_message_id( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_message_id forums_messages.message_id%TYPE; v_forum_id forums_messages.forum_id%TYPE; v_sortkey forums_messages.tree_sortkey%TYPE; -begin +BEGIN select forum_id, tree_sortkey into v_forum_id, v_sortkey from forums_messages @@ -24,5 +32,6 @@ and tree_sortkey = tree_ancestor_key(v_sortkey, 1); return v_message_id; -end; -' language 'plpgsql' with(isstrict,iscachable); +END; + +$$ LANGUAGE plpgsql with(isstrict,iscachable); Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0.6-1.0.7d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0.6-1.0.7d.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0.6-1.0.7d.sql 13 Jan 2005 13:58:16 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0.6-1.0.7d.sql 12 Aug 2013 09:34:30 -0000 1.3 @@ -66,26 +66,32 @@ drop function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer); -create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_message_id alias for $1; - p_object_type alias for $2; - p_forum_id alias for $3; - p_subject alias for $4; - p_content alias for $5; - p_format alias for $6; - p_user_id alias for $7; - p_state alias for $8; - p_parent_id alias for $9; - p_creation_date alias for $10; - p_creation_user alias for $11; - p_creation_ip alias for $12; - p_context_id alias for $13; + + +-- +-- procedure forums_message__new/13 +-- +CREATE OR REPLACE FUNCTION forums_message__new( + p_message_id integer, + p_object_type varchar, -- default 'forums_message' + p_forum_id integer, + p_subject varchar, + p_content text, + p_format char, + p_user_id integer, + p_state varchar, + p_parent_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer + +) RETURNS integer AS $$ +DECLARE v_message_id integer; v_forum_policy forums_forums.posting_policy%TYPE; v_state forums_messages.state%TYPE; -begin +BEGIN v_message_id := acs_object__new( p_message_id, p_object_type, @@ -101,9 +107,9 @@ from forums_forums where forum_id = p_forum_id; - if v_forum_policy = ''moderated'' - then v_state := ''pending''; - else v_state := ''approved''; + if v_forum_policy = 'moderated' + then v_state := 'pending'; + else v_state := 'approved'; end if; else v_state := p_state; @@ -120,7 +126,7 @@ where forum_id = p_forum_id; if p_parent_id is null then - if v_state = ''approved'' then + if v_state = 'approved' then update forums_forums set thread_count = thread_count + 1, approved_thread_count = approved_thread_count + 1 @@ -131,7 +137,7 @@ where forum_id=p_forum_id; end if; else - if v_state = ''approved'' then + if v_state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count + 1, reply_count = reply_count + 1, @@ -147,37 +153,44 @@ return v_message_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args ('forums_message__set_state', 'message_id,state'); -create or replace function forums_message__set_state(integer,varchar) returns integer as ' -declare - p_message_id alias for $1; - p_state alias for $2; + + +-- +-- procedure forums_message__set_state/2 +-- +CREATE OR REPLACE FUNCTION forums_message__set_state( + p_message_id integer, + p_state varchar +) RETURNS integer AS $$ +DECLARE v_cur record; -begin +BEGIN select into v_cur * from forums_messages where message_id = p_message_id; if v_cur.parent_id is null then - if p_state = ''approved'' and v_cur.state <> ''approved'' then + if p_state = 'approved' and v_cur.state <> 'approved' then update forums_forums set approved_thread_count = approved_thread_count + 1 where forum_id=v_cur.forum_id; - elsif p_state <> ''approved'' and v_cur.state = ''approved'' then + elsif p_state <> 'approved' and v_cur.state = 'approved' then update forums_forums set approved_thread_count = approved_thread_count - 1 where forum_id=v_cur.forum_id; end if; else - if p_state = ''approved'' and v_cur.state <> ''approved'' then + if p_state = 'approved' and v_cur.state <> 'approved' then update forums_messages set approved_reply_count = approved_reply_count + 1 where message_id = forums_message__root_message_id(v_cur.message_id); - elsif p_state <> ''approved'' and v_cur.state = ''approved'' then + elsif p_state <> 'approved' and v_cur.state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count - 1 where message_id = forums_message__root_message_id(v_cur.message_id); @@ -190,14 +203,23 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function forums_message__delete (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- added +select define_function_args('forums_message__delete','message_id'); + +-- +-- procedure forums_message__delete/1 +-- +CREATE OR REPLACE FUNCTION forums_message__delete( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_cur record; -begin +BEGIN -- Maintain the forum thread counts @@ -206,7 +228,7 @@ where message_id = p_message_id; if v_cur.parent_id is null then - if v_cur.state = ''approved'' then + if v_cur.state = 'approved' then update forums_forums set thread_count = thread_count - 1, approved_thread_count = approved_thread_count - 1 @@ -216,7 +238,7 @@ set thread_count = thread_count - 1 where forum_id=v_cur.forum_id; end if; - elsif v_cur.state = ''approved'' then + elsif v_cur.state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count - 1, reply_count = reply_count - 1 @@ -230,18 +252,24 @@ perform acs_object__delete(p_message_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args ('forums_message__delete_thread', 'message_id'); -create or replace function forums_message__delete_thread (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- +-- procedure forums_message__delete_thread/1 +-- +CREATE OR REPLACE FUNCTION forums_message__delete_thread( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_forum_id forums_messages.forum_id%TYPE; v_sortkey forums_messages.tree_sortkey%TYPE; v_message RECORD; -begin +BEGIN select forum_id, tree_sortkey into v_forum_id, v_sortkey from forums_messages @@ -270,5 +298,6 @@ perform forums_message__delete(p_message_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql 12 Jul 2004 14:49:59 -0000 1.3 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql 12 Aug 2013 09:34:30 -0000 1.4 @@ -29,28 +29,34 @@ select define_function_args ('forums_message__new', 'message_id,object_type;forums_message,forum_id,subject,content,format,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); -create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_message_id alias for $1; - p_object_type alias for $2; - p_forum_id alias for $3; - p_subject alias for $4; - p_content alias for $5; - p_format alias for $6; - p_user_id alias for $7; - p_posting_date alias for $8; - p_state alias for $9; - p_parent_id alias for $10; - p_creation_date alias for $11; - p_creation_user alias for $12; - p_creation_ip alias for $13; - p_context_id alias for $14; + + +-- +-- procedure forums_message__new/14 +-- +CREATE OR REPLACE FUNCTION forums_message__new( + p_message_id integer, + p_object_type varchar, -- default 'forums_message' + p_forum_id integer, + p_subject varchar, + p_content text, + p_format char, + p_user_id integer, + p_posting_date timestamptz, + p_state varchar, + p_parent_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer + +) RETURNS integer AS $$ +DECLARE v_message_id integer; v_forum_policy forums_forums.posting_policy%TYPE; v_state forums_messages.state%TYPE; v_posting_date forums_messages.posting_date%TYPE; -begin +BEGIN v_message_id := acs_object__new( p_message_id, p_object_type, @@ -66,9 +72,9 @@ from forums_forums where forum_id = p_forum_id; - if v_forum_policy = ''moderated'' - then v_state := ''pending''; - else v_state := ''approved''; + if v_forum_policy = 'moderated' + then v_state := 'pending'; + else v_state := 'approved'; end if; else v_state := p_state; @@ -95,5 +101,6 @@ return v_message_id; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1.2d4-1.1.2d5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1.2d4-1.1.2d5.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1.2d4-1.1.2d5.sql 8 Aug 2006 21:26:51 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1.2d4-1.1.2d5.sql 12 Aug 2013 09:34:30 -0000 1.3 @@ -28,23 +28,24 @@ create function inline_0 () returns integer as' begin - perform acs_privilege__remove_child(''read'',''forum_read''); - perform acs_privilege__remove_child(''create'',''forum_create''); - perform acs_privilege__remove_child(''write'',''forum_write''); - perform acs_privilege__remove_child(''delete'',''forum_delete''); - perform acs_privilege__remove_child(''forum_moderate'',''forum_read''); - perform acs_privilege__remove_child(''forum_moderate'',''forum_post''); - perform acs_privilege__remove_child(''forum_write'',''forum_read''); - perform acs_privilege__remove_child(''forum_write'',''forum_post''); + perform acs_privilege__remove_child('read','forum_read'); + perform acs_privilege__remove_child('create','forum_create'); + perform acs_privilege__remove_child('write','forum_write'); + perform acs_privilege__remove_child('delete','forum_delete'); + perform acs_privilege__remove_child('forum_moderate','forum_read'); + perform acs_privilege__remove_child('forum_moderate','forum_post'); + perform acs_privilege__remove_child('forum_write','forum_read'); + perform acs_privilege__remove_child('forum_write','forum_post'); - perform acs_privilege__drop_privilege(''forum_read''); - perform acs_privilege__drop_privilege(''forum_create''); - perform acs_privilege__drop_privilege(''forum_write''); - perform acs_privilege__drop_privilege(''forum_post''); - perform acs_privilege__drop_privilege(''forum_delete''); + perform acs_privilege__drop_privilege('forum_read'); + perform acs_privilege__drop_privilege('forum_create'); + perform acs_privilege__drop_privilege('forum_write'); + perform acs_privilege__drop_privilege('forum_post'); + perform acs_privilege__drop_privilege('forum_delete'); return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql 30 Mar 2004 21:00:31 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql 12 Aug 2013 09:34:30 -0000 1.3 @@ -1,29 +1,37 @@ -- replace new functions with ones that set acs_object.title, package_id -create or replace function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_forum_id alias for $1; - p_object_type alias for $2; - p_name alias for $3; - p_charter alias for $4; - p_presentation_type alias for $5; - p_posting_policy alias for $6; - p_package_id alias for $7; - p_creation_date alias for $8; - p_creation_user alias for $9; - p_creation_ip alias for $10; - p_context_id alias for $11; + + +-- added +select define_function_args('forums_forum__new','forum_id,object_type,name,charter,presentation_type,posting_policy,package_id,creation_date,creation_user,creation_ip,context_id'); + +-- +-- procedure forums_forum__new/11 +-- +CREATE OR REPLACE FUNCTION forums_forum__new( + p_forum_id integer, + p_object_type varchar, + p_name varchar, + p_charter varchar, + p_presentation_type varchar, + p_posting_policy varchar, + p_package_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer +) RETURNS integer AS $$ +DECLARE v_forum_id integer; -begin +BEGIN v_forum_id:= acs_object__new( p_forum_id, p_object_type, p_creation_date, p_creation_user, p_creation_ip, coalesce(p_context_id, p_package_id), - ''t'', + 't', p_name, p_package_id ); @@ -34,38 +42,47 @@ (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id); return v_forum_id; -end; -' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_message_id alias for $1; - p_object_type alias for $2; - p_forum_id alias for $3; - p_subject alias for $4; - p_content alias for $5; - p_html_p alias for $6; - p_user_id alias for $7; - p_posting_date alias for $8; - p_state alias for $9; - p_parent_id alias for $10; - p_creation_date alias for $11; - p_creation_user alias for $12; - p_creation_ip alias for $13; - p_context_id alias for $14; + + + +-- added +select define_function_args('forums_message__new','message_id,object_type,forum_id,subject,content,html_p,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); + +-- +-- procedure forums_message__new/14 +-- +CREATE OR REPLACE FUNCTION forums_message__new( + p_message_id integer, + p_object_type varchar, + p_forum_id integer, + p_subject varchar, + p_content text, + p_html_p char, + p_user_id integer, + p_posting_date timestamptz, + p_state varchar, + p_parent_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer +) RETURNS integer AS $$ +DECLARE v_message_id integer; v_forum_policy forums_forums.posting_policy%TYPE; v_state forums_messages.state%TYPE; v_posting_date forums_messages.posting_date%TYPE; v_package_id acs_objects.package_id%TYPE; -begin +BEGIN select package_id into v_package_id from forums_forums where forum_id = p_forum_id; if v_package_id is null then - raise exception ''forums_message__new: forum_id % not found'', p_forum_id; + raise exception 'forums_message__new: forum_id % not found', p_forum_id; end if; v_message_id := acs_object__new( @@ -75,7 +92,7 @@ p_creation_user, p_creation_ip, coalesce(p_context_id, p_forum_id), - ''t'', + 't', p_subject, v_package_id ); @@ -86,9 +103,9 @@ from forums_forums where forum_id = p_forum_id; - if v_forum_policy = ''moderated'' - then v_state := ''pending''; - else v_state := ''approved''; + if v_forum_policy = 'moderated' + then v_state := 'pending'; + else v_state := 'approved'; end if; else v_state := p_state; @@ -115,5 +132,6 @@ return v_message_id; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2.0d4-1.3d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2.0d4-1.3d1.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2.0d4-1.3d1.sql 8 Aug 2006 21:26:51 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2.0d4-1.3d1.sql 12 Aug 2013 09:34:31 -0000 1.3 @@ -1,16 +1,23 @@ -- Update the package ids for projects -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE ct RECORD; -begin +BEGIN for ct in select package_id, forum_id from forums_forums loop update acs_objects set package_id = ct.package_id where object_id = ct.forum_id; end loop; return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2d1-1.2d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2d1-1.2d2.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2d1-1.2d2.sql 15 Mar 2005 20:06:45 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2d1-1.2d2.sql 12 Aug 2013 09:34:31 -0000 1.2 @@ -55,33 +55,41 @@ -- -- Replace the procs which manipulate state and new message to save last_poster. -- -create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_message_id alias for $1; - p_object_type alias for $2; - p_forum_id alias for $3; - p_subject alias for $4; - p_content alias for $5; - p_format alias for $6; - p_user_id alias for $7; - p_state alias for $8; - p_parent_id alias for $9; - p_creation_date alias for $10; - p_creation_user alias for $11; - p_creation_ip alias for $12; - p_context_id alias for $13; + + +-- added +select define_function_args('forums_message__new','message_id,object_type,forum_id,subject,content,format,user_id,state,parent_id,creation_date,creation_user,creation_ip,context_id'); + +-- +-- procedure forums_message__new/13 +-- +CREATE OR REPLACE FUNCTION forums_message__new( + p_message_id integer, + p_object_type varchar, + p_forum_id integer, + p_subject varchar, + p_content text, + p_format char, + p_user_id integer, + p_state varchar, + p_parent_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer +) RETURNS integer AS $$ +DECLARE v_message_id integer; v_forum_policy forums_forums.posting_policy%TYPE; v_state forums_messages.state%TYPE; v_posting_date forums_messages.posting_date%TYPE; v_package_id acs_objects.package_id%TYPE; -begin +BEGIN select package_id into v_package_id from forums_forums where forum_id = p_forum_id; if v_package_id is null then - raise exception ''forums_message__new: forum_id % not found'', p_forum_id; + raise exception 'forums_message__new: forum_id % not found', p_forum_id; end if; v_message_id := acs_object__new( @@ -91,7 +99,7 @@ p_creation_user, p_creation_ip, coalesce(p_context_id, p_forum_id), - ''t'', + 't', p_subject, v_package_id ); @@ -102,9 +110,9 @@ from forums_forums where forum_id = p_forum_id; - if v_forum_policy = ''moderated'' - then v_state := ''pending''; - else v_state := ''approved''; + if v_forum_policy = 'moderated' + then v_state := 'pending'; + else v_state := 'approved'; end if; else v_state := p_state; @@ -121,7 +129,7 @@ where forum_id = p_forum_id; if p_parent_id is null then - if v_state = ''approved'' then + if v_state = 'approved' then update forums_forums set thread_count = thread_count + 1, approved_thread_count = approved_thread_count + 1 @@ -132,7 +140,7 @@ where forum_id=p_forum_id; end if; else - if v_state = ''approved'' then + if v_state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count + 1, reply_count = reply_count + 1, @@ -149,39 +157,46 @@ return v_message_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args ('forums_message__set_state', 'message_id,state'); -create or replace function forums_message__set_state(integer,varchar) returns integer as ' -declare - p_message_id alias for $1; - p_state alias for $2; + + +-- +-- procedure forums_message__set_state/2 +-- +CREATE OR REPLACE FUNCTION forums_message__set_state( + p_message_id integer, + p_state varchar +) RETURNS integer AS $$ +DECLARE v_cur record; -begin +BEGIN select into v_cur * from forums_messages where message_id = p_message_id; if v_cur.parent_id is null then - if p_state = ''approved'' and v_cur.state <> ''approved'' then + if p_state = 'approved' and v_cur.state <> 'approved' then update forums_forums set approved_thread_count = approved_thread_count + 1 where forum_id=v_cur.forum_id; - elsif p_state <> ''approved'' and v_cur.state = ''approved'' then + elsif p_state <> 'approved' and v_cur.state = 'approved' then update forums_forums set approved_thread_count = approved_thread_count - 1 where forum_id=v_cur.forum_id; end if; else - if p_state = ''approved'' and v_cur.state <> ''approved'' then + if p_state = 'approved' and v_cur.state <> 'approved' then update forums_messages set approved_reply_count = approved_reply_count + 1, last_poster = (case when v_cur.posting_date > last_child_post then v_cur.user_id else last_poster end), last_child_post = (case when v_cur.posting_date > last_child_post then v_cur.posting_date else last_child_post end) where message_id = forums_message__root_message_id(v_cur.message_id); - elsif p_state <> ''approved'' and v_cur.state = ''approved'' then + elsif p_state <> 'approved' and v_cur.state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count - 1 where message_id = forums_message__root_message_id(v_cur.message_id); @@ -194,16 +209,22 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select define_function_args ('forums_message__delete', 'message_id'); -create or replace function forums_message__delete (integer) -returns integer as ' -declare - p_message_id alias for $1; + + +-- +-- procedure forums_message__delete/1 +-- +CREATE OR REPLACE FUNCTION forums_message__delete( + p_message_id integer +) RETURNS integer AS $$ +DECLARE v_cur record; -begin +BEGIN -- Maintain the forum thread counts @@ -212,7 +233,7 @@ where message_id = p_message_id; if v_cur.parent_id is null then - if v_cur.state = ''approved'' then + if v_cur.state = 'approved' then update forums_forums set thread_count = thread_count - 1, approved_thread_count = approved_thread_count - 1 @@ -222,7 +243,7 @@ set thread_count = thread_count - 1 where forum_id=v_cur.forum_id; end if; - elsif v_cur.state = ''approved'' then + elsif v_cur.state = 'approved' then update forums_messages set approved_reply_count = approved_reply_count - 1, reply_count = reply_count - 1 @@ -235,4 +256,5 @@ perform acs_object__delete(p_message_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.3d3-1.3d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.3d3-1.3d4.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.3d3-1.3d4.sql 15 May 2007 20:14:40 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.3d3-1.3d4.sql 12 Aug 2013 09:34:31 -0000 1.3 @@ -4,30 +4,36 @@ -- And now rewrite the new function as old installations will not have this updated version that stores the package_id select define_function_args('forums_forum__new','forum_id,object_type;forums_forum,name,charter,presentation_type,posting_policy,package_id,creation_date,creation_user,creation_ip,context_id'); -create or replace function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamptz,integer,varchar,integer) -returns integer as ' -declare - p_forum_id alias for $1; - p_object_type alias for $2; - p_name alias for $3; - p_charter alias for $4; - p_presentation_type alias for $5; - p_posting_policy alias for $6; - p_package_id alias for $7; - p_creation_date alias for $8; - p_creation_user alias for $9; - p_creation_ip alias for $10; - p_context_id alias for $11; + + +-- +-- procedure forums_forum__new/11 +-- +CREATE OR REPLACE FUNCTION forums_forum__new( + p_forum_id integer, + p_object_type varchar, -- default 'forums_forum' + p_name varchar, + p_charter varchar, + p_presentation_type varchar, + p_posting_policy varchar, + p_package_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer + +) RETURNS integer AS $$ +DECLARE v_forum_id integer; -begin +BEGIN v_forum_id:= acs_object__new( p_forum_id, p_object_type, p_creation_date, p_creation_user, p_creation_ip, coalesce(p_context_id, p_package_id), - ''t'', + 't', p_name, p_package_id ); @@ -38,5 +44,6 @@ (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id); return v_forum_id; -end; -' language 'plpgsql'; +END; + +$$ LANGUAGE plpgsql;