oxdebugdb.php

Go to the documentation of this file.
00001 <?php
00002 
00006 class oxDebugDb
00007 {
00013     private static $_aSkipSqls = array();
00014 
00020     public function __construct()
00021     {
00022     }
00023 
00031     protected static function _skipWhiteSpace( $sStr )
00032     {
00033         return str_replace( array( ' ', "\t", "\r", "\n"), '', $sStr );
00034     }
00035 
00043     protected static function _isSkipped($sSql)
00044     {
00045         if ( !count(self::$_aSkipSqls ) ) {
00046             $sFile = oxRegistry::getConfig()->getLogsDir() . 'oxdebugdb_skipped.sql';
00047             if (is_readable($sFile)) {
00048                 $aSkip = explode('-- -- ENTRY END', file_get_contents( $sFile ));
00049                 foreach ( $aSkip as $sQ ) {
00050                     if ( ( $sQ = self::_skipWhiteSpace( $sQ ) ) ) {
00051                         self::$_aSkipSqls[md5($sQ)] = true;
00052                     }
00053                 }
00054             }
00055         }
00056         $checkTpl = md5(self::_skipWhiteSpace(self::_getSqlTemplate($sSql)));
00057         $check = md5(self::_skipWhiteSpace($sSql));
00058         return self::$_aSkipSqls[$check] || self::$_aSkipSqls[$checkTpl];
00059     }
00060 
00066     public function getWarnings()
00067     {
00068         $aWarnings = array();
00069         $aHistory = array();
00070         $oDb = oxDb::getDb();
00071         if (method_exists($oDb, "logSQL")) {
00072             $iLastDbgState = $oDb->logSQL( false );
00073         }
00074         $rs = $oDb->select( "select sql0, sql1, tracer from adodb_logsql order by created limit 5000" );
00075         if ($rs != false && $rs->recordCount() > 0 ) {
00076             $aLastRecord = null;
00077             while ( !$rs->EOF ) {
00078                 $sId  = $rs->fields[0];
00079                 $sSql = $rs->fields[1];
00080 
00081                 if (!self::_isSkipped($sSql)) {
00082                     if ($this->_checkMissingKeys($sSql)) {
00083                         $aWarnings['MissingKeys'][$sId] = true;
00084                         // debug: echo "<li> <pre>".self::_getSqlTemplate($sSql)." </pre><br>";
00085                     }
00086                 }
00087 
00088                 // multiple executed single statements
00089                 if ( $aLastRecord && $this->_checkMess( $sSql, $aLastRecord[1] ) ) {
00090                     // sql0 matches, also, this is exactly following statement: MESS?
00091                     $aWarnings['MESS'][$sId] = true;
00092                     $aWarnings['MESS'][$aLastRecord[0]] = true;
00093                 }
00094 
00095                 foreach ($aHistory as $aHistItem) {
00096                     if ( $this->_checkMess( $sSql, $aHistItem[1] ) ) {
00097                         // sql0 matches, also, this is exactly following statement: MESS?
00098                         $aWarnings['MESS_ALL'][$sId] = true;
00099                         $aWarnings['MESS_ALL'][$aHistItem[0]] = true;
00100                     }
00101                 }
00102 
00103                 $aHistory[] = $aLastRecord = $rs->fields;
00104                 /*
00105                 if (preg_match('/select[^\*]*(?<!(from))\*.*?(?<!(from))from/im', $sSql)) {
00106                     $aWarnings['Select fields not strict'][$sId] = true;
00107                 }*/
00108                 $rs->moveNext();
00109             }
00110         }
00111         $aWarnings = $this->_generateWarningsResult($aWarnings);
00112         $this->_logToFile( $aWarnings );
00113         if (method_exists($oDb, "logSQL")) {
00114             $oDb->logSQL( $iLastDbgState );
00115         }
00116         return $aWarnings;
00117     }
00118 
00126     protected function _generateWarningsResult( $aInput )
00127     {
00128         $aOutput = array();
00129         $oDb = oxDb::getDb();
00130         foreach ($aInput as $fnc => $aWarnings) {
00131             $ids = implode(",", oxDb::getInstance()->quoteArray(array_keys($aWarnings)));
00132             $rs = $oDb->select("select sql1, timer, tracer from adodb_logsql where sql0 in ($ids)");
00133             if ($rs != false && $rs->recordCount() > 0) {
00134                 while (!$rs->EOF) {
00135                     $aOutputEntry = array();
00136                     $aOutputEntry['check'] = $fnc;
00137                     $aOutputEntry['sql'] = $rs->fields[0];
00138                     $aOutputEntry['time'] = $rs->fields[1];
00139                     $aOutputEntry['trace'] = $rs->fields[2];
00140                     $aOutput[] = $aOutputEntry;
00141                     $rs->moveNext();
00142                 }
00143             }
00144         }
00145         return $aOutput;
00146     }
00147 
00156     protected function _checkMissingKeys( $sSql )
00157     {
00158         if ( strpos( strtolower( trim( $sSql ) ), 'select ' ) !== 0 ) {
00159             return false;
00160         }
00161 
00162         $rs = oxDb::getDb( oxDb::FETCH_MODE_ASSOC )->execute( "explain $sSql" );
00163         if ( $rs != false && $rs->recordCount() > 0 ) {
00164             while (!$rs->EOF) {
00165                 if ( $this->_missingKeysChecker( $rs->fields ) ) {
00166                     return true;
00167                 }
00168                 $rs->moveNext();
00169             }
00170         }
00171         return false;
00172     }
00173 
00182     private function _missingKeysChecker($aExplain)
00183     {
00184         if ( $aExplain['type'] == 'system' ) {
00185             return false;
00186         }
00187 
00188         if ( strstr($aExplain['Extra'], 'Impossible WHERE' ) !== false ) {
00189             return false;
00190         }
00191 
00192         if ( $aExplain['key'] === null ) {
00193             return true;
00194         }
00195 
00196         if ( strpos( $aExplain['type'], 'range' ) ) {
00197             return true;
00198         }
00199 
00200         if ( strpos($aExplain['type'], 'index' ) ) {
00201             return true;
00202         }
00203 
00204         if ( strpos( $aExplain['type'], 'ALL' ) ) {
00205             return true;
00206         }
00207 
00208         if ( strpos( $aExplain['Extra'], 'filesort' ) ) {
00209             if ( strpos( $aExplain['ref'], 'const' ) === false ) {
00210                 return true;
00211             }
00212         }
00213 
00214         if ( strpos( $aExplain['Extra'], 'temporary' ) ) {
00215             return true;
00216         }
00217 
00218         return false;
00219     }
00220 
00229     protected function _checkMess( $s1, $s2 )
00230     {
00231         if ( strpos( strtolower( trim( $s1 ) ), 'select ' ) !== 0 ) {
00232             return false;
00233         }
00234 
00235         if ( strpos( strtolower( trim( $s2 ) ), 'select ' ) !== 0 ) {
00236             return false;
00237         }
00238 
00239         // strip from values
00240         $s1 = self::_getSqlTemplate( $s1 );
00241         $s2 = self::_getSqlTemplate( $s2 );
00242 
00243         if (!strcmp($s1, $s2)) {
00244             return true;
00245         }
00246 
00247         return false;
00248     }
00249 
00257     protected static function _getSqlTemplate( $sSql )
00258     {
00259         $sSql = preg_replace( "/'.*?(?<!\\\\)'/", "'#VALUE#'", $sSql );
00260         $sSql = preg_replace( '/".*?(?<!\\\\)"/', '"#VALUE#"', $sSql );
00261         $sSql = preg_replace( '/[0-9]/', '#NUMVALUE#', $sSql );
00262 
00263         return $sSql;
00264     }
00265 
00273     protected function _logToFile($aWarnings)
00274     {
00275         $oStr = getStr();
00276         $sLogMsg = "\n\n\n\n\n\n-- ".date("m-d  H:i:s")." --\n\n";
00277         foreach ( $aWarnings as $w ) {
00278             $sLogMsg .= "{$w['check']}: {$w['time']} - ".$oStr->htmlentities($w['sql'])."\n\n";
00279             $sLogMsg .= $w['trace']."\n\n\n\n";
00280         }
00281         oxRegistry::getUtils()->writeToLog( $sLogMsg, 'oxdebugdb.txt' );
00282     }
00283 }