library/adodb/adodb-perf.inc.php

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