1<?php 2/* 3V4.98 13 Feb 2008 (c) 2000-2008 John Lim (jlim#natsoft.com.my). All rights reserved. 4 Released under both BSD license and Lesser GPL library license. 5 Whenever there is any discrepancy between the two licenses, 6 the BSD license will take precedence. See License.txt. 7 Set tabs to 4 for best viewing. 8 9 Latest version is available at http://adodb.sourceforge.net 10 11 Library for basic performance monitoring and tuning. 12 13 My apologies if you see code mixed with presentation. The presentation suits 14 my needs. If you want to separate code from presentation, be my guest. Patches 15 are welcome. 16 17*/ 18 19if (!defined('ADODB_DIR')) include_once(dirname(__FILE__).'/adodb.inc.php'); 20include_once(ADODB_DIR.'/tohtml.inc.php'); 21 22global $ADODB_PERF_MIN; 23$ADODB_PERF_MIN = 0.05; // log only if >= minimum number of secs to run 24 25define( 'ADODB_OPT_HIGH', 2); 26define( 'ADODB_OPT_LOW', 1); 27 28// returns in K the memory of current process, or 0 if not known 29function adodb_getmem() 30{ 31 if (function_exists('memory_get_usage')) 32 return (integer) ((memory_get_usage()+512)/1024); 33 34 $pid = getmypid(); 35 36 if ( strncmp(strtoupper(PHP_OS),'WIN',3)==0) { 37 $output = array(); 38 39 exec('tasklist /FI "PID eq ' . $pid. '" /FO LIST', $output); 40 return substr($output[5], strpos($output[5], ':') + 1); 41 } 42 43 /* Hopefully UNIX */ 44 exec("ps --pid $pid --no-headers -o%mem,size", $output); 45 if (sizeof($output) == 0) return 0; 46 47 $memarr = explode(' ',$output[0]); 48 if (sizeof($memarr)>=2) return (integer) $memarr[1]; 49 50 return 0; 51} 52 53// avoids localization problems where , is used instead of . 54function adodb_round($n,$prec) 55{ 56 return number_format($n, $prec, '.', ''); 57} 58 59/* return microtime value as a float */ 60function adodb_microtime() 61{ 62 $t = microtime(); 63 $t = explode(' ',$t); 64 return (float)$t[1]+ (float)$t[0]; 65} 66 67/* sql code timing */ 68function& adodb_log_sql(&$connx,$sql,$inputarr) 69{ 70 $perf_table = adodb_perf::table(); 71 $connx->fnExecute = false; 72 $t0 = microtime(); 73 $rs =& $connx->Execute($sql,$inputarr); 74 $t1 = microtime(); 75 76 if (!empty($connx->_logsql) && (empty($connx->_logsqlErrors) || !$rs)) { 77 global $ADODB_LOG_CONN; 78 79 if (!empty($ADODB_LOG_CONN)) { 80 $conn = &$ADODB_LOG_CONN; 81 if ($conn->databaseType != $connx->databaseType) 82 $prefix = '/*dbx='.$connx->databaseType .'*/ '; 83 else 84 $prefix = ''; 85 } else { 86 $conn =& $connx; 87 $prefix = ''; 88 } 89 90 $conn->_logsql = false; // disable logsql error simulation 91 $dbT = $conn->databaseType; 92 93 $a0 = explode(' ',$t0); 94 $a0 = (float)$a0[1]+(float)$a0[0]; 95 96 $a1 = explode(' ',$t1); 97 $a1 = (float)$a1[1]+(float)$a1[0]; 98 99 $time = $a1 - $a0; 100 101 if (!$rs) { 102 $errM = $connx->ErrorMsg(); 103 $errN = $connx->ErrorNo(); 104 $conn->lastInsID = 0; 105 $tracer = substr('ERROR: '.htmlspecialchars($errM),0,250); 106 } else { 107 $tracer = ''; 108 $errM = ''; 109 $errN = 0; 110 $dbg = $conn->debug; 111 $conn->debug = false; 112 if (!is_object($rs) || $rs->dataProvider == 'empty') 113 $conn->_affected = $conn->affected_rows(true); 114 $conn->lastInsID = @$conn->Insert_ID(); 115 $conn->debug = $dbg; 116 } 117 if (isset($_SERVER['HTTP_HOST'])) { 118 $tracer .= '<br>'.$_SERVER['HTTP_HOST']; 119 if (isset($_SERVER['PHP_SELF'])) $tracer .= htmlspecialchars($_SERVER['PHP_SELF']); 120 } else 121 if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.htmlspecialchars($_SERVER['PHP_SELF']); 122 //$tracer .= (string) adodb_backtrace(false); 123 124 $tracer = (string) substr($tracer,0,500); 125 126 if (is_array($inputarr)) { 127 if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr); 128 else { 129 // Quote string parameters so we can see them in the 130 // performance stats. This helps spot disabled indexes. 131 $xar_params = $inputarr; 132 foreach ($xar_params as $xar_param_key => $xar_param) { 133 if (gettype($xar_param) == 'string') 134 $xar_params[$xar_param_key] = '"' . $xar_param . '"'; 135 } 136 $params = implode(', ', $xar_params); 137 if (strlen($params) >= 3000) $params = substr($params, 0, 3000); 138 } 139 } else { 140 $params = ''; 141 } 142 143 if (is_array($sql)) $sql = $sql[0]; 144 if ($prefix) $sql = $prefix.$sql; 145 $arr = array('b'=>strlen($sql).'.'.crc32($sql), 146 'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6)); 147 //var_dump($arr); 148 $saved = $conn->debug; 149 $conn->debug = 0; 150 151 $d = $conn->sysTimeStamp; 152 if (empty($d)) $d = date("'Y-m-d H:i:s'"); 153 if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') { 154 $isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)"; 155 } else if ($dbT == 'odbc_mssql' || $dbT == 'informix' || strncmp($dbT,'odbtp',4)==0) { 156 $timer = $arr['f']; 157 if ($dbT == 'informix') $sql2 = substr($sql2,0,230); 158 159 $sql1 = $conn->qstr($arr['b']); 160 $sql2 = $conn->qstr($arr['c']); 161 $params = $conn->qstr($arr['d']); 162 $tracer = $conn->qstr($arr['e']); 163 164 $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)"; 165 if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql); 166 $arr = false; 167 } else { 168 if ($dbT == 'db2') $arr['f'] = (float) $arr['f']; 169 $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $d,?,?,?,?,?)"; 170 } 171 global $ADODB_PERF_MIN; 172 if ($errN != 0 || $time >= $ADODB_PERF_MIN) { 173 $ok = $conn->Execute($isql,$arr); 174 } else { 175 $ok = true; 176 } 177 $conn->debug = $saved; 178 179 if ($ok) { 180 $conn->_logsql = true; 181 } else { 182 $err2 = $conn->ErrorMsg(); 183 $conn->_logsql = true; // enable logsql error simulation 184 $perf =& NewPerfMonitor($conn); 185 if ($perf) { 186 if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr); 187 } else { 188 $ok = $conn->Execute("create table $perf_table ( 189 created varchar(50), 190 sql0 varchar(250), 191 sql1 varchar(4000), 192 params varchar(3000), 193 tracer varchar(500), 194 timer decimal(16,6))"); 195 } 196 if (!$ok) { 197 ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>"); 198 $conn->_logsql = false; 199 } 200 } 201 $connx->_errorMsg = $errM; 202 $connx->_errorCode = $errN; 203 } 204 $connx->fnExecute = 'adodb_log_sql'; 205 return $rs; 206} 207 208 209/* 210The settings data structure is an associative array that database parameter per element. 211 212Each database parameter element in the array is itself an array consisting of: 213 2140: category code, used to group related db parameters 2151: either 216 a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'", 217 b. array holding sql string and field to look for, e.g. array('show variables','table_cache'), 218 c. a string prefixed by =, then a PHP method of the class is invoked, 219 e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue', 2202: description of the database parameter 221*/ 222 223class adodb_perf { 224 var $conn; 225 var $color = '#F0F0F0'; 226 var $table = '<table border=1 bgcolor=white>'; 227 var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>'; 228 var $warnRatio = 90; 229 var $tablesSQL = false; 230 var $cliFormat = "%32s => %s \r\n"; 231 var $sql1 = 'sql1'; // used for casting sql1 to text for mssql 232 var $explain = true; 233 var $helpurl = "<a href=http://phplens.com/adodb/reference.functions.fnexecute.and.fncacheexecute.properties.html#logsql>LogSQL help</a>"; 234 var $createTableSQL = false; 235 var $maxLength = 2000; 236 237 // Sets the tablename to be used 238 function table($newtable = false) 239 { 240 static $_table; 241 242 if (!empty($newtable)) $_table = $newtable; 243 if (empty($_table)) $_table = 'adodb_logsql'; 244 return $_table; 245 } 246 247 // returns array with info to calculate CPU Load 248 function _CPULoad() 249 { 250/* 251 252cpu 524152 2662 2515228 336057010 253cpu0 264339 1408 1257951 168025827 254cpu1 259813 1254 1257277 168031181 255page 622307 25475680 256swap 24 1891 257intr 890153570 868093576 6 0 4 4 0 6 1 2 0 0 0 124 0 8098760 2 13961053 0 0 0 0 0 0 0 0 0 0 0 0 0 16 16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 258disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320) 259ctxt 66155838 260btime 1062315585 261processes 69293 262 263*/ 264 // Algorithm is taken from 265 // http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/example__obtaining_raw_performance_data.asp 266 if (strncmp(PHP_OS,'WIN',3)==0) { 267 if (PHP_VERSION == '5.0.0') return false; 268 if (PHP_VERSION == '5.0.1') return false; 269 if (PHP_VERSION == '5.0.2') return false; 270 if (PHP_VERSION == '5.0.3') return false; 271 if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737 272 273 @$c = new COM("WinMgmts:{impersonationLevel=impersonate}!Win32_PerfRawData_PerfOS_Processor.Name='_Total'"); 274 if (!$c) return false; 275 276 $info[0] = $c->PercentProcessorTime; 277 $info[1] = 0; 278 $info[2] = 0; 279 $info[3] = $c->TimeStamp_Sys100NS; 280 //print_r($info); 281 return $info; 282 } 283 284 // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com) 285 $statfile = '/proc/stat'; 286 if (!file_exists($statfile)) return false; 287 288 $fd = fopen($statfile,"r"); 289 if (!$fd) return false; 290 291 $statinfo = explode("\n",fgets($fd, 1024)); 292 fclose($fd); 293 foreach($statinfo as $line) { 294 $info = explode(" ",$line); 295 if($info[0]=="cpu") { 296 array_shift($info); // pop off "cpu" 297 if(!$info[0]) array_shift($info); // pop off blank space (if any) 298 return $info; 299 } 300 } 301 302 return false; 303 304 } 305 306 /* NOT IMPLEMENTED */ 307 function MemInfo() 308 { 309 /* 310 311 total: used: free: shared: buffers: cached: 312Mem: 1055289344 917299200 137990144 0 165437440 599773184 313Swap: 2146775040 11055104 2135719936 314MemTotal: 1030556 kB 315MemFree: 134756 kB 316MemShared: 0 kB 317Buffers: 161560 kB 318Cached: 581384 kB 319SwapCached: 4332 kB 320Active: 494468 kB 321Inact_dirty: 322856 kB 322Inact_clean: 24256 kB 323Inact_target: 168316 kB 324HighTotal: 131064 kB 325HighFree: 1024 kB 326LowTotal: 899492 kB 327LowFree: 133732 kB 328SwapTotal: 2096460 kB 329SwapFree: 2085664 kB 330Committed_AS: 348732 kB 331 */ 332 } 333 334 335 /* 336 Remember that this is client load, not db server load! 337 */ 338 var $_lastLoad; 339 function CPULoad() 340 { 341 $info = $this->_CPULoad(); 342 if (!$info) return false; 343 344 if (empty($this->_lastLoad)) { 345 sleep(1); 346 $this->_lastLoad = $info; 347 $info = $this->_CPULoad(); 348 } 349 350 $last = $this->_lastLoad; 351 $this->_lastLoad = $info; 352 353 $d_user = $info[0] - $last[0]; 354 $d_nice = $info[1] - $last[1]; 355 $d_system = $info[2] - $last[2]; 356 $d_idle = $info[3] - $last[3]; 357 358 //printf("Delta - User: %f Nice: %f System: %f Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle); 359 360 if (strncmp(PHP_OS,'WIN',3)==0) { 361 if ($d_idle < 1) $d_idle = 1; 362 return 100*(1-$d_user/$d_idle); 363 }else { 364 $total=$d_user+$d_nice+$d_system+$d_idle; 365 if ($total<1) $total=1; 366 return 100*($d_user+$d_nice+$d_system)/$total; 367 } 368 } 369 370 function Tracer($sql) 371 { 372 $perf_table = adodb_perf::table(); 373 $saveE = $this->conn->fnExecute; 374 $this->conn->fnExecute = false; 375 376 global $ADODB_FETCH_MODE; 377 $save = $ADODB_FETCH_MODE; 378 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 379 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 380 381 $sqlq = $this->conn->qstr($sql); 382 $arr = $this->conn->GetArray( 383"select count(*),tracer 384 from $perf_table where sql1=$sqlq 385 group by tracer 386 order by 1 desc"); 387 $s = ''; 388 if ($arr) { 389 $s .= '<h3>Scripts Affected</h3>'; 390 foreach($arr as $k) { 391 $s .= sprintf("%4d",$k[0]).' '.strip_tags($k[1]).'<br>'; 392 } 393 } 394 395 if (isset($savem)) $this->conn->SetFetchMode($savem); 396 $ADODB_CACHE_MODE = $save; 397 $this->conn->fnExecute = $saveE; 398 return $s; 399 } 400 401 /* 402 Explain Plan for $sql. 403 If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the 404 actual sql. 405 */ 406 function Explain($sql,$partial=false) 407 { 408 return false; 409 } 410 411 function InvalidSQL($numsql = 10) 412 { 413 414 if (isset($_GET['sql'])) return; 415 $s = '<h3>Invalid SQL</h3>'; 416 $saveE = $this->conn->fnExecute; 417 $this->conn->fnExecute = false; 418 $perf_table = adodb_perf::table(); 419 $rs =& $this->conn->SelectLimit("select distinct count(*),sql1,tracer as error_msg from $perf_table where tracer like 'ERROR:%' group by sql1,tracer order by 1 desc",$numsql);//,$numsql); 420 $this->conn->fnExecute = $saveE; 421 if ($rs) { 422 $s .= rs2html($rs,false,false,false,false); 423 } else 424 return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 425 426 return $s; 427 } 428 429 430 /* 431 This script identifies the longest running SQL 432 */ 433 function _SuspiciousSQL($numsql = 10) 434 { 435 global $ADODB_FETCH_MODE; 436 437 $perf_table = adodb_perf::table(); 438 $saveE = $this->conn->fnExecute; 439 $this->conn->fnExecute = false; 440 441 if (isset($_GET['exps']) && isset($_GET['sql'])) { 442 $partial = !empty($_GET['part']); 443 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 444 } 445 446 if (isset($_GET['sql'])) return; 447 $sql1 = $this->sql1; 448 449 $save = $ADODB_FETCH_MODE; 450 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 451 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 452 //$this->conn->debug=1; 453 $rs =& $this->conn->SelectLimit( 454 "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer 455 from $perf_table 456 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') 457 and (tracer is null or tracer not like 'ERROR:%') 458 group by sql1 459 order by 1 desc",$numsql); 460 if (isset($savem)) $this->conn->SetFetchMode($savem); 461 $ADODB_FETCH_MODE = $save; 462 $this->conn->fnExecute = $saveE; 463 464 if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 465 $s = "<h3>Suspicious SQL</h3> 466<font size=1>The following SQL have high average execution times</font><br> 467<table border=1 bgcolor=white><tr><td><b>Avg Time</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n"; 468 $max = $this->maxLength; 469 while (!$rs->EOF) { 470 $sql = $rs->fields[1]; 471 $raw = urlencode($sql); 472 if (strlen($raw)>$max-100) { 473 $sql2 = substr($sql,0,$max-500); 474 $raw = urlencode($sql2).'&part='.crc32($sql); 475 } 476 $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">"; 477 $suffix = "</a>"; 478 if ($this->explain == false || strlen($prefix)>$max) { 479 $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>'; 480 $prefix = ''; 481 } 482 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". 483 "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; 484 $rs->MoveNext(); 485 } 486 return $s."</table>"; 487 488 } 489 490 function CheckMemory() 491 { 492 return ''; 493 } 494 495 496 function SuspiciousSQL($numsql=10) 497 { 498 return adodb_perf::_SuspiciousSQL($numsql); 499 } 500 501 function ExpensiveSQL($numsql=10) 502 { 503 return adodb_perf::_ExpensiveSQL($numsql); 504 } 505 506 507 /* 508 This reports the percentage of load on the instance due to the most 509 expensive few SQL statements. Tuning these statements can often 510 make huge improvements in overall system performance. 511 */ 512 function _ExpensiveSQL($numsql = 10) 513 { 514 global $ADODB_FETCH_MODE; 515 516 $perf_table = adodb_perf::table(); 517 $saveE = $this->conn->fnExecute; 518 $this->conn->fnExecute = false; 519 520 if (isset($_GET['expe']) && isset($_GET['sql'])) { 521 $partial = !empty($_GET['part']); 522 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 523 } 524 525 if (isset($_GET['sql'])) return; 526 527 $sql1 = $this->sql1; 528 $save = $ADODB_FETCH_MODE; 529 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 530 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 531 532 $rs =& $this->conn->SelectLimit( 533 "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer 534 from $perf_table 535 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT') 536 and (tracer is null or tracer not like 'ERROR:%') 537 group by sql1 538 having count(*)>1 539 order by 1 desc",$numsql); 540 if (isset($savem)) $this->conn->SetFetchMode($savem); 541 $this->conn->fnExecute = $saveE; 542 $ADODB_FETCH_MODE = $save; 543 if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>"; 544 $s = "<h3>Expensive SQL</h3> 545<font size=1>Tuning the following SQL could reduce the server load substantially</font><br> 546<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"; 547 $max = $this->maxLength; 548 while (!$rs->EOF) { 549 $sql = $rs->fields[1]; 550 $raw = urlencode($sql); 551 if (strlen($raw)>$max-100) { 552 $sql2 = substr($sql,0,$max-500); 553 $raw = urlencode($sql2).'&part='.crc32($sql); 554 } 555 $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">"; 556 $suffix = "</a>"; 557 if($this->explain == false || strlen($prefix>$max)) { 558 $prefix = ''; 559 $suffix = ''; 560 } 561 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>". 562 "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>"; 563 $rs->MoveNext(); 564 } 565 return $s."</table>"; 566 } 567 568 /* 569 Raw function to return parameter value from $settings. 570 */ 571 function DBParameter($param) 572 { 573 if (empty($this->settings[$param])) return false; 574 $sql = $this->settings[$param][1]; 575 return $this->_DBParameter($sql); 576 } 577 578 /* 579 Raw function returning array of poll paramters 580 */ 581 function &PollParameters() 582 { 583 $arr[0] = (float)$this->DBParameter('data cache hit ratio'); 584 $arr[1] = (float)$this->DBParameter('data reads'); 585 $arr[2] = (float)$this->DBParameter('data writes'); 586 $arr[3] = (integer) $this->DBParameter('current connections'); 587 return $arr; 588 } 589 590 /* 591 Low-level Get Database Parameter 592 */ 593 function _DBParameter($sql) 594 { 595 $savelog = $this->conn->LogSQL(false); 596 if (is_array($sql)) { 597 global $ADODB_FETCH_MODE; 598 599 $sql1 = $sql[0]; 600 $key = $sql[1]; 601 if (sizeof($sql)>2) $pos = $sql[2]; 602 else $pos = 1; 603 if (sizeof($sql)>3) $coef = $sql[3]; 604 else $coef = false; 605 $ret = false; 606 $save = $ADODB_FETCH_MODE; 607 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 608 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 609 610 $rs = $this->conn->Execute($sql1); 611 612 if (isset($savem)) $this->conn->SetFetchMode($savem); 613 $ADODB_FETCH_MODE = $save; 614 if ($rs) { 615 while (!$rs->EOF) { 616 $keyf = reset($rs->fields); 617 if (trim($keyf) == $key) { 618 $ret = $rs->fields[$pos]; 619 if ($coef) $ret *= $coef; 620 break; 621 } 622 $rs->MoveNext(); 623 } 624 $rs->Close(); 625 } 626 $this->conn->LogSQL($savelog); 627 return $ret; 628 } else { 629 if (strncmp($sql,'=',1) == 0) { 630 $fn = substr($sql,1); 631 return $this->$fn(); 632 } 633 $sql = str_replace('$DATABASE',$this->conn->database,$sql); 634 $ret = $this->conn->GetOne($sql); 635 $this->conn->LogSQL($savelog); 636 637 return $ret; 638 } 639 } 640 641 /* 642 Warn if cache ratio falls below threshold. Displayed in "Description" column. 643 */ 644 function WarnCacheRatio($val) 645 { 646 if ($val < $this->warnRatio) 647 return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>'; 648 else return ''; 649 } 650 651 function clearsql() 652 { 653 $perf_table = adodb_perf::table(); 654 $this->conn->Execute("delete from $perf_table where created<".$this->conn->sysTimeStamp); 655 } 656 657 /***********************************************************************************************/ 658 // HIGH LEVEL UI FUNCTIONS 659 /***********************************************************************************************/ 660 661 662 function UI($pollsecs=5) 663 { 664 global $ADODB_LOG_CONN; 665 666 $perf_table = adodb_perf::table(); 667 $conn = $this->conn; 668 669 $app = $conn->host; 670 if ($conn->host && $conn->database) $app .= ', db='; 671 $app .= $conn->database; 672 673 if ($app) $app .= ', '; 674 $savelog = $this->conn->LogSQL(false); 675 $info = $conn->ServerInfo(); 676 if (isset($_GET['clearsql'])) { 677 $this->clearsql(); 678 } 679 $this->conn->LogSQL($savelog); 680 681 // magic quotes 682 683 if (isset($_GET['sql']) && get_magic_quotes_gpc()) { 684 $_GET['sql'] = $_GET['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$_GET['sql']); 685 } 686 687 if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10; 688 else $nsql = $_SESSION['ADODB_PERF_SQL']; 689 690 $app .= $info['description']; 691 692 693 if (isset($_GET['do'])) $do = $_GET['do']; 694 else if (isset($_POST['do'])) $do = $_POST['do']; 695 else if (isset($_GET['sql'])) $do = 'viewsql'; 696 else $do = 'stats'; 697 698 if (isset($_GET['nsql'])) { 699 if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql']; 700 } 701 echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>"; 702 if ($do == 'viewsql') $form = "<td><form># SQL:<input type=hidden value=viewsql name=do> <input type=text size=4 name=nsql value=$nsql><input type=submit value=Go></td></form>"; 703 else $form = "<td> </td>"; 704 705 $allowsql = !defined('ADODB_PERF_NO_RUN_SQL'); 706 global $ADODB_PERF_MIN; 707 $app .= " (Min sql timing \$ADODB_PERF_MIN=$ADODB_PERF_MIN secs)"; 708 709 if (empty($_GET['hidem'])) 710 echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2> 711 <b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td> 712 <a href=?do=stats><b>Performance Stats</b></a> <a href=?do=viewsql><b>View SQL</b></a> 713 <a href=?do=tables><b>View Tables</b></a> <a href=?do=poll><b>Poll Stats</b></a>", 714 $allowsql ? ' <a href=?do=dosql><b>Run SQL</b></a>' : '', 715 "$form", 716 "</tr></table>"; 717 718 719 switch ($do) { 720 default: 721 case 'stats': 722 723 if (empty($ADODB_LOG_CONN)) 724 echo "<p> <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>"; 725 echo $this->HealthCheck(); 726 //$this->conn->debug=1; 727 echo $this->CheckMemory(); 728 global $ADODB_LOG_CONN; 729 break; 730 case 'poll': 731 $self = htmlspecialchars($_SERVER['PHP_SELF']); 732 echo "<iframe width=720 height=80% 733 src=\"{$self}?do=poll2&hidem=1\"></iframe>"; 734 break; 735 case 'poll2': 736 echo "<pre>"; 737 $this->Poll($pollsecs); 738 break; 739 740 case 'dosql': 741 if (!$allowsql) break; 742 743 $this->DoSQLForm(); 744 break; 745 case 'viewsql': 746 if (empty($_GET['hidem'])) 747 echo " <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>"; 748 echo($this->SuspiciousSQL($nsql)); 749 echo($this->ExpensiveSQL($nsql)); 750 echo($this->InvalidSQL($nsql)); 751 break; 752 case 'tables': 753 echo $this->Tables(); break; 754 } 755 global $ADODB_vers; 756 echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>"; 757 } 758 759 /* 760 Runs in infinite loop, returning real-time statistics 761 */ 762 function Poll($secs=5) 763 { 764 $this->conn->fnExecute = false; 765 //$this->conn->debug=1; 766 if ($secs <= 1) $secs = 1; 767 echo "Accumulating statistics, every $secs seconds...\n";flush(); 768 $arro =& $this->PollParameters(); 769 $cnt = 0; 770 set_time_limit(0); 771 sleep($secs); 772 while (1) { 773 774 $arr =& $this->PollParameters(); 775 776 $hits = sprintf('%2.2f',$arr[0]); 777 $reads = sprintf('%12.4f',($arr[1]-$arro[1])/$secs); 778 $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs); 779 $sess = sprintf('%5d',$arr[3]); 780 781 $load = $this->CPULoad(); 782 if ($load !== false) { 783 $oslabel = 'WS-CPU%'; 784 $osval = sprintf(" %2.1f ",(float) $load); 785 }else { 786 $oslabel = ''; 787 $osval = ''; 788 } 789 if ($cnt % 10 == 0) echo " Time ".$oslabel." Hit% Sess Reads/s Writes/s\n"; 790 $cnt += 1; 791 echo date('H:i:s').' '.$osval."$hits $sess $reads $writes\n"; 792 flush(); 793 794 if (connection_aborted()) return; 795 796 sleep($secs); 797 $arro = $arr; 798 } 799 } 800 801 /* 802 Returns basic health check in a command line interface 803 */ 804 function HealthCheckCLI() 805 { 806 return $this->HealthCheck(true); 807 } 808 809 810 /* 811 Returns basic health check as HTML 812 */ 813 function HealthCheck($cli=false) 814 { 815 $saveE = $this->conn->fnExecute; 816 $this->conn->fnExecute = false; 817 if ($cli) $html = ''; 818 else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles; 819 820 $oldc = false; 821 $bgc = ''; 822 foreach($this->settings as $name => $arr) { 823 if ($arr === false) break; 824 825 if (!is_string($name)) { 826 if ($cli) $html .= " -- $arr -- \n"; 827 else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> </td></tr>"; 828 continue; 829 } 830 831 if (!is_array($arr)) break; 832 $category = $arr[0]; 833 $how = $arr[1]; 834 if (sizeof($arr)>2) $desc = $arr[2]; 835 else $desc = ' '; 836 837 838 if ($category == 'HIDE') continue; 839 840 $val = $this->_DBParameter($how); 841 842 if ($desc && strncmp($desc,"=",1) === 0) { 843 $fn = substr($desc,1); 844 $desc = $this->$fn($val); 845 } 846 847 if ($val === false) { 848 $m = $this->conn->ErrorMsg(); 849 $val = "Error: $m"; 850 } else { 851 if (is_numeric($val) && $val >= 256*1024) { 852 if ($val % (1024*1024) == 0) { 853 $val /= (1024*1024); 854 $val .= 'M'; 855 } else if ($val % 1024 == 0) { 856 $val /= 1024; 857 $val .= 'K'; 858 } 859 //$val = htmlspecialchars($val); 860 } 861 } 862 if ($category != $oldc) { 863 $oldc = $category; 864 //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color; 865 } 866 if (strlen($desc)==0) $desc = ' '; 867 if (strlen($val)==0) $val = ' '; 868 if ($cli) { 869 $html .= str_replace(' ','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc))); 870 871 }else { 872 $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n"; 873 } 874 } 875 876 if (!$cli) $html .= "</table>\n"; 877 $this->conn->fnExecute = $saveE; 878 879 return $html; 880 } 881 882 function Tables($orderby='1') 883 { 884 if (!$this->tablesSQL) return false; 885 886 $savelog = $this->conn->LogSQL(false); 887 $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby); 888 $this->conn->LogSQL($savelog); 889 $html = rs2html($rs,false,false,false,false); 890 return $html; 891 } 892 893 894 function CreateLogTable() 895 { 896 if (!$this->createTableSQL) return false; 897 898 $table = $this->table(); 899 $sql = str_replace('adodb_logsql',$table,$this->createTableSQL); 900 $savelog = $this->conn->LogSQL(false); 901 $ok = $this->conn->Execute($sql); 902 $this->conn->LogSQL($savelog); 903 return ($ok) ? true : false; 904 } 905 906 function DoSQLForm() 907 { 908 909 910 $PHP_SELF = htmlspecialchars($_SERVER['PHP_SELF']); 911 $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : ''; 912 913 if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows']; 914 else $rows = 3; 915 916 if (isset($_REQUEST['SMALLER'])) { 917 $rows /= 2; 918 if ($rows < 3) $rows = 3; 919 $_SESSION['phplens_sqlrows'] = $rows; 920 } 921 if (isset($_REQUEST['BIGGER'])) { 922 $rows *= 2; 923 $_SESSION['phplens_sqlrows'] = $rows; 924 } 925 926?> 927 928<form method="POST" action="<?php echo $PHP_SELF ?>"> 929<table><tr> 930<td> Form size: <input type="submit" value=" < " name="SMALLER"><input type="submit" value=" > > " name="BIGGER"> 931</td> 932<td align=right> 933<input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql> 934</td></tr> 935 <tr> 936 <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea> 937 </td> 938 </tr> 939 </table> 940</form> 941 942<?php 943 if (!isset($_REQUEST['sql'])) return; 944 945 $sql = $this->undomq(trim($sql)); 946 if (substr($sql,strlen($sql)-1) === ';') { 947 $print = true; 948 $sqla = $this->SplitSQL($sql); 949 } else { 950 $print = false; 951 $sqla = array($sql); 952 } 953 foreach($sqla as $sqls) { 954 955 if (!$sqls) continue; 956 957 if ($print) { 958 print "<p>".htmlspecialchars($sqls)."</p>"; 959 flush(); 960 } 961 $savelog = $this->conn->LogSQL(false); 962 $rs = $this->conn->Execute($sqls); 963 $this->conn->LogSQL($savelog); 964 if ($rs && is_object($rs) && !$rs->EOF) { 965 rs2html($rs); 966 while ($rs->NextRecordSet()) { 967 print "<table width=98% bgcolor=#C0C0FF><tr><td> </td></tr></table>"; 968 rs2html($rs); 969 } 970 } else { 971 $e1 = (integer) $this->conn->ErrorNo(); 972 $e2 = $this->conn->ErrorMsg(); 973 if (($e1) || ($e2)) { 974 if (empty($e1)) $e1 = '-1'; // postgresql fix 975 print ' '.$e1.': '.$e2; 976 } else { 977 print "<p>No Recordset returned<br></p>"; 978 } 979 } 980 } // foreach 981 } 982 983 function SplitSQL($sql) 984 { 985 $arr = explode(';',$sql); 986 return $arr; 987 } 988 989 function undomq($m) 990 { 991 if (get_magic_quotes_gpc()) { 992 // undo the damage 993 $m = str_replace('\\\\','\\',$m); 994 $m = str_replace('\"','"',$m); 995 $m = str_replace('\\\'','\'',$m); 996 } 997 return $m; 998} 999 1000 1001 /************************************************************************/ 1002 1003 /** 1004 * Reorganise multiple table-indices/statistics/.. 1005 * OptimizeMode could be given by last Parameter 1006 * 1007 * @example 1008 * <pre> 1009 * optimizeTables( 'tableA'); 1010 * </pre> 1011 * <pre> 1012 * optimizeTables( 'tableA', 'tableB', 'tableC'); 1013 * </pre> 1014 * <pre> 1015 * optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW); 1016 * </pre> 1017 * 1018 * @param string table name of the table to optimize 1019 * @param int mode optimization-mode 1020 * <code>ADODB_OPT_HIGH</code> for full optimization 1021 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 1022 * Default is LOW <code>ADODB_OPT_LOW</code> 1023 * @author Markus Staab 1024 * @return Returns <code>true</code> on success and <code>false</code> on error 1025 */ 1026 function OptimizeTables() 1027 { 1028 $args = func_get_args(); 1029 $numArgs = func_num_args(); 1030 1031 if ( $numArgs == 0) return false; 1032 1033 $mode = ADODB_OPT_LOW; 1034 $lastArg = $args[ $numArgs - 1]; 1035 if ( !is_string($lastArg)) { 1036 $mode = $lastArg; 1037 unset( $args[ $numArgs - 1]); 1038 } 1039 1040 foreach( $args as $table) { 1041 $this->optimizeTable( $table, $mode); 1042 } 1043 } 1044 1045 /** 1046 * Reorganise the table-indices/statistics/.. depending on the given mode. 1047 * Default Implementation throws an error. 1048 * 1049 * @param string table name of the table to optimize 1050 * @param int mode optimization-mode 1051 * <code>ADODB_OPT_HIGH</code> for full optimization 1052 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 1053 * Default is LOW <code>ADODB_OPT_LOW</code> 1054 * @author Markus Staab 1055 * @return Returns <code>true</code> on success and <code>false</code> on error 1056 */ 1057 function OptimizeTable( $table, $mode = ADODB_OPT_LOW) 1058 { 1059 ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType)); 1060 return false; 1061 } 1062 1063 /** 1064 * Reorganise current database. 1065 * Default implementation loops over all <code>MetaTables()</code> and 1066 * optimize each using <code>optmizeTable()</code> 1067 * 1068 * @author Markus Staab 1069 * @return Returns <code>true</code> on success and <code>false</code> on error 1070 */ 1071 function optimizeDatabase() 1072 { 1073 $conn = $this->conn; 1074 if ( !$conn) return false; 1075 1076 $tables = $conn->MetaTables( 'TABLES'); 1077 if ( !$tables ) return false; 1078 1079 foreach( $tables as $table) { 1080 if ( !$this->optimizeTable( $table)) { 1081 return false; 1082 } 1083 } 1084 1085 return true; 1086 } 1087 // end hack 1088} 1089 1090?>