crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/packages/DB/Table/Manager/mysql.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: mysql.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_mysql {
  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         $key_name = 'Key_name';
  98         $non_unique = 'Non_unique';
  99
 100         $query = "SHOW INDEX FROM $table";
 101         $indexes = $this->_db->getAll($query, null, DB_FETCHMODE_ASSOC);
 102         if (PEAR::isError($indexes)) {
 103             return $indexes;
 104         }
 105
 106         $result = array();
 107         foreach ($indexes as $index_data) {
 108             if ($index_data[$non_unique]) {
 109                 $result[$index_data[$key_name]] = true;
 110             }
 111         }
 112         $result = array_change_key_case($result, CASE_LOWER);
 113
 114         return array_keys($result);
 115     }
 116
 117
 118     /**
 119      * list all constraints in a table
 120      *
 121      * @param string    $table      name of table that should be used in method
 122      * @return mixed data array on success, a PEAR error on failure
 123      * @access public
 124      */
 125     function listTableConstraints($table)
 126     {
 127         $key_name = 'Key_name';
 128         $non_unique = 'Non_unique';
 129
 130         $query = "SHOW INDEX FROM $table";
 131         $indexes = $this->_db->getAll($query, null, DB_FETCHMODE_ASSOC);
 132         if (PEAR::isError($indexes)) {
 133             return $indexes;
 134         }
 135
 136         $result = array();
 137         foreach ($indexes as $index_data) {
 138             if (!$index_data[$non_unique]) {
 139                 if ($index_data[$key_name] !== 'PRIMARY') {
 140                     $index = $index_data[$key_name];
 141                 } else {
 142                     $index = 'PRIMARY';
 143                 }
 144                 $result[$index] = true;
 145             }
 146         }
 147         $result = array_change_key_case($result, CASE_LOWER);
 148
 149         return array_keys($result);
 150     }
 151
 152
 153     /**
 154      * get the structure of an index into an array
 155      *
 156      * @param string    $table      name of table that should be used in method
 157      * @param string    $index_name name of index that should be used in method
 158      * @return mixed data array on success, a PEAR error on failure
 159      * @access public
 160      */
 161     function getTableIndexDefinition($table, $index_name)
 162     {
 163         $result = $this->_db->query("SHOW INDEX FROM $table");
 164         if (PEAR::isError($result)) {
 165             return $result;
 166         }
 167
 168         $definition = array();
 169         while (is_array($row = $result->fetchRow(DB_FETCHMODE_ASSOC))) {
 170             $row = array_change_key_case($row, CASE_LOWER);
 171             $key_name = $row['key_name'];
 172             $key_name = strtolower($key_name);
 173
 174             if ($index_name == $key_name) {
 175                 $column_name = $row['column_name'];
 176                 $column_name = strtolower($column_name);
 177                 $definition['fields'][$column_name] = array();
 178                 if (array_key_exists('collation', $row)) {
 179                     $definition['fields'][$column_name]['sorting'] = ($row['collation'] == 'A'
 180                         ? 'ascending' : 'descending');
 181                 }
 182             }
 183         }
 184
 185         $result->free();
 186
 187         return $definition;
 188     }
 189
 190
 191     /**
 192      * get the structure of a constraint into an array
 193      *
 194      * @param string    $table      name of table that should be used in method
 195      * @param string    $index_name name of index that should be used in method
 196      * @return mixed data array on success, a PEAR error on failure
 197      * @access public
 198      */
 199     function getTableConstraintDefinition($table, $index_name)
 200     {
 201         $result = $this->_db->query("SHOW INDEX FROM $table");
 202         if (PEAR::isError($result)) {
 203             return $result;
 204         }
 205
 206         $definition = array();
 207         while (is_array($row = $result->fetchRow(DB_FETCHMODE_ASSOC))) {
 208             $row = array_change_key_case($row, CASE_LOWER);
 209             $key_name = $row['key_name'];
 210             $key_name = strtolower($key_name);
 211
 212             if ($index_name == $key_name) {
 213                 if ($row['key_name'] == 'PRIMARY') {
 214                     $definition['primary'] = true;
 215                 } else {
 216                     $definition['unique'] = true;
 217                 }
 218                 $column_name = $row['column_name'];
 219                 $column_name = strtolower($column_name);
 220                 $definition['fields'][$column_name] = array();
 221                 if (array_key_exists('collation', $row)) {
 222                     $definition['fields'][$column_name]['sorting'] = ($row['collation'] == 'A'
 223                         ? 'ascending' : 'descending');
 224                 }
 225             }
 226         }
 227
 228         $result->free();
 229
 230         return $definition;
 231     }
 232
 233
 234     /**
 235      * drop existing index
 236      *
 237      * @param string    $table         name of table that should be used in method
 238      * @param string    $name         name of the index to be dropped
 239      * @return mixed DB_OK on success, a PEAR error on failure
 240      * @access public
 241      */
 242     function dropIndex($table, $name)
 243     {
 244         $table = $this->_db->quoteIdentifier($table);
 245         $name = $this->_db->quoteIdentifier($name);
 246         return $this->_db->query("DROP INDEX $name ON $table");
 247     }
 248
 249
 250     /**
 251      * drop existing constraint
 252      *
 253      * @param string    $table         name of table that should be used in method
 254      * @param string    $name         name of the constraint to be dropped
 255      * @return mixed DB_OK on success, a PEAR error on failure
 256      * @access public
 257      */
 258     function dropConstraint($table, $name)
 259     {
 260         $table = $this->_db->quoteIdentifier($table);
 261         if (strtolower($name) == 'primary') {
 262             $query = "ALTER TABLE $table DROP PRIMARY KEY";
 263         } else {
 264             $name = $this->_db->quoteIdentifier($name);
 265             $query = "ALTER TABLE $table DROP INDEX $name";
 266         }
 267         return $this->_db->query($query);
 268     }
 269
 270
 271     /**
 272      * alter an existing table
 273      *
 274      * @param string $name         name of the table that is intended to be changed.
 275      * @param array $changes     associative array that contains the details of each type
 276      *                             of change that is intended to be performed. The types of
 277      *                             changes that are currently supported are defined as follows:
 278      *
 279      *                             name
 280      *
 281      *                                New name for the table.
 282      *
 283      *                            add
 284      *
 285      *                                Associative array with the names of fields to be added as
 286      *                                 indexes of the array. The value of each entry of the array
 287      *                                 should be set to another associative array with the properties
 288      *                                 of the fields to be added. The properties of the fields should
 289      *                                 be the same as defined by the Metabase parser.
 290      *
 291      *
 292      *                            remove
 293      *
 294      *                                Associative array with the names of fields to be removed as indexes
 295      *                                 of the array. Currently the values assigned to each entry are ignored.
 296      *                                 An empty array should be used for future compatibility.
 297      *
 298      *                            rename
 299      *
 300      *                                Associative array with the names of fields to be renamed as indexes
 301      *                                 of the array. The value of each entry of the array should be set to
 302      *                                 another associative array with the entry named name with the new
 303      *                                 field name and the entry named Declaration that is expected to contain
 304      *                                 the portion of the field declaration already in DBMS specific SQL code
 305      *                                 as it is used in the CREATE TABLE statement.
 306      *
 307      *                            change
 308      *
 309      *                                Associative array with the names of the fields to be changed as indexes
 310      *                                 of the array. Keep in mind that if it is intended to change either the
 311      *                                 name of a field and any other properties, the change array entries
 312      *                                 should have the new names of the fields as array indexes.
 313      *
 314      *                                The value of each entry of the array should be set to another associative
 315      *                                 array with the properties of the fields to that are meant to be changed as
 316      *                                 array entries. These entries should be assigned to the new values of the
 317      *                                 respective properties. The properties of the fields should be the same
 318      *                                 as defined by the Metabase parser.
 319      *
 320      *                            Example
 321      *                                array(
 322      *                                    'name' => 'userlist',
 323      *                                    'add' => array(
 324      *                                        'quota' => array(
 325      *                                            'type' => 'integer',
 326      *                                            'unsigned' => 1
 327      *                                        )
 328      *                                    ),
 329      *                                    'remove' => array(
 330      *                                        'file_limit' => array(),
 331      *                                        'time_limit' => array()
 332      *                                    ),
 333      *                                    'change' => array(
 334      *                                        'name' => array(
 335      *                                            'length' => '20',
 336      *                                            'definition' => array(
 337      *                                                'type' => 'text',
 338      *                                                'length' => 20,
 339      *                                            ),
 340      *                                        )
 341      *                                    ),
 342      *                                    'rename' => array(
 343      *                                        'sex' => array(
 344      *                                            'name' => 'gender',
 345      *                                            'definition' => array(
 346      *                                                'type' => 'text',
 347      *                                                'length' => 1,
 348      *                                                'default' => 'M',
 349      *                                            ),
 350      *                                        )
 351      *                                    )
 352      *                                )
 353      *
 354      * @param boolean $check     (ignored in DB_Table)
 355      * @access public
 356      *
 357      * @return mixed DB_OK on success, a PEAR error on failure
 358      */
 359     function alterTable($name, $changes, $check)
 360     {
 361         foreach ($changes as $change_name => $change) {
 362             switch ($change_name) {
 363             case 'add':
 364             case 'remove':
 365             case 'change':
 366             case 'rename':
 367             case 'name':
 368                 break;
 369             default:
 370                 return DB_Table::throwError(DB_TABLE_ERR_ALTER_TABLE_IMPOS);
 371             }
 372         }
 373
 374         $query = '';
 375         if (array_key_exists('name', $changes)) {
 376             $change_name = $this->_db->quoteIdentifier($changes['name']);
 377             $query .= 'RENAME TO ' . $change_name;
 378         }
 379
 380         if (array_key_exists('add', $changes)) {
 381             foreach ($changes['add'] as $field_name => $field) {
 382                 if ($query) {
 383                     $query.= ', ';
 384                 }
 385                 $query.= 'ADD ' . $field_name . ' ' . $field;
 386             }
 387         }
 388
 389         if (array_key_exists('remove', $changes)) {
 390             foreach ($changes['remove'] as $field_name => $field) {
 391                 if ($query) {
 392                     $query.= ', ';
 393                 }
 394                 $field_name = $this->_db->quoteIdentifier($field_name);
 395                 $query.= 'DROP ' . $field_name;
 396             }
 397         }
 398
 399         $rename = array();
 400         if (array_key_exists('rename', $changes)) {
 401             foreach ($changes['rename'] as $field_name => $field) {
 402                 $rename[$field['name']] = $field_name;
 403             }
 404         }
 405
 406         if (array_key_exists('change', $changes)) {
 407             foreach ($changes['change'] as $field_name => $field) {
 408                 if ($query) {
 409                     $query.= ', ';
 410                 }
 411                 if (isset($rename[$field_name])) {
 412                     $old_field_name = $rename[$field_name];
 413                     unset($rename[$field_name]);
 414                 } else {
 415                     $old_field_name = $field_name;
 416                 }
 417                 $old_field_name = $this->_db->quoteIdentifier($old_field_name);
 418                 $query.= "CHANGE $old_field_name " . $field_name . ' ' . $field['definition'];
 419             }
 420         }
 421
 422         if (!empty($rename)) {
 423             foreach ($rename as $rename_name => $renamed_field) {
 424                 if ($query) {
 425                     $query.= ', ';
 426                 }
 427                 $field = $changes['rename'][$renamed_field];
 428                 $renamed_field = $this->_db->quoteIdentifier($renamed_field);
 429                 $query.= 'CHANGE ' . $renamed_field . ' ' . $field['name'] . ' ' . $renamed_field['definition'];
 430             }
 431         }
 432
 433         if (!$query) {
 434             return DB_OK;
 435         }
 436
 437         $name = $this->_db->quoteIdentifier($name);
 438         return $this->_db->query("ALTER TABLE $name $query");
 439     }
 440
 441 }
 442
 443 ?>