5 # This script will get statistical data on newgroup usage
8 # It is part of the NewsStats package.
10 # Copyright (c) 2010-2013 Thomas Hochstein <thh@inter.net>
12 # It can be redistributed and/or modified under the same terms under
13 # which Perl itself is published.
16 our $VERSION = "0.01";
18 # we're in .../bin, so our module is in ../lib
19 push(@INC, dirname($0).'/../lib');
24 use NewsStats qw(:DEFAULT :TimePeriods :Output :SQLHelper ReadGroupList);
27 use Getopt::Long qw(GetOptions);
28 Getopt::Long::config ('bundling');
30 ################################# Main program #################################
32 ### read commandline options
33 my ($OptBoundType,$OptCaptions,$OptCheckgroupsFile,$OptComments,
34 $OptFileTemplate,$OptFormat,$OptGroupBy,$OptGroupsDB,$LowBound,$OptMonth,
35 $OptNewsgroups,$OptOrderBy,$OptReportType,$OptSums,$UppBound,$OptConfFile);
36 GetOptions ('b|boundary=s' => \$OptBoundType,
37 'c|captions!' => \$OptCaptions,
38 'checkgroups=s' => \$OptCheckgroupsFile,
39 'comments!' => \$OptComments,
40 'filetemplate=s' => \$OptFileTemplate,
41 'f|format=s' => \$OptFormat,
42 'g|group-by=s' => \$OptGroupBy,
43 'groupsdb=s' => \$OptGroupsDB,
44 'l|lower=i' => \$LowBound,
45 'm|month=s' => \$OptMonth,
46 'n|newsgroups=s' => \$OptNewsgroups,
47 'o|order-by=s' => \$OptOrderBy,
48 'r|report=s' => \$OptReportType,
49 's|sums!' => \$OptSums,
50 'u|upper=i' => \$UppBound,
51 'conffile=s' => \$OptConfFile,
52 'h|help' => \&ShowPOD,
53 'V|version' => \&ShowVersion) or exit 1;
55 # $OptComments defaults to TRUE
56 $OptComments = 1 if (!defined($OptComments));
57 # force --nocomments when --filetemplate is used
58 $OptComments = 0 if ($OptFileTemplate);
61 if ($OptBoundType =~ /level/i) {
62 $OptBoundType = 'level';
63 } elsif ($OptBoundType =~ /av(era)?ge?/i) {
64 $OptBoundType = 'average';
65 } elsif ($OptBoundType =~ /sums?/i) {
66 $OptBoundType = 'sum';
68 $OptBoundType = 'default';
71 # parse $OptReportType
73 if ($OptReportType =~ /av(era)?ge?/i) {
74 $OptReportType = 'average';
75 } elsif ($OptReportType =~ /sums?/i) {
76 $OptReportType = 'sum';
78 $OptReportType = 'default';
81 # honor $OptCheckgroupsFile,
82 # warn for $OptSums if set concurrently
84 if ($OptCheckgroupsFile) {
85 # read list of newsgroups from --checkgroups
86 # into a hash reference
87 $ValidGroups = &ReadGroupList($OptCheckgroupsFile);
88 &Bleat(1,"--sums option can't possibly work with --checkgroups option set")
92 ### read configuration
93 my %Conf = %{ReadConfig($OptConfFile)};
95 ### override configuration via commandline options
97 $ConfOverride{'DBTableGrps'} = $OptGroupsDB if $OptGroupsDB;
98 &OverrideConfig(\%Conf,\%ConfOverride);
101 my $DBHandle = InitDB(\%Conf,1);
103 ### get time period and newsgroups, prepare SQL 'WHERE' clause
105 # and set caption for output and expression for SQL 'WHERE' clause
106 my ($CaptionPeriod,$SQLWherePeriod) = &GetTimePeriod($OptMonth);
107 # bail out if --month is invalid
108 &Bleat(2,"--month option has an invalid format - ".
109 "please use 'YYYY-MM', 'YYYY-MM:YYYY-MM' or 'ALL'!") if !$CaptionPeriod;
110 # get list of newsgroups and set expression for SQL 'WHERE' clause
111 # with placeholders as well as a list of newsgroup to bind to them
112 my ($SQLWhereNewsgroups,@SQLBindNewsgroups);
113 if ($OptNewsgroups) {
114 ($SQLWhereNewsgroups,@SQLBindNewsgroups) = &SQLGroupList($OptNewsgroups);
115 # bail out if --newsgroups is invalid
116 &Bleat(2,"--newsgroups option has an invalid format!")
117 if !$SQLWhereNewsgroups;
120 ### build SQL WHERE clause (and HAVING clause, if needed)
121 my ($SQLWhereClause,$SQLHavingClause);
122 # $OptBoundType 'level'
123 if ($OptBoundType and $OptBoundType ne 'default') {
124 $SQLWhereClause = SQLBuildClause('where',$SQLWherePeriod,
125 $SQLWhereNewsgroups,&SQLHierarchies($OptSums));
126 $SQLHavingClause = SQLBuildClause('having',&SQLSetBounds($OptBoundType,
127 $LowBound,$UppBound));
128 # $OptBoundType 'threshold' / 'default' or none
130 $SQLWhereClause = SQLBuildClause('where',$SQLWherePeriod,
131 $SQLWhereNewsgroups,&SQLHierarchies($OptSums),
132 &SQLSetBounds('default',$LowBound,$UppBound));
135 ### get sort order and build SQL 'ORDER BY' clause
136 # default to 'newsgroup' for $OptBoundType 'level' or 'average'
137 $OptGroupBy = 'newsgroup' if (!$OptGroupBy and
138 $OptBoundType and $OptBoundType ne 'default');
139 # force to 'month' for $OptReportType 'average' or 'sum'
140 $OptGroupBy = 'month' if ($OptReportType and $OptReportType ne 'default');
141 # parse $OptGroupBy to $GroupBy, create ORDER BY clause $SQLOrderClause
142 my ($GroupBy,$SQLOrderClause) = SQLSortOrder($OptGroupBy, $OptOrderBy);
143 # $GroupBy will contain 'month' or 'newsgroup' (parsed result of $OptGroupBy)
144 # set it to 'month' or 'key' for OutputData()
145 $GroupBy = ($GroupBy eq 'month') ? 'month' : 'key';
147 ### get report type and build SQL 'SELECT' query
149 my $SQLGroupClause = '';
150 my $Precision = 0; # number of digits right of decimal point for output
151 if ($OptReportType and $OptReportType ne 'default') {
152 $SQLGroupClause = 'GROUP BY newsgroup';
153 # change $SQLOrderClause: replace everything before 'postings'
154 $SQLOrderClause =~ s/BY.+postings/BY postings/;
155 if ($OptReportType eq 'average') {
156 $SQLSelect = "'All months',newsgroup,AVG(postings)";
158 # change $SQLOrderClause: replace 'postings' with 'AVG(postings)'
159 $SQLOrderClause =~ s/postings/AVG(postings)/;
160 } elsif ($OptReportType eq 'sum') {
161 $SQLSelect = "'All months',newsgroup,SUM(postings)";
162 # change $SQLOrderClause: replace 'postings' with 'SUM(postings)'
163 $SQLOrderClause =~ s/postings/SUM(postings)/;
166 $SQLSelect = 'month,newsgroup,postings';
169 ### get length of longest newsgroup name delivered by query
170 ### for formatting purposes
171 my $Field = ($GroupBy eq 'month') ? 'newsgroup' : 'month';
172 my ($MaxLength,$MaxValLength) = &GetMaxLength($DBHandle,$Conf{'DBTableGrps'},
173 $Field,'postings',$SQLWhereClause,
177 ### build and execute SQL query
179 # special query preparation for $OptBoundType 'level', 'average' or 'sums'
180 if ($OptBoundType and $OptBoundType ne 'default') {
181 # prepare and execute first query:
182 # get list of newsgroups meeting level conditions
183 $DBQuery = $DBHandle->prepare(sprintf('SELECT newsgroup FROM %s.%s %s '.
184 'GROUP BY newsgroup %s',
185 $Conf{'DBDatabase'},$Conf{'DBTableGrps'},
186 $SQLWhereClause,$SQLHavingClause));
187 $DBQuery->execute(@SQLBindNewsgroups)
188 or &Bleat(2,sprintf("Can't get groups data for %s from %s.%s: %s\n",
189 $CaptionPeriod,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},
191 # add newsgroups to a comma-seperated list ready for IN(...) query
193 while (my ($Newsgroup) = $DBQuery->fetchrow_array) {
194 $GroupList .= ',' if $GroupList;
195 $GroupList .= "'$Newsgroup'";
197 # enhance $WhereClause
199 $SQLWhereClause = SQLBuildClause('where',$SQLWhereClause,
200 sprintf('newsgroup IN (%s)',$GroupList));
202 # condition cannot be satisfied;
203 # force query to fail by adding '0=1'
204 $SQLWhereClause = SQLBuildClause('where',$SQLWhereClause,'0=1');
209 $DBQuery = $DBHandle->prepare(sprintf('SELECT %s FROM %s.%s %s %s %s',
211 $Conf{'DBDatabase'},$Conf{'DBTableGrps'},
212 $SQLWhereClause,$SQLGroupClause,
216 $DBQuery->execute(@SQLBindNewsgroups)
217 or &Bleat(2,sprintf("Can't get groups data for %s from %s.%s: %s\n",
218 $CaptionPeriod,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},
222 # set default to 'pretty'
223 $OptFormat = 'pretty' if !$OptFormat;
224 # print captions if --caption is set
225 if ($OptCaptions && $OptComments) {
226 # print time period with report type
227 my $CaptionReportType= '(number of postings for each month)';
228 if ($OptReportType and $OptReportType ne 'default') {
229 $CaptionReportType= '(average number of postings for each month)'
230 if $OptReportType eq 'average';
231 $CaptionReportType= '(number of all postings for that time period)'
232 if $OptReportType eq 'sum';
234 printf("# ----- Report for %s %s\n",$CaptionPeriod,$CaptionReportType);
235 # print newsgroup list if --newsgroups is set
236 printf("# ----- Newsgroups: %s\n",join(',',split(/:/,$OptNewsgroups)))
238 # print boundaries, if set
239 my $CaptionBoundary= '(counting only month fulfilling this condition)';
240 if ($OptBoundType and $OptBoundType ne 'default') {
241 $CaptionBoundary= '(every single month)' if $OptBoundType eq 'level';
242 $CaptionBoundary= '(on average)' if $OptBoundType eq 'average';
243 $CaptionBoundary= '(all month summed up)' if $OptBoundType eq 'sum';
245 printf("# ----- Threshold: %s %s x %s %s %s\n",
246 $LowBound ? $LowBound : '',$LowBound ? '=>' : '',
247 $UppBound ? '<=' : '',$UppBound ? $UppBound : '',$CaptionBoundary)
248 if ($LowBound or $UppBound);
249 # print primary and secondary sort order
250 printf("# ----- Grouped by %s (%s), sorted %s%s\n",
251 ($GroupBy eq 'month') ? 'Months' : 'Newsgroups',
252 ($OptGroupBy and $OptGroupBy =~ /-?desc$/i) ? 'descending' : 'ascending',
253 ($OptOrderBy and $OptOrderBy =~ /posting/i) ? 'by number of postings ' : '',
254 ($OptOrderBy and $OptOrderBy =~ /-?desc$/i) ? 'descending' : 'ascending');
258 &OutputData($OptFormat,$OptComments,$GroupBy,$Precision,
259 $OptCheckgroupsFile ? $ValidGroups : '',
260 $OptFileTemplate,$DBQuery,$MaxLength,$MaxValLength);
263 $DBHandle->disconnect;
267 ################################ Documentation #################################
271 groupstats - create reports on newsgroup usage
275 B<groupstats> [B<-Vhcs> B<--comments>] [B<-m> I<YYYY-MM>[:I<YYYY-MM>] | I<all>] [B<-n> I<newsgroup(s)>] [B<--checkgroups> I<checkgroups file>] [B<-r> I<report type>] [B<-l> I<lower boundary>] [B<-u> I<upper boundary>] [B<-b> I<boundary type>] [B<-g> I<group by>] [B<-o> I<order by>] [B<-f> I<output format>] [B<--filetemplate> I<filename template>] [B<--groupsdb> I<database table>] [--conffile I<filename>]
283 This script create reports on newsgroup usage (number of postings per
284 group per month) taken from result tables created by
287 =head2 Features and options
289 =head3 Time period and newsgroups
291 The time period to act on defaults to last month; you can assign another
292 time period or a single month (or drop all time constraints) via the
293 B<--month> option (see below).
295 B<groupstats> will process all newsgroups by default; you can limit
296 processing to only some newsgroups by supplying a list of those groups via
297 B<--newsgroups> option (see below). You can include hierarchy levels in
298 the output by adding the B<--sums> switch (see below). Optionally
299 newsgroups not present in a checkgroups file can be excluded from output,
300 sse B<--checkgroups> below.
304 You can choose between different B<--report> types: postings per month,
305 average postings per month or all postings summed up; for details, see
308 =head3 Upper and lower boundaries
310 Furthermore you can set an upper and/or lower boundary to exclude some
311 results from output via the B<--lower> and B<--upper> options,
312 respectively. By default, all newsgroups with more and/or less postings
313 per month will be excluded from the result set (i.e. not shown and not
314 considered for average and sum reports). You can change the meaning of
315 those boundaries with the B<--boundary> option. For details, please see
318 =head3 Sorting and formatting the output
320 By default, all results are grouped by month; you can group results by
321 newsgroup instead via the B<--groupy-by> option. Within those groups, the
322 list of newsgroups (or months) is sorted alphabetically (or
323 chronologically, respectively) ascending. You can change that order (and
324 sort by number of postings) with the B<--order-by> option. For details and
325 exceptions, please see below.
327 The results will be formatted as a kind of table; you can change the
328 output format to a simple list or just a list of newsgroups and number of
329 postings with the B<--format> option. Captions will be added by means of
330 the B<--caption> option; all comments (and captions) can be supressed by
331 using B<--nocomments>.
333 Last but not least you can redirect all output to a number of files, e.g.
334 one for each month, by submitting the B<--filetemplate> option, see below.
335 Captions and comments are automatically disabled in this case.
339 B<groupstats> will read its configuration from F<newsstats.conf>
340 which should be present in the same directory via Config::Auto.
342 See doc/INSTALL for an overview of possible configuration options.
344 You can override some configuration options via the B<--groupsdb> option.
350 =item B<-V>, B<--version>
352 Print out version and copyright information and exit.
354 =item B<-h>, B<--help>
356 Print this man page and exit.
358 =item B<-m>, B<--month> I<YYYY-MM[:YYYY-MM]|all>
360 Set processing period to a single month in YYYY-MM format or to a time
361 period between two month in YYYY-MM:YYYY-MM format (two month, separated
362 by a colon). By using the keyword I<all> instead, you can set no
363 processing period to process the whole database.
365 =item B<-n>, B<--newsgroups> I<newsgroup(s)>
367 Limit processing to a certain set of newsgroups. I<newsgroup(s)> can
368 be a single newsgroup name (de.alt.test), a newsgroup hierarchy
369 (de.alt.*) or a list of either of these, separated by colons, for
372 de.test:de.alt.test:de.newusers.*
374 =item B<-s>, B<--sums|--nosums> (sum per hierarchy level)
376 Include "virtual" groups for every hierarchy level in output, for
383 See the B<gatherstats> man page for details.
385 This option does not work together with the B<--checkgroups> option as
386 all "virtual" groups will not be present in the checkgroups file.
388 =item B<--checkgroups> I<filename>
390 Restrict output to those newgroups present in a file in checkgroups format
391 (one newgroup name per line; everything after the first whitespace on each
392 line is ignored). All other newsgroups will be removed from output.
394 Contrary to B<gatherstats>, I<filename> is not a template, but refers to
395 a single file in checkgroups format.
397 The B<--sums> option will not work together with this option as "virtual"
398 groups will not be present in the checkgroups file.
400 =item B<-r>, B<--report> I<default|average|sums>
402 Choose the report type: I<default>, I<average> or I<sums>
404 By default, B<groupstats> will report the number of postings for each
405 newsgroup in each month. But it can also report the average number of
406 postings per group for all months or the total sum of postings per group
409 For report types I<average> and I<sums>, the B<group-by> option has no
410 meaning and will be silently ignored (see below).
412 =item B<-l>, B<--lower> I<lower boundary>
414 Set the lower boundary. See B<--boundary> below.
416 =item B<-l>, B<--upper> I<upper boundary>
418 Set the upper boundary. See B<--boundary> below.
420 =item B<-b>, B<--boundary> I<boundary type>
422 Set the boundary type to one of I<default>, I<level>, I<average> or
425 By default, all newsgroups with more postings per month than the upper
426 boundary and/or less postings per month than the lower boundary will be
427 excluded from further processing. For the default report that means each
428 month only newsgroups with a number of postings between the boundaries
429 will be displayed. For the other report types, newsgroups with a number of
430 postings exceeding the boundaries in all (!) months will not be
433 For example, lets take a list of newsgroups like this:
436 de.comp.datenbanken.misc 6
437 de.comp.datenbanken.ms-access 84
438 de.comp.datenbanken.mysql 88
440 de.comp.datenbanken.misc 8
441 de.comp.datenbanken.ms-access 126
442 de.comp.datenbanken.mysql 21
444 de.comp.datenbanken.misc 24
445 de.comp.datenbanken.ms-access 83
446 de.comp.datenbanken.mysql 36
448 With C<groupstats --month 2012-01:2012-03 --lower 25 --report sums>,
449 you'll get the following result:
452 de.comp.datenbanken.ms-access 293
453 de.comp.datenbanken.mysql 124
455 de.comp.datenbanken.misc has not been considered even though it has 38
456 postings in total, because it has less than 25 postings in every single
457 month. If you want to list all newsgroups with more than 25 postings
458 I<in total>, you'll have to set the boundary type to I<sum>, see below.
460 A boundary type of I<level> will show only those newsgroups - at all -
461 that satisfy the boundaries in each and every single month. With the above
462 list of newsgroups and
463 C<groupstats --month 2012-01:2012-03 --lower 25 --boundary level --report sums>,
464 you'll get this result:
467 de.comp.datenbanken.ms-access 293
469 de.comp.datenbanken.mysql has not been considered because it had less than
470 25 postings in 2012-02 (only).
472 You can use that to get a list of newsgroups that have more (or less) then
473 x postings in every month during the whole reporting period.
475 A boundary type of I<average> will show only those newsgroups - at all -that
476 satisfy the boundaries on average. With the above list of newsgroups and
477 C<groupstats --month 2012-01:2012-03 --lower 25 --boundary avg --report sums>,
478 you'll get this result:
481 de.comp.datenbanken.ms-access 293
482 de.comp.datenbanken.mysql 145
484 The average number of postings in the three groups is:
486 de.comp.datenbanken.misc 12.67
487 de.comp.datenbanken.ms-access 97.67
488 de.comp.datenbanken.mysql 48.33
490 Last but not least, a boundary type of I<sums> will show only those
491 newsgroups - at all - that satisfy the boundaries with the total sum of
492 all postings during the reporting period. With the above list of
494 C<groupstats --month 2012-01:2012-03 --lower 25 --boundary sum --report sums>,
495 you'll finally get this result:
498 de.comp.datenbanken.misc 38
499 de.comp.datenbanken.ms-access 293
500 de.comp.datenbanken.mysql 145
503 =item B<-g>, B<--group-by> I<month[-desc]|newsgroups[-desc]>
505 By default, all results are grouped by month, sorted chronologically in
506 ascending order, like this:
509 de.comp.datenbanken.ms-access 84
510 de.comp.datenbanken.mysql 88
512 de.comp.datenbanken.ms-access 126
513 de.comp.datenbanken.mysql 21
515 The results can be grouped by newsgroups instead via
516 B<--group-by> I<newsgroup>:
518 ----- de.comp.datenbanken.ms-access:
521 ----- de.comp.datenbanken.mysql:
525 By appending I<-desc> to the group-by option parameter, you can reverse
526 the sort order - e.g. B<--group-by> I<month-desc> will give:
529 de.comp.datenbanken.ms-access 126
530 de.comp.datenbanken.mysql 21
532 de.comp.datenbanken.ms-access 84
533 de.comp.datenbanken.mysql 88
535 Average and sums reports (see above) will always be grouped by months;
536 this option will therefore be ignored.
538 =item B<-o>, B<--order-by> I<default[-desc]|postings[-desc]>
540 Within each group (a single month or single newsgroup, see above), the
541 report will be sorted by newsgroup names in ascending alphabetical order
542 by default. You can change the sort order to descending or sort by number
545 =item B<-f>, B<--format> I<pretty|list|dump>
547 Select the output format, I<pretty> being the default:
550 de.comp.datenbanken.ms-access 84
551 de.comp.datenbanken.mysql 88
553 de.comp.datenbanken.ms-access 126
554 de.comp.datenbanken.mysql 21
556 I<list> format looks like this:
558 2012-01 de.comp.datenbanken.ms-access 84
559 2012-01 de.comp.datenbanken.mysql 88
560 2012-02 de.comp.datenbanken.ms-access 126
561 2012-02 de.comp.datenbanken.mysql 21
563 And I<dump> format looks like this:
566 de.comp.datenbanken.ms-access 84
567 de.comp.datenbanken.mysql 88
569 de.comp.datenbanken.ms-access 126
570 de.comp.datenbanken.mysql 21
572 You can remove the comments by using B<--nocomments>, see below.
574 =item B<-c>, B<--captions|--nocaptions>
576 Add captions to output, like this:
578 ----- Report for 2012-01 to 2012-02 (number of postings for each month)
579 ----- Newsgroups: de.comp.datenbanken.*
580 ----- Threshold: 10 => x <= 20 (on average)
581 ----- Grouped by Newsgroups (ascending), sorted by number of postings descending
585 =item B<--comments|--nocomments>
587 Add comments (group headers) to I<dump> and I<pretty> output. True by default.
589 Use I<--nocomments> to suppress anything except newsgroup names/months and
590 numbers of postings. This is enforced when using B<--filetemplate>, see below.
592 =item B<--filetemplate> I<filename template>
594 Save output to file(s) instead of dumping it to STDOUT. B<groupstats> will
595 create one file for each month (or each newsgroup, accordant to the
596 setting of B<--group-by>, see above), with filenames composed by adding
597 year and month (or newsgroup names) to the I<filename template>, for
598 example with B<--filetemplate> I<stats>:
604 B<--nocomments> is enforced, see above.
606 =item B<--groupsdb> I<database table>
608 Override I<DBTableGrps> from F<newsstats.conf>.
610 =item B<--conffile> I<filename>
612 Load configuration from I<filename> instead of F<newsstats.conf>.
622 Show number of postings per group for lasth month in I<pretty> format:
626 Show that report for January of 2010 and de.alt.* plus de.test,
627 including display of hierarchy levels:
629 groupstats --month 2010-01 --newsgroups de.alt.*:de.test --sums
631 Only show newsgroups with 30 postings or less last month, ordered
632 by number of postings, descending, in I<pretty> format:
634 groupstats --upper 30 --order-by postings-desc
636 Show the total of all postings for the year of 2010 for all groups that
637 had 30 postings or less in every single month in that year, ordered by
638 number of postings in descending order:
640 groupstats -m 2010-01:2010-12 -u 30 -b level -r sums -o postings-desc
642 The same for the average number of postings in the year of 2010:
644 groupstats -m 2010-01:2010-12 -u 30 -b level -r avg -o postings-desc
646 List number of postings per group for eacht month of 2010 and redirect
647 output to one file for each month, namend stats-2010-01 and so on, in
648 machine-readable form (without formatting):
650 groupstats -m 2010-01:2010-12 -f dump --filetemplate stats
657 =item F<bin/groupstats.pl>
661 =item F<lib/NewsStats.pm>
663 Library functions for the NewsStats package.
665 =item F<etc/newsstats.conf>
667 Runtime configuration file.
673 Please report any bugs or feature requests to the author or use the
674 bug tracker at L<http://bugs.th-h.de/>!
694 This script is part of the B<NewsStats> package.
698 Thomas Hochstein <thh@inter.net>
700 =head1 COPYRIGHT AND LICENSE
702 Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
704 This program is free software; you may redistribute it and/or modify it
705 under the same terms as Perl itself.