From bd164a75c42accdc1601a69d101e759d4326e018 Mon Sep 17 00:00:00 2001 From: Kevin Decherf Date: Thu, 20 Jul 2017 22:05:44 +0200 Subject: 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 --- app/DoctrineMigrations/Version20170719231144.php | 103 +++++++++++++++++++++++ 1 file changed, 103 insertions(+) create mode 100644 app/DoctrineMigrations/Version20170719231144.php (limited to 'app/DoctrineMigrations/Version20170719231144.php') 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 @@ +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 ...'); + } +} -- cgit v1.2.3 From 7b4f66881d694c948aca9372da6362b8873de6bb Mon Sep 17 00:00:00 2001 From: Kevin Decherf Date: Sun, 27 Aug 2017 16:59:02 +0200 Subject: php-cs-fixer on DoctrineMigrations/Version20170719231144 Signed-off-by: Kevin Decherf --- app/DoctrineMigrations/Version20170719231144.php | 48 ++++++++++++------------ 1 file changed, 24 insertions(+), 24 deletions(-) (limited to 'app/DoctrineMigrations/Version20170719231144.php') diff --git a/app/DoctrineMigrations/Version20170719231144.php b/app/DoctrineMigrations/Version20170719231144.php index 691eae51..0f5fa75a 100644 --- a/app/DoctrineMigrations/Version20170719231144.php +++ b/app/DoctrineMigrations/Version20170719231144.php @@ -8,7 +8,7 @@ use Symfony\Component\DependencyInjection\ContainerAwareInterface; use Symfony\Component\DependencyInjection\ContainerInterface; /** - * Changed tags to lowercase + * Changed tags to lowercase. */ class Version20170719231144 extends AbstractMigration implements ContainerAwareInterface { @@ -22,24 +22,19 @@ class Version20170719231144 extends AbstractMigration implements ContainerAwareI $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\'.'); - + $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(" + $dupTags = $this->connection->query(' SELECT LOWER(label) - FROM ".$this->getTable('tag')." + FROM ' . $this->getTable('tag') . ' GROUP BY LOWER(label) - HAVING COUNT(*) > 1" + HAVING COUNT(*) > 1' ); $dupTags->execute(); @@ -47,10 +42,10 @@ class Version20170719231144 extends AbstractMigration implements ContainerAwareI $label = $duplicates['LOWER(label)']; // Retrieve all duplicate tags for a given tag - $tags = $this->connection->query(" + $tags = $this->connection->query(' SELECT id - FROM ".$this->getTable('tag')." - WHERE LOWER(label) = '".$label."' + FROM ' . $this->getTable('tag') . " + WHERE LOWER(label) = '" . $label . "' ORDER BY id ASC" ); $tags->execute(); @@ -72,24 +67,24 @@ class Version20170719231144 extends AbstractMigration implements ContainerAwareI // 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).")" + $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).")" + $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)" + $this->addSql(' + UPDATE ' . $this->getTable('tag') . ' + SET label = LOWER(label)' ); } @@ -100,4 +95,9 @@ class Version20170719231144 extends AbstractMigration implements ContainerAwareI { throw new SkipMigrationException('Too complex ...'); } + + private function getTable($tableName) + { + return $this->container->getParameter('database_table_prefix') . $tableName; + } } -- cgit v1.2.3 From 3ef055ced3d6ea0d2f15ba660602545f477e9c3c Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 9 Oct 2017 16:47:15 +0200 Subject: CS --- app/DoctrineMigrations/Version20170719231144.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/DoctrineMigrations/Version20170719231144.php') diff --git a/app/DoctrineMigrations/Version20170719231144.php b/app/DoctrineMigrations/Version20170719231144.php index 0f5fa75a..0c749150 100644 --- a/app/DoctrineMigrations/Version20170719231144.php +++ b/app/DoctrineMigrations/Version20170719231144.php @@ -27,7 +27,7 @@ class Version20170719231144 extends AbstractMigration implements ContainerAwareI */ public function up(Schema $schema) { - $this->skipIf($this->connection->getDatabasePlatform()->getName() === 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); + $this->skipIf('sqlite' === $this->connection->getDatabasePlatform()->getName(), 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); // Find tags which need to be merged $dupTags = $this->connection->query(' -- cgit v1.2.3