cvs: docweb /scripts notes_stats.php

http://cvs.php.net/diff.php/docweb/scripts/notes_stats.php?r1=1.2&r2=1.3&ty=u
Index: docweb/scripts/notes_stats.php
diff -u docweb/scripts/notes_stats.php:1.2 docweb/scripts/notes_stats.php:1.3
--- docweb/scripts/notes_stats.php:1.2	Mon Jan 31 10:32:13 2005
+++ docweb/scripts/notes_stats.php	Tue Feb  1 12:34:44 2005
@@ -17,477 +17,143 @@
 |          Vincent Gevers <[email protected]>                            |
 | Credits: Sean Coates <[email protected]>                                  |
 +----------------------------------------------------------------------+
-$Id: notes_stats.php,v 1.2 2005/01/31 15:32:13 vincent Exp $
+$Id: notes_stats.php,v 1.3 2005/02/01 17:34:44 nlopess Exp $
 */
 
-/*
- * Warning: This script can use a lot of memory 
- *
- * Usage:
- * $ php notes_stats.php
- */
- 
-/*
- * TODO:
- * - Use a library
- * - Database improvements
- * - Speed improvements
- * - Nicer layout
- */
-
-// minimum amount actions
-$minact = 100;
-// after how many secs should the list be chopped
-$after = 182.5*24*60*60; // half year
-
-$startTime = getmicrotime();
-
 require_once '../build-ops.php';
 
 $DBFile = SQLITE_DIR . 'notes_stats.sqlite';
 
-if (!is_readable($DBFile)) {
-// asuming it's not created yet
-
-echo 'Creating the database: ' . $DBFile;
-
-$sqlite = sqlite_open($DBFile, 0666);
-
-// Table creation
-$sqlCreateInfo = "
-    CREATE
-    TABLE
-        notes_info
-        (
-            last_article INT,
-            build_date DATETIME,
-            subjects INT
-        );
-";
-sqlite_query($sqlite, $sqlCreateInfo);
-// total user stats
-$sqlCreateStats = "
-    CREATE
-    TABLE
-        notes_stats
-        (
-            username VARCHAR(255),
-            deleted INT,
-            rejected INT,
-            modified INT,
-            total INT
-        );
-";
-sqlite_query($sqlite, $sqlCreateStats);
-// older than $after
-$sqlCreateStatsOld = "
-    CREATE
-    TABLE
-        notes_stats_old
-        (
-            username VARCHAR(255),
-            deleted INT,
-            rejected INT,
-            modified INT,
-            total INT
-        );
-";
-sqlite_query($sqlite, $sqlCreateStatsOld);
-// newer than $after
-$sqlCreateStatsNew = "
-    CREATE
-    TABLE
-        notes_stats_new
-        (
-            username VARCHAR(255),
-            deleted INT,
-            rejected INT,
-            modified INT,
-            total INT
-        );
-";
-sqlite_query($sqlite, $sqlCreateStatsNew);
-// most active pages
-$sqlCreateFiles = "
-    CREATE
-    TABLE
-        notes_files
-        (
-            page VARCHAR(255),
-            total INT
-        );
-";
-sqlite_query($sqlite, $sqlCreateFiles);
-
-// only if this is a fresh db
-$sql = "
-    INSERT
-    INTO
-        notes_info (last_article, build_date, subjects)
-    VALUES
-        (1, ".time().", 0)
-";
-sqlite_query($sqlite, $sql);
-
-sqlite_close($sqlite);
 
-clearstatcache();
 if (is_readable($DBFile)) {
-    echo "\nOK, everything went fine while creating the database\n";
-} else {
-    echo "\nError: Please check the " . SQLIE_DIR . " dir for write access\n";
-    exit;
-}
+    $sqlite = sqlite_open($DBFile, 0666);
 
+// asuming it's not created yet
+} else {
+    $sqlite = create_db($DBFile);
 }
-// done creating a fresh db
-
-// everyting is ok, open the db
-$sqlite = sqlite_open($DBFile, 0666);
-
-$bytesRead = 0;
 
 $s = nntp_connect("news.php.net") or die("failed to connect to news server\n");
-$res = nntp_cmd($s,"GROUP php.notes",211) or die("failed to get infos on news group\n");
-
-$sql = "SELECT
-            last_article
-        FROM
-             notes_info
-";         
-list($first) = sqlite_fetch_array(sqlite_query($sqlite, $sql));
+$res = nntp_cmd($s, 'GROUP php.notes', 211) or die("failed to get infos on news group\n");
 
