SQL Redux

[Kearney, Rob]

here is what we did for SQL logging, to bypass temp-file stuff.

Just took the SQLLogging and made SQLRTLogging, to write directly to DB, We
have not noticed any degradation in performance
Basically, we took the functions of SQLLogging and EndSQLLogging and put
them together.
(dont forget Init and End scripts also

---
sub SQLRTLogging {
  my($message) = @_;
  my($dbh);
  $dbh =
DBI->connect("DBI:mysql:mailscanner:localhost:mysql_socket=/var/database/mys
ql/mysql.sock",
                      "mailscanner", "mailscanner",
                      {'PrintError' => 0})
   or MailScanner::Log::DieLog("Cannot connect to the database: %s",
                                $DBI::errstr);

  my $id = $message->{id};
  my $size = $message->{size};
  my $from = $message->{from};
  my ($from_user, $from_domain);

  # split the from address into user and domain bits.
  # This may be unnecessary for you; we use it to more easily determine
  # inbound vs outbound email in a multi-domain environment.
  # HINT: refine queries using SQL 'join' with a table containing local
  # domains.

  ($from_user, $from_domain) = split /\@/, $from;

  my @to   = @{$message->{to}};
  my $subject = $message->{subject};
  my $clientip = $message->{clientip};
  my $archives = join(',', @{$message->{archiveplaces}});
  my $isspam = $message->{isspam};
  my $ishighspam = $message->{ishigh};
  my $sascore = $message->{sascore};
  my $spamreport = $message->{spamreport};

  # Get rid of control chars and tidy-up SpamAssassin report
  $spamreport =~ s/\n/ /g;
  $spamreport =~ s/\t//g;

  # Get timestamp, and format it so it is suitable to use with MySQL
  my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
  my($timestamp) = sprintf("%d-%02d-%02d %02d:%02d:%02d",
                           $year+1900,$mon+1,$mday,$hour,$min,$sec);

# maillog_mail insert
    my @fields=($timestamp, $id, $size, $from_user, $from_domain,
                $subject, $clientip, $archives, $isspam, $ishighspam,
                $sascore, $spamreport);
    map { s/\'/\\'/g } @fields;

    # Insert @fields into a database table
    my($sth) = $dbh->prepare("INSERT INTO maillog_mail (time, msg_id, size,
from_user, from_domain, subject, clientip, archives, isspam, ishighspam,
sascore, spamreport) 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]) or
MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);


  my($file, $text);
  while(($file, $text) = each %{$message->{allreports}}) {
    $file = "the entire message" if $file eq "";
    # Use the sanitised filename to avoid problems caused by people forcing
    # logging of attachment filenames which contain nasty SQL instructions.
    $file = $message->{file2safefile}{$file} or $file;
    $text =~ s/\n/ /;  # Make sure text report only contains 1 line
    $text =~ s/\t/ /; # and no tab characters

    my @fields = ($id, $file, $text);
    map { s/\'/\\'/g } @fields;

    my($sth) = $dbh->prepare("INSERT INTO maillog_report (msg_id, filename,
filereport) VALUES (?,?,?)");
    $sth->execute($fields[0],$fields[1],$fields[2]) or
MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);

  }

  for (@to) {
    # again, split the recipient's email into user and domain halves first.
    # see comment above about splitting the email like this.

    my ($to_user, $to_domain);
    ($to_user, $to_domain) = split /\@/, $_;
    my @fields = ($id, $to_user, $to_domain);
    map { s/\'/\\'/g } @fields;
    my($sth) = $dbh->prepare("INSERT INTO maillog_recipient (msg_id,
to_user, to_domain) VALUES (?,?,?)");
    $sth->execute($fields[0],$fields[1],$fields[2]) or
MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);
  }

  # Close database connection
  $dbh->disconnect();

}

  MailScanner::Log::InfoLog("Ending SQL Real-Time Logging");
}

1;



-rob