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.2.3 -r1.10.2.4 --- openacs-4/packages/chat/sql/postgresql/chat-create.sql 28 Feb 2019 16:09:03 -0000 1.10.2.3 +++ openacs-4/packages/chat/sql/postgresql/chat-create.sql 1 Mar 2019 17:25:59 -0000 1.10.2.4 @@ -65,292 +65,3 @@ -- Site wite 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) - ); - - attr_id := acs_attribute__create_attribute ( - ''chat_room'', -- object_type - ''avatar_p'', -- attribute_name - ''boolean'', -- datatype - ''Avatar'', -- pretty_name - ''Avatars'', -- pretty_plural - null, -- table_name (default) - null, -- column_name (default) - ''t'', -- 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) on delete cascade, - -- 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, - -- if set, display user avatars in the chat room - avatar_p boolean default 't' -); - - --- 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) on delete cascade, - 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(room_id) on delete cascade -); - - ---------------------------------- - -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) on delete cascade - 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(room_id) on delete cascade -);