5 # This script will get statistical data on newgroup usage
8 # It is part of the NewsStats package.
10 # Copyright (c) 2010 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 push(@INC, dirname($0));
22 use NewsStats qw(:DEFAULT :TimePeriods :Output :SQLHelper);
26 ################################# Main program #################################
28 ### read commandline options
29 my %Options = &ReadOptions('m:p:an:o:t:l:b:iscqdf:g:');
31 ### read configuration
32 my %Conf = %{ReadConfig('newsstats.conf')};
34 ### override configuration via commandline options
36 $ConfOverride{'DBTableGrps'} = $Options{'g'} if $Options{'g'};
37 &OverrideConfig(\%Conf,\%ConfOverride);
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
42 # you can't use '-f' with '-b' or '-l'
43 if ($Options{'b'} or $Options{'l'}) {
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");
51 warn ("$MySelf: W: You cannot combine thresholds (-t) and top lists (-b) or levels (-l). Threshold '-t $Options{'t'}' was ignored.\n");
54 if ($Options{'b'} and $Options{'l'}) {
56 warn ("$MySelf: W: You cannot combine top lists (-b) and levels (-l). Level '-l $Options{'l'}' was ignored.\n");
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'};
65 # default output type to 'pretty'
66 $Options{'o'} = 'pretty' if !$Options{'o'};
67 # fail if more than one newsgroup is combined with 'dumpgroup' type
68 die ("$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
70 if ($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");
74 # set output type to 'pretty' for -l
75 if ($Options{'l'} and $Options{'o'} ne 'pretty') {
76 $Options{'o'} = 'pretty';
77 warn ("$MySelf: W: Output type forced to '-o pretty' due to usage of '-l'.\n");
79 # set output type to 'dump' for -f
80 if ($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");
86 my $DBHandle = InitDB(\%Conf,1);
89 my ($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 ...
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;
100 ($StartMonth,$EndMonth) = &GetTimePeriod($Options{'m'},$Options{'p'});
102 # if -p or -a are set: drop -m
103 undef $Options{'m'} if ($Options{'p'} or $Options{'a'});
104 # if time period is more than one month: force output type to '-o pretty' or '-o dumpgroup'
105 if ($Options{'o'} eq 'dump' and ($Options{'p'} or $Options{'a'})) {
106 if (defined($Options{'n'}) and $Options{'n'} !~ /:|\*/) {
107 # just one newsgroup is defined
108 warn ("$MySelf: W: You cannot combine time periods (-p) with '-o dump', changing output type to '-o dumpgroup'.\n");
109 $Options{'o'} = 'dumpgroup';
110 } elsif (!defined($Options{'f'})) {
111 # more than one newsgroup - and no file output
112 warn ("$MySelf: W: You cannot combine time periods (-p) with '-o dump', changing output type to '-o pretty'.\n");
113 $Options{'o'} = 'pretty';
118 # get list of newsgroups (-n)
119 my ($QueryGroupList,$QueryThreshold,@GroupList,@Params);
120 my $Newsgroups = $Options{'n'};
122 # explode list of newsgroups for WHERE clause
123 ($QueryGroupList,@GroupList) = &SQLGroupList($Newsgroups);
125 # set to dummy value (always true)
130 if (defined($Options{'t'})) {
132 # -i: list groups below threshold
133 $QueryThreshold .= ' postings < ?';
135 # default: list groups above threshold
136 $QueryThreshold .= ' postings > ?';
138 # push threshold to Params
139 push @Params,$Options{'t'};
141 # set to dummy value (always true)
145 # construct WHERE clause
146 # $QueryGroupList is "list of newsgroup" (or 1),
147 # $QueryThreshold is threshold definition (or 1),
148 # &SQLHierarchies() takes care of the exclusion of hierarchy levels (.ALL)
149 # according to setting of -s
150 my $WhereClause = sprintf('month BETWEEN ? AND ? AND %s AND %s %s',$QueryGroupList,$QueryThreshold,&SQLHierarchies($Options{'s'}));
152 # get length of longest newsgroup delivered by query for formatting purposes
154 my $MaxLength = &GetMaxLength($DBHandle,$Conf{'DBTableGrps'},'newsgroup',$WhereClause,$StartMonth,$EndMonth,(@GroupList,@Params));
156 my ($OrderClause,$DBQuery);
157 # -b (best of / top list) defined?
158 if (!defined($Options{'b'}) and !defined($Options{'l'})) {
159 # default: neither -b nor -l
160 # set ordering (ORDER BY) to "newsgroups" or "postings", "ASC" or "DESC"
161 # according to -q and -d
162 $OrderClause = 'newsgroup';
163 $OrderClause = 'postings' if $Options{'q'};
164 $OrderClause .= ' DESC' if $Options{'d'};
165 # prepare query: get number of postings per group from groups table for given months and newsgroups
166 $DBQuery = $DBHandle->prepare(sprintf("SELECT month,newsgroup,postings FROM %s.%s WHERE %s ORDER BY month,%s",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause));
167 } elsif ($Options{'b'}) {
168 # -b is set (then -l can't be!)
169 # set sorting order (-i): top or flop list?
171 $OrderClause = 'postings';
173 $OrderClause = 'postings DESC';
175 # set -b to 10 if < 1 (Top 10)
176 $Options{'b'} = 10 if $Options{'b'} !~ /^\d*$/ or $Options{'b'} < 1;
177 # push LIMIT to Params
178 push @Params,$Options{'b'};
179 # prepare query: get sum of postings per group from groups table for given months and newsgroups with LIMIT
180 $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));
182 # -l must be set now, as all other cases have been taken care of
183 # which kind of level (-i): more than -l x or less than -l x?
190 # prepare and execute query: get list of newsgroups meeting level condition
191 $DBQuery = $DBHandle->prepare(sprintf("SELECT newsgroup FROM %s.%s WHERE %s GROUP BY newsgroup HAVING MAX(postings) %s ?",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$Level));
192 $DBQuery->execute($StartMonth,$EndMonth,@GroupList,$Options{'l'})
193 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);
194 # add newsgroups to a comma-seperated list ready for IN(...) query
196 while (my ($Newsgroup) = $DBQuery->fetchrow_array) {
197 $GroupList .= ',' if (defined($GroupList) and $GroupList ne '');
198 $GroupList .= "'$Newsgroup'";
200 $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));
204 $DBQuery->execute($StartMonth,$EndMonth,@GroupList,@Params)
205 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);
208 # reset caption (-c) if -f is set
209 undef($Options{'c'}) if $Options{'f'};
210 # print caption (-c) with time period if -m or -p is set
213 printf ("----- Report for %s\n",$StartMonth);
215 printf ("----- Report from %s to %s %s\n",$StartMonth,$EndMonth,$Options{'a'} ? '(all months)' : '');
218 # print caption (-c) with newsgroup list if -n is set
219 printf ("----- Newsgroups: %s\n",join(',',split(/:/,$Newsgroups))) if $Options{'c'} and $Options{'n'};
220 # print caption (-c) with threshold if -t is set, taking -i in account
221 printf ("----- Threshold: %s %u\n",$Options{'i'} ? '<' : '>',$Options{'t'}) if $Options{'c'} and $Options{'t'};
222 if (!defined($Options{'b'}) and !defined($Options{'l'})) {
223 # default: neither -b nor -l
224 &OutputData($Options{'o'},$Options{'f'},$DBQuery,$MaxLength);
225 } elsif ($Options{'b'}) {
226 # -b is set (then -l can't be!)
227 # we have to read in the query results ourselves, as they do not have standard layout
228 while (my ($Newsgroup,$Postings) = $DBQuery->fetchrow_array) {
229 # we just assign "top x" or "bottom x" instead of a month for the caption and force an output type of pretty
230 print &FormatOutput('pretty', ($Options{'i'} ? 'Bottom ' : 'Top ').$Options{'b'}, $Newsgroup, $Postings, $MaxLength);
233 # -l must be set now, as all other cases have been taken care of
234 # print caption (-c) with level, taking -i in account
235 printf ("----- Newsgroups with %s than %u postings over the whole time period\n",$Options{'i'} ? 'less' : 'more',$Options{'l'}) if $Options{'c'};
236 # we have to read in the query results ourselves, as they do not have standard layout
237 while (my ($Month,$Newsgroup,$Postings) = $DBQuery->fetchrow_array) {
238 # we just switch $Newsgroups and $Month for output generation
239 print &FormatOutput($Options{'o'}, $Newsgroup, $Month, $Postings, 7);
244 $DBHandle->disconnect;
248 ################################ Documentation #################################
252 groupstats - create reports on newsgroup usage
256 B<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>]
260 See doc/README: Perl 5.8.x itself and the following modules from CPAN:
276 This script create reports on newsgroup usage (number of postings per
277 group per month) taken from result tables created by
280 The time period to act on defaults to last month; you can assign
281 another month via the B<-m> switch or a time period via the B<-p>
282 switch; the latter takes preference.
284 B<groupstats> will process all newsgroups by default; you can limit
285 that to only some newsgroups by supplying a list of those groups via
286 B<-n> (see below). You can include hierarchy levels in the output by
287 adding the B<-s> switch (see below).
289 Furthermore you can set a threshold via B<-t> so that only newsgroups
290 with more postings per month will be included in the report. You can
291 invert that by the B<-i> switch so only newsgroups with less than
292 I<threshold> postings per month will be included.
294 You can sort the output by number of postings per month instead of the
295 default (alphabetical list of newsgroups) by using B<-q>; you can
296 reverse the sorting order (from highest to lowest or in reversed
297 alphabetical order) by using B<-d>.
299 Furthermore, you can create a list of newsgroups that had consistently
300 more (or less) than x postings per month during the whole report
301 period by using B<-l> (together with B<i> as needed).
303 Last but not least you can create a "best of" list of the top x
304 newsgroups via B<-b> (or a "worst of" list by adding B<i>).
306 By default, B<groupstats> will dump an alphabetical list of newsgroups,
307 one per line, followed by the number of postings in that group, for
308 every month. You can change the output format by using B<-o> (see
309 below). Captions can be added by setting the B<-c> switch.
313 B<groupstats> will read its configuration from F<newsstats.conf>
314 which should be present in the same directory via Config::Auto.
316 See doc/INSTALL for an overview of possible configuration options.
318 You can override configuration options via the B<-g> switch.
324 =item B<-V> (version)
326 Print out version and copyright information on B<yapfaq> and exit.
330 Print this man page and exit.
332 =item B<-m> I<YYYY-MM> (month)
334 Set processing period to a month in YYYY-MM format. Ignored if B<-p>
337 =item B<-p> I<YYYY-MM:YYYY-MM> (period)
339 Set processing period to a time period between two month, each in
340 YYYY-MM format, separated by a colon. Overrides B<-m>. Ignored if
345 Set no processing period (process whole database). Overrides B<-m>
348 =item B<-n> I<newsgroup(s)> (newsgroups)
350 Limit processing to a certain set of newsgroups. I<newsgroup(s)> can
351 be a single newsgroup name (de.alt.test), a newsgroup hierarchy
352 (de.alt.*) or a list of either of these, separated by colons, for
355 de.test:de.alt.test:de.newusers.*
357 =item B<-t> I<threshold> (threshold)
359 Only include newsgroups with more than I<threshold> postings per
360 month. Can be inverted by the B<-i> switch so that only newsgroups
361 with less than I<threshold> postings will be included.
363 This setting will be ignored if B<-l> or B<-b> is set.
365 =item B<-l> I<level> (level)
367 Only include newsgroups with more than I<level> postings per
368 month, every month during the whole reporting period. Can be inverted
369 by the B<-i> switch so that only newsgroups with less than I<level>
370 postings every single month will be included. Output will be ordered
371 by newsgroup name, followed by month.
373 This setting will be ignored if B<-b> is set. Overrides B<-t> and
374 can't be used together with B<-q>, B<-d> or B<-f>.
376 =item B<-b> I<n> (best of)
378 Create a list of the I<n> newsgroups with the most postings over the
379 whole reporting period. Can be inverted by the B<-i> switch so that a
380 list of the I<n> newsgroups with the least postings over the whole
381 period is generated. Output will be ordered by sum of postings.
383 Overrides B<-t> and B<-l> and can't be used together with B<-q>, B<-d>
384 or B<-f>. Output format is set to I<pretty> (see below).
388 Used in conjunction with B<-t>, B<-l> or B<-b> to set a lower
389 threshold or level or generate a "bottom list" instead of a top list.
391 =item B<-s> (sum per hierarchy level)
393 Include "virtual" groups for every hierarchy level in output, for
400 See the B<gatherstats> man page for details.
402 =item B<-o> I<output type> (output format)
404 Set output format. Default is I<pretty>, which will print a header for
405 each new month, followed by an alphabetical list of newsgroups, each
406 on a new line, followed by the number of postings in that month.
407 B<groupstats> will try to align newsgroup names and posting counts.
408 Usage of B<-b> will force this format; it cannot be used together with
411 I<dump> format is used to create an easily parsable output consisting
412 of an alphabetical list of newsgroups, each on a new line, followed by
413 the number of postings in that month, without any alignment. This
414 default format can't be used with time periods of more than one month.
415 Usage of B<-f> will force this format.
417 I<list> format is like I<dump>, but will print the month in front of
420 I<dumpgroup> format can only be use with a group list (see B<-n>) of
421 exactly one newsgroup and is like I<dump>, but will output months,
422 followed by the number of postings.
424 =item B<-c> (captions)
426 Add captions to output (reporting period, newsgroups list, threshold
429 This setting will be ignored if B<-f> is set.
431 =item B<-q> (quantity of postings)
433 Sort by number of postings instead of by newsgroup names.
435 Cannot be used with B<-l> or B<-b>.
437 =item B<-d> (descending)
439 Change sort order to descending.
441 Cannot be used with B<-l> or B<-b>.
443 =item B<-f> I<filename template> (output file)
445 Save output to file instead of dumping it to STDOUT. B<groupstats>
446 will create one file for each month, with filenames composed by
447 adding year and month to the I<filename template>, for example
454 This setting will be ignored if B<-l> or B<-b> is set. Output format
455 is set to I<dump> (see above).
457 =item B<-g> I<table> (postings per group table)
459 Override I<DBTableGrps> from F<newsstats.conf>.
469 Show number of postings per group for lasth month in I<dump> format:
473 Show that report for January of 2010 and de.alt.* plus de.test,
474 including display of hierarchy levels:
476 groupstats -m 2010-01 -n de.alt.*:de.test -s
478 Show that report for the year of 2010 in I<pretty> format:
480 groupstats -p 2010-01:2010-12 -o pretty
482 Only show newsgroups with less than 30 postings last month, ordered
483 by number of postings, descending, in I<pretty> format:
485 groupstats -iqdt 30 -o pretty
487 Show top 10 for the first half-year of of 2010 in I<pretty> format:
489 groupstats -p 2010-01:2010-06 -b 10 -o pretty
491 Report all groups that had less than 30 postings every singele month
492 in the year of 2010 (I<pretty> format is forced)
494 groupstats -p 2010-01:2010-12 -il 30
500 =item F<groupstats.pl>
504 =item F<NewsStats.pm>
506 Library functions for the NewsStats package.
508 =item F<newsstats.conf>
510 Runtime configuration file for B<yapfaq>.
516 Please report any bugs or feature requests to the author or use the
517 bug tracker at L<http://bugs.th-h.de/>!
537 This script is part of the B<NewsStats> package.
541 Thomas Hochstein <thh@inter.net>
543 =head1 COPYRIGHT AND LICENSE
545 Copyright (c) 2010 Thomas Hochstein <thh@inter.net>
547 This program is free software; you may redistribute it and/or modify it
548 under the same terms as Perl itself.