Hamie,
> But that's a log entry (And looks like it's intended purely as a log
> to be read sequentially, or by quick lookup by mailid)...
Not necessarily. The SQL database can be used as an index into quarantined
files. The quarantine file name contains mail_id, which is unique and is
the same mail_id as the in SQL table records (unless you override the
default template in $*_quarantine_method and lose mail_id from quarantine
file names).
> Which of course will grow for as long as you keep your logs around.
Trim it, e.g. nightly by a cron job. The exact SQL commands are included
at the end of README.sql, you only need to adjust the period to be kept
(the example uses 7 days):
DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 7*24*60*60;
DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 60*60 AND content IS NULL;
DELETE quarantine FROM quarantine LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;
DELETE msgrcpt FROM msgrcpt LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;
DELETE FROM maddr
WHERE NOT EXISTS (SELECT sid FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT rid FROM msgrcpt WHERE rid=id);
OPTIMIZE TABLE msgs, msgrcpt, maddr, quarantine;
The same cron job can use 'find' and delete quarantine files
older that the same n-day period, so you can keep SQL and files
in sync.
> But what I
> was really after was a SQL index of currently quarantined items.
Which it is.
> Which wouldn't be kept for as long as the logs (Only as long as the
> quarantined items themselves).
Delete old records or not delete them, it is up to you.
> Basically I was intent on separating out the info for performance
> mainly... To make it eiaser & quicker to find currently quarantined
> items by recipient. And thus where they were, to display info about
> the mail item & then remove or optionally un-quarantine the item for
> delivery.
Yes, that was the main purpose.
> Sorry. Without an ER diagram I'm having to piece together the
> relationships between the tables by hand... If the msgrcpt table has a
> row for every eamil,
No, in msgrcpt table there is a row for every recipient.
A mail may have more than one recipient. The unique key is mail_id + rid
(rid is a recipient id, to be joined with maddr.id to obtain
recipient e-mail address).
The table msgs has exactly one row for every mail.
msgrcpt is to be joined with msgs on mail_id to put together
information pertaining to the message as a whole, with per-recipient
additional information.
> that COULD doit, but it means a join in the
> select to find all the quarantined items to a particular email
> address...
There is normally one or none quarantine file for each msgs record.
If msgs.quar_type is space then mail was not quarantined, otherwise
there should be a quarantine file belonging to this msgs record,
with a name consisting of the same mail_id (with possible prefix
and suffix, depending on how you set the $*_quarantine_method template).
> And then there' still no status to say whether the item
> still exists
If you keep the habit (in the cron script) to trim quarantine files
and SQL records to the same moment in the past, both will be consistent.
Even if you don't keep the two exactly in sync, one can decide that
the SQL database is authoritative, and there may be a couple of older
quarantine files lying around, eventually to be deleted, but no longer
indexed.
> (Wheras with a separate index the auarantine expiry could
> indicate whether the itsem (should) still be in the file area or gone
> already.
You can go to trouble of deleting old files and SQL records in sync,
one by one, but it is not efficient and gains nothing.
> (Although an extra column could do that, and with 3x chars in
> a row it could be added into the table without resizing it... Although
> non-quarantined items would incur a NULL and I hate NULL's in SQL
> databases, so I still think it would be best as a separate quarentine
> index table.... I seem to be rambling somewhat here..).
I really don't see what the problem is.
Mark
-------------------------------------------------------
This SF.Net email is sponsored by Yahoo.
Introducing Yahoo! Search Developer Network - Create apps using Yahoo!
Search APIs Find out how you can build Yahoo! directly into your own
Applications - visit http://developer.yahoo.net/?fr=offad-ysdn-ostg-q22005
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
