3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2002 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@fast.no> |
18 // +----------------------------------------------------------------------+
20 // Based on code from the PHP CVS repository. The only modifications made
21 // have been modification of the include paths.
23 rcs_id('$Id: pgsql.php,v 1.1 2002-01-28 04:01:57 dairiki Exp $');
24 rcs_id('From Pear CVS: Id: pgsql.php,v 1.63 2002/01/17 11:31:12 cox 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 $connstr = 'host=' . $dsninfo['hostspec'];
96 if ($dsninfo['port']) {
97 $connstr .= ' port=' . $dsninfo['port'];
101 if (isset($dsninfo['database'])) {
102 $connstr .= ' dbname=' . $dsninfo['database'];
104 if (!empty($dsninfo['username'])) {
105 $connstr .= ' user=' . $dsninfo['username'];
107 if (!empty($dsninfo['password'])) {
108 $connstr .= ' password=' . $dsninfo['password'];
110 if (!empty($dsninfo['options'])) {
111 $connstr .= ' options=' . $dsninfo['options'];
113 if (!empty($dsninfo['tty'])) {
114 $connstr .= ' tty=' . $dsninfo['tty'];
117 $connect_function = $persistent ? 'pg_pconnect' : 'pg_connect';
120 $conn = $connect_function($connstr);
121 $error = ob_get_contents();
123 if ($conn == false) {
124 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null,
125 null, null, strip_tags($error));
127 $this->connection = $conn;
135 * Log out and disconnect from the database.
137 * @return bool TRUE on success, FALSE if not connected.
139 function disconnect()
141 $ret = @pg_close($this->connection); // XXX ERRORMSG
142 $this->connection = null;
150 * Send a query to PostgreSQL and return the results as a
151 * PostgreSQL resource identifier.
153 * @param $query the SQL query
155 * @return int returns a valid PostgreSQL result for successful SELECT
156 * queries, DB_OK for other successful queries. A DB error code
157 * is returned on failure.
159 function simpleQuery($query)
161 $ismanip = DB::isManip($query);
162 $this->last_query = $query;
163 $query = $this->modifyQuery($query);
164 if (!$this->autocommit && $ismanip) {
165 if ($this->transaction_opcount == 0) {
166 $result = @pg_exec($this->connection, "begin;");
168 return $this->pgsqlRaiseError();
171 $this->transaction_opcount++;
173 $result = @pg_exec($this->connection, $query);
175 return $this->pgsqlRaiseError();
177 // Determine which queries that should return data, and which
178 // should return an error code only.
180 $this->affected = @pg_cmdtuples($result);
182 } elseif (preg_match('/^\s*(SELECT)\s/i', $query) &&
183 !preg_match('/^\s*(SELECT\s+INTO)\s/i', $query)) {
184 /* PostgreSQL commands:
185 ABORT, ALTER, BEGIN, CLOSE, CLUSTER, COMMIT, COPY,
186 CREATE, DECLARE, DELETE, DROP TABLE, EXPLAIN, FETCH,
187 GRANT, INSERT, LISTEN, LOAD, LOCK, MOVE, NOTIFY, RESET,
188 REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SHOW,
189 UNLISTEN, UPDATE, VACUUM
191 $this->row[$result] = 0; // reset the row counter.
192 $numrows = $this->numrows($result);
193 if (is_object($numrows)) {
196 $this->num_rows[$result] = $numrows;
209 * Move the internal pgsql result pointer to the next available result
211 * @param a valid fbsql result resource
215 * @return true if a result is available otherwise return false
217 function nextResult($result)
226 * Map native error codes to DB's portable ones. Requires that
227 * the DB implementation's constructor fills in the $errorcode_map
230 * @param $nativecode the native error code, as returned by the backend
231 * database extension (string or integer)
233 * @return int a portable DB error code, or FALSE if this DB
234 * implementation has no mapping for the given error code.
237 function errorCode($errormsg)
239 static $error_regexps;
240 if (empty($error_regexps)) {
241 $error_regexps = array(
242 '/(Table does not exist\.|Relation \'.*\' does not exist|sequence does not exist|class ".+" not found)$/' => DB_ERROR_NOSUCHTABLE,
243 '/Relation \'.*\' already exists|Cannot insert a duplicate key into (a )?unique index.*/' => DB_ERROR_ALREADY_EXISTS,
244 '/divide by zero$/' => DB_ERROR_DIVZERO,
245 '/pg_atoi: error in .*: can\'t parse /' => DB_ERROR_INVALID_NUMBER,
246 '/ttribute \'.*\' not found$|Relation \'.*\' does not have attribute \'.*\'/' => DB_ERROR_NOSUCHFIELD,
247 '/parser: parse error at or near \"/' => DB_ERROR_SYNTAX,
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.
262 * Fetch and return a row of data (it uses fetchInto for that)
263 * @param $result PostgreSQL result identifier
264 * @param $fetchmode format of fetched row array
265 * @param $rownum the absolute row number to fetch
267 * @return array a row of data, or false on error
269 function fetchRow($result, $fetchmode = DB_FETCHMODE_DEFAULT, $rownum=null)
271 if ($fetchmode == DB_FETCHMODE_DEFAULT) {
272 $fetchmode = $this->fetchmode;
274 $res = $this->fetchInto ($result, $arr, $fetchmode, $rownum);
275 if ($res !== DB_OK) {
284 * Fetch a row and insert the data into an existing array.
286 * @param $result PostgreSQL result identifier
287 * @param $row (reference) array where data from the row is stored
288 * @param $fetchmode how the array data should be indexed
289 * @param $rownum the row number to fetch
291 * @return int DB_OK on success, a DB error code on failure
293 function fetchInto($result, &$row, $fetchmode, $rownum=null)
295 $rownum = ($rownum !== null) ? $rownum : $this->row[$result];
296 if ($rownum >= $this->num_rows[$result]) {
299 if ($fetchmode & DB_FETCHMODE_ASSOC) {
300 $row = @pg_fetch_array($result, $rownum, PGSQL_ASSOC);
302 $row = @pg_fetch_row($result, $rownum);
305 $err = pg_errormessage($this->connection);
309 return $this->pgsqlRaiseError();
311 $this->row[$result] = ++$rownum;
319 * Free the internal resources associated with $result.
321 * @param $result int PostgreSQL result identifier or DB statement identifier
323 * @return bool TRUE on success, FALSE if $result is invalid
325 function freeResult($result)
327 if (is_resource($result)) {
328 return @pg_freeresult($result);
330 if (!isset($this->prepare_tokens[(int)$result])) {
333 unset($this->prepare_tokens[(int)$result]);
334 unset($this->prepare_types[(int)$result]);
335 unset($this->row[(int)$result]);
336 unset($this->num_rows[(int)$result]);
344 * Quote the given string so it can be safely used within string delimiters
346 * @param $string mixed Data to be quoted
347 * @return mixed "NULL" string, quoted string or original data
349 function quote($str = null)
351 switch (strtolower(gettype($str))) {
359 $str = str_replace("'", "''", $str);
360 //PostgreSQL treats a backslash as an escape character.
361 $str = str_replace('\\', '\\\\', $str);
369 * Get the number of columns in a result set.
371 * @param $result resource PostgreSQL result identifier
373 * @return int the number of columns per row in $result
375 function numCols($result)
377 $cols = @pg_numfields($result);
379 return $this->pgsqlRaiseError();
388 * Get the number of rows in a result set.
390 * @param $result resource PostgreSQL result identifier
392 * @return int the number of rows in $result
394 function numRows($result)
396 $rows = @pg_numrows($result);
397 if ($rows === null) {
398 return $this->pgsqlRaiseError();
407 * Get the native error code of the last error (if any) that
408 * occured on the current connection.
410 * @return int native PostgreSQL error code
412 function errorNative()
414 return pg_errormessage($this->connection);
421 * Enable/disable automatic commits
423 function autoCommit($onoff = false)
425 // XXX if $this->transaction_opcount > 0, we should probably
426 // issue a warning here.
427 $this->autocommit = $onoff ? true : false;
435 * Commit the current transaction.
439 if ($this->transaction_opcount > 0) {
440 // (disabled) hack to shut up error messages from libpq.a
441 //@fclose(@fopen("php://stderr", "w"));
442 $result = @pg_exec($this->connection, "end;");
443 $this->transaction_opcount = 0;
445 return $this->pgsqlRaiseError();
455 * Roll back (undo) the current transaction.
459 if ($this->transaction_opcount > 0) {
460 $result = @pg_exec($this->connection, "abort;");
461 $this->transaction_opcount = 0;
463 return $this->pgsqlRaiseError();
470 // {{{ affectedRows()
473 * Gets the number of rows affected by the last query.
474 * if the last query was a select, returns 0.
476 * @return int number of rows affected by the last query or DB_ERROR
478 function affectedRows()
480 return $this->affected;
486 * Get the next value in a sequence.
488 * We are using native PostgreSQL sequences. If a sequence does
489 * not exist, it will be created, unless $ondemand is false.
492 * @param string $seq_name the name of the sequence
493 * @param bool $ondemand whether to create the sequence on demand
494 * @return a sequence integer, or a DB error
496 function nextId($seq_name, $ondemand = true)
498 $sqn = preg_replace('/[^a-z0-9_]/i', '_', $seq_name);
501 $this->pushErrorHandling(PEAR_ERROR_RETURN);
502 $result = $this->query("SELECT NEXTVAL('${sqn}_seq')");
503 $this->popErrorHandling();
504 if ($ondemand && DB::isError($result) &&
505 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
507 $result = $this->createSequence($seq_name);
508 if (DB::isError($result)) {
509 return $this->raiseError($result);
515 if (DB::isError($result)) {
516 return $this->raiseError($result);
518 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
524 // {{{ createSequence()
527 * Create the sequence
529 * @param string $seq_name the name of the sequence
530 * @return mixed DB_OK on success or DB error on error
533 function createSequence($seq_name)
535 $sqn = preg_replace('/[^a-z0-9_]/i', '_', $seq_name);
536 $this->pushErrorHandling(PEAR_ERROR_RETURN);
537 $result = $this->query("CREATE SEQUENCE ${sqn}_seq");
538 $this->popErrorHandling();
543 // {{{ dropSequence()
548 * @param string $seq_name the name of the sequence
549 * @return mixed DB_OK on success or DB error on error
552 function dropSequence($seq_name)
554 $sqn = preg_replace('/[^a-z0-9_]/i', '_', $seq_name);
555 return $this->query("DROP SEQUENCE ${sqn}_seq");
559 // {{{ modifyLimitQuery()
561 function modifyLimitQuery($query, $from, $count)
563 $query = $query . " LIMIT $count, $from";
568 // {{{ pgsqlRaiseError()
570 function pgsqlRaiseError($errno = null)
572 $native = $this->errorNative();
573 if ($errno === null) {
574 $err = $this->errorCode($native);
578 return $this->raiseError($err, null, null, null, $native);
582 // {{{ _pgFieldFlags()
587 * @param int $resource PostgreSQL result identifier
588 * @param int $num_field the field number
590 * @return string The flags of the field ("not_null", "default_xx", "primary_key",
591 * "unique" and "multiple_key" are supported)
594 function _pgFieldFlags($resource, $num_field, $table_name)
596 $field_name = @pg_fieldname($resource, $num_field);
598 $result = pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
599 FROM pg_attribute f, pg_class tab, pg_type typ
600 WHERE tab.relname = typ.typname
601 AND typ.typrelid = f.attrelid
602 AND f.attname = '$field_name'
603 AND tab.relname = '$table_name'");
604 if (pg_numrows($result) > 0) {
605 $row = pg_fetch_row($result, 0);
606 $flags = ($row[0] == 't') ? 'not_null ' : '';
608 if ($row[1] == 't') {
609 $result = pg_exec($this->connection, "SELECT a.adsrc
610 FROM pg_attribute f, pg_class tab, pg_type typ, pg_attrdef a
611 WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid
612 AND f.attrelid = a.adrelid AND f.attname = '$field_name'
613 AND tab.relname = '$table_name'");
614 $row = pg_fetch_row($result, 0);
615 $num = str_replace('\'', '', $row[0]);
617 $flags .= "default_$num ";
620 $result = pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey
621 FROM pg_attribute f, pg_class tab, pg_type typ, pg_index i
622 WHERE tab.relname = typ.typname
623 AND typ.typrelid = f.attrelid
624 AND f.attrelid = i.indrelid
625 AND f.attname = '$field_name'
626 AND tab.relname = '$table_name'");
627 $count = pg_numrows($result);
629 for ($i = 0; $i < $count ; $i++) {
630 $row = pg_fetch_row($result, $i);
631 $keys = explode(" ", $row[2]);
633 if (in_array($num_field + 1, $keys)) {
634 $flags .= ($row[0] == 't') ? 'unique ' : '';
635 $flags .= ($row[1] == 't') ? 'primary ' : '';
636 if (count($keys) > 1)
637 $flags .= 'multiple_key ';
648 * Returns information about a table or a result set
650 * NOTE: doesn't support table name and flags if called from a db_result
652 * @param mixed $resource PostgreSQL result identifier or table name
653 * @param int $mode A valid tableInfo mode (DB_TABLEINFO_ORDERTABLE or
654 * DB_TABLEINFO_ORDER)
656 * @return array An array with all the information
658 function tableInfo($result, $mode = null)
665 * depending on $mode, metadata returns the following values:
667 * - mode is false (default):
669 * [0]["table"] table name
670 * [0]["name"] field name
671 * [0]["type"] field type
672 * [0]["len"] field length
673 * [0]["flags"] field flags
675 * - mode is DB_TABLEINFO_ORDER
677 * ["num_fields"] number of metadata records
678 * [0]["table"] table name
679 * [0]["name"] field name
680 * [0]["type"] field type
681 * [0]["len"] field length
682 * [0]["flags"] field flags
683 * ["order"][field name] index of field named "field name"
684 * The last one is used, if you have a field name, but no index.
685 * Test: if (isset($result['meta']['myfield'])) { ...
687 * - mode is DB_TABLEINFO_ORDERTABLE
688 * the same as above. but additionally
689 * ["ordertable"][table name][field name] index of field
692 * this is, because if you have fields from different
693 * tables with the same field name * they override each
694 * other with DB_TABLEINFO_ORDER
696 * you can combine DB_TABLEINFO_ORDER and
697 * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER |
698 * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL
701 // if $result is a string, then we want information about a
702 // table without a resultset
704 if (is_string($result)) {
705 $id = pg_exec($this->connection,"SELECT * FROM $result");
707 return $this->pgsqlRaiseError();
709 } else { // else we want information about a resultset
712 return $this->pgsqlRaiseError();
716 $count = @pg_numfields($id);
718 // made this IF due to performance (one if is faster than $count if's)
721 for ($i=0; $i<$count; $i++) {
722 $res[$i]['table'] = (is_string($result)) ? $result : '';
723 $res[$i]['name'] = @pg_fieldname ($id, $i);
724 $res[$i]['type'] = @pg_fieldtype ($id, $i);
725 $res[$i]['len'] = @pg_fieldsize ($id, $i);
726 $res[$i]['flags'] = (is_string($result)) ? $this->_pgFieldflags($id, $i, $result) : '';
730 $res["num_fields"]= $count;
732 for ($i=0; $i<$count; $i++) {
733 $res[$i]['table'] = (is_string($result)) ? $result : '';
734 $res[$i]['name'] = @pg_fieldname ($id, $i);
735 $res[$i]['type'] = @pg_fieldtype ($id, $i);
736 $res[$i]['len'] = @pg_fieldsize ($id, $i);
737 $res[$i]['flags'] = (is_string($result)) ? $this->_pgFieldFlags($id, $i, $result) : '';
738 if ($mode & DB_TABLEINFO_ORDER) {
739 $res['order'][$res[$i]['name']] = $i;
741 if ($mode & DB_TABLEINFO_ORDERTABLE) {
742 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
747 // free the result only if we were called on a table
748 if (is_resource($id)) {
755 // {{{ getTablesQuery()
758 * Returns the query needed to get some backend info
759 * @param string $type What kind of info you want to retrieve
760 * @return string The SQL query string
762 function getSpecialQuery($type)
766 $sql = "SELECT c.relname as \"Name\"
767 FROM pg_class c, pg_user u
768 WHERE c.relowner = u.usesysid AND c.relkind = 'r'
769 AND not exists (select 1 from pg_views where viewname = c.relname)
770 AND c.relname !~ '^pg_'
772 SELECT c.relname as \"Name\"
774 WHERE c.relkind = 'r'
775 AND not exists (select 1 from pg_views where viewname = c.relname)
776 AND not exists (select 1 from pg_user where usesysid = c.relowner)
777 AND c.relname !~ '^pg_'";
781 // Table cols: viewname | viewowner | definition
782 $sql = "SELECT viewname FROM pg_views";
786 // cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
787 $sql = 'SELECT usename FROM pg_user';
791 $sql = 'SELECT datname FROM pg_database';
795 $sql = 'SELECT proname FROM pg_proc';