root/dev/common/includes/class.db_cache.php @ 202

<?php

class DBConnection
{
    function DBConnection()
    {
        static $conn_id;

        if (is_resource($conn_id))
        {
            $this->id_ = $conn_id;
            return;
        }
        if (!$this->id_ = mysql_connect(DB_HOST, DB_USER, DB_PASS))
            die("Unable to connect to mysql database.");

        mysql_select_db(DB_NAME);
        $conn_id = $this->id_;
    }

    function id()
    {
        return $this->id_;
    }

    function affectedRows()
    {
        return mysql_affected_rows($this->id_);
    }
}

class DBQuery
{
    function DBQuery()
    {
        $this->executed_ = false;
        $this->_cache = array();
        $this->_cached = false;

        // this is the minimum runtime a query has to run to be
        // eligible for caching in seconds
        $this->_minruntime = 0.1;

        // maximum size of a cached result set (512kB)
        $this->_maxcachesize = 524288;
        $this->d = true;
    }

    function checkCache()
    {
        // only cache selects
        // we don't use select ... into so there is no problem
        if (strtolower(substr($this->_sql, 0, 6)) != 'select' && strtolower(substr($this->_sql, 0, 4)) != 'show')
        {
            // this is no select, update the table
            $this->markAffectedTables();
            return false;
        }

        if (file_exists(KB_CACHEDIR.'/qcache_qry_'.$this->_hash))
        {
            $this->_mtime = filemtime(KB_CACHEDIR.'/qcache_qry_'.$this->_hash);
            if ($this->isCacheValid())
            {
                return true;
            }
        }

        return false;
    }

    function parseSQL()
    {
        // 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'))
        {
            $to = strlen($text);
        }
        $parse = trim(substr($text, $from, $to-$from));

        $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];
        }
        else
        {
            // no join so it is hopefully a simple table select
            $tables[] = $parse;
        }

        $this->_usedtables = $tables;
    }

    function isCacheValid()
    {
        // check if cachefiles are stil valid

        // first, we need to get all involved tables
        $this->parseSQL();

        foreach ($this->_usedtables as $table)
        {
            $file = KB_CACHEDIR.'/qcache_tbl_'.trim($table);
            if (file_exists($file))
            {
                // if one of the tables is outdated, the query is outdated
                if ($this->_mtime < filemtime($file))
                {
                    return false;
                }
            }
        }
        return true;
    }

    function markAffectedTables()
    {
        // this function invalidates cache files for touched tables
        $text = trim(strtolower($this->_sql));
        $text = str_replace(array('ignore','`', "\r\n", "\n"), '', $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')
        {
            $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] == 'alter')
        {
            return false;
        }
        else
        {
            var_dump($ta);
            trigger_error('No suitable handler for query found.',E_USER_WARNING);
            return false;
        }

        foreach ($tables as $table)
        {
            $file = KB_CACHEDIR.'/qcache_tbl_'.$table;
            touch($file);
        }
        // refresh php's filestatcache so we dont get wrong timestamps on changed files
        clearstatcache();
    }

    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
            $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;
        $this->executed_ = true;
    }

    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 = trim($sql);
        $this->_hash = md5($this->_sql);
        $this->_cache = array();
        $this->_cached = false;

        if ($this->checkCache())
        {
            $this->loadCache();
            return true;
        }

        // we got no or no valid cache so open the connection and run the query
        $this->dbconn_ = new DBConnection;

        $t1 = strtok(microtime(), ' ') + strtok('');

        $this->resid_ = mysql_query($sql, $this->dbconn_->id());

        if ($this->resid_ == false)
        {
            if (DB_HALTONERROR === true)
            {
                echo "Database error: ".mysql_error($this->dbconn_->id())."<br/>";
                echo "SQL: ".$this->_sql."<br/>";
                exit;
            }
            else
            {
                return false;
            }
        }

        $this->exectime_ = strtok(microtime(), ' ') + strtok('') - $t1;
        $this->executed_ = true;

        if (KB_PROFILE == 2)
        {
            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)
        {
            $this->genCache();
        }

        return true;
    }

    function recordCount()
    {
        if ($this->_cached)
        {
            return count($this->_cache);
        }
        return mysql_num_rows($this->resid_);
    }

    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 (is_resource($this->resid_))
        {
            return mysql_fetch_assoc($this->resid_);
        }
        return false;
    }

    function rewind()
    {
        if ($this->_cached)
        {
            $this->_currrow = 0;
        }
        @mysql_data_seek($this->resid_, 0);
    }

    function getInsertID()
    {
        return mysql_insert_id();
    }

    function execTime()
    {
        return $this->exectime_;
    }

    function executed()
    {
        return $this->executed_;
    }

    function getErrorMsg()
    {
        $msg = $this->sql_."<br>";
        $msg .= "Query failed. ".mysql_error($this->dbconn_->id());

        return $msg;
    }
}
?>