<?php
//--------------------------------------------------
// Hide output on specific scripts
if (!isset($GLOBALS['debugShowOutput'])) {
$GLOBALS['debugShowOutput'] = true;
}
//--------------------------------------------------
// Debug state
$GLOBALS['htmlDebugOutput'] = '';
$GLOBALS['debugRequiredFields'] = array();
$GLOBALS['debugRequiredFields'][] = 'deleted';
$GLOBALS['debugRequiredFields'][] = 'lang';
$GLOBALS['debugOutputHideDefault'] = true;
$GLOBALS['debugTimeStart'] = explode(' ', microtime());
$GLOBALS['debugTimeStart'] = ((float)$GLOBALS['debugTimeStart'][0] + (float)$GLOBALS['debugTimeStart'][1]);
$GLOBALS['debugQueryTime'] = 0;
$GLOBALS['debugQueries'] = true;
//--------------------------------------------------
// Pick up database query
function debug_database($db, $sql, $parameters, $exit_on_error) {
//--------------------------------------------------
// Skip if disabled debugging
if ($GLOBALS['debugQueries'] == false) {
return $db->query($sql, $parameters, false, $exit_on_error);
}
//--------------------------------------------------
// Full time
$time_init = microtime(true);
//--------------------------------------------------
// Query type
$select_query = preg_match('/^\W*SELECT.*FROM/is', $sql); // Check for "non-word" characters, as it may contain brackets, e.g. a UNION... And don't debug queries without a table, e.g. SELECT FOUND_ROWS();
if ($select_query && strpos($sql, 'SQL_NO_CACHE') === false) {
$sql = preg_replace('/^\W*SELECT/', '$0 SQL_NO_CACHE', $sql);
}
//--------------------------------------------------
// HTML Format for the query
$indent = 0;
$query_lines = array();
$query_text = preg_replace('/\) (AND|OR) \(/', "\n$0\n", $sql); // Could be better, just breaking up the keyword searching sections.
foreach (explode("\n", $query_text) as $line_text) {
$line_text = trim($line_text);
$line_indent = $indent;
if ($line_text == '') {
continue;
}
$open = strrpos($line_text, '('); // The LAST bracket is an OPEN bracket.
$close = strrpos($line_text, ')');
if ($open !== false && ($close === false || $open > $close)) {
$indent += 2;
}
$open = strpos($line_text, '('); // The FIRST bracket is a CLOSE bracket.
$close = strpos($line_text, ')');
if ($close !== false && ($open === false || $open > $close)) {
$indent -= 2;
if ($close == 0) { // Not always an exact match, e.g. ending a subquery with ") AS s"
$line_indent -= 2;
}
}
if (!preg_match('/^[A-Z]+( |$)/', $line_text)) { // Keywords, such as SELECT/FROM/WHERE/etc (not functions)
$line_indent += 1;
}
if ($line_indent < 0) {
$line_indent = 0;
}
$query_lines[] = str_repeat(' ', $line_indent) . $line_text;
}
$query_html = html(implode("\n", $query_lines) . ';');
//--------------------------------------------------
// Values
if ($parameters) {
$offset = 0;
$k = 0;
while (($pos = strpos($query_html, '?', $offset)) !== false) {
if (isset($parameters[$k])) {
$parameter_html = html($parameters[$k][0] == 's' ? '"' . $parameters[$k][1] . '"' : $parameters[$k][1]);
} else {
$parameter_html = 'NULL';
}
$parameter_html = '<strong class="value" style="color: #C00;">' . $parameter_html . '</strong>';
$query_html = substr($query_html, 0, $pos) . $parameter_html . substr($query_html, ($pos + 1));
$offset = ($pos + strlen($parameter_html));
$k++;
}
}
//--------------------------------------------------
// Called from
foreach (debug_backtrace() as $called_from) {
if (isset($called_from['file']) && $called_from['file'] != ROOT . '/a/php/database.php') {
break;
}
}
//--------------------------------------------------
// Explain how the query is executed
$explain_html = '';
if ($select_query) {
$explain_html .= '
<table style="border-spacing: 0; border-width: 0 1px 1px 0; border-style: solid; border-color: #000; margin: 0 0 2em 0;">';
$headers_printed = false;
$result = $db->query('EXPLAIN ' . $sql, $parameters, false, false); // No debug, and don't exit on error
if ($result) {
while ($row = $db->fetch_row($result)) {
if ($headers_printed == false) {
$headers_printed = true;
$explain_html .= '
<tr>';
foreach ($row as $key => $value) {
$explain_html .= '
<th style="border-width: 1px 0 0 1px; border-style: solid; border-color: #000; padding: 0.2em;">' . html($key) . '</th>';
}
$explain_html .= '
</tr>';
}
$explain_html .= '
<tr>';
foreach ($row as $key => $value) {
if ($key == 'possible_keys') {
$value = str_replace(',', ', ', $value);
}
$value_html = ($value == '' ? ' ' : html($value));
if ($key == 'type') {
$explain_html .= '
<td style="border-width: 1px 0 0 1px; border-style: solid; border-color: #000; padding: 0.2em;"><a href="https://dev.mysql.com/doc/refman/5.0/en/explain-output.html#jointype_' . html($value) . '">' . $value_html . '</a></td>';
} else {
$explain_html .= '
<td style="border-width: 1px 0 0 1px; border-style: solid; border-color: #000; padding: 0.2em;">' . $value_html . '</td>';
}
}
$explain_html .= '
</tr>';
}
}
$explain_html .= '
</table>';
}
//--------------------------------------------------
// Get all the table references, and if any of them
// have a "deleted" column, make sure that it's
// being used
$text_html = '';
if (preg_match('/^\W*(SELECT|UPDATE|DELETE)/i', ltrim($sql))) {
$tables = array();
// if (preg_match('/WHERE(.*)/ims', $sql, $matches)) {
// $where_sql = $matches[1];
// $where_sql = preg_replace('/ORDER BY.*/ms', '', $where_sql);
// $where_sql = preg_replace('/LIMIT\W+[0-9].*/ms', '', $where_sql);
// } else {
// $where_sql = '';
// }
$where_sql = '';
preg_match_all('/WHERE(.*?)(GROUP BY|ORDER BY|LIMIT\W+[0-9]|LEFT JOIN|$)/is', $sql, $matches_sql, PREG_SET_ORDER);
foreach ($matches_sql as $match_sql) {
$where_sql .= $match_sql[1];
}
if (DB_PREFIX != '') {
preg_match_all('/\b(' . preg_quote(DB_PREFIX, '/') . '[a-z0-9_]+)`?( AS ([a-z0-9]+))?/', $sql, $matches, PREG_SET_ORDER);
} else {
$matches = array();
preg_match_all('/(UPDATE|FROM)([^\(]*?)(WHERE|GROUP BY|HAVING|ORDER BY|LIMIT|$)/isD', $sql, $from_matches, PREG_SET_ORDER);
foreach ($from_matches as $match) {
foreach (preg_split('/(,|(NATURAL\s+)?(LEFT|RIGHT|INNER|CROSS)\s+(OUTER\s+)?JOIN)/', $match[2]) as $table) {
if (preg_match('/([a-z0-9_]+)( AS ([a-z0-9]+))?/', $table, $ref)) {
$matches[] = $ref;
}
}
}
}
foreach ($matches as $table) {
$found = array();
foreach ($GLOBALS['debugRequiredFields'] as $required_field) {
$result = $db->query('SHOW COLUMNS FROM ' . $table[1] . ' LIKE "' . $required_field . '"', NULL, false, false); // No debug, and don't exit on error
if ($result && $row = $db->fetch_row($result)) {
//--------------------------------------------------
// Found
$found[] = $required_field;
//--------------------------------------------------
// Table name
$required_clause = (isset($table[3]) ? '`' . $table[3] . '`.' : '') . '`' . $required_field . '`';
//--------------------------------------------------
// Test
$sql_conditions = array($where_sql);
if (preg_match('/' . preg_quote($table[1], '/') . (isset($table[3]) ? ' +AS +' . preg_quote($table[3], '/') : '') . ' +ON(.*)/ms', $sql, $on_details)) {
$sql_conditions[] = preg_replace('/(LEFT|RIGHT|INNER|CROSS|WHERE|GROUP BY|HAVING|ORDER BY|LIMIT).*/ms', '', $on_details[1]);
}
$valid = false;
foreach ($sql_conditions as $sql_condition) {
if (preg_match('/' . str_replace('`', '(`|\b)', preg_quote($required_clause, '/')) . ' +(IS NULL|IS NOT NULL|=|>|>=|<|<=|!=)/', $sql_condition)) {
$valid = true;
break;
}
}
//--------------------------------------------------
// If missing
if (!$valid) {
echo "\n";
echo '<div>' . "\n";
echo ' <h1>Error</h1>' . "\n";
echo ' <p><strong>' . str_replace(ROOT, '', $called_from['file']) . '</strong> (line ' . $called_from['line'] . ')</p>' . "\n";
echo ' <p>Missing reference to "' . html(str_replace('`', '', $required_clause)) . '" column on the table "' . html($table[1]) . '".</p>' . "\n";
echo ' <hr />' . "\n";
echo ' <p><pre>' . "\n\n" . $query_html . "\n\n" . '</pre></p>' . "\n";
echo '</div>' . "\n";
exit();
}
}
}
$tables[] = $table[1] . ': ' . (count($found) > 0 ? implode(', ', $found) : 'N/A');
}
if (count($tables) > 0) {
$text_html .= '
<ul>';
foreach ($tables as $table) {
$text_html .= '
<li style="padding: 0; margin: 0; background: #FFF; color: #000; text-align: left;">' . preg_replace('/: (.*)/', ': <strong>$1</strong>', html($table)) . '</li>';
}
$text_html .= '
</ul>';
}
}
//--------------------------------------------------
// Run query
$time_start = microtime(true);
$result = $db->query($sql, $parameters, false, $exit_on_error);
$time_check = round(($time_start - $time_init), 3);
$time_query = round((microtime(true) - $time_start), 3);
if ($select_query && $result) {
$results_html = '<p style="text-align: left; padding: 0; margin: 1em 0;">Rows: ' . html($db->num_rows($result)) . '</p>';
} else {
$results_html = '';
}
$GLOBALS['debugQueryTime'] += $time_query;
//--------------------------------------------------
// Create debug output
$single_line = (strpos($query_html, "\n") === false);
$html = '<strong>' . str_replace(ROOT, '', $called_from['file']) . '</strong> (line ' . $called_from['line'] . ')<br />' . ($single_line ? "\n\n" : "\n");
$html .= '<pre class="debug_sql" style="display: block; text-align: left; padding: 0; margin: 1em 0;">' . $query_html . '</pre>';
$GLOBALS['htmlDebugOutput'] .= '
<div style="margin: 1em 0; padding: 1em; background: #FFF; color: #000; border: 1px solid #000; clear: both;">
<p style="text-align: left; padding: 0; margin: 1em 0;">' . $html . '</p>
<p style="text-align: left; padding: 0; margin: 1em 0;">Time Elapsed: ' . html($time_query) . '</p>
' . $results_html . '
' . $explain_html . '
' . $text_html . '
</div>';
//--------------------------------------------------
// Return
return $result;
}
//--------------------------------------------------
// Allow script to add note
function debugAddNote($note = NULL) {
//--------------------------------------------------
// Time position
$timeEnd = explode(' ', microtime());
$timeEnd = ((float)$timeEnd[0] + (float)$timeEnd[1]);
$timeTotal = round(($timeEnd - $GLOBALS['debugTimeStart']), 3);
//--------------------------------------------------
// Note
$GLOBALS['htmlDebugOutput'] .= '
<div style="margin: 1em 0; padding: 1em; background: #FFF; color: #000; border: 1px solid #000; clear: both;">
' . ($note === NULL ? '' : '<p style="text-align: left; padding: 0; margin: 0;">' . nl2br(str_replace(' ', ' ', html($note))) . '</p>') . '
<p style="text-align: left; padding: 0; margin: 0;">Time Elapsed: ' . html($timeTotal) . '</p>
</div>';
}
//--------------------------------------------------
// Add debug output
function debugShutdown($buffer) {
//--------------------------------------------------
// Suppression
if ($GLOBALS['debugShowOutput'] == false) {
return $buffer;
}
//--------------------------------------------------
// Time taken
$timeEnd = explode(' ', microtime());
$timeEnd = ((float)$timeEnd[0] + (float)$timeEnd[1]);
$timeTotal = round(($timeEnd - $GLOBALS['debugTimeStart']), 3);
$htmlOutput = '
<div style="margin: 1em 0; padding: 1em; background: #FFF; color: #000; border: 1px solid #000; clear: both;">
<p style="text-align: left; padding: 0; margin: 0;">Time Elapsed: ' . html($timeTotal) . '</p>
<p style="text-align: left; padding: 0; margin: 0;">Query time: ' . html($GLOBALS['debugQueryTime']) . '</p>
</div>';
//--------------------------------------------------
// Current debug output
$htmlOutput .= $GLOBALS['htmlDebugOutput'];
//--------------------------------------------------
// Wrapper
if ($htmlOutput != '') {
$htmlOutput = "\n\n<!-- START OF DEBUG -->\n<!--sphider_noindex-->\n\n" . '
<div id="debug_output" style="margin: 1em 1em 0 1em; padding: 0; clear: both;">
<p style="margin: 0; padding: 0; text-align: left; font-size: 1em;"><a href="#" style="text-decoration: none; color: #AAA; font-size: 1em; font-weight: normal; text-align: right;" onclick="document.getElementById(\'htmlDebugOutput\').style.display = (document.getElementById(\'htmlDebugOutput\').style.display == \'block\' ? \'none\' : \'block\'); return false;">+</a></p>
<div style="display: ' . html($GLOBALS['debugOutputHideDefault'] ? 'none' : 'block') . ';" id="htmlDebugOutput">
' . $htmlOutput . '
</div>
</div>' . "\n\n<!--/sphider_noindex-->\n<!-- END OF DEBUG -->\n\n";
}
//--------------------------------------------------
// Add
$pos = strpos(strtolower($buffer), '</body>');
if ($pos !== false) {
return substr($buffer, 0, $pos) . $htmlOutput . substr($buffer, $pos);
} else {
if ($GLOBALS['pageMimeType'] == 'application/xhtml+xml') {
setMimeType('text/html');
}
return $buffer . $htmlOutput;
}
}
if (isset($GLOBALS['createDebugOutput']) && $GLOBALS['createDebugOutput']) {
ob_start('debugShutdown');
}
//--------------------------------------------------
// Fire PHP support
$debugFirePhpPath = '/Volumes/WebServer/Resources/cpoets.dev/setup/firePHP/fb.php';
if (is_file($debugFirePhpPath)) {
require_once($debugFirePhpPath);
}
?>