DatabaseTableChangeProcessor.class.php

<?php

namespace wcf\system\database\table;

use wcf\data\package\Package;
use wcf\system\application\ApplicationHandler;
use wcf\system\database\editor\DatabaseEditor;
use wcf\system\database\table\column\AbstractIntDatabaseTableColumn;
use wcf\system\database\table\column\IDatabaseTableColumn;
use wcf\system\database\table\column\IDefaultValueDatabaseTableColumn;
use wcf\system\database\table\column\TinyintDatabaseTableColumn;
use wcf\system\database\table\column\YearDatabaseTableColumn;
use wcf\system\database\table\index\DatabaseTableForeignKey;
use wcf\system\database\table\index\DatabaseTableIndex;
use wcf\system\database\util\PreparedStatementConditionBuilder;
use wcf\system\package\SplitNodeException;
use wcf\system\WCF;

/**
 * Processes a given set of changes to database tables.
 *
 * @author  Matthias Schmidt
 * @copyright   2001-2020 WoltLab GmbH
 * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
 * @since   5.2
 */
final class DatabaseTableChangeProcessor
{
    /**
     * maps the registered database table column names to the ids of the packages they belong to
     * @var int[][]
     */
    private array $columnPackageIDs = [];

    /**
     * database table columns that will be added grouped by the name of the table to which they
     * will be added
     * @var IDatabaseTableColumn[][]
     */
    private array $columnsToAdd = [];

    /**
     * database table columns that will be altered grouped by the name of the table to which
     * they belong
     * @var IDatabaseTableColumn[][]
     */
    private array $columnsToAlter = [];

    /**
     * database table columns that will be dropped grouped by the name of the table from which
     * they will be dropped
     * @var IDatabaseTableColumn[][]
     */
    private array $columnsToDrop = [];

    /**
     * database editor to apply the relevant changes to the table layouts
     */
    private DatabaseEditor $dbEditor;

    /**
     * list of all existing tables in the used database
     * @var string[]
     */
    private array $existingTableNames = [];

    /**
     * existing database tables
     * @var DatabaseTable[]
     */
    private array $existingTables = [];

    /**
     * maps the registered database table index names to the ids of the packages they belong to
     * @var int[][]
     */
    private array $indexPackageIDs = [];

    /**
     * indices that will be added grouped by the name of the table to which they will be added
     * @var DatabaseTableIndex[][]
     */
    private array $indicesToAdd = [];

    /**
     * indices that will be dropped grouped by the name of the table from which they will be dropped
     * @var DatabaseTableIndex[][]
     */
    private array $indicesToDrop = [];

    /**
     * maps the registered database table foreign key names to the ids of the packages they belong to
     * @var int[][]
     */
    private array $foreignKeyPackageIDs = [];

    /**
     * foreign keys that will be added grouped by the name of the table to which they will be
     * added
     * @var DatabaseTableForeignKey[][]
     */
    private array $foreignKeysToAdd = [];

    /**
     * foreign keys that will be dropped grouped by the name of the table from which they will
     * be dropped
     * @var DatabaseTableForeignKey[][]
     */
    private array $foreignKeysToDrop = [];

    /**
     * package that wants to apply the changes
     */
    private Package $package;

    /**
     * message for the split node exception thrown after the changes have been applied
     */
    private string $splitNodeMessage = '';

    /**
     * layouts/layout changes of the relevant database table
     * @var DatabaseTable[]
     */
    private array $tables;

    /**
     * maps the registered database table names to the ids of the packages they belong to
     * @var int[]
     */
    private array $tablePackageIDs = [];

    /**
     * database table that will be created
     * @var DatabaseTable[]
     */
    private array $tablesToCreate = [];

    /**
     * database tables that will be dropped
     * @var DatabaseTable[]
     */
    private array $tablesToDrop = [];

    /**
     * database tables, that are unknown (but belongs theoretically to the WoltLab Suite)
     * and must be dropped before installation
     * @var DatabaseTable[]
     * @since 5.5
     */
    private array $tablesToCleanup = [];

    /**
     * Creates a new instance of `DatabaseTableChangeProcessor`.
     *
     * @param DatabaseTable[] $tables
     */
    public function __construct(Package $package, array $tables, DatabaseEditor $dbEditor)
    {
        $this->package = $package;

        $tableNames = [];
        foreach ($tables as $table) {
            if (!($table instanceof DatabaseTable)) {
                throw new \InvalidArgumentException("Tables must be instance of '" . DatabaseTable::class . "'");
            }

            $tableNames[] = $table->getName();
        }

        $this->tables = $tables;
        $this->dbEditor = $dbEditor;

        $this->existingTableNames = $dbEditor->getTableNames();

        $conditionBuilder = new PreparedStatementConditionBuilder();
        $conditionBuilder->add('sqlTable IN (?)', [$tableNames]);
        $conditionBuilder->add('isDone = ?', [1]);

        $sql = "SELECT  *
                FROM    wcf1_package_installation_sql_log
                {$conditionBuilder}";
        $statement = WCF::getDB()->prepare($sql);
        $statement->execute($conditionBuilder->getParameters());

        while ($row = $statement->fetchArray()) {
            if ($row['sqlIndex'] === '' && $row['sqlColumn'] === '') {
                $this->tablePackageIDs[$row['sqlTable']] = $row['packageID'];
            } elseif ($row['sqlIndex'] === '') {
                $this->columnPackageIDs[$row['sqlTable']][$row['sqlColumn']] = $row['packageID'];
            } elseif (\substr($row['sqlIndex'], -3) === '_fk') {
                $this->foreignKeyPackageIDs[$row['sqlTable']][$row['sqlIndex']] = $row['packageID'];
            } else {
                $this->indexPackageIDs[$row['sqlTable']][$row['sqlIndex']] = $row['packageID'];
            }
        }
    }

