1 <?php

2 namespace TYPO3\CMS\Install\Service;

3 

21 class SqlSchemaMigrationService {

22 

26  const MYSQL_MAXIMUM_FIELD_WIDTH = 64;

27 

31  protected $deletedPrefixKey = 'zzz_deleted_';

32 

36  protected $character_sets = array();

37 

43  public function setDeletedPrefixKey($prefix) {

44  $this->deletedPrefixKey = $prefix;

45  }

46 

52  public function getDeletedPrefixKey() {

53  return $this->deletedPrefixKey;

54  }

55 

62  public function getFieldDefinitions_fileContent($fileContent) {

63  $lines = \TYPO3\CMS\Core\Utility\GeneralUtility::trimExplode(LF, $fileContent, TRUE);

64  $table = '';

65  $total = array();

66  foreach ($lines as $value) {

67  if ($value[0] === '#') {

68  // Ignore comments

69  continue;

70  }

71  if (!strlen($table)) {

72  $parts = \TYPO3\CMS\Core\Utility\GeneralUtility::trimExplode(' ', $value, TRUE);

73  if (strtoupper($parts[0]) === 'CREATE' && strtoupper($parts[1]) === 'TABLE') {

74  $table = str_replace('`', '', $parts[2]);

75  // tablenames are always lowercase on windows!

76  if (TYPO3_OS == 'WIN') {

77  $table = strtolower($table);

78  }

79  }

80  } else {

81  if ($value[0] === ')' && substr($value, -1) === ';') {

82  $ttype = array();

83  if (preg_match('/(ENGINE|TYPE)[ ]*=[ ]*([a-zA-Z]*)/', $value, $ttype)) {

84  $total[$table]['extra']['ENGINE'] = $ttype[2];

85  }

86  // Otherwise, just do nothing: If table engine is not defined, just accept the system default.

87  // Set the collation, if specified

88  if (preg_match('/(COLLATE)[ ]*=[ ]*([a-zA-z0-9_-]+)/', $value, $tcollation)) {

89  $total[$table]['extra']['COLLATE'] = $tcollation[2];

90  } else {

91  // 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)

92  if (preg_match('/(CHARSET|CHARACTER SET)[ ]*=[ ]*([a-zA-z0-9_-]+)/', $value, $tcharset)) {

93  // Note: Keywords "DEFAULT CHARSET" and "CHARSET" are the same, so "DEFAULT" can just be ignored

94  $charset = $tcharset[2];

95  } else {

96  $charset = $GLOBALS['TYPO3_DB']->default_charset;

97  }

98  $total[$table]['extra']['COLLATE'] = $this->getCollationForCharset($charset);

99  }

100  // Remove table marker and start looking for the next "CREATE TABLE" statement

101  $table = '';

102  } else {

103  // Strip trailing commas

104  $lineV = preg_replace('/,$/', '', $value);

105  $lineV = str_replace('`', '', $lineV);

106  // Reduce muliple blanks and tabs

107  $lineV = preg_replace('/[ \t]+/', ' ', $lineV);

108  $parts = explode(' ', $lineV, 2);

109  // Field definition

110  if (!preg_match('/(PRIMARY|UNIQUE|FULLTEXT|INDEX|KEY)/', $parts[0])) {

111  // Make sure there is no default value when auto_increment is set

112  if (stristr($parts[1], 'auto_increment')) {

113  $parts[1] = preg_replace('/ default \'0\'/i', '', $parts[1]);

114  }

115  // "default" is always lower-case

116  if (stristr($parts[1], ' DEFAULT ')) {

117  $parts[1] = str_ireplace(' DEFAULT ', ' default ', $parts[1]);

118  }

119  // Change order of "default" and "NULL" statements

120  $parts[1] = preg_replace('/(.*) (default .*) (NOT NULL)/', '$1 $3 $2', $parts[1]);

121  $parts[1] = preg_replace('/(.*) (default .*) (NULL)/', '$1 $3 $2', $parts[1]);

122  $key = $parts[0];

123  $total[$table]['fields'][$key] = $parts[1];

124  } else {

125  // Key definition

126  $search = array('/UNIQUE (INDEX|KEY)/', '/FULLTEXT (INDEX|KEY)/', '/INDEX/');

127  $replace = array('UNIQUE', 'FULLTEXT', 'KEY');

128  $lineV = preg_replace($search, $replace, $lineV);

129  if (preg_match('/PRIMARY|UNIQUE|FULLTEXT/', $parts[0])) {

130  $parts[1] = preg_replace('/^(KEY|INDEX) /', '', $parts[1]);

131  }

132  $newParts = explode(' ', $parts[1], 2);

133  $key = $parts[0] == 'PRIMARY' ? $parts[0] : $newParts[0];

134  $total[$table]['keys'][$key] = $lineV;

135  // This is a protection against doing something stupid: Only allow clearing of cache_* and index_* tables.

136  if (preg_match('/^(cache|index)_/', $table)) {

137  // Suggest to truncate (clear) this table

138  $total[$table]['extra']['CLEAR'] = 1;

139  }

140  }

141  }

142  }

143  }

144  return $total;

145  }

146 

153  public function getCollationForCharset($charset) {

154  // Load character sets, if not cached already

155  if (!count($this->character_sets)) {

156  if (method_exists($GLOBALS['TYPO3_DB'], 'admin_get_charsets')) {

157  $this->character_sets = $GLOBALS['TYPO3_DB']->admin_get_charsets();

158  } else {

159  // Add empty element to avoid that the check will be repeated

160  $this->character_sets[$charset] = array();

161  }

162  }

163  $collation = '';

164  if (isset($this->character_sets[$charset]['Default collation'])) {

165  $collation = $this->character_sets[$charset]['Default collation'];

166  }

167  return $collation;

168  }

169 

175  public function getFieldDefinitions_database() {

176  $total = array();

177  $tempKeys = array();

178  $tempKeysPrefix = array();

179  $GLOBALS['TYPO3_DB']->connectDB();

180  echo $GLOBALS['TYPO3_DB']->sql_error();

181  $tables = $GLOBALS['TYPO3_DB']->admin_get_tables();

182  foreach ($tables as $tableName => $tableStatus) {

183  // Fields

184  $fieldInformation = $GLOBALS['TYPO3_DB']->admin_get_fields($tableName);

185  foreach ($fieldInformation as $fN => $fieldRow) {

186  $total[$tableName]['fields'][$fN] = $this->assembleFieldDefinition($fieldRow);

187  }

188  // Keys

189  $keyInformation = $GLOBALS['TYPO3_DB']->admin_get_keys($tableName);

190  foreach ($keyInformation as $keyRow) {

191  $keyName = $keyRow['Key_name'];

192  $colName = $keyRow['Column_name'];

193  if ($keyRow['Sub_part']) {

194  $colName .= '(' . $keyRow['Sub_part'] . ')';

195  }

196  $tempKeys[$tableName][$keyName][$keyRow['Seq_in_index']] = $colName;

197  if ($keyName == 'PRIMARY') {

198  $prefix = 'PRIMARY KEY';

199  } else {

200  if ($keyRow['Index_type'] == 'FULLTEXT') {

201  $prefix = 'FULLTEXT';

202  } elseif ($keyRow['Non_unique']) {

203  $prefix = 'KEY';

204  } else {

205  $prefix = 'UNIQUE';

206  }

207  $prefix .= ' ' . $keyName;

208  }

209  $tempKeysPrefix[$tableName][$keyName] = $prefix;

210  }

211  // Table status (storage engine, collaction, etc.)

212  if (is_array($tableStatus)) {

213  $tableExtraFields = array(

214  'Engine' => 'ENGINE',

215  'Collation' => 'COLLATE'

216  );

217  foreach ($tableExtraFields as $mysqlKey => $internalKey) {

218  if (isset($tableStatus[$mysqlKey])) {

219  $total[$tableName]['extra'][$internalKey] = $tableStatus[$mysqlKey];

220  }

221  }

222  }

223  }

224  // Compile key information:

225  if (count($tempKeys)) {

226  foreach ($tempKeys as $table => $keyInf) {

227  foreach ($keyInf as $kName => $index) {

228  ksort($index);

229  $total[$table]['keys'][$kName] = $tempKeysPrefix[$table][$kName] . ' (' . implode(',', $index) . ')';

230  }

231  }

232  }

233  return $total;

234  }

235 

246  public function getDatabaseExtra($FDsrc, $FDcomp, $onlyTableList = '', $ignoreNotNullWhenComparing = FALSE) {

247  $extraArr = array();

248  $diffArr = array();

249  if (is_array($FDsrc)) {

250  foreach ($FDsrc as $table => $info) {

251  if (!strlen($onlyTableList) || \TYPO3\CMS\Core\Utility\GeneralUtility::inList($onlyTableList, $table)) {

252  if (!isset($FDcomp[$table])) {

253  // If the table was not in the FDcomp-array, the result array is loaded with that table.

254  $extraArr[$table] = $info;

255  $extraArr[$table]['whole_table'] = 1;

256  } else {

257  $keyTypes = explode(',', 'extra,fields,keys');

258  foreach ($keyTypes as $theKey) {

259  if (is_array($info[$theKey])) {

260  foreach ($info[$theKey] as $fieldN => $fieldC) {

261  $fieldN = str_replace('`', '', $fieldN);

262  if ($fieldN == 'COLLATE') {

263  // TODO: collation support is currently disabled (needs more testing)

264  continue;

265  }

266  if (!isset($FDcomp[$table][$theKey][$fieldN])) {

267  $extraArr[$table][$theKey][$fieldN] = $fieldC;

268  } else {

269  $fieldC = trim($fieldC);

270 

271  // Lowercase the field type to surround false-positive schema changes to be

272  // reported just because of different caseing of characters

273  // The regex does just trigger for the first word followed by parentheses

274  // that contain a length. It does not trigger for e.g. "PRIMARY KEY" because

275  // "PRIMARY KEY" is being returned from the DB in upper case.

276  $fieldC = preg_replace_callback(

277  '/^([a-zA-Z0-9]+)(\([^)]*\)\s.*)/',

278  function($matches) { return strtolower($matches[1]) . $matches[2]; },

279  $fieldC

280  );

281 

282  if ($ignoreNotNullWhenComparing) {

283  $fieldC = str_replace(' NOT NULL', '', $fieldC);

284  $FDcomp[$table][$theKey][$fieldN] = str_replace(' NOT NULL', '', $FDcomp[$table][$theKey][$fieldN]);

285  }

286  if ($fieldC !== $FDcomp[$table][$theKey][$fieldN]) {

287  $diffArr[$table][$theKey][$fieldN] = $fieldC;

288  $diffArr_cur[$table][$theKey][$fieldN] = $FDcomp[$table][$theKey][$fieldN];

289  }

290  }

291  }

292  }

293  }

294  }

295  }

296  }

297  }

298  $output = array(

299  'extra' => $extraArr,

300  'diff' => $diffArr,

301  'diff_currentValues' => $diffArr_cur

302  );

303  return $output;

304  }

305 

313  public function getUpdateSuggestions($diffArr, $keyList = 'extra,diff') {

314  $statements = array();

315  $deletedPrefixKey = $this->deletedPrefixKey;

316  $deletedPrefixLength = strlen($deletedPrefixKey);

317  $remove = 0;

318  if ($keyList == 'remove') {

319  $remove = 1;

320  $keyList = 'extra';

321  }

322  $keyList = explode(',', $keyList);

323  foreach ($keyList as $theKey) {

324  if (is_array($diffArr[$theKey])) {

325  foreach ($diffArr[$theKey] as $table => $info) {

326  $whole_table = array();

327  if (isset($info['keys']) && is_array($info['keys'])) {

328  foreach ($info['keys'] as $fN => $fV) {

329  if (!$info['whole_table'] && $theKey === 'extra' && $remove) {

330  $statement = 'ALTER TABLE ' . $table . ($fN === 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY ' . $fN) . ';';

331  $statements['drop'][md5($statement)] = $statement;

332  }

333  }

334  }

335  if (is_array($info['fields'])) {

336  foreach ($info['fields'] as $fN => $fV) {

337  if ($info['whole_table']) {

338  $whole_table[] = $fN . ' ' . $fV;

339  } else {

340  // Special case to work around MySQL problems when adding auto_increment fields:

341  if (stristr($fV, 'auto_increment')) {

342  // The field can only be set "auto_increment" if there exists a PRIMARY key of that field already.

343  // The check does not look up which field is primary but just assumes it must be the field with the auto_increment value...

344  if (isset($info['keys']['PRIMARY'])) {

345  if (!$this->isDbalEnabled()) {

346  // Combine adding the field and the primary key into a single statement

347  $fV .= ', ADD PRIMARY KEY (' . $fN . ')';

348  unset($info['keys']['PRIMARY']);

349  }

350  } else {

351  // In the next step, attempt to clear the table once again (2 = force)

352  $info['extra']['CLEAR'] = 2;

353  }

354  }

355  if ($theKey == 'extra') {

356  if ($remove) {

357  if (substr($fN, 0, $deletedPrefixLength) !== $deletedPrefixKey) {

358  // we've to make sure we don't exceed the maximal length

359  $prefixedFieldName = $deletedPrefixKey . substr($fN, ($deletedPrefixLength - self::MYSQL_MAXIMUM_FIELD_WIDTH));

360  $statement = 'ALTER TABLE ' . $table . ' CHANGE ' . $fN . ' ' . $prefixedFieldName . ' ' . $fV . ';';

361  $statements['change'][md5($statement)] = $statement;

362  } else {

363  $statement = 'ALTER TABLE ' . $table . ' DROP ' . $fN . ';';

364  $statements['drop'][md5($statement)] = $statement;

365  }

366  } else {

367  $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fN . ' ' . $fV . ';';

368  $statements['add'][md5($statement)] = $statement;

369  }

370  } elseif ($theKey == 'diff') {

371  $statement = 'ALTER TABLE ' . $table . ' CHANGE ' . $fN . ' ' . $fN . ' ' . $fV . ';';

372  $statements['change'][md5($statement)] = $statement;

373  $statements['change_currentValue'][md5($statement)] = $diffArr['diff_currentValues'][$table]['fields'][$fN];

374  }

375  }

376  }

377  }

378  if (is_array($info['keys'])) {

379  foreach ($info['keys'] as $fN => $fV) {

380  if ($info['whole_table']) {

381  $whole_table[] = $fV;

382  } else {

383  if ($theKey == 'extra') {

384  if (!$remove) {

385  $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fV . ';';

386  $statements['add'][md5($statement)] = $statement;

387  }

388  } elseif ($theKey == 'diff') {

389  $statement = 'ALTER TABLE ' . $table . ($fN == 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY ' . $fN) . ';';

390  $statements['change'][md5($statement)] = $statement;

391  $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fV . ';';

392  $statements['change'][md5($statement)] = $statement;

393  }

394  }

395  }

396  }

397  if (is_array($info['extra'])) {

398  $extras = array();

399  $extras_currentValue = array();

400  $clear_table = FALSE;

401  foreach ($info['extra'] as $fN => $fV) {

402  // Only consider statements which are missing in the database but don't remove existing properties

403  if (!$remove) {

404  if (!$info['whole_table']) {

405  // If the whole table is created at once, we take care of this later by imploding all elements of $info['extra']

406  if ($fN == 'CLEAR') {

407  // Truncate table must happen later, not now

408  // Valid values for CLEAR: 1=only clear if keys are missing, 2=clear anyway (force)

409  if (count($info['keys']) || $fV == 2) {

410  $clear_table = TRUE;

411  }

412  continue;

413  } else {

414  $extras[] = $fN . '=' . $fV;

415  $extras_currentValue[] = $fN . '=' . $diffArr['diff_currentValues'][$table]['extra'][$fN];

416  }

417  }

418  }

419  }

420  if ($clear_table) {

421  $statement = 'TRUNCATE TABLE ' . $table . ';';

422  $statements['clear_table'][md5($statement)] = $statement;

423  }

424  if (count($extras)) {

425  $statement = 'ALTER TABLE ' . $table . ' ' . implode(' ', $extras) . ';';

426  $statements['change'][md5($statement)] = $statement;

427  $statements['change_currentValue'][md5($statement)] = implode(' ', $extras_currentValue);

428  }

429  }

430  if ($info['whole_table']) {

431  if ($remove) {

432  if (substr($table, 0, $deletedPrefixLength) !== $deletedPrefixKey) {

433  // we've to make sure we don't exceed the maximal length

434  $prefixedTableName = $deletedPrefixKey . substr($table, ($deletedPrefixLength - self::MYSQL_MAXIMUM_FIELD_WIDTH));

435  $statement = 'ALTER TABLE ' . $table . ' RENAME ' . $prefixedTableName . ';';

436  $statements['change_table'][md5($statement)] = $statement;

437  } else {

438  $statement = 'DROP TABLE ' . $table . ';';

439  $statements['drop_table'][md5($statement)] = $statement;

440  }

441  // Count

442  $count = $GLOBALS['TYPO3_DB']->exec_SELECTcountRows('*', $table);

443  $statements['tables_count'][md5($statement)] = $count ? 'Records in table: ' . $count : '';

444  } else {

445  $statement = 'CREATE TABLE ' . $table . ' (

446 ' . implode(',

447 ', $whole_table) . '

448 )';

449  if ($info['extra']) {

450  foreach ($info['extra'] as $k => $v) {

451  if ($k == 'COLLATE' || $k == 'CLEAR') {

452  // Skip these special statements. TODO: collation support is currently disabled (needs more testing)

453  continue;

454  }

455  // Add extra attributes like ENGINE, CHARSET, etc.

456  $statement .= ' ' . $k . '=' . $v;

457  }

458  }

459  $statement .= ';';

460  $statements['create_table'][md5($statement)] = $statement;

461  }

462  }

463  }

464  }

465  }

466  return $statements;

467  }

468 

475  public function assembleFieldDefinition($row) {

476  $field = array($row['Type']);

477  if ($row['Null'] == 'NO') {

478  $field[] = 'NOT NULL';

479  }

480  if (!strstr($row['Type'], 'blob') && !strstr($row['Type'], 'text')) {

481  // Add a default value if the field is not auto-incremented (these fields never have a default definition)

482  if (!stristr($row['Extra'], 'auto_increment')) {

483  if ($row['Default'] === NULL) {

484  $field[] = 'default NULL';

485  } else {

486  $field[] = 'default \'' . addslashes($row['Default']) . '\'';

487  }

488  }

489  }

490  if ($row['Extra']) {

491  $field[] = $row['Extra'];

492  }

493  return implode(' ', $field);

494  }

495 

504  public function getStatementArray($sqlcode, $removeNonSQL = FALSE, $query_regex = '') {

505  $sqlcodeArr = explode(LF, $sqlcode);

506  // Based on the assumption that the sql-dump has

507  $statementArray = array();

508  $statementArrayPointer = 0;

509  foreach ($sqlcodeArr as $line => $lineContent) {

510  $lineContent = trim($lineContent);

511  $is_set = 0;

512  // Auto_increment fields cannot have a default value!

513  if (stristr($lineContent, 'auto_increment')) {

514  $lineContent = preg_replace('/ default \'0\'/i', '', $lineContent);

515  }

516  if (!$removeNonSQL || $lineContent !== '' && $lineContent[0] !== '#' && substr($lineContent, 0, 2) !== '--') {

517  // '--' is seen as mysqldump comments from server version 3.23.49

518  $statementArray[$statementArrayPointer] .= $lineContent;

519  $is_set = 1;

520  }

521  if (substr($lineContent, -1) === ';') {

522  if (isset($statementArray[$statementArrayPointer])) {

523  if (!trim($statementArray[$statementArrayPointer]) || $query_regex && !preg_match(('/' . $query_regex . '/i'), trim($statementArray[$statementArrayPointer]))) {

524  unset($statementArray[$statementArrayPointer]);

525  }

526  }

527  $statementArrayPointer++;

528  } elseif ($is_set) {

529  $statementArray[$statementArrayPointer] .= LF;

530  }

531  }

532  return $statementArray;

533  }

534 

542  public function getCreateTables($statements, $insertCountFlag = FALSE) {

543  $crTables = array();

544  $insertCount = array();

545  foreach ($statements as $line => $lineContent) {

546  $reg = array();

547  if (preg_match('/^create[[:space:]]*table[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {

548  $table = trim($reg[1]);

549  if ($table) {

550  // Table names are always lowercase on Windows!

551  if (TYPO3_OS == 'WIN') {

552  $table = strtolower($table);

553  }

554  $sqlLines = explode(LF, $lineContent);

555  foreach ($sqlLines as $k => $v) {

556  if (stristr($v, 'auto_increment')) {

557  $sqlLines[$k] = preg_replace('/ default \'0\'/i', '', $v);

558  }

559  }

560  $lineContent = implode(LF, $sqlLines);

561  $crTables[$table] = $lineContent;

562  }

563  } elseif ($insertCountFlag && preg_match('/^insert[[:space:]]*into[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {

564  $nTable = trim($reg[1]);

565  $insertCount[$nTable]++;

566  }

567  }

568  return array($crTables, $insertCount);

569  }

570 

578  public function getTableInsertStatements($statements, $table) {

579  $outStatements = array();

580  foreach ($statements as $line => $lineContent) {

581  $reg = array();

582  if (preg_match('/^insert[[:space:]]*into[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {

583  $nTable = trim($reg[1]);

584  if ($nTable && $table === $nTable) {

585  $outStatements[] = $lineContent;

586  }

587  }

588  }

589  return $outStatements;

590  }

591 

599  public function performUpdateQueries($arr, $keyArr) {

600  $result = array();

601  if (is_array($arr)) {

602  foreach ($arr as $key => $string) {

603  if (isset($keyArr[$key]) && $keyArr[$key]) {

604  $res = $GLOBALS['TYPO3_DB']->admin_query($string);

605  if ($res === FALSE) {

606  $result[$key] = $GLOBALS['TYPO3_DB']->sql_error();

607  } elseif (is_resource($res) || is_a($res, '\\mysqli_result')) {

608  $GLOBALS['TYPO3_DB']->sql_free_result($res);

609  }

610  }

611  }

612  }

613  if (count($result) > 0) {

614  return $result;

615  } else {

616  return TRUE;

617  }

618  }

619 

626  public function getListOfTables() {

627  $whichTables = $GLOBALS['TYPO3_DB']->admin_get_tables(TYPO3_db);

628  foreach ($whichTables as $key => &$value) {

629  $value = $key;

630  }

631  unset($value);

632  return $whichTables;

633  }

634 

640  protected function isDbalEnabled() {

641  return \TYPO3\CMS\Core\Utility\ExtensionManagementUtility::isLoaded('dbal');

642  }

643 }