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.10 -r1.11 --- openacs-4/packages/chat/sql/postgresql/chat-create.sql 12 Jul 2018 12:43:09 -0000 1.10 +++ openacs-4/packages/chat/sql/postgresql/chat-create.sql 3 Sep 2024 15:37:36 -0000 1.11 @@ -60,420 +60,5 @@ select acs_privilege__add_child('chat_room_admin', 'chat_moderator_revoke'); select acs_privilege__add_child('chat_room_admin', 'chat_moderator'); --- Site wite admin also administrator of the chat room. +-- Site-wide admin also administrator of the chat room. select acs_privilege__add_child('admin', 'chat_room_admin'); - - --- create chat room object type -CREATE FUNCTION inline_0() returns integer -AS 'declare - attr_id acs_attributes.attribute_id%TYPE; - begin - PERFORM - - acs_object_type__create_type( - ''chat_room'', -- object_type - ''Chat Room'', -- pretty_name - ''Chat Rooms'', -- pretty_plural - ''acs_object'', -- supertype - ''chat_rooms'', -- table_name - ''room_id'', -- id_column - null, -- package_name - ''f'', -- abstract_p - null, -- type_extension_table - null -- name_method - ); - - - attr_id := acs_attribute__create_attribute ( - ''chat_room'', -- object_type - ''pretty_name'', -- attribute_name - ''string'', -- datatype - ''Room name'', -- pretty_name - ''Room names'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - - attr_id := acs_attribute__create_attribute ( - ''chat_room'', -- object_type - ''description'', -- attribute_name - ''string'', -- datatype - ''Description'', -- pretty_name - ''Descriptions'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - attr_id := acs_attribute__create_attribute ( - ''chat_room'', -- object_type - ''moderated_p'', -- attribute_name - ''boolean'', -- datatype - ''Moderated'', -- pretty_name - ''Moderated'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - attr_id := acs_attribute__create_attribute ( - ''chat_room'', -- object_type - ''active_p'', -- attribute_name - ''boolean'', -- datatype - ''Activated'', -- pretty_name - ''Activated'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - attr_id := acs_attribute__create_attribute ( - ''chat_room'', -- object_type - ''archive_p'', -- attribute_name - ''boolean'', -- datatype - ''Archived'', -- pretty_name - ''Archived'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - return 0; - - end;' - -language 'plpgsql'; -SELECT inline_0(); -DROP function inline_0(); - - -create table chat_rooms ( - - room_id integer - constraint chat_rooms_room_id_pk primary key - constraint chat_rooms_room_id_fk - references acs_objects(object_id), - -- This is room name. - pretty_name varchar(100) - constraint chat_rooms_pretty_name_nn not null, - description varchar(2000), - moderated_p boolean - default 'f' - constraint chat_rooms_moderate_p_ck - check (moderated_p in ('t','f')), - active_p boolean - default 't' - constraint chat_rooms_active_p_ck - check (active_p in ('t','f')), - -- if set then log all chat messages in this room. - archive_p boolean - default 't' - constraint chat_rooms_archive_p_ck - check (archive_p in ('t', 'f')), - -- 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', - -- 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 -AS 'declare - attr_id acs_attributes.attribute_id%TYPE; - begin - PERFORM - - acs_object_type__create_type( - ''chat_transcript'', -- object_type - ''Chat Transcript'', -- pretty_name - ''Chat Transcripts'', -- pretty_plural - ''acs_object'', -- supertype - ''chat_transcripts'', -- table_name - ''transcript_id'', -- id_column - null, -- package_name - ''f'', -- abstract_p - null, -- type_extension_table - null -- name_method - ); - - - attr_id := acs_attribute__create_attribute ( - ''chat_transcript'', -- object_type - ''pretty_name'', -- attribute_name - ''string'', -- datatype - ''Transcript name'', -- pretty_name - ''Transcript names'',-- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - attr_id := acs_attribute__create_attribute ( - ''chat_transcript'', -- object_type - ''description'', -- attribute_name - ''string'', -- datatype - ''Description'', -- pretty_name - ''Descriptions'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - attr_id := acs_attribute__create_attribute ( - ''chat_transcript'', -- object_type - ''contents'', -- attribute_name - ''string'', -- datatype - ''Transcript content'', -- pretty_name - ''Transcript contents'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - null, -- default_value (default) - 1, -- min_n_values (default) - 1, -- max_n_values (default) - null, -- sort_order (default) - ''type_specific'', -- storage (default) - ''f'' -- static_p (default) - ); - - - - return 0; - - end;' - -language 'plpgsql'; -SELECT inline_0(); -DROP function inline_0(); - - ---------------------------------- - -create table chat_transcripts ( - transcript_id integer - constraint chat_trans_transcript_id_pk primary key - constraint chat_trans_transcript_id_fk - references acs_objects(object_id), - contents varchar(32000) - constraint chat_trans_contents_nn not null, - -- Chat transcript name. - pretty_name varchar(100) - constraint chat_trans_pretty_name_nn not null, - description varchar(2000), - room_id integer - constraint chat_trans_room_id_fk references chat_rooms -); - - ---------------------------------- - -create table chat_msgs ( - msg_id integer - constraint chat_msgs_msg_id_pk primary key, - msg varchar(4000), - msg_len integer - constraint chat_msgs_msg_len_ck - check (msg_len >= 0), - html_p boolean - default 'f' - constraint chat_msgs_html_p_ck - check (html_p in ('t','f')), - approved_p boolean - default 't' - constraint chat_msgs_approve_p_ck - check(approved_p in ('t','f')), - creation_user integer - constraint chat_msgs_creation_user_fk - references parties(party_id) - constraint chat_msgs_creation_user_nn not null, - creation_ip varchar(50) , - creation_date timestamptz, - room_id integer - constraint chat_msgs_room_id_fk references chat_rooms -); - - ---------------------------------- - - --- 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; - ---------------------------------- - - --- 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? - - - -- First erase all the messages relate to this chat room. - delete from chat_msgs where room_id = p_room_id; - - -- Delete all privileges associate with this room - delete from acs_permissions where object_id = p_room_id; - - -- Now delete the chat room itself. - delete from chat_rooms where room_id = p_room_id; - - PERFORM acs_object__delete(p_room_id); - - return 0; -END; -$$ LANGUAGE plpgsql; - - ---------------------------------- - - --- 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; - - delete from chat_transcripts - where transcript_id = p_transcript_id; - - PERFORM acs_object__delete(p_transcript_id); - return 0; -END; -$$ LANGUAGE plpgsql; ----------------------------- - - --- added -select define_function_args('chat_room__message_post','room_id,msg,creation_user,creation_ip'); - --- --- 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 - -- Get msg id from the global acs_object sequence. - 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 - v_msg := p_msg; - else - v_msg := null; - end if; - - -- Insert into chat_msgs table. - insert into chat_msgs ( - msg_id, - room_id, - msg, - creation_user, - creation_ip, - creation_date) - values ( - v_msg_id, - p_room_id, - v_msg, - p_creation_user, - p_creation_ip, - now()) ; - - - -return 0; -END; -$$ LANGUAGE plpgsql; - ----------------------------