lib/adodb/adodb-perf.inc.php

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