wcfsetup/install/files/lib/system/database/table/DatabaseTableChangeProcessor.class.php

   1 <?php
   2 namespace wcf\system\database\table;
   3 use wcf\data\package\Package;
   4 use wcf\system\database\editor\DatabaseEditor;
   5 use wcf\system\database\table\column\AbstractIntDatabaseTableColumn;
   6 use wcf\system\database\table\column\IDatabaseTableColumn;
   7 use wcf\system\database\table\column\TinyintDatabaseTableColumn;
   8 use wcf\system\database\table\index\DatabaseTableForeignKey;
   9 use wcf\system\database\table\index\DatabaseTableIndex;
  10 use wcf\system\database\util\PreparedStatementConditionBuilder;
  11 use wcf\system\package\SplitNodeException;
  12 use wcf\system\WCF;
  13
  14 /**
  15  * Processes a given set of changes to database tables.
  16  *
  17  * @author      Matthias Schmidt
  18  * @copyright   2001-2020 WoltLab GmbH
  19  * @license     GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
  20  * @package     WoltLabSuite\Core\System\Database\Table
  21  * @since       5.2
  22  */
  23 class DatabaseTableChangeProcessor {
  24         /**
  25          * maps the registered database table column names to the ids of the packages they belong to
  26          * @var int[][]
  27          */
  28         protected $columnPackageIDs = [];
  29
  30         /**
  31          * database table columns that will be added grouped by the name of the table to which they
  32          * will be added
  33          * @var IDatabaseTableColumn[][]
  34          */
  35         protected $columnsToAdd = [];
  36
  37         /**
  38          * database table columns that will be altered grouped by the name of the table to which
  39          * they belong
  40          * @var IDatabaseTableColumn[][]
  41          */
  42         protected $columnsToAlter = [];
  43
  44         /**
  45          * database table columns that will be dropped grouped by the name of the table from which
  46          * they will be dropped
  47          * @var IDatabaseTableColumn[][]
  48          */
  49         protected $columnsToDrop = [];
  50
  51         /**
  52          * database editor to apply the relevant changes to the table layouts
  53          * @var DatabaseEditor
  54          */
  55         protected $dbEditor;
  56
  57         /**
  58          * list of all existing tables in the used database
  59          * @var string[]
  60          */
  61         protected $existingTableNames = [];
  62
  63         /**
  64          * existing database tables
  65          * @var DatabaseTable[]
  66          */
  67         protected $existingTables = [];
  68
  69         /**
  70          * maps the registered database table index names to the ids of the packages they belong to
  71          * @var int[][]
  72          */
  73         protected $indexPackageIDs = [];
  74
  75         /**
  76          * indices that will be added grouped by the name of the table to which they will be added
  77          * @var DatabaseTableIndex[][]
  78          */
  79         protected $indicesToAdd = [];
  80
  81         /**
  82          * indices that will be dropped grouped by the name of the table from which they will be dropped
  83          * @var DatabaseTableIndex[][]
  84          */
  85         protected $indicesToDrop = [];
  86
  87         /**
  88          * maps the registered database table foreign key names to the ids of the packages they belong to
  89          * @var int[][]
  90          */
  91         protected $foreignKeyPackageIDs = [];
  92
  93         /**
  94          * foreign keys that will be added grouped by the name of the table to which they will be
  95          * added
  96          * @var DatabaseTableForeignKey[][]
  97          */
  98         protected $foreignKeysToAdd = [];
  99
 100         /**
 101          * foreign keys that will be dropped grouped by the name of the table from which they will
 102          * be dropped
 103          * @var DatabaseTableForeignKey[][]
 104          */
 105         protected $foreignKeysToDrop = [];
 106
 107         /**
 108          * package that wants to apply the changes
 109          * @var Package
 110          */
 111         protected $package;
 112
 113         /**
 114          * message for the split node exception thrown after the changes have been applied
 115          * @var string
 116          */
 117         protected $splitNodeMessage = '';
 118
 119         /**
 120          * layouts/layout changes of the relevant database table
 121          * @var DatabaseTable[]
 122          */
 123         protected $tables;
 124
 125         /**
 126          * maps the registered database table names to the ids of the packages they belong to
 127          * @var int[]
 128          */
 129         protected $tablePackageIDs = [];
 130
 131         /**
 132          * database table that will be created
 133          * @var DatabaseTable[]
 134          */
 135         protected $tablesToCreate = [];
 136
 137         /**
 138          * database tables that will be dropped
 139          * @var DatabaseTable[]
 140          */
 141         protected $tablesToDrop = [];
 142
 143         /**
 144          * Creates a new instance of `DatabaseTableChangeProcessor`.
 145          *
 146          * @param       Package                 $package
 147          * @param       DatabaseTable[]         $tables
 148          * @param       DatabaseEditor          $dbEditor
 149          */
 150         public function __construct(Package $package, array $tables, DatabaseEditor $dbEditor) {
 151                 $this->package = $package;
 152
 153                 $tableNames = [];
 154                 foreach ($tables as $table) {
 155                         if (!($table instanceof DatabaseTable)) {
 156                                 throw new \InvalidArgumentException("Tables must be instance of '" . DatabaseTable::class . "'");
 157                         }
 158
 159                         $tableNames[] = $table->getName();
 160                 }
 161
 162                 $this->tables = $tables;
 163                 $this->dbEditor = $dbEditor;
 164
 165                 $this->existingTableNames = $dbEditor->getTableNames();
 166
 167                 $conditionBuilder = new PreparedStatementConditionBuilder();
 168                 $conditionBuilder->add('sqlTable IN (?)', [$tableNames]);
 169                 $conditionBuilder->add('isDone = ?', [1]);
 170
 171                 $sql = "SELECT  *
 172                         FROM    wcf" . WCF_N . "_package_installation_sql_log
 173                         " . $conditionBuilder;
 174                 $statement = WCF::getDB()->prepareStatement($sql);
 175                 $statement->execute($conditionBuilder->getParameters());
 176
 177                 while ($row = $statement->fetchArray()) {
 178                         if ($row['sqlIndex'] === '' && $row['sqlColumn'] === '') {
 179                                 $this->tablePackageIDs[$row['sqlTable']] = $row['packageID'];
 180                         }
 181                         else if ($row['sqlIndex'] === '') {
 182                                 $this->columnPackageIDs[$row['sqlTable']][$row['sqlColumn']] = $row['packageID'];
 183                         }
 184                         else if (substr($row['sqlIndex'], -3) === '_fk') {
 185                                 $this->foreignKeyPackageIDs[$row['sqlTable']][$row['sqlIndex']] = $row['packageID'];
 186                         }
 187                         else {
 188                                 $this->indexPackageIDs[$row['sqlTable']][$row['sqlIndex']] = $row['packageID'];
 189                         }
 190                 }
 191         }
 192
 193         /**
 194          * Adds the given index to the table.
 195          *
 196          * @param       string                          $tableName
 197          * @param       DatabaseTableForeignKey         $foreignKey
 198          */
 199         protected function addForeignKey($tableName, DatabaseTableForeignKey $foreignKey) {
 200                 $this->dbEditor->addForeignKey($tableName, $foreignKey->getName(), $foreignKey->getData());
 201         }
 202
 203         /**
 204          * Adds the given index to the table.
 205          *
 206          * @param       string                  $tableName
 207          * @param       DatabaseTableIndex      $index
 208          */
 209         protected function addIndex($tableName, DatabaseTableIndex $index) {
 210                 $this->dbEditor->addIndex($tableName, $index->getName(), $index->getData());
 211         }
 212
 213         /**
 214          * Applies all of the previously determined changes to achieve the desired database layout.
 215          *
 216          * @throws      SplitNodeException      if any change has been applied
 217          */
 218         protected function applyChanges() {
 219                 $appliedAnyChange = false;
 220
 221                 foreach ($this->tablesToCreate as $table) {
 222                         $appliedAnyChange = true;
 223
 224                         $this->prepareTableLog($table);
 225                         $this->createTable($table);
 226                         $this->finalizeTableLog($table);
 227                 }
 228
 229                 foreach ($this->tablesToDrop as $table) {
 230                         $appliedAnyChange = true;
 231
 232                         $this->dropTable($table);
 233                         $this->deleteTableLog($table);
 234                 }
 235
 236                 $columnTables = array_unique(array_merge(
 237                         array_keys($this->columnsToAdd),
 238                         array_keys($this->columnsToAlter),
 239                         array_keys($this->columnsToDrop)
 240                 ));
 241                 foreach ($columnTables as $tableName) {
 242                         $appliedAnyChange = true;
 243
 244                         $columnsToAdd = $this->columnsToAdd[$tableName] ?? [];
 245                         $columnsToAlter = $this->columnsToAlter[$tableName] ?? [];
 246                         $columnsToDrop = $this->columnsToDrop[$tableName] ?? [];
 247
 248                         foreach ($columnsToAdd as $column) {
 249                                 $this->prepareColumnLog($tableName, $column);
 250                         }
 251
 252                         $this->applyColumnChanges(
 253                                 $tableName,
 254                                 $columnsToAdd,
 255                                 $columnsToAlter,
 256                                 $columnsToDrop
 257                         );
 258
 259                         foreach ($columnsToAdd as $column) {
 260                                 $this->finalizeColumnLog($tableName, $column);
 261                         }
 262
 263                         foreach ($columnsToDrop as $column) {
 264                                 $this->deleteColumnLog($tableName, $column);
 265                         }
 266                 }
 267
 268                 foreach ($this->foreignKeysToDrop as $tableName => $foreignKeys) {
 269                         foreach ($foreignKeys as $foreignKey) {
 270                                 $appliedAnyChange = true;
 271
 272                                 $this->dropForeignKey($tableName, $foreignKey);
 273                                 $this->deleteForeignKeyLog($tableName, $foreignKey);
 274                         }
 275                 }
 276
 277                 foreach ($this->foreignKeysToAdd as $tableName => $foreignKeys) {
 278                         foreach ($foreignKeys as $foreignKey) {
 279                                 $appliedAnyChange = true;
 280
 281                                 $this->prepareForeignKeyLog($tableName, $foreignKey);
 282                                 $this->addForeignKey($tableName, $foreignKey);
 283                                 $this->finalizeForeignKeyLog($tableName, $foreignKey);
 284                         }
 285                 }
 286
 287                 foreach ($this->indicesToDrop as $tableName => $indices) {
 288                         foreach ($indices as $index) {
 289                                 $appliedAnyChange = true;
 290
 291                                 $this->dropIndex($tableName, $index);
 292                                 $this->deleteIndexLog($tableName, $index);
 293                         }
 294                 }
 295
 296                 foreach ($this->indicesToAdd as $tableName => $indices) {
 297                         foreach ($indices as $index) {
 298                                 $appliedAnyChange = true;
 299
 300                                 $this->prepareIndexLog($tableName, $index);
 301                                 $this->addIndex($tableName, $index);
 302                                 $this->finalizeIndexLog($tableName, $index);
 303                         }
 304                 }
 305
 306                 if ($appliedAnyChange) {
 307                         throw new SplitNodeException($this->splitNodeMessage);
 308                 }
 309         }
 310
 311         /**
 312          * Adds, alters, and drop columns of the same table.
 313          *
 314          * Before a column is dropped, all of its foreign keys are dropped.
 315          *
 316          * @param       string                  $tableName
 317          * @param       IDatabaseTableColumn[]  $addedColumns
 318          * @param       IDatabaseTableColumn[]  $alteredColumns
 319          * @param       IDatabaseTableColumn[]  $droppedColumns
 320          */
 321         protected function applyColumnChanges($tableName, array $addedColumns, array $alteredColumns, array $droppedColumns) {
 322                 $dropForeignKeys = [];
 323
 324                 $columnData = [];
 325                 foreach ($droppedColumns as $droppedColumn) {
 326                         $columnData[$droppedColumn->getName()] = [
 327                                 'action' => 'drop'
 328                         ];
 329
 330                         foreach ($this->getExistingTable($tableName)->getForeignKeys() as $foreignKey) {
 331                                 if (in_array($droppedColumn->getName(), $foreignKey->getColumns())) {
 332                                         $dropForeignKeys[] = $foreignKey;
 333                                 }
 334                         }
 335                 }
 336                 foreach ($addedColumns as $addedColumn) {
 337                         $columnData[$addedColumn->getName()] = [
 338                                 'action' => 'add',
 339                                 'data' => $addedColumn->getData()
 340                         ];
 341                 }
 342                 foreach ($alteredColumns as $alteredColumn) {
 343                         $columnData[$alteredColumn->getName()] = [
 344                                 'action' => 'alter',
 345                                 'data' => $alteredColumn->getData(),
 346                                 'oldColumnName' => $alteredColumn->getName()
 347                         ];
 348                 }
 349
 350                 if (!empty($columnData)) {
 351                         foreach ($dropForeignKeys as $foreignKey) {
 352                                 $this->dropForeignKey($tableName, $foreignKey);
 353                                 $this->deleteForeignKeyLog($tableName, $foreignKey);
 354                         }
 355
 356                         $this->dbEditor->alterColumns($tableName, $columnData);
 357                 }
 358         }
 359
 360         /**
 361          * Calculates all of the necessary changes to be executed.
 362          */
 363         protected function calculateChanges() {
 364                 foreach ($this->tables as $table) {
 365                         $tableName = $table->getName();
 366
 367                         if ($table->willBeDropped()) {
 368                                 if (in_array($tableName, $this->existingTableNames)) {
 369                                         $this->tablesToDrop[] = $table;
 370
 371                                         $this->splitNodeMessage .= "Dropped table '{$tableName}'.";
 372                                         break;
 373                                 }
 374                                 else if (isset($this->tablePackageIDs[$tableName])) {
 375                                         $this->deleteTableLog($table);
 376                                 }
 377                         }
 378                         else if (!in_array($tableName, $this->existingTableNames)) {
 379                                 if ($table instanceof PartialDatabaseTable) {
 380                                         throw new \LogicException("Partial table '{$tableName}' cannot be created.");
 381                                 }
 382
 383                                 $this->tablesToCreate[] = $table;
 384
 385                                 $this->splitNodeMessage .= "Created table '{$tableName}'.";
 386                                 break;
 387                         }
 388                         else {
 389                                 // calculate difference between tables
 390                                 $existingTable = $this->getExistingTable($tableName);
 391                                 $existingColumns = $existingTable->getColumns();
 392
 393                                 foreach ($table->getColumns() as $column) {
 394                                         if ($column->willBeDropped()) {
 395                                                 if (isset($existingColumns[$column->getName()])) {
 396                                                         if (!isset($this->columnsToDrop[$tableName])) {
 397                                                                 $this->columnsToDrop[$tableName] = [];
 398                                                         }
 399                                                         $this->columnsToDrop[$tableName][] = $column;
 400                                                 }
 401                                                 else if (isset($this->columnPackageIDs[$tableName][$column->getName()])) {
 402                                                         $this->deleteColumnLog($tableName, $column);
 403                                                 }
 404                                         }
 405                                         else if (!isset($existingColumns[$column->getName()])) {
 406                                                 if (!isset($this->columnsToAdd[$tableName])) {
 407                                                         $this->columnsToAdd[$tableName] = [];
 408                                                 }
 409                                                 $this->columnsToAdd[$tableName][] = $column;
 410                                         }
 411                                         else if ($this->diffColumns($existingColumns[$column->getName()], $column)) {
 412                                                 if (!isset($this->columnsToAlter[$tableName])) {
 413                                                         $this->columnsToAlter[$tableName] = [];
 414                                                 }
 415                                                 $this->columnsToAlter[$tableName][] = $column;
 416                                         }
 417                                 }
 418
 419                                 // all column-related changes are executed in one query thus break
 420                                 // here and not within the previous loop
 421                                 if (!empty($this->columnsToAdd) || !empty($this->columnsToAlter) || !empty($this->columnsToDrop)) {
 422                                         $this->splitNodeMessage .= "Altered columns of table '{$tableName}'.";
 423                                         break;
 424                                 }
 425
 426                                 $existingForeignKeys = $existingTable->getForeignKeys();
 427                                 foreach ($table->getForeignKeys() as $foreignKey) {
 428                                         $matchingExistingForeignKey = null;
 429                                         foreach ($existingForeignKeys as $existingForeignKey) {
 430                                                 if (empty(array_diff($foreignKey->getDiffData(), $existingForeignKey->getDiffData()))) {
 431                                                         $matchingExistingForeignKey = $existingForeignKey;
 432                                                         break;
 433                                                 }
 434                                         }
 435
 436                                         if ($foreignKey->willBeDropped()) {
 437                                                 if ($matchingExistingForeignKey !== null) {
 438                                                         if (!isset($this->foreignKeysToDrop[$tableName])) {
 439                                                                 $this->foreignKeysToDrop[$tableName] = [];
 440                                                         }
 441                                                         $this->foreignKeysToDrop[$tableName][] = $foreignKey;
 442
 443                                                         $this->splitNodeMessage .= "Dropped foreign key '{$tableName}." . implode(',', $foreignKey->getColumns()) . "'.";
 444                                                         break 2;
 445                                                 }
 446                                                 else if (isset($this->foreignKeyPackageIDs[$tableName][$foreignKey->getName()])) {
 447                                                         $this->deleteForeignKeyLog($tableName, $foreignKey);
 448                                                 }
 449                                         }
 450                                         else if ($matchingExistingForeignKey === null) {
 451                                                 if (!isset($this->foreignKeysToAdd[$tableName])) {
 452                                                         $this->foreignKeysToAdd[$tableName] = [];
 453                                                 }
 454                                                 $this->foreignKeysToAdd[$tableName][] = $foreignKey;
 455
 456                                                 $this->splitNodeMessage .= "Added foreign key '{$tableName}." . implode(',', $foreignKey->getColumns()) . "'.";
 457                                                 break 2;
 458                                         }
 459                                         else if (!empty(array_diff($foreignKey->getData(), $matchingExistingForeignKey->getData()))) {
 460                                                 if (!isset($this->foreignKeysToDrop[$tableName])) {
 461                                                         $this->foreignKeysToDrop[$tableName] = [];
 462                                                 }
 463                                                 $this->foreignKeysToDrop[$tableName][] = $matchingExistingForeignKey;
 464
 465                                                 if (!isset($this->foreignKeysToAdd[$tableName])) {
 466                                                         $this->foreignKeysToAdd[$tableName] = [];
 467                                                 }
 468                                                 $this->foreignKeysToAdd[$tableName][] = $foreignKey;
 469
 470                                                 $this->splitNodeMessage .= "Replaced foreign key '{$tableName}." . implode(',', $foreignKey->getColumns()) . "'.";
 471                                                 break 2;
 472                                         }
 473                                 }
 474
 475                                 $existingIndices = $existingTable->getIndices();
 476                                 foreach ($table->getIndices() as $index) {
 477                                         $matchingExistingIndex = null;
 478                                         foreach ($existingIndices as $existingIndex) {
 479                                                 if (!$this->diffIndices($existingIndex, $index)) {
 480                                                         $matchingExistingIndex = $existingIndex;
 481                                                         break;
 482                                                 }
 483                                         }
 484
 485                                         if ($index->willBeDropped()) {
 486                                                 if ($matchingExistingIndex !== null) {
 487                                                         if (!isset($this->indicesToDrop[$tableName])) {
 488                                                                 $this->indicesToDrop[$tableName] = [];
 489                                                         }
 490                                                         $this->indicesToDrop[$tableName][] = $matchingExistingIndex;
 491
 492                                                         $this->splitNodeMessage .= "Dropped index '{$tableName}." . implode(',', $index->getColumns()) . "'.";
 493                                                         break 2;
 494                                                 }
 495                                                 else if (isset($this->indexPackageIDs[$tableName][$index->getName()])) {
 496                                                         $this->deleteIndexLog($tableName, $index);
 497                                                 }
 498                                         }
 499                                         else if ($matchingExistingIndex !== null) {
 500                                                 // updating index type and index columns is supported with an
 501                                                 // explicit index name is given (automatically generated index
 502                                                 // names are not deterministic)
 503                                                 if (!$index->hasGeneratedName() && !empty(array_diff($matchingExistingIndex->getData(), $index->getData()))) {
 504                                                         if (!isset($this->indicesToDrop[$tableName])) {
 505                                                                 $this->indicesToDrop[$tableName] = [];
 506                                                         }
 507                                                         $this->indicesToDrop[$tableName][] = $matchingExistingIndex;
 508
 509                                                         if (!isset($this->indicesToAdd[$tableName])) {
 510                                                                 $this->indicesToAdd[$tableName] = [];
 511                                                         }
 512                                                         $this->indicesToAdd[$tableName][] = $index;
 513                                                 }
 514                                         }
 515                                         else {
 516                                                 if (!isset($this->indicesToAdd[$tableName])) {
 517                                                         $this->indicesToAdd[$tableName] = [];
 518                                                 }
 519                                                 $this->indicesToAdd[$tableName][] = $index;
 520
 521                                                 $this->splitNodeMessage .= "Added index '{$tableName}." . implode(',', $index->getColumns()) . "'.";
 522                                                 break 2;
 523                                         }
 524                                 }
 525                         }
 526                 }
 527         }
 528
 529         /**
 530          * Checks for any pending log entries for the package and either marks them as done or
 531          * deletes them so that after this method finishes, there are no more undone log entries
 532          * for the package.
 533          */
 534         protected function checkPendingLogEntries() {
 535                 $sql = "SELECT  *
 536                         FROM    wcf" . WCF_N . "_package_installation_sql_log
 537                         WHERE   packageID = ?
 538                                 AND isDone = ?";
 539                 $statement = WCF::getDB()->prepareStatement($sql);
 540                 $statement->execute([$this->package->packageID, 0]);
 541
 542                 $doneEntries = $undoneEntries = [];
 543                 while ($row = $statement->fetchArray()) {
 544                         // table
 545                         if ($row['sqlIndex'] === '' && $row['sqlColumn'] === '') {
 546                                 if (in_array($row['sqlTable'], $this->existingTableNames)) {
 547                                         $doneEntries[] = $row;
 548                                 }
 549                                 else {
 550                                         $undoneEntries[] = $row;
 551                                 }
 552                         }
 553                         // column
 554                         else if ($row['sqlIndex'] === '') {
 555                                 if (isset($this->getExistingTable($row['sqlTable'])->getColumns()[$row['sqlColumn']])) {
 556                                         $doneEntries[] = $row;
 557                                 }
 558                                 else {
 559                                         $undoneEntries[] = $row;
 560                                 }
 561                         }
 562                         // foreign key
 563                         else if (substr($row['sqlIndex'], -3) === '_fk') {
 564                                 if (isset($this->getExistingTable($row['sqlTable'])->getForeignKeys()[$row['sqlIndex']])) {
 565                                         $doneEntries[] = $row;
 566                                 }
 567                                 else {
 568                                         $undoneEntries[] = $row;
 569                                 }
 570                         }
 571                         // index
 572                         else {
 573                                 if (isset($this->getExistingTable($row['sqlTable'])->getIndices()[$row['sqlIndex']])) {
 574                                         $doneEntries[] = $row;
 575                                 }
 576                                 else {
 577                                         $undoneEntries[] = $row;
 578                                 }
 579                         }
 580                 }
 581
 582                 WCF::getDB()->beginTransaction();
 583                 foreach ($doneEntries as $entry) {
 584                         $this->finalizeLog($entry);
 585                 }
 586
 587                 // to achieve a consistent state, undone log entries will be deleted here even though
 588                 // they might be re-created later to ensure that after this method finishes, there are
 589                 // no more undone entries in the log for the relevant package
 590                 foreach ($undoneEntries as $entry) {
 591                         $this->deleteLog($entry);
 592                 }
 593                 WCF::getDB()->commitTransaction();
 594         }
 595
 596         /**
 597          * Creates a done log entry for the given foreign key.
 598          *
 599          * @param       string                          $tableName
 600          * @param       DatabaseTableForeignKey         $foreignKey
 601          */
 602         protected function createForeignKeyLog($tableName, DatabaseTableForeignKey $foreignKey) {
 603                 $sql = "INSERT INTO     wcf" . WCF_N . "_package_installation_sql_log
 604                                         (packageID, sqlTable, sqlIndex, isDone)
 605                         VALUES          (?, ?, ?, ?)";
 606                 $statement = WCF::getDB()->prepareStatement($sql);
 607
 608                 $statement->execute([
 609                         $this->package->packageID,
 610                         $tableName,
 611                         $foreignKey->getName(),
 612                         1
 613                 ]);
 614         }
 615
 616         /**
 617          * Creates the given table.
 618          *
 619          * @param       DatabaseTable           $table
 620          */
 621         protected function createTable(DatabaseTable $table) {
 622                 $hasPrimaryKey = false;
 623                 $columnData = array_map(function(IDatabaseTableColumn $column) use (&$hasPrimaryKey) {
 624                         $data = $column->getData();
 625                         if (isset($data['key']) && $data['key'] === 'PRIMARY') {
 626                                 $hasPrimaryKey = true;
 627                         }
 628
 629                         return [
 630                                 'data' => $data,
 631                                 'name' => $column->getName()
 632                         ];
 633                 }, $table->getColumns());
 634                 $indexData = array_map(function(DatabaseTableIndex $index) {
 635                         return [
 636                                 'data' => $index->getData(),
 637                                 'name' => $index->getName()
 638                         ];
 639                 }, $table->getIndices());
 640
 641                 // Auto columns are implicitly defined as the primary key by MySQL.
 642                 if ($hasPrimaryKey) {
 643                         $indexData = array_filter($indexData, function($key) {
 644                                 return $key !== 'PRIMARY';
 645                         }, ARRAY_FILTER_USE_KEY);
 646                 }
 647
 648                 $this->dbEditor->createTable($table->getName(), $columnData, $indexData);
 649
 650                 foreach ($table->getForeignKeys() as $foreignKey) {
 651                         $this->dbEditor->addForeignKey($table->getName(), $foreignKey->getName(), $foreignKey->getData());
 652
 653                         // foreign keys need to be explicitly logged for proper uninstallation
 654                         $this->createForeignKeyLog($table->getName(), $foreignKey);
 655                 }
 656         }
 657
 658         /**
 659          * Deletes the log entry for the given column.
 660          *
 661          * @param       string                          $tableName
 662          * @param       IDatabaseTableColumn            $column
 663          */
 664         protected function deleteColumnLog($tableName, IDatabaseTableColumn $column) {
 665                 $this->deleteLog(['sqlTable' => $tableName, 'sqlColumn' => $column->getName()]);
 666         }
 667
 668         /**
 669          * Deletes the log entry for the given foreign key.
 670          *
 671          * @param       string                          $tableName
 672          * @param       DatabaseTableForeignKey         $foreignKey
 673          */
 674         protected function deleteForeignKeyLog($tableName, DatabaseTableForeignKey $foreignKey) {
 675                 $this->deleteLog(['sqlTable' => $tableName, 'sqlIndex' => $foreignKey->getName()]);
 676         }
 677
 678         /**
 679          * Deletes the log entry for the given index.
 680          *
 681          * @param       string                  $tableName
 682          * @param       DatabaseTableIndex      $index
 683          */
 684         protected function deleteIndexLog($tableName, DatabaseTableIndex $index) {
 685                 $this->deleteLog(['sqlTable' => $tableName, 'sqlIndex' => $index->getName()]);
 686         }
 687
 688         /**
 689          * Deletes a log entry.
 690          *
 691          * @param       array   $data
 692          */
 693         protected function deleteLog(array $data) {
 694                 $sql = "DELETE FROM     wcf" . WCF_N . "_package_installation_sql_log
 695                         WHERE           packageID = ?
 696                                         AND sqlTable = ?
 697                                         AND sqlColumn = ?
 698                                         AND sqlIndex = ?";
 699                 $statement = WCF::getDB()->prepareStatement($sql);
 700
 701                 $statement->execute([
 702                         $this->package->packageID,
 703                         $data['sqlTable'],
 704                         $data['sqlColumn'] ?? '',
 705                         $data['sqlIndex'] ?? ''
 706                 ]);
 707         }
 708
 709         /**
 710          * Deletes all log entry related to the given table.
 711          *
 712          * @param       DatabaseTable   $table
 713          */
 714         protected function deleteTableLog(DatabaseTable $table) {
 715                 $sql = "DELETE FROM     wcf" . WCF_N . "_package_installation_sql_log
 716                         WHERE           packageID = ?
 717                                         AND sqlTable = ?";
 718                 $statement = WCF::getDB()->prepareStatement($sql);
 719
 720                 $statement->execute([
 721                         $this->package->packageID,
 722                         $table->getName()
 723                 ]);
 724         }
 725
 726         /**
 727          * Returns `true` if the two columns differ.
 728          *
 729          * @param       IDatabaseTableColumn    $oldColumn
 730          * @param       IDatabaseTableColumn    $newColumn
 731          * @return      bool
 732          */
 733         protected function diffColumns(IDatabaseTableColumn $oldColumn, IDatabaseTableColumn $newColumn) {
 734                 $diff = array_diff($oldColumn->getData(), $newColumn->getData());
 735                 if (!empty($diff)) {
 736                         // see https://github.com/WoltLab/WCF/pull/3167
 737                         if (
 738                                 array_key_exists('length', $diff)
 739                                 && $oldColumn instanceof AbstractIntDatabaseTableColumn
 740                                 && (
 741                                         !($oldColumn instanceof TinyintDatabaseTableColumn)
 742                                         || $oldColumn->getLength() != 1
 743                                 )
 744                         ) {
 745                                 unset($diff['length']);
 746                         }
 747
 748                         if (!empty($diff)) {
 749                                 return true;
 750                         }
 751                 }
 752
 753                 // default type has to be checked explicitly for `null` to properly detect changing
 754                 // from no default value (`null`) and to an empty string as default value (and vice
 755                 // versa)
 756                 if ($oldColumn->getDefaultValue() === null || $newColumn->getDefaultValue() === null) {
 757                         return $oldColumn->getDefaultValue() !== $newColumn->getDefaultValue();
 758                 }
 759
 760                 // for all other cases, use weak comparison so that `'1'` (from database) and `1`
 761                 // (from script PIP) match, for example
 762                 return $oldColumn->getDefaultValue() != $newColumn->getDefaultValue();
 763         }
 764
 765         /**
 766          * Returns `true` if the two indices differ.
 767          *
 768          * @param       DatabaseTableIndex      $oldIndex
 769          * @param       DatabaseTableIndex      $newIndex
 770          * @return      bool
 771          */
 772         protected function diffIndices(DatabaseTableIndex $oldIndex, DatabaseTableIndex $newIndex) {
 773                 if ($newIndex->hasGeneratedName()) {
 774                         return !empty(array_diff($oldIndex->getData(), $newIndex->getData()));
 775                 }
 776
 777                 return $oldIndex->getName() !== $newIndex->getName();
 778         }
 779
 780         /**
 781          * Drops the given foreign key.
 782          *
 783          * @param       string                          $tableName
 784          * @param       DatabaseTableForeignKey         $foreignKey
 785          */
 786         protected function dropForeignKey($tableName, DatabaseTableForeignKey $foreignKey) {
 787                 $this->dbEditor->dropForeignKey($tableName, $foreignKey->getName());
 788                 $this->dbEditor->dropIndex($tableName, $foreignKey->getName());
 789         }
 790
 791         /**
 792          * Drops the given index.
 793          *
 794          * @param       string                  $tableName
 795          * @param       DatabaseTableIndex      $index
 796          */
 797         protected function dropIndex($tableName, DatabaseTableIndex $index) {
 798                 $this->dbEditor->dropIndex($tableName, $index->getName());
 799         }
 800
 801         /**
 802          * Drops the given table.
 803          *
 804          * @param       DatabaseTable           $table
 805          */
 806         protected function dropTable(DatabaseTable $table) {
 807                 $this->dbEditor->dropTable($table->getName());
 808         }
 809
 810         /**
 811          * Finalizes the log entry for the creation of the given column.
 812          *
 813          * @param       string                  $tableName
 814          * @param       IDatabaseTableColumn    $column
 815          */
 816         protected function finalizeColumnLog($tableName, IDatabaseTableColumn $column) {
 817                 $this->finalizeLog(['sqlTable' => $tableName, 'sqlColumn' => $column->getName()]);
 818         }
 819
 820         /**
 821          * Finalizes the log entry for adding the given index.
 822          *
 823          * @param       string                          $tableName
 824          * @param       DatabaseTableForeignKey         $foreignKey
 825          */
 826         protected function finalizeForeignKeyLog($tableName, DatabaseTableForeignKey $foreignKey) {
 827                 $this->finalizeLog(['sqlTable' => $tableName, 'sqlIndex' => $foreignKey->getName()]);
 828         }
 829
 830         /**
 831          * Finalizes the log entry for adding the given index.
 832          *
 833          * @param       string                  $tableName
 834          * @param       DatabaseTableIndex      $index
 835          */
 836         protected function finalizeIndexLog($tableName, DatabaseTableIndex $index) {
 837                 $this->finalizeLog(['sqlTable' => $tableName, 'sqlIndex' => $index->getName()]);
 838         }
 839
 840         /**
 841          * Finalizes a log entry after the relevant change has been executed.
 842          *
 843          * @param       array   $data
 844          */
 845         protected function finalizeLog(array $data) {
 846                 $sql = "UPDATE  wcf" . WCF_N . "_package_installation_sql_log
 847                         SET     isDone = ?
 848                         WHERE   packageID = ?
 849                                 AND sqlTable = ?
 850                                 AND sqlColumn = ?
 851                                 AND sqlIndex = ?";
 852                 $statement = WCF::getDB()->prepareStatement($sql);
 853
 854                 $statement->execute([
 855                         1,
 856                         $this->package->packageID,
 857                         $data['sqlTable'],
 858                         $data['sqlColumn'] ?? '',
 859                         $data['sqlIndex'] ?? ''
 860                 ]);
 861         }
 862
 863         /**
 864          * Finalizes the log entry for the creation of the given table.
 865          *
 866          * @param       DatabaseTable   $table
 867          */
 868         protected function finalizeTableLog(DatabaseTable $table) {
 869                 $this->finalizeLog(['sqlTable' => $table->getName()]);
 870         }
 871
 872         /**
 873          * Returns the id of the package to with the given column belongs to. If there is no specific
 874          * log entry for the given column, the table log is checked and the relevant package id of
 875          * the whole table is returned. If the package of the table is also unknown, `null` is returned.
 876          *
 877          * @param       DatabaseTable           $table
 878          * @param       IDatabaseTableColumn    $column
 879          * @return      null|int
 880          */
 881         protected function getColumnPackageID(DatabaseTable $table, IDatabaseTableColumn $column) {
 882                 if (isset($this->columnPackageIDs[$table->getName()][$column->getName()])) {
 883                         return $this->columnPackageIDs[$table->getName()][$column->getName()];
 884                 }
 885                 else if (isset($this->tablePackageIDs[$table->getName()])) {
 886                         return $this->tablePackageIDs[$table->getName()];
 887                 }
 888
 889                 return null;
 890         }
 891
 892         /**
 893          * Returns the `DatabaseTable` object for the table with the given name.
 894          *
 895          * @param       string          $tableName
 896          * @return      DatabaseTable
 897          */
 898         protected function getExistingTable($tableName) {
 899                 if (!isset($this->existingTables[$tableName])) {
 900                         $this->existingTables[$tableName] = DatabaseTable::createFromExistingTable($this->dbEditor, $tableName);
 901                 }
 902
 903                 return $this->existingTables[$tableName];
 904         }
 905
 906         /**
 907          * Returns the id of the package to with the given foreign key belongs to. If there is no specific
 908          * log entry for the given foreign key, the table log is checked and the relevant package id of
 909          * the whole table is returned. If the package of the table is also unknown, `null` is returned.
 910          *
 911          * @param       DatabaseTable                   $table
 912          * @param       DatabaseTableForeignKey         $foreignKey
 913          * @return      null|int
 914          */
 915         protected function getForeignKeyPackageID(DatabaseTable $table, DatabaseTableForeignKey $foreignKey) {
 916                 if (isset($this->foreignKeyPackageIDs[$table->getName()][$foreignKey->getName()])) {
 917                         return $this->foreignKeyPackageIDs[$table->getName()][$foreignKey->getName()];
 918                 }
 919                 else if (isset($this->tablePackageIDs[$table->getName()])) {
 920                         return $this->tablePackageIDs[$table->getName()];
 921                 }
 922
 923                 return null;
 924         }
 925
 926         /**
 927          * Returns the id of the package to with the given index belongs to. If there is no specific
 928          * log entry for the given index, the table log is checked and the relevant package id of
 929          * the whole table is returned. If the package of the table is also unknown, `null` is returned.
 930          *
 931          * @param       DatabaseTable           $table
 932          * @param       DatabaseTableIndex      $index
 933          * @return      null|int
 934          */
 935         protected function getIndexPackageID(DatabaseTable $table, DatabaseTableIndex $index) {
 936                 if (isset($this->indexPackageIDs[$table->getName()][$index->getName()])) {
 937                         return $this->indexPackageIDs[$table->getName()][$index->getName()];
 938                 }
 939                 else if (isset($this->tablePackageIDs[$table->getName()])) {
 940                         return $this->tablePackageIDs[$table->getName()];
 941                 }
 942
 943                 return null;
 944         }
 945
 946         /**
 947          * Prepares the log entry for the creation of the given column.
 948          *
 949          * @param       string                  $tableName
 950          * @param       IDatabaseTableColumn    $column
 951          */
 952         protected function prepareColumnLog($tableName, IDatabaseTableColumn $column) {
 953                 $this->prepareLog(['sqlTable' => $tableName, 'sqlColumn' => $column->getName()]);
 954         }
 955
 956         /**
 957          * Prepares the log entry for adding the given foreign key.
 958          *
 959          * @param       string                          $tableName
 960          * @param       DatabaseTableForeignKey         $foreignKey
 961          */
 962         protected function prepareForeignKeyLog($tableName, DatabaseTableForeignKey $foreignKey) {
 963                 $this->prepareLog(['sqlTable' => $tableName, 'sqlIndex' => $foreignKey->getName()]);
 964         }
 965
 966         /**
 967          * Prepares the log entry for adding the given index.
 968          *
 969          * @param       string                  $tableName
 970          * @param       DatabaseTableIndex      $index
 971          */
 972         protected function prepareIndexLog($tableName, DatabaseTableIndex $index) {
 973                 $this->prepareLog(['sqlTable' => $tableName, 'sqlIndex' => $index->getName()]);
 974         }
 975
 976         /**
 977          * Prepares a log entry before the relevant change has been executed.
 978          *
 979          * @param       array   $data
 980          */
 981         protected function prepareLog(array $data) {
 982                 $sql = "INSERT INTO     wcf" . WCF_N . "_package_installation_sql_log
 983                                         (packageID, sqlTable, sqlColumn, sqlIndex, isDone)
 984                         VALUES          (?, ?, ?, ?, ?)";
 985                 $statement = WCF::getDB()->prepareStatement($sql);
 986
 987                 $statement->execute([
 988                         $this->package->packageID,
 989                         $data['sqlTable'],
 990                         $data['sqlColumn'] ?? '',
 991                         $data['sqlIndex'] ?? '',
 992                         0
 993                 ]);
 994         }
 995
 996         /**
 997          * Prepares the log entry for the creation of the given table.
 998          *
 999          * @param       DatabaseTable   $table
1000          */
1001         protected function prepareTableLog(DatabaseTable $table) {
1002                 $this->prepareLog(['sqlTable' => $table->getName()]);
1003         }
1004
1005         /**
1006          * Processes all tables and updates the current table layouts to match the specified layouts.
1007          *
1008          * @throws      \RuntimeException       if validation of the required layout changes fails
1009          */
1010         public function process() {
1011                 $this->checkPendingLogEntries();
1012
1013                 $errors = $this->validate();
1014                 if (!empty($errors)) {
1015                         throw new \RuntimeException(WCF::getLanguage()->getDynamicVariable('wcf.acp.package.error.databaseChange', [
1016                                 'errors' => $errors
1017                         ]));
1018                 }
1019
1020                 $this->calculateChanges();
1021
1022                 $this->applyChanges();
1023         }
1024
1025         /**
1026          * Checks if the relevant table layout changes can be executed and returns an array with information
1027          * on all validation errors.
1028          *
1029          * @return      array
1030          */
1031         public function validate() {
1032                 $errors = [];
1033                 foreach ($this->tables as $table) {
1034                         if ($table->willBeDropped()) {
1035                                 if (in_array($table->getName(), $this->existingTableNames)) {
1036                                         if (!isset($this->tablePackageIDs[$table->getName()])) {
1037                                                 $errors[] = [
1038                                                         'tableName' => $table->getName(),
1039                                                         'type' => 'unregisteredTableDrop'
1040                                                 ];
1041                                         }
1042                                         else if ($this->tablePackageIDs[$table->getName()] !== $this->package->packageID) {
1043                                                 $errors[] = [
1044                                                         'tableName' => $table->getName(),
1045                                                         'type' => 'foreignTableDrop'
1046                                                 ];
1047                                         }
1048                                 }
1049                         }
1050                         else {
1051                                 $existingTable = null;
1052                                 if (in_array($table->getName(), $this->existingTableNames)) {
1053                                         if (!isset($this->tablePackageIDs[$table->getName()])) {
1054                                                 $errors[] = [
1055                                                         'tableName' => $table->getName(),
1056                                                         'type' => 'unregisteredTableChange',
1057                                                 ];
1058                                         }
1059                                         else {
1060                                                 $existingTable = DatabaseTable::createFromExistingTable($this->dbEditor, $table->getName());
1061                                                 $existingColumns = $existingTable->getColumns();
1062                                                 $existingIndices = $existingTable->getIndices();
1063                                                 $existingForeignKeys = $existingTable->getForeignKeys();
1064
1065                                                 foreach ($table->getColumns() as $column) {
1066                                                         if (isset($existingColumns[$column->getName()])) {
1067                                                                 $columnPackageID = $this->getColumnPackageID($table, $column);
1068                                                                 if ($column->willBeDropped()) {
1069                                                                         if ($columnPackageID !== $this->package->packageID) {
1070                                                                                 $errors[] = [
1071                                                                                         'columnName' => $column->getName(),
1072                                                                                         'tableName' => $table->getName(),
1073                                                                                         'type' => 'foreignColumnDrop',
1074                                                                                 ];
1075                                                                         }
1076                                                                 }
1077                                                                 else if ($columnPackageID !== $this->package->packageID) {
1078                                                                         $errors[] = [
1079                                                                                 'columnName' => $column->getName(),
1080                                                                                 'tableName' => $table->getName(),
1081                                                                                 'type' => 'foreignColumnChange',
1082                                                                         ];
1083                                                                 }
1084                                                         }
1085                                                 }
1086
1087                                                 foreach ($table->getIndices() as $index) {
1088                                                         foreach ($existingIndices as $existingIndex) {
1089                                                                 if (empty(array_diff($index->getData(), $existingIndex->getData()))) {
1090                                                                         if ($index->willBeDropped()) {
1091                                                                                 if ($this->getIndexPackageID($table, $index) !== $this->package->packageID) {
1092                                                                                         $errors[] = [
1093                                                                                                 'columnNames' => implode(',', $existingIndex->getColumns()),
1094                                                                                                 'tableName' => $table->getName(),
1095                                                                                                 'type' => 'foreignIndexDrop',
1096                                                                                         ];
1097                                                                                 }
1098                                                                         }
1099
1100                                                                         continue 2;
1101                                                                 }
1102                                                         }
1103                                                 }
1104
1105                                                 foreach ($table->getForeignKeys() as $foreignKey) {
1106                                                         foreach ($existingForeignKeys as $existingForeignKey) {
1107                                                                 if (empty(array_diff($foreignKey->getData(), $existingForeignKey->getData()))) {
1108                                                                         if ($foreignKey->willBeDropped()) {
1109                                                                                 if ($this->getForeignKeyPackageID($table, $foreignKey) !== $this->package->packageID) {
1110                                                                                         $errors[] = [
1111                                                                                                 'columnNames' => implode(',', $existingForeignKey->getColumns()),
1112                                                                                                 'tableName' => $table->getName(),
1113                                                                                                 'type' => 'foreignForeignKeyDrop',
1114                                                                                         ];
1115                                                                                 }
1116                                                                         }
1117
1118                                                                         continue 2;
1119                                                                 }
1120                                                         }
1121                                                 }
1122                                         }
1123                                 }
1124
1125                                 foreach ($table->getIndices() as $index) {
1126                                         foreach ($index->getColumns() as $indexColumn) {
1127                                                 $column = $this->getColumnByName($indexColumn, $table, $existingTable);
1128                                                 if ($column === null) {
1129                                                         if (!$index->willBeDropped()) {
1130                                                                 $errors[] = [
1131                                                                         'columnName' => $indexColumn,
1132                                                                         'columnNames' => implode(',', $index->getColumns()),
1133                                                                         'tableName' => $table->getName(),
1134                                                                         'type' => 'nonexistingColumnInIndex',
1135                                                                 ];
1136                                                         }
1137                                                 }
1138                                                 else if (
1139                                                         $index->getType() === DatabaseTableIndex::PRIMARY_TYPE
1140                                                         && !$index->willBeDropped()
1141                                                         && !$column->isNotNull()
1142                                                 ) {
1143                                                         $errors[] = [
1144                                                                 'columnName' => $indexColumn,
1145                                                                 'columnNames' => implode(',', $index->getColumns()),
1146                                                                 'tableName' => $table->getName(),
1147                                                                 'type' => 'nullColumnInPrimaryIndex',
1148                                                         ];
1149                                                 }
1150                                         }
1151                                 }
1152
1153                                 foreach ($table->getForeignKeys() as $foreignKey) {
1154                                         $referencedTableExists = in_array($foreignKey->getReferencedTable(), $this->existingTableNames);
1155                                         foreach ($this->tables as $processedTable) {
1156                                                 if ($processedTable->getName() === $foreignKey->getReferencedTable()) {
1157                                                         $referencedTableExists = !$processedTable->willBeDropped();
1158                                                 }
1159                                         }
1160
1161                                         if (!$referencedTableExists) {
1162                                                 $errors[] = [
1163                                                         'columnNames' => implode(',', $foreignKey->getColumns()),
1164                                                         'referencedTableName' => $foreignKey->getReferencedTable(),
1165                                                         'tableName' => $table->getName(),
1166                                                         'type' => 'unknownTableInForeignKey',
1167                                                 ];
1168                                         }
1169                                 }
1170                         }
1171                 }
1172
1173                 return $errors;
1174         }
1175
1176         /**
1177          * Returns the column with the given name from the given table.
1178          *
1179          * @param       string                  $columnName
1180          * @param       DatabaseTable           $updateTable
1181          * @param       DatabaseTable|null      $existingTable
1182          * @return      IDatabaseTableColumn|null
1183          * @since       5.2.10
1184          */
1185         protected function getColumnByName($columnName, DatabaseTable $updateTable, DatabaseTable $existingTable = null) {
1186                 foreach ($updateTable->getColumns() as $column) {
1187                         if ($column->getName() === $columnName) {
1188                                 return $column;
1189                         }
1190                 }
1191
1192                 if ($existingTable) {
1193                         foreach ($existingTable->getColumns() as $column) {
1194                                 if ($column->getName() === $columnName) {
1195                                         return $column;
1196                                 }
1197                         }
1198                 }
1199
1200                 return null;
1201         }
1202 }