1 <?php

2 /*

3 V5.19 23-Apr-2014 (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.

4  Released under both BSD license and Lesser GPL library license.

5  Whenever there is any discrepancy between the two licenses,

6  the BSD license will take precedence. See License.txt.

7  Set tabs to 4 for best viewing.

8 

9  Latest version is available at http://adodb.sourceforge.net

10 

11  Library for basic performance monitoring and tuning.

12 

13  My apologies if you see code mixed with presentation. The presentation suits

14  my needs. If you want to separate code from presentation, be my guest. Patches

15  are welcome.

16 

17 */

18 

19 if (!defined('ADODB_DIR')) include_once(dirname(__FILE__).'/adodb.inc.php');

20 include_once(ADODB_DIR.'/tohtml.inc.php');

21 

22 define( 'ADODB_OPT_HIGH', 2);

23 define( 'ADODB_OPT_LOW', 1);

24 

25 global $ADODB_PERF_MIN;

26 $ADODB_PERF_MIN = 0.05; // log only if >= minimum number of secs to run

27 

28 

29 // returns in K the memory of current process, or 0 if not known

30 function adodb_getmem()

31 {

32  if (function_exists('memory_get_usage'))

33  return (integer) ((memory_get_usage()+512)/1024);

34 

35  $pid = getmypid();

36 

37  if ( strncmp(strtoupper(PHP_OS),'WIN',3)==0) {

38  $output = array();

39 

40  exec('tasklist /FI "PID eq ' . $pid. '" /FO LIST', $output);

41  return substr($output[5], strpos($output[5], ':') + 1);

42  }

43 

44  /* Hopefully UNIX */

45  exec("ps --pid $pid --no-headers -o%mem,size", $output);

46  if (sizeof($output) == 0) return 0;

47 

48  $memarr = explode(' ',$output[0]);

49  if (sizeof($memarr)>=2) return (integer) $memarr[1];

50 

51  return 0;

52 }

53 

54 // avoids localization problems where , is used instead of .

55 function adodb_round($n,$prec)

56 {

57  return number_format($n, $prec, '.', '');

58 }

59 

60 /* obsolete: return microtime value as a float. Retained for backward compat */

61 function adodb_microtime()

62 {

63  return microtime(true);

64 }

65 

66 /* sql code timing */

67 function adodb_log_sql(&$connx,$sql,$inputarr)

68 {

69  $perf_table = adodb_perf::table();

70  $connx->fnExecute = false;

71  $a0 = microtime(true);

72  $rs = $connx->Execute($sql,$inputarr);

73  $a1 = microtime(true);

74 

75  if (!empty($connx->_logsql) && (empty($connx->_logsqlErrors) || !$rs)) {

76  global $ADODB_LOG_CONN;

77 

78  if (!empty($ADODB_LOG_CONN)) {

79  $conn = $ADODB_LOG_CONN;

80  if ($conn->databaseType != $connx->databaseType)

81  $prefix = '/*dbx='.$connx->databaseType .'*/ ';

82  else

83  $prefix = '';

84  } else {

85  $conn = $connx;

86  $prefix = '';

87  }

88 

89  $conn->_logsql = false; // disable logsql error simulation

90  $dbT = $conn->databaseType;

91 

92  $time = $a1 - $a0;

93 

94  if (!$rs) {

95  $errM = $connx->ErrorMsg();

96  $errN = $connx->ErrorNo();

97  $conn->lastInsID = 0;

98  $tracer = substr('ERROR: '.htmlspecialchars($errM),0,250);

99  } else {

100  $tracer = '';

101  $errM = '';

102  $errN = 0;

103  $dbg = $conn->debug;

104  $conn->debug = false;

105  if (!is_object($rs) || $rs->dataProvider == 'empty')

106  $conn->_affected = $conn->affected_rows(true);

107  $conn->lastInsID = @$conn->Insert_ID();

108  $conn->debug = $dbg;

109  }

110  if (isset($_SERVER['HTTP_HOST'])) {

111  $tracer .= '<br>'.$_SERVER['HTTP_HOST'];

112  if (isset($_SERVER['PHP_SELF'])) $tracer .= htmlspecialchars($_SERVER['PHP_SELF']);

113  } else

114  if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.htmlspecialchars($_SERVER['PHP_SELF']);

115  //$tracer .= (string) adodb_backtrace(false);

116 

117  $tracer = (string) substr($tracer,0,500);

118 

119  if (is_array($inputarr)) {

120  if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr);

121  else {

122  // Quote string parameters so we can see them in the

123  // performance stats. This helps spot disabled indexes.

124  $xar_params = $inputarr;

125  foreach ($xar_params as $xar_param_key => $xar_param) {

126  if (gettype($xar_param) == 'string')

127  $xar_params[$xar_param_key] = '"' . $xar_param . '"';

128  }

129  $params = implode(', ', $xar_params);

130  if (strlen($params) >= 3000) $params = substr($params, 0, 3000);

131  }

132  } else {

133  $params = '';

134  }

135 

136  if (is_array($sql)) $sql = $sql[0];

137  if ($prefix) $sql = $prefix.$sql;

138  $arr = array('b'=>strlen($sql).'.'.crc32($sql),

139  'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6));

140  //var_dump($arr);

141  $saved = $conn->debug;

142  $conn->debug = 0;

143 

144  $d = $conn->sysTimeStamp;

145  if (empty($d)) $d = date("'Y-m-d H:i:s'");

146  if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') {

147  $isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)";

148  } else if ($dbT == 'odbc_mssql' || $dbT == 'informix' || strncmp($dbT,'odbtp',4)==0) {

149  $timer = $arr['f'];

150  if ($dbT == 'informix') $sql2 = substr($sql2,0,230);

151 

152  $sql1 = $conn->qstr($arr['b']);

153  $sql2 = $conn->qstr($arr['c']);

154  $params = $conn->qstr($arr['d']);

155  $tracer = $conn->qstr($arr['e']);

156 

157  $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)";

