Forum OpenACS Q&A: email handler & postgres DBD

Hi George, Did you ever get the email handler to insert new emails into postgres. Did you write your own script? If so, I'd love to see it. I'd soon like to get our bboard system to accept e-mail replies directly instead of the current "to post a response, come back to the bulletin board at blabla.com/other?/stuffffff" I know that this will take encoding of the subject or something so that replies will get to the same thread.

Thank you. -Bob

#!/usr/bin/perl -I /usr/lib/perl5/site_perl/5.005/i386-linux/DBD
#
# Respond to incoming mail message on STDIN
#
# hqm@ai.mit.edu
#
# modified for posgres by
#   Steven Caranci 
#   caranci@eecg.toronto.edu
#
# This script does the following:
#
sub usage () {
    print '
  usage: queue_message.pl db_datasrc db_user db_passwd destaddr

  Inserts the data from stdin into a queue table.

  Assumes the following table and sequence are defined in the db:

    create table incoming_email_queue (
	    id 		int4 not null,
	    destaddr	varchar(256),
	    content		text,		-- the entire raw message content
					    -- including all headers
	    arrival_time	datetime
    );

    create sequence incoming_email_queue_sequence;

';
}

use strict;
use DBI;

################################################################
# Global Definitions

# max string size we allow in db. Truncate any message content beyond this
#$MAX_MSGSIZE = 400000;
# is limit of text type 1 block? 
# assume so for now. also assume 8k block.
my $MAX_MSGSIZE = 8000;

my $db_datasrc        = shift;
my $db_user           = shift;
my $db_passwd         = shift;
my $destaddr          = shift;
my $datasource_full   = "dbi:Pg:dbname=$db_datasrc";
my ($conn, $content, $sql, $ntuples, $result);

# we don't support hosts/ports other than the default (localhost/5432).
# we probably should.

my $DEBUG = 1;
my $debug_logfile = "/tmp/mailhandler-log.txt";

if (!defined $db_datasrc) {
    $db_datasrc = 'dbi:Pg';
}

if (!defined $db_user) {
    usage();
    die("You must pass a db user in the command line");
}

if (!defined $db_passwd) {
    usage();
    die("You must pass a db passwd in the command line");
}

#################################################################
## Snarf down incoming msg on STDIN
#################################################################

while (<>) {
    $content .= $_; 
}

# double the single quotes
#$content =~ s/'/''/g;
$content = qq( $content );

# limit content length
$content = substr($content,0,$MAX_MSGSIZE);


if ($DEBUG) {
    open (LOG, ">>$debug_logfile");
    debug("================================================================
");
    debug("Received content:
$content
");
}


# Open the database connection.
$conn = DBI->connect( $datasource_full, 
                      $db_user, 
                      $db_passwd,
                      { AutoCommit => 1 }
                    ) || die "errorMessage from Pg::connect: $DBI::errstr";

# Insert mail into db
debug("Status: inserting into email queue
");
$sql = "
INSERT INTO incoming_email_queue 
  (id, destaddr,  content, arrival_time) 
VALUES 
  (nextval('incoming_email_queue_sequence'), '$destaddr', '$content', 'now')";

my $sth = $conn->prepare( $sql );
$sth->execute();

my $rowcount = $sth->rows;
if ( $rowcount != 1 ) {
    debug( "
Error! inserted $rowcount rows instead of 1)
" );
}

debug("[closing log]
");
if ($DEBUG) { close LOG; }

sub debug () {
    my ($msg) = @_;
    print LOG $msg;
}

$conn->disconnect();