OXID eShop CE  4.8.11
 All Classes Files Functions Variables Pages
oxdebugdb.php
Go to the documentation of this file.
1 <?php
2 
6 class oxDebugDb
7 {
13  private static $_aSkipSqls = array();
14 
20  public function __construct()
21  {
22  }
23 
31  protected static function _skipWhiteSpace( $sStr )
32  {
33  return str_replace( array( ' ', "\t", "\r", "\n"), '', $sStr );
34  }
35 
43  protected static function _isSkipped($sSql)
44  {
45  if ( !count(self::$_aSkipSqls ) ) {
46  $sFile = oxRegistry::getConfig()->getLogsDir() . 'oxdebugdb_skipped.sql';
47  if (is_readable($sFile)) {
48  $aSkip = explode('-- -- ENTRY END', file_get_contents( $sFile ));
49  foreach ( $aSkip as $sQ ) {
50  if ( ( $sQ = self::_skipWhiteSpace( $sQ ) ) ) {
51  self::$_aSkipSqls[md5($sQ)] = true;
52  }
53  }
54  }
55  }
56  $checkTpl = md5(self::_skipWhiteSpace(self::_getSqlTemplate($sSql)));
57  $check = md5(self::_skipWhiteSpace($sSql));
58  return self::$_aSkipSqls[$check] || self::$_aSkipSqls[$checkTpl];
59  }
60 
66  public function getWarnings()
67  {
68  $aWarnings = array();
69  $aHistory = array();
70  $oDb = oxDb::getDb();
71  if (method_exists($oDb, "logSQL")) {
72  $iLastDbgState = $oDb->logSQL( false );
73  }
74  $rs = $oDb->select( "select sql0, sql1, tracer from adodb_logsql order by created limit 5000" );
75  if ($rs != false && $rs->recordCount() > 0 ) {
76  $aLastRecord = null;
77  while ( !$rs->EOF ) {
78  $sId = $rs->fields[0];
79  $sSql = $rs->fields[1];
80 
81  if (!self::_isSkipped($sSql)) {
82  if ($this->_checkMissingKeys($sSql)) {
83  $aWarnings['MissingKeys'][$sId] = true;
84  // debug: echo "<li> <pre>".self::_getSqlTemplate($sSql)." </pre><br>";
85  }
86  }
87 
88  // multiple executed single statements
89  if ( $aLastRecord && $this->_checkMess( $sSql, $aLastRecord[1] ) ) {
90  // sql0 matches, also, this is exactly following statement: MESS?
91  $aWarnings['MESS'][$sId] = true;
92  $aWarnings['MESS'][$aLastRecord[0]] = true;
93  }
94 
95  foreach ($aHistory as $aHistItem) {
96  if ( $this->_checkMess( $sSql, $aHistItem[1] ) ) {
97  // sql0 matches, also, this is exactly following statement: MESS?
98  $aWarnings['MESS_ALL'][$sId] = true;
99  $aWarnings['MESS_ALL'][$aHistItem[0]] = true;
100  }
101  }
102 
103  $aHistory[] = $aLastRecord = $rs->fields;
104  /*
105  if (preg_match('/select[^\*]*(?<!(from))\*.*?(?<!(from))from/im', $sSql)) {
106  $aWarnings['Select fields not strict'][$sId] = true;
107  }*/
108  $rs->moveNext();
109  }
110  }
111  $aWarnings = $this->_generateWarningsResult($aWarnings);
112  $this->_logToFile( $aWarnings );
113  if (method_exists($oDb, "logSQL")) {
114  $oDb->logSQL( $iLastDbgState );
115  }
116  return $aWarnings;
117  }
118 
126  protected function _generateWarningsResult( $aInput )
127  {
128  $aOutput = array();
129  $oDb = oxDb::getDb();
130  foreach ($aInput as $fnc => $aWarnings) {
131  $ids = implode(",", oxDb::getInstance()->quoteArray(array_keys($aWarnings)));
132  $rs = $oDb->select("select sql1, timer, tracer from adodb_logsql where sql0 in ($ids)");
133  if ($rs != false && $rs->recordCount() > 0) {
134  while (!$rs->EOF) {
135  $aOutputEntry = array();
136  $aOutputEntry['check'] = $fnc;
137  $aOutputEntry['sql'] = $rs->fields[0];
138  $aOutputEntry['time'] = $rs->fields[1];
139  $aOutputEntry['trace'] = $rs->fields[2];
140  $aOutput[] = $aOutputEntry;
141  $rs->moveNext();
142  }
143  }
144  }
145  return $aOutput;
146  }
147 
156  protected function _checkMissingKeys( $sSql )
157  {
158  if ( strpos( strtolower( trim( $sSql ) ), 'select ' ) !== 0 ) {
159  return false;
160  }
161 
162  $rs = oxDb::getDb( oxDb::FETCH_MODE_ASSOC )->execute( "explain $sSql" );
163  if ( $rs != false && $rs->recordCount() > 0 ) {
164  while (!$rs->EOF) {
165  if ( $this->_missingKeysChecker( $rs->fields ) ) {
166  return true;
167  }
168  $rs->moveNext();
169  }
170  }
171  return false;
172  }
173 
182  private function _missingKeysChecker($aExplain)
183  {
184  if ( $aExplain['type'] == 'system' ) {
185  return false;
186  }
187 
188  if ( strstr($aExplain['Extra'], 'Impossible WHERE' ) !== false ) {
189  return false;
190  }
191 
192  if ( $aExplain['key'] === null ) {
193  return true;
194  }
195 
196  if ( strpos( $aExplain['type'], 'range' ) ) {
197  return true;
198  }
199 
200  if ( strpos($aExplain['type'], 'index' ) ) {
201  return true;
202  }
203 
204  if ( strpos( $aExplain['type'], 'ALL' ) ) {
205  return true;
206  }
207 
208  if ( strpos( $aExplain['Extra'], 'filesort' ) ) {
209  if ( strpos( $aExplain['ref'], 'const' ) === false ) {
210  return true;
211  }
212  }
213 
214  if ( strpos( $aExplain['Extra'], 'temporary' ) ) {
215  return true;
216  }
217 
218  return false;
219  }
220 
229  protected function _checkMess( $s1, $s2 )
230  {
231  if ( strpos( strtolower( trim( $s1 ) ), 'select ' ) !== 0 ) {
232  return false;
233  }
234 
235  if ( strpos( strtolower( trim( $s2 ) ), 'select ' ) !== 0 ) {
236  return false;
237  }
238 
239  // strip from values
240  $s1 = self::_getSqlTemplate( $s1 );
241  $s2 = self::_getSqlTemplate( $s2 );
242 
243  if (!strcmp($s1, $s2)) {
244  return true;
245  }
246 
247  return false;
248  }
249 
257  protected static function _getSqlTemplate( $sSql )
258  {
259  $sSql = preg_replace( "/'.*?(?<!\\\\)'/", "'#VALUE#'", $sSql );
260  $sSql = preg_replace( '/".*?(?<!\\\\)"/', '"#VALUE#"', $sSql );
261  $sSql = preg_replace( '/[0-9]/', '#NUMVALUE#', $sSql );
262 
263  return $sSql;
264  }
265 
273  protected function _logToFile($aWarnings)
274  {
275  $oStr = getStr();
276  $sLogMsg = "\n\n\n\n\n\n-- ".date("m-d H:i:s")." --\n\n";
277  foreach ( $aWarnings as $w ) {
278  $sLogMsg .= "{$w['check']}: {$w['time']} - ".$oStr->htmlentities($w['sql'])."\n\n";
279  $sLogMsg .= $w['trace']."\n\n\n\n";
280  }
281  oxRegistry::getUtils()->writeToLog( $sLogMsg, 'oxdebugdb.txt' );
282  }
283 }