如何使用条令迁移创建sql触发器?
这是迁移:
<?php
namespace AppMigrations;
use DoctrineDBALMigrationsAbstractMigration;
use DoctrineDBALSchemaSchema;
use DoctrineDBALSchemaTable;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20151211173441 extends AbstractMigration
{
/**
* @param Schema $schema
*/
public function up(Schema $schema)
{
$table = $schema->createTable('public.users');
$table->addColumn('id', 'integer', [
'autoincrement' => true
]);
$table->addColumn('name', 'string');
$table->setPrimaryKey(['id']);
$this->addSql("
CREATE OR REPLACE FUNCTION public.users_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
NEW.name := NEW.name || ' test';
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
");
$this->addSql("
CREATE TRIGGER users_on_insert_trigger
BEFORE INSERT ON public.users
FOR EACH ROW EXECUTE PROCEDURE public.users_insert_trigger();
");
}
/**
* @param Schema $schema
*/
public function down(Schema $schema)
{
$this->addSql("DROP TRIGGER public.users_on_insert_trigger ON users");
$this->addSql("DROP FUNCTION public.users_insert_trigger()");
$schema->dropTable('users');
}
}
这会产生以下错误:
SQLSTATE[42P01]:未定义的表:7错误:关系"public.users"不存在
并生成以下sql:
CREATE OR REPLACE FUNCTION public.users_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
NEW.name := NEW.name || ' test';
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER users_on_insert_trigger
BEFORE INSERT ON public.users
FOR EACH ROW EXECUTE PROCEDURE public.users_insert_trigger();
CREATE TABLE public.users (id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY(id));
INSERT INTO common.migrations (version) VALUES ('20151211173441');
如果我理解正确的话,模式中的更改将在迁移结束时应用。但它不适合我…
那么,我该怎么处理呢?
您已经正确地总结了这个问题:迁移运行程序实际上在使用模式生成SQL之前执行您通过addSQL()添加的SQL。
您需要将此更改作为两个独立的迁移进行。
好的,我发现了两个相关问题:
- https://github.com/doctrine/migrations/issues/352
- https://github.com/doctrine/migrations/issues/369
所以你可以:
- 正如@wschalle所说,您可以通过两次迁移来处理它
- 另一种方式-不要使用Schema进行迁移(我更喜欢)
没有Schema,您的迁移看起来像这个
<?php
public function up(Schema $schema)
{
$platform = $this->connection->getSchemaManager()->getDatabasePlatform();
$table = new Table('table_name'/*, ...*/);
// ...
$sql = $platform->getCreateTableSQL($table);
// or
$sql = $platform->getDropTableSQL($table);
// etc.
$this->addSql($sql);
}