release/src/router/mysql/tests/pmail.pl

   1 #!/usr/bin/perl -w
   2
   3 # Copyright (C) 2000, 2005 MySQL AB
   4 #
   5 # This program is free software; you can redistribute it and/or modify
   6 # it under the terms of the GNU General Public License as published by
   7 # the Free Software Foundation; version 2 of the License.
   8 #
   9 # This program is distributed in the hope that it will be useful,
  10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 # GNU General Public License for more details.
  13 #
  14 # You should have received a copy of the GNU General Public License
  15 # along with this program; if not, write to the Free Software
  16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
  17
  18 #
  19 # Prints mails to standard output
  20 #
  21 ####
  22 #### Standard inits and get options
  23 ####
  24
  25 use DBI;
  26 use Getopt::Long;
  27
  28 $VER="2.0";
  29
  30 @fldnms= ("mail_from","mail_to","cc","date","time_zone","file","sbj","txt");
  31 my $fields= 0;
  32 my $base_q= "";
  33 my $mail_count= 0;
  34
  35 $opt_user= $opt_password= "";
  36 $opt_socket= "/tmp/mysql.sock";
  37 $opt_port= 3306;
  38 $opt_db="mail";
  39 $opt_table="my_mail";
  40 $opt_help=$opt_count=0;
  41 $opt_thread= 0;
  42 $opt_host= "";
  43 $opt_message_id= 0;
  44
  45 GetOptions("help","count","port=i","db=s","table=s","host=s","password=s",
  46            "user=s","socket=s", "thread","message_id") || usage();
  47
  48 if ($opt_host eq '')
  49 {
  50   $opt_host = "localhost";
  51 }
  52
  53 if ($opt_help || !$ARGV[0])
  54 {
  55   usage();
  56 }
  57
  58 ####
  59 #### Connect and parsing the query to MySQL
  60 ####
  61
  62 $dbh= DBI->connect("DBI:mysql:$opt_db:$opt_host:port=$opt_port:mysql_socket=$opt_socket", $opt_user,$opt_password, { PrintError => 0})
  63 || die $DBI::errstr;
  64
  65 main();
  66
  67 ####
  68 #### main
  69 ####
  70
  71 sub main
  72 {
  73   my ($row, $val, $q, $mail, $sth);
  74
  75   if ($opt_count)
  76   {
  77     count_mails();
  78   }
  79
  80   $base_q= "SELECT ";
  81   foreach $val (@fldnms)
  82   {
  83     if (!$fields)
  84     {
  85       $base_q.= "$val";
  86     }
  87     else
  88     {
  89       $base_q.= ",$val";
  90     }
  91     $fields++;
  92   }
  93   $base_q.= ",message_id" if ($opt_thread || $opt_message_id);
  94   $base_q.= " FROM $opt_table";
  95   $q= " WHERE $ARGV[0]";
  96
  97   $sth= $dbh->prepare($base_q . $q);
  98   if (!$sth->execute)
  99   {
 100     print "$DBI::errstr\n";
 101     $sth->finish;
 102     die;
 103   }
 104   for (; ($row= $sth->fetchrow_arrayref); $mail_count++)
 105   {
 106     for ($i= 0; $i < $fields; $i++)
 107     {
 108       if ($opt_message_id)
 109       {
 110         $mail[$fields][$mail_count]= $row->[$fields];
 111         $mail[$fields][$mail_count].= "\nNumber of Replies: " . get_nr_replies($row->[$fields]);
 112       }
 113       $mail[$i][$mail_count]= $row->[$i];
 114     }
 115     if ($opt_thread)
 116     {
 117       get_mail_by_message_id($row->[$fields], $mail);
 118     }
 119   }
 120   print_mails($mail);
 121 }
 122
 123 ####
 124 #### Function, which fetches mail by searching in-reply-to with
 125 #### a given message_id. Saves the value (mail) in mail variable.
 126 #### Returns the message id of the mail found and searches again
 127 #### and saves, until no more mails are found with that message_id.
 128 ####
 129
 130 sub get_mail_by_message_id
 131 {
 132   my ($message_id, $mail)= @_;
 133   my ($q, $query, $i, $row, $sth);
 134
 135   $q= " WHERE in_reply_to = \"$message_id\"";
 136   $query= $base_q . $q;
 137   $sth= $dbh->prepare($query);
 138   if (!$sth->execute)
 139   {
 140     print "QUERY: $query\n$DBI::errstr\n";
 141     $sth->finish;
 142     die;
 143   }
 144   while (($row= $sth->fetchrow_arrayref))
 145   {
 146     $mail_count++;
 147     for ($i= 0; $i < $fields; $i++)
 148     {
 149       if ($opt_message_id)
 150       {
 151         $mail[$fields][$mail_count]= $row->[$fields];
 152         $mail[$fields][$mail_count].= "\nNumber of Replies: " . get_nr_replies($row->[$fields]);
 153       }
 154       $mail[$i][$mail_count]= $row->[$i];
 155     }
 156     $new_message_id= $row->[$fields];
 157     if (defined($new_message_id) && length($new_message_id))
 158     {
 159       get_mail_by_message_id($new_message_id, $mail);
 160     }
 161   }
 162   return;
 163 }
 164
 165 ####
 166 #### Get number of replies for a given message_id
 167 ####
 168
 169 sub get_nr_replies
 170 {
 171   my ($message_id)= @_;
 172   my ($sth, $sth2, $q, $row, $row2, $nr_replies);
 173
 174   $nr_replies= 0;
 175   $q= "SELECT COUNT(*) FROM my_mail WHERE in_reply_to=\"$message_id\"";
 176   $sth= $dbh->prepare($q);
 177   if (!$sth->execute)
 178   {
 179     print "QUERY: $q\n$DBI::errstr\n";
 180     $sth->finish;
 181     die;
 182   }
 183   while (($row= $sth->fetchrow_arrayref))
 184   {
 185     if (($nr_replies= $row->[0]))
 186     {
 187       $q= "SELECT message_id FROM my_mail WHERE in_reply_to=\"$message_id\"";
 188       $sth2= $dbh->prepare($q);
 189       if (!$sth2->execute)
 190       {
 191         print "QUERY: $q\n$DBI::errstr\n";
 192         $sth->finish;
 193         die;
 194       }
 195       while (($row2= $sth2->fetchrow_arrayref))
 196       {
 197         # There may be several replies to the same mail. Also the
 198         # replies to the 'parent' mail may contain several replies
 199         # and so on. Thus we need to calculate it recursively.
 200         $nr_replies+= get_nr_replies($row2->[0]);
 201       }
 202     }
 203     return $nr_replies;
 204   }
 205 }
 206
 207 ####
 208 #### Print mails
 209 ####
 210
 211 sub print_mails
 212 {
 213   my ($mail)= @_;
 214   my ($i);
 215
 216   for ($i=0; $mail[0][$i]; $i++)
 217   {
 218     print "#" x 33;
 219     print " " . ($i+1) . ". Mail ";
 220     print "#" x 33;
 221     print "\n";
 222     if ($opt_message_id)
 223     {
 224       print "Msg ID: $mail[$fields][$i]\n";
 225     }
 226     print "From: $mail[0][$i]\n";
 227     print "To: $mail[1][$i]\n";
 228     print "Cc:" . (defined($mail[2][$i]) ? $mail[2][$i] : "") . "\n";
 229     print "Date: $mail[3][$i]\n";
 230     print "Timezone: $mail[4][$i]\n";
 231     print "File: $mail[5][$i]\n";
 232     print "Subject: $mail[6][$i]\n";
 233     print "Message:\n$mail[7][$i]\n";
 234   }
 235   print "#" x 20;
 236   print " Summary: ";
 237   if ($i == 1)
 238   {
 239     print "$i Mail ";
 240     print "matches the query ";
 241   }
 242   else
 243   {
 244     print "$i Mails ";
 245     print "match the query ";
 246   }
 247   print "#" x 20;
 248   print "\n";
 249 }
 250
 251 ####
 252 #### Count mails that matches the query, but don't show them
 253 ####
 254
 255 sub count_mails
 256 {
 257   my ($sth);
 258
 259   $sth= $dbh->prepare("select count(*) from $opt_table where $ARGV[0]");
 260   if (!$sth->execute)
 261   {
 262     print "$DBI::errstr\n";
 263     $sth->finish;
 264     die;
 265   }
 266   while (($row= $sth->fetchrow_arrayref))
 267   {
 268     $mail_count= $row->[0];
 269   }
 270   if ($mail_count == 1)
 271   {
 272     print "$mail_count Mail matches the query.\n";
 273   }
 274   else
 275   {
 276     print "$mail_count Mails match the query.\n";
 277   }
 278   exit;
 279 }
 280
 281 ####
 282 #### Usage
 283 ####
 284
 285 sub usage
 286 {
 287   print <<EOF;
 288   pmail version $VER by Jani Tolonen
 289
 290   Usage: pmail [options] "SQL where clause"
 291   Options:
 292   --help       show this help
 293   --count      Shows how many mails matches the query, but not the mails.
 294   --db=        database to use (Default: $opt_db)
 295   --host=      Hostname which to connect (Default: $opt_host)
 296   --socket=    Unix socket to be used for connection (Default: $opt_socket)
 297   --password=  Password to use for mysql
 298   --user=      User to be used for mysql connection, if not current user
 299   --port=      mysql port to be used (Default: $opt_port)
 300   --thread     Will search for possible replies to emails found by the search
 301                criteria. Replies, if found, will be displayed right after the
 302                original mail.
 303   --message_id Display message_id on top of each mail. Useful when searching
 304                email threads with --thread. On the second line is the number
 305                of replies to the same thread, starting counting from that
 306                mail (excluding possible parent mails).
 307   "SQL where clause" is the end of the select clause,
 308   where the condition is expressed. The result will
 309   be the mail(s) that matches the condition and
 310   will be displayed with the fields:
 311   - From
 312   - To
 313   - Cc
 314   - Date
 315   - Timezone
 316   - File (Where from the current mail was loaded into the database)
 317   - Subject
 318   - Message text
 319   The field names that can be used in the where clause are:
 320     Field       Type
 321   - message_id  varchar(255) # Use with --thread and --message_id
 322   - in_reply_to varchar(255) # Internally used by --thread
 323   - mail_from   varchar(120)
 324   - date        datetime
 325   - sbj         varchar(200)
 326   - txt         mediumtext
 327   - cc          text
 328   - mail_to     text
 329   - time_zone   varchar(6)
 330   - reply       varchar(120)
 331   - file        varchar(32)
 332   - hash        int(11)
 333   An example of pmail:
 334   pmail "txt like '%libmysql.dll%' and sbj like '%delphi%'"
 335   NOTE: the txt field is NOT case sensitive!
 336 EOF
 337   exit(0);
 338 }