Skip to content

Instantly share code, notes, and snippets.

@ramosmerino
Forked from bruceoutdoors/DbmigrateController.php
Last active January 30, 2017 06:32
Show Gist options
  • Save ramosmerino/632a06672bcee52e192c to your computer and use it in GitHub Desktop.
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.
<?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