REV 183 + db_cache.php

This is the archive to keep the actual forum clean so we can use it as some kind of todo-list.
Post Reply
ralle030583
Padavan
Posts: 219
Joined: Sat Jun 14, 2008 20:24
Location: Germany
Contact:

REV 183 + db_cache.php

Post by ralle030583 » Mon Jan 29, 2007 10:34

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /XXX/common/class.db_cache.php on line 238

get this error when fetching other KB or try to delete a Killmail..

edit: feetching works and deleting too but the warning is there..

P.S: When does the query cache refreshs?

User avatar
exi
Administrator
Posts: 88
Joined: Sat Jun 14, 2008 20:06
Location: Krefeld, Germany
Contact:

Post by exi » Thu Feb 01, 2007 15:52

Ok guys, i'm at "work" so i'll post something here which might work but maybe not (got no access to ftp, ssh or svn from here).
So you should check it for errors and stuff if you gonna use it.

[edit] updated for the mapgen bug

Code: Select all

&lt?php

class DBConnection
{
    function DBConnection()
    {
        if (!$this->id_ = mysql_pconnect(DB_HOST, DB_USER, DB_PASS))
            die("Unable to connect to mysql database.");

        mysql_select_db(DB_NAME);
    }

    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;
    }

    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, '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 one 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_'.$table;
            if (file_exists($file))
            {
                // if one of the tables is outdated, the query is outdated
                if ($this->_mtime _sql));
        $ta = explode(' ', $text);

        // 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];
        }
        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);

        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())."";
                echo "SQL: ".$this->_sql."";
                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_."";
        $msg .= "Query failed. ".mysql_error($this->dbconn_->id());

        return $msg;
    }
}
?>

Post Reply

Return to “Fixed/Old Bugs”

Who is online

Users browsing this forum: No registered users and 1 guest