[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3  *
   4  * @package dbal
   5  * @version $Id$
   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  if (!class_exists('phpbb_error_collector'))
  22  {
  23      include($phpbb_root_path . 'includes/error_collector.' . $phpEx);
  24  }
  25  
  26  /**
  27  * PostgreSQL Database Abstraction Layer
  28  * Minimum Requirement is Version 7.3+
  29  * @package dbal
  30  */
  31  class dbal_postgres extends dbal
  32  {
  33      var $last_query_text = '';
  34      var $connect_error = '';
  35  
  36      /**
  37      * Connect to server
  38      */
  39  	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
  40      {
  41          $connect_string = '';
  42  
  43          if ($sqluser)
  44          {
  45              $connect_string .= "user=$sqluser ";
  46          }
  47  
  48          if ($sqlpassword)
  49          {
  50              $connect_string .= "password=$sqlpassword ";
  51          }
  52  
  53          if ($sqlserver)
  54          {
  55              // $sqlserver can carry a port separated by : for compatibility reasons
  56              // If $sqlserver has more than one : it's probably an IPv6 address.
  57              // In this case we only allow passing a port via the $port variable.
  58              if (substr_count($sqlserver, ':') === 1)
  59              {
  60                  list($sqlserver, $port) = explode(':', $sqlserver);
  61              }
  62  
  63              if ($sqlserver !== 'localhost')
  64              {
  65                  $connect_string .= "host=$sqlserver ";
  66              }
  67  
  68              if ($port)
  69              {
  70                  $connect_string .= "port=$port ";
  71              }
  72          }
  73  
  74          $schema = '';
  75  
  76          if ($database)
  77          {
  78              $this->dbname = $database;
  79              if (strpos($database, '.') !== false)
  80              {
  81                  list($database, $schema) = explode('.', $database);
  82              }
  83              $connect_string .= "dbname=$database";
  84          }
  85  
  86          $this->persistency = $persistency;
  87  
  88          if ($this->persistency)
  89          {
  90              if (!function_exists('pg_pconnect'))
  91              {
  92                  $this->connect_error = 'pg_pconnect function does not exist, is pgsql extension installed?';
  93                  return $this->sql_error('');
  94              }
  95              $collector = new phpbb_error_collector;
  96              $collector->install();
  97              $this->db_connect_id = (!$new_link) ? @pg_pconnect($connect_string) : @pg_pconnect($connect_string, PGSQL_CONNECT_FORCE_NEW);
  98          }
  99          else
 100          {
 101              if (!function_exists('pg_connect'))
 102              {
 103                  $this->connect_error = 'pg_connect function does not exist, is pgsql extension installed?';
 104                  return $this->sql_error('');
 105              }
 106              $collector = new phpbb_error_collector;
 107              $collector->install();
 108              $this->db_connect_id = (!$new_link) ? @pg_connect($connect_string) : @pg_connect($connect_string, PGSQL_CONNECT_FORCE_NEW);
 109          }
 110  
 111          $collector->uninstall();
 112  
 113          if ($this->db_connect_id)
 114          {
 115              if (version_compare($this->sql_server_info(true), '8.2', '>='))
 116              {
 117                  $this->multi_insert = true;
 118              }
 119  
 120              if ($schema !== '')
 121              {
 122                  @pg_query($this->db_connect_id, 'SET search_path TO ' . $schema);
 123              }
 124              return $this->db_connect_id;
 125          }
 126  
 127          $this->connect_error = $collector->format_errors();
 128          return $this->sql_error('');
 129      }
 130  
 131      /**
 132      * Version information about used database
 133      * @param bool $raw if true, only return the fetched sql_server_version
 134      * @param bool $use_cache If true, it is safe to retrieve the value from the cache
 135      * @return string sql server version
 136      */
 137  	function sql_server_info($raw = false, $use_cache = true)
 138      {
 139          global $cache;
 140  
 141          if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('pgsql_version')) === false)
 142          {
 143              $query_id = @pg_query($this->db_connect_id, 'SELECT VERSION() AS version');
 144              $row = @pg_fetch_assoc($query_id, null);
 145              @pg_free_result($query_id);
 146  
 147              $this->sql_server_version = (!empty($row['version'])) ? trim(substr($row['version'], 10)) : 0;
 148  
 149              if (!empty($cache) && $use_cache)
 150              {
 151                  $cache->put('pgsql_version', $this->sql_server_version);
 152              }
 153          }
 154  
 155          return ($raw) ? $this->sql_server_version : 'PostgreSQL ' . $this->sql_server_version;
 156      }
 157  
 158      /**
 159      * SQL Transaction
 160      * @access private
 161      */
 162  	function _sql_transaction($status = 'begin')
 163      {
 164          switch ($status)
 165          {
 166              case 'begin':
 167                  return @pg_query($this->db_connect_id, 'BEGIN');
 168              break;
 169  
 170              case 'commit':
 171                  return @pg_query($this->db_connect_id, 'COMMIT');
 172              break;
 173  
 174              case 'rollback':
 175                  return @pg_query($this->db_connect_id, 'ROLLBACK');
 176              break;
 177          }
 178  
 179          return true;
 180      }
 181  
 182      /**
 183      * Base query method
 184      *
 185      * @param    string    $query        Contains the SQL query which shall be executed
 186      * @param    int        $cache_ttl    Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
 187      * @return    mixed                When casted to bool the returned value returns true on success and false on failure
 188      *
 189      * @access    public
 190      */
 191  	function sql_query($query = '', $cache_ttl = 0)
 192      {
 193          if ($query != '')
 194          {
 195              global $cache;
 196  
 197              // EXPLAIN only in extra debug mode
 198              if (defined('DEBUG_EXTRA'))
 199              {
 200                  $this->sql_report('start', $query);
 201              }
 202  
 203              $this->last_query_text = $query;
 204              $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
 205              $this->sql_add_num_queries($this->query_result);
 206  
 207              if ($this->query_result === false)
 208              {
 209                  if (($this->query_result = @pg_query($this->db_connect_id, $query)) === false)
 210                  {
 211                      $this->sql_error($query);
 212                  }
 213  
 214                  if (defined('DEBUG_EXTRA'))
 215                  {
 216                      $this->sql_report('stop', $query);
 217                  }
 218  
 219                  if ($cache_ttl && method_exists($cache, 'sql_save'))
 220                  {
 221                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 222                      $cache->sql_save($query, $this->query_result, $cache_ttl);
 223                  }
 224                  else if (strpos($query, 'SELECT') === 0 && $this->query_result)
 225                  {
 226                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 227                  }
 228              }
 229              else if (defined('DEBUG_EXTRA'))
 230              {
 231                  $this->sql_report('fromcache', $query);
 232              }
 233          }
 234          else
 235          {
 236              return false;
 237          }
 238  
 239          return $this->query_result;
 240      }
 241  
 242      /**
 243      * Build db-specific query data
 244      * @access private
 245      */
 246  	function _sql_custom_build($stage, $data)
 247      {
 248          return $data;
 249      }
 250  
 251      /**
 252      * Build LIMIT query
 253      */
 254  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 255      {
 256          $this->query_result = false;
 257  
 258          // if $total is set to 0 we do not want to limit the number of rows
 259          if ($total == 0)
 260          {
 261              $total = 'ALL';
 262          }
 263  
 264          $query .= "\n LIMIT $total OFFSET $offset";
 265  
 266          return $this->sql_query($query, $cache_ttl);
 267      }
 268  
 269      /**
 270      * Return number of affected rows
 271      */
 272  	function sql_affectedrows()
 273      {
 274          return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
 275      }
 276  
 277      /**
 278      * Fetch current row
 279      */
 280  	function sql_fetchrow($query_id = false)
 281      {
 282          global $cache;
 283  
 284          if ($query_id === false)
 285          {
 286              $query_id = $this->query_result;
 287          }
 288  
 289          if (isset($cache->sql_rowset[$query_id]))
 290          {
 291              return $cache->sql_fetchrow($query_id);
 292          }
 293  
 294          return ($query_id !== false) ? @pg_fetch_assoc($query_id, null) : false;
 295      }
 296  
 297      /**
 298      * Seek to given row number
 299      * rownum is zero-based
 300      */
 301  	function sql_rowseek($rownum, &$query_id)
 302      {
 303          global $cache;
 304  
 305          if ($query_id === false)
 306          {
 307              $query_id = $this->query_result;
 308          }
 309  
 310          if (isset($cache->sql_rowset[$query_id]))
 311          {
 312              return $cache->sql_rowseek($rownum, $query_id);
 313          }
 314  
 315          return ($query_id !== false) ? @pg_result_seek($query_id, $rownum) : false;
 316      }
 317  
 318      /**
 319      * Get last inserted id after insert statement
 320      */
 321  	function sql_nextid()
 322      {
 323          $query_id = $this->query_result;
 324  
 325          if ($query_id !== false && $this->last_query_text != '')
 326          {
 327              if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
 328              {
 329                  $query = "SELECT currval('" . $tablename[1] . "_seq') AS last_value";
 330                  $temp_q_id = @pg_query($this->db_connect_id, $query);
 331  
 332                  if (!$temp_q_id)
 333                  {
 334                      return false;
 335                  }
 336  
 337                  $temp_result = @pg_fetch_assoc($temp_q_id, NULL);
 338                  @pg_free_result($query_id);
 339  
 340                  return ($temp_result) ? $temp_result['last_value'] : false;
 341              }
 342          }
 343  
 344          return false;
 345      }
 346  
 347      /**
 348      * Free sql result
 349      */
 350  	function sql_freeresult($query_id = false)
 351      {
 352          global $cache;
 353  
 354          if ($query_id === false)
 355          {
 356              $query_id = $this->query_result;
 357          }
 358  
 359          if (isset($cache->sql_rowset[$query_id]))
 360          {
 361              return $cache->sql_freeresult($query_id);
 362          }
 363  
 364          if (isset($this->open_queries[(int) $query_id]))
 365          {
 366              unset($this->open_queries[(int) $query_id]);
 367              return @pg_free_result($query_id);
 368          }
 369  
 370          return false;
 371      }
 372  
 373      /**
 374      * Escape string used in sql query
 375      * Note: Do not use for bytea values if we may use them at a later stage
 376      */
 377  	function sql_escape($msg)
 378      {
 379          return @pg_escape_string($msg);
 380      }
 381  
 382      /**
 383      * Build LIKE expression
 384      * @access private
 385      */
 386  	function _sql_like_expression($expression)
 387      {
 388          return $expression;
 389      }
 390  
 391      /**
 392      * return sql error array
 393      * @access private
 394      */
 395  	function _sql_error()
 396      {
 397          // pg_last_error only works when there is an established connection.
 398          // Connection errors have to be tracked by us manually.
 399          if ($this->db_connect_id)
 400          {
 401              $message = @pg_last_error($this->db_connect_id);
 402          }
 403          else
 404          {
 405              $message = $this->connect_error;
 406          }
 407  
 408          return array(
 409              'message'    => $message,
 410              'code'        => ''
 411          );
 412      }
 413  
 414      /**
 415      * Close sql connection
 416      * @access private
 417      */
 418  	function _sql_close()
 419      {
 420          return @pg_close($this->db_connect_id);
 421      }
 422  
 423      /**
 424      * Build db-specific report
 425      * @access private
 426      */
 427  	function _sql_report($mode, $query = '')
 428      {
 429          switch ($mode)
 430          {
 431              case 'start':
 432  
 433                  $explain_query = $query;
 434                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 435                  {
 436                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 437                  }
 438                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 439                  {
 440                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 441                  }
 442  
 443                  if (preg_match('/^SELECT/', $explain_query))
 444                  {
 445                      $html_table = false;
 446  
 447                      if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
 448                      {
 449                          while ($row = @pg_fetch_assoc($result, NULL))
 450                          {
 451                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 452                          }
 453                      }
 454                      @pg_free_result($result);
 455  
 456                      if ($html_table)
 457                      {
 458                          $this->html_hold .= '</table>';
 459                      }
 460                  }
 461  
 462              break;
 463  
 464              case 'fromcache':
 465                  $endtime = explode(' ', microtime());
 466                  $endtime = $endtime[0] + $endtime[1];
 467  
 468                  $result = @pg_query($this->db_connect_id, $query);
 469                  while ($void = @pg_fetch_assoc($result, NULL))
 470                  {
 471                      // Take the time spent on parsing rows into account
 472                  }
 473                  @pg_free_result($result);
 474  
 475                  $splittime = explode(' ', microtime());
 476                  $splittime = $splittime[0] + $splittime[1];
 477  
 478                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 479  
 480              break;
 481          }
 482      }
 483  }
 484  
 485  ?>