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 * 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';