OpenACS

maltes
committed
MAIN:maltes:20061205041649
on 05 Dec 06
Fixed #2722
/upgrade/upgrade-5.0d6-5.1d.sql (+6 -21)
119 119   transcript_id INTEGER,
120 120   partitipant VARCHAR
121 121 ) WITH OIDS;
122 122
123 123 ---------------------------------
124 124
125 125 CREATE TABLE chat_room_transcript_keywords (
126 126   keyword VARCHAR,
127 127   transcript_id INTEGER
128 128 ) WITH OIDS;
129 129
130 130 ---------------------------------
131 131
132 132 CREATE TABLE chat_transcription_rss (
133 133   rss_id INTEGER,
134 134   transcription_id INTEGER
135 135 ) WITH OIDS;
136 136  
137 137 ---------------------------------
138 138
139   CREATE OR REPLACE FUNCTION chat_room__new (varchar, varchar, varchar, varchar, integer, timestamp, boolean, boolean, boolean, boolean, boolean, integer, integer, integer, varchar, varchar, varchar, varchar) RETURNS integer AS'
  139 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'
  140 /* Nuevo cuerpo de Function */
140 141 declare
141 142    p_pretty_name    alias for $1;
142 143    p_alias          alias for $2;
143 144    p_description    alias for $3;
144 145    p_key_words      alias for $4;
145 146    p_maxP           alias for $5;
146 147    p_end_date       alias for $6;
147 148    p_Rss_service    alias for $7;
148 149    p_Mail_service   alias for $8;
149 150    p_moderated_p    alias for $9;
150 151    p_active_p       alias for $10;
151 152    p_archive_p      alias for $11;
152 153    p_context_id     alias for $12;
153      p_comm_id        alias for $13;
  154    p_comm_name      alias for $13;
154 155    p_creation_user  alias for $14;
155 156    p_creation_ip    alias for $15;
156 157    p_object_type    alias for $16;
157 158    p_frequency1     alias for $17;
158 159    p_frequency2     alias for $18;
159 160    v_room_id        chat_rooms.room_id%TYPE;
160 161    v_registered_id  chat_rooms.room_id%TYPE;
161 162    v_comm_name      varchar;
162 163 begin
163 164      v_room_id := acs_object__new(null,''chat_room'',now(),p_creation_user,p_creation_ip,p_context_id   );
164 165      v_registered_id := acs_object__new(null,''chat_room'',now(),p_creation_user,p_creation_ip,p_context_id   );
165 166
166        if exists (select dot.pretty_name from dotlrn_communities_all as dot where dot.community_id = p_comm_id) then
167           select into v_comm_name dot.pretty_name from dotlrn_communities_all as dot where dot.community_id = p_comm_id;
168 167         insert into chat_rooms
169 168                (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)
170 169        values
171 170              (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);
172        else
173            insert into chat_rooms
174                   (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)
175            values
176                  (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'',''true'', ''false'',''false'',p_frequency1);
177        end if;
178 171
179 172
180 173    insert into chat_registered_users
181 174        (alias, room_id, user_id, RSS_service, mail_service, registered_id, frequency_mail)
182 175    values
183 176        (p_alias, v_room_id, p_creation_user, p_Rss_service, p_Mail_service, v_registered_id, p_frequency2);
184 177
185 178 return v_room_id;
186 179
187 180 end;
188 181 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
189 182  
190 183 -------------------------------
191 184
192 185 CREATE OR REPLACE FUNCTION chat_room__del (integer) RETURNS integer AS'
193 186 declare
194 187    p_room_id        alias for $1;
195 188 begin
196 189
197 190     --TO DO: delete transcriptions?
 
347 340
348 341 ---------------------------
349 342
350 343 CREATE OR REPLACE FUNCTION chat_room__insert_keywords (varchar, integer) RETURNS integer AS'
351 344 /* Nuevo cuerpo de Function */
352 345 declare
353 346 p_word      alias for $1;
354 347 p_room_id           alias for $2;
355 348 begin
356 349 insert into chat_keywords
357 350                (keyword,room_id)
358 351        values
359 352              (p_word, p_room_id);
360 353 return 0;
361 354
362 355 end;
363 356 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
364 357
365 358 ----------------------------
366 359
367   CREATE OR REPLACE FUNCTION chat_room__private_new (varchar, varchar, varchar, varchar, integer, timestamp, boolean, boolean, boolean, boolean, boolean, integer, integer, integer, varchar, varchar, boolean) RETURNS integer AS'
  360 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'
368 361 /* Nuevo cuerpo de Function */
369 362 declare
370 363    p_pretty_name    alias for $1;
371 364    p_alias          alias for $2;
372 365    p_description    alias for $3;
373 366    p_key_words      alias for $4;
374 367    p_maxP           alias for $5;
375 368    p_end_date       alias for $6;
376 369    p_Rss_service    alias for $7;
377 370    p_Mail_service   alias for $8;
378 371    p_moderated_p    alias for $9;
379 372    p_active_p       alias for $10;
380 373    p_archive_p      alias for $11;
381 374    p_context_id     alias for $12;
382      p_comm_id        alias for $13;
  375    p_comm_name        alias for $13;
383 376    p_creation_user  alias for $14;
384 377    p_creation_ip    alias for $15;
385 378    p_object_type    alias for $16;
386 379    p_private        alias for $17;
387 380    v_room_id        chat_rooms.room_id%TYPE;
388 381    v_registered_id  chat_rooms.room_id%TYPE;
389 382    v_comm_name      varchar;
390 383 begin
391 384
392 385      v_room_id := acs_object__new(null,''chat_room'',now(),p_creation_user,p_creation_ip,p_context_id   );
393 386      v_registered_id := acs_object__new(null,''chat_room'',now(),p_creation_user,p_creation_ip,p_context_id   );
394 387
395        if exists (select dot.pretty_name from dotlrn_communities_all as dot where dot.community_id = p_comm_id) then
396           select into v_comm_name dot.pretty_name from dotlrn_communities_all as dot where dot.community_id = p_comm_id;
397 388      insert into chat_rooms
398 389                (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)
399 390        values
400 391              (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);
401        else
402            insert into chat_rooms
403                   (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)
404            values
405                  (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'',''true'', ''false'',p_private);
406        end if;
407 392
408 393
409 394    insert into chat_registered_users
410 395        (alias, room_id, user_id, RSS_service, mail_service, registered_id)
411 396    values
412 397        (p_alias, v_room_id, p_creation_user, p_Rss_service, p_Mail_service, v_registered_id);
413 398
414 399 return v_room_id;
415 400
416 401 end;
417 402 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
418 403
419 404 ------------------------------------
420 405
421 406 CREATE OR REPLACE FUNCTION chat_room__send_files (integer, varchar, varchar, varchar, date, integer, integer, varchar, integer) RETURNS integer AS'
422 407 /* Nuevo cuerpo de Function */
423 408
424 409 declare
425 410    p_chat_id    alias for $1;
426 411    p_file    alias for $2;