Index: openacs-4/packages/mail-tracking/sql/postgresql/mail-tracking-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/mail-tracking/sql/postgresql/mail-tracking-create.sql,v diff -u -N --- openacs-4/packages/mail-tracking/sql/postgresql/mail-tracking-create.sql 14 May 2006 16:11:19 -0000 1.7 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,273 +0,0 @@ --- ================================================================================ --- Postgres SQL Script File --- --- --- @Location: mail-tracking\sql\postgresql\acs_mail_log-create.sql --- --- @author: Nima Mazloumi --- @creation-date: Mon May 30 17:55:50 CEST 2005 --- @cvs-id $Id: mail-tracking-create.sql,v 1.7 2006/05/14 16:11:19 maltes Exp $ --- ================================================================================ --- --- - --- ====================================================== --- Tracking Table acs_object_log -- --- ====================================================== - -create table acs_mail_log ( - - log_id integer primary key - constraint acs_mail_log_object_id_fk - references acs_objects(object_id), - message_id varchar(300), - -- object_id of the object that triggered the sending of the email - object_id integer - constraint acs_mail_log_owner_id_fk - references acs_objects(object_id), - sender_id integer - constraint acs_mail_log_sender_id_fk - references parties(party_id), - package_id integer, - subject varchar(1024), - body text, - -- List of CC/BCC E-Mail addresses, seperated by "," as passed in from acs-mail-lite::send prozedures - -- Only used for those emails that do not have a party_id in openacs. - cc varchar(4000), - bcc varchar(4000), - sent_date timestamp); - -create index acs_mail_log_object_idx on acs_mail_log(object_id); -create index acs_mail_log_sender_idx on acs_mail_log(sender_id); - -create table acs_mail_log_recipient_map ( - recipient_id integer constraint - acs_mail_log_recipient_id_fk - references parties(party_id), - log_id integer - constraint acs_mail_log_log_id_fk - references acs_mail_log(log_id), - type varchar(30) -); - -create index acs_mail_log_recipient_map_log_idx on acs_mail_log_recipient_map(log_id); -create index acs_mail_log_recipient_map_recipient_idx on acs_mail_log_recipient_map(recipient_id); -create index acs_mail_log_um_log_rec_idx on acs_mail_log_recipient_map(log_id,recipient_id,type); - --- create the content type -select acs_object_type__create_type ( - 'mail_log', -- content_type - '#mail-tracking.ACS_Mail_Log_Entry#', -- pretty_name - '#mail-tracking.ACS_Mail_Log_Entries#', -- pretty_plural - 'acs_object', -- supertype - 'acs_mail_log', -- table_name (should this be pm_task?) - 'log_id', -- id_column - 'mail_tracking', -- package_name - 'f', -- abstract_p - NULL, -- type_extension_table - NULL -- name_method -); - -create or replace function acs_mail_log__new (integer,varchar, integer, integer, varchar, varchar,integer,varchar,integer,integer,varchar,varchar,varchar) -returns integer as ' -declare - p_log_id alias for $1; - p_message_id alias for $2; - p_sender_id alias for $3; - p_package_id alias for $4; - p_subject alias for $5; - p_body alias for $6; - p_creation_user alias for $7; - p_creation_ip alias for $8; - p_context_id alias for $9; - p_object_id alias for $10; - p_cc alias for $11; - p_bcc alias for $12; - p_to_addr alias for $13; - v_log_id acs_mail_log.log_id%TYPE; -begin - v_log_id := acs_object__new ( - p_log_id, -- object_id - ''mail_log'' -- object_type - ); - - insert into acs_mail_log - (log_id, message_id, sender_id, package_id, subject, body, sent_date, object_id, cc, bcc, to_addr) - values - (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); - - return v_log_id; - -end;' language 'plpgsql'; - - -create function acs_mail_log__delete (integer) -returns integer as' -declare - p_message_id alias for $1; -begin - - delete from acs_mail_log where message_id = p_message_id; - - raise NOTICE ''Deleting Acs Mail Log Entry...''; - - PERFORM acs_object_delete(p_message_id); - - return 0; - -end;' language 'plpgsql'; - - --- ====================================================== --- Tracking requests table acs_mail_tracking_request -- --- ====================================================== - -create table acs_mail_tracking_request ( - request_id integer - constraint acs_mail_request_id_pk - primary key, - user_id integer - constraint acs_mail_request_user_id_fk - references users (user_id), - -- on delete cascade, - -- The package instance this request pertains to - object_id integer - constraint acs_mail_request_object_id_fk - references acs_objects (object_id) - -- on delete cascade -); - - -create or replace function acs_mail_tracking_request__new (integer,integer,integer) -returns integer as ' - -DECLARE - p_request_id alias for $1; - p_object_id alias for $2; - p_user_id alias for $3; - v_request_id integer; - -BEGIN - - select t_acs_object_id_seq.NEXTVAL into v_request_id; - - insert into acs_mail_tracking_request - (request_id, object_id, user_id) - values - (p_request_id, p_object_id, p_user_id); - - return v_request_id; - -END; -' language 'plpgsql'; - - -create or replace function acs_mail_tracking_request__delete(integer) -returns integer as ' -declare - p_request_id alias for $1; -begin - delete from acs_mail_tracking_request where request_id = p_request_id; - return 0; -end; -' language 'plpgsql'; - - -create or replace function acs_mail_tracking_request__delete_all(integer) -returns integer as ' -declare - v_request RECORD; - -begin - for v_request in select request_id from acs_mail_tracking_request - loop - perform acs_mail_tracking_request__delete(v_request.request_id); - end loop; - - return 0; -end; -' language 'plpgsql'; - - --- ====================================================== --- Tracking Trigger acs_mail_log_tr -- --- ====================================================== - --- CREATE OR REPLACE FUNCTION public.acs_mail_log_tr() --- RETURNS trigger AS ---' ---declare --- v_recepient_id integer; --- v_sender_id integer default 0; --- v_track_all_p bool default 0; --- v_object_id integer; --- begin --- --- if old.package_id is null then --- raise notice \'Tracking: No way to track. Package Id was %. You need to check why.\', old.package_id; --- return old; --- end if; --- --- v_recepient_id := substring (old.to_addr from \'user_id ([0-9]+)\'); --- select into v_sender_id party_id from parties where email = old.from_addr; --- --- if v_recepient_id is null then --- raise notice \'Tracking: Unable to extract user_id from: %. Not able to log this message.\', old.to_addr; --- return old; --- end if; --- --- if v_sender_id is null then --- raise notice \'Tracking: Unknown sender %. Not able to log this message.\', old.from_addr; --- return old; --- end if; --- --- -- if TrackAllMails parameter is set to 0 we only track mails from packages that have requests --- --- select into v_track_all_p pv.attr_value --- from apm_parameter_values pv, apm_parameters p --- where p.parameter_id = pv.parameter_id --- and p.parameter_name = \'TrackAllMails\' --- and p.package_key = \'mail-tracking\' --- limit 1; --- --- if v_track_all_p = \'1\' then --- --- perform acs_mail_log__new ( --- old.message_id, --- v_recepient_id, --- v_sender_id, --- old.package_id, --- old.subject, --- old.body --- ); --- --- else --- select into v_object_id object_id from acs_mail_tracking_request where object_id = old.package_id; --- --- if v_object_id is not null then --- --- raise notice \'Tracking: Logged mail for package_id %.\', v_object_id; --- --- perform acs_mail_log__new ( --- old.message_id, --- v_recepient_id, --- v_sender_id, --- old.package_id, --- old.subject, --- old.body --- ); --- else --- raise notice \'Tracking: No request for package id % and tracking all mails is turned off.\', old.package_id; --- end if; --- --- end if; --- --- return old; --- end; --- --- LANGUAGE 'plpgsql'; --- --- ---reate trigger acs_mail_log_tr after delete on acs_mail_lite_queue ---or each row execute procedure acs_mail_log_tr(); --- \ No newline at end of file