lib/adodb/adodb-perf.inc.php

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