--- /dev/null
+#! /usr/bin/perl -W\r
+#\r
+# groupstats.pl\r
+#\r
+# This script will get statistical data on newgroup usage\r
+# form a database.\r
+# \r
+# It is part of the NewsStats package.\r
+#\r
+# Copyright (c) 2010 Thomas Hochstein <thh@inter.net>\r
+#\r
+# It can be redistributed and/or modified under the same terms under \r
+# which Perl itself is published.\r
+\r
+BEGIN {\r
+ our $VERSION = "0.01";\r
+ use File::Basename;\r
+ push(@INC, dirname($0));\r
+}\r
+use strict;\r
+\r
+use NewsStats qw(:DEFAULT :TimePeriods :Output :SQLHelper);\r
+\r
+use DBI;\r
+\r
+################################# Definitions ##################################\r
+\r
+# ...\r
+\r
+################################# Main program #################################\r
+\r
+### read commandline options\r
+my %Options = &ReadOptions('m:p:n:o:t:l:b:iscqdg:');\r
+\r
+### read configuration\r
+my %Conf = %{ReadConfig('newsstats.conf')};\r
+\r
+### override configuration via commandline options\r
+my %ConfOverride;\r
+$ConfOverride{'DBTableGrps'} = $Options{'g'} if $Options{'g'};\r
+&OverrideConfig(\%Conf,\%ConfOverride);\r
+\r
+### default output type to 'dump'\r
+$Options{'o'} = 'dump' if !$Options{'o'};\r
+# fail if more than one newsgroup is combined with 'dumpgroup' type\r
+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'} =~ /:|\*/);\r
+# accept 'dumpgroup' only with -n\r
+if ($Options{'o'} eq 'dumpgroup' and !defined($Options{'n'})) {\r
+ $Options{'o'} = 'dump';\r
+ warn ("$MySelf: W: You must submit exactly one newsgroup ('-n news.group') for '-o dumpgroup'. Output type was set to 'dump'.\n");\r
+};\r
+# you can't mix '-t' and '-b'\r
+if ($Options{'b'}) {\r
+ if ($Options{'t'}) {\r
+ warn ("$MySelf: W: You cannot combine thresholds (-t) and top lists (-b). Threshold '-t $Options{'t'}' was ignored.\n");\r
+ undef($Options{'t'});\r
+ };\r
+ warn ("$MySelf: W: Sorting by number of postings (-q) ignored due to top list mode (-b).\n") if $Options{'q'};\r
+ warn ("$MySelf: W: Reverse sorting (-d) ignored due to top list mode (-b).\n") if $Options{'d'};\r
+};\r
+\r
+### get query type, default to 'postings'\r
+#die "$MySelf: E: Unknown query type -q $Options{'q'}!\n" if ($Options{'q'} and !exists($LegalTypes{$Options{'q'}}));\r
+#die "$MySelf: E: You must submit a threshold ('-t') for query type '-q $Options{'q'}'!\n" if ($Options{'q'} and !$Options{'t'});\r
+\r
+### get time period\r
+my ($StartMonth,$EndMonth) = &GetTimePeriod($Options{'m'},$Options{'p'});\r
+# reset to one month for 'dump' type\r
+if ($Options{'o'} eq 'dump' and $Options{'p'}) {\r
+ $StartMonth = $EndMonth;\r
+ warn ("$MySelf: W: You cannot combine time periods (-p) with '-o dump'. Month was set to $StartMonth.\n");\r
+};\r
+\r
+### init database\r
+my $DBHandle = InitDB(\%Conf,1);\r
+\r
+### get data\r
+# get list of newsgroups (-n)\r
+my ($QueryPart,@GroupList);\r
+my $Newsgroups = $Options{'n'};\r
+if ($Newsgroups) {\r
+ ($QueryPart,@GroupList) = &SQLGroupList($Newsgroups);\r
+} else {\r
+ $QueryPart = 1;\r
+};\r
+\r
+# manage thresholds\r
+if (defined($Options{'t'})) {\r
+ if ($Options{'i'}) {\r
+ $QueryPart .= ' AND postings < ?';\r
+ } else {\r
+ $QueryPart .= ' AND postings > ?';\r
+ };\r
+ push @GroupList,$Options{'t'};\r
+}\r
+\r
+# construct WHERE clause\r
+my $WhereClause = sprintf('month BETWEEN ? AND ? AND %s %s',$QueryPart,&SQLHierarchies($Options{'s'}));\r
+\r
+# get lenght of longest newsgroup delivered by query for formatting purposes\r
+my $MaxLength = &GetMaxLenght($DBHandle,$Conf{'DBTableGrps'},'newsgroup',$WhereClause,$StartMonth,$EndMonth,@GroupList);\r
+\r
+my ($OrderClause,$DBQuery);\r
+# -b (best of) defined?\r
+if (!defined($Options{'b'}) and !defined($Options{'l'})) {\r
+ $OrderClause = 'newsgroup';\r
+ $OrderClause = 'postings' if $Options{'q'};\r
+ $OrderClause .= ' DESC' if $Options{'d'};\r
+ # do query: get number of postings per group from groups table for given months and newsgroups\r
+ $DBQuery = $DBHandle->prepare(sprintf("SELECT month,newsgroup,postings FROM %s.%s WHERE %s ORDER BY month,%s",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause));\r
+} elsif ($Options{'b'}) {\r
+ # set sorting order (-i)\r
+ if ($Options{'i'}) {\r
+ $OrderClause = 'postings';\r
+ } else {\r
+ $OrderClause = 'postings DESC';\r
+ };\r
+ # push LIMIT to GroupList to match number of binding vars\r
+ push @GroupList,$Options{'b'};\r
+ # do query: get sum of postings per group from groups table for given months and newsgroups with LIMIT\r
+ $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));\r
+} else { # -l\r
+ # set sorting order (-i)\r
+ if ($Options{'i'}) {\r
+ $OrderClause = '<';\r
+ } else {\r
+ $OrderClause = '>';\r
+ };\r
+ # push level and $StartMonth,$EndMonth - again - to GroupList to match number of binding vars\r
+ push @GroupList,$Options{'l'};\r
+ push @GroupList,$StartMonth,$EndMonth;\r
+ # do query: get number of postings per group from groups table for given months and \r
+ $DBQuery = $DBHandle->prepare(sprintf("SELECT month,newsgroup,postings FROM %s.%s WHERE newsgroup IN (SELECT newsgroup FROM %s.%s WHERE %s GROUP BY newsgroup HAVING MAX(postings) %s ?) AND %s ORDER BY newsgroup,month",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause,$WhereClause));\r
+};\r
+\r
+# execute query\r
+$DBQuery->execute($StartMonth,$EndMonth,@GroupList) 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);\r
+\r
+# output result\r
+printf ("----- Report from %s to %s\n",$StartMonth,$EndMonth) if $Options{'c'} and ($Options{'m'} or $Options{'p'});\r
+printf ("----- Newsgroups: %s\n",join(',',split(/:/,$Newsgroups))) if $Options{'c'} and $Options{'n'};\r
+printf ("----- Threshold: %s %u\n",$Options{'i'} ? '<' : '>',$Options{'t'}) if $Options{'c'} and $Options{'t'};\r
+if (!defined($Options{'b'}) and !defined($Options{'l'})) {\r
+ &OutputData($Options{'o'},$DBQuery,$MaxLength);\r
+} elsif ($Options{'b'}) {\r
+ while (my ($Newsgroup,$Postings) = $DBQuery->fetchrow_array) {\r
+ print &FormatOutput($Options{'o'}, ($Options{'i'} ? 'Bottom ' : 'Top ').$Options{'b'}, $Newsgroup, $Postings, $MaxLength);\r
+ };\r
+} else { # -l\r
+ while (my ($Month,$Newsgroup,$Postings) = $DBQuery->fetchrow_array) {\r
+ print &FormatOutput($Options{'o'}, $Newsgroup, $Month, $Postings, 7);\r
+ };\r
+};\r
+\r
+### close handles\r
+$DBHandle->disconnect;\r
+\r
projects / usenet / newsstats.git / blobdiff