]>
Commit | Line | Data |
---|---|---|
24acf2e0 | 1 | <?php |
d1dbe10e OM |
2 | |
3 | namespace Application\Migrations; | |
4 | ||
5 | use Doctrine\DBAL\Schema\Schema; | |
24acf2e0 | 6 | use Wallabag\CoreBundle\Doctrine\WallabagMigration; |
d1dbe10e OM |
7 | |
8 | /** | |
24acf2e0 | 9 | * Adding more index to kill some slow queries. |
d1dbe10e | 10 | */ |
24acf2e0 | 11 | final class Version20190806130304 extends WallabagMigration |
d1dbe10e | 12 | { |
24acf2e0 | 13 | public function up(Schema $schema): void |
d1dbe10e | 14 | { |
24acf2e0 JB |
15 | switch ($this->connection->getDatabasePlatform()->getName()) { |
16 | case 'sqlite': | |
17 | $this->addSql('DROP INDEX uid'); | |
18 | $this->addSql('DROP INDEX created_at'); | |
19 | $this->addSql('DROP INDEX hashed_url_user_id'); | |
20 | $this->addSql('DROP INDEX IDX_F4D18282A76ED395'); | |
21 | $this->addSql('DROP INDEX hashed_given_url_user_id'); | |
22 | $this->addSql('CREATE TEMPORARY TABLE __temp__wallabag_entry AS SELECT id, user_id, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, domain_name, preview_picture, uid, http_status, published_at, starred_at, origin_url, archived_at, given_url, reading_time, published_by, headers, hashed_url, hashed_given_url FROM ' . $this->getTable('entry', true)); | |
23 | $this->addSql('DROP TABLE ' . $this->getTable('entry', true)); | |
24 | $this->addSql('CREATE TABLE ' . $this->getTable('entry', true) . ' (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user_id INTEGER DEFAULT NULL, title CLOB DEFAULT NULL COLLATE BINARY, url CLOB DEFAULT NULL COLLATE BINARY, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content CLOB DEFAULT NULL COLLATE BINARY, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype CLOB DEFAULT NULL COLLATE BINARY, domain_name CLOB DEFAULT NULL COLLATE BINARY, preview_picture CLOB DEFAULT NULL COLLATE BINARY, uid VARCHAR(23) DEFAULT NULL COLLATE BINARY, http_status VARCHAR(3) DEFAULT NULL COLLATE BINARY, published_at DATETIME DEFAULT NULL, starred_at DATETIME DEFAULT NULL, origin_url CLOB DEFAULT NULL COLLATE BINARY, archived_at DATETIME DEFAULT NULL, given_url CLOB DEFAULT NULL COLLATE BINARY, reading_time INTEGER NOT NULL, published_by CLOB DEFAULT NULL COLLATE BINARY --(DC2Type:array) | |
25 | , headers CLOB DEFAULT NULL COLLATE BINARY --(DC2Type:array) | |
26 | , hashed_url VARCHAR(40) DEFAULT NULL COLLATE BINARY, hashed_given_url VARCHAR(40) DEFAULT NULL COLLATE BINARY, language VARCHAR(20) DEFAULT NULL, CONSTRAINT FK_F4D18282A76ED395 FOREIGN KEY (user_id) REFERENCES "wallabag_user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE)'); | |
27 | $this->addSql('INSERT INTO ' . $this->getTable('entry', true) . ' (id, user_id, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, domain_name, preview_picture, uid, http_status, published_at, starred_at, origin_url, archived_at, given_url, reading_time, published_by, headers, hashed_url, hashed_given_url) SELECT id, user_id, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, domain_name, preview_picture, uid, http_status, published_at, starred_at, origin_url, archived_at, given_url, reading_time, published_by, headers, hashed_url, hashed_given_url FROM __temp__wallabag_entry'); | |
28 | $this->addSql('DROP TABLE __temp__wallabag_entry'); | |
29 | $this->addSql('CREATE INDEX uid ON ' . $this->getTable('entry', true) . ' (uid)'); | |
30 | $this->addSql('CREATE INDEX created_at ON ' . $this->getTable('entry', true) . ' (created_at)'); | |
31 | $this->addSql('CREATE INDEX hashed_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_url)'); | |
32 | $this->addSql('CREATE INDEX IDX_F4D18282A76ED395 ON ' . $this->getTable('entry', true) . ' (user_id)'); | |
33 | $this->addSql('CREATE INDEX hashed_given_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_given_url)'); | |
34 | $this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry', true) . ' (language, user_id)'); | |
588de419 | 35 | $this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry', true) . ' (user_id, is_archived, archived_at)'); |
2b0f4e86 | 36 | $this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry', true) . ' (user_id, created_at)'); |
24acf2e0 JB |
37 | $this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry', true) . ' (user_id, is_starred, starred_at)'); |
38 | break; | |
39 | case 'mysql': | |
40 | $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' MODIFY language VARCHAR(20) DEFAULT NULL'); | |
41 | $this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry') . ' (language, user_id)'); | |
588de419 | 42 | $this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry') . ' (user_id, is_archived, archived_at)'); |
2b0f4e86 | 43 | $this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry') . ' (user_id, created_at)'); |
24acf2e0 JB |
44 | $this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry') . ' (user_id, is_starred, starred_at)'); |
45 | break; | |
46 | case 'postgresql': | |
b700b38e | 47 | $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' ALTER language TYPE VARCHAR(20)'); |
24acf2e0 | 48 | $this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry') . ' (language, user_id)'); |
588de419 | 49 | $this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry') . ' (user_id, is_archived, archived_at)'); |
2b0f4e86 | 50 | $this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry') . ' (user_id, created_at)'); |
24acf2e0 JB |
51 | $this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry') . ' (user_id, is_starred, starred_at)'); |
52 | break; | |
53 | } | |
d1dbe10e OM |
54 | } |
55 | ||
24acf2e0 | 56 | public function down(Schema $schema): void |
d1dbe10e | 57 | { |
24acf2e0 JB |
58 | switch ($this->connection->getDatabasePlatform()->getName()) { |
59 | case 'sqlite': | |
60 | $this->addSql('DROP INDEX IDX_F4D18282A76ED395'); | |
61 | $this->addSql('DROP INDEX created_at'); | |
62 | $this->addSql('DROP INDEX uid'); | |
63 | $this->addSql('DROP INDEX hashed_url_user_id'); | |
64 | $this->addSql('DROP INDEX hashed_given_url_user_id'); | |
65 | $this->addSql('DROP INDEX user_language'); | |
588de419 | 66 | $this->addSql('DROP INDEX user_archived'); |
2b0f4e86 | 67 | $this->addSql('DROP INDEX user_created'); |
24acf2e0 JB |
68 | $this->addSql('DROP INDEX user_starred'); |
69 | $this->addSql('CREATE TEMPORARY TABLE __temp__wallabag_entry AS SELECT id, user_id, uid, title, url, hashed_url, origin_url, given_url, hashed_given_url, is_archived, archived_at, is_starred, content, created_at, updated_at, published_at, published_by, starred_at, mimetype, language, reading_time, domain_name, preview_picture, http_status, headers FROM ' . $this->getTable('entry', true)); | |
70 | $this->addSql('DROP TABLE ' . $this->getTable('entry', true)); | |
71 | $this->addSql('CREATE TABLE ' . $this->getTable('entry', true) . ' (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user_id INTEGER DEFAULT NULL, uid VARCHAR(23) DEFAULT NULL, title CLOB DEFAULT NULL, url CLOB DEFAULT NULL, hashed_url VARCHAR(40) DEFAULT NULL, origin_url CLOB DEFAULT NULL, given_url CLOB DEFAULT NULL, hashed_given_url VARCHAR(40) DEFAULT NULL, is_archived BOOLEAN NOT NULL, archived_at DATETIME DEFAULT NULL, is_starred BOOLEAN NOT NULL, content CLOB DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, published_at DATETIME DEFAULT NULL, published_by CLOB DEFAULT NULL --(DC2Type:array) | |
72 | , starred_at DATETIME DEFAULT NULL, mimetype CLOB DEFAULT NULL, reading_time INTEGER NOT NULL, domain_name CLOB DEFAULT NULL, preview_picture CLOB DEFAULT NULL, http_status VARCHAR(3) DEFAULT NULL, headers CLOB DEFAULT NULL --(DC2Type:array) | |
73 | , language CLOB DEFAULT NULL COLLATE BINARY)'); | |
74 | $this->addSql('INSERT INTO ' . $this->getTable('entry', true) . ' (id, user_id, uid, title, url, hashed_url, origin_url, given_url, hashed_given_url, is_archived, archived_at, is_starred, content, created_at, updated_at, published_at, published_by, starred_at, mimetype, language, reading_time, domain_name, preview_picture, http_status, headers) SELECT id, user_id, uid, title, url, hashed_url, origin_url, given_url, hashed_given_url, is_archived, archived_at, is_starred, content, created_at, updated_at, published_at, published_by, starred_at, mimetype, language, reading_time, domain_name, preview_picture, http_status, headers FROM __temp__wallabag_entry'); | |
75 | $this->addSql('DROP TABLE __temp__wallabag_entry'); | |
76 | $this->addSql('CREATE INDEX IDX_F4D18282A76ED395 ON ' . $this->getTable('entry', true) . ' (user_id)'); | |
77 | $this->addSql('CREATE INDEX created_at ON ' . $this->getTable('entry', true) . ' (created_at)'); | |
78 | $this->addSql('CREATE INDEX uid ON ' . $this->getTable('entry', true) . ' (uid)'); | |
79 | $this->addSql('CREATE INDEX hashed_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_url)'); | |
80 | $this->addSql('CREATE INDEX hashed_given_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_given_url)'); | |
81 | break; | |
82 | case 'mysql': | |
83 | $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' MODIFY language LONGTEXT DEFAULT NULL'); | |
84 | $this->addSql('DROP INDEX user_language ON ' . $this->getTable('entry')); | |
588de419 | 85 | $this->addSql('DROP INDEX user_archived ON ' . $this->getTable('entry')); |
2b0f4e86 | 86 | $this->addSql('DROP INDEX user_created ON ' . $this->getTable('entry')); |
24acf2e0 JB |
87 | $this->addSql('DROP INDEX user_starred ON ' . $this->getTable('entry')); |
88 | break; | |
89 | case 'postgresql': | |
b700b38e | 90 | $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' ALTER language TYPE TEXT'); |
24acf2e0 | 91 | $this->addSql('DROP INDEX user_language ON ' . $this->getTable('entry')); |
588de419 | 92 | $this->addSql('DROP INDEX user_archived ON ' . $this->getTable('entry')); |
2b0f4e86 | 93 | $this->addSql('DROP INDEX user_created ON ' . $this->getTable('entry')); |
24acf2e0 JB |
94 | $this->addSql('DROP INDEX user_starred ON ' . $this->getTable('entry')); |
95 | break; | |
96 | } | |
d1dbe10e OM |
97 | } |
98 | } |