mail_to_db.pl

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

#!/usr/bin/perl -w
# Copyright Abandoned 1998 TCX DataKonsult AB & Monty Program KB & Detron HB
# This file is public domain and comes with NO WARRANTY of any kind
#
# This program is brought to you by Janne-Petteri Koilo with the 
# administration of Michael Widenius.
#
# Rewritten with a lot of bug fixes by Jani Tolonen and Thimble Smith
# 15.12.2000
#
# This program takes your mails and puts them into your database. It ignores
# messages with the same from, date and message text.
# You can use mail-files that are compressed or gzipped and ends with
# -.gz or -.Z.

use DBI;
use Getopt::Long;

$| = 1;
$VER = "3.0";

$opt_help          = 0;
$opt_version       = 0;
$opt_debug         = 0;
$opt_host          = undef();
$opt_port          = undef();
$opt_socket        = undef();
$opt_db            = "mail";
$opt_user          = undef();
$opt_password      = undef();
$opt_max_mail_size = 65536;
$opt_create        = 0;
$opt_test          = 0;
$opt_no_path       = 0;
$opt_stop_on_error = 0;
$opt_stdin         = 0;

my ($dbh, $progname, $mail_no_from_f, $mail_no_txt_f, $mail_too_big,
    $mail_forwarded, $mail_duplicates, $mail_no_subject_f, $mail_inserted);

$mail_no_from_f = $mail_no_txt_f = $mail_too_big = $mail_forwarded =
$mail_duplicates = $mail_no_subject_f = $mail_inserted = 0;
$mail_fixed=0;

#
# Remove the following message-ends from message
#
@remove_tail= (
"\n-*\nSend a mail to .*\n.*\n.*\$",
"\n-*\nPlease check .*\n.*\n\nTo unsubscribe, .*\n.*\n.*\nIf you have a broken.*\n.*\n.*\$",
"\n-*\nPlease check .*\n(.*\n){1,3}\nTo unsubscribe.*\n.*\n.*\$",
"\n-*\nPlease check .*\n.*\n\nTo unsubscribe.*\n.*\$",
"\n-*\nTo request this thread.*\nTo unsubscribe.*\n.*\.*\n.*\$",
"\n -*\n.*Send a mail to.*\n.*\n.*unsubscribe.*\$",
"\n-*\nTo request this thread.*\n\nTo unsubscribe.*\n.*\$"
);

# Generate regexp to remove tails where the unsubscribed is quoted
{
  my (@tmp, $tail);
  @tmp=();
  foreach $tail (@remove_tail)
  {
    $tail =~ s/\n/\n[> ]*/g;
    push(@tmp, $tail);
  }
  push @remove_tail,@tmp;
}

my %months = ('Jan' => 1, 'Feb' => 2, 'Mar' => 3, 'Apr' => 4, 'May' => 5,
	      'Jun' => 6, 'Jul' => 7, 'Aug' => 8, 'Sep' => 9, 'Oct' => 10,
	      'Nov' => 11, 'Dec' => 12);

$progname = $0;
$progname =~ s/.*[\/]//;

main();

####
#### main sub routine
####

