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