app/DoctrineMigrations/Version20161001072726.php

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