[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3  *
   4  * This file is part of the phpBB Forum Software package.
   5  *
   6  * @copyright (c) phpBB Limited <https://www.phpbb.com>
   7  * @license GNU General Public License, version 2 (GPL-2.0)
   8  *
   9  * For full copyright and license information, please see
  10  * the docs/CREDITS.txt file.
  11  *
  12  */
  13  
  14  namespace phpbb\db\driver;
  15  
  16  /**
  17  * PostgreSQL Database Abstraction Layer
  18  * Minimum Requirement is Version 8.3+
  19  */
  20  class postgres extends \phpbb\db\driver\driver
  21  {
  22      var $multi_insert = true;
  23      var $last_query_text = '';
  24      var $connect_error = '';
  25  
  26      /**
  27      * {@inheritDoc}
  28      */
  29  	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
  30      {
  31          $connect_string = '';
  32  
  33          if ($sqluser)
  34          {
  35              $connect_string .= "user=$sqluser ";
  36          }
  37  
  38          if ($sqlpassword)
  39          {
  40              $connect_string .= "password='$sqlpassword' ";
  41          }
  42  
  43          if ($sqlserver)
  44          {
  45              // $sqlserver can carry a port separated by : for compatibility reasons
  46              // If $sqlserver has more than one : it's probably an IPv6 address.
  47              // In this case we only allow passing a port via the $port variable.
  48              if (substr_count($sqlserver, ':') === 1)
  49              {
  50                  list($sqlserver, $port) = explode(':', $sqlserver);
  51              }
  52  
  53              if ($sqlserver !== 'localhost')
  54              {
  55                  $connect_string .= "host=$sqlserver ";
  56              }
  57  
  58              if ($port)
  59              {
  60                  $connect_string .= "port=$port ";
  61              }
  62          }
  63  
  64          $schema = '';
  65  
  66          if ($database)
  67          {
  68              $this->dbname = $database;
  69              if (strpos($database, '.') !== false)
  70              {
  71                  list($database, $schema) = explode('.', $database);
  72              }
  73              $connect_string .= "dbname=$database";
  74          }
  75  
  76          $this->persistency = $persistency;
  77  
  78          if ($this->persistency)
  79          {
  80              if (!function_exists('pg_pconnect'))
  81              {
  82                  $this->connect_error = 'pg_pconnect function does not exist, is pgsql extension installed?';
  83                  return $this->sql_error('');
  84              }
  85              $collector = new \phpbb\error_collector;
  86              $collector->install();
  87              $this->db_connect_id = (!$new_link) ? @pg_pconnect($connect_string) : @pg_pconnect($connect_string, PGSQL_CONNECT_FORCE_NEW);
  88          }
  89          else
  90          {
  91              if (!function_exists('pg_connect'))
  92              {
  93                  $this->connect_error = 'pg_connect function does not exist, is pgsql extension installed?';
  94                  return $this->sql_error('');
  95              }
  96              $collector = new \phpbb\error_collector;
  97              $collector->install();
  98              $this->db_connect_id = (!$new_link) ? @pg_connect($connect_string) : @pg_connect($connect_string, PGSQL_CONNECT_FORCE_NEW);
  99          }
 100  
 101          $collector->uninstall();
 102  
 103          if ($this->db_connect_id)
 104          {
 105              if ($schema !== '')
 106              {
 107                  @pg_query($this->db_connect_id, 'SET search_path TO ' . $schema);
 108              }
 109              return $this->db_connect_id;
 110          }
 111  
 112          $this->connect_error = $collector->format_errors();
 113          return $this->sql_error('');
 114      }
 115  
 116      /**
 117      * {@inheritDoc}
 118      */
 119  	function sql_server_info($raw = false, $use_cache = true)
 120      {
 121          global $cache;
 122  
 123          if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('pgsql_version')) === false)
 124          {
 125              $query_id = @pg_query($this->db_connect_id, 'SELECT VERSION() AS version');
 126              if ($query_id)
 127              {
 128                  $row = pg_fetch_assoc($query_id, null);
 129                  pg_free_result($query_id);
 130  
 131                  $this->sql_server_version = (!empty($row['version'])) ? trim(substr($row['version'], 10)) : 0;
 132  
 133                  if (!empty($cache) && $use_cache)
 134                  {
 135                      $cache->put('pgsql_version', $this->sql_server_version);
 136                  }
 137              }
 138          }
 139  
 140          return ($raw) ? $this->sql_server_version : 'PostgreSQL ' . $this->sql_server_version;
 141      }
 142  
 143      /**
 144      * SQL Transaction
 145      * @access private
 146      */
 147  	function _sql_transaction($status = 'begin')
 148      {
 149          switch ($status)
 150          {
 151              case 'begin':
 152                  return @pg_query($this->db_connect_id, 'BEGIN');
 153              break;
 154  
 155              case 'commit':
 156                  return @pg_query($this->db_connect_id, 'COMMIT');
 157              break;
 158  
 159              case 'rollback':
 160                  return @pg_query($this->db_connect_id, 'ROLLBACK');
 161              break;
 162          }
 163  
 164          return true;
 165      }
 166  
 167      /**
 168      * {@inheritDoc}
 169      */
 170  	function sql_query($query = '', $cache_ttl = 0)
 171      {
 172          if ($query != '')
 173          {
 174              global $cache;
 175  
 176              if ($this->debug_sql_explain)
 177              {
 178                  $this->sql_report('start', $query);
 179              }
 180              else if ($this->debug_load_time)
 181              {
 182                  $this->curtime = microtime(true);
 183              }
 184  
 185              $this->last_query_text = $query;
 186              $this->query_result = ($cache && $cache_ttl) ? $cache->sql_load($query) : false;
 187              $this->sql_add_num_queries($this->query_result);
 188  
 189              if ($this->query_result === false)
 190              {
 191                  try
 192                  {
 193                      $this->query_result = @pg_query($this->db_connect_id, $query);
 194                  }
 195                  catch (\Error $e)
 196                  {
 197                      // Do nothing as SQL driver will report the error
 198                  }
 199  
 200                  if ($this->query_result === false)
 201                  {
 202                      $this->sql_error($query);
 203                  }
 204  
 205                  if ($this->debug_sql_explain)
 206                  {
 207                      $this->sql_report('stop', $query);
 208                  }
 209                  else if ($this->debug_load_time)
 210                  {
 211                      $this->sql_time += microtime(true) - $this->curtime;
 212                  }
 213  
 214                  if (!$this->query_result)
 215                  {
 216                      return false;
 217                  }
 218  
 219                  $safe_query_id = $this->clean_query_id($this->query_result);
 220  
 221                  if ($cache && $cache_ttl)
 222                  {
 223                      $this->open_queries[$safe_query_id] = $this->query_result;
 224                      $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl);
 225                  }
 226                  else if (strpos($query, 'SELECT') === 0)
 227                  {
 228                      $this->open_queries[$safe_query_id] = $this->query_result;
 229                  }
 230              }
 231              else if ($this->debug_sql_explain)
 232              {
 233                  $this->sql_report('fromcache', $query);
 234              }
 235          }
 236          else
 237          {
 238              return false;
 239          }
 240  
 241          return $this->query_result;
 242      }
 243  
 244      /**
 245      * Build db-specific query data
 246      * @access private
 247      */
 248  	function _sql_custom_build($stage, $data)
 249      {
 250          return $data;
 251      }
 252  
 253      /**
 254      * Build LIMIT query
 255      */
 256  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 257      {
 258          $this->query_result = false;
 259  
 260          // if $total is set to 0 we do not want to limit the number of rows
 261          if ($total == 0)
 262          {
 263              $total = 'ALL';
 264          }
 265  
 266          $query .= "\n LIMIT $total OFFSET $offset";
 267  
 268          return $this->sql_query($query, $cache_ttl);
 269      }
 270  
 271      /**
 272      * {@inheritDoc}
 273      */
 274  	function sql_affectedrows()
 275      {
 276          return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
 277      }
 278  
 279      /**
 280      * {@inheritDoc}
 281      */
 282  	function sql_fetchrow($query_id = false)
 283      {
 284          global $cache;
 285  
 286          if ($query_id === false)
 287          {
 288              $query_id = $this->query_result;
 289          }
 290  
 291          $safe_query_id = $this->clean_query_id($query_id);
 292          if ($cache && $cache->sql_exists($safe_query_id))
 293          {
 294              return $cache->sql_fetchrow($safe_query_id);
 295          }
 296  
 297          return ($query_id) ? pg_fetch_assoc($query_id, null) : false;
 298      }
 299  
 300      /**
 301      * {@inheritDoc}
 302      */
 303  	function sql_rowseek($rownum, &$query_id)
 304      {
 305          global $cache;
 306  
 307          if ($query_id === false)
 308          {
 309              $query_id = $this->query_result;
 310          }
 311  
 312          $safe_query_id = $this->clean_query_id($query_id);
 313          if ($cache && $cache->sql_exists($safe_query_id))
 314          {
 315              return $cache->sql_rowseek($rownum, $safe_query_id);
 316          }
 317  
 318          return ($query_id) ? @pg_result_seek($query_id, $rownum) : false;
 319      }
 320  
 321      /**
 322       * {@inheritDoc}
 323       */
 324  	function sql_fetchfield($field, $rownum = false, $query_id = false)
 325      {
 326          global $cache;
 327  
 328          if ($query_id === false)
 329          {
 330              $query_id = $this->query_result;
 331          }
 332  
 333          if ($query_id)
 334          {
 335              if ($rownum !== false)
 336              {
 337                  $this->sql_rowseek($rownum, $query_id);
 338              }
 339  
 340              $safe_query_id = $this->clean_query_id($query_id);
 341              if ($cache && !is_object($query_id) && $cache->sql_exists($safe_query_id))
 342              {
 343                  return $cache->sql_fetchfield($safe_query_id, $field);
 344              }
 345  
 346              $row = $this->sql_fetchrow($query_id);
 347              return (isset($row[$field])) ? $row[$field] : false;
 348          }
 349  
 350          return false;
 351      }
 352  
 353      /**
 354       * {@inheritdoc}
 355       */
 356  	public function sql_last_inserted_id()
 357      {
 358          $query_id = $this->query_result;
 359  
 360          if ($query_id !== false && $this->last_query_text != '')
 361          {
 362              if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
 363              {
 364                  $query = "SELECT currval('" . $tablename[1] . "_seq') AS last_value";
 365                  $temp_q_id = @pg_query($this->db_connect_id, $query);
 366  
 367                  if (!$temp_q_id)
 368                  {
 369                      return false;
 370                  }
 371  
 372                  $temp_result = pg_fetch_assoc($temp_q_id, null);
 373                  pg_free_result($query_id);
 374  
 375                  return ($temp_result) ? $temp_result['last_value'] : false;
 376              }
 377          }
 378  
 379          return false;
 380      }
 381  
 382      /**
 383      * {@inheritDoc}
 384      */
 385  	function sql_freeresult($query_id = false)
 386      {
 387          global $cache;
 388  
 389          if ($query_id === false)
 390          {
 391              $query_id = $this->query_result;
 392          }
 393  
 394          $safe_query_id = $this->clean_query_id($query_id);
 395          if ($cache && !is_object($query_id) && $cache->sql_exists($safe_query_id))
 396          {
 397              return $cache->sql_freeresult($safe_query_id);
 398          }
 399  
 400          if (isset($this->open_queries[$safe_query_id]))
 401          {
 402              unset($this->open_queries[$safe_query_id]);
 403              return pg_free_result($query_id);
 404          }
 405  
 406          return false;
 407      }
 408  
 409      /**
 410      * {@inheritDoc}
 411      */
 412  	function sql_escape($msg)
 413      {
 414          return @pg_escape_string($msg);
 415      }
 416  
 417      /**
 418      * Build LIKE expression
 419      * @access private
 420      */
 421  	function _sql_like_expression($expression)
 422      {
 423          return $expression;
 424      }
 425  
 426      /**
 427      * Build NOT LIKE expression
 428      * @access private
 429      */
 430  	function _sql_not_like_expression($expression)
 431      {
 432          return $expression;
 433      }
 434  
 435      /**
 436      * {@inheritDoc}
 437      */
 438  	function cast_expr_to_bigint($expression)
 439      {
 440          return 'CAST(' . $expression . ' as DECIMAL(255, 0))';
 441      }
 442  
 443      /**
 444      * {@inheritDoc}
 445      */
 446  	function cast_expr_to_string($expression)
 447      {
 448          return 'CAST(' . $expression . ' as VARCHAR(255))';
 449      }
 450  
 451      /**
 452      * return sql error array
 453      * @access private
 454      */
 455  	function _sql_error()
 456      {
 457          // pg_last_error only works when there is an established connection.
 458          // Connection errors have to be tracked by us manually.
 459          if ($this->db_connect_id)
 460          {
 461              $message = @pg_last_error($this->db_connect_id);
 462          }
 463          else
 464          {
 465              $message = $this->connect_error;
 466          }
 467  
 468          return array(
 469              'message'    => $message,
 470              'code'        => ''
 471          );
 472      }
 473  
 474      /**
 475      * Close sql connection
 476      * @access private
 477      */
 478  	function _sql_close()
 479      {
 480          // Released resources are already closed, return true in this case
 481          if (!is_resource($this->db_connect_id))
 482          {
 483              return true;
 484          }
 485          return @pg_close($this->db_connect_id);
 486      }
 487  
 488      /**
 489      * Build db-specific report
 490      * @access private
 491      */
 492  	function _sql_report($mode, $query = '')
 493      {
 494          switch ($mode)
 495          {
 496              case 'start':
 497  
 498                  $explain_query = $query;
 499                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 500                  {
 501                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 502                  }
 503                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 504                  {
 505                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 506                  }
 507  
 508                  if (preg_match('/^SELECT/', $explain_query))
 509                  {
 510                      $html_table = false;
 511  
 512                      if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
 513                      {
 514                          while ($row = pg_fetch_assoc($result, null))
 515                          {
 516                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 517                          }
 518                          pg_free_result($result);
 519                      }
 520  
 521                      if ($html_table)
 522                      {
 523                          $this->html_hold .= '</table>';
 524                      }
 525                  }
 526  
 527              break;
 528  
 529              case 'fromcache':
 530                  $endtime = explode(' ', microtime());
 531                  $endtime = $endtime[0] + $endtime[1];
 532  
 533                  $result = @pg_query($this->db_connect_id, $query);
 534                  if ($result)
 535                  {
 536                      while ($void = pg_fetch_assoc($result, null))
 537                      {
 538                          // Take the time spent on parsing rows into account
 539                      }
 540                      pg_free_result($result);
 541                  }
 542  
 543                  $splittime = explode(' ', microtime());
 544                  $splittime = $splittime[0] + $splittime[1];
 545  
 546                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 547  
 548              break;
 549          }
 550      }
 551  
 552      /**
 553      * {@inheritDoc}
 554      */
 555  	function sql_quote($msg)
 556      {
 557          return '"' . $msg . '"';
 558      }
 559  }