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 +- build.xml | 101 ++++++++++++++++++++--- 3 files changed, 98 insertions(+), 13 deletions(-) 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';"); } } diff --git a/build.xml b/build.xml index 37b40743..aae076e1 100644 --- a/build.xml +++ b/build.xml @@ -1,9 +1,9 @@ - - - + + + @@ -26,11 +26,6 @@ - - - - - @@ -45,12 +40,40 @@ - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -59,12 +82,40 @@ - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -73,12 +124,40 @@ - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- cgit v1.2.3 From cafb105681e7f9e3979f3ffef4cef6b65968b8fa Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 12 Dec 2016 11:29:45 +0100 Subject: Manually import file for PostgreSQL --- build.xml | 21 +++++++++++++-------- 1 file changed, 13 insertions(+), 8 deletions(-) diff --git a/build.xml b/build.xml index aae076e1..4adf3403 100644 --- a/build.xml +++ b/build.xml @@ -34,7 +34,7 @@ - + @@ -76,7 +76,7 @@ - + @@ -118,7 +118,7 @@ - + @@ -146,11 +146,16 @@ - - - - - + + + + + + + + + + -- cgit v1.2.3 From 81915ad8df5a1aed268508dce71445f99274d18c Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 12 Dec 2016 11:32:20 +0100 Subject: And of course it doesn't work for SQLite --- build.xml | 9 +++++++++ 1 file changed, 9 insertions(+) diff --git a/build.xml b/build.xml index 4adf3403..107e912d 100644 --- a/build.xml +++ b/build.xml @@ -98,11 +98,20 @@ + + + + + -- cgit v1.2.3 From 5354485885c1ccfd5bc968ed4e4a44f0faeec726 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 12 Dec 2016 11:37:26 +0100 Subject: Add orignal DB schema --- data/sql/mysql_base.sql | 25 +++++++++++++++++++ data/sql/pgsql_base.sql | 62 ++++++++++++++++++++++++++++++++++++++++++++++++ data/sql/sqlite_base.sql | 33 ++++++++++++++++++++++++++ 3 files changed, 120 insertions(+) create mode 100644 data/sql/mysql_base.sql create mode 100644 data/sql/pgsql_base.sql create mode 100644 data/sql/sqlite_base.sql diff --git a/data/sql/mysql_base.sql b/data/sql/mysql_base.sql new file mode 100644 index 00000000..13fa6302 --- /dev/null +++ b/data/sql/mysql_base.sql @@ -0,0 +1,25 @@ +CREATE TABLE wallabag_craue_config_setting (name VARCHAR(255) NOT NULL, value VARCHAR(255) DEFAULT NULL, section VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_5D9649505E237E06 (name), PRIMARY KEY(name)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE `wallabag_entry` (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, title LONGTEXT DEFAULT NULL, url LONGTEXT DEFAULT NULL, is_archived TINYINT(1) NOT NULL, is_starred TINYINT(1) NOT NULL, content LONGTEXT DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype LONGTEXT DEFAULT NULL, language LONGTEXT DEFAULT NULL, reading_time INT DEFAULT NULL, domain_name LONGTEXT DEFAULT NULL, preview_picture LONGTEXT DEFAULT NULL, is_public TINYINT(1) DEFAULT '0', INDEX IDX_F4D18282A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE wallabag_entry_tag (entry_id INT NOT NULL, tag_id INT NOT NULL, INDEX IDX_C9F0DD7CBA364942 (entry_id), INDEX IDX_C9F0DD7CBAD26311 (tag_id), PRIMARY KEY(entry_id, tag_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE `wallabag_config` (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, theme VARCHAR(255) NOT NULL, items_per_page INT NOT NULL, language VARCHAR(255) NOT NULL, rss_token VARCHAR(255) DEFAULT NULL, rss_limit INT DEFAULT NULL, reading_speed DOUBLE PRECISION DEFAULT NULL, UNIQUE INDEX UNIQ_87E64C53A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE `wallabag_tagging_rule` (id INT AUTO_INCREMENT NOT NULL, config_id INT DEFAULT NULL, rule VARCHAR(255) NOT NULL, tags LONGTEXT NOT NULL COMMENT '(DC2Type:simple_array)', INDEX IDX_2D9B3C5424DB0683 (config_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE `wallabag_tag` (id INT AUTO_INCREMENT NOT NULL, `label` LONGTEXT NOT NULL, slug VARCHAR(128) NOT NULL, UNIQUE INDEX UNIQ_4CA58A8C989D9B62 (slug), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE wallabag_oauth2_clients (id INT AUTO_INCREMENT NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris LONGTEXT NOT NULL COMMENT '(DC2Type:array)', secret VARCHAR(255) NOT NULL, allowed_grant_types LONGTEXT NOT NULL COMMENT '(DC2Type:array)', PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE wallabag_oauth2_access_tokens (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_368A42095F37A13B (token), INDEX IDX_368A420919EB6921 (client_id), INDEX IDX_368A4209A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE wallabag_oauth2_refresh_tokens (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_20C9FB245F37A13B (token), INDEX IDX_20C9FB2419EB6921 (client_id), INDEX IDX_20C9FB24A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE wallabag_oauth2_auth_codes (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri LONGTEXT NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_EE52E3FA5F37A13B (token), INDEX IDX_EE52E3FA19EB6921 (client_id), INDEX IDX_EE52E3FAA76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE `wallabag_user` (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked TINYINT(1) NOT NULL, expired TINYINT(1) NOT NULL, expires_at DATETIME DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT '(DC2Type:array)', credentials_expired TINYINT(1) NOT NULL, credentials_expire_at DATETIME DEFAULT NULL, name LONGTEXT DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, authCode INT DEFAULT NULL, twoFactorAuthentication TINYINT(1) NOT NULL, trusted LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json_array)', UNIQUE INDEX UNIQ_1D63E7E592FC23A8 (username_canonical), UNIQUE INDEX UNIQ_1D63E7E5A0D96FBF (email_canonical), UNIQUE INDEX UNIQ_1D63E7E5C05FB297 (confirmation_token), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +CREATE TABLE wallabag_annotation (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, entry_id INT DEFAULT NULL, text LONGTEXT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, quote VARCHAR(255) NOT NULL, ranges LONGTEXT NOT NULL COMMENT '(DC2Type:array)', INDEX IDX_A7AED006A76ED395 (user_id), INDEX IDX_A7AED006BA364942 (entry_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; +ALTER TABLE `wallabag_entry` ADD CONSTRAINT FK_F4D18282A76ED395 FOREIGN KEY (user_id) REFERENCES `wallabag_user` (id); +ALTER TABLE wallabag_entry_tag ADD CONSTRAINT FK_C9F0DD7CBA364942 FOREIGN KEY (entry_id) REFERENCES `wallabag_entry` (id); +ALTER TABLE wallabag_entry_tag ADD CONSTRAINT FK_C9F0DD7CBAD26311 FOREIGN KEY (tag_id) REFERENCES `wallabag_tag` (id); +ALTER TABLE `wallabag_config` ADD CONSTRAINT FK_87E64C53A76ED395 FOREIGN KEY (user_id) REFERENCES `wallabag_user` (id); +ALTER TABLE `wallabag_tagging_rule` ADD CONSTRAINT FK_2D9B3C5424DB0683 FOREIGN KEY (config_id) REFERENCES `wallabag_config` (id); +ALTER TABLE wallabag_oauth2_access_tokens ADD CONSTRAINT FK_368A420919EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id); +ALTER TABLE wallabag_oauth2_access_tokens ADD CONSTRAINT FK_368A4209A76ED395 FOREIGN KEY (user_id) REFERENCES `wallabag_user` (id); +ALTER TABLE wallabag_oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB2419EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id); +ALTER TABLE wallabag_oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB24A76ED395 FOREIGN KEY (user_id) REFERENCES `wallabag_user` (id); +ALTER TABLE wallabag_oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FA19EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id); +ALTER TABLE wallabag_oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FAA76ED395 FOREIGN KEY (user_id) REFERENCES `wallabag_user` (id); +ALTER TABLE wallabag_annotation ADD CONSTRAINT FK_A7AED006A76ED395 FOREIGN KEY (user_id) REFERENCES `wallabag_user` (id); +ALTER TABLE wallabag_annotation ADD CONSTRAINT FK_A7AED006BA364942 FOREIGN KEY (entry_id) REFERENCES `wallabag_entry` (id); diff --git a/data/sql/pgsql_base.sql b/data/sql/pgsql_base.sql new file mode 100644 index 00000000..6688fe83 --- /dev/null +++ b/data/sql/pgsql_base.sql @@ -0,0 +1,62 @@ +CREATE TABLE wallabag_craue_config_setting (name VARCHAR(255) NOT NULL, value VARCHAR(255) DEFAULT NULL, section VARCHAR(255) DEFAULT NULL, PRIMARY KEY(name)); +CREATE UNIQUE INDEX UNIQ_5D9649505E237E06 ON wallabag_craue_config_setting (name); +CREATE TABLE "wallabag_entry" (id INT NOT NULL, user_id INT DEFAULT NULL, title TEXT DEFAULT NULL, url TEXT DEFAULT NULL, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content TEXT DEFAULT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, mimetype TEXT DEFAULT NULL, language TEXT DEFAULT NULL, reading_time INT DEFAULT NULL, domain_name TEXT DEFAULT NULL, preview_picture TEXT DEFAULT NULL, is_public BOOLEAN DEFAULT 'false', PRIMARY KEY(id)); +CREATE INDEX IDX_F4D18282A76ED395 ON "wallabag_entry" (user_id); +CREATE TABLE wallabag_entry_tag (entry_id INT NOT NULL, tag_id INT NOT NULL, PRIMARY KEY(entry_id, tag_id)); +CREATE INDEX IDX_C9F0DD7CBA364942 ON wallabag_entry_tag (entry_id); +CREATE INDEX IDX_C9F0DD7CBAD26311 ON wallabag_entry_tag (tag_id); +CREATE TABLE "wallabag_config" (id INT NOT NULL, user_id INT DEFAULT NULL, theme VARCHAR(255) NOT NULL, items_per_page INT NOT NULL, language VARCHAR(255) NOT NULL, rss_token VARCHAR(255) DEFAULT NULL, rss_limit INT DEFAULT NULL, reading_speed DOUBLE PRECISION DEFAULT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_87E64C53A76ED395 ON "wallabag_config" (user_id); +CREATE TABLE "wallabag_tagging_rule" (id INT NOT NULL, config_id INT DEFAULT NULL, rule VARCHAR(255) NOT NULL, tags TEXT NOT NULL, PRIMARY KEY(id)); +CREATE INDEX IDX_2D9B3C5424DB0683 ON "wallabag_tagging_rule" (config_id); +COMMENT ON COLUMN "wallabag_tagging_rule".tags IS '(DC2Type:simple_array)'; +CREATE TABLE "wallabag_tag" (id INT NOT NULL, label TEXT NOT NULL, slug VARCHAR(128) NOT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_4CA58A8C989D9B62 ON "wallabag_tag" (slug); +CREATE TABLE wallabag_oauth2_clients (id INT NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris TEXT NOT NULL, secret VARCHAR(255) NOT NULL, allowed_grant_types TEXT NOT NULL, PRIMARY KEY(id)); +COMMENT ON COLUMN wallabag_oauth2_clients.redirect_uris IS '(DC2Type:array)'; +COMMENT ON COLUMN wallabag_oauth2_clients.allowed_grant_types IS '(DC2Type:array)'; +CREATE TABLE wallabag_oauth2_access_tokens (id INT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_368A42095F37A13B ON wallabag_oauth2_access_tokens (token); +CREATE INDEX IDX_368A420919EB6921 ON wallabag_oauth2_access_tokens (client_id); +CREATE INDEX IDX_368A4209A76ED395 ON wallabag_oauth2_access_tokens (user_id); +CREATE TABLE wallabag_oauth2_refresh_tokens (id INT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_20C9FB245F37A13B ON wallabag_oauth2_refresh_tokens (token); +CREATE INDEX IDX_20C9FB2419EB6921 ON wallabag_oauth2_refresh_tokens (client_id); +CREATE INDEX IDX_20C9FB24A76ED395 ON wallabag_oauth2_refresh_tokens (user_id); +CREATE TABLE wallabag_oauth2_auth_codes (id INT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri TEXT NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_EE52E3FA5F37A13B ON wallabag_oauth2_auth_codes (token); +CREATE INDEX IDX_EE52E3FA19EB6921 ON wallabag_oauth2_auth_codes (client_id); +CREATE INDEX IDX_EE52E3FAA76ED395 ON wallabag_oauth2_auth_codes (user_id); +CREATE TABLE "wallabag_user" (id INT NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled BOOLEAN NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, locked BOOLEAN NOT NULL, expired BOOLEAN NOT NULL, expires_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, roles TEXT NOT NULL, credentials_expired BOOLEAN NOT NULL, credentials_expire_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, name TEXT DEFAULT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, authCode INT DEFAULT NULL, twoFactorAuthentication BOOLEAN NOT NULL, trusted TEXT DEFAULT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_1D63E7E592FC23A8 ON "wallabag_user" (username_canonical); +CREATE UNIQUE INDEX UNIQ_1D63E7E5A0D96FBF ON "wallabag_user" (email_canonical); +CREATE UNIQUE INDEX UNIQ_1D63E7E5C05FB297 ON "wallabag_user" (confirmation_token); +COMMENT ON COLUMN "wallabag_user".roles IS '(DC2Type:array)'; +COMMENT ON COLUMN "wallabag_user".trusted IS '(DC2Type:json_array)'; +CREATE TABLE wallabag_annotation (id INT NOT NULL, user_id INT DEFAULT NULL, entry_id INT DEFAULT NULL, text TEXT NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, quote VARCHAR(255) NOT NULL, ranges TEXT NOT NULL, PRIMARY KEY(id)); +CREATE INDEX IDX_A7AED006A76ED395 ON wallabag_annotation (user_id); +CREATE INDEX IDX_A7AED006BA364942 ON wallabag_annotation (entry_id); +COMMENT ON COLUMN wallabag_annotation.ranges IS '(DC2Type:array)'; +CREATE SEQUENCE "entry_id_seq" INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE "config_id_seq" INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE "tagging_rule_id_seq" INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE "tag_id_seq" INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE oauth2_clients_id_seq INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE oauth2_access_tokens_id_seq INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE oauth2_refresh_tokens_id_seq INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE oauth2_auth_codes_id_seq INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE "user_id_seq" INCREMENT BY 1 MINVALUE 1 START 1; +CREATE SEQUENCE annotation_id_seq INCREMENT BY 1 MINVALUE 1 START 1; +ALTER TABLE "wallabag_entry" ADD CONSTRAINT FK_F4D18282A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_entry_tag ADD CONSTRAINT FK_C9F0DD7CBA364942 FOREIGN KEY (entry_id) REFERENCES "wallabag_entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_entry_tag ADD CONSTRAINT FK_C9F0DD7CBAD26311 FOREIGN KEY (tag_id) REFERENCES "wallabag_tag" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE "wallabag_config" ADD CONSTRAINT FK_87E64C53A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE "wallabag_tagging_rule" ADD CONSTRAINT FK_2D9B3C5424DB0683 FOREIGN KEY (config_id) REFERENCES "wallabag_config" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_oauth2_access_tokens ADD CONSTRAINT FK_368A420919EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_oauth2_access_tokens ADD CONSTRAINT FK_368A4209A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB2419EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB24A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FA19EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FAA76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_annotation ADD CONSTRAINT FK_A7AED006A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE wallabag_annotation ADD CONSTRAINT FK_A7AED006BA364942 FOREIGN KEY (entry_id) REFERENCES "wallabag_entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE; diff --git a/data/sql/sqlite_base.sql b/data/sql/sqlite_base.sql new file mode 100644 index 00000000..d2780d96 --- /dev/null +++ b/data/sql/sqlite_base.sql @@ -0,0 +1,33 @@ +CREATE TABLE wallabag_craue_config_setting (name VARCHAR(255) NOT NULL, value VARCHAR(255) DEFAULT NULL, section VARCHAR(255) DEFAULT NULL, PRIMARY KEY(name)); +CREATE UNIQUE INDEX UNIQ_5D9649505E237E06 ON wallabag_craue_config_setting (name); +CREATE TABLE "wallabag_tagging_rule" (id INTEGER NOT NULL, config_id INTEGER DEFAULT NULL, rule VARCHAR(255) NOT NULL, tags CLOB NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_2D9B3C5424DB0683 FOREIGN KEY (config_id) REFERENCES "wallabag_config" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE INDEX IDX_2D9B3C5424DB0683 ON "wallabag_tagging_rule" (config_id); +CREATE TABLE "wallabag_tag" (id INTEGER NOT NULL, label CLOB NOT NULL, slug VARCHAR(128) NOT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_4CA58A8C989D9B62 ON "wallabag_tag" (slug); +CREATE TABLE "wallabag_entry" (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, title CLOB DEFAULT NULL, url CLOB DEFAULT NULL, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content CLOB DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype CLOB DEFAULT NULL, language CLOB DEFAULT NULL, reading_time INTEGER DEFAULT NULL, domain_name CLOB DEFAULT NULL, preview_picture CLOB DEFAULT NULL, is_public BOOLEAN DEFAULT '0', PRIMARY KEY(id), CONSTRAINT FK_F4D18282A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE INDEX IDX_F4D18282A76ED395 ON "wallabag_entry" (user_id); +CREATE TABLE wallabag_entry_tag (entry_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(entry_id, tag_id), CONSTRAINT FK_C9F0DD7CBA364942 FOREIGN KEY (entry_id) REFERENCES "wallabag_entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_C9F0DD7CBAD26311 FOREIGN KEY (tag_id) REFERENCES "wallabag_tag" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE INDEX IDX_C9F0DD7CBA364942 ON wallabag_entry_tag (entry_id); +CREATE INDEX IDX_C9F0DD7CBAD26311 ON wallabag_entry_tag (tag_id); +CREATE TABLE "wallabag_config" (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, theme VARCHAR(255) NOT NULL, items_per_page INTEGER NOT NULL, language VARCHAR(255) NOT NULL, rss_token VARCHAR(255) DEFAULT NULL, rss_limit INTEGER DEFAULT NULL, reading_speed DOUBLE PRECISION DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_87E64C53A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE UNIQUE INDEX UNIQ_87E64C53A76ED395 ON "wallabag_config" (user_id); +CREATE TABLE wallabag_oauth2_refresh_tokens (id INTEGER NOT NULL, client_id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INTEGER DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_20C9FB2419EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_20C9FB24A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE UNIQUE INDEX UNIQ_20C9FB245F37A13B ON wallabag_oauth2_refresh_tokens (token); +CREATE INDEX IDX_20C9FB2419EB6921 ON wallabag_oauth2_refresh_tokens (client_id); +CREATE INDEX IDX_20C9FB24A76ED395 ON wallabag_oauth2_refresh_tokens (user_id); +CREATE TABLE wallabag_oauth2_access_tokens (id INTEGER NOT NULL, client_id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INTEGER DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_368A420919EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_368A4209A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE UNIQUE INDEX UNIQ_368A42095F37A13B ON wallabag_oauth2_access_tokens (token); +CREATE INDEX IDX_368A420919EB6921 ON wallabag_oauth2_access_tokens (client_id); +CREATE INDEX IDX_368A4209A76ED395 ON wallabag_oauth2_access_tokens (user_id); +CREATE TABLE wallabag_oauth2_auth_codes (id INTEGER NOT NULL, client_id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri CLOB NOT NULL, expires_at INTEGER DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_EE52E3FA19EB6921 FOREIGN KEY (client_id) REFERENCES wallabag_oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_EE52E3FAA76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE UNIQUE INDEX UNIQ_EE52E3FA5F37A13B ON wallabag_oauth2_auth_codes (token); +CREATE INDEX IDX_EE52E3FA19EB6921 ON wallabag_oauth2_auth_codes (client_id); +CREATE INDEX IDX_EE52E3FAA76ED395 ON wallabag_oauth2_auth_codes (user_id); +CREATE TABLE wallabag_oauth2_clients (id INTEGER NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris CLOB NOT NULL, secret VARCHAR(255) NOT NULL, allowed_grant_types CLOB NOT NULL, PRIMARY KEY(id)); +CREATE TABLE "wallabag_user" (id INTEGER NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled BOOLEAN NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked BOOLEAN NOT NULL, expired BOOLEAN NOT NULL, expires_at DATETIME DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles CLOB NOT NULL, credentials_expired BOOLEAN NOT NULL, credentials_expire_at DATETIME DEFAULT NULL, name CLOB DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, authCode INTEGER DEFAULT NULL, twoFactorAuthentication BOOLEAN NOT NULL, trusted CLOB DEFAULT NULL, PRIMARY KEY(id)); +CREATE UNIQUE INDEX UNIQ_1D63E7E592FC23A8 ON "wallabag_user" (username_canonical); +CREATE UNIQUE INDEX UNIQ_1D63E7E5A0D96FBF ON "wallabag_user" (email_canonical); +CREATE UNIQUE INDEX UNIQ_1D63E7E5C05FB297 ON "wallabag_user" (confirmation_token); +CREATE TABLE wallabag_annotation (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, entry_id INTEGER DEFAULT NULL, text CLOB NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, quote VARCHAR(255) NOT NULL, ranges CLOB NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_A7AED006A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_A7AED006BA364942 FOREIGN KEY (entry_id) REFERENCES "wallabag_entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE); +CREATE INDEX IDX_A7AED006A76ED395 ON wallabag_annotation (user_id); +CREATE INDEX IDX_A7AED006BA364942 ON wallabag_annotation (entry_id); -- cgit v1.2.3 From bd8db77f5f8a5ee9cc1f2d2b10413292e1844a80 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 12 Dec 2016 11:46:43 +0100 Subject: Remove comment "The string "--" is not permitted within comments." --- build.xml | 11 ----------- 1 file changed, 11 deletions(-) diff --git a/build.xml b/build.xml index 107e912d..c34c7d79 100644 --- a/build.xml +++ b/build.xml @@ -98,16 +98,6 @@ - @@ -156,7 +146,6 @@ - -- cgit v1.2.3 From d9efa42f6619a0b0784e6e28b9b7f2784b2f71d3 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Mon, 12 Dec 2016 11:55:37 +0100 Subject: fixup! Manually import file for PostgreSQL --- build.xml | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/build.xml b/build.xml index c34c7d79..b5727e62 100644 --- a/build.xml +++ b/build.xml @@ -139,12 +139,6 @@ - - - - - - @@ -155,6 +149,12 @@ + + + + + + -- 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(-) 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(-) 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(-) 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 + src/Wallabag/ApiBundle/Entity/Client.php | 2 +- src/Wallabag/CoreBundle/Entity/Config.php | 2 +- src/Wallabag/CoreBundle/Entity/Entry.php | 2 +- 4 files changed, 4 insertions(+), 3 deletions(-) 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, ]); } diff --git a/src/Wallabag/ApiBundle/Entity/Client.php b/src/Wallabag/ApiBundle/Entity/Client.php index 427a4c7f..9ed9f980 100644 --- a/src/Wallabag/ApiBundle/Entity/Client.php +++ b/src/Wallabag/ApiBundle/Entity/Client.php @@ -22,7 +22,7 @@ class Client extends BaseClient /** * @var string * - * @ORM\Column(name="name", type="text", nullable=true) + * @ORM\Column(name="name", type="text", nullable=false) */ protected $name; diff --git a/src/Wallabag/CoreBundle/Entity/Config.php b/src/Wallabag/CoreBundle/Entity/Config.php index 0b75270d..b902ae2c 100644 --- a/src/Wallabag/CoreBundle/Entity/Config.php +++ b/src/Wallabag/CoreBundle/Entity/Config.php @@ -93,7 +93,7 @@ class Config /** * @var int * - * @ORM\Column(name="action_mark_as_read", type="integer", nullable=true) + * @ORM\Column(name="action_mark_as_read", type="integer", nullable=true, options={"default" = 0}) */ private $actionMarkAsRead; diff --git a/src/Wallabag/CoreBundle/Entity/Entry.php b/src/Wallabag/CoreBundle/Entity/Entry.php index 3cf9ac1a..3ae5334f 100644 --- a/src/Wallabag/CoreBundle/Entity/Entry.php +++ b/src/Wallabag/CoreBundle/Entity/Entry.php @@ -183,7 +183,7 @@ class Entry /** * @var string * - * @ORM\Column(name="http_status", type="text", nullable=true) + * @ORM\Column(name="http_status", type="string", length=3, nullable=true) * * @Groups({"entries_for_user", "export_all"}) */ -- 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(-) 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