From a72f3dc308f2e4386f73e88af41db919ba0acaf3 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Fri, 23 Dec 2016 09:00:13 +0100 Subject: Fix bad migration for PostreSQL MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Queries weren’t executed for PostgreSQL, bad syntax. --- app/DoctrineMigrations/Version20161001072726.php | 76 +++++++++++++++++++++--- 1 file 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 $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); // remove all FK from entry_tag - $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()."'"); - $query->execute(); + switch ($this->connection->getDatabasePlatform()->getName()) { + case 'mysql': + $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()."'" + ); + $query->execute(); - foreach ($query->fetchAll() as $fk) { - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); + } + break; + + case 'postgresql': + // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk + $query = $this->connection->query(" + SELECT conrelid::regclass AS table_from + ,conname + ,pg_get_constraintdef(c.oid) + FROM pg_constraint c + JOIN pg_namespace n ON n.oid = c.connamespace + WHERE contype = 'f' + AND conrelid::regclass::text = '".$this->getTable('entry_tag')."' + AND n.nspname = 'public';" + ); + $query->execute(); + + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP CONSTRAINT '.$fk['conname']); + } + break; } $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'); $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'); // remove entry FK from annotation - $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()."'"); - $query->execute(); - foreach ($query->fetchAll() as $fk) { - $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); + switch ($this->connection->getDatabasePlatform()->getName()) { + case 'mysql': + $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()."'" + ); + $query->execute(); + + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); + } + break; + + case 'postgresql': + // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk + $query = $this->connection->query(" + SELECT conrelid::regclass AS table_from + ,conname + ,pg_get_constraintdef(c.oid) + FROM pg_constraint c + JOIN pg_namespace n ON n.oid = c.connamespace + WHERE contype = 'f' + AND conrelid::regclass::text = '".$this->getTable('annotation')."' + AND n.nspname = 'public' + AND pg_get_constraintdef(c.oid) LIKE '%entry_id%';" + ); + $query->execute(); + + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP CONSTRAINT '.$fk['conname']); + } + break; } $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_annotation_entry FOREIGN KEY (entry_id) REFERENCES '.$this->getTable('entry').' (id) ON DELETE CASCADE'); -- cgit v1.2.3