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)
  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->query('
  46                 SELECT id
  47                 FROM   ' . $this->getTable('tag') . "
  48                 WHERE  LOWER(label) = '" . $label . "'
  49                 ORDER BY id ASC"
  50             );
  51             $tags->execute();
  52
  53             $first = true;
  54             $newId = null;
  55             $ids = [];
  56
  57             foreach ($tags->fetchAll() as $tag) {
  58                 // Ignore the first tag as we use it as the new reference tag
  59                 if ($first) {
  60                     $first = false;
  61                     $newId = $tag['id'];
  62                 } else {
  63                     $ids[] = $tag['id'];
  64                 }
  65             }
  66
  67             // Just in case...
  68             if (count($ids) > 0) {
  69                 // Merge tags
  70                 $this->addSql('
  71                     UPDATE ' . $this->getTable('entry_tag') . '
  72                     SET    tag_id = ' . $newId . '
  73                     WHERE  tag_id IN (' . implode(',', $ids) . ')'
  74                 );
  75
  76                 // Delete unused tags
  77                 $this->addSql('
  78                     DELETE FROM ' . $this->getTable('tag') . '
  79                     WHERE id IN (' . implode(',', $ids) . ')'
  80                 );
  81             }
  82         }
  83
  84         // Iterate over all tags to lowercase them
  85         $this->addSql('
  86             UPDATE ' . $this->getTable('tag') . '
  87             SET label = LOWER(label)'
  88         );
  89     }
  90
  91     /**
  92      * @param Schema $schema
  93      */
  94     public function down(Schema $schema)
  95     {
  96         throw new SkipMigrationException('Too complex ...');
  97     }
  98
  99     private function getTable($tableName)
 100     {
 101         return $this->container->getParameter('database_table_prefix') . $tableName;
 102     }
 103 }