ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
oci8.php
Go to the documentation of this file.
1<?php
2// +----------------------------------------------------------------------+
3// | PHP versions 4 and 5 |
4// +----------------------------------------------------------------------+
5// | Copyright (c) 1998-2006 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
45// $Id: oci8.php,v 1.87 2007/04/05 07:07:04 quipo Exp $
46
47require_once 'MDB2/Driver/Manager/Common.php';
48
56class MDB2_Driver_Manager_oci8 extends MDB2_Driver_Manager_Common
57{
58 // {{{ createDatabase()
59
68 function createDatabase($name)
69 {
70 $db =& $this->getDBInstance();
71 if (PEAR::isError($db)) {
72 return $db;
73 }
74
75 if (!$db->options['emulate_database']) {
76 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
77 'database creation is only supported if the "emulate_database" option is enabled', __FUNCTION__);
78 }
79
80 $username = $db->options['database_name_prefix'].$name;
81 $password = $db->dsn['password'] ? $db->dsn['password'] : $name;
82 $tablespace = $db->options['default_tablespace']
83 ? ' DEFAULT TABLESPACE '.$db->options['default_tablespace'] : '';
84
85 $query = 'CREATE USER '.$username.' IDENTIFIED BY '.$password.$tablespace;
86 $result = $db->standaloneQuery($query, null, true);
87 if (PEAR::isError($result)) {
88 return $result;
89 }
90 $query = 'GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE, CREATE SEQUENCE, CREATE TRIGGER TO '.$username;
91 $result = $db->standaloneQuery($query, null, true);
92 if (PEAR::isError($result)) {
93 $query = 'DROP USER '.$username.' CASCADE';
94 $result2 = $db->standaloneQuery($query, null, true);
95 if (PEAR::isError($result2)) {
96 return $db->raiseError($result2, null, null,
97 'could not setup the database user', __FUNCTION__);
98 }
99 return $result;
100 }
101 return MDB2_OK;
102 }
103
104 // }}}
105 // {{{ dropDatabase()
106
115 function dropDatabase($name)
116 {
117 $db =& $this->getDBInstance();
118 if (PEAR::isError($db)) {
119 return $db;
120 }
121
122 if (!$db->options['emulate_database']) {
123 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
124 'database dropping is only supported if the "emulate_database" option is enabled', __FUNCTION__);
125 }
126
127 $username = $db->options['database_name_prefix'].$name;
128 return $db->standaloneQuery('DROP USER '.$username.' CASCADE', null, true);
129 }
130
131
132 // }}}
133 // {{{ _makeAutoincrement()
134
144 function _makeAutoincrement($name, $table, $start = 1)
145 {
146 $db =& $this->getDBInstance();
147 if (PEAR::isError($db)) {
148 return $db;
149 }
150
151 $table = strtoupper($table);
152 $index_name = $table . '_AI_PK';
153 $definition = array(
154 'primary' => true,
155 'fields' => array($name => true),
156 );
157 $result = $this->createConstraint($table, $index_name, $definition);
158 if (PEAR::isError($result)) {
159 return $db->raiseError($result, null, null,
160 'primary key for autoincrement PK could not be created', __FUNCTION__);
161 }
162
163 if (is_null($start)) {
164 $db->beginTransaction();
165 $query = 'SELECT MAX(' . $db->quoteIdentifier($name, true) . ') FROM ' . $db->quoteIdentifier($table, true);
166 $start = $this->db->queryOne($query, 'integer');
167 if (PEAR::isError($start)) {
168 return $start;
169 }
170 ++$start;
171 $result = $this->createSequence($table, $start);
172 $db->commit();
173 } else {
174 $result = $this->createSequence($table, $start);
175 }
176 if (PEAR::isError($result)) {
177 return $db->raiseError($result, null, null,
178 'sequence for autoincrement PK could not be created', __FUNCTION__);
179 }
180 $sequence_name = $db->getSequenceName($table);
181 $trigger_name = $db->quoteIdentifier($table . '_AI_PK', true);
182 $table = $db->quoteIdentifier($table, true);
183 $name = $db->quoteIdentifier($name, true);
184 $trigger_sql = '
185CREATE TRIGGER '.$trigger_name.'
186 BEFORE INSERT
187 ON '.$table.'
188 FOR EACH ROW
189DECLARE
190 last_Sequence NUMBER;
191 last_InsertID NUMBER;
192BEGIN
193 SELECT '.$sequence_name.'.NEXTVAL INTO :NEW.'.$name.' FROM DUAL;
194 IF (:NEW.'.$name.' IS NULL OR :NEW.'.$name.' = 0) THEN
195 SELECT '.$sequence_name.'.NEXTVAL INTO :NEW.'.$name.' FROM DUAL;
196 ELSE
197 SELECT NVL(Last_Number, 0) INTO last_Sequence
198 FROM User_Sequences
199 WHERE UPPER(Sequence_Name) = UPPER(\''.$sequence_name.'\');
200 SELECT :NEW.'.$name.' INTO last_InsertID FROM DUAL;
201 WHILE (last_InsertID > last_Sequence) LOOP
202 SELECT '.$sequence_name.'.NEXTVAL INTO last_Sequence FROM DUAL;
203 END LOOP;
204 END IF;
205END;
206';
207 return $db->exec($trigger_sql);
208 }
209
210 // }}}
211 // {{{ _dropAutoincrement()
212
220 function _dropAutoincrement($table)
221 {
222 $db =& $this->getDBInstance();
223 if (PEAR::isError($db)) {
224 return $db;
225 }
226
227 $table = strtoupper($table);
228 $trigger_name = $table . '_AI_PK';
229 $trigger_name_quoted = $db->quote($trigger_name, 'text');
230 $query = 'SELECT trigger_name FROM user_triggers';
231 $query.= ' WHERE trigger_name='.$trigger_name_quoted.' OR trigger_name='.strtoupper($trigger_name_quoted);
232 $trigger = $db->queryOne($query);
233 if (PEAR::isError($trigger)) {
234 return $trigger;
235 }
236
237 if ($trigger) {
238 $trigger_name = $db->quoteIdentifier($table . '_AI_PK', true);
239 $trigger_sql = 'DROP TRIGGER ' . $trigger_name;
240 $result = $db->exec($trigger_sql);
241 if (PEAR::isError($result)) {
242 return $db->raiseError($result, null, null,
243 'trigger for autoincrement PK could not be dropped', __FUNCTION__);
244 }
245
246 $result = $this->dropSequence($table);
247 if (PEAR::isError($result)) {
248 return $db->raiseError($result, null, null,
249 'sequence for autoincrement PK could not be dropped', __FUNCTION__);
250 }
251
252 $index_name = $table . '_AI_PK';
253 $result = $this->dropConstraint($table, $index_name);
254 if (PEAR::isError($result)) {
255 return $db->raiseError($result, null, null,
256 'primary key for autoincrement PK could not be dropped', __FUNCTION__);
257 }
258 }
259
260 return MDB2_OK;
261 }
262
263 // }}}
264 // {{{ _getTemporaryTableQuery()
265
273 function _getTemporaryTableQuery()
274 {
275 return 'GLOBAL TEMPORARY';
276 }
277
278 // }}}
279 // {{{ createTable()
280
316 function createTable($name, $fields, $options = array())
317 {
318 $db =& $this->getDBInstance();
319 if (PEAR::isError($db)) {
320 return $db;
321 }
322 $db->beginNestedTransaction();
323 $result = parent::createTable($name, $fields, $options);
324 if (!PEAR::isError($result)) {
325 foreach ($fields as $field_name => $field) {
326 if (!empty($field['autoincrement'])) {
327 $result = $this->_makeAutoincrement($field_name, $name);
328 }
329 }
330 }
331 $db->completeNestedTransaction();
332 return $result;
333 }
334
335 // }}}
336 // {{{ dropTable()
337
345 function dropTable($name)
346 {
347 $db =& $this->getDBInstance();
348 if (PEAR::isError($db)) {
349 return $db;
350 }
351 $db->beginNestedTransaction();
352 $result = $this->_dropAutoincrement($name);
353 if (!PEAR::isError($result)) {
354 $result = parent::dropTable($name);
355 }
356 $db->completeNestedTransaction();
357 return $result;
358 }
359
360 // }}}
361 // {{{ alterTable()
362
453 function alterTable($name, $changes, $check)
454 {
455 $db =& $this->getDBInstance();
456 if (PEAR::isError($db)) {
457 return $db;
458 }
459
460 foreach ($changes as $change_name => $change) {
461 switch ($change_name) {
462 case 'add':
463 case 'remove':
464 case 'change':
465 case 'name':
466 case 'rename':
467 break;
468 default:
469 return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
470 'change type "'.$change_name.'" not yet supported', __FUNCTION__);
471 }
472 }
473
474 if ($check) {
475 return MDB2_OK;
476 }
477
478 $name = $db->quoteIdentifier($name, true);
479
480 if (!empty($changes['add']) && is_array($changes['add'])) {
481 $fields = array();
482 foreach ($changes['add'] as $field_name => $field) {
483 $fields[] = $db->getDeclaration($field['type'], $field_name, $field);
484 }
485 $result = $db->exec("ALTER TABLE $name ADD (". implode(', ', $fields).')');
486 if (PEAR::isError($result)) {
487 return $result;
488 }
489 }
490
491 if (!empty($changes['change']) && is_array($changes['change'])) {
492 $fields = array();
493 foreach ($changes['change'] as $field_name => $field) {
494 $fields[] = $field_name. ' ' . $db->getDeclaration($field['definition']['type'], '', $field['definition']);
495 }
496 $result = $db->exec("ALTER TABLE $name MODIFY (". implode(', ', $fields).')');
497 if (PEAR::isError($result)) {
498 return $result;
499 }
500 }
501
502 if (!empty($changes['rename']) && is_array($changes['rename'])) {
503 foreach ($changes['rename'] as $field_name => $field) {
504 $field_name = $db->quoteIdentifier($field_name, true);
505 //$query = "ALTER TABLE $name RENAME COLUMN $field_name TO ".$db->quoteIdentifier($field['name']);
506 // Disabled case sensitive renaming smeyer
507 $query = "ALTER TABLE $name RENAME COLUMN $field_name TO ".$field['name'];
508 $result = $db->exec($query);
509 if (PEAR::isError($result)) {
510 return $result;
511 }
512 }
513 }
514
515 if (!empty($changes['remove']) && is_array($changes['remove'])) {
516 $fields = array();
517 foreach ($changes['remove'] as $field_name => $field) {
518 $fields[] = $db->quoteIdentifier($field_name, true);
519 }
520 $result = $db->exec("ALTER TABLE $name DROP COLUMN ". implode(', ', $fields));
521 if (PEAR::isError($result)) {
522 return $result;
523 }
524 }
525
526 if (!empty($changes['name'])) {
527 $change_name = $db->quoteIdentifier($changes['name'], true);
528 $result = $db->exec("ALTER TABLE $name RENAME TO ".$change_name);
529 if (PEAR::isError($result)) {
530 return $result;
531 }
532 }
533
534 return MDB2_OK;
535 }
536
537 // }}}
538 // {{{ listDatabases()
539
546 function listDatabases()
547 {
548 $db =& $this->getDBInstance();
549 if (PEAR::isError($db)) {
550 return $db;
551 }
552
553 if (!$db->options['emulate_database']) {
554 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
555 'database listing is only supported if the "emulate_database" option is enabled', __FUNCTION__);
556 }
557
558 if ($db->options['database_name_prefix']) {
559 $query = 'SELECT SUBSTR(username, ';
560 $query.= (strlen($db->options['database_name_prefix'])+1);
561 $query.= ") FROM sys.dba_users WHERE username LIKE '";
562 $query.= $db->options['database_name_prefix']."%'";
563 } else {
564 $query = 'SELECT username FROM sys.dba_users';
565 }
566 $result2 = $db->standaloneQuery($query, array('text'), false);
567 if (PEAR::isError($result2)) {
568 return $result2;
569 }
570 $result = $result2->fetchCol();
571 if (PEAR::isError($result)) {
572 return $result;
573 }
574 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
575 && $db->options['field_case'] == CASE_LOWER
576 ) {
577 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
578 }
579 $result2->free();
580 return $result;
581 }
582
583 // }}}
584 // {{{ listUsers()
585
592 function listUsers()
593 {
594 $db =& $this->getDBInstance();
595 if (PEAR::isError($db)) {
596 return $db;
597 }
598
599 if ($db->options['emulate_database'] && $db->options['database_name_prefix']) {
600 $query = 'SELECT SUBSTR(username, ';
601 $query.= (strlen($db->options['database_name_prefix'])+1);
602 $query.= ") FROM sys.dba_users WHERE username NOT LIKE '";
603 $query.= $db->options['database_name_prefix']."%'";
604 } else {
605 $query = 'SELECT username FROM sys.dba_users';
606 }
607 return $db->queryCol($query);
608 }
609
610 // }}}
611 // {{{ listViews()
612
619 function listViews()
620 {
621 $db =& $this->getDBInstance();
622 if (PEAR::isError($db)) {
623 return $db;
624 }
625
626 $query = 'SELECT view_name FROM sys.user_views';
627 $result = $db->queryCol($query);
628 if (PEAR::isError($result)) {
629 return $result;
630 }
631 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
632 && $db->options['field_case'] == CASE_LOWER
633 ) {
634 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
635 }
636 return $result;
637 }
638
639 // }}}
640 // {{{ listFunctions()
641
648 function listFunctions()
649 {
650 $db =& $this->getDBInstance();
651 if (PEAR::isError($db)) {
652 return $db;
653 }
654
655 $query = "SELECT name FROM sys.user_source WHERE line = 1 AND type = 'FUNCTION'";
656 $result = $db->queryCol($query);
657 if (PEAR::isError($result)) {
658 return $result;
659 }
660 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
661 && $db->options['field_case'] == CASE_LOWER
662 ) {
663 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
664 }
665 return $result;
666 }
667
668 // }}}
669 // {{{ listTables()
670
677 function listTables()
678 {
679 $db =& $this->getDBInstance();
680 if (PEAR::isError($db)) {
681 return $db;
682 }
683
684 $query = 'SELECT table_name FROM sys.user_tables';
685 $result = $db->queryCol($query);
686 if (PEAR::isError($result)) {
687 return $result;
688 }
689 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
690 && $db->options['field_case'] == CASE_LOWER
691 ) {
692 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
693 }
694 return $result;
695 }
696
697 // }}}
698 // {{{ listTableFields()
699
707 function listTableFields($table)
708 {
709 $db =& $this->getDBInstance();
710 if (PEAR::isError($db)) {
711 return $db;
712 }
713
714 $table = $db->quote($table, 'text');
715 $query = 'SELECT column_name FROM user_tab_columns';
716 $query.= ' WHERE table_name='.$table.' OR table_name='.strtoupper($table).' ORDER BY column_id';
717 $result = $db->queryCol($query);
718 if (PEAR::isError($result)) {
719 return $result;
720 }
721 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
722 && $db->options['field_case'] == CASE_LOWER
723 ) {
724 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
725 }
726 return $result;
727 }
728
729 // }}}
730 // {{{ listTableIndexes()
731
739 function listTableIndexes($table)
740 {
741 $db =& $this->getDBInstance();
742 if (PEAR::isError($db)) {
743 return $db;
744 }
745
746 $table = $db->quote($table, 'text');
747 $query = 'SELECT index_name name FROM user_indexes';
748 $query.= ' WHERE (table_name='.$table.' OR table_name='.strtoupper($table);
749 $query.= ') AND generated=' .$db->quote('N', 'text');
750 $indexes = $db->queryCol($query, 'text');
751 if (PEAR::isError($indexes)) {
752 return $indexes;
753 }
754
755 $result = array();
756 foreach ($indexes as $index) {
757 $index = $this->_fixIndexName($index);
758 if (!empty($index)) {
759 $result[$index] = true;
760 }
761 }
762
763 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
764 && $db->options['field_case'] == CASE_LOWER
765 ) {
766 $result = array_change_key_case($result, $db->options['field_case']);
767 }
768 return array_keys($result);
769 }
770
771 // }}}
772 // {{{ listTableConstraints()
773
781 function listTableConstraints($table)
782 {
783 $db =& $this->getDBInstance();
784 if (PEAR::isError($db)) {
785 return $db;
786 }
787
788 $table = $db->quote($table, 'text');
789 $query = 'SELECT constraint_name name FROM user_constraints';
790 $query.= ' WHERE table_name='.$table.' OR table_name='.strtoupper($table);
791 $constraints = $db->queryCol($query);
792 if (PEAR::isError($constraints)) {
793 return $constraints;
794 }
795
796 $result = array();
797 foreach ($constraints as $constraint) {
798 $constraint = $this->_fixIndexName($constraint);
799 if (!empty($constraint)) {
800 $result[$constraint] = true;
801 }
802 }
803
804 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
805 && $db->options['field_case'] == CASE_LOWER
806 ) {
807 $result = array_change_key_case($result, $db->options['field_case']);
808 }
809 return array_keys($result);
810 }
811
812 // }}}
813 // {{{ createSequence()
814
824 function createSequence($seq_name, $start = 1)
825 {
826 $db =& $this->getDBInstance();
827 if (PEAR::isError($db)) {
828 return $db;
829 }
830
831 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
832 $query = "CREATE SEQUENCE $sequence_name START WITH $start INCREMENT BY 1 NOCACHE";
833 $query.= ($start < 1 ? " MINVALUE $start" : '');
834 return $db->exec($query);
835 }
836
837 // }}}
838 // {{{ dropSequence()
839
848 function dropSequence($seq_name)
849 {
850 $db =& $this->getDBInstance();
851 if (PEAR::isError($db)) {
852 return $db;
853 }
854
855 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
856 return $db->exec("DROP SEQUENCE $sequence_name");
857 }
858
859 // }}}
860 // {{{ listSequences()
861
868 function listSequences()
869 {
870 $db =& $this->getDBInstance();
871 if (PEAR::isError($db)) {
872 return $db;
873 }
874
875 $query = "SELECT sequence_name FROM sys.user_sequences";
876 $table_names = $db->queryCol($query);
877 if (PEAR::isError($table_names)) {
878 return $table_names;
879 }
880 $result = array();
881 foreach ($table_names as $table_name) {
882 $result[] = $this->_fixSequenceName($table_name);
883 }
884 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
885 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
886 }
887 return $result;
888 }
889}
890?>
$result
$result
Definition: CleanUpTest.php:407
MDB2_ERROR_UNSUPPORTED
const MDB2_ERROR_UNSUPPORTED
Definition: MDB2.php:73
MDB2_OK
const MDB2_OK
The method mapErrorCode in each MDB2_dbtype implementation maps native error codes to one of these.
Definition: MDB2.php:67
MDB2_Driver_Manager_Common
Definition: Common.php:62
MDB2_Driver_Manager_Common\createConstraint
createConstraint($table, $name, $definition)
create a constraint on a table
Definition: Common.php:729
MDB2_Driver_Manager_oci8
Definition: oci8.php:57
MDB2_Driver_Manager_oci8\dropDatabase
dropDatabase($name)
drop an existing database
Definition: oci8.php:115
MDB2_Driver_Manager_oci8\createSequence
createSequence($seq_name, $start=1)
create sequence
Definition: oci8.php:824
MDB2_Driver_Manager_oci8\createDatabase
createDatabase($name)
create a new database
Definition: oci8.php:68
MDB2_Driver_Manager_oci8\_makeAutoincrement
_makeAutoincrement($name, $table, $start=1)
add an autoincrement sequence + trigger
Definition: oci8.php:144
MDB2_Module_Common\getDBInstance
& getDBInstance()
Get the instance of MDB2 associated with the module instance.
Definition: MDB2.php:4206
PEAR\isError
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:279