Add migration to change case of tags

This migration does not support SQLite as long as this engine does not
support Unicode in LOWER().

This migration starts by retrieving the list of lowercase tags which
need to be migrated. Then it retrieves the list of tags for each tags
from the previous step in order to migrate entries. It handles deletion
of empty tags. At the end the migration makes a full scan to update the
label of all remaining tags.

WARNING: THIS MIGRATION IS IRREVERSIBLE.

Signed-off-by: Kevin Decherf <kevin@kdecherf.com>

1 files changed, 103 insertions, 0 deletions

diff --git a/app/DoctrineMigrations/Version20170719231144.php b/app/DoctrineMigrations/Version20170719231144.php
new file mode 100644
index 00000000..691eae51
--- /dev/null
+++ b/app/DoctrineMigrations/Version20170719231144.php
@@ -0,0 +1,103 @@
+<?php
+
+namespace Application\Migrations;
+
+use Doctrine\DBAL\Migrations\AbstractMigration;
+use Doctrine\DBAL\Schema\Schema;
+use Symfony\Component\DependencyInjection\ContainerAwareInterface;
+use Symfony\Component\DependencyInjection\ContainerInterface;
+
+/**
+ * Changed tags to lowercase
+ */
+class Version20170719231144 extends AbstractMigration implements ContainerAwareInterface
+{
+    /**
+     * @var ContainerInterface
+     */
+    private $container;
+
+    public function setContainer(ContainerInterface $container = null)
+    {
+        $this->container = $container;
+    }
+
+    private function getTable($tableName)
+    {
+        return $this->container->getParameter('database_table_prefix').$tableName;
+    }
+
+    /**
+     * @param Schema $schema
+     */
+    public function up(Schema $schema)
+    {
+        $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.');
+        
+        // Find tags which need to be merged
+        $dupTags = $this->connection->query("
+            SELECT LOWER(label)
+            FROM   ".$this->getTable('tag')."
+            GROUP BY LOWER(label)
+            HAVING COUNT(*) > 1"
+        );
+        $dupTags->execute();
+
+        foreach ($dupTags->fetchAll() as $duplicates) {
+            $label = $duplicates['LOWER(label)'];
+
+            // Retrieve all duplicate tags for a given tag
+            $tags = $this->connection->query("
+                SELECT id
+                FROM   ".$this->getTable('tag')."
+                WHERE  LOWER(label) = '".$label."'
+                ORDER BY id ASC"
+            );
+            $tags->execute();
+
+            $first = true;
+            $newId = null;
+            $ids = [];
+
+            foreach ($tags->fetchAll() as $tag) {
+                // Ignore the first tag as we use it as the new reference tag
+                if ($first) {
+                    $first = false;
+                    $newId = $tag['id'];
+                } else {
+                    $ids[] = $tag['id'];
+                }
+            }
+
+            // Just in case...
+            if (count($ids) > 0) {
+                // Merge tags
+                $this->addSql("
+                    UPDATE ".$this->getTable('entry_tag')."
+                    SET    tag_id = ".$newId."
+                    WHERE  tag_id IN (".implode(',', $ids).")"
+                );
+
+                // Delete unused tags
+                $this->addSql("
+                    DELETE FROM ".$this->getTable('tag')."
+                    WHERE id IN (".implode(',', $ids).")"
+                );
+            }
+        }
+
+        // Iterate over all tags to lowercase them
+        $this->addSql("
+            UPDATE ".$this->getTable('tag')."
+            SET label = LOWER(label)"
+        );
+    }
+
+    /**
+     * @param Schema $schema
+     */
+    public function down(Schema $schema)
+    {
+        throw new SkipMigrationException('Too complex ...');
+    }
+}