158  if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql);

159  $arr = false;

160  } else {

161  if ($dbT == 'db2') $arr['f'] = (float) $arr['f'];

162  $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $d,?,?,?,?,?)";

163  }

164 

165  global $ADODB_PERF_MIN;

166  if ($errN != 0 || $time >= $ADODB_PERF_MIN) {

167  $ok = $conn->Execute($isql,$arr);

168  } else

169  $ok = true;

170 

171  $conn->debug = $saved;

172 

173  if ($ok) {

174  $conn->_logsql = true;

175  } else {

176  $err2 = $conn->ErrorMsg();

177  $conn->_logsql = true; // enable logsql error simulation

178  $perf = NewPerfMonitor($conn);

179  if ($perf) {

180  if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr);

181  } else {

182  $ok = $conn->Execute("create table $perf_table (

183  created varchar(50),

184  sql0 varchar(250),

185  sql1 varchar(4000),

186  params varchar(3000),

187  tracer varchar(500),

188  timer decimal(16,6))");

189  }

190  if (!$ok) {

191  ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>");

192  $conn->_logsql = false;

193  }

194  }

195  $connx->_errorMsg = $errM;

196  $connx->_errorCode = $errN;

197  }

198  $connx->fnExecute = 'adodb_log_sql';

199  return $rs;

200 }

201 

202 

203 /*

204 The settings data structure is an associative array that database parameter per element.

205 

206 Each database parameter element in the array is itself an array consisting of:

207 

208 0: category code, used to group related db parameters

209 1: either

210  a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'",

211  b. array holding sql string and field to look for, e.g. array('show variables','table_cache'),

212  c. a string prefixed by =, then a PHP method of the class is invoked,

213  e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue',

214 2: description of the database parameter

215 */

216 

