oxdebugdb.php

Go to the documentation of this file.

<?php


class oxDebugDb
{

    private static $_aSkipSqls = array();

    protected static function _skipWhiteSpace($str){
        return str_replace(array(' ', "\t", "\r", "\n"), '', $str);
    }

    protected static function _isSkipped($sSql){
        if (!count(self::$_aSkipSqls)) {
            $file = file_get_contents(realpath(dirname(__FILE__).'/..').'/oxdebugdb_skipped.sql');
            $m = explode('-- -- ENTRY END', $file);
            foreach ($m as $n) {
                $n = self::_skipWhiteSpace($n);
                if ($n)
                    self::$_aSkipSqls[md5($n)] = true;
            }
        }
        $checkTpl = md5(self::_skipWhiteSpace(self::getSqlTemplate($sSql)));
        $check = md5(self::_skipWhiteSpace($sSql));
        return self::$_aSkipSqls[$check] || self::$_aSkipSqls[$checkTpl];
    }

    public function getWarnings()
    {
        $aWarnings = array();
        $aHistory = array();
        $oDb = oxDb::getDb();
        $_lastDbgState = $oDb->LogSQL( false );
        $rs = $oDb->Execute("select sql0, sql1, tracer from adodb_logsql order by created limit 5000");
        if ($rs != false && $rs->recordCount() > 0) {
            $_lastRecord = null;
            while (!$rs->EOF) {
                $sId  = $rs->fields[0];
                $sSql = $rs->fields[1];

                if (!self::_isSkipped($sSql)) {
                    if ($this->_checkMissingKeys($sSql)) {
                        $aWarnings['MissingKeys'][$sId] = true;
// debug: echo "<li> <pre>".self::getSqlTemplate($sSql)." </pre><br>";
                    }
                }

                // multiple executed single statements
                if ($_lastRecord && $this->_checkMess($sSql,$_lastRecord[1])) {
                    // sql0 matches, also, this is exactly following statement: MESS?
                    $aWarnings['MESS'][$sId] = true;
                    $aWarnings['MESS'][$_lastRecord[0]] = true;
                }

                foreach ($aHistory as $aHistItem) {
                    if ($this->_checkMess($sSql,$aHistItem[1])) {
                        // sql0 matches, also, this is exactly following statement: MESS?
                        $aWarnings['MESS_ALL'][$sId] = true;
                        $aWarnings['MESS_ALL'][$aHistItem[0]] = true;
                    }
                }

                $aHistory[] = $_lastRecord = $rs->fields;
/*
                if (preg_match('/select[^\*]*(?<!(from))\*.*?(?<!(from))from/im', $sSql)) {
                    $aWarnings['Select fields not strict'][$sId] = true;
                }*/
                $rs->moveNext();
            }
        }
        $aWarnings = $this->_generateWarningsResult($aWarnings);
        $this->_logToFile($aWarnings);
        $oDb->LogSQL( $_lastDbgState );
        return $aWarnings;
    }

    protected function _generateWarningsResult($aInput)
    {
        $aOutput = array();
        $oDb = oxDb::getDb();
        foreach ($aInput as $fnc => $aWarnings) {
            $ids = implode("','", array_keys($aWarnings));
            $rs = $oDb->Execute("select sql1, timer, tracer from adodb_logsql where sql0 in ('$ids')");
            if ($rs != false && $rs->recordCount() > 0) {
                while (!$rs->EOF) {
                    $aOutputEntry = array();
                    $aOutputEntry['check'] = $fnc;
                    $aOutputEntry['sql'] = $rs->fields[0];
                    $aOutputEntry['time'] = $rs->fields[1];
                    $aOutputEntry['trace'] = $rs->fields[2];
                    $aOutput[] = $aOutputEntry;
                    $rs->moveNext();
                }
            }
        }
        return $aOutput;
    }

    protected function _checkMissingKeys($sSql)
    {
        if (strpos(strtolower(trim($sSql)), 'select ') !== 0)
            return false;

        $oDb = oxDb::getDb(true);
        $rs = $oDb->Execute("explain $sSql");
        if ($rs != false && $rs->recordCount() > 0) {
            while (!$rs->EOF) {
                if ($this->_missingKeysChecker($rs->fields))
                    return true;
                $rs->moveNext();
            }
        }
        return false;
    }

    private function _missingKeysChecker($aExplain)
    {

        if ($aExplain['type'] == 'system') {
            return false;
        }

        if ($aExplain['key'] === null)
            return true;

        if (strpos($aExplain['type'], 'range'))
            return true;
        if (strpos($aExplain['type'], 'index'))
            return true;
        if (strpos($aExplain['type'], 'ALL'))
            return true;

        if (strpos($aExplain['Extra'], 'filesort')) {
            if (strpos($aExplain['ref'], 'const') === false)
                return true;
        }
        if (strpos($aExplain['Extra'], 'temporary'))
            return true;

        return false;
    }

    protected function _checkMess($s1, $s2)
    {
        if (strpos(strtolower(trim($s1)), 'select ') !== 0)
            return false;
        if (strpos(strtolower(trim($s2)), 'select ') !== 0)
            return false;

        // strip from values
        $s1 = self::getSqlTemplate($s1);
        $s2 = self::getSqlTemplate($s2);

        if (!strcmp($s1, $s2)) {
            return true;
        }

        return false;
    }

    protected static function getSqlTemplate($sSql) {
        $sSql = preg_replace("/'.*?(?<!\\\\)'/", "'#VALUE#'", $sSql);
        $sSql = preg_replace('/".*?(?<!\\\\)"/', '"#VALUE#"', $sSql);
        $sSql = preg_replace('/[0-9]/', '#NUMVALUE#', $sSql);
        return $sSql;
    }



    protected function _logToFile($aWarnings)
    {
        $s = "\n\n\n\n\n\n-- ".date("m-d  H:i:s")." --\n\n";
        foreach ($aWarnings as $w)
        {
            $s .= "{$w['check']}: {$w['time']} - ".htmlentities($w['sql'])."\n\n";
            $s .= $w['trace']."\n\n\n\n";
        }
        $f = fopen(realpath(dirname(__FILE__).'/..').'/oxdebugdb.txt', "a+");
        if (!$f)
            return;
        fwrite($f, $s);
        fclose($f);
    }
}

---