groupstats.pl: Change default output format to 'pretty'.
[usenet/newsstats.git] / groupstats.pl
CommitLineData
2832c235
TH
1#! /usr/bin/perl -W
2#
3# groupstats.pl
4#
5# This script will get statistical data on newgroup usage
6# form a database.
7#
8# It is part of the NewsStats package.
9#
10# Copyright (c) 2010 Thomas Hochstein <thh@inter.net>
11#
12# It can be redistributed and/or modified under the same terms under
13# which Perl itself is published.
14
15BEGIN {
16 our $VERSION = "0.01";
17 use File::Basename;
18 push(@INC, dirname($0));
19}
20use strict;
21
22use NewsStats qw(:DEFAULT :TimePeriods :Output :SQLHelper);
23
24use DBI;
25
26################################# Main program #################################
27
28### read commandline options
78389b28 29my %Options = &ReadOptions('m:p:an:o:t:l:b:iscqdf:g:');
2832c235
TH
30
31### read configuration
32my %Conf = %{ReadConfig('newsstats.conf')};
33
34### override configuration via commandline options
35my %ConfOverride;
36$ConfOverride{'DBTableGrps'} = $Options{'g'} if $Options{'g'};
37&OverrideConfig(\%Conf,\%ConfOverride);
38
39### check for incompatible command line options
40# you can't mix '-t', '-b' and '-l'
41# -b/-l take preference over -t, and -b takes preference over -l
78389b28 42# you can't use '-f' with '-b' or '-l'
2832c235 43if ($Options{'b'} or $Options{'l'}) {
78389b28
TH
44 if ($Options{'f'}) {
45 # drop -f
46 warn ("$MySelf: W: You cannot save the report to monthly files when using top lists (-b) or levels (-l). Filename template '-f $Options{'f'}' was ignored.\n");
47 undef($Options{'f'});
48 };
2832c235
TH
49 if ($Options{'t'}) {
50 # drop -t
51 warn ("$MySelf: W: You cannot combine thresholds (-t) and top lists (-b) or levels (-l). Threshold '-t $Options{'t'}' was ignored.\n");
52 undef($Options{'t'});
53 };
54 if ($Options{'b'} and $Options{'l'}) {
55 # drop -l
56 warn ("$MySelf: W: You cannot combine top lists (-b) and levels (-l). Level '-l $Options{'l'}' was ignored.\n");
57 undef($Options{'l'});
58 };
59 # -q/-d don't work with -b or -l
60 warn ("$MySelf: W: Sorting by number of postings (-q) ignored due to top list mode (-b) / levels (-l).\n") if $Options{'q'};
61 warn ("$MySelf: W: Reverse sorting (-d) ignored due to top list mode (-b) / levels (-l).\n") if $Options{'d'};
62};
63
64### check output type
628a183c
TH
65# default output type to 'pretty'
66$Options{'o'} = 'pretty' if !$Options{'o'};
2832c235
TH
67# fail if more than one newsgroup is combined with 'dumpgroup' type
68die ("$MySelf: E: You cannot combine newsgroup lists (-n) with more than one group with '-o dumpgroup'!\n") if ($Options{'o'} eq 'dumpgroup' and defined($Options{'n'}) and $Options{'n'} =~ /:|\*/);
69# accept 'dumpgroup' only with -n
70if ($Options{'o'} eq 'dumpgroup' and !defined($Options{'n'})) {
71 $Options{'o'} = 'dump';
72 warn ("$MySelf: W: You must submit exactly one newsgroup ('-n news.group') for '-o dumpgroup'. Output type was set to 'dump'.\n");
73};
74# set output type to 'pretty' for -l
628a183c 75if ($Options{'l'} and $Options{'o'} ne 'pretty') {
2832c235
TH
76 $Options{'o'} = 'pretty';
77 warn ("$MySelf: W: Output type forced to '-o pretty' due to usage of '-l'.\n");
78};
628a183c
TH
79# set output type to 'dump' for -f
80if ($Options{'f'} and $Options{'o'} ne 'dump') {
81 $Options{'o'} = 'dump';
82 warn ("$MySelf: W: Output type forced to '-o dump' due to usage of '-f'.\n");
83};
2832c235 84
f2ddfd8a
TH
85### init database
86my $DBHandle = InitDB(\%Conf,1);
87
2832c235 88### get time period
f2ddfd8a
TH
89my ($StartMonth,$EndMonth);
90# if '-a' is set, set start/end month from database
91# FIXME - it doesn't make that much sense to get first/last month from database to query it
92# with a time period that equals no time period ...
93if ($Options{'a'}) {
94 undef($Options{'m'});
95 undef($Options{'p'});
96 my $DBQuery = $DBHandle->prepare(sprintf("SELECT MIN(month),MAX(month) FROM %s.%s",$Conf{'DBDatabase'},$Conf{'DBTableGrps'}));
97 $DBQuery->execute or die sprintf("$MySelf: E: Can't get MIN/MAX month from %s.%s: %s\n",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$DBI::errstr);
98 ($StartMonth,$EndMonth) = $DBQuery->fetchrow_array;
99} else {
100 ($StartMonth,$EndMonth) = &GetTimePeriod($Options{'m'},$Options{'p'});
101};
628a183c 102# if time period is more than one month: force output type to '-o pretty' or '-o dumpgroup'
f2ddfd8a 103if ($Options{'o'} eq 'dump' and ($Options{'p'} or $Options{'a'})) {
e742bcf5 104 if (defined($Options{'n'}) and $Options{'n'} !~ /:|\*/) {
78389b28
TH
105 # just one newsgroup is defined
106 warn ("$MySelf: W: You cannot combine time periods (-p) with '-o dump', changing output type to '-o dumpgroup'.\n");
107 $Options{'o'} = 'dumpgroup';
108 } elsif (!defined($Options{'f'})) {
109 # more than one newsgroup - and no file output
110 warn ("$MySelf: W: You cannot combine time periods (-p) with '-o dump', changing output type to '-o pretty'.\n");
111 $Options{'o'} = 'pretty';
e742bcf5 112 }
2832c235
TH
113};
114
2832c235
TH
115### create report
116# get list of newsgroups (-n)
6b95accb 117my ($QueryGroupList,$QueryThreshold,@GroupList,@Params);
2832c235
TH
118my $Newsgroups = $Options{'n'};
119if ($Newsgroups) {
120 # explode list of newsgroups for WHERE clause
6b95accb 121 ($QueryGroupList,@GroupList) = &SQLGroupList($Newsgroups);
2832c235
TH
122} else {
123 # set to dummy value (always true)
6b95accb 124 $QueryGroupList = 1;
2832c235
TH
125};
126
127# manage thresholds
128if (defined($Options{'t'})) {
129 if ($Options{'i'}) {
130 # -i: list groups below threshold
6b95accb 131 $QueryThreshold .= ' postings < ?';
2832c235
TH
132 } else {
133 # default: list groups above threshold
6b95accb 134 $QueryThreshold .= ' postings > ?';
2832c235 135 };
6b95accb
TH
136 # push threshold to Params
137 push @Params,$Options{'t'};
138} else {
139 # set to dummy value (always true)
140 $QueryThreshold = 1;
2832c235
TH
141}
142
143# construct WHERE clause
6b95accb
TH
144# $QueryGroupList is "list of newsgroup" (or 1),
145# $QueryThreshold is threshold definition (or 1),
2832c235
TH
146# &SQLHierarchies() takes care of the exclusion of hierarchy levels (.ALL)
147# according to setting of -s
6b95accb 148my $WhereClause = sprintf('month BETWEEN ? AND ? AND %s AND %s %s',$QueryGroupList,$QueryThreshold,&SQLHierarchies($Options{'s'}));
2832c235 149
404c1acd 150# get length of longest newsgroup delivered by query for formatting purposes
2832c235 151# FIXME
6b95accb 152my $MaxLength = &GetMaxLenght($DBHandle,$Conf{'DBTableGrps'},'newsgroup',$WhereClause,$StartMonth,$EndMonth,(@GroupList,@Params));
2832c235
TH
153
154my ($OrderClause,$DBQuery);
155# -b (best of / top list) defined?
156if (!defined($Options{'b'}) and !defined($Options{'l'})) {
157 # default: neither -b nor -l
158 # set ordering (ORDER BY) to "newsgroups" or "postings", "ASC" or "DESC"
159 # according to -q and -d
160 $OrderClause = 'newsgroup';
161 $OrderClause = 'postings' if $Options{'q'};
162 $OrderClause .= ' DESC' if $Options{'d'};
163 # prepare query: get number of postings per group from groups table for given months and newsgroups
164 $DBQuery = $DBHandle->prepare(sprintf("SELECT month,newsgroup,postings FROM %s.%s WHERE %s ORDER BY month,%s",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause));
165} elsif ($Options{'b'}) {
166 # -b is set (then -l can't be!)
404c1acd 167 # set sorting order (-i): top or flop list?
2832c235
TH
168 if ($Options{'i'}) {
169 $OrderClause = 'postings';
170 } else {
171 $OrderClause = 'postings DESC';
172 };
d8695b1c
TH
173 # set -b to 10 if < 1 (Top 10)
174 $Options{'b'} = 10 if $Options{'b'} !~ /^\d*$/ or $Options{'b'} < 1;
6b95accb
TH
175 # push LIMIT to Params
176 push @Params,$Options{'b'};
2832c235
TH
177 # prepare query: get sum of postings per group from groups table for given months and newsgroups with LIMIT
178 $DBQuery = $DBHandle->prepare(sprintf("SELECT newsgroup,SUM(postings) AS postings FROM %s.%s WHERE %s GROUP BY newsgroup ORDER BY %s,newsgroup LIMIT ?",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause));
179} else {
180 # -l must be set now, as all other cases have been taken care of
404c1acd 181 # which kind of level (-i): more than -l x or less than -l x?
6b95accb 182 my ($Level);
2832c235 183 if ($Options{'i'}) {
6b95accb 184 $Level = '<';
2832c235 185 } else {
6b95accb 186 $Level = '>';
2832c235 187 };
b802bc3d
TH
188 # prepare and execute query: get list of newsgroups meeting level condition
189 $DBQuery = $DBHandle->prepare(sprintf("SELECT newsgroup FROM %s.%s WHERE %s GROUP BY newsgroup HAVING MAX(postings) %s ?",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$Level));
190 $DBQuery->execute($StartMonth,$EndMonth,@GroupList,$Options{'l'})
191 or die sprintf("$MySelf: E: Can't get groups data for %s to %s from %s.%s: %s\n",$StartMonth,$EndMonth,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$DBI::errstr);
192 # add newsgroups to a comma-seperated list ready for IN(...) query
193 my $GroupList;
194 while (my ($Newsgroup) = $DBQuery->fetchrow_array) {
195 $GroupList .= ',' if (defined($GroupList) and $GroupList ne '');
196 $GroupList .= "'$Newsgroup'";
197 };
198 $DBQuery = $DBHandle->prepare(sprintf("SELECT month,newsgroup,postings FROM %s.%s WHERE newsgroup IN (%s) AND %s ORDER BY newsgroup,month",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$GroupList,$WhereClause));
2832c235
TH
199};
200
201# execute query
6b95accb 202$DBQuery->execute($StartMonth,$EndMonth,@GroupList,@Params)
2832c235
TH
203 or die sprintf("$MySelf: E: Can't get groups data for %s to %s from %s.%s: %s\n",$StartMonth,$EndMonth,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$DBI::errstr);
204
205# output results
78389b28
TH
206# reset caption (-c) if -f is set
207undef($Options{'c'}) if $Options{'f'};
2832c235 208# print caption (-c) with time period if -m or -p is set
b2e0fd24
TH
209if ($Options{'c'}) {
210 if ($Options{'p'}) {
211 printf ("----- Report from %s to %s\n",$StartMonth,$EndMonth);
212 } elsif ($Options{'m'}) {
213 printf ("----- Report for %s\n",$StartMonth);
214 };
215};
2832c235
TH
216# print caption (-c) with newsgroup list if -n is set
217printf ("----- Newsgroups: %s\n",join(',',split(/:/,$Newsgroups))) if $Options{'c'} and $Options{'n'};
218# print caption (-c) with threshold if -t is set, taking -i in account
219printf ("----- Threshold: %s %u\n",$Options{'i'} ? '<' : '>',$Options{'t'}) if $Options{'c'} and $Options{'t'};
220if (!defined($Options{'b'}) and !defined($Options{'l'})) {
221 # default: neither -b nor -l
78389b28 222 &OutputData($Options{'o'},$Options{'f'},$DBQuery,$MaxLength);
2832c235
TH
223} elsif ($Options{'b'}) {
224 # -b is set (then -l can't be!)
225 # we have to read in the query results ourselves, as they do not have standard layout
226 while (my ($Newsgroup,$Postings) = $DBQuery->fetchrow_array) {
b2e0fd24
TH
227 # we just assign "top x" or "bottom x" instead of a month for the caption and force an output type of pretty
228 print &FormatOutput('pretty', ($Options{'i'} ? 'Bottom ' : 'Top ').$Options{'b'}, $Newsgroup, $Postings, $MaxLength);
2832c235
TH
229 };
230} else {
231 # -l must be set now, as all other cases have been taken care of
b2e0fd24
TH
232 # print caption (-c) with level, taking -i in account
233 printf ("----- Newsgroups with %s than %u postings over the whole time period\n",$Options{'i'} ? 'less' : 'more',$Options{'l'}) if $Options{'c'};
2832c235
TH
234 # we have to read in the query results ourselves, as they do not have standard layout
235 while (my ($Month,$Newsgroup,$Postings) = $DBQuery->fetchrow_array) {
236 # we just switch $Newsgroups and $Month for output generation
2832c235
TH
237 print &FormatOutput($Options{'o'}, $Newsgroup, $Month, $Postings, 7);
238 };
239};
240
241### close handles
242$DBHandle->disconnect;
243
244__END__
245
246################################ Documentation #################################
247
248=head1 NAME
249
250groupstats - create reports on newsgroup usage
251
252=head1 SYNOPSIS
253
78389b28 254B<groupstats> [B<-Vhiscqd>] [B<-m> I<YYYY-MM> | B<-p> I<YYYY-MM:YYYY-MM> | B<-a>] [B<-n> I<newsgroup(s)>] [B<-t> I<threshold>] [B<-l> I<level>] [B<-b> I<number>] [B<-o> I<output type>] [B<-f> I<filename template>] [B<-g> I<database table>]
2832c235
TH
255
256=head1 REQUIREMENTS
257
258See doc/README: Perl 5.8.x itself and the following modules from CPAN:
259
260=over 2
261
262=item -
263
264Config::Auto
265
266=item -
267
268DBI
269
270=back
271
272=head1 DESCRIPTION
273
274This script create reports on newsgroup usage (number of postings per
275group per month) taken from result tables created by
276F<gatherstats.pl>.
277
278The time period to act on defaults to last month; you can assign
279another month via the B<-m> switch or a time period via the B<-p>
280switch; the latter takes preference.
281
282B<groupstats> will process all newsgroups by default; you can limit
283that to only some newsgroups by supplying a list of those groups via
284B<-n> (see below). You can include hierarchy levels in the output by
285adding the B<-s> switch (see below).
286
287Furthermore you can set a threshold via B<-t> so that only newsgroups
288with more postings per month will be included in the report. You can
289invert that by the B<-i> switch so only newsgroups with less than
290I<threshold> postings per month will be included.
291
292You can sort the output by number of postings per month instead of the
293default (alphabetical list of newsgroups) by using B<-q>; you can
294reverse the sorting order (from highest to lowest or in reversed
295alphabetical order) by using B<-d>.
296
297Furthermore, you can create a list of newsgroups that had consistently
298more (or less) than x postings per month during the whole report
299period by using B<-l> (together with B<i> as needed).
300
301Last but not least you can create a "best of" list of the top x
302newsgroups via B<-b> (or a "worst of" list by adding B<i>).
303
628a183c
TH
304By default, B<groupstats> will dump an alphabetical list of newsgroups,
305one per line, followed by the number of postings in that group, for
306every month. You can change the output format by using B<-o> (see
2832c235
TH
307below). Captions can be added by setting the B<-c> switch.
308
309=head2 Configuration
310
f2ddfd8a 311B<groupstats> will read its configuration from F<newsstats.conf>
2832c235
TH
312which should be present in the same directory via Config::Auto.
313
314See doc/INSTALL for an overview of possible configuration options.
315
316You can override configuration options via the B<-g> switch.
317
318=head1 OPTIONS
319
320=over 3
321
322=item B<-V> (version)
323
324Print out version and copyright information on B<yapfaq> and exit.
325
326=item B<-h> (help)
327
328Print this man page and exit.
329
330=item B<-m> I<YYYY-MM> (month)
331
332Set processing period to a month in YYYY-MM format. Ignored if B<-p>
f2ddfd8a 333or B<-a> is set.
2832c235
TH
334
335=item B<-p> I<YYYY-MM:YYYY-MM> (period)
336
337Set processing period to a time period between two month, each in
f2ddfd8a
TH
338YYYY-MM format, separated by a colon. Overrides B<-m>. Ignored if
339B<-a> is set.
340
341=item B<-a> (all)
342
343Set no processing period (process whole database). Overrides B<-m>
344and B<-p>.
2832c235
TH
345
346=item B<-n> I<newsgroup(s)> (newsgroups)
347
348Limit processing to a certain set of newsgroups. I<newsgroup(s)> can
349be a single newsgroup name (de.alt.test), a newsgroup hierarchy
350(de.alt.*) or a list of either of these, separated by colons, for
351example
352
353 de.test:de.alt.test:de.newusers.*
354
355=item B<-t> I<threshold> (threshold)
356
357Only include newsgroups with more than I<threshold> postings per
358month. Can be inverted by the B<-i> switch so that only newsgroups
359with less than I<threshold> postings will be included.
360
361This setting will be ignored if B<-l> or B<-b> is set.
362
363=item B<-l> I<level> (level)
364
365Only include newsgroups with more than I<level> postings per
366month, every month during the whole reporting period. Can be inverted
367by the B<-i> switch so that only newsgroups with less than I<level>
368postings every single month will be included. Output will be ordered
369by newsgroup name, followed by month.
370
371This setting will be ignored if B<-b> is set. Overrides B<-t> and
78389b28 372can't be used together with B<-q>, B<-d> or B<-f>.
2832c235
TH
373
374=item B<-b> I<n> (best of)
375
376Create a list of the I<n> newsgroups with the most postings over the
377whole reporting period. Can be inverted by the B<-i> switch so that a
378list of the I<n> newsgroups with the least postings over the whole
379period is generated. Output will be ordered by sum of postings.
380
78389b28
TH
381Overrides B<-t> and B<-l> and can't be used together with B<-q>, B<-d>
382or B<-f>. Output format is set to I<pretty> (see below).
2832c235
TH
383
384=item B<-i> (invert)
385
386Used in conjunction with B<-t>, B<-l> or B<-b> to set a lower
387threshold or level or generate a "bottom list" instead of a top list.
388
389=item B<-s> (sum per hierarchy level)
390
391Include "virtual" groups for every hierarchy level in output, for
392example:
393
394 de.alt.ALL 10
395 de.alt.test 5
396 de.alt.admin 7
397
398See the B<gatherstats> man page for details.
399
400=item B<-o> I<output type> (output format)
401
628a183c
TH
402Set output format. Default is I<pretty>, which will print a header for
403each new month, followed by an alphabetical list of newsgroups, each
404on a new line, followed by the number of postings in that month.
405B<groupstats> will try to align newsgroup names and posting counts.
406Usage of B<-b> will force this format; it cannot be used together with
407B<-f>.
408
409I<dump> format is used to create an easily parsable output consisting
410of an alphabetical list of newsgroups, each on a new line, followed by
411the number of postings in that month, without any alignment. This
412default format can't be used with time periods of more than one month.
413Usage of B<-f> will force this format.
2832c235
TH
414
415I<list> format is like I<dump>, but will print the month in front of
416the newsgroup name.
417
418I<dumpgroup> format can only be use with a group list (see B<-n>) of
419exactly one newsgroup and is like I<dump>, but will output months,
420followed by the number of postings.
421
2832c235
TH
422=item B<-c> (captions)
423
78389b28
TH
424Add captions to output (reporting period, newsgroups list, threshold
425and so on).
426
427This setting will be ignored if B<-f> is set.
2832c235
TH
428
429=item B<-q> (quantity of postings)
430
431Sort by number of postings instead of by newsgroup names.
432
433Cannot be used with B<-l> or B<-b>.
434
435=item B<-d> (descending)
436
437Change sort order to descending.
438
439Cannot be used with B<-l> or B<-b>.
440
78389b28
TH
441=item B<-f> I<filename template> (output file)
442
443Save output to file instead of dumping it to STDOUT. B<groupstats>
444will create one file for each month, with filenames composed by
445adding year and month to the I<filename template>, for example
446with B<-f> I<stats>:
447
448 stats-2010-01
449 stats-2010-02
450 ... and so on
451
452This setting will be ignored if B<-l> or B<-b> is set. Output format
453is set to I<dump> (see above).
454
2832c235
TH
455=item B<-g> I<table> (postings per group table)
456
457Override I<DBTableGrps> from F<newsstats.conf>.
458
459=back
460
461=head1 INSTALLATION
462
463See doc/INSTALL.
464
465=head1 EXAMPLES
466
467Show number of postings per group for lasth month in I<dump> format:
468
469 groupstats
470
471Show that report for January of 2010 and de.alt.* plus de.test,
472including display of hierarchy levels:
473
474 groupstats -m 2010-01 -n de.alt.*:de.test -s
475
476Show that report for the year of 2010 in I<pretty> format:
477
478 groupstats -p 2010-01:2010-12 -o pretty
479
480Only show newsgroups with less than 30 postings last month, ordered
481by number of postings, descending, in I<pretty> format:
482
483 groupstats -iqdt 30 -o pretty
484
485Show top 10 for the first half-year of of 2010 in I<pretty> format:
486
487 groupstats -p 2010-01:2010-06 -b 10 -o pretty
488
489Report all groups that had less than 30 postings every singele month
490in the year of 2010 (I<pretty> format is forced)
491
492 groupstats -p 2010-01:2010-12 -il 30
493
494=head1 FILES
495
496=over 4
497
498=item F<groupstats.pl>
499
500The script itself.
501
502=item F<NewsStats.pm>
503
504Library functions for the NewsStats package.
505
506=item F<newsstats.conf>
507
508Runtime configuration file for B<yapfaq>.
509
510=back
511
512=head1 BUGS
513
514Please report any bugs or feature requests to the author or use the
515bug tracker at L<http://bugs.th-h.de/>!
516
517=head1 SEE ALSO
518
519=over 2
520
521=item -
522
523doc/README
524
525=item -
526
527doc/INSTALL
528
529=item -
530
531gatherstats -h
532
533=back
534
535This script is part of the B<NewsStats> package.
536
537=head1 AUTHOR
538
539Thomas Hochstein <thh@inter.net>
540
541=head1 COPYRIGHT AND LICENSE
542
543Copyright (c) 2010 Thomas Hochstein <thh@inter.net>
544
545This program is free software; you may redistribute it and/or modify it
546under the same terms as Perl itself.
547
548=cut
This page took 0.037351 seconds and 4 git commands to generate.