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 # read list of newsgroups from --checkgroups
82 # into a hash reference
83 my $ValidGroups = &ReadGroupList($OptCheckgroupsFile) if $OptCheckgroupsFile;
85 ### read configuration
86 my %Conf = %{ReadConfig($OptConfFile)};
88 ### override configuration via commandline options
90 $ConfOverride{'DBTableGrps'} = $OptGroupsDB if $OptGroupsDB;
91 &OverrideConfig(\%Conf,\%ConfOverride);
94 my $DBHandle = InitDB(\%Conf,1);
96 ### get time period and newsgroups, prepare SQL 'WHERE' clause
98 # and set caption for output and expression for SQL 'WHERE' clause
99 my ($CaptionPeriod,$SQLWherePeriod) = &GetTimePeriod($OptMonth);
100 # bail out if --month is invalid
101 &Bleat(2,"--month option has an invalid format - ".
102 "please use 'YYYY-MM', 'YYYY-MM:YYYY-MM' or 'ALL'!") if !$CaptionPeriod;
103 # get list of newsgroups and set expression for SQL 'WHERE' clause
104 # with placeholders as well as a list of newsgroup to bind to them
105 my ($SQLWhereNewsgroups,@SQLBindNewsgroups);
106 if ($OptNewsgroups) {
107 ($SQLWhereNewsgroups,@SQLBindNewsgroups) = &SQLGroupList($OptNewsgroups);
108 # bail out if --newsgroups is invalid
109 &Bleat(2,"--newsgroups option has an invalid format!")
110 if !$SQLWhereNewsgroups;
113 ### build SQL WHERE clause (and HAVING clause, if needed)
114 my ($SQLWhereClause,$SQLHavingClause);
115 # $OptBoundType 'level'
116 if ($OptBoundType and $OptBoundType ne 'default') {
117 $SQLWhereClause = SQLBuildClause('where',$SQLWherePeriod,
118 $SQLWhereNewsgroups,&SQLHierarchies($OptSums));
119 $SQLHavingClause = SQLBuildClause('having',&SQLSetBounds($OptBoundType,
120 $LowBound,$UppBound));
121 # $OptBoundType 'threshold' / 'default' or none
123 $SQLWhereClause = SQLBuildClause('where',$SQLWherePeriod,
124 $SQLWhereNewsgroups,&SQLHierarchies($OptSums),
125 &SQLSetBounds('default',$LowBound,$UppBound));
128 ### get sort order and build SQL 'ORDER BY' clause
129 # force to 'month' for $OptReportType 'average' or 'sum'
130 $OptGroupBy = 'month' if ($OptReportType and $OptReportType ne 'default');
131 # default to 'newsgroup' for $OptBoundType 'level' or 'average'
132 $OptGroupBy = 'newsgroup' if (!$OptGroupBy and
133 $OptBoundType and $OptBoundType ne 'default');
134 # default to 'newsgroup' if $OptGroupBy is not set and
135 # just one newsgroup is requested, but more than one month
136 $OptGroupBy = 'newsgroup' if (!$OptGroupBy and
137 $OptMonth =~ /:/ and $OptNewsgroups !~ /[:*%]/);
138 # parse $OptGroupBy to $GroupBy, create ORDER BY clause $SQLOrderClause
139 # if $OptGroupBy is still not set, SQLSortOrder() will default to 'month'
140 my ($GroupBy,$SQLOrderClause) = SQLSortOrder($OptGroupBy, $OptOrderBy);
141 # $GroupBy will contain 'month' or 'newsgroup' (parsed result of $OptGroupBy)
142 # set it to 'month' or 'key' for OutputData()
143 $GroupBy = ($GroupBy eq 'month') ? 'month' : 'key';
145 ### get report type and build SQL 'SELECT' query
147 my $SQLGroupClause = '';
148 my $Precision = 0; # number of digits right of decimal point for output
149 if ($OptReportType and $OptReportType ne 'default') {
150 $SQLGroupClause = 'GROUP BY newsgroup';
151 # change $SQLOrderClause: replace everything before 'postings'
152 $SQLOrderClause =~ s/BY.+postings/BY postings/;
153 if ($OptReportType eq 'average') {
154 $SQLSelect = "'All months',newsgroup,AVG(postings)";
156 # change $SQLOrderClause: replace 'postings' with 'AVG(postings)'
157 $SQLOrderClause =~ s/postings/AVG(postings)/;
158 } elsif ($OptReportType eq 'sum') {
159 $SQLSelect = "'All months',newsgroup,SUM(postings)";
160 # change $SQLOrderClause: replace 'postings' with 'SUM(postings)'
161 $SQLOrderClause =~ s/postings/SUM(postings)/;
164 $SQLSelect = 'month,newsgroup,postings';
167 ### get length of longest newsgroup name delivered by query
168 ### for formatting purposes
169 my $Field = ($GroupBy eq 'month') ? 'newsgroup' : 'month';
170 my ($MaxLength,$MaxValLength) = &GetMaxLength($DBHandle,$Conf{'DBTableGrps'},
171 $Field,'postings',$SQLWhereClause,
175 ### build and execute SQL query
177 # special query preparation for $OptBoundType 'level', 'average' or 'sums'
178 if ($OptBoundType and $OptBoundType ne 'default') {
179 # prepare and execute first query:
180 # get list of newsgroups meeting level conditions
181 $DBQuery = $DBHandle->prepare(sprintf('SELECT newsgroup FROM %s.%s %s '.
182 'GROUP BY newsgroup %s',
183 $Conf{'DBDatabase'},$Conf{'DBTableGrps'},
184 $SQLWhereClause,$SQLHavingClause));
185 $DBQuery->execute(@SQLBindNewsgroups)
186 or &Bleat(2,sprintf("Can't get groups data for %s from %s.%s: %s\n",
187 $CaptionPeriod,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},
189 # add newsgroups to a comma-seperated list ready for IN(...) query
191 while (my ($Newsgroup) = $DBQuery->fetchrow_array) {
192 $GroupList .= ',' if $GroupList;
193 $GroupList .= "'$Newsgroup'";
195 # enhance $WhereClause
197 $SQLWhereClause = SQLBuildClause('where',$SQLWhereClause,
198 sprintf('newsgroup IN (%s)',$GroupList));
200 # condition cannot be satisfied;
201 # force query to fail by adding '0=1'
202 $SQLWhereClause = SQLBuildClause('where',$SQLWhereClause,'0=1');
207 $DBQuery = $DBHandle->prepare(sprintf('SELECT %s FROM %s.%s %s %s %s',
209 $Conf{'DBDatabase'},$Conf{'DBTableGrps'},
210 $SQLWhereClause,$SQLGroupClause,
214 $DBQuery->execute(@SQLBindNewsgroups)
215 or &Bleat(2,sprintf("Can't get groups data for %s from %s.%s: %s\n",
216 $CaptionPeriod,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},
220 # set default to 'pretty'
221 $OptFormat = 'pretty' if !$OptFormat;
222 # print captions if --caption is set
223 if ($OptCaptions && $OptComments) {
224 # print time period with report type
225 my $CaptionReportType= '(number of postings for each month)';
226 if ($OptReportType and $OptReportType ne 'default') {
227 $CaptionReportType= '(average number of postings for each month)'
228 if $OptReportType eq 'average';
229 $CaptionReportType= '(number of all postings for that time period)'
230 if $OptReportType eq 'sum';
232 printf("# ----- Report for %s %s\n",$CaptionPeriod,$CaptionReportType);
233 # print newsgroup list if --newsgroups is set
234 printf("# ----- Newsgroups: %s\n",join(',',split(/:/,$OptNewsgroups)))
236 # print boundaries, if set
237 my $CaptionBoundary= '(counting only month fulfilling this condition)';
238 if ($OptBoundType and $OptBoundType ne 'default') {
239 $CaptionBoundary= '(every single month)' if $OptBoundType eq 'level';
240 $CaptionBoundary= '(on average)' if $OptBoundType eq 'average';
241 $CaptionBoundary= '(all month summed up)' if $OptBoundType eq 'sum';
243 printf("# ----- Threshold: %s %s x %s %s %s\n",
244 $LowBound ? $LowBound : '',$LowBound ? '=>' : '',
245 $UppBound ? '<=' : '',$UppBound ? $UppBound : '',$CaptionBoundary)
246 if ($LowBound or $UppBound);
247 # print primary and secondary sort order
248 printf("# ----- Grouped by %s (%s), sorted %s%s\n",
249 ($GroupBy eq 'month') ? 'Months' : 'Newsgroups',
250 ($OptGroupBy and $OptGroupBy =~ /-?desc$/i) ? 'descending' : 'ascending',
251 ($OptOrderBy and $OptOrderBy =~ /posting/i) ? 'by number of postings ' : '',
252 ($OptOrderBy and $OptOrderBy =~ /-?desc$/i) ? 'descending' : 'ascending');
256 &OutputData($OptFormat,$OptComments,$GroupBy,$Precision,
257 $OptCheckgroupsFile ? $ValidGroups : '',
258 $OptFileTemplate,$DBQuery,$MaxLength,$MaxValLength);
261 $DBHandle->disconnect;
265 ################################ Documentation #################################
269 groupstats - create reports on newsgroup usage
273 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>]
281 This script create reports on newsgroup usage (number of postings per
282 group per month) taken from result tables created by
285 =head2 Features and options
287 =head3 Time period and newsgroups
289 The time period to act on defaults to last month; you can assign another
290 time period or a single month (or drop all time constraints) via the
291 B<--month> option (see below).
293 B<groupstats> will process all newsgroups by default; you can limit
294 processing to only some newsgroups by supplying a list of those groups via
295 B<--newsgroups> option (see below). You can include hierarchy levels in
296 the output by adding the B<--sums> switch (see below). Optionally
297 newsgroups not present in a checkgroups file can be excluded from output,
298 sse B<--checkgroups> below.
302 You can choose between different B<--report> types: postings per month,
303 average postings per month or all postings summed up; for details, see
306 =head3 Upper and lower boundaries
308 Furthermore you can set an upper and/or lower boundary to exclude some
309 results from output via the B<--lower> and B<--upper> options,
310 respectively. By default, all newsgroups with more and/or less postings
311 per month will be excluded from the result set (i.e. not shown and not
312 considered for average and sum reports). You can change the meaning of
313 those boundaries with the B<--boundary> option. For details, please see
316 =head3 Sorting and formatting the output
318 By default, all results are grouped by month; you can group results by
319 newsgroup instead via the B<--groupy-by> option. Within those groups, the
320 list of newsgroups (or months) is sorted alphabetically (or
321 chronologically, respectively) ascending. You can change that order (and
322 sort by number of postings) with the B<--order-by> option. For details and
323 exceptions, please see below.
325 The results will be formatted as a kind of table; you can change the
326 output format to a simple list or just a list of newsgroups and number of
327 postings with the B<--format> option. Captions will be added by means of
328 the B<--caption> option; all comments (and captions) can be supressed by
329 using B<--nocomments>.
331 Last but not least you can redirect all output to a number of files, e.g.
332 one for each month, by submitting the B<--filetemplate> option, see below.
333 Captions and comments are automatically disabled in this case.
337 B<groupstats> will read its configuration from F<newsstats.conf>
338 which should be present in the same directory via Config::Auto.
340 See doc/INSTALL for an overview of possible configuration options.
342 You can override some configuration options via the B<--groupsdb> option.
348 =item B<-V>, B<--version>
350 Print out version and copyright information and exit.
352 =item B<-h>, B<--help>
354 Print this man page and exit.
356 =item B<-m>, B<--month> I<YYYY-MM[:YYYY-MM]|all>
358 Set processing period to a single month in YYYY-MM format or to a time
359 period between two month in YYYY-MM:YYYY-MM format (two month, separated
360 by a colon). By using the keyword I<all> instead, you can set no
361 processing period to process the whole database.
363 =item B<-n>, B<--newsgroups> I<newsgroup(s)>
365 Limit processing to a certain set of newsgroups. I<newsgroup(s)> can
366 be a single newsgroup name (de.alt.test), a newsgroup hierarchy
367 (de.alt.*) or a list of either of these, separated by colons, for
370 de.test:de.alt.test:de.newusers.*
372 =item B<-s>, B<--sums|--nosums> (sum per hierarchy level)
374 Include "virtual" groups for every hierarchy level in output, for
381 See the B<gatherstats> man page for details.
383 =item B<--checkgroups> I<filename>
385 Restrict output to those newgroups present in a file in checkgroups format
386 (one newgroup name per line; everything after the first whitespace on each
387 line is ignored). All other newsgroups will be removed from output.
389 Contrary to B<gatherstats>, I<filename> is not a template, but refers to
390 a single file in checkgroups format.
392 =item B<-r>, B<--report> I<default|average|sums>
394 Choose the report type: I<default>, I<average> or I<sums>
396 By default, B<groupstats> will report the number of postings for each
397 newsgroup in each month. But it can also report the average number of
398 postings per group for all months or the total sum of postings per group
401 For report types I<average> and I<sums>, the B<group-by> option has no
402 meaning and will be silently ignored (see below).
404 =item B<-l>, B<--lower> I<lower boundary>
406 Set the lower boundary. See B<--boundary> below.
408 =item B<-l>, B<--upper> I<upper boundary>
410 Set the upper boundary. See B<--boundary> below.
412 =item B<-b>, B<--boundary> I<boundary type>
414 Set the boundary type to one of I<default>, I<level>, I<average> or
417 By default, all newsgroups with more postings per month than the upper
418 boundary and/or less postings per month than the lower boundary will be
419 excluded from further processing. For the default report that means each
420 month only newsgroups with a number of postings between the boundaries
421 will be displayed. For the other report types, newsgroups with a number of
422 postings exceeding the boundaries in all (!) months will not be
425 For example, lets take a list of newsgroups like this:
428 de.comp.datenbanken.misc 6
429 de.comp.datenbanken.ms-access 84
430 de.comp.datenbanken.mysql 88
432 de.comp.datenbanken.misc 8
433 de.comp.datenbanken.ms-access 126
434 de.comp.datenbanken.mysql 21
436 de.comp.datenbanken.misc 24
437 de.comp.datenbanken.ms-access 83
438 de.comp.datenbanken.mysql 36
440 With C<groupstats --month 2012-01:2012-03 --lower 25 --report sums>,
441 you'll get the following result:
444 de.comp.datenbanken.ms-access 293
445 de.comp.datenbanken.mysql 124
447 de.comp.datenbanken.misc has not been considered even though it has 38
448 postings in total, because it has less than 25 postings in every single
449 month. If you want to list all newsgroups with more than 25 postings
450 I<in total>, you'll have to set the boundary type to I<sum>, see below.
452 A boundary type of I<level> will show only those newsgroups - at all -
453 that satisfy the boundaries in each and every single month. With the above
454 list of newsgroups and
455 C<groupstats --month 2012-01:2012-03 --lower 25 --boundary level --report sums>,
456 you'll get this result:
459 de.comp.datenbanken.ms-access 293
461 de.comp.datenbanken.mysql has not been considered because it had less than
462 25 postings in 2012-02 (only).
464 You can use that to get a list of newsgroups that have more (or less) then
465 x postings in every month during the whole reporting period.
467 A boundary type of I<average> will show only those newsgroups - at all -that
468 satisfy the boundaries on average. With the above list of newsgroups and
469 C<groupstats --month 2012-01:2012-03 --lower 25 --boundary avg --report sums>,
470 you'll get this result:
473 de.comp.datenbanken.ms-access 293
474 de.comp.datenbanken.mysql 145
476 The average number of postings in the three groups is:
478 de.comp.datenbanken.misc 12.67
479 de.comp.datenbanken.ms-access 97.67
480 de.comp.datenbanken.mysql 48.33
482 Last but not least, a boundary type of I<sums> will show only those
483 newsgroups - at all - that satisfy the boundaries with the total sum of
484 all postings during the reporting period. With the above list of
486 C<groupstats --month 2012-01:2012-03 --lower 25 --boundary sum --report sums>,
487 you'll finally get this result:
490 de.comp.datenbanken.misc 38
491 de.comp.datenbanken.ms-access 293
492 de.comp.datenbanken.mysql 145
495 =item B<-g>, B<--group-by> I<month[-desc]|newsgroups[-desc]>
497 By default, all results are grouped by month, sorted chronologically in
498 ascending order, like this:
501 de.comp.datenbanken.ms-access 84
502 de.comp.datenbanken.mysql 88
504 de.comp.datenbanken.ms-access 126
505 de.comp.datenbanken.mysql 21
507 The results can be grouped by newsgroups instead via
508 B<--group-by> I<newsgroup>:
510 ----- de.comp.datenbanken.ms-access:
513 ----- de.comp.datenbanken.mysql:
517 By appending I<-desc> to the group-by option parameter, you can reverse
518 the sort order - e.g. B<--group-by> I<month-desc> will give:
521 de.comp.datenbanken.ms-access 126
522 de.comp.datenbanken.mysql 21
524 de.comp.datenbanken.ms-access 84
525 de.comp.datenbanken.mysql 88
527 Average and sums reports (see above) will always be grouped by months;
528 this option will therefore be ignored.
530 =item B<-o>, B<--order-by> I<default[-desc]|postings[-desc]>
532 Within each group (a single month or single newsgroup, see above), the
533 report will be sorted by newsgroup names in ascending alphabetical order
534 by default. You can change the sort order to descending or sort by number
537 =item B<-f>, B<--format> I<pretty|list|dump>
539 Select the output format, I<pretty> being the default:
542 de.comp.datenbanken.ms-access 84
543 de.comp.datenbanken.mysql 88
545 de.comp.datenbanken.ms-access 126
546 de.comp.datenbanken.mysql 21
548 I<list> format looks like this:
550 2012-01 de.comp.datenbanken.ms-access 84
551 2012-01 de.comp.datenbanken.mysql 88
552 2012-02 de.comp.datenbanken.ms-access 126
553 2012-02 de.comp.datenbanken.mysql 21
555 And I<dump> format looks like this:
558 de.comp.datenbanken.ms-access 84
559 de.comp.datenbanken.mysql 88
561 de.comp.datenbanken.ms-access 126
562 de.comp.datenbanken.mysql 21
564 You can remove the comments by using B<--nocomments>, see below.
566 =item B<-c>, B<--captions|--nocaptions>
568 Add captions to output, like this:
570 ----- Report for 2012-01 to 2012-02 (number of postings for each month)
571 ----- Newsgroups: de.comp.datenbanken.*
572 ----- Threshold: 10 => x <= 20 (on average)
573 ----- Grouped by Newsgroups (ascending), sorted by number of postings descending
577 =item B<--comments|--nocomments>
579 Add comments (group headers) to I<dump> and I<pretty> output. True by default.
581 Use I<--nocomments> to suppress anything except newsgroup names/months and
582 numbers of postings. This is enforced when using B<--filetemplate>, see below.
584 =item B<--filetemplate> I<filename template>
586 Save output to file(s) instead of dumping it to STDOUT. B<groupstats> will
587 create one file for each month (or each newsgroup, accordant to the
588 setting of B<--group-by>, see above), with filenames composed by adding
589 year and month (or newsgroup names) to the I<filename template>, for
590 example with B<--filetemplate> I<stats>:
596 B<--nocomments> is enforced, see above.
598 =item B<--groupsdb> I<database table>
600 Override I<DBTableGrps> from F<newsstats.conf>.
602 =item B<--conffile> I<filename>
604 Load configuration from I<filename> instead of F<newsstats.conf>.
614 Show number of postings per group for lasth month in I<pretty> format:
618 Show that report for January of 2010 and de.alt.* plus de.test,
619 including display of hierarchy levels:
621 groupstats --month 2010-01 --newsgroups de.alt.*:de.test --sums
623 Only show newsgroups with 30 postings or less last month, ordered
624 by number of postings, descending, in I<pretty> format:
626 groupstats --upper 30 --order-by postings-desc
628 Show the total of all postings for the year of 2010 for all groups that
629 had 30 postings or less in every single month in that year, ordered by
630 number of postings in descending order:
632 groupstats -m 2010-01:2010-12 -u 30 -b level -r sums -o postings-desc
634 The same for the average number of postings in the year of 2010:
636 groupstats -m 2010-01:2010-12 -u 30 -b level -r avg -o postings-desc
638 List number of postings per group for eacht month of 2010 and redirect
639 output to one file for each month, namend stats-2010-01 and so on, in
640 machine-readable form (without formatting):
642 groupstats -m 2010-01:2010-12 -f dump --filetemplate stats
649 =item F<bin/groupstats.pl>
653 =item F<lib/NewsStats.pm>
655 Library functions for the NewsStats package.
657 =item F<etc/newsstats.conf>
659 Runtime configuration file.
665 Please report any bugs or feature requests to the author or use the
666 bug tracker at L<http://bugs.th-h.de/>!
686 This script is part of the B<NewsStats> package.
690 Thomas Hochstein <thh@inter.net>
692 =head1 COPYRIGHT AND LICENSE
694 Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
696 This program is free software; you may redistribute it and/or modify it
697 under the same terms as Perl itself.