ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
47 require_once 'MDB2/Driver/Manager/Common.php';
48 
56 class 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 = '
185 CREATE TRIGGER '.$trigger_name.'
186  BEFORE INSERT
187  ON '.$table.'
188  FOR EACH ROW
189 DECLARE
190  last_Sequence NUMBER;
191  last_InsertID NUMBER;
192 BEGIN
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;
205 END;
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 ?>