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 // +----------------------------------------------------------------------+
23 require_once 'DB/common.php';
26 * Database independent query interface definition for PHP's PostgreSQL
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 * Escapes a string according to the current DBMS's standards
383 * {@internal PostgreSQL treats a backslash as an escape character,
384 * so they are escaped as well.
386 * @param string $str the string to be escaped
388 * @return string the escaped string
390 * @see DB_common::quoteSmart()
391 * @since Method available since Release 1.6.0
393 function escapeSimple($str)
395 if (function_exists('pg_escape_string')) {
396 /* This fixes an undocumented BC break in PHP 5.2.0 which changed
397 * the prototype of pg_escape_string. I'm not thrilled about having
398 * to sniff the PHP version, quite frankly, but it's the only way
399 * to deal with the problem. Revision 1.331.2.13.2.10 on
400 * php-src/ext/pgsql/pgsql.c (PHP_5_2 branch) is to blame, for the
402 if (version_compare(PHP_VERSION, '5.2.0', '>=')) {
403 return pg_escape_string($this->connection, $str);
405 return pg_escape_string($str);
408 return str_replace("'", "''", str_replace('\\', '\\\\', $str));
416 * Get the number of columns in a result set.
418 * @param $result resource PostgreSQL result identifier
420 * @return int the number of columns per row in $result
422 function numCols($result)
424 $cols = @pg_numfields($result);
426 return $this->pgsqlRaiseError();
435 * Get the number of rows in a result set.
437 * @param $result resource PostgreSQL result identifier
439 * @return int the number of rows in $result
441 function numRows($result)
443 $rows = @pg_numrows($result);
444 if ($rows === null) {
445 return $this->pgsqlRaiseError();
454 * Get the native error code of the last error (if any) that
455 * occured on the current connection.
457 * @return int native PostgreSQL error code
459 function errorNative()
461 return pg_errormessage($this->connection);
468 * Enable/disable automatic commits
470 function autoCommit($onoff = false)
472 // XXX if $this->transaction_opcount > 0, we should probably
473 // issue a warning here.
474 $this->autocommit = $onoff ? true : false;
482 * Commit the current transaction.
486 if ($this->transaction_opcount > 0) {
487 // (disabled) hack to shut up error messages from libpq.a
488 //@fclose(@fopen("php://stderr", "w"));
489 $result = @pg_exec($this->connection, 'end;');
490 $this->transaction_opcount = 0;
492 return $this->pgsqlRaiseError();
502 * Roll back (undo) the current transaction.
506 if ($this->transaction_opcount > 0) {
507 $result = @pg_exec($this->connection, 'abort;');
508 $this->transaction_opcount = 0;
510 return $this->pgsqlRaiseError();
517 // {{{ affectedRows()
520 * Gets the number of rows affected by the last query.
521 * if the last query was a select, returns 0.
523 * @return int number of rows affected by the last query or DB_ERROR
525 function affectedRows()
527 return $this->affected;
534 * Returns the next free id in a sequence
536 * @param string $seq_name name of the sequence
537 * @param boolean $ondemand when true, the seqence is automatically
538 * created if it does not exist
540 * @return int the next id number in the sequence. DB_Error if problem.
543 * @see DB_common::nextID()
546 function nextId($seq_name, $ondemand = true)
548 $seqname = $this->getSequenceName($seq_name);
551 $this->pushErrorHandling(PEAR_ERROR_RETURN);
552 $result =& $this->query("SELECT NEXTVAL('${seqname}')");
553 $this->popErrorHandling();
554 if ($ondemand && DB::isError($result) &&
555 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
557 $this->pushErrorHandling(PEAR_ERROR_RETURN);
558 $result = $this->createSequence($seq_name);
559 $this->popErrorHandling();
560 if (DB::isError($result)) {
561 return $this->raiseError($result);
567 if (DB::isError($result)) {
568 return $this->raiseError($result);
570 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
576 // {{{ createSequence()
579 * Create the sequence
581 * @param string $seq_name the name of the sequence
582 * @return mixed DB_OK on success or DB error on error
585 function createSequence($seq_name)
587 $seqname = $this->getSequenceName($seq_name);
588 $result = $this->query("CREATE SEQUENCE ${seqname}");
593 // {{{ dropSequence()
598 * @param string $seq_name the name of the sequence
599 * @return mixed DB_OK on success or DB error on error
602 function dropSequence($seq_name)
604 $seqname = $this->getSequenceName($seq_name);
605 return $this->query("DROP SEQUENCE ${seqname}");
609 // {{{ modifyLimitQuery()
611 function modifyLimitQuery($query, $from, $count)
613 $query = $query . " LIMIT $count OFFSET $from";
618 // {{{ pgsqlRaiseError()
621 * Gather information about an error, then use that info to create a
622 * DB error object and finally return that object.
624 * @param integer $errno PEAR error number (usually a DB constant) if
625 * manually raising an error
626 * @return object DB error object
629 * @see DB_common::raiseError()
631 function pgsqlRaiseError($errno = null)
633 $native = $this->errorNative();
634 if ($errno === null) {
635 $err = $this->errorCode($native);
639 return $this->raiseError($err, null, null, null, $native);
643 // {{{ _pgFieldFlags()
648 * @param int $resource PostgreSQL result identifier
649 * @param int $num_field the field number
651 * @return string The flags of the field ("not_null", "default_value",
652 * "primary_key", "unique_key" and "multiple_key"
653 * are supported). The default value is passed
654 * through rawurlencode() in case there are spaces in it.
657 function _pgFieldFlags($resource, $num_field, $table_name)
659 $field_name = @pg_fieldname($resource, $num_field);
661 $result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
662 FROM pg_attribute f, pg_class tab, pg_type typ
663 WHERE tab.relname = typ.typname
664 AND typ.typrelid = f.attrelid
665 AND f.attname = '$field_name'
666 AND tab.relname = '$table_name'");
667 if (@pg_numrows($result) > 0) {
668 $row = @pg_fetch_row($result, 0);
669 $flags = ($row[0] == 't') ? 'not_null ' : '';
671 if ($row[1] == 't') {
672 $result = @pg_exec($this->connection, "SELECT a.adsrc
673 FROM pg_attribute f, pg_class tab, pg_type typ, pg_attrdef a
674 WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid
675 AND f.attrelid = a.adrelid AND f.attname = '$field_name'
676 AND tab.relname = '$table_name' AND f.attnum = a.adnum");
677 $row = @pg_fetch_row($result, 0);
678 $num = preg_replace("/'(.*)'::\w+/", "\\1", $row[0]);
679 $flags .= 'default_' . rawurlencode($num) . ' ';
684 $result = @pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey
685 FROM pg_attribute f, pg_class tab, pg_type typ, pg_index i
686 WHERE tab.relname = typ.typname
687 AND typ.typrelid = f.attrelid
688 AND f.attrelid = i.indrelid
689 AND f.attname = '$field_name'
690 AND tab.relname = '$table_name'");
691 $count = @pg_numrows($result);
693 for ($i = 0; $i < $count ; $i++) {
694 $row = @pg_fetch_row($result, $i);
695 $keys = explode(' ', $row[2]);
697 if (in_array($num_field + 1, $keys)) {
698 $flags .= ($row[0] == 't' && $row[1] == 'f') ? 'unique_key ' : '';
699 $flags .= ($row[1] == 't') ? 'primary_key ' : '';
700 if (count($keys) > 1)
701 $flags .= 'multiple_key ';
712 * Returns information about a table or a result set.
714 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
717 * @param object|string $result DB_result object from a query or a
718 * string containing the name of a table
719 * @param int $mode a valid tableInfo mode
720 * @return array an associative array with the information requested
721 * or an error object if something is wrong
724 * @see DB_common::tableInfo()
726 function tableInfo($result, $mode = null)
728 if (isset($result->result)) {
730 * Probably received a result object.
731 * Extract the result resource identifier.
733 $id = $result->result;
735 } elseif (is_string($result)) {
737 * Probably received a table name.
738 * Create a result resource identifier.
740 $id = @pg_exec($this->connection, "SELECT * FROM $result LIMIT 0");
744 * Probably received a result resource identifier.
746 * Deprecated. Here for compatibility only.
752 if (!is_resource($id)) {
753 return $this->pgsqlRaiseError(DB_ERROR_NEED_MORE_DATA);
756 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
757 $case_func = 'strtolower';
759 $case_func = 'strval';
762 $count = @pg_numfields($id);
764 // made this IF due to performance (one if is faster than $count if's)
767 for ($i=0; $i<$count; $i++) {
768 $res[$i]['table'] = $got_string ? $case_func($result) : '';
769 $res[$i]['name'] = $case_func(@pg_fieldname($id, $i));
770 $res[$i]['type'] = @pg_fieldtype($id, $i);
771 $res[$i]['len'] = @pg_fieldsize($id, $i);
772 $res[$i]['flags'] = $got_string ? $this->_pgFieldflags($id, $i, $result) : '';
776 $res['num_fields']= $count;
778 for ($i=0; $i<$count; $i++) {
779 $res[$i]['table'] = $got_string ? $case_func($result) : '';
780 $res[$i]['name'] = $case_func(@pg_fieldname($id, $i));
781 $res[$i]['type'] = @pg_fieldtype($id, $i);
782 $res[$i]['len'] = @pg_fieldsize($id, $i);
783 $res[$i]['flags'] = $got_string ? $this->_pgFieldFlags($id, $i, $result) : '';
785 if ($mode & DB_TABLEINFO_ORDER) {
786 $res['order'][$res[$i]['name']] = $i;
788 if ($mode & DB_TABLEINFO_ORDERTABLE) {
789 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
794 // free the result only if we were called on a table
802 // {{{ getTablesQuery()
805 * Returns the query needed to get some backend info
806 * @param string $type What kind of info you want to retrieve
807 * @return string The SQL query string
809 function getSpecialQuery($type)
813 return "SELECT c.relname as \"Name\"
814 FROM pg_class c, pg_user u
815 WHERE c.relowner = u.usesysid AND c.relkind = 'r'
816 AND not exists (select 1 from pg_views where viewname = c.relname)
817 AND c.relname !~ '^pg_'
819 SELECT c.relname as \"Name\"
821 WHERE c.relkind = 'r'
822 AND not exists (select 1 from pg_views where viewname = c.relname)
823 AND not exists (select 1 from pg_user where usesysid = c.relowner)
824 AND c.relname !~ '^pg_'";
826 // Table cols: viewname | viewowner | definition
827 return 'SELECT viewname FROM pg_views';
829 // cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
830 return 'SELECT usename FROM pg_user';
832 return 'SELECT datname FROM pg_database';
834 return 'SELECT proname FROM pg_proc';