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