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