app/DoctrineMigrations/Version20170719231144.php

   1 <?php
   2
   3 namespace Application\Migrations;
   4
   5 use Doctrine\DBAL\Migrations\AbstractMigration;
   6 use Doctrine\DBAL\Schema\Schema;
   7 use Symfony\Component\DependencyInjection\ContainerAwareInterface;
   8 use Symfony\Component\DependencyInjection\ContainerInterface;
   9
  10 /**
  11  * Changed tags to lowercase.
  12  */
  13 class Version20170719231144 extends AbstractMigration implements ContainerAwareInterface
  14 {
  15     /**
  16      * @var ContainerInterface
  17      */
  18     private $container;
  19
  20     public function setContainer(ContainerInterface $container = null)
  21     {
  22         $this->container = $container;
  23     }
  24
  25     /**
  26      * @param Schema $schema
  27      */
  28     public function up(Schema $schema)
  29     {
  30         $this->skipIf('sqlite' === $this->connection->getDatabasePlatform()->getName(), 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.');
  31
  32         // Find tags which need to be merged
  33         $dupTags = $this->connection->query('
  34             SELECT LOWER(label) AS lower_label
  35             FROM   ' . $this->getTable('tag') . '
  36             GROUP BY LOWER(label)
  37             HAVING COUNT(*) > 1'
  38         );
  39         $dupTags->execute();
  40
  41         foreach ($dupTags->fetchAll() as $duplicates) {
  42             $label = $duplicates['lower_label'];
  43
  44             // Retrieve all duplicate tags for a given tag
  45             $tags = $this->connection->executeQuery('
  46                 SELECT id
  47                 FROM   ' . $this->getTable('tag') . '
  48                 WHERE  LOWER(label) = :label
  49                 ORDER BY id ASC',
  50                 [
  51                   'label' => $label,
  52                 ]
  53             );
  54
  55             $first = true;
  56             $newId = null;
  57             $ids = [];
  58
  59             foreach ($tags->fetchAll() as $tag) {
  60                 // Ignore the first tag as we use it as the new reference tag
  61                 if ($first) {
  62                     $first = false;
  63                     $newId = $tag['id'];
  64                 } else {
  65                     $ids[] = $tag['id'];
  66                 }
  67             }
  68
  69             // Just in case...
  70             if (count($ids) > 0) {
  71                 // Merge tags
  72                 $this->addSql('
  73                     UPDATE ' . $this->getTable('entry_tag') . '
  74                     SET    tag_id = ' . $newId . '
  75                     WHERE  tag_id IN (' . implode(',', $ids) . ')
  76                         AND entry_id NOT IN (
  77                            SELECT entry_id
  78                            FROM (SELECT * FROM ' . $this->getTable('entry_tag') . ') AS _entry_tag
  79                            WHERE tag_id = ' . $newId . '
  80                         )'
  81                 );
  82
  83                 // Delete links to unused tags
  84                 $this->addSql('
  85                     DELETE FROM ' . $this->getTable('entry_tag') . '
  86                     WHERE tag_id IN (' . implode(',', $ids) . ')'
  87                 );
  88
  89                 // Delete unused tags
  90                 $this->addSql('
  91                     DELETE FROM ' . $this->getTable('tag') . '
  92                     WHERE id IN (' . implode(',', $ids) . ')'
  93                 );
  94             }
  95         }
  96
  97         // Iterate over all tags to lowercase them
  98         $this->addSql('
  99             UPDATE ' . $this->getTable('tag') . '
 100             SET label = LOWER(label)'
 101         );
 102     }
 103
 104     /**
 105      * @param Schema $schema
 106      */
 107     public function down(Schema $schema)
 108     {
 109         throw new SkipMigrationException('Too complex ...');
 110     }
 111
 112     private function getTable($tableName)
 113     {
 114         return $this->container->getParameter('database_table_prefix') . $tableName;
 115     }
 116 }