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