OXID eShop CE  4.9.8
 All Classes Files Functions Variables Pages
oxdebugdb.php
Go to the documentation of this file.
1 <?php
2 
6 class oxDebugDb
7 {
8 
14  private static $_aSkipSqls = array();
15 
19  public function __construct()
20  {
21  }
22 
30  protected static function _skipWhiteSpace($sStr)
31  {
32  return str_replace(array(' ', "\t", "\r", "\n"), '', $sStr);
33  }
34 
42  protected static function _isSkipped($sSql)
43  {
44  if (!count(self::$_aSkipSqls)) {
45  $sFile = oxRegistry::getConfig()->getLogsDir() . 'oxdebugdb_skipped.sql';
46  if (is_readable($sFile)) {
47  $aSkip = explode('-- -- ENTRY END', file_get_contents($sFile));
48  foreach ($aSkip as $sQ) {
49  if (($sQ = self::_skipWhiteSpace($sQ))) {
50  self::$_aSkipSqls[md5($sQ)] = true;
51  }
52  }
53  }
54  }
55  $checkTpl = md5(self::_skipWhiteSpace(self::_getSqlTemplate($sSql)));
56  $check = md5(self::_skipWhiteSpace($sSql));
57 
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 
117  return $aWarnings;
118  }
119 
127  protected function _generateWarningsResult($aInput)
128  {
129  $aOutput = array();
130  $oDb = oxDb::getDb();
131  foreach ($aInput as $fnc => $aWarnings) {
132  $ids = implode(",", oxDb::getInstance()->quoteArray(array_keys($aWarnings)));
133  $rs = $oDb->select("select sql1, timer, tracer from adodb_logsql where sql0 in ($ids)");
134  if ($rs != false && $rs->recordCount() > 0) {
135  while (!$rs->EOF) {
136  $aOutputEntry = array();
137  $aOutputEntry['check'] = $fnc;
138  $aOutputEntry['sql'] = $rs->fields[0];
139  $aOutputEntry['time'] = $rs->fields[1];
140  $aOutputEntry['trace'] = $rs->fields[2];
141  $aOutput[] = $aOutputEntry;
142  $rs->moveNext();
143  }
144  }
145  }
146 
147  return $aOutput;
148  }
149 
158  protected function _checkMissingKeys($sSql)
159  {
160  if (strpos(strtolower(trim($sSql)), 'select ') !== 0) {
161  return false;
162  }
163 
164  $rs = oxDb::getDb(oxDb::FETCH_MODE_ASSOC)->execute("explain $sSql");
165  if ($rs != false && $rs->recordCount() > 0) {
166  while (!$rs->EOF) {
167  if ($this->_missingKeysChecker($rs->fields)) {
168  return true;
169  }
170  $rs->moveNext();
171  }
172  }
173 
174  return false;
175  }
176 
185  private function _missingKeysChecker($aExplain)
186  {
187  if ($aExplain['type'] == 'system') {
188  return false;
189  }
190 
191  if (strstr($aExplain['Extra'], 'Impossible WHERE') !== false) {
192  return false;
193  }
194 
195  if ($aExplain['key'] === null) {
196  return true;
197  }
198 
199  if (strpos($aExplain['type'], 'range')) {
200  return true;
201  }
202 
203  if (strpos($aExplain['type'], 'index')) {
204  return true;
205  }
206 
207  if (strpos($aExplain['type'], 'ALL')) {
208  return true;
209  }
210 
211  if (strpos($aExplain['Extra'], 'filesort')) {
212  if (strpos($aExplain['ref'], 'const') === false) {
213  return true;
214  }
215  }
216 
217  if (strpos($aExplain['Extra'], 'temporary')) {
218  return true;
219  }
220 
221  return false;
222  }
223 
232  protected function _checkMess($s1, $s2)
233  {
234  if (strpos(strtolower(trim($s1)), 'select ') !== 0) {
235  return false;
236  }
237 
238  if (strpos(strtolower(trim($s2)), 'select ') !== 0) {
239  return false;
240  }
241 
242  // strip from values
243  $s1 = self::_getSqlTemplate($s1);
244  $s2 = self::_getSqlTemplate($s2);
245 
246  if (!strcmp($s1, $s2)) {
247  return true;
248  }
249 
250  return false;
251  }
252 
260  protected static function _getSqlTemplate($sSql)
261  {
262  $sSql = preg_replace("/'.*?(?<!\\\\)'/", "'#VALUE#'", $sSql);
263  $sSql = preg_replace('/".*?(?<!\\\\)"/', '"#VALUE#"', $sSql);
264  $sSql = preg_replace('/[0-9]/', '#NUMVALUE#', $sSql);
265 
266  return $sSql;
267  }
268 
274  protected function _logToFile($aWarnings)
275  {
276  $oStr = getStr();
277  $sLogMsg = "\n\n\n\n\n\n-- " . date("m-d H:i:s") . " --\n\n";
278  foreach ($aWarnings as $w) {
279  $sLogMsg .= "{$w['check']}: {$w['time']} - " . $oStr->htmlentities($w['sql']) . "\n\n";
280  $sLogMsg .= $w['trace'] . "\n\n\n\n";
281  }
282  oxRegistry::getUtils()->writeToLog($sLogMsg, 'oxdebugdb.txt');
283  }
284 }