1<?php 2 3/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */ 4 5/** 6 * The PEAR DB driver for PHP's pgsql extension 7 * for interacting with PostgreSQL databases 8 * 9 * PHP versions 4 and 5 10 * 11 * LICENSE: This source file is subject to version 3.0 of the PHP license 12 * that is available through the world-wide-web at the following URI: 13 * http://www.php.net/license/3_0.txt. If you did not receive a copy of 14 * the PHP License and are unable to obtain it through the web, please 15 * send a note to license@php.net so we can mail you a copy immediately. 16 * 17 * @category Database 18 * @package DB 19 * @author Rui Hirokawa <hirokawa@php.net> 20 * @author Stig Bakken <ssb@php.net> 21 * @author Daniel Convissor <danielc@php.net> 22 * @copyright 1997-2007 The PHP Group 23 * @license http://www.php.net/license/3_0.txt PHP License 3.0 24 * @version CVS: $Id$ 25 * @link http://pear.php.net/package/DB 26 */ 27 28/** 29 * Obtain the DB_common class so it can be extended from 30 */ 31require_once 'DB/common.php'; 32 33/** 34 * The methods PEAR DB uses to interact with PHP's pgsql extension 35 * for interacting with PostgreSQL databases 36 * 37 * These methods overload the ones declared in DB_common. 38 * 39 * @category Database 40 * @package DB 41 * @author Rui Hirokawa <hirokawa@php.net> 42 * @author Stig Bakken <ssb@php.net> 43 * @author Daniel Convissor <danielc@php.net> 44 * @copyright 1997-2007 The PHP Group 45 * @license http://www.php.net/license/3_0.txt PHP License 3.0 46 * @version Release: @package_version@ 47 * @link http://pear.php.net/package/DB 48 */ 49class DB_pgsql extends DB_common 50{ 51 // {{{ properties 52 53 /** 54 * The DB driver type (mysql, oci8, odbc, etc.) 55 * @var string 56 */ 57 var $phptype = 'pgsql'; 58 59 /** 60 * The database syntax variant to be used (db2, access, etc.), if any 61 * @var string 62 */ 63 var $dbsyntax = 'pgsql'; 64 65 /** 66 * The capabilities of this DB implementation 67 * 68 * The 'new_link' element contains the PHP version that first provided 69 * new_link support for this DBMS. Contains false if it's unsupported. 70 * 71 * Meaning of the 'limit' element: 72 * + 'emulate' = emulate with fetch row by number 73 * + 'alter' = alter the query 74 * + false = skip rows 75 * 76 * @var array 77 */ 78 var $features = array( 79 'limit' => 'alter', 80 'new_link' => '4.3.0', 81 'numrows' => true, 82 'pconnect' => true, 83 'prepare' => false, 84 'ssl' => true, 85 'transactions' => true, 86 ); 87 88 /** 89 * A mapping of native error codes to DB error codes 90 * @var array 91 */ 92 var $errorcode_map = array( 93 ); 94 95 /** 96 * The raw database connection created by PHP 97 * @var resource 98 */ 99 var $connection; 100 101 /** 102 * The DSN information for connecting to a database 103 * @var array 104 */ 105 var $dsn = array(); 106 107 108 /** 109 * Should data manipulation queries be committed automatically? 110 * @var bool 111 * @access private 112 */ 113 var $autocommit = true; 114 115 /** 116 * The quantity of transactions begun 117 * 118 * {@internal While this is private, it can't actually be designated 119 * private in PHP 5 because it is directly accessed in the test suite.}} 120 * 121 * @var integer 122 * @access private 123 */ 124 var $transaction_opcount = 0; 125 126 /** 127 * The number of rows affected by a data manipulation query 128 * @var integer 129 */ 130 var $affected = 0; 131 132 /** 133 * The current row being looked at in fetchInto() 134 * @var array 135 * @access private 136 */ 137 var $row = array(); 138 139 /** 140 * The number of rows in a given result set 141 * @var array 142 * @access private 143 */ 144 var $_num_rows = array(); 145 146 147 // }}} 148 // {{{ constructor 149 150 /** 151 * This constructor calls <kbd>$this->DB_common()</kbd> 152 * 153 * @return void 154 */ 155 function DB_pgsql() 156 { 157 $this->DB_common(); 158 } 159 160 // }}} 161 // {{{ connect() 162 163 /** 164 * Connect to the database server, log in and open the database 165 * 166 * Don't call this method directly. Use DB::connect() instead. 167 * 168 * PEAR DB's pgsql driver supports the following extra DSN options: 169 * + connect_timeout How many seconds to wait for a connection to 170 * be established. Available since PEAR DB 1.7.0. 171 * + new_link If set to true, causes subsequent calls to 172 * connect() to return a new connection link 173 * instead of the existing one. WARNING: this is 174 * not portable to other DBMS's. Available only 175 * if PHP is >= 4.3.0 and PEAR DB is >= 1.7.0. 176 * + options Command line options to be sent to the server. 177 * Available since PEAR DB 1.6.4. 178 * + service Specifies a service name in pg_service.conf that 179 * holds additional connection parameters. 180 * Available since PEAR DB 1.7.0. 181 * + sslmode How should SSL be used when connecting? Values: 182 * disable, allow, prefer or require. 183 * Available since PEAR DB 1.7.0. 184 * + tty This was used to specify where to send server 185 * debug output. Available since PEAR DB 1.6.4. 186 * 187 * Example of connecting to a new link via a socket: 188 * <code> 189 * require_once 'DB.php'; 190 * 191 * $dsn = 'pgsql://user:pass@unix(/tmp)/dbname?new_link=true'; 192 * $options = array( 193 * 'portability' => DB_PORTABILITY_ALL, 194 * ); 195 * 196 * $db = DB::connect($dsn, $options); 197 * if (PEAR::isError($db)) { 198 * die($db->getMessage()); 199 * } 200 * </code> 201 * 202 * @param array $dsn the data source name 203 * @param bool $persistent should the connection be persistent? 204 * 205 * @return int DB_OK on success. A DB_Error object on failure. 206 * 207 * @link http://www.postgresql.org/docs/current/static/libpq.html#LIBPQ-CONNECT 208 */ 209 function connect($dsn, $persistent = false) 210 { 211 if (!PEAR::loadExtension('pgsql')) { 212 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND); 213 } 214 215 $this->dsn = $dsn; 216 if ($dsn['dbsyntax']) { 217 $this->dbsyntax = $dsn['dbsyntax']; 218 } 219 220 $protocol = $dsn['protocol'] ? $dsn['protocol'] : 'tcp'; 221 222 $params = array(''); 223 if ($protocol == 'tcp') { 224 if ($dsn['hostspec']) { 225 $params[0] .= 'host=' . $dsn['hostspec']; 226 } 227 if ($dsn['port']) { 228 $params[0] .= ' port=' . $dsn['port']; 229 } 230 } elseif ($protocol == 'unix') { 231 // Allow for pg socket in non-standard locations. 232 if ($dsn['socket']) { 233 $params[0] .= 'host=' . $dsn['socket']; 234 } 235 if ($dsn['port']) { 236 $params[0] .= ' port=' . $dsn['port']; 237 } 238 } 239 if ($dsn['database']) { 240 $params[0] .= ' dbname=\'' . addslashes($dsn['database']) . '\''; 241 } 242 if ($dsn['username']) { 243 $params[0] .= ' user=\'' . addslashes($dsn['username']) . '\''; 244 } 245 if ($dsn['password']) { 246 $params[0] .= ' password=\'' . addslashes($dsn['password']) . '\''; 247 } 248 if (!empty($dsn['options'])) { 249 $params[0] .= ' options=' . $dsn['options']; 250 } 251 if (!empty($dsn['tty'])) { 252 $params[0] .= ' tty=' . $dsn['tty']; 253 } 254 if (!empty($dsn['connect_timeout'])) { 255 $params[0] .= ' connect_timeout=' . $dsn['connect_timeout']; 256 } 257 if (!empty($dsn['sslmode'])) { 258 $params[0] .= ' sslmode=' . $dsn['sslmode']; 259 } 260 if (!empty($dsn['service'])) { 261 $params[0] .= ' service=' . $dsn['service']; 262 } 263 264 if (isset($dsn['new_link']) 265 && ($dsn['new_link'] == 'true' || $dsn['new_link'] === true)) 266 { 267 if (version_compare(phpversion(), '4.3.0', '>=')) { 268 $params[] = PGSQL_CONNECT_FORCE_NEW; 269 } 270 } 271 272 $connect_function = $persistent ? 'pg_pconnect' : 'pg_connect'; 273 274 $ini = ini_get('track_errors'); 275 $php_errormsg = ''; 276 if ($ini) { 277 $this->connection = @call_user_func_array($connect_function, 278 $params); 279 } else { 280 @ini_set('track_errors', 1); 281 $this->connection = @call_user_func_array($connect_function, 282 $params); 283 @ini_set('track_errors', $ini); 284 } 285 286 if (!$this->connection) { 287 return $this->raiseError(DB_ERROR_CONNECT_FAILED, 288 null, null, null, 289 $php_errormsg); 290 } 291 return DB_OK; 292 } 293 294 // }}} 295 // {{{ disconnect() 296 297 /** 298 * Disconnects from the database server 299 * 300 * @return bool TRUE on success, FALSE on failure 301 */ 302 function disconnect() 303 { 304 $ret = @pg_close($this->connection); 305 $this->connection = null; 306 return $ret; 307 } 308 309 // }}} 310 // {{{ simpleQuery() 311 312 /** 313 * Sends a query to the database server 314 * 315 * @param string the SQL query string 316 * 317 * @return mixed + a PHP result resrouce for successful SELECT queries 318 * + the DB_OK constant for other successful queries 319 * + a DB_Error object on failure 320 */ 321 function simpleQuery($query) 322 { 323 $ismanip = $this->_checkManip($query); 324 $this->last_query = $query; 325 $query = $this->modifyQuery($query); 326 if (!$this->autocommit && $ismanip) { 327 if ($this->transaction_opcount == 0) { 328 $result = @pg_exec($this->connection, 'begin;'); 329 if (!$result) { 330 return $this->pgsqlRaiseError(); 331 } 332 } 333 $this->transaction_opcount++; 334 } 335 $result = @pg_exec($this->connection, $query); 336 if (!$result) { 337 return $this->pgsqlRaiseError(); 338 } 339 340 /* 341 * Determine whether queries produce affected rows, result or nothing. 342 * 343 * This logic was introduced in version 1.1 of the file by ssb, 344 * though the regex has been modified slightly since then. 345 * 346 * PostgreSQL commands: 347 * ABORT, ALTER, BEGIN, CLOSE, CLUSTER, COMMIT, COPY, 348 * CREATE, DECLARE, DELETE, DROP TABLE, EXPLAIN, FETCH, 349 * GRANT, INSERT, LISTEN, LOAD, LOCK, MOVE, NOTIFY, RESET, 350 * REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SHOW, 351 * UNLISTEN, UPDATE, VACUUM 352 */ 353 if ($ismanip) { 354 $this->affected = @pg_affected_rows($result); 355 return DB_OK; 356 } elseif (preg_match('/^\s*\(*\s*(SELECT|EXPLAIN|FETCH|SHOW)\s/si', 357 $query)) 358 { 359 $this->row[(int)$result] = 0; // reset the row counter. 360 $numrows = $this->numRows($result); 361 if (is_object($numrows)) { 362 return $numrows; 363 } 364 $this->_num_rows[(int)$result] = $numrows; 365 $this->affected = 0; 366 return $result; 367 } else { 368 $this->affected = 0; 369 return DB_OK; 370 } 371 } 372 373 // }}} 374 // {{{ nextResult() 375 376 /** 377 * Move the internal pgsql result pointer to the next available result 378 * 379 * @param a valid fbsql result resource 380 * 381 * @access public 382 * 383 * @return true if a result is available otherwise return false 384 */ 385 function nextResult($result) 386 { 387 return false; 388 } 389 390 // }}} 391 // {{{ fetchInto() 392 393 /** 394 * Places a row from the result set into the given array 395 * 396 * Formating of the array and the data therein are configurable. 397 * See DB_result::fetchInto() for more information. 398 * 399 * This method is not meant to be called directly. Use 400 * DB_result::fetchInto() instead. It can't be declared "protected" 401 * because DB_result is a separate object. 402 * 403 * @param resource $result the query result resource 404 * @param array $arr the referenced array to put the data in 405 * @param int $fetchmode how the resulting array should be indexed 406 * @param int $rownum the row number to fetch (0 = first row) 407 * 408 * @return mixed DB_OK on success, NULL when the end of a result set is 409 * reached or on failure 410 * 411 * @see DB_result::fetchInto() 412 */ 413 function fetchInto($result, &$arr, $fetchmode, $rownum = null) 414 { 415 $result_int = (int)$result; 416 $rownum = ($rownum !== null) ? $rownum : $this->row[$result_int]; 417 if ($rownum >= $this->_num_rows[$result_int]) { 418 return null; 419 } 420 if ($fetchmode & DB_FETCHMODE_ASSOC) { 421 $arr = @pg_fetch_array($result, $rownum, PGSQL_ASSOC); 422 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) { 423 $arr = array_change_key_case($arr, CASE_LOWER); 424 } 425 } else { 426 $arr = @pg_fetch_row($result, $rownum); 427 } 428 if (!$arr) { 429 return null; 430 } 431 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) { 432 $this->_rtrimArrayValues($arr); 433 } 434 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) { 435 $this->_convertNullArrayValuesToEmpty($arr); 436 } 437 $this->row[$result_int] = ++$rownum; 438 return DB_OK; 439 } 440 441 // }}} 442 // {{{ freeResult() 443 444 /** 445 * Deletes the result set and frees the memory occupied by the result set 446 * 447 * This method is not meant to be called directly. Use 448 * DB_result::free() instead. It can't be declared "protected" 449 * because DB_result is a separate object. 450 * 451 * @param resource $result PHP's query result resource 452 * 453 * @return bool TRUE on success, FALSE if $result is invalid 454 * 455 * @see DB_result::free() 456 */ 457 function freeResult($result) 458 { 459 if (is_resource($result)) { 460 unset($this->row[(int)$result]); 461 unset($this->_num_rows[(int)$result]); 462 $this->affected = 0; 463 return @pg_freeresult($result); 464 } 465 return false; 466 } 467 468 // }}} 469 // {{{ quote() 470 471 /** 472 * @deprecated Deprecated in release 1.6.0 473 * @internal 474 */ 475 function quote($str) 476 { 477 return $this->quoteSmart($str); 478 } 479 480 // }}} 481 // {{{ quoteBoolean() 482 483 /** 484 * Formats a boolean value for use within a query in a locale-independent 485 * manner. 486 * 487 * @param boolean the boolean value to be quoted. 488 * @return string the quoted string. 489 * @see DB_common::quoteSmart() 490 * @since Method available since release 1.7.8. 491 */ 492 function quoteBoolean($boolean) { 493 return $boolean ? 'TRUE' : 'FALSE'; 494 } 495 496 // }}} 497 // {{{ escapeSimple() 498 499 /** 500 * Escapes a string according to the current DBMS's standards 501 * 502 * {@internal PostgreSQL treats a backslash as an escape character, 503 * so they are escaped as well. 504 * 505 * @param string $str the string to be escaped 506 * 507 * @return string the escaped string 508 * 509 * @see DB_common::quoteSmart() 510 * @since Method available since Release 1.6.0 511 */ 512 function escapeSimple($str) 513 { 514 if (function_exists('pg_escape_string')) { 515 /* This fixes an undocumented BC break in PHP 5.2.0 which changed 516 * the prototype of pg_escape_string. I'm not thrilled about having 517 * to sniff the PHP version, quite frankly, but it's the only way 518 * to deal with the problem. Revision 1.331.2.13.2.10 on 519 * php-src/ext/pgsql/pgsql.c (PHP_5_2 branch) is to blame, for the 520 * record. */ 521 if (version_compare(PHP_VERSION, '5.2.0', '>=')) { 522 return pg_escape_string($this->connection, $str); 523 } else { 524 return pg_escape_string($str); 525 } 526 } else { 527 return str_replace("'", "''", str_replace('\\', '\\\\', $str)); 528 } 529 } 530 531 // }}} 532 // {{{ numCols() 533 534 /** 535 * Gets the number of columns in a result set 536 * 537 * This method is not meant to be called directly. Use 538 * DB_result::numCols() instead. It can't be declared "protected" 539 * because DB_result is a separate object. 540 * 541 * @param resource $result PHP's query result resource 542 * 543 * @return int the number of columns. A DB_Error object on failure. 544 * 545 * @see DB_result::numCols() 546 */ 547 function numCols($result) 548 { 549 $cols = @pg_numfields($result); 550 if (!$cols) { 551 return $this->pgsqlRaiseError(); 552 } 553 return $cols; 554 } 555 556 // }}} 557 // {{{ numRows() 558 559 /** 560 * Gets the number of rows in a result set 561 * 562 * This method is not meant to be called directly. Use 563 * DB_result::numRows() instead. It can't be declared "protected" 564 * because DB_result is a separate object. 565 * 566 * @param resource $result PHP's query result resource 567 * 568 * @return int the number of rows. A DB_Error object on failure. 569 * 570 * @see DB_result::numRows() 571 */ 572 function numRows($result) 573 { 574 $rows = @pg_numrows($result); 575 if ($rows === null) { 576 return $this->pgsqlRaiseError(); 577 } 578 return $rows; 579 } 580 581 // }}} 582 // {{{ autoCommit() 583 584 /** 585 * Enables or disables automatic commits 586 * 587 * @param bool $onoff true turns it on, false turns it off 588 * 589 * @return int DB_OK on success. A DB_Error object if the driver 590 * doesn't support auto-committing transactions. 591 */ 592 function autoCommit($onoff = false) 593 { 594 // XXX if $this->transaction_opcount > 0, we should probably 595 // issue a warning here. 596 $this->autocommit = $onoff ? true : false; 597 return DB_OK; 598 } 599 600 // }}} 601 // {{{ commit() 602 603 /** 604 * Commits the current transaction 605 * 606 * @return int DB_OK on success. A DB_Error object on failure. 607 */ 608 function commit() 609 { 610 if ($this->transaction_opcount > 0) { 611 // (disabled) hack to shut up error messages from libpq.a 612 //@fclose(@fopen("php://stderr", "w")); 613 $result = @pg_exec($this->connection, 'end;'); 614 $this->transaction_opcount = 0; 615 if (!$result) { 616 return $this->pgsqlRaiseError(); 617 } 618 } 619 return DB_OK; 620 } 621 622 // }}} 623 // {{{ rollback() 624 625 /** 626 * Reverts the current transaction 627 * 628 * @return int DB_OK on success. A DB_Error object on failure. 629 */ 630 function rollback() 631 { 632 if ($this->transaction_opcount > 0) { 633 $result = @pg_exec($this->connection, 'abort;'); 634 $this->transaction_opcount = 0; 635 if (!$result) { 636 return $this->pgsqlRaiseError(); 637 } 638 } 639 return DB_OK; 640 } 641 642 // }}} 643 // {{{ affectedRows() 644 645 /** 646 * Determines the number of rows affected by a data maniuplation query 647 * 648 * 0 is returned for queries that don't manipulate data. 649 * 650 * @return int the number of rows. A DB_Error object on failure. 651 */ 652 function affectedRows() 653 { 654 return $this->affected; 655 } 656 657 // }}} 658 // {{{ nextId() 659 660 /** 661 * Returns the next free id in a sequence 662 * 663 * @param string $seq_name name of the sequence 664 * @param boolean $ondemand when true, the seqence is automatically 665 * created if it does not exist 666 * 667 * @return int the next id number in the sequence. 668 * A DB_Error object on failure. 669 * 670 * @see DB_common::nextID(), DB_common::getSequenceName(), 671 * DB_pgsql::createSequence(), DB_pgsql::dropSequence() 672 */ 673 function nextId($seq_name, $ondemand = true) 674 { 675 $seqname = $this->getSequenceName($seq_name); 676 $repeat = false; 677 do { 678 $this->pushErrorHandling(PEAR_ERROR_RETURN); 679 $result = $this->query("SELECT NEXTVAL('${seqname}')"); 680 $this->popErrorHandling(); 681 if ($ondemand && DB::isError($result) && 682 $result->getCode() == DB_ERROR_NOSUCHTABLE) { 683 $repeat = true; 684 $this->pushErrorHandling(PEAR_ERROR_RETURN); 685 $result = $this->createSequence($seq_name); 686 $this->popErrorHandling(); 687 if (DB::isError($result)) { 688 return $this->raiseError($result); 689 } 690 } else { 691 $repeat = false; 692 } 693 } while ($repeat); 694 if (DB::isError($result)) { 695 return $this->raiseError($result); 696 } 697 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED); 698 $result->free(); 699 return $arr[0]; 700 } 701 702 // }}} 703 // {{{ createSequence() 704 705 /** 706 * Creates a new sequence 707 * 708 * @param string $seq_name name of the new sequence 709 * 710 * @return int DB_OK on success. A DB_Error object on failure. 711 * 712 * @see DB_common::createSequence(), DB_common::getSequenceName(), 713 * DB_pgsql::nextID(), DB_pgsql::dropSequence() 714 */ 715 function createSequence($seq_name) 716 { 717 $seqname = $this->getSequenceName($seq_name); 718 $result = $this->query("CREATE SEQUENCE ${seqname}"); 719 return $result; 720 } 721 722 // }}} 723 // {{{ dropSequence() 724 725 /** 726 * Deletes a sequence 727 * 728 * @param string $seq_name name of the sequence to be deleted 729 * 730 * @return int DB_OK on success. A DB_Error object on failure. 731 * 732 * @see DB_common::dropSequence(), DB_common::getSequenceName(), 733 * DB_pgsql::nextID(), DB_pgsql::createSequence() 734 */ 735 function dropSequence($seq_name) 736 { 737 return $this->query('DROP SEQUENCE ' 738 . $this->getSequenceName($seq_name)); 739 } 740 741 // }}} 742 // {{{ modifyLimitQuery() 743 744 /** 745 * Adds LIMIT clauses to a query string according to current DBMS standards 746 * 747 * @param string $query the query to modify 748 * @param int $from the row to start to fetching (0 = the first row) 749 * @param int $count the numbers of rows to fetch 750 * @param mixed $params array, string or numeric data to be used in 751 * execution of the statement. Quantity of items 752 * passed must match quantity of placeholders in 753 * query: meaning 1 placeholder for non-array 754 * parameters or 1 placeholder per array element. 755 * 756 * @return string the query string with LIMIT clauses added 757 * 758 * @access protected 759 */ 760 function modifyLimitQuery($query, $from, $count, $params = array()) 761 { 762 return "$query LIMIT $count OFFSET $from"; 763 } 764 765 // }}} 766 // {{{ pgsqlRaiseError() 767 768 /** 769 * Produces a DB_Error object regarding the current problem 770 * 771 * @param int $errno if the error is being manually raised pass a 772 * DB_ERROR* constant here. If this isn't passed 773 * the error information gathered from the DBMS. 774 * 775 * @return object the DB_Error object 776 * 777 * @see DB_common::raiseError(), 778 * DB_pgsql::errorNative(), DB_pgsql::errorCode() 779 */ 780 function pgsqlRaiseError($errno = null) 781 { 782 $native = $this->errorNative(); 783 if (!$native) { 784 $native = 'Database connection has been lost.'; 785 $errno = DB_ERROR_CONNECT_FAILED; 786 } 787 if ($errno === null) { 788 $errno = $this->errorCode($native); 789 } 790 return $this->raiseError($errno, null, null, null, $native); 791 } 792 793 // }}} 794 // {{{ errorNative() 795 796 /** 797 * Gets the DBMS' native error message produced by the last query 798 * 799 * {@internal Error messages are used instead of error codes 800 * in order to support older versions of PostgreSQL.}} 801 * 802 * @return string the DBMS' error message 803 */ 804 function errorNative() 805 { 806 return @pg_errormessage($this->connection); 807 } 808 809 // }}} 810 // {{{ errorCode() 811 812 /** 813 * Determines PEAR::DB error code from the database's text error message. 814 * 815 * @param string $errormsg error message returned from the database 816 * @return integer an error number from a DB error constant 817 */ 818 function errorCode($errormsg) 819 { 820 static $error_regexps; 821 if (!isset($error_regexps)) { 822 $error_regexps = array( 823 '/column .* (of relation .*)?does not exist/i' 824 => DB_ERROR_NOSUCHFIELD, 825 '/(relation|sequence|table).*does not exist|class .* not found/i' 826 => DB_ERROR_NOSUCHTABLE, 827 '/index .* does not exist/' 828 => DB_ERROR_NOT_FOUND, 829 '/relation .* already exists/i' 830 => DB_ERROR_ALREADY_EXISTS, 831 '/(divide|division) by zero$/i' 832 => DB_ERROR_DIVZERO, 833 '/pg_atoi: error in .*: can\'t parse /i' 834 => DB_ERROR_INVALID_NUMBER, 835 '/invalid input syntax for( type)? (integer|numeric)/i' 836 => DB_ERROR_INVALID_NUMBER, 837 '/value .* is out of range for type \w*int/i' 838 => DB_ERROR_INVALID_NUMBER, 839 '/integer out of range/i' 840 => DB_ERROR_INVALID_NUMBER, 841 '/value too long for type character/i' 842 => DB_ERROR_INVALID, 843 '/attribute .* not found|relation .* does not have attribute/i' 844 => DB_ERROR_NOSUCHFIELD, 845 '/column .* specified in USING clause does not exist in (left|right) table/i' 846 => DB_ERROR_NOSUCHFIELD, 847 '/parser: parse error at or near/i' 848 => DB_ERROR_SYNTAX, 849 '/syntax error at/' 850 => DB_ERROR_SYNTAX, 851 '/column reference .* is ambiguous/i' 852 => DB_ERROR_SYNTAX, 853 '/permission denied/' 854 => DB_ERROR_ACCESS_VIOLATION, 855 '/violates not-null constraint/' 856 => DB_ERROR_CONSTRAINT_NOT_NULL, 857 '/violates [\w ]+ constraint/' 858 => DB_ERROR_CONSTRAINT, 859 '/referential integrity violation/' 860 => DB_ERROR_CONSTRAINT, 861 '/more expressions than target columns/i' 862 => DB_ERROR_VALUE_COUNT_ON_ROW, 863 ); 864 } 865 foreach ($error_regexps as $regexp => $code) { 866 if (preg_match($regexp, $errormsg)) { 867 return $code; 868 } 869 } 870 // Fall back to DB_ERROR if there was no mapping. 871 return DB_ERROR; 872 } 873 874 // }}} 875 // {{{ tableInfo() 876 877 /** 878 * Returns information about a table or a result set 879 * 880 * NOTE: only supports 'table' and 'flags' if <var>$result</var> 881 * is a table name. 882 * 883 * @param object|string $result DB_result object from a query or a 884 * string containing the name of a table. 885 * While this also accepts a query result 886 * resource identifier, this behavior is 887 * deprecated. 888 * @param int $mode a valid tableInfo mode 889 * 890 * @return array an associative array with the information requested. 891 * A DB_Error object on failure. 892 * 893 * @see DB_common::tableInfo() 894 */ 895 function tableInfo($result, $mode = null) 896 { 897 if (is_string($result)) { 898 /* 899 * Probably received a table name. 900 * Create a result resource identifier. 901 */ 902 $id = @pg_exec($this->connection, "SELECT * FROM $result LIMIT 0"); 903 $got_string = true; 904 } elseif (isset($result->result)) { 905 /* 906 * Probably received a result object. 907 * Extract the result resource identifier. 908 */ 909 $id = $result->result; 910 $got_string = false; 911 } else { 912 /* 913 * Probably received a result resource identifier. 914 * Copy it. 915 * Deprecated. Here for compatibility only. 916 */ 917 $id = $result; 918 $got_string = false; 919 } 920 921 if (!is_resource($id)) { 922 return $this->pgsqlRaiseError(DB_ERROR_NEED_MORE_DATA); 923 } 924 925 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) { 926 $case_func = 'strtolower'; 927 } else { 928 $case_func = 'strval'; 929 } 930 931 $count = @pg_numfields($id); 932 $res = array(); 933 934 if ($mode) { 935 $res['num_fields'] = $count; 936 } 937 938 for ($i = 0; $i < $count; $i++) { 939 $res[$i] = array( 940 'table' => $got_string ? $case_func($result) : '', 941 'name' => $case_func(@pg_fieldname($id, $i)), 942 'type' => @pg_fieldtype($id, $i), 943 'len' => @pg_fieldsize($id, $i), 944 'flags' => $got_string 945 ? $this->_pgFieldFlags($id, $i, $result) 946 : '', 947 ); 948 if ($mode & DB_TABLEINFO_ORDER) { 949 $res['order'][$res[$i]['name']] = $i; 950 } 951 if ($mode & DB_TABLEINFO_ORDERTABLE) { 952 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i; 953 } 954 } 955 956 // free the result only if we were called on a table 957 if ($got_string) { 958 @pg_freeresult($id); 959 } 960 return $res; 961 } 962 963 // }}} 964 // {{{ _pgFieldFlags() 965 966 /** 967 * Get a column's flags 968 * 969 * Supports "not_null", "default_value", "primary_key", "unique_key" 970 * and "multiple_key". The default value is passed through 971 * rawurlencode() in case there are spaces in it. 972 * 973 * @param int $resource the PostgreSQL result identifier 974 * @param int $num_field the field number 975 * 976 * @return string the flags 977 * 978 * @access private 979 */ 980 function _pgFieldFlags($resource, $num_field, $table_name) 981 { 982 $field_name = @pg_fieldname($resource, $num_field); 983 984 // Check if there's a schema in $table_name and update things 985 // accordingly. 986 $from = 'pg_attribute f, pg_class tab, pg_type typ'; 987 if (strpos($table_name, '.') !== false) { 988 $from .= ', pg_namespace nsp'; 989 list($schema, $table) = explode('.', $table_name); 990 $tableWhere = "tab.relname = '$table' AND tab.relnamespace = nsp.oid AND nsp.nspname = '$schema'"; 991 } else { 992 $tableWhere = "tab.relname = '$table_name'"; 993 } 994 995 $result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef 996 FROM $from 997 WHERE tab.relname = typ.typname 998 AND typ.typrelid = f.attrelid 999 AND f.attname = '$field_name' 1000 AND $tableWhere"); 1001 if (@pg_numrows($result) > 0) { 1002 $row = @pg_fetch_row($result, 0); 1003 $flags = ($row[0] == 't') ? 'not_null ' : ''; 1004 1005 if ($row[1] == 't') { 1006 $result = @pg_exec($this->connection, "SELECT a.adsrc 1007 FROM $from, pg_attrdef a 1008 WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid 1009 AND f.attrelid = a.adrelid AND f.attname = '$field_name' 1010 AND $tableWhere AND f.attnum = a.adnum"); 1011 $row = @pg_fetch_row($result, 0); 1012 $num = preg_replace("/'(.*)'::\w+/", "\\1", $row[0]); 1013 $flags .= 'default_' . rawurlencode($num) . ' '; 1014 } 1015 } else { 1016 $flags = ''; 1017 } 1018 $result = @pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey 1019 FROM $from, pg_index i 1020 WHERE tab.relname = typ.typname 1021 AND typ.typrelid = f.attrelid 1022 AND f.attrelid = i.indrelid 1023 AND f.attname = '$field_name' 1024 AND $tableWhere"); 1025 $count = @pg_numrows($result); 1026 1027 for ($i = 0; $i < $count ; $i++) { 1028 $row = @pg_fetch_row($result, $i); 1029 $keys = explode(' ', $row[2]); 1030 1031 if (in_array($num_field + 1, $keys)) { 1032 $flags .= ($row[0] == 't' && $row[1] == 'f') ? 'unique_key ' : ''; 1033 $flags .= ($row[1] == 't') ? 'primary_key ' : ''; 1034 if (count($keys) > 1) 1035 $flags .= 'multiple_key '; 1036 } 1037 } 1038 1039 return trim($flags); 1040 } 1041 1042 // }}} 1043 // {{{ getSpecialQuery() 1044 1045 /** 1046 * Obtains the query string needed for listing a given type of objects 1047 * 1048 * @param string $type the kind of objects you want to retrieve 1049 * 1050 * @return string the SQL query string or null if the driver doesn't 1051 * support the object type requested 1052 * 1053 * @access protected 1054 * @see DB_common::getListOf() 1055 */ 1056 function getSpecialQuery($type) 1057 { 1058 switch ($type) { 1059 case 'tables': 1060 return 'SELECT c.relname AS "Name"' 1061 . ' FROM pg_class c, pg_user u' 1062 . ' WHERE c.relowner = u.usesysid' 1063 . " AND c.relkind = 'r'" 1064 . ' AND NOT EXISTS' 1065 . ' (SELECT 1 FROM pg_views' 1066 . ' WHERE viewname = c.relname)' 1067 . " AND c.relname !~ '^(pg_|sql_)'" 1068 . ' UNION' 1069 . ' SELECT c.relname AS "Name"' 1070 . ' FROM pg_class c' 1071 . " WHERE c.relkind = 'r'" 1072 . ' AND NOT EXISTS' 1073 . ' (SELECT 1 FROM pg_views' 1074 . ' WHERE viewname = c.relname)' 1075 . ' AND NOT EXISTS' 1076 . ' (SELECT 1 FROM pg_user' 1077 . ' WHERE usesysid = c.relowner)' 1078 . " AND c.relname !~ '^pg_'"; 1079 case 'schema.tables': 1080 return "SELECT schemaname || '.' || tablename" 1081 . ' AS "Name"' 1082 . ' FROM pg_catalog.pg_tables' 1083 . ' WHERE schemaname NOT IN' 1084 . " ('pg_catalog', 'information_schema', 'pg_toast')"; 1085 case 'schema.views': 1086 return "SELECT schemaname || '.' || viewname from pg_views WHERE schemaname" 1087 . " NOT IN ('information_schema', 'pg_catalog')"; 1088 case 'views': 1089 // Table cols: viewname | viewowner | definition 1090 return 'SELECT viewname from pg_views WHERE schemaname' 1091 . " NOT IN ('information_schema', 'pg_catalog')"; 1092 case 'users': 1093 // cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil 1094 return 'SELECT usename FROM pg_user'; 1095 case 'databases': 1096 return 'SELECT datname FROM pg_database'; 1097 case 'functions': 1098 case 'procedures': 1099 return 'SELECT proname FROM pg_proc WHERE proowner <> 1'; 1100 default: 1101 return null; 1102 } 1103 } 1104 1105 // }}} 1106 1107} 1108 1109/* 1110 * Local variables: 1111 * tab-width: 4 1112 * c-basic-offset: 4 1113 * End: 1114 */ 1115 1116?> 1117