Changeset 184

Timestamp:

02/01/07 18:07:42 (13 years ago)

Author:

exi

Message:

Fix for ticket:39 in class.db_cache.php.
Several additions to the parser and cache loader to support more sql statements.

Files:

• dev/common/class.db_cache.php (modified) (14 diffs)

dev/common/class.db_cache.php

@@ -33 +33 @@
         // eligible for caching in seconds
         $this->_minruntime = 0.1;
+
+        // maximum size of a cached result set (512kB)
+        $this->_maxcachesize = 524288;
     }
 
@@ -62 +65 @@
         // gets all involved tables for a select statement
         $text = strtolower($this->_sql).' ';
+
+        // we try to get the text from 'from' to 'where' because all involved
+        // tables are declared in that part
         $from = strpos($text, 'from')+5;
         if (!$to = strpos($text, 'where'))
@@ -70 +76 @@
 
         $tables = array();
+        if (strpos($parse, ',') !== false)
+        {
+            // , is a synonym for join so we'll replace them
+            $parse = str_replace(',', ' join ', $parse);
+        }
         if (strpos($parse, 'join'))
         {
+            // if this query is a join we parse it with regexp to get all tables
             preg_match_all('/join (.*?) /', $parse, $match);
             $tables = $match[1];
@@ -77 +89 @@
         else
         {
+            // no join so it is hopefully a simple table select
             $tables[] = $parse;
         }
@@ -85 +98 @@
     function isCacheValid()
     {
-        // check if one of the tables needs to be updated
+        // check if cachefiles are stil valid
 
         // first, we need to get all involved tables
@@ -109 +122 @@
         // this function invalidates cache files for touched tables
         $text = trim(strtolower($this->_sql));
-        $ta = explode(' ', $text);
-
+        $text = str_replace(array('ignore','`'), '', $text);
+        $ta = preg_split('/ /', $text, 0, PREG_SPLIT_NO_EMPTY);
+
+        // check for sql keywords and get the table from the appropriate position
         $tables = array();
         if ($ta[0] == 'update')
@@ -140 +155 @@
             touch($file);
         }
+        // refresh php's filestatcache so we dont get wrong timestamps on changed files
+        clearstatcache();
     }
 
@@ -146 +163 @@
         // this function fetches all rows and writes the data into a textfile
 
+        // don't attemp to cache updates!
+        if (strtolower(substr($this->_sql, 0, 6)) != 'select' && strtolower(substr($this->_sql, 0, 4)) != 'show')
+        {
+            return false;
+        }
+
+        $bsize = 0;
         while ($row = $this->getRow())
         {
             $this->_cache[] = $row;
+
+            // if the bytesize of the table exceeds the limit we'll abort
+            // the cache generation and leave this query unbuffered
+            $bsize += join('', $row);
+            if ($bsize > $this->_maxcachesize)
+            {
+                $this->_cache[] = array();
+                $this->_cached = false;
+                $this->rewind();
+                return false;
+            }
         }
 
         // write data into textfile
         file_put_contents(KB_CACHEDIR.'/qcache_qry_'.$this->_hash, serialize($this->_cache));
-
-        $this->_cached = true;
-        $this->_currrow = 0;
-    }
-
-    function loadCache()
-    {
-        // loads the cachefile into the memory
-        $this->_cache = unserialize(file_get_contents(KB_CACHEDIR.'/qcache_qry_'.$this->_hash));
 
         $this->_cached = true;
@@ -168 +194 @@
     }
 
+    function loadCache()
+    {
+        // loads the cachefile into the memory
+        $this->_cache = unserialize(file_get_contents(KB_CACHEDIR.'/qcache_qry_'.$this->_hash));
+
+        $this->_cached = true;
+        $this->_currrow = 0;
+        $this->executed_ = true;
+    }
+
     function execute($sql)
     {
-        $this->_sql = $sql;
-        $this->_hash = md5($sql);
+        $this->_sql = trim($sql);
+        $this->_hash = md5($this->_sql);
 
         if ($this->checkCache())
@@ -178 +214 @@
             return true;
         }
+
+        // we got no or no valid cache so open the connection and run the query
         $this->dbconn_ = new DBConnection;
 
@@ -186 +224 @@
         if ($this->resid_ == false)
         {
-            if (defined('DB_HALTONERROR') && DB_HALTONERROR)
-            {
-                echo "Database error: " . mysql_error($this->dbconn_->id()) . "<br>";
-                echo "SQL: " . $sql . "<br>";
+            if (DB_HALTONERROR === true)
+            {
+                echo "Database error: ".mysql_error($this->dbconn_->id())."<br/>";
+                echo "SQL: ".$this->_sql."<br/>";
                 exit;
             }
@@ -203 +241 @@
         if (KB_PROFILE == 2)
         {
-            file_put_contents('/tmp/profile.lst', $sql . "\nExecution time: " . $this->exectime_ . "\n", FILE_APPEND);
-        }
-
+            file_put_contents('/tmp/profile.lst', $sql."\nExecution time: ".$this->exectime_."\n", FILE_APPEND);
+        }
+
+        // if the query was too slow we'll fetch all rows and run it cached
         if ($this->exectime_ > $this->_minruntime)
         {
@@ -227 +266 @@
         if ($this->_cached)
         {
+            if (!isset($this->_cache[$this->_currrow]))
+            {
+                return false;
+            }
             // return the current row and increase the pointer by one
             return $this->_cache[$this->_currrow++];
         }
-        if ($this->resid_)
+        if (is_resource($this->resid_))
         {
             return mysql_fetch_assoc($this->resid_);
@@ -263 +306 @@
     function getErrorMsg()
     {
-        $msg = $this->sql_ . "<br>";
-        $msg .= "Query failed. " . mysql_error($this->dbconn_->id());
+        $msg = $this->sql_."<br>";
+        $msg .= "Query failed. ".mysql_error($this->dbconn_->id());
 
         return $msg;