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"; }