217 class adodb_perf {

218  var $conn;

219  var $color = '#F0F0F0';

220  var $table = '<table border=1 bgcolor=white>';

221  var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>';

222  var $warnRatio = 90;

223  var $tablesSQL = false;

224  var $cliFormat = "%32s => %s \r\n";

225  var $sql1 = 'sql1'; // used for casting sql1 to text for mssql

226  var $explain = true;

227  var $helpurl = "<a href=http://phplens.com/adodb/reference.functions.fnexecute.and.fncacheexecute.properties.html#logsql>LogSQL help</a>";

228  var $createTableSQL = false;

229  var $maxLength = 2000;

230 

231  // Sets the tablename to be used

232  static function table($newtable = false)

233  {

234  static $_table;

235 

236  if (!empty($newtable)) $_table = $newtable;

237  if (empty($_table)) $_table = 'adodb_logsql';

238  return $_table;

239  }

240 

241  // returns array with info to calculate CPU Load

242  function _CPULoad()

243  {

244 /*

245 

246 cpu 524152 2662 2515228 336057010

247 cpu0 264339 1408 1257951 168025827

248 cpu1 259813 1254 1257277 168031181

249 page 622307 25475680

250 swap 24 1891

251 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

252 disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320)

253 ctxt 66155838

254 btime 1062315585

255 processes 69293

256 

257 */

258  // Algorithm is taken from

259  // http://social.technet.microsoft.com/Forums/en-US/winservergen/thread/414b0e1b-499c-411e-8a02-6a12e339c0f1/

260  if (strncmp(PHP_OS,'WIN',3)==0) {

261  if (PHP_VERSION == '5.0.0') return false;

262  if (PHP_VERSION == '5.0.1') return false;

263  if (PHP_VERSION == '5.0.2') return false;

264  if (PHP_VERSION == '5.0.3') return false;

265  if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737

266 

267  static $FAIL = false;

268  if ($FAIL) return false;

269 

270  $objName = "winmgmts:{impersonationLevel=impersonate}!\\\\.\\root\\CIMV2";

271  $myQuery = "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor WHERE Name = '_Total'";

272 

273  try {

274  @$objWMIService = new COM($objName);

275  if (!$objWMIService) {

276  $FAIL = true;

277  return false;

278  }

279 

280  $info[0] = -1;

281  $info[1] = 0;

282  $info[2] = 0;

283  $info[3] = 0;

284  foreach($objWMIService->ExecQuery($myQuery) as $objItem) {

285  $info[0] = $objItem->PercentProcessorTime();

286  }

287 

288  } catch(Exception $e) {

289  $FAIL = true;

290  echo $e->getMessage();

291  return false;

292  }

293 

294  return $info;

295  }

296 

297  // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com)

298  $statfile = '/proc/stat';

299  if (!file_exists($statfile)) return false;

300 

301  $fd = fopen($statfile,"r");

302  if (!$fd) return false;

303 

304  $statinfo = explode("\n",fgets($fd, 1024));

305  fclose($fd);

306  foreach($statinfo as $line) {

307  $info = explode(" ",$line);

308  if($info[0]=="cpu") {

309  array_shift($info); // pop off "cpu"

310  if(!$info[0]) array_shift($info); // pop off blank space (if any)

311  return $info;

312  }

313  }

314 

315  return false;

316 

317  }

318 

319  /* NOT IMPLEMENTED */

320  function MemInfo()

321  {

322  /*

323 

324  total: used: free: shared: buffers: cached:

325 Mem: 1055289344 917299200 137990144 0 165437440 599773184

326 Swap: 2146775040 11055104 2135719936

327 MemTotal: 1030556 kB

328 MemFree: 134756 kB

329 MemShared: 0 kB

330 Buffers: 161560 kB

331 Cached: 581384 kB

332 SwapCached: 4332 kB

333 Active: 494468 kB

334 Inact_dirty: 322856 kB

335 Inact_clean: 24256 kB

336 Inact_target: 168316 kB

337 HighTotal: 131064 kB

338 HighFree: 1024 kB

339 LowTotal: 899492 kB

340 LowFree: 133732 kB

341 SwapTotal: 2096460 kB

342 SwapFree: 2085664 kB

343 Committed_AS: 348732 kB

344  */

345  }

346 

347 

348  /*

349  Remember that this is client load, not db server load!

350  */

351  var $_lastLoad;

352  function CPULoad()

353  {

354  $info = $this->_CPULoad();

355  if (!$info) return false;

356 

357  if (strncmp(PHP_OS,'WIN',3)==0) {

358  return (integer) $info[0];

359  }else {

360  if (empty($this->_lastLoad)) {

361  sleep(1);

362  $this->_lastLoad = $info;

363  $info = $this->_CPULoad();

364  }

365 

366  $last = $this->_lastLoad;

367  $this->_lastLoad = $info;

368 

369  $d_user = $info[0] - $last[0];

370  $d_nice = $info[1] - $last[1];

371  $d_system = $info[2] - $last[2];

372  $d_idle = $info[3] - $last[3];

373 

374  //printf("Delta - User: %f Nice: %f System: %f Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle);

375 

376  $total=$d_user+$d_nice+$d_system+$d_idle;

377  if ($total<1) $total=1;

378  return 100*($d_user+$d_nice+$d_system)/$total;

379  }

380  }

381 

382  function Tracer($sql)

383  {

384  $perf_table = adodb_perf::table();

385  $saveE = $this->conn->fnExecute;

386  $this->conn->fnExecute = false;

387 

388  global $ADODB_FETCH_MODE;

389  $save = $ADODB_FETCH_MODE;

390  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

391  if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

392 

393  $sqlq = $this->conn->qstr($sql);

394  $arr = $this->conn->GetArray(

395 "select count(*),tracer

396  from $perf_table where sql1=$sqlq

397  group by tracer

398  order by 1 desc");

399  $s = '';

400  if ($arr) {

401  $s .= '<h3>Scripts Affected</h3>';

402  foreach($arr as $k) {

403  $s .= sprintf("%4d",$k[0]).' &nbsp; '.strip_tags($k[1]).'<br>';

404  }

405  }

406 

407  if (isset($savem)) $this->conn->SetFetchMode($savem);

408  $ADODB_CACHE_MODE = $save;

409  $this->conn->fnExecute = $saveE;

410  return $s;

411  }

412 

413  /*

414  Explain Plan for $sql.

415  If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the

416  actual sql.

417  */

418  function Explain($sql,$partial=false)

419  {

420  return false;

421  }

422 

423  function InvalidSQL($numsql = 10)

424  {

425 

426  if (isset($_GET['sql'])) return;

427  $s = '<h3>Invalid SQL</h3>';

428  $saveE = $this->conn->fnExecute;

429  $this->conn->fnExecute = false;

430  $perf_table = adodb_perf::table();

431  $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);

432  $this->conn->fnExecute = $saveE;

433  if ($rs) {

434  $s .= rs2html($rs,false,false,false,false);

435  } else

436  return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";

437 

438  return $s;

439  }

440 

441 

442  /*

443  This script identifies the longest running SQL

444  */

445  function _SuspiciousSQL($numsql = 10)

446  {

447  global $ADODB_FETCH_MODE;

448 

449  $perf_table = adodb_perf::table();

450  $saveE = $this->conn->fnExecute;

451  $this->conn->fnExecute = false;

452 

453  if (isset($_GET['exps']) && isset($_GET['sql'])) {

454  $partial = !empty($_GET['part']);

455  echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";

456  }

457 

458  if (isset($_GET['sql'])) return;

459  $sql1 = $this->sql1;

460 

461  $save = $ADODB_FETCH_MODE;

462  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

463  if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

464  //$this->conn->debug=1;

465  $rs = $this->conn->SelectLimit(

466  "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer

467  from $perf_table

468  where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')

469  and (tracer is null or tracer not like 'ERROR:%')

470  group by sql1

471  order by 1 desc",$numsql);

472  if (isset($savem)) $this->conn->SetFetchMode($savem);

473  $ADODB_FETCH_MODE = $save;

474  $this->conn->fnExecute = $saveE;

475 

476  if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";

477  $s = "<h3>Suspicious SQL</h3>

478 <font size=1>The following SQL have high average execution times</font><br>

479 <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";

480  $max = $this->maxLength;

481  while (!$rs->EOF) {

482  $sql = $rs->fields[1];

483  $raw = urlencode($sql);

484  if (strlen($raw)>$max-100) {

485  $sql2 = substr($sql,0,$max-500);

486  $raw = urlencode($sql2).'&part='.crc32($sql);

487  }

488  $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">";

489  $suffix = "</a>";

490  if ($this->explain == false || strlen($prefix)>$max) {

491  $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>';

492  $prefix = '';

493  }

494  $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".

495  "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";

496  $rs->MoveNext();

497  }

498  return $s."</table>";

499 

500  }

501 

502  function CheckMemory()

503  {

504  return '';

505  }

506 

507 

508  function SuspiciousSQL($numsql=10)

509  {

510  return adodb_perf::_SuspiciousSQL($numsql);

511  }

512 

513  function ExpensiveSQL($numsql=10)

514  {

515  return adodb_perf::_ExpensiveSQL($numsql);

516  }

517 

518 

519  /*

520  This reports the percentage of load on the instance due to the most

521  expensive few SQL statements. Tuning these statements can often

522  make huge improvements in overall system performance.

523  */

524  function _ExpensiveSQL($numsql = 10)

525  {

526  global $ADODB_FETCH_MODE;

527 

528  $perf_table = adodb_perf::table();

529  $saveE = $this->conn->fnExecute;

530  $this->conn->fnExecute = false;

531 

532  if (isset($_GET['expe']) && isset($_GET['sql'])) {

533  $partial = !empty($_GET['part']);

534  echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";

535  }

536 

537  if (isset($_GET['sql'])) return;

538 

539  $sql1 = $this->sql1;

540  $save = $ADODB_FETCH_MODE;

541  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

542  if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

543 

544  $rs = $this->conn->SelectLimit(

545  "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer

546  from $perf_table

547  where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')

548  and (tracer is null or tracer not like 'ERROR:%')

549  group by sql1

550  having count(*)>1

551  order by 1 desc",$numsql);

552  if (isset($savem)) $this->conn->SetFetchMode($savem);

553  $this->conn->fnExecute = $saveE;

554  $ADODB_FETCH_MODE = $save;

555  if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";

556  $s = "<h3>Expensive SQL</h3>

557 <font size=1>Tuning the following SQL could reduce the server load substantially</font><br>

558 <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";

559  $max = $this->maxLength;

560  while (!$rs->EOF) {

561  $sql = $rs->fields[1];

562  $raw = urlencode($sql);

563  if (strlen($raw)>$max-100) {

564  $sql2 = substr($sql,0,$max-500);

565  $raw = urlencode($sql2).'&part='.crc32($sql);

566  }

567  $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">";

568  $suffix = "</a>";

569  if($this->explain == false || strlen($prefix>$max)) {

570  $prefix = '';

571  $suffix = '';

572  }

573  $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".

574  "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";

575  $rs->MoveNext();

576  }

577  return $s."</table>";

578  }

579 

580  /*

581  Raw function to return parameter value from $settings.

582  */

583  function DBParameter($param)

584  {

585  if (empty($this->settings[$param])) return false;

586  $sql = $this->settings[$param][1];

587  return $this->_DBParameter($sql);

588  }

589 

590  /*

591  Raw function returning array of poll paramters

592  */

593  function PollParameters()

594  {

595  $arr[0] = (float)$this->DBParameter('data cache hit ratio');

596  $arr[1] = (float)$this->DBParameter('data reads');

597  $arr[2] = (float)$this->DBParameter('data writes');

598  $arr[3] = (integer) $this->DBParameter('current connections');

599  return $arr;

600  }

601 

602  /*

603  Low-level Get Database Parameter

604  */

605  function _DBParameter($sql)

606  {

607  $savelog = $this->conn->LogSQL(false);

608  if (is_array($sql)) {

609  global $ADODB_FETCH_MODE;

610 

611  $sql1 = $sql[0];

612  $key = $sql[1];

613  if (sizeof($sql)>2) $pos = $sql[2];

614  else $pos = 1;

615  if (sizeof($sql)>3) $coef = $sql[3];

616  else $coef = false;

617  $ret = false;

618  $save = $ADODB_FETCH_MODE;

619  $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

620  if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

621 

622  $rs = $this->conn->Execute($sql1);

623 

624  if (isset($savem)) $this->conn->SetFetchMode($savem);

625  $ADODB_FETCH_MODE = $save;

626  if ($rs) {

627  while (!$rs->EOF) {

628  $keyf = reset($rs->fields);

629  if (trim($keyf) == $key) {

630  $ret = $rs->fields[$pos];

631  if ($coef) $ret *= $coef;

632  break;

633  }

634  $rs->MoveNext();

635  }

636  $rs->Close();

637  }

638  $this->conn->LogSQL($savelog);

639  return $ret;

640  } else {

641  if (strncmp($sql,'=',1) == 0) {

642  $fn = substr($sql,1);

643  return $this->$fn();

644  }

645  $sql = str_replace('$DATABASE',$this->conn->database,$sql);

646  $ret = $this->conn->GetOne($sql);

647  $this->conn->LogSQL($savelog);

648 

649  return $ret;

650  }

651  }

652 

653  /*

654  Warn if cache ratio falls below threshold. Displayed in "Description" column.

655  */

656  function WarnCacheRatio($val)

657  {

658  if ($val < $this->warnRatio)

659  return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>';

660  else return '';

661  }

662 

663  function clearsql()

664  {

665  $perf_table = adodb_perf::table();

666  $this->conn->Execute("delete from $perf_table where created<".$this->conn->sysTimeStamp);

667  }

668  /***********************************************************************************************/

669  // HIGH LEVEL UI FUNCTIONS

670  /***********************************************************************************************/

671 

672 

673  function UI($pollsecs=5)

674  {

675  global $ADODB_LOG_CONN;

676 

677  $perf_table = adodb_perf::table();

678  $conn = $this->conn;

679 

680  $app = $conn->host;

681  if ($conn->host && $conn->database) $app .= ', db=';

682  $app .= $conn->database;

683 

684  if ($app) $app .= ', ';

685  $savelog = $this->conn->LogSQL(false);

686  $info = $conn->ServerInfo();

687  if (isset($_GET['clearsql'])) {

688  $this->clearsql();

689  }

690  $this->conn->LogSQL($savelog);

691 

692  // magic quotes

693 

694  if (isset($_GET['sql']) && get_magic_quotes_gpc()) {

695  $_GET['sql'] = $_GET['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$_GET['sql']);

696  }

697 

698  if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10;

699  else $nsql = $_SESSION['ADODB_PERF_SQL'];

700 

701  $app .= $info['description'];

702 

703 

704  if (isset($_GET['do'])) $do = $_GET['do'];

705  else if (isset($_POST['do'])) $do = $_POST['do'];

706  else if (isset($_GET['sql'])) $do = 'viewsql';

707  else $do = 'stats';

708 

709  if (isset($_GET['nsql'])) {

710  if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql'];

711  }

712  echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>";

713  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>";

714  else $form = "<td>&nbsp;</td>";

715 

716  $allowsql = !defined('ADODB_PERF_NO_RUN_SQL');

717  global $ADODB_PERF_MIN;

718  $app .= " (Min sql timing \$ADODB_PERF_MIN=$ADODB_PERF_MIN secs)";

719 

720  if (empty($_GET['hidem']))

721  echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2>

722  <b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td>

723  <a href=?do=stats><b>Performance Stats</b></a> &nbsp; <a href=?do=viewsql><b>View SQL</b></a>

724  &nbsp; <a href=?do=tables><b>View Tables</b></a> &nbsp; <a href=?do=poll><b>Poll Stats</b></a>",

725  $allowsql ? ' &nbsp; <a href=?do=dosql><b>Run SQL</b></a>' : '',

726  "$form",

727  "</tr></table>";

728 

729 

730  switch ($do) {

731  default:

732  case 'stats':

733  if (empty($ADODB_LOG_CONN))

734  echo "<p>&nbsp; <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";

735  echo $this->HealthCheck();

736  //$this->conn->debug=1;

737  echo $this->CheckMemory();

738  break;

739  case 'poll':

740  $self = htmlspecialchars($_SERVER['PHP_SELF']);

741  echo "<iframe width=720 height=80%

742  src=\"{$self}?do=poll2&hidem=1\"></iframe>";

743  break;

744  case 'poll2':

745  echo "<pre>";

746  $this->Poll($pollsecs);

747  break;

748 

749  case 'dosql':

750  if (!$allowsql) break;

751 

752  $this->DoSQLForm();

753  break;

754  case 'viewsql':

755  if (empty($_GET['hidem']))

756  echo "&nbsp; <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";

757  echo($this->SuspiciousSQL($nsql));

758  echo($this->ExpensiveSQL($nsql));

759  echo($this->InvalidSQL($nsql));

760  break;

761  case 'tables':

762  echo $this->Tables(); break;

763  }

764  global $ADODB_vers;

765  echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>";

766  }

767 

768  /*

769  Runs in infinite loop, returning real-time statistics

770  */

771  function Poll($secs=5)

772  {

773  $this->conn->fnExecute = false;

774  //$this->conn->debug=1;

775  if ($secs <= 1) $secs = 1;

776  echo "Accumulating statistics, every $secs seconds...\n";flush();

777  $arro = $this->PollParameters();

778  $cnt = 0;

779  set_time_limit(0);

780  sleep($secs);

781  while (1) {

782 

783  $arr = $this->PollParameters();

784 

785  $hits = sprintf('%2.2f',$arr[0]);

786  $reads = sprintf('%12.4f',($arr[1]-$arro[1])/$secs);

787  $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs);

788  $sess = sprintf('%5d',$arr[3]);

789 

790  $load = $this->CPULoad();

791  if ($load !== false) {

792  $oslabel = 'WS-CPU%';

793  $osval = sprintf(" %2.1f ",(float) $load);

794  }else {

795  $oslabel = '';

796  $osval = '';

797  }

798  if ($cnt % 10 == 0) echo " Time ".$oslabel." Hit% Sess Reads/s Writes/s\n";

799  $cnt += 1;

800  echo date('H:i:s').' '.$osval."$hits $sess $reads $writes\n";

801  flush();

802 

803  if (connection_aborted()) return;

804 

805  sleep($secs);

806  $arro = $arr;

807  }

808  }

809 

810  /*

811  Returns basic health check in a command line interface

812  */

813  function HealthCheckCLI()

814  {

815  return $this->HealthCheck(true);

816  }

817 

818 

819  /*

820  Returns basic health check as HTML

821  */

822  function HealthCheck($cli=false)

823  {

824  $saveE = $this->conn->fnExecute;

825  $this->conn->fnExecute = false;

826  if ($cli) $html = '';

827  else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles;

828 

829  $oldc = false;

830  $bgc = '';

831  foreach($this->settings as $name => $arr) {

832  if ($arr === false) break;

833 

834  if (!is_string($name)) {

835  if ($cli) $html .= " -- $arr -- \n";

836  else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> &nbsp;</td></tr>";

837  continue;

838  }

839 

840  if (!is_array($arr)) break;

841  $category = $arr[0];

842  $how = $arr[1];

843  if (sizeof($arr)>2) $desc = $arr[2];

844  else $desc = ' &nbsp; ';

845 

846 

847  if ($category == 'HIDE') continue;

848 

849  $val = $this->_DBParameter($how);

850 

851  if ($desc && strncmp($desc,"=",1) === 0) {

852  $fn = substr($desc,1);

853  $desc = $this->$fn($val);

854  }

855 

856  if ($val === false) {

857  $m = $this->conn->ErrorMsg();

858  $val = "Error: $m";

859  } else {

860  if (is_numeric($val) && $val >= 256*1024) {

861  if ($val % (1024*1024) == 0) {

862  $val /= (1024*1024);

863  $val .= 'M';

864  } else if ($val % 1024 == 0) {

865  $val /= 1024;

866  $val .= 'K';

867  }

868  //$val = htmlspecialchars($val);

869  }

870  }

871  if ($category != $oldc) {

872  $oldc = $category;

873  //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color;

874  }

875  if (strlen($desc)==0) $desc = '&nbsp;';

876  if (strlen($val)==0) $val = '&nbsp;';

877  if ($cli) {

878  $html .= str_replace('&nbsp;','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc)));

879 

880  }else {

881  $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n";

882  }

883  }

884 

885  if (!$cli) $html .= "</table>\n";

886  $this->conn->fnExecute = $saveE;

887 

888  return $html;

889  }

890 

891  function Tables($orderby='1')

892  {

893  if (!$this->tablesSQL) return false;

894 

895  $savelog = $this->conn->LogSQL(false);

896  $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby);

897  $this->conn->LogSQL($savelog);

898  $html = rs2html($rs,false,false,false,false);

899  return $html;

900  }

901 

902 

903  function CreateLogTable()

904  {

905  if (!$this->createTableSQL) return false;

906 

907  $table = $this->table();

908  $sql = str_replace('adodb_logsql',$table,$this->createTableSQL);

909  $savelog = $this->conn->LogSQL(false);

910  $ok = $this->conn->Execute($sql);

911  $this->conn->LogSQL($savelog);

912  return ($ok) ? true : false;

913  }

914 

915  function DoSQLForm()

916  {

917 

918 

919  $PHP_SELF = htmlspecialchars($_SERVER['PHP_SELF']);

920  $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : '';

921 

922  if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows'];

923  else $rows = 3;

924 

925  if (isset($_REQUEST['SMALLER'])) {

926  $rows /= 2;

927  if ($rows < 3) $rows = 3;

928  $_SESSION['phplens_sqlrows'] = $rows;

929  }

930  if (isset($_REQUEST['BIGGER'])) {

931  $rows *= 2;

932  $_SESSION['phplens_sqlrows'] = $rows;

933  }

934 

935 ?>

936 

937 <form method="POST" action="<?php echo $PHP_SELF ?>">

938 <table><tr>

939 <td> Form size: <input type="submit" value=" &lt; " name="SMALLER"><input type="submit" value=" &gt; &gt; " name="BIGGER">

940 </td>

941 <td align=right>

942 <input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql>

943 </td></tr>

944  <tr>

945  <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea>

946  </td>

947  </tr>

948  </table>

949 </form>

950 

951 <?php

952  if (!isset($_REQUEST['sql'])) return;

953 

954  $sql = $this->undomq(trim($sql));

955  if (substr($sql,strlen($sql)-1) === ';') {

956  $print = true;

957  $sqla = $this->SplitSQL($sql);

958  } else {

959  $print = false;

960  $sqla = array($sql);

961  }

962  foreach($sqla as $sqls) {

963 

964  if (!$sqls) continue;

965 

966  if ($print) {

967  print "<p>".htmlspecialchars($sqls)."</p>";

968  flush();

969  }

970  $savelog = $this->conn->LogSQL(false);

971  $rs = $this->conn->Execute($sqls);

972  $this->conn->LogSQL($savelog);

973  if ($rs && is_object($rs) && !$rs->EOF) {

974  rs2html($rs);

975  while ($rs->NextRecordSet()) {

976  print "<table width=98% bgcolor=#C0C0FF><tr><td>&nbsp;</td></tr></table>";

977  rs2html($rs);

978  }

979  } else {

980  $e1 = (integer) $this->conn->ErrorNo();

981  $e2 = $this->conn->ErrorMsg();

982  if (($e1) || ($e2)) {

983  if (empty($e1)) $e1 = '-1'; // postgresql fix

984  print ' &nbsp; '.$e1.': '.$e2;

985  } else {

986  print "<p>No Recordset returned<br></p>";

987  }

988  }

989  } // foreach

990  }

991 

992  function SplitSQL($sql)

993  {

994  $arr = explode(';',$sql);

995  return $arr;

996  }

997 

998  function undomq($m)

999  {

1000  if (get_magic_quotes_gpc()) {

1001  // undo the damage

1002  $m = str_replace('\\\\','\\',$m);

1003  $m = str_replace('\"','"',$m);

1004  $m = str_replace('\\\'','\'',$m);

1005  }

1006  return $m;

1007 }

1008 

1009 

1010  /************************************************************************/

1011 

1035  function OptimizeTables()

1036  {

1037  $args = func_get_args();

1038  $numArgs = func_num_args();

1039 

1040  if ( $numArgs == 0) return false;

1041 

1042  $mode = ADODB_OPT_LOW;

1043  $lastArg = $args[ $numArgs - 1];

1044  if ( !is_string($lastArg)) {

1045  $mode = $lastArg;

1046  unset( $args[ $numArgs - 1]);

1047  }

1048 

1049  foreach( $args as $table) {

1050  $this->optimizeTable( $table, $mode);

1051  }

1052  }

1053 

1066  function OptimizeTable( $table, $mode = ADODB_OPT_LOW)

1067  {

1068  ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType));

1069  return false;

1070  }

1071 

1080  function optimizeDatabase()

1081  {

1082  $conn = $this->conn;

1083  if ( !$conn) return false;

1084 

1085  $tables = $conn->MetaTables( 'TABLES');

1086  if ( !$tables ) return false;

1087 

1088  foreach( $tables as $table) {

1089  if ( !$this->optimizeTable( $table)) {

1090  return false;

1091  }

1092  }

1093 

1094  return true;

1095  }

1096  // end hack

1097 }