From a48b5d5a5aa437cff0474321962c12b0e045dbf8 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 12 Dec 2016 11:01:54 +0100 Subject: Run migration in test, ready for MySQL --- app/DoctrineMigrations/Version20161022134138.php | 6 ++++++ app/DoctrineMigrations/Version20161031132655.php | 4 ++-- 2 files changed, 8 insertions(+), 2 deletions(-) (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20161022134138.php b/app/DoctrineMigrations/Version20161022134138.php index b3d02b40..3ac8cc5b 100644 --- a/app/DoctrineMigrations/Version20161022134138.php +++ b/app/DoctrineMigrations/Version20161022134138.php @@ -33,6 +33,12 @@ class Version20161022134138 extends AbstractMigration implements ContainerAwareI $this->addSql('ALTER DATABASE '.$this->container->getParameter('database_name').' CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;'); + // convert field length for utf8mb4 + // http://stackoverflow.com/a/31474509/569101 + $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE confirmation_token confirmation_token VARCHAR(180) NOT NULL;'); + $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE salt salt VARCHAR(180) NOT NULL;'); + $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE password password VARCHAR(180) NOT NULL;'); + $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;'); diff --git a/app/DoctrineMigrations/Version20161031132655.php b/app/DoctrineMigrations/Version20161031132655.php index 770ad2d8..f81898ff 100644 --- a/app/DoctrineMigrations/Version20161031132655.php +++ b/app/DoctrineMigrations/Version20161031132655.php @@ -36,7 +36,7 @@ class Version20161031132655 extends AbstractMigration implements ContainerAwareI $this->skipIf(false !== $images, 'It seems that you already played this migration.'); - $this->addSql('INSERT INTO "'.$this->getTable('craue_config_setting')."\" (name, value, section) VALUES ('download_images_enabled', 0, 'misc')"); + $this->addSql('INSERT INTO '.$this->getTable('craue_config_setting')." (name, value, section) VALUES ('download_images_enabled', 0, 'misc')"); } /** @@ -44,6 +44,6 @@ class Version20161031132655 extends AbstractMigration implements ContainerAwareI */ public function down(Schema $schema) { - $this->addSql('DELETE FROM "'.$this->getTable('craue_config_setting')."\" WHERE name = 'download_images_enabled';"); + $this->addSql('DELETE FROM '.$this->getTable('craue_config_setting')." WHERE name = 'download_images_enabled';"); } } -- cgit v1.2.3 From 3ad4061dbab52a17cc961bef15abad181e352448 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Tue, 13 Dec 2016 09:35:54 +0100 Subject: pocket_consumer_key can be null --- app/DoctrineMigrations/Version20160916201049.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20160916201049.php b/app/DoctrineMigrations/Version20160916201049.php index 9390755e..8b42bb87 100644 --- a/app/DoctrineMigrations/Version20160916201049.php +++ b/app/DoctrineMigrations/Version20160916201049.php @@ -33,7 +33,7 @@ class Version20160916201049 extends AbstractMigration implements ContainerAwareI $this->skipIf($configTable->hasColumn('pocket_consumer_key'), 'It seems that you already played this migration.'); - $configTable->addColumn('pocket_consumer_key', 'string'); + $configTable->addColumn('pocket_consumer_key', 'string', ['notnull' => false]); $this->addSql('DELETE FROM '.$this->getTable('craue_config_setting')." WHERE name = 'pocket_consumer_key';"); } -- cgit v1.2.3 From d44016b072ebe146f3f875f6bf3e9268b367867e Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Tue, 13 Dec 2016 10:31:53 +0100 Subject: Use actual database name We can't retrieve the environment within the migration. And when we ran migration on Travis, database name isn't `database_name` but `test_database_name`. Retrieve the database name from the connection is more accurate --- app/DoctrineMigrations/Version20161022134138.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20161022134138.php b/app/DoctrineMigrations/Version20161022134138.php index 3ac8cc5b..22469f63 100644 --- a/app/DoctrineMigrations/Version20161022134138.php +++ b/app/DoctrineMigrations/Version20161022134138.php @@ -31,7 +31,7 @@ class Version20161022134138 extends AbstractMigration implements ContainerAwareI { $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 DATABASE '.$this->connection->getParams()['dbname'].' CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;'); // convert field length for utf8mb4 // http://stackoverflow.com/a/31474509/569101 @@ -62,7 +62,7 @@ class Version20161022134138 extends AbstractMigration implements ContainerAwareI { $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 DATABASE '.$this->connection->getParams()['dbname'].' 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;'); -- cgit v1.2.3 From 6514f0ed37da14346338b11ee055c1d49813df49 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Tue, 13 Dec 2016 10:41:47 +0100 Subject: fixup! Run migration in test, ready for MySQL --- app/DoctrineMigrations/Version20161022134138.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20161022134138.php b/app/DoctrineMigrations/Version20161022134138.php index 22469f63..d0e5cb3f 100644 --- a/app/DoctrineMigrations/Version20161022134138.php +++ b/app/DoctrineMigrations/Version20161022134138.php @@ -35,7 +35,7 @@ class Version20161022134138 extends AbstractMigration implements ContainerAwareI // convert field length for utf8mb4 // http://stackoverflow.com/a/31474509/569101 - $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE confirmation_token confirmation_token VARCHAR(180) NOT NULL;'); + $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE confirmation_token confirmation_token VARCHAR(180) DEFAULT NULL;'); $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE salt salt VARCHAR(180) NOT NULL;'); $this->addSql('ALTER TABLE '.$this->getTable('user').' CHANGE password password VARCHAR(180) NOT NULL;'); -- cgit v1.2.3 From 3ef75cc4e327146513ff077d69de65c8f93b775c Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 19 Dec 2016 14:24:19 +0100 Subject: Be consistent between migration & schema definition --- app/DoctrineMigrations/Version20161106113822.php | 1 + 1 file changed, 1 insertion(+) (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20161106113822.php b/app/DoctrineMigrations/Version20161106113822.php index 5032a8f0..55bd87a2 100644 --- a/app/DoctrineMigrations/Version20161106113822.php +++ b/app/DoctrineMigrations/Version20161106113822.php @@ -35,6 +35,7 @@ class Version20161106113822 extends AbstractMigration implements ContainerAwareI $configTable->addColumn('action_mark_as_read', 'integer', [ 'default' => 0, + 'notnull' => false, ]); } -- cgit v1.2.3 From a72f3dc308f2e4386f73e88af41db919ba0acaf3 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Fri, 23 Dec 2016 09:00:13 +0100 Subject: Fix bad migration for PostreSQL MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Queries weren’t executed for PostgreSQL, bad syntax. --- app/DoctrineMigrations/Version20161001072726.php | 76 +++++++++++++++++++++--- 1 file changed, 68 insertions(+), 8 deletions(-) (limited to 'app/DoctrineMigrations') diff --git a/app/DoctrineMigrations/Version20161001072726.php b/app/DoctrineMigrations/Version20161001072726.php index 5ab88555..f6930778 100644 --- a/app/DoctrineMigrations/Version20161001072726.php +++ b/app/DoctrineMigrations/Version20161001072726.php @@ -32,22 +32,82 @@ class Version20161001072726 extends AbstractMigration implements ContainerAwareI $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(); + switch ($this->connection->getDatabasePlatform()->getName()) { + case 'mysql': + $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']); + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP FOREIGN KEY '.$fk['CONSTRAINT_NAME']); + } + break; + + case 'postgresql': + // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk + $query = $this->connection->query(" + SELECT conrelid::regclass AS table_from + ,conname + ,pg_get_constraintdef(c.oid) + FROM pg_constraint c + JOIN pg_namespace n ON n.oid = c.connamespace + WHERE contype = 'f' + AND conrelid::regclass::text = '".$this->getTable('entry_tag')."' + AND n.nspname = 'public';" + ); + $query->execute(); + + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('entry_tag').' DROP CONSTRAINT '.$fk['conname']); + } + break; } $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']); + switch ($this->connection->getDatabasePlatform()->getName()) { + case 'mysql': + $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']); + } + break; + + case 'postgresql': + // http://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk + $query = $this->connection->query(" + SELECT conrelid::regclass AS table_from + ,conname + ,pg_get_constraintdef(c.oid) + FROM pg_constraint c + JOIN pg_namespace n ON n.oid = c.connamespace + WHERE contype = 'f' + AND conrelid::regclass::text = '".$this->getTable('annotation')."' + AND n.nspname = 'public' + AND pg_get_constraintdef(c.oid) LIKE '%entry_id%';" + ); + $query->execute(); + + foreach ($query->fetchAll() as $fk) { + $this->addSql('ALTER TABLE '.$this->getTable('annotation').' DROP CONSTRAINT '.$fk['conname']); + } + break; } $this->addSql('ALTER TABLE '.$this->getTable('annotation').' ADD CONSTRAINT FK_annotation_entry FOREIGN KEY (entry_id) REFERENCES '.$this->getTable('entry').' (id) ON DELETE CASCADE'); -- cgit v1.2.3