How do I write a Doctrine migration which can redistribute data into new tables

Question

I have a database (which was actually created using Propel in a Symfony1 application). I am reimplementing it in Symfony2 and Doctrine, but I also want to take the opportunity to refactor the database somewhat.

I have defined a set of Doctrine Entities and run doctrine:migrations:diff, which has created me a basic Migration to add tables, columns and constraints, and drop a load of columns.

However, before dropping these columns I want to copy the data into some of the new tables, and then link the new records in these table to new columns in the first table. I don't believe it's possible to do this in pure SQL (in general, the contents of one table are being distributed among three or four tables).

This gave me a hint, and caused me to find this (which I had skipped, because I had no idea what relevance "containers" might be to my problem).

But what I have not found anywhere in the Symfony or Doctrine documentation is an example of actually moving data around in a migration - which to me seems to be one of the core purposes of migrations!

It is possible that I could use the hints in those links above, but then I'm not sure how to proceed. I do not have (and don't really want to take the time to create, though I'm sure I could do) Doctrine entities for the existing database schema: can I then use DQL? I simply don't know.

So two questions:

1. Can somebody give me an example of a Doctrine migration which moves data between tables?

2. Alternatively, can anybody clarify how dependent the syntax of DQL is on the definitions of the Entities in Doctrine? Can I use it to specify columns which are not in the Entity definitions?

Lammergeier

The container is just the Symfony 2 dependency injection container. Not really relevant. The migration stuff is mostly based on having Doctrine 2 elements right from the start. And DQL requires you have all your column relations defined. – Generable

What is do is to get the database connections object, use sql to query the legacy database and store the results on a yaml file. I then load the new database from the yaml file. Probably not the best way to do things but it's pretty straight forward. – Generable

Answer 1

OK, I seem to have found it, from a number of sources (including this) and trial and error.

Cerad's comments were a little help, but mainly I'm doing it by using the DBAL layer to read in the data (which I can get at by $this->connection), and the ORM to save the new data (which requires the EntityManager, so I did have to use the trick with the container).

I put all the code in postUp(), including the generated code to drop columns from tables.

Sample bits of my code:

use Symfony\Component\DependencyInjection\ContainerAwareInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;

use PG\InventoryBundle\Entity\Item;
use PG\InventoryBundle\Entity\Address;
         .
         .
         .

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20140519211228 extends AbstractMigration implements ContainerAwareInterface
{
  private $container;

  public function setContainer(ContainerInterface $container = null)
  {
    $this->container = $container;
  }

  public function up(Schema $schema)
  {
         .
         .
         .
  }
}

public function postUp(Schema $schema)
{
    $em = $this->container->get('doctrine.orm.entity_manager');
    // ... update the entities
    $query = "SELECT * FROM item";
    $stmt = $this->connection->prepare($query);
    $stmt->execute();

    // We can't use Doctrine's ORM to fetch the item, because it has a load of extra fields
    // that aren't in the entity definition.
    while ($row = $stmt->fetch()) {
      // But we will also get the entity, so that we can put addresses in it.
      $id = $row['id'];
      // And create new objects
      $stock = new Stock();
         .
         .
         .

      $stock->setAssetNo($row['asset_no']);
      $stock->setItemId($row['id']);
      $em->persist($stock);

      $em->flush();
    }

    // Now we can drop fields we don't need. 
    $this->connection->executeQuery("ALTER TABLE item DROP container_id");
    $this->connection->executeQuery("ALTER TABLE item DROP location_id");
         .
         .
         .

 }

Lammergeier

To drop the tables in postUp the connection will need to be used directly like so; $this->connection->executeQuery("ALTER TABLE item DROP container_id"); – Kennithkennon

What is the purpose of the $item variable inside the while loop? It gets set but then isn't used or modified. – Sillabub

It's used in the material I redacted - I could have redacted that line as well. – Lammergeier