[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                  if (($this->query_result = @pg_query($this->db_connect_id, $query)) === false)
 192                  {
 193                      $this->sql_error($query);
 194                  }
 195  
 196                  if ($this->debug_sql_explain)
 197                  {
 198                      $this->sql_report('stop', $query);
 199                  }
 200                  else if ($this->debug_load_time)
 201                  {
 202                      $this->sql_time += microtime(true) - $this->curtime;
 203                  }
 204  
 205                  if (!$this->query_result)
 206                  {
 207                      return false;
 208                  }
 209  
 210                  $safe_query_id = $this->clean_query_id($this->query_result);
 211  
 212                  if ($cache && $cache_ttl)
 213                  {
 214                      $this->open_queries[$safe_query_id] = $this->query_result;
 215                      $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl);
 216                  }
 217                  else if (strpos($query, 'SELECT') === 0)
 218                  {
 219                      $this->open_queries[$safe_query_id] = $this->query_result;
 220                  }
 221              }
 222              else if ($this->debug_sql_explain)
 223              {
 224                  $this->sql_report('fromcache', $query);
 225              }
 226          }
 227          else
 228          {
 229              return false;
 230          }
 231  
 232          return $this->query_result;
 233      }
 234  
 235      /**
 236      * Build db-specific query data
 237      * @access private
 238      */
 239  	function _sql_custom_build($stage, $data)
 240      {
 241          return $data;
 242      }
 243  
 244      /**
 245      * Build LIMIT query
 246      */
 247  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 248      {
 249          $this->query_result = false;
 250  
 251          // if $total is set to 0 we do not want to limit the number of rows
 252          if ($total == 0)
 253          {
 254              $total = 'ALL';
 255          }
 256  
 257          $query .= "\n LIMIT $total OFFSET $offset";
 258  
 259          return $this->sql_query($query, $cache_ttl);
 260      }
 261  
 262      /**
 263      * {@inheritDoc}
 264      */
 265  	function sql_affectedrows()
 266      {
 267          return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
 268      }
 269  
 270      /**
 271      * {@inheritDoc}
 272      */
 273  	function sql_fetchrow($query_id = false)
 274      {
 275          global $cache;
 276  
 277          if ($query_id === false)
 278          {
 279              $query_id = $this->query_result;
 280          }
 281  
 282          $safe_query_id = $this->clean_query_id($query_id);
 283          if ($cache && $cache->sql_exists($safe_query_id))
 284          {
 285              return $cache->sql_fetchrow($safe_query_id);
 286          }
 287  
 288          return ($query_id) ? pg_fetch_assoc($query_id, null) : false;
 289      }
 290  
 291      /**
 292      * {@inheritDoc}
 293      */
 294  	function sql_rowseek($rownum, &$query_id)
 295      {
 296          global $cache;
 297  
 298          if ($query_id === false)
 299          {
 300              $query_id = $this->query_result;
 301          }
 302  
 303          $safe_query_id = $this->clean_query_id($query_id);
 304          if ($cache && $cache->sql_exists($safe_query_id))
 305          {
 306              return $cache->sql_rowseek($rownum, $safe_query_id);
 307          }
 308  
 309          return ($query_id) ? @pg_result_seek($query_id, $rownum) : false;
 310      }
 311  
 312      /**
 313       * {@inheritDoc}
 314       */
 315  	function sql_fetchfield($field, $rownum = false, $query_id = false)
 316      {
 317          global $cache;
 318  
 319          if ($query_id === false)
 320          {
 321              $query_id = $this->query_result;
 322          }
 323  
 324          if ($query_id)
 325          {
 326              if ($rownum !== false)
 327              {
 328                  $this->sql_rowseek($rownum, $query_id);
 329              }
 330  
 331              $safe_query_id = $this->clean_query_id($query_id);
 332              if ($cache && !is_object($query_id) && $cache->sql_exists($safe_query_id))
 333              {
 334                  return $cache->sql_fetchfield($safe_query_id, $field);
 335              }
 336  
 337              $row = $this->sql_fetchrow($query_id);
 338              return (isset($row[$field])) ? $row[$field] : false;
 339          }
 340  
 341          return false;
 342      }
 343  
 344      /**
 345      * {@inheritDoc}
 346      */
 347  	function sql_nextid()
 348      {
 349          $query_id = $this->query_result;
 350  
 351          if ($query_id !== false && $this->last_query_text != '')
 352          {
 353              if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
 354              {
 355                  $query = "SELECT currval('" . $tablename[1] . "_seq') AS last_value";
 356                  $temp_q_id = @pg_query($this->db_connect_id, $query);
 357  
 358                  if (!$temp_q_id)
 359                  {
 360                      return false;
 361                  }
 362  
 363                  $temp_result = pg_fetch_assoc($temp_q_id, null);
 364                  pg_free_result($query_id);
 365  
 366                  return ($temp_result) ? $temp_result['last_value'] : false;
 367              }
 368          }
 369  
 370          return false;
 371      }
 372  
 373      /**
 374      * {@inheritDoc}
 375      */
 376  	function sql_freeresult($query_id = false)
 377      {
 378          global $cache;
 379  
 380          if ($query_id === false)
 381          {
 382              $query_id = $this->query_result;
 383          }
 384  
 385          $safe_query_id = $this->clean_query_id($query_id);
 386          if ($cache && !is_object($query_id) && $cache->sql_exists($safe_query_id))
 387          {
 388              return $cache->sql_freeresult($safe_query_id);
 389          }
 390  
 391          if (isset($this->open_queries[$safe_query_id]))
 392          {
 393              unset($this->open_queries[$safe_query_id]);
 394              return pg_free_result($query_id);
 395          }
 396  
 397          return false;
 398      }
 399  
 400      /**
 401      * {@inheritDoc}
 402      */
 403  	function sql_escape($msg)
 404      {
 405          return @pg_escape_string($msg);
 406      }
 407  
 408      /**
 409      * Build LIKE expression
 410      * @access private
 411      */
 412  	function _sql_like_expression($expression)
 413      {
 414          return $expression;
 415      }
 416  
 417      /**
 418      * Build NOT LIKE expression
 419      * @access private
 420      */
 421  	function _sql_not_like_expression($expression)
 422      {
 423          return $expression;
 424      }
 425  
 426      /**
 427      * {@inheritDoc}
 428      */
 429  	function cast_expr_to_bigint($expression)
 430      {
 431          return 'CAST(' . $expression . ' as DECIMAL(255, 0))';
 432      }
 433  
 434      /**
 435      * {@inheritDoc}
 436      */
 437  	function cast_expr_to_string($expression)
 438      {
 439          return 'CAST(' . $expression . ' as VARCHAR(255))';
 440      }
 441  
 442      /**
 443      * return sql error array
 444      * @access private
 445      */
 446  	function _sql_error()
 447      {
 448          // pg_last_error only works when there is an established connection.
 449          // Connection errors have to be tracked by us manually.
 450          if ($this->db_connect_id)
 451          {
 452              $message = @pg_last_error($this->db_connect_id);
 453          }
 454          else
 455          {
 456              $message = $this->connect_error;
 457          }
 458  
 459          return array(
 460              'message'    => $message,
 461              'code'        => ''
 462          );
 463      }
 464  
 465      /**
 466      * Close sql connection
 467      * @access private
 468      */
 469  	function _sql_close()
 470      {
 471          // Released resources are already closed, return true in this case
 472          if (!is_resource($this->db_connect_id))
 473          {
 474              return true;
 475          }
 476          return @pg_close($this->db_connect_id);
 477      }
 478  
 479      /**
 480      * Build db-specific report
 481      * @access private
 482      */
 483  	function _sql_report($mode, $query = '')
 484      {
 485          switch ($mode)
 486          {
 487              case 'start':
 488  
 489                  $explain_query = $query;
 490                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 491                  {
 492                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 493                  }
 494                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 495                  {
 496                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 497                  }
 498  
 499                  if (preg_match('/^SELECT/', $explain_query))
 500                  {
 501                      $html_table = false;
 502  
 503                      if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
 504                      {
 505                          while ($row = pg_fetch_assoc($result, null))
 506                          {
 507                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 508                          }
 509                          pg_free_result($result);
 510                      }
 511  
 512                      if ($html_table)
 513                      {
 514                          $this->html_hold .= '</table>';
 515                      }
 516                  }
 517  
 518              break;
 519  
 520              case 'fromcache':
 521                  $endtime = explode(' ', microtime());
 522                  $endtime = $endtime[0] + $endtime[1];
 523  
 524                  $result = @pg_query($this->db_connect_id, $query);
 525                  if ($result)
 526                  {
 527                      while ($void = pg_fetch_assoc($result, null))
 528                      {
 529                          // Take the time spent on parsing rows into account
 530                      }
 531                      pg_free_result($result);
 532                  }
 533  
 534                  $splittime = explode(' ', microtime());
 535                  $splittime = $splittime[0] + $splittime[1];
 536  
 537                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 538  
 539              break;
 540          }
 541      }
 542  
 543      /**
 544      * {@inheritDoc}
 545      */
 546  	function sql_quote($msg)
 547      {
 548          return '"' . $msg . '"';
 549      }
 550  
 551      /**
 552       * Ensure query ID can be used by cache
 553       *
 554       * @param resource|int|string $query_id Mixed type query id
 555       *
 556       * @return int|string Query id in string or integer format
 557       */
 558  	private function clean_query_id($query_id)
 559      {
 560          // As of PHP 8.1 PgSQL functions accept/return \PgSQL\* objects instead of "pgsql *" resources
 561          // Attempting to cast object to int will throw error, hence correctly handle all cases
 562          if (is_resource($query_id))
 563          {
 564              return function_exists('get_resource_id') ? get_resource_id($query_id) : (int) $query_id;
 565          }
 566          else
 567          {
 568              return is_object($query_id) ? spl_object_id($query_id) : $query_id;
 569          }
 570      }
 571  }