    /**
     * Adds the given index to the table.
     */
    private function addForeignKey(string $tableName, DatabaseTableForeignKey $foreignKey): void
    {
        $this->dbEditor->addForeignKey($tableName, $foreignKey->getName(), $foreignKey->getData());
    }

    /**
     * Adds the given index to the table.
     */
    private function addIndex(string $tableName, DatabaseTableIndex $index): void
    {
        $this->dbEditor->addIndex($tableName, $index->getName(), $index->getData());
    }

    /**
     * Applies all of the previously determined changes to achieve the desired database layout.
     *
     * @throws  SplitNodeException  if any change has been applied
     */
    private function applyChanges(): void
    {
        $appliedAnyChange = false;

        foreach ($this->tablesToCleanup as $table) {
            $this->dropTable($table);
        }

        foreach ($this->tablesToCreate as $table) {
            $appliedAnyChange = true;

            $this->prepareTableLog($table);
            $this->createTable($table);
            $this->finalizeTableLog($table);
        }

        foreach ($this->tablesToDrop as $table) {
            $appliedAnyChange = true;

            $this->dropTable($table);
            $this->deleteTableLog($table);
        }

        $columnTables = \array_unique(\array_merge(
            \array_keys($this->columnsToAdd),
            \array_keys($this->columnsToAlter),
            \array_keys($this->columnsToDrop)
        ));
        foreach ($columnTables as $tableName) {
            $appliedAnyChange = true;

            $columnsToAdd = $this->columnsToAdd[$tableName] ?? [];
            $columnsToAlter = $this->columnsToAlter[$tableName] ?? [];
            $columnsToDrop = $this->columnsToDrop[$tableName] ?? [];

            foreach ($columnsToAdd as $column) {
                $this->prepareColumnLog($tableName, $column);
            }

            $renamedColumnsWithLog = [];
            foreach ($columnsToAlter as $column) {
                if ($column->getNewName() && $this->getColumnLog($tableName, $column) !== null) {
                    $this->prepareColumnLog($tableName, $column, true);
                    $renamedColumnsWithLog[] = $column;
                }
            }

            $this->applyColumnChanges(
                $tableName,
                $columnsToAdd,
                $columnsToAlter,
                $columnsToDrop
            );

            foreach ($columnsToAdd as $column) {
                $this->finalizeColumnLog($tableName, $column);
            }

            foreach ($renamedColumnsWithLog as $column) {
                $this->finalizeColumnLog($tableName, $column, true);
                $this->deleteColumnLog($tableName, $column);
            }

            foreach ($columnsToDrop as $column) {
                $this->deleteColumnLog($tableName, $column);
            }
        }

        foreach ($this->foreignKeysToDrop as $tableName => $foreignKeys) {
            foreach ($foreignKeys as $foreignKey) {
                $appliedAnyChange = true;

                $this->dropForeignKey($tableName, $foreignKey);
                $this->deleteForeignKeyLog($tableName, $foreignKey);
            }
        }

        foreach ($this->foreignKeysToAdd as $tableName => $foreignKeys) {
            foreach ($foreignKeys as $foreignKey) {
                $appliedAnyChange = true;

                $this->prepareForeignKeyLog($tableName, $foreignKey);
                $this->addForeignKey($tableName, $foreignKey);
                $this->finalizeForeignKeyLog($tableName, $foreignKey);
            }
        }

        foreach ($this->indicesToDrop as $tableName => $indices) {
            foreach ($indices as $index) {
                $appliedAnyChange = true;

                $this->dropIndex($tableName, $index);
                $this->deleteIndexLog($tableName, $index);
            }
        }

        foreach ($this->indicesToAdd as $tableName => $indices) {
            foreach ($indices as $index) {
                $appliedAnyChange = true;

                $this->prepareIndexLog($tableName, $index);
                $this->addIndex($tableName, $index);
                $this->finalizeIndexLog($tableName, $index);
            }
        }

        if ($appliedAnyChange) {
            throw new SplitNodeException($this->splitNodeMessage);
        }
    }

    /**
     * Adds, alters, and drop columns of the same table.
     *
     * Before a column is dropped, all of its foreign keys are dropped.
     *
     * @param IDatabaseTableColumn[] $addedColumns
     * @param IDatabaseTableColumn[] $alteredColumns
     * @param IDatabaseTableColumn[] $droppedColumns
     */
    private function applyColumnChanges(
        string $tableName,
        array $addedColumns,
        array $alteredColumns,
        array $droppedColumns
    ): void {
        $dropForeignKeys = [];

        $columnData = [];
        foreach ($droppedColumns as $droppedColumn) {
            $columnData[$droppedColumn->getName()] = [
                'action' => 'drop',
            ];

            foreach ($this->getExistingTable($tableName)->getForeignKeys() as $foreignKey) {
                if (\in_array($droppedColumn->getName(), $foreignKey->getColumns())) {
                    $dropForeignKeys[] = $foreignKey;
                }
            }
        }
        foreach ($addedColumns as $addedColumn) {
            $columnData[$addedColumn->getName()] = [
                'action' => 'add',
                'data' => $addedColumn->getData(),
            ];
        }
        foreach ($alteredColumns as $alteredColumn) {
            $columnData[$alteredColumn->getName()] = [
                'action' => 'alter',
                'data' => $alteredColumn->getData(),
                'newColumnName' => $alteredColumn->getNewName() ?? $alteredColumn->getName(),
            ];
        }

        if ($columnData !== []) {
            foreach ($dropForeignKeys as $foreignKey) {
                $this->dropForeignKey($tableName, $foreignKey);
                $this->deleteForeignKeyLog($tableName, $foreignKey);
            }

            $this->dbEditor->alterColumns($tableName, $columnData);
        }
    }

