PageRenderTime 67ms CodeModel.GetById 27ms RepoModel.GetById 1ms app.codeStats 0ms

/common/libraries/plugin/pear/MDB2/Driver/Manager/pgsql.php

https://bitbucket.org/renaatdemuynck/chamilo
PHP | 1042 lines | 601 code | 105 blank | 336 comment | 102 complexity | c9a0a990ef8f4bf529ad423650095cf9 MD5 | raw file
Possible License(s): BSD-3-Clause, LGPL-2.1, LGPL-3.0, GPL-3.0, MIT, GPL-2.0
  1. <?php
  2. // +----------------------------------------------------------------------+
  3. // | PHP versions 4 and 5 |
  4. // +----------------------------------------------------------------------+
  5. // | Copyright (c) 1998-2008 Manuel Lemos, Tomas V.V.Cox, |
  6. // | Stig. S. Bakken, Lukas Smith |
  7. // | All rights reserved. |
  8. // +----------------------------------------------------------------------+
  9. // | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |
  10. // | API as well as database abstraction for PHP applications. |
  11. // | This LICENSE is in the BSD license style. |
  12. // | |
  13. // | Redistribution and use in source and binary forms, with or without |
  14. // | modification, are permitted provided that the following conditions |
  15. // | are met: |
  16. // | |
  17. // | Redistributions of source code must retain the above copyright |
  18. // | notice, this list of conditions and the following disclaimer. |
  19. // | |
  20. // | Redistributions in binary form must reproduce the above copyright |
  21. // | notice, this list of conditions and the following disclaimer in the |
  22. // | documentation and/or other materials provided with the distribution. |
  23. // | |
  24. // | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
  25. // | Lukas Smith nor the names of his contributors may be used to endorse |
  26. // | or promote products derived from this software without specific prior|
  27. // | written permission. |
  28. // | |
  29. // | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
  30. // | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
  31. // | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
  32. // | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
  33. // | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
  34. // | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
  35. // | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
  36. // | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
  37. // | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
  38. // | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
  39. // | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
  40. // | POSSIBILITY OF SUCH DAMAGE. |
  41. // +----------------------------------------------------------------------+
  42. // | Author: Paul Cooper <pgc@ucecom.com> |
  43. // +----------------------------------------------------------------------+
  44. //
  45. // $Id: pgsql.php 137 2009-11-09 13:24:37Z vanpouckesven $
  46. require_once 'MDB2/Driver/Manager/Common.php';
  47. /**
  48. * MDB2 MySQL driver for the management modules
  49. *
  50. * @package MDB2
  51. * @category Database
  52. * @author Paul Cooper <pgc@ucecom.com>
  53. */
  54. class MDB2_Driver_Manager_pgsql extends MDB2_Driver_Manager_Common
  55. {
  56. // {{{ createDatabase()
  57. /**
  58. * create a new database
  59. *
  60. * @param string $name name of the database that should be created
  61. * @param array $options array with charset info
  62. *
  63. * @return mixed MDB2_OK on success, a MDB2 error on failure
  64. * @access public
  65. */
  66. function createDatabase($name, $options = array())
  67. {
  68. $db = & $this->getDBInstance();
  69. if (PEAR :: isError($db))
  70. {
  71. return $db;
  72. }
  73. $name = $db->quoteIdentifier($name, true);
  74. $query = 'CREATE DATABASE ' . $name;
  75. if (! empty($options['charset']))
  76. {
  77. $query .= ' WITH ENCODING ' . $db->quote($options['charset'], 'text');
  78. }
  79. return $db->standaloneQuery($query, null, true);
  80. }
  81. // }}}
  82. // {{{ alterDatabase()
  83. /**
  84. * alter an existing database
  85. *
  86. * @param string $name name of the database that is intended to be changed
  87. * @param array $options array with name, owner info
  88. *
  89. * @return mixed MDB2_OK on success, a MDB2 error on failure
  90. * @access public
  91. */
  92. function alterDatabase($name, $options = array())
  93. {
  94. $db = & $this->getDBInstance();
  95. if (PEAR :: isError($db))
  96. {
  97. return $db;
  98. }
  99. $query = 'ALTER DATABASE ' . $db->quoteIdentifier($name, true);
  100. if (! empty($options['name']))
  101. {
  102. $query .= ' RENAME TO ' . $options['name'];
  103. }
  104. if (! empty($options['owner']))
  105. {
  106. $query .= ' OWNER TO ' . $options['owner'];
  107. }
  108. return $db->standaloneQuery($query, null, true);
  109. }
  110. // }}}
  111. // {{{ dropDatabase()
  112. /**
  113. * drop an existing database
  114. *
  115. * @param string $name name of the database that should be dropped
  116. * @return mixed MDB2_OK on success, a MDB2 error on failure
  117. * @access public
  118. */
  119. function dropDatabase($name)
  120. {
  121. $db = & $this->getDBInstance();
  122. if (PEAR :: isError($db))
  123. {
  124. return $db;
  125. }
  126. $name = $db->quoteIdentifier($name, true);
  127. $query = "DROP DATABASE $name";
  128. return $db->standaloneQuery($query, null, true);
  129. }
  130. // }}}
  131. // {{{ _getAdvancedFKOptions()
  132. /**
  133. * Return the FOREIGN KEY query section dealing with non-standard options
  134. * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
  135. *
  136. * @param array $definition
  137. * @return string
  138. * @access protected
  139. */
  140. function _getAdvancedFKOptions($definition)
  141. {
  142. $query = '';
  143. if (! empty($definition['match']))
  144. {
  145. $query .= ' MATCH ' . $definition['match'];
  146. }
  147. if (! empty($definition['onupdate']))
  148. {
  149. $query .= ' ON UPDATE ' . $definition['onupdate'];
  150. }
  151. if (! empty($definition['ondelete']))
  152. {
  153. $query .= ' ON DELETE ' . $definition['ondelete'];
  154. }
  155. if (! empty($definition['deferrable']))
  156. {
  157. $query .= ' DEFERRABLE';
  158. }
  159. else
  160. {
  161. $query .= ' NOT DEFERRABLE';
  162. }
  163. if (! empty($definition['initiallydeferred']))
  164. {
  165. $query .= ' INITIALLY DEFERRED';
  166. }
  167. else
  168. {
  169. $query .= ' INITIALLY IMMEDIATE';
  170. }
  171. return $query;
  172. }
  173. // }}}
  174. // {{{ truncateTable()
  175. /**
  176. * Truncate an existing table (if the TRUNCATE TABLE syntax is not supported,
  177. * it falls back to a DELETE FROM TABLE query)
  178. *
  179. * @param string $name name of the table that should be truncated
  180. * @return mixed MDB2_OK on success, a MDB2 error on failure
  181. * @access public
  182. */
  183. function truncateTable($name)
  184. {
  185. $db = & $this->getDBInstance();
  186. if (PEAR :: isError($db))
  187. {
  188. return $db;
  189. }
  190. $name = $db->quoteIdentifier($name, true);
  191. return $db->exec("TRUNCATE TABLE $name");
  192. }
  193. // }}}
  194. // {{{ vacuum()
  195. /**
  196. * Optimize (vacuum) all the tables in the db (or only the specified table)
  197. * and optionally run ANALYZE.
  198. *
  199. * @param string $table table name (all the tables if empty)
  200. * @param array $options an array with driver-specific options:
  201. * - timeout [int] (in seconds) [mssql-only]
  202. * - analyze [boolean] [pgsql and mysql]
  203. * - full [boolean] [pgsql-only]
  204. * - freeze [boolean] [pgsql-only]
  205. *
  206. * @return mixed MDB2_OK success, a MDB2 error on failure
  207. * @access public
  208. */
  209. function vacuum($table = null, $options = array())
  210. {
  211. $db = & $this->getDBInstance();
  212. if (PEAR :: isError($db))
  213. {
  214. return $db;
  215. }
  216. $query = 'VACUUM';
  217. if (! empty($options['full']))
  218. {
  219. $query .= ' FULL';
  220. }
  221. if (! empty($options['freeze']))
  222. {
  223. $query .= ' FREEZE';
  224. }
  225. if (! empty($options['analyze']))
  226. {
  227. $query .= ' ANALYZE';
  228. }
  229. if (! empty($table))
  230. {
  231. $query .= ' ' . $db->quoteIdentifier($table, true);
  232. }
  233. return $db->exec($query);
  234. }
  235. // }}}
  236. // {{{ alterTable()
  237. /**
  238. * alter an existing table
  239. *
  240. * @param string $name name of the table that is intended to be changed.
  241. * @param array $changes associative array that contains the details of each type
  242. * of change that is intended to be performed. The types of
  243. * changes that are currently supported are defined as follows:
  244. *
  245. * name
  246. *
  247. * New name for the table.
  248. *
  249. * add
  250. *
  251. * Associative array with the names of fields to be added as
  252. * indexes of the array. The value of each entry of the array
  253. * should be set to another associative array with the properties
  254. * of the fields to be added. The properties of the fields should
  255. * be the same as defined by the MDB2 parser.
  256. *
  257. *
  258. * remove
  259. *
  260. * Associative array with the names of fields to be removed as indexes
  261. * of the array. Currently the values assigned to each entry are ignored.
  262. * An empty array should be used for future compatibility.
  263. *
  264. * rename
  265. *
  266. * Associative array with the names of fields to be renamed as indexes
  267. * of the array. The value of each entry of the array should be set to
  268. * another associative array with the entry named name with the new
  269. * field name and the entry named Declaration that is expected to contain
  270. * the portion of the field declaration already in DBMS specific SQL code
  271. * as it is used in the CREATE TABLE statement.
  272. *
  273. * change
  274. *
  275. * Associative array with the names of the fields to be changed as indexes
  276. * of the array. Keep in mind that if it is intended to change either the
  277. * name of a field and any other properties, the change array entries
  278. * should have the new names of the fields as array indexes.
  279. *
  280. * The value of each entry of the array should be set to another associative
  281. * array with the properties of the fields to that are meant to be changed as
  282. * array entries. These entries should be assigned to the new values of the
  283. * respective properties. The properties of the fields should be the same
  284. * as defined by the MDB2 parser.
  285. *
  286. * Example
  287. * array(
  288. * 'name' => 'userlist',
  289. * 'add' => array(
  290. * 'quota' => array(
  291. * 'type' => 'integer',
  292. * 'unsigned' => 1
  293. * )
  294. * ),
  295. * 'remove' => array(
  296. * 'file_limit' => array(),
  297. * 'time_limit' => array()
  298. * ),
  299. * 'change' => array(
  300. * 'name' => array(
  301. * 'length' => '20',
  302. * 'definition' => array(
  303. * 'type' => 'text',
  304. * 'length' => 20,
  305. * ),
  306. * )
  307. * ),
  308. * 'rename' => array(
  309. * 'sex' => array(
  310. * 'name' => 'gender',
  311. * 'definition' => array(
  312. * 'type' => 'text',
  313. * 'length' => 1,
  314. * 'default' => 'M',
  315. * ),
  316. * )
  317. * )
  318. * )
  319. *
  320. * @param boolean $check indicates whether the function should just check if the DBMS driver
  321. * can perform the requested table alterations if the value is true or
  322. * actually perform them otherwise.
  323. * @access public
  324. *
  325. * @return mixed MDB2_OK on success, a MDB2 error on failure
  326. */
  327. function alterTable($name, $changes, $check)
  328. {
  329. $db = & $this->getDBInstance();
  330. if (PEAR :: isError($db))
  331. {
  332. return $db;
  333. }
  334. foreach ($changes as $change_name => $change)
  335. {
  336. switch ($change_name)
  337. {
  338. case 'add' :
  339. case 'remove' :
  340. case 'change' :
  341. case 'name' :
  342. case 'rename' :
  343. break;
  344. default :
  345. return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null, 'change type "' . $change_name . '\" not yet supported', __FUNCTION__);
  346. }
  347. }
  348. if ($check)
  349. {
  350. return MDB2_OK;
  351. }
  352. $name = $db->quoteIdentifier($name, true);
  353. if (! empty($changes['remove']) && is_array($changes['remove']))
  354. {
  355. foreach ($changes['remove'] as $field_name => $field)
  356. {
  357. $field_name = $db->quoteIdentifier($field_name, true);
  358. $query = 'DROP ' . $field_name;
  359. $result = $db->exec("ALTER TABLE $name $query");
  360. if (PEAR :: isError($result))
  361. {
  362. return $result;
  363. }
  364. }
  365. }
  366. if (! empty($changes['rename']) && is_array($changes['rename']))
  367. {
  368. foreach ($changes['rename'] as $field_name => $field)
  369. {
  370. $field_name = $db->quoteIdentifier($field_name, true);
  371. $result = $db->exec("ALTER TABLE $name RENAME COLUMN $field_name TO " . $db->quoteIdentifier($field['name'], true));
  372. if (PEAR :: isError($result))
  373. {
  374. return $result;
  375. }
  376. }
  377. }
  378. if (! empty($changes['add']) && is_array($changes['add']))
  379. {
  380. foreach ($changes['add'] as $field_name => $field)
  381. {
  382. $query = 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field);
  383. $result = $db->exec("ALTER TABLE $name $query");
  384. if (PEAR :: isError($result))
  385. {
  386. return $result;
  387. }
  388. }
  389. }
  390. if (! empty($changes['change']) && is_array($changes['change']))
  391. {
  392. foreach ($changes['change'] as $field_name => $field)
  393. {
  394. $field_name = $db->quoteIdentifier($field_name, true);
  395. if (! empty($field['definition']['type']))
  396. {
  397. $server_info = $db->getServerVersion();
  398. if (PEAR :: isError($server_info))
  399. {
  400. return $server_info;
  401. }
  402. if (is_array($server_info) && $server_info['major'] < 8)
  403. {
  404. return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null, 'changing column type for "' . $change_name . '\" requires PostgreSQL 8.0 or above', __FUNCTION__);
  405. }
  406. $db->loadModule('Datatype', null, true);
  407. $type = $db->datatype->getTypeDeclaration($field['definition']);
  408. $query = "ALTER $field_name TYPE $type USING CAST($field_name AS $type)";
  409. $result = $db->exec("ALTER TABLE $name $query");
  410. if (PEAR :: isError($result))
  411. {
  412. return $result;
  413. }
  414. }
  415. if (array_key_exists('default', $field['definition']))
  416. {
  417. $query = "ALTER $field_name SET DEFAULT " . $db->quote($field['definition']['default'], $field['definition']['type']);
  418. $result = $db->exec("ALTER TABLE $name $query");
  419. if (PEAR :: isError($result))
  420. {
  421. return $result;
  422. }
  423. }
  424. if (! empty($field['definition']['notnull']))
  425. {
  426. $query = "ALTER $field_name " . ($field['definition']['notnull'] ? 'SET' : 'DROP') . ' NOT NULL';
  427. $result = $db->exec("ALTER TABLE $name $query");
  428. if (PEAR :: isError($result))
  429. {
  430. return $result;
  431. }
  432. }
  433. }
  434. }
  435. if (! empty($changes['name']))
  436. {
  437. $change_name = $db->quoteIdentifier($changes['name'], true);
  438. $result = $db->exec("ALTER TABLE $name RENAME TO " . $change_name);
  439. if (PEAR :: isError($result))
  440. {
  441. return $result;
  442. }
  443. }
  444. return MDB2_OK;
  445. }
  446. // }}}
  447. // {{{ listDatabases()
  448. /**
  449. * list all databases
  450. *
  451. * @return mixed array of database names on success, a MDB2 error on failure
  452. * @access public
  453. */
  454. function listDatabases()
  455. {
  456. $db = & $this->getDBInstance();
  457. if (PEAR :: isError($db))
  458. {
  459. return $db;
  460. }
  461. $query = 'SELECT datname FROM pg_database';
  462. $result2 = $db->standaloneQuery($query, array('text'), false);
  463. if (! MDB2 :: isResultCommon($result2))
  464. {
  465. return $result2;
  466. }
  467. $result = $result2->fetchCol();
  468. $result2->free();
  469. if (PEAR :: isError($result))
  470. {
  471. return $result;
  472. }
  473. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  474. {
  475. $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
  476. }
  477. return $result;
  478. }
  479. // }}}
  480. // {{{ listUsers()
  481. /**
  482. * list all users
  483. *
  484. * @return mixed array of user names on success, a MDB2 error on failure
  485. * @access public
  486. */
  487. function listUsers()
  488. {
  489. $db = & $this->getDBInstance();
  490. if (PEAR :: isError($db))
  491. {
  492. return $db;
  493. }
  494. $query = 'SELECT usename FROM pg_user';
  495. $result2 = $db->standaloneQuery($query, array('text'), false);
  496. if (! MDB2 :: isResultCommon($result2))
  497. {
  498. return $result2;
  499. }
  500. $result = $result2->fetchCol();
  501. $result2->free();
  502. return $result;
  503. }
  504. // }}}
  505. // {{{ listViews()
  506. /**
  507. * list all views in the current database
  508. *
  509. * @return mixed array of view names on success, a MDB2 error on failure
  510. * @access public
  511. */
  512. function listViews()
  513. {
  514. $db = & $this->getDBInstance();
  515. if (PEAR :: isError($db))
  516. {
  517. return $db;
  518. }
  519. $query = "SELECT viewname
  520. FROM pg_views
  521. WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  522. AND viewname !~ '^pg_'";
  523. $result = $db->queryCol($query);
  524. if (PEAR :: isError($result))
  525. {
  526. return $result;
  527. }
  528. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  529. {
  530. $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
  531. }
  532. return $result;
  533. }
  534. // }}}
  535. // {{{ listTableViews()
  536. /**
  537. * list the views in the database that reference a given table
  538. *
  539. * @param string table for which all referenced views should be found
  540. * @return mixed array of view names on success, a MDB2 error on failure
  541. * @access public
  542. */
  543. function listTableViews($table)
  544. {
  545. $db = & $this->getDBInstance();
  546. if (PEAR :: isError($db))
  547. {
  548. return $db;
  549. }
  550. $query = 'SELECT viewname FROM pg_views NATURAL JOIN pg_tables';
  551. $query .= ' WHERE tablename =' . $db->quote($table, 'text');
  552. $result = $db->queryCol($query);
  553. if (PEAR :: isError($result))
  554. {
  555. return $result;
  556. }
  557. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  558. {
  559. $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
  560. }
  561. return $result;
  562. }
  563. // }}}
  564. // {{{ listFunctions()
  565. /**
  566. * list all functions in the current database
  567. *
  568. * @return mixed array of function names on success, a MDB2 error on failure
  569. * @access public
  570. */
  571. function listFunctions()
  572. {
  573. $db = & $this->getDBInstance();
  574. if (PEAR :: isError($db))
  575. {
  576. return $db;
  577. }
  578. $query = "
  579. SELECT
  580. proname
  581. FROM
  582. pg_proc pr,
  583. pg_type tp
  584. WHERE
  585. tp.oid = pr.prorettype
  586. AND pr.proisagg = FALSE
  587. AND tp.typname <> 'trigger'
  588. AND pr.pronamespace IN
  589. (SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
  590. $result = $db->queryCol($query);
  591. if (PEAR :: isError($result))
  592. {
  593. return $result;
  594. }
  595. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  596. {
  597. $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
  598. }
  599. return $result;
  600. }
  601. // }}}
  602. // {{{ listTableTriggers()
  603. /**
  604. * list all triggers in the database that reference a given table
  605. *
  606. * @param string table for which all referenced triggers should be found
  607. * @return mixed array of trigger names on success, a MDB2 error on failure
  608. * @access public
  609. */
  610. function listTableTriggers($table = null)
  611. {
  612. $db = & $this->getDBInstance();
  613. if (PEAR :: isError($db))
  614. {
  615. return $db;
  616. }
  617. $query = 'SELECT trg.tgname AS trigger_name
  618. FROM pg_trigger trg,
  619. pg_class tbl
  620. WHERE trg.tgrelid = tbl.oid';
  621. if (! is_null($table))
  622. {
  623. $table = $db->quote(strtoupper($table), 'text');
  624. $query .= " AND tbl.relname = $table";
  625. }
  626. $result = $db->queryCol($query);
  627. if (PEAR :: isError($result))
  628. {
  629. return $result;
  630. }
  631. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  632. {
  633. $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
  634. }
  635. return $result;
  636. }
  637. // }}}
  638. // {{{ listTables()
  639. /**
  640. * list all tables in the current database
  641. *
  642. * @return mixed array of table names on success, a MDB2 error on failure
  643. * @access public
  644. */
  645. function listTables()
  646. {
  647. $db = & $this->getDBInstance();
  648. if (PEAR :: isError($db))
  649. {
  650. return $db;
  651. }
  652. // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
  653. $query = 'SELECT c.relname AS "Name"' . ' FROM pg_class c, pg_user u' . ' WHERE c.relowner = u.usesysid' . " AND c.relkind = 'r'" . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_views' . ' WHERE viewname = c.relname)' . " AND c.relname !~ '^(pg_|sql_)'" . ' UNION' . ' SELECT c.relname AS "Name"' . ' FROM pg_class c' . " WHERE c.relkind = 'r'" . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_views' . ' WHERE viewname = c.relname)' . ' AND NOT EXISTS' . ' (SELECT 1 FROM pg_user' . ' WHERE usesysid = c.relowner)' . " AND c.relname !~ '^pg_'";
  654. $result = $db->queryCol($query);
  655. if (PEAR :: isError($result))
  656. {
  657. return $result;
  658. }
  659. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  660. {
  661. $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
  662. }
  663. return $result;
  664. }
  665. // }}}
  666. // {{{ listTableFields()
  667. /**
  668. * list all fields in a table in the current database
  669. *
  670. * @param string $table name of table that should be used in method
  671. * @return mixed array of field names on success, a MDB2 error on failure
  672. * @access public
  673. */
  674. function listTableFields($table)
  675. {
  676. $db = & $this->getDBInstance();
  677. if (PEAR :: isError($db))
  678. {
  679. return $db;
  680. }
  681. list($schema, $table) = $this->splitTableSchema($table);
  682. $table = $db->quoteIdentifier($table, true);
  683. if (! empty($schema))
  684. {
  685. $table = $db->quoteIdentifier($schema, true) . '.' . $table;
  686. }
  687. $db->setLimit(1);
  688. $result2 = $db->query("SELECT * FROM $table");
  689. if (PEAR :: isError($result2))
  690. {
  691. return $result2;
  692. }
  693. $result = $result2->getColumnNames();
  694. $result2->free();
  695. if (PEAR :: isError($result))
  696. {
  697. return $result;
  698. }
  699. return array_flip($result);
  700. }
  701. // }}}
  702. // {{{ listTableIndexes()
  703. /**
  704. * list all indexes in a table
  705. *
  706. * @param string $table name of table that should be used in method
  707. * @return mixed array of index names on success, a MDB2 error on failure
  708. * @access public
  709. */
  710. function listTableIndexes($table)
  711. {
  712. $db = & $this->getDBInstance();
  713. if (PEAR :: isError($db))
  714. {
  715. return $db;
  716. }
  717. list($schema, $table) = $this->splitTableSchema($table);
  718. $table = $db->quote($table, 'text');
  719. $subquery = "SELECT indexrelid
  720. FROM pg_index
  721. LEFT JOIN pg_class ON pg_class.oid = pg_index.indrelid
  722. LEFT JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
  723. WHERE pg_class.relname = $table
  724. AND indisunique != 't'
  725. AND indisprimary != 't'";
  726. if (! empty($schema))
  727. {
  728. $subquery .= ' AND pg_namespace.nspname = ' . $db->quote($schema, 'text');
  729. }
  730. $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
  731. $indexes = $db->queryCol($query, 'text');
  732. if (PEAR :: isError($indexes))
  733. {
  734. return $indexes;
  735. }
  736. $result = array();
  737. foreach ($indexes as $index)
  738. {
  739. $index = $this->_fixIndexName($index);
  740. if (! empty($index))
  741. {
  742. $result[$index] = true;
  743. }
  744. }
  745. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  746. {
  747. $result = array_change_key_case($result, $db->options['field_case']);
  748. }
  749. return array_keys($result);
  750. }
  751. // }}}
  752. // {{{ dropConstraint()
  753. /**
  754. * drop existing constraint
  755. *
  756. * @param string $table name of table that should be used in method
  757. * @param string $name name of the constraint to be dropped
  758. * @param string $primary hint if the constraint is primary
  759. *
  760. * @return mixed MDB2_OK on success, a MDB2 error on failure
  761. * @access public
  762. */
  763. function dropConstraint($table, $name, $primary = false)
  764. {
  765. $db = & $this->getDBInstance();
  766. if (PEAR :: isError($db))
  767. {
  768. return $db;
  769. }
  770. // is it an UNIQUE index?
  771. $query = 'SELECT relname
  772. FROM pg_class
  773. WHERE oid IN (
  774. SELECT indexrelid
  775. FROM pg_index, pg_class
  776. WHERE pg_class.relname = ' . $db->quote($table, 'text') . '
  777. AND pg_class.oid = pg_index.indrelid
  778. AND indisunique = \'t\')
  779. EXCEPT
  780. SELECT conname
  781. FROM pg_constraint, pg_class
  782. WHERE pg_constraint.conrelid = pg_class.oid
  783. AND relname = ' . $db->quote($table, 'text');
  784. $unique = $db->queryCol($query, 'text');
  785. if (PEAR :: isError($unique) || empty($unique))
  786. {
  787. // not an UNIQUE index, maybe a CONSTRAINT
  788. return parent :: dropConstraint($table, $name, $primary);
  789. }
  790. if (in_array($name, $unique))
  791. {
  792. return $db->exec('DROP INDEX ' . $db->quoteIdentifier($name, true));
  793. }
  794. $idxname = $db->getIndexName($name);
  795. if (in_array($idxname, $unique))
  796. {
  797. return $db->exec('DROP INDEX ' . $db->quoteIdentifier($idxname, true));
  798. }
  799. return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, $name . ' is not an existing constraint for table ' . $table, __FUNCTION__);
  800. }
  801. // }}}
  802. // {{{ listTableConstraints()
  803. /**
  804. * list all constraints in a table
  805. *
  806. * @param string $table name of table that should be used in method
  807. * @return mixed array of constraint names on success, a MDB2 error on failure
  808. * @access public
  809. */
  810. function listTableConstraints($table)
  811. {
  812. $db = & $this->getDBInstance();
  813. if (PEAR :: isError($db))
  814. {
  815. return $db;
  816. }
  817. list($schema, $table) = $this->splitTableSchema($table);
  818. $table = $db->quote($table, 'text');
  819. $query = 'SELECT conname
  820. FROM pg_constraint
  821. LEFT JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
  822. LEFT JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
  823. WHERE relname = ' . $table;
  824. if (! empty($schema))
  825. {
  826. $query .= ' AND pg_namespace.nspname = ' . $db->quote($schema, 'text');
  827. }
  828. $query .= '
  829. UNION DISTINCT
  830. SELECT relname
  831. FROM pg_class
  832. WHERE oid IN (
  833. SELECT indexrelid
  834. FROM pg_index
  835. LEFT JOIN pg_class ON pg_class.oid = pg_index.indrelid
  836. LEFT JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
  837. WHERE pg_class.relname = ' . $table . '
  838. AND indisunique = \'t\'';
  839. if (! empty($schema))
  840. {
  841. $query .= ' AND pg_namespace.nspname = ' . $db->quote($schema, 'text');
  842. }
  843. $query .= ')';
  844. $constraints = $db->queryCol($query);
  845. if (PEAR :: isError($constraints))
  846. {
  847. return $constraints;
  848. }
  849. $result = array();
  850. foreach ($constraints as $constraint)
  851. {
  852. $constraint = $this->_fixIndexName($constraint);
  853. if (! empty($constraint))
  854. {
  855. $result[$constraint] = true;
  856. }
  857. }
  858. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE && $db->options['field_case'] == CASE_LOWER)
  859. {
  860. $result = array_change_key_case($result, $db->options['field_case']);
  861. }
  862. return array_keys($result);
  863. }
  864. // }}}
  865. // {{{ createSequence()
  866. /**
  867. * create sequence
  868. *
  869. * @param string $seq_name name of the sequence to be created
  870. * @param string $start start value of the sequence; default is 1
  871. * @return mixed MDB2_OK on success, a MDB2 error on failure
  872. * @access public
  873. */
  874. function createSequence($seq_name, $start = 1)
  875. {
  876. $db = & $this->getDBInstance();
  877. if (PEAR :: isError($db))
  878. {
  879. return $db;
  880. }
  881. $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
  882. return $db->exec("CREATE SEQUENCE $sequence_name INCREMENT 1" . ($start < 1 ? " MINVALUE $start" : '') . " START $start");
  883. }
  884. // }}}
  885. // {{{ dropSequence()
  886. /**
  887. * drop existing sequence
  888. *
  889. * @param string $seq_name name of the sequence to be dropped
  890. * @return mixed MDB2_OK on success, a MDB2 error on failure
  891. * @access public
  892. */
  893. function dropSequence($seq_name)
  894. {
  895. $db = & $this->getDBInstance();
  896. if (PEAR :: isError($db))
  897. {
  898. return $db;
  899. }
  900. $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
  901. return $db->exec("DROP SEQUENCE $sequence_name");
  902. }
  903. // }}}
  904. // {{{ listSequences()
  905. /**
  906. * list all sequences in the current database
  907. *
  908. * @return mixed array of sequence names on success, a MDB2 error on failure
  909. * @access public
  910. */
  911. function listSequences()
  912. {
  913. $db = & $this->getDBInstance();
  914. if (PEAR :: isError($db))
  915. {
  916. return $db;
  917. }
  918. $query = "SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN";
  919. $query .= "(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
  920. $table_names = $db->queryCol($query);
  921. if (PEAR :: isError($table_names))
  922. {
  923. return $table_names;
  924. }
  925. $result = array();
  926. foreach ($table_names as $table_name)
  927. {
  928. $result[] = $this->_fixSequenceName($table_name);
  929. }
  930. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE)
  931. {
  932. $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
  933. }
  934. return $result;
  935. }
  936. }
  937. ?>