Forked from bruceoutdoors/DbmigrateController.php
Last active
January 30, 2017 06:32
-
-
Save ramosmerino/632a06672bcee52e192c to your computer and use it in GitHub Desktop.
The same ol' DbmigrateController, on steroids, for Laravel 5, and with some tweaks that I find useful for my work. In brief, get migration files by a created database, one for each table or all in one, ordered in creation time by their foreign keys, to only call the 'php artisan migrate'. Simply sugar.
This file contains 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\Http\Controllers; | |
use \Illuminate\Support\Facades\DB; | |
use \Illuminate\Support\Str; | |
use League\Flysystem\Exception; | |
/** | |
* Class SAMODbmigrateController | |
* | |
* Converts an existing MySQL database to migrations files for Laravel 5. | |
* The same ol' DbmigrateController, on steroids, for Laravel 5, and with | |
* some tweaks that I find useful for my work. | |
* In brief, get migration files by a created database, one for each table or all in one, | |
* ordered in creation time by their foreign keys, to only call the 'php artisan migrate'. Simply sugar. | |
* | |
* - Change the namespace to your application's name. | |
* - In SAMODbmigrateController class, edit the attributes `$databaseName` and `$ignore`. | |
* - Place this file inside app/Http/Controllers/ | |
* - Add to your app/Http/routes.php: Route::controller('dbmigrate', 'SAMODbmigrateController'); | |
* - Going to http://your-site.com/dbmigrate, when you want one file for each table in the schema. | |
* - Going to http://your-site.com/dbmigrate/one-file, when you want one file with all tables in the schema. | |
* - By default, the resulting migration files will be generated in *database/migrations/* | |
* - Remove this file and routes in production environment. | |
* | |
* ## Features | |
* Export migrations to one file for whole schema, or each file per table | |
* Recognize `created_at` and `updated_at` columns, transforming to `$table->timestamps();` | |
* Recognize `deleted_at`, transforming to `$table->softDeletes();` | |
* Recognize `remember_token`, transforming to `$table->rememberToken();` | |
* Supports: | |
* Integers: TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT (BIT transformed to TINYINT) | |
* *The AUTO_INCREMENT it is only supported in INT and BIGINT. | |
* Fixed-point: DECIMAL, NUMERIC. | |
* Floating-point: FLOAT, DOUBLE. | |
* String: CHAR, VARCHAR | |
* TEXT, MEDIUMTEXT, LONGTEXT (TINYTEXT transformed to TEXT) | |
* BLOB (TINYBLOB, MEDIUMBLOB, LONGBLOB transformed to BLOB) | |
* Date and time: TIME, TIMESTAMP, DATE, DATETIME | |
* Boolean: BOOL, BOOLEAN | |
* API Reference | |
* It is probably pretentious to make an API reference to a gist of this size, and the PHPDocs are clearer, but for quick reference: | |
* SAMODbmigrateController | |
* `$databaseName` : The name of the schema to be mapped. | |
* `$ignore` : An array of table names to be ignored. | |
* `getIndex()` : Creates one migration file for each table. Run by going to http://your-site.com/dbmigrate. | |
* `getOneFile()` : Creates only one migration file for the whole schema. Run by going to http://your-site.com/dbmigrate/one-file. | |
* SqlMigrations | |
* `__contruct(string $databaseName, $allPKToAi = true)` : Well, the class constructor. | |
* `getMessage()` : The snitch. Tells the messages when all it is OK. | |
* `ignore(string[] $tables)` : Sets an array of table names to be ignored | |
* `write(string $migrationsDirectory = '../database/migrations/')` : Writes the schema in migrations for each table. | |
* `writeToOneFile(string $migrationsDirectory = '../database/migrations/')` : Writes the schema to all-in-one migration file. | |
* | |
* Credits to @Christopher Pitt, @michaeljcalkins and @Lee Zhen Yong, from whom this gist is forked off. | |
* | |
* @author Cristóbal Ramos Merino <cristobal@ramosmerino.cl> | |
*/ | |
class SAMODbmigrateController extends Controller | |
{ | |
/** | |
* @var string $databaseName The name of the schema to be mapped. | |
*/ | |
private $databaseName = 'schemaName'; | |
/** | |
* @var string[] $ignore An array of table names to be ignored. | |
*/ | |
private $ignore = ['some_table_name', 'another_table_name']; | |
/** | |
* Access by dbmigrate. Creates one migration file for each table. | |
*/ | |
public function getIndex() | |
{ | |
try { | |
$migrate = new SqlMigrations($this->databaseName); | |
$migrate->ignore($this->ignore); | |
$migrate->write(); | |
echo '<pre>' . $migrate->getMessage() . '</pre>'; | |
} catch (Exception $e) { | |
echo $e->getMessage(); | |
} | |
echo '<a href="../dbmigrate/one-file">Create one-file migration.</a>'; | |
} | |
/** | |
* Access by dbmigrate/one-file. Creates only one migration file for the whole schema. | |
*/ | |
public function getOneFile() | |
{ | |
try { | |
$migrate = new SqlMigrations($this->databaseName); | |
$migrate->ignore($this->ignore); | |
$migrate->writeToOneFile(); | |
echo '<pre>' . $migrate->getMessage() . '</pre>'; | |
} catch (Exception $e) { | |
echo $e->getMessage(); | |
} | |
echo '<a href="../dbmigrate">Create migrations for each table.</a>'; | |
} | |
} | |
class SqlMigrations | |
{ | |
private $ignore = ['migrations']; | |
private $database = ''; | |
private $schema = []; | |
private $tables = []; | |
private $message = ''; | |
private $date = null; | |
/** | |
* Class constructor. | |
* @param string $databaseName | |
* @param bool $allPKToAI Default true. Sets all primary keys to auto increment. If it's false, sets to auto increment only the explicitly indicated in the schema. | |
*/ | |
public function __construct($databaseName, $allPKToAI = true) | |
{ | |
$this->database = $databaseName; | |
$this->tables = DB::select('SELECT table_name FROM information_schema.tables WHERE table_schema="' . $this->database . '"'); | |
$this->convert($allPKToAI); | |
$this->message .= "{$databaseName} mapped to migrations.\n"; | |
$this->date = new \DateTime(); | |
} | |
private function getColumnsInformation($table) | |
{ | |
return DB::table('information_schema.columns') | |
->where('information_schema.columns.table_schema', '=', $this->database) | |
->where('information_schema.columns.table_name', '=', $table) | |
->get(['column_name as Field', | |
'column_type as Type', | |
'is_nullable as Null', | |
'column_key as Key', | |
'column_default as Default', | |
'extra as Extra', | |
'data_type as Data_Type']); | |
} | |
private function getTableInformation($table) | |
{ | |
return DB::table('information_schema.tables') | |
->where('information_schema.tables.table_schema', '=', $this->database) | |
->where('information_schema.tables.table_name', '=', $table) | |
->first(); | |
} | |
private function getForeigners($table) | |
{ | |
return DB::table('information_schema.KEY_COLUMN_USAGE') | |
->where('CONSTRAINT_SCHEMA', '=', $this->database) | |
->where('REFERENCED_TABLE_SCHEMA', '=', $this->database) | |
->where('TABLE_NAME', '=', $table) | |
->select('COLUMN_NAME', 'REFERENCED_TABLE_NAME', 'REFERENCED_COLUMN_NAME') | |
->get(); | |
} | |
private function compileTable($table) | |
{ | |
$up = $this->schema[$table->table_name]['up']; | |
$down = $this->schema[$table->table_name]['down']; | |
$upForeigners = ''; | |
$downForeigners = ''; | |
if (array_key_exists('foreign', $this->schema[$table->table_name])) { | |
$upForeigners = $this->schema[$table->table_name]['foreign']['up']; | |
$downForeigners = $this->schema[$table->table_name]['foreign']['down']; | |
} | |
$migration = "<?php" | |
. "\n\nuse Illuminate\\Database\\Schema\\Blueprint;" | |
. "\nuse Illuminate\\Database\\Migrations\\Migration;" | |
. "\n\nclass Create" . str_replace('_', '', Str::title($table->table_name)) . "Table extends Migration {" | |
. "\n\n\t/**\n\t * Run the migrations.\n\t *\n\t * @return void\n\t */" | |
. "\n\tpublic function up()\n\t{\n {$up} {$upForeigners} \t}" | |
. "\n\n\t/**\n\t * Reverse the migrations.\n\t *\n\t * @return void\n\t */" | |
. "\n\tpublic function down()\n\t{\n {$downForeigners} {$down} \t}" | |
. "\n}"; | |
return $migration; | |
} | |
private function compileDatabase() | |
{ | |
$up = ''; | |
$down = ''; | |
$upForeigners = ''; | |
$downForeigners = ''; | |
foreach ($this->schema as $schema) { | |
$up .= $schema['up']; | |
$down .= $schema['down']; | |
if (array_key_exists('foreign', $schema)) { | |
$upForeigners .= $schema['foreign']['up']; | |
$downForeigners .= $schema['foreign']['down']; | |
} | |
} | |
$migration = "<?php" | |
. "\n\nuse Illuminate\\Database\\Schema\\Blueprint;" | |
. "\nuse Illuminate\\Database\\Migrations\\Migration;" | |
. "\n\nclass Create" . str_replace('_', '', Str::title($this->database)) . "Database extends Migration {" | |
. "\n\n\t/**\n\t * Run the migrations.\n\t *\n\t * @return void\n\t */" | |
. "\n\tpublic function up()\n\t{\n {$up} {$upForeigners} \t}" | |
. "\n\n\t/**\n\t * Reverse the migrations.\n\t *\n\t * @return void\n\t */" | |
. "\n\tpublic function down()\n\t{\n {$downForeigners} {$down} \t}" | |
. "\n}"; | |
return $migration; | |
} | |
/** | |
* Creates the migration file for databaseName database. | |
* @param bool $allPKToAI Sets all primary keys to auto increment or only the explicitly indicated in the schema. | |
* @return SqlMigrations | |
*/ | |
private function convert($allPKToAI) | |
{ | |
foreach ($this->tables as $key => $value) { | |
if (in_array($value->table_name, $this->ignore)) { | |
continue; | |
} | |
$down = "\t\tSchema::drop('{$value->table_name}');\n"; | |
$up = "\t\tSchema::create('{$value->table_name}', function (Blueprint \$table) {\n"; | |
$columnsDescribes = $this->getColumnsInformation($value->table_name); | |
$tableDescribes = $this->getTableInformation($value->table_name); | |
// Count the created_at and updated_at coincidences. | |
$tsCount = 0; | |
foreach ($columnsDescribes as $values) { | |
switch ($values->Field) { | |
case 'deleted_at': | |
$up .= "\t\t\t\$table->softDeletes();\n"; | |
// Remember, in PHP the switch statement is considered a looping structure for the purposes of continue. | |
continue 2; | |
case 'remember_token': | |
$up .= "\t\t\t\$table->rememberToken();\n"; | |
continue 2; | |
case 'created_at': | |
case 'updated_at': | |
++$tsCount; | |
if ($tsCount == 2) { | |
$up .= "\t\t\t\$table->timestamps();\n"; | |
} | |
continue 2; | |
} | |
$method = ""; | |
$parameters = strpos($values->Type, '('); | |
$type = $parameters > -1 ? substr($values->Type, 0, $parameters) : $values->Type; | |
$numbers = ""; | |
$nullable = $values->Null == "NO" ? "" : "->nullable()"; | |
$default = empty($values->Default) ? "" : "->default(\"{$values->Default}\")"; | |
$unsigned = strpos($values->Type, "unsigned") === false ? '' : '->unsigned()'; | |
$unique = $values->Key == 'UNI' ? "->unique()" : ""; | |
switch ($type) { | |
// Exact value | |
case 'int' : | |
$method = 'integer'; | |
break; | |
case 'bit' : | |
case 'tinyint' : | |
$method = 'tinyInteger'; | |
break; | |
case 'smallint' : | |
$method = 'smallInteger'; | |
break; | |
case 'mediumint' : | |
$method = 'mediumInteger'; | |
break; | |
case 'bigint' : | |
$method = 'bigInteger'; | |
break; | |
case 'dec' : | |
case 'decimal' : | |
case 'numeric' : | |
$parameters = strpos($values->Type, '('); | |
$numbers = ", " . substr($values->Type, $parameters + 1, -1); | |
$method = 'decimal'; | |
break; | |
// Approximate value | |
case 'float' : | |
$method = 'float'; | |
break; | |
case 'double' : | |
$parameters = strpos($values->Type, '('); | |
$numbers = ", " . substr($values->Type, $parameters + 1, -1); | |
$method = 'double'; | |
break; | |
// String value | |
case 'char' : | |
$parameters = strpos($values->Type, '('); | |
$numbers = ", " . substr($values->Type, $parameters + 1, -1); | |
$method = 'char'; | |
break; | |
case 'varchar' : | |
$parameters = strpos($values->Type, '('); | |
$numbers = ", " . substr($values->Type, $parameters + 1, -1); | |
$method = 'string'; | |
break; | |
// BLOB and TEXT value | |
case 'blob' : | |
case 'tinyblob' : | |
case 'mediumblob' : | |
case 'longblob' : | |
$method = 'binary'; | |
break; | |
case 'tinytext': | |
case 'text' : | |
$method = 'text'; | |
break; | |
case 'mediumtext' : | |
$method = 'mediumText'; | |
break; | |
case 'longtext' : | |
$method = 'longText'; | |
break; | |
// Date value | |
case 'date' : | |
$method = 'date'; | |
break; | |
case 'time' : | |
$method = 'time'; | |
break; | |
case 'timestamp' : | |
$method = 'timestamp'; | |
break; | |
case 'datetime' : | |
$method = 'dateTime'; | |
break; | |
// Boolean value | |
case 'bool' : | |
case 'boolean' : | |
$method = 'boolean'; | |
break; | |
} | |
if ($values->Key == 'PRI' | |
&& ($tableDescribes->AUTO_INCREMENT == 1 || $allPKToAI)) { | |
$method = ($type === 'bigint') ? 'bigIncrements' : 'increments'; | |
} | |
$up .= "\t\t\t\$table->{$method}('{$values->Field}'{$numbers}){$nullable}{$default}{$unsigned}{$unique};\n"; | |
} | |
$up .= "\n\t\t\t\$table->engine = '{$tableDescribes->ENGINE}';\n\t\t});\n"; | |
$this->schema[$value->table_name] = array( | |
'up' => $up, | |
'down' => $down | |
); | |
$tableForeigners = $this->getForeigners($value->table_name); | |
if (sizeof($tableForeigners) !== 0) { | |
$foreignUp = "\t\t// " . Str::title($value->table_name) . " foreign key constrains\n" | |
. "\t\tSchema::table('{$value->table_name}', function(\$table) {\n"; | |
$foreignDown = "\t\tSchema::table('{$value->table_name}', function(\$table) {\n"; | |
foreach ($tableForeigners as $foreign) { | |
$foreignUp .= "\t\t\t\$table->foreign('{$foreign->COLUMN_NAME}')->references('{$foreign->REFERENCED_COLUMN_NAME}')->on('{$foreign->REFERENCED_TABLE_NAME}');\n"; | |
$foreignDown .= "\t\t\t\$table->dropForeign('{$value->table_name}_{$foreign->COLUMN_NAME}_foreign');\n"; | |
} | |
$foreignUp .= "\t\t});\n"; | |
$foreignDown .= "\t\t});\n"; | |
$this->schema[$value->table_name]['foreign']['up'] = $foreignUp; | |
$this->schema[$value->table_name]['foreign']['down'] = $foreignDown; | |
} | |
} | |
} | |
/** | |
* The snitch. | |
* @return string Messages returned by the class. | |
*/ | |
public function getMessage() | |
{ | |
return $this->message; | |
} | |
/** | |
* Set an array of table names to be ignored. | |
* @param string[] $tables Array of table names to be ignored | |
*/ | |
public function ignore($tables) | |
{ | |
$this->ignore = array_merge($tables, $this->ignore); | |
} | |
/** | |
* Write the schema in migrations for each table. | |
* @param string $migrationsDirectory | |
* @throws Exception | |
*/ | |
public function write($migrationsDirectory = '../database/migrations/') | |
{ | |
usort($this->tables, function ($a, $b) { | |
$fka = count($this->getForeigners($a->table_name)); | |
$fkb = count($this->getForeigners($b->table_name)); | |
if ($fka == $fkb) { | |
return 0; | |
} | |
return ($fka < $fkb) ? -1 : 1; | |
}); | |
$i = 0; | |
foreach ($this->tables as $table) { | |
if (in_array($table->table_name, $this->ignore)) { | |
continue; | |
} | |
$this->date->add(new \DateInterval('PT' . ++$i . 'S')); | |
$migration = $this->compileTable($table); | |
$filename = $this->date->format('Y_m_d_His') . "_create_{$table->table_name}_table.php"; | |
if (file_put_contents("{$migrationsDirectory}{$filename}", $migration) === false) { | |
throw new Exception('Exception during ' . $filename . ' creation.'); | |
} | |
$this->message .= "File {$filename} created.\n"; | |
} | |
} | |
/** | |
* Write the schema to all-in-one migration file. | |
* @param string $migrationsDirectory | |
* @throws Exception | |
*/ | |
public function writeToOneFile($migrationsDirectory = '../database/migrations/') | |
{ | |
$migration = $this->compileDatabase(); | |
$filename = $this->date->format('Y_m_d_His') . "_create_{$this->database}_database.php"; | |
if (file_put_contents("{$migrationsDirectory}{$filename}", $migration) === false) { | |
throw new Exception('Exception during ' . $filename . ' creation.'); | |
} | |
$this->message .= "File {$filename} created.\n"; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment