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