2
/* vim: set expandtab tabstop=4 shiftwidth=4 foldmethod=marker: */
3
// +----------------------------------------------------------------------+
5
// +----------------------------------------------------------------------+
6
// | Copyright (c) 1997-2004 The PHP Group |
7
// +----------------------------------------------------------------------+
8
// | This source file is subject to version 2.02 of the PHP license, |
9
// | that is bundled with this package in the file LICENSE, and is |
10
// | available at through the world-wide-web at |
11
// | http://www.php.net/license/2_02.txt. |
12
// | If you did not receive a copy of the PHP license and are unable to |
13
// | obtain it through the world-wide-web, please send a note to |
14
// | license@php.net so we can mail you a copy immediately. |
15
// +----------------------------------------------------------------------+
16
// | Authors: Rui Hirokawa <hirokawa@php.net> |
17
// | Stig Bakken <ssb@php.net> |
18
// | Maintainer: Daniel Convissor <danielc@php.net> |
19
// +----------------------------------------------------------------------+
21
// $Id: pgsql.php 6184 2008-08-22 10:33:41Z vargenau $
23
require_once 'DB/common.php';
26
* Database independent query interface definition for PHP's PostgreSQL
30
* @version $Id: pgsql.php 6184 2008-08-22 10:33:41Z vargenau $
32
* @author Rui Hirokawa <hirokawa@php.net>
33
* @author Stig Bakken <ssb@php.net>
35
class DB_pgsql extends DB_common
40
var $phptype, $dbsyntax;
41
var $prepare_tokens = array();
42
var $prepare_types = array();
43
var $transaction_opcount = 0;
46
var $num_rows = array();
48
var $autocommit = true;
49
var $fetchmode = DB_FETCHMODE_ORDERED;
57
$this->phptype = 'pgsql';
58
$this->dbsyntax = 'pgsql';
59
$this->features = array(
62
'transactions' => true,
65
$this->errorcode_map = array(
73
* Connect to a database and log in as the specified user.
75
* @param $dsn the data source name (see DB::parseDSN for syntax)
76
* @param $persistent (optional) whether the connection should
79
* @return int DB_OK on success, a DB error code on failure.
81
function connect($dsninfo, $persistent = false)
83
if (!DB::assertExtension('pgsql')) {
84
return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
87
$this->dsn = $dsninfo;
88
$protocol = $dsninfo['protocol'] ? $dsninfo['protocol'] : 'tcp';
91
if ($protocol == 'tcp') {
92
if ($dsninfo['hostspec']) {
93
$connstr .= 'host=' . $dsninfo['hostspec'];
95
if ($dsninfo['port']) {
96
$connstr .= ' port=' . $dsninfo['port'];
98
} elseif ($protocol == 'unix') {
99
// Allow for pg socket in non-standard locations.
100
if ($dsninfo['socket']) {
101
$connstr .= 'host=' . $dsninfo['socket'];
105
if ($dsninfo['database']) {
106
$connstr .= ' dbname=\'' . addslashes($dsninfo['database']) . '\'';
108
if ($dsninfo['username']) {
109
$connstr .= ' user=\'' . addslashes($dsninfo['username']) . '\'';
111
if ($dsninfo['password']) {
112
$connstr .= ' password=\'' . addslashes($dsninfo['password']) . '\'';
114
if (isset($dsninfo['options'])) {
115
$connstr .= ' options=' . $dsninfo['options'];
117
if (isset($dsninfo['tty'])) {
118
$connstr .= ' tty=' . $dsninfo['tty'];
121
$connect_function = $persistent ? 'pg_pconnect' : 'pg_connect';
124
$conn = $connect_function($connstr);
125
$error = ob_get_contents();
127
if ($conn == false) {
128
return $this->raiseError(DB_ERROR_CONNECT_FAILED, null,
129
null, null, strip_tags($error));
131
$this->connection = $conn;
139
* Log out and disconnect from the database.
141
* @return bool true on success, false if not connected.
143
function disconnect()
145
$ret = @pg_close($this->connection);
146
$this->connection = null;
154
* Send a query to PostgreSQL and return the results as a
155
* PostgreSQL resource identifier.
157
* @param $query the SQL query
159
* @return int returns a valid PostgreSQL result for successful SELECT
160
* queries, DB_OK for other successful queries. A DB error code
161
* is returned on failure.
163
function simpleQuery($query)
165
$ismanip = DB::isManip($query);
166
$this->last_query = $query;
167
$query = $this->modifyQuery($query);
168
if (!$this->autocommit && $ismanip) {
169
if ($this->transaction_opcount == 0) {
170
$result = @pg_exec($this->connection, 'begin;');
172
return $this->pgsqlRaiseError();
175
$this->transaction_opcount++;
177
$result = @pg_exec($this->connection, $query);
179
return $this->pgsqlRaiseError();
181
// Determine which queries that should return data, and which
182
// should return an error code only.
184
$this->affected = @pg_cmdtuples($result);
186
} elseif (preg_match('/^\s*\(?\s*(SELECT(?!\s+INTO)|EXPLAIN|SHOW)\s/si', $query)) {
187
/* PostgreSQL commands:
188
ABORT, ALTER, BEGIN, CLOSE, CLUSTER, COMMIT, COPY,
189
CREATE, DECLARE, DELETE, DROP TABLE, EXPLAIN, FETCH,
190
GRANT, INSERT, LISTEN, LOAD, LOCK, MOVE, NOTIFY, RESET,
191
REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SHOW,
192
UNLISTEN, UPDATE, VACUUM
194
$this->row[(int)$result] = 0; // reset the row counter.
195
$numrows = $this->numrows($result);
196
if (is_object($numrows)) {
199
$this->num_rows[(int)$result] = $numrows;
212
* Move the internal pgsql result pointer to the next available result
214
* @param a valid fbsql result resource
218
* @return true if a result is available otherwise return false
220
function nextResult($result)
229
* Determine PEAR::DB error code from the database's text error message.
231
* @param string $errormsg error message returned from the database
232
* @return integer an error number from a DB error constant
234
function errorCode($errormsg)
236
static $error_regexps;
237
if (!isset($error_regexps)) {
238
$error_regexps = array(
239
'/(([Rr]elation|[Ss]equence|[Tt]able)( [\"\'].*[\"\'])? does not exist|[Cc]lass ".+" not found)$/' => DB_ERROR_NOSUCHTABLE,
240
'/[Cc]olumn [\"\'].*[\"\'] does not exist/' => DB_ERROR_NOSUCHFIELD,
241
'/[Rr]elation [\"\'].*[\"\'] already exists|[Cc]annot insert a duplicate key into (a )?unique index.*/' => DB_ERROR_ALREADY_EXISTS,
242
'/(divide|division) by zero$/' => DB_ERROR_DIVZERO,
243
'/pg_atoi: error in .*: can\'t parse /' => DB_ERROR_INVALID_NUMBER,
244
'/invalid input syntax for integer/' => DB_ERROR_INVALID_NUMBER,
245
'/ttribute [\"\'].*[\"\'] not found$|[Rr]elation [\"\'].*[\"\'] does not have attribute [\"\'].*[\"\']/' => DB_ERROR_NOSUCHFIELD,
246
'/parser: parse error at or near \"/' => DB_ERROR_SYNTAX,
247
'/syntax error at/' => DB_ERROR_SYNTAX,
248
'/violates not-null constraint/' => DB_ERROR_CONSTRAINT_NOT_NULL,
249
'/violates [\w ]+ constraint/' => DB_ERROR_CONSTRAINT,
250
'/referential integrity violation/' => DB_ERROR_CONSTRAINT
253
foreach ($error_regexps as $regexp => $code) {
254
if (preg_match($regexp, $errormsg)) {
258
// Fall back to DB_ERROR if there was no mapping.
266
* Fetch a row and insert the data into an existing array.
268
* Formating of the array and the data therein are configurable.
269
* See DB_result::fetchInto() for more information.
271
* @param resource $result query result identifier
272
* @param array $arr (reference) array where data from the row
274
* @param int $fetchmode how the resulting array should be indexed
275
* @param int $rownum the row number to fetch
277
* @return mixed DB_OK on success, null when end of result set is
278
* reached or on failure
280
* @see DB_result::fetchInto()
283
function fetchInto($result, &$arr, $fetchmode, $rownum=null)
285
$rownum = ($rownum !== null) ? $rownum : $this->row[$result];
286
if ($rownum >= $this->num_rows[$result]) {
289
if ($fetchmode & DB_FETCHMODE_ASSOC) {
290
$arr = @pg_fetch_array($result, $rownum, PGSQL_ASSOC);
291
if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
292
$arr = array_change_key_case($arr, CASE_LOWER);
295
$arr = @pg_fetch_row($result, $rownum);
298
$err = pg_errormessage($this->connection);
302
return $this->pgsqlRaiseError();
304
if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
305
$this->_rtrimArrayValues($arr);
307
if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
308
$this->_convertNullArrayValuesToEmpty($arr);
310
$this->row[$result] = ++$rownum;
318
* Free the internal resources associated with $result.
320
* @param $result int PostgreSQL result identifier
322
* @return bool true on success, false if $result is invalid
324
function freeResult($result)
326
if (is_resource($result)) {
327
unset($this->row[(int)$result]);
328
unset($this->num_rows[(int)$result]);
330
return @pg_freeresult($result);
339
* @deprecated Deprecated in release 1.6.0
342
function quote($str) {
343
return $this->quoteSmart($str);
350
* Format input so it can be safely used in a query
352
* @param mixed $in data to be quoted
354
* @return mixed Submitted variable's type = returned value:
355
* + null = the string <samp>NULL</samp>
356
* + boolean = string <samp>TRUE</samp> or <samp>FALSE</samp>
357
* + integer or double = the unquoted number
358
* + other (including strings and numeric strings) =
359
* the data escaped according to MySQL's settings
360
* then encapsulated between single quotes
364
function quoteSmart($in)
366
if (is_int($in) || is_double($in)) {
368
} elseif (is_bool($in)) {
369
return $in ? 'TRUE' : 'FALSE';
370
} elseif (is_null($in)) {
373
return "'" . $this->escapeSimple($in) . "'";
378
// {{{ escapeSimple()
381
* Escape a string according to the current DBMS's standards
383
* PostgreSQL treats a backslash as an escape character, so they are
386
* Not using pg_escape_string() yet because it requires PostgreSQL
387
* to be at version 7.2 or greater.
389
* @param string $str the string to be escaped
391
* @return string the escaped string
395
function escapeSimple($str) {
396
return str_replace("'", "''", str_replace('\\', '\\\\', $str));
403
* Get the number of columns in a result set.
405
* @param $result resource PostgreSQL result identifier
407
* @return int the number of columns per row in $result
409
function numCols($result)
411
$cols = @pg_numfields($result);
413
return $this->pgsqlRaiseError();
422
* Get the number of rows in a result set.
424
* @param $result resource PostgreSQL result identifier
426
* @return int the number of rows in $result
428
function numRows($result)
430
$rows = @pg_numrows($result);
431
if ($rows === null) {
432
return $this->pgsqlRaiseError();
441
* Get the native error code of the last error (if any) that
442
* occured on the current connection.
444
* @return int native PostgreSQL error code
446
function errorNative()
448
return pg_errormessage($this->connection);
455
* Enable/disable automatic commits
457
function autoCommit($onoff = false)
459
// XXX if $this->transaction_opcount > 0, we should probably
460
// issue a warning here.
461
$this->autocommit = $onoff ? true : false;
469
* Commit the current transaction.
473
if ($this->transaction_opcount > 0) {
474
// (disabled) hack to shut up error messages from libpq.a
475
//@fclose(@fopen("php://stderr", "w"));
476
$result = @pg_exec($this->connection, 'end;');
477
$this->transaction_opcount = 0;
479
return $this->pgsqlRaiseError();
489
* Roll back (undo) the current transaction.
493
if ($this->transaction_opcount > 0) {
494
$result = @pg_exec($this->connection, 'abort;');
495
$this->transaction_opcount = 0;
497
return $this->pgsqlRaiseError();
504
// {{{ affectedRows()
507
* Gets the number of rows affected by the last query.
508
* if the last query was a select, returns 0.
510
* @return int number of rows affected by the last query or DB_ERROR
512
function affectedRows()
514
return $this->affected;
521
* Returns the next free id in a sequence
523
* @param string $seq_name name of the sequence
524
* @param boolean $ondemand when true, the seqence is automatically
525
* created if it does not exist
527
* @return int the next id number in the sequence. DB_Error if problem.
530
* @see DB_common::nextID()
533
function nextId($seq_name, $ondemand = true)
535
$seqname = $this->getSequenceName($seq_name);
538
$this->pushErrorHandling(PEAR_ERROR_RETURN);
539
$result =& $this->query("SELECT NEXTVAL('${seqname}')");
540
$this->popErrorHandling();
541
if ($ondemand && DB::isError($result) &&
542
$result->getCode() == DB_ERROR_NOSUCHTABLE) {
544
$this->pushErrorHandling(PEAR_ERROR_RETURN);
545
$result = $this->createSequence($seq_name);
546
$this->popErrorHandling();
547
if (DB::isError($result)) {
548
return $this->raiseError($result);
554
if (DB::isError($result)) {
555
return $this->raiseError($result);
557
$arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
563
// {{{ createSequence()
566
* Create the sequence
568
* @param string $seq_name the name of the sequence
569
* @return mixed DB_OK on success or DB error on error
572
function createSequence($seq_name)
574
$seqname = $this->getSequenceName($seq_name);
575
$result = $this->query("CREATE SEQUENCE ${seqname}");
580
// {{{ dropSequence()
585
* @param string $seq_name the name of the sequence
586
* @return mixed DB_OK on success or DB error on error
589
function dropSequence($seq_name)
591
$seqname = $this->getSequenceName($seq_name);
592
return $this->query("DROP SEQUENCE ${seqname}");
596
// {{{ modifyLimitQuery()
598
function modifyLimitQuery($query, $from, $count)
600
$query = $query . " LIMIT $count OFFSET $from";
605
// {{{ pgsqlRaiseError()
608
* Gather information about an error, then use that info to create a
609
* DB error object and finally return that object.
611
* @param integer $errno PEAR error number (usually a DB constant) if
612
* manually raising an error
613
* @return object DB error object
616
* @see DB_common::raiseError()
618
function pgsqlRaiseError($errno = null)
620
$native = $this->errorNative();
621
if ($errno === null) {
622
$err = $this->errorCode($native);
626
return $this->raiseError($err, null, null, null, $native);
630
// {{{ _pgFieldFlags()
635
* @param int $resource PostgreSQL result identifier
636
* @param int $num_field the field number
638
* @return string The flags of the field ("not_null", "default_value",
639
* "primary_key", "unique_key" and "multiple_key"
640
* are supported). The default value is passed
641
* through rawurlencode() in case there are spaces in it.
644
function _pgFieldFlags($resource, $num_field, $table_name)
646
$field_name = @pg_fieldname($resource, $num_field);
648
$result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
649
FROM pg_attribute f, pg_class tab, pg_type typ
650
WHERE tab.relname = typ.typname
651
AND typ.typrelid = f.attrelid
652
AND f.attname = '$field_name'
653
AND tab.relname = '$table_name'");
654
if (@pg_numrows($result) > 0) {
655
$row = @pg_fetch_row($result, 0);
656
$flags = ($row[0] == 't') ? 'not_null ' : '';
658
if ($row[1] == 't') {
659
$result = @pg_exec($this->connection, "SELECT a.adsrc
660
FROM pg_attribute f, pg_class tab, pg_type typ, pg_attrdef a
661
WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid
662
AND f.attrelid = a.adrelid AND f.attname = '$field_name'
663
AND tab.relname = '$table_name' AND f.attnum = a.adnum");
664
$row = @pg_fetch_row($result, 0);
665
$num = preg_replace("/'(.*)'::\w+/", "\\1", $row[0]);
666
$flags .= 'default_' . rawurlencode($num) . ' ';
671
$result = @pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey
672
FROM pg_attribute f, pg_class tab, pg_type typ, pg_index i
673
WHERE tab.relname = typ.typname
674
AND typ.typrelid = f.attrelid
675
AND f.attrelid = i.indrelid
676
AND f.attname = '$field_name'
677
AND tab.relname = '$table_name'");
678
$count = @pg_numrows($result);
680
for ($i = 0; $i < $count ; $i++) {
681
$row = @pg_fetch_row($result, $i);
682
$keys = explode(' ', $row[2]);
684
if (in_array($num_field + 1, $keys)) {
685
$flags .= ($row[0] == 't' && $row[1] == 'f') ? 'unique_key ' : '';
686
$flags .= ($row[1] == 't') ? 'primary_key ' : '';
687
if (count($keys) > 1)
688
$flags .= 'multiple_key ';
699
* Returns information about a table or a result set.
701
* NOTE: only supports 'table' and 'flags' if <var>$result</var>
704
* @param object|string $result DB_result object from a query or a
705
* string containing the name of a table
706
* @param int $mode a valid tableInfo mode
707
* @return array an associative array with the information requested
708
* or an error object if something is wrong
711
* @see DB_common::tableInfo()
713
function tableInfo($result, $mode = null)
715
if (isset($result->result)) {
717
* Probably received a result object.
718
* Extract the result resource identifier.
720
$id = $result->result;
722
} elseif (is_string($result)) {
724
* Probably received a table name.
725
* Create a result resource identifier.
727
$id = @pg_exec($this->connection, "SELECT * FROM $result LIMIT 0");
731
* Probably received a result resource identifier.
733
* Deprecated. Here for compatibility only.
739
if (!is_resource($id)) {
740
return $this->pgsqlRaiseError(DB_ERROR_NEED_MORE_DATA);
743
if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
744
$case_func = 'strtolower';
746
$case_func = 'strval';
749
$count = @pg_numfields($id);
751
// made this IF due to performance (one if is faster than $count if's)
754
for ($i=0; $i<$count; $i++) {
755
$res[$i]['table'] = $got_string ? $case_func($result) : '';
756
$res[$i]['name'] = $case_func(@pg_fieldname($id, $i));
757
$res[$i]['type'] = @pg_fieldtype($id, $i);
758
$res[$i]['len'] = @pg_fieldsize($id, $i);
759
$res[$i]['flags'] = $got_string ? $this->_pgFieldflags($id, $i, $result) : '';
763
$res['num_fields']= $count;
765
for ($i=0; $i<$count; $i++) {
766
$res[$i]['table'] = $got_string ? $case_func($result) : '';
767
$res[$i]['name'] = $case_func(@pg_fieldname($id, $i));
768
$res[$i]['type'] = @pg_fieldtype($id, $i);
769
$res[$i]['len'] = @pg_fieldsize($id, $i);
770
$res[$i]['flags'] = $got_string ? $this->_pgFieldFlags($id, $i, $result) : '';
772
if ($mode & DB_TABLEINFO_ORDER) {
773
$res['order'][$res[$i]['name']] = $i;
775
if ($mode & DB_TABLEINFO_ORDERTABLE) {
776
$res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
781
// free the result only if we were called on a table
789
// {{{ getTablesQuery()
792
* Returns the query needed to get some backend info
793
* @param string $type What kind of info you want to retrieve
794
* @return string The SQL query string
796
function getSpecialQuery($type)
800
return "SELECT c.relname as \"Name\"
801
FROM pg_class c, pg_user u
802
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
803
AND not exists (select 1 from pg_views where viewname = c.relname)
804
AND c.relname !~ '^pg_'
806
SELECT c.relname as \"Name\"
808
WHERE c.relkind = 'r'
809
AND not exists (select 1 from pg_views where viewname = c.relname)
810
AND not exists (select 1 from pg_user where usesysid = c.relowner)
811
AND c.relname !~ '^pg_'";
813
// Table cols: viewname | viewowner | definition
814
return 'SELECT viewname FROM pg_views';
816
// cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
817
return 'SELECT usename FROM pg_user';
819
return 'SELECT datname FROM pg_database';
821
return 'SELECT proname FROM pg_proc';