title: Doctrine2 Migration Using DBAL instead of $this->addSql
body: <p>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.</p>
  
  <p>I've been digging into the library a little and I see that <code>$this-&gt;addSql()</code> is used to build a list of SQL to execute and then it gets executed later.</p>
  
  <p>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 <code>protected $connection</code> in a migration safely? Or is that bad because it would execute statements before any of my <code>$this-&gt;addSql()</code> SQL gets executed? Also it seems like this would break the <code>dry-run</code> setting from what I've seen in the code. Has anyone had any experience with this type of migration? Are there any best practices?</p>
  
  <p>The following is the migration I want to do, but I'm not confident that this is supported by Doctrine Migrations:</p>
  
  <pre><code>public function up(Schema $schema)
  {
      // this up() migration is autogenerated, please modify it to your needs
      $this-&gt;abortIf($this-&gt;connection-&gt;getDatabasePlatform()-&gt;getName() != "mysql");
  
      $this-&gt;addSql("ALTER TABLE article_enclosures ADD is_scrape TINYINT(1) NOT NULL");
      $this-&gt;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-&gt;connection-&gt;prepare($sql);
      $stmt-&gt;execute();
      $scrapesToDelete = array();
      while ($row = $stmt-&gt;fetch()) {
          $scrapeArticle = $row['article_id'];
          $scrapeOldId = $row['id'];
          $scrapeUrl = $row['url'];
          $scrapeExtension = $row['extension'];
          $scrapeUrlHash = $row['url_hash'];
          $imageId = $row['image_id'];
  
          $this-&gt;connection-&gt;insert('article_enclosures', array(
              'url' =&gt; $scrapeUrl,
              'extension' =&gt; $scrapeExtension,
              'url_hash' =&gt; $scrapeUrlHash
          ));
  
          $scrapeNewId = $this-&gt;connection-&gt;lastInsertId();
  
          $this-&gt;connection-&gt;update('images', array(
              'enclosure_id' =&gt; $scrapeNewId,
              'scrape_id' =&gt; null
          ), array(
              'id' =&gt; $imageId
          ));
  
          $scrapesToDelete[] = $scrapeOldId;
      }
  
      foreach ($scrapesToDelete as $id) {
          $this-&gt;connection-&gt;delete('article_image_scrapes', array('id' =&gt; $id));
      }
  
      $this-&gt;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-&gt;addSql("DROP INDEX IDX_E01FBE6AA536AAC7 ON images");
      $this-&gt;addSql("ALTER TABLE images DROP scrape_id, CHANGE enclosure_id enclosure_id INT NOT NULL");
  }
  </code></pre>