| [ Index ] |
|
Code source de b2evolution 2.1.0-beta |
1 <?php 2 /** 3 * This file implements functions useful for upgrading DB schema. 4 * 5 * This file is part of the b2evolution/evocms project - {@link http://b2evolution.net/}. 6 * See also {@link http://sourceforge.net/projects/evocms/}. 7 * 8 * @copyright (c)2003-2007 by Francois PLANQUE - {@link http://fplanque.net/}. 9 * Parts of this file are copyright (c)2004-2005 by Daniel HAHLER - {@link https://thequod.de/}. 10 * 11 * {@link db_delta()} is based on dbDelta() from {@link http://wordpress.com Wordpress}, see 12 * {@link http://trac.wordpress.org/file/trunk/wp-admin/upgrade-functions.php}. 13 * 14 * @license http://b2evolution.net/about/license.html GNU General Public License (GPL) 15 * 16 * {@internal Open Source relicensing agreement: 17 * Daniel HAHLER grants Francois PLANQUE the right to license 18 * Daniel HAHLER's contributions to this file and the b2evolution project 19 * under any OSI approved OSS license (http://www.opensource.org/licenses/). 20 * }} 21 * 22 * @package evocore 23 * 24 * {@internal Below is a list of authors who have contributed to design/coding of this file: }} 25 * @author fplanque: Francois PLANQUE 26 * @author blueyed: Daniel HAHLER 27 * @author Wordpress team 28 * 29 * @version $Id: _upgrade.funcs.php,v 1.1 2007/06/25 10:59:00 fplanque Exp $ 30 */ 31 if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' ); 32 33 34 /** 35 * Get the delta query to adjust the current database according to a given (list of) 36 * "CREATE TABLE"-, "CREATE DATABASE"-, "INSERT"- or "UPDATE"-statement(s). 37 * 38 * It's not recommend to use INSERT or UPDATE statements with this function, as they 39 * are just handled "as-is". 40 * 41 * NOTE: 42 * - You should use single quotes (') to give string type values (this is in fact 43 * required for ENUM and SET fields). 44 * - KEYs for AUTO_INCREMENT fields should be defined in column_definition, otherwise 45 * we had to detect the key type from the INDEX query and add it to the ALTER/ADD query. 46 * - If a column changes from "NULL" to "NOT NULL" we generate an extra UPDATE query 47 * to prevent "Data truncated for column 'X' at row Y" errors. 48 * 49 * The following query types are generated/marked and can be excluded: 50 * - 'create_table' 51 * - 'create_database' 52 * - 'insert' 53 * - 'update' 54 * - 'drop_column' 55 * - 'change_column' 56 * - 'change_default' 57 * - 'add_column' 58 * - 'add_index' 59 * - 'drop_index' 60 * NOTE: it may be needed to merge an 'add_index' or 'drop_index' type query into an 61 * 'add_column'/'change_column' query (adding "AUTO_INCREMENT" for example)! 62 * 63 * NOTE: collations and charset changes are ignored. It seems quite difficult to support this, 64 * and it seems to be best to handle this "manually". 65 * 66 * @author Originally taken from Wordpress, heavily enhanced and modified by blueyed 67 * 68 * @todo Handle COMMENT for tables?! 69 * 70 * @see http://dev.mysql.com/doc/refman/4.1/en/create-table.html 71 * 72 * @param array The list of queries for which the DB should be adjusted 73 * @param array Exclude query types (see list above). 74 * @param boolean Execute generated queries? TODO: get this outta here!!!! (sooooo bloated!) 75 * @return array The generated queries. 76 * table_name => array of arrays (queries with keys 'queries' (array), 'note' (string) and 'type' (string)) 77 * There's usually just a single query in "queries", but in some cases additional queries 78 * are needed (e.g., 'UPDATE' before we can change "NULL" setting). 79 */ 80 function db_delta( $queries, $exclude_types = array(), $execute = false ) 81 { 82 global $Debuglog, $DB, $debug; 83 84 if( ! is_array($queries) ) 85 { 86 $queries = array( $queries ); 87 } 88 89 if( ! is_array($exclude_types) ) 90 { 91 $exclude_types = empty($exclude_types) ? array() : array($exclude_types); 92 } 93 94 /** 95 * Generated query items, indexed by table name. 96 */ 97 $items = array(); 98 99 100 // Split the queries into $items, by their type: 101 foreach( $queries as $qry ) 102 { 103 if( preg_match( '|^(\s*CREATE TABLE\s+)(IF NOT EXISTS\s+)?([^\s(]+)(.*)$|is', $qry, $match) ) 104 { 105 $tablename = db_delta_remove_backticks(preg_replace( $DB->dbaliases, $DB->dbreplaces, $match[3] )); 106 $qry = $match[1].( empty($match[2]) ? '' : $match[2] ).$tablename.$match[4]; 107 108 $items[strtolower($tablename)][] = array( 109 'queries' => array($qry), 110 'note' => sprintf( 'Created table «<strong>%s</strong>»', $tablename ), 111 'type' => 'create_table' ); 112 } 113 elseif( preg_match( '|^\s*CREATE DATABASE\s([\S]+)|i', $qry, $match) ) 114 { // add to the beginning 115 array_unshift( $items, array( 116 'queries' => array($qry), 117 'note' => sprintf( 'Created database «<strong>%s</strong>»', $match[1] ), 118 'type' => 'create_database' ) ); 119 } 120 elseif( preg_match( '|^(\s*INSERT INTO\s+)([\S]+)(.*)$|is', $qry, $match) ) 121 { 122 $tablename = db_delta_remove_backticks(preg_replace( $DB->dbaliases, $DB->dbreplaces, $match[2] )); 123 $items[strtolower($tablename)][] = array( 124 'queries' => array($match[1].$tablename.$match[3]), 125 'note' => '', 126 'type' => 'insert' ); 127 } 128 elseif( preg_match( '|^(\s*UPDATE\s+)([\S]+)(.*)$|is', $qry, $match) ) 129 { 130 $tablename = db_delta_remove_backticks(preg_replace( $DB->dbaliases, $DB->dbreplaces, $match[2] )); 131 $items[strtolower($tablename)][] = array( 132 'queries' => array($match[1].$tablename.$match[3]), 133 'note' => '', 134 'type' => 'update' ); 135 } 136 else 137 { 138 $Debuglog->add( 'db_delta: Unrecognized query type: '.$qry, 'note' ); 139 } 140 } 141 142 143 /** 144 * @global array Available tables in the current database 145 */ 146 $tables = $DB->get_col('SHOW TABLES'); 147 148 // Loop through existing tables and check which tables and fields exist 149 foreach($tables as $table) 150 { // For every table in the database 151 $table_lowered = strtolower($table); // table names are treated case insensitive 152 153 if( ! isset( $items[$table_lowered] ) ) 154 { // This table exists in the database, but not in the creation queries. 155 continue; 156 } 157 158 /** 159 * @global array Hold the indices we want to create/have, with meta data keys. 160 */ 161 $indices = array(); 162 163 /** 164 * @global array Initially all existing indices. Any index, that does not get unset here, generates a 'drop_index' type query. 165 */ 166 $obsolete_indices = array(); 167 168 /** 169 * @global array Fields of the existing primary key (if any) 170 */ 171 $existing_primary_fields = array(); 172 173 /** 174 * @global array Fields of existing keys (including PRIMARY), lowercased (if any) 175 */ 176 $existing_key_fields = array(); 177 178 /** 179 * @global array Column field names of PRIMARY KEY, lowercased (if any) 180 */ 181 $primary_key_fields = array(); 182 183 /** 184 * @global array of col_names that have KEYs (including PRIMARY; lowercased). We use this for AUTO_INCREMENT magic. 185 */ 186 $fields_with_keys = array(); 187 188 /** 189 * @global string Holds the fielddef of an obsolete ("drop_column") AUTO_INCREMENT field. We must alter this with a PK "ADD COLUMN" query. 190 */ 191 $obsolete_autoincrement = NULL; 192 193 194 /** 195 * @global array List of fields (and definition from query) 196 * <code>fieldname (lowercase) => array( 197 * 'field' => "column_definition", 198 * 'where' => "[FIRST|AFTER xxx]" ) 199 * </code> 200 */ 201 $wanted_fields = array(); 202 203 /** 204 * @global boolean Do we have any variable-length fields? (see http://dev.mysql.com/doc/refman/4.1/en/silent-column-changes.html) 205 */ 206 $has_variable_length_field = false; 207 208 209 // Get all of the field names in the query from between the parens 210 preg_match( '|\((.*)\)|s', $items[$table_lowered][0]['queries'][0], $match ); // we have only one query here 211 $qryline = trim($match[1]); 212 213 // Separate field lines into an array 214 #$flds = preg_split( '~,(\r?\n|\r)~', $qryline, -1, PREG_SPLIT_NO_EMPTY ); 215 216 $flds = array(); 217 $in_parens = 0; 218 $buffer = ''; 219 for( $i = 0; $i < strlen($qryline); $i++ ) 220 { 221 $c = $qryline[$i]; 222 223 if( $c == ',' && ! $in_parens ) 224 { // split here: 225 $flds[] = trim($buffer); 226 $buffer = ''; 227 continue; 228 } 229 230 if( $c == '(' ) 231 { 232 $in_parens++; 233 } 234 elseif( $c == ')' ) 235 { 236 $in_parens--; 237 } 238 239 $buffer .= $c; 240 } 241 if( strlen($buffer) ) 242 { 243 $flds[] = trim($buffer); 244 } 245 246 //echo "<hr/><pre>\n".print_r(strtolower($table), true).":\n".print_r($items, true)."</pre><hr/>"; 247 248 $prev_fld = ''; 249 foreach( $flds as $create_definition ) 250 { // For every field line specified in the query 251 // Extract the field name 252 preg_match( '|^([^\s(]+)|', trim($create_definition), $match ); 253 $fieldname = db_delta_remove_backticks($match[1]); 254 $fieldname_lowered = strtolower($fieldname); 255 256 $create_definition = trim($create_definition, ", \r\n\t"); 257 258 if( in_array( $fieldname_lowered, array( '', 'primary', 'index', 'fulltext', 'unique', 'key' ) ) ) 259 { // INDEX (but not in column_definition - those get handled later) 260 $add_index = array( 261 'create_definition' => $create_definition, 262 ); 263 264 if( ! preg_match( '~^(PRIMARY(?:\s+KEY)|UNIQUE(?:\s+(?:INDEX|KEY))?|KEY|INDEX) (?:\s+(\w+))? (\s+USING \w+)? \s* \((.*)\)$~ix', $create_definition, $match ) ) 265 { // invalid type, should not happen 266 debug_die( 'Invalid type in $indices: '.$create_definition ); 267 } 268 $add_index['keyword'] = $match[1]; 269 $add_index['name'] = strtoupper($match[2]); 270 $add_index['type'] = $match[3]; // "USING [type_name]" 271 $add_index['col_names'] = explode( ',', $match[4] ); 272 foreach( $add_index['col_names'] as $k => $v ) 273 { 274 $add_index['col_names'][$k] = strtolower(trim($v)); 275 } 276 277 if( $fieldname_lowered == 'primary' ) 278 { // Remember PRIMARY KEY fields to be indexed (used for NULL check) 279 $primary_key_fields = $add_index['col_names']; 280 $add_index['is_PK'] = true; 281 } 282 else 283 { 284 $add_index['is_PK'] = false; 285 } 286 $fields_with_keys = array_unique( array_merge( $fields_with_keys, $add_index['col_names'] ) ); 287 288 $indices[] = $add_index; 289 } 290 else 291 { // "normal" field, add it to the field array 292 $wanted_fields[ strtolower($fieldname_lowered) ] = array( 293 'field' => $create_definition, 294 'where' => ( empty($prev_fld) ? 'FIRST' : 'AFTER '.$prev_fld ), 295 ); 296 $prev_fld = $fieldname; 297 298 if( preg_match( '~^\S+\s+(VARCHAR|TEXT|BLOB)~i', $create_definition ) ) 299 { 300 $has_variable_length_field = true; 301 } 302 } 303 } 304 305 306 // INDEX STUFF: 307 308 /** 309 * @global array Holds the existing indices (with array's key UPPERcased) 310 */ 311 $existing_indices = array(); 312 313 // Fetch the table index structure from the database 314 $tableindices = $DB->get_results( 'SHOW INDEX FROM '.$table ); 315 316 if( ! empty($tableindices) ) 317 { 318 // For every index in the table 319 foreach( $tableindices as $tableindex ) 320 { 321 // Add the index to the index data array 322 $keyname = strtoupper($tableindex->Key_name); 323 324 $existing_indices[$keyname]['name'] = $tableindex->Key_name; // original case 325 $existing_indices[$keyname]['columns'][] = array('fieldname' => $tableindex->Column_name, 'subpart' => $tableindex->Sub_part); 326 $existing_indices[$keyname]['unique'] = ($tableindex->Non_unique == 0) ? true : false; 327 } 328 unset($tableindices); 329 330 // Let's see which indices are present already for the table: 331 // TODO: dh> use meta data available now in $indices, instead of building a regular expression!? 332 $obsolete_indices = $existing_indices; // will get unset as found 333 } 334 335 336 // Pre-run KEYs defined in "column_definition" (e.g. used for AUTO_INCREMENT handling) 337 foreach( $wanted_fields as $fieldname_lowered => $field_info ) 338 { 339 $parse = $field_info['field']; 340 341 if( preg_match( '~ \b UNIQUE (?:\s+ KEY)? \b ~ix ', $parse, $match ) ) 342 { // This has an "inline" UNIQUE index: 343 $indices[] = array( 344 'name' => $fieldname_lowered, 345 'is_PK' => false, 346 'create_definition' => NULL, // "inline" 347 'col_names' => array($fieldname_lowered), 348 'keyword' => NULL, 349 #'type' => $match[3], // "USING [type_name]" 350 ); 351 352 unset( $obsolete_indices[strtoupper($fieldname_lowered)] ); 353 $parse = str_replace( $match[0], '', $parse ); 354 $fields_with_keys[] = $fieldname_lowered; 355 } 356 357 if( preg_match( '~ \b (PRIMARY\s+)? KEY \b ~ix', $parse, $match ) ) 358 { // inline PK: 359 $indices[] = array( 360 'name' => 'PRIMARY', 361 'is_PK' => true, 362 'create_definition' => NULL, // "inline" 363 'col_names' => array($fieldname_lowered), 364 'keyword' => NULL, 365 #'type' => $match[3], // "USING [type_name]" 366 ); 367 $fields_with_keys[] = $fieldname_lowered; 368 $primary_key_fields = array($fieldname_lowered); 369 unset( $obsolete_indices['PRIMARY'] ); 370 } 371 } 372 $fields_with_keys = array_unique($fields_with_keys); 373 374 375 foreach( $existing_indices as $index_name => $index_data ) 376 { 377 // Build a create string to compare to the query 378 $index_pattern = '^'; 379 if( $index_name == 'PRIMARY' ) 380 { 381 $index_pattern .= 'PRIMARY(\s+KEY)?'; 382 // optional primary key name: 383 $index_pattern .= '(\s+\w+)?'; 384 } 385 elseif( $index_data['unique'] ) 386 { 387 $index_pattern .= 'UNIQUE(\s+(?:INDEX|KEY))?'; 388 } 389 else 390 { 391 $index_pattern .= '(INDEX|KEY)'; 392 } 393 if( $index_name != 'PRIMARY' ) 394 { 395 $index_pattern .= '(\s+`?'.$index_name.'`?)?'; // optionally in backticks (and index name is optionally itself) 396 } 397 398 $index_columns = ''; 399 // For each column in the index 400 foreach( $index_data['columns'] as $column_data ) 401 { 402 if( $index_columns != '' ) 403 { 404 $index_columns .= '\s*,\s*'; 405 } 406 // Add the field to the column list string 407 $index_columns .= '`?'.$column_data['fieldname'].'`?'; // optionally in backticks 408 if( ! empty($column_data['subpart']) ) 409 { 410 $index_columns .= '\s*\(\s*'.$column_data['subpart'].'\s*\)\s*'; 411 } 412 } 413 414 // Sort index definitions with names to the beginning: 415 /* 416 usort( $indices, create_function( '$a, $b', ' 417 if( preg_match( "~^\w+\s+[^(]~", $a["create_definition"] ) 418 { 419 420 }' ) ); 421 */ 422 423 424 $used_auto_keys = array(); 425 foreach( $indices as $k => $index ) 426 { 427 $pattern = $index_pattern; 428 if( ! preg_match( '~^\w+\s+[^(]~', $index['create_definition'], $match ) ) 429 { // no key name given, make the name part optional, if it's the default one: 430 // (Default key name seems to be the first column, eventually with "_\d+"-suffix) 431 $auto_key = db_delta_remove_backticks(strtoupper($index['col_names'][0])); 432 if( isset($used_auto_keys[$auto_key]) ) 433 { 434 $used_auto_keys[$auto_key]++; 435 $auto_key .= '_'.$used_auto_keys[$auto_key]; 436 } 437 $used_auto_keys[$auto_key] = 1; 438 439 if( $auto_key == $index_name ) 440 { // the auto-generated keyname is the same as the one we have, so make it optional in the pattern: 441 $pattern .= '?'; 442 } 443 } 444 // Add the column list to the index create string 445 $pattern .= '\s*\(\s*'.$index_columns.'\s*\)'; 446 447 #pre_dump( '~'.$pattern.'~i', trim($index['create_definition']) ); 448 if( preg_match( '~'.$pattern.'~i', trim($index['create_definition']) ) ) 449 { // This index already exists: remove the index from our indices to create 450 unset($indices[$k]); 451 unset($obsolete_indices[$index_name]); 452 break; 453 } 454 } 455 if( isset($obsolete_indices[$index_name]) ) 456 { 457 #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"; 458 } 459 } 460 461 // Set $existing_primary_fields and $existing_key_fields 462 foreach( $existing_indices as $l_key_name => $l_key_info ) 463 { 464 $l_key_fields = array(); 465 foreach( $l_key_info['columns'] as $l_col ) 466 { 467 $l_key_fields[] = strtolower($l_col['fieldname']); 468 } 469 if( $l_key_name == 'PRIMARY' ) 470 { // Remember _existing_ PRIMARY KEYs 471 $existing_primary_fields = $l_key_fields; 472 } 473 474 $existing_key_fields = array_merge( $existing_key_fields, $l_key_fields ); 475 } 476 $existing_key_fields = array_unique($existing_key_fields); 477 #pre_dump( 'existing_primary_fields', $existing_primary_fields ); 478 #pre_dump( 'existing_key_fields', $existing_key_fields ); 479 480 481 // Fetch the table column structure from the database 482 $tablefields = $DB->get_results( 'DESCRIBE '.$table ); 483 484 485 // If "drop_column" is not excluded we have to check if all existing cols would get dropped, 486 // to prevent "You can't delete all columns with ALTER TABLE; use DROP TABLE instead(Errno=1090)" 487 if( ! in_array('drop_column', $exclude_types) ) 488 { 489 $at_least_one_col_stays = false; 490 foreach($tablefields as $tablefield) 491 { 492 $fieldname_lowered = strtolower($tablefield->Field); 493 494 if( isset($wanted_fields[ $fieldname_lowered ]) ) 495 { 496 $at_least_one_col_stays = true; 497 } 498 } 499 500 if( ! $at_least_one_col_stays ) 501 { // all columns get dropped: so we need to DROP TABLE and then use the original CREATE TABLE 502 array_unshift($items[$table_lowered], array( 503 'queries' => array('DROP TABLE '.$table), 504 'note' => 'Dropped <strong>'.$table.'.</strong>', 505 'type' => 'drop_column' )); 506 continue; // next $table 507 } 508 } 509 510 // For every field in the existing table 511 foreach($tablefields as $tablefield) 512 { 513 $fieldname_lowered = strtolower($tablefield->Field); 514 515 if( ! isset($wanted_fields[ $fieldname_lowered ]) ) 516 { // This field exists in the table, but not in the creation queries 517 518 if( in_array('drop_column', $exclude_types) ) 519 { 520 if( preg_match('~\bAUTO_INCREMENT\b~i', $tablefield->Extra) ) 521 { // must be modified with a ADD COLUMN which drops a PK 522 $obsolete_autoincrement = $tablefield; 523 } 524 } 525 else 526 { 527 $items[$table_lowered][] = array( 528 'queries' => array('ALTER TABLE '.$table.' DROP COLUMN '.$tablefield->Field), 529 'note' => 'Dropped '.$table.'.<strong>'.$tablefield->Field.'</strong>', 530 'type' => 'drop_column' ); 531 532 // Unset in key indices: 533 if( ($k = array_search($fieldname_lowered, $existing_key_fields)) !== false ) 534 { 535 unset($existing_key_fields[$k]); 536 } 537 if( ($k = array_search($fieldname_lowered, $existing_primary_fields)) !== false ) 538 { 539 unset($existing_primary_fields[$k]); 540 } 541 } 542 543 continue; 544 } 545 546 $column_definition = trim( $wanted_fields[$fieldname_lowered]['field'] ); 547 548 unset($type_matches); // have we detected the type as matching (for optional length param) 549 $fieldtype = ''; 550 551 $pattern_field = '`?'.$tablefield->Field.'`?'; // optionally in backticks 552 553 // Get the field type from the query 554 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 ) ) 555 { 556 $fieldtype = strtoupper($match[1]); 557 558 if( $fieldtype == 'INTEGER' ) 559 { // synonym 560 $fieldtype = 'INT'; 561 } 562 elseif( $fieldtype == 'DECIMAL' ) 563 { // synonym 564 $fieldtype = 'DEC'; 565 } 566 567 if( isset($match[2]) ) 568 { // append optional "length" param (trimmed) 569 $fieldtype .= preg_replace( '~\s+~', '', $match[2] ); 570 } 571 if( ! empty($match[3]) ) 572 { // "unsigned" 573 $fieldtype .= ' '.trim($match[3]); 574 } 575 if( ! empty($match[4]) ) 576 { // "zerofill" 577 $fieldtype .= ' '.trim($match[4]); 578 } 579 580 $field_to_parse = $match[5]; 581 582 // The length param is optional: 583 $matches_pattern = '~^'.preg_replace( '~\((\d+)\)~', '(\(\d+\))?', $tablefield->Type ).'$~i'; 584 $type_matches = preg_match( $matches_pattern, $fieldtype ); 585 } 586 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 ) ) 587 { 588 $fieldtype = strtoupper($match[1]); 589 if( isset($match[2]) ) 590 { // "binary" 591 $fieldtype .= trim($match[2]); 592 } 593 $field_to_parse = $match[3]; 594 595 // There's a bug with a "NOT NULL" field reported as "NULL", work around it (http://bugs.mysql.com/bug.php?id=20910): 596 if( $fieldtype == 'TIMESTAMP' ) 597 { 598 $ct_sql = $DB->get_var( 'SHOW CREATE TABLE '.$table, 1, 0 ); 599 if( preg_match( '~^\s*`'.$tablefield->Field.'`\s+TIMESTAMP\s+(NOT )?NULL~im', $ct_sql, $match ) ) 600 { 601 $tablefield->Null = empty($match[1]) ? 'YES' : 'NO'; 602 } 603 } 604 } 605 elseif( preg_match( '~^'.$pattern_field.'\s+ (CHAR|VARCHAR|BINARY|VARBINARY) \s* \( ([\d\s]+) \) (\s+ (BINARY|ASCII|UNICODE) )? (.*)$~ix', $column_definition, $match ) ) 606 { 607 $len = trim($match[2]); 608 $fieldtype = strtoupper($match[1]).'('.$len.')'; 609 610 if( ! empty($match[3]) ) 611 { // "binary", "ascii", "unicode" 612 $fieldtype .= ' '.$match[3]; 613 } 614 $field_to_parse = $match[5]; 615 616 if( strtoupper($match[1]) == 'VARCHAR' ) 617 { 618 if( $len < 4 ) 619 { // VARCHAR shorter than 4 get converted to CHAR (but reported as VARCHAR in MySQL 5.0) 620 $type_matches = preg_match( '~^(VAR)?CHAR\('.$len.'\)'.( $match[3] ? ' '.$match[3] : '' ).'$~i', $tablefield->Type ); 621 } 622 } 623 elseif( $has_variable_length_field && strtoupper($match[1]) == 'CHAR' ) 624 { // CHARs in a row with variable length fields get silently converted to VARCHAR (but reported as CHAR in MySQL 5.0) 625 $type_matches = preg_match( '~^(VAR)?'.preg_quote( $fieldtype, '~' ).'$~i', $tablefield->Type ); 626 } 627 } 628 elseif( preg_match( '~^'.$pattern_field.'\s+ (ENUM|SET) \s* \( (.*) \) (.*)$~ix', $column_definition, $match ) ) 629 { 630 $values = preg_split( '~\s*,\s*~', trim($match[2]), -1, PREG_SPLIT_NO_EMPTY ); // TODO: will fail for values containing ",".. 631 $values = implode( ',', $values ); 632 633 $fieldtype = strtoupper($match[1]).'('.$values.')'; 634 $field_compare = strtolower($match[1]).'('.$values.')'; 635 636 // compare case-sensitive 637 $type_matches = ( $field_compare == $tablefield->Type ); 638 639 $field_to_parse = $match[3]; 640 } 641 else 642 { 643 if( $debug ) 644 { 645 debug_die( 'db_delta(): Cannot find existing types field in column definition ('.$pattern_field.'/'.$column_definition.')' ); 646 } 647 continue; 648 } 649 650 651 // DEFAULT 652 $want_default = false; 653 if( preg_match( '~^(.*?) \s DEFAULT \s+ (?: (?: (["\']) (.*?) \2 ) | (\w+) ) (\s .*)?$~ix', $field_to_parse, $match ) ) 654 { 655 if( isset($match[4]) && $match[4] !== '' ) 656 { 657 $want_default = $match[4]; 658 $want_default_set = $match[4]; 659 } 660 else 661 { 662 $want_default = $match[3]; 663 $want_default_set = $match[2].$match[3].$match[2]; // encapsulate in quotes again 664 } 665 666 $field_to_parse = $match[1].( isset($match[5]) ? $match[5] : '' ); 667 } 668 669 670 // KEY 671 $has_inline_primary_key = false; 672 if( preg_match( '~^(.*) \b (?: (UNIQUE) (?:\s+ (?:INDEX|KEY))? | (?:PRIMARY \s+)? KEY ) \b (.*)$~ix', $field_to_parse, $match ) ) 673 { // fields got added to primary_key_fields and fields_with_keys before 674 $field_to_parse = $match[1].$match[3]; 675 if( empty($match[2]) ) 676 { 677 $has_inline_primary_key = true; // we need to DROP the PK if this column definition does not match 678 } 679 } 680 681 682 // AUTO_INCREMENT (with special index handling: AUTO_INCREMENT fields need to be PRIMARY or UNIQUE) 683 $is_auto_increment = false; 684 if( preg_match( '~(.*?) \b AUTO_INCREMENT \b (.*)$~ix', $field_to_parse, $match ) ) 685 { 686 $is_auto_increment = true; 687 $field_to_parse = $match[1].$match[2]; 688 689 if( ! preg_match( '~\bAUTO_INCREMENT\b~i', $tablefield->Extra ) ) 690 { // not AUTO_INCREMENT yet 691 $type_matches = false; 692 } 693 694 if( ! in_array( $fieldname_lowered, $fields_with_keys ) ) 695 { // no KEY defined (but required for AUTO_INCREMENT fields) 696 debug_die('No KEY/INDEX defined for AUTO_INCREMENT column!'); 697 } 698 699 if( in_array( $fieldname_lowered, $existing_key_fields ) ) 700 { 701 if( ! empty( $primary_key_fields ) ) 702 { 703 $column_definition .= ', DROP PRIMARY KEY'; 704 unset( $obsolete_indices['PRIMARY'] ); 705 } 706 } 707 else 708 { // a key for this AUTO_INCREMENT field does not exist yet, we search it in $indices 709 foreach( $indices as $k_index => $l_index ) 710 { // go through the indexes we want to have 711 712 if( array_search( $fieldname_lowered, $l_index['col_names'] ) === false ) 713 { // this is not an index for our column 714 continue; 715 } 716 717 // this index definition affects us, we have to add it to our ALTER statement.. 718 719 // See if we need to drop it, before adding it: 720 if( $l_index['is_PK'] ) 721 { // Part of a PRIMARY key.. 722 if( ! empty( $existing_primary_fields ) ) 723 { // and a PRIMARY key exists already 724 $column_definition .= ', DROP PRIMARY KEY'; 725 unset( $obsolete_indices['PRIMARY'] ); 726 } 727 $existing_primary_fields = array(); // we expect no existing primary key anymore 728 $primary_key_fields = $l_index['col_names']; // this becomes our primary key 729 } 730 elseif( isset( $existing_indices[$l_index['name']] ) ) 731 { // this index already exists, drop it: 732 $column_definition .= ', DROP INDEX '.$existing_indices[$l_index['name']]; // original case 733 unset( $existing_indices[$l_index['name']] ); // we expect that it does not exist anymore 734 if( ! in_array( $fieldname_lowered, $fields_with_keys ) ) 735 { // add te field to the list of keys we want/expect to have: 736 $fields_with_keys[] = $fieldname_lowered; 737 } 738 } 739 740 // Merge the INDEX creation into our ALTER query: 741 $column_definition .= ', ADD '.$l_index['create_definition']; 742 unset( $indices[$k_index] ); 743 } 744 } 745 } 746 747 748 // "[NOT] NULL" (requires $primary_key_fields to be finalized) 749 if( preg_match( '~(.*?) \b (NOT\s+)? NULL \b (.*)$~ix', $field_to_parse, $match ) ) 750 { // if "NOT" not matched it's NULL 751 $want_null = empty($match[2]); 752 $field_to_parse = $match[1].$match[3]; 753 } 754 else 755 { // not specified: "NULL" is default 756 $want_null = true; 757 } 758 759 if( in_array($fieldname_lowered, $primary_key_fields) || $is_auto_increment ) 760 { // If part of PRIMARY KEY or AUTO_INCREMENT field "NULL" is implicit 761 $change_null = false; // implicit NULL 762 $want_null = 'IMPLICIT'; 763 } 764 elseif( in_array($fieldname_lowered, $existing_primary_fields) && ! in_array($fieldname_lowered, $primary_key_fields) ) 765 { // the field was in PRIMARY KEY, but is no longer. It should get altered only if we want "NOT NULL" 766 $change_null = ( ! $want_null && $tablefield->Null == 'YES' ); 767 #pre_dump( $want_null ); 768 #$want_null = 'IMPLICIT2'; 769 #pre_dump( $primary_key_fields ); 770 } 771 else 772 { 773 if( $tablefield->Null == 'YES' ) 774 { 775 $change_null = ! $want_null; 776 } 777 else 778 { // I've seen '' and 'NO' for no.. 779 $change_null = $want_null; 780 } 781 } 782 783 784 if( ! isset($type_matches) ) 785 { // not tried to match before 786 $type_matches = ( strtoupper($tablefield->Type) == $fieldtype ); 787 } 788 789 #pre_dump( 'change_null ($change_null, $tablefield, $want_null)', $change_null, $tablefield, $want_null ); 790 #pre_dump( 'type_matches', $type_matches, strtolower($tablefield->Type), $fieldtype ); 791 792 793 // See what DEFAULT we would get or want 794 $update_default = NULL; 795 $update_default_set = NULL; 796 797 if( $want_default !== false ) 798 { 799 $update_default = $want_default; 800 $update_default_set = $want_default_set; 801 } 802 else 803 { // implicit default, see http://dev.mysql.com/doc/refman/4.1/en/data-type-defaults.html 804 if( preg_match( '~^(TINYINT|SMALLINT|MEDIUMINT|INTEGER|INT|BIGINT|REAL|DOUBLE|FLOAT|DECIMAL|DEC|NUMERIC)$~', $fieldtype ) ) 805 { // numeric 806 $update_default = '0'; 807 $update_default_set = '0'; 808 } 809 elseif( $fieldtype == 'TIMESTAMP' ) 810 { // TODO: the default should be current date and time for the first field - but AFAICS we won't have NULL fields anyway 811 } 812 elseif( preg_match( '~^(DATETIME|DATE|TIME|YEAR)$~', $fieldtype ) ) 813 { 814 $update_default = '0'; // short form for various special "zero" values 815 $update_default_set = '0'; 816 } 817 elseif( substr($fieldtype, 0, 4) == 'ENUM' ) 818 { 819 preg_match( '~["\']?.*?["\']?\s*[,)]~x', substr($fieldtype,5), $match ); 820 $update_default_set = trim( $match[0], "\n\r\t\0\x0bB ()," ); // strip default whitespace, braces & comma 821 // first value (until "," or end) of $fieldtype_param: 822 $update_default = preg_replace( '~^(["\'])(.*)\1$~', '$2', $update_default_set ); // without quotes 823 } 824 else 825 { 826 $update_default_set = "''"; // empty string for string types 827 $update_default = ''; 828 } 829 } 830 831 832 // Is actual field type different from the field type in query? 833 if( ! $type_matches || $change_null ) 834 { // Change the whole column to $column_definition: 835 /* 836 echo '<h2>No_Match</h2>'; 837 pre_dump( $type_matches, $change_null, $want_null ); 838 pre_dump( $tablefield, $column_definition ); 839 pre_dump( 'flds', $flds ); 840 pre_dump( 'wanted_fields', $wanted_fields ); 841 pre_dump( strtolower($tablefield->Type), $fieldtype, $column_definition ); 842 */ 843 844 $queries = array( 'ALTER TABLE '.$table ); 845 846 // Handle inline PRIMARY KEY definition: 847 if( $has_inline_primary_key && ! empty($existing_primary_fields) ) // there's a PK that needs to get removed 848 { // the column is part of the PRIMARY KEY, which needs to get dropped before (we already handle that for AUTO_INCREMENT fields) 849 $queries[0] .= ' DROP PRIMARY KEY,'; 850 $existing_primary_fields = array(); // we expect no existing primary key anymore 851 unset( $obsolete_indices['PRIMARY'] ); 852 } 853 854 $queries[0] .= ' CHANGE COLUMN '.$tablefield->Field.' '.$column_definition; 855 856 // Handle changes from "NULL" to "NOT NULL" 857 if( $change_null && ! $want_null && isset($update_default_set) ) 858 { // Prepend query to update NULL fields to default 859 array_unshift( $queries, 'UPDATE '.$table.' SET '.$tablefield->Field.' = '.$update_default_set.' WHERE '.$tablefield->Field.' IS NULL' ); 860 861 if( substr( $tablefield->Type, 0, 5 ) == 'enum(' ) 862 { 863 $existing_enum_field_values = preg_split( '~\s*,\s*~', substr( $tablefield->Type, 5, -1 ), -1, PREG_SPLIT_NO_EMPTY ); 864 865 foreach( $existing_enum_field_values as $k => $v ) 866 { 867 $existing_enum_field_values[$k] = preg_replace( '~^(["\'])(.*)\1$~', '$2', $v ); // strip quotes 868 } 869 870 if( ! in_array( $update_default, $existing_enum_field_values ) ) 871 { // we cannot update straight to the new default, because it does not exist yet! 872 873 // Update the column first, without the NULL change 874 array_unshift( $queries, 'ALTER TABLE '.$table.' CHANGE COLUMN '.$tablefield->Field.' '.preg_replace( '~\sNOT\s+NULL~i', '', $column_definition ) ); 875 } 876 } 877 } 878 879 // Add a query to change the column type 880 $items[$table_lowered][] = array( 881 'queries' => $queries, 882 'note' => 'Changed type of '.$table.'.<strong>'.$tablefield->Field.'</strong> from '.$tablefield->Type.' to '.$column_definition, 883 'type' => 'change_column' ); 884 } 885 else 886 { // perhaps alter or drop DEFAULT: 887 if( $want_default !== false ) 888 { // DEFAULT given 889 $existing_default = $tablefield->Default === NULL ? 'NULL' : $tablefield->Default; 890 891 if( $existing_default != $want_default ) // DEFAULT is case-sensitive 892 { // Add a query to change the column's default value 893 $items[$table_lowered][] = array( 894 'queries' => array('ALTER TABLE '.$table.' ALTER COLUMN '.$tablefield->Field.' SET DEFAULT '.$want_default_set), 895 'note' => "Changed default value of {$table}.<strong>{$tablefield->Field}</strong> from $existing_default to $want_default_set", 896 'type' => 'change_default' ); 897 } 898 } 899 elseif( ! empty($tablefield->Default) && $tablefield->Default != $update_default ) 900 { // No DEFAULT given, but it exists one, so drop it (IF not a TIMESTAMP or DATETIME field) 901 if( $tablefield->Type != 'timestamp' && $tablefield->Type != 'datetime' ) 902 { 903 $items[$table_lowered][] = array( 904 'queries' => array('ALTER TABLE '.$table.' ALTER COLUMN '.$tablefield->Field.' DROP DEFAULT'), 905 'note' => "Dropped default value of {$table}.<strong>{$tablefield->Field}</strong>", 906 'type' => 'change_default' ); // might be also 'drop_default' 907 } 908 } 909 } 910 911 // Remove the field from the array (so it's not added) 912 unset($wanted_fields[$fieldname_lowered]); 913 } 914 915 916 foreach($wanted_fields as $fieldname_lowered => $fielddef) 917 { // For every remaining field specified for the table 918 $column_definition = $fielddef['field'].' '.$fielddef['where']; 919 920 $is_auto_increment = false; 921 // AUTO_INCREMENT (with special index handling: AUTO_INCREMENT fields need to be PRIMARY or UNIQUE) 922 if( preg_match( '~(.*?) \b AUTO_INCREMENT \b (.*)$~ix', $fielddef['field'], $match ) ) 923 { 924 if( ! in_array( $fieldname_lowered, $fields_with_keys ) ) 925 { // no KEY defined (but required for AUTO_INCREMENT fields) 926 debug_die('No KEY/INDEX defined for AUTO_INCREMENT column!'); 927 } 928 $is_auto_increment = true; 929 930 931 foreach( $indices as $k_index => $l_index ) 932 { // go through the indexes we want to have 933 934 if( array_search( $fieldname_lowered, $l_index['col_names'] ) === false ) 935 { // this is not an index for our column 936 continue; 937 } 938 939 // this index definition affects us, we have to add it to our ALTER statement.. 940 941 // See if we need to drop it, before adding it: 942 if( $l_index['is_PK'] ) 943 { // Part of a PRIMARY key.. 944 if( ! empty( $existing_primary_fields ) ) 945 { // and a PRIMARY key exists already 946 $column_definition .= ', DROP PRIMARY KEY'; 947 unset( $obsolete_indices['PRIMARY'] ); 948 } 949 $existing_primary_fields = array(); // we expect no existing primary key anymore 950 $primary_key_fields = $l_index['col_names']; // this becomes our primary key 951 } 952 elseif( isset( $existing_indices[$l_index['name']] ) ) 953 { // this index already exists, drop it: 954 $column_definition .= ', DROP INDEX '.$existing_indices[$l_index['name']]; // original case 955 unset( $existing_indices[$l_index['name']] ); // we expect that it does not exist anymore 956 if( ! in_array( $fieldname_lowered, $fields_with_keys ) ) 957 { // add te field to the list of keys we want/expect to have: 958 $fields_with_keys[] = $fieldname_lowered; 959 } 960 } 961 962 // Merge the INDEX creation into our ALTER query: 963 $column_definition .= ', ADD '.$l_index['create_definition']; 964 unset( $indices[$k_index] ); 965 } 966 } 967 968 // Push a query line into $items that adds the field to that table 969 $query = 'ALTER TABLE '.$table.' ADD COLUMN '.$column_definition; 970 971 // Handle inline PRIMARY KEY definition: 972 if( preg_match( '~^(.*) \b (?: (UNIQUE) (?:\s+ (?:INDEX|KEY))? | (?:PRIMARY \s+)? KEY ) \b (.*)$~ix', $column_definition, $match ) // "has_inline_primary_key" 973 && count($existing_primary_fields) 974 && ! in_array($fieldname_lowered, $existing_primary_fields) ) 975 { // the column is part of the PRIMARY KEY, which needs to get dropped before (we already handle that for AUTO_INCREMENT fields) 976 $query .= ', DROP PRIMARY KEY'; 977 $existing_primary_fields = array(); // we expect no existing primary key anymore 978 unset( $obsolete_indices['PRIMARY'] ); 979 980 if( isset($obsolete_autoincrement) ) 981 { 982 $query .= ', MODIFY COLUMN '.$obsolete_autoincrement->Field.' '.$obsolete_autoincrement->Type.' '.( $obsolete_autoincrement->Field == 'YES' ? 'NULL' : 'NOT NULL' ); 983 } 984 } 985 986 $items[$table_lowered][] = array( 987 'queries' => array($query), 988 'note' => 'Added column '.$table.'.<strong>'.$fielddef['field'].'</strong>', 989 'type' => 'add_column' ); 990 } 991 992 993 // Remove the original table creation query from processing 994 array_shift( $items[$table_lowered] ); 995 996 // Add the remaining indices (which are not "inline" with a column definition and therefor already handled): 997 foreach( $indices as $index ) 998 { 999 if( empty($index['create_definition']) ) 1000 { // skip "inline" 1001 continue; 1002 } 1003 $query = 'ALTER TABLE '.$table; 1004 if( $index['is_PK'] && $existing_primary_fields ) 1005 { 1006 $query .= ' DROP PRIMARY KEY,'; 1007 unset( $obsolete_indices['PRIMARY'] ); 1008 } 1009 // Push a query line into $items that adds the index to that table 1010 $items[$table_lowered][] = array( 1011 'queries' => array($query.' ADD '.$index['create_definition']), 1012 'note' => 'Added index <strong>'.$index['create_definition'].'</strong>', 1013 'type' => 'add_index' ); 1014 } 1015 1016 1017 foreach( $obsolete_indices as $index_info ) 1018 { 1019 // Push a query line into $items that drops the index from the table 1020 $items[$table_lowered][] = array( 1021 'queries' => array("ALTER TABLE {$table} DROP ".( $index_info['name'] == 'PRIMARY' ? 'PRIMARY KEY' : 'INDEX '.$index_info['name'] )), 1022 'note' => 'Dropped index <strong>'.$index_info['name'].'</strong>', 1023 'type' => 'drop_index' ); 1024 } 1025 } 1026 1027 1028 // Filter types we want to exclude: 1029 if( ! empty($exclude_types) ) 1030 { 1031 foreach( $items as $table => $itemlist ) 1032 { 1033 $removed_one = false; 1034 foreach( $itemlist as $k => $item ) 1035 { 1036 if( in_array($item['type'], $exclude_types) ) 1037 { 1038 unset( $items[$table][$k] ); 1039 $removed_one = true; 1040 } 1041 } 1042 if( $removed_one ) 1043 { // Re-order (0, 1, 2, ..) 1044 $items[$table] = array_values($items[$table]); 1045 } 1046 } 1047 } 1048 1049 // Unset empty table indices: 1050 foreach( $items as $table => $itemlist ) 1051 { 1052 if( empty($itemlist) ) 1053 { 1054 unset( $items[$table] ); 1055 continue; 1056 } 1057 } 1058 1059 if( $execute ) 1060 { 1061 foreach( $items as $table => $itemlist ) 1062 { 1063 foreach( $itemlist as $item ) 1064 { 1065 foreach( $item['queries'] as $query ) 1066 { 1067 #pre_dump( $query ); 1068 $DB->query( $query ); 1069 } 1070 } 1071 } 1072 } 1073 1074 return $items; 1075 } 1076 1077 1078 /** 1079 * Remove backticks around a field/table name. 1080 * 1081 * "backtick" means "single backquote" (`) 1082 * 1083 * @param string Field name 1084 * @return string 1085 */ 1086 function db_delta_remove_backticks($fieldname) 1087 { 1088 if( substr($fieldname, 0, 1) == '`' && substr($fieldname, -1) == '`' ) 1089 { // backticks: 1090 $fieldname = substr($fieldname, 1, -1); 1091 } 1092 return $fieldname; 1093 } 1094 1095 1096 /** 1097 * Alter the DB schema to match the current expected one ({@link $schema_queries}). 1098 * 1099 * @todo if used by install only, then put it into the install folde!!! 1100 * 1101 * @param boolean Display what we've done? 1102 */ 1103 function install_make_db_schema_current( $display = true ) 1104 { 1105 global $schema_queries, $DB, $debug; 1106 1107 foreach( $schema_queries as $table => $query_info ) 1108 { 1109 $items_need_update = db_delta( $query_info[1], array('drop_column', 'drop_index') ); 1110 1111 if( empty($items_need_update) ) 1112 { 1113 continue; 1114 } 1115 1116 if( ! $display ) 1117 { // just execute queries 1118 foreach( $items_need_update as $table => $itemlist ) 1119 { 1120 foreach( $itemlist as $item ) 1121 { 1122 foreach( $item['queries'] as $query ) 1123 { 1124 $DB->query( $query ); 1125 } 1126 } 1127 } 1128 } 1129 else 1130 { // the same, but with output 1131 foreach( $items_need_update as $table => $itemlist ) 1132 { 1133 if( count($itemlist) == 1 && $itemlist[0]['type'] == 'create_table' ) 1134 { 1135 echo $itemlist[0]['note'].'<br />'; 1136 foreach( $itemlist[0]['queries'] as $query ) 1137 { // should be just one, but just in case 1138 $DB->query( $query ); 1139 } 1140 } 1141 else 1142 { 1143 echo 'Altering table «'.$table.'»...'; 1144 echo '<ul>'; 1145 foreach( $itemlist as $item ) 1146 { 1147 echo '<li>'.$item['note']; 1148 if( $debug ) 1149 { 1150 pre_dump( $item['queries'] ); 1151 } 1152 echo '</li>'; 1153 foreach( $item['queries'] as $query ) 1154 { 1155 $DB->query( $query ); 1156 } 1157 } 1158 echo "</ul>"; 1159 } 1160 } 1161 } 1162 } 1163 } 1164 1165 1166 /* {{{ Revision log: 1167 * $Log: _upgrade.funcs.php,v $ 1168 * Revision 1.1 2007/06/25 10:59:00 fplanque 1169 * MODULES (refactored MVC) 1170 * 1171 * Revision 1.34 2007/04/26 00:11:08 fplanque 1172 * (c) 2007 1173 * 1174 * Revision 1.33 2007/04/15 22:16:59 blueyed 1175 * db_delta() fixes regarding index handlin 1176 * 1177 * Revision 1.32 2007/02/11 02:17:27 blueyed 1178 * Normalized case handling for $fieldtype; fixed $update_default for ENUM 1179 * 1180 * Revision 1.29 2007/01/18 21:03:51 blueyed 1181 * db_delta() fixes: splitting fields by comma; inline UNIQUE and PK handling 1182 * 1183 * Revision 1.28 2007/01/14 21:55:07 blueyed 1184 * doc 1185 * 1186 * Revision 1.27 2007/01/14 21:40:17 blueyed 1187 * db_delta() fix for PK handling/obsoleting 1188 * 1189 * Revision 1.26 2007/01/14 03:05:54 blueyed 1190 * db_delta() fix: handle/remove backticks in auto-generated index names 1191 * 1192 * Revision 1.25 2007/01/12 01:34:39 fplanque 1193 * doc 1194 * 1195 * Revision 1.24 2007/01/12 01:21:38 blueyed 1196 * db_delta() fixes: handle backticks (to be tested more), dropping all existing columns in a table and index/key names (to be tested more) 1197 * 1198 * Revision 1.23 2006/11/24 17:41:59 blueyed 1199 * Fixed NULL handling of TIMESTAMPs and work around the buggy behaviour I was experiencing 1200 * }}} 1201 */ 1202 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Thu Nov 29 23:58:50 2007 | par Balluche grâce à PHPXref 0.7 |
|
|