gacl/adodb/adodb-perf.inc.php

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