    /**
     * Calculates all of the necessary changes to be executed.
     */
    private function calculateChanges(): void
    {
        foreach ($this->tables as $table) {
            $tableName = $table->getName();

            if ($table->willBeDropped()) {
                if (\in_array($tableName, $this->existingTableNames)) {
                    $this->tablesToDrop[] = $table;

                    $this->splitNodeMessage .= "Dropped table '{$tableName}'.";
                    break;
                } elseif (isset($this->tablePackageIDs[$tableName])) {
                    $this->deleteTableLog($table);
                }
            } elseif (
                \in_array($tableName, $this->existingTableNames)
                && !isset($this->tablePackageIDs[$table->getName()])
            ) {
                if ($table instanceof PartialDatabaseTable) {
                    throw new \LogicException("Partial table '{$tableName}' cannot be created (table exists but is unknown).");
                }

                // The table is currently unknown to the system and should be removed,
                // before it will be created again. This protect us from havely outdated
                // tables.
                $this->tablesToCleanup[] = $table;
                $this->splitNodeMessage .= "Clean up table '{$tableName}'.";

                $this->tablesToCreate[] = $table;
                $this->splitNodeMessage .= "Created table '{$tableName}'.";
            } elseif (!\in_array($tableName, $this->existingTableNames)) {
                if ($table instanceof PartialDatabaseTable) {
                    throw new \LogicException("Partial table '{$tableName}' cannot be created.");
                }

                $this->tablesToCreate[] = $table;

                $this->splitNodeMessage .= "Created table '{$tableName}'.";
                break;
            } else {
                // calculate difference between tables
                $existingTable = $this->getExistingTable($tableName);
                $existingColumns = $existingTable->getColumns();

                foreach ($table->getColumns() as $column) {
                    if ($column->willBeDropped()) {
                        if (isset($existingColumns[$column->getName()])) {
                            if (!isset($this->columnsToDrop[$tableName])) {
                                $this->columnsToDrop[$tableName] = [];
                            }
                            $this->columnsToDrop[$tableName][] = $column;
                        } elseif (isset($this->columnPackageIDs[$tableName][$column->getName()])) {
                            $this->deleteColumnLog($tableName, $column);
                        }
                    } elseif (!isset($existingColumns[$column->getName()])) {
                        // It was already checked in `validate()` that for renames, the column either
                        // exists with the old or new name.
                        if (!$column->getNewName()) {
                            if (!isset($this->columnsToAdd[$tableName])) {
                                $this->columnsToAdd[$tableName] = [];
                            }
                            $this->columnsToAdd[$tableName][] = $column;
                        }
                    } elseif ($this->diffColumns($existingColumns[$column->getName()], $column)) {
                        if (!isset($this->columnsToAlter[$tableName])) {
                            $this->columnsToAlter[$tableName] = [];
                        }
                        $this->columnsToAlter[$tableName][] = $column;
                    }
                }

                // all column-related changes are executed in one query thus break
                // here and not within the previous loop
                if ($this->columnsToAdd !== [] || $this->columnsToAlter !== [] || $this->columnsToDrop !== []) {
                    $this->splitNodeMessage .= "Altered columns of table '{$tableName}'.";
                    break;
                }

                $existingForeignKeys = $existingTable->getForeignKeys();
                foreach ($table->getForeignKeys() as $foreignKey) {
                    $matchingExistingForeignKey = null;
                    foreach ($existingForeignKeys as $existingForeignKey) {
                        if (\array_diff_assoc($foreignKey->getDiffData(), $existingForeignKey->getDiffData()) === []) {
                            $matchingExistingForeignKey = $existingForeignKey;
                            break;
                        }
                    }

                    if ($foreignKey->willBeDropped()) {
                        if ($matchingExistingForeignKey !== null) {
                            if (!isset($this->foreignKeysToDrop[$tableName])) {
                                $this->foreignKeysToDrop[$tableName] = [];
                            }
                            $this->foreignKeysToDrop[$tableName][] = $matchingExistingForeignKey;

                            $this->splitNodeMessage .= "Dropped foreign key '{$tableName}." . \implode(
                                ',',
                                $foreignKey->getColumns()
                            ) . "'.";
                            break 2;
                        } elseif (isset($this->foreignKeyPackageIDs[$tableName][$foreignKey->getName()])) {
                            $this->deleteForeignKeyLog($tableName, $foreignKey);
                        }
                    } elseif ($matchingExistingForeignKey === null) {
                        // If the referenced database table does not already exists, delay the
                        // foreign key creation until after the referenced table has been created.
                        if (!\in_array($foreignKey->getReferencedTable(), $this->existingTableNames)) {
                            continue;
                        }

                        if (!isset($this->foreignKeysToAdd[$tableName])) {
                            $this->foreignKeysToAdd[$tableName] = [];
                        }
                        $this->foreignKeysToAdd[$tableName][] = $foreignKey;

                        $this->splitNodeMessage .= "Added foreign key '{$tableName}." . \implode(
                            ',',
                            $foreignKey->getColumns()
                        ) . "'.";
                        break 2;
                    } elseif (\array_diff_assoc($foreignKey->getData(), $matchingExistingForeignKey->getData()) !== []) {
                        if (!isset($this->foreignKeysToDrop[$tableName])) {
                            $this->foreignKeysToDrop[$tableName] = [];
                        }
                        $this->foreignKeysToDrop[$tableName][] = $matchingExistingForeignKey;

                        if (!isset($this->foreignKeysToAdd[$tableName])) {
                            $this->foreignKeysToAdd[$tableName] = [];
                        }
                        $this->foreignKeysToAdd[$tableName][] = $foreignKey;

                        $this->splitNodeMessage .= "Replaced foreign key '{$tableName}." . \implode(
                            ',',
                            $foreignKey->getColumns()
                        ) . "'.";
                        break 2;
                    }
                }

                $existingIndices = $existingTable->getIndices();
                foreach ($table->getIndices() as $index) {
                    $matchingExistingIndex = null;
                    foreach ($existingIndices as $existingIndex) {
                        if (!$this->diffIndices($existingIndex, $index)) {
                            $matchingExistingIndex = $existingIndex;
                            break;
                        }
                    }

                    if ($index->willBeDropped()) {
                        if ($matchingExistingIndex !== null) {
                            if (!isset($this->indicesToDrop[$tableName])) {
                                $this->indicesToDrop[$tableName] = [];
                            }
                            $this->indicesToDrop[$tableName][] = $matchingExistingIndex;

                            $this->splitNodeMessage .= "Dropped index '{$tableName}." . \implode(
                                ',',
                                $index->getColumns()
                            ) . "'.";
                            break 2;
                        } elseif (isset($this->indexPackageIDs[$tableName][$index->getName()])) {
                            $this->deleteIndexLog($tableName, $index);
                        }
                    } elseif ($matchingExistingIndex !== null) {
                        // updating index type and index columns is supported with an
                        // explicit index name is given (automatically generated index
                        // names are not deterministic)
                        if (
                            !$index->hasGeneratedName()
                            && \array_diff_assoc($matchingExistingIndex->getData(), $index->getData()) !== []
                        ) {
                            if (!isset($this->indicesToDrop[$tableName])) {
                                $this->indicesToDrop[$tableName] = [];
                            }
                            $this->indicesToDrop[$tableName][] = $matchingExistingIndex;

                            if (!isset($this->indicesToAdd[$tableName])) {
                                $this->indicesToAdd[$tableName] = [];
                            }
                            $this->indicesToAdd[$tableName][] = $index;
                        }
                    } else {
                        if (!isset($this->indicesToAdd[$tableName])) {
                            $this->indicesToAdd[$tableName] = [];
                        }
                        $this->indicesToAdd[$tableName][] = $index;

                        $this->splitNodeMessage .= "Added index '{$tableName}." . \implode(
                            ',',
                            $index->getColumns()
                        ) . "'.";
                        break 2;
                    }
                }
            }
        }
    }

