<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2655.35">
<TITLE>RE: SQL Redux</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>I added a counter so it would write to DB after n emails and then remaining when shutdown.</FONT>
</P>
<P><FONT SIZE=2>Seemed like a good compromise.</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Kearney, Rob [<A HREF="mailto:RKearney@AZERTY.COM">mailto:RKearney@AZERTY.COM</A>]</FONT>
<BR><FONT SIZE=2>Sent: Thursday, July 31, 2003 10:57 AM</FONT>
<BR><FONT SIZE=2>To: MAILSCANNER@jiscmail.ac.uk</FONT>
<BR><FONT SIZE=2>Subject: Re: SQL Redux</FONT>
</P>
<BR>
<P><FONT SIZE=2>here is what we did for SQL logging, to bypass temp-file stuff.</FONT>
</P>
<P><FONT SIZE=2>Just took the SQLLogging and made SQLRTLogging, to write directly to DB, We</FONT>
<BR><FONT SIZE=2>have not noticed any degradation in performance</FONT>
<BR><FONT SIZE=2>Basically, we took the functions of SQLLogging and EndSQLLogging and put</FONT>
<BR><FONT SIZE=2>them together.</FONT>
<BR><FONT SIZE=2>(dont forget Init and End scripts also</FONT>
</P>
<P><FONT SIZE=2>---</FONT>
<BR><FONT SIZE=2>sub SQLRTLogging {</FONT>
<BR><FONT SIZE=2> my($message) = @_;</FONT>
<BR><FONT SIZE=2> my($dbh);</FONT>
<BR><FONT SIZE=2> $dbh =</FONT>
<BR><FONT SIZE=2>DBI->connect("DBI:mysql:mailscanner:localhost:mysql_socket=/var/database/mys</FONT>
<BR><FONT SIZE=2>ql/mysql.sock",</FONT>
<BR><FONT SIZE=2> "mailscanner", "mailscanner",</FONT>
<BR><FONT SIZE=2> {'PrintError' => 0})</FONT>
<BR><FONT SIZE=2> or MailScanner::Log::DieLog("Cannot connect to the database: %s",</FONT>
<BR><FONT SIZE=2> $DBI::errstr);</FONT>
</P>
<P><FONT SIZE=2> my $id = $message->{id};</FONT>
<BR><FONT SIZE=2> my $size = $message->{size};</FONT>
<BR><FONT SIZE=2> my $from = $message->{from};</FONT>
<BR><FONT SIZE=2> my ($from_user, $from_domain);</FONT>
</P>
<P><FONT SIZE=2> # split the from address into user and domain bits.</FONT>
<BR><FONT SIZE=2> # This may be unnecessary for you; we use it to more easily determine</FONT>
<BR><FONT SIZE=2> # inbound vs outbound email in a multi-domain environment.</FONT>
<BR><FONT SIZE=2> # HINT: refine queries using SQL 'join' with a table containing local</FONT>
<BR><FONT SIZE=2> # domains.</FONT>
</P>
<P><FONT SIZE=2> ($from_user, $from_domain) = split /\@/, $from;</FONT>
</P>
<P><FONT SIZE=2> my @to = @{$message->{to}};</FONT>
<BR><FONT SIZE=2> my $subject = $message->{subject};</FONT>
<BR><FONT SIZE=2> my $clientip = $message->{clientip};</FONT>
<BR><FONT SIZE=2> my $archives = join(',', @{$message->{archiveplaces}});</FONT>
<BR><FONT SIZE=2> my $isspam = $message->{isspam};</FONT>
<BR><FONT SIZE=2> my $ishighspam = $message->{ishigh};</FONT>
<BR><FONT SIZE=2> my $sascore = $message->{sascore};</FONT>
<BR><FONT SIZE=2> my $spamreport = $message->{spamreport};</FONT>
</P>
<P><FONT SIZE=2> # Get rid of control chars and tidy-up SpamAssassin report</FONT>
<BR><FONT SIZE=2> $spamreport =~ s/\n/ /g;</FONT>
<BR><FONT SIZE=2> $spamreport =~ s/\t//g;</FONT>
</P>
<P><FONT SIZE=2> # Get timestamp, and format it so it is suitable to use with MySQL</FONT>
<BR><FONT SIZE=2> my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();</FONT>
<BR><FONT SIZE=2> my($timestamp) = sprintf("%d-%02d-%02d %02d:%02d:%02d",</FONT>
<BR><FONT SIZE=2> $year+1900,$mon+1,$mday,$hour,$min,$sec);</FONT>
</P>
<P><FONT SIZE=2># maillog_mail insert</FONT>
<BR><FONT SIZE=2> my @fields=($timestamp, $id, $size, $from_user, $from_domain,</FONT>
<BR><FONT SIZE=2> $subject, $clientip, $archives, $isspam, $ishighspam,</FONT>
<BR><FONT SIZE=2> $sascore, $spamreport);</FONT>
<BR><FONT SIZE=2> map { s/\'/\\'/g } @fields;</FONT>
</P>
<P><FONT SIZE=2> # Insert @fields into a database table</FONT>
<BR><FONT SIZE=2> my($sth) = $dbh->prepare("INSERT INTO maillog_mail (time, msg_id, size,</FONT>
<BR><FONT SIZE=2>from_user, from_domain, subject, clientip, archives, isspam, ishighspam,</FONT>
<BR><FONT SIZE=2>sascore, spamreport) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");</FONT>
</P>
<P><FONT SIZE=2>$sth->execute($fields[0],$fields[1],$fields[2],$fields[3],$fields[4],$fields</FONT>
<BR><FONT SIZE=2>[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fields[11]) or</FONT>
<BR><FONT SIZE=2>MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);</FONT>
</P>
<BR>
<P><FONT SIZE=2> my($file, $text);</FONT>
<BR><FONT SIZE=2> while(($file, $text) = each %{$message->{allreports}}) {</FONT>
<BR><FONT SIZE=2> $file = "the entire message" if $file eq "";</FONT>
<BR><FONT SIZE=2> # Use the sanitised filename to avoid problems caused by people forcing</FONT>
<BR><FONT SIZE=2> # logging of attachment filenames which contain nasty SQL instructions.</FONT>
<BR><FONT SIZE=2> $file = $message->{file2safefile}{$file} or $file;</FONT>
<BR><FONT SIZE=2> $text =~ s/\n/ /; # Make sure text report only contains 1 line</FONT>
<BR><FONT SIZE=2> $text =~ s/\t/ /; # and no tab characters</FONT>
</P>
<P><FONT SIZE=2> my @fields = ($id, $file, $text);</FONT>
<BR><FONT SIZE=2> map { s/\'/\\'/g } @fields;</FONT>
</P>
<P><FONT SIZE=2> my($sth) = $dbh->prepare("INSERT INTO maillog_report (msg_id, filename,</FONT>
<BR><FONT SIZE=2>filereport) VALUES (?,?,?)");</FONT>
<BR><FONT SIZE=2> $sth->execute($fields[0],$fields[1],$fields[2]) or</FONT>
<BR><FONT SIZE=2>MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);</FONT>
</P>
<P><FONT SIZE=2> }</FONT>
</P>
<P><FONT SIZE=2> for (@to) {</FONT>
<BR><FONT SIZE=2> # again, split the recipient's email into user and domain halves first.</FONT>
<BR><FONT SIZE=2> # see comment above about splitting the email like this.</FONT>
</P>
<P><FONT SIZE=2> my ($to_user, $to_domain);</FONT>
<BR><FONT SIZE=2> ($to_user, $to_domain) = split /\@/, $_;</FONT>
<BR><FONT SIZE=2> my @fields = ($id, $to_user, $to_domain);</FONT>
<BR><FONT SIZE=2> map { s/\'/\\'/g } @fields;</FONT>
<BR><FONT SIZE=2> my($sth) = $dbh->prepare("INSERT INTO maillog_recipient (msg_id,</FONT>
<BR><FONT SIZE=2>to_user, to_domain) VALUES (?,?,?)");</FONT>
<BR><FONT SIZE=2> $sth->execute($fields[0],$fields[1],$fields[2]) or</FONT>
<BR><FONT SIZE=2>MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);</FONT>
<BR><FONT SIZE=2> }</FONT>
</P>
<P><FONT SIZE=2> # Close database connection</FONT>
<BR><FONT SIZE=2> $dbh->disconnect();</FONT>
</P>
<P><FONT SIZE=2>}</FONT>
</P>
<P><FONT SIZE=2> MailScanner::Log::InfoLog("Ending SQL Real-Time Logging");</FONT>
<BR><FONT SIZE=2>}</FONT>
</P>
<P><FONT SIZE=2>1;</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=2>-rob</FONT>
</P>
</BODY>
</HTML>