20230812003955_UpdateUserIds.php

<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

const logActions = false;

class UpdateUserIds extends AbstractMigration
{

    public function up()
    {
        $currentDatabase = $this->getAdapter()->getOption('name');

        $tables = $this->getTables($currentDatabase);
        $foreignKeys = [];

        foreach ($tables as $table) {
            // Gather information about foreign keys before dropping
            $foreignKeys = $this->getForeignKeys($table, $currentDatabase);

            // Drop foreign key constraints
            foreach ($foreignKeys as $foreignKey) {
                if (logActions) {
                    $this->output->writeln("ALTER TABLE $table DROP FOREIGN KEY {$foreignKey['constraint_name']};");
                }
                $this->execute("ALTER TABLE $table DROP FOREIGN KEY {$foreignKey['constraint_name']};");
            }
        }

        foreach ($tables as $table) {
            $this->alterColumnToUnsigned('user_id', $table);
            $this->alterColumnToUnsigned('user', $table);
        }

        // Alter the 'users' table separately
        $this->alterColumnToUnsigned('id', 'users');

        foreach ($tables as $table) {
            foreach ($foreignKeys as $foreignKey) {
                if (logActions) {
                    $this->output->writeln("ALTER TABLE $table ADD CONSTRAINT {$foreignKey['constraint_name']} FOREIGN KEY ({$foreignKey['column_name']}) REFERENCES {$foreignKey['referenced_table']} ({$foreignKey['referenced_column']}) ON DELETE {$foreignKey['delete_rule']} ON UPDATE {$foreignKey['update_rule']};");
                }
                $this->execute("ALTER TABLE $table ADD CONSTRAINT {$foreignKey['constraint_name']} FOREIGN KEY ({$foreignKey['column_name']}) REFERENCES {$foreignKey['referenced_table']} ({$foreignKey['referenced_column']}) ON DELETE {$foreignKey['delete_rule']} ON UPDATE {$foreignKey['update_rule']};");
            }
        }
    }

    /**
     * Get tables with a 'user_id' column.
     *
     * @param string $currentDatabase
     * @return array
     */
    private function getTables($currentDatabase)
    {
        $tables = [];

        // Query the information schema to get tables with 'user_id' column
        $result = $this->query("
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE (COLUMN_NAME = 'user_id' OR COLUMN_NAME = 'user') 
                AND TABLE_SCHEMA = '$currentDatabase'
        ");

        foreach ($result as $row) {
            $tables[] = $row['TABLE_NAME'];
        }

        return $tables;
    }

    /**
     * Get foreign keys for a given table.
     *
     * @param string $table
     * @param string $currentDatabase
     * @return array
     */
    private function getForeignKeys($table, $currentDatabase)
    {
        $foreignKeys = [];

        // Query the information schema to get foreign keys for the table
        $result = $this->query("
            SELECT
                KCU.CONSTRAINT_NAME,
                KCU.COLUMN_NAME,
                KCU.REFERENCED_TABLE_NAME,
                KCU.REFERENCED_COLUMN_NAME,
                RC.DELETE_RULE,
                RC.UPDATE_RULE
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
            LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
            WHERE KCU.TABLE_NAME = '$table' AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
                AND KCU.TABLE_SCHEMA = '$currentDatabase' AND RC.CONSTRAINT_SCHEMA = '$currentDatabase'
        ");

        foreach ($result as $row) {
            $foreignKeys[] = [
                'constraint_name' => $row['CONSTRAINT_NAME'],
                'column_name' => $row['COLUMN_NAME'],
                'referenced_table' => $row['REFERENCED_TABLE_NAME'],
                'referenced_column' => $row['REFERENCED_COLUMN_NAME'],
                'delete_rule' => $row['DELETE_RULE'],
                'update_rule' => $row['UPDATE_RULE'],
            ];
        }

        return $foreignKeys;
    }

    /**
     * Alter a column to be unsigned.
     *
     * @param string $columnName
     * @param string $table
     */
    private function alterColumnToUnsigned($columnName, $table)
    {
        $column = $this->table($table)->getColumn($columnName);

        if ($column && $column->getType() === 'integer') {
            $this->updateColumnValues($table, $columnName);

            if (logActions) {
                $this->output->writeln("ALTER TABLE $table CHANGE $columnName $columnName INT(11) UNSIGNED DEFAULT NULL;");
            }
            $this->table($table)
                ->changeColumn($columnName, 'integer', [
                    'signed' => false,
                    'limit' => 11,
                ])
                ->update();
        }
    }

    /**
     * Update specific column values in the table.
     *
     * @param string $table
     * @param string $columnName
     */
    private function updateColumnValues($table, $columnName)
    {
        $updateValues = [
            GUEST_USER_ID => -1,
            DELETED_USER_ID => -2,
        ];

        $builder = $this->getQueryBuilder();

        foreach ($updateValues as $newValue => $oldValue) {
            $builder
                ->update($table)
                ->set($columnName, $newValue)
                ->where([$columnName => $oldValue])
                ->execute();
        }
    }


    public function down()
    {
        $currentDatabase = $this->getAdapter()->getOption('name');

        $tables = $this->getTables($currentDatabase);
        $foreignKeys = [];

        foreach ($tables as $table) {
            // Gather information about foreign keys before dropping
            $foreignKeys = $this->getForeignKeys($table, $currentDatabase);

            // Drop foreign key constraints
            foreach ($foreignKeys as $foreignKey) {
                if (logActions) {
                    $this->output->writeln("ALTER TABLE $table DROP FOREIGN KEY {$foreignKey['constraint_name']};");
                }
                $this->execute("ALTER TABLE $table DROP FOREIGN KEY {$foreignKey['constraint_name']};");
            }
        }

        foreach ($tables as $table) {
            // Alter the column back to its original type
            $this->alterColumnToOriginal($table, 'user_id');
            $this->alterColumnToOriginal($table, 'user');
        }

        // Alter the 'users' table separately
        $this->alterColumnToOriginal('users', 'id');

        foreach ($tables as $table) {
            // Recreate foreign key constraints
            foreach ($foreignKeys as $foreignKey) {
                if (logActions) {
                    $this->output->writeln("ALTER TABLE $table ADD CONSTRAINT {$foreignKey['constraint_name']} FOREIGN KEY ({$foreignKey['column_name']}) REFERENCES {$foreignKey['referenced_table']} ({$foreignKey['referenced_column']}) ON DELETE {$foreignKey['delete_rule']} ON UPDATE {$foreignKey['update_rule']};");
                }
                $this->execute("ALTER TABLE $table ADD CONSTRAINT {$foreignKey['constraint_name']} FOREIGN KEY ({$foreignKey['column_name']}) REFERENCES {$foreignKey['referenced_table']} ({$foreignKey['referenced_column']}) ON DELETE {$foreignKey['delete_rule']} ON UPDATE {$foreignKey['update_rule']};");
            }
        }
    }

    /**
     * Alter a column back to its original type.
     *
     * @param string $table
     * @param string $columnName
     */
    private function alterColumnToOriginal($table, $columnName)
    {
        if (logActions) {
            $this->output->writeln("ALTER TABLE $table CHANGE $columnName $columnName INT(11) SIGNED DEFAULT NULL;");
        }
        $this->table($table)
            ->changeColumn($columnName, 'integer', [
                'signed' => true,
            ])
            ->update();
    }
}