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.3 -r1.4 --- openacs-4/packages/chat/sql/postgresql/chat-create.sql 8 Aug 2006 21:26:19 -0000 1.3 +++ openacs-4/packages/chat/sql/postgresql/chat-create.sql 26 Oct 2006 11:11:19 -0000 1.4 @@ -180,36 +180,38 @@ SELECT inline_0(); DROP function inline_0(); +--------------------------------------------- -create table chat_rooms ( +CREATE TABLE "public"."chat_rooms" ( + "room_id" INTEGER NOT NULL, + "pretty_name" VARCHAR(100) NOT NULL, + "description" VARCHAR(2000), + "moderated_p" BOOLEAN DEFAULT false, + "active_p" BOOLEAN DEFAULT true, + "archive_p" BOOLEAN DEFAULT false, + "maximal_participants" INTEGER, + "end_date" DATE, + "creator" INTEGER, + "context_id" INTEGER, + "comm_name" VARCHAR, + CONSTRAINT "chat_rooms_room_id_pk" PRIMARY KEY("room_id"), + CONSTRAINT "chat_rooms_active_p_ck" CHECK ((active_p = true) OR (active_p = false)), + CONSTRAINT "chat_rooms_archive_p_ck" CHECK ((archive_p = true) OR (archive_p = false)), + CONSTRAINT "chat_rooms_moderate_p_ck" CHECK ((moderated_p = true) OR (moderated_p = false)), + CONSTRAINT "chat_rooms_fk" FOREIGN KEY ("creator") + REFERENCES "public"."users"("user_id") + ON DELETE NO ACTION + ON UPDATE NO ACTION + NOT DEFERRABLE, + CONSTRAINT "chat_rooms_room_id_fk" FOREIGN KEY ("room_id") + REFERENCES "public"."acs_objects"("object_id") + ON DELETE NO ACTION + ON UPDATE NO ACTION + NOT DEFERRABLE +) WITH OIDS; - 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' -); +--------------------------------------- -- create chat transcript object type CREATE FUNCTION inline_0() @@ -340,43 +342,86 @@ ); +-------------------------------- + +CREATE TABLE "public"."chat_registered_users" ( + "alias" VARCHAR(20) NOT NULL, + "room_id" INTEGER, + "user_id" INTEGER, + "rss_service" BOOLEAN, + "mail_service" BOOLEAN, + "registered_id" INTEGER NOT NULL, + CONSTRAINT "chat_registered_users_pkey" PRIMARY KEY("registered_id"), + CONSTRAINT "chat_registered_users_fk" FOREIGN KEY ("room_id") + REFERENCES "public"."chat_rooms"("room_id") + ON DELETE NO ACTION + ON UPDATE NO ACTION + NOT DEFERRABLE, + CONSTRAINT "chat_registered_users_fk1" FOREIGN KEY ("user_id") + REFERENCES "public"."users"("user_id") + ON DELETE NO ACTION + ON UPDATE NO ACTION + NOT DEFERRABLE, + CONSTRAINT "chat_registered_users_fk2" FOREIGN KEY ("registered_id") + REFERENCES "public"."acs_objects"("object_id") + ON DELETE NO ACTION + ON UPDATE NO ACTION + NOT DEFERRABLE +) WITH OIDS; + + + --------------------------------- -create or replace function chat_room__new (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean, integer, timestamptz, integer, varchar, varchar) -returns integer as ' +CREATE OR REPLACE FUNCTION "public"."chat_room__new" (varchar, varchar, varchar, varchar, integer, timestamp, boolean, boolean, boolean, boolean, boolean, integer, integer, integer, varchar, varchar) RETURNS integer AS' +/* Nuevo cuerpo de Function */ declare - p_room_id alias for $1; - p_pretty_name alias for $2; + p_pretty_name alias for $1; + p_alias 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; + p_key_words alias for $4; + p_maxP alias for $5; + p_end_date alias for $6; + p_Rss_service alias for $7; + p_Mail_service alias for $8; + p_moderated_p alias for $9; + p_active_p alias for $10; + p_archive_p alias for $11; + p_context_id alias for $12; + p_comm_id alias for $13; + p_creation_user alias for $14; + p_creation_ip alias for $15; + p_object_type alias for $16; v_room_id chat_rooms.room_id%TYPE; + v_registered_id chat_rooms.room_id%TYPE; + v_comm_name varchar; begin - v_room_id := acs_object__new ( - null, - ''chat_room'', - now(), - p_creation_user, - p_creation_ip, - p_context_id - ); + v_room_id := acs_object__new(null,''chat_room'',now(),p_creation_user,p_creation_ip,p_context_id ); + v_registered_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) + if exists (select dot.pretty_name from dotlrn_communities_all as dot where dot.community_id = p_comm_id) then + select into v_comm_name dot.pretty_name from dotlrn_communities_all as dot where dot.community_id = p_comm_id; + insert into chat_rooms + (room_id, pretty_name, description, moderated_p, active_p, archive_p, maximal_participants, end_date, creator, context_id,comm_name) + values + (v_room_id, p_pretty_name, p_description, p_moderated_p, p_active_p, p_archive_p, p_maxP, p_end_date,p_creation_user, p_context_id,v_comm_name); + else + insert into chat_rooms + (room_id, pretty_name, description, moderated_p, active_p, archive_p, maximal_participants, end_date, creator, context_id,comm_name) + values + (v_room_id, p_pretty_name, p_description, p_moderated_p, p_active_p, p_archive_p, p_maxP, p_end_date,p_creation_user, p_context_id,''Dotlrn''); + end if; + + + insert into chat_registered_users + (alias, room_id, user_id, RSS_service, mail_service, registered_id) 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); + (p_alias, v_room_id, p_creation_user, p_Rss_service, p_Mail_service, v_registered_id); return v_room_id; -end;' language 'plpgsql'; +end; +'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; @@ -442,6 +487,9 @@ -- First erase all the messages relate to this chat room. delete from chat_msgs where room_id = p_room_id; + + -- Secondly erase all the registered users relate to this chat room. + delete from chat_registered_users where room_id = p_room_id; -- Delete all privileges associate with this room delete from acs_permissions where object_id = p_room_id; @@ -457,7 +505,45 @@ --------------------------------- +CREATE OR REPLACE FUNCTION "public"."chat_room__delete_registered_users" (integer, integer) RETURNS integer AS' +declare + p_room_id alias for $1; + p_user_id alias for $2; +begin + delete from chat_registered_users where room_id = p_room_id + and user_id = p_user_id; + return 0; +end; +'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; + + +--------------------------------- + +CREATE OR REPLACE FUNCTION "public"."chat_room_registered__user" (varchar, integer, integer, boolean, boolean, integer, varchar) RETURNS integer AS' +/* Nuevo cuerpo de Function */ +declare + p_alias alias for $1; + p_user_id alias for $2; + p_room_id alias for $3; + p_RSS_service alias for $4; + p_mail_service alias for $5; + p_context_id alias for $6; + p_creation_ip alias for $7; + v_registered_id chat_rooms.room_id%TYPE; +begin + v_registered_id := acs_object__new(null,''chat_room'',now(),p_user_id,p_creation_ip,p_context_id ); + insert into chat_registered_users + (alias, room_id, user_id, RSS_service, mail_service, registered_id) + values + (p_alias, p_room_id, p_user_id, p_RSS_service, p_mail_service, v_registered_id); +return v_registered_id; +end; +'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; + +--------------------------------- + + create function chat_transcript__new (varchar, varchar, varchar, integer, integer, timestamptz, integer, varchar, varchar) returns integer as ' declare @@ -509,31 +595,44 @@ ---------------------------- -create or replace function chat_room__edit (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean) -returns integer as ' +CREATE OR REPLACE FUNCTION "public"."chat_room__edit" (varchar, varchar, varchar, varchar, integer, timestamp, boolean, boolean, boolean, boolean, boolean, integer, integer) RETURNS integer AS' +/* Nuevo cuerpo de Function */ declare - p_room_id alias for $1; - p_pretty_name alias for $2; + p_pretty_name alias for $1; + p_alias 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_key_words alias for $4; + p_maxP alias for $5; + p_end_date alias for $6; + p_Rss_service alias for $7; + p_Mail_service alias for $8; + p_moderated_p alias for $9; + p_active_p alias for $10; + p_archive_p alias for $11; + p_user_id alias for $12; + p_room_id alias for $13; begin - update chat_rooms set + update chat_rooms set pretty_name = p_pretty_name, description = p_description, + maximal_participants = p_maxP, + end_date = p_end_date, 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'; + archive_p = p_archive_p + where room_id = p_room_id; + + update chat_registered_users set + alias = p_alias, + RSS_service = p_Rss_service, + mail_service = p_Mail_service + where room_id = p_room_id + and user_id = p_user_id; + + return 0; +end; +'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---------------------------