#!/usr/bin/perl -w

#############################################################
# Usage... | mailtosql <dbname> <dbusername> <dbpass>       #
# Pipe mail to this script and it will stick it in a mysql  #
# table called Email. This is usefull for writing web       #
# interfaces to email :) or other stuff.                    #
#############################################################

use DBI;
use Mail::Internet;
use strict;
use vars qw/$line $header @header @dats @fields $dbh $mail @arr $sth $statement $body $col @cols @body/;

$dbh = DBI->connect("DBI:mysql:$ARGV[0]",$ARGV[1],$ARGV[2],{
        'RaiseError' => 0,
        'PrintError' => 1
});

$mail = new Mail::Internet \*STDIN;
$header = $mail->head();

if (!($dbh->do("desc Email"))) {
        $statement = "CREATE TABLE Email \(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,ETo varchar\(150\),EFrom varchar\(150\),ESender varchar\(150\),ECc varchar\(150\),EDate varchar\(150\),ESubject varchar\(150\),EHeader text,EBody text\);";
        $dbh->do($statement) || die "Could not create table \"Email\" on DB \"$ARGV[0]\"\n";
}

die "Bad mail file\n" unless(defined($mail));


# get fields 
$sth = $dbh->prepare("desc Email");
$sth->execute;
while(@arr = $sth->fetchrow_array) {
        push(@cols,$arr[0]);
}

map {
        chomp($line = $header->get($_));
        $line = $dbh->quote($line);
        $_ = "E".$_;
        foreach $col(@cols) {
                if ($_ eq $col) {
                        push(@fields,$_);
                        push(@dats,$line);
                }
        }
} $header->tags;

map {
        push(@header,$dbh->quote("$_: ".$header->get($_)));
} $header->tags;
push(@fields,"EHeader");
push(@dats,"@header");

push(@fields,"EBody");
@body = map {$dbh->quote($_)} @{$mail->body};
push(@dats,"@body");

$statement = "INSERT INTO Email (".join(',',@fields).") VALUES(".join(',',@dats).");";
$sth = $dbh->prepare($statement);
$sth->execute;