From c3c99a7c279f28d4b77748d93e43241de2ba025d Mon Sep 17 00:00:00 2001 From: Remi Collet Date: Sun, 23 May 2010 08:49:51 +0200 Subject: add comptage, a acces_log parser for RPM download stat --- .gitignore | 2 + comptage | 212 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 214 insertions(+) create mode 100755 comptage diff --git a/.gitignore b/.gitignore index f3c7fb1..0b5a2cf 100644 --- a/.gitignore +++ b/.gitignore @@ -2,3 +2,5 @@ config.inc.php *.gz .sendate sendbox +stats.xml +stats.csv diff --git a/comptage b/comptage new file mode 100755 index 0000000..0b33cbb --- /dev/null +++ b/comptage @@ -0,0 +1,212 @@ +#!/usr/bin/php + yum install php-pear-Console-Getargs\n"); +} + +function TraiteFile ($db, $table, $path, $Verb) { + $fic=fopen($path, "r"); + if ($fic) { + while ($tab=fgetcsv($fic, 4000, ' ', '"')) { + if (count($tab)==10 && $tab[6]==200) { + # 0:IP, 3:Date, 5:URL, 6:Status, 7:Size, 9:Agent + list($ordre,$name,$prot)=explode(' ', $tab[5]); + $time=substr($tab[3],1)." ".substr($tab[4],0,-1); + $time=date("Y-m-d H:i:s", strtotime($time)); + + } else if (count($tab)==11 && $tab[7]==200) { + # 0:IP, 3:Date, 5:Host, 6:URL, 7:Status, 8:Size, 10:Agent + list($ordre,$name,$prot)=explode(' ', $tab[6]); + $time=substr($tab[3],1)." ".substr($tab[4],0,-1); + $time=date("Y-m-d H:i:s", strtotime($time)); + } else { + continue; + } + if ($Verb>3 && substr($name,-4)==".rpm") echo "+ $ordre: ".$name."\n"; + + # 1:name, 2:version, 3:release, 4:distro, 6:repotag 7:arch + /// if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.([a-z]*)\.(i386|x86_64|ppc|noarch)\.rpm$@',$name,$regs)) { + + $ok=0; + if ($ordre != "GET") { + $ok=0; + } else if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.[a-z\.0-9]*\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) { + $ok=1; + $rpm=$regs[1]; + $ver=$regs[2]; + $rel=$regs[3]; + $dis=$regs[4]; + $arc=$regs[6]; + } else if (preg_match('@^.*/(.*)-(.*)-(.*)\.[a-z]*\.((fc|el)[0-9]*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) { + $ok=2; + $rpm=$regs[1]; + $ver=$regs[2]; + $rel=$regs[3]; + $dis=$regs[4]; + $arc=$regs[6]; + } else if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) { + $ok=3; + $rpm=$regs[1]; + $ver=$regs[2]; + $rel=$regs[3]; + $dis=$regs[4]; + $arc=$regs[6]; + } else if (preg_match('@^.*/(.*)-(.*)-(.*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) { + $ok=4; + $rpm=$regs[1]; + $ver=$regs[2]; + $rel=$regs[3]; + $dis="all"; + $arc=$regs[4]; + } else if ($Verb>3 && substr($name,-4)==".rpm") + echo "+ rejected \n"; + + if ($ok) { + + $sql = sprintf("SELECT ref FROM %s WHERE `name`='%s' AND `version`='%s' AND `release`='%s' AND `distro`='%s' AND `arch`='%s'", + $table, $rpm, $ver, $rel, $dis, $arc); + if ($Verb>3) echo "+ SQL: $sql\n"; + $Res=$db->query($sql) or die ("** SQL error\n** ".$db->error."\n"); + + if ($Row=$Res->fetch_object()) { + $sql = sprintf("UPDATE %s SET cpt=cpt+1, last='%s', size=size+%d WHERE ref=%d", $table, $time, $tab[7], $Row->ref); + if ($Verb>3) echo "+ SQL: $sql\n"; + $db->query($sql) or die ("** SQL error\n** ".$db->error."\n"); + } else { + $sql = sprintf("INSERT INTO %s SET `name`='%s', `version`='%s', `release`='%s', distro='%s', arch='%s', cpt=1, first='%s', last='%s', size=%d", + $table, $rpm, $ver, $rel, $dis, $arc, $time, $time, $tab[7]); + if ($Verb>3) echo "+ SQL: $sql\n"; + $db->query($sql) or die ("** SQL error\n** ".$db->error."\n"); + } + } + } + fclose($fic); + } else { + echo "Cannot read $path !\n"; + } +} +if (isset($_SERVER["USERNAME"])) $defuser=$_SERVER["USERNAME"]; else $defuser='root'; +$config = array( + "database" => array('short' => 'b', 'max' => 1, 'min' => 1, 'desc' => "Database name", 'default' => "stats"), + "comment" => array('short' => 'x', 'max' => 1, 'min' => 1, 'desc' => "Comment for CSV file", 'default' => ""), + "create" => array('short' => 'c', 'max' => 0, 'desc' => "Create table"), + "debug" => array('short' => 'd', 'max' => 0, 'desc' => "Very-verbose output"), + "password" => array('short' => 'p', 'max' => 1, 'min' => 1, 'desc' => "Password for user on server", 'default' => ""), + "quiet" => array('short' => 'q', 'max' => 0, 'desc' => "Minimal output"), + "server" => array('short' => 's', 'max' => 1, 'min' => 1, 'desc' => "MySQL server name", 'default' => "localhost"), + "table" => array('short' => 't', 'max' => 1, 'min' => 1, 'desc' => "Table name", 'default' => "rpms"), + "user" => array('short' => 'u', 'max' => 1, 'min' => 1, 'desc' => "User login on server", 'default' => $defuser), + "verbose" => array('short' => 'v', 'max' => 0, 'desc' => "Verbose output"), + CONSOLE_GETARGS_PARAMS => array('min' => 1, 'max' => -1, 'desc' => 'files to analyse.', 'default' => "php://stdin") + ); +$args =& Console_Getargs::factory($config); + +if (PEAR::isError($args)) { + echo "comptage version " . VERSION . "\n"; + if ($args->getCode() === CONSOLE_GETARGS_ERROR_USER) + echo Console_Getargs::getHelp($config, null, $args->getMessage())."\n"; + else if ($args->getCode() === CONSOLE_GETARGS_HELP) + echo Console_Getargs::getHelp($config)."\n"; + exit; +} +$Verb=1; +if ($args->getValue('quiet')) $Verb=0; +if ($args->getValue('verbose')) $Verb=2; +if ($args->getValue('debug')) $Verb=4; + +$Server = $args->getValue('server'); +$User = $args->getValue('user'); +$Pass = $args->getValue('password'); +$Base = $args->getValue('database'); +$Table = $args->getValue('table'); +$Comment = $args->getValue('comment'); + +if ($Verb>1) { + echo "comptage version " . VERSION . "\n"; + echo "+ Server = $Server\n"; + echo "+ User = $User\n"; + echo "+ Database = $Base\n"; + echo "+ Table = $Table\n"; +} + +$db=new mysqli($Server, $User, $Pass, $Base); +if (mysqli_connect_errno()) { + die ("** Connection failed\n** ".mysqli_connect_error()."\n"); +} +if ($args->getValue('create')){ + if ($Verb>1) echo "+ Create table $Base.$Table\n"; + + $sql="CREATE TABLE IF NOT EXISTS `rpms` ( + `ref` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, + `version` varchar(20) COLLATE utf8_unicode_ci NOT NULL, + `release` varchar(30) COLLATE utf8_unicode_ci NOT NULL, + `distro` varchar(10) COLLATE utf8_unicode_ci NOT NULL, + `arch` varchar(10) COLLATE utf8_unicode_ci NOT NULL, + `first` datetime DEFAULT NULL, + `last` datetime DEFAULT NULL, + `cpt` int(11) NOT NULL DEFAULT '0', + `size` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`ref`), + UNIQUE KEY `name` (`name`,`version`,`release`,`distro`,`arch`), + KEY `first` (`first`), + KEY `last` (`last`), + KEY `distro` (`distro`,`arch`), + KEY `arch` (`arch`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"; + $db->query($sql) or die ("** SQL error\n** ".$db->error."\n"); +} + +$db->autocommit(TRUE); + +$files=$args->getValue('parameters'); +if (is_array($files)) foreach ($files as $file) { + if ($Verb>1) echo "+ File = $file\n"; + TraiteFile($db, $Table, $file, $Verb); +} else { + if ($Verb>1) echo "+ File = $files\n"; + TraiteFile($db, $Table, $files, $Verb); +} + +// Compute total number of RPM download +if ($res = $db->query("SELECT SUM( cpt ) AS RPM, NOW( ) AS Date FROM `rpms`")) { + $msg ="\n\n"; + if ($row = $res->fetch_assoc()) { + $msg .= "\t".$row['RPM']."\n"; + $msg .= "\t".$row['Date']."\n"; + } + $msg .= "\n"; + + // Record last value to XML file + file_put_contents('stats.xml', $msg); + + // Record value to CSV file (history) + error_log($row['Date'].';'.$row['RPM'].";$Comment\n",3,'stats.csv'); +} +$db->close(); +?> -- cgit