Development

Symfony2 Doctrine Migrations with Unique Indexes (Slugs)

This is something me and my colleagues encounter from time to time.

The Problem

One of the problems of adding unique indexes to existing data, like adding the sluggable Doctrine behaviour using the StofDoctrineExtensionsBundle, is that the generated migration will end up throwing an error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'UNIQ_BDAFD8C8989D9B62'

Causes

Since the default values for the new MySQL column are not unique, adding the unique index is not possible - which is what the error above is telling us. So we will need to change the migration to also generate the unique values before adding the index.

Solution

In order to do so, we will have to split the generated migration into 2 different migrations, one for adding the new column, which could be a slug, and the other to add the unique index. After running the first migration, we need to execute the code that generates the unique values needed for the index. We can use the postUp method in a Doctrine migration to execute code after the “up” migration finished. We will also need to instantiate and boot the kernel in order to gain access to the Symfony framework and build our functionality like we would do in a controller:

<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20151021133750 extends AbstractMigration
{
    protected static $class = 'AppKernel';
    protected static $kernel;
 
    /**
     * Creates a Kernel.
     *
     * Available options:
     *
     *  * environment
     *  * debug
     *
     * @param array $options An array of options
     *
     * @return HttpKernelInterface A HttpKernelInterface instance
     */
    protected static function createKernel(array $options = array())
    {
        if (null === static::$class) {
            static::$class = static::getKernelClass();
        }
 
        return new static::$class(
            isset($options['environment']) ? $options['environment'] : 'test',
            isset($options['debug']) ? $options['debug'] : true
        );
    }
 
    /**
     * Creates a Client.
     *
     * @param array $options An array of options to pass to the createKernel class
     * @param array $server  An array of server parameters
     *
     * @return Client A Client instance
     */
    protected static function createClient(array $options = array(), array $server = array())
    {
        if (null !== static::$kernel) {
            static::$kernel->shutdown();
        }
 
        static::$kernel = static::createKernel($options);
        static::$kernel->boot();
 
        $client = static::$kernel->getContainer()->get('test.client');
        $client->setServerParameters($server);
 
        return $client;
    }
    
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('ALTER TABLE book ADD slug VARCHAR(128) DEFAULT ""');
        //$this->addSql('CREATE UNIQUE INDEX UNIQ_CBE5A331989D9B62 ON book (slug)');
    }
    
    public function postUp(Schema $schema)
    {
        $this->client = self::createClient();
        $this->em = $this->client->getKernel()->getContainer()->get('doctrine')->getEntityManager();
        
        $books = $this->em->getRepository('AppBundle:Book')->findAll();
        foreach($books as $book){
            // need this so we force the generation of a new slug
            $book->setSlug(null);
            $this->em->persist($book);                                                                            
        }
        $this->em->flush();
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        //$this->addSql('DROP INDEX UNIQ_CBE5A331989D9B62 ON book');
        $this->addSql('ALTER TABLE book DROP slug');
    }
}

For the second migration file we only add the code necessary to add/remove the unique indexes:

<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20151021141028 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('ALTER TABLE book CHANGE slug slug VARCHAR(128) NOT NULL');
        $this->addSql('CREATE UNIQUE INDEX UNIQ_CBE5A331989D9B62 ON book (slug)');
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('DROP INDEX UNIQ_CBE5A331989D9B62 ON book');
        $this->addSql('ALTER TABLE book CHANGE slug slug VARCHAR(128) DEFAULT \'\' COLLATE utf8_unicode_ci');
    }
}

You can generate an empty migration file using the doctrine:migratios:generate command.

If you now run the doctrine:migrations:migrate command everything should be fine and the database should be populated with the unique values we needed in the first place.

Conclusion

Luckily I solved this issue before an important deadline. Let me know if you found any other way around it, or a quicker solution to this issue.

by Dragos Holban