oxdebugdb.php

Go to the documentation of this file.
00001 <?php
00002 
00006 class oxDebugDb
00007 {
00013     private static $_aSkipSqls = array();
00014 
00022     protected static function _skipWhiteSpace( $sStr )
00023     {
00024         return str_replace( array( ' ', "\t", "\r", "\n"), '', $sStr );
00025     }
00026 
00034     protected static function _isSkipped($sSql)
00035     {
00036         if ( !count(self::$_aSkipSqls ) ) {
00037             $file = file_get_contents(realpath(dirname(__FILE__).'/..').'/oxdebugdb_skipped.sql');
00038             $m = explode('-- -- ENTRY END', $file);
00039             foreach ( $m as $n ) {
00040                 if ( ( $n = self::_skipWhiteSpace( $n ) ) ) {
00041                     self::$_aSkipSqls[md5($n)] = true;
00042                 }
00043             }
00044         }
00045         $checkTpl = md5(self::_skipWhiteSpace(self::_getSqlTemplate($sSql)));
00046         $check = md5(self::_skipWhiteSpace($sSql));
00047         return self::$_aSkipSqls[$check] || self::$_aSkipSqls[$checkTpl];
00048     }
00049 
00055     public function getWarnings()
00056     {
00057         $aWarnings = array();
00058         $aHistory = array();
00059         $oDb = oxDb::getDb();
00060         $iLastDbgState = $oDb->logSQL( false );
00061         $rs = $oDb->execute( "select sql0, sql1, tracer from adodb_logsql order by created limit 5000" );
00062         if ($rs != false && $rs->recordCount() > 0 ) {
00063             $aLastRecord = null;
00064             while ( !$rs->EOF ) {
00065                 $sId  = $rs->fields[0];
00066                 $sSql = $rs->fields[1];
00067 
00068                 if (!self::_isSkipped($sSql)) {
00069                     if ($this->_checkMissingKeys($sSql)) {
00070                         $aWarnings['MissingKeys'][$sId] = true;
00071                         // debug: echo "<li> <pre>".self::_getSqlTemplate($sSql)." </pre><br>";
00072                     }
00073                 }
00074 
00075                 // multiple executed single statements
00076                 if ( $aLastRecord && $this->_checkMess( $sSql, $aLastRecord[1] ) ) {
00077                     // sql0 matches, also, this is exactly following statement: MESS?
00078                     $aWarnings['MESS'][$sId] = true;
00079                     $aWarnings['MESS'][$aLastRecord[0]] = true;
00080                 }
00081 
00082                 foreach ($aHistory as $aHistItem) {
00083                     if ( $this->_checkMess( $sSql, $aHistItem[1] ) ) {
00084                         // sql0 matches, also, this is exactly following statement: MESS?
00085                         $aWarnings['MESS_ALL'][$sId] = true;
00086                         $aWarnings['MESS_ALL'][$aHistItem[0]] = true;
00087                     }
00088                 }
00089 
00090                 $aHistory[] = $aLastRecord = $rs->fields;
00091                 /*
00092                 if (preg_match('/select[^\*]*(?<!(from))\*.*?(?<!(from))from/im', $sSql)) {
00093                     $aWarnings['Select fields not strict'][$sId] = true;
00094                 }*/
00095                 $rs->moveNext();
00096             }
00097         }
00098         $aWarnings = $this->_generateWarningsResult($aWarnings);
00099         $this->_logToFile( $aWarnings );
00100         $oDb->logSQL( $iLastDbgState );
00101         return $aWarnings;
00102     }
00103 
00111     protected function _generateWarningsResult( $aInput )
00112     {
00113         $aOutput = array();
00114         $oDb = oxDb::getDb();
00115         foreach ($aInput as $fnc => $aWarnings) {
00116             $ids = implode("','", array_keys($aWarnings));
00117             $rs = $oDb->execute("select sql1, timer, tracer from adodb_logsql where sql0 in ('$ids')");
00118             if ($rs != false && $rs->recordCount() > 0) {
00119                 while (!$rs->EOF) {
00120                     $aOutputEntry = array();
00121                     $aOutputEntry['check'] = $fnc;
00122                     $aOutputEntry['sql'] = $rs->fields[0];
00123                     $aOutputEntry['time'] = $rs->fields[1];
00124                     $aOutputEntry['trace'] = $rs->fields[2];
00125                     $aOutput[] = $aOutputEntry;
00126                     $rs->moveNext();
00127                 }
00128             }
00129         }
00130         return $aOutput;
00131     }
00132 
00141     protected function _checkMissingKeys( $sSql )
00142     {
00143         if ( strpos( strtolower( trim( $sSql ) ), 'select ' ) !== 0 ) {
00144             return false;
00145         }
00146 
00147         $rs = oxDb::getDb(true)->execute( "explain $sSql" );
00148         if ( $rs != false && $rs->recordCount() > 0 ) {
00149             while (!$rs->EOF) {
00150                 if ( $this->_missingKeysChecker( $rs->fields ) ) {
00151                     return true;
00152                 }
00153                 $rs->moveNext();
00154             }
00155         }
00156         return false;
00157     }
00158 
00167     private function _missingKeysChecker($aExplain)
00168     {
00169         if ( $aExplain['type'] == 'system' ) {
00170             return false;
00171         }
00172 
00173         if ( $aExplain['key'] === null ) {
00174             return true;
00175         }
00176 
00177         if ( strpos( $aExplain['type'], 'range' ) ) {
00178             return true;
00179         }
00180 
00181         if ( strpos($aExplain['type'], 'index' ) ) {
00182             return true;
00183         }
00184 
00185         if ( strpos( $aExplain['type'], 'ALL' ) ) {
00186             return true;
00187         }
00188 
00189         if ( strpos( $aExplain['Extra'], 'filesort' ) ) {
00190             if ( strpos( $aExplain['ref'], 'const' ) === false ) {
00191                 return true;
00192             }
00193         }
00194 
00195         if ( strpos( $aExplain['Extra'], 'temporary' ) ) {
00196             return true;
00197         }
00198 
00199         return false;
00200     }
00201 
00210     protected function _checkMess( $s1, $s2 )
00211     {
00212         if ( strpos( strtolower( trim( $s1 ) ), 'select ' ) !== 0 ) {
00213             return false;
00214         }
00215 
00216         if ( strpos( strtolower( trim( $s2 ) ), 'select ' ) !== 0 ) {
00217             return false;
00218         }
00219 
00220         // strip from values
00221         $s1 = self::_getSqlTemplate( $s1 );
00222         $s2 = self::_getSqlTemplate( $s2 );
00223 
00224         if (!strcmp($s1, $s2)) {
00225             return true;
00226         }
00227 
00228         return false;
00229     }
00230 
00238     protected static function _getSqlTemplate( $sSql )
00239     {
00240         $sSql = preg_replace( "/'.*?(?<!\\\\)'/", "'#VALUE#'", $sSql );
00241         $sSql = preg_replace( '/".*?(?<!\\\\)"/', '"#VALUE#"', $sSql );
00242         $sSql = preg_replace( '/[0-9]/', '#NUMVALUE#', $sSql );
00243 
00244         return $sSql;
00245     }
00246 
00254     protected function _logToFile($aWarnings)
00255     {
00256         $oStr = getStr();
00257         $s = "\n\n\n\n\n\n-- ".date("m-d  H:i:s")." --\n\n";
00258         foreach ( $aWarnings as $w ) {
00259             $s .= "{$w['check']}: {$w['time']} - ".$oStr->htmlentities($w['sql'])."\n\n";
00260             $s .= $w['trace']."\n\n\n\n";
00261         }
00262 
00263         if ( !( $f = fopen(realpath(dirname(__FILE__).'/..').'/oxdebugdb.txt', "a+" ) ) ) {
00264             return;
00265         }
00266 
00267         fwrite( $f, $s );
00268         fclose( $f );
00269     }
00270 }

Generated on Wed Apr 22 12:26:31 2009 for OXID eShop CE by  doxygen 1.5.5