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 use Doctrine\DBAL\Migrations\SkipMigrationException;
  10
  11 /**
  12  * Added pocket_consumer_key field on wallabag_config.
  13  */
  14 class Version20161001072726 extends AbstractMigration implements ContainerAwareInterface
  15 {
  16     /**
  17      * @var ContainerInterface
  18      */
  19     private $container;
  20
  21     public function setContainer(ContainerInterface $container = null)
  22     {
  23         $this->container = $container;
  24     }
  25
  26     private function getTable($tableName)
  27     {
  28         return $this->container->getParameter('database_table_prefix').$tableName;
  29     }
  30
  31     /**
  32      * @param Schema $schema
  33      */
  34     public function up(Schema $schema)
  35     {
  36         $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.');
  37
  38         // remove all FK from entry_tag
  39         switch ($this->connection->getDatabasePlatform()->getName()) {
  40             case 'mysql':
  41                 $query = $this->connection->query("
  42                     SELECT CONSTRAINT_NAME
  43                     FROM information_schema.key_column_usage
  44                     WHERE TABLE_NAME = '".$this->getTable('entry_tag')."' AND CONSTRAINT_NAME LIKE 'FK_%'
  45                     AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'"
  46                 );
  47                 $query->execute();
  48
  49                 foreach ($query->fetchAll() as $fk) {
  50                     $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']);
  51                 }
  52                 break;
  53
  54             case 'postgresql':
  55                 // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk
  56                 $query = $this->connection->query("
  57                     SELECT conrelid::regclass AS table_from
  58                           ,conname
  59                           ,pg_get_constraintdef(c.oid)
  60                     FROM   pg_constraint c
  61                     JOIN   pg_namespace n ON n.oid = c.connamespace
  62                     WHERE  contype = 'f'
  63                     AND    conrelid::regclass::text = '".$this->getTable('entry_tag')."'
  64                     AND    n.nspname = 'public';"
  65                 );
  66                 $query->execute();
  67
  68                 foreach ($query->fetchAll() as $fk) {
  69                     $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP CONSTRAINT '.$fk['conname']);
  70                 }
  71                 break;
  72         }
  73
  74         $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');
  75         $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');
  76
  77         // remove entry FK from annotation
  78
  79         switch ($this->connection->getDatabasePlatform()->getName()) {
  80             case 'mysql':
  81                 $query = $this->connection->query("
  82                     SELECT CONSTRAINT_NAME
  83                     FROM information_schema.key_column_usage
  84                     WHERE TABLE_NAME = '".$this->getTable('annotation')."'
  85                     AND CONSTRAINT_NAME LIKE 'FK_%'
  86                     AND COLUMN_NAME = 'entry_id'
  87                     AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'"
  88                 );
  89                 $query->execute();
  90
  91                 foreach ($query->fetchAll() as $fk) {
  92                     $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']);
  93                 }
  94                 break;
  95
  96             case 'postgresql':
  97                 // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk
  98                 $query = $this->connection->query("
  99                     SELECT conrelid::regclass AS table_from
 100                           ,conname
 101                           ,pg_get_constraintdef(c.oid)
 102                     FROM   pg_constraint c
 103                     JOIN   pg_namespace n ON n.oid = c.connamespace
 104                     WHERE  contype = 'f'
 105                     AND    conrelid::regclass::text = '".$this->getTable('annotation')."'
 106                     AND    n.nspname = 'public'
 107                     AND    pg_get_constraintdef(c.oid) LIKE '%entry_id%';"
 108                 );
 109                 $query->execute();
 110
 111                 foreach ($query->fetchAll() as $fk) {
 112                     $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP CONSTRAINT '.$fk['conname']);
 113                 }
 114                 break;
 115         }
 116
 117         $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_annotation_entry FOREIGN KEY (entry_id) REFERENCES '.$this->getTable('entry').' (id) ON DELETE CASCADE');
 118     }
 119
 120     /**
 121      * @param Schema $schema
 122      */
 123     public function down(Schema $schema)
 124     {
 125         throw new SkipMigrationException('Too complex ...');
 126     }
 127 }