app/DoctrineMigrations/Version20161001072726.php

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