sql - 如何将 Laravel 迁移转换为原始 SQL 脚本？

Question

我团队的开发人员真的习惯了 Laravel 迁移的强大功能，他们在本地机器和我们的开发服务器上工作得很好。
但是客户的数据库管理员不会接受 Laravel 迁移。他要求为我们应用程序的每个新版本提供原始 SQL 脚本。

是否有任何工具或编程技术可以捕获从 Laravel 迁移到上/下 SQL 脚本的输出？

如果我们可以在创建生产构建时将 SQL 脚本生成集成到我们的 CI 系统 (TeamCity) 中，那将是完美的。

顺便说一下，我们将在这个项目中使用 Laravel 5 和 PostgreSQL。

Answer 1

使用迁移命令

您可以添加 --pretend运行时标记 php artisan migrate将查询输出到终端:

php artisan migrate --pretend

这看起来像这样:

Migration table created successfully.
CreateUsersTable: create table "users" ("id" integer not null primary key autoincrement, "name" varchar not null, "email" varchar not null, "password" varchar not null, "remember_token" varchar null, "created_at" datetime not null, "updated_at" datetime not null)
CreateUsersTable: create unique index users_email_unique on "users" ("email")
CreatePasswordResetsTable: create table "password_resets" ("email" varchar not null, "token" varchar not null, "created_at" datetime not null)
CreatePasswordResetsTable: create index password_resets_email_index on "password_resets" ("email")
CreatePasswordResetsTable: create index password_resets_token_index on "password_resets" ("token")

要将其保存到文件中，只需重定向输出 没有ansi :

php artisan migrate --pretend --no-ansi > migrate.sql

This command only include the migrations that hasn't been migrated yet.

破解迁移命令

要进一步自定义获取查询的方式，请考虑破解源代码并制作您自己的自定义命令或类似的东西。为了让您开始，这里有一些快速代码来获取所有迁移。

示例代码

$migrator = app('migrator');
$db = $migrator->resolveConnection(null);
$migrations = $migrator->getMigrationFiles('database/migrations');
$queries = [];

foreach($migrations as $migration) {
    $migration_name = $migration;
    $migration = $migrator->resolve($migration);

    $queries[] = [
        'name' => $migration_name,
        'queries' => array_column($db->pretend(function() use ($migration) { $migration->up(); }), 'query'),
    ];
}

dd($queries);

示例输出

array:2 [
  0 => array:2 [
    "name" => "2014_10_12_000000_create_users_table"
    "queries" => array:2 [
      0 => "create table "users" ("id" integer not null primary key autoincrement, "name" varchar not null, "email" varchar not null, "password" varchar not null, "remember_token" varchar null, "created_at" datetime not null, "updated_at" datetime not null)"
      1 => "create unique index users_email_unique on "users" ("email")"
    ]
  ]
  1 => array:2 [
    "name" => "2014_10_12_100000_create_password_resets_table"
    "queries" => array:3 [
      0 => "create table "password_resets" ("email" varchar not null, "token" varchar not null, "created_at" datetime not null)"
      1 => "create index password_resets_email_index on "password_resets" ("email")"
      2 => "create index password_resets_token_index on "password_resets" ("token")"
    ]
  ]
]

This code will include all the migrations. To see how to only get what isn't already migrated take a look at the run() method in vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php.