Doctrine2迁移使用DBAL而不是$ this-> addSql [英] Doctrine2 Migration Using DBAL instead of $this->addSql

问题描述

所以我做了一堆Doctrine2迁移（https://github.com/doctrine/migrations），但是我有一个新的迁移问题我试图做。

So I've done a bunch of Doctrine2 migrations (https://github.com/doctrine/migrations) but I have a question for a new migration I'm trying to do.

我一直在挖掘图书馆，我看到 $ this-> addSql（）用于构建SQL列表执行，然后稍后执行。

I've been digging into the library a little and I see that $this->addSql() is used to build a list of SQL to execute and then it gets executed later.

我想做一些我选择一些数据的东西，迭代行，根据这些数据插入新的数据，然后删除我选择的数据这很容易使DBAL图书馆很自然，但我想知道，我可以安全地在迁移中使用 protected $ connection 吗？或者这是坏的，因为它会在我的任何 $ this-> addSql（） SQL执行之前执行语句？也似乎这样会从我在代码中看到的中断 dry-run 设置。有没有人有过这种类型的迁移的经验？有没有最佳实践？

I wanted to do something where I select some data, iterate over the rows, insert new data based on that, and then delete the data I selected. This lends itself to the DBAL library pretty easily, but I'm wondering, can I use the protected $connection in a migration safely? Or is that bad because it would execute statements before any of my $this->addSql() SQL gets executed? Also it seems like this would break the dry-run setting from what I've seen in the code. Has anyone had any experience with this type of migration? Are there any best practices?

以下是我想要迁移的内容，但我不确定Doctrine Migrations支持：

The following is the migration I want to do, but I'm not confident that this is supported by Doctrine Migrations:

public function up(Schema $schema)
{
    // this up() migration is autogenerated, please modify it to your needs
    $this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql");

    $this->addSql("ALTER TABLE article_enclosures ADD is_scrape TINYINT(1) NOT NULL");
    $this->addSql("ALTER TABLE images DROP FOREIGN KEY FK_E01FBE6AA536AAC7");

    // now lets take all images with a scrape and convert the scrape to an enclosure
    // 
    // Select all images where not scrape_id is null (join on article_image_scrape)
    // for each image:
    //     insert into article_enclosures
    //     update image set enclosure_id = new ID
    //     delete from article_image_scrape where id...
    //
    // insert into article_enclosures select article_image_scrapes...

    $sql = "SELECT i.id img_id, e.* FROM images i JOIN article_image_scrapes e ON i.scrape_id = e.id";
    $stmt = $this->connection->prepare($sql);
    $stmt->execute();
    $scrapesToDelete = array();
    while ($row = $stmt->fetch()) {
        $scrapeArticle = $row['article_id'];
        $scrapeOldId = $row['id'];
        $scrapeUrl = $row['url'];
        $scrapeExtension = $row['extension'];
        $scrapeUrlHash = $row['url_hash'];
        $imageId = $row['image_id'];

        $this->connection->insert('article_enclosures', array(
            'url' => $scrapeUrl,
            'extension' => $scrapeExtension,
            'url_hash' => $scrapeUrlHash
        ));

        $scrapeNewId = $this->connection->lastInsertId();

        $this->connection->update('images', array(
            'enclosure_id' => $scrapeNewId,
            'scrape_id' => null
        ), array(
            'id' => $imageId
        ));

        $scrapesToDelete[] = $scrapeOldId;
    }

    foreach ($scrapesToDelete as $id) {
        $this->connection->delete('article_image_scrapes', array('id' => $id));
    }

    $this->addSql("INSERT INTO article_scrapes (article_id, url, extension, url_hash) "
            ."SELECT s.id, s.url, s.extension, s.url_hash"
            ."FROM article_image_scrapes s");

    $this->addSql("DROP INDEX IDX_E01FBE6AA536AAC7 ON images");
    $this->addSql("ALTER TABLE images DROP scrape_id, CHANGE enclosure_id enclosure_id INT NOT NULL");
}

推荐答案

您可以使用 $ connection 像这样

$result = $this->connection->fetchAssoc('SELECT id, name FROM table1 WHERE id = 1');
$this->abortIf(!$result, 'row with id not found');
$this->abortIf($result['name'] != 'jo', 'id 1 is not jo');
// etc..

您应该只读数据库，而不使用连接进行更新/删除，因此不会破坏干运行选项。

You should only read the database and not use the connection to make update/delete so it won't break the dry-run option.

在您的示例中，您应该进行两次迁移。第一个会做两个alter table。第二个将执行刮刮图像并将刮刀转换为外壳程序。如果出现问题，使用多次迁移更容易恢复。

In your example, you should do two migrations. The first will do the two alter table. The second will do the "images with a scrape and convert the scrape to an enclosure" routine. Using multiple migration is easier to revert them if something goes wrong.

这篇关于Doctrine2迁移使用DBAL而不是$ this-> addSql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！