[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                  if ($cache && $cache_ttl)
 211                  {
 212                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 213                      $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl);
 214                  }
 215                  else if (strpos($query, 'SELECT') === 0)
 216                  {
 217                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 218                  }
 219              }
 220              else if ($this->debug_sql_explain)
 221              {
 222                  $this->sql_report('fromcache', $query);
 223              }
 224          }
 225          else
 226          {
 227              return false;
 228          }
 229  
 230          return $this->query_result;
 231      }
 232  
 233      /**
 234      * Build db-specific query data
 235      * @access private
 236      */
 237  	function _sql_custom_build($stage, $data)
 238      {
 239          return $data;
 240      }
 241  
 242      /**
 243      * Build LIMIT query
 244      */
 245  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 246      {
 247          $this->query_result = false;
 248  
 249          // if $total is set to 0 we do not want to limit the number of rows
 250          if ($total == 0)
 251          {
 252              $total = 'ALL';
 253          }
 254  
 255          $query .= "\n LIMIT $total OFFSET $offset";
 256  
 257          return $this->sql_query($query, $cache_ttl);
 258      }
 259  
 260      /**
 261      * {@inheritDoc}
 262      */
 263  	function sql_affectedrows()
 264      {
 265          return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
 266      }
 267  
 268      /**
 269      * {@inheritDoc}
 270      */
 271  	function sql_fetchrow($query_id = false)
 272      {
 273          global $cache;
 274  
 275          if ($query_id === false)
 276          {
 277              $query_id = $this->query_result;
 278          }
 279  
 280          if ($cache && $cache->sql_exists($query_id))
 281          {
 282              return $cache->sql_fetchrow($query_id);
 283          }
 284  
 285          return ($query_id) ? pg_fetch_assoc($query_id, null) : false;
 286      }
 287  
 288      /**
 289      * {@inheritDoc}
 290      */
 291  	function sql_rowseek($rownum, &$query_id)
 292      {
 293          global $cache;
 294  
 295          if ($query_id === false)
 296          {
 297              $query_id = $this->query_result;
 298          }
 299  
 300          if ($cache && $cache->sql_exists($query_id))
 301          {
 302              return $cache->sql_rowseek($rownum, $query_id);
 303          }
 304  
 305          return ($query_id) ? @pg_result_seek($query_id, $rownum) : false;
 306      }
 307  
 308      /**
 309      * {@inheritDoc}
 310      */
 311  	function sql_nextid()
 312      {
 313          $query_id = $this->query_result;
 314  
 315          if ($query_id !== false && $this->last_query_text != '')
 316          {
 317              if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
 318              {
 319                  $query = "SELECT currval('" . $tablename[1] . "_seq') AS last_value";
 320                  $temp_q_id = @pg_query($this->db_connect_id, $query);
 321  
 322                  if (!$temp_q_id)
 323                  {
 324                      return false;
 325                  }
 326  
 327                  $temp_result = pg_fetch_assoc($temp_q_id, null);
 328                  pg_free_result($query_id);
 329  
 330                  return ($temp_result) ? $temp_result['last_value'] : false;
 331              }
 332          }
 333  
 334          return false;
 335      }
 336  
 337      /**
 338      * {@inheritDoc}
 339      */
 340  	function sql_freeresult($query_id = false)
 341      {
 342          global $cache;
 343  
 344          if ($query_id === false)
 345          {
 346              $query_id = $this->query_result;
 347          }
 348  
 349          if ($cache && !is_object($query_id) && $cache->sql_exists($query_id))
 350          {
 351              return $cache->sql_freeresult($query_id);
 352          }
 353  
 354          if (isset($this->open_queries[(int) $query_id]))
 355          {
 356              unset($this->open_queries[(int) $query_id]);
 357              return pg_free_result($query_id);
 358          }
 359  
 360          return false;
 361      }
 362  
 363      /**
 364      * {@inheritDoc}
 365      */
 366  	function sql_escape($msg)
 367      {
 368          return @pg_escape_string($msg);
 369      }
 370  
 371      /**
 372      * Build LIKE expression
 373      * @access private
 374      */
 375  	function _sql_like_expression($expression)
 376      {
 377          return $expression;
 378      }
 379  
 380      /**
 381      * Build NOT LIKE expression
 382      * @access private
 383      */
 384  	function _sql_not_like_expression($expression)
 385      {
 386          return $expression;
 387      }
 388  
 389      /**
 390      * {@inheritDoc}
 391      */
 392  	function cast_expr_to_bigint($expression)
 393      {
 394          return 'CAST(' . $expression . ' as DECIMAL(255, 0))';
 395      }
 396  
 397      /**
 398      * {@inheritDoc}
 399      */
 400  	function cast_expr_to_string($expression)
 401      {
 402          return 'CAST(' . $expression . ' as VARCHAR(255))';
 403      }
 404  
 405      /**
 406      * return sql error array
 407      * @access private
 408      */
 409  	function _sql_error()
 410      {
 411          // pg_last_error only works when there is an established connection.
 412          // Connection errors have to be tracked by us manually.
 413          if ($this->db_connect_id)
 414          {
 415              $message = @pg_last_error($this->db_connect_id);
 416          }
 417          else
 418          {
 419              $message = $this->connect_error;
 420          }
 421  
 422          return array(
 423              'message'    => $message,
 424              'code'        => ''
 425          );
 426      }
 427  
 428      /**
 429      * Close sql connection
 430      * @access private
 431      */
 432  	function _sql_close()
 433      {
 434          return @pg_close($this->db_connect_id);
 435      }
 436  
 437      /**
 438      * Build db-specific report
 439      * @access private
 440      */
 441  	function _sql_report($mode, $query = '')
 442      {
 443          switch ($mode)
 444          {
 445              case 'start':
 446  
 447                  $explain_query = $query;
 448                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 449                  {
 450                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 451                  }
 452                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 453                  {
 454                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 455                  }
 456  
 457                  if (preg_match('/^SELECT/', $explain_query))
 458                  {
 459                      $html_table = false;
 460  
 461                      if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
 462                      {
 463                          while ($row = pg_fetch_assoc($result, null))
 464                          {
 465                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 466                          }
 467                          pg_free_result($result);
 468                      }
 469  
 470                      if ($html_table)
 471                      {
 472                          $this->html_hold .= '</table>';
 473                      }
 474                  }
 475  
 476              break;
 477  
 478              case 'fromcache':
 479                  $endtime = explode(' ', microtime());
 480                  $endtime = $endtime[0] + $endtime[1];
 481  
 482                  $result = @pg_query($this->db_connect_id, $query);
 483                  if ($result)
 484                  {
 485                      while ($void = pg_fetch_assoc($result, null))
 486                      {
 487                          // Take the time spent on parsing rows into account
 488                      }
 489                      pg_free_result($result);
 490                  }
 491  
 492                  $splittime = explode(' ', microtime());
 493                  $splittime = $splittime[0] + $splittime[1];
 494  
 495                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 496  
 497              break;
 498          }
 499      }
 500  }