[ Index ]

PHP Cross Reference of phpBB 3.0 Beta 3

title

Body

[close]

/includes/db/ -> oracle.php (source)

   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  ?>


Generated: Wed Nov 22 00:35:05 2006 Cross-referenced by PHPXref 0.6