    /**
     * Checks for any pending log entries for the package and either marks them as done or
     * deletes them so that after this method finishes, there are no more undone log entries
     * for the package.
     */
    private function checkPendingLogEntries(): void
    {
        $sql = "SELECT  *
                FROM    wcf1_package_installation_sql_log
                WHERE   packageID = ?
                    AND isDone = ?";
        $statement = WCF::getDB()->prepare($sql);
        $statement->execute([$this->package->packageID, 0]);

        $doneEntries = $undoneEntries = [];
        while ($row = $statement->fetchArray()) {
            // table
            if ($row['sqlIndex'] === '' && $row['sqlColumn'] === '') {
                if (\in_array($row['sqlTable'], $this->existingTableNames)) {
                    $doneEntries[] = $row;
                } else {
                    $undoneEntries[] = $row;
                }
            } // column
            elseif ($row['sqlIndex'] === '') {
                if (isset($this->getExistingTable($row['sqlTable'])->getColumns()[$row['sqlColumn']])) {
                    $doneEntries[] = $row;
                } else {
                    $undoneEntries[] = $row;
                }
            } // foreign key
            elseif (\substr($row['sqlIndex'], -3) === '_fk') {
                if (isset($this->getExistingTable($row['sqlTable'])->getForeignKeys()[$row['sqlIndex']])) {
                    $doneEntries[] = $row;
                } else {
                    $undoneEntries[] = $row;
                }
            } // index
            else {
                if (isset($this->getExistingTable($row['sqlTable'])->getIndices()[$row['sqlIndex']])) {
                    $doneEntries[] = $row;
                } else {
                    $undoneEntries[] = $row;
                }
            }
        }

        WCF::getDB()->beginTransaction();
        foreach ($doneEntries as $entry) {
            $this->finalizeLog($entry);
        }

        // to achieve a consistent state, undone log entries will be deleted here even though
        // they might be re-created later to ensure that after this method finishes, there are
        // no more undone entries in the log for the relevant package
        foreach ($undoneEntries as $entry) {
            $this->deleteLog($entry);
        }
        WCF::getDB()->commitTransaction();
    }

    /**
     * Creates a done log entry for the given foreign key.
     */
    private function createForeignKeyLog(string $tableName, DatabaseTableForeignKey $foreignKey): void
    {
        $sql = "INSERT INTO wcf1_package_installation_sql_log
                            (packageID, sqlTable, sqlIndex, isDone)
                VALUES      (?, ?, ?, ?)";
        $statement = WCF::getDB()->prepare($sql);

        $statement->execute([
            $this->package->packageID,
            $tableName,
            $foreignKey->getName(),
            1,
        ]);
    }

