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