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 $file = file_get_contents( oxConfig::getInstance()->getLogsDir() . 'oxdebugdb_skipped.sql' );
00047 $m = explode('-- -- ENTRY END', $file);
00048 foreach ( $m as $n ) {
00049 if ( ( $n = self::_skipWhiteSpace( $n ) ) ) {
00050 self::$_aSkipSqls[md5($n)] = true;
00051 }
00052 }
00053 }
00054 $checkTpl = md5(self::_skipWhiteSpace(self::_getSqlTemplate($sSql)));
00055 $check = md5(self::_skipWhiteSpace($sSql));
00056 return self::$_aSkipSqls[$check] || self::$_aSkipSqls[$checkTpl];
00057 }
00058
00064 public function getWarnings()
00065 {
00066 $aWarnings = array();
00067 $aHistory = array();
00068 $oDb = oxDb::getDb();
00069 $iLastDbgState = $oDb->logSQL( false );
00070 $rs = $oDb->execute( "select sql0, sql1, tracer from adodb_logsql order by created limit 5000" );
00071 if ($rs != false && $rs->recordCount() > 0 ) {
00072 $aLastRecord = null;
00073 while ( !$rs->EOF ) {
00074 $sId = $rs->fields[0];
00075 $sSql = $rs->fields[1];
00076
00077 if (!self::_isSkipped($sSql)) {
00078 if ($this->_checkMissingKeys($sSql)) {
00079 $aWarnings['MissingKeys'][$sId] = true;
00080
00081 }
00082 }
00083
00084
00085 if ( $aLastRecord && $this->_checkMess( $sSql, $aLastRecord[1] ) ) {
00086
00087 $aWarnings['MESS'][$sId] = true;
00088 $aWarnings['MESS'][$aLastRecord[0]] = true;
00089 }
00090
00091 foreach ($aHistory as $aHistItem) {
00092 if ( $this->_checkMess( $sSql, $aHistItem[1] ) ) {
00093
00094 $aWarnings['MESS_ALL'][$sId] = true;
00095 $aWarnings['MESS_ALL'][$aHistItem[0]] = true;
00096 }
00097 }
00098
00099 $aHistory[] = $aLastRecord = $rs->fields;
00100
00101
00102
00103
00104 $rs->moveNext();
00105 }
00106 }
00107 $aWarnings = $this->_generateWarningsResult($aWarnings);
00108 $this->_logToFile( $aWarnings );
00109 $oDb->logSQL( $iLastDbgState );
00110 return $aWarnings;
00111 }
00112
00120 protected function _generateWarningsResult( $aInput )
00121 {
00122 $aOutput = array();
00123 $oDb = oxDb::getDb();
00124 foreach ($aInput as $fnc => $aWarnings) {
00125 $ids = implode(",", oxDb::getInstance()->quoteArray(array_keys($aWarnings)));
00126 $rs = $oDb->execute("select sql1, timer, tracer from adodb_logsql where sql0 in ($ids)");
00127 if ($rs != false && $rs->recordCount() > 0) {
00128 while (!$rs->EOF) {
00129 $aOutputEntry = array();
00130 $aOutputEntry['check'] = $fnc;
00131 $aOutputEntry['sql'] = $rs->fields[0];
00132 $aOutputEntry['time'] = $rs->fields[1];
00133 $aOutputEntry['trace'] = $rs->fields[2];
00134 $aOutput[] = $aOutputEntry;
00135 $rs->moveNext();
00136 }
00137 }
00138 }
00139 return $aOutput;
00140 }
00141
00150 protected function _checkMissingKeys( $sSql )
00151 {
00152 if ( strpos( strtolower( trim( $sSql ) ), 'select ' ) !== 0 ) {
00153 return false;
00154 }
00155
00156 $rs = oxDb::getDb(true)->execute( "explain $sSql" );
00157 if ( $rs != false && $rs->recordCount() > 0 ) {
00158 while (!$rs->EOF) {
00159 if ( $this->_missingKeysChecker( $rs->fields ) ) {
00160 return true;
00161 }
00162 $rs->moveNext();
00163 }
00164 }
00165 return false;
00166 }
00167
00176 private function _missingKeysChecker($aExplain)
00177 {
00178 if ( $aExplain['type'] == 'system' ) {
00179 return false;
00180 }
00181
00182 if ( $aExplain['key'] === null ) {
00183 return true;
00184 }
00185
00186 if ( strpos( $aExplain['type'], 'range' ) ) {
00187 return true;
00188 }
00189
00190 if ( strpos($aExplain['type'], 'index' ) ) {
00191 return true;
00192 }
00193
00194 if ( strpos( $aExplain['type'], 'ALL' ) ) {
00195 return true;
00196 }
00197
00198 if ( strpos( $aExplain['Extra'], 'filesort' ) ) {
00199 if ( strpos( $aExplain['ref'], 'const' ) === false ) {
00200 return true;
00201 }
00202 }
00203
00204 if ( strpos( $aExplain['Extra'], 'temporary' ) ) {
00205 return true;
00206 }
00207
00208 return false;
00209 }
00210
00219 protected function _checkMess( $s1, $s2 )
00220 {
00221 if ( strpos( strtolower( trim( $s1 ) ), 'select ' ) !== 0 ) {
00222 return false;
00223 }
00224
00225 if ( strpos( strtolower( trim( $s2 ) ), 'select ' ) !== 0 ) {
00226 return false;
00227 }
00228
00229
00230 $s1 = self::_getSqlTemplate( $s1 );
00231 $s2 = self::_getSqlTemplate( $s2 );
00232
00233 if (!strcmp($s1, $s2)) {
00234 return true;
00235 }
00236
00237 return false;
00238 }
00239
00247 protected static function _getSqlTemplate( $sSql )
00248 {
00249 $sSql = preg_replace( "/'.*?(?<!\\\\)'/", "'#VALUE#'", $sSql );
00250 $sSql = preg_replace( '/".*?(?<!\\\\)"/', '"#VALUE#"', $sSql );
00251 $sSql = preg_replace( '/[0-9]/', '#NUMVALUE#', $sSql );
00252
00253 return $sSql;
00254 }
00255
00263 protected function _logToFile($aWarnings)
00264 {
00265 $oStr = getStr();
00266 $sLogMsg = "\n\n\n\n\n\n-- ".date("m-d H:i:s")." --\n\n";
00267 foreach ( $aWarnings as $w ) {
00268 $sLogMsg .= "{$w['check']}: {$w['time']} - ".$oStr->htmlentities($w['sql'])."\n\n";
00269 $sLogMsg .= $w['trace']."\n\n\n\n";
00270 }
00271 oxUtils::getInstance()->writeToLog( $sLogMsg, 'oxdebugdb.txt' );
00272 }
00273 }