Jorge, you are one lazy bum.
All of this information (except the delivery status) is already there in
the database for you to retrieve.
datetime (dbmail_physmessage.internal_date)
from (dbmail_fromfield)
to (dbmail_tofield)
subject (dbmail_subjectfield)
msg_size (dbmail_physmessage.rfcsize)
dest_user (dbmail_mailbox.owner_idnr)
status (delivered/deleted by a sieve script/something else)
So try this:
create view received_messages as
select u.userid,p.internal_date,p.rfcsize,
s.subjectfield,f.fromfield,t.tofield
from dbmail_physmessage p
join dbmail_subjectfield s on s.physmessage_id=p.id
join dbmail_fromfield f on f.physmessage_id=p.id
join dbmail_tofield t on t.physmessage_id=p.id
join dbmail_messages m on m.physmessage_id=p.id
join dbmail_mailboxes b on m.mailbox_idnr=b.mailbox_idnr
join dbmail_users u on b.owner_idnr=u.user_idnr;
And so a select on the new view. Will work just fine on (> 2.2 and <
2.3.6) with a minor modification:
create view received_messages as
select u.userid,p.internal_date,p.rfcsize,
s.subjectfield,f.fromaddr,t.toaddr
from dbmail_physmessage p
join dbmail_subjectfield s on s.physmessage_id=p.id
join dbmail_fromfield f on f.physmessage_id=p.id
join dbmail_tofield t on t.physmessage_id=p.id
join dbmail_messages m on m.physmessage_id=p.id
join dbmail_mailboxes b on m.mailbox_idnr=b.mailbox_idnr
join dbmail_users u on b.owner_idnr=u.user_idnr;
So make sure you drop this view before upgrading to 2.3.6!
And no: I *really* don't want to keep tabs on messages deleted/forwarded
by sieve! Grep the logs!
Jorge Bastos wrote:
> Hi,
>
> John & Paul,
>
>
>
> I talked about this in the past, funny is that if I had this today it
> were very handy, I had one client that needed this confirmation, of a
> certain email was received by the server ‘cause someone “told” him that
> had sent it and it wasn’t delivered.
>
>
>
> The idea is to create a table and log the received messages by LMTPD,
> something like:
>
>
>
> datetime,from,to,subject (if possible),msg_size,dest_user,status
> (delivered/deleted by a sieve script/something else)
>
>
>
> this should have a parameter to switch on/off in dbmail.conf also.
>
>
>
> John, could you give a hand on this? I have no idea how to do it :P
>
>
>
>
>
> Jorge,
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Dbmail-dev mailing list
> [email protected]
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
