2 /* vim: set expandtab tabstop=4 shiftwidth=4 foldmethod=marker: */
3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2003 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 <rui_hirokawa@ybb.ne.jp> |
17 // | Stig Bakken <ssb@php.net> |
18 // +----------------------------------------------------------------------+
20 // Based on DB 1.3 from the pear.php.net repository.
21 // The only modifications made have been modification of the include paths.
23 rcs_id('$Id: pgsql.php,v 1.3 2004-02-07 10:41:25 rurban Exp $');
24 rcs_id('From Pear CVS: Id: pgsql.php,v 1.22 2003/05/07 16:58:28 mj Exp');
26 // Database independent query interface definition for PHP's PostgreSQL
33 // XXX ERRORMSG: The error message from the pgsql function should
34 // be registered here.
37 require_once 'lib/pear/DB/common.php';
39 class DB_pgsql extends DB_common
44 var $phptype, $dbsyntax;
45 var $prepare_tokens = array();
46 var $prepare_types = array();
47 var $transaction_opcount = 0;
50 var $num_rows = array();
52 var $autocommit = true;
53 var $fetchmode = DB_FETCHMODE_ORDERED;
61 $this->phptype = 'pgsql';
62 $this->dbsyntax = 'pgsql';
63 $this->features = array(
66 'transactions' => true,
69 $this->errorcode_map = array(
77 * Connect to a database and log in as the specified user.
79 * @param $dsn the data source name (see DB::parseDSN for syntax)
80 * @param $persistent (optional) whether the connection should
83 * @return int DB_OK on success, a DB error code on failure
85 function connect($dsninfo, $persistent = false)
87 if (!DB::assertExtension('pgsql'))
88 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
90 $this->dsn = $dsninfo;
91 $protocol = (isset($dsninfo['protocol'])) ? $dsninfo['protocol'] : 'tcp';
94 if ($protocol == 'tcp') {
95 if (!empty($dsninfo['hostspec'])) {
96 $connstr = 'host=' . $dsninfo['hostspec'];
98 if (!empty($dsninfo['port'])) {
99 $connstr .= ' port=' . $dsninfo['port'];
103 if (isset($dsninfo['database'])) {
104 $connstr .= ' dbname=\'' . addslashes($dsninfo['database']) . '\'';
106 if (!empty($dsninfo['username'])) {
107 $connstr .= ' user=\'' . addslashes($dsninfo['username']) . '\'';
109 if (!empty($dsninfo['password'])) {
110 $connstr .= ' password=\'' . addslashes($dsninfo['password']) . '\'';
112 if (!empty($dsninfo['options'])) {
113 $connstr .= ' options=' . $dsninfo['options'];
115 if (!empty($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+/si', $query) &&
185 !preg_match('/^\s*\(?\s*SELECT\s+INTO\s/si', $query)) {
186 /* PostgreSQL commands:
187 ABORT, ALTER, BEGIN, CLOSE, CLUSTER, COMMIT, COPY,
188 CREATE, DECLARE, DELETE, DROP TABLE, EXPLAIN, FETCH,
189 GRANT, INSERT, LISTEN, LOAD, LOCK, MOVE, NOTIFY, RESET,
190 REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SHOW,
191 UNLISTEN, UPDATE, VACUUM
193 $this->row[$result] = 0; // reset the row counter.
194 $numrows = $this->numrows($result);
195 if (is_object($numrows)) {
198 $this->num_rows[$result] = $numrows;
211 * Move the internal pgsql result pointer to the next available result
213 * @param a valid fbsql result resource
217 * @return true if a result is available otherwise return false
219 function nextResult($result)
228 * Map native error codes to DB's portable ones. Requires that
229 * the DB implementation's constructor fills in the $errorcode_map
232 * @param $nativecode the native error code, as returned by the backend
233 * database extension (string or integer)
235 * @return int a portable DB error code, or FALSE if this DB
236 * implementation has no mapping for the given error code.
239 function errorCode($errormsg)
241 static $error_regexps;
242 if (empty($error_regexps)) {
243 $error_regexps = array(
244 '/(Table does not exist\.|Relation [\"\'].*[\"\'] does not exist|sequence does not exist|class ".+" not found)$/' => DB_ERROR_NOSUCHTABLE,
245 '/Relation [\"\'].*[\"\'] already exists|Cannot insert a duplicate key into (a )?unique index.*/' => DB_ERROR_ALREADY_EXISTS,
246 '/divide by zero$/' => DB_ERROR_DIVZERO,
247 '/pg_atoi: error in .*: can\'t parse /' => DB_ERROR_INVALID_NUMBER,
248 '/ttribute [\"\'].*[\"\'] not found$|Relation [\"\'].*[\"\'] does not have attribute [\"\'].*[\"\']/' => DB_ERROR_NOSUCHFIELD,
249 '/parser: parse error at or near \"/' => DB_ERROR_SYNTAX,
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 and return a row of data (it uses fetchInto for that)
267 * @param $result PostgreSQL result identifier
268 * @param $fetchmode format of fetched row array
269 * @param $rownum the absolute row number to fetch
271 * @return array a row of data, or false on error
273 function fetchRow($result, $fetchmode = DB_FETCHMODE_DEFAULT, $rownum=null)
275 if ($fetchmode == DB_FETCHMODE_DEFAULT) {
276 $fetchmode = $this->fetchmode;
278 $res = $this->fetchInto ($result, $arr, $fetchmode, $rownum);
279 if ($res !== DB_OK) {
289 * Fetch a row and insert the data into an existing array.
291 * @param $result PostgreSQL result identifier
292 * @param $row (reference) array where data from the row is stored
293 * @param $fetchmode how the array data should be indexed
294 * @param $rownum the row number to fetch
296 * @return int DB_OK on success, a DB error code on failure
298 function fetchInto($result, &$row, $fetchmode, $rownum=null)
300 $rownum = ($rownum !== null) ? $rownum : $this->row[$result];
301 if ($rownum >= $this->num_rows[$result]) {
304 if ($fetchmode & DB_FETCHMODE_ASSOC) {
305 $row = @pg_fetch_array($result, $rownum, PGSQL_ASSOC);
307 $row = @pg_fetch_row($result, $rownum);
310 $err = pg_errormessage($this->connection);
314 return $this->pgsqlRaiseError();
316 $this->row[$result] = ++$rownum;
324 * Free the internal resources associated with $result.
326 * @param $result int PostgreSQL result identifier or DB statement identifier
328 * @return bool TRUE on success, FALSE if $result is invalid
330 function freeResult($result)
332 if (is_resource($result)) {
333 return @pg_freeresult($result);
335 if (!isset($this->prepare_tokens[(int)$result])) {
338 unset($this->prepare_tokens[(int)$result]);
339 unset($this->prepare_types[(int)$result]);
340 unset($this->row[(int)$result]);
341 unset($this->num_rows[(int)$result]);
349 * Quote the given string so it can be safely used within string delimiters
351 * @param $string mixed Data to be quoted
352 * @return mixed "NULL" string, quoted string or original data
354 function quote($str = null)
356 switch (strtolower(gettype($str))) {
363 return $str ? 'TRUE' : 'FALSE';
366 $str = str_replace("'", "''", $str);
367 //PostgreSQL treats a backslash as an escape character.
368 $str = str_replace('\\', '\\\\', $str);
376 * Get the number of columns in a result set.
378 * @param $result resource PostgreSQL result identifier
380 * @return int the number of columns per row in $result
382 function numCols($result)
384 $cols = @pg_numfields($result);
386 return $this->pgsqlRaiseError();
395 * Get the number of rows in a result set.
397 * @param $result resource PostgreSQL result identifier
399 * @return int the number of rows in $result
401 function numRows($result)
403 $rows = @pg_numrows($result);
404 if ($rows === null) {
405 return $this->pgsqlRaiseError();
414 * Get the native error code of the last error (if any) that
415 * occured on the current connection.
417 * @return int native PostgreSQL error code
419 function errorNative()
421 return pg_errormessage($this->connection);
428 * Enable/disable automatic commits
430 function autoCommit($onoff = false)
432 // XXX if $this->transaction_opcount > 0, we should probably
433 // issue a warning here.
434 $this->autocommit = $onoff ? true : false;
442 * Commit the current transaction.
446 if ($this->transaction_opcount > 0) {
447 // (disabled) hack to shut up error messages from libpq.a
448 //@fclose(@fopen("php://stderr", "w"));
449 $result = @pg_exec($this->connection, "end;");
450 $this->transaction_opcount = 0;
452 return $this->pgsqlRaiseError();
462 * Roll back (undo) the current transaction.
466 if ($this->transaction_opcount > 0) {
467 $result = @pg_exec($this->connection, "abort;");
468 $this->transaction_opcount = 0;
470 return $this->pgsqlRaiseError();
477 // {{{ affectedRows()
480 * Gets the number of rows affected by the last query.
481 * if the last query was a select, returns 0.
483 * @return int number of rows affected by the last query or DB_ERROR
485 function affectedRows()
487 return $this->affected;
493 * Get the next value in a sequence.
495 * We are using native PostgreSQL sequences. If a sequence does
496 * not exist, it will be created, unless $ondemand is false.
499 * @param string $seq_name the name of the sequence
500 * @param bool $ondemand whether to create the sequence on demand
501 * @return a sequence integer, or a DB error
503 function nextId($seq_name, $ondemand = true)
505 $seqname = $this->getSequenceName($seq_name);
508 $this->pushErrorHandling(PEAR_ERROR_RETURN);
509 $result = $this->query("SELECT NEXTVAL('${seqname}')");
510 $this->popErrorHandling();
511 if ($ondemand && DB::isError($result) &&
512 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
514 $result = $this->createSequence($seq_name);
515 if (DB::isError($result)) {
516 return $this->raiseError($result);
522 if (DB::isError($result)) {
523 return $this->raiseError($result);
525 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
531 // {{{ createSequence()
534 * Create the sequence
536 * @param string $seq_name the name of the sequence
537 * @return mixed DB_OK on success or DB error on error
540 function createSequence($seq_name)
542 $seqname = $this->getSequenceName($seq_name);
543 $this->pushErrorHandling(PEAR_ERROR_RETURN);
544 $result = $this->query("CREATE SEQUENCE ${seqname}");
545 $this->popErrorHandling();
550 // {{{ dropSequence()
555 * @param string $seq_name the name of the sequence
556 * @return mixed DB_OK on success or DB error on error
559 function dropSequence($seq_name)
561 $seqname = $this->getSequenceName($seq_name);
562 return $this->query("DROP SEQUENCE ${seqname}");
566 // {{{ modifyLimitQuery()
568 function modifyLimitQuery($query, $from, $count)
570 $query = $query . " LIMIT $count OFFSET $from";
575 // {{{ pgsqlRaiseError()
577 function pgsqlRaiseError($errno = null)
579 $native = $this->errorNative();
580 if ($errno === null) {
581 $err = $this->errorCode($native);
585 return $this->raiseError($err, null, null, null, $native);
589 // {{{ _pgFieldFlags()
594 * @param int $resource PostgreSQL result identifier
595 * @param int $num_field the field number
597 * @return string The flags of the field ("not_null", "default_xx", "primary_key",
598 * "unique" and "multiple_key" are supported)
601 function _pgFieldFlags($resource, $num_field, $table_name)
603 $field_name = @pg_fieldname($resource, $num_field);
605 $result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
606 FROM pg_attribute f, pg_class tab, pg_type typ
607 WHERE tab.relname = typ.typname
608 AND typ.typrelid = f.attrelid
609 AND f.attname = '$field_name'
610 AND tab.relname = '$table_name'");
611 if (@pg_numrows($result) > 0) {
612 $row = @pg_fetch_row($result, 0);
613 $flags = ($row[0] == 't') ? 'not_null ' : '';
615 if ($row[1] == 't') {
616 $result = @pg_exec($this->connection, "SELECT a.adsrc
617 FROM pg_attribute f, pg_class tab, pg_type typ, pg_attrdef a
618 WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid
619 AND f.attrelid = a.adrelid AND f.attname = '$field_name'
620 AND tab.relname = '$table_name' AND f.attnum = a.adnum");
621 $row = @pg_fetch_row($result, 0);
622 $num = str_replace('\'', '', $row[0]);
624 $flags .= "default_$num ";
627 $result = @pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey
628 FROM pg_attribute f, pg_class tab, pg_type typ, pg_index i
629 WHERE tab.relname = typ.typname
630 AND typ.typrelid = f.attrelid
631 AND f.attrelid = i.indrelid
632 AND f.attname = '$field_name'
633 AND tab.relname = '$table_name'");
634 $count = @pg_numrows($result);
636 for ($i = 0; $i < $count ; $i++) {
637 $row = @pg_fetch_row($result, $i);
638 $keys = explode(" ", $row[2]);
640 if (in_array($num_field + 1, $keys)) {
641 $flags .= ($row[0] == 't') ? 'unique ' : '';
642 $flags .= ($row[1] == 't') ? 'primary ' : '';
643 if (count($keys) > 1)
644 $flags .= 'multiple_key ';
655 * Returns information about a table or a result set
657 * NOTE: doesn't support table name and flags if called from a db_result
659 * @param mixed $resource PostgreSQL result identifier or table name
660 * @param int $mode A valid tableInfo mode (DB_TABLEINFO_ORDERTABLE or
661 * DB_TABLEINFO_ORDER)
663 * @return array An array with all the information
665 function tableInfo($result, $mode = null)
672 * depending on $mode, metadata returns the following values:
674 * - mode is false (default):
676 * [0]["table"] table name
677 * [0]["name"] field name
678 * [0]["type"] field type
679 * [0]["len"] field length
680 * [0]["flags"] field flags
682 * - mode is DB_TABLEINFO_ORDER
684 * ["num_fields"] number of metadata records
685 * [0]["table"] table name
686 * [0]["name"] field name
687 * [0]["type"] field type
688 * [0]["len"] field length
689 * [0]["flags"] field flags
690 * ["order"][field name] index of field named "field name"
691 * The last one is used, if you have a field name, but no index.
692 * Test: if (isset($result['meta']['myfield'])) { ...
694 * - mode is DB_TABLEINFO_ORDERTABLE
695 * the same as above. but additionally
696 * ["ordertable"][table name][field name] index of field
699 * this is, because if you have fields from different
700 * tables with the same field name * they override each
701 * other with DB_TABLEINFO_ORDER
703 * you can combine DB_TABLEINFO_ORDER and
704 * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER |
705 * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL
708 // if $result is a string, then we want information about a
709 // table without a resultset
711 if (is_string($result)) {
712 $id = @pg_exec($this->connection,"SELECT * FROM $result LIMIT 0");
714 return $this->pgsqlRaiseError();
716 } else { // else we want information about a resultset
719 return $this->pgsqlRaiseError();
723 $count = @pg_numfields($id);
725 // made this IF due to performance (one if is faster than $count if's)
728 for ($i=0; $i<$count; $i++) {
729 $res[$i]['table'] = (is_string($result)) ? $result : '';
730 $res[$i]['name'] = @pg_fieldname ($id, $i);
731 $res[$i]['type'] = @pg_fieldtype ($id, $i);
732 $res[$i]['len'] = @pg_fieldsize ($id, $i);
733 $res[$i]['flags'] = (is_string($result)) ? $this->_pgFieldflags($id, $i, $result) : '';
737 $res["num_fields"]= $count;
739 for ($i=0; $i<$count; $i++) {
740 $res[$i]['table'] = (is_string($result)) ? $result : '';
741 $res[$i]['name'] = @pg_fieldname ($id, $i);
742 $res[$i]['type'] = @pg_fieldtype ($id, $i);
743 $res[$i]['len'] = @pg_fieldsize ($id, $i);
744 $res[$i]['flags'] = (is_string($result)) ? $this->_pgFieldFlags($id, $i, $result) : '';
745 if ($mode & DB_TABLEINFO_ORDER) {
746 $res['order'][$res[$i]['name']] = $i;
748 if ($mode & DB_TABLEINFO_ORDERTABLE) {
749 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
754 // free the result only if we were called on a table
755 if (is_string($result) && is_resource($id)) {
762 // {{{ getTablesQuery()
765 * Returns the query needed to get some backend info
766 * @param string $type What kind of info you want to retrieve
767 * @return string The SQL query string
769 function getSpecialQuery($type)
773 $sql = "SELECT c.relname as \"Name\"
774 FROM pg_class c, pg_user u
775 WHERE c.relowner = u.usesysid AND c.relkind = 'r'
776 AND not exists (select 1 from pg_views where viewname = c.relname)
777 AND c.relname !~ '^pg_'
779 SELECT c.relname as \"Name\"
781 WHERE c.relkind = 'r'
782 AND not exists (select 1 from pg_views where viewname = c.relname)
783 AND not exists (select 1 from pg_user where usesysid = c.relowner)
784 AND c.relname !~ '^pg_'";
788 // Table cols: viewname | viewowner | definition
789 $sql = "SELECT viewname FROM pg_views";
793 // cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
794 $sql = 'SELECT usename FROM pg_user';
798 $sql = 'SELECT datname FROM pg_database';
802 $sql = 'SELECT proname FROM pg_proc';