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