[csw-devel] SF.net SVN: opencsw:[374] utilities/packagesStatistics/tools/ updateQaStatistics.pl
wbonnet at users.sourceforge.net
wbonnet at users.sourceforge.net
Sun Jun 26 23:35:23 CEST 2011
Revision: 374
http://opencsw.svn.sourceforge.net/opencsw/?rev=374&view=rev
Author: wbonnet
Date: 2011-06-26 21:35:22 +0000 (Sun, 26 Jun 2011)
Log Message:
-----------
Initial commit
Added Paths:
-----------
utilities/packagesStatistics/tools/updateQaStatistics.pl
Added: utilities/packagesStatistics/tools/updateQaStatistics.pl
===================================================================
--- utilities/packagesStatistics/tools/updateQaStatistics.pl (rev 0)
+++ utilities/packagesStatistics/tools/updateQaStatistics.pl 2011-06-26 21:35:22 UTC (rev 374)
@@ -0,0 +1,289 @@
+#!/opt/csw/bin/perl
+#
+# The contents of this file are subject to the COMMON DEVELOPMENT AND
+# DISTRIBUTION LICENSE (CDDL) (the "License"); you may not use this
+# file except in compliance with the License.
+#
+# Software distributed under the License is distributed on an "AS IS" basis,
+# WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
+# for the specific language governing rights and limitations under the
+# License.
+#
+# Alternatively, the contents of this file may be used under the terms of
+# either the GNU General Public License Version 3 or later (the "GPL"),
+# in which case the provisions of the GPL are applicable instead
+# of those above. If you wish to allow use of your version of this file only
+# under the terms of either the GPL, and not to allow others to
+# use your version of this file under the terms of the CDDL, indicate your
+# decision by deleting the provisions above and replace them with the notice
+# and other provisions required by the GPL. If you do not delete
+# the provisions above, a recipient may use your version of this file under
+# the terms of any one of the CDDL, or the GPL.
+#
+# Copyright 2011 (http://www.opencsw.org). All rights reserved.
+# Use is subject to license terms.
+#
+#
+# Contributors list :
+#
+# William Bonnet wbonnet at opencsw.org
+#
+#
+# Version :
+#
+# $Id: $
+#
+#
+
+use strict;
+use warnings;
+
+use Getopt::Long;
+use Switch;
+use DBD::mysql;
+use DBI();
+
+# -----------------------------------------------------------------------------
+#
+# Definition of the variables that will receive command lines values after parsing
+#
+# -----------------------------------------------------------------------------
+
+# Database connection
+my $dbHost = "localhost"; # Variable containing the database hostname
+my $dbUser = "qa_stat"; # Variable containing the username for database connection
+my $dbPasswd = "qa_stat"; # Variable containing the password for database connection
+my $dbSchema = "qa_stat"; # Variable containing the schema for database connection
+my $DSN; # Variable containing the database connection string
+my $dbConnect; # Variable containing the database connection
+
+# Schema
+my $dbTablePrefix = "QA_STATS_"; # Prefix to all tables names
+my $dbTableUwatch = $dbTablePrefix . "UWATCH"; # Name of the table containg the uwatch stats
+my $dbTableBugs = $dbTablePrefix . "BUGS"; # Name of the table containg the bugs stats
+
+# Uwatch schema
+my $dbUWatchTablePrefix = "UWATCH_"; # Prefix to all uwatch tables names
+my $dbUWatchTablePackages = $dbUWatchTablePrefix . "PKG_VERSION"; # Name of the table containg the packages
+my $dbUWatchTableHistory = $dbUWatchTablePrefix . "VERSION_HISTORY"; # Name of the table containg the version history
+
+# Command line arguments
+my $command; # Command to process
+my $verbose; # Versbose flag
+
+# Date override
+my $forceYear; # Override, from the command line, the value of the current date
+my $forceMonth; # Override, from the command line, the value of the current date
+my $forceDay; # Override, from the command line, the value of the current date
+my $noDateCheck; # Override, the control on the date using this option you can update with old data
+
+
+# -----------------------------------------------------------------------------
+#
+# args : year and month to increase
+#
+# This functions increase the update counter for the given month and year
+#
+# -----------------------------------------------------------------------------
+sub updateUwatchStatisticsErrorCount {
+
+ # Pop the packages list from the arguments
+ my $dbConnect = shift; # Database connection
+ my $dayToUpdate = shift; # Day of the record to update
+ my $monthToUpdate = shift; # Month of the record to update
+ my $yearToUpdate = shift; # Year of the record to update
+
+ # Try to retrieve the record containing the field to update
+ my $sqlQuery = "select count(1) from " . $dbUWatchTablePackages . " where PKG_LAST_UPSTREAM_CHECK_STATUS = 0 and PKG_GAR_PATH is not null limit 1";
+
+ # Now retrieve data from the table.
+ my $sth = $dbConnect->prepare($sqlQuery) or die "Error:" . $dbConnect->errstr . "\n";
+
+ # Execute the query
+ $sth->execute() or die "Error when executing query : " . $sqlQuery . "\n";
+
+ # Fetch the result into ref variable
+ my @row = $sth->fetchrow_array();
+
+ # We have to finish previous query
+ $sth->finish();
+
+ my $count = 0;
+
+ # If ref is not empty then the record already exist in database
+ if (scalar @row) {
+ $count = $row[0];
+ }
+
+ # Try to retrieve the record containing the field to update
+ $sqlQuery = "select ID_STU from " . $dbTableUwatch . " where STU_YEAR = '" . $yearToUpdate. "' and STU_MONTH = '" . $monthToUpdate. "' and STU_DAY = '" . $dayToUpdate. "'";
+
+ # Now retrieve data from the table.
+ $sth = $dbConnect->prepare($sqlQuery) or die "Error:" . $dbConnect->errstr . "\n";
+
+ # Execute the query
+ $sth->execute() or die "Error when executing query : " . $sqlQuery . "\n";
+
+ # Fetch the result into ref variable
+ @row = $sth->fetchrow_array();
+
+ # We have to finish previous query
+ $sth->finish();
+
+ # If ref is not empty then the record already exist in database
+ if (scalar @row) {
+ # Copy the row id into a local variable and finish the query. Nothing else to do...
+ my $idDb = $row[0];
+
+ # The only two fields we set yet are the package name and the catalog name
+ $sqlQuery = "update $dbTableUwatch set STU_UWATCH_ERROR_COUNT = ? where ID_STU = ?";
+
+ # Execute the query
+ $dbConnect->do($sqlQuery, undef, $count, $idDb) or die "Error:" . $dbConnect->errstr . "\n";
+
+ } else {
+
+ # Entry does not exist, then we have to create it with the update counter set to one
+ $sqlQuery = "insert into $dbTableUwatch (STU_YEAR, STU_MONTH, STU_DAY, STU_UWATCH_ERROR_COUNT) values (?, ?, ?, ?)" ;
+
+ # Execute the query
+ $dbConnect->do($sqlQuery, undef, $yearToUpdate, $monthToUpdate, $dayToUpdate, $count) or die "Error:" . $dbConnect->errstr . "\n";
+ }
+}
+
+# -----------------------------------------------------------------------------
+#
+# args : year and month to increase
+#
+# This functions increase the update counter for the given month and year
+#
+# -----------------------------------------------------------------------------
+sub updateUwatchStatistics {
+
+ # Variables used to store current time
+ my $curSecond; my $curMinute; my $curHour; my $curDay; my $curMonth; my $curYear; my $curWeekDay; my $curDayOfYear; my $curIsDST;
+
+ # Connect to database
+ $DSN = "DBI:mysql:database=" . $dbSchema . ";host=" . $dbHost ;
+ $dbConnect = DBI->connect($DSN, $dbUser, $dbPasswd, {'RaiseError' => 1}) or die "Couldn't connect to database: " . DBI->errstr;
+
+ # Retrieve execution date
+ ($curSecond, $curMinute, $curHour, $curDay, $curMonth, $curYear, $curWeekDay, $curDayOfYear, $curIsDST) = localtime(time);
+ $curYear += 1900;
+ $curMonth++;
+
+ # Process date overriding. If command line has defined the variables, then we replace local time values
+ if (defined($forceYear)) { $curYear = $forceYear; }
+ if (defined($forceMonth)) { $curMonth = $forceMonth; }
+ if (defined($forceDay)) { $curDay = $forceDay; }
+
+ # Update the statistics about the number of errors
+ &updateUwatchStatisticsErrorCount($dbConnect, $curDay, $curMonth, $curYear);
+
+ # Disconnect from the database.
+ $dbConnect->disconnect();
+}
+
+
+
+# -----------------------------------------------------------------------------
+#
+# args : package list list to import
+#
+# This functions compare each package version with the one in the database
+# and updates if necessary
+#
+# -----------------------------------------------------------------------------
+sub updateBugStatistics {
+
+}
+
+
+# -----------------------------------------------------------------------------
+#
+# args : none
+#
+# Display command usage to stdout
+#
+# -----------------------------------------------------------------------------
+sub displayUsage {
+ print "updateQaStatistics version: 0.1\n" ;
+ print "Copyright (C) 2011 OpenCSW http://www.opencsw.org\n" ;
+ print "\n" ;
+ print "This tool is used to produce weekly QA statistics.\n" ;
+ print "updateQaStatistics process the uwatch and bug tables and compute statistics used by the web site to display the qa statistics pages\n" ;
+ print "\n" ;
+ print "Usage : updateQaStatistics [OPTIONS] cmd1 cmd2 ...\n" ;
+ print "\n" ;
+ print " Command list :\n" ;
+ print " --command=[command]\n" ;
+ print " updateUwatchStatistics : Parse a catalog and stores statistics in the database\n" ;
+ print " updateBugStatistics : Parse a 'newpkgs' email, and stores update only statistics in the database\n" ;
+ print "\n" ;
+ print " Options list :\n" ;
+ print " Database options\n" ;
+ print " --dbHost=[mySQL hostname] defaults to : $dbHost\n";
+ print " Defines the database host to use in the connection string\n" ;
+ print " --dbUser=[mySQL userame] defaults to : $dbUser\n";
+ print " Defines the database user to use in the connection string\n" ;
+ print " --dbPasswd=[mySQL password] defaults to : $dbPasswd\n";
+ print " Defines the database password to use in the connection string\n" ;
+ print " --dbSchema=[mySQL database name] defaults to : $dbSchema\n";
+ print " Defines the database to use in the connection string\n" ;
+ print "\n" ;
+ print " Old catalog import options\n" ;
+ print " --forceYear=[year] defaults to : <unset>\n";
+ print " If set, force the year value used when updating packages and statistics\n" ;
+ print " --forceMonth=[month] defaults to : <unset>\n";
+ print " If set, force the month value used when updating packages and statistics\n" ;
+ print " --forceDay=[day] defaults to : <unset>\n";
+ print " If set, force the day value used when updating packages and statistics\n" ;
+ print " --verbose defaults to : <unset>\n" ;
+ print " Make output a bit more verbose\n" ;
+ print "\n" ;
+}
+
+# -----------------------------------------------------------------------------
+#
+# Main part of the script
+#
+# -----------------------------------------------------------------------------
+
+# Check for the number of arguments. If zero then display usage and exit
+my $numArgs = $#ARGV + 1;
+if ($numArgs == 0) {
+ displayUsage() ;
+ exit ;
+}
+
+# Parse the arguments
+my $result = GetOptions ("command=s" => \$command,
+ "dbHost=s" => \$dbHost,
+ "dbUser=s" => \$dbUser,
+ "dbPasswd=s" => \$dbPasswd,
+ "dbSchema=s" => \$dbSchema,
+ "forceYear=i" => \$forceYear,
+ "forceMonth=i" => \$forceMonth,
+ "forceDay=i" => \$forceDay,
+ "noDateCheck" => \$noDateCheck,
+ "verbose" => \$verbose);
+
+die ("Error : missing command\n") unless defined ($command);
+
+# Select the procedure to call to handle the command
+SWITCH: for ($command) {
+ # Command is : import a catalog file in pkg-get format
+ /updateUwatchStatistics/i && do {
+ &updateUwatchStatistics ;
+ last ;
+ } ;
+
+ # Command is : update packages and stats from the content of the newpkgs emails
+ /updateBugStatistics/i && do {
+ &updateBugStatistics ;
+ last ;
+ } ;
+
+ # Default is : unknown command. Exit with an error message
+ die ("unknown command : $command\n") ;
+}
Property changes on: utilities/packagesStatistics/tools/updateQaStatistics.pl
___________________________________________________________________
Added: svn:executable
+ *
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
More information about the devel
mailing list