/**
* During development you may need to check how your queries are being executed and how long they are taking. This
* routine uses MySQL's EXPLAIN to return useful information.
*
* @param string $sQuery MySQL query to check.
* @return string HTML output of the information we have found about the query.
*/
public function sqlReport($sQuery)
{
$sHtml = '';
$sExplainQuery = $sQuery;
if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $sQuery, $m)) {
$sExplainQuery = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
} elseif (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $sQuery, $m)) {
$sExplainQuery = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
}
$sExplainQuery = trim($sExplainQuery);
if (preg_match('/SELECT/se', $sExplainQuery) || preg_match('/^\\(SELECT/', $sExplainQuery)) {
$bTable = false;
if ($hResult = @($this->_aCmd['mysql_query'] == 'mysqli_query' ? $this->_aCmd['mysql_query']($this->_hMaster, "EXPLAIN {$sExplainQuery}") : $this->_aCmd['mysql_query']("EXPLAIN {$sExplainQuery}", $this->_hMaster))) {
while ($aRow = @$this->_aCmd['mysql_fetch_assoc']($hResult)) {
list($bTable, $sData) = Phpfox_Debug::addRow($bTable, $aRow);
$sHtml .= $sData;
}
}
@$this->_aCmd['mysql_free_result']($hResult);
if ($bTable) {
$sHtml .= '</table>';
}
}
return $sHtml;
}
public function sqlReport($sQuery)
{
if (!preg_match('/^SELECT/', $sQuery)) {
return '';
}
$bTable = false;
$sHtml = '';
@mssql_query('SET SHOWPLAN_TEXT ON;', $this->_hMaster);
if ($hResult = @mssql_query($sQuery, $this->_hMaster)) {
@mssql_next_result($hResult);
while ($aRow = @mssql_fetch_row($hResult)) {
list($bTable, $sData) = Phpfox_Debug::addRow($bTable, $aRow);
$sHtml .= $sData;
}
}
@mssql_query('SET SHOWPLAN_TEXT OFF;', $this->_hMaster);
@mssql_free_result($hResult);
if ($bTable) {
$sHtml .= '</table>';
}
return $sHtml;
}
function sqlReport($sQuery)
{
$sHtml = '';
$sExplainQuery = $sQuery;
if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $sQuery, $m)) {
$sExplainQuery = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
} elseif (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $sQuery, $m)) {
$sExplainQuery = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
}
if (preg_match('/^SELECT/', $sExplainQuery)) {
$bTable = false;
if ($hResult = @pg_query($this->_hMaster, "EXPLAIN {$sExplainQuery}")) {
while ($aRow = @pg_fetch_assoc($hResult)) {
list($bTable, $sData) = Phpfox_Debug::addRow($bTable, $aRow);
$sHtml .= $sData;
}
}
@pg_free_result($hResult);
if ($bTable) {
$sHtml .= '</table>';
}
}
return $sHtml;
}
public function sqlReport($query)
{
$sHtml = '';
$html_table = false;
// Grab a plan table, any will do
$sql = "SELECT table_name\n\t\t\t\t\tFROM USER_TABLES\n\t\t\t\t\tWHERE table_name LIKE '%PLAN_TABLE%'";
$stmt = ociparse($this->_hMaster, $sql);
ociexecute($stmt);
$result = array();
if (ocifetchinto($stmt, $result, OCI_ASSOC + OCI_RETURN_NULLS)) {
$table = $result['TABLE_NAME'];
// This is the statement_id that will allow us to track the plan
$statement_id = substr(md5($query), 0, 30);
// Remove any stale plans
$stmt2 = ociparse($this->_hMaster, "DELETE FROM {$table} WHERE statement_id='{$statement_id}'");
ociexecute($stmt2);
ocifreestatement($stmt2);
// Explain the plan
$sql = "EXPLAIN PLAN\n\t\t\t\t\t\tSET STATEMENT_ID = '{$statement_id}'\n\t\t\t\t\t\tFOR {$query}";
$stmt2 = ociparse($this->_hMaster, $sql);
ociexecute($stmt2);
ocifreestatement($stmt2);
// Get the data from the plan
$sql = "SELECT operation, options, object_name, object_type, cardinality, cost\n\t\t\t\t\t\tFROM plan_table\n\t\t\t\t\t\tSTART WITH id = 0 AND statement_id = '{$statement_id}'\n\t\t\t\t\t\tCONNECT BY PRIOR id = parent_id\n\t\t\t\t\t\t\tAND statement_id = '{$statement_id}'";
$stmt2 = ociparse($this->_hMaster, $sql);
ociexecute($stmt2);
$row = array();
while (ocifetchinto($stmt2, $row, OCI_ASSOC + OCI_RETURN_NULLS)) {
list($html_table, $sData) = Phpfox_Debug::addRow($html_table, $row);
$sHtml .= $sData;
}
ocifreestatement($stmt2);
// Remove the plan we just made, we delete them on request anyway
$stmt2 = ociparse($this->_hMaster, "DELETE FROM {$table} WHERE statement_id='{$statement_id}'");
ociexecute($stmt2);
ocifreestatement($stmt2);
}
ocifreestatement($stmt);
if ($html_table) {
$sHtml .= '</table>';
}
return $sHtml;
}
