Buffer Server for Website Generated Email

Complete Perl source code below.

Programming Challenge: 3 / 5

Technologies Involved: Perl, Web Forms, Automated Email, SQL Database

Background: Websites commonly have a page with a form that the user fills out.  When the user submits this form, a server-side script sends an email.  Sometimes, the script sends the email to the site owner notifying him of the details filled out in the web form.  Sometimes it sends an email back to the user, confirming the receipt of the web form.

Challenge: The legacy ASP software I was using (Chili!Soft ASP) could not send outbound mail when I upgraded OS to Red Hat 9.  This compatibility problem could not be resolved on the IT level, so the ASP pages needed to run without outbound mail sending.

Solution:
First, I created a brand new database called MailQueue.  It contains a single table with all the mail fields (to, from, subject, body, and a sent flag).  When an ASP page is commissioned to send an email, it will simply add the email as a row to the database.  This is a change from how it worked before, where ASP was sending the email natively to the local Unix email service.

The second part is the Perl engine that runs every minute of the day.  It simply opens a database and looks for all rows WHERE sent=false.  If it finds nothing, it will exit in a millisecond (which is why there is no load concern even though it runs every single minute of every day).  If it does see email in that table, then it will send it using external native sendmail commands.

So in summary, I elliminated the ASP mail calls altogether.  And instead the ASP simply adds rows to a database when it wants to send a the mailbot I wrote in Perl.

Fortunately, there is a little bit of a dual-purpose here.  The system not only works as it did before, but in fact it works a little better.  Since the mail server command isn't being invoked directly by the web page, it elliminates the risk of burdining the user with an ASP error if there is a mail server probelm.  Since there is a queue, if a problem occurs, I can fix it while the active websites are still sending mail to my database queue.  Then when the problem is fixed, I can simply run it on the accumulated queue.  Nobody will ever know that there was a problem.

Another advantage is scalability.  The mailbot processes mail synchronously so the system will not get bogged down if 50 people decide to sumbit an emaliing web form at the same time.

Perl Source Code for pmail.pl:

#!/usr/bin/perl

use DBI;

my $db = DBI->connect('DBI:mysql:mailqueue', 'username', 'password');
my $query='SELECT * FROM spool WHERE Sent=0';
my $rst=$db->prepare($query);

$rst->execute;

$rst->bind_columns(undef, \$id, \$from, \$to, \$subject, \$body, \$sent);

while(my(@data)=$rst->fetchrow_array)
{
  print "---\n";
  my $sendmail = "/usr/sbin/sendmail -t -f\\$from";
  my $mailheader = "From: $from\nTo: $to\nSubject: $subject\nContent-type: text\/plain";

  open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!\n";
  # open(SENDMAIL, ">myfile");
  print SENDMAIL $mailheader."\n\n";
  print SENDMAIL $body;
  close(SENDMAIL);

  my $updateh=$db->prepare('UPDATE spool SET Sent=1 WHERE MailID='.$id);
  $updateh->execute;

  print "Date: ".localtime()."\n";
  print $mailheader."\n\n";

}