app/DoctrineMigrations/Version20161001072726.php

   1 <?php
   2
   3 namespace Application\Migrations;
   4
   5 use Doctrine\DBAL\Migrations\AbstractMigration;
   6 use Doctrine\DBAL\Schema\Schema;
   7 use Symfony\Component\DependencyInjection\ContainerAwareInterface;
   8 use Symfony\Component\DependencyInjection\ContainerInterface;
   9
  10 class Version20161001072726 extends AbstractMigration implements ContainerAwareInterface
  11 {
  12     /**
  13      * @var ContainerInterface
  14      */
  15     private $container;
  16
  17     public function setContainer(ContainerInterface $container = null)
  18     {
  19         $this->container = $container;
  20     }
  21
  22     private function getTable($tableName)
  23     {
  24         return $this->container->getParameter('database_table_prefix').$tableName;
  25     }
  26
  27     /**
  28      * @param Schema $schema
  29      */
  30     public function up(Schema $schema)
  31     {
  32         $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.');
  33
  34         // remove all FK from entry_tag
  35         switch ($this->connection->getDatabasePlatform()->getName()) {
  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();
  44
  45                 foreach ($query->fetchAll() as $fk) {
  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;
  68         }
  69
  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');
  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');
  72
  73         // remove entry FK from annotation
  74
  75         switch ($this->connection->getDatabasePlatform()->getName()) {
  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;
 111         }
 112
 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');
 114     }
 115
 116     /**
 117      * @param Schema $schema
 118      */
 119     public function down(Schema $schema)
 120     {
 121         throw new SkipMigrationException('Too complex ...');
 122     }
 123 }