1: #!/usr/bin/perl -w
   2: 
   3: #############################################################
   4: # Usage... | mailtosql <dbname> <dbusername> <dbpass>       #
   5: # Pipe mail to this script and it will stick it in a mysql  #
   6: # table called Email. This is usefull for writing web       #
   7: # interfaces to email :) or other stuff.                    #
   8: #############################################################
   9: 
  10: use DBI;
  11: use Mail::Internet;
  12: use strict;
  13: use vars qw/$line $header @header @dats @fields $dbh $mail @arr $sth $statement $body $col @cols @body/;
  14: 
  15: $dbh = DBI->connect("DBI:mysql:$ARGV[0]",$ARGV[1],$ARGV[2],{
  16:         'RaiseError' => 0,
  17:         'PrintError' => 1
  18: });
  19: 
  20: $mail = new Mail::Internet \*STDIN;
  21: $header = $mail->head();
  22: 
  23: if (!($dbh->do("desc Email"))) {
  24:         $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\);";
  25:         $dbh->do($statement) || die "Could not create table \"Email\" on DB \"$ARGV[0]\"\n";
  26: }
  27: 
  28: die "Bad mail file\n" unless(defined($mail));
  29: 
  30: 
  31: # get fields 
  32: $sth = $dbh->prepare("desc Email");
  33: $sth->execute;
  34: while(@arr = $sth->fetchrow_array) {
  35:         push(@cols,$arr[0]);
  36: }
  37: 
  38: map {
  39:         chomp($line = $header->get($_));
  40:         $line = $dbh->quote($line);
  41:         $_ = "E".$_;
  42:         foreach $col(@cols) {
  43:                 if ($_ eq $col) {
  44:                         push(@fields,$_);
  45:                         push(@dats,$line);
  46:                 }
  47:         }
  48: } $header->tags;
  49: 
  50: map {
  51:         push(@header,$dbh->quote("$_: ".$header->get($_)));
  52: } $header->tags;
  53: push(@fields,"EHeader");
  54: push(@dats,"@header");
  55: 
  56: push(@fields,"EBody");
  57: @body = map {$dbh->quote($_)} @{$mail->body};
  58: push(@dats,"@body");
  59: 
  60: $statement = "INSERT INTO Email (".join(',',@fields).") VALUES(".join(',',@dats).");";
  61: $sth = $dbh->prepare($statement);
  62: $sth->execute;