SQL logging

[Steve Freegard]

Julian,

You're right of course.  Corrected version attached, tested and a bit more
speedier.

Steve.

-----Original Message-----
From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
Sent: 26 February 2003 18:27
To: MAILSCANNER at jiscmail.ac.uk
Subject: Re: SQL logging


One question on your code:

     # Insert @fields into a database table
     my($sth) = $dbh->prepare("INSERT INTO maillog VALUES
(?,?,?,?,?,?,?,?,?,?,?,
?,?,?)");

$sth->execute($fields[0],$fields[1],$fields[2],$fields[3],$fields[4],$fields
[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fields[11],$fie
lds[
12],$fields[13]) or MailScanner::Log::DieLog("Cannot insert row: %s",
$DBI::errs
tr);

Surely you can just do the dbh->prepare once before the loop and then run
sth->execute inside the loop. Otherwise you are doing loads of "prepare"
statements you don't need.

Can you test this for me please before I commit the code into the main
codebase?

At 17:58 26/02/2003, you wrote:
>Julian,
>
>Thanks again for all your work on this.
>
>As promised - I've attached a tar.gz with all the patches that you wrote
>with my modifications and instructions on how to install SQL logging under
>RedHat 7.3.
>
>I've been using this live on by eval box for a few days now with no
problems
>- there is still plenty that could be improved on, but at least it's a
>start.
>
>Kind regards,
>Steve.
>
>-----Original Message-----
>From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
>Sent: 23 February 2003 14:33
>To: MAILSCANNER at jiscmail.ac.uk
>Subject: Re: SQL logging
>
>
>Folks,
>
>I have written you some code which will do this:
>
>1. Log information about every message to a temporary file while
>MailScanner is running, giving you minimum performance hit. I've written
>all the code you need to do this bit.
>2. When MailScanner worker processes die of old age, or are shutdown
>cleanly (don't use kill -9 on them!), they will read their log file and
>pump the contents into an SQL database all at 1 go, which should be nice
>and quick. There are bits missing from this, see the comments in the code.
>
>I don't log everything about each message, but I log a fair chunk of info
>about each one. I assume you will customise this to log what you reckon you
>need.
>It creates 1 record per message (whether clean or infected) and then also
>generates an extra record for each infection report. So a clean message
>will produce 1 record, a message with 2 infections will produce 3 records.
>The SQL "INSERT" code is deliberately missing. You can write that better
>than me anyway.
>
>To use it, apply the other patches I supplied the other day to give you a
>new configuration option "Always Looked Up Last".
>Then insert the attached file on the end of CustomConfig.pm and write the
>SQL code you need, and create the database and the db table this lot is
>going to be written to.
>Then set
>         Always Looked Up Last = &SQLLogging
>in your MailScanner.conf and "restart" (not just "reload") MailScanner.
>
>To find obvious syntax errors, I would advise you do
>         perl -c CustomConfig.pm
>before trying to use it, as that will catch syntax screw-ups.
>
>I make no guarantees whatsoever about the attached code actually working. I
>have written it to get you started, and to show you how to pull interesting
>things out of the "Message" data structure. If you want the full list of
>Message attributes, read the top of Message.pm.
>
>Please let me know how you get on. If someone can post me some working code
>with all the SQL bits written and tested, I will happily include it in
>future releases (next release due on 1st March).
>
>
>
>**********************************************************************
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the system manager.
>
>This footnote also confirms that this email message has been swept by
>MIMEsweeper for the presence of computer viruses.
>
>www.lbsltd.co.uk
>**********************************************************************
>

--
Julian Field
www.MailScanner.info
MailScanner thanks transtec Computers for their support



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.lbsltd.co.uk
**********************************************************************

-------------- next part --------------
A non-text attachment was scrubbed...
Name: CustomConfig.pm
Type: application/octet-stream
Size: 7352 bytes
Desc: not available
Url : http://lists.mailscanner.info/pipermail/mailscanner/attachments/20030226/17c32ba5/CustomConfig.obj