vendor/adodb/adodb-perf.inc.php

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