-$new = explode(" ", $res);
-$last =  $new[0];
+$first = sqlite_single_query($sqlite, 'SELECT last_article FROM info');
+list($last) = explode(' ', $res);
 
-//$first = 82000;
-//$last =  84164;
-
-if ($first == $last)
+if ($first >= $last)
     die("Nothing I can do, no new notes available\n");
 
 echo "Fetching items: $first-$last\n";
-$res = nntp_cmd($s,"XOVER $first-$last", 224) or die("failed to XOVER the new items\n");
+$res = nntp_cmd($s, "XOVER $first-$last", 224) or die("failed to XOVER the new items\n");
+
+$sql = '';
+$last_update = time();
 
-$files = $team = $tmp = array();
-$tmp['o'] = array();
-$tmp['n'] = array();
 
-$in_old = false;
-$dlStart = getmicrotime();
 for ($i = $first; $i < $last; $i++) {
     $line = fgets($s, 4096);
-    $bytesRead += strlen($line);
-    list($n,$subj,$author,$odate) = explode("\t", $line, 5);
+    list($n, $subj, $author, $odate) = explode("\t", $line, 5);
 
+    /* check if the server has closed the connection
+       the program will continue to fetch data later */
     if (feof($s)) {
-        die("EOF, Please re-run this script\n");
+        break;
     }
-    echo "$i: $subj @ $odate\n"; // for debugging
 
-/*
- * What should be matched:
- * note ID deleted from SECTION by EDITOR
- * note ID rejected from SECTION by EDITOR
- * note ID modified in SECTION by EDITOR
- * note ID moved from SECTION to SECTION by EDITOR (not matched yet)
- */
+    echo "\r$i";
 
-$reg = '/^note (\d*) (.*) (?:from|in) (\S*) by (\w*)/';
+    /*
+     * What should be matched:
+     * note ID deleted from SECTION by EDITOR
+     * note ID rejected from SECTION by EDITOR
+     * note ID modified in SECTION by EDITOR
+     */
 
-    if (preg_match($reg, $subj, $d)) {
-        if ($d[2] == 'rejected and deleted')
-            $d[2] = 'rejected';
-        if ($d[2] == 'approved')
+    if (preg_match('/note (\d+) (.+) (?:from|in) (\S+) by (\w*)/S', $subj, $d)) {
+        if ($d[2] == 'approved') {
             continue;
-         
-        if(calc_time($odate)) {
-            // 'new' before $after
-            @$team['n'][$d[4]]['total']++;
-            @$team['n'][$d[4]][$d[2]]++; 
-            @$tmp['n'][$d[4]]++;
-            @$files['n'][$d[3]]++;
-        } else {
-            // 'old' after $after
-            @$team['o'][$d[4]][$d[2]]++; 
-            @$tmp['o'][$d[4]]++;
-            @$team['o'][$d[4]]['total']++; 
-            @$files['o'][$d[3]]++;
         }
-        
-        // the normal arrays
-        @$team[$d[4]]['total']++;
-        @$team[$d[4]][$d[2]]++; 
-        @$tmp[$d[4]]++;
-        @$files[$d[3]]++;
- 
-    } // end if(preg_match
-    if ($i == $last)
-        break;
-} // end for loop
 
-$dlDone = getmicrotime();
+        if ($d[2] == 'rejected and deleted') {
+            $d[2] = 'rejected';
+        }
 
-ksort($team);
-arsort($files);
-arsort($tmp);
-arsort($tmp['n']);
-arsort($tmp['o']);
-
-// SELECT all users first, check if they are in the db :: UPDATE } else { INSERT
-
-$sql = "SELECT
-            username
-        FROM
-            notes_stats
-";
-
-$result = sqlite_query($sqlite, $sql);
-
-$users = array();
-while ($users[] = sqlite_fetch_array($result, SQLITE_ASSOC)) {
-// nothing here
-}     
-
-
-// Total editor stats
-foreach ($tmp as $user => $total) {
-    if($user == 'o' or $user =='n')
-       continue;
-
-if (in_array($user, $users)) {
-// update
-$sql = "
-        UPDATE
-            notes_stats
-        SET
-            deleted = deleted + '".(isset($team[$user]['deleted']) ? $team[$user]['deleted'] : '0')."',
-            rejected = rejected + '".(isset($team[$user]['rejected']) ? $team[$user]['rejected'] : '0')."',
-            modified = modified + '".(isset($team[$user]['modified']) ? $team[$user]['modified'] : '0')."',
-            total = total + '".$total."'
-        WHERE
-            username = '" . $user . "'
-";
-} else {
-// insert       
-$sql = "
-        INSERT
-        INTO
-            notes_stats (username, deleted, rejected, modified, total)
-        VALUES
-            (
-            '".escape($user)."',
-            '".(isset($team[$user]['deleted']) ? $team[$user]['deleted'] : '0')."',
-            '".(isset($team[$user]['rejected']) ? $team[$user]['rejected'] : '0')."',
-            '".(isset($team[$user]['modified']) ? $team[$user]['modified'] : '0')."',
-            '".$total."'
-            )
-";
-}
+        $d[] = strtotime($odate);
+        $sql .= make_sql($d);
 
-sqlite_query($sqlite, $sql);
-   
-}
+    } // end if(preg_match
 
-// Last half year (with more than $minact actions counted)
-foreach ($tmp['n'] as $user => $total) {
+} // end for loop
 
-if (in_array($user, $users)) {
-// update
-$sql = "
-        UPDATE
-            notes_stats_new
-        SET
-            deleted = deleted + '".(isset($team['n'][$user]['deleted']) ? $team['n'][$user]['deleted'] : '0')."',
-            rejected = rejected + '".(isset($team['n'][$user]['rejected']) ? $team['n'][$user]['rejected'] : '0')."',
-            modified = modified + '".(isset($team['n'][$user]['modified']) ? $team['n'][$user]['modified'] : '0')."',
-            total = total + '".$total."'
-        WHERE
-            username = '" . $user . "'
-";
-} else {
-// insert       
-$sql = "
-        INSERT
-        INTO
-            notes_stats_new (username, deleted, rejected, modified, total)
-        VALUES
-            (
-            '".escape($user)."',
-            '".(isset($team['n'][$user]['deleted']) ? $team['n'][$user]['deleted'] : '0')."',
-            '".(isset($team['n'][$user]['rejected']) ? $team['n'][$user]['rejected'] : '0')."',
-            '".(isset($team['n'][$user]['modified']) ? $team['n'][$user]['modified'] : '0')."',
-            '".$total."'
-            )
-";
-}
+$sql .= "UPDATE info SET last_article=$last, build_date=$last_update;";
 
 sqlite_query($sqlite, $sql);
+sqlite_close($sqlite);
 
-}
 
