Index: openacs-4/packages/chat/sql/oracle/upgrade/upgrade-5.0d6-5.0d7.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/oracle/upgrade/upgrade-5.0d6-5.0d7.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/chat/sql/oracle/upgrade/upgrade-5.0d6-5.0d7.sql 5 Mar 2008 09:44:13 -0000 1.1
@@ -0,0 +1,264 @@
+-- packages/chat
+-- oracle/upgrade/upgrade-5.0d6-5.0d7.sql
+
+
+declare
+ col_exists_p integer;
+begin
+ select decode(count(*), 0, 0, 1) into col_exists_p from user_tab_columns where table_name='CHAT_ROOMS' and column_name='AUTO_FLUSH_P';
+ if col_exists_p = 0 then
+ execute immediate 'alter table chat_rooms add auto_flush_p char(1) default ''t'' ';
+ execute immediate 'alter table chat_rooms add constraint chat_rooms_auto_flush_ck check(auto_flush_p in (''t'',''f'')) ';
+ end if;
+
+ select decode(count(*), 0, 0, 1) into col_exists_p from user_tab_columns where table_name='CHAT_ROOMS' and column_name='AUTO_TRANSCRIPT_P';
+ if col_exists_p = 0 then
+ execute immediate 'alter table chat_rooms add auto_transcript_p char(1) default ''f'' ';
+ execute immediate 'alter table chat_rooms add constraint chat_rooms_auto_transcript_ck check(auto_transcript_p in (''t'',''f'')) ';
+ end if;
+end;
+/
+show errors;
+commit;
+
+
+-- chat_room package specification and body
+
+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',
+ 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 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',
+ 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)
+ 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);
+
+ 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