    /**
     * Creates the given table.
     */
    private function createTable(DatabaseTable $table): void
    {
        $hasPrimaryKey = false;
        $columnData = \array_map(static function (IDatabaseTableColumn $column) use (&$hasPrimaryKey) {
            $data = $column->getData();
            if (isset($data['key']) && $data['key'] === 'PRIMARY') {
                $hasPrimaryKey = true;
            }

            return [
                'data' => $data,
                'name' => $column->getName(),
            ];
        }, $table->getColumns());
        $indexData = \array_map(static function (DatabaseTableIndex $index) {
            return [
                'data' => $index->getData(),
                'name' => $index->getName(),
            ];
        }, $table->getIndices());

        // Auto columns are implicitly defined as the primary key by MySQL.
        if ($hasPrimaryKey) {
            $indexData = \array_filter($indexData, static function ($key) {
                return $key !== 'PRIMARY';
            }, \ARRAY_FILTER_USE_KEY);
        }

        $this->dbEditor->createTable($table->getName(), $columnData, $indexData);

        foreach ($table->getForeignKeys() as $foreignKey) {
            // Only try to create the foreign key if the referenced database table already exists.
            // If it will be created later on, delay the foreign key creation until after the
            // referenced table has been created.
            if (
                \in_array($foreignKey->getReferencedTable(), $this->existingTableNames)
                || $foreignKey->getReferencedTable() === $table->getName()
            ) {
                $this->dbEditor->addForeignKey($table->getName(), $foreignKey->getName(), $foreignKey->getData());

                // foreign keys need to be explicitly logged for proper uninstallation
                $this->createForeignKeyLog($table->getName(), $foreignKey);
            }
        }
    }

    /**
     * Deletes the log entry for the given column.
     */
    private function deleteColumnLog(string $tableName, IDatabaseTableColumn $column): void
    {
        $this->deleteLog(['sqlTable' => $tableName, 'sqlColumn' => $column->getName()]);
    }

    /**
     * Deletes the log entry for the given foreign key.
     */
    private function deleteForeignKeyLog(string $tableName, DatabaseTableForeignKey $foreignKey): void
    {
        $this->deleteLog(['sqlTable' => $tableName, 'sqlIndex' => $foreignKey->getName()]);
    }

    /**
     * Deletes the log entry for the given index.
     */
    private function deleteIndexLog(string $tableName, DatabaseTableIndex $index): void
    {
        $this->deleteLog(['sqlTable' => $tableName, 'sqlIndex' => $index->getName()]);
    }

    /**
     * Deletes a log entry.
     *
     * @param array{sqlTable: string, sqlColumn?: string, sqlIndex?: string} $data
     */
    private function deleteLog(array $data): void
    {
        $sql = "DELETE FROM wcf1_package_installation_sql_log
                WHERE       packageID = ?
                        AND sqlTable = ?
                        AND sqlColumn = ?
                        AND sqlIndex = ?";
        $statement = WCF::getDB()->prepare($sql);

        $statement->execute([
            $this->package->packageID,
            $data['sqlTable'],
            $data['sqlColumn'] ?? '',
            $data['sqlIndex'] ?? '',
        ]);
    }

    /**
     * Deletes all log entry related to the given table.
     */
    private function deleteTableLog(DatabaseTable $table): void
    {
        $sql = "DELETE FROM wcf1_package_installation_sql_log
                WHERE       packageID = ?
                        AND sqlTable = ?";
        $statement = WCF::getDB()->prepare($sql);

        $statement->execute([
            $this->package->packageID,
            $table->getName(),
        ]);
    }

    /**
     * Returns `true` if the two columns differ.
     */
    private function diffColumns(IDatabaseTableColumn $oldColumn, IDatabaseTableColumn $newColumn): bool
    {
        $diff = \array_diff_assoc($oldColumn->getData(), $newColumn->getData());
        if ($diff !== []) {
            // see https://github.com/WoltLab/WCF/pull/3167
            if (\array_key_exists('length', $diff)) {
                if (
                    (
                        $oldColumn instanceof AbstractIntDatabaseTableColumn
                        && (
                            !($oldColumn instanceof TinyintDatabaseTableColumn)
                            || $oldColumn->getLength() != 1
                        )
                    )
                    || $oldColumn instanceof YearDatabaseTableColumn
                ) {
                    unset($diff['length']);
                }
            }

            if (isset($diff['type'])) {
                // In MariaDB JSON is an alias for LONGTEXT COLLATE utf8mb4_bin
                // introduced for compatibility reasons with MySQL's JSON data type.
                if (
                    $oldColumn->getType() === 'longtext'
                    && $newColumn->getType() === 'json'
                    && \stripos(WCF::getDB()->getVersion(), 'MariaDB') !== false
                ) {
                    unset($diff['type']);
                }
            }

            if ($diff !== []) {
                return true;
            }
        }

        if ($newColumn->getNewName()) {
            return true;
        }

        if (
            !($oldColumn instanceof IDefaultValueDatabaseTableColumn)
            || !($newColumn instanceof IDefaultValueDatabaseTableColumn)
        ) {
            \assert(
                ($oldColumn instanceof IDefaultValueDatabaseTableColumn)
                    === ($newColumn instanceof IDefaultValueDatabaseTableColumn),
                "Default support must be identical, because different types have been rejected above."
            );

            return false;
        }

        // default type has to be checked explicitly for `null` to properly detect changing
        // from no default value (`null`) and to an empty string as default value (and vice
        // versa)
        if ($oldColumn->getDefaultValue() === null || $newColumn->getDefaultValue() === null) {
            return $oldColumn->getDefaultValue() !== $newColumn->getDefaultValue();
        }

        // for all other cases, use weak comparison so that `'1'` (from database) and `1`
        // (from script PIP) match, for example
        return $oldColumn->getDefaultValue() != $newColumn->getDefaultValue();
    }

