/common/includes/class.dbcachedquery.php
PHP | 468 lines | 298 code | 46 blank | 124 comment | 71 complexity | c95c73e057edb1bc01e55cdc983b54d2 MD5 | raw file
Possible License(s): AGPL-1.0, LGPL-2.1
- <?php
- /**
- * mysqli file-cached query class. Manages SQL queries to a MySQL DB using mysqli.
- * @package EDK
- */
- class DBCachedQuery extends DBBaseQuery
- {
- /**
- * @var float
- * this is the minimum runtime a query has to run to be
- * eligible for caching in seconds
- */
- protected static $minruntime = 0.1;
- /** @var float */
- protected static $maxmem = null;
- /**
- * @var integer
- * maximum size of a cached result set (512kB)
- */
- protected static $maxcachesize = 524288;
- /** @var string */
- protected static $location = "SQL";
- /** @var integer */
- protected static $maxage = 10800;
-
- /** @var array */
- protected $cache = array();
- /** @var array */
- protected $usedtables = array();
- /** @var boolean */
- protected $cached = false;
- /** @var boolean */
- protected $nocache = false;
- /** @var string */
- protected $sql = '';
- /** @var integer */
- protected $mtime = 0;
- /**
- * @var integer
- * The current row of the cached result.
- */
- protected $currrow = 0;
- /** @var MySQLi_Result|boolean */
- protected $resid = null;
- /** @var CacheHandlerHashed */
- protected static $cachehandler = null;
- /** @var array */
- protected static $baseTables = null;
-
- /**
- * Set up a mysqli cached query object with default values.
- *
- * @param boolean $nocache true to retrieve results directly from the db.
- */
- function DBCachedQuery($nocache = false)
- {
- $this->nocache = $nocache;
-
- self::$cachehandler = new CacheHandlerHashed();
-
- if(is_null(self::$maxmem))
- {
- $tmp = @ini_get('memory_limit');
- $tmp = @str_replace('M', '000000', $tmp) * 0.8;
- self::$maxmem = @intval(str_replace('G', '000000000', $tmp) * 0.8);
- if(!self::$maxmem) self::$maxmem = 128000000;
- }
- }
- /**
- * Check if this query has been cached and the cache valid.
- *
- * @return boolean true if this query has been cached and the cache is valid.
- */
- protected function checkCache()
- {
- // only cache selects
- // we don't use select ... into so there is no problem
- $this->sql = str_replace(array("\r\n", "\n"), ' ', $this->sql);
- if (strtolower(substr($this->sql, 0, 6)) != 'select' && strtolower(substr($this->sql, 0, 4)) != 'show')
- {
- // this is no select, update the table
- self::markAffectedTables($this->sql);
- return false;
- }
-
- if($this->nocache) return false;
- if (self::$cachehandler->exists($this->sql, self::$location))
- {
- $this->mtime = self::$cachehandler->age($this->sql, self::$location);
- if ($this->mtime > self::$maxage ) return false;
- if ($this->isCacheValid()) return true;
- }
- return false;
- }
-
- /**
- * Extract all tables affected by a database modification.
- */
- protected function parseSQL($sql)
- {
- // Check list of tables daily.
- $daily = 86400;
- if(is_null(self::$baseTables)
- && ( self::$cachehandler->age('SHOW TABLES', self::$location) > $daily
- || !self::$cachehandler->exists('SHOW TABLES', self::$location)))
- {
- // we have no valid cache so open the connection and run the query
- if(is_null(self::$dbconn))self::$dbconn = new DBConnection();
-
- $t1 = microtime(true);
-
- $resid = mysqli_query(self::$dbconn->id(), 'SHOW TABLES');
-
- if (!$resid || self::$dbconn->id()->errno)
- {
- // Clear the cache to prevent errors spreading.
- DBDebug::killCache();
- if(defined('KB_PROFILE'))
- {
- DBDebug::recordError("Database error: ".self::$dbconn->id()->error);
- DBDebug::recordError("SQL: ".$this->sql);
- }
- throw new Exception( "SQL error (".self::$dbconn->id()->error );
- }
-
- $this->exectime = microtime(true) - $t1;
- self::$totalexectime += $this->exectime;
-
- if(defined('KB_PROFILE')) DBDebug::profile($sql, $this->exectime);
-
- $this->queryCount(true);
-
- $bsize = 0;
- while ($row = $resid->fetch_assoc())
- {
- $table = strtolower(array_shift($row));
- self::$baseTables[$table] = $table;
- }
- // write data to cache
- self::$cachehandler->put('SHOW TABLES', self::$baseTables, self::$location, $daily);
- }
- else if(is_null(self::$baseTables)) self::$baseTables = self::$cachehandler->get('SHOW TABLES', self::$location);
-
-
- // gets all involved tables for a select statement
- $sql = strtolower($sql).' ';
-
- $regex = '/'.implode('|', self::$baseTables).'/';
- $matches = array();
- if(!preg_match_all($regex, $sql, $matches)) $this->usedtables = array();
- else $this->usedtables = $matches[0];
-
- return '';
- }
- /**
- * Check if the cached query is valid.
- *
- * Determines whether the tables used by a query have been modified
- * since the query was cached.
- *
- * @return boolean
- */
- protected function isCacheValid()
- {
- // check if cachefiles are still valid
- $this->usedtables = array();
- // first, we need to get all involved tables
- $this->parseSQL($this->sql);
-
- foreach ($this->usedtables as $table)
- {
- $file = 'qcache_tbl_'.trim($table);
-
- if (self::$cachehandler->exists($file, self::$location))
- {
- // if one of the tables is outdated, the query is outdated
- $age = self::$cachehandler->age($file, self::$location);
-
- if ($this->mtime >= $age) return false;
- }
- }
- return true;
- }
- /**
- * Marks all tables affected by a database modification
- */
- public static function markAffectedTables($sql = null)
- {
- if(is_null($sql)) return true;
- // this function invalidates cache files for touched tables
- $text = trim(strtolower($sql));
- $text = str_replace(array('ignore','`', "\r\n", "\n"), '', $text);
- $text = str_replace('(', ' (', $text);
- $ta = preg_split('/\s/', $text, 0, PREG_SPLIT_NO_EMPTY);
-
- // check for sql keywords and get the table from the appropriate position
- $tables = array();
- if ($ta[0] == 'update')
- {
- $tables[] = $ta[1];
- }
- elseif ($ta[0] == 'insert')
- {
- $tables[] = $ta[2];
- }
- elseif ($ta[0] == 'replace')
- {
- $tables[] = $ta[2];
- }
- elseif ($ta[0] == 'delete')
- {
- $tables[] = $ta[2];
- }elseif ($ta[0] == 'drop')
- {
- $tables[] = $ta[2];
- }
- elseif ($ta[0] == 'alter')
- {
- if ($ta[1] == 'ignore') $tables[] = $ta[3];
- else $tables[] = $ta[2];
- }
- elseif ($ta[0] == 'create')
- {
- return false;
- }
- elseif ($ta[0] == 'truncate')
- {
- if($ta[1] == 'table') $tables[] = $ta[2];
- else $tables[] = $ta[1];
- }
- elseif ($ta[0] == 'lock')
- {
- return false;
- }
- elseif ($ta[0] == 'unlock')
- {
- return false;
- }
- elseif ($ta[0] == 'set')
- {
- return false;
- }
- else
- {
- trigger_error('No suitable handler for query found. "'.$ta[0].'"',E_USER_NOTICE);
- return false;
- }
-
- foreach ($tables as $table)
- {
- self::$cachehandler->put('qcache_tbl_'.$table, time(), self::$location, 0);
- }
- }
- /**
- * Generate the query cache.
- *
- * Serialise a query and write to file.
- *
- * @return boolean
- */
- protected function genCache()
- {
-
- // 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
- // If we're running out of memory then run uncached.
- $bsize += strlen(join('', $row));
- if ($bsize > self::$maxcachesize || self::$maxmem < memory_get_usage())
- {
- unset($this->cache);
- $this->cache = array();
- $this->cached = false;
- $this->rewind();
- return false;
- }
- }
-
- // write data to cache
- self::$cachehandler->put($this->sql, $this->cache, self::$location, self::$maxage);
-
- $this->cached = true;
- $this->currrow = 0;
- $this->executed = true;
- }
- /**
- * Read a cached query from file.
- */
- protected function loadCache()
- {
- // loads the cachefile into the memory
- $this->cache = self::$cachehandler->get($this->sql, self::$location);
-
- $this->cached = true;
- $this->currrow = 0;
- $this->executed = true;
- }
-
- /**
- * Execute an SQL string.
- *
- * @param string $sql
- * @return boolean false on error or true if successful.
- */
- function execute($sql)
- {
- $t1 = microtime(true);
-
- $this->sql = $sql;
- unset($this->cache);
- $this->cache = array();
- $this->cached = false;
-
- if ($this->checkCache())
- {
- $this->loadCache();
- $this->queryCachedCount(true);
- $this->exectime = microtime(true) - $t1;
- self::$totalexectime += $this->exectime;
- return true;
- }
- // we have no valid cache so open the connection and run the query
- if(is_null(self::$dbconn))self::$dbconn = new DBConnection();
-
- $this->resid = mysqli_query(self::$dbconn->id(), $this->sql);
-
- if (!$this->resid || self::$dbconn->id()->errno)
- {
- // Clear the cache to prevent errors spreading.
- DBDebug::killCache();
- if(defined('KB_PROFILE'))
- {
- DBDebug::recordError("Database error: ".self::$dbconn->id()->error);
- DBDebug::recordError("SQL: ".$this->sql);
- }
- throw new Exception( "SQL Execution error: " . self::$dbconn->id()->error . " Query: " . $this->sql );
- }
-
- $this->exectime = microtime(true) - $t1;
- self::$totalexectime += $this->exectime;
- $this->executed = true;
-
- if(defined('KB_PROFILE')) DBDebug::profile($sql, $this->exectime);
-
- // if the query was too slow we'll fetch all rows and run it cached
- if ($this->exectime > self::$minruntime)
- {
-
- $this->genCache();
- }
-
- $this->queryCount(true);
- return true;
- }
-
- /**
- * Return the number of rows returned by the last query.
- *
- * @return integer|boolean
- */
- function recordCount()
- {
- if ($this->cached)
- {
- return count($this->cache);
- }
- elseif ($this->resid)
- {
- return $this->resid->num_rows;
- }
- return false;
- }
-
- /**
- * Return the next row of results from the last query.
- *
- * @return array
- */
- function getRow()
- {
- 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)
- {
- return $this->resid->fetch_assoc();
- }
- return false;
- }
-
- /**
- * Reset list of results to return the first row from the last query.
- */
- function rewind()
- {
- if ($this->cached)
- {
- $this->currrow = 0;
- }
- if(!is_null($this->resid)) {
- @mysqli_data_seek($this->resid, 0);
- }
- }
-
- /**
- * Return the most recent error message for the DB connection.
- */
- function getErrorMsg()
- {
- $msg = $this->sql."<br>";
- $msg .= "Query failed. ".mysqli_error(self::$dbconn->id());
-
- return $msg;
- }
-
- /**
- * Set the autocommit status.
- *
- * The default of true commits after every query.
- * If set to false the queries will not be commited until autocommit is set
- * to true.
- * @param boolean$commit The new autocommit status.
- * @return boolean true on success and false on failure.
- */
- function autocommit($commit = true)
- {
- if(defined('KB_PROFILE') && KB_PROFILE == 3)
- {
- if(!$commit) DBDebug::recordError("Transaction started.");
- else DBDebug::recordError("Transaction ended.");
- }
-
- if(!self::$dbconn) self::$dbconn = new DBConnection();
- return self::$dbconn->id()->autocommit($commit);
- }
-
- /**
- * Rollback all queries in the current transaction.
- *
- * @return boolean true on success.
- */
- function rollback()
- {
- // if there's no connection to the db then there's nothing to roll back
- if(!self::$dbconn) {
- return true;
- }
- return self::$dbconn->id()->rollback();
- }
- }