3
# Copyright (C) 2000, 2005 MySQL AB
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.
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.
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
19
# Prints mails to standard output
22
#### Standard inits and get options
30
@fldnms= ("mail_from","mail_to","cc","date","time_zone","file","sbj","txt");
35
$opt_user= $opt_password= "";
36
$opt_socket= "/tmp/mysql.sock";
40
$opt_help=$opt_count=0;
45
GetOptions("help","count","port=i","db=s","table=s","host=s","password=s",
46
"user=s","socket=s", "thread","message_id") || usage();
50
$opt_host = "localhost";
53
if ($opt_help || !$ARGV[0])
59
#### Connect and parsing the query to MySQL
62
$dbh= DBI->connect("DBI:mysql:$opt_db:$opt_host:port=$opt_port:mysql_socket=$opt_socket", $opt_user,$opt_password, { PrintError => 0})
73
my ($row, $val, $q, $mail, $sth);
81
foreach $val (@fldnms)
93
$base_q.= ",message_id" if ($opt_thread || $opt_message_id);
94
$base_q.= " FROM $opt_table";
95
$q= " WHERE $ARGV[0]";
97
$sth= $dbh->prepare($base_q . $q);
100
print "$DBI::errstr\n";
104
for (; ($row= $sth->fetchrow_arrayref); $mail_count++)
106
for ($i= 0; $i < $fields; $i++)
110
$mail[$fields][$mail_count]= $row->[$fields];
111
$mail[$fields][$mail_count].= "\nNumber of Replies: " . get_nr_replies($row->[$fields]);
113
$mail[$i][$mail_count]= $row->[$i];
117
get_mail_by_message_id($row->[$fields], $mail);
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.
130
sub get_mail_by_message_id
132
my ($message_id, $mail)= @_;
133
my ($q, $query, $i, $row, $sth);
135
$q= " WHERE in_reply_to = \"$message_id\"";
136
$query= $base_q . $q;
137
$sth= $dbh->prepare($query);
140
print "QUERY: $query\n$DBI::errstr\n";
144
while (($row= $sth->fetchrow_arrayref))
147
for ($i= 0; $i < $fields; $i++)
151
$mail[$fields][$mail_count]= $row->[$fields];
152
$mail[$fields][$mail_count].= "\nNumber of Replies: " . get_nr_replies($row->[$fields]);
154
$mail[$i][$mail_count]= $row->[$i];
156
$new_message_id= $row->[$fields];
157
if (defined($new_message_id) && length($new_message_id))
159
get_mail_by_message_id($new_message_id, $mail);
166
#### Get number of replies for a given message_id
171
my ($message_id)= @_;
172
my ($sth, $sth2, $q, $row, $row2, $nr_replies);
175
$q= "SELECT COUNT(*) FROM my_mail WHERE in_reply_to=\"$message_id\"";
176
$sth= $dbh->prepare($q);
179
print "QUERY: $q\n$DBI::errstr\n";
183
while (($row= $sth->fetchrow_arrayref))
185
if (($nr_replies= $row->[0]))
187
$q= "SELECT message_id FROM my_mail WHERE in_reply_to=\"$message_id\"";
188
$sth2= $dbh->prepare($q);
191
print "QUERY: $q\n$DBI::errstr\n";
195
while (($row2= $sth2->fetchrow_arrayref))
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]);
216
for ($i=0; $mail[0][$i]; $i++)
219
print " " . ($i+1) . ". Mail ";
224
print "Msg ID: $mail[$fields][$i]\n";
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";
240
print "matches the query ";
245
print "match the query ";
252
#### Count mails that matches the query, but don't show them
259
$sth= $dbh->prepare("select count(*) from $opt_table where $ARGV[0]");
262
print "$DBI::errstr\n";
266
while (($row= $sth->fetchrow_arrayref))
268
$mail_count= $row->[0];
270
if ($mail_count == 1)
272
print "$mail_count Mail matches the query.\n";
276
print "$mail_count Mails match the query.\n";
288
pmail version $VER by Jani Tolonen
290
Usage: pmail [options] "SQL where clause"
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
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:
316
- File (Where from the current mail was loaded into the database)
319
The field names that can be used in the where clause are:
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)
329
- time_zone varchar(6)
334
pmail "txt like '%libmysql.dll%' and sbj like '%delphi%'"
335
NOTE: the txt field is NOT case sensitive!