phpBB/includes/db/postgres.php

   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 /**
  20 * PostgreSQL Database Abstraction Layer
  21 * Minimum Requirement: 8.2+
  22 * @package dbal
  23 */
  24 class phpbb_dbal_postgres extends phpbb_dbal
  25 {
  26         /**
  27         * @var string Database type. No distinction between versions or used extensions.
  28         */
  29         public $dbms_type = 'postgres';
  30
  31         /**
  32         * @var array Database type map, column layout information
  33         */
  34         public $dbms_type_map = array(
  35                 'INT:'          => 'INT4',
  36                 'BINT'          => 'INT8',
  37                 'UINT'          => 'INT4', // unsigned
  38                 'UINT:'         => 'INT4', // unsigned
  39                 'USINT'         => 'INT2', // unsigned
  40                 'BOOL'          => 'INT2', // unsigned
  41                 'TINT:'         => 'INT2',
  42                 'VCHAR'         => 'varchar(255)',
  43                 'VCHAR:'        => 'varchar(%d)',
  44                 'CHAR:'         => 'char(%d)',
  45                 'XSTEXT'        => 'varchar(1000)',
  46                 'STEXT'         => 'varchar(3000)',
  47                 'TEXT'          => 'varchar(8000)',
  48                 'MTEXT'         => 'TEXT',
  49                 'XSTEXT_UNI'=> 'varchar(100)',
  50                 'STEXT_UNI'     => 'varchar(255)',
  51                 'TEXT_UNI'      => 'varchar(4000)',
  52                 'MTEXT_UNI'     => 'TEXT',
  53                 'TIMESTAMP'     => 'INT4', // unsigned
  54                 'DECIMAL'       => 'decimal(5,2)',
  55                 'DECIMAL:'      => 'decimal(%d,2)',
  56                 'PDECIMAL'      => 'decimal(6,3)',
  57                 'PDECIMAL:'     => 'decimal(%d,3)',
  58                 'VCHAR_UNI'     => 'varchar(255)',
  59                 'VCHAR_UNI:'=> 'varchar(%d)',
  60                 'VARBINARY'     => 'bytea',
  61         );
  62
  63         /**
  64         * @var string PostgreSQL schema (if supplied with $database -> database.schema)
  65         */
  66         public $schema = '';
  67
  68         /**
  69         * Connect to server. See {@link phpbb_dbal::sql_connect() sql_connect()} for details.
  70         */
  71         public function sql_connect($server, $user, $password, $database, $port = false, $persistency = false , $new_link = false)
  72         {
  73                 $this->persistency = $persistency;
  74                 $this->user = $user;
  75                 $this->server = $server;
  76                 $this->dbname = $database;
  77                 $this->port = $port;
  78
  79                 $connect_string = '';
  80
  81                 if ($this->user)
  82                 {
  83                         $connect_string .= 'user=' . $this->user . ' ';
  84                 }
  85
  86                 if ($password)
  87                 {
  88                         $connect_string .= 'password=' . $password . ' ';
  89                 }
  90
  91                 if ($this->server)
  92                 {
  93                         if (strpos($this->server, ':') !== false)
  94                         {
  95                                 list($this->server, $this->port) = explode(':', $this->server, 2);
  96                         }
  97
  98                         if ($this->server !== 'localhost')
  99                         {
 100                                 $connect_string .= 'host=' . $this->server . ' ';
 101                         }
 102
 103                         if ($this->port)
 104                         {
 105                                 $connect_string .= 'port=' . $this->port . ' ';
 106                         }
 107                 }
 108
 109                 $this->schema = '';
 110
 111                 if ($this->dbname)
 112                 {
 113                         if (strpos($this->dbname, '.') !== false)
 114                         {
 115                                 list($this->dbname, $this->schema) = explode('.', $this->dbname, 2);
 116                         }
 117                         $connect_string .= 'dbname=' . $this->dbname;
 118                 }
 119
 120                 $this->db_connect_id = ($this->persistency) ? @pg_pconnect($connect_string, ($new_link) ? PGSQL_CONNECT_FORCE_NEW : false) : @pg_connect($connect_string, ($new_link) ? PGSQL_CONNECT_FORCE_NEW : false);
 121
 122                 if (!$this->db_connect_id)
 123                 {
 124                         return $this->sql_error(htmlspecialchars_decode(phpbb::$last_notice['message']));
 125                 }
 126
 127                 if ($this->schema)
 128                 {
 129                         @pg_query($this->db_connect_id, 'SET search_path TO ' . $this->schema);
 130                 }
 131
 132                 return $this->db_connect_id;
 133         }
 134
 135         /**
 136         * Version information about used database. See {@link phpbb_dbal::sql_server_info() sql_server_info()} for details.
 137         */
 138         public function sql_server_info($raw = false)
 139         {
 140                 if (!phpbb::registered('acm') || ($this->sql_server_version = phpbb::$acm->get('#pgsql_version')) === false)
 141                 {
 142                         $query_id = @pg_query($this->db_connect_id, 'SELECT VERSION() AS version');
 143                         $row = @pg_fetch_assoc($query_id, null);
 144                         @pg_free_result($query_id);
 145
 146                         $this->sql_server_version = (!empty($row['version'])) ? trim(substr($row['version'], 10)) : 0;
 147
 148                         if (phpbb::registered('acm'))
 149                         {
 150                                 phpbb::$acm->put('#pgsql_version', $this->sql_server_version);
 151                         }
 152                 }
 153
 154                 return ($raw) ? $this->sql_server_version : 'PostgreSQL ' . $this->sql_server_version;
 155         }
 156
 157         /**
 158         * DB-specific base query method. See {@link phpbb_dbal::_sql_query() _sql_query()} for details.
 159         */
 160         protected function _sql_query($query)
 161         {
 162                 return @pg_query($this->db_connect_id, $query);
 163         }
 164
 165         /**
 166         * Build LIMIT query and run it. See {@link phpbb_dbal::_sql_query_limit() _sql_query_limit()} for details.
 167         */
 168         protected function _sql_query_limit($query, $total, $offset, $cache_ttl)
 169         {
 170                 // if $total is set to 0 we do not want to limit the number of rows
 171                 if ($total == 0)
 172                 {
 173                         $total = 'ALL';
 174                 }
 175
 176                 $query .= "\n LIMIT $total OFFSET $offset";
 177                 return $this->sql_query($query, $cache_ttl);
 178         }
 179
 180         /**
 181         * Close sql connection. See {@link phpbb_dbal::_sql_close() _sql_close()} for details.
 182         */
 183         protected function _sql_close()
 184         {
 185                 return @pg_close($this->db_connect_id);
 186         }
 187
 188         /**
 189         * SQL Transaction. See {@link phpbb_dbal::_sql_transaction() _sql_transaction()} for details.
 190         */
 191         protected function _sql_transaction($status)
 192         {
 193                 switch ($status)
 194                 {
 195                         case 'begin':
 196                                 return @pg_query($this->db_connect_id, 'BEGIN');
 197                         break;
 198
 199                         case 'commit':
 200                                 return @pg_query($this->db_connect_id, 'COMMIT');
 201                         break;
 202
 203                         case 'rollback':
 204                                 return @pg_query($this->db_connect_id, 'ROLLBACK');
 205                         break;
 206                 }
 207
 208                 return true;
 209         }
 210
 211         /**
 212         * Return number of affected rows. See {@link phpbb_dbal::sql_affectedrows() sql_affectedrows()} for details.
 213         */
 214         public function sql_affectedrows()
 215         {
 216                 return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
 217         }
 218
 219         /**
 220         * Get last inserted id after insert statement. See {@link phpbb_dbal::sql_nextid() sql_nextid()} for details.
 221         */
 222         public function sql_nextid()
 223         {
 224                 if (!$this->db_connect_id)
 225                 {
 226                         return false;
 227                 }
 228
 229                 $query = "SELECT lastval() AS last_value";
 230                 $temp_q_id = @pg_query($this->db_connect_id, $query);
 231
 232                 if (!$temp_q_id)
 233                 {
 234                         return false;
 235                 }
 236
 237                 $temp_result = @pg_fetch_assoc($temp_q_id, NULL);
 238                 @pg_free_result($query_id);
 239
 240                 return ($temp_result) ? $temp_result['last_value'] : false;
 241         }
 242
 243         /**
 244         * Fetch current row. See {@link phpbb_dbal::_sql_fetchrow() _sql_fetchrow()} for details.
 245         */
 246         protected function _sql_fetchrow($query_id)
 247         {
 248                 return @pg_fetch_assoc($query_id, null);
 249         }
 250
 251         /**
 252         * Free query result. See {@link phpbb_dbal::_sql_freeresult() _sql_freeresult()} for details.
 253         */
 254         protected function _sql_freeresult($query_id)
 255         {
 256                 return @pg_free_result($query_id);
 257         }
 258
 259         /**
 260         * Correctly adjust LIKE expression for special characters. See {@link phpbb_dbal::_sql_like_expression() _sql_like_expression()} for details.
 261         */
 262         protected function _sql_like_expression($expression)
 263         {
 264                 return $expression;
 265         }
 266
 267         /**
 268         * Escape string used in sql query. See {@link phpbb_dbal::sql_escape() sql_escape()} for details.
 269         * Note: Do not use for bytea values if we may use them at a later stage
 270         */
 271         public function sql_escape($msg)
 272         {
 273                 return @pg_escape_string($msg);
 274         }
 275
 276         /**
 277         * Expose a DBMS specific function. See {@link phpbb_dbal::sql_function() sql_function()} for details.
 278         */
 279         public function sql_function($type, $col)
 280         {
 281                 switch ($type)
 282                 {
 283                         case 'length_varchar':
 284                         case 'length_text':
 285                                 return 'LENGTH(' . $col . ')';
 286                         break;
 287                 }
 288         }
 289
 290 /*
 291         /**
 292         * Handle data by using prepared statements. See {@link phpbb_dbal::sql_handle_data() sql_handle_data()} for details.
 293         public function sql_handle_data($type, $table, $data, $where = '')
 294         {
 295                 // for now, stmtname is an empty string, it might change to something more unique in the future
 296                 if ($type === 'INSERT')
 297                 {
 298                         $stmt = pg_prepare($this->dbms_type, '', "INSERT INTO $table (". implode(', ', array_keys($data)) . ") VALUES ($" . implode(', $', range(1, sizeof($data))) . ')');
 299                 }
 300                 else
 301                 {
 302                         $query = "UPDATE $table SET ";
 303
 304                         $set = array();
 305                         foreach (array_keys($data) as $key_id => $key)
 306                         {
 307                                 $set[] = $key . ' = $' . $key_id;
 308                         }
 309                         $query .= implode(', ', $set);
 310
 311                         if ($where !== '')
 312                         {
 313                                 $query .= $where;
 314                         }
 315
 316                         $stmt = pg_prepare($this->db_connect_id, '', $query);
 317                 }
 318
 319                 // add the stmtname to the top
 320                 array_unshift($data, '');
 321
 322                 // add the connection resource
 323                 array_unshift($data, $this->db_connect_id);
 324
 325                 call_user_func_array('pg_execute', $data);
 326         }
 327 */
 328
 329         /**
 330         * Build DB-specific query bits. See {@link phpbb_dbal::_sql_custom_build() _sql_custom_build()} for details.
 331         */
 332         protected function _sql_custom_build($stage, $data)
 333         {
 334                 return $data;
 335         }
 336
 337         /**
 338         * return sql error array. See {@link phpbb_dbal::_sql_error() _sql_error()} for details.
 339         */
 340         protected function _sql_error()
 341         {
 342                 return array(
 343                         'message'       => (!$this->db_connect_id) ? @pg_last_error() : @pg_last_error($this->db_connect_id),
 344                         'code'          => ''
 345                 );
 346         }
 347
 348         /**
 349         * Run DB-specific code to build SQL Report to explain queries, show statistics and runtime information. See {@link phpbb_dbal::_sql_report() _sql_report()} for details.
 350         */
 351         protected function _sql_report($mode, $query = '')
 352         {
 353                 switch ($mode)
 354                 {
 355                         case 'start':
 356
 357                                 $explain_query = $query;
 358                                 if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 359                                 {
 360                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 361                                 }
 362                                 else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 363                                 {
 364                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 365                                 }
 366
 367                                 if (preg_match('/^SELECT/', $explain_query))
 368                                 {
 369                                         $html_table = false;
 370
 371                                         if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
 372                                         {
 373                                                 while ($row = @pg_fetch_assoc($result, NULL))
 374                                                 {
 375                                                         $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 376                                                 }
 377                                         }
 378                                         @pg_free_result($result);
 379
 380                                         if ($html_table)
 381                                         {
 382                                                 $this->html_hold .= '</table>';
 383                                         }
 384                                 }
 385
 386                         break;
 387
 388                         case 'fromcache':
 389                                 $endtime = explode(' ', microtime());
 390                                 $endtime = $endtime[0] + $endtime[1];
 391
 392                                 $result = @pg_query($this->db_connect_id, $query);
 393                                 while ($void = @pg_fetch_assoc($result, NULL))
 394                                 {
 395                                         // Take the time spent on parsing rows into account
 396                                 }
 397                                 @pg_free_result($result);
 398
 399                                 $splittime = explode(' ', microtime());
 400                                 $splittime = $splittime[0] + $splittime[1];
 401
 402                                 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 403
 404                         break;
 405                 }
 406         }
 407 }
 408
 409 ?>