将 MySQL 中已存在的数据表转化为 Laravel 迁移文件
说明：本代码基于Laravel 4，Laravel 5中使用需要做相应目录调整
简介
下面的代码片段创建了一个Artisan命令用来读取已存在的MySQL数据表结构并创建与之对应的Laravel迁移文件。
当你需要手动创建字段到数据库或者需要创建大量迁移文件时这段代码就会派上用场。这段代码执行后只会生成单个可以用于创建所有表和字段的迁移。
安装
将下面这段代码片段拷贝到app/commands目录下并将其命名为ConvertMigrationsCommand.php。
然后添加
Artisan::add(new ConvertMigrationsCommand);
到app/start/artisan.php文件。
使用
现在运行php artisan就会看到convert:migrations选项，你可以通过运行
php artisan convert:migrations myDatabaseName
使用这个命令。
如果你想要忽略指定表可以使用--ignore选项（逗号分隔多个表）：
php artisan convert:migrations myDatabaseName --ignore="table1, table2"
GitHub
https://github.com/adamkearsley/laravel-convert-migrations
代码
<?php
use Illuminate\Console\Command;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Input\InputArgument;
class ConvertMigrationsCommand extends Command {
/**
* The console command name.
*
* @var string
*/
protected $name = 'convert:migrations';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Converts an existing MySQL database to migrations.';
/**
* Create a new command instance.
*
* @return void
*/
public function __construct()
{
parent::__construct();
}
/**
* Execute the console command.
*
* @return mixed
*/
public function fire()
{
$ignoreInput = str_replace(' ', '', $this->option('ignore'));
$ignoreInput = explode(',', $ignoreInput);
$migrate = new SqlMigrations;
$migrate->ignore($ignoreInput);
$migrate->convert($this->argument('database'));
$migrate->write();
$this->info('Migration Created Successfully');
}
/**
* Get the console command arguments.
*
* @return array
*/
protected function getArguments()
{
return array(
array('database', InputArgument::REQUIRED, 'Name of Database to convert'),
);
}
/**
* Get the console command options.
*
* @return array
*/
protected function getOptions()
{
return array(
array('ignore', null, InputOption::VALUE_OPTIONAL, 'Tables to ignore', null),
);
}
}
class SqlMigrations
{
private static $ignore = array('migrations');
private static $database = "";
private static $migrations = false;
private static $schema = array();
private static $selects = array(
'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 static $instance;
private static $up = "";
private static $down = "";
private static function getTables()
{
return DB::select('SELECT table_name FROM information_schema.tables WHERE table_schema="' . self::$database . '"');
}
private static function getTableDescribes($table)
{
return DB::table('information_schema.columns')
->where('table_schema', '=', self::$database)
->where('table_name', '=', $table)
->get(self::$selects);
}
private static function getForeignTables()
{
return DB::table('information_schema.KEY_COLUMN_USAGE')
->where('CONSTRAINT_SCHEMA', '=', self::$database)
->where('REFERENCED_TABLE_SCHEMA', '=', self::$database)
->select('TABLE_NAME')->distinct()
->get();
}
private static function getForeigns($table)
{
return DB::table('information_schema.KEY_COLUMN_USAGE')
->where('CONSTRAINT_SCHEMA', '=', self::$database)
->where('REFERENCED_TABLE_SCHEMA', '=', self::$database)
->where('TABLE_NAME', '=', $table)
->select('COLUMN_NAME', 'REFERENCED_TABLE_NAME', 'REFERENCED_COLUMN_NAME')
->get();
}
private static function compileSchema()
{
$upSchema = "";
$downSchema = "";
$newSchema = "";
foreach (self::$schema as $name => $values) {
if (in_array($name, self::$ignore)) {
continue;
}
$upSchema .= "{$values['up']}";
$downSchema .= "{$values['down']}";
}
$schema = "<?php
//
// NOTE Migration Created: " . date("Y-m-d H:i:s") . "
// --------------------------------------------------
class Create" . str_replace('_', '', Str::title(self::$database)) . "Database {
//
// NOTE - Make changes to the database.
// --------------------------------------------------
public function up()
{
" . $upSchema . "
" . self::$up . "
}
//
// NOTE - Revert the changes to the database.
// --------------------------------------------------
public function down()
{
" . $downSchema . "
" . self::$down . "
}
}";
return $schema;
}
public function up($up)
{
self::$up = $up;
return self::$instance;
}
public function down($down)
{
self::$down = $down;
return self::$instance;
}
public function ignore($tables)
{
self::$ignore = array_merge($tables, self::$ignore);
return self::$instance;
}
public function migrations()
{
self::$migrations = true;
return self::$instance;
}
public function write()
{
$schema = self::compileSchema();
$filename = date('Y_m_d_His') . "_create_" . self::$database . "_database.php";
$path = app_path('database/migrations/');
file_put_contents($path.$filename, $schema);
}
public function get()
{
return self::compileSchema();
}
public function convert($database)
{
self::$instance = new self();
self::$database = $database;
$table_headers = array('Field', 'Type', 'Null', 'Key', 'Default', 'Extra');
$tables = self::getTables();
foreach ($tables as $key => $value) {
if (in_array($value->table_name, self::$ignore)) {
continue;
}
$down = "Schema::drop('{$value->table_name}');";
$up = "Schema::create('{$value->table_name}', function($" . "table) {\n";
$tableDescribes = self::getTableDescribes($value->table_name);
foreach ($tableDescribes as $values) {
$method = "";
$para = strpos($values->Type, '(');
$type = $para > -1 ? substr($values->Type, 0, $para) : $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) {
case 'int' :
$method = 'unsignedInteger';
break;
case 'char' :
case 'varchar' :
$para = strpos($values->Type, '(');
$numbers = ", " . substr($values->Type, $para + 1, -1);
$method = 'string';
break;
case 'float' :
$method = 'float';
break;
case 'decimal' :
$para = strpos($values->Type, '(');
$numbers = ", " . substr($values->Type, $para + 1, -1);
$method = 'decimal';
break;
case 'tinyint' :
$method = 'boolean';
break;
case 'date':
$method = 'date';
break;
case 'timestamp' :
$method = 'timestamp';
break;
case 'datetime' :
$method = 'dateTime';
break;
case 'mediumtext' :
$method = 'mediumtext';
break;
case 'text' :
$method = 'text';
break;
}
if ($values->Key == 'PRI') {
$method = 'increments';
}
$up .= " $" . "table->{$method}('{$values->Field}'{$numbers}){$nullable}{$default}{$unsigned}{$unique};\n";
}
$up .= " });\n\n";
self::$schema[$value->table_name] = array(
'up' => $up,
'down' => $down
);
}
// add foreign constraints, if any
$tableForeigns = self::getForeignTables();
if (sizeof($tableForeigns) !== 0) {
foreach ($tableForeigns as $key => $value) {
$up = "Schema::table('{$value->TABLE_NAME}', function($" . "table) {\n";
$foreign = self::getForeigns($value->TABLE_NAME);
foreach ($foreign as $k => $v) {
$up .= " $" . "table->foreign('{$v->COLUMN_NAME}')->references('{$v->REFERENCED_COLUMN_NAME}')->on('{$v->REFERENCED_TABLE_NAME}');\n";
}
$up .= " });\n\n";
self::$schema[$value->TABLE_NAME . '_foreign'] = array(
'up' => $up,
'down' => $down
);
}
}
return self::$instance;
}
}
12 Comments