[Mimedefang] defang DB

[Du Plooy, Ernst]

David, it seems that Pg is slightly faster than DBD::Pg for mass record
insertions.  Check
http://www.faqchest.com/prgm/dbi-l/dbi-01/dbi-0111/dbi-011106/dbi0111131
8_27417.html.

Ray, included is a simplified un-tested version for email header logging
into MySQL/PostgreSQL.  Please note the following:
1)	You might want to change the error handling since email will be
tempfailed (451 4.7.1 Please try again later) if the database
connection/insert fails.
2)	Consider not to connect/disconnect for every email message
received, but only once every time mimedefang-multiplexor kills the perl
process.
3)	Some characters are illegal in a database e.g. "\".  Before
inserting variables do some string parsing.
4)	This example only logs minimal header info.  Other variables
like message type etc. are also available inside the filter.  If you
need more header info you could always parse the ./HEADER file.
5)	The same code can be used in PostgreSQL. You only need to change
the $sql_connect_string.


use DBI;
$sql_connect_string="DBI:mysql:databasename:localhost:3306";
$sql_username = "user";
$sql_password = "password";
....
....
sub filter_end {
  my($entity) = @_;
  my($recips, $now, $size);
  $now = time_str;
  $size = (stat("./INPUTMSG"))[7];
  $recips = join(", ", @Recipients);
  $sql="INSERT INTO tablename (rdate,sender,recipient,subject,
size,msgID,relayID,relayaddr) VALUES (\"$now\",\"$Sender\",\"$recips\",
\"$Subject\",\"$size\",\"$MessageID\",\"$MsgID\",\"$RelayAddr\")";	
  connect_to_database();
  process_sql();
  disconnect_from_database();
}

# connect to database
	sub connect_to_database {
	        $dbh =
DBI->connect($sql_connect_string,$sql_username,$sql_password) or die
"Connecting... : $DBI::errstr\n ";
	}

# disconnect from database
	sub disconnect_from_database 
        {
	  $sth->finish;      
          $dbh->disconnect();
	}

# Execute SQL command	
           sub process_sql {
		$sth = $dbh->prepare("$sql") or die "preparing:
",$dbh->errstr;
	        $sth->execute or die "executing: ", $dbh->errstr;
	}


-----Original Message-----
From: Ray Parish [mailto:rparish at digitalwebzone.com] 
Sent: 01 September 2003 14:58PM
To: mimedefang at lists.roaringpenguin.com
Subject: Re: [Mimedefang] defang DB

Would you like to share your mimedefang-filter where you are extracting
the
header information and storing into mysql?
Ray


-----------------------------------------------------------------
This e-mail is subjected to the disclaimer that can be viewed at:
* http://www.tfs.ac.za/www/disclaimer/email_disclaimer
-----------------------------------------------------------------