1<?php
2/*
3V4.66 28 Sept 2005  (c) 2000-2005 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
19if (!defined(ADODB_DIR)) include_once(dirname(__FILE__).'/adodb.inc.php');
20include_once(ADODB_DIR.'/tohtml.inc.php');
21
22define( 'ADODB_OPT_HIGH', 2);
23define( 'ADODB_OPT_LOW', 1);
24
25// returns in K the memory of current process, or 0 if not known
26function 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 .
51function adodb_round($n,$prec)
52{
53	return number_format($n, $prec, '.', '');
54}
55
56/* return microtime value as a float */
57function adodb_microtime()
58{
59	$t = microtime();
60	$t = explode(' ',$t);
61	return (float)$t[1]+ (float)$t[0];
62}
63
64/* sql code timing */
65function& 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') {
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/*
189The settings data structure is an associative array that database parameter per element.
190
191Each database parameter element in the array is itself an array consisting of:
192
1930: category code, used to group related db parameters
1941: 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',
1992: description of the database parameter
200*/
201
202class 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
231cpu  524152 2662 2515228 336057010
232cpu0 264339 1408 1257951 168025827
233cpu1 259813 1254 1257277 168031181
234page 622307 25475680
235swap 24 1891
236intr 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
237disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320)
238ctxt 66155838
239btime 1062315585
240processes 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:
291Mem:  1055289344 917299200 137990144        0 165437440 599773184
292Swap: 2146775040 11055104 2135719936
293MemTotal:      1030556 kB
294MemFree:        134756 kB
295MemShared:           0 kB
296Buffers:        161560 kB
297Cached:         581384 kB
298SwapCached:       4332 kB
299Active:         494468 kB
300Inact_dirty:    322856 kB
301Inact_clean:     24256 kB
302Inact_target:   168316 kB
303HighTotal:      131064 kB
304HighFree:         1024 kB
305LowTotal:       899492 kB
306LowFree:        133732 kB
307SwapTotal:     2096460 kB
308SwapFree:      2085664 kB
309Committed_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->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		$savelog = $this->conn->LogSQL(false);
864		$ok = $this->conn->Execute($this->createTableSQL);
865		$this->conn->LogSQL($savelog);
866		return ($ok) ? true : false;
867	}
868
869	function DoSQLForm()
870	{
871
872
873		$PHP_SELF = $_SERVER['PHP_SELF'];
874		$sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : '';
875
876		if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows'];
877		else $rows = 3;
878
879		if (isset($_REQUEST['SMALLER'])) {
880			$rows /= 2;
881			if ($rows < 3) $rows = 3;
882			$_SESSION['phplens_sqlrows'] = $rows;
883		}
884		if (isset($_REQUEST['BIGGER'])) {
885			$rows *= 2;
886			$_SESSION['phplens_sqlrows'] = $rows;
887		}
888
889?>
890
891<form method="POST" action="<?php echo $PHP_SELF ?>">
892<table><tr>
893<td> Form size: <input type="submit" value=" &lt; " name="SMALLER"><input type="submit" value=" &gt; &gt; " name="BIGGER">
894</td>
895<td align=right>
896<input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql>
897</td></tr>
898  <tr>
899  <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea>
900  </td>
901  </tr>
902 </table>
903</form>
904
905<?php
906		if (!isset($_REQUEST['sql'])) return;
907
908		$sql = $this->undomq(trim($sql));
909		if (substr($sql,strlen($sql)-1) === ';') {
910			$print = true;
911			$sqla = $this->SplitSQL($sql);
912		} else  {
913			$print = false;
914			$sqla = array($sql);
915		}
916		foreach($sqla as $sqls) {
917
918			if (!$sqls) continue;
919
920			if ($print) {
921				print "<p>".htmlspecialchars($sqls)."</p>";
922				flush();
923			}
924			$savelog = $this->conn->LogSQL(false);
925			$rs = $this->conn->Execute($sqls);
926			$this->conn->LogSQL($savelog);
927			if ($rs && is_object($rs) && !$rs->EOF) {
928				rs2html($rs);
929				while ($rs->NextRecordSet()) {
930					print "<table width=98% bgcolor=#C0C0FF><tr><td>&nbsp;</td></tr></table>";
931					rs2html($rs);
932				}
933			} else {
934				$e1 = (integer) $this->conn->ErrorNo();
935				$e2 = $this->conn->ErrorMsg();
936				if (($e1) || ($e2)) {
937					if (empty($e1)) $e1 = '-1'; // postgresql fix
938					print ' &nbsp; '.$e1.': '.$e2;
939				} else {
940					print "<p>No Recordset returned<br></p>";
941				}
942			}
943		} // foreach
944	}
945
946	function SplitSQL($sql)
947	{
948		$arr = explode(';',$sql);
949		return $arr;
950	}
951
952	function undomq(&$m)
953	{
954	if (get_magic_quotes_gpc()) {
955		// undo the damage
956		$m = str_replace('\\\\','\\',$m);
957		$m = str_replace('\"','"',$m);
958		$m = str_replace('\\\'','\'',$m);
959	}
960	return $m;
961}
962
963
964   /************************************************************************/
965
966    /**
967     * Reorganise multiple table-indices/statistics/..
968     * OptimizeMode could be given by last Parameter
969     *
970     * @example
971     *      <pre>
972     *          optimizeTables( 'tableA');
973     *      </pre>
974     *      <pre>
975     *          optimizeTables( 'tableA', 'tableB', 'tableC');
976     *      </pre>
977     *      <pre>
978     *          optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW);
979     *      </pre>
980     *
981     * @param string table name of the table to optimize
982     * @param int mode optimization-mode
983     *      <code>ADODB_OPT_HIGH</code> for full optimization
984     *      <code>ADODB_OPT_LOW</code> for CPU-less optimization
985     *      Default is LOW <code>ADODB_OPT_LOW</code>
986     * @author Markus Staab
987     * @return Returns <code>true</code> on success and <code>false</code> on error
988     */
989    function OptimizeTables()
990    {
991        $args = func_get_args();
992        $numArgs = func_num_args();
993
994        if ( $numArgs == 0) return false;
995
996        $mode = ADODB_OPT_LOW;
997        $lastArg = $args[ $numArgs - 1];
998        if ( !is_string($lastArg)) {
999            $mode = $lastArg;
1000            unset( $args[ $numArgs - 1]);
1001        }
1002
1003        foreach( $args as $table) {
1004            $this->optimizeTable( $table, $mode);
1005        }
1006	}
1007
1008    /**
1009     * Reorganise the table-indices/statistics/.. depending on the given mode.
1010     * Default Implementation throws an error.
1011     *
1012     * @param string table name of the table to optimize
1013     * @param int mode optimization-mode
1014     *      <code>ADODB_OPT_HIGH</code> for full optimization
1015     *      <code>ADODB_OPT_LOW</code> for CPU-less optimization
1016     *      Default is LOW <code>ADODB_OPT_LOW</code>
1017     * @author Markus Staab
1018     * @return Returns <code>true</code> on success and <code>false</code> on error
1019     */
1020    function OptimizeTable( $table, $mode = ADODB_OPT_LOW)
1021    {
1022        ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType));
1023        return false;
1024    }
1025
1026    /**
1027     * Reorganise current database.
1028     * Default implementation loops over all <code>MetaTables()</code> and
1029     * optimize each using <code>optmizeTable()</code>
1030     *
1031     * @author Markus Staab
1032     * @return Returns <code>true</code> on success and <code>false</code> on error
1033     */
1034    function optimizeDatabase()
1035    {
1036        $conn = $this->conn;
1037        if ( !$conn) return false;
1038
1039        $tables = $conn->MetaTables( 'TABLES');
1040        if ( !$tables ) return false;
1041
1042        foreach( $tables as $table) {
1043            if ( !$this->optimizeTable( $table)) {
1044                return false;
1045            }
1046        }
1047
1048        return true;
1049    }
1050    // end hack
1051}
1052
1053?>