On Dec 12, 2010, at 6:32 PM, Zhang Huangbin wrote:
> Hi, all.
>
> I have "@storage_sql_dsn = ([DBI:mysql:xxx]);" configured in amavisd,
> the question is, how can i know which record in sql table "msgs" is
> incoming or outgoing message?
I fetch all "received" mails with below SQL:
# <--
SELECT
msgs.mail_id, msgs.subject, msgs.time_iso,
sender.email as sender_email,
recip.email as recipient
FROM msgs
LEFT JOIN msgrcpt ON msgs.mail_id = msgrcpt.mail_id
LEFT JOIN maddr AS sender ON msgs.sid = sender.id
LEFT JOIN maddr AS recip ON msgrcpt.rid = recip.id
WHERE content IS NOT NULL AND recip.domain IN ('com.example', 'com.test')
ORDER BY msgs.time_num DESC
LIMIT 50
OFFSET 0
# <--
It can fetch all received mails which sent to "example.com" and "test.com".
But when i use similar command to fetch "sent" mails, it returns NULL
"sender_email" and "recipient".
I try to fetch all mails sent from users under domain "example.com" with below
SQL command:
# <--
SELECT
msgs.sid, msgs.mail_id,
sender.email as sender_email,
recip.email as recipient
FROM msgs
LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
LEFT JOIN maddr AS sender ON (msgs.sid = sender.id AND sender.domain in
('com.example'))
LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id AND recip.domain NOT in
('com.example'))
WHERE content IS NOT NULL
ORDER BY msgs.time_num DESC
LIMIT 10
OFFSET 0;
# <--
Sample output:
+-----+--------------+-----------------------+------------------------+
| sid | mail_id | sender_email | recipient |
+-----+--------------+-----------------------+------------------------+
| 1 | j921ztGurlsw | z...@example.com | NULL |
| 1 | veX1EVEonw77 | z...@example.com | some...@gmail.com |
| 1 | EaXIeScIkpLJ | z...@example.com | s...@gmail.com |
| 108 | psRl0Cz-bAz6 | NULL | NULL |
| 107 | blB1pPpVt9F8 | NULL | t...@test.de |
| 106 | RGKmzi2q3zmp | NULL | j...@test-daniel.org |
| 104 | iSEYFQtGgxnM | NULL | some...@hotmail.com |
| 103 | 93aOvkS+sNVL | NULL | wuha...@iredtest.com |
| 89 | zNDHL3GSfTOJ | chensh...@example.com | NULL |
| 89 | zNDHL3GSfTOJ | chensh...@example.com | z...@otherdomain.com |
+-----+--------------+-----------------------------+------------------+
As you can see, if sender or recipient domain is "example.com", it shows as
"NULL".
How can i remove/filter records with sender_email=NULL or recipient=NULL?
Thanks :)
------------------------------------------------------------------------------
Oracle to DB2 Conversion Guide: Learn learn about native support for PL/SQL,
new data types, scalar functions, improved concurrency, built-in packages,
OCI, SQL*Plus, data movement tools, best practices and more.
http://p.sf.net/sfu/oracle-sfdev2dev
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
Please visit http://www.ijs.si/software/amavisd/ regularly
For administrativa requests please send email to rainer at openantivirus dot
org
