app/DoctrineMigrations/Version20170719231144.php

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