From: Thomas Hochstein Date: Mon, 2 Sep 2013 11:00:05 +0000 (+0200) Subject: Merge branch 'thh-bug53' into next X-Git-Tag: 0.01~1^2~2 X-Git-Url: https://code.th-h.de/?p=usenet%2Fnewsstats.git;a=commitdiff_plain;h=38fa44f89b2f0a5dac65ef1b152aefebd5513c78;hp=-c Merge branch 'thh-bug53' into next * thh-bug53: Improve output padding. --- 38fa44f89b2f0a5dac65ef1b152aefebd5513c78 diff --combined NewsStats.pm index 12334cd,cd059cf..1d07be6 --- a/NewsStats.pm +++ b/NewsStats.pm @@@ -382,16 -382,17 +382,17 @@@ sub OutputData ### $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}); @@@ -427,7 -428,7 +428,7 @@@ $Handle = $OUT; }; print $Handle &FormatOutput($Format, $Comments, $Caption, $Key, $Value, - $Precision, $PadGroup); + $Precision, $PadField, $PadValue); $LastIteration = $Caption; }; close $OUT if ($FileTempl); @@@ -443,9 -444,11 +444,11 @@@ sub FormatOutput ### $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); @@@ -462,8 -465,13 +465,13 @@@ # 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; }; @@@ -485,26 -493,30 +493,30 @@@ sub SQLHierarchies ################################################################################ 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); }; ################################################################################ @@@ -574,49 -586,16 +586,49 @@@ sub SQLGroupList 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); }; @@@ -628,6 -607,7 +640,6 @@@ sub SQLGroupWildcard ### (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 { @@@ -730,19 -710,6 +742,19 @@@ sub SQLBuildClause 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; diff --combined groupstats.pl index a6e54ba,ea1148f..067cffc --- a/groupstats.pl +++ b/groupstats.pl @@@ -1,4 -1,4 +1,4 @@@ -#! /usr/bin/perl -W +#! /usr/bin/perl # # groupstats.pl # @@@ -18,7 -18,6 +18,7 @@@ BEGIN push(@INC, dirname($0)); } use strict; +use warnings; use NewsStats qw(:DEFAULT :TimePeriods :Output :SQLHelper ReadGroupList); @@@ -100,13 -99,8 +100,13 @@@ my ($CaptionPeriod,$SQLWherePeriod) = & "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); @@@ -160,9 -154,10 +160,10 @@@ if ($OptReportType and $OptReportType n ### 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); @@@ -199,8 -194,8 +200,8 @@@ if ($OptBoundType and $OptBoundType ne $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) @@@ -247,7 -242,7 +248,7 @@@ if ($OptCaptions && $OptComments) # output data &OutputData($OptFormat,$OptComments,$GroupBy,$Precision, $OptCheckgroupsFile ? $ValidGroups : '', - $OptFileTemplate,$DBQuery,$MaxLength); + $OptFileTemplate,$DBQuery,$MaxLength,$MaxValLength); ### close handles $DBHandle->disconnect;