PageRenderTime 71ms CodeModel.GetById 35ms RepoModel.GetById 0ms app.codeStats 0ms

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

https://bitbucket.org/renaatdemuynck/chamilo
PHP | 1457 lines | 829 code | 153 blank | 475 comment | 128 complexity | ab54313417e76bc6a06f90a874776190 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: Lukas Smith <smith@pooteeweet.org> |
  43. // +----------------------------------------------------------------------+
  44. // $Id: oci8.php 137 2009-11-09 13:24:37Z vanpouckesven $
  45. require_once 'MDB2/Driver/Manager/Common.php';
  46. /**
  47. * MDB2 oci8 driver for the management modules
  48. *
  49. * @package MDB2
  50. * @category Database
  51. * @author Lukas Smith <smith@pooteeweet.org>
  52. */
  53. class MDB2_Driver_Manager_oci8 extends MDB2_Driver_Manager_Common
  54. {
  55. // {{{ createDatabase()
  56. /**
  57. * create a new database
  58. *
  59. * @param string $name name of the database that should be created
  60. * @param array $options array with charset, collation info
  61. *
  62. * @return mixed MDB2_OK on success, a MDB2 error on failure
  63. * @access public
  64. */
  65. function createDatabase($name, $options = array())
  66. {
  67. $db = & $this->getDBInstance();
  68. if (PEAR :: isError($db))
  69. {
  70. return $db;
  71. }
  72. $username = $db->options['database_name_prefix'] . $name;
  73. $password = $db->dsn['password'] ? $db->dsn['password'] : $name;
  74. $tablespace = $db->options['default_tablespace'] ? ' DEFAULT TABLESPACE ' . $db->options['default_tablespace'] : '';
  75. $query = 'CREATE USER ' . $username . ' IDENTIFIED BY ' . $password . $tablespace;
  76. $result = $db->standaloneQuery($query, null, true);
  77. if (PEAR :: isError($result))
  78. {
  79. return $result;
  80. }
  81. $query = 'GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE, CREATE SEQUENCE, CREATE TRIGGER TO ' . $username;
  82. $result = $db->standaloneQuery($query, null, true);
  83. if (PEAR :: isError($result))
  84. {
  85. $query = 'DROP USER ' . $username . ' CASCADE';
  86. $result2 = $db->standaloneQuery($query, null, true);
  87. if (PEAR :: isError($result2))
  88. {
  89. return $db->raiseError($result2, null, null, 'could not setup the database user', __FUNCTION__);
  90. }
  91. return $result;
  92. }
  93. return MDB2_OK;
  94. }
  95. // }}}
  96. // {{{ alterDatabase()
  97. /**
  98. * alter an existing database
  99. *
  100. * IMPORTANT: the safe way to change the db charset is to do a full import/export!
  101. * If - and only if - the new character set is a strict superset of the current
  102. * character set, it is possible to use the ALTER DATABASE CHARACTER SET to
  103. * expedite the change in the database character set.
  104. *
  105. * @param string $name name of the database that is intended to be changed
  106. * @param array $options array with name, charset info
  107. *
  108. * @return mixed MDB2_OK on success, a MDB2 error on failure
  109. * @access public
  110. */
  111. function alterDatabase($name, $options = array())
  112. {
  113. //disabled
  114. //return parent::alterDatabase($name, $options);
  115. $db = & $this->getDBInstance();
  116. if (PEAR :: isError($db))
  117. {
  118. return $db;
  119. }
  120. if (! empty($options['name']))
  121. {
  122. $query = 'ALTER DATABASE ' . $db->quoteIdentifier($name, true) . ' RENAME GLOBAL_NAME TO ' . $db->quoteIdentifier($options['name'], true);
  123. $result = $db->standaloneQuery($query);
  124. if (PEAR :: isError($result))
  125. {
  126. return $result;
  127. }
  128. }
  129. if (! empty($options['charset']))
  130. {
  131. $queries = array();
  132. $queries[] = 'SHUTDOWN IMMEDIATE'; //or NORMAL
  133. $queries[] = 'STARTUP MOUNT';
  134. $queries[] = 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
  135. $queries[] = 'ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0';
  136. $queries[] = 'ALTER DATABASE OPEN';
  137. $queries[] = 'ALTER DATABASE CHARACTER SET ' . $options['charset'];
  138. $queries[] = 'ALTER DATABASE NATIONAL CHARACTER SET ' . $options['charset'];
  139. $queries[] = 'SHUTDOWN IMMEDIATE'; //or NORMAL
  140. $queries[] = 'STARTUP';
  141. foreach ($queries as $query)
  142. {
  143. $result = $db->standaloneQuery($query);
  144. if (PEAR :: isError($result))
  145. {
  146. return $result;
  147. }
  148. }
  149. }
  150. return MDB2_OK;
  151. }
  152. // }}}
  153. // {{{ dropDatabase()
  154. /**
  155. * drop an existing database
  156. *
  157. * @param object $db database object that is extended by this class
  158. * @param string $name name of the database that should be dropped
  159. * @return mixed MDB2_OK on success, a MDB2 error on failure
  160. * @access public
  161. */
  162. function dropDatabase($name)
  163. {
  164. $db = & $this->getDBInstance();
  165. if (PEAR :: isError($db))
  166. {
  167. return $db;
  168. }
  169. $username = $db->options['database_name_prefix'] . $name;
  170. return $db->standaloneQuery('DROP USER ' . $username . ' CASCADE', null, true);
  171. }
  172. // }}}
  173. // {{{ _makeAutoincrement()
  174. /**
  175. * add an autoincrement sequence + trigger
  176. *
  177. * @param string $name name of the PK field
  178. * @param string $table name of the table
  179. * @param string $start start value for the sequence
  180. * @return mixed MDB2_OK on success, a MDB2 error on failure
  181. * @access private
  182. */
  183. function _makeAutoincrement($name, $table, $start = 1)
  184. {
  185. $db = & $this->getDBInstance();
  186. if (PEAR :: isError($db))
  187. {
  188. return $db;
  189. }
  190. $table_uppercase = strtoupper($table);
  191. $index_name = $table_uppercase . '_AI_PK';
  192. $definition = array('primary' => true, 'fields' => array($name => true));
  193. $idxname_format = $db->getOption('idxname_format');
  194. $db->setOption('idxname_format', '%s');
  195. $result = $this->createConstraint($table, $index_name, $definition);
  196. $db->setOption('idxname_format', $idxname_format);
  197. if (PEAR :: isError($result))
  198. {
  199. return $db->raiseError($result, null, null, 'primary key for autoincrement PK could not be created', __FUNCTION__);
  200. }
  201. $seq_name = $table . '_' . $name;
  202. if (is_null($start))
  203. {
  204. $db->beginTransaction();
  205. $query = 'SELECT MAX(' . $db->quoteIdentifier($name, true) . ') FROM ' . $db->quoteIdentifier($table, true);
  206. $start = $this->db->queryOne($query, 'integer');
  207. if (PEAR :: isError($start))
  208. {
  209. return $start;
  210. }
  211. ++ $start;
  212. $result = $this->createSequence($seq_name, $start);
  213. $db->commit();
  214. }
  215. else
  216. {
  217. $result = $this->createSequence($seq_name, $start);
  218. }
  219. if (PEAR :: isError($result))
  220. {
  221. return $db->raiseError($result, null, null, 'sequence for autoincrement PK could not be created', __FUNCTION__);
  222. }
  223. $seq_name = $db->getSequenceName($seq_name);
  224. $trigger_name = $db->quoteIdentifier($table_uppercase . '_AI_PK', true);
  225. $seq_name_quoted = $db->quoteIdentifier($seq_name, true);
  226. $table = $db->quoteIdentifier($table, true);
  227. $name = $db->quoteIdentifier($name, true);
  228. $trigger_sql = '
  229. CREATE TRIGGER ' . $trigger_name . '
  230. BEFORE INSERT
  231. ON ' . $table . '
  232. FOR EACH ROW
  233. DECLARE
  234. last_Sequence NUMBER;
  235. last_InsertID NUMBER;
  236. BEGIN
  237. SELECT ' . $seq_name_quoted . '.NEXTVAL INTO :NEW.' . $name . ' FROM DUAL;
  238. IF (:NEW.' . $name . ' IS NULL OR :NEW.' . $name . ' = 0) THEN
  239. SELECT ' . $seq_name_quoted . '.NEXTVAL INTO :NEW.' . $name . ' FROM DUAL;
  240. ELSE
  241. SELECT NVL(Last_Number, 0) INTO last_Sequence
  242. FROM User_Sequences
  243. WHERE UPPER(Sequence_Name) = UPPER(\'' . $seq_name . '\');
  244. SELECT :NEW.' . $name . ' INTO last_InsertID FROM DUAL;
  245. WHILE (last_InsertID > last_Sequence) LOOP
  246. SELECT ' . $seq_name_quoted . '.NEXTVAL INTO last_Sequence FROM DUAL;
  247. END LOOP;
  248. END IF;
  249. END;
  250. ';
  251. $result = $db->exec($trigger_sql);
  252. if (PEAR :: isError($result))
  253. {
  254. return $result;
  255. }
  256. return MDB2_OK;
  257. }
  258. // }}}
  259. // {{{ _dropAutoincrement()
  260. /**
  261. * drop an existing autoincrement sequence + trigger
  262. *
  263. * @param string $table name of the table
  264. * @return mixed MDB2_OK on success, a MDB2 error on failure
  265. * @access private
  266. */
  267. function _dropAutoincrement($table)
  268. {
  269. $db = & $this->getDBInstance();
  270. if (PEAR :: isError($db))
  271. {
  272. return $db;
  273. }
  274. $table = strtoupper($table);
  275. $trigger_name = $table . '_AI_PK';
  276. $trigger_name_quoted = $db->quote($trigger_name, 'text');
  277. $query = 'SELECT trigger_name FROM user_triggers';
  278. $query .= ' WHERE trigger_name=' . $trigger_name_quoted . ' OR trigger_name=' . strtoupper($trigger_name_quoted);
  279. $trigger = $db->queryOne($query);
  280. if (PEAR :: isError($trigger))
  281. {
  282. return $trigger;
  283. }
  284. if ($trigger)
  285. {
  286. $trigger_name = $db->quoteIdentifier($table . '_AI_PK', true);
  287. $trigger_sql = 'DROP TRIGGER ' . $trigger_name;
  288. $result = $db->exec($trigger_sql);
  289. if (PEAR :: isError($result))
  290. {
  291. return $db->raiseError($result, null, null, 'trigger for autoincrement PK could not be dropped', __FUNCTION__);
  292. }
  293. $result = $this->dropSequence($table);
  294. if (PEAR :: isError($result))
  295. {
  296. return $db->raiseError($result, null, null, 'sequence for autoincrement PK could not be dropped', __FUNCTION__);
  297. }
  298. $index_name = $table . '_AI_PK';
  299. $idxname_format = $db->getOption('idxname_format');
  300. $db->setOption('idxname_format', '%s');
  301. $result1 = $this->dropConstraint($table, $index_name);
  302. $db->setOption('idxname_format', $idxname_format);
  303. $result2 = $this->dropConstraint($table, $index_name);
  304. if (PEAR :: isError($result1) && PEAR :: isError($result2))
  305. {
  306. return $db->raiseError($result1, null, null, 'primary key for autoincrement PK could not be dropped', __FUNCTION__);
  307. }
  308. }
  309. return MDB2_OK;
  310. }
  311. // }}}
  312. // {{{ _getTemporaryTableQuery()
  313. /**
  314. * A method to return the required SQL string that fits between CREATE ... TABLE
  315. * to create the table as a temporary table.
  316. *
  317. * @return string The string required to be placed between "CREATE" and "TABLE"
  318. * to generate a temporary table, if possible.
  319. */
  320. function _getTemporaryTableQuery()
  321. {
  322. return 'GLOBAL TEMPORARY';
  323. }
  324. // }}}
  325. // {{{ _getAdvancedFKOptions()
  326. /**
  327. * Return the FOREIGN KEY query section dealing with non-standard options
  328. * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
  329. *
  330. * @param array $definition
  331. * @return string
  332. * @access protected
  333. */
  334. function _getAdvancedFKOptions($definition)
  335. {
  336. $query = '';
  337. if (! empty($definition['ondelete']) && (strtoupper($definition['ondelete']) != 'NO ACTION'))
  338. {
  339. $query .= ' ON DELETE ' . $definition['ondelete'];
  340. }
  341. if (! empty($definition['deferrable']))
  342. {
  343. $query .= ' DEFERRABLE';
  344. }
  345. else
  346. {
  347. $query .= ' NOT DEFERRABLE';
  348. }
  349. if (! empty($definition['initiallydeferred']))
  350. {
  351. $query .= ' INITIALLY DEFERRED';
  352. }
  353. else
  354. {
  355. $query .= ' INITIALLY IMMEDIATE';
  356. }
  357. return $query;
  358. }
  359. // }}}
  360. // {{{ createTable()
  361. /**
  362. * create a new table
  363. *
  364. * @param string $name Name of the database that should be created
  365. * @param array $fields Associative array that contains the definition of each field of the new table
  366. * The indexes of the array entries are the names of the fields of the table an
  367. * the array entry values are associative arrays like those that are meant to be
  368. * passed with the field definitions to get[Type]Declaration() functions.
  369. *
  370. * Example
  371. * array(
  372. *
  373. * 'id' => array(
  374. * 'type' => 'integer',
  375. * 'unsigned' => 1
  376. * 'notnull' => 1
  377. * 'default' => 0
  378. * ),
  379. * 'name' => array(
  380. * 'type' => 'text',
  381. * 'length' => 12
  382. * ),
  383. * 'password' => array(
  384. * 'type' => 'text',
  385. * 'length' => 12
  386. * )
  387. * );
  388. * @param array $options An associative array of table options:
  389. * array(
  390. * 'comment' => 'Foo',
  391. * 'temporary' => true|false,
  392. * );
  393. * @return mixed MDB2_OK on success, a MDB2 error on failure
  394. * @access public
  395. */
  396. function createTable($name, $fields, $options = array())
  397. {
  398. $db = & $this->getDBInstance();
  399. if (PEAR :: isError($db))
  400. {
  401. return $db;
  402. }
  403. $db->beginNestedTransaction();
  404. $result = parent :: createTable($name, $fields, $options);
  405. if (! PEAR :: isError($result))
  406. {
  407. foreach ($fields as $field_name => $field)
  408. {
  409. if (! empty($field['autoincrement']))
  410. {
  411. $result = $this->_makeAutoincrement($field_name, $name);
  412. }
  413. }
  414. }
  415. $db->completeNestedTransaction();
  416. return $result;
  417. }
  418. // }}}
  419. // {{{ dropTable()
  420. /**
  421. * drop an existing table
  422. *
  423. * @param string $name name of the table that should be dropped
  424. * @return mixed MDB2_OK on success, a MDB2 error on failure
  425. * @access public
  426. */
  427. function dropTable($name)
  428. {
  429. $db = & $this->getDBInstance();
  430. if (PEAR :: isError($db))
  431. {
  432. return $db;
  433. }
  434. $db->beginNestedTransaction();
  435. $result = $this->_dropAutoincrement($name);
  436. if (! PEAR :: isError($result))
  437. {
  438. $result = parent :: dropTable($name);
  439. }
  440. $db->completeNestedTransaction();
  441. return $result;
  442. }
  443. // }}}
  444. // {{{ truncateTable()
  445. /**
  446. * Truncate an existing table (if the TRUNCATE TABLE syntax is not supported,
  447. * it falls back to a DELETE FROM TABLE query)
  448. *
  449. * @param string $name name of the table that should be truncated
  450. * @return mixed MDB2_OK on success, a MDB2 error on failure
  451. * @access public
  452. */
  453. function truncateTable($name)
  454. {
  455. $db = & $this->getDBInstance();
  456. if (PEAR :: isError($db))
  457. {
  458. return $db;
  459. }
  460. $name = $db->quoteIdentifier($name, true);
  461. return $db->exec("TRUNCATE TABLE $name");
  462. }
  463. // }}}
  464. // {{{ vacuum()
  465. /**
  466. * Optimize (vacuum) all the tables in the db (or only the specified table)
  467. * and optionally run ANALYZE.
  468. *
  469. * @param string $table table name (all the tables if empty)
  470. * @param array $options an array with driver-specific options:
  471. * - timeout [int] (in seconds) [mssql-only]
  472. * - analyze [boolean] [pgsql and mysql]
  473. * - full [boolean] [pgsql-only]
  474. * - freeze [boolean] [pgsql-only]
  475. *
  476. * @return mixed MDB2_OK success, a MDB2 error on failure
  477. * @access public
  478. */
  479. function vacuum($table = null, $options = array())
  480. {
  481. // not needed in Oracle
  482. return MDB2_OK;
  483. }
  484. // }}}
  485. // {{{ alterTable()
  486. /**
  487. * alter an existing table
  488. *
  489. * @param string $name name of the table that is intended to be changed.
  490. * @param array $changes associative array that contains the details of each type
  491. * of change that is intended to be performed. The types of
  492. * changes that are currently supported are defined as follows:
  493. *
  494. * name
  495. *
  496. * New name for the table.
  497. *
  498. * add
  499. *
  500. * Associative array with the names of fields to be added as
  501. * indexes of the array. The value of each entry of the array
  502. * should be set to another associative array with the properties
  503. * of the fields to be added. The properties of the fields should
  504. * be the same as defined by the MDB2 parser.
  505. *
  506. *
  507. * remove
  508. *
  509. * Associative array with the names of fields to be removed as indexes
  510. * of the array. Currently the values assigned to each entry are ignored.
  511. * An empty array should be used for future compatibility.
  512. *
  513. * rename
  514. *
  515. * Associative array with the names of fields to be renamed as indexes
  516. * of the array. The value of each entry of the array should be set to
  517. * another associative array with the entry named name with the new
  518. * field name and the entry named Declaration that is expected to contain
  519. * the portion of the field declaration already in DBMS specific SQL code
  520. * as it is used in the CREATE TABLE statement.
  521. *
  522. * change
  523. *
  524. * Associative array with the names of the fields to be changed as indexes
  525. * of the array. Keep in mind that if it is intended to change either the
  526. * name of a field and any other properties, the change array entries
  527. * should have the new names of the fields as array indexes.
  528. *
  529. * The value of each entry of the array should be set to another associative
  530. * array with the properties of the fields to that are meant to be changed as
  531. * array entries. These entries should be assigned to the new values of the
  532. * respective properties. The properties of the fields should be the same
  533. * as defined by the MDB2 parser.
  534. *
  535. * Example
  536. * array(
  537. * 'name' => 'userlist',
  538. * 'add' => array(
  539. * 'quota' => array(
  540. * 'type' => 'integer',
  541. * 'unsigned' => 1
  542. * )
  543. * ),
  544. * 'remove' => array(
  545. * 'file_limit' => array(),
  546. * 'time_limit' => array()
  547. * ),
  548. * 'change' => array(
  549. * 'name' => array(
  550. * 'length' => '20',
  551. * 'definition' => array(
  552. * 'type' => 'text',
  553. * 'length' => 20,
  554. * ),
  555. * )
  556. * ),
  557. * 'rename' => array(
  558. * 'sex' => array(
  559. * 'name' => 'gender',
  560. * 'definition' => array(
  561. * 'type' => 'text',
  562. * 'length' => 1,
  563. * 'default' => 'M',
  564. * ),
  565. * )
  566. * )
  567. * )
  568. *
  569. * @param boolean $check indicates whether the function should just check if the DBMS driver
  570. * can perform the requested table alterations if the value is true or
  571. * actually perform them otherwise.
  572. * @access public
  573. *
  574. * @return mixed MDB2_OK on success, a MDB2 error on failure
  575. */
  576. function alterTable($name, $changes, $check)
  577. {
  578. $db = & $this->getDBInstance();
  579. if (PEAR :: isError($db))
  580. {
  581. return $db;
  582. }
  583. foreach ($changes as $change_name => $change)
  584. {
  585. switch ($change_name)
  586. {
  587. case 'add' :
  588. case 'remove' :
  589. case 'change' :
  590. case 'name' :
  591. case 'rename' :
  592. break;
  593. default :
  594. return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null, 'change type "' . $change_name . '" not yet supported', __FUNCTION__);
  595. }
  596. }
  597. if ($check)
  598. {
  599. return MDB2_OK;
  600. }
  601. $name = $db->quoteIdentifier($name, true);
  602. if (! empty($changes['add']) && is_array($changes['add']))
  603. {
  604. $fields = array();
  605. foreach ($changes['add'] as $field_name => $field)
  606. {
  607. $fields[] = $db->getDeclaration($field['type'], $field_name, $field);
  608. }
  609. $result = $db->exec("ALTER TABLE $name ADD (" . implode(', ', $fields) . ')');
  610. if (PEAR :: isError($result))
  611. {
  612. return $result;
  613. }
  614. }
  615. if (! empty($changes['change']) && is_array($changes['change']))
  616. {
  617. $fields = array();
  618. foreach ($changes['change'] as $field_name => $field)
  619. {
  620. //fix error "column to be modified to NOT NULL is already NOT NULL"
  621. if (! array_key_exists('notnull', $field))
  622. {
  623. unset($field['definition']['notnull']);
  624. }
  625. $fields[] = $db->getDeclaration($field['definition']['type'], $field_name, $field['definition']);
  626. }
  627. $result = $db->exec("ALTER TABLE $name MODIFY (" . implode(', ', $fields) . ')');
  628. if (PEAR :: isError($result))
  629. {
  630. return $result;
  631. }
  632. }
  633. if (! empty($changes['rename']) && is_array($changes['rename']))
  634. {
  635. foreach ($changes['rename'] as $field_name => $field)
  636. {
  637. $field_name = $db->quoteIdentifier($field_name, true);
  638. $query = "ALTER TABLE $name RENAME COLUMN $field_name TO " . $db->quoteIdentifier($field['name']);
  639. $result = $db->exec($query);
  640. if (PEAR :: isError($result))
  641. {
  642. return $result;
  643. }
  644. }
  645. }
  646. if (! empty($changes['remove']) && is_array($changes['remove']))
  647. {
  648. $fields = array();
  649. foreach ($changes['remove'] as $field_name => $field)
  650. {
  651. $fields[] = $db->quoteIdentifier($field_name, true);
  652. }
  653. $result = $db->exec("ALTER TABLE $name DROP COLUMN " . implode(', ', $fields));
  654. if (PEAR :: isError($result))
  655. {
  656. return $result;
  657. }
  658. }
  659. if (! empty($changes['name']))
  660. {
  661. $change_name = $db->quoteIdentifier($changes['name'], true);
  662. $result = $db->exec("ALTER TABLE $name RENAME TO " . $change_name);
  663. if (PEAR :: isError($result))
  664. {
  665. return $result;
  666. }
  667. }
  668. return MDB2_OK;
  669. }
  670. // }}}
  671. // {{{ _fetchCol()
  672. /**
  673. * Utility method to fetch and format a column from a resultset
  674. *
  675. * @param resource $result
  676. * @param boolean $fixname (used when listing indices or constraints)
  677. * @return mixed array of names on success, a MDB2 error on failure
  678. * @access private
  679. */
  680. function _fetchCol($result, $fixname = false)
  681. {
  682. if (PEAR :: isError($result))
  683. {
  684. return $result;
  685. }
  686. $col = $result->fetchCol();
  687. if (PEAR :: isError($col))
  688. {
  689. return $col;
  690. }
  691. $result->free();
  692. $db = & $this->getDBInstance();
  693. if (PEAR :: isError($db))
  694. {
  695. return $db;
  696. }
  697. if ($fixname)
  698. {
  699. foreach ($col as $k => $v)
  700. {
  701. $col[$k] = $this->_fixIndexName($v);
  702. }
  703. }
  704. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE && $db->options['field_case'] == CASE_LOWER)
  705. {
  706. $col = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $col);
  707. }
  708. return $col;
  709. }
  710. // }}}
  711. // {{{ listDatabases()
  712. /**
  713. * list all databases
  714. *
  715. * @return mixed array of database names on success, a MDB2 error on failure
  716. * @access public
  717. */
  718. function listDatabases()
  719. {
  720. $db = & $this->getDBInstance();
  721. if (PEAR :: isError($db))
  722. {
  723. return $db;
  724. }
  725. if (! $db->options['emulate_database'])
  726. {
  727. return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null, 'database listing is only supported if the "emulate_database" option is enabled', __FUNCTION__);
  728. }
  729. if ($db->options['database_name_prefix'])
  730. {
  731. $query = 'SELECT SUBSTR(username, ';
  732. $query .= (strlen($db->options['database_name_prefix']) + 1);
  733. $query .= ") FROM sys.dba_users WHERE username LIKE '";
  734. $query .= $db->options['database_name_prefix'] . "%'";
  735. }
  736. else
  737. {
  738. $query = 'SELECT username FROM sys.dba_users';
  739. }
  740. $result = $db->standaloneQuery($query, array('text'), false);
  741. return $this->_fetchCol($result);
  742. }
  743. // }}}
  744. // {{{ listUsers()
  745. /**
  746. * list all users
  747. *
  748. * @return mixed array of user names on success, a MDB2 error on failure
  749. * @access public
  750. */
  751. function listUsers()
  752. {
  753. $db = & $this->getDBInstance();
  754. if (PEAR :: isError($db))
  755. {
  756. return $db;
  757. }
  758. if ($db->options['emulate_database'] && $db->options['database_name_prefix'])
  759. {
  760. $query = 'SELECT SUBSTR(username, ';
  761. $query .= (strlen($db->options['database_name_prefix']) + 1);
  762. $query .= ") FROM sys.dba_users WHERE username NOT LIKE '";
  763. $query .= $db->options['database_name_prefix'] . "%'";
  764. }
  765. else
  766. {
  767. $query = 'SELECT username FROM sys.dba_users';
  768. }
  769. return $db->queryCol($query);
  770. }
  771. // }}}
  772. // {{{ listViews()
  773. /**
  774. * list all views in the current database
  775. *
  776. * @param string owner, the current is default
  777. * @return mixed array of view names on success, a MDB2 error on failure
  778. * @access public
  779. */
  780. function listViews($owner = null)
  781. {
  782. $db = & $this->getDBInstance();
  783. if (PEAR :: isError($db))
  784. {
  785. return $db;
  786. }
  787. if (empty($owner))
  788. {
  789. $owner = $db->dsn['username'];
  790. }
  791. $query = 'SELECT view_name
  792. FROM sys.all_views
  793. WHERE owner=? OR owner=?';
  794. $stmt = $db->prepare($query);
  795. if (PEAR :: isError($stmt))
  796. {
  797. return $stmt;
  798. }
  799. $result = $stmt->execute(array($owner, strtoupper($owner)));
  800. return $this->_fetchCol($result);
  801. }
  802. // }}}
  803. // {{{ listFunctions()
  804. /**
  805. * list all functions in the current database
  806. *
  807. * @param string owner, the current is default
  808. * @return mixed array of function names on success, a MDB2 error on failure
  809. * @access public
  810. */
  811. function listFunctions($owner = null)
  812. {
  813. $db = & $this->getDBInstance();
  814. if (PEAR :: isError($db))
  815. {
  816. return $db;
  817. }
  818. if (empty($owner))
  819. {
  820. $owner = $db->dsn['username'];
  821. }
  822. $query = "SELECT name
  823. FROM sys.all_source
  824. WHERE line = 1
  825. AND type = 'FUNCTION'
  826. AND (owner=? OR owner=?)";
  827. $stmt = $db->prepare($query);
  828. if (PEAR :: isError($stmt))
  829. {
  830. return $stmt;
  831. }
  832. $result = $stmt->execute(array($owner, strtoupper($owner)));
  833. return $this->_fetchCol($result);
  834. }
  835. // }}}
  836. // {{{ listTableTriggers()
  837. /**
  838. * list all triggers in the database that reference a given table
  839. *
  840. * @param string table for which all referenced triggers should be found
  841. * @return mixed array of trigger names on success, a MDB2 error on failure
  842. * @access public
  843. */
  844. function listTableTriggers($table = null)
  845. {
  846. $db = & $this->getDBInstance();
  847. if (PEAR :: isError($db))
  848. {
  849. return $db;
  850. }
  851. if (empty($owner))
  852. {
  853. $owner = $db->dsn['username'];
  854. }
  855. $query = "SELECT trigger_name
  856. FROM sys.all_triggers
  857. WHERE (table_name=? OR table_name=?)
  858. AND (owner=? OR owner=?)";
  859. $stmt = $db->prepare($query);
  860. if (PEAR :: isError($stmt))
  861. {
  862. return $stmt;
  863. }
  864. $args = array($table, strtoupper($table), $owner, strtoupper($owner));
  865. $result = $stmt->execute($args);
  866. return $this->_fetchCol($result);
  867. }
  868. // }}}
  869. // {{{ listTables()
  870. /**
  871. * list all tables in the database
  872. *
  873. * @param string owner, the current is default
  874. * @return mixed array of table names on success, a MDB2 error on failure
  875. * @access public
  876. */
  877. function listTables($owner = null)
  878. {
  879. $db = & $this->getDBInstance();
  880. if (PEAR :: isError($db))
  881. {
  882. return $db;
  883. }
  884. if (empty($owner))
  885. {
  886. $owner = $db->dsn['username'];
  887. }
  888. $query = 'SELECT table_name
  889. FROM sys.all_tables
  890. WHERE owner=? OR owner=?';
  891. $stmt = $db->prepare($query);
  892. if (PEAR :: isError($stmt))
  893. {
  894. return $stmt;
  895. }
  896. $result = $stmt->execute(array($owner, strtoupper($owner)));
  897. return $this->_fetchCol($result);
  898. }
  899. // }}}
  900. // {{{ listTableFields()
  901. /**
  902. * list all fields in a table in the current database
  903. *
  904. * @param string $table name of table that should be used in method
  905. * @return mixed array of field names on success, a MDB2 error on failure
  906. * @access public
  907. */
  908. function listTableFields($table)
  909. {
  910. $db = & $this->getDBInstance();
  911. if (PEAR :: isError($db))
  912. {
  913. return $db;
  914. }
  915. list($owner, $table) = $this->splitTableSchema($table);
  916. if (empty($owner))
  917. {
  918. $owner = $db->dsn['username'];
  919. }
  920. $query = 'SELECT column_name
  921. FROM all_tab_columns
  922. WHERE (table_name=? OR table_name=?)
  923. AND (owner=? OR owner=?)
  924. ORDER BY column_id';
  925. $stmt = $db->prepare($query);
  926. if (PEAR :: isError($stmt))
  927. {
  928. return $stmt;
  929. }
  930. $args = array($table, strtoupper($table), $owner, strtoupper($owner));
  931. $result = $stmt->execute($args);
  932. return $this->_fetchCol($result);
  933. }
  934. // }}}
  935. // {{{ listTableIndexes()
  936. /**
  937. * list all indexes in a table
  938. *
  939. * @param string $table name of table that should be used in method
  940. * @return mixed array of index names on success, a MDB2 error on failure
  941. * @access public
  942. */
  943. function listTableIndexes($table)
  944. {
  945. $db = & $this->getDBInstance();
  946. if (PEAR :: isError($db))
  947. {
  948. return $db;
  949. }
  950. list($owner, $table) = $this->splitTableSchema($table);
  951. if (empty($owner))
  952. {
  953. $owner = $db->dsn['username'];
  954. }
  955. $query = 'SELECT i.index_name name
  956. FROM all_indexes i
  957. LEFT JOIN all_constraints c
  958. ON c.index_name = i.index_name
  959. AND c.owner = i.owner
  960. AND c.table_name = i.table_name
  961. WHERE (i.table_name=? OR i.table_name=?)
  962. AND (i.owner=? OR i.owner=?)
  963. AND c.index_name IS NULL
  964. AND i.generated=' . $db->quote('N', 'text');
  965. $stmt = $db->prepare($query);
  966. if (PEAR :: isError($stmt))
  967. {
  968. return $stmt;
  969. }
  970. $args = array($table, strtoupper($table), $owner, strtoupper($owner));
  971. $result = $stmt->execute($args);
  972. return $this->_fetchCol($result, true);
  973. }
  974. // }}}
  975. // {{{ createConstraint()
  976. /**
  977. * create a constraint on a table
  978. *
  979. * @param string $table name of the table on which the constraint is to be created
  980. * @param string $name name of the constraint to be created
  981. * @param array $definition associative array that defines properties of the constraint to be created.
  982. * Currently, only one property named FIELDS is supported. This property
  983. * is also an associative with the names of the constraint fields as array
  984. * constraints. Each entry of this array is set to another type of associative
  985. * array that specifies properties of the constraint that are specific to
  986. * each field.
  987. *
  988. * Example
  989. * array(
  990. * 'fields' => array(
  991. * 'user_name' => array(),
  992. * 'last_login' => array()
  993. * )
  994. * )
  995. * @return mixed MDB2_OK on success, a MDB2 error on failure
  996. * @access public
  997. */
  998. function createConstraint($table, $name, $definition)
  999. {
  1000. $result = parent :: createConstraint($table, $name, $definition);
  1001. if (PEAR :: isError($result))
  1002. {
  1003. return $result;
  1004. }
  1005. if (! empty($definition['foreign']))
  1006. {
  1007. return $this->_createFKTriggers($table, array($name => $definition));
  1008. }
  1009. return MDB2_OK;
  1010. }
  1011. // }}}
  1012. // {{{ dropConstraint()
  1013. /**
  1014. * drop existing constraint
  1015. *
  1016. * @param string $table name of table that should be used in method
  1017. * @param string $name name of the constraint to be dropped
  1018. * @param string $primary hint if the constraint is primary
  1019. * @return mixed MDB2_OK on success, a MDB2 error on failure
  1020. * @access public
  1021. */
  1022. function dropConstraint($table, $name, $primary = false)
  1023. {
  1024. $db = & $this->getDBInstance();
  1025. if (PEAR :: isError($db))
  1026. {
  1027. return $db;
  1028. }
  1029. //is it a FK constraint? If so, also delete the associated triggers
  1030. $db->loadModule('Reverse', null, true);
  1031. $definition = $db->reverse->getTableConstraintDefinition($table, $name);
  1032. if (! PEAR :: isError($definition) && ! empty($definition['foreign']))
  1033. {
  1034. //first drop the FK enforcing triggers
  1035. $result = $this->_dropFKTriggers($table, $name, $definition['references']['table']);
  1036. if (PEAR :: isError($result))
  1037. {
  1038. return $result;
  1039. }
  1040. }
  1041. return parent :: dropConstraint($table, $name, $primary);
  1042. }
  1043. // }}}
  1044. // {{{ _createFKTriggers()
  1045. /**
  1046. * Create triggers to enforce the FOREIGN KEY constraint on the table
  1047. *
  1048. * @param string $table table name
  1049. * @param array $foreign_keys FOREIGN KEY definitions
  1050. *
  1051. * @return mixed MDB2_OK on success, a MDB2 error on failure
  1052. * @access private
  1053. */
  1054. function _createFKTriggers($table, $foreign_keys)
  1055. {
  1056. $db = & $this->getDBInstance();
  1057. if (PEAR :: isError($db))
  1058. {
  1059. return $db;
  1060. }
  1061. // create triggers to enforce FOREIGN KEY constraints
  1062. if ($db->supports('triggers') && ! empty($foreign_keys))
  1063. {
  1064. $table = $db->quoteIdentifier($table, true);
  1065. foreach ($foreign_keys as $fkname => $fkdef)
  1066. {
  1067. if (empty($fkdef))
  1068. {
  1069. continue;
  1070. }
  1071. $fkdef['onupdate'] = empty($fkdef['onupdate']) ? $db->options['default_fk_action_onupdate'] : strtoupper($fkdef['onupdate']);
  1072. if ('RESTRICT' == $fkdef['onupdate'] || 'NO ACTION' == $fkdef['onupdate'])
  1073. {
  1074. // already handled by default
  1075. continue;
  1076. }
  1077. $trigger_name = substr(strtolower($fkname . '_pk_upd_trg'), 0, $db->options['max_identifiers_length']);
  1078. $table_fields = array_keys($fkdef['fields']);
  1079. $referenced_fields = array_keys($fkdef['references']['fields']);
  1080. //create the ON UPDATE trigger on the primary table
  1081. $restrict_action = ' IF (SELECT ';
  1082. $aliased_fields = array();
  1083. foreach ($table_fields as $field)
  1084. {
  1085. $aliased_fields[] = $table . '.' . $field . ' AS ' . $field;
  1086. }
  1087. $restrict_action .= implode(',', $aliased_fields) . ' FROM ' . $table . ' WHERE ';
  1088. $conditions = array();
  1089. $new_values = array();
  1090. $null_values = array();
  1091. for($i = 0; $i < count($table_fields); $i ++)
  1092. {
  1093. $conditions[] = $table_fields[$i] . ' = :OLD.' . $referenced_fields[$i];
  1094. $new_values[] = $table_fields[$i] . ' = :NEW.' . $referenced_fields[$i];
  1095. $null_values[] = $table_fields[$i] . ' = NULL';
  1096. }
  1097. $cascade_action = 'UPDATE ' . $table . ' SET ' . implode(', ', $new_values) . ' WHERE ' . implode(' AND ', $conditions) . ';';
  1098. $setnull_action = 'UPDATE ' . $table . ' SET ' . implode(', ', $null_values) . ' WHERE ' . implode(' AND ', $conditions) . ';';
  1099. if ('SET DEFAULT' == $fkdef['onupdate'] || 'SET DEFAULT' == $fkdef['ondelete'])
  1100. {
  1101. $db->loadModule('Reverse', null, true);
  1102. $default_values = array();
  1103. foreach ($table_fields as $table_field)
  1104. {
  1105. $field_definition = $db->reverse->getTableFieldDefinition($table, $field);
  1106. if (PEAR :: isError($field_definition))
  1107. {
  1108. return $field_definition;
  1109. }
  1110. $default_values[] = $table_field . ' = ' . $field_definition[0]['default'];
  1111. }
  1112. $setdefault_action = 'UPDATE ' . $table . ' SET ' . implode(', ', $default_values) . ' WHERE ' . implode(' AND ', $conditions) . ';';
  1113. }
  1114. $query = 'CREATE TRIGGER %s' . ' %s ON ' . $fkdef['references']['table'] . ' FOR EACH ROW ' . ' BEGIN ';
  1115. if ('CASCADE' == $fkdef['onupdate'])
  1116. {
  1117. $sql_update = sprintf($query, $trigger_name, 'BEFORE UPDATE', 'update') . $cascade_action;
  1118. }
  1119. elseif ('SET NULL' == $fkdef['onupdate'])
  1120. {
  1121. $sql_update = sprintf($query, $trigger_name, 'BEFORE UPDATE', 'update') . $setnull_action;
  1122. }
  1123. elseif ('SET DEFAULT' == $fkdef['onupdate'])
  1124. {
  1125. $sql_update = sprintf($query, $trigger_name, 'BEFORE UPDATE', 'update') . $setdefault_action;
  1126. }
  1127. $sql_update .= ' END;';
  1128. $result = $db->exec($sql_update);
  1129. if (PEAR :: isError($result))
  1130. {
  1131. return $result;
  1132. }
  1133. }
  1134. }
  1135. return MDB2_OK;
  1136. }
  1137. // }}}
  1138. // {{{ _dropFKTriggers()
  1139. /**
  1140. * Drop the triggers created to enforce the FOREIGN KEY constraint on the table
  1141. *
  1142. * @param string $table table name
  1143. * @param string $fkname FOREIGN KEY constraint name
  1144. * @param string $referenced_table referenced table name
  1145. *
  1146. * @return mixed MDB2_OK on success, a MDB2 error on failure
  1147. * @access private
  1148. */
  1149. function _dropFKTriggers($table, $fkname, $referenced_table)
  1150. {
  1151. $db = & $this->getDBInstance();
  1152. if (PEAR :: isError($db))
  1153. {
  1154. return $db;
  1155. }
  1156. $triggers = $this->listTableTriggers($table);
  1157. $triggers2 = $this->listTableTriggers($referenced_table);
  1158. if (! PEAR :: isError($triggers2) && ! PEAR :: isError($triggers))
  1159. {
  1160. $triggers = array_merge($triggers, $triggers2);
  1161. $trigger_name = substr(strtolower($fkname . '_pk_upd_trg'), 0, $db->options['max_identifiers_length']);
  1162. $pattern = '/^' . $trigger_name . '$/i';
  1163. foreach ($triggers as $trigger)
  1164. {
  1165. if (preg_match($pattern, $trigger))
  1166. {
  1167. $result = $db->exec('DROP TRIGGER ' . $trigger);
  1168. if (PEAR :: isError($result))
  1169. {
  1170. return $result;
  1171. }
  1172. }
  1173. }
  1174. }
  1175. return MDB2_OK;
  1176. }
  1177. // }}}
  1178. // {{{ listTableConstraints()
  1179. /**
  1180. * list all constraints in a table
  1181. *
  1182. * @param string $table name of table that should be used in method
  1183. * @return mixed array of constraint names on success, a MDB2 error on failure
  1184. * @access public
  1185. */
  1186. function listTableConstraints($table)
  1187. {
  1188. $db = & $this->getDBInstance();
  1189. if (PEAR :: isError($db))
  1190. {
  1191. return $db;
  1192. }
  1193. list($owner, $table) = $this->splitTableSchema($table);
  1194. if (empty($owner))
  1195. {
  1196. $owner = $db->dsn['username'];
  1197. }
  1198. $query = 'SELECT constraint_name
  1199. FROM all_constraints
  1200. WHERE (table_name=? OR table_name=?)
  1201. AND (owner=? OR owner=?)';
  1202. $stmt = $db->prepare($query);
  1203. if (PEAR :: isError($stmt))
  1204. {
  1205. return $stmt;
  1206. }
  1207. $args = array($table, strtoupper($table), $owner, strtoupper($owner));
  1208. $result = $stmt->execute($args);
  1209. return $this->_fetchCol($result, true);
  1210. }
  1211. // }}}
  1212. // {{{ createSequence()
  1213. /**
  1214. * create sequence
  1215. *
  1216. * @param object $db database object that is extended by this class
  1217. * @param string $seq_name name of the sequence to be created
  1218. * @param string $start start value of the sequence; default is 1
  1219. * @return mixed MDB2_OK on success, a MDB2 error on failure
  1220. * @access public
  1221. */
  1222. function createSequence($seq_name, $start = 1)
  1223. {
  1224. $db = & $this->getDBInstance();
  1225. if (PEAR :: isError($db))
  1226. {
  1227. return $db;
  1228. }
  1229. $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
  1230. $query = "CREATE SEQUENCE $sequence_name START WITH $start INCREMENT BY 1 NOCACHE";
  1231. $query .= ($start < 1 ? " MINVALUE $start" : '');
  1232. return $db->exec($query);
  1233. }
  1234. // }}}
  1235. // {{{ dropSequence()
  1236. /**
  1237. * drop existing sequence
  1238. *
  1239. * @param object $db database object that is extended by this class
  1240. * @param string $seq_name name of the sequence to be dropped
  1241. * @return mixed MDB2_OK on success, a MDB2 error on failure
  1242. * @access public
  1243. */
  1244. function dropSequence($seq_name)
  1245. {
  1246. $db = & $this->getDBInstance();
  1247. if (PEAR :: isError($db))
  1248. {
  1249. return $db;
  1250. }
  1251. $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
  1252. return $db->exec("DROP SEQUENCE $sequence_name");
  1253. }
  1254. // }}}
  1255. // {{{ listSequences()
  1256. /**
  1257. * list all sequences in the current database
  1258. *
  1259. * @param string owner, the current is default
  1260. * @return mixed array of sequence names on success, a MDB2 error on failure
  1261. * @access public
  1262. */
  1263. function listSequences($owner = null)
  1264. {
  1265. $db = & $this->getDBInstance();
  1266. if (PEAR :: isError($db))
  1267. {
  1268. return $db;
  1269. }
  1270. if (empty($owner))
  1271. {
  1272. $owner = $db->dsn['username'];
  1273. }
  1274. $query = 'SELECT sequence_name
  1275. FROM sys.all_sequences
  1276. WHERE (sequence_owner=? OR sequence_owner=?)';
  1277. $stmt = $db->prepare($query);
  1278. if (PEAR :: isError($stmt))
  1279. {
  1280. return $stmt;
  1281. }
  1282. $result = $stmt->execute(array($owner, strtoupper($owner)));
  1283. if (PEAR :: isError($result))
  1284. {
  1285. return $result;
  1286. }
  1287. $col = $result->fetchCol();
  1288. if (PEAR :: isError($col))
  1289. {
  1290. return $col;
  1291. }
  1292. $result->free();
  1293. foreach ($col as $k => $v)
  1294. {
  1295. $col[$k] = $this->_fixSequenceName($v);
  1296. }
  1297. if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE && $db->options['field_case'] == CASE_LOWER)
  1298. {
  1299. $col = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $col);
  1300. }
  1301. return $col;
  1302. }
  1303. }
  1304. ?>