    /**
     * Returns `true` if the two indices differ.
     */
    private function diffIndices(DatabaseTableIndex $oldIndex, DatabaseTableIndex $newIndex): bool
    {
        if ($newIndex->hasGeneratedName()) {
            return \array_diff_assoc($oldIndex->getData(), $newIndex->getData()) !== [];
        }

        return $oldIndex->getName() !== $newIndex->getName();
    }

    /**
     * Drops the given foreign key.
     */
    private function dropForeignKey(string $tableName, DatabaseTableForeignKey $foreignKey): void
    {
        $this->dbEditor->dropForeignKey($tableName, $foreignKey->getName());
        $this->dbEditor->dropIndex($tableName, $foreignKey->getName());
    }

    /**
     * Drops the given index.
     */
    private function dropIndex(string $tableName, DatabaseTableIndex $index): void
    {
        $this->dbEditor->dropIndex($tableName, $index->getName());
    }

    /**
     * Drops the given table.
     */
    private function dropTable(DatabaseTable $table): void
    {
        $this->dbEditor->dropTable($table->getName());
    }

    /**
     * Finalizes the log entry for the creation of the given column.
     */
    private function finalizeColumnLog(string $tableName, IDatabaseTableColumn $column, bool $useNewName = false): void
    {
        $this->finalizeLog([
            'sqlTable' => $tableName,
            'sqlColumn' => $useNewName ? $column->getNewName() : $column->getName(),
        ]);
    }

    /**
     * Finalizes the log entry for adding the given index.
     */
    private function finalizeForeignKeyLog(string $tableName, DatabaseTableForeignKey $foreignKey): void
    {
        $this->finalizeLog(['sqlTable' => $tableName, 'sqlIndex' => $foreignKey->getName()]);
    }

    /**
     * Finalizes the log entry for adding the given index.
     */
    private function finalizeIndexLog(string $tableName, DatabaseTableIndex $index): void
    {
        $this->finalizeLog(['sqlTable' => $tableName, 'sqlIndex' => $index->getName()]);
    }

    /**
     * Finalizes a log entry after the relevant change has been executed.
     *
     * @param array{sqlTable: string, sqlColumn?: string, sqlIndex?: string} $data
     */
    private function finalizeLog(array $data): void
    {
        $sql = "UPDATE  wcf1_package_installation_sql_log
                SET     isDone = ?
                WHERE   packageID = ?
                    AND sqlTable = ?
                    AND sqlColumn = ?
                    AND sqlIndex = ?";
        $statement = WCF::getDB()->prepare($sql);

        $statement->execute([
            1,
            $this->package->packageID,
            $data['sqlTable'],
            $data['sqlColumn'] ?? '',
            $data['sqlIndex'] ?? '',
        ]);
    }

    /**
     * Finalizes the log entry for the creation of the given table.
     */
    private function finalizeTableLog(DatabaseTable $table): void
    {
        $this->finalizeLog(['sqlTable' => $table->getName()]);
    }

    /**
     * Returns the log entry for the given column or `null` if there is no explicit entry for
     * this column.
     *
     * @return ?array{
     *  packageID: int,
     *  sqlTable: string,
     *  sqlColumn: string,
     *  sqlIndex: string,
     *  isDone: 0|1
     * }
     * @since 5.4
     */
    private function getColumnLog(string $tableName, IDatabaseTableColumn $column): ?array
    {
        $sql = "SELECT  *
                FROM    wcf1_package_installation_sql_log
                WHERE   packageID = ?
                    AND sqlTable = ?
                    AND sqlColumn = ?";
        $statement = WCF::getDB()->prepare($sql);

        $statement->execute([
            $this->package->packageID,
            $tableName,
            $column->getName(),
        ]);

        $row = $statement->fetchSingleRow();
        if ($row === false) {
            return null;
        }

        return $row;
    }

    /**
     * Returns the id of the package to with the given column belongs to. If there is no specific
     * log entry for the given column, the table log is checked and the relevant package id of
     * the whole table is returned. If the package of the table is also unknown, `null` is returned.
     */
    private function getColumnPackageID(DatabaseTable $table, IDatabaseTableColumn $column): ?int
    {
        if (isset($this->columnPackageIDs[$table->getName()][$column->getName()])) {
            return $this->columnPackageIDs[$table->getName()][$column->getName()];
        } elseif (isset($this->tablePackageIDs[$table->getName()])) {
            return $this->tablePackageIDs[$table->getName()];
        }

        return null;
    }

    /**
     * Returns the `DatabaseTable` object for the table with the given name.
     */
    private function getExistingTable(string $tableName): DatabaseTable
    {
        if (!isset($this->existingTables[$tableName])) {
            $this->existingTables[$tableName] = DatabaseTable::createFromExistingTable($this->dbEditor, $tableName);
        }

        return $this->existingTables[$tableName];
    }

    /**
     * Returns the id of the package to with the given foreign key belongs to. If there is no specific
     * log entry for the given foreign key, the table log is checked and the relevant package id of
     * the whole table is returned. If the package of the table is also unknown, `null` is returned.
     */
    private function getForeignKeyPackageID(DatabaseTable $table, DatabaseTableForeignKey $foreignKey): ?int
    {
        if (isset($this->foreignKeyPackageIDs[$table->getName()][$foreignKey->getName()])) {
            return $this->foreignKeyPackageIDs[$table->getName()][$foreignKey->getName()];
        } elseif (isset($this->tablePackageIDs[$table->getName()])) {
            return $this->tablePackageIDs[$table->getName()];
        }

        return null;
    }

