Mailing List
Sender and Recepient can be anyone. Even non-registered users. But if a mailing list contains users who are not registered at the system a new account is created automatically. So storing the recepient_id is alright. From what I see this is not necessarily true for the sender. So storing the sender_id might break. Should we store the senders email address then as well?
Mailing list has some wrappers for acs-mail-lite but still uses in to finally send emails, so we should be alright with that package.
ML has also some log functionalities: ml_email_log-table stores the senders user_id, the mail_job_id and when the job was sent as send_date while the table ml_mail_jobs stores the complete job content including attributes like mails_sent, mails_bounced. To reconstruct the list of users who actually received the email the table ml_mailing_list_user_map exists. But no versioning is supported. Changing the list will return bad results from a history perspective.
We need to extract the mail specific api and tracking from mailing list into more general packages.
ACS Mail Lite - revisited
The acs_mail_lite_queue table is not very well designed. The attribute to_addr stores a tcl list of the format
email email_address name namefromdb ser_id user_id_if_present_or_empty_string
. This means in the worst case that we don't know who the receiver is.
My suggestion to solve the logging using a trigger makes this a bit tricky since I have to use regular expressions to extract the user_id from to_addr. Here a solution:
v_recepient_id := substring (to_addr from \'user_id ([0-9]+)\');
Now the question is what we do if
null is returned. I would suggest we simply raise a notice and fall through without logging that email. Any comments?
Service Contract/ Call Back versus Trigger
Due to the above problems another solution would be to define a service contract or call back instead that is called from each package that wants to log messages sent out. This solution would allow a more application driven compared to the above database driven solution but would also mean that each packages mentioned in my former post need to be touched.
Any comments?
