From 206bade58a279d7f2e34c2dbada10366b90d2d6b Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Sat, 1 Oct 2016 09:26:32 +0200 Subject: Add ability to reset some datas - annotations - tags - entries --- app/DoctrineMigrations/Version20161001072726.php | 54 ++++++++++++++++++++++++ 1 file changed, 54 insertions(+) create mode 100644 app/DoctrineMigrations/Version20161001072726.php (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20161001072726.php b/app/DoctrineMigrations/Version20161001072726.php new file mode 100644 index 00000000..2e112949 --- /dev/null +++ b/app/DoctrineMigrations/Version20161001072726.php @@ -0,0 +1,54 @@ +container = $container; + } + + private function getTable($tableName) + { + return $this->container->getParameter('database_table_prefix') . $tableName; + } + + /** + * @param Schema $schema + */ + public function up(Schema $schema) + { + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BA364942'); + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BAD26311'); + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BA364942 FOREIGN KEY (entry_id) REFERENCES `entry` (id) ON DELETE CASCADE'); + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BAD26311 FOREIGN KEY (tag_id) REFERENCES `tag` (id) ON DELETE CASCADE'); + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY FK_2E443EF2BA364942'); + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_2E443EF2BA364942 FOREIGN KEY (entry_id) REFERENCES `entry` (id) ON DELETE CASCADE'); + } + + /** + * @param Schema $schema + */ + public function down(Schema $schema) + { + $this->abortIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); + + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY FK_2E443EF2BA364942'); + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_2E443EF2BA364942 FOREIGN KEY (entry_id) REFERENCES entry (id)'); + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BA364942'); + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BAD26311'); + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BA364942 FOREIGN KEY (entry_id) REFERENCES entry (id)'); + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id)'); + } +} -- cgit v1.2.3 From 5751b41491b9fd69c5b553c95e70a90fbf975aeb Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Sat, 22 Oct 2016 14:01:58 +0200 Subject: Add migration for MySQL utf8mb4 --- app/DoctrineMigrations/Version20161022134138.php | 77 ++++++++++++++++++++++++ 1 file changed, 77 insertions(+) create mode 100644 app/DoctrineMigrations/Version20161022134138.php (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20161022134138.php b/app/DoctrineMigrations/Version20161022134138.php new file mode 100644 index 00000000..5cce55a5 --- /dev/null +++ b/app/DoctrineMigrations/Version20161022134138.php @@ -0,0 +1,77 @@ +container = $container; + } + + private function getTable($tableName) + { + return $this->container->getParameter('database_table_prefix') . $tableName; + } + + /** + * @param Schema $schema + */ + public function up(Schema $schema) + { + $this->skipIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'This migration only apply to MySQL'); + + $this->addSql('ALTER DATABASE '.$this->container->getParameter('database_name').' CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('entry').' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('tag').' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('user').' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' CHANGE `text` `text` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' CHANGE `quote` `quote` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('entry').' CHANGE `title` `title` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('entry').' CHANGE `content` `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('tag').' CHANGE `label` `label` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE `name` `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); + } + + /** + * @param Schema $schema + */ + public function down(Schema $schema) + { + $this->skipIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'This migration only apply to MySQL'); + + $this->addSql('ALTER DATABASE '.$this->container->getParameter('database_name').' CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('entry').' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('tag').' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('user').' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' CHANGE `text` `text` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' CHANGE `quote` `quote` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('entry').' CHANGE `title` `title` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + $this->addSql('ALTER TABLE '.$this->getTable('entry').' CHANGE `content` `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('tag').' CHANGE `label` `label` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + + $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE `name` `name` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + + } +} -- cgit v1.2.3 From 5ce1528953998ee2957cd548ee123870b82f4079 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Sun, 23 Oct 2016 12:35:57 +0200 Subject: Fix migrations --- app/DoctrineMigrations/Version20160410190541.php | 12 +++++--- app/DoctrineMigrations/Version20160812120952.php | 15 +++++++--- app/DoctrineMigrations/Version20160911214952.php | 4 +-- app/DoctrineMigrations/Version20160916201049.php | 6 ++-- app/DoctrineMigrations/Version20161001072726.php | 37 +++++++++++++++--------- 5 files changed, 47 insertions(+), 27 deletions(-) (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20160410190541.php b/app/DoctrineMigrations/Version20160410190541.php index 4014857b..c41b0465 100644 --- a/app/DoctrineMigrations/Version20160410190541.php +++ b/app/DoctrineMigrations/Version20160410190541.php @@ -29,8 +29,12 @@ class Version20160410190541 extends AbstractMigration implements ContainerAwareI */ public function up(Schema $schema) { - $this->addSql('ALTER TABLE `'.$this->getTable('entry').'` ADD `uuid` LONGTEXT DEFAULT NULL'); - $this->addSql("INSERT INTO `".$this->getTable('craue_config_setting')."` (`name`, `value`, `section`) VALUES ('share_public', '1', 'entry')"); + if ($this->connection->getDatabasePlatform()->getName() == 'postgresql') { + $this->addSql('ALTER TABLE '.$this->getTable('entry').' ADD uuid UUID DEFAULT NULL'); + } else { + $this->addSql('ALTER TABLE '.$this->getTable('entry').' ADD uuid LONGTEXT DEFAULT NULL'); + } + $this->addSql("INSERT INTO ".$this->getTable('craue_config_setting')." (name, value, section) VALUES ('share_public', '1', 'entry')"); } /** @@ -40,7 +44,7 @@ class Version20160410190541 extends AbstractMigration implements ContainerAwareI { $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'sqlite', 'This down migration can\'t be executed on SQLite databases, because SQLite don\'t support DROP COLUMN.'); - $this->addSql('ALTER TABLE `'.$this->getTable('entry').'` DROP `uuid`'); - $this->addSql("DELETE FROM `".$this->getTable('craue_config_setting')."` WHERE `name` = 'share_public'"); + $this->addSql('ALTER TABLE '.$this->getTable('entry').' DROP uuid'); + $this->addSql("DELETE FROM ".$this->getTable('craue_config_setting')." WHERE name = 'share_public'"); } } diff --git a/app/DoctrineMigrations/Version20160812120952.php b/app/DoctrineMigrations/Version20160812120952.php index a8d3bcf2..39423e2f 100644 --- a/app/DoctrineMigrations/Version20160812120952.php +++ b/app/DoctrineMigrations/Version20160812120952.php @@ -29,10 +29,17 @@ class Version20160812120952 extends AbstractMigration implements ContainerAwareI */ public function up(Schema $schema) { - if ($this->connection->getDatabasePlatform()->getName() == 'sqlite') { - $this->addSql('ALTER TABLE '.$this->getTable('oauth2_clients').' ADD name longtext DEFAULT NULL'); - } else { - $this->addSql('ALTER TABLE '.$this->getTable('oauth2_clients').' ADD name longtext COLLATE \'utf8_unicode_ci\' DEFAULT NULL'); + switch ($this->connection->getDatabasePlatform()->getName()) { + case 'sqlite': + $this->addSql('ALTER TABLE '.$this->getTable('oauth2_clients').' ADD name longtext DEFAULT NULL'); + break; + + case 'mysql': + $this->addSql('ALTER TABLE '.$this->getTable('oauth2_clients').' ADD name longtext COLLATE \'utf8_unicode_ci\' DEFAULT NULL'); + break; + + case 'postgresql': + $this->addSql('ALTER TABLE '.$this->getTable('oauth2_clients').' ADD name text DEFAULT NULL'); } } diff --git a/app/DoctrineMigrations/Version20160911214952.php b/app/DoctrineMigrations/Version20160911214952.php index 35809cec..3f988ccf 100644 --- a/app/DoctrineMigrations/Version20160911214952.php +++ b/app/DoctrineMigrations/Version20160911214952.php @@ -29,8 +29,8 @@ class Version20160911214952 extends AbstractMigration implements ContainerAwareI */ public function up(Schema $schema) { - $this->addSql('INSERT INTO `'.$this->getTable('craue_config_setting').'` (`name`, `value`, `section`) VALUES (\'import_with_redis\', \'0\', \'import\')'); - $this->addSql('INSERT INTO `'.$this->getTable('craue_config_setting').'` (`name`, `value`, `section`) VALUES (\'import_with_rabbitmq\', \'0\', \'import\')'); + $this->addSql('INSERT INTO '.$this->getTable('craue_config_setting').' (name, value, section) VALUES (\'import_with_redis\', \'0\', \'import\')'); + $this->addSql('INSERT INTO '.$this->getTable('craue_config_setting').' (name, value, section) VALUES (\'import_with_rabbitmq\', \'0\', \'import\')'); } /** diff --git a/app/DoctrineMigrations/Version20160916201049.php b/app/DoctrineMigrations/Version20160916201049.php index 202901e6..fc4e700a 100644 --- a/app/DoctrineMigrations/Version20160916201049.php +++ b/app/DoctrineMigrations/Version20160916201049.php @@ -30,7 +30,7 @@ class Version20160916201049 extends AbstractMigration implements ContainerAwareI public function up(Schema $schema) { $this->addSql('ALTER TABLE '.$this->getTable('config').' ADD pocket_consumer_key VARCHAR(255) DEFAULT NULL'); - $this->addSql("DELETE FROM `".$this->getTable('craue_config_setting')."` WHERE `name` = 'pocket_consumer_key';"); + $this->addSql("DELETE FROM ".$this->getTable('craue_config_setting')." WHERE name = 'pocket_consumer_key';"); } /** @@ -40,7 +40,7 @@ class Version20160916201049 extends AbstractMigration implements ContainerAwareI { $this->abortIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); - $this->addSql('ALTER TABLE `'.$this->getTable('config').'` DROP pocket_consumer_key'); - $this->addSql("INSERT INTO `".$this->getTable('craue_config_setting')."` (`name`, `value`, `section`) VALUES ('pocket_consumer_key', NULL, 'import')"); + $this->addSql('ALTER TABLE '.$this->getTable('config').' DROP pocket_consumer_key'); + $this->addSql("INSERT INTO ".$this->getTable('craue_config_setting')." (name, value, section) VALUES ('pocket_consumer_key', NULL, 'import')"); } } diff --git a/app/DoctrineMigrations/Version20161001072726.php b/app/DoctrineMigrations/Version20161001072726.php index 2e112949..237db932 100644 --- a/app/DoctrineMigrations/Version20161001072726.php +++ b/app/DoctrineMigrations/Version20161001072726.php @@ -29,12 +29,28 @@ class Version20161001072726 extends AbstractMigration implements ContainerAwareI */ public function up(Schema $schema) { - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BA364942'); - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BAD26311'); - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BA364942 FOREIGN KEY (entry_id) REFERENCES `entry` (id) ON DELETE CASCADE'); - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BAD26311 FOREIGN KEY (tag_id) REFERENCES `tag` (id) ON DELETE CASCADE'); - $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY FK_2E443EF2BA364942'); - $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_2E443EF2BA364942 FOREIGN KEY (entry_id) REFERENCES `entry` (id) ON DELETE CASCADE'); + $this->skipIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); + + // remove all FK from entry_tag + $query = $this->connection->query("SELECT CONSTRAINT_NAME FROM information_schema.key_column_usage WHERE TABLE_NAME = '".$this->getTable('entry_tag')."' AND CONSTRAINT_NAME LIKE 'FK_%' AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'"); + $query->execute(); + + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); + } + + $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'); + $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'); + + // remove entry FK from annotation + $query = $this->connection->query("SELECT CONSTRAINT_NAME FROM information_schema.key_column_usage WHERE TABLE_NAME = '".$this->getTable('annotation')."' AND CONSTRAINT_NAME LIKE 'FK_%' and COLUMN_NAME = 'entry_id' AND TABLE_SCHEMA = '".$this->connection->getDatabase()."'"); + $query->execute(); + + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); + } + + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_annotation_entry FOREIGN KEY (entry_id) REFERENCES '.$this->getTable('entry').' (id) ON DELETE CASCADE'); } /** @@ -42,13 +58,6 @@ class Version20161001072726 extends AbstractMigration implements ContainerAwareI */ public function down(Schema $schema) { - $this->abortIf($this->connection->getDatabasePlatform()->getName() == 'sqlite', 'Migration can only be executed safely on \'mysql\' or \'postgresql\'.'); - - $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP FOREIGN KEY FK_2E443EF2BA364942'); - $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_2E443EF2BA364942 FOREIGN KEY (entry_id) REFERENCES entry (id)'); - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BA364942'); - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY FK_F035C9E5BAD26311'); - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BA364942 FOREIGN KEY (entry_id) REFERENCES entry (id)'); - $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' ADD CONSTRAINT FK_F035C9E5BAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id)'); + throw new SkipMigrationException('Too complex ...'); } } -- cgit v1.2.3