    /**
     * Returns the id of the package to with the given index belongs to. If there is no specific
     * log entry for the given index, the table log is checked and the relevant package id of
     * the whole table is returned. If the package of the table is also unknown, `null` is returned.
     */
    private function getIndexPackageID(DatabaseTable $table, DatabaseTableIndex $index): ?int
    {
        if (isset($this->indexPackageIDs[$table->getName()][$index->getName()])) {
            return $this->indexPackageIDs[$table->getName()][$index->getName()];
        } elseif (isset($this->tablePackageIDs[$table->getName()])) {
            return $this->tablePackageIDs[$table->getName()];
        }

        return null;
    }

    /**
     * Prepares the log entry for the creation of the given column.
     */
    private function prepareColumnLog(string $tableName, IDatabaseTableColumn $column, bool $useNewName = false): void
    {
        $this->prepareLog([
            'sqlTable' => $tableName,
            'sqlColumn' => $useNewName ? $column->getNewName() : $column->getName(),
        ]);
    }

    /**
     * Prepares the log entry for adding the given foreign key.
     */
    private function prepareForeignKeyLog(string $tableName, DatabaseTableForeignKey $foreignKey): void
    {
        $this->prepareLog(['sqlTable' => $tableName, 'sqlIndex' => $foreignKey->getName()]);
    }

    /**
     * Prepares the log entry for adding the given index.
     */
    private function prepareIndexLog(string $tableName, DatabaseTableIndex $index): void
    {
        $this->prepareLog(['sqlTable' => $tableName, 'sqlIndex' => $index->getName()]);
    }

    /**
     * Prepares a log entry before the relevant change has been executed.
     *
     * @param array{sqlTable: string, sqlColumn?: string, sqlIndex?: string} $data
     */
    private function prepareLog(array $data): void
    {
        $sql = "INSERT INTO wcf1_package_installation_sql_log
                            (packageID, sqlTable, sqlColumn, sqlIndex, isDone)
                VALUES      (?, ?, ?, ?, ?)";
        $statement = WCF::getDB()->prepare($sql);

        $statement->execute([
            $this->package->packageID,
            $data['sqlTable'],
            $data['sqlColumn'] ?? '',
            $data['sqlIndex'] ?? '',
            0,
        ]);
    }

    /**
     * Prepares the log entry for the creation of the given table.
     */
    private function prepareTableLog(DatabaseTable $table): void
    {
        $this->prepareLog(['sqlTable' => $table->getName()]);
    }

    /**
     * Processes all tables and updates the current table layouts to match the specified layouts.
     *
     * @throws  \RuntimeException   if validation of the required layout changes fails
     */
    public function process(): void
    {
        $this->checkPendingLogEntries();

        $errors = $this->validate();
        if ($errors !== []) {
            throw new \RuntimeException(WCF::getLanguage()->getDynamicVariable(
                'wcf.acp.package.error.databaseChange',
                [
                    'errors' => $errors,
                ]
            ));
        }

        $this->calculateChanges();

        $this->applyChanges();
    }

