b2evolution PHP Cross Reference Blogging Systems

Source: /inc/_core/model/db/_upgrade.funcs.php - 1660 lines - 57661 bytes - Summary - Text - Print

Description: This file implements functions useful for upgrading DB schema. This file is part of the b2evolution/evocms project - {@link http://b2evolution.net/}. See also {@link http://sourceforge.net/projects/evocms/}.

   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-2014 by Francois Planque - {@link http://fplanque.com/}.
   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 6136 2014-03-08 07:59:48Z manuel $
  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 recommended 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   *  - 'alter_engine'
  61   * NOTE: it may be needed to merge an 'add_index' or 'drop_index' type query into an
  62   *       'add_column'/'change_column' query (adding "AUTO_INCREMENT" for example)!
  63   *
  64   * NOTE: collations and charset changes are ignored. It seems quite difficult to support this,
  65   *       and it seems to be best to handle this "manually".
  66   *
  67   * @author Originally taken from Wordpress, heavily enhanced and modified by blueyed
  68   *
  69   * @todo Handle COMMENT for tables?!
  70   *
  71   * @see http://dev.mysql.com/doc/refman/4.1/en/create-table.html
  72   *
  73   * @param array The list of queries for which the DB should be adjusted
  74   * @param array Exclude query types (see list above).
  75   * @param boolean Execute generated queries?  TODO: get this outta here!!!! (sooooo bloated!)
  76   * @return array The generated queries.
  77   *        table_name => array of arrays (queries with keys 'queries' (array), 'note' (string) and 'type' (string))
  78   *        There's usually just a single query in "queries", but in some cases additional queries
  79   *        are needed (e.g., 'UPDATE' before we can change "NULL" setting).
  80   */
  81  function db_delta( $queries, $exclude_types = array(), $execute = false )
  82  {
  83      global $Debuglog, $DB, $debug;
  84  
  85      if( ! is_array($queries) )
  86      {
  87          $queries = array( $queries );
  88      }
  89  
  90      if( ! is_array($exclude_types) )
  91      {
  92          $exclude_types = empty($exclude_types) ? array() : array($exclude_types);
  93      }
  94  
  95      /**
  96       * Generated query items, indexed by table name.
  97       */
  98      $items = array();
  99  
 100  
 101      // Split the queries into $items, by their type:
 102      foreach( $queries as $qry )
 103      {
 104          // Remove any comments from the SQL:
 105          $qry = remove_comments_from_query( $qry );
 106  
 107          if( preg_match( '|^(\s*CREATE TABLE\s+)(IF NOT EXISTS\s+)?([^\s(]+)(.*)$|is', $qry, $match) )
 108          {
 109              $tablename = db_delta_remove_quotes(preg_replace( $DB->dbaliases, $DB->dbreplaces, $match[3] ));
 110              $qry = $match[1].( empty($match[2]) ? '' : $match[2] ).$tablename.$match[4];
 111  
 112              $items[strtolower($tablename)][] = array(
 113                  'queries' => array($qry),
 114                  'note' => sprintf( 'Created table &laquo;<strong>%s</strong>&raquo;', $tablename ),
 115                  'type' => 'create_table' );
 116          }
 117          elseif( preg_match( '|^\s*CREATE DATABASE\s([\S]+)|i', $qry, $match) )
 118          { // add to the beginning
 119              array_unshift( $items, array(
 120                  'queries' => array($qry),
 121                  'note' => sprintf( 'Created database &laquo;<strong>%s</strong>&raquo;', $match[1] ),
 122                  'type' => 'create_database' ) );
 123          }
 124          elseif( preg_match( '|^(\s*INSERT INTO\s+)([\S]+)(.*)$|is', $qry, $match) )
 125          {
 126              $tablename = db_delta_remove_quotes(preg_replace( $DB->dbaliases, $DB->dbreplaces, $match[2] ));
 127              $items[strtolower($tablename)][] = array(
 128                  'queries' => array($match[1].$tablename.$match[3]),
 129                  'note' => '',
 130                  'type' => 'insert' );
 131          }
 132          elseif( preg_match( '|^(\s*UPDATE\s+)([\S]+)(.*)$|is', $qry, $match) )
 133          {
 134              $tablename = db_delta_remove_quotes(preg_replace( $DB->dbaliases, $DB->dbreplaces, $match[2] ));
 135              $items[strtolower($tablename)][] = array(
 136                  'queries' => array($match[1].$tablename.$match[3]),
 137                  'note' => '',
 138                  'type' => 'update' );
 139          }
 140          else
 141          {
 142              $Debuglog->add( 'db_delta: Unrecognized query type: '.$qry, 'note' );
 143          }
 144      }
 145  
 146      /**
 147       * @global array Available tables in the current database
 148       */
 149      $tables = $DB->get_col('SHOW TABLES');
 150  
 151      // Loop through existing tables and check which tables and fields exist
 152      foreach($tables as $table)
 153      { // For every table in the database
 154          $table_lowered = strtolower($table);  // table names are treated case insensitive
 155  
 156          if( ! isset( $items[$table_lowered] ) )
 157          { // This table exists in the database, but not in the creation queries.
 158              continue;
 159          }
 160  
 161          /**
 162           * @global array Hold the indices we want to create/have, with meta data keys.
 163           */
 164          $indices = array();
 165  
 166          /**
 167           * @global array Initially all existing indices. Any index, that does not get unset here, generates a 'drop_index' type query.
 168           */
 169          $obsolete_indices = array();
 170  
 171          /**
 172           * @global array Fields of the existing primary key (if any)
 173           */
 174          $existing_primary_fields = array();
 175  
 176          /**
 177           * @global array Fields of existing keys (including PRIMARY), lowercased (if any)
 178           */
 179          $existing_key_fields = array();
 180  
 181          /**
 182           * @global array Column field names of PRIMARY KEY, lowercased (if any)
 183           */
 184          $primary_key_fields = array();
 185  
 186          /**
 187           * @global array Column field names of FOREIGN KEY, lowercased (if any)
 188           */
 189          $foreign_key_fields = array();
 190  
 191          /**
 192           * @global array of col_names that have KEYs (including PRIMARY; lowercased). We use this for AUTO_INCREMENT magic.
 193           */
 194          $fields_with_keys = array();
 195  
 196          /**
 197           * @global string Holds the fielddef of an obsolete ("drop_column") AUTO_INCREMENT field. We must alter this with a PK "ADD COLUMN" query.
 198           */
 199          $obsolete_autoincrement = NULL;
 200  
 201  
 202          /**
 203           * @global array List of fields (and definition from query)
 204           *   <code>fieldname (lowercase) => array(
 205           *         'field' => "column_definition",
 206           *         'where' => "[FIRST|AFTER xxx]" )
 207           *   </code>
 208           */
 209          $wanted_fields = array();
 210  
 211          /**
 212           * @global boolean Do we have any variable-length fields? (see http://dev.mysql.com/doc/refman/4.1/en/silent-column-changes.html)
 213           */
 214          $has_variable_length_field = false;
 215  
 216  
 217          // Get all of the field names in the query from between the parens
 218          $flds = get_fieldlines_from_query( $items[$table_lowered][0]['queries'][0] );
 219  
 220          //echo "<hr/><pre>\n".print_r(strtolower($table), true).":\n".print_r($items, true)."</pre><hr/>";
 221  
 222          // ALTER ENGINE, if different (and given in query):
 223          if( ( $wanted_engine = get_engine_from_query( $items[$table_lowered][0]['queries'][0] ) ) !== false )
 224          {
 225              $current_engine = $DB->get_row( '
 226                  SHOW TABLE STATUS LIKE '.$DB->quote($table) );
 227              $current_engine = $current_engine->Engine;
 228  
 229              if( strtolower($current_engine) != strtolower($wanted_engine) )
 230              {
 231                  $items[$table_lowered][] = array(
 232                              'queries' => array('ALTER TABLE '.$table.' ENGINE='.$wanted_engine),
 233                              'note' => 'Alter engine of <strong>'.$table.'.</strong> to <strong>'.$wanted_engine.'</strong>',
 234                              'type' => 'alter_engine' );
 235              }
 236          }
 237  
 238          $prev_fld = '';
 239          foreach( $flds as $create_definition )
 240          { // For every field line specified in the query
 241              // Extract the field name
 242              preg_match( '|^([^\s(]+)|', trim($create_definition), $match );
 243              $fieldname = db_delta_remove_quotes($match[1]);
 244              $fieldname_lowered = strtolower($fieldname);
 245  
 246              $create_definition = trim($create_definition, ", \r\n\t");
 247  
 248              if( in_array( $fieldname_lowered, array( '', 'primary', 'foreign', 'index', 'fulltext', 'unique', 'key' ) ) )
 249              { // INDEX (but not in column_definition - those get handled later)
 250                  $add_index = array(
 251                      'create_definition' => $create_definition,
 252                  );
 253  
 254                  if( !  preg_match( '~^(PRIMARY(?:\s+KEY)|(?:FULLTEXT|UNIQUE)(?:\s+(?:INDEX|KEY))?|KEY|INDEX) (?:\s+()     (\w+)      )? (\s+USING\s+\w+)? \s* \((.*)\)$~ix', $create_definition, $match )
 255                      && ! 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 )
 256                      && ! preg_match( '~^(FOREIGN\s+KEY) \s* \((.*)\) \s* (REFERENCES) \s* ([^( ]*) \s* \((.*)\) \s* (.*)$~ixs', $create_definition, $match ) )
 257                  { // invalid type, should not happen
 258                      debug_die( 'Invalid type in $indices: '.$create_definition );
 259                      // TODO: add test: Invalid type in $indices: KEY "coord" ("lon","lat")
 260                  }
 261  
 262                  if( $fieldname_lowered == 'foreign' )
 263                  { // Remember FOREIGN KEY fields, but they don't have to be indexed
 264                      $reference_table_name = db_delta_remove_quotes(preg_replace( $DB->dbaliases, $DB->dbreplaces, $match[4] ));
 265                      $foreign_key_fields[] = array( 'fk_fields' => $match[2], 'reference_table' => $reference_table_name, 'reference_columns' => $match[5], 'fk_definition' => $match[6], 'create' => true );
 266                      continue;
 267                  }
 268  
 269                  $add_index['keyword'] = $match[1];
 270                  $add_index['name'] = strtoupper($match[3]);
 271                  $add_index['type'] = $match[4]; // "USING [type_name]"
 272                  $add_index['col_names'] = explode( ',', $match[5] );
 273                  foreach( $add_index['col_names'] as $k => $v )
 274                  {
 275                      $add_index['col_names'][$k] = strtolower(db_delta_remove_quotes(trim($v)));
 276                  }
 277  
 278                  if( $fieldname_lowered == 'primary' )
 279                  { // Remember PRIMARY KEY fields to be indexed (used for NULL check)
 280                      $primary_key_fields = $add_index['col_names'];
 281                      $add_index['is_PK'] = true;
 282                  }
 283                  else
 284                  {
 285                      $add_index['is_PK'] = false;
 286                  }
 287                  $fields_with_keys = array_unique( array_merge( $fields_with_keys, $add_index['col_names'] ) );
 288  
 289                  $indices[] = $add_index;
 290              }
 291              else
 292              { // "normal" field, add it to the field array
 293                  $wanted_fields[ strtolower($fieldname_lowered) ] = array(
 294                          'field' => $create_definition,
 295                          'where' => ( empty($prev_fld) ? 'FIRST' : 'AFTER '.$prev_fld ),
 296                      );
 297                  $prev_fld = $fieldname;
 298  
 299                  if( preg_match( '~^\S+\s+(VARCHAR|TEXT|BLOB)~i', $create_definition ) )
 300                  {
 301                      $has_variable_length_field = true;
 302                  }
 303              }
 304          }
 305  
 306  
 307          // INDEX STUFF:
 308  
 309          /**
 310           * @global array Holds the existing indices (with array's key UPPERcased)
 311           */
 312          $existing_indices = array();
 313  
 314          // Fetch the table index structure from the database
 315          $tableindices = $DB->get_results( 'SHOW INDEX FROM '.$table );
 316  
 317          if( ! empty($tableindices) )
 318          {
 319              // For every index in the table
 320              foreach( $tableindices as $tableindex )
 321              {
 322                  // Add the index to the index data array
 323                  $keyname = strtoupper($tableindex->Key_name);
 324  
 325                  $existing_indices[$keyname]['name'] = $tableindex->Key_name; // original case
 326                  $existing_indices[$keyname]['columns'][] = array('fieldname' => $tableindex->Column_name, 'subpart' => $tableindex->Sub_part);
 327                  $existing_indices[$keyname]['unique'] = ($tableindex->Non_unique == 0) ? true : false;
 328              }
 329              unset($tableindices);
 330  
 331              // Let's see which indices are present already for the table:
 332              // TODO: dh> use meta data available now in $indices, instead of building a regular expression!?
 333              $obsolete_indices = $existing_indices; // will get unset as found
 334          }
 335  
 336  
 337          // Pre-run KEYs defined in "column_definition" (e.g. used for AUTO_INCREMENT handling)
 338          foreach( $wanted_fields as $fieldname_lowered => $field_info )
 339          {
 340              $parse = $field_info['field'];
 341  
 342              if( preg_match( '~ \b UNIQUE (?:\s+ KEY)? \b ~ix ', $parse, $match ) )
 343              { // This has an "inline" UNIQUE index:
 344                  if( ! is_in_quote( $parse, ' '.$match[0] ) )
 345                  { // isn't between quotation marks, so it must be a primary key.
 346                      $indices[] = array(
 347                              'name' => $fieldname_lowered,
 348                              'is_PK' => false,
 349                              'create_definition' => NULL, // "inline"
 350                              'col_names' => array($fieldname_lowered),
 351                              'keyword' => NULL,
 352                              #'type' => $match[3], // "USING [type_name]"
 353                          );
 354  
 355                      unset( $obsolete_indices[strtoupper($fieldname_lowered)] );
 356                      $parse = str_replace( $match[0], '', $parse );
 357                      $fields_with_keys[] = $fieldname_lowered;
 358                  }
 359              }
 360  
 361              if( preg_match( '~ \b (PRIMARY\s+)? KEY \b ~ix', $parse, $match ) )
 362              { // inline PK:
 363                  // Check if this key is between quotation marks
 364                  if( ! is_in_quote( $parse, ' '.$match[0] ) )
 365                  { // it isn't between quotation marks, so it must be a primary key.
 366                      $indices[] = array(
 367                              'name' => 'PRIMARY',
 368                              'is_PK' => true,
 369                              'create_definition' => NULL, // "inline"
 370                              'col_names' => array($fieldname_lowered),
 371                              'keyword' => NULL,
 372                              #'type' => $match[3], // "USING [type_name]"
 373                          );
 374                      $fields_with_keys[] = $fieldname_lowered;
 375                      $primary_key_fields = array($fieldname_lowered);
 376                      unset( $obsolete_indices['PRIMARY'] );
 377                  }
 378              }
 379          }
 380          $fields_with_keys = array_unique($fields_with_keys);
 381  
 382  
 383          foreach( $existing_indices as $index_name => $index_data )
 384          {
 385              // Build a create string to compare to the query
 386              $index_pattern = '^';
 387              if( $index_name == 'PRIMARY' )
 388              {
 389                  $index_pattern .= 'PRIMARY(\s+KEY)?';
 390                  // optional primary key name:
 391                  $index_pattern .= '(\s+[`"]?\w+[`"]?)?';
 392              }
 393              elseif( $index_data['unique'] )
 394              {
 395                  $index_pattern .= 'UNIQUE(\s+(?:INDEX|KEY))?';
 396              }
 397              else
 398              {
 399                  $index_pattern .= '(INDEX|(?:FULLTEXT\s+)?KEY)';
 400              }
 401              if( $index_name != 'PRIMARY' )
 402              {
 403                  $index_pattern .= '(\s+[`"]?'.$index_name.'[`"]?)?'; // optionally in backticks (and index name is optionally itself)
 404              }
 405  
 406              $index_columns = '';
 407              // For each column in the index
 408              foreach( $index_data['columns'] as $column_data )
 409              {
 410                  if( $index_columns != '' )
 411                  {
 412                      $index_columns .= '\s*,\s*';
 413                  }
 414                  // Add the field to the column list string
 415                  $index_columns .= '[`"]?'.$column_data['fieldname'].'[`"]?'; // optionally in backticks
 416                  if( ! empty($column_data['subpart']) )
 417                  {
 418                      $index_columns .= '\s*\(\s*'.$column_data['subpart'].'\s*\)\s*';
 419                  }
 420              }
 421  
 422              // Sort index definitions with names to the beginning:
 423              /*
 424              usort( $indices, create_function( '$a, $b', '
 425                  if( preg_match( "~^\w+\s+[^(]~", $a["create_definition"] )
 426                  {
 427  
 428                  }' ) );
 429              */
 430  
 431  
 432              $used_auto_keys = array();
 433              foreach( $indices as $k => $index )
 434              {
 435                  $pattern = $index_pattern;
 436                  if( ! preg_match( '~^\w+\s+[^(]~', $index['create_definition'], $match ) )
 437                  { // no key name given, make the name part optional, if it's the default one:
 438                      // (Default key name seems to be the first column, eventually with "_\d+"-suffix)
 439                      $auto_key = db_delta_remove_quotes(strtoupper($index['col_names'][0]));
 440                      if( isset($used_auto_keys[$auto_key]) )
 441                      {
 442                          $used_auto_keys[$auto_key]++;
 443                          $auto_key .= '_'.$used_auto_keys[$auto_key];
 444                      }
 445                      $used_auto_keys[$auto_key] = 1;
 446  
 447                      if( $auto_key == $index_name )
 448                      { // the auto-generated keyname is the same as the one we have, so make it optional in the pattern:
 449                          $pattern .= '?';
 450                      }
 451                  }
 452                  // Add the column list to the index create string
 453                  $pattern .= '\s*\(\s*'.$index_columns.'\s*\)';
 454  
 455                  #pre_dump( '~'.$pattern.'~i', trim($index['create_definition']) );
 456                  if( preg_match( '~'.$pattern.'~i', trim($index['create_definition']) ) )
 457                  { // This index already exists: remove the index from our indices to create
 458                      unset($indices[$k]);
 459                      unset($obsolete_indices[$index_name]);
 460                      break;
 461                  }
 462              }
 463              if( isset($obsolete_indices[$index_name]) )
 464              {
 465                  #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";
 466              }
 467          }
 468  
 469          // Set $existing_primary_fields and $existing_key_fields
 470          foreach( $existing_indices as $l_key_name => $l_key_info )
 471          {
 472              $l_key_fields = array();
 473              foreach( $l_key_info['columns'] as $l_col )
 474              {
 475                  $l_key_fields[] = strtolower($l_col['fieldname']);
 476              }
 477              if( $l_key_name == 'PRIMARY' )
 478              { // Remember _existing_ PRIMARY KEYs
 479                  $existing_primary_fields = $l_key_fields;
 480              }
 481  
 482              $existing_key_fields = array_merge( $existing_key_fields, $l_key_fields );
 483          }
 484          $existing_key_fields = array_unique($existing_key_fields);
 485          #pre_dump( 'existing_primary_fields', $existing_primary_fields );
 486          #pre_dump( 'existing_key_fields', $existing_key_fields );
 487  
 488  
 489          // Fetch the table column structure from the database
 490          $tablefields = $DB->get_results( 'SHOW FULL COLUMNS FROM '.$table );
 491  
 492  
 493          // If "drop_column" is not excluded we have to check if all existing cols would get dropped,
 494          // to prevent "You can't delete all columns with ALTER TABLE; use DROP TABLE instead(Errno=1090)"
 495          if( ! in_array('drop_column', $exclude_types) )
 496          {
 497              $at_least_one_col_stays = false;
 498              foreach($tablefields as $tablefield)
 499              {
 500                  $fieldname_lowered = strtolower($tablefield->Field);
 501  
 502                  if( isset($wanted_fields[ $fieldname_lowered ]) )
 503                  {
 504                      $at_least_one_col_stays = true;
 505                  }
 506              }
 507  
 508              if( ! $at_least_one_col_stays )
 509              { // all columns get dropped: so we need to DROP TABLE and then use the original CREATE TABLE
 510                  array_unshift($items[$table_lowered], array(
 511                      'queries' => array('DROP TABLE '.$table),
 512                      'note' => 'Dropped <strong>'.$table.'.</strong>',
 513                      'type' => 'drop_column' ));
 514                  continue; // next $table
 515              }
 516          }
 517  
 518          // For every field in the existing table
 519          foreach($tablefields as $tablefield)
 520          {
 521              $fieldname_lowered = strtolower($tablefield->Field);
 522  
 523              if( ! isset($wanted_fields[ $fieldname_lowered ]) )
 524              { // This field exists in the table, but not in the creation queries
 525  
 526                  if( in_array('drop_column', $exclude_types) )
 527                  {
 528                      if( preg_match('~\bAUTO_INCREMENT\b~i', $tablefield->Extra) )
 529                      { // must be modified with a ADD COLUMN which drops a PK
 530                          $obsolete_autoincrement = $tablefield;
 531                      }
 532                  }
 533                  else
 534                  {
 535                      $items[$table_lowered][] = array(
 536                          'queries' => array('ALTER TABLE '.$table.' DROP COLUMN '.$tablefield->Field),
 537                          'note' => 'Dropped '.$table.'.<strong>'.$tablefield->Field.'</strong>',
 538                          'type' => 'drop_column' );
 539  
 540                      // Unset in key indices:
 541                      if( ($k = array_search($fieldname_lowered, $existing_key_fields)) !== false )
 542                      {
 543                          unset($existing_key_fields[$k]);
 544                      }
 545                      if( ($k = array_search($fieldname_lowered, $existing_primary_fields)) !== false )
 546                      {
 547                          unset($existing_primary_fields[$k]);
 548                      }
 549                  }
 550  
 551                  continue;
 552              }
 553  
 554              $column_definition = trim( $wanted_fields[$fieldname_lowered]['field'] );
 555  
 556              unset($type_matches); // have we detected the type as matching (for optional length param)
 557              $fieldtype = '';
 558  
 559              $pattern_field = '[`"]?'.$tablefield->Field.'[`"]?'; // optionally in backticks
 560  
 561              // Get the field type from the query
 562              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 ) )
 563              {
 564                  $fieldtype = strtoupper($match[1]);
 565  
 566                  if( $fieldtype == 'INTEGER' )
 567                  { // synonym
 568                      $fieldtype = 'INT';
 569                  }
 570                  elseif( $fieldtype == 'DEC' )
 571                  { // synonym
 572                      $fieldtype = 'DECIMAL';
 573                  }
 574  
 575                  if( isset($match[2]) )
 576                  { // append optional "length" param (trimmed)
 577                      $fieldtype .= preg_replace( '~\s+~', '', $match[2] );
 578                  }
 579                  if( ! empty($match[3]) )
 580                  { // "unsigned"
 581                      $fieldtype .= ' '.trim($match[3]);
 582                  }
 583                  if( ! empty($match[4]) )
 584                  { // "zerofill"
 585                      $fieldtype .= ' '.trim($match[4]);
 586                  }
 587  
 588                  $field_to_parse = $match[5];
 589  
 590                  // The length param is optional:
 591                  if( substr($fieldtype, 0, 7) == 'DECIMAL' )
 592                      $matches_pattern = '~^'.preg_quote($tablefield->Type, '~').'$~i';
 593                  else
 594                      $matches_pattern = '~^'.preg_replace( '~\((\d+)\)~', '(\(\d+\))?', $tablefield->Type ).'$~i';
 595                  $type_matches = preg_match( $matches_pattern, $fieldtype );
 596              }
 597              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 ) )
 598              {
 599                  $fieldtype = strtoupper($match[1]);
 600                  if( isset($match[2]) )
 601                  { // "binary"
 602                      $fieldtype .= trim($match[2]);
 603                  }
 604                  $field_to_parse = $match[3];
 605  
 606                  // There's a bug with a "NOT NULL" field reported as "NULL", work around it (http://bugs.mysql.com/bug.php?id=20910):
 607                  if( $fieldtype == 'TIMESTAMP' )
 608                  {
 609                      $ct_sql = $DB->get_var( 'SHOW CREATE TABLE '.$table, 1, 0 );
 610                      if( preg_match( '~^\s*`'.$tablefield->Field.'`\s+TIMESTAMP\s+(NOT )?NULL~im', $ct_sql, $match ) )
 611                      {
 612                          $tablefield->Null = empty($match[1]) ? 'YES' : 'NO';
 613                      }
 614                  }
 615              }
 616              elseif( preg_match( '~^'.$pattern_field.'\s+ (CHAR|VARCHAR|BINARY|VARBINARY) \s* \( ([\d\s]+) \) (\s+ (BINARY|ASCII|UNICODE) )? (.*)$~ix', $column_definition, $match ) )
 617              {
 618                  $len = trim($match[2]);
 619                  $fieldtype = strtoupper($match[1]).'('.$len.')';
 620  
 621                  if( ! empty($match[3]) )
 622                  { // "binary", "ascii", "unicode"
 623                      $fieldtype .= ' '.$match[3];
 624                  }
 625                  $field_to_parse = $match[5];
 626  
 627                  if( strtoupper($match[1]) == 'VARCHAR' )
 628                  {
 629                      if( $len < 4 )
 630                      { // VARCHAR shorter than 4 get converted to CHAR (but reported as VARCHAR in MySQL 5.0)
 631                          $type_matches = preg_match( '~^(VAR)?CHAR\('.$len.'\)'.( $match[3] ? ' '.$match[3] : '' ).'$~i', $tablefield->Type );
 632                      }
 633                  }
 634                  elseif( $has_variable_length_field && strtoupper($match[1]) == 'CHAR' )
 635                  { // CHARs in a row with variable length fields get silently converted to VARCHAR (but reported as CHAR in MySQL 5.0)
 636                      $type_matches = preg_match( '~^(VAR)?'.preg_quote( $fieldtype, '~' ).'$~i', $tablefield->Type );
 637                  }
 638              }
 639              elseif( preg_match( '~^'.$pattern_field.'\s+ (ENUM|SET) \s* \( (.*) \) (.*)$~ix', $column_definition, $match ) )
 640              {
 641                  $values = preg_split( '~\s*,\s*~', trim($match[2]), -1, PREG_SPLIT_NO_EMPTY ); // TODO: will fail for values containing ","..
 642                  $values = implode( ',', $values );
 643  
 644                  $fieldtype = strtoupper($match[1]).'('.$values.')';
 645                  $field_compare = strtolower($match[1]).'('.$values.')';
 646  
 647                  // compare case-sensitive
 648                  $type_matches = ( $field_compare == $tablefield->Type );
 649  
 650                  $field_to_parse = $match[3];
 651              }
 652              else
 653              {
 654                  if( $debug )
 655                  {
 656                      debug_die( 'db_delta(): Cannot find existing types field in column definition ('.$pattern_field.'/'.$column_definition.')' );
 657                  }
 658                  continue;
 659              }
 660  
 661  
 662              // DEFAULT
 663              $want_default = false;
 664              if( preg_match( '~^(.*?) \s DEFAULT \s+ (?: (?: (["\']) (.*?) \2 ) | (\w+) ) (\s .*)?$~ix', $field_to_parse, $match ) )
 665              {
 666                  if( isset($match[4]) && $match[4] !== '' )
 667                  {
 668                      $want_default = $match[4];
 669                      $want_default_set = $match[4];
 670                  }
 671                  else
 672                  {
 673                      $want_default = $match[3];
 674                      $want_default_set = $match[2].$match[3].$match[2];  // encapsulate in quotes again
 675                  }
 676  
 677                  $field_to_parse = $match[1].( isset($match[5]) ? $match[5] : '' );
 678              }
 679  
 680  
 681              // KEY
 682              $has_inline_primary_key = false;
 683              if( preg_match( '~^(.*) \b (?: (UNIQUE) (?:\s+ (?:INDEX|KEY))? | (?:PRIMARY \s+)? KEY ) \b (.*)$~ix', $field_to_parse, $match ) )
 684              { // fields got added to primary_key_fields and fields_with_keys before
 685                  // Check if this key is between quotation marks
 686                  if( ! has_open_quote( $match[1] ) )
 687                  { // is not between quotation marks, so it must be a key.
 688                      $field_to_parse = $match[1].$match[3];
 689                      if( empty($match[2]) )
 690                      {
 691                          $has_inline_primary_key = true; // we need to DROP the PK if this column definition does not match
 692                      }
 693                  }
 694              }
 695  
 696  
 697              // AUTO_INCREMENT (with special index handling: AUTO_INCREMENT fields need to be PRIMARY or UNIQUE)
 698              $is_auto_increment = false;
 699              if( preg_match( '~(.*?) \b AUTO_INCREMENT \b (.*)$~ix', $field_to_parse, $match ) )
 700              {
 701                  $is_auto_increment = true;
 702                  $field_to_parse = $match[1].$match[2];
 703  
 704                  if( ! preg_match( '~\bAUTO_INCREMENT\b~i', $tablefield->Extra ) )
 705                  { // not AUTO_INCREMENT yet
 706                      $type_matches = false;
 707                  }
 708  
 709                  if( ! in_array( $fieldname_lowered, $fields_with_keys ) )
 710                  { // no KEY defined (but required for AUTO_INCREMENT fields)
 711                      debug_die('No KEY/INDEX defined for AUTO_INCREMENT column!');
 712                  }
 713  
 714                  if( in_array( $fieldname_lowered, $existing_key_fields ) )
 715                  {
 716                      if( ! empty( $primary_key_fields ) )
 717                      {
 718                          $column_definition .= ', DROP PRIMARY KEY';
 719                          unset( $obsolete_indices['PRIMARY'] );
 720                      }
 721                  }
 722                  else
 723                  { // a key for this AUTO_INCREMENT field does not exist yet, we search it in $indices
 724                      foreach( $indices as $k_index => $l_index )
 725                      { // go through the indexes we want to have
 726  
 727                          if( array_search( $fieldname_lowered, $l_index['col_names'] ) === false )
 728                          { // this is not an index for our column
 729                              continue;
 730                          }
 731  
 732                          // this index definition affects us, we have to add it to our ALTER statement..
 733  
 734                          // See if we need to drop it, before adding it:
 735                          if( $l_index['is_PK'] )
 736                          { // Part of a PRIMARY key..
 737                              if( ! empty( $existing_primary_fields ) )
 738                              { // and a PRIMARY key exists already
 739                                  $column_definition .= ', DROP PRIMARY KEY';
 740                                  unset( $obsolete_indices['PRIMARY'] );
 741                              }
 742                              $existing_primary_fields = array(); // we expect no existing primary key anymore
 743                              $primary_key_fields = $l_index['col_names']; // this becomes our primary key
 744                          }
 745                          elseif( isset( $existing_indices[$l_index['name']] ) )
 746                          { // this index already exists, drop it:
 747                              $column_definition .= ', DROP INDEX '.$existing_indices[$l_index['name']]; // original case
 748                              unset( $existing_indices[$l_index['name']] ); // we expect that it does not exist anymore
 749                              if( ! in_array( $fieldname_lowered, $fields_with_keys ) )
 750                              { // add te field to the list of keys we want/expect to have:
 751                                  $fields_with_keys[] = $fieldname_lowered;
 752                              }
 753                          }
 754  
 755                          // Merge the INDEX creation into our ALTER query:
 756                          $column_definition .= ', ADD '.$l_index['create_definition'];
 757                          unset( $indices[$k_index] );
 758                      }
 759                  }
 760              }
 761  
 762  
 763              // "[NOT] NULL" (requires $primary_key_fields to be finalized)
 764              if( preg_match( '~(.*?) \b (NOT\s+)? NULL \b (.*)$~ix', $field_to_parse, $match ) )
 765              { // if "NOT" not matched it's NULL
 766                  $want_null = empty($match[2]);
 767                  $field_to_parse = $match[1].$match[3];
 768              }
 769              else
 770              { // not specified: "NULL" is default
 771                  $want_null = true;
 772              }
 773  
 774              if( in_array($fieldname_lowered, $primary_key_fields) || $is_auto_increment )
 775              { // If part of PRIMARY KEY or AUTO_INCREMENT field "NULL" is implicit
 776                  $change_null = false; // implicit NULL
 777                  $want_null = 'IMPLICIT';
 778              }
 779              elseif( in_array($fieldname_lowered, $existing_primary_fields) && ! in_array($fieldname_lowered, $primary_key_fields) )
 780              { // the field was in PRIMARY KEY, but is no longer. It should get altered only if we want "NOT NULL"
 781                  $change_null = ( ! $want_null && $tablefield->Null == 'YES' );
 782                  #pre_dump( $want_null );
 783                  #$want_null = 'IMPLICIT2';
 784                  #pre_dump( $primary_key_fields );
 785              }
 786              else
 787              {
 788                  if( $tablefield->Null == 'YES' )
 789                  {
 790                      $change_null = ! $want_null;
 791                  }
 792                  else
 793                  { // I've seen '' and 'NO' for no..
 794                      $change_null = $want_null;
 795                  }
 796              }
 797  
 798  
 799              // COMMENT ( check if there is difference in field comment )
 800              if( preg_match( '~^(.*?) \s COMMENT \s+ (?: (?: (["\']) (.*?) \2 ) ) (\s .*)?$~ix', $field_to_parse, $match ) )
 801              {
 802                  if( isset($match[4]) && $match[4] !== '' )
 803                  {
 804                      $want_comment = $match[4];
 805                  }
 806                  else
 807                  {
 808                      $want_comment = $match[3];
 809                  }
 810  
 811                  $want_comment = stripslashes( $want_comment );
 812                  $type_matches = $tablefield->Comment == $want_comment;
 813  
 814                  $field_to_parse = $match[1].( isset($match[5]) ? $match[5] : '' );
 815              }
 816  
 817  
 818              // TODO: "COLLATE" and other attribute handling should happen here, based on $field_to_parse
 819  
 820  
 821              if( ! isset($type_matches) )
 822              { // not tried to match before
 823                  $type_matches = ( strtoupper($tablefield->Type) == $fieldtype );
 824              }
 825  
 826              #pre_dump( 'change_null ($change_null, $tablefield, $want_null)', $change_null, $tablefield, $want_null );
 827              #pre_dump( 'type_matches', $type_matches, strtolower($tablefield->Type), $fieldtype );
 828  
 829  
 830              // See what DEFAULT we would get or want
 831              $update_default = NULL;
 832              $update_default_set = NULL;
 833  
 834              if( $want_default !== false )
 835              {
 836                  $update_default = $want_default;
 837                  $update_default_set = $want_default_set;
 838              }
 839              else
 840              { // implicit default, see http://dev.mysql.com/doc/refman/4.1/en/data-type-defaults.html
 841                  if( preg_match( '~^(TINYINT|SMALLINT|MEDIUMINT|INTEGER|INT|BIGINT|REAL|DOUBLE|FLOAT|DECIMAL|DEC|NUMERIC)~', $fieldtype ) )
 842                  { // numeric
 843                      $update_default = '0';
 844                      $update_default_set = '0';
 845                  }
 846                  elseif( $fieldtype == 'TIMESTAMP' )
 847                  { // TODO: the default should be current date and time for the first field - but AFAICS we won't have NULL fields anyway
 848                  }
 849                  elseif( preg_match( '~^(DATETIME|DATE|TIME|YEAR)$~', $fieldtype ) )
 850                  {
 851                      $update_default = '0'; // short form for various special "zero" values
 852                      $update_default_set = '0';
 853                  }
 854                  elseif( substr($fieldtype, 0, 4) == 'ENUM' )
 855                  {
 856                      preg_match( '~["\']?.*?["\']?\s*[,)]~x', substr($fieldtype,5), $match );
 857                      $update_default_set = trim( $match[0], "\n\r\t\0\x0bB ()," ); // strip default whitespace, braces & comma
 858                      // first value (until "," or end) of $fieldtype_param:
 859                      $update_default = preg_replace( '~^(["\'])(.*)\1$~', '$2', $update_default_set ); // without quotes
 860                  }
 861                  else
 862                  {
 863                      $update_default_set = "''"; // empty string for string types
 864                      $update_default = '';
 865                  }
 866              }
 867  
 868  
 869              // Is actual field type different from the field type in query?
 870              if( ! $type_matches || $change_null )
 871              { // Change the whole column to $column_definition:
 872                  /*
 873                  echo '<h2>No_Match</h2>';
 874                  pre_dump( $type_matches, $change_null, $want_null );
 875                  pre_dump( $tablefield, $column_definition );
 876                  pre_dump( 'flds', $flds );
 877                  pre_dump( 'wanted_fields', $wanted_fields );
 878                  pre_dump( strtolower($tablefield->Type), $fieldtype, $column_definition );
 879                  */
 880  
 881                  $queries = array( 'ALTER TABLE '.$table );
 882  
 883                  // Handle inline PRIMARY KEY definition:
 884                  if( $has_inline_primary_key && ! empty($existing_primary_fields) ) // there's a PK that needs to get removed
 885                  { // the column is part of the PRIMARY KEY, which needs to get dropped before (we already handle that for AUTO_INCREMENT fields)
 886                      $queries[0] .= ' DROP PRIMARY KEY,';
 887                      $existing_primary_fields = array(); // we expect no existing primary key anymore
 888                      unset( $obsolete_indices['PRIMARY'] );
 889                  }
 890  
 891                  $queries[0] .= ' CHANGE COLUMN '.$tablefield->Field.' '.$column_definition;
 892  
 893                  // Handle changes from "NULL" to "NOT NULL"
 894                  if( $change_null && ! $want_null && isset($update_default_set) )
 895                  { // Prepend query to update NULL fields to default
 896                      array_unshift( $queries, 'UPDATE '.$table.' SET '.$tablefield->Field.' = '.$update_default_set.' WHERE '.$tablefield->Field.' IS NULL' );
 897  
 898                      if( substr( $tablefield->Type, 0, 5 ) == 'enum(' )
 899                      {
 900                          $existing_enum_field_values = preg_split( '~\s*,\s*~', substr( $tablefield->Type, 5, -1 ), -1, PREG_SPLIT_NO_EMPTY );
 901  
 902                          foreach( $existing_enum_field_values as $k => $v )
 903                          {
 904                              $existing_enum_field_values[$k] = preg_replace( '~^(["\'])(.*)\1$~', '$2', $v ); // strip quotes
 905                          }
 906  
 907                          if( ! in_array( $update_default, $existing_enum_field_values ) )
 908                          { // we cannot update straight to the new default, because it does not exist yet!
 909  
 910                              // Update the column first, without the NULL change
 911                              array_unshift( $queries, 'ALTER TABLE '.$table.' CHANGE COLUMN '.$tablefield->Field.' '.preg_replace( '~\sNOT\s+NULL~i', '', $column_definition ) );
 912                          }
 913                      }
 914                  }
 915  
 916                  // Add a query to change the column type
 917                  $items[$table_lowered][] = array(
 918                      'queries' => $queries,
 919                      'note' => 'Changed type of '.$table.'.<strong>'.$tablefield->Field.'</strong> from '.$tablefield->Type.' to '.$column_definition,
 920                      'type' => 'change_column' );
 921              }
 922              else
 923              { // perhaps alter or drop DEFAULT:
 924                  if( $want_default !== false )
 925                  { // DEFAULT given
 926                      $existing_default = $tablefield->Default === NULL ? 'NULL' : $tablefield->Default;
 927  
 928                      if( $existing_default != $want_default ) // DEFAULT is case-sensitive
 929                      { // Add a query to change the column's default value
 930                          $items[$table_lowered][] = array(
 931                              'queries' => array('ALTER TABLE '.$table.' ALTER COLUMN '.$tablefield->Field.' SET DEFAULT '.$want_default_set),
 932                              'note' => "Changed default value of {$table}.<strong>{$tablefield->Field}</strong> from $existing_default to $want_default_set",
 933                              'type' => 'change_default' );
 934                      }
 935                  }
 936                  elseif( ! empty($tablefield->Default) && $tablefield->Default != $update_default )
 937                  { // No DEFAULT given, but it exists one, so drop it (IF not a TIMESTAMP or DATETIME field)
 938                      if( $tablefield->Type != 'timestamp' && $tablefield->Type != 'datetime' )
 939                      {
 940                          $items[$table_lowered][] = array(
 941                              'queries' => array('ALTER TABLE '.$table.' ALTER COLUMN '.$tablefield->Field.' DROP DEFAULT'),
 942                              'note' => "Dropped default value of {$table}.<strong>{$tablefield->Field}</strong>",
 943                              'type' => 'change_default' ); // might be also 'drop_default'
 944                      }
 945                  }
 946              }
 947  
 948              // Remove the field from the array (so it's not added)
 949              unset($wanted_fields[$fieldname_lowered]);
 950          }
 951  
 952  
 953          foreach($wanted_fields as $fieldname_lowered => $fielddef)
 954          { // For every remaining field specified for the table
 955              $column_definition = $fielddef['field'].' '.$fielddef['where'];
 956  
 957              $is_auto_increment = false;
 958              // AUTO_INCREMENT (with special index handling: AUTO_INCREMENT fields need to be PRIMARY or UNIQUE)
 959              if( preg_match( '~(.*?) \b AUTO_INCREMENT \b (.*)$~ix', $fielddef['field'], $match ) )
 960              {
 961                  if( ! in_array( $fieldname_lowered, $fields_with_keys ) )
 962                  { // no KEY defined (but required for AUTO_INCREMENT fields)
 963                      debug_die('No KEY/INDEX defined for AUTO_INCREMENT column!');
 964                  }
 965                  $is_auto_increment = true;
 966  
 967  
 968                  foreach( $indices as $k_index => $l_index )
 969                  { // go through the indexes we want to have
 970  
 971                      if( array_search( $fieldname_lowered, $l_index['col_names'] ) === false )
 972                      { // this is not an index for our column
 973                          continue;
 974                      }
 975  
 976                      // this index definition affects us, we have to add it to our ALTER statement..
 977  
 978                      // See if we need to drop it, before adding it:
 979                      if( $l_index['is_PK'] )
 980                      { // Part of a PRIMARY key..
 981                          if( ! empty( $existing_primary_fields ) )
 982                          { // and a PRIMARY key exists already
 983                              $column_definition .= ', DROP PRIMARY KEY';
 984                              unset( $obsolete_indices['PRIMARY'] );
 985                          }
 986                          $existing_primary_fields = array(); // we expect no existing primary key anymore
 987                          $primary_key_fields = $l_index['col_names']; // this becomes our primary key
 988                      }
 989                      elseif( isset( $existing_indices[$l_index['name']] ) )
 990                      { // this index already exists, drop it:
 991                          $column_definition .= ', DROP INDEX '.$existing_indices[$l_index['name']]; // original case
 992                          unset( $existing_indices[$l_index['name']] ); // we expect that it does not exist anymore
 993                          if( ! in_array( $fieldname_lowered, $fields_with_keys ) )
 994                          { // add te field to the list of keys we want/expect to have:
 995                              $fields_with_keys[] = $fieldname_lowered;
 996                          }
 997                      }
 998  
 999                      // Merge the INDEX creation into our ALTER query:
1000                      $column_definition .= ', ADD '.$l_index['create_definition'];
1001                      unset( $indices[$k_index] );
1002                  }
1003              }
1004  
1005              // Push a query line into $items that adds the field to that table
1006              $query = 'ALTER TABLE '.$table.' ADD COLUMN '.$column_definition;
1007  
1008              // Handle inline PRIMARY KEY definition:
1009              if( preg_match( '~^(.*) \b (?: (UNIQUE) (?:\s+ (?:INDEX|KEY))? | (?:PRIMARY \s+)? KEY ) \b (.*)$~ix', $column_definition, $match ) // "has_inline_primary_key"
1010                      && count($existing_primary_fields)
1011                      && ! in_array($fieldname_lowered, $existing_primary_fields) )
1012              { // the column is part of the PRIMARY KEY, which needs to get dropped before (we already handle that for AUTO_INCREMENT fields)
1013                  $query .= ', DROP PRIMARY KEY';
1014                  $existing_primary_fields = array(); // we expect no existing primary key anymore
1015                  unset( $obsolete_indices['PRIMARY'] );
1016  
1017                  if( isset($obsolete_autoincrement) )
1018                  {
1019                      $query .= ', MODIFY COLUMN '.$obsolete_autoincrement->Field.' '.$obsolete_autoincrement->Type.' '.( $obsolete_autoincrement->Field == 'YES' ? 'NULL' : 'NOT NULL' );
1020                  }
1021              }
1022  
1023              $items[$table_lowered][] = array(
1024                  'queries' => array($query),
1025                  'note' => 'Added column '.$table.'.<strong>'.$fielddef['field'].'</strong>',
1026                  'type' => 'add_column' );
1027          }
1028  
1029  
1030          // Remove the original table creation query from processing
1031          array_shift( $items[$table_lowered] );
1032  
1033          // Add foreign key constraints
1034          $result = db_delta_foreign_keys( $foreign_key_fields, $table, false );
1035          foreach( $result as $foreign_key_update )
1036          { // loop through foreign key differences in this table
1037              if( $foreign_key_update['type'] == 'alter_engine' )
1038              { // 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
1039                  $skip = false;
1040                  foreach( $items[$table_lowered] as $itemlist )
1041                  {
1042                      if( ( $itemlist[ 'type' ] == 'alter_engine' ) && ( $itemlist['queries'][0] == $foreign_key_update['queries'][0] ) )
1043                      { // the same command was already added, don't add again
1044                          $skip = true;
1045                          break;
1046                      }
1047                  }
1048                  if( $skip )
1049                  { // skip is set, don't add the alter engine command again
1050                      continue;
1051                  }
1052              }
1053              // add FK updates
1054              $items[$table_lowered][] = $foreign_key_update;
1055          }
1056  
1057          // Add the remaining indices (which are not "inline" with a column definition and therefor already handled):
1058          $add_index_queries = array();
1059          foreach( $indices as $k => $index )
1060          {
1061              if( empty($index['create_definition']) )
1062              { // skip "inline"
1063                  continue;
1064              }
1065              $query = 'ALTER TABLE '.$table;
1066              if( $index['is_PK'] && $existing_primary_fields )
1067              {
1068                  $query .= ' DROP PRIMARY KEY,';
1069                  unset( $obsolete_indices['PRIMARY'] );
1070              }
1071  
1072              // Create a query that adds the index to the table
1073              $query = array(
1074                  'queries' => array($query.' ADD '.$index['create_definition']),
1075                  'note' => 'Added index <strong>'.$index['create_definition'].'</strong>',
1076                  'type' => 'add_index',
1077                  'name' => $index['name'] );
1078  
1079              // Check if the index creation has to get appended after any DROPs (required for indices with the same name)
1080              $append_after_drops = false;
1081              foreach( $obsolete_indices as $obsolete_index )
1082              {
1083                  if( strtolower($obsolete_index['name']) == strtolower($index['name']) )
1084                  {
1085                      $append_after_drops = true;
1086                      break;
1087                  }
1088              }
1089              if( $append_after_drops )
1090              { // do this after any DROPs (i.e. KEY name changes)
1091                  $add_index_queries[] = $query;
1092              }
1093              else
1094              { // this needs to get done before any other DROPs
1095                  // to prevent e.g. "Incorrect table definition; there can be only one auto column and it must be defined as a key(Errno=1075)"
1096                  $items[$table_lowered][] = $query;
1097              }
1098          }
1099  
1100          // Now add queries to drop any (maybe changed!) indices
1101          foreach( $obsolete_indices as $index_info )
1102          {
1103              // Push a query line into $items that drops the index from the table
1104              $items[$table_lowered][] = array(
1105                  'queries' => array("ALTER TABLE {$table} DROP ".( $index_info['name'] == 'PRIMARY' ? 'PRIMARY KEY' : 'INDEX '.$index_info['name'] )),
1106                  'note' => 'Dropped index <strong>'.$index_info['name'].'</strong>',
1107                  'type' => 'drop_index',
1108                  'name' => $index_info['name'] );
1109          }
1110  
1111          // Add queries to (re)create (maybe changed indices) to the end
1112          $items[$table_lowered] = array_merge($items[$table_lowered], $add_index_queries);
1113      }
1114  
1115  
1116      // Filter types we want to exclude:
1117      if( ! empty($exclude_types) )
1118      {
1119          foreach( $items as $table => $itemlist )
1120          {
1121              $removed_one = false;
1122              foreach( $itemlist as $k => $item )
1123              {
1124                  if( in_array($item['type'], $exclude_types) )
1125                  { // this type of update should be excluded
1126                      if( $item['type'] == 'drop_index' )
1127                      { // drop index command should not be excluded in case when we would like to update an index!
1128                          $skip = false;
1129                          foreach( $itemlist as $other_item )
1130                          { // check if there are an add_index command for the same table with the same index name
1131                              if( ( $other_item['type'] == 'add_index' ) && ( strcasecmp( $item['name'], $other_item['name'] ) === 0 ) )
1132                              { // 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
1133                                  $skip = true;
1134                                  break;
1135                              }
1136                          }
1137                          if( $skip )
1138                          { // skip excluding this item
1139                              continue;
1140                          }
1141                      }
1142                      unset( $items[$table][$k] );
1143                      $removed_one = true;
1144                  }
1145              }
1146              if( $removed_one )
1147              { // Re-order (0, 1, 2, ..)
1148                  $items[$table] = array_values($items[$table]);
1149              }
1150          }
1151      }
1152  
1153      // Unset empty table indices:
1154      foreach( $items as $table => $itemlist )
1155      {
1156          if( empty($itemlist) )
1157          {
1158              unset( $items[$table] );
1159              continue;
1160          }
1161  
1162          // 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!
1163          $alter_engine_index = NULL;
1164          for( $i = 0; $i < count( $itemlist ); $i++ )
1165          {
1166              if( ( $itemlist[$i]['type'] == 'alter_engine' ) && ( $alter_engine_index == NULL ) )
1167              { // save alter engine query index
1168                  $alter_engine_index = $i;
1169              }
1170              elseif( ( $itemlist[$i]['type'] == 'drop_foreign_key' ) && ( $alter_engine_index !== NULL ) && ( $alter_engine_index < $i ) )
1171              { // 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
1172                  $switch_item = $itemlist[$alter_engine_index];
1173                  $items[$table][$alter_engine_index] = $itemlist[$i];
1174                  $items[$table][$i] = $switch_item;
1175                  // save new alter engine index and the alter engine command in case of we have to drop multiple foreign keys
1176                  $alter_engine_index = $i;
1177                  $itemlist[$i] = $switch_item;
1178              }
1179          }
1180      }
1181  
1182      if( $execute )
1183      {
1184          foreach( $items as $table => $itemlist )
1185          {
1186              foreach( $itemlist as $item )
1187              {
1188                  foreach( $item['queries'] as $query )
1189                  {
1190                      #pre_dump( $query );
1191                      $DB->query( $query );
1192                  }
1193              }
1194          }
1195      }
1196  
1197      return $items;
1198  }
1199  
1200  
1201  /**
1202   * Remove quotes/backticks around a field/table name.
1203   *
1204   * @param string Field name
1205   * @param string List of quote chars to remove
1206   * @return string
1207   */
1208  function db_delta_remove_quotes($fieldname, $quotes = '`"')
1209  {
1210      $quotes_len = strlen( $quotes );
1211  
1212      for( $i = 0; $i < $quotes_len; $i++ )
1213      {
1214          $char = $quotes[$i];
1215          if( substr($fieldname, 0, 1) == $char && substr($fieldname, -1) == $char )
1216          { // found quotes:
1217              $fieldname = substr($fieldname, 1, -1);
1218              return $fieldname;
1219          }
1220      }
1221      return $fieldname;
1222  }
1223  
1224  
1225  /**
1226   * Get the delta queries between existing foreign key values and new foreign key values in the given table
1227   *
1228   * @param array foreign key lines from the install script Create Table commands
1229   * @param string the processed table name
1230   * @param boolean set to false to not process the required queries without asking the user consent
1231   * @param string leave this to NUL for delta, set to 'add' to add a new foreign key and set to 'drop' to drop a single foreign key.
1232   * @return array the delta queries if there are any or empty array if the required db queries have been processed or if there are no foreign key differences
1233   */
1234  function db_delta_foreign_keys( $foreign_key_fields, $table, $silent = true, $action = NULL )
1235  {
1236      global $DB;
1237  
1238      $result = array();
1239  
1240      // get create table sql from db
1241      $ct_sql = $DB->get_var( 'SHOW CREATE TABLE '.$table, 1, 0 );
1242  
1243      // Check related tables engine because the foreign key table and the reference table both must have InnoDB engine
1244      if( !empty( $foreign_key_fields ) && ( $action != 'drop' ) )
1245      { // we have foreign key contraints, and we don't want to drop that ( In case of drop FK the table engine doesn't matter )
1246          // which tables engine must be changed
1247          $modify_table_eninges = array( $table => 'InnoDB' );
1248          foreach( $foreign_key_fields as $foreign_key )
1249          { // check reference tables engine
1250              $modify_table_eninges[ $foreign_key['reference_table'] ] = 'InnoDB';
1251          }
1252          $engine_queries = db_delta_table_engines( $modify_table_eninges, $silent );
1253          if( !$silent && !empty( $engine_queries ) )
1254          {
1255              $result = array_merge( $engine_queries, $result );
1256          }
1257      }
1258  
1259      // get foreign key constraints from db
1260      $existing_foreign_key_fields = array();
1261      $db_fieldlines = get_fieldlines_from_query( $ct_sql );
1262      foreach( $db_fieldlines as $fieldname => $fieldline )
1263      { // loop through all field lines and get those lines where are foreign key definitions
1264          $fieldline = str_replace( array( '`', '"' ), '', $fieldline );
1265          if( preg_match( '~^(?:(CONSTRAINT)* \s* ([^ ]*)) \s* (FOREIGN\s+KEY) \s* \((.*)\) \s* (REFERENCES) \s* ([^( ]*) \s* \((.*)\) \s* (.*)$~ixs', $fieldline, $match ) )
1266          { // add existing foreign key fields, but set drop param to true only if we don't want to add/drop a single foreign key!
1267              $existing_foreign_key_fields[] = array( 'fk_symbol' => $match[2], 'fk_fields' => $match[4], 'reference_table' => $match[6], 'reference_columns' => $match[7], 'fk_definition' => $match[8], 'drop' => ( $action == NULL ) );
1268          }
1269      }
1270  
1271      foreach( $existing_foreign_key_fields as &$existing_foreign_key )
1272      { // loop through existing foreign key definitions
1273          foreach( $foreign_key_fields as &$foreign_key )
1274          { // loop through the install script foreign key definitions
1275              if( ( $action == NULL ) && ( !$existing_foreign_key['drop'] ) && ( !$foreign_key['create'] ) )
1276              { // if we already know that an old key should not be removed, and the recent key already exists skip this check
1277                  continue;
1278              }
1279              // check if the two foreign key constraint is the same or not
1280              $match_found = ( $existing_foreign_key['fk_fields'] == $foreign_key['fk_fields'] )
1281                  && ( $existing_foreign_key['reference_table'] == $foreign_key['reference_table'] )
1282                  && ( $existing_foreign_key['reference_columns'] == $foreign_key['reference_columns'] );
1283              $exact_match_found = ( $match_found && ( $existing_foreign_key['fk_definition'] == $foreign_key['fk_definition'] ) );
1284              if( ( !empty( $action ) ) && $match_found )
1285              { // action is not empty it means that we would like to add or drop a single foreign key
1286                  if( $action == 'drop' )
1287                  { // set existing foreign key to be droped
1288                      $existing_foreign_key['drop'] = true;
1289                      break;
1290                  }
1291                  elseif( $action = 'add' )
1292                  { // add a new foreign key
1293                      if( $exact_match_found )
1294                      { // Exact match found so the FK already exists with the same definition
1295                          return;
1296                      }
1297                      // foreign key exists but not with the given definition so we have to drop the old FK
1298                      $existing_foreign_key['drop'] = true;
1299                      break;
1300                  }
1301              }
1302              // if exact match found betwen existing and recent foreign keys then we should not drop the old one
1303              $existing_foreign_key['drop'] = $existing_foreign_key['drop'] && ( !$exact_match_found );
1304              // if recent foreign keys already exists then we doesn't have to create a new one
1305              $foreign_key['create'] = $foreign_key['create'] && ( !$exact_match_found );
1306          }
1307          if( ( $action !== NULL ) && ( $existing_foreign_key['drop'] ) )
1308          { // in case of add/drop a single foreign key, if we have found a match, then we don't have to look forward
1309              break;
1310          }
1311      }
1312      unset( $existing_foreign_key );
1313      unset( $foreign_key );
1314  
1315      foreach( $existing_foreign_key_fields as $existing_foreign_key )
1316      { // loop through existing foreign keys
1317          if( $existing_foreign_key['drop'] )
1318          { // this foreign key constraint should be removed, create the query
1319              $query = 'ALTER TABLE '.$table.' DROP FOREIGN KEY '.$existing_foreign_key['fk_symbol'];
1320              if( $silent )
1321              { // execute query in silent mode
1322                  $DB->query( $query );
1323              }
1324              else
1325              { // set query definition
1326                  $result[] = array(
1327                      'queries' => array( $query ),
1328                      'note' => 'Drop <strong>'.$existing_foreign_key['fk_symbol'].'</strong> foreign key constraint from <strong>'.$table.'</strong> table.',
1329                      'type' => 'drop_foreign_key' );
1330              }
1331          }
1332      }
1333      foreach( $foreign_key_fields as $foreign_key )
1334      { // loop through in up to date foreign keys
1335          if( $foreign_key['create'] )
1336          { // // this foreign key constraint is new, it must be created
1337              // Create delete query for orphan entries
1338              $delete_query = 'DELETE FROM '.$table.
1339                                  ' WHERE '.$foreign_key['fk_fields'].' NOT IN (
1340                                      SELECT DISTINCT('.$foreign_key['reference_columns'].') FROM '.$foreign_key['reference_table'].' )';
1341              $query = 'ALTER TABLE '.$table.' ADD FOREIGN KEY ('.$foreign_key['fk_fields'].') REFERENCES '.$foreign_key['reference_table'].' ('.$foreign_key['reference_columns'].') '.$foreign_key['fk_definition'];
1342              if( $silent )
1343              { // execute queries in silent mode
1344                  if( $DB->query( $delete_query ) !== false )
1345                  { // orphan child entries have been deleted, create foreign key
1346                      $DB->query( $query );
1347                  }
1348              }
1349              else
1350              { // set query definition
1351                  $result[] = array(
1352                      'queries' => array( $delete_query ),
1353                      'note' => 'Delete orphan <strong>'.$table.'</strong> entries.',
1354                      'type' => 'delete_orphan_entries' );
1355                  $result[] = array(
1356                      'queries' => array( $query ),
1357                      'note' => 'Add foreign key constraint on <strong>'.$table.'('.$foreign_key['fk_fields'].')'.'</strong> in reference to <strong>'.$foreign_key['reference_table'].'('.$foreign_key['reference_columns'].')'.'</strong>',
1358                      'type' => 'add_foreign_key' );
1359              }
1360          }
1361      }
1362  
1363      return $result;
1364  }
1365  
1366  
1367  /**
1368   * Get/Process the delta queries between existing and required table engines. This function is used for Foreign Key update.
1369   *
1370   * @param array tableName => expectedEngine values
1371   * @param booelan set to true to process the required DB queries, false to return the requested queries
1372   * @result array|NULL the delta queries if there are any or empty array if the required db queries have been processed or if there are no difference between tables engine
1373   */
1374  function db_delta_table_engines( $tables, $silent )
1375  {
1376      global $DB;
1377  
1378      if( empty( $tables ) )
1379      { // no tables to check
1380          return NULL;
1381      }
1382  
1383      $modify_engine_queries = array();
1384      foreach( $tables as $table => $engine )
1385      {
1386          // get table engine from db
1387          $current_engine = $DB->get_row( 'SHOW TABLE STATUS LIKE '.$DB->quote($table) );
1388          $current_engine = $current_engine->Engine;
1389          if( strtolower( $current_engine ) != strtolower( $engine ) )
1390          { // table engine is not the expected one
1391              $modify_engine_queries[] = array(
1392                      'queries' => array( 'ALTER TABLE '.$table.' ENGINE='.$engine ),
1393                      'note' => 'Alter engine of <strong>'.$table.'.</strong> to <strong>innodb</strong>',
1394                      'type' => 'alter_engine'
1395                  );
1396          }
1397      }
1398  
1399      if( !$silent )
1400      { // return queries
1401          return $modify_engine_queries;
1402      }
1403  
1404      // Update engines silently
1405      foreach( $modify_engine_queries as $query )
1406      {
1407          $DB->query( $query['queries'][0] );
1408      }
1409      return NULL;
1410  }
1411  
1412  
1413  /**
1414   * Alter the DB schema to match the current expected one ({@link $schema_queries}).
1415   *
1416   * @todo if used by install only, then put it into the install folder!!!
1417   *
1418   * @param boolean Display what we've done?
1419   */
1420  function install_make_db_schema_current( $display = true )
1421  {
1422      global $schema_queries, $DB, $debug;
1423  
1424      // Go through all tables:
1425      foreach( $schema_queries as $table => $query_info )
1426      {
1427          // Look for differences between terrain & map:
1428          $items_need_update = db_delta( $query_info[1], array('drop_column', 'drop_index'), false );
1429  
1430          if( empty($items_need_update) )
1431          {
1432              continue;
1433          }
1434  
1435          if( ! $display )
1436          { // just execute queries
1437              foreach( $items_need_update as $table => $itemlist )
1438              {
1439                  foreach( $itemlist as $item )
1440                  {
1441                      foreach( $item['queries'] as $query )
1442                      {
1443                          $DB->query( $query );
1444                      }
1445                  }
1446              }
1447          }
1448          else
1449          { // execute & output
1450              foreach( $items_need_update as $table => $itemlist )
1451              {
1452                  if( count($itemlist) == 1 && $itemlist[0]['type'] == 'create_table' )
1453                  {
1454                      echo $itemlist[0]['note']."<br />\n";
1455                      evo_flush();
1456                      foreach( $itemlist[0]['queries'] as $query )
1457                      { // should be just one, but just in case
1458                          if( $debug >= 2 )
1459                          {
1460                              pre_dump( $query );
1461                          }
1462                          $DB->query( $query );
1463                      }
1464                  }
1465                  else
1466                  {
1467                      echo 'Altering table &laquo;'.$table.'&raquo;...';
1468                      echo '<ul>';
1469                      foreach( $itemlist as $item )
1470                      {
1471                          echo '<li>'.$item['note'];
1472                          if( $debug )
1473                          {
1474                              pre_dump( $item['queries'] );
1475                          }
1476                          echo '</li>';
1477                          foreach( $item['queries'] as $query )
1478                          {
1479                              $DB->query( $query );
1480                          }
1481                      }
1482                      echo "</ul>";
1483                  }
1484              }
1485          }
1486      }
1487  }
1488  
1489  
1490  /**
1491   * Check if needle is between quotation mark in the subject
1492   *
1493   * @param string subject
1494   * @param string needle
1495   * @return boolean true if is between quotation mark, false otherwise
1496   */
1497  function is_in_quote( $subject, $needle )
1498  {
1499      $length = strpos( $subject, $needle );
1500      if( $length === false )
1501      { // needle is not in the subject
1502          return false;
1503      }
1504  
1505      // We need a to create a substring because substr_count() $offset and $length params were added in PHP 5.1.0
1506      $subject_before_needle = substr( $subject, 0, $length );
1507      // Search quotes in the first part of the original subject, before the $needle position
1508      $quote_count = substr_count( $subject_before_needle, "'" );
1509      if( $quote_count > 0 )
1510      {
1511          $quote_count = $quote_count - substr_count( $subject_before_needle, "\'" );
1512      }
1513      return ( $quote_count % 2 );
1514  }
1515  
1516  
1517  /**
1518   * Check if this subject has not closed ' character
1519   *
1520   * @param $subject
1521   * @return boolean true if has not closed ' character, false otherwise
1522   */
1523  function has_open_quote( $subject )
1524  {
1525      $quote_count = substr_count( $subject, "'" );
1526      if( $quote_count > 0 )
1527      {
1528          $quote_count = $quote_count - substr_count( $subject, "\'" );
1529      }
1530      return ( $quote_count % 2 );
1531  }
1532  
1533  
1534  /**
1535   * Remove any comments from the SQL query
1536   *
1537   * @param string query
1538   * @return string the same query without comments
1539   */
1540  function remove_comments_from_query( $query )
1541  {
1542      $n = strlen( $query );
1543      $in_string = false;
1544      for( $i = 0; $i < $n; $i++ )
1545      {
1546          if( $query[$i] == '\\' )
1547          { // backslash/escape; skip
1548              continue;
1549          }
1550          if( $query[$i] == '"' || $query[$i] == "'" )
1551          {
1552              if( ! $in_string )
1553              { // string begins:
1554                  $in_string = $query[$i];
1555              }
1556              elseif( $query[$i] === $in_string )
1557              {
1558                  $in_string = false;
1559              }
1560          }
1561          elseif( $in_string === false )
1562          { // not in string, check for comment start:
1563              if( $query[$i] == '#' || substr($query, $i, 3) == '-- ' )
1564              { // comment start
1565                  // search for newline
1566                  for( $j = $i+1; $j < $n; $j++ )
1567                  {
1568                      if( $query[$j] == "\n" || $query[$j] == "\r" )
1569                      {
1570                          break;
1571                      }
1572                  }
1573                  // remove comment
1574                  $query = substr($query, 0, $i).substr($query, $j);
1575                  $n = strlen($query);
1576                  continue;
1577              }
1578          }
1579      }
1580      // return query without comments
1581      return $query;
1582  }
1583  
1584  
1585  /**
1586   * Get all lines from a create table query
1587   *
1588   * @param string the query
1589   * @return array field lines
1590   */
1591  function get_fieldlines_from_query( $query )
1592  {
1593      // Get all of the field names in the query from between the parens
1594      preg_match( '|\((.*)\).*$|s', $query, $match ); // we have only one query here
1595      $qrylines = trim($match[1]);
1596  
1597      $flds = array();
1598      $in_parens = 0;
1599      $in_quote = false;
1600      $buffer = '';
1601      for( $i = 0; $i < strlen($qrylines); $i++ )
1602      {
1603          $c = $qrylines[$i];
1604  
1605          if( ( $c == ',' ) && ( ! $in_parens ) && ( ! $in_quote ) )
1606          { // split here:
1607              $line = trim($buffer);
1608              preg_match( '|^([^\s(]+)|', $line, $linematch );
1609              $fieldname = db_delta_remove_quotes($linematch[1]);
1610              if( isset( $flds[$fieldname] ) )
1611              {
1612                  $fieldname .= '_'.$i;
1613              }
1614              $flds[$fieldname] = $line;
1615              $buffer = '';
1616              continue;
1617          }
1618  
1619          if( $c == '(' )
1620          {
1621              $in_parens++;
1622          }
1623          elseif( $c == ')' )
1624          {
1625              $in_parens--;
1626          }
1627  
1628          if( ( ! $in_parens ) && ( $c == "'" ) && ( $qrylines[$i - 1] != '\\' ) )
1629          { // Text between quotation marks outside from parentheses, must be in a field COMMENT
1630              // Commas in field comments are not separating two fields, so don't split there.
1631              $in_quote = ! $in_quote;
1632          }
1633  
1634          $buffer .= $c;
1635      }
1636      if( strlen($buffer) )
1637      {
1638          $flds[] = trim($buffer);
1639      }
1640  
1641      return $flds;
1642  }
1643  
1644  
1645  /**
1646   * Get engine type from a Create Table query
1647   *
1648   * @param string the query
1649   * @return mixed false if ENGINE is not given in the query, the ENGINE type otherwise
1650   */
1651  function get_engine_from_query( $query )
1652  {
1653      if( preg_match( '~\bENGINE\s*=\s*(\w+)~', $query, $match ) )
1654      {
1655          return $match[1];
1656      }
1657      return false;
1658  }
1659  
1660  ?>

title

Description

title

Description

title

Description

title

title

Body