CreateMissingForeignKeyMigration

CreateMissingForeignKeyMigration.php

<?php

namespace App\Console\Commands;

use Doctrine\Common\Inflector\Inflector;
use Doctrine\DBAL\Types\IntegerType;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CreateMissingForeignKeyMigration extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'createMissingForeignKey';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Create missing foreign keys';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        /** @var \Doctrine\DBAL\Connection $doctrineConnection */
        $doctrineConnection = DB::connection()->getDoctrineConnection();
        /** @var \Doctrine\DBAL\Schema\AbstractSchemaManager $schema */
        $schema = $doctrineConnection->getSchemaManager();

        $tables = $schema->listTables();

        $toMigrate = [];
        foreach ($tables as $table) {
            $columns = $table->getColumns();

            foreach ($columns as $column) {
                if (!ends_with($column->getName(), '_id')) {
                    continue;
                }

                if (!($column->getType() instanceof IntegerType)) {
                    continue;
                }

                // Skip columns which are already unsigned
                if ($column->getUnsigned()) {
                    continue;
                }

                $toMigrate[$table->getName()][$column->getName()] = true;
            }
        }

        // if (!empty($toMigrate)) {
        //     foreach ($toMigrate as $table => $columns) {
        //         echo 'Schema::table(\''.$table.'\', function(Blueprint $table) {'.PHP_EOL;
        //         foreach ($columns as $column => $dontCare) {
        //             echo '    $table->unsignedInteger(\''.$column.'\')->change();'.PHP_EOL;
        //         }
        //         echo '});'.PHP_EOL;
        //     }
        // }

        // $schemas = [];
        foreach ($tables as $table) {
            $existingForeignKeys = $table->getForeignKeys();

            $existingForeignKeyIdentifiers = [];
            foreach ($existingForeignKeys as $existingForeignKey) {
                $columns = $existingForeignKey->getLocalColumns();
                // Here we assume that a foreign key has only one local column
                if (count($columns) > 1) {
                    $this->error('Table '.$table->getName().', foreign key '.$existingForeignKey->getName().' is a composite foreign key, skipping.');
                    continue;
                }
                foreach ($columns as $column) {
                    $existingForeignKeyIdentifiers[$column] = true;
                    break;
                }
            }

            $columns = $table->getColumns();

            $newForeignKeys = [];
            foreach ($columns as $column) {
                if (!ends_with($column->getName(), '_id')) {
                    continue;
                }

                if (!($column->getType() instanceof IntegerType)) {
                    continue;
                }

                // Foreign key already exist in table
                if (array_key_exists($column->getName(), $existingForeignKeyIdentifiers)) {
                    continue;
                }

                $newForeignKeys[] = [
                    'key' => $column->getName(),
                    'table' => Inflector::pluralize(substr($column->getName(), 0, -3)),
                ];
            }

            if (!empty($newForeignKeys)) {
                echo 'Schema::table(\''.$table->getName().'\', function(Blueprint $table) {'.PHP_EOL;
                foreach ($newForeignKeys as $newForeignKey) {
                    echo '    $table->foreign(\'' . $newForeignKey['key'] . '\')->references(\'id\')->on(\'' . $newForeignKey['table'] . '\');' . PHP_EOL;
                }
                echo '});'.PHP_EOL;

                // echo 'Schema::table(\''.$table->getName().'\', function(Blueprint $table) {'.PHP_EOL;
                // foreach ($newForeignKeys as $newForeignKey) {
                //     echo '    $table->dropForeign([\'' . $newForeignKey['key'] . '\']);' . PHP_EOL;
                // }
                // echo '});'.PHP_EOL;
            }

            // $foreignKeys = $table->getForeignKeys();
            // echo $table->getName();
            // var_dump($foreignKeys);
        }
    }
}