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     private function getTable($tableName)
  26     {
  27         return $this->container->getParameter('database_table_prefix').$tableName;
  28     }
  29
  30     /**
  31      * @param Schema $schema
  32      */
  33     public function up(Schema $schema)
  34     {
  35         $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.');
  36
  37         // Find tags which need to be merged
  38         $dupTags = $this->connection->query("
  39             SELECT LOWER(label)
  40             FROM   ".$this->getTable('tag')."
  41             GROUP BY LOWER(label)
  42             HAVING COUNT(*) > 1"
  43         );
  44         $dupTags->execute();
  45
  46         foreach ($dupTags->fetchAll() as $duplicates) {
  47             $label = $duplicates['LOWER(label)'];
  48
  49             // Retrieve all duplicate tags for a given tag
  50             $tags = $this->connection->query("
  51                 SELECT id
  52                 FROM   ".$this->getTable('tag')."
  53                 WHERE  LOWER(label) = '".$label."'
  54                 ORDER BY id ASC"
  55             );
  56             $tags->execute();
  57
  58             $first = true;
  59             $newId = null;
  60             $ids = [];
  61
  62             foreach ($tags->fetchAll() as $tag) {
  63                 // Ignore the first tag as we use it as the new reference tag
  64                 if ($first) {
  65                     $first = false;
  66                     $newId = $tag['id'];
  67                 } else {
  68                     $ids[] = $tag['id'];
  69                 }
  70             }
  71
  72             // Just in case...
  73             if (count($ids) > 0) {
  74                 // Merge tags
  75                 $this->addSql("
  76                     UPDATE ".$this->getTable('entry_tag')."
  77                     SET    tag_id = ".$newId."
  78                     WHERE  tag_id IN (".implode(',', $ids).")"
  79                 );
  80
  81                 // Delete unused tags
  82                 $this->addSql("
  83                     DELETE FROM ".$this->getTable('tag')."
  84                     WHERE id IN (".implode(',', $ids).")"
  85                 );
  86             }
  87         }
  88
  89         // Iterate over all tags to lowercase them
  90         $this->addSql("
  91             UPDATE ".$this->getTable('tag')."
  92             SET label = LOWER(label)"
  93         );
  94     }
  95
  96     /**
  97      * @param Schema $schema
  98      */
  99     public function down(Schema $schema)
 100     {
 101         throw new SkipMigrationException('Too complex ...');
 102     }
 103 }