+################################################################################
+sub SQLGroupWildcard {
+################################################################################
+### build a valid SQL 'WHERE' expression with or without wildcards
+### IN : $Newsgroup: newsgroup expression, probably with wildcard
+### (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 'newsgroup LIKE ?';
+ }
+};
+
+################################################################################
+sub SQLSetBounds {
+################################################################################
+### set upper and/or lower boundary (number of postings)
+### IN : $Type: 'level', 'average', 'sum' or 'default'
+### $LowBound,$UppBound: lower/upper boundary, respectively
+### OUT: SQL code to become part of a WHERE or HAVING clause
+ my ($Type,$LowBound,$UppBound) = @_;
+ ($LowBound,$UppBound) = SQLCheckNumber($LowBound,$UppBound);
+ if($LowBound and $UppBound and $LowBound > $UppBound) {
+ &Bleat(1,"Lower boundary $LowBound is larger than Upper boundary ".
+ "$UppBound, exchanging boundaries.");
+ ($LowBound,$UppBound) = ($UppBound,$LowBound);
+ }
+ # default to 'default'
+ my $WhereHavingFunction = 'postings';
+ # set $LowBound to SQL statement:
+ # 'WHERE postings >=', 'HAVING MIN(postings) >=' or 'HAVING AVG(postings) >='
+ if ($Type eq 'level') {
+ $WhereHavingFunction = 'MIN(postings)'
+ } elsif ($Type eq 'average') {
+ $WhereHavingFunction = 'AVG(postings)'
+ } elsif ($Type eq 'sum') {
+ $WhereHavingFunction = 'SUM(postings)'
+ }
+ $LowBound = sprintf('%s >= '.$LowBound,$WhereHavingFunction) if ($LowBound);
+ # set $LowBound to SQL statement:
+ # 'WHERE postings <=', 'HAVING MAX(postings) <=' or 'HAVING AVG(postings) <='
+ if ($Type eq 'level') {
+ $WhereHavingFunction = 'MAX(postings)'
+ } elsif ($Type eq 'average') {
+ $WhereHavingFunction = 'AVG(postings)'
+ } elsif ($Type eq 'sum') {
+ $WhereHavingFunction = 'SUM(postings)'
+ }
+ $UppBound = sprintf('%s <= '.$UppBound,$WhereHavingFunction) if ($UppBound);
+ return ($LowBound,$UppBound);
+};
+
+################################################################################
+sub SQLCheckNumber {
+################################################################################
+### check if input is a valid positive integer; otherwise, make it one
+### IN : @Numbers: array of parameters
+### OUT: @Numbers: a valid positive integer
+ my (@Numbers) = @_;
+ foreach my $Number (@Numbers) {
+ if ($Number and $Number < 0) {
+ &Bleat(1,"Boundary $Number is < 0, set to ".-$Number);
+ $Number = -$Number;
+ }
+ $Number = '' if ($Number and $Number !~ /^\d+$/);
+ }
+ return @Numbers;
+};
+
+################################################################################
+sub SQLBuildClause {
+################################################################################
+### build a valid SQL WHERE, GROUP BY, ORDER BY or HAVING clause
+### from multiple expressions which *may* be empty
+### IN : $Type: 'where', 'having', 'group' or 'order'
+### @Expressions: array of expressions
+### OUT: $SQLClause: a SQL clause
+ my ($Type,@Expressions) = @_;
+ my ($SQLClause,$Separator,$Statement);
+ # set separator ('AND' or ',')
+ if ($Type eq 'where' or $Type eq 'having') {
+ $Separator = 'AND';
+ } else {
+ $Separator = ',';
+ }
+ # set statement
+ if ($Type eq 'where') {
+ $Statement = 'WHERE';
+ } elsif ($Type eq 'order') {
+ $Statement = 'ORDER BY';
+ } elsif ($Type eq 'having') {
+ $Statement = 'HAVING';
+ } else {
+ $Statement = 'GROUP BY';
+ }
+ # build query from expressions with separators
+ foreach my $Expression (@Expressions) {
+ if ($Expression) {
+ $SQLClause .= " $Separator " if ($SQLClause);
+ $SQLClause .= $Expression;
+ }
+ }
+ # add statement in front if not already present
+ $SQLClause = " $Statement " . $SQLClause
+ if ($SQLClause and $SQLClause !~ /$Statement/);
+ return $SQLClause;
+};
+
+
projects / usenet / newsstats.git / blobdiff