Blame | Letzte Änderung | Log anzeigen | RSS feed
<?php// +----------------------------------------------------------------------+// | PHP versions 4 and 5 |// +----------------------------------------------------------------------+// | Copyright (c) 1998-2007 Manuel Lemos, Tomas V.V.Cox, |// | Stig. S. Bakken, Lukas Smith |// | All rights reserved. |// +----------------------------------------------------------------------+// | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |// | API as well as database abstraction for PHP applications. |// | This LICENSE is in the BSD license style. |// | |// | Redistribution and use in source and binary forms, with or without |// | modification, are permitted provided that the following conditions |// | are met: |// | |// | Redistributions of source code must retain the above copyright |// | notice, this list of conditions and the following disclaimer. |// | |// | Redistributions in binary form must reproduce the above copyright |// | notice, this list of conditions and the following disclaimer in the |// | documentation and/or other materials provided with the distribution. |// | |// | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |// | Lukas Smith nor the names of his contributors may be used to endorse |// | or promote products derived from this software without specific prior|// | written permission. |// | |// | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |// | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |// | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |// | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |// | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |// | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |// | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|// | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |// | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |// | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|// | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |// | POSSIBILITY OF SUCH DAMAGE. |// +----------------------------------------------------------------------+// | Author: Lukas Smith <smith@pooteeweet.org> |// +----------------------------------------------------------------------+//// $Id: mysql.php,v 1.94 2007/03/04 22:50:16 quipo Exp $//require_once 'MDB2/Driver/Manager/Common.php';/*** MDB2 MySQL driver for the management modules** @package MDB2* @category Database* @author Lukas Smith <smith@pooteeweet.org>*/class MDB2_Driver_Manager_mysql extends MDB2_Driver_Manager_Common{// }}}// {{{ createDatabase()/*** create a new database** @param string $name name of the database that should be created* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function createDatabase($name){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$name = $db->quoteIdentifier($name, true);$query = "CREATE DATABASE $name";$result = $db->exec($query);if (PEAR::isError($result)) {return $result;}return MDB2_OK;}// }}}// {{{ dropDatabase()/*** drop an existing database** @param string $name name of the database that should be dropped* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function dropDatabase($name){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$name = $db->quoteIdentifier($name, true);$query = "DROP DATABASE $name";$result = $db->exec($query);if (PEAR::isError($result)) {return $result;}return MDB2_OK;}// }}}// {{{ createTable()/*** create a new table** @param string $name Name of the database that should be created* @param array $fields Associative array that contains the definition of each field of the new table* The indexes of the array entries are the names of the fields of the table an* the array entry values are associative arrays like those that are meant to be* passed with the field definitions to get[Type]Declaration() functions.* array(* 'id' => array(* 'type' => 'integer',* 'unsigned' => 1* 'notnull' => 1* 'default' => 0* ),* 'name' => array(* 'type' => 'text',* 'length' => 12* ),* 'password' => array(* 'type' => 'text',* 'length' => 12* )* );* @param array $options An associative array of table options:* array(* 'comment' => 'Foo',* 'charset' => 'utf8',* 'collate' => 'utf8_unicode_ci',* 'type' => 'innodb',* );** @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function createTable($name, $fields, $options = array()){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$query = $this->_getCreateTableQuery($name, $fields, $options);if (PEAR::isError($query)) {return $query;}$options_strings = array();if (!empty($options['comment'])) {$options_strings['comment'] = 'COMMENT = '.$db->quote($options['comment'], 'text');}if (!empty($options['charset'])) {$options_strings['charset'] = 'DEFAULT CHARACTER SET '.$options['charset'];if (!empty($options['collate'])) {$options_strings['charset'].= ' COLLATE '.$options['collate'];}}$type = false;if (!empty($options['type'])) {$type = $options['type'];} elseif ($db->options['default_table_type']) {$type = $db->options['default_table_type'];}if ($type) {$options_strings[] = "ENGINE = $type";}if (!empty($options_strings)) {$query .= ' '.implode(' ', $options_strings);}return $db->exec($query);}// }}}// {{{ alterTable()/*** alter an existing table** @param string $name name of the table that is intended to be changed.* @param array $changes associative array that contains the details of each type* of change that is intended to be performed. The types of* changes that are currently supported are defined as follows:** name** New name for the table.** add** Associative array with the names of fields to be added as* indexes of the array. The value of each entry of the array* should be set to another associative array with the properties* of the fields to be added. The properties of the fields should* be the same as defined by the MDB2 parser.*** remove** Associative array with the names of fields to be removed as indexes* of the array. Currently the values assigned to each entry are ignored.* An empty array should be used for future compatibility.** rename** Associative array with the names of fields to be renamed as indexes* of the array. The value of each entry of the array should be set to* another associative array with the entry named name with the new* field name and the entry named Declaration that is expected to contain* the portion of the field declaration already in DBMS specific SQL code* as it is used in the CREATE TABLE statement.** change** Associative array with the names of the fields to be changed as indexes* of the array. Keep in mind that if it is intended to change either the* name of a field and any other properties, the change array entries* should have the new names of the fields as array indexes.** The value of each entry of the array should be set to another associative* array with the properties of the fields to that are meant to be changed as* array entries. These entries should be assigned to the new values of the* respective properties. The properties of the fields should be the same* as defined by the MDB2 parser.** Example* array(* 'name' => 'userlist',* 'add' => array(* 'quota' => array(* 'type' => 'integer',* 'unsigned' => 1* )* ),* 'remove' => array(* 'file_limit' => array(),* 'time_limit' => array()* ),* 'change' => array(* 'name' => array(* 'length' => '20',* 'definition' => array(* 'type' => 'text',* 'length' => 20,* ),* )* ),* 'rename' => array(* 'sex' => array(* 'name' => 'gender',* 'definition' => array(* 'type' => 'text',* 'length' => 1,* 'default' => 'M',* ),* )* )* )** @param boolean $check indicates whether the function should just check if the DBMS driver* can perform the requested table alterations if the value is true or* actually perform them otherwise.* @access public** @return mixed MDB2_OK on success, a MDB2 error on failure*/function alterTable($name, $changes, $check){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}foreach ($changes as $change_name => $change) {switch ($change_name) {case 'add':case 'remove':case 'change':case 'rename':case 'name':break;default:return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,'change type "'.$change_name.'" not yet supported', __FUNCTION__);}}if ($check) {return MDB2_OK;}$query = '';if (!empty($changes['name'])) {$change_name = $db->quoteIdentifier($changes['name'], true);$query .= 'RENAME TO ' . $change_name;}if (!empty($changes['add']) && is_array($changes['add'])) {foreach ($changes['add'] as $field_name => $field) {if ($query) {$query.= ', ';}$query.= 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field);}}if (!empty($changes['remove']) && is_array($changes['remove'])) {foreach ($changes['remove'] as $field_name => $field) {if ($query) {$query.= ', ';}$field_name = $db->quoteIdentifier($field_name, true);$query.= 'DROP ' . $field_name;}}$rename = array();if (!empty($changes['rename']) && is_array($changes['rename'])) {foreach ($changes['rename'] as $field_name => $field) {$rename[$field['name']] = $field_name;}}if (!empty($changes['change']) && is_array($changes['change'])) {foreach ($changes['change'] as $field_name => $field) {if ($query) {$query.= ', ';}if (isset($rename[$field_name])) {$old_field_name = $rename[$field_name];unset($rename[$field_name]);} else {$old_field_name = $field_name;}$old_field_name = $db->quoteIdentifier($old_field_name, true);$query.= "CHANGE $old_field_name " . $db->getDeclaration($field['definition']['type'], $field_name, $field['definition']);}}if (!empty($rename) && is_array($rename)) {foreach ($rename as $rename_name => $renamed_field) {if ($query) {$query.= ', ';}$field = $changes['rename'][$renamed_field];$renamed_field = $db->quoteIdentifier($renamed_field, true);$query.= 'CHANGE ' . $renamed_field . ' ' . $db->getDeclaration($field['definition']['type'], $field['name'], $field['definition']);}}if (!$query) {return MDB2_OK;}$name = $db->quoteIdentifier($name, true);return $db->exec("ALTER TABLE $name $query");}// }}}// {{{ listDatabases()/*** list all databases** @return mixed array of database names on success, a MDB2 error on failure* @access public*/function listDatabases(){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$result = $db->queryCol('SHOW DATABASES');if (PEAR::isError($result)) {return $result;}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);}return $result;}// }}}// {{{ listUsers()/*** list all users** @return mixed array of user names on success, a MDB2 error on failure* @access public*/function listUsers(){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}return $db->queryCol('SELECT DISTINCT USER FROM mysql.USER');}// }}}// {{{ listFunctions()/*** list all functions in the current database** @return mixed array of function names on success, a MDB2 error on failure* @access public*/function listFunctions(){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$query = "SELECT name FROM mysql.proc";/*SELECT ROUTINE_NAMEFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_TYPE = 'FUNCTION'*/$result = $db->queryCol($query);if (PEAR::isError($result)) {return $result;}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);}return $result;}// }}}// {{{ listTableTriggers()/*** list all triggers in the database that reference a given table** @param string table for which all referenced triggers should be found* @return mixed array of trigger names on success, a MDB2 error on failure* @access public*/function listTableTriggers($table = null){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$query = 'SHOW TRIGGERS';if (!is_null($table)) {$table = $db->quote($table, 'text');$query .= " LIKE $table";}$result = $db->queryCol($query);if (PEAR::isError($result)) {return $result;}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);}return $result;}// }}}// {{{ listTables()/*** list all tables in the current database** @param string database, the current is default* @return mixed array of table names on success, a MDB2 error on failure* @access public*/function listTables($database = null){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$query = "SHOW /*!50002 FULL*/ TABLES";if (!is_null($database)) {$query .= " FROM $database";}$query.= "/*!50002 WHERE Table_type = 'BASE TABLE'*/";$table_names = $db->queryAll($query, null, MDB2_FETCHMODE_ORDERED);if (PEAR::isError($table_names)) {return $table_names;}$result = array();foreach ($table_names as $table) {if (!$this->_fixSequenceName($table[0], true)) {$result[] = $table[0];}}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);}return $result;}// }}}// {{{ listViews()/*** list all views in the current database** @param string database, the current is default* @return mixed array of view names on success, a MDB2 error on failure* @access public*/function listViews($database = null){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$query = 'SHOW FULL TABLES';if (!is_null($database)) {$query.= " FROM $database";}$query.= " WHERE Table_type = 'VIEW'";$result = $db->queryCol($query);if (PEAR::isError($result)) {return $result;}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);}return $result;}// }}}// {{{ listTableFields()/*** list all fields in a table in the current database** @param string $table name of table that should be used in method* @return mixed array of field names on success, a MDB2 error on failure* @access public*/function listTableFields($table){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$table = $db->quoteIdentifier($table, true);$result = $db->queryCol("SHOW COLUMNS FROM $table");if (PEAR::isError($result)) {return $result;}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);}return $result;}// }}}// {{{ createIndex()/*** Get the stucture of a field into an array** @author Leoncx* @param string $table name of the table on which the index is to be created* @param string $name name of the index to be created* @param array $definition associative array that defines properties of the index to be created.* Currently, only one property named FIELDS is supported. This property* is also an associative with the names of the index fields as array* indexes. Each entry of this array is set to another type of associative* array that specifies properties of the index that are specific to* each field.** Currently, only the sorting property is supported. It should be used* to define the sorting direction of the index. It may be set to either* ascending or descending.** Not all DBMS support index sorting direction configuration. The DBMS* drivers of those that do not support it ignore this property. Use the* function supports() to determine whether the DBMS driver can manage indexes.** Example* array(* 'fields' => array(* 'user_name' => array(* 'sorting' => 'ascending'* 'length' => 10* ),* 'last_login' => array()* )* )* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function createIndex($table, $name, $definition){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$table = $db->quoteIdentifier($table, true);$name = $db->quoteIdentifier($db->getIndexName($name), true);$query = "CREATE INDEX $name ON $table";$fields = array();foreach ($definition['fields'] as $field => $fieldinfo) {if (!empty($fieldinfo['length'])) {$fields[] = $db->quoteIdentifier($field, true) . '(' . $fieldinfo['length'] . ')';} else {$fields[] = $db->quoteIdentifier($field, true);}}$query .= ' ('. implode(', ', $fields) . ')';return $db->exec($query);}// }}}// {{{ dropIndex()/*** drop existing index** @param string $table name of table that should be used in method* @param string $name name of the index to be dropped* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function dropIndex($table, $name){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$table = $db->quoteIdentifier($table, true);$name = $db->quoteIdentifier($db->getIndexName($name), true);return $db->exec("DROP INDEX $name ON $table");}// }}}// {{{ listTableIndexes()/*** list all indexes in a table** @param string $table name of table that should be used in method* @return mixed array of index names on success, a MDB2 error on failure* @access public*/function listTableIndexes($table){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$key_name = 'Key_name';$non_unique = 'Non_unique';if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {if ($db->options['field_case'] == CASE_LOWER) {$key_name = strtolower($key_name);$non_unique = strtolower($non_unique);} else {$key_name = strtoupper($key_name);$non_unique = strtoupper($non_unique);}}$table = $db->quoteIdentifier($table, true);$query = "SHOW INDEX FROM $table";$indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);if (PEAR::isError($indexes)) {return $indexes;}$result = array();foreach ($indexes as $index_data) {if ($index_data[$non_unique] && ($index = $this->_fixIndexName($index_data[$key_name]))) {$result[$index] = true;}}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_change_key_case($result, $db->options['field_case']);}return array_keys($result);}// }}}// {{{ createConstraint()/*** create a constraint on a table** @param string $table name of the table on which the constraint is to be created* @param string $name name of the constraint to be created* @param array $definition associative array that defines properties of the constraint to be created.* Currently, only one property named FIELDS is supported. This property* is also an associative with the names of the constraint fields as array* constraints. Each entry of this array is set to another type of associative* array that specifies properties of the constraint that are specific to* each field.** Example* array(* 'fields' => array(* 'user_name' => array(),* 'last_login' => array()* )* )* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function createConstraint($table, $name, $definition){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$type = '';$name = $db->quoteIdentifier($db->getIndexName($name), true);if (!empty($definition['primary'])) {$type = 'PRIMARY';$name = 'KEY';} elseif (!empty($definition['unique'])) {$type = 'UNIQUE';}if (empty($type)) {return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,'invalid definition, could not create constraint', __FUNCTION__);}$table = $db->quoteIdentifier($table, true);$query = "ALTER TABLE $table ADD $type $name";$fields = array();foreach (array_keys($definition['fields']) as $field) {$fields[] = $db->quoteIdentifier($field, true);}$query .= ' ('. implode(', ', $fields) . ')';return $db->exec($query);}// }}}// {{{ dropConstraint()/*** drop existing constraint** @param string $table name of table that should be used in method* @param string $name name of the constraint to be dropped* @param string $primary hint if the constraint is primary* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function dropConstraint($table, $name, $primary = false){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$table = $db->quoteIdentifier($table, true);if ($primary || strtolower($name) == 'primary') {$query = "ALTER TABLE $table DROP PRIMARY KEY";} else {$name = $db->quoteIdentifier($db->getIndexName($name), true);$query = "ALTER TABLE $table DROP INDEX $name";}return $db->exec($query);}// }}}// {{{ listTableConstraints()/*** list all constraints in a table** @param string $table name of table that should be used in method* @return mixed array of constraint names on success, a MDB2 error on failure* @access public*/function listTableConstraints($table){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$key_name = 'Key_name';$non_unique = 'Non_unique';if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {if ($db->options['field_case'] == CASE_LOWER) {$key_name = strtolower($key_name);$non_unique = strtolower($non_unique);} else {$key_name = strtoupper($key_name);$non_unique = strtoupper($non_unique);}}$table = $db->quoteIdentifier($table, true);$query = "SHOW INDEX FROM $table";$indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);if (PEAR::isError($indexes)) {return $indexes;}$result = array();foreach ($indexes as $index_data) {if (!$index_data[$non_unique]) {if ($index_data[$key_name] !== 'PRIMARY') {$index = $this->_fixIndexName($index_data[$key_name]);} else {$index = 'PRIMARY';}if (!empty($index)) {$result[$index] = true;}}}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_change_key_case($result, $db->options['field_case']);}return array_keys($result);}// }}}// {{{ createSequence()/*** create sequence** @param string $seq_name name of the sequence to be created* @param string $start start value of the sequence; default is 1* @param array $options An associative array of table options:* array(* 'comment' => 'Foo',* 'charset' => 'utf8',* 'collate' => 'utf8_unicode_ci',* 'type' => 'innodb',* );* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function createSequence($seq_name, $start = 1, $options = array()){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);$seqcol_name = $db->quoteIdentifier($db->options['seqcol_name'], true);$options_strings = array();if (!empty($options['comment'])) {$options_strings['comment'] = 'COMMENT = '.$db->quote($options['comment'], 'text');}if (!empty($options['charset'])) {$options_strings['charset'] = 'DEFAULT CHARACTER SET '.$options['charset'];if (!empty($options['collate'])) {$options_strings['charset'].= ' COLLATE '.$options['collate'];}}$type = false;if (!empty($options['type'])) {$type = $options['type'];} elseif ($db->options['default_table_type']) {$type = $db->options['default_table_type'];}if ($type) {$options_strings[] = "ENGINE = $type";}$query = "CREATE TABLE $sequence_name ($seqcol_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ($seqcol_name))";if (!empty($options_strings)) {$query .= ' '.implode(' ', $options_strings);}$res = $db->exec($query);if (PEAR::isError($res)) {return $res;}if ($start == 1) {return MDB2_OK;}$query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (".($start-1).')';$res = $db->exec($query);if (!PEAR::isError($res)) {return MDB2_OK;}// Handle error$result = $db->exec("DROP TABLE $sequence_name");if (PEAR::isError($result)) {return $db->raiseError($result, null, null,'could not drop inconsistent sequence table', __FUNCTION__);}return $db->raiseError($res, null, null,'could not create sequence table', __FUNCTION__);}// }}}// {{{ dropSequence()/*** drop existing sequence** @param string $seq_name name of the sequence to be dropped* @return mixed MDB2_OK on success, a MDB2 error on failure* @access public*/function dropSequence($seq_name){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);return $db->exec("DROP TABLE $sequence_name");}// }}}// {{{ listSequences()/*** list all sequences in the current database** @param string database, the current is default* @return mixed array of sequence names on success, a MDB2 error on failure* @access public*/function listSequences($database = null){$db =& $this->getDBInstance();if (PEAR::isError($db)) {return $db;}$query = "SHOW TABLES";if (!is_null($database)) {$query .= " FROM $database";}$table_names = $db->queryCol($query);if (PEAR::isError($table_names)) {return $table_names;}$result = array();foreach ($table_names as $table_name) {if ($sqn = $this->_fixSequenceName($table_name, true)) {$result[] = $sqn;}}if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {$result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);}return $result;}// }}}}?>