8e6e6cc72c09fdab0bbb9e633a8a7963ebfa6fa2
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / adodb-perf.inc.php
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
25 global $ADODB_PERF_MIN;
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 */
351 var $_lastLoad;
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
1012 /**
1013 * Reorganise multiple table-indices/statistics/..
1014 * OptimizeMode could be given by last Parameter
1015 *
1016 * @example
1017 * <pre>
1018 * optimizeTables( 'tableA');
1019 * </pre>
1020 * <pre>
1021 * optimizeTables( 'tableA', 'tableB', 'tableC');
1022 * </pre>
1023 * <pre>
1024 * optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW);
1025 * </pre>
1026 *
1027 * @param string table name of the table to optimize
1028 * @param int mode optimization-mode
1029 * <code>ADODB_OPT_HIGH</code> for full optimization
1030 * <code>ADODB_OPT_LOW</code> for CPU-less optimization
1031 * Default is LOW <code>ADODB_OPT_LOW</code>
1032 * @author Markus Staab
1033 * @return Returns <code>true</code> on success and <code>false</code> on error
1034 */
1035 function 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
1054 /**
1055 * Reorganise the table-indices/statistics/.. depending on the given mode.
1056 * Default Implementation throws an error.
1057 *
1058 * @param string table name of the table to optimize
1059 * @param int mode optimization-mode
1060 * <code>ADODB_OPT_HIGH</code> for full optimization
1061 * <code>ADODB_OPT_LOW</code> for CPU-less optimization
1062 * Default is LOW <code>ADODB_OPT_LOW</code>
1063 * @author Markus Staab
1064 * @return Returns <code>true</code> on success and <code>false</code> on error
1065 */
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
1072 /**
1073 * Reorganise current database.
1074 * Default implementation loops over all <code>MetaTables()</code> and
1075 * optimize each using <code>optmizeTable()</code>
1076 *
1077 * @author Markus Staab
1078 * @return Returns <code>true</code> on success and <code>false</code> on error
1079 */
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 }