Documentation TYPO3 par Ameos

adodb-perf.inc.php

00001 <?php
00002 /* 
00003 V4.93 10 Oct 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. See License.txt. 
00007   Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011   Library for basic performance monitoring and tuning.
00012   
00013   My apologies if you see code mixed with presentation. The presentation suits
00014   my needs. If you want to separate code from presentation, be my guest. Patches
00015   are welcome.
00016   
00017 */
00018 
00019 if (!defined('ADODB_DIR')) include_once(dirname(__FILE__).'/adodb.inc.php');
00020 include_once(ADODB_DIR.'/tohtml.inc.php');
00021 
00022 define( 'ADODB_OPT_HIGH', 2);
00023 define( 'ADODB_OPT_LOW', 1);
00024 
00025 // returns in K the memory of current process, or 0 if not known
00026 function adodb_getmem()
00027 {
00028         if (function_exists('memory_get_usage'))
00029                 return (integer) ((memory_get_usage()+512)/1024);
00030         
00031         $pid = getmypid();
00032         
00033         if ( strncmp(strtoupper(PHP_OS),'WIN',3)==0) {
00034                 $output = array();
00035         
00036                 exec('tasklist /FI "PID eq ' . $pid. '" /FO LIST', $output); 
00037                 return substr($output[5], strpos($output[5], ':') + 1);
00038         } 
00039         
00040         /* Hopefully UNIX */
00041         exec("ps --pid $pid --no-headers -o%mem,size", $output);
00042         if (sizeof($output) == 0) return 0;
00043         
00044         $memarr = explode(' ',$output[0]);
00045         if (sizeof($memarr)>=2) return (integer) $memarr[1];
00046         
00047         return 0;
00048 }
00049 
00050 // avoids localization problems where , is used instead of .
00051 function adodb_round($n,$prec)
00052 {
00053         return number_format($n, $prec, '.', '');
00054 }
00055 
00056 /* return microtime value as a float */
00057 function adodb_microtime()
00058 {
00059         $t = microtime();
00060         $t = explode(' ',$t);
00061         return (float)$t[1]+ (float)$t[0];
00062 }
00063 
00064 /* sql code timing */
00065 function& adodb_log_sql(&$connx,$sql,$inputarr)
00066 {
00067     $perf_table = adodb_perf::table();
00068         $connx->fnExecute = false;
00069         $t0 = microtime();
00070         $rs =& $connx->Execute($sql,$inputarr);
00071         $t1 = microtime();
00072 
00073         if (!empty($connx->_logsql) && (empty($connx->_logsqlErrors) || !$rs)) {
00074         global $ADODB_LOG_CONN;
00075         
00076                 if (!empty($ADODB_LOG_CONN)) {
00077                         $conn = &$ADODB_LOG_CONN;
00078                         if ($conn->databaseType != $connx->databaseType)
00079                                 $prefix = '/*dbx='.$connx->databaseType .'*/ ';
00080                         else
00081                                 $prefix = '';
00082                 } else {
00083                         $conn =& $connx;
00084                         $prefix = '';
00085                 }
00086                 
00087                 $conn->_logsql = false; // disable logsql error simulation
00088                 $dbT = $conn->databaseType;
00089                 
00090                 $a0 = split(' ',$t0);
00091                 $a0 = (float)$a0[1]+(float)$a0[0];
00092                 
00093                 $a1 = split(' ',$t1);
00094                 $a1 = (float)$a1[1]+(float)$a1[0];
00095                 
00096                 $time = $a1 - $a0;
00097         
00098                 if (!$rs) {
00099                         $errM = $connx->ErrorMsg();
00100                         $errN = $connx->ErrorNo();
00101                         $conn->lastInsID = 0;
00102                         $tracer = substr('ERROR: '.htmlspecialchars($errM),0,250);
00103                 } else {
00104                         $tracer = '';
00105                         $errM = '';
00106                         $errN = 0;
00107                         $dbg = $conn->debug;
00108                         $conn->debug = false;
00109                         if (!is_object($rs) || $rs->dataProvider == 'empty') 
00110                                 $conn->_affected = $conn->affected_rows(true);
00111                         $conn->lastInsID = @$conn->Insert_ID();
00112                         $conn->debug = $dbg;
00113                 }
00114                 if (isset($_SERVER['HTTP_HOST'])) {
00115                         $tracer .= '<br>'.$_SERVER['HTTP_HOST'];
00116                         if (isset($_SERVER['PHP_SELF'])) $tracer .= $_SERVER['PHP_SELF'];
00117                 } else 
00118                         if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.$_SERVER['PHP_SELF'];
00119                 //$tracer .= (string) adodb_backtrace(false);
00120                 
00121                 $tracer = (string) substr($tracer,0,500);
00122                 
00123                 if (is_array($inputarr)) {
00124                         if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr);
00125                         else {
00126                                 // Quote string parameters so we can see them in the
00127                                 // performance stats. This helps spot disabled indexes.
00128                                 $xar_params = $inputarr;
00129                                 foreach ($xar_params as $xar_param_key => $xar_param) {
00130                                         if (gettype($xar_param) == 'string')
00131                                         $xar_params[$xar_param_key] = '"' . $xar_param . '"';
00132                                 }
00133                                 $params = implode(', ', $xar_params);
00134                                 if (strlen($params) >= 3000) $params = substr($params, 0, 3000);
00135                         }
00136                 } else {
00137                         $params = '';
00138                 }
00139                 
00140                 if (is_array($sql)) $sql = $sql[0];
00141                 if ($prefix) $sql = $prefix.$sql;
00142                 $arr = array('b'=>strlen($sql).'.'.crc32($sql),
00143                                         'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6));
00144                 //var_dump($arr);
00145                 $saved = $conn->debug;
00146                 $conn->debug = 0;
00147                 
00148                 $d = $conn->sysTimeStamp;
00149                 if (empty($d)) $d = date("'Y-m-d H:i:s'");
00150                 if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') {
00151                         $isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)";
00152                 } else if ($dbT == 'odbc_mssql' || $dbT == 'informix' || strncmp($dbT,'odbtp',4)==0) {
00153                         $timer = $arr['f'];
00154                         if ($dbT == 'informix') $sql2 = substr($sql2,0,230);
00155 
00156                         $sql1 = $conn->qstr($arr['b']);
00157                         $sql2 = $conn->qstr($arr['c']);
00158                         $params = $conn->qstr($arr['d']);
00159                         $tracer = $conn->qstr($arr['e']);
00160                         
00161                         $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)";
00162                         if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql);
00163                         $arr = false;
00164                 } else {
00165                         if ($dbT == 'db2') $arr['f'] = (float) $arr['f'];
00166                         $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $d,?,?,?,?,?)";
00167                 }
00168                 $ok = $conn->Execute($isql,$arr);
00169                 $conn->debug = $saved;
00170                 
00171                 if ($ok) {
00172                         $conn->_logsql = true; 
00173                 } else {
00174                         $err2 = $conn->ErrorMsg();
00175                         $conn->_logsql = true; // enable logsql error simulation
00176                         $perf =& NewPerfMonitor($conn);
00177                         if ($perf) {
00178                                 if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr);
00179                         } else {
00180                                 $ok = $conn->Execute("create table $perf_table (
00181                                 created varchar(50),
00182                                 sql0 varchar(250), 
00183                                 sql1 varchar(4000),
00184                                 params varchar(3000),
00185                                 tracer varchar(500),
00186                                 timer decimal(16,6))");
00187                         }
00188                         if (!$ok) {
00189                                 ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>");
00190                                 $conn->_logsql = false;
00191                         }
00192                 }
00193                 $connx->_errorMsg = $errM;
00194                 $connx->_errorCode = $errN;
00195         } 
00196         $connx->fnExecute = 'adodb_log_sql';
00197         return $rs;
00198 }
00199 
00200         
00201 /*
00202 The settings data structure is an associative array that database parameter per element.
00203 
00204 Each database parameter element in the array is itself an array consisting of:
00205 
00206 0: category code, used to group related db parameters
00207 1: either
00208         a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'", 
00209         b. array holding sql string and field to look for, e.g. array('show variables','table_cache'),
00210         c. a string prefixed by =, then a PHP method of the class is invoked, 
00211                 e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue',
00212 2: description of the database parameter
00213 */
00214 
00215 class adodb_perf {
00216         var $conn;
00217         var $color = '#F0F0F0';
00218         var $table = '<table border=1 bgcolor=white>';
00219         var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>';
00220         var $warnRatio = 90;
00221         var $tablesSQL = false;
00222         var $cliFormat = "%32s => %s \r\n";
00223         var $sql1 = 'sql1';  // used for casting sql1 to text for mssql
00224         var $explain = true;
00225         var $helpurl = "<a href=http://phplens.com/adodb/reference.functions.fnexecute.and.fncacheexecute.properties.html#logsql>LogSQL help</a>";
00226         var $createTableSQL = false;
00227         var $maxLength = 2000;
00228         
00229     // Sets the tablename to be used            
00230     function table($newtable = false)
00231     {
00232         static $_table;
00233 
00234         if (!empty($newtable))  $_table = $newtable;
00235                 if (empty($_table)) $_table = 'adodb_logsql';
00236         return $_table;
00237     }
00238 
00239         // returns array with info to calculate CPU Load
00240         function _CPULoad()
00241         {
00242 /*
00243 
00244 cpu  524152 2662 2515228 336057010
00245 cpu0 264339 1408 1257951 168025827
00246 cpu1 259813 1254 1257277 168031181
00247 page 622307 25475680
00248 swap 24 1891
00249 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
00250 disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320)
00251 ctxt 66155838
00252 btime 1062315585
00253 processes 69293
00254 
00255 */
00256                 // Algorithm is taken from
00257                 // http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/example__obtaining_raw_performance_data.asp
00258                 if (strncmp(PHP_OS,'WIN',3)==0) {
00259                         if (PHP_VERSION == '5.0.0') return false;
00260                         if (PHP_VERSION == '5.0.1') return false;
00261                         if (PHP_VERSION == '5.0.2') return false;
00262                         if (PHP_VERSION == '5.0.3') return false;
00263                         if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737
00264                         
00265                         @$c = new COM("WinMgmts:{impersonationLevel=impersonate}!Win32_PerfRawData_PerfOS_Processor.Name='_Total'");
00266                         if (!$c) return false;
00267                         
00268                         $info[0] = $c->PercentProcessorTime;
00269                         $info[1] = 0;
00270                         $info[2] = 0;
00271                         $info[3] = $c->TimeStamp_Sys100NS;
00272                         //print_r($info);
00273                         return $info;
00274                 }
00275                 
00276                 // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com)
00277                 $statfile = '/proc/stat';
00278                 if (!file_exists($statfile)) return false;
00279                 
00280                 $fd = fopen($statfile,"r");
00281                 if (!$fd) return false;
00282                 
00283                 $statinfo = explode("\n",fgets($fd, 1024));
00284                 fclose($fd);
00285                 foreach($statinfo as $line) {
00286                         $info = explode(" ",$line);
00287                         if($info[0]=="cpu") {
00288                                 array_shift($info);  // pop off "cpu"
00289                                 if(!$info[0]) array_shift($info); // pop off blank space (if any)
00290                                 return $info;
00291                         }
00292                 }
00293                 
00294                 return false;
00295                 
00296         }
00297         
00298         /* NOT IMPLEMENTED */
00299         function MemInfo()
00300         {
00301                 /*
00302 
00303         total:    used:    free:  shared: buffers:  cached:
00304 Mem:  1055289344 917299200 137990144        0 165437440 599773184
00305 Swap: 2146775040 11055104 2135719936
00306 MemTotal:      1030556 kB
00307 MemFree:        134756 kB
00308 MemShared:           0 kB
00309 Buffers:        161560 kB
00310 Cached:         581384 kB
00311 SwapCached:       4332 kB
00312 Active:         494468 kB
00313 Inact_dirty:    322856 kB
00314 Inact_clean:     24256 kB
00315 Inact_target:   168316 kB
00316 HighTotal:      131064 kB
00317 HighFree:         1024 kB
00318 LowTotal:       899492 kB
00319 LowFree:        133732 kB
00320 SwapTotal:     2096460 kB
00321 SwapFree:      2085664 kB
00322 Committed_AS:   348732 kB
00323                 */
00324         }
00325         
00326         
00327         /*
00328                 Remember that this is client load, not db server load!
00329         */
00330         var $_lastLoad;
00331         function CPULoad()
00332         {
00333                 $info = $this->_CPULoad();
00334                 if (!$info) return false;
00335                         
00336                 if (empty($this->_lastLoad)) {
00337                         sleep(1);
00338                         $this->_lastLoad = $info;
00339                         $info = $this->_CPULoad();
00340                 }
00341                 
00342                 $last = $this->_lastLoad;
00343                 $this->_lastLoad = $info;
00344                 
00345                 $d_user = $info[0] - $last[0];
00346                 $d_nice = $info[1] - $last[1];
00347                 $d_system = $info[2] - $last[2];
00348                 $d_idle = $info[3] - $last[3];
00349                 
00350                 //printf("Delta - User: %f  Nice: %f  System: %f  Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle);
00351 
00352                 if (strncmp(PHP_OS,'WIN',3)==0) {
00353                         if ($d_idle < 1) $d_idle = 1;
00354                         return 100*(1-$d_user/$d_idle);
00355                 }else {
00356                         $total=$d_user+$d_nice+$d_system+$d_idle;
00357                         if ($total<1) $total=1;
00358                         return 100*($d_user+$d_nice+$d_system)/$total; 
00359                 }
00360         }
00361         
00362         function Tracer($sql)
00363         {
00364         $perf_table = adodb_perf::table();
00365                 $saveE = $this->conn->fnExecute;
00366                 $this->conn->fnExecute = false;
00367                 
00368                 global $ADODB_FETCH_MODE;
00369                 $save = $ADODB_FETCH_MODE;
00370                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00371                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00372                                 
00373                 $sqlq = $this->conn->qstr($sql);
00374                 $arr = $this->conn->GetArray(
00375 "select count(*),tracer 
00376         from $perf_table where sql1=$sqlq 
00377         group by tracer
00378         order by 1 desc");
00379                 $s = '';
00380                 if ($arr) {
00381                         $s .= '<h3>Scripts Affected</h3>';
00382                         foreach($arr as $k) {
00383                                 $s .= sprintf("%4d",$k[0]).' &nbsp; '.strip_tags($k[1]).'<br>';
00384                         }
00385                 }
00386                 
00387                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00388                 $ADODB_CACHE_MODE = $save;
00389                 $this->conn->fnExecute = $saveE;
00390                 return $s;
00391         }
00392 
00393         /* 
00394                 Explain Plan for $sql.
00395                 If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the 
00396                         actual sql.
00397         */
00398         function Explain($sql,$partial=false)
00399         {       
00400                 return false;
00401         }
00402         
00403         function InvalidSQL($numsql = 10)
00404         {
00405         
00406                 if (isset($_GET['sql'])) return;
00407                 $s = '<h3>Invalid SQL</h3>';
00408                 $saveE = $this->conn->fnExecute;
00409                 $this->conn->fnExecute = false;
00410         $perf_table = adodb_perf::table();
00411                 $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);
00412                 $this->conn->fnExecute = $saveE;
00413                 if ($rs) {
00414                         $s .= rs2html($rs,false,false,false,false);
00415                 } else
00416                         return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
00417                 
00418                 return $s;
00419         }
00420 
00421         
00422         /*
00423                 This script identifies the longest running SQL
00424         */      
00425         function _SuspiciousSQL($numsql = 10)
00426         {
00427                 global $ADODB_FETCH_MODE;
00428                 
00429             $perf_table = adodb_perf::table();
00430                         $saveE = $this->conn->fnExecute;
00431                         $this->conn->fnExecute = false;
00432                         
00433                         if (isset($_GET['exps']) && isset($_GET['sql'])) {
00434                                 $partial = !empty($_GET['part']);
00435                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
00436                         }
00437                         
00438                         if (isset($_GET['sql'])) return;
00439                         $sql1 = $this->sql1;
00440                         
00441                         $save = $ADODB_FETCH_MODE;
00442                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00443                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00444                         //$this->conn->debug=1;
00445                         $rs =& $this->conn->SelectLimit(
00446                         "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
00447                                 from $perf_table
00448                                 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')
00449                                 and (tracer is null or tracer not like 'ERROR:%')
00450                                 group by sql1
00451                                 order by 1 desc",$numsql);
00452                         if (isset($savem)) $this->conn->SetFetchMode($savem);
00453                         $ADODB_FETCH_MODE = $save;
00454                         $this->conn->fnExecute = $saveE;
00455                         
00456                         if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
00457                         $s = "<h3>Suspicious SQL</h3>
00458 <font size=1>The following SQL have high average execution times</font><br>
00459 <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";
00460                         $max = $this->maxLength;
00461                         while (!$rs->EOF) {
00462                                 $sql = $rs->fields[1];
00463                                 $raw = urlencode($sql);
00464                                 if (strlen($raw)>$max-100) {
00465                                         $sql2 = substr($sql,0,$max-500);
00466                                         $raw = urlencode($sql2).'&part='.crc32($sql);
00467                                 }
00468                                 $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">";
00469                                 $suffix = "</a>";
00470                                 if ($this->explain == false || strlen($prefix)>$max) {
00471                                         $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>';
00472                                         $prefix = '';
00473                                 }
00474                                 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
00475                                         "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
00476                                 $rs->MoveNext();
00477                         }
00478                         return $s."</table>";
00479                 
00480         }
00481         
00482         function CheckMemory()
00483         {
00484                 return '';
00485         }
00486         
00487         
00488         function SuspiciousSQL($numsql=10)
00489         {
00490                 return adodb_perf::_SuspiciousSQL($numsql);
00491         }
00492 
00493         function ExpensiveSQL($numsql=10)
00494         {
00495                 return adodb_perf::_ExpensiveSQL($numsql);
00496         }
00497 
00498         
00499         /*
00500                 This reports the percentage of load on the instance due to the most 
00501                 expensive few SQL statements. Tuning these statements can often 
00502                 make huge improvements in overall system performance. 
00503         */
00504         function _ExpensiveSQL($numsql = 10)
00505         {
00506                 global $ADODB_FETCH_MODE;
00507                 
00508             $perf_table = adodb_perf::table();
00509                         $saveE = $this->conn->fnExecute;
00510                         $this->conn->fnExecute = false;
00511                         
00512                         if (isset($_GET['expe']) && isset($_GET['sql'])) {
00513                                 $partial = !empty($_GET['part']);
00514                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
00515                         }
00516                         
00517                         if (isset($_GET['sql'])) return;
00518                         
00519                         $sql1 = $this->sql1;
00520                         $save = $ADODB_FETCH_MODE;
00521                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00522                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00523                         
00524                         $rs =& $this->conn->SelectLimit(
00525                         "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
00526                                 from $perf_table
00527                                 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5))  not in ('DROP ','INSER','COMMI','CREAT')
00528                                 and (tracer is null or tracer not like 'ERROR:%')
00529                                 group by sql1
00530                                 having count(*)>1
00531                                 order by 1 desc",$numsql);
00532                         if (isset($savem)) $this->conn->SetFetchMode($savem);
00533                         $this->conn->fnExecute = $saveE;
00534                         $ADODB_FETCH_MODE = $save;
00535                         if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
00536                         $s = "<h3>Expensive SQL</h3>
00537 <font size=1>Tuning the following SQL could reduce the server load substantially</font><br>
00538 <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";
00539                         $max = $this->maxLength;
00540                         while (!$rs->EOF) {
00541                                 $sql = $rs->fields[1];
00542                                 $raw = urlencode($sql);
00543                                 if (strlen($raw)>$max-100) {
00544                                         $sql2 = substr($sql,0,$max-500);
00545                                         $raw = urlencode($sql2).'&part='.crc32($sql);
00546                                 }
00547                                 $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">";
00548                                 $suffix = "</a>";
00549                                 if($this->explain == false || strlen($prefix>$max)) {
00550                                         $prefix = '';
00551                                         $suffix = '';
00552                                 }
00553                                 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
00554                                         "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
00555                                 $rs->MoveNext();
00556                         }
00557                         return $s."</table>";
00558         }
00559         
00560         /*
00561                 Raw function to return parameter value from $settings.
00562         */
00563         function DBParameter($param)
00564         {
00565                 if (empty($this->settings[$param])) return false;
00566                 $sql = $this->settings[$param][1];
00567                 return $this->_DBParameter($sql);
00568         }
00569         
00570         /*
00571                 Raw function returning array of poll paramters
00572         */
00573         function &PollParameters()
00574         {
00575                 $arr[0] = (float)$this->DBParameter('data cache hit ratio');
00576                 $arr[1] = (float)$this->DBParameter('data reads');
00577                 $arr[2] = (float)$this->DBParameter('data writes');
00578                 $arr[3] = (integer) $this->DBParameter('current connections');
00579                 return $arr;
00580         }
00581         
00582         /*
00583                 Low-level Get Database Parameter
00584         */
00585         function _DBParameter($sql)
00586         {
00587                 $savelog = $this->conn->LogSQL(false);
00588                 if (is_array($sql)) {
00589                 global $ADODB_FETCH_MODE;
00590                 
00591                         $sql1 = $sql[0];
00592                         $key = $sql[1];
00593                         if (sizeof($sql)>2) $pos = $sql[2];
00594                         else $pos = 1;
00595                         if (sizeof($sql)>3) $coef = $sql[3];
00596                         else $coef = false;
00597                         $ret = false;
00598                         $save = $ADODB_FETCH_MODE;
00599                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00600                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00601                         
00602                         $rs = $this->conn->Execute($sql1);
00603                         
00604                         if (isset($savem)) $this->conn->SetFetchMode($savem);
00605                         $ADODB_FETCH_MODE = $save;
00606                         if ($rs) {
00607                                 while (!$rs->EOF) {
00608                                         $keyf = reset($rs->fields);
00609                                         if (trim($keyf) == $key) {
00610                                                 $ret = $rs->fields[$pos];
00611                                                 if ($coef) $ret *= $coef;
00612                                                 break;
00613                                         }
00614                                         $rs->MoveNext();
00615                                 }
00616                                 $rs->Close();
00617                         }
00618                         $this->conn->LogSQL($savelog);
00619                         return $ret;
00620                 } else {
00621                         if (strncmp($sql,'=',1) == 0) {
00622                                 $fn = substr($sql,1);
00623                                 return $this->$fn();
00624                         }
00625                         $sql = str_replace('$DATABASE',$this->conn->database,$sql);
00626                         $ret = $this->conn->GetOne($sql);
00627                         $this->conn->LogSQL($savelog);
00628                         
00629                         return $ret;
00630                 }
00631         }
00632         
00633         /*
00634                 Warn if cache ratio falls below threshold. Displayed in "Description" column.
00635         */
00636         function WarnCacheRatio($val)
00637         {
00638                 if ($val < $this->warnRatio) 
00639                          return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>';
00640                 else return '';
00641         }
00642         
00643         /***********************************************************************************************/
00644         //                                    HIGH LEVEL UI FUNCTIONS
00645         /***********************************************************************************************/
00646 
00647         
00648         function UI($pollsecs=5)
00649         {
00650         
00651     $perf_table = adodb_perf::table();
00652         $conn = $this->conn;
00653         
00654         $app = $conn->host;
00655         if ($conn->host && $conn->database) $app .= ', db=';
00656         $app .= $conn->database;
00657         
00658         if ($app) $app .= ', ';
00659         $savelog = $this->conn->LogSQL(false);  
00660         $info = $conn->ServerInfo();
00661         if (isset($_GET['clearsql'])) {
00662                 $this->conn->Execute("delete from $perf_table");
00663         }
00664         $this->conn->LogSQL($savelog);
00665         
00666         // magic quotes
00667         
00668         if (isset($_GET['sql']) && get_magic_quotes_gpc()) {
00669                 $_GET['sql'] = $_GET['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$_GET['sql']);
00670         }
00671         
00672         if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10;
00673         else  $nsql = $_SESSION['ADODB_PERF_SQL'];
00674         
00675         $app .= $info['description'];
00676         
00677         
00678         if (isset($_GET['do'])) $do = $_GET['do'];
00679         else if (isset($_POST['do'])) $do = $_POST['do'];
00680          else if (isset($_GET['sql'])) $do = 'viewsql';
00681          else $do = 'stats';
00682          
00683         if (isset($_GET['nsql'])) {
00684                 if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql'];
00685         }
00686         echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>";
00687         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>";
00688         else $form = "<td>&nbsp;</td>";
00689         
00690         $allowsql = !defined('ADODB_PERF_NO_RUN_SQL');
00691         
00692         if  (empty($_GET['hidem']))
00693         echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2>
00694         <b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td>
00695         <a href=?do=stats><b>Performance Stats</b></a> &nbsp; <a href=?do=viewsql><b>View SQL</b></a>
00696          &nbsp; <a href=?do=tables><b>View Tables</b></a> &nbsp; <a href=?do=poll><b>Poll Stats</b></a>",
00697          $allowsql ? ' &nbsp; <a href=?do=dosql><b>Run SQL</b></a>' : '',
00698          "$form",
00699          "</tr></table>";
00700 
00701          
00702                 switch ($do) {
00703                 default:
00704                 case 'stats':
00705                         echo $this->HealthCheck();
00706                         //$this->conn->debug=1;
00707                         echo $this->CheckMemory();
00708                         break;
00709                 case 'poll':
00710                         echo "<iframe width=720 height=80% 
00711                                 src=\"{$_SERVER['PHP_SELF']}?do=poll2&hidem=1\"></iframe>";
00712                         break;
00713                 case 'poll2':
00714                         echo "<pre>";
00715                         $this->Poll($pollsecs);
00716                         break;
00717                 
00718                 case 'dosql':
00719                         if (!$allowsql) break;
00720                         
00721                         $this->DoSQLForm();
00722                         break;
00723                 case 'viewsql':
00724                         if (empty($_GET['hidem']))
00725                                 echo "&nbsp; <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";
00726                         echo($this->SuspiciousSQL($nsql));
00727                         echo($this->ExpensiveSQL($nsql));
00728                         echo($this->InvalidSQL($nsql));
00729                         break;
00730                 case 'tables': 
00731                         echo $this->Tables(); break;
00732                 }
00733                 global $ADODB_vers;
00734                 echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>";
00735         }
00736         
00737         /*
00738                 Runs in infinite loop, returning real-time statistics
00739         */
00740         function Poll($secs=5)
00741         {
00742                 $this->conn->fnExecute = false;
00743                 //$this->conn->debug=1;
00744                 if ($secs <= 1) $secs = 1;
00745                 echo "Accumulating statistics, every $secs seconds...\n";flush();
00746                 $arro =& $this->PollParameters();
00747                 $cnt = 0;
00748                 set_time_limit(0);
00749                 sleep($secs);
00750                 while (1) {
00751 
00752                         $arr =& $this->PollParameters();
00753                         
00754                         $hits   = sprintf('%2.2f',$arr[0]);
00755                         $reads  = sprintf('%12.4f',($arr[1]-$arro[1])/$secs);
00756                         $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs);
00757                         $sess = sprintf('%5d',$arr[3]);
00758                         
00759                         $load = $this->CPULoad();
00760                         if ($load !== false) {
00761                                 $oslabel = 'WS-CPU%';
00762                                 $osval = sprintf(" %2.1f  ",(float) $load);
00763                         }else {
00764                                 $oslabel = '';
00765                                 $osval = '';
00766                         }
00767                         if ($cnt % 10 == 0) echo " Time   ".$oslabel."   Hit%   Sess           Reads/s          Writes/s\n"; 
00768                         $cnt += 1;
00769                         echo date('H:i:s').'  '.$osval."$hits  $sess $reads $writes\n";
00770                         flush();
00771                         
00772                         if (connection_aborted()) return;
00773                         
00774                         sleep($secs);
00775                         $arro = $arr;
00776                 }
00777         }
00778         
00779         /*
00780                 Returns basic health check in a command line interface
00781         */
00782         function HealthCheckCLI()
00783         {
00784                 return $this->HealthCheck(true);
00785         }
00786         
00787                 
00788         /*
00789                 Returns basic health check as HTML
00790         */
00791         function HealthCheck($cli=false)
00792         {
00793                 $saveE = $this->conn->fnExecute;
00794                 $this->conn->fnExecute = false; 
00795                 if ($cli) $html = '';
00796                 else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles;
00797                 
00798                 $oldc = false;
00799                 $bgc = '';
00800                 foreach($this->settings as $name => $arr) {
00801                         if ($arr === false) break;
00802                         
00803                         if (!is_string($name)) {
00804                                 if ($cli) $html .= " -- $arr -- \n";
00805                                 else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> &nbsp;</td></tr>";
00806                                 continue;
00807                         }
00808                         
00809                         if (!is_array($arr)) break;
00810                         $category = $arr[0];
00811                         $how = $arr[1];
00812                         if (sizeof($arr)>2) $desc = $arr[2];
00813                         else $desc = ' &nbsp; ';
00814                         
00815                         
00816                         if ($category == 'HIDE') continue;
00817                         
00818                         $val = $this->_DBParameter($how);
00819                         
00820                         if ($desc && strncmp($desc,"=",1) === 0) {
00821                                 $fn = substr($desc,1);
00822                                 $desc = $this->$fn($val);
00823                         }
00824                         
00825                         if ($val === false) {
00826                                 $m = $this->conn->ErrorMsg();
00827                                 $val = "Error: $m"; 
00828                         } else {
00829                                 if (is_numeric($val) && $val >= 256*1024) {
00830                                         if ($val % (1024*1024) == 0) {
00831                                                 $val /= (1024*1024);
00832                                                 $val .= 'M';
00833                                         } else if ($val % 1024 == 0) {
00834                                                 $val /= 1024;
00835                                                 $val .= 'K';
00836                                         }
00837                                         //$val = htmlspecialchars($val);
00838                                 }
00839                         }
00840                         if ($category != $oldc) {
00841                                 $oldc = $category;
00842                                 //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color;
00843                         }
00844                         if (strlen($desc)==0) $desc = '&nbsp;';
00845                         if (strlen($val)==0) $val = '&nbsp;';
00846                         if ($cli) {
00847                                 $html  .= str_replace('&nbsp;','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc)));
00848                                 
00849                         }else {
00850                                 $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n";
00851                         }
00852                 }
00853                 
00854                 if (!$cli) $html .= "</table>\n";
00855                 $this->conn->fnExecute = $saveE;
00856                         
00857                 return $html;   
00858         }
00859         
00860         function Tables($orderby='1')
00861         {
00862                 if (!$this->tablesSQL) return false;
00863                 
00864                 $savelog = $this->conn->LogSQL(false);
00865                 $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby);
00866                 $this->conn->LogSQL($savelog);
00867                 $html = rs2html($rs,false,false,false,false);
00868                 return $html;
00869         }
00870         
00871 
00872         function CreateLogTable()
00873         {
00874                 if (!$this->createTableSQL) return false;
00875                 
00876                 $table = $this->table();
00877                 $sql = str_replace('adodb_logsql',$table,$this->createTableSQL);
00878                 $savelog = $this->conn->LogSQL(false);
00879                 $ok = $this->conn->Execute($sql);
00880                 $this->conn->LogSQL($savelog);
00881                 return ($ok) ? true : false;
00882         }
00883         
00884         function DoSQLForm()
00885         {
00886         
00887                 
00888                 $PHP_SELF = $_SERVER['PHP_SELF'];
00889                 $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : '';
00890 
00891                 if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows'];
00892                 else $rows = 3;
00893                 
00894                 if (isset($_REQUEST['SMALLER'])) {
00895                         $rows /= 2;
00896                         if ($rows < 3) $rows = 3;
00897                         $_SESSION['phplens_sqlrows'] = $rows;
00898                 }
00899                 if (isset($_REQUEST['BIGGER'])) {
00900                         $rows *= 2;
00901                         $_SESSION['phplens_sqlrows'] = $rows;
00902                 }
00903                 
00904 ?>
00905 
00906 <form method="POST" action="<?php echo $PHP_SELF ?>">
00907 <table><tr>
00908 <td> Form size: <input type="submit" value=" &lt; " name="SMALLER"><input type="submit" value=" &gt; &gt; " name="BIGGER">
00909 </td>
00910 <td align=right>
00911 <input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql>
00912 </td></tr>
00913   <tr>
00914   <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea>
00915   </td>
00916   </tr>
00917  </table>
00918 </form>
00919 
00920 <?php
00921                 if (!isset($_REQUEST['sql'])) return;
00922                 
00923                 $sql = $this->undomq(trim($sql));
00924                 if (substr($sql,strlen($sql)-1) === ';') {
00925                         $print = true;
00926                         $sqla = $this->SplitSQL($sql);
00927                 } else  {
00928                         $print = false;
00929                         $sqla = array($sql);
00930                 }
00931                 foreach($sqla as $sqls) {
00932 
00933                         if (!$sqls) continue;
00934                         
00935                         if ($print) {
00936                                 print "<p>".htmlspecialchars($sqls)."</p>";
00937                                 flush();
00938                         }
00939                         $savelog = $this->conn->LogSQL(false);
00940                         $rs = $this->conn->Execute($sqls);
00941                         $this->conn->LogSQL($savelog);
00942                         if ($rs && is_object($rs) && !$rs->EOF) {
00943                                 rs2html($rs);
00944                                 while ($rs->NextRecordSet()) {
00945                                         print "<table width=98% bgcolor=#C0C0FF><tr><td>&nbsp;</td></tr></table>";
00946                                         rs2html($rs);
00947                                 }
00948                         } else {
00949                                 $e1 = (integer) $this->conn->ErrorNo();
00950                                 $e2 = $this->conn->ErrorMsg();
00951                                 if (($e1) || ($e2)) {
00952                                         if (empty($e1)) $e1 = '-1'; // postgresql fix
00953                                         print ' &nbsp; '.$e1.': '.$e2;
00954                                 } else {
00955                                         print "<p>No Recordset returned<br></p>";
00956                                 }
00957                         }
00958                 } // foreach
00959         }
00960         
00961         function SplitSQL($sql)
00962         {
00963                 $arr = explode(';',$sql);
00964                 return $arr;
00965         }
00966         
00967         function undomq($m) 
00968         {
00969         if (get_magic_quotes_gpc()) {
00970                 // undo the damage
00971                 $m = str_replace('\\\\','\\',$m);
00972                 $m = str_replace('\"','"',$m);
00973                 $m = str_replace('\\\'','\'',$m);
00974         }
00975         return $m;
00976 }
00977 
00978     
00979    /************************************************************************/
00980    
01004     function OptimizeTables()
01005     {
01006         $args = func_get_args();
01007         $numArgs = func_num_args();
01008         
01009         if ( $numArgs == 0) return false;
01010         
01011         $mode = ADODB_OPT_LOW; 
01012         $lastArg = $args[ $numArgs - 1];
01013         if ( !is_string($lastArg)) {
01014             $mode = $lastArg;
01015             unset( $args[ $numArgs - 1]);
01016         }
01017         
01018         foreach( $args as $table) {
01019             $this->optimizeTable( $table, $mode);
01020         }
01021         }
01022 
01035     function OptimizeTable( $table, $mode = ADODB_OPT_LOW) 
01036     {
01037         ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType));
01038         return false;
01039     }
01040     
01049     function optimizeDatabase() 
01050     {
01051         $conn = $this->conn;
01052         if ( !$conn) return false;
01053         
01054         $tables = $conn->MetaTables( 'TABLES');
01055         if ( !$tables ) return false;
01056 
01057         foreach( $tables as $table) {
01058             if ( !$this->optimizeTable( $table)) {
01059                 return false;
01060             }
01061         }
01062       
01063         return true;
01064     }
01065     // end hack 
01066 }
01067 
01068 ?>


Généré par L'expert TYPO3 avec  doxygen 1.4.6