Forum OpenACS Q&A: email handler & postgres DBD

Collapse
Posted by G Fairbanks on

I'm having problems getting the email handler to insert new emails into postgres.

I have installed the latest Perl DBD for Postgres v 0.95. The test program bunded with it runs correctly and can connect to the postgres db.

The first problem I had with using the queue-message.pl script was that it couldn't find Pg.pl. I edited the script so it includes -I /usr/lib/perl5/site_perl/5.005/i386-linux/DBD and then it was able to find it.

Now the queue-message.pl script has problems finding a procedure called "connectdb": Undefined subroutine &Pg::connectdb called at ./queue-message.pl line 96 I notice that the test program used "connect" and is also using DBI, not DBD.

Does anyone have this working? Am I missing something easy here? If not, the script doesn't look too complicated to write myself.

Thanks,

Collapse
Posted by MaineBob OConnor on

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

Collapse
Posted by Patrick Giagnocavo on
One way to handle encoding so that it ends up in the proper thread
would be to put a thread-id in the Reply-To: field.  Thus you might
have a Reply-To: that looked like "mailto:msg_id.5.topic.12@openacs.org"; or
something like that to refer to msg_id and topic that you were
replying to.

./././patrick

Collapse
Posted by G Fairbanks on
I did modify the script so that it works for me -- it wasn't difficult. I have the Perl DBI & DBD Postgres modules installed. I haven't really looked at it since I hacked it so there may be some cruft left. I hope this is helpful for you.

#!/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();