]>
Commit | Line | Data |
---|---|---|
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 | } |