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