Created
May 2, 2018 20:10
-
-
Save tomzx/4ec82348ad65cb3f8beee03cd50879a1 to your computer and use it in GitHub Desktop.
CreateMissingForeignKeyMigration
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment