[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              $row = @pg_fetch_assoc($query_id, null);
 127              @pg_free_result($query_id);
 128  
 129              $this->sql_server_version = (!empty($row['version'])) ? trim(substr($row['version'], 10)) : 0;
 130  
 131              if (!empty($cache) && $use_cache)
 132              {
 133                  $cache->put('pgsql_version', $this->sql_server_version);
 134              }
 135          }
 136  
 137          return ($raw) ? $this->sql_server_version : 'PostgreSQL ' . $this->sql_server_version;
 138      }
 139  
 140      /**
 141      * SQL Transaction
 142      * @access private
 143      */
 144  	function _sql_transaction($status = 'begin')
 145      {
 146          switch ($status)
 147          {
 148              case 'begin':
 149                  return @pg_query($this->db_connect_id, 'BEGIN');
 150              break;
 151  
 152              case 'commit':
 153                  return @pg_query($this->db_connect_id, 'COMMIT');
 154              break;
 155  
 156              case 'rollback':
 157                  return @pg_query($this->db_connect_id, 'ROLLBACK');
 158              break;
 159          }
 160  
 161          return true;
 162      }
 163  
 164      /**
 165      * {@inheritDoc}
 166      */
 167  	function sql_query($query = '', $cache_ttl = 0)
 168      {
 169          if ($query != '')
 170          {
 171              global $cache;
 172  
 173              // EXPLAIN only in extra debug mode
 174              if (defined('DEBUG'))
 175              {
 176                  $this->sql_report('start', $query);
 177              }
 178              else if (defined('PHPBB_DISPLAY_LOAD_TIME'))
 179              {
 180                  $this->curtime = microtime(true);
 181              }
 182  
 183              $this->last_query_text = $query;
 184              $this->query_result = ($cache && $cache_ttl) ? $cache->sql_load($query) : false;
 185              $this->sql_add_num_queries($this->query_result);
 186  
 187              if ($this->query_result === false)
 188              {
 189                  if (($this->query_result = @pg_query($this->db_connect_id, $query)) === false)
 190                  {
 191                      $this->sql_error($query);
 192                  }
 193  
 194                  if (defined('DEBUG'))
 195                  {
 196                      $this->sql_report('stop', $query);
 197                  }
 198                  else if (defined('PHPBB_DISPLAY_LOAD_TIME'))
 199                  {
 200                      $this->sql_time += microtime(true) - $this->curtime;
 201                  }
 202  
 203                  if ($cache && $cache_ttl)
 204                  {
 205                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 206                      $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl);
 207                  }
 208                  else if (strpos($query, 'SELECT') === 0 && $this->query_result)
 209                  {
 210                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 211                  }
 212              }
 213              else if (defined('DEBUG'))
 214              {
 215                  $this->sql_report('fromcache', $query);
 216              }
 217          }
 218          else
 219          {
 220              return false;
 221          }
 222  
 223          return $this->query_result;
 224      }
 225  
 226      /**
 227      * Build db-specific query data
 228      * @access private
 229      */
 230  	function _sql_custom_build($stage, $data)
 231      {
 232          return $data;
 233      }
 234  
 235      /**
 236      * Build LIMIT query
 237      */
 238  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 239      {
 240          $this->query_result = false;
 241  
 242          // if $total is set to 0 we do not want to limit the number of rows
 243          if ($total == 0)
 244          {
 245              $total = 'ALL';
 246          }
 247  
 248          $query .= "\n LIMIT $total OFFSET $offset";
 249  
 250          return $this->sql_query($query, $cache_ttl);
 251      }
 252  
 253      /**
 254      * {@inheritDoc}
 255      */
 256  	function sql_affectedrows()
 257      {
 258          return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
 259      }
 260  
 261      /**
 262      * {@inheritDoc}
 263      */
 264  	function sql_fetchrow($query_id = false)
 265      {
 266          global $cache;
 267  
 268          if ($query_id === false)
 269          {
 270              $query_id = $this->query_result;
 271          }
 272  
 273          if ($cache && $cache->sql_exists($query_id))
 274          {
 275              return $cache->sql_fetchrow($query_id);
 276          }
 277  
 278          return ($query_id !== false) ? @pg_fetch_assoc($query_id, null) : false;
 279      }
 280  
 281      /**
 282      * {@inheritDoc}
 283      */
 284  	function sql_rowseek($rownum, &$query_id)
 285      {
 286          global $cache;
 287  
 288          if ($query_id === false)
 289          {
 290              $query_id = $this->query_result;
 291          }
 292  
 293          if ($cache && $cache->sql_exists($query_id))
 294          {
 295              return $cache->sql_rowseek($rownum, $query_id);
 296          }
 297  
 298          return ($query_id !== false) ? @pg_result_seek($query_id, $rownum) : false;
 299      }
 300  
 301      /**
 302      * {@inheritDoc}
 303      */
 304  	function sql_nextid()
 305      {
 306          $query_id = $this->query_result;
 307  
 308          if ($query_id !== false && $this->last_query_text != '')
 309          {
 310              if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
 311              {
 312                  $query = "SELECT currval('" . $tablename[1] . "_seq') AS last_value";
 313                  $temp_q_id = @pg_query($this->db_connect_id, $query);
 314  
 315                  if (!$temp_q_id)
 316                  {
 317                      return false;
 318                  }
 319  
 320                  $temp_result = @pg_fetch_assoc($temp_q_id, null);
 321                  @pg_free_result($query_id);
 322  
 323                  return ($temp_result) ? $temp_result['last_value'] : false;
 324              }
 325          }
 326  
 327          return false;
 328      }
 329  
 330      /**
 331      * {@inheritDoc}
 332      */
 333  	function sql_freeresult($query_id = false)
 334      {
 335          global $cache;
 336  
 337          if ($query_id === false)
 338          {
 339              $query_id = $this->query_result;
 340          }
 341  
 342          if ($cache && !is_object($query_id) && $cache->sql_exists($query_id))
 343          {
 344              return $cache->sql_freeresult($query_id);
 345          }
 346  
 347          if (isset($this->open_queries[(int) $query_id]))
 348          {
 349              unset($this->open_queries[(int) $query_id]);
 350              return @pg_free_result($query_id);
 351          }
 352  
 353          return false;
 354      }
 355  
 356      /**
 357      * {@inheritDoc}
 358      */
 359  	function sql_escape($msg)
 360      {
 361          return @pg_escape_string($msg);
 362      }
 363  
 364      /**
 365      * Build LIKE expression
 366      * @access private
 367      */
 368  	function _sql_like_expression($expression)
 369      {
 370          return $expression;
 371      }
 372  
 373      /**
 374      * Build NOT LIKE expression
 375      * @access private
 376      */
 377  	function _sql_not_like_expression($expression)
 378      {
 379          return $expression;
 380      }
 381  
 382      /**
 383      * {@inheritDoc}
 384      */
 385  	function cast_expr_to_bigint($expression)
 386      {
 387          return 'CAST(' . $expression . ' as DECIMAL(255, 0))';
 388      }
 389  
 390      /**
 391      * {@inheritDoc}
 392      */
 393  	function cast_expr_to_string($expression)
 394      {
 395          return 'CAST(' . $expression . ' as VARCHAR(255))';
 396      }
 397  
 398      /**
 399      * return sql error array
 400      * @access private
 401      */
 402  	function _sql_error()
 403      {
 404          // pg_last_error only works when there is an established connection.
 405          // Connection errors have to be tracked by us manually.
 406          if ($this->db_connect_id)
 407          {
 408              $message = @pg_last_error($this->db_connect_id);
 409          }
 410          else
 411          {
 412              $message = $this->connect_error;
 413          }
 414  
 415          return array(
 416              'message'    => $message,
 417              'code'        => ''
 418          );
 419      }
 420  
 421      /**
 422      * Close sql connection
 423      * @access private
 424      */
 425  	function _sql_close()
 426      {
 427          return @pg_close($this->db_connect_id);
 428      }
 429  
 430      /**
 431      * Build db-specific report
 432      * @access private
 433      */
 434  	function _sql_report($mode, $query = '')
 435      {
 436          switch ($mode)
 437          {
 438              case 'start':
 439  
 440                  $explain_query = $query;
 441                  if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 442                  {
 443                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 444                  }
 445                  else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 446                  {
 447                      $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 448                  }
 449  
 450                  if (preg_match('/^SELECT/', $explain_query))
 451                  {
 452                      $html_table = false;
 453  
 454                      if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
 455                      {
 456                          while ($row = @pg_fetch_assoc($result, null))
 457                          {
 458                              $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 459                          }
 460                      }
 461                      @pg_free_result($result);
 462  
 463                      if ($html_table)
 464                      {
 465                          $this->html_hold .= '</table>';
 466                      }
 467                  }
 468  
 469              break;
 470  
 471              case 'fromcache':
 472                  $endtime = explode(' ', microtime());
 473                  $endtime = $endtime[0] + $endtime[1];
 474  
 475                  $result = @pg_query($this->db_connect_id, $query);
 476                  while ($void = @pg_fetch_assoc($result, null))
 477                  {
 478                      // Take the time spent on parsing rows into account
 479                  }
 480                  @pg_free_result($result);
 481  
 482                  $splittime = explode(' ', microtime());
 483                  $splittime = $splittime[0] + $splittime[1];
 484  
 485                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 486  
 487              break;
 488          }
 489      }
 490  }