OpenACS

maltes
committed
oacs-5-2:maltes:20061016074152
on 16 Oct 06
Minor bugfixes
/upgrade/upgrade-0.6.1.-0.6.2.sql (+63)
  1 -- file_mapping_table
  2 create table acs_mail_log_attachment_map (
  3         log_id                  integer
  4                                 constraint acs_mail_log_log_id2_fk
  5                                 references acs_mail_log(log_id),
  6         file_id                 integer
  7                                 constraint acs_mail_log_file_id_fk
  8                                 references cr_items(item_id)
  9 );
  10
  11 -- Get the file_ids and insert them into the tracking table
  12
  13 insert into acs_mail_log_attachment_map (log_id, file_id) select r.object_id_one as log_id, o.object_id as file_id
  14         from acs_data_links r, acs_objects o, acs_mail_log m
  15         where r.object_id_two = o.object_id
  16         and o.object_type in ('content_item')
  17         and r.object_id_one = m.log_id;
  18
  19
  20 insert into acs_mail_log_attachment_map (log_id, file_id) select r.object_id_one as log_id, cr.item_id as file_id
  21         from acs_data_links r, acs_objects o, acs_mail_log m, cr_revisions cr
  22         where r.object_id_two = o.object_id
  23         and o.object_id = cr.revision_id
  24         and o.object_type in ('content_revision')
  25         and r.object_id_one = m.log_id;
  26
  27 insert into acs_mail_log_attachment_map (log_id, file_id) select r.object_id_one as log_id, cr.item_id as file_id
  28         from acs_data_links r, acs_objects o, acs_mail_log m, cr_revisions cr
  29         where r.object_id_two = o.object_id
  30         and o.object_id = cr.revision_id
  31         and o.object_type in ('file_storage_object')
  32         and r.object_id_one = m.log_id;
  33
  34 insert into acs_mail_log_attachment_map (log_id, file_id) select r.object_id_one as log_id, cr.item_id as file_id
  35         from acs_data_links r, acs_objects o, acs_mail_log m, cr_revisions cr
  36         where r.object_id_two = o.object_id
  37         and o.object_id = cr.revision_id
  38         and o.object_type in ('image')
  39         and r.object_id_one = m.log_id;
  40
  41 create or replace function acs_mail_log__new (integer,varchar, integer, integer, varchar, varchar,integer,varchar,varchar,varchar)
  42 returns integer as '
  43 declare 
  44         p_log_id alias for $1;
  45         p_message_id alias for $2;
  46         p_sender_id alias for $3;
  47         p_package_id alias for $4;
  48         p_subject alias for $5;
  49         p_body alias for $6;
  50         p_object_id alias for $7;
  51         p_cc alias for $8;
  52         p_bcc alias for $9;
  53         p_to_addr alias for $10;
  54         v_log_id acs_mail_log.log_id%TYPE;
  55 begin
  56         insert into acs_mail_log
  57                 (log_id, message_id, sender_id, package_id, subject, body, sent_date, object_id, cc, bcc, to_addr)
  58         values
  59                 (v_log_id, p_message_id, p_sender_id, p_package_id, p_subject, p_body, now(), p_object_id, p_cc, p_bcc, p_to_addr);
  60
  61         return v_log_id;
  62
  63 end;' language 'plpgsql';