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