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