Index: openacs-4/packages/chat/sql/oracle/chat-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/oracle/chat-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/chat/sql/oracle/chat-create.sql 7 Aug 2017 23:48:07 -0000 1.5 +++ openacs-4/packages/chat/sql/oracle/chat-create.sql 3 Sep 2024 15:37:36 -0000 1.6 @@ -1,4 +1,4 @@ --- +-- -- packages/chat/sql/chat-create.sql -- -- @author ddao@arsdigita.com @@ -46,7 +46,7 @@ acs_privilege.add_child('chat_moderator', 'chat_user_grant'); acs_privilege.add_child('chat_moderator', 'chat_user_revoke'); acs_privilege.add_child('chat_moderator', 'chat_transcript_create'); - acs_privilege.add_child('chat_moderator', 'chat_transcript_view'); + acs_privilege.add_child('chat_moderator', 'chat_transcript_view'); acs_privilege.add_child('chat_moderator', 'chat_transcript_edit'); acs_privilege.add_child('chat_moderator', 'chat_transcript_delete'); acs_privilege.add_child('chat_moderator', 'chat_user'); @@ -61,543 +61,8 @@ acs_privilege.add_child('chat_room_admin', 'chat_moderator_revoke'); acs_privilege.add_child('chat_room_admin', 'chat_moderator'); - -- Site wite admin also administrator of the chat room. - acs_privilege.add_child('admin', 'chat_room_admin'); + -- Site-wide admin also administrator of the chat room. + acs_privilege.add_child('admin', 'chat_room_admin'); end; / show errors - - -declare - attr_id acs_attributes.attribute_id%TYPE; -begin - -- create chat room object type - acs_object_type.create_type ( - supertype => 'acs_object', - object_type => 'chat_room', - pretty_name => 'Chat Room', - pretty_plural => 'Chat Rooms', - table_name => 'CHAT_ROOMS', - id_column => 'ROOM_ID' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_room', - attribute_name => 'pretty_name', - pretty_name => 'Room name', - pretty_plural => 'Room names', - datatype => 'string' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_room', - attribute_name => 'description', - pretty_name => 'Description', - pretty_plural => 'Descriptions', - datatype => 'string' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_room', - attribute_name => 'moderated_p', - pretty_name => 'Moderated', - pretty_plural => 'Moderated', - datatype => 'boolean' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_room', - attribute_name => 'active_p', - pretty_name => 'Activated', - pretty_plural => 'Activated', - datatype => 'boolean' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_room', - attribute_name => 'archive_p', - pretty_name => 'Archived', - pretty_plural => 'Archived', - datatype => 'boolean' - ); -end; -/ -show errors; - -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 varchar2(100) - constraint chat_rooms_pretty_name_nn not null, - description varchar2(2000), - moderated_p char(1) default 'f' - constraint chat_rooms_moderate_p_ck - check (moderated_p in ('t','f')), - active_p char(1) 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 char(1) default 'f' - constraint chat_rooms_archive_p_ck - check (archive_p in ('t', 'f')), - auto_flush_p char(1) default 't' - constraint chat_rooms_auto_flush_ck - check (auto_flush_p in ('t', 'f')), - auto_transcript_p char(1) default 'f' - constraint chat_rooms_auto_transcript_ck - check (auto_transcript_p in ('t', 'f')), - login_messages_p char(1) default 't' - constraint chat_rooms_login_messages_ck - check (login_messages_p in ('t', 'f')), - - logout_messages_p char(1) default 't' - constraint chat_rooms_logout_messages_ck - check (logout_messages_p in ('t', 'f')) -); - -declare - attr_id acs_attributes.attribute_id%TYPE; -begin - -- create chat transcript object type - acs_object_type.create_type ( - supertype => 'acs_object', - object_type => 'chat_transcript', - pretty_name => 'Chat Transcript', - pretty_plural => 'Chat Transcripts', - table_name => 'CHAT_TRANSCRIPTS', - id_column => 'TRANSCRIPT_ID' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_transcript', - attribute_name => 'pretty_name', - pretty_name => 'Transcript name', - pretty_plural => 'Transcript names', - datatype => 'string' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_transcript', - attribute_name => 'description', - pretty_name => 'Description', - pretty_plural => 'Descriptions', - datatype => 'string' - ); - - attr_id := acs_attribute.create_attribute ( - object_type => 'chat_transcript', - attribute_name => 'contents', - pretty_name => 'Transcript content', - pretty_plural => 'Transcript contents', - datatype => 'string' - ); -end; -/ -show errors - -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 clob - constraint chat_trans_contents_nn not null, - -- Chat transcript name. - pretty_name varchar2(100) - constraint chat_trans_pretty_name_nn not null, - description varchar2(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 varchar2(4000), - msg_len integer - constraint chat_msgs_msg_len_ck - check (msg_len >= 0), - html_p char(1) default 'f' - constraint chat_msgs_html_p_ck - check (html_p in ('t','f')), - approved_p char(1) 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 varchar2(50) , - creation_date date - constraint chat_msgs_creation_date_nn not null, - room_id integer - constraint chat_msgs_room_id_fk references chat_rooms -); - --- --- Package declaration --- - -create or replace package chat_room -as - function new ( - room_id in chat_rooms.room_id%TYPE default null, - pretty_name in chat_rooms.pretty_name%TYPE, - description in chat_rooms.description%TYPE default null, - moderated_p in chat_rooms.moderated_p%TYPE default 'f', - active_p in chat_rooms.active_p%TYPE default 't', - archive_p in chat_rooms.archive_p%TYPE default 'f', - auto_flush_p in chat_rooms.auto_flush_p%TYPE default 't', - auto_transcript_p in chat_rooms.auto_transcript_p%TYPE default 'f', - login_messages_p in chat_rooms.login_messages_p%TYPE default 't', - logout_messages_p in chat_rooms.logout_messages_p%TYPE default 't', - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'chat_room' - ) return acs_objects.object_id%TYPE; - - procedure del ( - room_id in chat_rooms.room_id%TYPE - ); - - procedure edit ( - room_id in chat_rooms.room_id%TYPE, - pretty_name in chat_rooms.pretty_name%TYPE, - description in chat_rooms.description%TYPE, - moderated_p in chat_rooms.moderated_p%TYPE, - active_p in chat_rooms.active_p%TYPE, - archive_p in chat_rooms.archive_p%TYPE, - auto_flush_p in chat_rooms.auto_flush_p%TYPE, - auto_transcript_p in chat_rooms.auto_transcript_p%TYPE - ); - - function name ( - room_id in chat_rooms.room_id%TYPE - ) return chat_rooms.pretty_name%TYPE; - - procedure message_post ( - room_id in chat_msgs.room_id%TYPE, - msg in chat_msgs.msg%TYPE default null, - html_p in chat_msgs.html_p%TYPE default 'f', - approved_p in chat_msgs.approved_p%TYPE default 't', - creation_user in chat_msgs.creation_user%TYPE, - creation_ip in chat_msgs.creation_ip%TYPE default null, - creation_date in chat_msgs.creation_date%TYPE default sysdate - ); - - function message_count ( - room_id in chat_rooms.room_id%TYPE - ) return integer; - - procedure delete_all_msgs ( - room_id in chat_rooms.room_id%TYPE - ); - -end chat_room; -/ -show errors - -create or replace package chat_transcript -as - function new ( - transcript_id in chat_transcripts.transcript_id%TYPE default null, - pretty_name in chat_transcripts.pretty_name%TYPE, - contents in chat_transcripts.contents%TYPE, - description in chat_transcripts.description%TYPE, - room_id in chat_transcripts.room_id%TYPE, - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'chat_transcript' - ) return acs_objects.object_id%TYPE; - - procedure del ( - transcript_id in chat_transcripts.transcript_id%TYPE - ); - - procedure edit ( - transcript_id in chat_transcripts.transcript_id%TYPE, - pretty_name in chat_transcripts.pretty_name%TYPE, - contents in chat_transcripts.contents%TYPE, - description in chat_transcripts.description%TYPE - ); -end chat_transcript; -/ -show errors - --- --- End package definition --- - --- --- Begin package body --- - -create or replace package body chat_room -as - function new ( - room_id in chat_rooms.room_id%TYPE default null, - pretty_name in chat_rooms.pretty_name%TYPE, - description in chat_rooms.description%TYPE default null, - moderated_p in chat_rooms.moderated_p%TYPE default 'f', - active_p in chat_rooms.active_p%TYPE default 't', - archive_p in chat_rooms.archive_p%TYPE default 'f', - auto_flush_p in chat_rooms.auto_flush_p%TYPE default 't', - auto_transcript_p in chat_rooms.auto_transcript_p%TYPE default 'f', - login_messages_p in chat_rooms.login_messages_p%TYPE default 't', - logout_messages_p in chat_rooms.logout_messages_p%TYPE default 't', - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'chat_room' - ) return acs_objects.object_id%TYPE - is - v_room_id chat_rooms.room_id%TYPE; - begin - v_room_id := acs_object.new ( - object_type => chat_room.new.object_type, - creation_date => chat_room.new.creation_date, - creation_user => chat_room.new.creation_user, - creation_ip => chat_room.new.creation_ip, - context_id => chat_room.new.context_id - ); - - insert into chat_rooms ( - room_id, - pretty_name, - description, - moderated_p, - active_p, - archive_p, - auto_flush_p, - auto_transcript_p, - login_messages_p, - logout_messages_p) - values ( - v_room_id, - chat_room.new.pretty_name, - chat_room.new.description, - chat_room.new.moderated_p, - chat_room.new.active_p, - chat_room.new.archive_p, - chat_room.new.auto_flush_p, - chat_room.new.auto_transcript_p, - chat_room.new.login_messages_p, - chat_room.new.logout_messages_p); - - return v_room_id; - end new; - - procedure del ( - room_id in chat_rooms.room_id%TYPE - ) - is - begin - -- First erase all the messages relate to this chat room. - delete from chat_msgs where room_id = chat_room.del.room_id; - - -- Delete all privileges associate with this room - delete from acs_permissions where object_id = chat_room.del.room_id; - - -- Now delete the chat room itself. - delete from chat_rooms where room_id = chat_room.del.room_id; - - acs_object.del(room_id); - end del; - - procedure edit ( - room_id in chat_rooms.room_id%TYPE, - pretty_name in chat_rooms.pretty_name%TYPE, - description in chat_rooms.description%TYPE, - moderated_p in chat_rooms.moderated_p%TYPE, - active_p in chat_rooms.active_p%TYPE, - archive_p in chat_rooms.archive_p%TYPE, - auto_flush_p in chat_rooms.auto_flush_p%TYPE, - auto_transcript_p in chat_rooms.auto_transcript_p%TYPE - ) - is - begin - update chat_rooms set - pretty_name = chat_room.edit.pretty_name, - description = chat_room.edit.description, - moderated_p = chat_room.edit.moderated_p, - active_p = chat_room.edit.active_p, - archive_p = chat_room.edit.archive_p, - auto_flush_p = chat_room.edit.auto_flush_p, - auto_transcript_p = chat_room.edit.auto_transcript_p - where - room_id = chat_room.edit.room_id; - end edit; - - function name ( - room_id in chat_rooms.room_id%TYPE - ) return chat_rooms.pretty_name%TYPE - is - v_room_name chat_rooms.pretty_name%TYPE; - begin - select pretty_name into v_room_name - from chat_rooms - where room_id = chat_room.name.room_id; - - return v_room_name; - end name; - - procedure message_post ( - room_id in chat_msgs.room_id%TYPE, - msg in chat_msgs.msg%TYPE default null, - html_p in chat_msgs.html_p%TYPE default 'f', - approved_p in chat_msgs.approved_p%TYPE default 't', - creation_user in chat_msgs.creation_user%TYPE, - creation_ip in chat_msgs.creation_ip%TYPE default null, - creation_date in chat_msgs.creation_date%TYPE default sysdate - ) - is - 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 acs_object_id_seq.nextval into v_msg_id from dual; - - select archive_p into v_msg_archive_p - from chat_rooms - where room_id = chat_room.message_post.room_id; - - if v_msg_archive_p = 't' then - v_msg := msg; - else - v_msg := null; - end if; - - -- Insert into chat_msgs table. - insert into chat_msgs ( - msg_id, - room_id, - msg, - msg_len, - html_p, - approved_p, - creation_user, - creation_ip, - creation_date) - values ( - v_msg_id, - room_id, - v_msg, - nvl(length(msg), 0), - html_p, - approved_p, - creation_user, - creation_ip, - creation_date) ; - end message_post; - - - function message_count ( - room_id in chat_rooms.room_id%TYPE - ) return integer - is - v_count integer; - begin - select count(*) into v_count - from chat_msgs - where room_id = chat_room.message_count.room_id; - - return v_count; - end message_count; - - procedure delete_all_msgs ( - room_id in chat_rooms.room_id%TYPE - ) - is - begin - delete from chat_msgs where room_id = chat_room.delete_all_msgs.room_id; - end delete_all_msgs; - -end chat_room; -/ -show errors - -create or replace package body chat_transcript -as - function new ( - transcript_id in chat_transcripts.transcript_id%TYPE default null, - pretty_name in chat_transcripts.pretty_name%TYPE, - contents in chat_transcripts.contents%TYPE, - description in chat_transcripts.description%TYPE, - room_id in chat_transcripts.room_id%TYPE, - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'chat_transcript' - ) return acs_objects.object_id%TYPE - is - v_transcript_id chat_transcripts.transcript_id%TYPE; - begin - v_transcript_id := acs_object.new ( - object_type => object_type, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - context_id => context_id - ); - - insert into chat_transcripts (transcript_id, pretty_name, contents, description, room_id) - values (v_transcript_id, pretty_name, empty_clob(), description, room_id); - - return v_transcript_id; - end new; - - procedure del ( - transcript_id in chat_transcripts.transcript_id%TYPE - ) - is - begin - - -- Delete all privileges associate with this transcript - delete from acs_permissions where object_id = chat_transcript.del.transcript_id; - - delete from chat_transcripts - where transcript_id = chat_transcript.del.transcript_id; - - acs_object.del(transcript_id); - end del; - - procedure edit ( - transcript_id in chat_transcripts.transcript_id%TYPE, - pretty_name in chat_transcripts.pretty_name%TYPE, - contents in chat_transcripts.contents%TYPE, - description in chat_transcripts.description%TYPE - ) - is - begin - update chat_transcripts - set pretty_name = chat_transcript.edit.pretty_name, - contents = chat_transcript.edit.contents, - description = chat_transcript.edit.description - where - transcript_id = chat_transcript.edit.transcript_id; - - end edit; - -end chat_transcript; -/ -show errors - - - - - - - - - -