5
* @version $Id: postgres.php,v 1.48 2007/10/05 14:36:32 acydburn Exp $
6
* @copyright (c) 2005 phpBB Group
7
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
14
if (!defined('IN_PHPBB'))
19
include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
22
* PostgreSQL Database Abstraction Layer
23
* Minimum Requirement is Version 7.3+
26
class dbal_postgres extends dbal
28
var $last_query_text = '';
34
function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
40
$connect_string .= "user=$sqluser ";
45
$connect_string .= "password=$sqlpassword ";
50
if (strpos($sqlserver, ':') !== false)
52
list($sqlserver, $port) = explode(':', $sqlserver);
55
if ($sqlserver !== 'localhost')
57
$connect_string .= "host=$sqlserver ";
62
$connect_string .= "port=$port ";
70
$this->dbname = $database;
71
if (strpos($database, '.') !== false)
73
list($database, $schema) = explode('.', $database);
75
$connect_string .= "dbname=$database";
78
$this->persistency = $persistency;
80
$this->db_connect_id = ($this->persistency) ? @pg_pconnect($connect_string, $new_link) : @pg_connect($connect_string, $new_link);
82
if ($this->db_connect_id)
84
// determine what version of PostgreSQL is running, we can be more efficient if they are running 8.2+
85
if (version_compare(PHP_VERSION, '5.0.0', '>='))
87
$this->pgsql_version = @pg_parameter_status($this->db_connect_id, 'server_version');
91
$query_id = @pg_query($this->db_connect_id, 'SELECT VERSION()');
92
$row = @pg_fetch_assoc($query_id, null);
93
@pg_free_result($query_id);
95
if (!empty($row['version']))
97
$this->pgsql_version = substr($row['version'], 10);
101
if (!empty($this->pgsql_version) && $this->pgsql_version[0] >= '8' && $this->pgsql_version[2] >= '2')
103
$this->multi_insert = true;
108
@pg_query($this->db_connect_id, 'SET search_path TO ' . $schema);
110
return $this->db_connect_id;
113
return $this->sql_error('');
117
* Version information about used database
119
function sql_server_info()
121
return 'PostgreSQL ' . $this->pgsql_version;
128
function _sql_transaction($status = 'begin')
133
return @pg_query($this->db_connect_id, 'BEGIN');
137
return @pg_query($this->db_connect_id, 'COMMIT');
141
return @pg_query($this->db_connect_id, 'ROLLBACK');
151
* @param string $query Contains the SQL query which shall be executed
152
* @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
153
* @return mixed When casted to bool the returned value returns true on success and false on failure
157
function sql_query($query = '', $cache_ttl = 0)
163
// EXPLAIN only in extra debug mode
164
if (defined('DEBUG_EXTRA'))
166
$this->sql_report('start', $query);
169
$this->last_query_text = $query;
170
$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
171
$this->sql_add_num_queries($this->query_result);
173
if ($this->query_result === false)
175
if (($this->query_result = @pg_query($this->db_connect_id, $query)) === false)
177
$this->sql_error($query);
180
if (defined('DEBUG_EXTRA'))
182
$this->sql_report('stop', $query);
185
if ($cache_ttl && method_exists($cache, 'sql_save'))
187
$this->open_queries[(int) $this->query_result] = $this->query_result;
188
$cache->sql_save($query, $this->query_result, $cache_ttl);
190
else if (strpos($query, 'SELECT') === 0 && $this->query_result)
192
$this->open_queries[(int) $this->query_result] = $this->query_result;
195
else if (defined('DEBUG_EXTRA'))
197
$this->sql_report('fromcache', $query);
205
return ($this->query_result) ? $this->query_result : false;
209
* Build db-specific query data
212
function _sql_custom_build($stage, $data)
220
function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
222
$this->query_result = false;
224
// if $total is set to 0 we do not want to limit the number of rows
230
$query .= "\n LIMIT $total OFFSET $offset";
232
return $this->sql_query($query, $cache_ttl);
236
* Return number of affected rows
238
function sql_affectedrows()
240
return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
246
function sql_fetchrow($query_id = false)
250
if ($query_id === false)
252
$query_id = $this->query_result;
255
if (isset($cache->sql_rowset[$query_id]))
257
return $cache->sql_fetchrow($query_id);
260
return ($query_id !== false) ? @pg_fetch_assoc($query_id, null) : false;
264
* Seek to given row number
265
* rownum is zero-based
267
function sql_rowseek($rownum, &$query_id)
271
if ($query_id === false)
273
$query_id = $this->query_result;
276
if (isset($cache->sql_rowset[$query_id]))
278
return $cache->sql_rowseek($rownum, $query_id);
281
return ($query_id !== false) ? @pg_result_seek($query_id, $rownum) : false;
285
* Get last inserted id after insert statement
287
function sql_nextid()
289
$query_id = $this->query_result;
291
if ($query_id !== false && $this->last_query_text != '')
293
if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
295
$query = "SELECT currval('" . $tablename[1] . "_seq') AS last_value";
296
$temp_q_id = @pg_query($this->db_connect_id, $query);
303
$temp_result = @pg_fetch_assoc($temp_q_id, NULL);
304
@pg_free_result($query_id);
306
return ($temp_result) ? $temp_result['last_value'] : false;
316
function sql_freeresult($query_id = false)
320
if ($query_id === false)
322
$query_id = $this->query_result;
325
if (isset($cache->sql_rowset[$query_id]))
327
return $cache->sql_freeresult($query_id);
330
if (isset($this->open_queries[(int) $query_id]))
332
unset($this->open_queries[(int) $query_id]);
333
return @pg_free_result($query_id);
340
* Escape string used in sql query
341
* Note: Do not use for bytea values if we may use them at a later stage
343
function sql_escape($msg)
345
return @pg_escape_string($msg);
349
* Build LIKE expression
352
function _sql_like_expression($expression)
358
* return sql error array
361
function _sql_error()
364
'message' => (!$this->db_connect_id) ? @pg_last_error() : @pg_last_error($this->db_connect_id),
370
* Close sql connection
373
function _sql_close()
375
return @pg_close($this->db_connect_id);
379
* Build db-specific report
382
function _sql_report($mode, $query = '')
388
$explain_query = $query;
389
if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
391
$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
393
else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
395
$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
398
if (preg_match('/^SELECT/', $explain_query))
402
if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
404
while ($row = @pg_fetch_assoc($result, NULL))
406
$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
409
@pg_free_result($result);
413
$this->html_hold .= '</table>';
420
$endtime = explode(' ', microtime());
421
$endtime = $endtime[0] + $endtime[1];
423
$result = @pg_query($this->db_connect_id, $query);
424
while ($void = @pg_fetch_assoc($result, NULL))
426
// Take the time spent on parsing rows into account
428
@pg_free_result($result);
430
$splittime = explode(' ', microtime());
431
$splittime = $splittime[0] + $splittime[1];
433
$this->sql_report('record_fromcache', $query, $endtime, $splittime);
b'\\ No newline at end of file'
added
removed
www/php/phpBB3/includes/db/postgres.php
