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;
 
 
 ---------------------------