-// Before the last half year (with more than $minact actions counted)
-foreach ($tmp['o'] as $user => $total) {
 
+/* Open a connection to a NTTP server */
+function nntp_connect($server, $port = 119) {
 
-if (in_array($user, $users)) {
-// update
-$sql = "
-        UPDATE
-            notes_stats_old
-        SET
-            deleted = deleted + '".(isset($team['o'][$user]['deleted']) ? $team['o'][$user]['deleted'] : '0')."',
-            rejected = rejected + '".(isset($team['o'][$user]['rejected']) ? $team['o'][$user]['rejected'] : '0')."',
-            modified = modified + '".(isset($team['o'][$user]['modified']) ? $team['o'][$user]['modified'] : '0')."',
-            total = total + '".$total."'
-        WHERE
-            username = '" . $user . "'
-";
-} else {
-// insert       
-$sql = "
-        INSERT
-        INTO
-            notes_stats_old (username, deleted, rejected, modified, total)
-        VALUES
-            (
-            '".escape($user)."',
-            '".(isset($team['o'][$user]['deleted']) ? $team['o'][$user]['deleted'] : '0')."',
-            '".(isset($team['o'][$user]['rejected']) ? $team['o'][$user]['rejected'] : '0')."',
-            '".(isset($team['o'][$user]['modified']) ? $team['o'][$user]['modified'] : '0')."',
-            '".$total."'
-            )
-";
-}
+    if (!$socket = fsockopen($server, $port, $errno, $errstr, 30)) {
+        echo "error connecting to nntp server: $errstr\n";
+        return false;
+    }
 
-sqlite_query($sqlite, $sql);
+    if (substr(fgets($socket, 1024), 0, 4) != "200 ") {
+        echo "unexpected greeting: $hello\n";
+        return false;
+    }
 
+    return $socket;
 }
 
-// SELECT all sections first, check if they are in the db :: UPDATE } else { INSERT
 
-$sql = "SELECT
-            page
-        FROM
-            notes_files
-";
-
-$result = sqlite_query($sqlite, $sql);
-
-$pages = array();
-while ($pages[] = sqlite_fetch_array($result, SQLITE_ASSOC)) {
-// nothing here
-}
+/* issue a NTTP command */
+function nntp_cmd($conn, $command, $expected) {
+    if (strlen($command) > 510){
+        die("command too long: $command");
+    }
 
-// Manual pages most active top 20
+    fputs($conn, "$command\r\n");
+    list($code,$extra) = explode(' ', fgets($conn, 1024), 2);
 
-$i = 0;
-    foreach($files as $page => $total) {
-    if($page == 'o' or $page =='n' or $page == '')
-       continue;
-       
-        $i++;
-        
-if (in_array($page, $pages)) {
-// update
-$sql = "UPDATE
-            notes_files
-        SET
-            total = total + " . (isset($total) ? $total : '0') . "
-        WHERE
-            page = '" . $page . "'
-";
-} else {        
-// insert
-$sql = "INSERT
-        INTO
-            notes_files (page, total)
-        VALUES
-            ('" . $page . "', " . $total . ")
-";
+    return $code == $expected ? $extra : false;
 }
-sqlite_query($sqlite, $sql);
-
-}       
-
-// update information
-$sql = "
-    UPDATE
-        notes_info
-    SET
-        last_article = ". $last .",
-        subjects =  subjects + " . (is_array($files) ? array_sum($files) : '0') . ",
-        build_date = " . time() . "
-";
-sqlite_query($sqlite, $sql);
-
-$scriptTime = number_format(getmicrotime() - $startTime, 3);
-$bytesSec = number_format(round($bytesRead / ($dlDone - $dlStart)));
-$bytesRead = number_format($bytesRead, 1, ',', '');
-echo "Completed in $scriptTime seconds\n";
-echo "$bytesRead bytes read (~$bytesSec bytes/sec)\n";
-
-sqlite_close($sqlite);
 
 
-function escape ($data)
-{
-    return sqlite_escape_string($data);
-}
+/* create a new DB and table schema */
+function create_db($DBFile) {
+    echo "Creating the database: $DBFile\n";
 
-function nntp_connect($server,$port=119) {
-  $s = fsockopen($server,$port,$errno,$errstr,30);
+    $sqlite = sqlite_open($DBFile, 0666);
 
-  if (!$s) {
-    echo "error connecting to nntp server: $errstr\n";
-    return false;
-  }
-  $hello = fgets($s, 1024);
-  if (substr($hello,0,4) != "200 ") {
-    echo "unexpected greeting: $hello\n";
-    return false;
-  }
-  //echo "$hello\n";
-  return $s;
-}
+    $sql = <<< SQL
+CREATE TABLE info (
+  last_article INTEGER,
+  build_date INTEGER
+);
 
-function nntp_cmd($conn,$command,$expected) {
-  if (strlen($command) > 510) die("command too long: $command");
-  fputs($conn, "$command\r\n");
-  $res = fgets($conn, 1024);
-  list($code,$extra) = explode(" ", $res, 2);
-  return $code == $expected ? $extra : false;
-}
+CREATE TABLE notes (
+  note INTEGER PRIMARY KEY,
+  action TEXT,
+  manpage TEXT,
+  who TEXT,
+  time INTEGER
+);
 
-function getmicrotime() { 
-    list($usec, $sec) = explode(" ", microtime()); 
-    return ((float)$usec + (float)$sec); 
-} 
-
-function calc_time ($before) {
-global $after;
-
-// simple caching
-if(@$in_old == true) 
-   return false;
+CREATE INDEX time ON notes (time);
+INSERT INTO info VALUES(1, 0);
+SQL;
 
-if (!is_numeric($before)) {
-    $before = strtotime($before);
+    sqlite_query($sqlite, $sql);
+    return $sqlite;
 }
 
-$afterall =  $before - (time() - $after);
 
-if($afterall > 0) {
-    // more then $after
-    $in_old = false;    
-    return true;
-} else {
-    // older then $after
-    $in_old = true;
-    return false;
+/* makes a sql insert statment from an array */
+function make_sql($array) {
+    array_shift($array);
+    return 'INSERT OR IGNORE INTO notes VALUES ("' . implode('", "', $array) . '");';
 }
 
-}
-
-
 ?>