lib/WikiDB/adodb/adodb-perf.inc.php

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