oxdebugdb.php

Go to the documentation of this file.

<?php

class oxDebugDb
{
    private static $_aSkipSqls = array();

    public function __construct()
    {
    }

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

    protected static function _isSkipped($sSql)
    {
        if ( !count(self::$_aSkipSqls ) ) {
            $sFile = oxRegistry::getConfig()->getLogsDir() . 'oxdebugdb_skipped.sql';
            if (is_readable($sFile)) {
                $aSkip = explode('-- -- ENTRY END', file_get_contents( $sFile ));
                foreach ( $aSkip as $sQ ) {
                    if ( ( $sQ = self::_skipWhiteSpace( $sQ ) ) ) {
                        self::$_aSkipSqls[md5($sQ)] = 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();
        if (method_exists($oDb, "logSQL")) {
            $iLastDbgState = $oDb->logSQL( false );
        }
        $rs = $oDb->select( "select sql0, sql1, tracer from adodb_logsql order by created limit 5000" );
        if ($rs != false && $rs->recordCount() > 0 ) {
            $aLastRecord = 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 ( $aLastRecord && $this->_checkMess( $sSql, $aLastRecord[1] ) ) {
                    // sql0 matches, also, this is exactly following statement: MESS?
                    $aWarnings['MESS'][$sId] = true;
                    $aWarnings['MESS'][$aLastRecord[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[] = $aLastRecord = $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 );
        if (method_exists($oDb, "logSQL")) {
            $oDb->logSQL( $iLastDbgState );
        }
        return $aWarnings;
    }

    protected function _generateWarningsResult( $aInput )
    {
        $aOutput = array();
        $oDb = oxDb::getDb();
        foreach ($aInput as $fnc => $aWarnings) {
            $ids = implode(",", oxDb::getInstance()->quoteArray(array_keys($aWarnings)));
            $rs = $oDb->select("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;
        }

        $rs = oxDb::getDb( oxDb::FETCH_MODE_ASSOC )->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 ( strstr($aExplain['Extra'], 'Impossible WHERE' ) !== false ) {
            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)
    {
        $oStr = getStr();
        $sLogMsg = "\n\n\n\n\n\n-- ".date("m-d  H:i:s")." --\n\n";
        foreach ( $aWarnings as $w ) {
            $sLogMsg .= "{$w['check']}: {$w['time']} - ".$oStr->htmlentities($w['sql'])."\n\n";
            $sLogMsg .= $w['trace']."\n\n\n\n";
        }
        oxRegistry::getUtils()->writeToLog( $sLogMsg, 'oxdebugdb.txt' );
    }
}