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 require_once 'DB/common.php';
24 * Database independent query interface definition for PHP's PostgreSQL
30 * @author Rui Hirokawa <hirokawa@php.net>
31 * @author Stig Bakken <ssb@php.net>
33 class DB_pgsql extends DB_common
38 var $phptype, $dbsyntax;
39 var $prepare_tokens = array();
40 var $prepare_types = array();
41 var $transaction_opcount = 0;
44 var $num_rows = array();
46 var $autocommit = true;
47 var $fetchmode = DB_FETCHMODE_ORDERED;
55 $this->phptype = 'pgsql';
56 $this->dbsyntax = 'pgsql';
57 $this->features = array(
60 'transactions' => true,
63 $this->errorcode_map = array(
71 * Connect to a database and log in as the specified user.
73 * @param $dsn the data source name (see DB::parseDSN for syntax)
74 * @param $persistent (optional) whether the connection should
77 * @return int DB_OK on success, a DB error code on failure.
79 function connect($dsninfo, $persistent = false)
81 if (!DB::assertExtension('pgsql')) {
82 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
85 $this->dsn = $dsninfo;
86 $protocol = $dsninfo['protocol'] ? $dsninfo['protocol'] : 'tcp';
89 if ($protocol == 'tcp') {
90 if ($dsninfo['hostspec']) {
91 $connstr .= 'host=' . $dsninfo['hostspec'];
93 if ($dsninfo['port']) {
94 $connstr .= ' port=' . $dsninfo['port'];
96 } elseif ($protocol == 'unix') {
97 // Allow for pg socket in non-standard locations.
98 if ($dsninfo['socket']) {
99 $connstr .= 'host=' . $dsninfo['socket'];
103 if ($dsninfo['database']) {
104 $connstr .= ' dbname=\'' . addslashes($dsninfo['database']) . '\'';
106 if ($dsninfo['username']) {
107 $connstr .= ' user=\'' . addslashes($dsninfo['username']) . '\'';
109 if ($dsninfo['password']) {
110 $connstr .= ' password=\'' . addslashes($dsninfo['password']) . '\'';
112 if (isset($dsninfo['options'])) {
113 $connstr .= ' options=' . $dsninfo['options'];
115 if (isset($dsninfo['tty'])) {
116 $connstr .= ' tty=' . $dsninfo['tty'];
119 $connect_function = $persistent ? 'pg_pconnect' : 'pg_connect';
122 $conn = $connect_function($connstr);
123 $error = ob_get_contents();
125 if ($conn == false) {
126 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null,
127 null, null, strip_tags($error));
129 $this->connection = $conn;
137 * Log out and disconnect from the database.
139 * @return bool true on success, false if not connected.
141 function disconnect()
143 $ret = @pg_close($this->connection);
144 $this->connection = null;
152 * Send a query to PostgreSQL and return the results as a
153 * PostgreSQL resource identifier.
155 * @param $query the SQL query
157 * @return int returns a valid PostgreSQL result for successful SELECT
158 * queries, DB_OK for other successful queries. A DB error code
159 * is returned on failure.
161 function simpleQuery($query)
163 $ismanip = DB::isManip($query);
164 $this->last_query = $query;
165 $query = $this->modifyQuery($query);
166 if (!$this->autocommit && $ismanip) {
167 if ($this->transaction_opcount == 0) {
168 $result = @pg_exec($this->connection, 'begin;');
170 return $this->pgsqlRaiseError();
173 $this->transaction_opcount++;
175 $result = @pg_exec($this->connection, $query);
177 return $this->pgsqlRaiseError();
179 // Determine which queries that should return data, and which
180 // should return an error code only.
182 $this->affected = @pg_cmdtuples($result);
184 } elseif (preg_match('/^\s*\(?\s*(SELECT(?!\s+INTO)|EXPLAIN|SHOW)\s/si', $query)) {
185 /* PostgreSQL commands:
186 ABORT, ALTER, BEGIN, CLOSE, CLUSTER, COMMIT, COPY,
187 CREATE, DECLARE, DELETE, DROP TABLE, EXPLAIN, FETCH,
188 GRANT, INSERT, LISTEN, LOAD, LOCK, MOVE, NOTIFY, RESET,
189 REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SHOW,
190 UNLISTEN, UPDATE, VACUUM
192 $this->row[(int)$result] = 0; // reset the row counter.
193 $numrows = $this->numrows($result);
194 if (is_object($numrows)) {
197 $this->num_rows[(int)$result] = $numrows;
210 * Move the internal pgsql result pointer to the next available result
212 * @param a valid fbsql result resource
216 * @return true if a result is available otherwise return false
218 function nextResult($result)
227 * Determine PEAR::DB error code from the database's text error message.
229 * @param string $errormsg error message returned from the database
230 * @return integer an error number from a DB error constant
232 function errorCode($errormsg)
234 static $error_regexps;
235 if (!isset($error_regexps)) {
236 $error_regexps = array(
237 '/(([Rr]elation|[Ss]equence|[Tt]able)( [\"\'].*[\"\'])? does not exist|[Cc]lass ".+" not found)$/' => DB_ERROR_NOSUCHTABLE,
238 '/[Cc]olumn [\"\'].*[\"\'] does not exist/' => DB_ERROR_NOSUCHFIELD,
239 '/[Rr]elation [\"\'].*[\"\'] already exists|[Cc]annot insert a duplicate key into (a )?unique index.*/' => DB_ERROR_ALREADY_EXISTS,
240 '/(divide|division) by zero$/' => DB_ERROR_DIVZERO,
241 '/pg_atoi: error in .*: can\'t parse /' => DB_ERROR_INVALID_NUMBER,
242 '/invalid input syntax for integer/' => DB_ERROR_INVALID_NUMBER,
243 '/ttribute [\"\'].*[\"\'] not found$|[Rr]elation [\"\'].*[\"\'] does not have attribute [\"\'].*[\"\']/' => DB_ERROR_NOSUCHFIELD,
244 '/parser: parse error at or near \"/' => DB_ERROR_SYNTAX,
245 '/syntax error at/' => DB_ERROR_SYNTAX,
246 '/violates not-null constraint/' => DB_ERROR_CONSTRAINT_NOT_NULL,
247 '/violates [\w ]+ constraint/' => DB_ERROR_CONSTRAINT,
248 '/referential integrity violation/' => DB_ERROR_CONSTRAINT
251 foreach ($error_regexps as $regexp => $code) {
252 if (preg_match($regexp, $errormsg)) {
256 // Fall back to DB_ERROR if there was no mapping.
264 * Fetch a row and insert the data into an existing array.
266 * Formating of the array and the data therein are configurable.
267 * See DB_result::fetchInto() for more information.
269 * @param resource $result query result identifier
270 * @param array $arr (reference) array where data from the row
272 * @param int $fetchmode how the resulting array should be indexed
273 * @param int $rownum the row number to fetch
275 * @return mixed DB_OK on success, null when end of result set is
276 * reached or on failure
278 * @see DB_result::fetchInto()
281 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
283 $rownum = ($rownum !== null) ? $rownum : $this->row[$result];
284 if ($rownum >= $this->num_rows[$result]) {
287 if ($fetchmode & DB_FETCHMODE_ASSOC) {
288 $arr = @pg_fetch_array($result, $rownum, PGSQL_ASSOC);
289 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
290 $arr = array_change_key_case($arr, CASE_LOWER);
293 $arr = @pg_fetch_row($result, $rownum);
296 $err = pg_errormessage($this->connection);
300 return $this->pgsqlRaiseError();
302 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
303 $this->_rtrimArrayValues($arr);
305 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
306 $this->_convertNullArrayValuesToEmpty($arr);
308 $this->row[$result] = ++$rownum;
316 * Free the internal resources associated with $result.
318 * @param $result int PostgreSQL result identifier
320 * @return bool true on success, false if $result is invalid
322 function freeResult($result)
324 if (is_resource($result)) {
325 unset($this->row[(int)$result]);
326 unset($this->num_rows[(int)$result]);
328 return @pg_freeresult($result);
337 * @deprecated Deprecated in release 1.6.0
340 function quote($str) {
341 return $this->quoteSmart($str);
348 * Format input so it can be safely used in a query
350 * @param mixed $in data to be quoted
352 * @return mixed Submitted variable's type = returned value:
353 * + null = the string <samp>NULL</samp>
354 * + boolean = string <samp>TRUE</samp> or <samp>FALSE</samp>
355 * + integer or double = the unquoted number
356 * + other (including strings and numeric strings) =
357 * the data escaped according to MySQL's settings
358 * then encapsulated between single quotes
362 function quoteSmart($in)
364 if (is_int($in) || is_double($in)) {
366 } elseif (is_bool($in)) {
367 return $in ? 'TRUE' : 'FALSE';
368 } elseif (is_null($in)) {
371 return "'" . $this->escapeSimple($in) . "'";
376 // {{{ escapeSimple()
379 * Escapes a string according to the current DBMS's standards
381 * {@internal PostgreSQL treats a backslash as an escape character,
382 * so they are escaped as well.
384 * @param string $str the string to be escaped
386 * @return string the escaped string
388 * @see DB_common::quoteSmart()
389 * @since Method available since Release 1.6.0
391 function escapeSimple($str)
393 if (function_exists('pg_escape_string')) {
394 /* This fixes an undocumented BC break in PHP 5.2.0 which changed
395 * the prototype of pg_escape_string. I'm not thrilled about having
396 * to sniff the PHP version, quite frankly, but it's the only way
397 * to deal with the problem. Revision 1.331.2.13.2.10 on
398 * php-src/ext/pgsql/pgsql.c (PHP_5_2 branch) is to blame, for the
400 if (version_compare(PHP_VERSION, '5.2.0', '>=')) {
401 return pg_escape_string($this->connection, $str);
403 return pg_escape_string($str);
406 return str_replace("'", "''", str_replace('\\', '\\\\', $str));
414 * Get the number of columns in a result set.
416 * @param $result resource PostgreSQL result identifier
418 * @return int the number of columns per row in $result
420 function numCols($result)
422 $cols = @pg_numfields($result);
424 return $this->pgsqlRaiseError();
433 * Get the number of rows in a result set.
435 * @param $result resource PostgreSQL result identifier
437 * @return int the number of rows in $result
439 function numRows($result)
441 $rows = @pg_numrows($result);
442 if ($rows === null) {
443 return $this->pgsqlRaiseError();
452 * Get the native error code of the last error (if any) that
453 * occured on the current connection.
455 * @return int native PostgreSQL error code
457 function errorNative()
459 return pg_errormessage($this->connection);
466 * Enable/disable automatic commits
468 function autoCommit($onoff = false)
470 // XXX if $this->transaction_opcount > 0, we should probably
471 // issue a warning here.
472 $this->autocommit = $onoff ? true : false;
480 * Commit the current transaction.
484 if ($this->transaction_opcount > 0) {
485 // (disabled) hack to shut up error messages from libpq.a
486 //@fclose(@fopen("php://stderr", "w"));
487 $result = @pg_exec($this->connection, 'end;');
488 $this->transaction_opcount = 0;
490 return $this->pgsqlRaiseError();
500 * Roll back (undo) the current transaction.
504 if ($this->transaction_opcount > 0) {
505 $result = @pg_exec($this->connection, 'abort;');
506 $this->transaction_opcount = 0;
508 return $this->pgsqlRaiseError();
515 // {{{ affectedRows()
518 * Gets the number of rows affected by the last query.
519 * if the last query was a select, returns 0.
521 * @return int number of rows affected by the last query or DB_ERROR
523 function affectedRows()
525 return $this->affected;
532 * Returns the next free id in a sequence
534 * @param string $seq_name name of the sequence
535 * @param boolean $ondemand when true, the seqence is automatically
536 * created if it does not exist
538 * @return int the next id number in the sequence. DB_Error if problem.
541 * @see DB_common::nextID()
544 function nextId($seq_name, $ondemand = true)
546 $seqname = $this->getSequenceName($seq_name);
549 $this->pushErrorHandling(PEAR_ERROR_RETURN);
550 $result =& $this->query("SELECT NEXTVAL('${seqname}')");
551 $this->popErrorHandling();
552 if ($ondemand && DB::isError($result) &&
553 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
555 $this->pushErrorHandling(PEAR_ERROR_RETURN);
556 $result = $this->createSequence($seq_name);
557 $this->popErrorHandling();
558 if (DB::isError($result)) {
559 return $this->raiseError($result);
565 if (DB::isError($result)) {
566 return $this->raiseError($result);
568 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
574 // {{{ createSequence()
577 * Create the sequence
579 * @param string $seq_name the name of the sequence
580 * @return mixed DB_OK on success or DB error on error
583 function createSequence($seq_name)
585 $seqname = $this->getSequenceName($seq_name);
586 $result = $this->query("CREATE SEQUENCE ${seqname}");
591 // {{{ dropSequence()
596 * @param string $seq_name the name of the sequence
597 * @return mixed DB_OK on success or DB error on error
600 function dropSequence($seq_name)
602 $seqname = $this->getSequenceName($seq_name);
603 return $this->query("DROP SEQUENCE ${seqname}");
607 // {{{ modifyLimitQuery()
609 function modifyLimitQuery($query, $from, $count)
611 $query = $query . " LIMIT $count OFFSET $from";
616 // {{{ pgsqlRaiseError()
619 * Gather information about an error, then use that info to create a
620 * DB error object and finally return that object.
622 * @param integer $errno PEAR error number (usually a DB constant) if
623 * manually raising an error
624 * @return object DB error object
627 * @see DB_common::raiseError()
629 function pgsqlRaiseError($errno = null)
631 $native = $this->errorNative();
632 if ($errno === null) {
633 $err = $this->errorCode($native);
637 return $this->raiseError($err, null, null, null, $native);
641 // {{{ _pgFieldFlags()
646 * @param int $resource PostgreSQL result identifier
647 * @param int $num_field the field number
649 * @return string The flags of the field ("not_null", "default_value",
650 * "primary_key", "unique_key" and "multiple_key"
651 * are supported). The default value is passed
652 * through rawurlencode() in case there are spaces in it.
655 function _pgFieldFlags($resource, $num_field, $table_name)
657 $field_name = @pg_fieldname($resource, $num_field);
659 $result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
660 FROM pg_attribute f, pg_class tab, pg_type typ
661 WHERE tab.relname = typ.typname
662 AND typ.typrelid = f.attrelid
663 AND f.attname = '$field_name'
664 AND tab.relname = '$table_name'");
665 if (@pg_numrows($result) > 0) {
666 $row = @pg_fetch_row($result, 0);
667 $flags = ($row[0] == 't') ? 'not_null ' : '';
669 if ($row[1] == 't') {
670 $result = @pg_exec($this->connection, "SELECT a.adsrc
671 FROM pg_attribute f, pg_class tab, pg_type typ, pg_attrdef a
672 WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid
673 AND f.attrelid = a.adrelid AND f.attname = '$field_name'
674 AND tab.relname = '$table_name' AND f.attnum = a.adnum");
675 $row = @pg_fetch_row($result, 0);
676 $num = preg_replace("/'(.*)'::\w+/", "\\1", $row[0]);
677 $flags .= 'default_' . rawurlencode($num) . ' ';
682 $result = @pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey
683 FROM pg_attribute f, pg_class tab, pg_type typ, pg_index i
684 WHERE tab.relname = typ.typname
685 AND typ.typrelid = f.attrelid
686 AND f.attrelid = i.indrelid
687 AND f.attname = '$field_name'
688 AND tab.relname = '$table_name'");
689 $count = @pg_numrows($result);
691 for ($i = 0; $i < $count ; $i++) {
692 $row = @pg_fetch_row($result, $i);
693 $keys = explode(' ', $row[2]);
695 if (in_array($num_field + 1, $keys)) {
696 $flags .= ($row[0] == 't' && $row[1] == 'f') ? 'unique_key ' : '';
697 $flags .= ($row[1] == 't') ? 'primary_key ' : '';
698 if (count($keys) > 1)
699 $flags .= 'multiple_key ';
710 * Returns information about a table or a result set.
712 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
715 * @param object|string $result DB_result object from a query or a
716 * string containing the name of a table
717 * @param int $mode a valid tableInfo mode
718 * @return array an associative array with the information requested
719 * or an error object if something is wrong
722 * @see DB_common::tableInfo()
724 function tableInfo($result, $mode = null)
726 if (isset($result->result)) {
728 * Probably received a result object.
729 * Extract the result resource identifier.
731 $id = $result->result;
733 } elseif (is_string($result)) {
735 * Probably received a table name.
736 * Create a result resource identifier.
738 $id = @pg_exec($this->connection, "SELECT * FROM $result LIMIT 0");
742 * Probably received a result resource identifier.
744 * Deprecated. Here for compatibility only.
750 if (!is_resource($id)) {
751 return $this->pgsqlRaiseError(DB_ERROR_NEED_MORE_DATA);
754 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
755 $case_func = 'strtolower';
757 $case_func = 'strval';
760 $count = @pg_numfields($id);
762 // made this IF due to performance (one if is faster than $count if's)
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) : '';
774 $res['num_fields']= $count;
776 for ($i=0; $i<$count; $i++) {
777 $res[$i]['table'] = $got_string ? $case_func($result) : '';
778 $res[$i]['name'] = $case_func(@pg_fieldname($id, $i));
779 $res[$i]['type'] = @pg_fieldtype($id, $i);
780 $res[$i]['len'] = @pg_fieldsize($id, $i);
781 $res[$i]['flags'] = $got_string ? $this->_pgFieldFlags($id, $i, $result) : '';
783 if ($mode & DB_TABLEINFO_ORDER) {
784 $res['order'][$res[$i]['name']] = $i;
786 if ($mode & DB_TABLEINFO_ORDERTABLE) {
787 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
792 // free the result only if we were called on a table
800 // {{{ getTablesQuery()
803 * Returns the query needed to get some backend info
804 * @param string $type What kind of info you want to retrieve
805 * @return string The SQL query string
807 function getSpecialQuery($type)
811 return "SELECT c.relname as \"Name\"
812 FROM pg_class c, pg_user u
813 WHERE c.relowner = u.usesysid AND c.relkind = 'r'
814 AND not exists (select 1 from pg_views where viewname = c.relname)
815 AND c.relname !~ '^pg_'
817 SELECT c.relname as \"Name\"
819 WHERE c.relkind = 'r'
820 AND not exists (select 1 from pg_views where viewname = c.relname)
821 AND not exists (select 1 from pg_user where usesysid = c.relowner)
822 AND c.relname !~ '^pg_'";
824 // Table cols: viewname | viewowner | definition
825 return 'SELECT viewname FROM pg_views';
827 // cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
828 return 'SELECT usename FROM pg_user';
830 return 'SELECT datname FROM pg_database';
832 return 'SELECT proname FROM pg_proc';