/** * Get the delta query to adjust the current database according to a given (list of) * "CREATE TABLE"-, "CREATE DATABASE"-, "INSERT"- or "UPDATE"-statement(s). * * It's not recommended to use INSERT or UPDATE statements with this function, as they * are just handled "as-is". * * NOTE: * - You should use single quotes (') to give string type values (this is in fact * required for ENUM and SET fields). * - KEYs for AUTO_INCREMENT fields should be defined in column_definition, otherwise * we had to detect the key type from the INDEX query and add it to the ALTER/ADD query. * - If a column changes from "NULL" to "NOT NULL" we generate an extra UPDATE query * to prevent "Data truncated for column 'X' at row Y" errors. * * The following query types are generated/marked and can be excluded: * - 'create_table' * - 'create_database' * - 'insert' * - 'update' * - 'drop_column' * - 'change_column' * - 'change_default' * - 'add_column' * - 'add_index' * - 'drop_index' * - 'alter_engine' * NOTE: it may be needed to merge an 'add_index' or 'drop_index' type query into an * 'add_column'/'change_column' query (adding "AUTO_INCREMENT" for example)! * * NOTE: collations and charset changes are ignored. It seems quite difficult to support this, * and it seems to be best to handle this "manually". * * @author Originally taken from Wordpress, heavily enhanced and modified by blueyed * * @todo Handle COMMENT for tables?! * * @see http://dev.mysql.com/doc/refman/4.1/en/create-table.html * * @param array The list of queries for which the DB should be adjusted * @param array Exclude query types (see list above). * @param boolean Execute generated queries? TODO: get this outta here!!!! (sooooo bloated!) * @return array The generated queries. * table_name => array of arrays (queries with keys 'queries' (array), 'note' (string) and 'type' (string)) * There's usually just a single query in "queries", but in some cases additional queries * are needed (e.g., 'UPDATE' before we can change "NULL" setting). */ function db_delta($queries, $exclude_types = array(), $execute = false) { global $Debuglog, $DB, $debug; if (!is_array($queries)) { $queries = array($queries); } if (!is_array($exclude_types)) { $exclude_types = empty($exclude_types) ? array() : array($exclude_types); } /** * Generated query items, indexed by table name. */ $items = array(); // Split the queries into $items, by their type: foreach ($queries as $qry) { // Remove any comments from the SQL: $qry = remove_comments_from_query($qry); if (preg_match('|^(\\s*CREATE TABLE\\s+)(IF NOT EXISTS\\s+)?([^\\s(]+)(.*)$|is', $qry, $match)) { $tablename = db_delta_remove_quotes(preg_replace($DB->dbaliases, $DB->dbreplaces, $match[3])); $qry = $match[1] . (empty($match[2]) ? '' : $match[2]) . $tablename . $match[4]; $items[strtolower($tablename)][] = array('queries' => array($qry), 'note' => sprintf('Created table «<strong>%s</strong>»', $tablename), 'type' => 'create_table'); } elseif (preg_match('|^\\s*CREATE DATABASE\\s([\\S]+)|i', $qry, $match)) { // add to the beginning array_unshift($items, array('queries' => array($qry), 'note' => sprintf('Created database «<strong>%s</strong>»', $match[1]), 'type' => 'create_database')); } elseif (preg_match('|^(\\s*INSERT INTO\\s+)([\\S]+)(.*)$|is', $qry, $match)) { $tablename = db_delta_remove_quotes(preg_replace($DB->dbaliases, $DB->dbreplaces, $match[2])); $items[strtolower($tablename)][] = array('queries' => array($match[1] . $tablename . $match[3]), 'note' => '', 'type' => 'insert'); } elseif (preg_match('|^(\\s*UPDATE\\s+)([\\S]+)(.*)$|is', $qry, $match)) { $tablename = db_delta_remove_quotes(preg_replace($DB->dbaliases, $DB->dbreplaces, $match[2])); $items[strtolower($tablename)][] = array('queries' => array($match[1] . $tablename . $match[3]), 'note' => '', 'type' => 'update'); } else { $Debuglog->add('db_delta: Unrecognized query type: ' . $qry, 'note'); } } /** * @global array Available tables in the current database */ $tables = $DB->get_col('SHOW TABLES'); // Loop through existing tables and check which tables and fields exist foreach ($tables as $table) { // For every table in the database $table_lowered = strtolower($table); // table names are treated case insensitive if (!isset($items[$table_lowered])) { // This table exists in the database, but not in the creation queries. continue; } /** * @global array Hold the indices we want to create/have, with meta data keys. */ $indices = array(); /** * @global array Initially all existing indices. Any index, that does not get unset here, generates a 'drop_index' type query. */ $obsolete_indices = array(); /** * @global array Fields of the existing primary key (if any) */ $existing_primary_fields = array(); /** * @global array Fields of existing keys (including PRIMARY), lowercased (if any) */ $existing_key_fields = array(); /** * @global array Column field names of PRIMARY KEY, lowercased (if any) */ $primary_key_fields = array(); /** * @global array Column field names of FOREIGN KEY, lowercased (if any) */ $foreign_key_fields = array(); /** * @global array of col_names that have KEYs (including PRIMARY; lowercased). We use this for AUTO_INCREMENT magic. */ $fields_with_keys = array(); /** * @global string Holds the fielddef of an obsolete ("drop_column") AUTO_INCREMENT field. We must alter this with a PK "ADD COLUMN" query. */ $obsolete_autoincrement = NULL; /** * @global array List of fields (and definition from query) * <code>fieldname (lowercase) => array( * 'field' => "column_definition", * 'where' => "[FIRST|AFTER xxx]" ) * </code> */ $wanted_fields = array(); /** * @global boolean Do we have any variable-length fields? (see http://dev.mysql.com/doc/refman/4.1/en/silent-column-changes.html) */ $has_variable_length_field = false; // Get all of the field names in the query from between the parens $flds = get_fieldlines_from_query($items[$table_lowered][0]['queries'][0]); //echo "<hr/><pre>\n".print_r(strtolower($table), true).":\n".print_r($items, true)."</pre><hr/>"; // ALTER ENGINE, if different (and given in query): if (($wanted_engine = get_engine_from_query($items[$table_lowered][0]['queries'][0])) !== false) { $current_engine = $DB->get_row(' SHOW TABLE STATUS LIKE ' . $DB->quote($table)); $current_engine = $current_engine->Engine; if (strtolower($current_engine) != strtolower($wanted_engine)) { $items[$table_lowered][] = array('queries' => array('ALTER TABLE ' . $table . ' ENGINE=' . $wanted_engine), 'note' => 'Alter engine of <strong>' . $table . '.</strong> to <strong>' . $wanted_engine . '</strong>', 'type' => 'alter_engine'); } } $prev_fld = ''; foreach ($flds as $create_definition) { // For every field line specified in the query // Extract the field name preg_match('|^([^\\s(]+)|', trim($create_definition), $match); $fieldname = db_delta_remove_quotes($match[1]); $fieldname_lowered = strtolower($fieldname); $create_definition = trim($create_definition, ", \r\n\t"); if (in_array($fieldname_lowered, array('', 'primary', 'foreign', 'index', 'fulltext', 'unique', 'key'))) { // INDEX (but not in column_definition - those get handled later) $add_index = array('create_definition' => $create_definition); if (!preg_match('~^(PRIMARY(?:\\s+KEY)|(?:FULLTEXT|UNIQUE)(?:\\s+(?:INDEX|KEY))?|KEY|INDEX) (?:\\s+() (\\w+) )? (\\s+USING\\s+\\w+)? \\s* \\((.*)\\)$~ix', $create_definition, $match) && !preg_match('~^(PRIMARY(?:\\s+KEY)|(?:FULLTEXT|UNIQUE)(?:\\s+(?:INDEX|KEY))?|KEY|INDEX) (?:\\s+([`"])([\\w\\s]+)\\2)? (\\s+USING\\s+\\w+)? \\s* \\((.*)\\)$~ix', $create_definition, $match) && !preg_match('~^(FOREIGN\\s+KEY) \\s* \\((.*)\\) \\s* (REFERENCES) \\s* ([^( ]*) \\s* \\((.*)\\) \\s* (.*)$~ixs', $create_definition, $match)) { // invalid type, should not happen debug_die('Invalid type in $indices: ' . $create_definition); // TODO: add test: Invalid type in $indices: KEY "coord" ("lon","lat") } if ($fieldname_lowered == 'foreign') { // Remember FOREIGN KEY fields, but they don't have to be indexed $reference_table_name = db_delta_remove_quotes(preg_replace($DB->dbaliases, $DB->dbreplaces, $match[4])); $foreign_key_fields[] = array('fk_fields' => $match[2], 'reference_table' => $reference_table_name, 'reference_columns' => $match[5], 'fk_definition' => $match[6], 'create' => true); continue; } $add_index['keyword'] = $match[1]; $add_index['name'] = strtoupper($match[3]); $add_index['type'] = $match[4]; // "USING [type_name]" $add_index['col_names'] = explode(',', $match[5]); foreach ($add_index['col_names'] as $k => $v) { $add_index['col_names'][$k] = strtolower(db_delta_remove_quotes(trim($v))); } if ($fieldname_lowered == 'primary') { // Remember PRIMARY KEY fields to be indexed (used for NULL check) $primary_key_fields = $add_index['col_names']; $add_index['is_PK'] = true; } else { $add_index['is_PK'] = false; } $fields_with_keys = array_unique(array_merge($fields_with_keys, $add_index['col_names'])); $indices[] = $add_index; } else { // "normal" field, add it to the field array $wanted_fields[strtolower($fieldname_lowered)] = array('field' => $create_definition, 'where' => empty($prev_fld) ? 'FIRST' : 'AFTER ' . $prev_fld); $prev_fld = $fieldname; if (preg_match('~^\\S+\\s+(VARCHAR|TEXT|BLOB)~i', $create_definition)) { $has_variable_length_field = true; } } } // INDEX STUFF: /** * @global array Holds the existing indices (with array's key UPPERcased) */ $existing_indices = array(); // Fetch the table index structure from the database $tableindices = $DB->get_results('SHOW INDEX FROM ' . $table); if (!empty($tableindices)) { // For every index in the table foreach ($tableindices as $tableindex) { // Add the index to the index data array $keyname = strtoupper($tableindex->Key_name); $existing_indices[$keyname]['name'] = $tableindex->Key_name; // original case $existing_indices[$keyname]['columns'][] = array('fieldname' => $tableindex->Column_name, 'subpart' => $tableindex->Sub_part); $existing_indices[$keyname]['unique'] = $tableindex->Non_unique == 0 ? true : false; } unset($tableindices); // Let's see which indices are present already for the table: // TODO: dh> use meta data available now in $indices, instead of building a regular expression!? $obsolete_indices = $existing_indices; // will get unset as found } // Pre-run KEYs defined in "column_definition" (e.g. used for AUTO_INCREMENT handling) foreach ($wanted_fields as $fieldname_lowered => $field_info) { $parse = $field_info['field']; if (preg_match('~ \\b UNIQUE (?:\\s+ KEY)? \\b ~ix ', $parse, $match)) { // This has an "inline" UNIQUE index: if (!is_in_quote($parse, ' ' . $match[0])) { // isn't between quotation marks, so it must be a primary key. $indices[] = array('name' => $fieldname_lowered, 'is_PK' => false, 'create_definition' => NULL, 'col_names' => array($fieldname_lowered), 'keyword' => NULL); unset($obsolete_indices[strtoupper($fieldname_lowered)]); $parse = str_replace($match[0], '', $parse); $fields_with_keys[] = $fieldname_lowered; } } if (preg_match('~ \\b (PRIMARY\\s+)? KEY \\b ~ix', $parse, $match)) { // inline PK: // Check if this key is between quotation marks if (!is_in_quote($parse, ' ' . $match[0])) { // it isn't between quotation marks, so it must be a primary key. $indices[] = array('name' => 'PRIMARY', 'is_PK' => true, 'create_definition' => NULL, 'col_names' => array($fieldname_lowered), 'keyword' => NULL); $fields_with_keys[] = $fieldname_lowered; $primary_key_fields = array($fieldname_lowered); unset($obsolete_indices['PRIMARY']); } } } $fields_with_keys = array_unique($fields_with_keys); foreach ($existing_indices as $index_name => $index_data) { // Build a create string to compare to the query $index_pattern = '^'; if ($index_name == 'PRIMARY') { $index_pattern .= 'PRIMARY(\\s+KEY)?'; // optional primary key name: $index_pattern .= '(\\s+[`"]?\\w+[`"]?)?'; } elseif ($index_data['unique']) { $index_pattern .= 'UNIQUE(\\s+(?:INDEX|KEY))?'; } else { $index_pattern .= '(INDEX|(?:FULLTEXT\\s+)?KEY)'; } if ($index_name != 'PRIMARY') { $index_pattern .= '(\\s+[`"]?' . $index_name . '[`"]?)?'; // optionally in backticks (and index name is optionally itself) } $index_columns = ''; // For each column in the index foreach ($index_data['columns'] as $column_data) { if ($index_columns != '') { $index_columns .= '\\s*,\\s*'; } // Add the field to the column list string $index_columns .= '[`"]?' . $column_data['fieldname'] . '[`"]?'; // optionally in backticks if (!empty($column_data['subpart'])) { $index_columns .= '\\s*\\(\\s*' . $column_data['subpart'] . '\\s*\\)\\s*'; } } // Sort index definitions with names to the beginning: /* usort( $indices, create_function( '$a, $b', ' if( preg_match( "~^\w+\s+[^(]~", $a["create_definition"] ) { }' ) ); */ $used_auto_keys = array(); foreach ($indices as $k => $index) { $pattern = $index_pattern; if (!preg_match('~^\\w+\\s+[^(]~', $index['create_definition'], $match)) { // no key name given, make the name part optional, if it's the default one: // (Default key name seems to be the first column, eventually with "_\d+"-suffix) $auto_key = db_delta_remove_quotes(strtoupper($index['col_names'][0])); if (isset($used_auto_keys[$auto_key])) { $used_auto_keys[$auto_key]++; $auto_key .= '_' . $used_auto_keys[$auto_key]; } $used_auto_keys[$auto_key] = 1; if ($auto_key == $index_name) { // the auto-generated keyname is the same as the one we have, so make it optional in the pattern: $pattern .= '?'; } } // Add the column list to the index create string $pattern .= '\\s*\\(\\s*' . $index_columns . '\\s*\\)'; #pre_dump( '~'.$pattern.'~i', trim($index['create_definition']) ); if (preg_match('~' . $pattern . '~i', trim($index['create_definition']))) { // This index already exists: remove the index from our indices to create unset($indices[$k]); unset($obsolete_indices[$index_name]); break; } } if (isset($obsolete_indices[$index_name])) { #echo "<pre style=\"border:1px solid #ccc;margin-top:5px;\">{$table}:<br/><b>Did not find index:</b>".$index_name.'/'.$index_pattern."<br/>".print_r($indices, true)."</pre>\n"; } } // Set $existing_primary_fields and $existing_key_fields foreach ($existing_indices as $l_key_name => $l_key_info) { $l_key_fields = array(); foreach ($l_key_info['columns'] as $l_col) { $l_key_fields[] = strtolower($l_col['fieldname']); } if ($l_key_name == 'PRIMARY') { // Remember _existing_ PRIMARY KEYs $existing_primary_fields = $l_key_fields; } $existing_key_fields = array_merge($existing_key_fields, $l_key_fields); } $existing_key_fields = array_unique($existing_key_fields); #pre_dump( 'existing_primary_fields', $existing_primary_fields ); #pre_dump( 'existing_key_fields', $existing_key_fields ); // Fetch the table column structure from the database $tablefields = $DB->get_results('SHOW FULL COLUMNS FROM ' . $table); // If "drop_column" is not excluded we have to check if all existing cols would get dropped, // to prevent "You can't delete all columns with ALTER TABLE; use DROP TABLE instead(Errno=1090)" if (!in_array('drop_column', $exclude_types)) { $at_least_one_col_stays = false; foreach ($tablefields as $tablefield) { $fieldname_lowered = strtolower($tablefield->Field); if (isset($wanted_fields[$fieldname_lowered])) { $at_least_one_col_stays = true; } } if (!$at_least_one_col_stays) { // all columns get dropped: so we need to DROP TABLE and then use the original CREATE TABLE array_unshift($items[$table_lowered], array('queries' => array('DROP TABLE ' . $table), 'note' => 'Dropped <strong>' . $table . '.</strong>', 'type' => 'drop_column')); continue; // next $table } } // For every field in the existing table foreach ($tablefields as $tablefield) { $fieldname_lowered = strtolower($tablefield->Field); if (!isset($wanted_fields[$fieldname_lowered])) { // This field exists in the table, but not in the creation queries if (in_array('drop_column', $exclude_types)) { if (preg_match('~\\bAUTO_INCREMENT\\b~i', $tablefield->Extra)) { // must be modified with a ADD COLUMN which drops a PK $obsolete_autoincrement = $tablefield; } } else { $items[$table_lowered][] = array('queries' => array('ALTER TABLE ' . $table . ' DROP COLUMN ' . $tablefield->Field), 'note' => 'Dropped ' . $table . '.<strong>' . $tablefield->Field . '</strong>', 'type' => 'drop_column'); // Unset in key indices: if (($k = array_search($fieldname_lowered, $existing_key_fields)) !== false) { unset($existing_key_fields[$k]); } if (($k = array_search($fieldname_lowered, $existing_primary_fields)) !== false) { unset($existing_primary_fields[$k]); } } continue; } $column_definition = trim($wanted_fields[$fieldname_lowered]['field']); unset($type_matches); // have we detected the type as matching (for optional length param) $fieldtype = ''; $pattern_field = '[`"]?' . $tablefield->Field . '[`"]?'; // optionally in backticks // Get the field type from the query if (preg_match('~^' . $pattern_field . '\\s+ (TINYINT|SMALLINT|MEDIUMINT|INTEGER|INT|BIGINT|REAL|DOUBLE|FLOAT|DECIMAL|DEC|NUMERIC) ( \\s* \\([\\d\\s,]+\\) )? (\\s+ UNSIGNED)? (\\s+ ZEROFILL)? (.*)$~ix', $column_definition, $match)) { $fieldtype = strtoupper($match[1]); if ($fieldtype == 'INTEGER') { // synonym $fieldtype = 'INT'; } elseif ($fieldtype == 'DEC') { // synonym $fieldtype = 'DECIMAL'; } if (isset($match[2])) { // append optional "length" param (trimmed) $fieldtype .= preg_replace('~\\s+~', '', $match[2]); } if (!empty($match[3])) { // "unsigned" $fieldtype .= ' ' . trim($match[3]); } if (!empty($match[4])) { // "zerofill" $fieldtype .= ' ' . trim($match[4]); } $field_to_parse = $match[5]; // The length param is optional: if (substr($fieldtype, 0, 7) == 'DECIMAL') { $matches_pattern = '~^' . preg_quote($tablefield->Type, '~') . '$~i'; } else { $matches_pattern = '~^' . preg_replace('~\\((\\d+)\\)~', '(\\(\\d+\\))?', $tablefield->Type) . '$~i'; } $type_matches = preg_match($matches_pattern, $fieldtype); } elseif (preg_match('~^' . $pattern_field . '\\s+(DATETIME|DATE|TIMESTAMP|TIME|YEAR|TINYBLOB|BLOB|MEDIUMBLOB|LONGBLOB|TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT) ( \\s+ BINARY )? (.*)$~ix', $column_definition, $match)) { $fieldtype = strtoupper($match[1]); if (isset($match[2])) { // "binary" $fieldtype .= trim($match[2]); } $field_to_parse = $match[3]; // There's a bug with a "NOT NULL" field reported as "NULL", work around it (http://bugs.mysql.com/bug.php?id=20910): if ($fieldtype == 'TIMESTAMP') { $ct_sql = $DB->get_var('SHOW CREATE TABLE ' . $table, 1, 0); if (preg_match('~^\\s*`' . $tablefield->Field . '`\\s+TIMESTAMP\\s+(NOT )?NULL~im', $ct_sql, $match)) { $tablefield->Null = empty($match[1]) ? 'YES' : 'NO'; } } } elseif (preg_match('~^' . $pattern_field . '\\s+ (CHAR|VARCHAR|BINARY|VARBINARY) \\s* \\( ([\\d\\s]+) \\) (\\s+ (BINARY|ASCII|UNICODE) )? (.*)$~ix', $column_definition, $match)) { $len = trim($match[2]); $fieldtype = strtoupper($match[1]) . '(' . $len . ')'; if (!empty($match[3])) { // "binary", "ascii", "unicode" $fieldtype .= ' ' . $match[3]; } $field_to_parse = $match[5]; if (strtoupper($match[1]) == 'VARCHAR') { if ($len < 4) { // VARCHAR shorter than 4 get converted to CHAR (but reported as VARCHAR in MySQL 5.0) $type_matches = preg_match('~^(VAR)?CHAR\\(' . $len . '\\)' . ($match[3] ? ' ' . $match[3] : '') . '$~i', $tablefield->Type); } } elseif ($has_variable_length_field && strtoupper($match[1]) == 'CHAR') { // CHARs in a row with variable length fields get silently converted to VARCHAR (but reported as CHAR in MySQL 5.0) $type_matches = preg_match('~^(VAR)?' . preg_quote($fieldtype, '~') . '$~i', $tablefield->Type); } } elseif (preg_match('~^' . $pattern_field . '\\s+ (ENUM|SET) \\s* \\( (.*) \\) (.*)$~ix', $column_definition, $match)) { $values = preg_split('~\\s*,\\s*~', trim($match[2]), -1, PREG_SPLIT_NO_EMPTY); // TODO: will fail for values containing ",".. $values = implode(',', $values); $fieldtype = strtoupper($match[1]) . '(' . $values . ')'; $field_compare = strtolower($match[1]) . '(' . $values . ')'; // compare case-sensitive $type_matches = $field_compare == $tablefield->Type; $field_to_parse = $match[3]; } else { if ($debug) { debug_die('db_delta(): Cannot find existing types field in column definition (' . $pattern_field . '/' . $column_definition . ')'); } continue; } // DEFAULT $want_default = false; if (preg_match('~^(.*?) \\s DEFAULT \\s+ (?: (?: (["\']) (.*?) \\2 ) | (\\w+) ) (\\s .*)?$~ix', $field_to_parse, $match)) { if (isset($match[4]) && $match[4] !== '') { $want_default = $match[4]; $want_default_set = $match[4]; } else { $want_default = $match[3]; $want_default_set = $match[2] . $match[3] . $match[2]; // encapsulate in quotes again } $field_to_parse = $match[1] . (isset($match[5]) ? $match[5] : ''); } // KEY $has_inline_primary_key = false; if (preg_match('~^(.*) \\b (?: (UNIQUE) (?:\\s+ (?:INDEX|KEY))? | (?:PRIMARY \\s+)? KEY ) \\b (.*)$~ix', $field_to_parse, $match)) { // fields got added to primary_key_fields and fields_with_keys before // Check if this key is between quotation marks if (!has_open_quote($match[1])) { // is not between quotation marks, so it must be a key. $field_to_parse = $match[1] . $match[3]; if (empty($match[2])) { $has_inline_primary_key = true; // we need to DROP the PK if this column definition does not match } } } // AUTO_INCREMENT (with special index handling: AUTO_INCREMENT fields need to be PRIMARY or UNIQUE) $is_auto_increment = false; if (preg_match('~(.*?) \\b AUTO_INCREMENT \\b (.*)$~ix', $field_to_parse, $match)) { $is_auto_increment = true; $field_to_parse = $match[1] . $match[2]; if (!preg_match('~\\bAUTO_INCREMENT\\b~i', $tablefield->Extra)) { // not AUTO_INCREMENT yet $type_matches = false; } if (!in_array($fieldname_lowered, $fields_with_keys)) { // no KEY defined (but required for AUTO_INCREMENT fields) debug_die('No KEY/INDEX defined for AUTO_INCREMENT column!'); } if (in_array($fieldname_lowered, $existing_key_fields)) { if (!empty($primary_key_fields)) { $column_definition .= ', DROP PRIMARY KEY'; unset($obsolete_indices['PRIMARY']); } } else { // a key for this AUTO_INCREMENT field does not exist yet, we search it in $indices foreach ($indices as $k_index => $l_index) { // go through the indexes we want to have if (array_search($fieldname_lowered, $l_index['col_names']) === false) { // this is not an index for our column continue; } // this index definition affects us, we have to add it to our ALTER statement.. // See if we need to drop it, before adding it: if ($l_index['is_PK']) { // Part of a PRIMARY key.. if (!empty($existing_primary_fields)) { // and a PRIMARY key exists already $column_definition .= ', DROP PRIMARY KEY'; unset($obsolete_indices['PRIMARY']); } $existing_primary_fields = array(); // we expect no existing primary key anymore $primary_key_fields = $l_index['col_names']; // this becomes our primary key } elseif (isset($existing_indices[$l_index['name']])) { // this index already exists, drop it: $column_definition .= ', DROP INDEX ' . $existing_indices[$l_index['name']]; // original case unset($existing_indices[$l_index['name']]); // we expect that it does not exist anymore if (!in_array($fieldname_lowered, $fields_with_keys)) { // add te field to the list of keys we want/expect to have: $fields_with_keys[] = $fieldname_lowered; } } // Merge the INDEX creation into our ALTER query: $column_definition .= ', ADD ' . $l_index['create_definition']; unset($indices[$k_index]); } } } // "[NOT] NULL" (requires $primary_key_fields to be finalized) if (preg_match('~(.*?) \\b (NOT\\s+)? NULL \\b (.*)$~ix', $field_to_parse, $match)) { // if "NOT" not matched it's NULL $want_null = empty($match[2]); $field_to_parse = $match[1] . $match[3]; } else { // not specified: "NULL" is default $want_null = true; } if (in_array($fieldname_lowered, $primary_key_fields) || $is_auto_increment) { // If part of PRIMARY KEY or AUTO_INCREMENT field "NULL" is implicit $change_null = false; // implicit NULL $want_null = 'IMPLICIT'; } elseif (in_array($fieldname_lowered, $existing_primary_fields) && !in_array($fieldname_lowered, $primary_key_fields)) { // the field was in PRIMARY KEY, but is no longer. It should get altered only if we want "NOT NULL" $change_null = !$want_null && $tablefield->Null == 'YES'; #pre_dump( $want_null ); #$want_null = 'IMPLICIT2'; #pre_dump( $primary_key_fields ); } else { if ($tablefield->Null == 'YES') { $change_null = !$want_null; } else { // I've seen '' and 'NO' for no.. $change_null = $want_null; } } // COMMENT ( check if there is difference in field comment ) if (preg_match('~^(.*?) \\s COMMENT \\s+ (?: (?: (["\']) (.*?) \\2 ) ) (\\s .*)?$~ix', $field_to_parse, $match)) { if (isset($match[4]) && $match[4] !== '') { $want_comment = $match[4]; } else { $want_comment = $match[3]; } $want_comment = stripslashes($want_comment); $type_matches = $tablefield->Comment == $want_comment; $field_to_parse = $match[1] . (isset($match[5]) ? $match[5] : ''); } // TODO: "COLLATE" and other attribute handling should happen here, based on $field_to_parse if (!isset($type_matches)) { // not tried to match before $type_matches = strtoupper($tablefield->Type) == $fieldtype; } #pre_dump( 'change_null ($change_null, $tablefield, $want_null)', $change_null, $tablefield, $want_null ); #pre_dump( 'type_matches', $type_matches, strtolower($tablefield->Type), $fieldtype ); // See what DEFAULT we would get or want $update_default = NULL; $update_default_set = NULL; if ($want_default !== false) { $update_default = $want_default; $update_default_set = $want_default_set; } else { // implicit default, see http://dev.mysql.com/doc/refman/4.1/en/data-type-defaults.html if (preg_match('~^(TINYINT|SMALLINT|MEDIUMINT|INTEGER|INT|BIGINT|REAL|DOUBLE|FLOAT|DECIMAL|DEC|NUMERIC)~', $fieldtype)) { // numeric $update_default = '0'; $update_default_set = '0'; } elseif ($fieldtype == 'TIMESTAMP') { // TODO: the default should be current date and time for the first field - but AFAICS we won't have NULL fields anyway } elseif (preg_match('~^(DATETIME|DATE|TIME|YEAR)$~', $fieldtype)) { $update_default = '0'; // short form for various special "zero" values $update_default_set = '0'; } elseif (substr($fieldtype, 0, 4) == 'ENUM') { preg_match('~["\']?.*?["\']?\\s*[,)]~x', substr($fieldtype, 5), $match); $update_default_set = trim($match[0], "\n\r\t\vB (),"); // strip default whitespace, braces & comma // first value (until "," or end) of $fieldtype_param: $update_default = preg_replace('~^(["\'])(.*)\\1$~', '$2', $update_default_set); // without quotes } else { $update_default_set = "''"; // empty string for string types $update_default = ''; } } // Is actual field type different from the field type in query? if (!$type_matches || $change_null) { // Change the whole column to $column_definition: /* echo '<h2>No_Match</h2>'; pre_dump( $type_matches, $change_null, $want_null ); pre_dump( $tablefield, $column_definition ); pre_dump( 'flds', $flds ); pre_dump( 'wanted_fields', $wanted_fields ); pre_dump( strtolower($tablefield->Type), $fieldtype, $column_definition ); */ $queries = array('ALTER TABLE ' . $table); // Handle inline PRIMARY KEY definition: if ($has_inline_primary_key && !empty($existing_primary_fields)) { // the column is part of the PRIMARY KEY, which needs to get dropped before (we already handle that for AUTO_INCREMENT fields) $queries[0] .= ' DROP PRIMARY KEY,'; $existing_primary_fields = array(); // we expect no existing primary key anymore unset($obsolete_indices['PRIMARY']); } $queries[0] .= ' CHANGE COLUMN ' . $tablefield->Field . ' ' . $column_definition; // Handle changes from "NULL" to "NOT NULL" if ($change_null && !$want_null && isset($update_default_set)) { // Prepend query to update NULL fields to default array_unshift($queries, 'UPDATE ' . $table . ' SET ' . $tablefield->Field . ' = ' . $update_default_set . ' WHERE ' . $tablefield->Field . ' IS NULL'); if (substr($tablefield->Type, 0, 5) == 'enum(') { $existing_enum_field_values = preg_split('~\\s*,\\s*~', substr($tablefield->Type, 5, -1), -1, PREG_SPLIT_NO_EMPTY); foreach ($existing_enum_field_values as $k => $v) { $existing_enum_field_values[$k] = preg_replace('~^(["\'])(.*)\\1$~', '$2', $v); // strip quotes } if (!in_array($update_default, $existing_enum_field_values)) { // we cannot update straight to the new default, because it does not exist yet! // Update the column first, without the NULL change array_unshift($queries, 'ALTER TABLE ' . $table . ' CHANGE COLUMN ' . $tablefield->Field . ' ' . preg_replace('~\\sNOT\\s+NULL~i', '', $column_definition)); } } } // Add a query to change the column type $items[$table_lowered][] = array('queries' => $queries, 'note' => 'Changed type of ' . $table . '.<strong>' . $tablefield->Field . '</strong> from ' . $tablefield->Type . ' to ' . $column_definition, 'type' => 'change_column'); } else { // perhaps alter or drop DEFAULT: if ($want_default !== false) { // DEFAULT given $existing_default = $tablefield->Default === NULL ? 'NULL' : $tablefield->Default; if ($existing_default != $want_default) { // Add a query to change the column's default value $items[$table_lowered][] = array('queries' => array('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $tablefield->Field . ' SET DEFAULT ' . $want_default_set), 'note' => "Changed default value of {$table}.<strong>{$tablefield->Field}</strong> from {$existing_default} to {$want_default_set}", 'type' => 'change_default'); } } elseif (!empty($tablefield->Default) && $tablefield->Default != $update_default) { // No DEFAULT given, but it exists one, so drop it (IF not a TIMESTAMP or DATETIME field) if ($tablefield->Type != 'timestamp' && $tablefield->Type != 'datetime') { $items[$table_lowered][] = array('queries' => array('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $tablefield->Field . ' DROP DEFAULT'), 'note' => "Dropped default value of {$table}.<strong>{$tablefield->Field}</strong>", 'type' => 'change_default'); // might be also 'drop_default' } } } // Remove the field from the array (so it's not added) unset($wanted_fields[$fieldname_lowered]); } foreach ($wanted_fields as $fieldname_lowered => $fielddef) { // For every remaining field specified for the table $column_definition = $fielddef['field'] . ' ' . $fielddef['where']; $is_auto_increment = false; // AUTO_INCREMENT (with special index handling: AUTO_INCREMENT fields need to be PRIMARY or UNIQUE) if (preg_match('~(.*?) \\b AUTO_INCREMENT \\b (.*)$~ix', $fielddef['field'], $match)) { if (!in_array($fieldname_lowered, $fields_with_keys)) { // no KEY defined (but required for AUTO_INCREMENT fields) debug_die('No KEY/INDEX defined for AUTO_INCREMENT column!'); } $is_auto_increment = true; foreach ($indices as $k_index => $l_index) { // go through the indexes we want to have if (array_search($fieldname_lowered, $l_index['col_names']) === false) { // this is not an index for our column continue; } // this index definition affects us, we have to add it to our ALTER statement.. // See if we need to drop it, before adding it: if ($l_index['is_PK']) { // Part of a PRIMARY key.. if (!empty($existing_primary_fields)) { // and a PRIMARY key exists already $column_definition .= ', DROP PRIMARY KEY'; unset($obsolete_indices['PRIMARY']); } $existing_primary_fields = array(); // we expect no existing primary key anymore $primary_key_fields = $l_index['col_names']; // this becomes our primary key } elseif (isset($existing_indices[$l_index['name']])) { // this index already exists, drop it: $column_definition .= ', DROP INDEX ' . $existing_indices[$l_index['name']]; // original case unset($existing_indices[$l_index['name']]); // we expect that it does not exist anymore if (!in_array($fieldname_lowered, $fields_with_keys)) { // add te field to the list of keys we want/expect to have: $fields_with_keys[] = $fieldname_lowered; } } // Merge the INDEX creation into our ALTER query: $column_definition .= ', ADD ' . $l_index['create_definition']; unset($indices[$k_index]); } } // Push a query line into $items that adds the field to that table $query = 'ALTER TABLE ' . $table . ' ADD COLUMN ' . $column_definition; // Handle inline PRIMARY KEY definition: if (preg_match('~^(.*) \\b (?: (UNIQUE) (?:\\s+ (?:INDEX|KEY))? | (?:PRIMARY \\s+)? KEY ) \\b (.*)$~ix', $column_definition, $match) && count($existing_primary_fields) && !in_array($fieldname_lowered, $existing_primary_fields)) { // the column is part of the PRIMARY KEY, which needs to get dropped before (we already handle that for AUTO_INCREMENT fields) $query .= ', DROP PRIMARY KEY'; $existing_primary_fields = array(); // we expect no existing primary key anymore unset($obsolete_indices['PRIMARY']); if (isset($obsolete_autoincrement)) { $query .= ', MODIFY COLUMN ' . $obsolete_autoincrement->Field . ' ' . $obsolete_autoincrement->Type . ' ' . ($obsolete_autoincrement->Field == 'YES' ? 'NULL' : 'NOT NULL'); } } $items[$table_lowered][] = array('queries' => array($query), 'note' => 'Added column ' . $table . '.<strong>' . $fielddef['field'] . '</strong>', 'type' => 'add_column'); } // Remove the original table creation query from processing array_shift($items[$table_lowered]); // Add foreign key constraints $result = db_delta_foreign_keys($foreign_key_fields, $table, false); foreach ($result as $foreign_key_update) { // loop through foreign key differences in this table if ($foreign_key_update['type'] == 'alter_engine') { // this is an alter engine command, check if this command was already added during engine difference detection, and skip to the next if it was added $skip = false; foreach ($items[$table_lowered] as $itemlist) { if ($itemlist['type'] == 'alter_engine' && $itemlist['queries'][0] == $foreign_key_update['queries'][0]) { // the same command was already added, don't add again $skip = true; break; } } if ($skip) { // skip is set, don't add the alter engine command again continue; } } // add FK updates $items[$table_lowered][] = $foreign_key_update; } // Add the remaining indices (which are not "inline" with a column definition and therefor already handled): $add_index_queries = array(); foreach ($indices as $k => $index) { if (empty($index['create_definition'])) { // skip "inline" continue; } $query = 'ALTER TABLE ' . $table; if ($index['is_PK'] && $existing_primary_fields) { $query .= ' DROP PRIMARY KEY,'; unset($obsolete_indices['PRIMARY']); } // Create a query that adds the index to the table $query = array('queries' => array($query . ' ADD ' . $index['create_definition']), 'note' => 'Added index <strong>' . $index['create_definition'] . '</strong>', 'type' => 'add_index', 'name' => $index['name']); // Check if the index creation has to get appended after any DROPs (required for indices with the same name) $append_after_drops = false; foreach ($obsolete_indices as $obsolete_index) { if (strtolower($obsolete_index['name']) == strtolower($index['name'])) { $append_after_drops = true; break; } } if ($append_after_drops) { // do this after any DROPs (i.e. KEY name changes) $add_index_queries[] = $query; } else { // this needs to get done before any other DROPs // to prevent e.g. "Incorrect table definition; there can be only one auto column and it must be defined as a key(Errno=1075)" $items[$table_lowered][] = $query; } } // Now add queries to drop any (maybe changed!) indices foreach ($obsolete_indices as $index_info) { // Push a query line into $items that drops the index from the table $items[$table_lowered][] = array('queries' => array("ALTER TABLE {$table} DROP " . ($index_info['name'] == 'PRIMARY' ? 'PRIMARY KEY' : 'INDEX ' . $index_info['name'])), 'note' => 'Dropped index <strong>' . $index_info['name'] . '</strong>', 'type' => 'drop_index', 'name' => $index_info['name']); } // Add queries to (re)create (maybe changed indices) to the end $items[$table_lowered] = array_merge($items[$table_lowered], $add_index_queries); } // Filter types we want to exclude: if (!empty($exclude_types)) { foreach ($items as $table => $itemlist) { $removed_one = false; foreach ($itemlist as $k => $item) { if (in_array($item['type'], $exclude_types)) { // this type of update should be excluded if ($item['type'] == 'drop_index') { // drop index command should not be excluded in case when we would like to update an index! $skip = false; foreach ($itemlist as $other_item) { // check if there are an add_index command for the same table with the same index name if ($other_item['type'] == 'add_index' && strcasecmp($item['name'], $other_item['name']) === 0) { // add index with the same index name was found so we need to process this drop_index command to be able to add a new correct index with the same name $skip = true; break; } } if ($skip) { // skip excluding this item continue; } } unset($items[$table][$k]); $removed_one = true; } } if ($removed_one) { // Re-order (0, 1, 2, ..) $items[$table] = array_values($items[$table]); } } } // Unset empty table indices: foreach ($items as $table => $itemlist) { if (empty($itemlist)) { unset($items[$table]); continue; } // Check if we have alter engine and drop foreign key queries for the same table. In this case the drop query must be processed before the alter engine query! $alter_engine_index = NULL; for ($i = 0; $i < count($itemlist); $i++) { if ($itemlist[$i]['type'] == 'alter_engine' && $alter_engine_index == NULL) { // save alter engine query index $alter_engine_index = $i; } elseif ($itemlist[$i]['type'] == 'drop_foreign_key' && $alter_engine_index !== NULL && $alter_engine_index < $i) { // switch engine update and drop foreign key queries, because in many cases we must drop the foreign key first to be able to chagne the table engine $switch_item = $itemlist[$alter_engine_index]; $items[$table][$alter_engine_index] = $itemlist[$i]; $items[$table][$i] = $switch_item; // save new alter engine index and the alter engine command in case of we have to drop multiple foreign keys $alter_engine_index = $i; $itemlist[$i] = $switch_item; } } } if ($execute) { foreach ($items as $table => $itemlist) { foreach ($itemlist as $item) { foreach ($item['queries'] as $query) { #pre_dump( $query ); $DB->query($query); } } } } return $items; }
/** * Drop a Foreign Key constraint. * If this foreign key not exists the function won't give error. * * @param string foreign key table * @param string foreing key column name * @param string foreign key refrence table name * @param string reference column name in the reference table */ function db_drop_foreign_key($table, $field_name, $reference_table, $reference_field_name) { global $DB; $table = preg_replace($DB->dbaliases, $DB->dbreplaces, $table); $reference_table = preg_replace($DB->dbaliases, $DB->dbreplaces, $reference_table); $foreign_key_fields = array(array('fk_fields' => $field_name, 'reference_table' => $reference_table, 'reference_columns' => $reference_field_name, 'fk_definition' => '', 'create' => false)); db_delta_foreign_keys($foreign_key_fields, $table, true, 'drop'); }