Changeset 1262

Timestamp:

07/21/07 02:40:43 (5 years ago)

Author:

benoitg

Message:

• AnonymisedDataExport?.php: Work around the huge memory requirements this report used to have.

Location:

trunk/wifidog-auth

Files:

• CHANGELOG (modified) (1 diff)
• wifidog/classes/AbstractDb.php (modified) (1 diff)
• wifidog/classes/StatisticReport/AnonymisedDataExport.php (modified) (3 diffs)

trunk/wifidog-auth/CHANGELOG

@@ -1 +1 @@
 # $Id$
-
+2007-07-21 Benoit Grégoire  <bock@step.polymtl.ca>
+        * AnonymisedDataExport.php:  Work around the huge memory requirements this report used to have.
+        
 2007-07-20 Benoit Grégoire  <bock@step.polymtl.ca>
         * At long last, implement #9:  Automatic new node creation.  When attempting to login from an unknown node, the user (if he has the permissions) will be prompted to create the node, or "steal" en existing one (for hardware swaps).

trunk/wifidog-auth/wifidog/classes/AbstractDb.php

@@ -192 +192 @@
     }
 
+        /**
+     * Execute an SQL query and returns the RAW postgresql result handle or throws an error
+     * This is NOT for general use, as it breaks abstraction
+     * @param $sql SQL query to execute
+     * @param $resultSet The postgresql result handle
+     * @param $debug When set to true the function will spit out debug informations
+     * @return TRUE indicated the query went fine, FALSE something went wrong
+     */
+    function execSqlRaw($sql, & $resultSet, $debug = false)
+    {
+        // Get a connection handle
+        $connection = $this->connect(NULL);
+
+        // In debug mode spit out the SQL query
+        if ($debug == TRUE)
+        {
+            // Header
+            echo "<hr/><p/>execSql() : "._("SQL Query")."<br/>\n<pre>{$sql}</pre></p>\n";
+
+            // Prepend EXPLAIN statement to the SQL query
+            $result = @pg_query($connection, "EXPLAIN ".$sql);
+            if($result) {
+                echo "<p>"._("Query plan")." :<br/>\n";
+                $plan_array = pg_fetch_all($result);
+
+                foreach ($plan_array as $plan_line)
+                echo $plan_line['QUERY PLAN']."<br/>\n";
+                echo "</p>\n";
+            }
+
+        }
+
+        // Start the clockwatch
+        $sql_starttime = microtime();
+        $result = @pg_query($connection, $sql);
+        $sql_endtime = microtime();
+
+        $sql_timetaken = $this->logQueries($sql, 'SELECT', $sql_starttime, $sql_endtime);
+
+        if ($debug == TRUE)
+        echo "<p>".sprintf(_("Elapsed time for query execution : %.6f second(s)"), $sql_timetaken)."</p>\n";
+
+        if ($result == FALSE)
+        {
+            echo "<p>execSql() : "._("An error occured while executing the following SQL query")." :<br>{$sql}</p>";
+            echo "<p>"._("Error message")." : <br/>".pg_last_error($connection)."</p>";
+            echo "<p>"._("Backtrace:")."</p>";
+            echo "<pre>";
+            $btArray = debug_backtrace();
+            foreach($btArray as $index=>$bt) {
+                printf("#%d %s(%d): %s%s%s()\n", $index, $bt['file'], $bt['line'], $bt['class'], $bt['type'], $bt['function']);
+            }
+            echo "</pre>";
+            $resultSet = NULL;
+            $return_value = FALSE;
+        }
+        else
+        if (pg_num_rows($result) == 0)
+        {
+            $resultSet = NULL;
+            $return_value = TRUE;
+        }
+        else
+        {
+            $resultSet = $result;
+            $return_value = TRUE;
+        }
+        return $return_value;
+    }
+    
     /* Logs a sql query for profiling purposes */
     function logQueries($sql, $type, $sql_starttime, $sql_endtime)

trunk/wifidog-auth/wifidog/classes/StatisticReport/AnonymisedDataExport.php

@@ -99 +99 @@
         else
         {
+            header('Content-Type: application/octet-stream');
+            header('Content-Disposition: inline; filename="anonymised_data.sql"');
+            header("Content-Transfer-Encoding: binary");
+
             $html  .= <<<EOT
             CREATE TABLE connections_anonymised
@@ -113 +117 @@
 EOT;
 $html .= "\n";
+            echo $html;
             $distinguish_users_by = $this->stats->getDistinguishUsersBy();
 
@@ -118 +123 @@
 
             $sql = "$candidate_connections_sql ORDER BY timestamp_in DESC";
-            $db->execSql($sql, $rows, false);
-            foreach ($rows as $row)
-            {
-                $keys = null;
-                $values = null;
-                $first = true;
-                foreach ($row as $key=>$value)
+            $db->execSqlRaw($sql, $resultHandle, false);
+            if($resultHandle) {
+                while($row=pg_fetch_array($resultHandle,null,PGSQL_ASSOC))
                 {
-                    if($key == 'user_id' || $key == 'node_id' || $key == 'conn_id' || $key == 'user_mac' ) {
-                        $value = "'".$this->getNonRepeatableHash($value)."'";
+
+                    $keys = null;
+                    $values = null;
+                    $first = true;
+                    foreach ($row as $key=>$value)
+                    {
+                        if($key == 'user_id' || $key == 'node_id' || $key == 'conn_id' || $key == 'user_mac' ) {
+                            $value = "'".$this->getNonRepeatableHash($value)."'";
+                        }
+                        else if ($key == 'timestamp_out' && empty ($value)) {
+                            $value = 'NULL';
+                        }
+                        else {
+                            $value = "'$value'";
+                        }
+                        if(!$first) {
+                            $keys .= ', ';
+                            $values .= ', ';
+                        }
+                        else {
+                            $first = false;
+                        }
+                        $keys .= $key;
+                        $values .= $value;
                     }
-                    else if ($key == 'timestamp_out' && empty ($value)) {
-                        $value = 'NULL';
-                    }
-                    else {
-                        $value = "'$value'";
-                    }
-                    if(!$first) {
-                        $keys .= ', ';
-                        $values .= ', ';
-                    }
-                    else {
-                        $first = false;
-                    }
-                    $keys .= $key;
-                    $values .= $value;
+                    //fwrite($temp, "INSERT INTO connections_anonymised ($keys) VALUES ($values);\n");
+                    echo "INSERT INTO connections_anonymised ($keys) VALUES ($values);\n";
                 }
-                $html .= "INSERT INTO connections_anonymised ($keys) VALUES ($values);\n";
-                //$html .= "<br/>\n";
             }
+            exit;
         }
-        header('Content-Type: text/plain');
-        header('Content-Disposition: inline; filename="anonymised_data.sql"');
-        header("Content-Transfer-Encoding: binary");
-        echo $html;
-        exit;
+        return $html;
     }