PageRenderTime 55ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 1ms

/lib/php/DB/pgsql.php

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