t3lib/class.t3lib_install_sql.php

   1 <?php
   2 /***************************************************************
   3  *  Copyright notice
   4  *
   5  *  (c) 2011 Christian Kuhn <lolli@schwarzbu.ch>
   6  *  All rights reserved
   7  *
   8  *  This script is part of the TYPO3 project. The TYPO3 project is
   9  *  free software; you can redistribute it and/or modify
  10  *  it under the terms of the GNU General Public License as published by
  11  *  the Free Software Foundation; either version 2 of the License, or
  12  *  (at your option) any later version.
  13  *
  14  *  The GNU General Public License can be found at
  15  *  http://www.gnu.org/copyleft/gpl.html.
  16  *  A copy is found in the textfile GPL.txt and important notices to the license
  17  *  from the author is found in LICENSE.txt distributed with these scripts.
  18  *
  19  *
  20  *  This script is distributed in the hope that it will be useful,
  21  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  22  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  23  *  GNU General Public License for more details.
  24  *
  25  *  This copyright notice MUST APPEAR in all copies of the script!
  26  ***************************************************************/
  27
  28 /**
  29  * Verify TYPO3 DB table structure. ainly used in install tool
  30  * compare wizard and extension manager.
  31  *
  32  * @author Christian Kuhn <lolli@schwarzbu.ch>
  33  * @package TYPO3
  34  * @subpackage t3lib
  35  */
  36 class t3lib_install_Sql {
  37
  38         /**
  39          * @var string Prefix of deleted tables
  40          */
  41         protected $deletedPrefixKey = 'zzz_deleted_'; // Prefix used for tables/fields when deleted/renamed.
  42
  43         /**
  44          * @var float|int Multiplier of SQL field size (for char, varchar and text fields)
  45          */
  46         protected $multiplySize = 1;
  47
  48         /**
  49          * @var array Caching output of $GLOBALS['TYPO3_DB']->admin_get_charsets()
  50          */
  51         protected $character_sets = array();
  52
  53         /**
  54          * Constructor function
  55          */
  56         public function __construct() {
  57                 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['multiplyDBfieldSize'] >= 1 && $GLOBALS['TYPO3_CONF_VARS']['SYS']['multiplyDBfieldSize'] <= 5) {
  58                         $this->multiplySize = (double) $GLOBALS['TYPO3_CONF_VARS']['SYS']['multiplyDBfieldSize'];
  59                 }
  60         }
  61
  62         /**
  63          * Set prefix of deleted tables
  64          *
  65          * @param string $prefix Prefix string
  66          */
  67         public function setDeletedPrefixKey($prefix) {
  68                 $this->deletedPrefixKey = $prefix;
  69         }
  70
  71         /**
  72          * Get prefix of deleted tables
  73          *
  74          * @return string
  75          */
  76         public function getDeletedPrefixKey() {
  77                 return $this->deletedPrefixKey;
  78         }
  79
  80         /**
  81          * Reads the field definitions for the input SQL-file string
  82          *
  83          * @param string $fileContent Should be a string read from an SQL-file made with 'mysqldump [database_name] -d'
  84          * @return array Array with information about table.
  85          */
  86         public function getFieldDefinitions_fileContent($fileContent) {
  87                 $lines = t3lib_div::trimExplode(LF, $fileContent, 1);
  88                 $table = '';
  89                 $total = array();
  90
  91                 foreach ($lines as $value) {
  92                         if (substr($value, 0, 1) == '#') {
  93                                 continue; // Ignore comments
  94                         }
  95
  96                         if (!strlen($table)) {
  97                                 $parts = t3lib_div::trimExplode(' ', $value, TRUE);
  98                                 if (strtoupper($parts[0]) === 'CREATE' && strtoupper($parts[1]) === 'TABLE') {
  99                                         $table = str_replace('`', '', $parts[2]);
 100                                         if (TYPO3_OS == 'WIN') { // tablenames are always lowercase on windows!
 101                                                 $table = strtolower($table);
 102                                         }
 103                                 }
 104                         } else {
 105                                 if (substr($value, 0, 1) == ')' && substr($value, -1) == ';') {
 106                                         $ttype = array();
 107                                         if (preg_match('/(ENGINE|TYPE)[ ]*=[ ]*([a-zA-Z]*)/', $value, $ttype)) {
 108                                                 $total[$table]['extra']['ENGINE'] = $ttype[2];
 109                                         } // Otherwise, just do nothing: If table engine is not defined, just accept the system default.
 110
 111                                                 // Set the collation, if specified
 112                                         if (preg_match('/(COLLATE)[ ]*=[ ]*([a-zA-z0-9_-]+)/', $value, $tcollation)) {
 113                                                 $total[$table]['extra']['COLLATE'] = $tcollation[2];
 114                                         } else {
 115                                                         // Otherwise, get the CHARACTER SET and try to find the default collation for it as returned by "SHOW CHARACTER SET" query (for details, see http://dev.mysql.com/doc/refman/5.1/en/charset-table.html)
 116                                                 if (preg_match('/(CHARSET|CHARACTER SET)[ ]*=[ ]*([a-zA-z0-9_-]+)/', $value, $tcharset)) { // Note: Keywords "DEFAULT CHARSET" and "CHARSET" are the same, so "DEFAULT" can just be ignored
 117                                                         $charset = $tcharset[2];
 118                                                 } else {
 119                                                         $charset = $GLOBALS['TYPO3_DB']->default_charset; // Fallback to default charset
 120                                                 }
 121                                                 $total[$table]['extra']['COLLATE'] = $this->getCollationForCharset($charset);
 122                                         }
 123
 124                                         $table = ''; // Remove table marker and start looking for the next "CREATE TABLE" statement
 125                                 } else {
 126                                         $lineV = preg_replace('/,$/', '', $value); // Strip trailing commas
 127                                         $lineV = str_replace('`', '', $lineV);
 128                                         $lineV = str_replace('  ', ' ', $lineV); // Remove double blanks
 129
 130                                         $parts = explode(' ', $lineV, 2);
 131                                         if (!preg_match('/(PRIMARY|UNIQUE|FULLTEXT|INDEX|KEY)/', $parts[0])) { // Field definition
 132
 133                                                         // Make sure there is no default value when auto_increment is set
 134                                                 if (stristr($parts[1], 'auto_increment')) {
 135                                                         $parts[1] = preg_replace('/ default \'0\'/i', '', $parts[1]);
 136                                                 }
 137                                                         // "default" is always lower-case
 138                                                 if (stristr($parts[1], ' DEFAULT ')) {
 139                                                         $parts[1] = str_ireplace(' DEFAULT ', ' default ', $parts[1]);
 140                                                 }
 141
 142                                                         // Change order of "default" and "NULL" statements
 143                                                 $parts[1] = preg_replace('/(.*) (default .*) (NOT NULL)/', '$1 $3 $2', $parts[1]);
 144                                                 $parts[1] = preg_replace('/(.*) (default .*) (NULL)/', '$1 $3 $2', $parts[1]);
 145
 146                                                 $key = $parts[0];
 147                                                 $total[$table]['fields'][$key] = $parts[1];
 148
 149                                         } else { // Key definition
 150                                                 $search = array('/UNIQUE (INDEX|KEY)/', '/FULLTEXT (INDEX|KEY)/', '/INDEX/');
 151                                                 $replace = array('UNIQUE', 'FULLTEXT', 'KEY');
 152                                                 $lineV = preg_replace($search, $replace, $lineV);
 153
 154                                                 if (preg_match('/PRIMARY|UNIQUE|FULLTEXT/', $parts[0])) {
 155                                                         $parts[1] = preg_replace('/^(KEY|INDEX) /', '', $parts[1]);
 156                                                 }
 157
 158                                                 $newParts = explode(' ', $parts[1], 2);
 159                                                 $key = $parts[0] == 'PRIMARY' ? $parts[0] : $newParts[0];
 160
 161                                                 $total[$table]['keys'][$key] = $lineV;
 162
 163                                                         // This is a protection against doing something stupid: Only allow clearing of cache_* and index_* tables.
 164                                                 if (preg_match('/^(cache|index)_/', $table)) {
 165                                                                 // Suggest to truncate (clear) this table
 166                                                         $total[$table]['extra']['CLEAR'] = 1;
 167                                                 }
 168                                         }
 169                                 }
 170                         }
 171                 }
 172
 173                 $this->getFieldDefinitions_sqlContent_parseTypes($total);
 174                 return $total;
 175         }
 176
 177         /**
 178          * Multiplies varchars/tinytext fields in size according to $this->multiplySize
 179          * Useful if you want to use UTF-8 in the database and needs to extend the field sizes in the database so UTF-8 chars are not discarded. For most charsets available as single byte sets, multiplication with 2 should be enough. For chinese, use 3.
 180          *
 181          * @param array $total Total array (from getFieldDefinitions_fileContent())
 182          * @return      void
 183          * @access private
 184          * @see getFieldDefinitions_fileContent()
 185          */
 186         protected function getFieldDefinitions_sqlContent_parseTypes(&$total) {
 187
 188                 $mSize = (double) $this->multiplySize;
 189                 if ($mSize > 1) {
 190
 191                                 /** @var $sqlParser t3lib_sqlparser */
 192                         $sqlParser = t3lib_div::makeInstance('t3lib_sqlparser');
 193                         foreach ($total as $table => $cfg) {
 194                                 if (is_array($cfg['fields'])) {
 195                                         foreach ($cfg['fields'] as $fN => $fType) {
 196                                                 $orig_fType = $fType;
 197                                                 $fInfo = $sqlParser->parseFieldDef($fType);
 198
 199                                                 switch ($fInfo['fieldType']) {
 200                                                         case 'char':
 201                                                         case 'varchar':
 202                                                                 $newSize = round($fInfo['value'] * $mSize);
 203
 204                                                                 if ($newSize <= 255) {
 205                                                                         $fInfo['value'] = $newSize;
 206                                                                 } else {
 207                                                                         $fInfo = array(
 208                                                                                 'fieldType' => 'text',
 209                                                                                 'featureIndex' => array(
 210                                                                                         'NOTNULL' => array(
 211                                                                                                 'keyword' => 'NOT NULL'
 212                                                                                         )
 213                                                                                 )
 214                                                                         );
 215                                                                                 // Change key definition if necessary (must use "prefix" on TEXT columns)
 216                                                                         if (is_array($cfg['keys'])) {
 217                                                                                 foreach ($cfg['keys'] as $kN => $kType) {
 218                                                                                         $match = array();
 219                                                                                         preg_match('/^([^(]*)\(([^)]+)\)(.*)/', $kType, $match);
 220                                                                                         $keys = array();
 221                                                                                         foreach (t3lib_div::trimExplode(',', $match[2]) as $kfN) {
 222                                                                                                 if ($fN == $kfN) {
 223                                                                                                         $kfN .= '(' . $newSize . ')';
 224                                                                                                 }
 225                                                                                                 $keys[] = $kfN;
 226                                                                                         }
 227                                                                                         $total[$table]['keys'][$kN] = $match[1] . '(' . implode(',', $keys) . ')' . $match[3];
 228                                                                                 }
 229                                                                         }
 230                                                                 }
 231                                                         break;
 232                                                         case 'tinytext':
 233                                                                 $fInfo['fieldType'] = 'text';
 234                                                         break;
 235                                                 }
 236
 237                                                 $total[$table]['fields'][$fN] = $sqlParser->compileFieldCfg($fInfo);
 238                                                 if ($sqlParser->parse_error) {
 239                                                         throw new RuntimeException(
 240                                                                 'TYPO3 Fatal Error: ' . $sqlParser->parse_error,
 241                                                                 1270853961
 242                                                         );
 243                                                 }
 244                                         }
 245                                 }
 246                         }
 247                 }
 248         }
 249
 250         /**
 251          * Look up the default collation for specified character set based on "SHOW CHARACTER SET" output
 252          *
 253          * @param string $charset Character set
 254          * @return string Corresponding default collation
 255          */
 256         public function getCollationForCharset($charset) {
 257                         // Load character sets, if not cached already
 258                 if (!count($this->character_sets)) {
 259                         if (method_exists($GLOBALS['TYPO3_DB'], 'admin_get_charsets')) {
 260                                 $this->character_sets = $GLOBALS['TYPO3_DB']->admin_get_charsets();
 261                         } else {
 262                                 $this->character_sets[$charset] = array(); // Add empty element to avoid that the check will be repeated
 263                         }
 264                 }
 265
 266                 $collation = '';
 267                 if (isset($this->character_sets[$charset]['Default collation'])) {
 268                         $collation = $this->character_sets[$charset]['Default collation'];
 269                 }
 270
 271                 return $collation;
 272         }
 273
 274         /**
 275          * Reads the field definitions for the current database
 276          *
 277          * @return array Array with information about table.
 278          */
 279         public function getFieldDefinitions_database() {
 280                 $total = array();
 281                 $tempKeys = array();
 282                 $tempKeysPrefix = array();
 283
 284                 $GLOBALS['TYPO3_DB']->sql_select_db(TYPO3_db);
 285                 echo $GLOBALS['TYPO3_DB']->sql_error();
 286
 287                 $tables = $GLOBALS['TYPO3_DB']->admin_get_tables(TYPO3_db);
 288                 foreach ($tables as $tableName => $tableStatus) {
 289
 290                                 // Fields:
 291                         $fieldInformation = $GLOBALS['TYPO3_DB']->admin_get_fields($tableName);
 292                         foreach ($fieldInformation as $fN => $fieldRow) {
 293                                 $total[$tableName]['fields'][$fN] = $this->assembleFieldDefinition($fieldRow);
 294                         }
 295
 296                                 // Keys:
 297                         $keyInformation = $GLOBALS['TYPO3_DB']->admin_get_keys($tableName);
 298
 299                         foreach ($keyInformation as $keyRow) {
 300                                 $keyName = $keyRow['Key_name'];
 301                                 $colName = $keyRow['Column_name'];
 302                                 if ($keyRow['Sub_part']) {
 303                                         $colName .= '(' . $keyRow['Sub_part'] . ')';
 304                                 }
 305                                 $tempKeys[$tableName][$keyName][$keyRow['Seq_in_index']] = $colName;
 306                                 if ($keyName == 'PRIMARY') {
 307                                         $prefix = 'PRIMARY KEY';
 308                                 } else {
 309                                         if ($keyRow['Index_type'] == 'FULLTEXT') {
 310                                                 $prefix = 'FULLTEXT';
 311                                         } elseif ($keyRow['Non_unique']) {
 312                                                 $prefix = 'KEY';
 313                                         } else {
 314                                                 $prefix = 'UNIQUE';
 315                                         }
 316                                         $prefix .= ' ' . $keyName;
 317                                 }
 318                                 $tempKeysPrefix[$tableName][$keyName] = $prefix;
 319                         }
 320
 321                                 // Table status (storage engine, collaction, etc.)
 322                         if (is_array($tableStatus)) {
 323                                 $tableExtraFields = array(
 324                                         'Engine' => 'ENGINE',
 325                                         'Collation' => 'COLLATE',
 326                                 );
 327
 328                                 foreach ($tableExtraFields as $mysqlKey => $internalKey) {
 329                                         if (isset($tableStatus[$mysqlKey])) {
 330                                                 $total[$tableName]['extra'][$internalKey] = $tableStatus[$mysqlKey];
 331                                         }
 332                                 }
 333                         }
 334                 }
 335
 336                         // Compile key information:
 337                 if (count($tempKeys)) {
 338                         foreach ($tempKeys as $table => $keyInf) {
 339                                 foreach ($keyInf as $kName => $index) {
 340                                         ksort($index);
 341                                         $total[$table]['keys'][$kName] = $tempKeysPrefix[$table][$kName] . ' (' . implode(',', $index) . ')';
 342                                 }
 343                         }
 344                 }
 345
 346                 return $total;
 347         }
 348
 349         /**
 350          * Compares two arrays with field information and returns information about fields that are MISSING and fields that have CHANGED.
 351          * FDsrc and FDcomp can be switched if you want the list of stuff to remove rather than update.
 352          *
 353          * @param array $FDsrc Field definitions, source (from getFieldDefinitions_fileContent())
 354          * @param array $FDcomp Field definitions, comparison. (from getFieldDefinitions_database())
 355          * @param string $onlyTableList Table names (in list) which is the ONLY one observed.
 356          * @param boolean $ignoreNotNullWhenComparing If set, this function ignores NOT NULL statements of the SQL file field definition when comparing current field definition from database with field definition from SQL file. This way, NOT NULL statements will be executed when the field is initially created, but the SQL parser will never complain about missing NOT NULL statements afterwards.
 357          * @return array Returns an array with 1) all elements from $FDsrc that is not in $FDcomp (in key 'extra') and 2) all elements from $FDsrc that is different from the ones in $FDcomp
 358          */
 359         public function getDatabaseExtra($FDsrc, $FDcomp, $onlyTableList = '', $ignoreNotNullWhenComparing = TRUE) {
 360                 $extraArr = array();
 361                 $diffArr = array();
 362
 363                 if (is_array($FDsrc)) {
 364                         foreach ($FDsrc as $table => $info) {
 365                                 if (!strlen($onlyTableList) || t3lib_div::inList($onlyTableList, $table)) {
 366                                         if (!isset($FDcomp[$table])) {
 367                                                 $extraArr[$table] = $info; // If the table was not in the FDcomp-array, the result array is loaded with that table.
 368                                                 $extraArr[$table]['whole_table'] = 1;
 369                                         } else {
 370                                                 $keyTypes = explode(',', 'extra,fields,keys');
 371                                                 foreach ($keyTypes as $theKey) {
 372                                                         if (is_array($info[$theKey])) {
 373                                                                 foreach ($info[$theKey] as $fieldN => $fieldC) {
 374                                                                         $fieldN = str_replace('`', '', $fieldN);
 375                                                                         if ($fieldN == 'COLLATE') {
 376                                                                                 continue; // TODO: collation support is currently disabled (needs more testing)
 377                                                                         }
 378
 379                                                                         if (!isset($FDcomp[$table][$theKey][$fieldN])) {
 380                                                                                 $extraArr[$table][$theKey][$fieldN] = $fieldC;
 381                                                                         } else {
 382                                                                                 $fieldC = trim($fieldC);
 383                                                                                 if ($ignoreNotNullWhenComparing) {
 384                                                                                         $fieldC = str_replace(' NOT NULL', '', $fieldC);
 385                                                                                         $FDcomp[$table][$theKey][$fieldN] = str_replace(' NOT NULL', '', $FDcomp[$table][$theKey][$fieldN]);
 386                                                                                 }
 387                                                                                 if ($fieldC !== $FDcomp[$table][$theKey][$fieldN]) {
 388                                                                                         $diffArr[$table][$theKey][$fieldN] = $fieldC;
 389                                                                                         $diffArr_cur[$table][$theKey][$fieldN] = $FDcomp[$table][$theKey][$fieldN];
 390                                                                                 }
 391                                                                         }
 392                                                                 }
 393                                                         }
 394                                                 }
 395                                         }
 396                                 }
 397                         }
 398                 }
 399
 400                 $output = array(
 401                         'extra' => $extraArr,
 402                         'diff' => $diffArr,
 403                         'diff_currentValues' => $diffArr_cur
 404                 );
 405
 406                 return $output;
 407         }
 408
 409         /**
 410          * Returns an array with SQL-statements that is needed to update according to the diff-array
 411          *
 412          * @param array $diffArr Array with differences of current and needed DB settings. (from getDatabaseExtra())
 413          * @param string $keyList List of fields in diff array to take notice of.
 414          * @return array Array of SQL statements (organized in keys depending on type)
 415          */
 416         public function getUpdateSuggestions($diffArr, $keyList = 'extra,diff') {
 417                 $statements = array();
 418                 $deletedPrefixKey = $this->deletedPrefixKey;
 419                 $remove = 0;
 420                 if ($keyList == 'remove') {
 421                         $remove = 1;
 422                         $keyList = 'extra';
 423                 }
 424                 $keyList = explode(',', $keyList);
 425                 foreach ($keyList as $theKey) {
 426                         if (is_array($diffArr[$theKey])) {
 427                                 foreach ($diffArr[$theKey] as $table => $info) {
 428                                         $whole_table = array();
 429                                         if (is_array($info['fields'])) {
 430                                                 foreach ($info['fields'] as $fN => $fV) {
 431                                                         if ($info['whole_table']) {
 432                                                                 $whole_table[] = $fN . ' ' . $fV;
 433                                                         } else {
 434                                                                         // Special case to work around MySQL problems when adding auto_increment fields:
 435                                                                 if (stristr($fV, 'auto_increment')) {
 436                                                                                 // The field can only be set "auto_increment" if there exists a PRIMARY key of that field already.
 437                                                                                 // The check does not look up which field is primary but just assumes it must be the field with the auto_increment value...
 438                                                                         if (isset($diffArr['extra'][$table]['keys']['PRIMARY'])) {
 439                                                                                         // Remove "auto_increment" from the statement - it will be suggested in a 2nd step after the primary key was created
 440                                                                                 $fV = str_replace(' auto_increment', '', $fV);
 441                                                                         } else {
 442                                                                                         // In the next step, attempt to clear the table once again (2 = force)
 443                                                                                 $info['extra']['CLEAR'] = 2;
 444                                                                         }
 445                                                                 }
 446                                                                 if ($theKey == 'extra') {
 447                                                                         if ($remove) {
 448                                                                                 if (substr($fN, 0, strlen($deletedPrefixKey)) != $deletedPrefixKey) {
 449                                                                                         $statement = 'ALTER TABLE ' . $table . ' CHANGE ' . $fN . ' ' . $deletedPrefixKey . $fN . ' ' . $fV . ';';
 450                                                                                         $statements['change'][md5($statement)] = $statement;
 451                                                                                 } else {
 452                                                                                         $statement = 'ALTER TABLE ' . $table . ' DROP ' . $fN . ';';
 453                                                                                         $statements['drop'][md5($statement)] = $statement;
 454                                                                                 }
 455                                                                         } else {
 456                                                                                 $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fN . ' ' . $fV . ';';
 457                                                                                 $statements['add'][md5($statement)] = $statement;
 458                                                                         }
 459                                                                 } elseif ($theKey == 'diff') {
 460                                                                         $statement = 'ALTER TABLE ' . $table . ' CHANGE ' . $fN . ' ' . $fN . ' ' . $fV . ';';
 461                                                                         $statements['change'][md5($statement)] = $statement;
 462                                                                         $statements['change_currentValue'][md5($statement)] = $diffArr['diff_currentValues'][$table]['fields'][$fN];
 463                                                                 }
 464                                                         }
 465                                                 }
 466                                         }
 467                                         if (is_array($info['keys'])) {
 468                                                 foreach ($info['keys'] as $fN => $fV) {
 469                                                         if ($info['whole_table']) {
 470                                                                 $whole_table[] = $fV;
 471                                                         } else {
 472                                                                 if ($theKey == 'extra') {
 473                                                                         if ($remove) {
 474                                                                                 $statement = 'ALTER TABLE ' . $table . ($fN == 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY ' . $fN) . ';';
 475                                                                                 $statements['drop'][md5($statement)] = $statement;
 476                                                                         } else {
 477                                                                                 $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fV . ';';
 478                                                                                 $statements['add'][md5($statement)] = $statement;
 479                                                                         }
 480                                                                 } elseif ($theKey == 'diff') {
 481                                                                         $statement = 'ALTER TABLE ' . $table . ($fN == 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY ' . $fN) . ';';
 482                                                                         $statements['change'][md5($statement)] = $statement;
 483                                                                         $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fV . ';';
 484                                                                         $statements['change'][md5($statement)] = $statement;
 485                                                                 }
 486                                                         }
 487                                                 }
 488                                         }
 489                                         if (is_array($info['extra'])) {
 490                                                 $extras = array();
 491                                                 $extras_currentValue = array();
 492                                                 $clear_table = FALSE;
 493
 494                                                 foreach ($info['extra'] as $fN => $fV) {
 495
 496                                                                 // Only consider statements which are missing in the database but don't remove existing properties
 497                                                         if (!$remove) {
 498                                                                 if (!$info['whole_table']) { // If the whole table is created at once, we take care of this later by imploding all elements of $info['extra']
 499                                                                         if ($fN == 'CLEAR') {
 500                                                                                         // Truncate table must happen later, not now
 501                                                                                         // Valid values for CLEAR: 1=only clear if keys are missing, 2=clear anyway (force)
 502                                                                                 if (count($info['keys']) || $fV == 2) {
 503                                                                                         $clear_table = TRUE;
 504                                                                                 }
 505                                                                                 continue;
 506                                                                         } else {
 507                                                                                 $extras[] = $fN . '=' . $fV;
 508                                                                                 $extras_currentValue[] = $fN . '=' . $diffArr['diff_currentValues'][$table]['extra'][$fN];
 509                                                                         }
 510                                                                 }
 511                                                         }
 512                                                 }
 513                                                 if ($clear_table) {
 514                                                         $statement = 'TRUNCATE TABLE ' . $table . ';';
 515                                                         $statements['clear_table'][md5($statement)] = $statement;
 516                                                 }
 517                                                 if (count($extras)) {
 518                                                         $statement = 'ALTER TABLE ' . $table . ' ' . implode(' ', $extras) . ';';
 519                                                         $statements['change'][md5($statement)] = $statement;
 520                                                         $statements['change_currentValue'][md5($statement)] = implode(' ', $extras_currentValue);
 521                                                 }
 522                                         }
 523                                         if ($info['whole_table']) {
 524                                                 if ($remove) {
 525                                                         if (substr($table, 0, strlen($deletedPrefixKey)) != $deletedPrefixKey) {
 526                                                                 $statement = 'ALTER TABLE ' . $table . ' RENAME ' . $deletedPrefixKey . $table . ';';
 527                                                                 $statements['change_table'][md5($statement)] = $statement;
 528                                                         } else {
 529                                                                 $statement = 'DROP TABLE ' . $table . ';';
 530                                                                 $statements['drop_table'][md5($statement)] = $statement;
 531                                                         }
 532                                                                 // count:
 533                                                         $count = $GLOBALS['TYPO3_DB']->exec_SELECTcountRows('*', $table);
 534                                                         $statements['tables_count'][md5($statement)] = $count ? 'Records in table: ' . $count : '';
 535                                                 } else {
 536                                                         $statement = 'CREATE TABLE ' . $table . " (\n" . implode(",\n", $whole_table) . "\n)";
 537                                                         if ($info['extra']) {
 538                                                                 foreach ($info['extra'] as $k => $v) {
 539                                                                         if ($k == 'COLLATE' || $k == 'CLEAR') {
 540                                                                                 continue; // Skip these special statements. TODO: collation support is currently disabled (needs more testing)
 541                                                                         }
 542                                                                         $statement .= ' ' . $k . '=' . $v; // Add extra attributes like ENGINE, CHARSET, etc.
 543                                                                 }
 544                                                         }
 545                                                         $statement .= ';';
 546                                                         $statements['create_table'][md5($statement)] = $statement;
 547                                                 }
 548                                         }
 549                                 }
 550                         }
 551                 }
 552
 553                 return $statements;
 554         }
 555
 556         /**
 557          * Converts a result row with field information into the SQL field definition string
 558          *
 559          * @param array $row MySQL result row
 560          * @return string Field definition
 561          */
 562         public function assembleFieldDefinition($row) {
 563                 $field = array($row['Type']);
 564
 565                 if ($row['Null'] == 'NO') {
 566                         $field[] = 'NOT NULL';
 567                 }
 568                 if (!strstr($row['Type'], 'blob') && !strstr($row['Type'], 'text')) {
 569                                 // Add a default value if the field is not auto-incremented (these fields never have a default definition)
 570                         if (!stristr($row['Extra'], 'auto_increment')) {
 571                                 if ($row['Default'] === NULL) {
 572                                         $field[] = 'default NULL';
 573                                 } else {
 574                                         $field[] = 'default \'' . addslashes($row['Default']) . '\'';
 575                                 }
 576                         }
 577                 }
 578                 if ($row['Extra']) {
 579                         $field[] = $row['Extra'];
 580                 }
 581
 582                 return implode(' ', $field);
 583         }
 584
 585         /**
 586          * Returns an array where every entry is a single SQL-statement. Input must be formatted like an ordinary MySQL-dump files.
 587          *
 588          * @param string $sqlcode The SQL-file content. Provided that 1) every query in the input is ended with ';' and that a line in the file contains only one query or a part of a query.
 589          * @param boolean $removeNonSQL If set, non-SQL content (like comments and blank lines) is not included in the final output
 590          * @param string $query_regex Regex to filter SQL lines to include
 591          * @return array Array of SQL statements
 592          */
 593         public function getStatementArray($sqlcode, $removeNonSQL = FALSE, $query_regex = '') {
 594                 $sqlcodeArr = explode(LF, $sqlcode);
 595
 596                         // Based on the assumption that the sql-dump has
 597                 $statementArray = array();
 598                 $statementArrayPointer = 0;
 599
 600                 foreach ($sqlcodeArr as $line => $lineContent) {
 601                         $is_set = 0;
 602
 603                                 // auto_increment fields cannot have a default value!
 604                         if (stristr($lineContent, 'auto_increment')) {
 605                                 $lineContent = preg_replace('/ default \'0\'/i', '', $lineContent);
 606                         }
 607
 608                         if (!$removeNonSQL || (strcmp(trim($lineContent), '') && substr(trim($lineContent), 0, 1) != '#' && substr(trim($lineContent), 0, 2) != '--')) { // '--' is seen as mysqldump comments from server version 3.23.49
 609                                 $statementArray[$statementArrayPointer] .= $lineContent;
 610                                 $is_set = 1;
 611                         }
 612                         if (substr(trim($lineContent), -1) == ';') {
 613                                 if (isset($statementArray[$statementArrayPointer])) {
 614                                         if (!trim($statementArray[$statementArrayPointer]) || ($query_regex && !preg_match('/' . $query_regex . '/i', trim($statementArray[$statementArrayPointer])))) {
 615                                                 unset($statementArray[$statementArrayPointer]);
 616                                         }
 617                                 }
 618                                 $statementArrayPointer++;
 619
 620                         } elseif ($is_set) {
 621                                 $statementArray[$statementArrayPointer] .= LF;
 622                         }
 623                 }
 624
 625                 return $statementArray;
 626         }
 627
 628         /**
 629          * Returns tables to create and how many records in each
 630          *
 631          * @param array $statements Array of SQL statements to analyse.
 632          * @param boolean $insertCountFlag If set, will count number of INSERT INTO statements following that table definition
 633          * @return array Array with table definitions in index 0 and count in index 1
 634          */
 635         public function getCreateTables($statements, $insertCountFlag = FALSE) {
 636                 $crTables = array();
 637                 $insertCount = array();
 638                 foreach ($statements as $line => $lineContent) {
 639                         $reg = array();
 640                         if (preg_match('/^create[[:space:]]*table[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {
 641                                 $table = trim($reg[1]);
 642                                 if ($table) {
 643                                                 // table names are always lowercase on Windows!
 644                                         if (TYPO3_OS == 'WIN') {
 645                                                 $table = strtolower($table);
 646                                         }
 647                                         $sqlLines = explode(LF, $lineContent);
 648                                         foreach ($sqlLines as $k => $v) {
 649                                                 if (stristr($v, 'auto_increment')) {
 650                                                         $sqlLines[$k] = preg_replace('/ default \'0\'/i', '', $v);
 651                                                 }
 652                                         }
 653                                         $lineContent = implode(LF, $sqlLines);
 654                                         $crTables[$table] = $lineContent;
 655                                 }
 656                         } elseif ($insertCountFlag && preg_match('/^insert[[:space:]]*into[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {
 657                                 $nTable = trim($reg[1]);
 658                                 $insertCount[$nTable]++;
 659                         }
 660                 }
 661
 662                 return array($crTables, $insertCount);
 663         }
 664
 665         /**
 666          * Extracts all insert statements from $statement array where content is inserted into $table
 667          *
 668          * @param array $statements Array of SQL statements
 669          * @param string $table Table name
 670          * @return array Array of INSERT INTO statements where table match $table
 671          */
 672         public function getTableInsertStatements($statements, $table) {
 673                 $outStatements = array();
 674                 foreach ($statements as $line => $lineContent) {
 675                         $reg = array();
 676                         if (preg_match('/^insert[[:space:]]*into[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {
 677                                 $nTable = trim($reg[1]);
 678                                 if ($nTable && !strcmp($table, $nTable)) {
 679                                         $outStatements[] = $lineContent;
 680                                 }
 681                         }
 682                 }
 683                 return $outStatements;
 684         }
 685
 686         /**
 687          * Performs the queries passed from the input array.
 688          *
 689          * @param array $arr Array of SQL queries to execute.
 690          * @param array $keyArr Array with keys that must match keys in $arr. Only where a key in this array is set and TRUE will the query be executed (meant to be passed from a form checkbox)
 691          * @return mixed Array with error message from database if any occured. Otherwise TRUE if everything was executed successfully.
 692          */
 693         public function performUpdateQueries($arr, $keyArr) {
 694                 $result = array();
 695                 if (is_array($arr)) {
 696                         foreach ($arr as $key => $string) {
 697                                 if (isset($keyArr[$key]) && $keyArr[$key]) {
 698                                         $res = $GLOBALS['TYPO3_DB']->admin_query($string);
 699                                         if ($res === FALSE) {
 700                                                 $result[$key] = $GLOBALS['TYPO3_DB']->sql_error();
 701                                         } elseif (is_resource($res)) {
 702                                                 $GLOBALS['TYPO3_DB']->sql_free_result($res);
 703                                         }
 704                                 }
 705                         }
 706                 }
 707                 if (count($result) > 0) {
 708                         return $result;
 709                 } else {
 710                         return TRUE;
 711                 }
 712         }
 713
 714         /**
 715          * Returns list of tables in the database
 716          *
 717          * @return array List of tables.
 718          * @see t3lib_db::admin_get_tables()
 719          */
 720         public function getListOfTables() {
 721                 $whichTables = $GLOBALS['TYPO3_DB']->admin_get_tables(TYPO3_db);
 722                 foreach ($whichTables as $key => &$value) {
 723                         $value = $key;
 724                 }
 725                 unset($value);
 726                 return $whichTables;
 727         }
 728 }
 729
 730 ?>