| [ Index ] |
PHP Cross Reference of Moodle 1.9.3 [Build 15-Oct-2008] |
[Summary view] [Print] [Text view]
1 <?php 2 /* 3 V4.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 19 if (!defined('ADODB_DIR')) include_once(dirname(__FILE__).'/adodb.inc.php'); 20 include_once (ADODB_DIR.'/tohtml.inc.php'); 21 22 global $ADODB_PERF_MIN; 23 $ADODB_PERF_MIN = 0.05; // log only if >= minimum number of secs to run 24 25 define( 'ADODB_OPT_HIGH', 2); 26 define( 'ADODB_OPT_LOW', 1); 27 28 // returns in K the memory of current process, or 0 if not known 29 function 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 . 54 function adodb_round($n,$prec) 55 { 56 return number_format($n, $prec, '.', ''); 57 } 58 59 /* return microtime value as a float */ 60 function adodb_microtime() 61 { 62 $t = microtime(); 63 $t = explode(' ',$t); 64 return (float)$t[1]+ (float)$t[0]; 65 } 66 67 /* sql code timing */ 68 function& 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 = split(' ',$t0); 94 $a0 = (float)$a0[1]+(float)$a0[0]; 95 96 $a1 = split(' ',$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 .= $_SERVER['PHP_SELF']; 120 } else 121 if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.$_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 /* 210 The settings data structure is an associative array that database parameter per element. 211 212 Each database parameter element in the array is itself an array consisting of: 213 214 0: category code, used to group related db parameters 215 1: 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', 220 2: description of the database parameter 221 */ 222 223 class 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 252 cpu 524152 2662 2515228 336057010 253 cpu0 264339 1408 1257951 168025827 254 cpu1 259813 1254 1257277 168031181 255 page 622307 25475680 256 swap 24 1891 257 intr 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 258 disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320) 259 ctxt 66155838 260 btime 1062315585 261 processes 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: 312 Mem: 1055289344 917299200 137990144 0 165437440 599773184 313 Swap: 2146775040 11055104 2135719936 314 MemTotal: 1030556 kB 315 MemFree: 134756 kB 316 MemShared: 0 kB 317 Buffers: 161560 kB 318 Cached: 581384 kB 319 SwapCached: 4332 kB 320 Active: 494468 kB 321 Inact_dirty: 322856 kB 322 Inact_clean: 24256 kB 323 Inact_target: 168316 kB 324 HighTotal: 131064 kB 325 HighFree: 1024 kB 326 LowTotal: 899492 kB 327 LowFree: 133732 kB 328 SwapTotal: 2096460 kB 329 SwapFree: 2085664 kB 330 Committed_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 echo "<iframe width=720 height=80% 732 src=\"{$_SERVER['PHP_SELF']}?do=poll2&hidem=1\"></iframe>"; 733 break; 734 case 'poll2': 735 echo "<pre>"; 736 $this->Poll($pollsecs); 737 break; 738 739 case 'dosql': 740 if (!$allowsql) break; 741 742 $this->DoSQLForm(); 743 break; 744 case 'viewsql': 745 if (empty($_GET['hidem'])) 746 echo " <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>"; 747 echo($this->SuspiciousSQL($nsql)); 748 echo($this->ExpensiveSQL($nsql)); 749 echo($this->InvalidSQL($nsql)); 750 break; 751 case 'tables': 752 echo $this->Tables(); break; 753 } 754 global $ADODB_vers; 755 echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>"; 756 } 757 758 /* 759 Runs in infinite loop, returning real-time statistics 760 */ 761 function Poll($secs=5) 762 { 763 $this->conn->fnExecute = false; 764 //$this->conn->debug=1; 765 if ($secs <= 1) $secs = 1; 766 echo "Accumulating statistics, every $secs seconds...\n";flush(); 767 $arro =& $this->PollParameters(); 768 $cnt = 0; 769 set_time_limit(0); 770 sleep($secs); 771 while (1) { 772 773 $arr =& $this->PollParameters(); 774 775 $hits = sprintf('%2.2f',$arr[0]); 776 $reads = sprintf('%12.4f',($arr[1]-$arro[1])/$secs); 777 $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs); 778 $sess = sprintf('%5d',$arr[3]); 779 780 $load = $this->CPULoad(); 781 if ($load !== false) { 782 $oslabel = 'WS-CPU%'; 783 $osval = sprintf(" %2.1f ",(float) $load); 784 }else { 785 $oslabel = ''; 786 $osval = ''; 787 } 788 if ($cnt % 10 == 0) echo " Time ".$oslabel." Hit% Sess Reads/s Writes/s\n"; 789 $cnt += 1; 790 echo date('H:i:s').' '.$osval."$hits $sess $reads $writes\n"; 791 flush(); 792 793 if (connection_aborted()) return; 794 795 sleep($secs); 796 $arro = $arr; 797 } 798 } 799 800 /* 801 Returns basic health check in a command line interface 802 */ 803 function HealthCheckCLI() 804 { 805 return $this->HealthCheck(true); 806 } 807 808 809 /* 810 Returns basic health check as HTML 811 */ 812 function HealthCheck($cli=false) 813 { 814 $saveE = $this->conn->fnExecute; 815 $this->conn->fnExecute = false; 816 if ($cli) $html = ''; 817 else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles; 818 819 $oldc = false; 820 $bgc = ''; 821 foreach($this->settings as $name => $arr) { 822 if ($arr === false) break; 823 824 if (!is_string($name)) { 825 if ($cli) $html .= " -- $arr -- \n"; 826 else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> </td></tr>"; 827 continue; 828 } 829 830 if (!is_array($arr)) break; 831 $category = $arr[0]; 832 $how = $arr[1]; 833 if (sizeof($arr)>2) $desc = $arr[2]; 834 else $desc = ' '; 835 836 837 if ($category == 'HIDE') continue; 838 839 $val = $this->_DBParameter($how); 840 841 if ($desc && strncmp($desc,"=",1) === 0) { 842 $fn = substr($desc,1); 843 $desc = $this->$fn($val); 844 } 845 846 if ($val === false) { 847 $m = $this->conn->ErrorMsg(); 848 $val = "Error: $m"; 849 } else { 850 if (is_numeric($val) && $val >= 256*1024) { 851 if ($val % (1024*1024) == 0) { 852 $val /= (1024*1024); 853 $val .= 'M'; 854 } else if ($val % 1024 == 0) { 855 $val /= 1024; 856 $val .= 'K'; 857 } 858 //$val = htmlspecialchars($val); 859 } 860 } 861 if ($category != $oldc) { 862 $oldc = $category; 863 //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color; 864 } 865 if (strlen($desc)==0) $desc = ' '; 866 if (strlen($val)==0) $val = ' '; 867 if ($cli) { 868 $html .= str_replace(' ','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc))); 869 870 }else { 871 $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n"; 872 } 873 } 874 875 if (!$cli) $html .= "</table>\n"; 876 $this->conn->fnExecute = $saveE; 877 878 return $html; 879 } 880 881 function Tables($orderby='1') 882 { 883 if (!$this->tablesSQL) return false; 884 885 $savelog = $this->conn->LogSQL(false); 886 $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby); 887 $this->conn->LogSQL($savelog); 888 $html = rs2html($rs,false,false,false,false); 889 return $html; 890 } 891 892 893 function CreateLogTable() 894 { 895 if (!$this->createTableSQL) return false; 896 897 $table = $this->table(); 898 $sql = str_replace('adodb_logsql',$table,$this->createTableSQL); 899 $savelog = $this->conn->LogSQL(false); 900 $ok = $this->conn->Execute($sql); 901 $this->conn->LogSQL($savelog); 902 return ($ok) ? true : false; 903 } 904 905 function DoSQLForm() 906 { 907 908 909 $PHP_SELF = $_SERVER['PHP_SELF']; 910 $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : ''; 911 912 if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows']; 913 else $rows = 3; 914 915 if (isset($_REQUEST['SMALLER'])) { 916 $rows /= 2; 917 if ($rows < 3) $rows = 3; 918 $_SESSION['phplens_sqlrows'] = $rows; 919 } 920 if (isset($_REQUEST['BIGGER'])) { 921 $rows *= 2; 922 $_SESSION['phplens_sqlrows'] = $rows; 923 } 924 925 ?> 926 927 <form method="POST" action="<?php echo $PHP_SELF ?>"> 928 <table><tr> 929 <td> Form size: <input type="submit" value=" < " name="SMALLER"><input type="submit" value=" > > " name="BIGGER"> 930 </td> 931 <td align=right> 932 <input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql> 933 </td></tr> 934 <tr> 935 <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea> 936 </td> 937 </tr> 938 </table> 939 </form> 940 941 <?php 942 if (!isset($_REQUEST['sql'])) return; 943 944 $sql = $this->undomq(trim($sql)); 945 if (substr($sql,strlen($sql)-1) === ';') { 946 $print = true; 947 $sqla = $this->SplitSQL($sql); 948 } else { 949 $print = false; 950 $sqla = array($sql); 951 } 952 foreach($sqla as $sqls) { 953 954 if (!$sqls) continue; 955 956 if ($print) { 957 print "<p>".htmlspecialchars($sqls)."</p>"; 958 flush(); 959 } 960 $savelog = $this->conn->LogSQL(false); 961 $rs = $this->conn->Execute($sqls); 962 $this->conn->LogSQL($savelog); 963 if ($rs && is_object($rs) && !$rs->EOF) { 964 rs2html($rs); 965 while ($rs->NextRecordSet()) { 966 print "<table width=98% bgcolor=#C0C0FF><tr><td> </td></tr></table>"; 967 rs2html($rs); 968 } 969 } else { 970 $e1 = (integer) $this->conn->ErrorNo(); 971 $e2 = $this->conn->ErrorMsg(); 972 if (($e1) || ($e2)) { 973 if (empty($e1)) $e1 = '-1'; // postgresql fix 974 print ' '.$e1.': '.$e2; 975 } else { 976 print "<p>No Recordset returned<br></p>"; 977 } 978 } 979 } // foreach 980 } 981 982 function SplitSQL($sql) 983 { 984 $arr = explode(';',$sql); 985 return $arr; 986 } 987 988 function undomq($m) 989 { 990 if (get_magic_quotes_gpc()) { 991 // undo the damage 992 $m = str_replace('\\\\','\\',$m); 993 $m = str_replace('\"','"',$m); 994 $m = str_replace('\\\'','\'',$m); 995 } 996 return $m; 997 } 998 999 1000 /************************************************************************/ 1001 1002 /** 1003 * Reorganise multiple table-indices/statistics/.. 1004 * OptimizeMode could be given by last Parameter 1005 * 1006 * @example 1007 * <pre> 1008 * optimizeTables( 'tableA'); 1009 * </pre> 1010 * <pre> 1011 * optimizeTables( 'tableA', 'tableB', 'tableC'); 1012 * </pre> 1013 * <pre> 1014 * optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW); 1015 * </pre> 1016 * 1017 * @param string table name of the table to optimize 1018 * @param int mode optimization-mode 1019 * <code>ADODB_OPT_HIGH</code> for full optimization 1020 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 1021 * Default is LOW <code>ADODB_OPT_LOW</code> 1022 * @author Markus Staab 1023 * @return Returns <code>true</code> on success and <code>false</code> on error 1024 */ 1025 function OptimizeTables() 1026 { 1027 $args = func_get_args(); 1028 $numArgs = func_num_args(); 1029 1030 if ( $numArgs == 0) return false; 1031 1032 $mode = ADODB_OPT_LOW; 1033 $lastArg = $args[ $numArgs - 1]; 1034 if ( !is_string($lastArg)) { 1035 $mode = $lastArg; 1036 unset( $args[ $numArgs - 1]); 1037 } 1038 1039 foreach( $args as $table) { 1040 $this->optimizeTable( $table, $mode); 1041 } 1042 } 1043 1044 /** 1045 * Reorganise the table-indices/statistics/.. depending on the given mode. 1046 * Default Implementation throws an error. 1047 * 1048 * @param string table name of the table to optimize 1049 * @param int mode optimization-mode 1050 * <code>ADODB_OPT_HIGH</code> for full optimization 1051 * <code>ADODB_OPT_LOW</code> for CPU-less optimization 1052 * Default is LOW <code>ADODB_OPT_LOW</code> 1053 * @author Markus Staab 1054 * @return Returns <code>true</code> on success and <code>false</code> on error 1055 */ 1056 function OptimizeTable( $table, $mode = ADODB_OPT_LOW) 1057 { 1058 ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType)); 1059 return false; 1060 } 1061 1062 /** 1063 * Reorganise current database. 1064 * Default implementation loops over all <code>MetaTables()</code> and 1065 * optimize each using <code>optmizeTable()</code> 1066 * 1067 * @author Markus Staab 1068 * @return Returns <code>true</code> on success and <code>false</code> on error 1069 */ 1070 function optimizeDatabase() 1071 { 1072 $conn = $this->conn; 1073 if ( !$conn) return false; 1074 1075 $tables = $conn->MetaTables( 'TABLES'); 1076 if ( !$tables ) return false; 1077 1078 foreach( $tables as $table) { 1079 if ( !$this->optimizeTable( $table)) { 1080 return false; 1081 } 1082 } 1083 1084 return true; 1085 } 1086 // end hack 1087 } 1088 1089 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Jan 14 11:33:29 2009 | Cross-referenced by PHPXref 0.7 |