SQL logging

[Steven Patterson]

----- Original Message -----
From: "Julian Field" <mailscanner at ECS.SOTON.AC.UK>
To: <MAILSCANNER at JISCMAIL.AC.UK>


> 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.

Most definitely true - and you'll get a very noticable perfomance
improvement by doing it outside the loop!

Can I make a couple of other comments, mostly to do with support for other
RDBMS in the future?

- Don't use "timestamp", insert the system time manually.  You won't find an
equivalent "timestamp" data type in Oracle (as one example) and instead
you'd have to write a trigger.  While this isn't particularly difficult it's
probably easier to make the database end of things as simple as possible and
write generic code.
- Similarly don't use "tinyint", etc - use a nice ANSI datatype like
char(1).  Is decimal() an ANSI datatype?
- Dump the TYPE= on the table definitaion?
- The parameters for DBI->connect really ought to be in your config file,
not hard-coded into your procedure.
- Make sure you set autocommit to "off" and simply commit every 200 rows or
so (and before dbh->disconnect) - it'll be a huge performance boost (on a
"real" RDBMS with proper transaction support, at least)
- Not necessary, but a comment on the SQL stye - it's ALWAYS sensible to
INSERT INTO table(column1, column2, ...) VALUES (arg1, arg2, ...) rather
than INSERT across all columns because then your application doesn't break
if the table definition changes.

I'm more of the opinion that getting your logs into a database is a wider
problem than mailscanner and it shouldn't really be tackled by the
appication (rather it would ideally be handled by syslogd) but I appreciate
that it's a convenient feature for some.  I'll help if I can!

Steve
--
Steven Patterson, MSci OCP.  Tel: +44 (0)2380 595810
Primary Information Services Support and Development
Information Systems Services, University of Southampton, UK.
Public PGP Key: http://www.bottleneck.org/pubkey.php