[ Index ] |
PHP Cross Reference of phpBB 3.0 Beta 3 |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * 4 * @package dbal 5 * @version $Id: oracle.php,v 1.29 2006/10/14 14:56:44 acydburn Exp $ 6 * @copyright (c) 2005 phpBB Group 7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License 8 * 9 */ 10 11 /** 12 * @ignore 13 */ 14 if (!defined('IN_PHPBB')) 15 { 16 exit; 17 } 18 19 include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx); 20 21 /** 22 * Oracle Database Abstraction Layer 23 * @package dbal 24 */ 25 class dbal_oracle extends dbal 26 { 27 var $last_query_text = ''; 28 29 /** 30 * Connect to server 31 */ 32 function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) 33 { 34 $this->persistency = $persistency; 35 $this->user = $sqluser; 36 $this->server = $sqlserver . (($port) ? ':' . $port : ''); 37 $this->dbname = $database; 38 39 $this->db_connect_id = ($this->persistency) ? @ociplogon($this->user, $sqlpassword, $this->server, 'UTF8') : @ocinlogon($this->user, $sqlpassword, $this->server, 'UTF8'); 40 41 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); 42 } 43 44 /** 45 * Version information about used database 46 */ 47 function sql_server_info() 48 { 49 return 'Oracle ' . @ociserverversion($this->db_connect_id); 50 } 51 52 /** 53 * SQL Transaction 54 * @access private 55 */ 56 function _sql_transaction($status = 'begin') 57 { 58 switch ($status) 59 { 60 case 'begin': 61 return true; 62 break; 63 64 case 'commit': 65 return @ocicommit($this->db_connect_id); 66 break; 67 68 case 'rollback': 69 return @ocirollback($this->db_connect_id); 70 break; 71 } 72 73 return true; 74 } 75 76 /** 77 * Base query method 78 * 79 * @param string $query Contains the SQL query which shall be executed 80 * @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache 81 * @return mixed When casted to bool the returned value returns true on success and false on failure 82 * 83 * @access public 84 */ 85 function sql_query($query = '', $cache_ttl = 0) 86 { 87 if ($query != '') 88 { 89 global $cache; 90 91 // EXPLAIN only in extra debug mode 92 if (defined('DEBUG_EXTRA')) 93 { 94 $this->sql_report('start', $query); 95 } 96 97 $this->last_query_text = $query; 98 $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false; 99 $this->sql_add_num_queries($this->query_result); 100 101 if ($this->query_result === false) 102 { 103 $in_transaction = false; 104 if (!$this->transaction) 105 { 106 $this->sql_transaction('begin'); 107 } 108 else 109 { 110 $in_transaction = true; 111 } 112 113 $array = array(); 114 115 // We overcome Oracle's 4000 char limit by binding vars 116 if (preg_match('/^(INSERT INTO[^(]+)\\(([^()]+)\\) VALUES[^(]+\\(([^()]+)\\)$/', $query, $regs)) 117 { 118 if (strlen($regs[3]) > 4000) 119 { 120 $cols = explode(', ', $regs[2]); 121 $vals = explode(', ', $regs[3]); 122 foreach ($vals as $key => $value) 123 { 124 if (strlen($value) > 4002) // check to see if this thing is greater than the max + 'x2 125 { 126 $vals[$key] = ':' . strtoupper($cols[$key]); 127 $array[$vals[$key]] = substr($value, 1, -1); 128 } 129 } 130 $query = $regs[1] . '(' . implode(', ', $cols) . ') VALUES (' . implode(', ', $vals) . ')'; 131 } 132 } 133 else if (preg_match('/^(UPDATE.*?)SET (.*)(\\sWHERE.*)$/s', $query, $regs)) 134 { 135 if (strlen($regs[2]) > 4000) 136 { 137 $args = explode(', ', $regs[2]); 138 $cols = array(); 139 foreach ($args as $value) 140 { 141 $temp_array = explode('=', $value); 142 $cols[$temp_array[0]] = $temp_array[1]; 143 } 144 145 foreach ($cols as $col => $val) 146 { 147 if (strlen($val) > 4003) // check to see if this thing is greater than the max + 'x2 + a space 148 { 149 $cols[$col] = ' :' . strtoupper(rtrim($col)); 150 $array[ltrim($cols[$col])] = substr(trim($val), 2, -1); 151 } 152 } 153 154 $art = array(); 155 foreach ($cols as $col => $val) 156 { 157 $art[] = $col . '=' . $val; 158 } 159 $query = $regs[1] . 'SET ' . implode(', ', $art) . $regs[3]; 160 } 161 } 162 163 $this->query_result = @ociparse($this->db_connect_id, $query); 164 165 foreach ($array as $key => $value) 166 { 167 @ocibindbyname($this->query_result, $key, $array[$key], -1); 168 } 169 170 $success = @ociexecute($this->query_result, OCI_DEFAULT); 171 172 if (!$success) 173 { 174 $this->sql_error($query); 175 $this->query_result = false; 176 } 177 else 178 { 179 if (!$in_transaction) 180 { 181 $this->sql_transaction('commit'); 182 } 183 } 184 185 if (defined('DEBUG_EXTRA')) 186 { 187 $this->sql_report('stop', $query); 188 } 189 190 if ($cache_ttl && method_exists($cache, 'sql_save')) 191 { 192 $this->open_queries[(int) $this->query_result] = $this->query_result; 193 $cache->sql_save($query, $this->query_result, $cache_ttl); 194 } 195 else if (strpos($query, 'SELECT') === 0 && $this->query_result) 196 { 197 $this->open_queries[(int) $this->query_result] = $this->query_result; 198 } 199 } 200 else if (defined('DEBUG_EXTRA')) 201 { 202 $this->sql_report('fromcache', $query); 203 } 204 } 205 else 206 { 207 return false; 208 } 209 210 return ($this->query_result) ? $this->query_result : false; 211 } 212 213 /** 214 * Build LIMIT query 215 */ 216 function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) 217 { 218 if ($query != '') 219 { 220 $this->query_result = false; 221 222 // Any implicit columns exist? 223 if (strpos($query, '.*') !== false) 224 { 225 // This sucker does a few things for us. It grabs all the explicitly named columns and what tables are being used 226 preg_match('/SELECT (?:DISTINCT )?(.*?)FROM(.*?)(?:WHERE|(ORDER|GROUP) BY|$)/s', $query, $tables); 227 228 // The prefixes of the explicit columns don't matter, they simply get in the way 229 preg_match_all('/\.(\w+)/', trim($tables[1]), $columns); 230 231 // Flip lets us do an easy isset() call 232 $columns = array_flip($columns[1]); 233 234 $table_data = trim($tables[2]); 235 236 // Grab the implicitly named columns, they need expanding... 237 preg_match_all('/(\w)\.\*/', $query, $info); 238 239 $cols = array(); 240 241 foreach ($info[1] as $table_alias) 242 { 243 // We need to get the name of the aliased table 244 preg_match('/(\w+) ' . $table_alias . '/', $table_data, $table_name); 245 $table_name = $table_name[1]; 246 247 $sql = "SELECT column_name 248 FROM all_tab_cols 249 WHERE table_name = '" . strtoupper($table_name) . "'"; 250 251 $result = $this->sql_query($sql); 252 while ($row = $this->sql_fetchrow($result)) 253 { 254 if (!isset($columns[strtolower($row['column_name'])])) 255 { 256 $cols[] = $table_alias . '.' . strtolower($row['column_name']); 257 } 258 } 259 $this->sql_freeresult($result); 260 261 // Remove the implicity .* with it's full expansion 262 $query = str_replace($table_alias . '.*', implode(', ', $cols), $query); 263 264 unset($cols); 265 } 266 } 267 268 $query = 'SELECT * FROM (SELECT /*+ FIRST_ROWS */ rownum AS xrownum, a.* FROM (' . $query . ') a WHERE rownum <= ' . ($offset + $total) . ') WHERE xrownum >= ' . $offset; 269 270 return $this->sql_query($query, $cache_ttl); 271 } 272 else 273 { 274 return false; 275 } 276 } 277 278 /** 279 * Return number of affected rows 280 */ 281 function sql_affectedrows() 282 { 283 return ($this->query_result) ? @ocirowcount($this->query_result) : false; 284 } 285 286 /** 287 * Fetch current row 288 */ 289 function sql_fetchrow($query_id = false) 290 { 291 global $cache; 292 293 if ($query_id === false) 294 { 295 $query_id = $this->query_result; 296 } 297 298 if (isset($cache->sql_rowset[$query_id])) 299 { 300 return $cache->sql_fetchrow($query_id); 301 } 302 303 if ($query_id !== false) 304 { 305 $row = array(); 306 $result = @ocifetchinto($query_id, $row, OCI_ASSOC + OCI_RETURN_NULLS); 307 308 if (!$result || !$row) 309 { 310 return false; 311 } 312 313 $result_row = array(); 314 foreach ($row as $key => $value) 315 { 316 // OCI->CLOB? 317 if (is_object($value)) 318 { 319 $value = $value->load(); 320 } 321 322 $result_row[strtolower($key)] = $value; 323 } 324 325 return $result_row; 326 } 327 328 return false; 329 } 330 331 /** 332 * Seek to given row number 333 * rownum is zero-based 334 */ 335 function sql_rowseek($rownum, $query_id = false) 336 { 337 global $cache; 338 339 if ($query_id === false) 340 { 341 $query_id = $this->query_result; 342 } 343 344 if (isset($cache->sql_rowset[$query_id])) 345 { 346 return $cache->sql_rowseek($rownum, $query_id); 347 } 348 349 if ($query_id === false) 350 { 351 return false; 352 } 353 354 // Reset internal pointer 355 @ociexecute($query_id, OCI_DEFAULT); 356 357 // We do not fetch the row for rownum == 0 because then the next resultset would be the second row 358 for ($i = 0; $i < $rownum; $i++) 359 { 360 if (!$this->sql_fetchrow($query_id)) 361 { 362 return false; 363 } 364 } 365 366 return true; 367 } 368 369 /** 370 * Get last inserted id after insert statement 371 */ 372 function sql_nextid() 373 { 374 $query_id = $this->query_result; 375 376 if ($query_id !== false && $this->last_query_text != '') 377 { 378 if (preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#is', $this->last_query_text, $tablename)) 379 { 380 $query = 'SELECT ' . $tablename[1] . '_seq.currval FROM DUAL'; 381 $stmt = @ociparse($this->db_connect_id, $query); 382 @ociexecute($stmt, OCI_DEFAULT); 383 384 $temp_result = @ocifetchinto($stmt, $temp_array, OCI_ASSOC + OCI_RETURN_NULLS); 385 @ocifreestatement($stmt); 386 387 if ($temp_result) 388 { 389 return $temp_array['CURRVAL']; 390 } 391 else 392 { 393 return false; 394 } 395 } 396 } 397 398 return false; 399 } 400 401 /** 402 * Free sql result 403 */ 404 function sql_freeresult($query_id = false) 405 { 406 global $cache; 407 408 if ($query_id === false) 409 { 410 $query_id = $this->query_result; 411 } 412 413 if (isset($cache->sql_rowset[$query_id])) 414 { 415 return $cache->sql_freeresult($query_id); 416 } 417 418 if (isset($this->open_queries[(int) $query_id])) 419 { 420 unset($this->open_queries[(int) $query_id]); 421 return @ocifreestatement($query_id); 422 } 423 424 return false; 425 } 426 427 /** 428 * Escape string used in sql query 429 */ 430 function sql_escape($msg) 431 { 432 return str_replace("'", "''", $msg); 433 } 434 435 function _sql_custom_build($stage, $data) 436 { 437 return $data; 438 } 439 440 /** 441 * return sql error array 442 * @access private 443 */ 444 function _sql_error() 445 { 446 $error = @ocierror(); 447 $error = (!$error) ? @ocierror($this->query_result) : $error; 448 $error = (!$error) ? @ocierror($this->db_connect_id) : $error; 449 450 if ($error) 451 { 452 $this->last_error_result = $error; 453 } 454 else 455 { 456 $error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array(); 457 } 458 459 return $error; 460 } 461 462 /** 463 * Close sql connection 464 * @access private 465 */ 466 function _sql_close() 467 { 468 return @ocilogoff($this->db_connect_id); 469 } 470 471 /** 472 * Build db-specific report 473 * @access private 474 */ 475 function _sql_report($mode, $query = '') 476 { 477 switch ($mode) 478 { 479 case 'start': 480 break; 481 482 case 'fromcache': 483 $endtime = explode(' ', microtime()); 484 $endtime = $endtime[0] + $endtime[1]; 485 486 $result = @ociparse($this->db_connect_id, $query); 487 $success = @ociexecute($result, OCI_DEFAULT); 488 $row = array(); 489 490 while (@ocifetchinto($result, $row, OCI_ASSOC + OCI_RETURN_NULLS)) 491 { 492 // Take the time spent on parsing rows into account 493 } 494 @ocifreestatement($result); 495 496 $splittime = explode(' ', microtime()); 497 $splittime = $splittime[0] + $splittime[1]; 498 499 $this->sql_report('record_fromcache', $query, $endtime, $splittime); 500 501 break; 502 } 503 } 504 } 505 506 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
Generated: Wed Nov 22 00:35:05 2006 | Cross-referenced by PHPXref 0.6 |