diff options
| -rw-r--r-- | app/DoctrineMigrations/Version20161001072726.php | 76 |
1 files changed, 68 insertions, 8 deletions
diff --git a/app/DoctrineMigrations/Version20161001072726.php b/app/DoctrineMigrations/Version20161001072726.php index 5ab88555..f6930778 100644 --- a/app/DoctrineMigrations/Version20161001072726.php +++ b/app/DoctrineMigrations/Version20161001072726.php | |||
| @@ -32,22 +32,82 @@ class Version20161001072726 extends AbstractMigration implements ContainerAwareI | |||
| 32 | $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); | 32 | $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); |
| 33 | 33 | ||
| 34 | // remove all FK from entry_tag | 34 | // remove all FK from entry_tag |
| 35 | $query = $this->connection->query("SELECT CONSTRAINT_NAME FROM information_schema.key_column_usage WHERE TABLE_NAME = '".$this->getTable('entry_tag')."' AND CONSTRAINT_NAME LIKE 'FK_%' AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'"); | 35 | switch ($this->connection->getDatabasePlatform()->getName()) { |
| 36 | $query->execute(); | 36 | case 'mysql': |
| 37 | $query = $this->connection->query(" | ||
| 38 | SELECT CONSTRAINT_NAME | ||
| 39 | FROM information_schema.key_column_usage | ||
| 40 | WHERE TABLE_NAME = '".$this->getTable('entry_tag')."' AND CONSTRAINT_NAME LIKE 'FK_%' | ||
| 41 | AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'" | ||
| 42 | ); | ||
| 43 | $query->execute(); | ||
| 37 | 44 | ||
| 38 | foreach ($query->fetchAll() as $fk) { | 45 | foreach ($query->fetchAll() as $fk) { |
| 39 | $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); | 46 | $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); |
| 47 | } | ||
| 48 | break; | ||
| 49 | |||
| 50 | case 'postgresql': | ||
| 51 | // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk | ||
| 52 | $query = $this->connection->query(" | ||
| 53 | SELECT conrelid::regclass AS table_from | ||
| 54 | ,conname | ||
| 55 | ,pg_get_constraintdef(c.oid) | ||
| 56 | FROM pg_constraint c | ||
| 57 | JOIN pg_namespace n ON n.oid = c.connamespace | ||
| 58 | WHERE contype = 'f' | ||
| 59 | AND conrelid::regclass::text = '".$this->getTable('entry_tag')."' | ||
| 60 | AND n.nspname = 'public';" | ||
| 61 | ); | ||
| 62 | $query->execute(); | ||
| 63 | |||
| 64 | foreach ($query->fetchAll() as $fk) { | ||
| 65 | $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP CONSTRAINT '.$fk['conname']); | ||
| 66 | } | ||
| 67 | break; | ||
| 40 | } | 68 | } |
| 41 | 69 | ||
| 42 | $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'); | 70 | $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'); |
| 43 | $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'); | 71 | $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'); |
| 44 | 72 | ||
| 45 | // remove entry FK from annotation | 73 | // remove entry FK from annotation |
| 46 | $query = $this->connection->query("SELECT CONSTRAINT_NAME FROM information_schema.key_column_usage WHERE TABLE_NAME = '".$this->getTable('annotation')."' AND CONSTRAINT_NAME LIKE 'FK_%' and COLUMN_NAME = 'entry_id' AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'"); | ||
| 47 | $query->execute(); | ||
| 48 | 74 | ||
| 49 | foreach ($query->fetchAll() as $fk) { | 75 | switch ($this->connection->getDatabasePlatform()->getName()) { |
| 50 | $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); | 76 | case 'mysql': |
| 77 | $query = $this->connection->query(" | ||
| 78 | SELECT CONSTRAINT_NAME | ||
| 79 | FROM information_schema.key_column_usage | ||
| 80 | WHERE TABLE_NAME = '".$this->getTable('annotation')."' | ||
| 81 | AND CONSTRAINT_NAME LIKE 'FK_%' | ||
| 82 | AND COLUMN_NAME = 'entry_id' | ||
| 83 | AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'" | ||
| 84 | ); | ||
| 85 | $query->execute(); | ||
| 86 | |||
| 87 | foreach ($query->fetchAll() as $fk) { | ||
| 88 | $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); | ||
| 89 | } | ||
| 90 | break; | ||
| 91 | |||
| 92 | case 'postgresql': | ||
| 93 | // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk | ||
| 94 | $query = $this->connection->query(" | ||
| 95 | SELECT conrelid::regclass AS table_from | ||
| 96 | ,conname | ||
| 97 | ,pg_get_constraintdef(c.oid) | ||
| 98 | FROM pg_constraint c | ||
| 99 | JOIN pg_namespace n ON n.oid = c.connamespace | ||
| 100 | WHERE contype = 'f' | ||
| 101 | AND conrelid::regclass::text = '".$this->getTable('annotation')."' | ||
| 102 | AND n.nspname = 'public' | ||
| 103 | AND pg_get_constraintdef(c.oid) LIKE '%entry_id%';" | ||
| 104 | ); | ||
| 105 | $query->execute(); | ||
| 106 | |||
| 107 | foreach ($query->fetchAll() as $fk) { | ||
| 108 | $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP CONSTRAINT '.$fk['conname']); | ||
| 109 | } | ||
| 110 | break; | ||
| 51 | } | 111 | } |
| 52 | 112 | ||
| 53 | $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_annotation_entry FOREIGN KEY (entry_id) REFERENCES '.$this->getTable('entry').' (id) ON DELETE CASCADE'); | 113 | $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_annotation_entry FOREIGN KEY (entry_id) REFERENCES '.$this->getTable('entry').' (id) ON DELETE CASCADE'); |