function &adodb_log_sql(&$conn, $sql, $inputarr)
{
/**
OXID changes - create separate mysql connection for logging for not loosing info from last query (R)
*/
$_logSqlDbInstance = $conn->_logSqlDbInstance;
$perf_table = adodb_perf::table();
$conn->_logsql = false;
// replaces setting ::fnExecute=false in ADOdb
$t0 = microtime();
$rs =& $conn->Execute($sql, $inputarr);
$t1 = microtime();
$conn->_logsql = true;
// reverse setting ::_logsql=false
if (!$_logSqlDbInstance) {
$myConfig = oxConfig::getInstance();
$conn->_logSqlDbInstance = $_logSqlDbInstance =& NewADOConnection($myConfig->getConfigParam("dbType"));
$_logSqlDbInstance->_connect($conn->host, $conn->username, $conn->password, $conn->database, false, true);
}
if (!empty($conn->_logsql)) {
//$conn->_logsql = false; // disable logsql error simulation
$dbT = $conn->dbtype;
$a0 = explode(' ', $t0);
$a0 = (double) $a0[1] + (double) $a0[0];
$a1 = explode(' ', $t1);
$a1 = (double) $a1[1] + (double) $a1[0];
$time = $a1 - $a0;
if (!$rs) {
$errM = $conn->ErrorMsg();
$errN = $conn->ErrorNo();
$tracer = substr('ERROR: ' . htmlspecialchars($errM), 0, 250);
} else {
$tracer = '';
$errM = '';
$errN = 0;
}
if (isset($_SERVER['HTTP_HOST'])) {
$tracer .= '<br>' . $_SERVER['HTTP_HOST'];
if (isset($_SERVER['PHP_SELF'])) {
$tracer .= $_SERVER['PHP_SELF'];
}
} elseif (isset($_SERVER['PHP_SELF'])) {
$tracer .= '<br>' . $_SERVER['PHP_SELF'];
}
// OXID - added backtrace
$_aTrace = debug_backtrace();
$_sTrace = '';
foreach ($_aTrace as $_trace) {
$_sTrace .= "{$_trace['file']} - {$_trace['function']}:{$_trace['line']}\n";
}
$tracer .= "\n\nBacktrace:\n" . $_sTrace;
$tracer = (string) substr($tracer, 0, 5000);
if (is_array($inputarr)) {
if (is_array(reset($inputarr))) {
$params = 'Array sizeof=' . sizeof($inputarr);
} else {
// Quote string parameters so we can see them in the
// performance stats. This helps spot disabled indexes.
$xar_params = $inputarr;
foreach ($xar_params as $xar_param_key => $xar_param) {
if (gettype($xar_param) == 'string') {
$xar_params[$xar_param_key] = '"' . $xar_param . '"';
}
}
$params = implode(', ', $xar_params);
if (strlen($params) >= 3000) {
$params = substr($params, 0, 3000);
}
}
} else {
$params = '';
}
if (is_array($sql)) {
$sql = $sql[0];
}
$arr = array('b' => strlen($sql) . '.' . crc32($sql), 'c' => substr($sql, 0, 3900), 'd' => $params, 'e' => $tracer, 'f' => adodb_round($time, 6));
//$saved = $_logSqlDbInstance->debug;
//$_logSqlDbInstance->debug = 0;
$d = $conn->sysTimeStamp;
if (empty($d)) {
$d = date("'Y-m-d H:i:s'");
}
/*
// OCI/Informix/ODBC_MSSQL - not sure if/how available in adodb-lite so I've commented out the section for now - (Pádraic)
*/
/*if ($dbT == 'oci8' && $dbT != 'oci8po')
{
$isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)";
}
elseif($dbT == 'odbc_mssql' || $dbT == 'informix')
{
$timer = $arr['f'];
if ($dbT == 'informix') $sql2 = substr($sql2,0,230);
$sql1 = $conn->qstr($arr['b']);
$sql2 = $conn->qstr($arr['c']);
$params = $conn->qstr($arr['d']);
$tracer = $conn->qstr($arr['e']);
$isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)";
if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql);
$arr = false;
} else {*/
$isql = "insert into {$perf_table} (created,sql0,sql1,params,tracer,timer) values( {$d},?,?,?,?,?)";
//}
// OXID change - added try catch wrapping
try {
$ok = $_logSqlDbInstance->Execute($isql, $arr);
} catch (Exception $e) {
$ok = false;
}
//$_logSqlDbInstance->debug = $saved;
if ($ok) {
//$_logSqlDbInstance->_logsql = true;
} else {
$err2 = $_logSqlDbInstance->ErrorMsg();
//$_logSqlDbInstance->_logsql = true; // enable logsql error simulation
$perf =& NewPerfMonitor($_logSqlDbInstance);
if ($perf) {
if ($perf->CreateLogTable()) {
$ok = $_logSqlDbInstance->Execute($isql, $arr);
}
} else {
$ok = $_logSqlDbInstance->Execute("create table {$perf_table} (\r\n created varchar(50),\r\n sql0 varchar(250),\r\n sql1 varchar(4000),\r\n params varchar(3000),\r\n tracer varchar(5000),\r\n timer decimal(16,6))");
}
/*if (!$ok) {
ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>");
$conn->_logsql = false;
}*/
}
//$conn->_errorMsg = $errM;
//$conn->_errorCode = $errN;
}
return $rs;
}
function _ExpensiveSQL($numsql = 10)
{
global $ADODB_FETCH_MODE;
$perf_table = adodb_perf::table();
$saveE = $this->conn->fnExecute;
$this->conn->fnExecute = false;
if (isset($_GET['expe']) && isset($_GET['sql'])) {
$partial = !empty($_GET['part']);
echo "<a name=explain></a>" . $this->Explain($_GET['sql'], $partial) . "\n";
}
if (isset($_GET['sql'])) {
return;
}
$sql1 = $this->sql1;
$save = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
if ($this->conn->fetchMode !== false) {
$savem = $this->conn->SetFetchMode(false);
}
$rs =& $this->conn->SelectLimit("select sum(timer) as total,{$sql1},count(*),max(timer) as max_timer,min(timer) as min_timer\n\t\t\t\tfrom {$perf_table}\n\t\t\t\twhere {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')\n\t\t\t\tand (tracer is null or tracer not like 'ERROR:%')\n\t\t\t\tgroup by sql1\n\t\t\t\thaving count(*)>1\n\t\t\t\torder by 1 desc", $numsql);
if (isset($savem)) {
$this->conn->SetFetchMode($savem);
}
$this->conn->fnExecute = $saveE;
$ADODB_FETCH_MODE = $save;
if (!$rs) {
return "<p>{$this->helpurl}. " . $this->conn->ErrorMsg() . "</p>";
}
$s = "<h3>Expensive SQL</h3>\n<font size=1>Tuning the following SQL could reduce the server load substantially</font><br>\n<table border=1 bgcolor=white><tr><td><b>Load</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n";
$max = $this->maxLength;
while (!$rs->EOF) {
$sql = $rs->fields[1];
$raw = urlencode($sql);
if (strlen($raw) > $max - 100) {
$sql2 = substr($sql, 0, $max - 500);
$raw = urlencode($sql2) . '&part=' . crc32($sql);
}
$prefix = "<a target=sqle" . rand() . " href=\"?hidem=1&expe=1&sql=" . $raw . "&x#explain\">";
$suffix = "</a>";
if ($this->explain == false || strlen($prefix > $max)) {
$prefix = '';
$suffix = '';
}
$s .= "<tr><td>" . adodb_round($rs->fields[0], 6) . "<td align=right>" . $rs->fields[2] . "<td><font size=-1>" . $prefix . htmlspecialchars($sql) . $suffix . "</font>" . "<td>" . $rs->fields[3] . "<td>" . $rs->fields[4] . "</tr>";
$rs->MoveNext();
}
return $s . "</table>";
}
function &adodb_log_sql(&$conn, $sql, $inputarr)
{
$perf_table = adodb_perf::table();
$conn->_logsql = false;
// replaces setting ::fnExecute=false in ADOdb
$t0 = microtime();
$rs =& $conn->Execute($sql, $inputarr);
$t1 = microtime();
$conn->_logsql = true;
// reverse setting ::_logsql=false
if (!empty($conn->_logsql)) {
$conn->_logsql = false;
// disable logsql error simulation
$dbT = $conn->dbtype;
$a0 = split(' ', $t0);
$a0 = (double) $a0[1] + (double) $a0[0];
$a1 = split(' ', $t1);
$a1 = (double) $a1[1] + (double) $a1[0];
$time = $a1 - $a0;
if (!$rs) {
$errM = $conn->ErrorMsg();
$errN = $conn->ErrorNo();
$tracer = substr('ERROR: ' . htmlspecialchars($errM), 0, 250);
} else {
$tracer = '';
$errM = '';
$errN = 0;
}
if (isset($_SERVER['HTTP_HOST'])) {
$tracer .= '<br>' . $_SERVER['HTTP_HOST'];
if (isset($_SERVER['PHP_SELF'])) {
$tracer .= $_SERVER['PHP_SELF'];
}
} elseif (isset($_SERVER['PHP_SELF'])) {
$tracer .= '<br>' . $_SERVER['PHP_SELF'];
}
$tracer = (string) substr($tracer, 0, 500);
if (is_array($inputarr)) {
if (is_array(reset($inputarr))) {
$params = 'Array sizeof=' . sizeof($inputarr);
} else {
// Quote string parameters so we can see them in the
// performance stats. This helps spot disabled indexes.
$xar_params = $inputarr;
foreach ($xar_params as $xar_param_key => $xar_param) {
if (gettype($xar_param) == 'string') {
$xar_params[$xar_param_key] = '"' . $xar_param . '"';
}
}
$params = implode(', ', $xar_params);
if (strlen($params) >= 3000) {
$params = substr($params, 0, 3000);
}
}
} else {
$params = '';
}
if (is_array($sql)) {
$sql = $sql[0];
}
$arr = array('b' => strlen($sql) . '.' . crc32($sql), 'c' => substr($sql, 0, 3900), 'd' => $params, 'e' => $tracer, 'f' => adodb_round($time, 6));
$saved = $conn->debug;
$conn->debug = 0;
$d = $conn->sysTimeStamp;
if (empty($d)) {
$d = date("'Y-m-d H:i:s'");
}
/*
// OCI/Informix/ODBC_MSSQL - not sure if/how available in adodb-lite so I've commented out the section for now - (Pádraic)
*/
/*if ($dbT == 'oci8' && $dbT != 'oci8po')
{
$isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)";
}
elseif($dbT == 'odbc_mssql' || $dbT == 'informix')
{
$timer = $arr['f'];
if ($dbT == 'informix') $sql2 = substr($sql2,0,230);
$sql1 = $conn->qstr($arr['b']);
$sql2 = $conn->qstr($arr['c']);
$params = $conn->qstr($arr['d']);
$tracer = $conn->qstr($arr['e']);
$isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)";
if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql);
$arr = false;
} else {*/
$isql = "insert into {$perf_table} (created,sql0,sql1,params,tracer,timer) values( {$d},?,?,?,?,?)";
//}
$ok = $conn->Execute($isql, $arr);
$conn->debug = $saved;
if ($ok) {
$conn->_logsql = true;
} else {
$err2 = $conn->ErrorMsg();
$conn->_logsql = true;
// enable logsql error simulation
$perf =& NewPerfMonitor($conn);
if ($perf) {
if ($perf->CreateLogTable()) {
$ok = $conn->Execute($isql, $arr);
}
} else {
$ok = $conn->Execute("create table {$perf_table} (\n\t\t\t\tcreated varchar(50),\n\t\t\t\tsql0 varchar(250), \n\t\t\t\tsql1 varchar(4000),\n\t\t\t\tparams varchar(3000),\n\t\t\t\ttracer varchar(500),\n\t\t\t\ttimer decimal(16,6))");
}
/*if (!$ok) {
ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>");
$conn->_logsql = false;
}*/
}
$conn->_errorMsg = $errM;
$conn->_errorCode = $errN;
}
return $rs;
}
function &adodb_log_sql(&$conn, $sql, $inputarr)
{
/**
OXID changes - create separate mysql connection for logging for not loosing info from last query (R)
*/
if (isset($_SESSION['debugPHP']) && is_string($_SESSION['debugPHP'])) {
$sIdent = $_SESSION['debugPHP'];
} else {
$sIdent = false;
}
$_logSqlDbInstance = $conn->_logSqlDbInstance;
$perf_table = 'adodb_debugphp_logsql';
$conn->_logsql = false;
// replaces setting ::fnExecute=false in ADOdb
$t0 = microtime();
$rs =& $conn->Execute($sql, $inputarr);
$t1 = microtime();
$conn->_logsql = true;
// reverse setting ::_logsql=false
if (!$_logSqlDbInstance) {
$myConfig = oxConfig::getInstance();
$conn->_logSqlDbInstance = $_logSqlDbInstance =& NewADOConnection($myConfig->getConfigParam("dbType"));
$_logSqlDbInstance->_connect($conn->host, $conn->username, $conn->password, $conn->database, false, true);
}
if (!empty($conn->_logsql) && $sIdent != false && strpos(strtolower(trim($sql)), 'select') !== true && strpos(strtolower(trim($sql)), 'show') !== true) {
//if (!empty($conn->_logsql)) {
//$conn->_logsql = false; // disable logsql error simulation
$dbT = $conn->dbtype;
$a0 = explode(' ', $t0);
$a0 = (double) $a0[1] + (double) $a0[0];
$a1 = explode(' ', $t1);
$a1 = (double) $a1[1] + (double) $a1[0];
$time = $a1 - $a0;
if (!$rs) {
$errM = $conn->ErrorMsg();
$errN = $conn->ErrorNo();
$tracer = substr('ERROR: ' . htmlspecialchars($errM), 0, 250);
} else {
$tracer = '';
$errM = '';
$errN = 0;
}
if (isset($_SERVER['HTTP_HOST'])) {
$tracer .= $_SERVER['HTTP_HOST'];
if (isset($_SERVER['PHP_SELF'])) {
$tracer .= $_SERVER['PHP_SELF'];
}
} elseif (isset($_SERVER['PHP_SELF'])) {
$tracer .= $_SERVER['PHP_SELF'];
}
if (strpos($tracer, "debugax/www/index.php") !== false) {
return $rs;
}
$tracer = (string) substr($tracer, 0, 5000);
if (is_array($inputarr)) {
if (is_array(reset($inputarr))) {
$params = 'Array sizeof=' . sizeof($inputarr);
} else {
// Quote string parameters so we can see them in the
// performance stats. This helps spot disabled indexes.
$xar_params = $inputarr;
foreach ($xar_params as $xar_param_key => $xar_param) {
if (gettype($xar_param) == 'string') {
$xar_params[$xar_param_key] = '"' . $xar_param . '"';
}
}
$params = implode(', ', $xar_params);
if (strlen($params) >= 3000) {
$params = substr($params, 0, 3000);
}
}
} else {
$params = '';
}
if (is_array($sql)) {
$sql = $sql[0];
}
$arr = array('b' => strlen($sql) . '.' . crc32($sql), 'c' => substr($sql, 0, 3900), 'd' => json_encode($rs->fields), 'e' => $tracer, 'f' => adodb_round($time, 6));
$d = $conn->sysTimeStamp;
if (empty($d)) {
$d = date("'Y-m-d H:i:s'");
}
$isql = "insert into {$perf_table} (created,sql0,sql1,params,tracer,timer,type, ident) values( {$d},?,?,?,?,?,'sql','{$sIdent}')";
try {
$ok = $_logSqlDbInstance->Execute($isql, $arr);
} catch (Exception $e) {
$ok = false;
}
if ($ok) {
} else {
$err2 = $_logSqlDbInstance->ErrorMsg();
$perf =& NewPerfMonitor($_logSqlDbInstance);
if ($perf) {
if ($perf->CreateLogTable()) {
$ok = $_logSqlDbInstance->Execute($isql, $arr);
}
} else {
$ok = $_logSqlDbInstance->Execute("CREATE TABLE IF NOT EXISTS `adodb_debugphp_logsql` (\r\n `id` int(11) NOT NULL AUTO_INCREMENT,\r\n `created` datetime NOT NULL,\r\n `sql0` varchar(250) COLLATE latin1_general_ci NOT NULL,\r\n `sql1` text COLLATE latin1_general_ci NOT NULL,\r\n `params` text COLLATE latin1_general_ci NOT NULL,\r\n `tracer` text COLLATE latin1_general_ci NOT NULL,\r\n `timer` decimal(16,6) NOT NULL,\r\n `type` char(50) COLLATE latin1_general_ci NOT NULL,\r\n `ident` char(50) COLLATE latin1_general_ci NOT NULL,\r\n PRIMARY KEY (`id`),\r\n KEY `created` (`created`),\r\n KEY `ident` (`ident`)\r\n ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;");
}
}
}
return $rs;
}
function _ExpensiveSQL($numsql = 10)
{
global $ADODB_FETCH_MODE;
$perf_table = perfmon_parent_ADOConnection::table();
$saveE = $this->LogSQL(false);
if (isset($_GET['expe']) && isset($_GET['sql'])) {
$partial = !empty($_GET['part']);
echo "\n<a name=\"explain\"></a>" . $this->Explain($_GET['sql'], $partial) . "\n";
}
if (isset($_GET['sql'])) {
return;
}
$sql1 = $this->sql1;
$save = $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$rs =& $this->SelectLimit("select sum(timer) as total,{$sql1},count(*),max(timer) as max_timer,min(timer) as min_timer\n\t\t\tfrom {$perf_table}\n\t\t\twhere {$this->upperCase}({$this->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')\n\t\t\tand (tracer is null or tracer not like 'ERROR:%')\n\t\t\tgroup by sql1\n\t\t\thaving count(*)>1\n\t\t\torder by 1 desc", $numsql);
$this->LogSQL($saveE);
$ADODB_FETCH_MODE = $save;
if (!$rs) {
return "<p>{$this->helpurl}. " . $this->ErrorMsg() . "</p>\n";
}
$s = "\n<h3>Expensive SQL</h3>\n<span style=\"font-size: 8pt;\">Tuning the following SQL could reduce the server load substantially</span><br />\n<table style=\"border: 2px groove #000000;\">\n<tr>\n<td><strong>Load</strong></td>\n<td><strong>Count</strong></td>\n<td><strong>SQL</strong></td>\n<td><strong>Max</strong></td>\n<td><strong>Min</strong></td>\n</tr>\n";
$max = $this->maxLength;
while (!$rs->EOF) {
$sql = $rs->fields[1];
$raw = urlencode($sql);
if (strlen($raw) > $max - 100) {
$sql2 = substr($sql, 0, $max - 500);
$raw = urlencode($sql2) . '&part=' . crc32($sql);
}
$prefix = "<a target=\"sqle" . rand() . "\" href=\"?hidem=1&expe=1&sql=" . $raw . "&x#explain\">";
$suffix = "</a>\n";
if ($this->explain == false || strlen($prefix > $max)) {
$prefix = '';
$suffix = '';
}
$s .= "\n<tr>\n<td>\n" . adodb_round($rs->fields[0], 6) . "\n</td><td style='text-align: right;'>\n" . $rs->fields[2] . "\n</td>\n<td><span style=\"font-size: 10pt;\">" . $prefix . htmlentities($sql, ENT_QUOTES, 'UTF-8') . $suffix . "</span>" . "\n</td><td>" . $rs->fields[3] . "\n</td><td>" . $rs->fields[4] . "\n</tr>";
$rs->MoveNext();
}
return $s . "\n</table>\n";
}