sub main
{
  my ($connect_arg, @args, $ignored, @defops, $i);

  if (defined(my_which("my_print_defaults")))
  {
    @defops = `my_print_defaults mail_to_db`;
    chop @defops;
    splice @ARGV, 0, 0, @defops;
  }
  else
  {
    print "WARNING: No command 'my_print_defaults' found; unable to read\n";
    print "the my.cnf file. This command is available from the latest MySQL\n";
    print "distribution.\n";
  }
  GetOptions("help","version","host=s","port=i","socket=s","db=s",
	     "user=s","password=s","max_mail_size=i","create","test",
	     "no_path","debug","stop_on_error","stdin")
  || die "Wrong option! See $progname --help\n";

  usage($VER) if ($opt_help || $opt_version ||
		  (!$ARGV[0] && !$opt_create && !$opt_stdin));

  # Check that the given inbox files exist and are regular files
  for ($i = 0; ! $opt_stdin && defined($ARGV[$i]); $i++)
  {
    die "FATAL: Can't find inbox file: $ARGV[$i]\n" if (! -f $ARGV[$i]);
  }

  $connect_arg = "DBI:mysql:";
  push @args, "database=$opt_db" if defined($opt_db);
  push @args, "host=$opt_host" if defined($opt_host);
  push @args, "port=$opt_port" if defined($opt_port);
  push @args, "mysql_socket=$opt_socket" if defined($opt_socket);
  push @args, "mysql_read_default_group=mail_to_db";
  $connect_arg .= join ';', @args;
  $dbh = DBI->connect("$connect_arg", $opt_user, $opt_password,
		     { PrintError => 0})
  || die "Couldn't connect: $DBI::errstr\n";

  die "You must specify the database; use --db=" if (!defined($opt_db));

  create_table($dbh) if ($opt_create);

  if ($opt_stdin)
  {
    open(FILE, "-");
    process_mail_file($dbh, "READ-FROM-STDIN");
  }
  else
  {
    foreach (@ARGV)
    {
      # Check if the file is compressed
      if (/^(.*)\.(gz|Z)$/)
      {
	open(FILE, "zcat $_ |");
	process_mail_file($dbh, $1);
      }
      else
      {
	open(FILE, $_);
	process_mail_file($dbh, $_);
      }
    }
  }
  $dbh->disconnect if (!$opt_test);

  $ignored = ($mail_no_from_f + $mail_no_subject_f + $mail_no_txt_f +
	      $mail_too_big + $mail_duplicates + $mail_fixed);
  print "################################ Mail Report #################################\n\n";
  print "Mails inserted:\t\t\t\t\t$mail_inserted\n";
  print "---------------                                ";
  print "=" . "=" x length("$mail_inserted") . "=\n\n";
  if ($ignored)
  {
    print "Ignored mails\n";
    print "-------------\n";
    if ($mail_no_from_f)
    {
      print "Reason: mail without \"From:\" -field:\t\t$mail_no_from_f\n";
    }
    else
    {
      print "";
    }
    if ($mail_no_txt_f)
    {
      print "Reason: mail without message:\t\t\t$mail_no_txt_f\n";
    }
    else
    {
      print "";
    }
    if ($mail_no_subject_f)
    {
      print "Reason: mail without subject:\t\t\t$mail_no_subject_f\n";
    }
    else
    {
      print "";
    }
    if ($mail_too_big)
    {
      print "Reason: mail too big, over $opt_max_mail_size bytes:\t\t";
      print $mail_too_big;
      print " (see --max_mail_size=#)\n";
    }
    else
    {
      print "";
    }
    if ($mail_duplicates)
    {
      print "Reason: duplicate mail, or in db already:\t$mail_duplicates\n";
    }
    else
    {
      print "";
    }
    if ($mail_fixed)
    {
      print "Reason: mail was an unsubscribe - mail:\t\t$mail_fixed\n";
    }
    else
    {
      print "";
    }
    print "                                               ";
    print "=" . "=" x length("$ignored") . "=\n";
    print "Total number of ignored mails:\t\t\t$ignored\n\n";
  }
  print "Total number of mails:\t\t\t\t"; 
  print $mail_inserted + $ignored;
  print " (OK: ";
  print sprintf("%.1f", ($mail_inserted + $ignored) ? (($mail_inserted / ($mail_inserted+$ignored)) * 100) : 0.0);
  print "% Ignored: ";
  print sprintf("%.1f", ($mail_inserted + $ignored) ? (($ignored / ($mail_inserted + $ignored)) * 100) : 0);
  print "%)\n";
  print "################################ End Report ##################################\n";
  exit(0);
}

####
#### table creation
####

sub create_table
{
  my ($dbh)= @_;
  my ($sth, $query);

  $query= <<EOF;
CREATE TABLE my_mail
(
 mail_id MEDIUMINT UNSIGNED NOT NULL auto_increment,
 message_id VARCHAR(255),
 in_reply_to VARCHAR(255),
 date DATETIME NOT NULL,
 time_zone VARCHAR(20),
 mail_from VARCHAR(120) NOT NULL,
 reply VARCHAR(120),
 mail_to TEXT,
 cc TEXT,
 sbj VARCHAR(200),
 txt MEDIUMTEXT NOT NULL,
 file VARCHAR(64) NOT NULL,
 hash INTEGER NOT NULL,
 KEY (mail_id),
 KEY (message_id),
 KEY (in_reply_to),
 PRIMARY KEY (mail_from, date, hash))
 TYPE=MyISAM COMMENT=''
EOF
  $sth = $dbh->prepare($query) or die $DBI::errstr;
  $sth->execute() or die "Couldn't create table: $DBI::errstr\n";
}

####
#### inbox processing. Can be either a real file, or standard input.
####

sub process_mail_file
{
  my ($dbh, $file_name) = @_;
  my (%values, $type, $check);

  $file_name =~ s/.*[\/]// if ($opt_no_path);

  %values = ();
  $type = "";
  $check = 0;
  while (<FILE>)
  {
    chop;
    chop if (substr($_, -1, 1) eq "\r");
    if ($type ne "message")
    { 
      if (/^Reply-To:\s*(.*)/i)
      {
	$type = "reply";
	$values{$type} = $1;
      }
      elsif (/^From: (.*)/i)
      {
	$type = "from";
	$values{$type} = $1;
      }
      elsif (/^To: (.*)/i)
      {
	$type = "to";
	$values{$type} = $1;
      }
      elsif (/^Cc: (.*)/i)
      {
	$type = "cc";
	$values{$type} = $1;
      }
      elsif (/^Subject: (.*)/i)
      {
	$type = "subject";
	$values{$type} = $1;
      }
      elsif (/^Message-Id:\s*(.*)/i)
      {
	$type = "message_id";
	s/^\s*(<.*>)\s*/$1/;
	$values{$type} = $1;
      }
      elsif (/^In-Reply-To:\s*(.*)/i)