lib/adodb/adodb-perf.inc.php

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