]>
Commit | Line | Data |
---|---|---|
206bade5 JB |
1 | <?php |
2 | ||
3 | namespace Application\Migrations; | |
4 | ||
f808b016 | 5 | use Doctrine\DBAL\Migrations\SkipMigrationException; |
206bade5 | 6 | use Doctrine\DBAL\Schema\Schema; |
bfe7a692 | 7 | use Wallabag\CoreBundle\Doctrine\WallabagMigration; |
206bade5 | 8 | |
b87f1712 | 9 | /** |
01736b5a | 10 | * Added pocket_consumer_key field on wallabag_config. |
b87f1712 | 11 | */ |
bfe7a692 | 12 | class Version20161001072726 extends WallabagMigration |
206bade5 | 13 | { |
206bade5 JB |
14 | public function up(Schema $schema) |
15 | { | |
3ef055ce | 16 | $this->skipIf('sqlite' === $this->connection->getDatabasePlatform()->getName(), 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); |
5ce15289 JB |
17 | |
18 | // remove all FK from entry_tag | |
a72f3dc3 JB |
19 | switch ($this->connection->getDatabasePlatform()->getName()) { |
20 | case 'mysql': | |
21 | $query = $this->connection->query(" | |
22 | SELECT CONSTRAINT_NAME | |
23 | FROM information_schema.key_column_usage | |
49b4c875 | 24 | WHERE TABLE_NAME = '" . $this->getTable('entry_tag', WallabagMigration::UN_ESCAPED_TABLE) . "' AND CONSTRAINT_NAME LIKE 'FK_%' |
f808b016 | 25 | AND TABLE_SCHEMA = '" . $this->connection->getDatabase() . "'" |
a72f3dc3 JB |
26 | ); |
27 | $query->execute(); | |
5ce15289 | 28 | |
a72f3dc3 | 29 | foreach ($query->fetchAll() as $fk) { |
f808b016 | 30 | $this->addSql('ALTER TABLE ' . $this->getTable('entry_tag') . ' DROP FOREIGN KEY ' . $fk['CONSTRAINT_NAME']); |
a72f3dc3 JB |
31 | } |
32 | break; | |
a72f3dc3 JB |
33 | case 'postgresql': |
34 | // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk | |
35 | $query = $this->connection->query(" | |
36 | SELECT conrelid::regclass AS table_from | |
37 | ,conname | |
38 | ,pg_get_constraintdef(c.oid) | |
39 | FROM pg_constraint c | |
40 | JOIN pg_namespace n ON n.oid = c.connamespace | |
41 | WHERE contype = 'f' | |
49b4c875 | 42 | AND conrelid::regclass::text = '" . $this->getTable('entry_tag', WallabagMigration::UN_ESCAPED_TABLE) . "' |
a72f3dc3 JB |
43 | AND n.nspname = 'public';" |
44 | ); | |
45 | $query->execute(); | |
46 | ||
47 | foreach ($query->fetchAll() as $fk) { | |
f808b016 | 48 | $this->addSql('ALTER TABLE ' . $this->getTable('entry_tag') . ' DROP CONSTRAINT ' . $fk['conname']); |
a72f3dc3 JB |
49 | } |
50 | break; | |
5ce15289 JB |
51 | } |
52 | ||
f808b016 JB |
53 | $this->addSql('ALTER TABLE ' . $this->getTable('entry_tag') . ' ADD CONSTRAINT FK_entry_tag_entry FOREIGN KEY (entry_id) REFERENCES ' . $this->getTable('entry') . ' (id) ON DELETE CASCADE'); |
54 | $this->addSql('ALTER TABLE ' . $this->getTable('entry_tag') . ' ADD CONSTRAINT FK_entry_tag_tag FOREIGN KEY (tag_id) REFERENCES ' . $this->getTable('tag') . ' (id) ON DELETE CASCADE'); | |
5ce15289 JB |
55 | |
56 | // remove entry FK from annotation | |
5ce15289 | 57 | |
a72f3dc3 JB |
58 | switch ($this->connection->getDatabasePlatform()->getName()) { |
59 | case 'mysql': | |
60 | $query = $this->connection->query(" | |
61 | SELECT CONSTRAINT_NAME | |
62 | FROM information_schema.key_column_usage | |
49b4c875 | 63 | WHERE TABLE_NAME = '" . $this->getTable('annotation', WallabagMigration::UN_ESCAPED_TABLE) . "' |
a72f3dc3 JB |
64 | AND CONSTRAINT_NAME LIKE 'FK_%' |
65 | AND COLUMN_NAME = 'entry_id' | |
f808b016 | 66 | AND TABLE_SCHEMA = '" . $this->connection->getDatabase() . "'" |
a72f3dc3 JB |
67 | ); |
68 | $query->execute(); | |
69 | ||
70 | foreach ($query->fetchAll() as $fk) { | |
f808b016 | 71 | $this->addSql('ALTER TABLE ' . $this->getTable('annotation') . ' DROP FOREIGN KEY ' . $fk['CONSTRAINT_NAME']); |
a72f3dc3 JB |
72 | } |
73 | break; | |
a72f3dc3 JB |
74 | case 'postgresql': |
75 | // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk | |
76 | $query = $this->connection->query(" | |
77 | SELECT conrelid::regclass AS table_from | |
78 | ,conname | |
79 | ,pg_get_constraintdef(c.oid) | |
80 | FROM pg_constraint c | |
81 | JOIN pg_namespace n ON n.oid = c.connamespace | |
82 | WHERE contype = 'f' | |
49b4c875 | 83 | AND conrelid::regclass::text = '" . $this->getTable('annotation', WallabagMigration::UN_ESCAPED_TABLE) . "' |
a72f3dc3 JB |
84 | AND n.nspname = 'public' |
85 | AND pg_get_constraintdef(c.oid) LIKE '%entry_id%';" | |
86 | ); | |
87 | $query->execute(); | |
88 | ||
89 | foreach ($query->fetchAll() as $fk) { | |
f808b016 | 90 | $this->addSql('ALTER TABLE ' . $this->getTable('annotation') . ' DROP CONSTRAINT ' . $fk['conname']); |
a72f3dc3 JB |
91 | } |
92 | break; | |
5ce15289 JB |
93 | } |
94 | ||
f808b016 | 95 | $this->addSql('ALTER TABLE ' . $this->getTable('annotation') . ' ADD CONSTRAINT FK_annotation_entry FOREIGN KEY (entry_id) REFERENCES ' . $this->getTable('entry') . ' (id) ON DELETE CASCADE'); |
206bade5 JB |
96 | } |
97 | ||
206bade5 JB |
98 | public function down(Schema $schema) |
99 | { | |
5ce15289 | 100 | throw new SkipMigrationException('Too complex ...'); |
206bade5 JB |
101 | } |
102 | } |