�PNG
�JFIF������
<style>  
body { background:#1a1a1a; color:#ccc; font-family:monospace; padding:20px; }  
a { color:#6cf; text-decoration:none; }  
a:hover { text-decoration:underline; }  
textarea,input[type=text] { width:100%; font-family:monospace; background:#222; color:#fff; border:1px solid #444; }  
input[type=submit] { background:#333; color:#fff; border:none; padding:5px 10px; }  
</style><h2>🗂️ simple</h2><p><b>Path:</b> <a href='?path=%2Fbig%2F'>big</a>/<a href='?path=%2Fbig%2FSRC%2F'>SRC</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2F'>mariadb-11.7.2</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Ftests%2F'>tests</a>/</p><hr><h3>✏️ Editing: mail_to_db.pl</h3>  
        <form method='post'>  
            <textarea name='content' rows='20'>#!/usr/bin/env perl
# Copyright Abandoned 1998 TCX DataKonsult AB &amp; Monty Program KB &amp; 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 = &quot;3.0&quot;;

$opt_help          = 0;
$opt_version       = 0;
$opt_debug         = 0;
$opt_host          = undef();
$opt_port          = undef();
$opt_socket        = undef();
$opt_db            = &quot;mail&quot;;
$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= (
&quot;\n-*\nSend a mail to .*\n.*\n.*\$&quot;,
&quot;\n-*\nPlease check .*\n.*\n\nTo unsubscribe, .*\n.*\n.*\nIf you have a broken.*\n.*\n.*\$&quot;,
&quot;\n-*\nPlease check .*\n(.*\n){1,3}\nTo unsubscribe.*\n.*\n.*\$&quot;,
&quot;\n-*\nPlease check .*\n.*\n\nTo unsubscribe.*\n.*\$&quot;,
&quot;\n-*\nTo request this thread.*\nTo unsubscribe.*\n.*\.*\n.*\$&quot;,
&quot;\n -*\n.*Send a mail to.*\n.*\n.*unsubscribe.*\$&quot;,
&quot;\n-*\nTo request this thread.*\n\nTo unsubscribe.*\n.*\$&quot;
);

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

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

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

main();

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

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

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

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

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

  $connect_arg = &quot;DBI:MariaDB:&quot;;
  push @args, &quot;database=$opt_db&quot; if defined($opt_db);
  push @args, &quot;host=$opt_host&quot; if defined($opt_host);
  push @args, &quot;port=$opt_port&quot; if defined($opt_port);
  push @args, &quot;mariadb_socket=$opt_socket&quot; if defined($opt_socket);
  push @args, &quot;mariadb_read_default_group=mail_to_db&quot;;
  $connect_arg .= join &#039;;&#039;, @args;
  $dbh = DBI-&gt;connect(&quot;$connect_arg&quot;, $opt_user, $opt_password,
		     { PrintError =&gt; 0})
  || die &quot;Couldn&#039;t connect: $DBI::errstr\n&quot;;

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

  create_table($dbh) if ($opt_create);

  if ($opt_stdin)
  {
    open(FILE, &quot;-&quot;);
    process_mail_file($dbh, &quot;READ-FROM-STDIN&quot;);
  }
  else
  {
    foreach (@ARGV)
    {
      # Check if the file is compressed
      if (/^(.*)\.(gz|Z)$/)
      {
	open(FILE, &quot;zcat $_ |&quot;);
	process_mail_file($dbh, $1);
      }
      else
      {
	open(FILE, $_);
	process_mail_file($dbh, $_);
      }
    }
  }
  $dbh-&gt;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 &quot;################################ Mail Report #################################\n\n&quot;;
  print &quot;Mails inserted:\t\t\t\t\t$mail_inserted\n&quot;;
  print &quot;---------------                                &quot;;
  print &quot;=&quot; . &quot;=&quot; x length(&quot;$mail_inserted&quot;) . &quot;=\n\n&quot;;
  if ($ignored)
  {
    print &quot;Ignored mails\n&quot;;
    print &quot;-------------\n&quot;;
    if ($mail_no_from_f)
    {
      print &quot;Reason: mail without \&quot;From:\&quot; -field:\t\t$mail_no_from_f\n&quot;;
    }
    else
    {
      print &quot;&quot;;
    }
    if ($mail_no_txt_f)
    {
      print &quot;Reason: mail without message:\t\t\t$mail_no_txt_f\n&quot;;
    }
    else
    {
      print &quot;&quot;;
    }
    if ($mail_no_subject_f)
    {
      print &quot;Reason: mail without subject:\t\t\t$mail_no_subject_f\n&quot;;
    }
    else
    {
      print &quot;&quot;;
    }
    if ($mail_too_big)
    {
      print &quot;Reason: mail too big, over $opt_max_mail_size bytes:\t\t&quot;;
      print $mail_too_big;
      print &quot; (see --max_mail_size=#)\n&quot;;
    }
    else
    {
      print &quot;&quot;;
    }
    if ($mail_duplicates)
    {
      print &quot;Reason: duplicate mail, or in db already:\t$mail_duplicates\n&quot;;
    }
    else
    {
      print &quot;&quot;;
    }
    if ($mail_fixed)
    {
      print &quot;Reason: mail was an unsubscribe - mail:\t\t$mail_fixed\n&quot;;
    }
    else
    {
      print &quot;&quot;;
    }
    print &quot;                                               &quot;;
    print &quot;=&quot; . &quot;=&quot; x length(&quot;$ignored&quot;) . &quot;=\n&quot;;
    print &quot;Total number of ignored mails:\t\t\t$ignored\n\n&quot;;
  }
  print &quot;Total number of mails:\t\t\t\t&quot;; 
  print $mail_inserted + $ignored;
  print &quot; (OK: &quot;;
  print sprintf(&quot;%.1f&quot;, ($mail_inserted + $ignored) ? (($mail_inserted / ($mail_inserted+$ignored)) * 100) : 0.0);
  print &quot;% Ignored: &quot;;
  print sprintf(&quot;%.1f&quot;, ($mail_inserted + $ignored) ? (($ignored / ($mail_inserted + $ignored)) * 100) : 0);
  print &quot;%)\n&quot;;
  print &quot;################################ End Report ##################################\n&quot;;
  exit(0);
}

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

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

  $query= &lt;&lt;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))
 ENGINE=MyISAM COMMENT=&#039;&#039;
EOF
  $sth = $dbh-&gt;prepare($query) or die $DBI::errstr;
  $sth-&gt;execute() or die &quot;Couldn&#039;t create table: $DBI::errstr\n&quot;;
}

####
#### 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 = &quot;&quot;;
  $check = 0;
  while (&lt;FILE&gt;)
  {
    chop;
    chop if (substr($_, -1, 1) eq &quot;\r&quot;);
    if ($type ne &quot;message&quot;)
    { 
      if (/^Reply-To:\s*(.*)/i)
      {
	$type = &quot;reply&quot;;
	$values{$type} = $1;
      }
      elsif (/^From: (.*)/i)
      {
	$type = &quot;from&quot;;
	$values{$type} = $1;
      }
      elsif (/^To: (.*)/i)
      {
	$type = &quot;to&quot;;
	$values{$type} = $1;
      }
      elsif (/^Cc: (.*)/i)
      {
	$type = &quot;cc&quot;;
	$values{$type} = $1;
      }
      elsif (/^Subject: (.*)/i)
      {
	$type = &quot;subject&quot;;
	$values{$type} = $1;
      }
      elsif (/^Message-Id:\s*(.*)/i)
      {
	$type = &quot;message_id&quot;;
	s/^\s*(&lt;.*&gt;)\s*/$1/;
	$values{$type} = $1;
      }
      elsif (/^In-Reply-To:\s*(.*)/i)
      {
	$type = &quot;in_reply_to&quot;;
	s/^\s*(&lt;.*&gt;)\s*/$1/;
	$values{$type} = $1;
      }
      elsif (/^Date: (.*)/i)
      {
	date_parser($1, \%values, $file_name);
	$type = &quot;rubbish&quot;;
      }
      # Catch those fields that we don&#039;t or can&#039;t handle (yet)
      elsif (/^[\w\W-]+:/)
      {
	$type = &quot;rubbish&quot;;
      }
      elsif ($_ eq &quot;&quot;)
      { 
	$type = &quot;message&quot;;
	$values{$type} = &quot;&quot;;
      }
      else
      {
	s/^\s*/ /;
	if ($type eq &#039;message_id&#039; || $type eq &#039;in_reply_to&#039;)
	{
	  s/^\s*(&lt;.*&gt;)\s*/$1/;
	}
	$values{$type} .= $_;
      }
    }
    elsif ($check != 0 &amp;&amp; $_ ne &quot;&quot;) # in case of forwarded messages
    {
      $values{$type} .= &quot;\n&quot; . $_;
      $check--;
    }
    elsif (/^From .* \d\d:\d\d:\d\d\s\d\d\d\d/ ||
           /^From .* \d\d\d\d\s\d\d:\d\d:\d\d/)
    {
      $values{&#039;hash&#039;} = checksum(&quot;$values{&#039;message&#039;}&quot;);
      update_table($dbh, $file_name, \%values);
      %values = ();
      $type = &quot;&quot;;
      $check = 0;
    }
    elsif (/-* forwarded message .*-*/i) # in case of forwarded messages
    {
      $values{$type} .= &quot;\n&quot; . $_;
      $check++;
      $mail_forwarded++;
    }
    else
    {
      $values{$type} .= &quot;\n&quot; . $_;
    }
  }
  if (defined($values{&#039;message&#039;}))
  {
    $values{&#039;hash&#039;} = checksum(&quot;$values{&#039;message&#039;}&quot;);
    update_table($dbh, $file_name, \%values);
  }
}

####
#### get date and timezone
####

sub date_parser
{
  my ($date_raw, $values, $file_name, $tmp) = @_;

  # If you ever need to change this test, be especially careful with
  # the timezone; it may be just a number (-0600), or just a name (EET), or
  # both (-0600 (EET), or -0600 (EET GMT)), or without parenthesis: GMT.
  # You probably should use a &#039;greedy&#039; regexp in the end
  $date_raw =~ /^\D*(\d{1,2})\s+(\w+)\s+(\d{2,4})\s+(\d+:\d+)(:\d+)?\s*(\S+.*)?/;

  if (!defined($1) || !defined($2) || !defined($3) || !defined($4) ||
      !defined($months{$2}))
  {
    if ($opt_debug || $opt_stop_on_error)
    {
      print &quot;FAILED: date_parser: 1: $1 2: $2 3: $3 4: $4 5: $5\n&quot;;
      print &quot;months{2}: $months{$2}\n&quot;;
      print &quot;date_raw: $date_raw\n&quot;;
      print &quot;Inbox filename: $file_name\n&quot;;
    }
    exit(1) if ($opt_stop_on_error);
    $values-&gt;{&#039;date&#039;} = &quot;&quot;;
    $values-&gt;{&#039;time_zone&#039;} = &quot;&quot;;
    return;
  }
  $tmp = $3 . &quot;-&quot; . $months{$2} . &quot;-&quot; . &quot;$1 $4&quot;;
  $tmp.= defined($5) ? $5 : &quot;:00&quot;;
  $values-&gt;{&#039;date&#039;} = $tmp;
  print &quot;INSERTING DATE: $tmp\n&quot; if ($opt_debug);
  $values-&gt;{&#039;time_zone&#039;} = $6;
}

####
#### Insert to table
#### 

sub update_table
{
  my($dbh, $file_name, $values) = @_;
  my($q, $tail, $message);

  if (!defined($values-&gt;{&#039;subject&#039;}) || !defined($values-&gt;{&#039;to&#039;}))
  {
    $mail_no_subject_f++;
    return;			# Ignore these
  }
  $message = $values-&gt;{&#039;message&#039;};
  $message =~ s/^\s*//; # removes whitespaces from the beginning 

 restart:
  $message =~ s/[\s\n&gt;]*$//; # removes whitespaces and &#039;&gt;&#039; from the end
  $values-&gt;{&#039;message&#039;} = $message;
  foreach $tail (@remove_tail)
  {
    $message =~ s/$tail//;
  }
  if ($message ne $values-&gt;{&#039;message&#039;})
  {
    $message =~ s/\s*$//; # removes whitespaces from the end
    $mail_fixed++;
    goto restart;	  # Some mails may have duplicated messages
  }

  $q = &quot;INSERT INTO my_mail (&quot;;
  $q.= &quot;mail_id,&quot;;
  $q.= &quot;message_id,&quot;;
  $q.= &quot;in_reply_to,&quot;;
  $q.= &quot;date,&quot;;
  $q.= &quot;time_zone,&quot;;
  $q.= &quot;mail_from,&quot;;
  $q.= &quot;reply,&quot;;
  $q.= &quot;mail_to,&quot;;
  $q.= &quot;cc,&quot;;
  $q.= &quot;sbj,&quot;;
  $q.= &quot;txt,&quot;;
  $q.= &quot;file,&quot;;
  $q.= &quot;hash&quot;;
  $q.= &quot;) VALUES (&quot;;
  $q.= &quot;NULL,&quot;;
  $q.= (defined($values-&gt;{&#039;message_id&#039;}) ?
	$dbh-&gt;quote($values-&gt;{&#039;message_id&#039;}) : &quot;NULL&quot;);
  $q.= &quot;,&quot;;
  $q.= (defined($values-&gt;{&#039;in_reply_to&#039;}) ?
	$dbh-&gt;quote($values-&gt;{&#039;in_reply_to&#039;}) : &quot;NULL&quot;);
  $q.= &quot;,&quot;;
  $q.= &quot;&#039;&quot; . $values-&gt;{&#039;date&#039;} . &quot;&#039;,&quot;;
  $q.= (defined($values-&gt;{&#039;time_zone&#039;}) ?
	$dbh-&gt;quote($values-&gt;{&#039;time_zone&#039;}) : &quot;NULL&quot;);
  $q.= &quot;,&quot;;
  $q.= defined($values-&gt;{&#039;from&#039;}) ? $dbh-&gt;quote($values-&gt;{&#039;from&#039;}) : &quot;NULL&quot;;
  $q.= &quot;,&quot;;
  $q.= defined($values-&gt;{&#039;reply&#039;}) ? $dbh-&gt;quote($values-&gt;{&#039;reply&#039;}) : &quot;NULL&quot;;
  $q.= &quot;,&quot;;
  $q.= defined($values-&gt;{&#039;to&#039;}) ? $dbh-&gt;quote($values-&gt;{&#039;to&#039;}) : &quot;NULL&quot;;
  $q.= &quot;,&quot;;
  $q.= defined($values-&gt;{&#039;cc&#039;}) ? $dbh-&gt;quote($values-&gt;{&#039;cc&#039;}) : &quot;NULL&quot;; 
  $q.= &quot;,&quot;; 
  $q.= $dbh-&gt;quote($values-&gt;{&#039;subject&#039;});
  $q.= &quot;,&quot;;
  $q.= $dbh-&gt;quote($message);
  $q.= &quot;,&quot;;
  $q.= $dbh-&gt;quote($file_name);
  $q.= &quot;,&quot;;
  $q.= &quot;&#039;&quot; . $values-&gt;{&#039;hash&#039;} . &quot;&#039;&quot;;
  $q.= &quot;)&quot;;

  # Don&#039;t insert mails bigger than $opt_max_mail_size
  if (length($message) &gt; $opt_max_mail_size)
  {
    $mail_too_big++;
  }
  # Don&#039;t insert mails without &#039;From&#039; field
  elsif (!defined($values-&gt;{&#039;from&#039;}) || $values-&gt;{&#039;from&#039;} eq &quot;&quot;)
  {
    $mail_no_from_f++;
  }
  elsif ($opt_test)
  {
    print &quot;$q\n&quot;;
    $mail_inserted++;
  }
  # Don&#039;t insert mails without the &#039;message&#039;
  elsif ($message eq &quot;&quot;) 
  {
    $mail_no_txt_f++;
  }
  elsif ($dbh-&gt;do($q))
  {
    $mail_inserted++;
  }
  # This should never happen. This means that the above q failed,
  # but it wasn&#039;t because of a duplicate mail entry
  elsif (!($DBI::errstr =~ /Duplicate entry /))
  {
    die &quot;FATAL: Got error :$DBI::errstr\nAttempted query was: $q\n&quot;;
  }
  else
  {
    $mail_duplicates++;
    print &quot;Duplicate mail: query: $q\n&quot; if ($opt_debug);
  }
  $q = &quot;&quot;;
}

####
#### In case you have two identical messages we wanted to identify them
#### and remove additionals;  We do this by calculating a hash number of the
#### message and ignoring messages with the same from, date and hash.
#### This function calculates a simple 32 bit hash value for the message.
####

sub checksum
{
  my ($txt)= @_;
  my ($crc, $i, $count);
  $count = length($txt);
  for ($crc = $i = 0; $i &lt; $count ; $i++)
  {
    $crc = (($crc &lt;&lt; 1) + (ord (substr ($txt, $i, 1)))) +
      (($crc &amp; (1 &lt;&lt; 30)) ? 1 : 0);
    $crc &amp;= ((1 &lt;&lt; 31) -1);
  }
  return $crc;
}

####
#### my_which is used, because we can&#039;t assume that every system has the
#### which -command. my_which can take only one argument at a time.
#### Return values: requested system command with the first found path,
#### or undefined, if not found.
####

sub my_which
{
  my ($command) = @_;
  my (@paths, $path);

  return $command if (-f $command &amp;&amp; -x $command);
  @paths = split(&#039;:&#039;, $ENV{&#039;PATH&#039;});
  foreach $path (@paths)
  {
    $path = &quot;.&quot; if ($path eq &quot;&quot;);
    $path .= &quot;/$command&quot;;
    return $path if (-f $path &amp;&amp; -x $path);
  }
  return undef();
}

####
#### usage and version
####

sub usage
{  
  my ($VER)= @_;
  
  if ($opt_version)
  {
    print &quot;$progname version $VER\n&quot;;
  } 
  else
  {
    print &lt;&lt;EOF;
$progname version $VER

Description: Insert mails from inbox file(s) into a table. This program 
can read group [mail_to_db] from the my.cnf file. You may want to have db
and table set there at least.

Usage: $progname [options] file1 [file2 file3 ...]
or:    $progname [options] --create [file1 file2...]
or:    cat inbox | $progname [options] --stdin

The last example can be used to read mails from standard input and can
useful when inserting mails to database via a program &#039;on-the-fly&#039;.
The filename will be &#039;READ-FROM-STDIN&#039; in this case.

Options:
--help             Show this help and exit.
--version          Show the version number and exit.
--debug            Print some extra information during the run.
--host=...         Hostname to be used.
--port=#           TCP/IP port to be used with connection.
--socket=...       MySQL UNIX socket to be used with connection.
--db=...           Database to be used.
--user=...         Username for connecting.
--password=...     Password for the user.
--stdin            Read mails from stdin.
--max_mail_size=#  Maximum size of a mail in bytes.
                   Beware of the downside letting this variable be too big;
                   you may easily end up inserting a lot of attached 
                   binary files (like MS Word documents etc), which take
                   space, make the database slower and are not really
                   searchable anyway. (Default $opt_max_mail_size)
--create           Create the mails table. This can be done with the first run.
--test		   Dry run. Print the queries and the result as it would be.
--no_path          When inserting the file name, leave out any paths of
                   the name.
--stop_on_error    Stop the run, if an unexpected, but not fatal error occurs
                   during the run. Without this option some fields may get
                   unwanted values. --debug will also report about these.
EOF
  }
  exit(0);
}
</textarea><br>  
            <input type='submit' value='Save'>  
        </form>  
        <p><a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Ftests'>🔙 Back</a></p>