    /**
     * Checks if the relevant table layout changes can be executed and returns an array with information
     * on all validation errors.
     *
     * @return list<array{
     *  tableName: string,
     *  type: string,
     *  columnName?: string,
     *  columnNames?: string,
     *  referencedTableName?: string,
     * }>
     */
    public function validate(): array
    {
        $errors = [];
        foreach ($this->tables as $table) {
            if ($table->willBeDropped()) {
                if (\in_array($table->getName(), $this->existingTableNames)) {
                    if (!isset($this->tablePackageIDs[$table->getName()])) {
                        $errors[] = [
                            'tableName' => $table->getName(),
                            'type' => 'unregisteredTableDrop',
                        ];
                    } elseif ($this->tablePackageIDs[$table->getName()] !== $this->package->packageID) {
                        $errors[] = [
                            'tableName' => $table->getName(),
                            'type' => 'foreignTableDrop',
                        ];
                    }
                }
            } else {
                $existingTable = null;
                if (\in_array($table->getName(), $this->existingTableNames)) {
                    if (!isset($this->tablePackageIDs[$table->getName()])) {
                        $abbreviationWithWcfNumber = \explode('_', $table->getName(), 2)[0];

                        // Remove the \WCF_N from the table prafix.
                        $abbreviation = \substr(
                            $abbreviationWithWcfNumber,
                            0,
                            \strlen($abbreviationWithWcfNumber) - \strlen((string)\WCF_N)
                        );

                        // Throws an error only if the table has an unknown prefix.
                        // If the prefix is known, the unknown table is deleted
                        // before it is created again (this is registered in self::calculateChanges()).
                        if (
                            !\in_array(
                                $abbreviation,
                                ApplicationHandler::getInstance()->getAbbreviations()
                            )
                        ) {
                            $errors[] = [
                                'tableName' => $table->getName(),
                                'type' => 'unregisteredTableChange',
                            ];
                        }
                    } else {
                        $existingTable = DatabaseTable::createFromExistingTable($this->dbEditor, $table->getName());
                        $existingColumns = $existingTable->getColumns();
                        $existingIndices = $existingTable->getIndices();
                        $existingForeignKeys = $existingTable->getForeignKeys();

                        foreach ($table->getColumns() as $column) {
                            if (isset($existingColumns[$column->getName()])) {
                                $columnPackageID = $this->getColumnPackageID($table, $column);
                                if ($column->willBeDropped()) {
                                    if ($columnPackageID !== $this->package->packageID) {
                                        $errors[] = [
                                            'columnName' => $column->getName(),
                                            'tableName' => $table->getName(),
                                            'type' => 'foreignColumnDrop',
                                        ];
                                    }
                                } elseif ($columnPackageID !== $this->package->packageID) {
                                    $errors[] = [
                                        'columnName' => $column->getName(),
                                        'tableName' => $table->getName(),
                                        'type' => 'foreignColumnChange',
                                    ];
                                }
                            } elseif ($column->getNewName() && !isset($existingColumns[$column->getNewName()])) {
                                // Only show error message for a column rename if no column with the
                                // old or new name exists.
                                $errors[] = [
                                    'columnName' => $column->getName(),
                                    'tableName' => $table->getName(),
                                    'type' => 'renameNonexistingColumn',
                                ];
                            }
                        }

                        foreach ($table->getIndices() as $index) {
                            foreach ($existingIndices as $existingIndex) {
                                if (!$this->diffIndices($existingIndex, $index)) {
                                    if ($index->willBeDropped()) {
                                        if ($this->getIndexPackageID($table, $index) !== $this->package->packageID) {
                                            $errors[] = [
                                                'columnNames' => \implode(',', $existingIndex->getColumns()),
                                                'tableName' => $table->getName(),
                                                'type' => 'foreignIndexDrop',
                                            ];
                                        }
                                    }

                                    continue 2;
                                }
                            }
                        }

                        foreach ($table->getForeignKeys() as $foreignKey) {
                            foreach ($existingForeignKeys as $existingForeignKey) {
                                if (\array_diff_assoc($foreignKey->getData(), $existingForeignKey->getData()) === []) {
                                    if ($foreignKey->willBeDropped()) {
                                        if (
                                            $this->getForeignKeyPackageID(
                                                $table,
                                                $foreignKey
                                            ) !== $this->package->packageID
                                        ) {
                                            $errors[] = [
                                                'columnNames' => \implode(',', $existingForeignKey->getColumns()),
                                                'tableName' => $table->getName(),
                                                'type' => 'foreignForeignKeyDrop',
                                            ];
                                        }
                                    }

                                    continue 2;
                                }
                            }
                        }
                    }
                }

                foreach ($table->getIndices() as $index) {
                    if (\count($index->getColumns()) !== \count(\array_unique($index->getColumns()))) {
                        $errors[] = [
                            'columnNames' => \implode(',', $index->getColumns()),
                            'tableName' => $table->getName(),
                            'type' => 'duplicateColumnInIndex',
                        ];
                    }

                    if (
                        $index->getType() === DatabaseTableIndex::PRIMARY_TYPE
                        && $index->getName() !== 'PRIMARY'
                    ) {
                        $errors[] = [
                            'columnNames' => \implode(',', $index->getColumns()),
                            'tableName' => $table->getName(),
                            'type' => 'primaryNotCalledPrimary',
                        ];
                    }

                    foreach ($index->getColumns() as $indexColumn) {
                        $column = $this->getColumnByName($indexColumn, $table, $existingTable);
                        if ($column === null) {
                            if (!$index->willBeDropped()) {
                                $errors[] = [
                                    'columnName' => $indexColumn,
                                    'columnNames' => \implode(',', $index->getColumns()),
                                    'tableName' => $table->getName(),
                                    'type' => 'nonexistingColumnInIndex',
                                ];
                            }
                        } elseif (
                            $index->getType() === DatabaseTableIndex::PRIMARY_TYPE
                            && !$index->willBeDropped()
                            && !$column->isNotNull()
                        ) {
                            $errors[] = [
                                'columnName' => $indexColumn,
                                'columnNames' => \implode(',', $index->getColumns()),
                                'tableName' => $table->getName(),
                                'type' => 'nullColumnInPrimaryIndex',
                            ];
                        }
                    }
                }

                foreach ($table->getForeignKeys() as $foreignKey) {
                    $referencedTableExists = \in_array($foreignKey->getReferencedTable(), $this->existingTableNames);
                    foreach ($this->tables as $processedTable) {
                        if ($processedTable->getName() === $foreignKey->getReferencedTable()) {
                            $referencedTableExists = !$processedTable->willBeDropped();
                        }
                    }

                    if (!$referencedTableExists) {
                        $errors[] = [
                            'columnNames' => \implode(',', $foreignKey->getColumns()),
                            'referencedTableName' => $foreignKey->getReferencedTable(),
                            'tableName' => $table->getName(),
                            'type' => 'unknownTableInForeignKey',
                        ];
                    }

                    if (!\str_ends_with($foreignKey->getName(), '_fk') && !$foreignKey->willBeDropped()) {
                        $errors[] = [
                            'name' => $foreignKey->getName(),
                            'columnNames' => \implode(',', $foreignKey->getColumns()),
                            'referencedTableName' => $foreignKey->getReferencedTable(),
                            'tableName' => $table->getName(),
                            'type' => 'missingFkSuffixInForeignKey',
                        ];
                    }
                }
            }
        }

        return $errors;
    }

    /**
     * Returns the column with the given name from the given table.
     *
     * @since       5.2.10
     */
    private function getColumnByName(
        string $columnName,
        DatabaseTable $updateTable,
        ?DatabaseTable $existingTable = null
    ): ?IDatabaseTableColumn {
        foreach ($updateTable->getColumns() as $column) {
            if (
                ($column->getNewName() === $columnName)
                || ($column->getName() === $columnName && !$column->getNewName())
            ) {
                return $column;
            }
        }

        if ($existingTable) {
            foreach ($existingTable->getColumns() as $column) {
                if ($column->getName() === $columnName) {
                    return $column;
                }
            }
        }

        return null;
    }
}