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. |
13 // | Redistribution and use in source and binary forms, with or without |
14 // | modification, are permitted provided that the following conditions |
17 // | Redistributions of source code must retain the above copyright |
18 // | notice, this list of conditions and the following disclaimer. |
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. |
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. |
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 // +----------------------------------------------------------------------+
45 // $Id: mysql.php 327310 2012-08-27 15:16:18Z danielc $
48 require_once 'MDB2/Driver/Manager/Common.php';
51 * MDB2 MySQL driver for the management modules
55 * @author Lukas Smith <smith@pooteeweet.org>
57 class MDB2_Driver_Manager_mysql extends MDB2_Driver_Manager_Common
61 // {{{ createDatabase()
64 * create a new database
66 * @param string $name name of the database that should be created
67 * @param array $options array with charset, collation info
69 * @return mixed MDB2_OK on success, a MDB2 error on failure
72 function createDatabase($name, $options = array())
74 $db = $this->getDBInstance();
75 if (MDB2::isError($db)) {
79 $name = $db->quoteIdentifier($name, true);
80 $query = 'CREATE DATABASE ' . $name;
81 if (!empty($options['charset'])) {
82 $query .= ' DEFAULT CHARACTER SET ' . $db->quote($options['charset'], 'text');
84 if (!empty($options['collation'])) {
85 $query .= ' COLLATE ' . $db->quote($options['collation'], 'text');
87 return $db->standaloneQuery($query, null, true);
91 // {{{ alterDatabase()
94 * alter an existing database
96 * @param string $name name of the database that is intended to be changed
97 * @param array $options array with charset, collation info
99 * @return mixed MDB2_OK on success, a MDB2 error on failure
102 function alterDatabase($name, $options = array())
104 $db = $this->getDBInstance();
105 if (MDB2::isError($db)) {
109 $query = 'ALTER DATABASE '. $db->quoteIdentifier($name, true);
110 if (!empty($options['charset'])) {
111 $query .= ' DEFAULT CHARACTER SET ' . $db->quote($options['charset'], 'text');
113 if (!empty($options['collation'])) {
114 $query .= ' COLLATE ' . $db->quote($options['collation'], 'text');
116 return $db->standaloneQuery($query, null, true);
120 // {{{ dropDatabase()
123 * drop an existing database
125 * @param string $name name of the database that should be dropped
126 * @return mixed MDB2_OK on success, a MDB2 error on failure
129 function dropDatabase($name)
131 $db = $this->getDBInstance();
132 if (MDB2::isError($db)) {
136 $name = $db->quoteIdentifier($name, true);
137 $query = "DROP DATABASE $name";
138 return $db->standaloneQuery($query, null, true);
142 // {{{ _getAdvancedFKOptions()
145 * Return the FOREIGN KEY query section dealing with non-standard options
146 * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
148 * @param array $definition
152 function _getAdvancedFKOptions($definition)
155 if (!empty($definition['match'])) {
156 $query .= ' MATCH '.$definition['match'];
158 if (!empty($definition['onupdate'])) {
159 $query .= ' ON UPDATE '.$definition['onupdate'];
161 if (!empty($definition['ondelete'])) {
162 $query .= ' ON DELETE '.$definition['ondelete'];
173 * @param string $name Name of the database that should be created
174 * @param array $fields Associative array that contains the definition of each field of the new table
175 * The indexes of the array entries are the names of the fields of the table an
176 * the array entry values are associative arrays like those that are meant to be
177 * passed with the field definitions to get[Type]Declaration() functions.
180 * 'type' => 'integer',
189 * 'password' => array(
194 * @param array $options An associative array of table options:
196 * 'comment' => 'Foo',
197 * 'charset' => 'utf8',
198 * 'collate' => 'utf8_unicode_ci',
199 * 'type' => 'innodb',
202 * @return mixed MDB2_OK on success, a MDB2 error on failure
205 function createTable($name, $fields, $options = array())
207 $db = $this->getDBInstance();
208 if (MDB2::isError($db)) {
212 // if we have an AUTO_INCREMENT column and a PK on more than one field,
213 // we have to handle it differently...
214 $autoincrement = null;
215 if (empty($options['primary'])) {
216 $pk_fields = array();
217 foreach ($fields as $fieldname => $def) {
218 if (!empty($def['primary'])) {
219 $pk_fields[$fieldname] = true;
221 if (!empty($def['autoincrement'])) {
222 $autoincrement = $fieldname;
225 if ((null !== $autoincrement) && count($pk_fields) > 1) {
226 $options['primary'] = $pk_fields;
228 // the PK constraint is on max one field => OK
229 $autoincrement = null;
233 $query = $this->_getCreateTableQuery($name, $fields, $options);
234 if (MDB2::isError($query)) {
238 if (null !== $autoincrement) {
239 // we have to remove the PK clause added by _getIntegerDeclaration()
240 $query = str_replace('AUTO_INCREMENT PRIMARY KEY', 'AUTO_INCREMENT', $query);
243 $options_strings = array();
245 if (!empty($options['comment'])) {
246 $options_strings['comment'] = 'COMMENT = '.$db->quote($options['comment'], 'text');
249 if (!empty($options['charset'])) {
250 $options_strings['charset'] = 'DEFAULT CHARACTER SET '.$options['charset'];
251 if (!empty($options['collate'])) {
252 $options_strings['charset'].= ' COLLATE '.$options['collate'];
257 if (!empty($options['type'])) {
258 $type = $options['type'];
259 } elseif ($db->options['default_table_type']) {
260 $type = $db->options['default_table_type'];
263 $options_strings[] = "ENGINE = $type";
266 if (!empty($options_strings)) {
267 $query .= ' '.implode(' ', $options_strings);
269 $result = $db->exec($query);
270 if (MDB2::isError($result)) {
280 * drop an existing table
282 * @param string $name name of the table that should be dropped
283 * @return mixed MDB2_OK on success, a MDB2 error on failure
286 function dropTable($name)
288 $db = $this->getDBInstance();
289 if (MDB2::isError($db)) {
293 //delete the triggers associated to existing FK constraints
294 $constraints = $this->listTableConstraints($name);
295 if (!MDB2::isError($constraints) && !empty($constraints)) {
296 $db->loadModule('Reverse', null, true);
297 foreach ($constraints as $constraint) {
298 $definition = $db->reverse->getTableConstraintDefinition($name, $constraint);
299 if (!MDB2::isError($definition) && !empty($definition['foreign'])) {
300 $result = $this->_dropFKTriggers($name, $constraint, $definition['references']['table']);
301 if (MDB2::isError($result)) {
308 return parent::dropTable($name);
312 // {{{ truncateTable()
315 * Truncate an existing table (if the TRUNCATE TABLE syntax is not supported,
316 * it falls back to a DELETE FROM TABLE query)
318 * @param string $name name of the table that should be truncated
319 * @return mixed MDB2_OK on success, a MDB2 error on failure
322 function truncateTable($name)
324 $db = $this->getDBInstance();
325 if (MDB2::isError($db)) {
329 $name = $db->quoteIdentifier($name, true);
330 $result = $db->exec("TRUNCATE TABLE $name");
331 if (MDB2::isError($result)) {
341 * Optimize (vacuum) all the tables in the db (or only the specified table)
342 * and optionally run ANALYZE.
344 * @param string $table table name (all the tables if empty)
345 * @param array $options an array with driver-specific options:
346 * - timeout [int] (in seconds) [mssql-only]
347 * - analyze [boolean] [pgsql and mysql]
348 * - full [boolean] [pgsql-only]
349 * - freeze [boolean] [pgsql-only]
351 * @return mixed MDB2_OK success, a MDB2 error on failure
354 function vacuum($table = null, $options = array())
356 $db = $this->getDBInstance();
357 if (MDB2::isError($db)) {
362 $table = $this->listTables();
363 if (MDB2::isError($table)) {
367 if (is_array($table)) {
368 foreach (array_keys($table) as $k) {
369 $table[$k] = $db->quoteIdentifier($table[$k], true);
371 $table = implode(', ', $table);
373 $table = $db->quoteIdentifier($table, true);
376 $result = $db->exec('OPTIMIZE TABLE '.$table);
377 if (MDB2::isError($result)) {
380 if (!empty($options['analyze'])) {
381 $result = $db->exec('ANALYZE TABLE '.$table);
382 if (MDB2::isError($result)) {
393 * alter an existing table
395 * @param string $name name of the table that is intended to be changed.
396 * @param array $changes associative array that contains the details of each type
397 * of change that is intended to be performed. The types of
398 * changes that are currently supported are defined as follows:
402 * New name for the table.
406 * Associative array with the names of fields to be added as
407 * indexes of the array. The value of each entry of the array
408 * should be set to another associative array with the properties
409 * of the fields to be added. The properties of the fields should
410 * be the same as defined by the MDB2 parser.
415 * Associative array with the names of fields to be removed as indexes
416 * of the array. Currently the values assigned to each entry are ignored.
417 * An empty array should be used for future compatibility.
421 * Associative array with the names of fields to be renamed as indexes
422 * of the array. The value of each entry of the array should be set to
423 * another associative array with the entry named name with the new
424 * field name and the entry named Declaration that is expected to contain
425 * the portion of the field declaration already in DBMS specific SQL code
426 * as it is used in the CREATE TABLE statement.
430 * Associative array with the names of the fields to be changed as indexes
431 * of the array. Keep in mind that if it is intended to change either the
432 * name of a field and any other properties, the change array entries
433 * should have the new names of the fields as array indexes.
435 * The value of each entry of the array should be set to another associative
436 * array with the properties of the fields to that are meant to be changed as
437 * array entries. These entries should be assigned to the new values of the
438 * respective properties. The properties of the fields should be the same
439 * as defined by the MDB2 parser.
443 * 'name' => 'userlist',
446 * 'type' => 'integer',
451 * 'file_limit' => array(),
452 * 'time_limit' => array()
457 * 'definition' => array(
465 * 'name' => 'gender',
466 * 'definition' => array(
475 * @param boolean $check indicates whether the function should just check if the DBMS driver
476 * can perform the requested table alterations if the value is true or
477 * actually perform them otherwise.
480 * @return mixed MDB2_OK on success, a MDB2 error on failure
482 function alterTable($name, $changes, $check)
484 $db = $this->getDBInstance();
485 if (MDB2::isError($db)) {
489 foreach ($changes as $change_name => $change) {
490 switch ($change_name) {
498 return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
499 'change type "'.$change_name.'" not yet supported', __FUNCTION__);
508 if (!empty($changes['name'])) {
509 $change_name = $db->quoteIdentifier($changes['name'], true);
510 $query .= 'RENAME TO ' . $change_name;
513 if (!empty($changes['add']) && is_array($changes['add'])) {
514 foreach ($changes['add'] as $field_name => $field) {
518 $query.= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field);
522 if (!empty($changes['remove']) && is_array($changes['remove'])) {
523 foreach ($changes['remove'] as $field_name => $field) {
527 $field_name = $db->quoteIdentifier($field_name, true);
528 $query.= 'DROP ' . $field_name;
533 if (!empty($changes['rename']) && is_array($changes['rename'])) {
534 foreach ($changes['rename'] as $field_name => $field) {
535 $rename[$field['name']] = $field_name;
539 if (!empty($changes['change']) && is_array($changes['change'])) {
540 foreach ($changes['change'] as $field_name => $field) {
544 if (isset($rename[$field_name])) {
545 $old_field_name = $rename[$field_name];
546 unset($rename[$field_name]);
548 $old_field_name = $field_name;
550 $old_field_name = $db->quoteIdentifier($old_field_name, true);
551 $query.= "CHANGE $old_field_name " . $db->getDeclaration($field['definition']['type'], $field_name, $field['definition']);
555 if (!empty($rename) && is_array($rename)) {
556 foreach ($rename as $rename_name => $renamed_field) {
560 $field = $changes['rename'][$renamed_field];
561 $renamed_field = $db->quoteIdentifier($renamed_field, true);
562 $query.= 'CHANGE ' . $renamed_field . ' ' . $db->getDeclaration($field['definition']['type'], $field['name'], $field['definition']);
570 $name = $db->quoteIdentifier($name, true);
571 $result = $db->exec("ALTER TABLE $name $query");
572 if (MDB2::isError($result)) {
579 // {{{ listDatabases()
584 * @return mixed array of database names on success, a MDB2 error on failure
587 function listDatabases()
589 $db = $this->getDBInstance();
590 if (MDB2::isError($db)) {
594 $result = $db->queryCol('SHOW DATABASES');
595 if (MDB2::isError($result)) {
598 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
599 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
610 * @return mixed array of user names on success, a MDB2 error on failure
615 $db = $this->getDBInstance();
616 if (MDB2::isError($db)) {
620 return $db->queryCol('SELECT DISTINCT USER FROM mysql.USER');
624 // {{{ listFunctions()
627 * list all functions in the current database
629 * @return mixed array of function names on success, a MDB2 error on failure
632 function listFunctions()
634 $db = $this->getDBInstance();
635 if (MDB2::isError($db)) {
639 $query = "SELECT name FROM mysql.proc";
642 FROM INFORMATION_SCHEMA.ROUTINES
643 WHERE ROUTINE_TYPE = 'FUNCTION'
645 $result = $db->queryCol($query);
646 if (MDB2::isError($result)) {
649 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
650 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
656 // {{{ listTableTriggers()
659 * list all triggers in the database that reference a given table
661 * @param string table for which all referenced triggers should be found
662 * @return mixed array of trigger names on success, a MDB2 error on failure
665 function listTableTriggers($table = null)
667 $db = $this->getDBInstance();
668 if (MDB2::isError($db)) {
672 $query = 'SHOW TRIGGERS';
673 if (null !== $table) {
674 $table = $db->quote($table, 'text');
675 $query .= " LIKE $table";
677 $result = $db->queryCol($query);
678 if (MDB2::isError($result)) {
681 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
682 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
691 * list all tables in the current database
693 * @param string database, the current is default
694 * @return mixed array of table names on success, a MDB2 error on failure
697 function listTables($database = null)
699 $db = $this->getDBInstance();
700 if (MDB2::isError($db)) {
704 $query = "SHOW /*!50002 FULL*/ TABLES";
705 if (null !== $database) {
706 $query .= " FROM $database";
708 $query.= "/*!50002 WHERE Table_type = 'BASE TABLE'*/";
710 $table_names = $db->queryAll($query, null, MDB2_FETCHMODE_ORDERED);
711 if (MDB2::isError($table_names)) {
716 foreach ($table_names as $table) {
717 if (!$this->_fixSequenceName($table[0], true)) {
718 $result[] = $table[0];
721 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
722 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
731 * list all views in the current database
733 * @param string database, the current is default
734 * @return mixed array of view names on success, a MDB2 error on failure
737 function listViews($database = null)
739 $db = $this->getDBInstance();
740 if (MDB2::isError($db)) {
744 $query = 'SHOW FULL TABLES';
745 if (null !== $database) {
746 $query.= " FROM $database";
748 $query.= " WHERE Table_type = 'VIEW'";
750 $result = $db->queryCol($query);
751 if (MDB2::isError($result)) {
755 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
756 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
762 // {{{ listTableFields()
765 * list all fields in a table in the current database
767 * @param string $table name of table that should be used in method
768 * @return mixed array of field names on success, a MDB2 error on failure
771 function listTableFields($table)
773 $db = $this->getDBInstance();
774 if (MDB2::isError($db)) {
778 $table = $db->quoteIdentifier($table, true);
779 $result = $db->queryCol("SHOW COLUMNS FROM $table");
780 if (MDB2::isError($result)) {
783 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
784 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
793 * Get the stucture of a field into an array
796 * @param string $table name of the table on which the index is to be created
797 * @param string $name name of the index to be created
798 * @param array $definition associative array that defines properties of the index to be created.
799 * Currently, only one property named FIELDS is supported. This property
800 * is also an associative with the names of the index fields as array
801 * indexes. Each entry of this array is set to another type of associative
802 * array that specifies properties of the index that are specific to
805 * Currently, only the sorting property is supported. It should be used
806 * to define the sorting direction of the index. It may be set to either
807 * ascending or descending.
809 * Not all DBMS support index sorting direction configuration. The DBMS
810 * drivers of those that do not support it ignore this property. Use the
811 * function supports() to determine whether the DBMS driver can manage indexes.
816 * 'user_name' => array(
817 * 'sorting' => 'ascending'
820 * 'last_login' => array()
824 * @return mixed MDB2_OK on success, a MDB2 error on failure
827 function createIndex($table, $name, $definition)
829 $db = $this->getDBInstance();
830 if (MDB2::isError($db)) {
834 $table = $db->quoteIdentifier($table, true);
835 $name = $db->quoteIdentifier($db->getIndexName($name), true);
836 $query = "CREATE INDEX $name ON $table";
838 foreach ($definition['fields'] as $field => $fieldinfo) {
839 if (!empty($fieldinfo['length'])) {
840 $fields[] = $db->quoteIdentifier($field, true) . '(' . $fieldinfo['length'] . ')';
842 $fields[] = $db->quoteIdentifier($field, true);
845 $query .= ' ('. implode(', ', $fields) . ')';
846 $result = $db->exec($query);
847 if (MDB2::isError($result)) {
857 * drop existing index
859 * @param string $table name of table that should be used in method
860 * @param string $name name of the index to be dropped
861 * @return mixed MDB2_OK on success, a MDB2 error on failure
864 function dropIndex($table, $name)
866 $db = $this->getDBInstance();
867 if (MDB2::isError($db)) {
871 $table = $db->quoteIdentifier($table, true);
872 $name = $db->quoteIdentifier($db->getIndexName($name), true);
873 $result = $db->exec("DROP INDEX $name ON $table");
874 if (MDB2::isError($result)) {
881 // {{{ listTableIndexes()
884 * list all indexes in a table
886 * @param string $table name of table that should be used in method
887 * @return mixed array of index names on success, a MDB2 error on failure
890 function listTableIndexes($table)
892 $db = $this->getDBInstance();
893 if (MDB2::isError($db)) {
897 $key_name = 'Key_name';
898 $non_unique = 'Non_unique';
899 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
900 if ($db->options['field_case'] == CASE_LOWER) {
901 $key_name = strtolower($key_name);
902 $non_unique = strtolower($non_unique);
904 $key_name = strtoupper($key_name);
905 $non_unique = strtoupper($non_unique);
909 $table = $db->quoteIdentifier($table, true);
910 $query = "SHOW INDEX FROM $table";
911 $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);
912 if (MDB2::isError($indexes)) {
917 foreach ($indexes as $index_data) {
918 if ($index_data[$non_unique] && ($index = $this->_fixIndexName($index_data[$key_name]))) {
919 $result[$index] = true;
923 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
924 $result = array_change_key_case($result, $db->options['field_case']);
926 return array_keys($result);
930 // {{{ createConstraint()
933 * create a constraint on a table
935 * @param string $table name of the table on which the constraint is to be created
936 * @param string $name name of the constraint to be created
937 * @param array $definition associative array that defines properties of the constraint to be created.
938 * Currently, only one property named FIELDS is supported. This property
939 * is also an associative with the names of the constraint fields as array
940 * constraints. Each entry of this array is set to another type of associative
941 * array that specifies properties of the constraint that are specific to
947 * 'user_name' => array(),
948 * 'last_login' => array()
951 * @return mixed MDB2_OK on success, a MDB2 error on failure
954 function createConstraint($table, $name, $definition)
956 $db = $this->getDBInstance();
957 if (MDB2::isError($db)) {
962 $idx_name = $db->quoteIdentifier($db->getIndexName($name), true);
963 if (!empty($definition['primary'])) {
966 } elseif (!empty($definition['unique'])) {
968 } elseif (!empty($definition['foreign'])) {
969 $type = 'CONSTRAINT';
972 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
973 'invalid definition, could not create constraint', __FUNCTION__);
976 $table_quoted = $db->quoteIdentifier($table, true);
977 $query = "ALTER TABLE $table_quoted ADD $type $idx_name";
978 if (!empty($definition['foreign'])) {
979 $query .= ' FOREIGN KEY';
982 foreach ($definition['fields'] as $field => $fieldinfo) {
983 $quoted = $db->quoteIdentifier($field, true);
984 if (!empty($fieldinfo['length'])) {
985 $quoted .= '(' . $fieldinfo['length'] . ')';
989 $query .= ' ('. implode(', ', $fields) . ')';
990 if (!empty($definition['foreign'])) {
991 $query.= ' REFERENCES ' . $db->quoteIdentifier($definition['references']['table'], true);
992 $referenced_fields = array();
993 foreach (array_keys($definition['references']['fields']) as $field) {
994 $referenced_fields[] = $db->quoteIdentifier($field, true);
996 $query .= ' ('. implode(', ', $referenced_fields) . ')';
997 $query .= $this->_getAdvancedFKOptions($definition);
999 // add index on FK column(s) or we can't add a FK constraint
1000 // @see http://forums.mysql.com/read.php?22,19755,226009
1001 $result = $this->createIndex($table, $name.'_fkidx', $definition);
1002 if (MDB2::isError($result)) {
1006 $res = $db->exec($query);
1007 if (MDB2::isError($res)) {
1010 if (!empty($definition['foreign'])) {
1011 return $this->_createFKTriggers($table, array($name => $definition));
1017 // {{{ dropConstraint()
1020 * drop existing constraint
1022 * @param string $table name of table that should be used in method
1023 * @param string $name name of the constraint to be dropped
1024 * @param string $primary hint if the constraint is primary
1025 * @return mixed MDB2_OK on success, a MDB2 error on failure
1028 function dropConstraint($table, $name, $primary = false)
1030 $db = $this->getDBInstance();
1031 if (MDB2::isError($db)) {
1035 if ($primary || strtolower($name) == 'primary') {
1036 $query = 'ALTER TABLE '. $db->quoteIdentifier($table, true) .' DROP PRIMARY KEY';
1037 $result = $db->exec($query);
1038 if (MDB2::isError($result)) {
1044 //is it a FK constraint? If so, also delete the associated triggers
1045 $db->loadModule('Reverse', null, true);
1046 $definition = $db->reverse->getTableConstraintDefinition($table, $name);
1047 if (!MDB2::isError($definition) && !empty($definition['foreign'])) {
1048 //first drop the FK enforcing triggers
1049 $result = $this->_dropFKTriggers($table, $name, $definition['references']['table']);
1050 if (MDB2::isError($result)) {
1053 //then drop the constraint itself
1054 $table = $db->quoteIdentifier($table, true);
1055 $name = $db->quoteIdentifier($db->getIndexName($name), true);
1056 $query = "ALTER TABLE $table DROP FOREIGN KEY $name";
1057 $result = $db->exec($query);
1058 if (MDB2::isError($result)) {
1064 $table = $db->quoteIdentifier($table, true);
1065 $name = $db->quoteIdentifier($db->getIndexName($name), true);
1066 $query = "ALTER TABLE $table DROP INDEX $name";
1067 $result = $db->exec($query);
1068 if (MDB2::isError($result)) {
1075 // {{{ _createFKTriggers()
1078 * Create triggers to enforce the FOREIGN KEY constraint on the table
1080 * NB: since there's no RAISE_APPLICATION_ERROR facility in mysql,
1081 * we call a non-existent procedure to raise the FK violation message.
1082 * @see http://forums.mysql.com/read.php?99,55108,71877#msg-71877
1084 * @param string $table table name
1085 * @param array $foreign_keys FOREIGN KEY definitions
1087 * @return mixed MDB2_OK on success, a MDB2 error on failure
1090 function _createFKTriggers($table, $foreign_keys)
1092 $db = $this->getDBInstance();
1093 if (MDB2::isError($db)) {
1096 // create triggers to enforce FOREIGN KEY constraints
1097 if ($db->supports('triggers') && !empty($foreign_keys)) {
1098 $table_quoted = $db->quoteIdentifier($table, true);
1099 foreach ($foreign_keys as $fkname => $fkdef) {
1100 if (empty($fkdef)) {
1103 //set actions to default if not set
1104 $fkdef['onupdate'] = empty($fkdef['onupdate']) ? $db->options['default_fk_action_onupdate'] : strtoupper($fkdef['onupdate']);
1105 $fkdef['ondelete'] = empty($fkdef['ondelete']) ? $db->options['default_fk_action_ondelete'] : strtoupper($fkdef['ondelete']);
1107 $trigger_names = array(
1108 'insert' => $fkname.'_insert_trg',
1109 'update' => $fkname.'_update_trg',
1110 'pk_update' => $fkname.'_pk_update_trg',
1111 'pk_delete' => $fkname.'_pk_delete_trg',
1113 $table_fields = array_keys($fkdef['fields']);
1114 $referenced_fields = array_keys($fkdef['references']['fields']);
1116 //create the ON [UPDATE|DELETE] triggers on the primary table
1117 $restrict_action = ' IF (SELECT ';
1118 $aliased_fields = array();
1119 foreach ($table_fields as $field) {
1120 $aliased_fields[] = $table_quoted .'.'.$field .' AS '.$field;
1122 $restrict_action .= implode(',', $aliased_fields)
1123 .' FROM '.$table_quoted
1125 $conditions = array();
1126 $new_values = array();
1127 $null_values = array();
1128 for ($i=0; $i<count($table_fields); $i++) {
1129 $conditions[] = $table_fields[$i] .' = OLD.'.$referenced_fields[$i];
1130 $new_values[] = $table_fields[$i] .' = NEW.'.$referenced_fields[$i];
1131 $null_values[] = $table_fields[$i] .' = NULL';
1133 $conditions2 = array();
1134 for ($i=0; $i<count($referenced_fields); $i++) {
1135 $conditions2[] = 'NEW.'.$referenced_fields[$i] .' <> OLD.'.$referenced_fields[$i];
1138 $restrict_action .= implode(' AND ', $conditions).') IS NOT NULL';
1139 $restrict_action2 = empty($conditions2) ? '' : ' AND (' .implode(' OR ', $conditions2) .')';
1140 $restrict_action3 = ' THEN CALL %s_ON_TABLE_'.$table.'_VIOLATES_FOREIGN_KEY_CONSTRAINT();'
1143 $restrict_action_update = $restrict_action . $restrict_action2 . $restrict_action3;
1144 $restrict_action_delete = $restrict_action . $restrict_action3; // There is no NEW row in on DELETE trigger
1146 $cascade_action_update = 'UPDATE '.$table_quoted.' SET '.implode(', ', $new_values) .' WHERE '.implode(' AND ', $conditions). ';';
1147 $cascade_action_delete = 'DELETE FROM '.$table_quoted.' WHERE '.implode(' AND ', $conditions). ';';
1148 $setnull_action = 'UPDATE '.$table_quoted.' SET '.implode(', ', $null_values).' WHERE '.implode(' AND ', $conditions). ';';
1150 if ('SET DEFAULT' == $fkdef['onupdate'] || 'SET DEFAULT' == $fkdef['ondelete']) {
1151 $db->loadModule('Reverse', null, true);
1152 $default_values = array();
1153 foreach ($table_fields as $table_field) {
1154 $field_definition = $db->reverse->getTableFieldDefinition($table, $field);
1155 if (MDB2::isError($field_definition)) {
1156 return $field_definition;
1158 $default_values[] = $table_field .' = '. $field_definition[0]['default'];
1160 $setdefault_action = 'UPDATE '.$table_quoted.' SET '.implode(', ', $default_values).' WHERE '.implode(' AND ', $conditions). ';';
1163 $query = 'CREATE TRIGGER %s'
1164 .' %s ON '.$fkdef['references']['table']
1165 .' FOR EACH ROW BEGIN '
1166 .' SET FOREIGN_KEY_CHECKS = 0; '; //only really needed for ON UPDATE CASCADE
1168 if ('CASCADE' == $fkdef['onupdate']) {
1169 $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $cascade_action_update;
1170 } elseif ('SET NULL' == $fkdef['onupdate']) {
1171 $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setnull_action;
1172 } elseif ('SET DEFAULT' == $fkdef['onupdate']) {
1173 $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setdefault_action;
1174 } elseif ('NO ACTION' == $fkdef['onupdate']) {
1175 $sql_update = sprintf($query.$restrict_action_update, $trigger_names['pk_update'], 'AFTER UPDATE', 'update');
1176 } elseif ('RESTRICT' == $fkdef['onupdate']) {
1177 $sql_update = sprintf($query.$restrict_action_update, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update');
1179 if ('CASCADE' == $fkdef['ondelete']) {
1180 $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete') . $cascade_action_delete;
1181 } elseif ('SET NULL' == $fkdef['ondelete']) {
1182 $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete') . $setnull_action;
1183 } elseif ('SET DEFAULT' == $fkdef['ondelete']) {
1184 $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete') . $setdefault_action;
1185 } elseif ('NO ACTION' == $fkdef['ondelete']) {
1186 $sql_delete = sprintf($query.$restrict_action_delete, $trigger_names['pk_delete'], 'AFTER DELETE', 'delete');
1187 } elseif ('RESTRICT' == $fkdef['ondelete']) {
1188 $sql_delete = sprintf($query.$restrict_action_delete, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete');
1190 $sql_update .= ' SET FOREIGN_KEY_CHECKS = 1; END;';
1191 $sql_delete .= ' SET FOREIGN_KEY_CHECKS = 1; END;';
1193 $db->pushErrorHandling(PEAR_ERROR_RETURN);
1194 $db->expectError(MDB2_ERROR_CANNOT_CREATE);
1195 $result = $db->exec($sql_delete);
1196 $expected_errmsg = 'This MySQL version doesn\'t support multiple triggers with the same action time and event for one table';
1198 $db->popErrorHandling();
1199 if (MDB2::isError($result)) {
1200 if ($result->getCode() != MDB2_ERROR_CANNOT_CREATE) {
1203 $db->warnings[] = $expected_errmsg;
1205 $db->pushErrorHandling(PEAR_ERROR_RETURN);
1206 $db->expectError(MDB2_ERROR_CANNOT_CREATE);
1207 $result = $db->exec($sql_update);
1209 $db->popErrorHandling();
1210 if (MDB2::isError($result) && $result->getCode() != MDB2_ERROR_CANNOT_CREATE) {
1211 if ($result->getCode() != MDB2_ERROR_CANNOT_CREATE) {
1214 $db->warnings[] = $expected_errmsg;
1222 // {{{ _dropFKTriggers()
1225 * Drop the triggers created to enforce the FOREIGN KEY constraint on the table
1227 * @param string $table table name
1228 * @param string $fkname FOREIGN KEY constraint name
1229 * @param string $referenced_table referenced table name
1231 * @return mixed MDB2_OK on success, a MDB2 error on failure
1234 function _dropFKTriggers($table, $fkname, $referenced_table)
1236 $db = $this->getDBInstance();
1237 if (MDB2::isError($db)) {
1241 $triggers = $this->listTableTriggers($table);
1242 $triggers2 = $this->listTableTriggers($referenced_table);
1243 if (!MDB2::isError($triggers2) && !MDB2::isError($triggers)) {
1244 $triggers = array_merge($triggers, $triggers2);
1245 $pattern = '/^'.$fkname.'(_pk)?_(insert|update|delete)_trg$/i';
1246 foreach ($triggers as $trigger) {
1247 if (preg_match($pattern, $trigger)) {
1248 $result = $db->exec('DROP TRIGGER '.$trigger);
1249 if (MDB2::isError($result)) {
1259 // {{{ listTableConstraints()
1262 * list all constraints in a table
1264 * @param string $table name of table that should be used in method
1265 * @return mixed array of constraint names on success, a MDB2 error on failure
1268 function listTableConstraints($table)
1270 $db = $this->getDBInstance();
1271 if (MDB2::isError($db)) {
1275 $key_name = 'Key_name';
1276 $non_unique = 'Non_unique';
1277 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
1278 if ($db->options['field_case'] == CASE_LOWER) {
1279 $key_name = strtolower($key_name);
1280 $non_unique = strtolower($non_unique);
1282 $key_name = strtoupper($key_name);
1283 $non_unique = strtoupper($non_unique);
1287 $query = 'SHOW INDEX FROM ' . $db->quoteIdentifier($table, true);
1288 $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);
1289 if (MDB2::isError($indexes)) {
1294 foreach ($indexes as $index_data) {
1295 if (!$index_data[$non_unique]) {
1296 if ($index_data[$key_name] !== 'PRIMARY') {
1297 $index = $this->_fixIndexName($index_data[$key_name]);
1301 if (!empty($index)) {
1302 $result[$index] = true;
1307 //list FOREIGN KEY constraints...
1308 $query = 'SHOW CREATE TABLE '. $db->escape($table);
1309 $definition = $db->queryOne($query, 'text', 1);
1310 if (!MDB2::isError($definition) && !empty($definition)) {
1311 $pattern = '/\bCONSTRAINT\b\s+([^\s]+)\s+\bFOREIGN KEY\b/Uims';
1312 if (preg_match_all($pattern, str_replace('`', '', $definition), $matches) > 0) {
1313 foreach ($matches[1] as $constraint) {
1314 $result[$constraint] = true;
1319 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
1320 $result = array_change_key_case($result, $db->options['field_case']);
1322 return array_keys($result);
1326 // {{{ createSequence()
1331 * @param string $seq_name name of the sequence to be created
1332 * @param string $start start value of the sequence; default is 1
1333 * @param array $options An associative array of table options:
1335 * 'comment' => 'Foo',
1336 * 'charset' => 'utf8',
1337 * 'collate' => 'utf8_unicode_ci',
1338 * 'type' => 'innodb',
1340 * @return mixed MDB2_OK on success, a MDB2 error on failure
1343 function createSequence($seq_name, $start = 1, $options = array())
1345 $db = $this->getDBInstance();
1346 if (MDB2::isError($db)) {
1350 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
1351 $seqcol_name = $db->quoteIdentifier($db->options['seqcol_name'], true);
1353 $options_strings = array();
1355 if (!empty($options['comment'])) {
1356 $options_strings['comment'] = 'COMMENT = '.$db->quote($options['comment'], 'text');
1359 if (!empty($options['charset'])) {
1360 $options_strings['charset'] = 'DEFAULT CHARACTER SET '.$options['charset'];
1361 if (!empty($options['collate'])) {
1362 $options_strings['charset'].= ' COLLATE '.$options['collate'];
1367 if (!empty($options['type'])) {
1368 $type = $options['type'];
1369 } elseif ($db->options['default_table_type']) {
1370 $type = $db->options['default_table_type'];
1373 $options_strings[] = "ENGINE = $type";
1376 $query = "CREATE TABLE $sequence_name ($seqcol_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ($seqcol_name))";
1377 if (!empty($options_strings)) {
1378 $query .= ' '.implode(' ', $options_strings);
1380 $res = $db->exec($query);
1381 if (MDB2::isError($res)) {
1389 $query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (".($start-1).')';
1390 $res = $db->exec($query);
1391 if (!MDB2::isError($res)) {
1396 $result = $db->exec("DROP TABLE $sequence_name");
1397 if (MDB2::isError($result)) {
1398 return $db->raiseError($result, null, null,
1399 'could not drop inconsistent sequence table', __FUNCTION__);
1402 return $db->raiseError($res, null, null,
1403 'could not create sequence table', __FUNCTION__);
1407 // {{{ dropSequence()
1410 * drop existing sequence
1412 * @param string $seq_name name of the sequence to be dropped
1413 * @return mixed MDB2_OK on success, a MDB2 error on failure
1416 function dropSequence($seq_name)
1418 $db = $this->getDBInstance();
1419 if (MDB2::isError($db)) {
1423 $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
1424 $result = $db->exec("DROP TABLE $sequence_name");
1425 if (MDB2::isError($result)) {
1432 // {{{ listSequences()
1435 * list all sequences in the current database
1437 * @param string database, the current is default
1438 * @return mixed array of sequence names on success, a MDB2 error on failure
1441 function listSequences($database = null)
1443 $db = $this->getDBInstance();
1444 if (MDB2::isError($db)) {
1448 $query = "SHOW TABLES";
1449 if (null !== $database) {
1450 $query .= " FROM $database";
1452 $table_names = $db->queryCol($query);
1453 if (MDB2::isError($table_names)) {
1454 return $table_names;
1458 foreach ($table_names as $table_name) {
1459 if ($sqn = $this->_fixSequenceName($table_name, true)) {
1463 if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
1464 $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);