Index: openacs-4/packages/chat/sql/postgresql/chat-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/postgresql/chat-create.sql,v diff -u -N -r1.8 -r1.8.2.1 --- openacs-4/packages/chat/sql/postgresql/chat-create.sql 14 Dec 2010 15:04:43 -0000 1.8 +++ openacs-4/packages/chat/sql/postgresql/chat-create.sql 20 Jun 2016 09:00:22 -0000 1.8.2.1 @@ -65,8 +65,7 @@ -- create chat room object type -CREATE FUNCTION inline_0() -RETURNS integer +CREATE FUNCTION inline_0() returns integer AS 'declare attr_id acs_attributes.attribute_id%TYPE; begin @@ -176,7 +175,7 @@ end;' -LANGUAGE 'plpgsql'; +language 'plpgsql'; SELECT inline_0(); DROP function inline_0(); @@ -212,8 +211,7 @@ -- create chat transcript object type -CREATE FUNCTION inline_0() -RETURNS integer +CREATE FUNCTION inline_0() returns integer AS 'declare attr_id acs_attributes.attribute_id%TYPE; begin @@ -289,7 +287,7 @@ end;' -LANGUAGE 'plpgsql'; +language 'plpgsql'; SELECT inline_0(); DROP function inline_0(); @@ -342,27 +340,35 @@ --------------------------------- -create or replace function chat_room__new (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean, integer, timestamptz, integer, varchar, varchar) -returns integer as ' -declare - p_room_id alias for $1; - p_pretty_name alias for $2; - p_description alias for $3; - p_moderated_p alias for $4; - p_active_p alias for $5; - p_archive_p alias for $6; - p_auto_flush_p alias for $7; - p_auto_transcript_p alias for $8; - p_context_id alias for $9; - p_creation_date alias for $10; - p_creation_user alias for $11; - p_creation_ip alias for $12; - p_object_type alias for $13; + + +-- added +select define_function_args('chat_room__new','room_id,pretty_name,description,moderated_p,active_p,archive_p,auto_flush_p,auto_transcript_p,context_id,creation_date,creation_user,creation_ip,object_type'); + +-- +-- procedure chat_room__new/13 +-- +CREATE OR REPLACE FUNCTION chat_room__new( + p_room_id integer, + p_pretty_name varchar, + p_description varchar, + p_moderated_p boolean, + p_active_p boolean, + p_archive_p boolean, + p_auto_flush_p boolean, + p_auto_transcript_p boolean, + p_context_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_object_type varchar +) RETURNS integer AS $$ +DECLARE v_room_id chat_rooms.room_id%TYPE; -begin +BEGIN v_room_id := acs_object__new ( null, - ''chat_room'', + 'chat_room', now(), p_creation_user, p_creation_ip, @@ -376,23 +382,33 @@ return v_room_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; --------------------------------- -create function chat_room__name (integer) -returns varchar as ' -declare - p_room_id alias for $1; + + +-- added +select define_function_args('chat_room__name','room_id'); + +-- +-- procedure chat_room__name/1 +-- +CREATE OR REPLACE FUNCTION chat_room__name( + p_room_id integer +) RETURNS varchar AS $$ +DECLARE v_pretty_name chat_rooms.pretty_name%TYPE; -begin +BEGIN select into v_pretty_name pretty_name from chat_rooms where room_id = p_room_id; return v_pretty_name; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; @@ -401,42 +417,68 @@ -create function chat_room__message_count (integer) -returns integer as ' -declare - p_room_id alias for $1; + + +-- added +select define_function_args('chat_room__message_count','room_id'); + +-- +-- procedure chat_room__message_count/1 +-- +CREATE OR REPLACE FUNCTION chat_room__message_count( + p_room_id integer +) RETURNS integer AS $$ +DECLARE v_count integer; -begin +BEGIN select count(*) as total into v_count from chat_msgs where room_id = p_room_id; return v_count; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; --------------------------------- -create function chat_room__delete_all_msgs (integer) -returns integer as ' -declare - p_room_id alias for $1; -begin + + +-- added +select define_function_args('chat_room__delete_all_msgs','room_id'); + +-- +-- procedure chat_room__delete_all_msgs/1 +-- +CREATE OR REPLACE FUNCTION chat_room__delete_all_msgs( + p_room_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN delete from chat_msgs where room_id = p_room_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; --------------------------------- -create function chat_room__del (integer) -returns integer as ' -declare - p_room_id alias for $1; -begin + +-- added +select define_function_args('chat_room__del','room_id'); + +-- +-- procedure chat_room__del/1 +-- +CREATE OR REPLACE FUNCTION chat_room__del( + p_room_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN + --TO DO: delete transcriptions? @@ -452,30 +494,39 @@ PERFORM acs_object__delete(p_room_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; --------------------------------- -create function chat_transcript__new (varchar, varchar, varchar, integer, integer, timestamptz, integer, varchar, varchar) -returns integer as ' -declare - p_pretty_name alias for $1; - p_contents alias for $2; - p_description alias for $3; - p_room_id alias for $4; - p_context_id alias for $5; - p_creation_date alias for $6; - p_creation_user alias for $7; - p_creation_ip alias for $8; - p_object_type alias for $9; + +-- added +select define_function_args('chat_transcript__new','pretty_name,contents,description,room_id,context_id,creation_date,creation_user,creation_ip,object_type'); + +-- +-- procedure chat_transcript__new/9 +-- +CREATE OR REPLACE FUNCTION chat_transcript__new( + p_pretty_name varchar, + p_contents varchar, + p_description varchar, + p_room_id integer, + p_context_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_object_type varchar +) RETURNS integer AS $$ +DECLARE + v_transcript_id chat_transcripts.transcript_id%TYPE; -begin +BEGIN v_transcript_id := acs_object__new ( null, - ''chat_transcript'', + 'chat_transcript', now(), p_creation_user, p_creation_ip, @@ -486,17 +537,26 @@ values (v_transcript_id, p_pretty_name, p_contents, p_description, p_room_id); return v_transcript_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; ----------------------------- -create function chat_transcript__del (integer) -returns integer as ' -declare - p_transcript_id alias for $1; -begin + +-- added +select define_function_args('chat_transcript__del','transcript_id'); + +-- +-- procedure chat_transcript__del/1 +-- +CREATE OR REPLACE FUNCTION chat_transcript__del( + p_transcript_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN + -- Delete all privileges associate with this transcript delete from acs_permissions where object_id = p_transcript_id; @@ -505,23 +565,32 @@ PERFORM acs_object__delete(p_transcript_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; ---------------------------- -create or replace function chat_room__edit (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean) -returns integer as ' -declare - p_room_id alias for $1; - p_pretty_name alias for $2; - p_description alias for $3; - p_moderated_p alias for $4; - p_active_p alias for $5; - p_archive_p alias for $6; - p_auto_flush_p alias for $7; - p_auto_transcript_p alias for $8; -begin + +-- added +select define_function_args('chat_room__edit','room_id,pretty_name,description,moderated_p,active_p,archive_p,auto_flush_p,auto_transcript_p'); + +-- +-- procedure chat_room__edit/8 +-- +CREATE OR REPLACE FUNCTION chat_room__edit( + p_room_id integer, + p_pretty_name varchar, + p_description varchar, + p_moderated_p boolean, + p_active_p boolean, + p_archive_p boolean, + p_auto_flush_p boolean, + p_auto_transcript_p boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + update chat_rooms set pretty_name = p_pretty_name, description = p_description, @@ -533,31 +602,38 @@ where room_id = p_room_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; --------------------------- -create function chat_room__message_post (integer, varchar, integer, varchar) -returns integer as ' -declare - p_room_id alias for $1; - p_msg alias for $2; - p_creation_user alias for $3; - p_creation_ip alias for $4; - -- p_html_p alias for $3; - -- p_approved_p alias for $4; + + +-- added +select define_function_args('chat_room__message_post','room_id,msg,html_p,approved_p'); + +-- +-- procedure chat_room__message_post/4 +-- +CREATE OR REPLACE FUNCTION chat_room__message_post( + p_room_id integer, + p_msg varchar, + p_html_p integer, + p_approved_p varchar +) RETURNS integer AS $$ +DECLARE v_msg_id chat_msgs.msg_id%TYPE; v_msg_archive_p chat_rooms.archive_p%TYPE; v_msg chat_msgs.msg%TYPE; -begin +BEGIN -- Get msg id from the global acs_object sequence. - select nextval(''t_acs_object_id_seq'') into v_msg_id from dual; + select nextval('t_acs_object_id_seq') into v_msg_id from dual; select archive_p into v_msg_archive_p from chat_rooms where room_id = p_room_id; - if v_msg_archive_p = ''t'' then + if v_msg_archive_p = 't' then v_msg := p_msg; else v_msg := null; @@ -583,19 +659,28 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; --------------------------- -create function chat_transcript__edit (integer, varchar, varchar, varchar ) -returns integer as ' -declare - p_transcript_id alias for $1; - p_pretty_name alias for $2; - p_contents alias for $3; - p_description alias for $4; -begin + + +-- added +select define_function_args('chat_transcript__edit','transcript_id,pretty_name,contents,description'); + +-- +-- procedure chat_transcript__edit/4 +-- +CREATE OR REPLACE FUNCTION chat_transcript__edit( + p_transcript_id integer, + p_pretty_name varchar, + p_contents varchar, + p_description varchar +) RETURNS integer AS $$ +DECLARE +BEGIN update chat_transcripts set pretty_name = p_pretty_name, contents = p_contents, @@ -605,7 +690,8 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/chat/sql/postgresql/chat-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/postgresql/chat-drop.sql,v diff -u -N -r1.4 -r1.4.6.1 --- openacs-4/packages/chat/sql/postgresql/chat-drop.sql 19 Nov 2007 01:14:15 -0000 1.4 +++ openacs-4/packages/chat/sql/postgresql/chat-drop.sql 20 Jun 2016 09:00:22 -0000 1.4.6.1 @@ -8,25 +8,32 @@ --drop objects -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; -begin +BEGIN - for object_rec in select object_id from acs_objects where object_type=''chat_transcript'' + for object_rec in select object_id from acs_objects where object_type='chat_transcript' loop PERFORM acs_object__delete( object_rec.object_id ); end loop; - for object_rec in select object_id from acs_objects where object_type=''chat_room'' + for object_rec in select object_id from acs_objects where object_type='chat_room' loop PERFORM acs_object__delete( object_rec.object_id ); end loop; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); @@ -69,65 +76,65 @@ -- Drop all chat privileges -- -create function inline_0 () -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$ +BEGIN -- Drop child privileges for regular chat user. - PERFORM acs_privilege__remove_child(''chat_user'', ''chat_read''); - PERFORM acs_privilege__remove_child(''chat_user'', ''chat_write''); + PERFORM acs_privilege__remove_child('chat_user', 'chat_read'); + PERFORM acs_privilege__remove_child('chat_user', 'chat_write'); -- Drop child privileges for chat moderator. - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_room_moderate''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_user_ban''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_user_unban''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_user_grant''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_user_revoke''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_transcript_create''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_transcript_view''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_transcript_edit''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_transcript_delete''); - PERFORM acs_privilege__remove_child(''chat_moderator'', ''chat_user''); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_room_moderate'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_user_ban'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_user_unban'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_user_grant'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_user_revoke'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_transcript_create'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_transcript_view'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_transcript_edit'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_transcript_delete'); + PERFORM acs_privilege__remove_child('chat_moderator', 'chat_user'); -- Drop child privileges for chat administrator. - PERFORM acs_privilege__remove_child(''chat_room_admin'', ''chat_room_create''); - PERFORM acs_privilege__remove_child(''chat_room_admin'', ''chat_room_delete''); - PERFORM acs_privilege__remove_child(''chat_room_admin'', ''chat_room_edit''); - PERFORM acs_privilege__remove_child(''chat_room_admin'', ''chat_room_view''); - PERFORM acs_privilege__remove_child(''chat_room_admin'', ''chat_moderator_grant''); - PERFORM acs_privilege__remove_child(''chat_room_admin'', ''chat_moderator_revoke''); - PERFORM acs_privilege__remove_child(''chat_room_admin'', ''chat_moderator''); + PERFORM acs_privilege__remove_child('chat_room_admin', 'chat_room_create'); + PERFORM acs_privilege__remove_child('chat_room_admin', 'chat_room_delete'); + PERFORM acs_privilege__remove_child('chat_room_admin', 'chat_room_edit'); + PERFORM acs_privilege__remove_child('chat_room_admin', 'chat_room_view'); + PERFORM acs_privilege__remove_child('chat_room_admin', 'chat_moderator_grant'); + PERFORM acs_privilege__remove_child('chat_room_admin', 'chat_moderator_revoke'); + PERFORM acs_privilege__remove_child('chat_room_admin', 'chat_moderator'); -- remove Site wite admin also administrator of the chat room - PERFORM acs_privilege__remove_child(''admin'', ''chat_room_admin''); + PERFORM acs_privilege__remove_child('admin', 'chat_room_admin'); - PERFORM acs_privilege__drop_privilege(''chat_room_create''); - PERFORM acs_privilege__drop_privilege(''chat_room_view''); - PERFORM acs_privilege__drop_privilege(''chat_room_edit''); - PERFORM acs_privilege__drop_privilege(''chat_room_delete''); - PERFORM acs_privilege__drop_privilege(''chat_transcript_create''); - PERFORM acs_privilege__drop_privilege(''chat_transcript_view''); - PERFORM acs_privilege__drop_privilege(''chat_transcript_edit''); - PERFORM acs_privilege__drop_privilege(''chat_transcript_delete''); - PERFORM acs_privilege__drop_privilege(''chat_room_moderate''); - PERFORM acs_privilege__drop_privilege(''chat_moderator_grant''); - PERFORM acs_privilege__drop_privilege(''chat_moderator_revoke''); - PERFORM acs_privilege__drop_privilege(''chat_user_grant''); - PERFORM acs_privilege__drop_privilege(''chat_user_revoke''); - PERFORM acs_privilege__drop_privilege(''chat_user_ban''); - PERFORM acs_privilege__drop_privilege(''chat_user_unban''); - PERFORM acs_privilege__drop_privilege(''chat_ban''); - PERFORM acs_privilege__drop_privilege(''chat_read''); - PERFORM acs_privilege__drop_privilege(''chat_write''); - PERFORM acs_privilege__drop_privilege(''chat_room_admin''); - PERFORM acs_privilege__drop_privilege(''chat_moderator''); - PERFORM acs_privilege__drop_privilege(''chat_user''); + PERFORM acs_privilege__drop_privilege('chat_room_create'); + PERFORM acs_privilege__drop_privilege('chat_room_view'); + PERFORM acs_privilege__drop_privilege('chat_room_edit'); + PERFORM acs_privilege__drop_privilege('chat_room_delete'); + PERFORM acs_privilege__drop_privilege('chat_transcript_create'); + PERFORM acs_privilege__drop_privilege('chat_transcript_view'); + PERFORM acs_privilege__drop_privilege('chat_transcript_edit'); + PERFORM acs_privilege__drop_privilege('chat_transcript_delete'); + PERFORM acs_privilege__drop_privilege('chat_room_moderate'); + PERFORM acs_privilege__drop_privilege('chat_moderator_grant'); + PERFORM acs_privilege__drop_privilege('chat_moderator_revoke'); + PERFORM acs_privilege__drop_privilege('chat_user_grant'); + PERFORM acs_privilege__drop_privilege('chat_user_revoke'); + PERFORM acs_privilege__drop_privilege('chat_user_ban'); + PERFORM acs_privilege__drop_privilege('chat_user_unban'); + PERFORM acs_privilege__drop_privilege('chat_ban'); + PERFORM acs_privilege__drop_privilege('chat_read'); + PERFORM acs_privilege__drop_privilege('chat_write'); + PERFORM acs_privilege__drop_privilege('chat_room_admin'); + PERFORM acs_privilege__drop_privilege('chat_moderator'); + PERFORM acs_privilege__drop_privilege('chat_user'); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); Index: openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d4-5.0d5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d4-5.0d5.sql,v diff -u -N -r1.2 -r1.2.8.1 --- openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d4-5.0d5.sql 24 Jun 2006 14:23:41 -0000 1.2 +++ openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d4-5.0d5.sql 20 Jun 2016 09:00:22 -0000 1.2.8.1 @@ -12,19 +12,27 @@ alter table chat_msgs drop column creation_date; alter table chat_msgs rename column creation_date2 to creation_date; -create or replace function chat_room__edit (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean) -returns integer as ' -declare - p_room_id alias for $1; - p_pretty_name alias for $2; - p_description alias for $3; - p_moderated_p alias for $4; - p_active_p alias for $5; - p_archive_p alias for $6; - p_auto_flush_p alias for $7; - p_auto_transcript_p alias for $8; -begin + +-- added +select define_function_args('chat_room__edit','room_id,pretty_name,description,moderated_p,active_p,archive_p,auto_flush_p,auto_transcript_p'); + +-- +-- procedure chat_room__edit/8 +-- +CREATE OR REPLACE FUNCTION chat_room__edit( + p_room_id integer, + p_pretty_name varchar, + p_description varchar, + p_moderated_p boolean, + p_active_p boolean, + p_archive_p boolean, + p_auto_flush_p boolean, + p_auto_transcript_p boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + update chat_rooms set pretty_name = p_pretty_name, description = p_description, @@ -36,29 +44,38 @@ where room_id = p_room_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function chat_room__new (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean, integer, timestamptz, integer, varchar, varchar) -returns integer as ' -declare - p_room_id alias for $1; - p_pretty_name alias for $2; - p_description alias for $3; - p_moderated_p alias for $4; - p_active_p alias for $5; - p_archive_p alias for $6; - p_auto_flush_p alias for $7; - p_auto_transcript_p alias for $8; - p_context_id alias for $9; - p_creation_date alias for $10; - p_creation_user alias for $11; - p_creation_ip alias for $12; - p_object_type alias for $13; + + +-- added +select define_function_args('chat_room__new','room_id,pretty_name,description,moderated_p,active_p,archive_p,auto_flush_p,auto_transcript_p,context_id,creation_date,creation_user,creation_ip,object_type'); + +-- +-- procedure chat_room__new/13 +-- +CREATE OR REPLACE FUNCTION chat_room__new( + p_room_id integer, + p_pretty_name varchar, + p_description varchar, + p_moderated_p boolean, + p_active_p boolean, + p_archive_p boolean, + p_auto_flush_p boolean, + p_auto_transcript_p boolean, + p_context_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_object_type varchar +) RETURNS integer AS $$ +DECLARE v_room_id chat_rooms.room_id%TYPE; -begin +BEGIN v_room_id := acs_object__new ( null, - ''chat_room'', + 'chat_room', now(), p_creation_user, p_creation_ip, @@ -72,5 +89,6 @@ return v_room_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d6-5.0d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d6-5.0d7.sql,v diff -u -N -r1.1 -r1.1.6.1 --- openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d6-5.0d7.sql 4 Mar 2008 08:16:17 -0000 1.1 +++ openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d6-5.0d7.sql 20 Jun 2016 09:00:22 -0000 1.1.6.1 @@ -1,25 +1,33 @@ -create function inline_0 () returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE col_exists_p boolean; -begin - select count(*) > 0 into col_exists_p from information_schema.columns where table_name=''chat_rooms'' and column_name=''auto_flush_p''; +BEGIN + select count(*) > 0 into col_exists_p from information_schema.columns where table_name='chat_rooms' and column_name='auto_flush_p'; if not col_exists_p then alter table chat_rooms add column auto_flush_p boolean; - alter table chat_rooms alter column auto_flush_p set default ''t''; - update chat_rooms set auto_flush_p=''t''; + alter table chat_rooms alter column auto_flush_p set default 't'; + update chat_rooms set auto_flush_p='t'; end if; - select count(*) > 0 into col_exists_p from information_schema.columns where table_name=''chat_rooms'' and column_name=''auto_transcript_p''; + select count(*) > 0 into col_exists_p from information_schema.columns where table_name='chat_rooms' and column_name='auto_transcript_p'; if not col_exists_p then alter table chat_rooms add column auto_transcript_p boolean; - alter table chat_rooms alter column auto_transcript_p set default ''f''; - update chat_rooms set auto_transcript_p=''f''; + alter table chat_rooms alter column auto_transcript_p set default 'f'; + update chat_rooms set auto_transcript_p='f'; end if; return null; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); @@ -29,19 +37,27 @@ alter table chat_msgs drop column creation_date; alter table chat_msgs rename column creation_date2 to creation_date; -create or replace function chat_room__edit (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean) -returns integer as ' -declare - p_room_id alias for $1; - p_pretty_name alias for $2; - p_description alias for $3; - p_moderated_p alias for $4; - p_active_p alias for $5; - p_archive_p alias for $6; - p_auto_flush_p alias for $7; - p_auto_transcript_p alias for $8; -begin + +-- added +select define_function_args('chat_room__edit','room_id,pretty_name,description,moderated_p,active_p,archive_p,auto_flush_p,auto_transcript_p'); + +-- +-- procedure chat_room__edit/8 +-- +CREATE OR REPLACE FUNCTION chat_room__edit( + p_room_id integer, + p_pretty_name varchar, + p_description varchar, + p_moderated_p boolean, + p_active_p boolean, + p_archive_p boolean, + p_auto_flush_p boolean, + p_auto_transcript_p boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + update chat_rooms set pretty_name = p_pretty_name, description = p_description, @@ -53,29 +69,38 @@ where room_id = p_room_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function chat_room__new (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean, integer, timestamptz, integer, varchar, varchar) -returns integer as ' -declare - p_room_id alias for $1; - p_pretty_name alias for $2; - p_description alias for $3; - p_moderated_p alias for $4; - p_active_p alias for $5; - p_archive_p alias for $6; - p_auto_flush_p alias for $7; - p_auto_transcript_p alias for $8; - p_context_id alias for $9; - p_creation_date alias for $10; - p_creation_user alias for $11; - p_creation_ip alias for $12; - p_object_type alias for $13; + + +-- added +select define_function_args('chat_room__new','room_id,pretty_name,description,moderated_p,active_p,archive_p,auto_flush_p,auto_transcript_p,context_id,creation_date,creation_user,creation_ip,object_type'); + +-- +-- procedure chat_room__new/13 +-- +CREATE OR REPLACE FUNCTION chat_room__new( + p_room_id integer, + p_pretty_name varchar, + p_description varchar, + p_moderated_p boolean, + p_active_p boolean, + p_archive_p boolean, + p_auto_flush_p boolean, + p_auto_transcript_p boolean, + p_context_id integer, + p_creation_date timestamptz, + p_creation_user integer, + p_creation_ip varchar, + p_object_type varchar +) RETURNS integer AS $$ +DECLARE v_room_id chat_rooms.room_id%TYPE; -begin +BEGIN v_room_id := acs_object__new ( null, - ''chat_room'', + 'chat_room', now(), p_creation_user, p_creation_ip, @@ -89,4 +114,5 @@ return v_room_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d8-5.0d9.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d8-5.0d9.sql,v diff -u -N -r1.1 -r1.1.2.1 --- openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d8-5.0d9.sql 14 Dec 2010 15:04:44 -0000 1.1 +++ openacs-4/packages/chat/sql/postgresql/upgrade/upgrade-5.0d8-5.0d9.sql 20 Jun 2016 09:00:22 -0000 1.1.2.1 @@ -1,26 +1,32 @@ -- PG 9.0 support -- @author Victor Guerra ( vguerra@gmail.com ) -create or replace function chat_room__message_post (integer, varchar, integer, varchar) -returns integer as ' -declare - p_room_id alias for $1; - p_msg alias for $2; - p_creation_user alias for $3; - p_creation_ip alias for $4; - -- p_html_p alias for $3; - -- p_approved_p alias for $4; + + +-- added +select define_function_args('chat_room__message_post','room_id,msg,html_p,approved_p'); + +-- +-- procedure chat_room__message_post/4 +-- +CREATE OR REPLACE FUNCTION chat_room__message_post( + p_room_id integer, + p_msg varchar, + p_html_p integer, + p_approved_p varchar +) RETURNS integer AS $$ +DECLARE v_msg_id chat_msgs.msg_id%TYPE; v_msg_archive_p chat_rooms.archive_p%TYPE; v_msg chat_msgs.msg%TYPE; -begin +BEGIN -- Get msg id from the global acs_object sequence. - select nextval(''t_acs_object_id_seq'') into v_msg_id from dual; + select nextval('t_acs_object_id_seq') into v_msg_id from dual; select archive_p into v_msg_archive_p from chat_rooms where room_id = p_room_id; - if v_msg_archive_p = ''t'' then + if v_msg_archive_p = 't' then v_msg := p_msg; else v_msg := null; @@ -46,4 +52,5 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-create.sql,v diff -u -N -r1.1 -r1.1.12.1 --- openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-create.sql 14 Mar 2006 12:23:37 -0000 1.1 +++ openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-create.sql 20 Jun 2016 09:04:30 -0000 1.1.12.1 @@ -20,158 +20,165 @@ -- @creation-date 2004-10-10 -- @version $Id: chat-admin-portlet-create.sql,v 0.1 2004/10/10 -create function inline_0() -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE ds_id portal_datasources.datasource_id%TYPE; -begin +BEGIN - ds_id = portal_datasource__new( ''chat_admin_portlet'', ''Chat Admin portlet''); + ds_id = portal_datasource__new( 'chat_admin_portlet', 'Chat Admin portlet'); -- the standard 4 params -- shadeable_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''shadeable_p'', - ''f'' + 't', + 't', + 'shadeable_p', + 'f' ); -- hideable_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''hideable_p'', - ''f'' + 't', + 't', + 'hideable_p', + 'f' ); -- user_editable_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''user_editable_p'', - ''f'' + 't', + 't', + 'user_editable_p', + 'f' ); -- shaded_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''shaded_p'', - ''f'' + 't', + 't', + 'shaded_p', + 'f' ); -- link_hideable_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''link_hideable_p'', - ''t'' + 't', + 't', + 'link_hideable_p', + 't' ); -- chat_admin-specific params -- package_id must be configured perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''f'', - ''package_id'', - '''' + 't', + 'f', + 'package_id', + '' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); -create function inline_1() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_1() RETURNS integer AS $$ +BEGIN -- create the implementation perform acs_sc_impl__new ( - ''portal_datasource'', - ''chat_admin_portlet'', - ''chat_admin_portlet'' + 'portal_datasource', + 'chat_admin_portlet', + 'chat_admin_portlet' ); -- add all the hooks perform acs_sc_impl_alias__new( - ''portal_datasource'', - ''chat_admin_portlet'', - ''GetMyName'', - ''chat_admin_portlet::get_my_name'', - ''TCL'' + 'portal_datasource', + 'chat_admin_portlet', + 'GetMyName', + 'chat_admin_portlet::get_my_name', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_admin_portlet'', - ''GetPrettyName'', - ''chat_admin_portlet::get_pretty_name'', - ''TCL'' + 'portal_datasource', + 'chat_admin_portlet', + 'GetPrettyName', + 'chat_admin_portlet::get_pretty_name', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_admin_portlet'', - ''Link'', - ''chat_admin_portlet::link'', - ''TCL'' + 'portal_datasource', + 'chat_admin_portlet', + 'Link', + 'chat_admin_portlet::link', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_admin_portlet'', - ''AddSelfToPage'', - ''chat_admin_portlet::add_self_to_page'', - ''TCL'' + 'portal_datasource', + 'chat_admin_portlet', + 'AddSelfToPage', + 'chat_admin_portlet::add_self_to_page', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_admin_portlet'', - ''Show'', - ''chat_admin_portlet::show'', - ''TCL'' + 'portal_datasource', + 'chat_admin_portlet', + 'Show', + 'chat_admin_portlet::show', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_admin_portlet'', - ''Edit'', - ''chat_admin_portlet::edit'', - ''TCL'' + 'portal_datasource', + 'chat_admin_portlet', + 'Edit', + 'chat_admin_portlet::edit', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_admin_portlet'', - ''RemoveSelfFromPage'', - ''chat_admin_portlet::remove_self_from_page'', - ''TCL'' + 'portal_datasource', + 'chat_admin_portlet', + 'RemoveSelfFromPage', + 'chat_admin_portlet::remove_self_from_page', + 'TCL' ); -- Add the binding perform acs_sc_binding__new ( - ''portal_datasource'', - ''chat_admin_portlet'' + 'portal_datasource', + 'chat_admin_portlet' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_1(); drop function inline_1(); Index: openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-drop.sql,v diff -u -N -r1.1 -r1.1.12.1 --- openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-drop.sql 14 Mar 2006 12:23:37 -0000 1.1 +++ openacs-4/packages/chat-portlet/sql/postgresql/chat-admin-portlet-drop.sql 20 Jun 2016 09:04:30 -0000 1.1.12.1 @@ -20,18 +20,24 @@ -- @creation-date 2004-10-10 -- @version $Id: chat-admin-portlet-drop.sql,v 0.1 2004/10/10 -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE ds_id portal_datasources.datasource_id%TYPE; -begin +BEGIN select datasource_id into ds_id from portal_datasources - where name = ''chat_admin_portlet''; + where name = 'chat_admin_portlet'; if not found then - raise exception ''No datasource_id found here '',ds_id ; + raise exception 'No datasource_id found here ',ds_id ; ds_id := null; end if; @@ -42,7 +48,8 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); Index: openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-create.sql,v diff -u -N -r1.1 -r1.1.12.1 --- openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-create.sql 14 Mar 2006 12:23:37 -0000 1.1 +++ openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-create.sql 20 Jun 2016 09:04:30 -0000 1.1.12.1 @@ -20,161 +20,168 @@ -- @creation-date 2004-10-10 -- @version $Id: chat-portlet-create.sql,v 0.1 2004/10/10 -create function inline_0() -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE ds_id portal_datasources.datasource_id%TYPE; -begin +BEGIN ds_id = portal_datasource__new( - ''chat_portlet'', -- Name - ''Chat portlet'' -- description + 'chat_portlet', -- Name + 'Chat portlet' -- description ); -- the standard 4 params -- shadeable_p perform portal_datasource__set_def_param ( ds_id, -- datasource_id - ''t'', -- config_required_p - ''t'', -- configured_p - ''shadeable_p'', -- key - ''t'' -- value + 't', -- config_required_p + 't', -- configured_p + 'shadeable_p', -- key + 't' -- value ); -- hideable_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''hideable_p'', - ''t'' + 't', + 't', + 'hideable_p', + 't' ); -- user_editable_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''user_editable_p'', - ''f'' + 't', + 't', + 'user_editable_p', + 'f' ); -- shaded_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''shaded_p'', - ''f'' + 't', + 't', + 'shaded_p', + 'f' ); -- link_hideable_p perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''t'', - ''link_hideable_p'', - ''t'' + 't', + 't', + 'link_hideable_p', + 't' ); -- chat-specific params -- community_id must be configured perform portal_datasource__set_def_param ( ds_id, - ''t'', - ''f'', - ''package_id'', - '''' + 't', + 'f', + 'package_id', + '' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0(); drop function inline_0(); -create function inline_1() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION inline_1() RETURNS integer AS $$ +BEGIN -- create the implementation perform acs_sc_impl__new ( - ''portal_datasource'', - ''chat_portlet'', - ''chat_portlet'' + 'portal_datasource', + 'chat_portlet', + 'chat_portlet' ); -- add all the hooks perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_portlet'', - ''GetMyName'', - ''chat_portlet::get_my_name'', - ''TCL'' + 'portal_datasource', + 'chat_portlet', + 'GetMyName', + 'chat_portlet::get_my_name', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_portlet'', - ''GetPrettyName'', - ''chat_portlet::get_pretty_name'', - ''TCL'' + 'portal_datasource', + 'chat_portlet', + 'GetPrettyName', + 'chat_portlet::get_pretty_name', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_portlet'', - ''Link'', - ''chat_portlet::link'', - ''TCL'' + 'portal_datasource', + 'chat_portlet', + 'Link', + 'chat_portlet::link', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_portlet'', - ''AddSelfToPage'', - ''chat_portlet::add_self_to_page'', - ''TCL'' + 'portal_datasource', + 'chat_portlet', + 'AddSelfToPage', + 'chat_portlet::add_self_to_page', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_portlet'', - ''Show'', - ''chat_portlet::show'', - ''TCL'' + 'portal_datasource', + 'chat_portlet', + 'Show', + 'chat_portlet::show', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_portlet'', - ''Edit'', - ''chat_portlet::edit'', - ''TCL'' + 'portal_datasource', + 'chat_portlet', + 'Edit', + 'chat_portlet::edit', + 'TCL' ); perform acs_sc_impl_alias__new ( - ''portal_datasource'', - ''chat_portlet'', - ''RemoveSelfFromPage'', - ''chat_portlet::remove_self_from_page'', - ''TCL'' + 'portal_datasource', + 'chat_portlet', + 'RemoveSelfFromPage', + 'chat_portlet::remove_self_from_page', + 'TCL' ); -- Add the binding perform acs_sc_binding__new ( - ''portal_datasource'', - ''chat_portlet'' + 'portal_datasource', + 'chat_portlet' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_1(); Index: openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-drop.sql,v diff -u -N -r1.1 -r1.1.12.1 --- openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-drop.sql 14 Mar 2006 12:23:37 -0000 1.1 +++ openacs-4/packages/chat-portlet/sql/postgresql/chat-portlet-drop.sql 20 Jun 2016 09:04:30 -0000 1.1.12.1 @@ -20,18 +20,24 @@ -- @creation-date 2004-10-10 -- @version $Id: chat-portlet-drop.sql,v 0.1 2004/10/10 -create function inline_1() -returns integer as ' -declare + + +-- +-- procedure inline_1/0 +-- +CREATE OR REPLACE FUNCTION inline_1( + +) RETURNS integer AS $$ +DECLARE ds_id portal_datasources.datasource_id%TYPE; -begin +BEGIN select datasource_id into ds_id from portal_datasources - where name = ''chat_portlet''; + where name = 'chat_portlet'; if not found then - RAISE EXCEPTION '' No datasource id found '', ds_id; + RAISE EXCEPTION ' No datasource id found ', ds_id; ds_id := null; end if; @@ -41,62 +47,63 @@ -- drop the hooks perform acs_sc_impl_alias__delete ( - ''portal_datasource'', - ''chat_portlet'', - ''GetMyName'' + 'portal_datasource', + 'chat_portlet', + 'GetMyName' ); perform acs_sc_impl_alias__delete ( - ''portal_datasource'', - ''chat_portlet'', - ''GetPrettyName'' + 'portal_datasource', + 'chat_portlet', + 'GetPrettyName' ); perform acs_sc_impl_alias__delete ( - ''portal_datasource'', - ''chat_portlet'', - ''Link'' + 'portal_datasource', + 'chat_portlet', + 'Link' ); perform acs_sc_impl_alias__delete ( - ''portal_datasource'', - ''chat_portlet'', - ''AddSelfToPage'' + 'portal_datasource', + 'chat_portlet', + 'AddSelfToPage' ); perform acs_sc_impl_alias__delete ( - ''portal_datasource'', - ''chat_portlet'', - ''Show'' + 'portal_datasource', + 'chat_portlet', + 'Show' ); perform acs_sc_impl_alias__delete ( - ''portal_datasource'', - ''chat_portlet'', - ''Edit'' + 'portal_datasource', + 'chat_portlet', + 'Edit' ); perform acs_sc_impl_alias__delete ( - ''portal_datasource'', - ''chat_portlet'', - ''RemoveSelfFromPage'' + 'portal_datasource', + 'chat_portlet', + 'RemoveSelfFromPage' ); -- Drop the binding perform acs_sc_binding__delete ( - ''portal_datasource'', - ''chat_portlet'' + 'portal_datasource', + 'chat_portlet' ); -- drop the impl perform acs_sc_impl__delete ( - ''portal_datasource'', - ''chat_portlet'' + 'portal_datasource', + 'chat_portlet' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_1(); drop function inline_1();