crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/packages/DB/Table/Manager/oci8.php

   1 <?php
   2
   3 /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
   4
   5 /**
   6  * Index, constraint and alter methods for DB_Table usage with
   7  * PEAR::DB as backend.
   8  *
   9  * The code in this class was adopted from the MDB2 PEAR package.
  10  *
  11  * PHP versions 4 and 5
  12  *
  13  * LICENSE:
  14  *
  15  * Copyright (c) 1997-2007, Lukas Smith <smith@pooteeweet.org>
  16  *                          Paul M. Jones <pmjones@php.net>
  17  *                          David C. Morse <morse@php.net>
  18  *                          Mark Wiesemann <wiesemann@php.net>
  19  * All rights reserved.
  20  *
  21  * Redistribution and use in source and binary forms, with or without
  22  * modification, are permitted provided that the following conditions
  23  * are met:
  24  *
  25  *    * Redistributions of source code must retain the above copyright
  26  *      notice, this list of conditions and the following disclaimer.
  27  *    * Redistributions in binary form must reproduce the above copyright
  28  *      notice, this list of conditions and the following disclaimer in the
  29  *      documentation and/or other materials provided with the distribution.
  30  *    * The names of the authors may not be used to endorse or promote products
  31  *      derived from this software without specific prior written permission.
  32  *
  33  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
  34  * IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
  35  * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
  36  * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
  37  * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
  38  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
  39  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
  40  * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
  41  * OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
  42  * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  43  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  44  *
  45  * @category Database
  46  * @package  DB_Table
  47  * @author   Lukas Smith <smith@pooteeweet.org>
  48  * @author   Mark Wiesemann <wiesemann@php.net>
  49  * @license  http://opensource.org/licenses/bsd-license.php New BSD License
  50  * @version  CVS: $Id: oci8.php,v 1.5 2007/12/13 16:52:15 wiesemann Exp $
  51  * @link     http://pear.php.net/package/DB_Table
  52  */
  53
  54 /**
  55  * require DB_Table class
  56  */
  57 require_once 'DB/Table.php';
  58
  59 /**
  60  * Index, constraint and alter methods for DB_Table usage with
  61  * PEAR::DB as backend.
  62  *
  63  * The code in this class was adopted from the MDB2 PEAR package.
  64  *
  65  * @category Database
  66  * @package  DB_Table
  67  * @author   Lukas Smith <smith@pooteeweet.org>
  68  * @author   Mark Wiesemann <wiesemann@php.net>
  69  * @version  Release: 1.5.6
  70  * @link     http://pear.php.net/package/DB_Table
  71  */
  72
  73 class DB_Table_Manager_oci8 {
  74
  75     /**
  76     *
  77     * The PEAR DB object that connects to the database.
  78     *
  79     * @access private
  80     *
  81     * @var object
  82     *
  83     */
  84
  85     var $_db = null;
  86
  87
  88     /**
  89      * list all indexes in a table
  90      *
  91      * @param string    $table      name of table that should be used in method
  92      * @return mixed data array on success, a PEAR error on failure
  93      * @access public
  94      */
  95     function listTableIndexes($table)
  96     {
  97         $table = strtoupper($table);
  98         $query = "SELECT index_name name FROM user_indexes WHERE table_name='$table'";
  99         $indexes = $this->_db->getCol($query);
 100         if (PEAR::isError($indexes)) {
 101             return $indexes;
 102         }
 103
 104         $result = array();
 105         foreach ($indexes as $index) {
 106             $result[$index] = true;
 107         }
 108         $result = array_change_key_case($result, CASE_LOWER);
 109
 110         return array_keys($result);
 111     }
 112
 113
 114     /**
 115      * list all constraints in a table
 116      *
 117      * @param string    $table      name of table that should be used in method
 118      * @return mixed data array on success, a PEAR error on failure
 119      * @access public
 120      */
 121     function listTableConstraints($table)
 122     {
 123         $table = strtoupper($table);
 124         $query = "SELECT index_name name FROM user_constraints WHERE table_name='$table'";
 125         $constraints = $this->_db->getCol($query);
 126         if (PEAR::isError($constraints)) {
 127             return $constraints;
 128         }
 129
 130         $result = array();
 131         foreach ($constraints as $constraint) {
 132             $result[$constraint] = true;
 133         }
 134         $result = array_change_key_case($result, CASE_LOWER);
 135
 136         return array_keys($result);
 137     }
 138
 139
 140     /**
 141      * get the structure of an index into an array
 142      *
 143      * @param string    $table      name of table that should be used in method
 144      * @param string    $index_name name of index that should be used in method
 145      * @return mixed data array on success, a PEAR error on failure
 146      * @access public
 147      */
 148     function getTableIndexDefinition($table, $index_name)
 149     {
 150         $index_name = $this->_db->quoteSmart(strtoupper($index_name));
 151         $table = $this->_db->quoteSmart(strtoupper($table));
 152         $query = "SELECT * FROM user_indexes where table_name = $table AND index_name = $index_name";
 153         $row = $this->_db->getRow($query, null, DB_FETCHMODE_ASSOC);
 154         if (PEAR::isError($row)) {
 155             return $row;
 156         }
 157
 158         $definition = array();
 159         if ($row) {
 160             $row = array_change_key_case($row, CASE_LOWER);
 161             $key_name = $row['index_name'];
 162             $key_name = strtolower($key_name);
 163             $query = "SELECT * FROM user_ind_columns WHERE index_name = $index_name AND table_name = $table";
 164             $result = $this->_db->query($query);
 165             if (PEAR::isError($result)) {
 166                 return $result;
 167             }
 168             while ($colrow = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
 169                 $column_name = $colrow['column_name'];
 170                 $column_name = strtolower($column_name);
 171                 $definition['fields'][$column_name] = array();
 172                 if (array_key_exists('descend', $colrow)) {
 173                     $definition['fields'][$column_name]['sorting'] =
 174                         ($colrow['descend'] == 'ASC' ? 'ascending' : 'descending');
 175                 }
 176             }
 177             $result->free();
 178         }
 179
 180         return $definition;
 181     }
 182
 183
 184     /**
 185      * get the structure of a constraint into an array
 186      *
 187      * @param string    $table      name of table that should be used in method
 188      * @param string    $index_name name of index that should be used in method
 189      * @return mixed data array on success, a PEAR error on failure
 190      * @access public
 191      */
 192     function getTableConstraintDefinition($table, $index_name)
 193     {
 194         $dsn = DB::parseDSN($this->_db->dsn);
 195         vd($dsn);
 196         $dbName = $this->_db->quoteSmart($dsn['database']);
 197         $index_name = $this->_db->quoteSmart($index_name);
 198         $table = $this->_db->quoteSmart($table);
 199         $query = "SELECT * FROM all_contraints WHERE owner = $dbName AND table_name = $table AND index_name = $index_name";
 200         $result = $this->_db->query($query);
 201         if (PEAR::isError($result)) {
 202             return $result;
 203         }
 204
 205         $definition = array();
 206         while (is_array($row = $result->fetchRow(DB_FETCHMODE_ASSOC))) {
 207             $row = array_change_key_case($row, CASE_LOWER);
 208             $key_name = $row['constraint_name'];
 209             $key_name = strtolower($key_name);
 210             if ($row) {
 211                 $definition['primary'] = $row['constraint_type'] == 'P';
 212                 $definition['unique'] = $row['constraint_type'] == 'U';
 213
 214                 $query = 'SELECT * FROM all_cons_columns WHERE contraint_name = ';
 215                 $query.= $this->_db->quoteSmart($key_name).' AND table_name = '.$table;
 216                 $colres = $this->_db->query($query);
 217                 while ($colrow = $colres->fetchRow(DB_FETCHMODE_ASSOC)) {
 218                     $column_name = $row['column_name'];
 219                     $column_name = strtolower($column_name);
 220                     $definition['fields'][$column_name] = array();
 221                 }
 222             }
 223         }
 224         $result->free();
 225
 226         return $definition;
 227     }
 228
 229
 230     /**
 231      * drop existing index
 232      *
 233      * @param string    $table         name of table that should be used in method
 234      * @param string    $name         name of the index to be dropped
 235      * @return mixed DB_OK on success, a PEAR error on failure
 236      * @access public
 237      */
 238     function dropIndex($table, $name)
 239     {
 240         $name = $this->_db->quoteIdentifier($name);
 241         return $this->_db->query("DROP INDEX $name");
 242     }
 243
 244
 245     /**
 246      * drop existing constraint
 247      *
 248      * @param string    $table         name of table that should be used in method
 249      * @param string    $name         name of the constraint to be dropped
 250      * @return mixed DB_OK on success, a PEAR error on failure
 251      * @access public
 252      */
 253     function dropConstraint($table, $name)
 254     {
 255         $table = $this->_db->quoteIdentifier($table);
 256         $name = $this->_db->quoteIdentifier($name);
 257         return $this->_db->query("ALTER TABLE $table DROP CONSTRAINT $name");
 258     }
 259
 260
 261     /**
 262      * alter an existing table
 263      *
 264      * @param string $name         name of the table that is intended to be changed.
 265      * @param array $changes     associative array that contains the details of each type
 266      *                             of change that is intended to be performed. The types of
 267      *                             changes that are currently supported are defined as follows:
 268      *
 269      *                             name
 270      *
 271      *                                New name for the table.
 272      *
 273      *                            add
 274      *
 275      *                                Associative array with the names of fields to be added as
 276      *                                 indexes of the array. The value of each entry of the array
 277      *                                 should be set to another associative array with the properties
 278      *                                 of the fields to be added. The properties of the fields should
 279      *                                 be the same as defined by the Metabase parser.
 280      *
 281      *
 282      *                            remove
 283      *
 284      *                                Associative array with the names of fields to be removed as indexes
 285      *                                 of the array. Currently the values assigned to each entry are ignored.
 286      *                                 An empty array should be used for future compatibility.
 287      *
 288      *                            rename
 289      *
 290      *                                Associative array with the names of fields to be renamed as indexes
 291      *                                 of the array. The value of each entry of the array should be set to
 292      *                                 another associative array with the entry named name with the new
 293      *                                 field name and the entry named Declaration that is expected to contain
 294      *                                 the portion of the field declaration already in DBMS specific SQL code
 295      *                                 as it is used in the CREATE TABLE statement.
 296      *
 297      *                            change
 298      *
 299      *                                Associative array with the names of the fields to be changed as indexes
 300      *                                 of the array. Keep in mind that if it is intended to change either the
 301      *                                 name of a field and any other properties, the change array entries
 302      *                                 should have the new names of the fields as array indexes.
 303      *
 304      *                                The value of each entry of the array should be set to another associative
 305      *                                 array with the properties of the fields to that are meant to be changed as
 306      *                                 array entries. These entries should be assigned to the new values of the
 307      *                                 respective properties. The properties of the fields should be the same
 308      *                                 as defined by the Metabase parser.
 309      *
 310      *                            Example
 311      *                                array(
 312      *                                    'name' => 'userlist',
 313      *                                    'add' => array(
 314      *                                        'quota' => array(
 315      *                                            'type' => 'integer',
 316      *                                            'unsigned' => 1
 317      *                                        )
 318      *                                    ),
 319      *                                    'remove' => array(
 320      *                                        'file_limit' => array(),
 321      *                                        'time_limit' => array()
 322      *                                    ),
 323      *                                    'change' => array(
 324      *                                        'name' => array(
 325      *                                            'length' => '20',
 326      *                                            'definition' => array(
 327      *                                                'type' => 'text',
 328      *                                                'length' => 20,
 329      *                                            ),
 330      *                                        )
 331      *                                    ),
 332      *                                    'rename' => array(
 333      *                                        'sex' => array(
 334      *                                            'name' => 'gender',
 335      *                                            'definition' => array(
 336      *                                                'type' => 'text',
 337      *                                                'length' => 1,
 338      *                                                'default' => 'M',
 339      *                                            ),
 340      *                                        )
 341      *                                    )
 342      *                                )
 343      *
 344      * @param boolean $check     (ignored in DB_Table)
 345      * @access public
 346      *
 347      * @return mixed DB_OK on success, a PEAR error on failure
 348      */
 349     function alterTable($name, $changes, $check)
 350     {
 351         foreach ($changes as $change_name => $change) {
 352             switch ($change_name) {
 353             case 'add':
 354             case 'remove':
 355             case 'change':
 356             case 'name':
 357             case 'rename':
 358                 break;
 359             default:
 360                 return DB_Table::throwError(DB_TABLE_ERR_ALTER_TABLE_IMPOS);
 361             }
 362         }
 363
 364         $name = $this->_db->quoteIdentifier($name);
 365
 366         if (array_key_exists('add', $changes)) {
 367             $fields = array();
 368             foreach ($changes['add'] as $field_name => $field) {
 369                 $fields[] = $field_name . ' ' . $field;
 370             }
 371             $result = $this->_db->query("ALTER TABLE $name ADD (". implode(', ', $fields).')');
 372             if (PEAR::isError($result)) {
 373                 return $result;
 374             }
 375         }
 376
 377         if (array_key_exists('change', $changes)) {
 378             $fields = array();
 379             foreach ($changes['change'] as $field_name => $field) {
 380                 $fields[] = $field_name. ' ' . $field['definition'];
 381             }
 382             $result = $this->_db->query("ALTER TABLE $name MODIFY (". implode(', ', $fields).')');
 383             if (PEAR::isError($result)) {
 384                 return $result;
 385             }
 386         }
 387
 388         if (array_key_exists('rename', $changes)) {
 389             foreach ($changes['rename'] as $field_name => $field) {
 390                 $field_name = $this->_db->quoteIdentifier($field_name);
 391                 $query = "ALTER TABLE $name RENAME COLUMN $field_name TO ".$this->_db->quoteIdentifier($field['name']);
 392                 $result = $this->_db->query($query);
 393                 if (PEAR::isError($result)) {
 394                     return $result;
 395                 }
 396             }
 397         }
 398
 399         if (array_key_exists('remove', $changes)) {
 400             $fields = array();
 401             foreach ($changes['remove'] as $field_name => $field) {
 402                 $fields[] = $this->_db->quoteIdentifier($field_name);
 403             }
 404             $result = $this->_db->query("ALTER TABLE $name DROP COLUMN ". implode(', ', $fields));
 405             if (PEAR::isError($result)) {
 406                 return $result;
 407             }
 408         }
 409
 410         if (array_key_exists('name', $changes)) {
 411             $change_name = $this->_db->quoteIdentifier($changes['name']);
 412             $result = $this->_db->query("ALTER TABLE $name RENAME TO ".$change_name);
 413             if (PEAR::isError($result)) {
 414                 return $result;
 415             }
 416         }
 417
 418         return DB_OK;
 419     }
 420
 421 }
 422
 423 ?>