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