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 -N -r1.6 -r1.7 --- openacs-4/packages/chat/sql/postgresql/chat-create.sql 24 Jun 2007 15:21:19 -0000 1.6 +++ openacs-4/packages/chat/sql/postgresql/chat-create.sql 19 Nov 2007 01:14:15 -0000 1.7 @@ -1,7 +1,9 @@ -- -- packages/chat/sql/chat-create.sql -- --- @author ddao@arsdigita.com and Pablo Mu�oz(pablomp@tid.es) +-- @author ddao@arsdigita.com +-- @creation-date November 09, 2000 +-- @cvs-id $Id$ -- @@ -84,8 +86,6 @@ ); - - attr_id := acs_attribute__create_attribute ( ''chat_room'', -- object_type ''pretty_name'', -- attribute_name @@ -180,8 +180,37 @@ 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), + -- 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() RETURNS integer @@ -265,299 +294,115 @@ DROP function inline_0(); ---------------------------------------------- - -CREATE TABLE 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, - open BOOLEAN, - auto_transcript_p BOOLEAN, - file_sent BOOLEAN, - private BOOLEAN, - frequency1 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 users(user_id) - ON DELETE cascade - NOT DEFERRABLE, - CONSTRAINT chat_rooms_room_id_fk FOREIGN KEY (room_id) - REFERENCES acs_objects(object_id) - ON DELETE NO ACTION - ON UPDATE NO ACTION - NOT DEFERRABLE -) WITH OIDS; - - --------------------------------- -CREATE TABLE chat_transcripts ( - transcript_id INTEGER NOT NULL, - contents VARCHAR(32000) NOT NULL, - pretty_name VARCHAR(100) NOT NULL, - description VARCHAR(2000), - room_id INTEGER, - date DATE, - keywords VARCHAR, - CONSTRAINT chat_trans_transcript_id_pk PRIMARY KEY(transcript_id), - CONSTRAINT chat_trans_room_id_fk FOREIGN KEY (room_id) - REFERENCES chat_rooms(room_id) - ON DELETE NO ACTION - ON UPDATE NO ACTION - NOT DEFERRABLE, - CONSTRAINT chat_trans_transcript_id_fk FOREIGN KEY (transcript_id) - REFERENCES acs_objects(object_id) - ON DELETE NO ACTION - ON UPDATE NO ACTION - NOT DEFERRABLE -) WITH OIDS; +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 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 +); --------------------------------- -CREATE TABLE chat_msgs ( - msg_id INTEGER NOT NULL, - msg VARCHAR(4000), - msg_len INTEGER, - html_p BOOLEAN DEFAULT false, - approved_p BOOLEAN DEFAULT true, - creation_user INTEGER NOT NULL, - creation_ip VARCHAR(50), - creation_date DATE NOT NULL, - room_id INTEGER, - CONSTRAINT chat_msgs_msg_id_pk PRIMARY KEY(msg_id), - CONSTRAINT chat_msgs_approve_p_ck CHECK ((approved_p = true) OR (approved_p = false)), - CONSTRAINT chat_msgs_html_p_ck CHECK ((html_p = true) OR (html_p = false)), - CONSTRAINT chat_msgs_msg_len_ck CHECK (msg_len >= 0), - CONSTRAINT chat_msgs_creation_user_fk FOREIGN KEY (creation_user) - REFERENCES parties(party_id) - ON DELETE cascade - NOT DEFERRABLE, - CONSTRAINT chat_msgs_room_id_fk FOREIGN KEY (room_id) - REFERENCES chat_rooms(room_id) - ON DELETE cascade - NOT DEFERRABLE -) WITH OIDS; +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) + 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 +); --------------------------------- - -CREATE TABLE 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, - frequency_mail varchar, - registered_date TIMESTAMP(0) WITHOUT TIME ZONE, - CONSTRAINT chat_registered_users_pkey PRIMARY KEY(registered_id), - CONSTRAINT chat_registered_users_fk FOREIGN KEY (room_id) - REFERENCES chat_rooms(room_id) - ON DELETE cascade - NOT DEFERRABLE, - CONSTRAINT chat_registered_users_fk1 FOREIGN KEY (user_id) - REFERENCES users(user_id) - ON DELETE cascade - NOT DEFERRABLE, - CONSTRAINT chat_registered_users_fk2 FOREIGN KEY (registered_id) - REFERENCES acs_objects(object_id) - ON DELETE cascade - NOT DEFERRABLE -) WITH OIDS; - - --------------------------------- -CREATE TABLE chat_keywords ( - room_id INTEGER, - keyword VARCHAR, - CONSTRAINT chat_keywords_fk FOREIGN KEY (room_id) - REFERENCES chat_rooms(room_id) - ON DELETE cascade - 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 ' +declare + p_room_id alias for $1; + p_pretty_name 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; + v_room_id chat_rooms.room_id%TYPE; +begin + v_room_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) + 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); ---------------------------------- +return v_room_id; -CREATE TABLE chat_private_room_users ( - room_id INTEGER, - user_id1 INTEGER, - user_id2 INTEGER, - CONSTRAINT chat_private_room_users_fk FOREIGN KEY (room_id) - REFERENCES chat_rooms(room_id) - ON DELETE cascade - NOT DEFERRABLE -) WITH OIDS; +end;' language 'plpgsql'; ---------------------------------- -CREATE TABLE chat_room_user_id ( - room_id INTEGER NOT NULL references chat_rooms - on delete cascade, - user_id INTEGER NOT NULL, - CONSTRAINT chat_room_user_id_fk FOREIGN KEY (user_id) - REFERENCES users(user_id) - ON DELETE cascade - NOT DEFERRABLE -) WITH OIDS; --------------------------------- - -CREATE TABLE chat_rooms_files_sent ( - file VARCHAR, - title VARCHAR, - room_id INTEGER NOT NULL, - date DATE, - send_file_id INTEGER references cr_revisions - on delete cascade, - description VARCHAR, - message BOOLEAN, - creation_user INTEGER, - CONSTRAINT chat_rooms_files_sent_fk FOREIGN KEY (room_id) - REFERENCES chat_rooms(room_id) - ON DELETE cascade - NOT DEFERRABLE -) WITH OIDS; - - ---------------------------------- - - -CREATE TABLE chat_rss ( - room_name VARCHAR, - creator VARCHAR, - end_date DATE, - description VARCHAR, - comm_name VARCHAR, - user_registered VARCHAR, - rss_id INTEGER NOT NULL, - date TIMESTAMP(0) WITHOUT TIME ZONE, - entry_timestamp TIMESTAMP(0) WITHOUT TIME ZONE, - CONSTRAINT "chat_rss_pkey" PRIMARY KEY("rss_id") -) WITH OIDS; - - ---------------------------------- - -CREATE TABLE chat_file_transcript ( - transcript_id INTEGER, - file_id INTEGER -) WITH OIDS; - ---------------------------------- - -CREATE TABLE chat_files_rss ( - file_id INTEGER, - rss_id INTEGER -) WITH OIDS; - - ---------------------------------- - -CREATE TABLE chat_key_rss ( - rss_id INTEGER, - key VARCHAR -) WITH OIDS; - - ---------------------------------- - -CREATE TABLE chat_partitipants_rss ( - partitipant VARCHAR, - rss_id INTEGER -) WITH OIDS; - - ---------------------------------- - -CREATE TABLE chat_partitipants_transcript ( - transcript_id INTEGER, - partitipant VARCHAR -) WITH OIDS; - ---------------------------------- - -CREATE TABLE chat_room_transcript_keywords ( - keyword VARCHAR, - transcript_id INTEGER -) WITH OIDS; - - ---------------------------------- - -CREATE TABLE chat_transcription_rss ( - rss_id INTEGER, - transcription_id INTEGER -) WITH OIDS; - - ---------------------------------- - -CREATE OR REPLACE FUNCTION chat_room__new (varchar, varchar, varchar, varchar, integer, timestamp, boolean, boolean, boolean, boolean, boolean, integer, varchar, integer, varchar, varchar, varchar, varchar) RETURNS integer AS' -/* Nuevo cuerpo de Function */ +create function chat_room__name (integer) +returns varchar as ' declare - p_pretty_name alias for $1; - p_alias alias for $2; - p_description alias for $3; - 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_name alias for $13; - p_creation_user alias for $14; - p_creation_ip alias for $15; - p_object_type alias for $16; - p_frequency1 alias for $17; - p_frequency2 alias for $18; - v_room_id chat_rooms.room_id%TYPE; - v_registered_id chat_rooms.room_id%TYPE; - v_comm_name varchar; + p_room_id alias for $1; + v_pretty_name chat_rooms.pretty_name%TYPE; begin - 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 ); + select into v_pretty_name pretty_name from chat_rooms where room_id = p_room_id; + return v_pretty_name; +end;' language 'plpgsql'; - insert into chat_rooms - (room_id, pretty_name, description, moderated_p, active_p, archive_p, maximal_participants, end_date, creator, context_id,comm_name,auto_transcript_p,file_sent,private,frequency1) - 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,''true'',''false'',''false'',p_frequency1); - insert into chat_registered_users - (alias, room_id, user_id, RSS_service, mail_service, registered_id, frequency_mail) - values - (p_alias, v_room_id, p_creation_user, p_Rss_service, p_Mail_service, v_registered_id, p_frequency2); -return v_room_id; +------------------------------- -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; - -------------------------------- - -CREATE OR REPLACE FUNCTION chat_room__message_count (integer) RETURNS integer AS' +create function chat_room__message_count (integer) +returns integer as ' declare p_room_id alias for $1; v_count integer; @@ -567,108 +412,54 @@ where room_id = p_room_id; return v_count; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; +end;' language 'plpgsql'; --------------------------------- -CREATE OR REPLACE FUNCTION chat_room__delete_all_msgs (integer) RETURNS integer AS' + + +create function chat_room__delete_all_msgs (integer) +returns integer as ' declare p_room_id alias for $1; begin delete from chat_msgs where room_id = p_room_id; return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; +end;' language 'plpgsql'; --------------------------------- -CREATE OR REPLACE FUNCTION chat_room__del (integer) RETURNS integer AS' + +create function chat_room__del (integer) +returns integer as ' declare p_room_id alias for $1; begin --TO DO: delete transcriptions? - -- Erase all the users relate to this chat room. - delete from chat_private_room_users where room_id = p_room_id; - - -- Erase all the files sent relate to this chat room. - delete from chat_rooms_files_sent where room_id = p_room_id; - -- Erase all the messages relate to this chat room. + -- First erase all the messages relate to this chat room. delete from chat_msgs where room_id = p_room_id; - -- Erase all the registered users relate to this chat room. - delete from chat_registered_users where room_id = p_room_id; - - -- Erase all the transcripts relate to this chat room. - delete from chat_transcripts where room_id = p_room_id; - - -- Erase all the keywords to this chat room. - delete from chat_keywords where room_id = p_room_id; - -- Delete all privileges associate with this room delete from acs_permissions where object_id = p_room_id; -- Now delete the chat room itself. delete from chat_rooms where room_id = p_room_id; - - delete from rss_gen_subscrs where summary_context_id = p_room_id; - - delete from acs_objects where context_id = p_room_id; PERFORM acs_object__delete(p_room_id); return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; +end;' language 'plpgsql'; ---------------------------------- -CREATE OR REPLACE FUNCTION chat_room__delete_registered_users (integer, integer) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -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 chat_room_registered__user (varchar, integer, integer, boolean, boolean, integer, varchar, 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; - p_frequency_mail alias for $8; - 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, frequency_mail, registered_date) - values - (p_alias, p_room_id, p_user_id, p_RSS_service, p_mail_service, v_registered_id, p_frequency_mail, now()); -return v_registered_id; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---------------------------------- - - -CREATE OR REPLACE FUNCTION chat_transcript__new (varchar, varchar, varchar, integer, integer, timestamptz, integer, varchar, varchar) RETURNS integer AS' +create function chat_transcript__new (varchar, varchar, varchar, integer, integer, timestamptz, integer, varchar, varchar) +returns integer as ' declare p_pretty_name alias for $1; @@ -691,19 +482,17 @@ p_context_id ); - insert into chat_transcripts (transcript_id, pretty_name, contents, description, room_id, date) - values (v_transcript_id, p_pretty_name, p_contents, p_description, p_room_id, now()); + insert into chat_transcripts (transcript_id, pretty_name, contents, description, room_id) + values (v_transcript_id, p_pretty_name, p_contents, p_description, p_room_id); return v_transcript_id; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; +end;' language 'plpgsql'; ----------------------------- - - -CREATE OR REPLACE FUNCTION chat_transcript__del (integer) RETURNS integer AS' +create function chat_transcript__del (integer) +returns integer as ' declare p_transcript_id alias for $1; begin @@ -716,61 +505,41 @@ PERFORM acs_object__delete(p_transcript_id); return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; - - +end;' language 'plpgsql'; ---------------------------- -CREATE OR REPLACE FUNCTION chat_room__edit (varchar, varchar, varchar, varchar, integer, timestamp, boolean, boolean, boolean, boolean, boolean, integer, integer, varchar, varchar) RETURNS integer AS' -/* Nuevo cuerpo de Function */ + +create or replace function chat_room__edit (integer, varchar, varchar, boolean, boolean, boolean, boolean, boolean) +returns integer as ' declare - p_pretty_name alias for $1; - p_alias alias for $2; + p_room_id alias for $1; + p_pretty_name alias for $2; p_description alias for $3; - 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; - p_frequency1 alias for $14; - p_frequency_mail alias for $15; + 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; begin - update chat_rooms - set pretty_name = p_pretty_name, + 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, - frequency1 = p_frequency1 - where - room_id = p_room_id; - - update chat_registered_users - set alias = p_alias, - RSS_service = p_Rss_service, - mail_service = p_Mail_service, - frequency_mail = p_frequency_mail - where - room_id = p_room_id - and user_id = p_user_id; - - return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; + 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'; --------------------------- -CREATE OR REPLACE FUNCTION chat_room__message_post (integer, varchar, integer, varchar) RETURNS integer AS' +create function chat_room__message_post (integer, varchar, integer, varchar) +returns integer as ' declare p_room_id alias for $1; p_msg alias for $2; @@ -814,13 +583,13 @@ return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; +end;' language 'plpgsql'; --------------------------- -CREATE OR REPLACE FUNCTION chat_transcript__edit (integer, varchar, varchar, varchar) RETURNS integer AS' +create function chat_transcript__edit (integer, varchar, varchar, varchar ) +returns integer as ' declare p_transcript_id alias for $1; p_pretty_name alias for $2; @@ -836,379 +605,84 @@ return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; +end;' language 'plpgsql'; ------------------------------ -CREATE OR REPLACE FUNCTION chat_room__insert_keywords (varchar, integer) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare -p_word alias for $1; -p_room_id alias for $2; -begin -insert into chat_keywords - (keyword,room_id) - values - (p_word, p_room_id); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ----------------------------- -CREATE OR REPLACE FUNCTION chat_room__name (integer) RETURNS varchar AS' -declare - p_room_id alias for $1; - v_pretty_name chat_rooms.pretty_name%TYPE; -begin - select into v_pretty_name pretty_name from chat_rooms where room_id = p_room_id; - return v_pretty_name; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ----------------------------- -CREATE OR REPLACE FUNCTION chat_room__private_new (varchar, varchar, varchar, varchar, integer, timestamp, boolean, boolean, boolean, boolean, boolean, integer, varchar, integer, varchar, varchar, boolean) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare - p_pretty_name alias for $1; - p_alias alias for $2; - p_description alias for $3; - 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_name alias for $13; - p_creation_user alias for $14; - p_creation_ip alias for $15; - p_object_type alias for $16; - p_private alias for $17; - 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_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, maximal_participants, end_date, creator, context_id,comm_name,auto_transcript_p,file_sent,private) - 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,''true'',''false'',p_private); - insert into chat_registered_users - (alias, room_id, user_id, RSS_service, mail_service, registered_id) - values - (p_alias, v_room_id, p_creation_user, p_Rss_service, p_Mail_service, v_registered_id); -return v_room_id; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ------------------------------------- -CREATE OR REPLACE FUNCTION chat_room__send_files (integer, varchar, varchar, varchar, date, integer, integer, varchar, integer) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare - p_chat_id alias for $1; - p_file alias for $2; - p_title alias for $3; - p_description alias for $4; - p_date alias for $5; - p_context_id alias for $6; - p_creation_user alias for $7; - p_creation_ip alias for $8; - p_send_file_id alias for $9; -begin - insert into chat_rooms_files_sent - (room_id, file, title, description, date, send_file_id, message,creation_user) - values - (p_chat_id, p_file, p_title, p_description, p_date, p_send_file_id,''false'',p_creation_user); -return p_send_file_id; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ------------------------------------------ -CREATE OR REPLACE FUNCTION chat_room__send_files_message (integer) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare -p_chat_id alias for $1; -begin -update chat_rooms_files_sent set - message = ''true'' - where room_id = p_chat_id; -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ------------------------------------------ -CREATE OR REPLACE FUNCTION chat_rss__store_db (varchar, varchar, date, varchar, -varchar, varchar, timestamp) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare -p_room_name alias for $1; -p_room_description alias for $2; -p_end_date alias for $3; -p_r_creator alias for $4; -p_comm_name alias for $5; -p_registered_users alias for $6; -p_entry_timestamp alias for $7; -v_rss_id chat_rss.rss_id%TYPE; -begin -v_rss_id := acs_object__new(null,''chat_room'',now(),null,null,null ); -insert into chat_rss - ( room_name, creator, end_date, description, comm_name, -user_registered, rss_id,date, entry_timestamp) - values - (p_room_name, p_r_creator,p_end_date, -p_room_description,p_comm_name,p_registered_users,v_rss_id,now(),p_entry_timestamp); -return v_rss_id; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ------------------------------------------- -CREATE OR REPLACE FUNCTION chat_update_rss (varchar, varchar, timestamp, varchar, varchar, varchar, varchar) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare -p_room_name alias for $1; -p_room_creator alias for $2; -p_end_date alias for $3; -p_room_description alias for $4; -p_comm_name alias for $5; -p_msg_creator alias for $6; -p_registered_users alias for $7; -begin - update chat_rss set - room_name = p_room_name, - creator = p_room_creator, - end_date = p_end_date, - description = p_room_description, - comm_name = p_comm_name, --- xxx jopez no existe este campo ? msg_creator = p_msg_creator, - user_registered = p_registered_users - where room_id = p_room_id; - - return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; -------------------------------------- -CREATE OR REPLACE FUNCTION chat_upload (varchar, varchar, timestamp, varchar, varchar, varchar, varchar) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare -p_room_name alias for $1; -p_room_creator alias for $2; -p_end_date alias for $3; -p_room_description alias for $4; -p_comm_name alias for $5; -p_msg_creator alias for $6; -p_registered_users alias for $7; -begin - update chat_rss set - room_name = p_room_name, - creator = p_room_creator, - end_date = p_end_date, - description = p_room_description, - comm_name = p_comm_name, --- xxx jopez no existe este campo ? msg_creator = p_msg_creator - user_registered = p_registered_users - where rss_id = p_rss_id; - return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ------------------------------------ -CREATE OR REPLACE FUNCTION chat_room__edit_admin (varchar, boolean, boolean, integer, integer, varchar, integer, varchar) RETURNS integer AS' -/* Nuevo cuerpo de Function */ -declare - - p_alias alias for $1; - p_Rss_service alias for $2; - p_Mail_service alias for $3; - p_context_id alias for $4; - p_creation_user alias for $5; - p_creation_ip alias for $6; - p_room_id alias for $7; - p_frequency_mail alias for $8; - v_registered_id chat_rooms.room_id%TYPE; - - begin - - v_registered_id := acs_object__new(null,''chat_room'',now(),p_creation_user,p_creation_ip,p_context_id ); - if exists (select ru.user_id from chat_registered_users as ru where ru.room_id = p_room_id and ru.user_id = p_creation_user) then - else - insert into chat_registered_users - (alias, room_id, user_id, RSS_service, mail_service, registered_id, frequency_mail) - values - (p_alias, p_room_id, p_creation_user, p_Rss_service, p_Mail_service, v_registered_id, p_frequency_mail); - end if; -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; -------------------------------------- -CREATE OR REPLACE FUNCTION chat_room__store_transcripts_keywords (varchar, integer) RETURNS integer AS' -/* New function body */ -declare -p_keyword alias for $1; -p_transcript_id alias for $2; -begin -insert into chat_room_transcript_keywords - ( transcript_id,keyword) - values - (p_transcript_id,p_keyword); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ------------------------------------------- -CREATE OR REPLACE FUNCTION chat_rss__store_keywords_rss (integer, varchar) RETURNS integer AS' -/* New function body */ -declare -p_rss_id alias for $1; -p_keywords alias for $2; -begin -insert into chat_key_rss - (rss_id,key) - values - (p_rss_id, p_keywords); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---------------------------------------------- -CREATE OR REPLACE FUNCTION chat_rss__store_partitipants_rss (integer, varchar) RETURNS integer AS' -/* New function body */ -declare -p_rss_id alias for $1; -p_partitipant alias for $2; -begin -insert into chat_partitipants_rss - ( partitipant,rss_id) - values - (p_partitipant,p_rss_id); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ----------------------------------------------- -CREATE OR REPLACE FUNCTION chat_rss__store_sent_files_rss (integer, integer) RETURNS integer AS' -/* New function body */ -declare -p_rss_id alias for $1; -p_file_id alias for $2; -begin -insert into chat_files_rss - ( rss_id,file_id) - values - (p_rss_id, p_file_id); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---------------------------------------------- -CREATE OR REPLACE FUNCTION chat_rss__store_transcripts_rss (integer, integer) RETURNS integer AS' -/* New function body */ -declare -p_rss_id alias for $1; -p_t_id alias for $2; -begin -insert into chat_transcription_rss - ( rss_id,transcription_id) - values - (p_rss_id, p_t_id); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ----------------------------------------------- -CREATE OR REPLACE FUNCTION chat_transcript__store_partitipants_transcript (integer, varchar) RETURNS integer AS' -/* New function body */ -declare -p_transcript_id alias for $1; -p_partitipant alias for $2; -begin -insert into chat_partitipants_transcript - ( transcript_id,partitipant) - values - (p_transcript_id,p_partitipant); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ------------------------------------------------- -CREATE OR REPLACE FUNCTION chat_transcript__store_sent_files_tanscript (integer, integer) RETURNS integer AS' -/* New function body */ -declare -p_transcript_id alias for $1; -p_f_id alias for $2; -begin -insert into chat_file_transcript - ( transcript_id,file_id) - values - (p_transcript_id, p_f_id); -return 0; -end; -'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; @@ -1229,79 +703,3 @@ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -