### $FileTempl: file name template (--filetemplate): filetempl-YYYY-MM
### $DBQuery : database query handle with executed query,
### containing $Month, $Key, $Value
- ### $PadGroup : padding length for key field (optional) for 'pretty'
+ ### $PadField : padding length for key field (optional) for 'pretty'
+ ### $PadValue : padding length for value field (optional) for 'pretty'
my ($Format, $Comments, $GroupBy, $Precision, $ValidKeys, $FileTempl,
- $DBQuery, $PadGroup) = @_;
+ $DBQuery, $PadField, $PadValue) = @_;
my %ValidKeys = %{$ValidKeys} if $ValidKeys;
my ($FileName, $Handle, $OUT);
our $LastIteration;
# define output types
my %LegalOutput;
- @LegalOutput{('dump',,'list','pretty')} = ();
+ @LegalOutput{('dump','list','pretty')} = ();
# bail out if format is unknown
&Bleat(2,"Unknown output type '$Format'!") if !exists($LegalOutput{$Format});
$Handle = $OUT;
};
print $Handle &FormatOutput($Format, $Comments, $Caption, $Key, $Value,
- $Precision, $PadGroup);
+ $Precision, $PadField, $PadValue);
$LastIteration = $Caption;
};
close $OUT if ($FileTempl);
### $Key : newsgroup, client, ... or $Month, as above
### $Value : number of postings with that attribute
### $Precision: number of digits right of decimal point (0 or 2)
- ### $PadGroup : padding length for key field (optional) for 'pretty'
+ ### $PadField : padding length for key field (optional) for 'pretty'
+ ### $PadValue : padding length for value field (optional) for 'pretty'
### OUT: $Output: formatted output
- my ($Format, $Comments, $Caption, $Key, $Value, $Precision, $PadGroup) = @_;
+ my ($Format, $Comments, $Caption, $Key, $Value, $Precision, $PadField,
+ $PadValue) = @_;
my ($Output);
# keep last caption in mind
our ($LastIteration);
# output as a table
$Output = sprintf ("# ----- %s:\n",$Caption)
if ($Comments and (!defined($LastIteration) or $Caption ne $LastIteration));
- $Output .= sprintf ($PadGroup ? sprintf("%%-%us %%10.*f\n",$PadGroup) :
- "%s %.*f\n",$Key,$Precision,$Value);
+ # increase $PadValue for numbers with decimal point
+ $PadValue += $Precision+1 if $Precision;
+ # add padding if $PadField is set; $PadValue HAS to be set then
+ $Output .= sprintf ($PadField ?
+ sprintf("%%-%us%%s %%%u.*f\n",$PadField,$PadValue) :
+ "%s%s %.*f\n",$Key,$Comments ? ':' : '',
+ $Precision,$Value);
};
return $Output;
};
################################################################################
sub GetMaxLength {
################################################################################
- ### get length of longest field in future query result
- ### IN : $DBHandle : database handel
+ ### get length of longest fields in future query result
+ ### IN : $DBHandle : database handle
### $Table : table to query
- ### $Field : field to check
+ ### $Field : field (key!, i.e. month, newsgroup, ...) to check
+ ### $Value : field (value!, i.e. postings) to check
### $WhereClause : WHERE clause
### $HavingClause: HAVING clause
### @BindVars : bind variables for WHERE clause
- ### OUT: $Length: length of longest instnace of $Field
- my ($DBHandle,$Table,$Field,$WhereClause,$HavingClause,@BindVars) = @_;
- my $DBQuery = $DBHandle->prepare(sprintf("SELECT MAX(LENGTH(%s)) ".
- "FROM %s %s %s",$Field,$Table,
- $WhereClause,$HavingClause ?
+ ### OUT: $FieldLength : length of longest instance of $Field
+ ### $ValueLength : length of longest instance of $Value
+ my ($DBHandle,$Table,$Field,$Value,$WhereClause,$HavingClause,@BindVars) = @_;
+ my $DBQuery = $DBHandle->prepare(sprintf("SELECT MAX(LENGTH(%s)),".
+ "MAX(%s) ".
+ "FROM %s %s %s",$Field,,$Value,
+ $Table,$WhereClause,$HavingClause ?
'GROUP BY newsgroup' . $HavingClause .
' ORDER BY LENGTH(newsgroup) '.
'DESC LIMIT 1': ''));
$DBQuery->execute(@BindVars) or &Bleat(1,sprintf("Can't get field length ".
"for '%s' from table '%s': ".
"$DBI::errstr",$Field,$Table));
- my ($Length) = $DBQuery->fetchrow_array;
- return $Length;
+ my ($FieldLength,$ValueMax) = $DBQuery->fetchrow_array;
+ my $ValueLength = length($ValueMax) if ($ValueMax);
+ return ($FieldLength,$ValueLength);
};
################################################################################
my ($Newsgroups) = @_;
# substitute '*' wildcard with SQL wildcard character '%'
$Newsgroups =~ s/\*/%/g;
+ return (undef,undef) if !CheckValidNewsgroups($Newsgroups);
# just one newsgroup?
return (SQLGroupWildcard($Newsgroups),$Newsgroups) if $Newsgroups !~ /:/;
+ my ($SQL,@WildcardGroups,@NoWildcardGroups);
# list of newsgroups separated by ':'
- my $SQL = '(';
my @GroupList = split /:/, $Newsgroups;
foreach (@GroupList) {
- $SQL .= ' OR ' if $SQL gt '(';
- $SQL .= SQLGroupWildcard($_);
+ if ($_ !~ /%/) {
+ # add to list of newsgroup names WITHOUT wildcard
+ push (@NoWildcardGroups,$_);
+ } else {
+ # add to list of newsgroup names WITH wildcard
+ push (@WildcardGroups,$_);
+ # add wildcard to SQL clause
+ # 'OR' if SQL clause is not empty
+ $SQL .= ' OR ' if $SQL;
+ $SQL .= 'newsgroup LIKE ?'
+ }
};
- $SQL .= ')';
+ if (scalar(@NoWildcardGroups)) {
+ # add 'OR' if SQL clause is not empty
+ $SQL .= ' OR ' if $SQL;
+ if (scalar(@NoWildcardGroups) < 2) {
+ # special case: just one newsgroup without wildcard
+ $SQL .= 'newsgroup = ?';
+ } else {
+ # create list of newsgroups to include: 'newsgroup IN (...)'
+ $SQL .= 'newsgroup IN (';
+ my $SQLin;
+ foreach (@NoWildcardGroups) {
+ $SQLin .= ',' if $SQLin;
+ $SQLin .= '?';
+ }
+ # add list to SQL clause
+ $SQL .= $SQLin .= ')';
+ }
+ }
+ # add brackets '()' to SQL clause as needed (more than one wildcard group)
+ if (scalar(@WildcardGroups)) {
+ $SQL = '(' . $SQL .')';
+ }
+ # rebuild @GroupList in (now) correct order
+ @GroupList = (@WildcardGroups,@NoWildcardGroups);
return ($SQL,@GroupList);
};
### (group.name or group.name.%)
### OUT: SQL code to become part of a 'WHERE' clause
my ($Newsgroup) = @_;
- # FIXME: check for validity
if ($Newsgroup !~ /%/) {
return 'newsgroup = ?';
} else {
return $SQLClause;
};
+#####--------------------------- Verifications ----------------------------#####
+
+################################################################################
+sub CheckValidNewsgroups {
+################################################################################
+### syntax check of newgroup list
+### IN : $Newsgroups: list of newsgroups (group.one.*:group.two:group.three.*)
+### OUT: boolean
+ my ($Newsgroups) = @_;
+ my $InvalidCharRegExp = ',; ';
+ return ($Newsgroups =~ /[$InvalidCharRegExp]/) ? 0 : 1;
+};
+
#####------------------------------- done ---------------------------------#####
1;
-#! /usr/bin/perl -W
+#! /usr/bin/perl
#
# groupstats.pl
#
push(@INC, dirname($0));
}
use strict;
+use warnings;
use NewsStats qw(:DEFAULT :TimePeriods :Output :SQLHelper ReadGroupList);
"please use 'YYYY-MM', 'YYYY-MM:YYYY-MM' or 'ALL'!") if !$CaptionPeriod;
# get list of newsgroups and set expression for SQL 'WHERE' clause
# with placeholders as well as a list of newsgroup to bind to them
-my ($SQLWhereNewsgroups,@SQLBindNewsgroups) = &SQLGroupList($OptNewsgroups)
- if $OptNewsgroups;;
+my ($SQLWhereNewsgroups,@SQLBindNewsgroups);
+if ($OptNewsgroups) {
+ ($SQLWhereNewsgroups,@SQLBindNewsgroups) = &SQLGroupList($OptNewsgroups);
+ # bail out if --newsgroups is invalid
+ &Bleat(2,"--newsgroups option has an invalid format!")
+ if !$SQLWhereNewsgroups;
+}
### build SQL WHERE clause (and HAVING clause, if needed)
my ($SQLWhereClause,$SQLHavingClause);
### get length of longest newsgroup name delivered by query
### for formatting purposes
my $Field = ($GroupBy eq 'month') ? 'newsgroup' : 'month';
- my $MaxLength = &GetMaxLength($DBHandle,$Conf{'DBTableGrps'},
- $Field,$SQLWhereClause,$SQLHavingClause,
- @SQLBindNewsgroups);
+ my ($MaxLength,$MaxValLength) = &GetMaxLength($DBHandle,$Conf{'DBTableGrps'},
+ $Field,'postings',$SQLWhereClause,
+ $SQLHavingClause,
+ @SQLBindNewsgroups);
### build and execute SQL query
my ($DBQuery);
$DBQuery = $DBHandle->prepare(sprintf('SELECT %s FROM %s.%s %s %s %s',
$SQLSelect,
$Conf{'DBDatabase'},$Conf{'DBTableGrps'},
- $SQLWhereClause,$SQLGroupClause,$
- SQLOrderClause));
+ $SQLWhereClause,$SQLGroupClause,
+ $SQLOrderClause));
# execute query
$DBQuery->execute(@SQLBindNewsgroups)
# output data
&OutputData($OptFormat,$OptComments,$GroupBy,$Precision,
$OptCheckgroupsFile ? $ValidGroups : '',
- $OptFileTemplate,$DBQuery,$MaxLength);
+ $OptFileTemplate,$DBQuery,$MaxLength,$MaxValLength);
### close handles
$DBHandle->disconnect;
projects / usenet / newsstats.git / commitdiff
