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 -r1.8 -r1.9 --- 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 7 Aug 2017 23:48:07 -0000 1.9 @@ -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(); @@ -204,16 +203,22 @@ default 't' constraint chat_rooms_archive_p_ck check (archive_p in ('t', 'f')), - -- flush the rooms messages every night at 00:05 + -- flush the rooms messages every night at 00:05 auto_flush_p boolean default 't', -- automatically create a transcript after flushing the room - auto_transcript_p boolean default 'f' + auto_transcript_p boolean default 'f', + -- allow to set whether we want login/logout messages or not + login_messages_p boolean default 't', + logout_messages_p boolean default 't', + -- set how much in the past users will see when entering a chat in + -- seconds this is needed to specify, for example, that users will + -- see only the previous 10 minutes of the conversation + messages_time_window integer default 600 ); -- 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 +294,7 @@ end;' -LANGUAGE 'plpgsql'; +language 'plpgsql'; SELECT inline_0(); DROP function inline_0(); @@ -342,100 +347,37 @@ --------------------------------- -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; - v_room_id chat_rooms.room_id%TYPE; -begin - v_room_id := acs_object__new ( - null, - ''chat_room'', - now(), - p_creation_user, - p_creation_ip, - p_context_id - ); - insert into chat_rooms - (room_id, pretty_name, description, moderated_p, active_p, archive_p, auto_flush_p, auto_transcript_p) - values - (v_room_id, p_pretty_name, p_description, p_moderated_p, p_active_p, p_archive_p, p_auto_flush_p, p_auto_transcript_p); +-- added +select define_function_args('chat_room__delete_all_msgs','room_id'); -return v_room_id; - -end;' language 'plpgsql'; - - - - - ---------------------------------- - -create function chat_room__name (integer) -returns varchar as ' -declare - p_room_id alias for $1; - v_pretty_name chat_rooms.pretty_name%TYPE; -begin - select into v_pretty_name pretty_name from chat_rooms where room_id = p_room_id; - return v_pretty_name; -end;' language 'plpgsql'; - - - - -------------------------------- - - - -create function chat_room__message_count (integer) -returns integer as ' -declare - p_room_id alias for $1; - v_count integer; -begin - select count(*) as total into v_count - from chat_msgs - where room_id = p_room_id; - return v_count; - -end;' language 'plpgsql'; - ---------------------------------- - - - -create function chat_room__delete_all_msgs (integer) -returns integer as ' -declare - p_room_id alias for $1; -begin +-- +-- 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; --------------------------------- +-- added +select define_function_args('chat_room__del','room_id'); -create function chat_room__del (integer) -returns integer as ' -declare - p_room_id alias for $1; -begin +-- +-- 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,51 +394,25 @@ 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 +-- added +select define_function_args('chat_transcript__del','transcript_id'); - 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; - v_transcript_id chat_transcripts.transcript_id%TYPE; -begin - v_transcript_id := acs_object__new ( - null, - ''chat_transcript'', - now(), - p_creation_user, - p_creation_ip, - p_context_id - ); +-- +-- procedure chat_transcript__del/1 +-- +CREATE OR REPLACE FUNCTION chat_transcript__del( + p_transcript_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN - insert into chat_transcripts (transcript_id, pretty_name, contents, description, room_id) - values (v_transcript_id, p_pretty_name, p_contents, p_description, p_room_id); - - return v_transcript_id; -end;' language 'plpgsql'; - - - ------------------------------ -create function chat_transcript__del (integer) -returns integer as ' -declare - p_transcript_id alias for $1; -begin - -- Delete all privileges associate with this transcript delete from acs_permissions where object_id = p_transcript_id; @@ -505,67 +421,41 @@ 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__message_post','room_id,msg,creation_user,creation_ip'); - update chat_rooms set - pretty_name = p_pretty_name, - description = p_description, - moderated_p = p_moderated_p, - active_p = p_active_p, - archive_p = p_archive_p, - auto_flush_p = p_auto_flush_p, - auto_transcript_p = p_auto_transcript_p - where - room_id = p_room_id; - return 0; -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; +-- +-- procedure chat_room__message_post/4 +-- +CREATE OR REPLACE FUNCTION chat_room__message_post( + p_room_id integer, + p_msg varchar, + p_creation_user integer, + p_creation_ip 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; end if; - -- TO DO: aproved_p, Hhtml_p and lengh - -- Insert into chat_msgs table. - insert into chat_msgs ( + -- Insert into chat_msgs table. + insert into chat_msgs ( msg_id, room_id, msg, @@ -583,123 +473,7 @@ 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 - update chat_transcripts - set pretty_name = p_pretty_name, - contents = p_contents, - description = p_description - where - transcript_id = p_transcript_id; - - -return 0; -end;' language 'plpgsql'; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -