Merge pull request #4081 from shtrom/fix-slow-queries

Fix slow/unindexed queries
author: Jérémy Benoist <j0k3r@users.noreply.github.com> 2019-08-08 15:27:49 +0200
committer: GitHub <noreply@github.com> 2019-08-08 15:27:49 +0200
commit: 97c678a0b26d748f9829d732910d5c402c609dc2 (patch)
tree: e82f95e192cd54e1094227c3987ae2af5215d2b4
parent: 1a6980dbd16fd75f7be2420feeb9cc72b46884ef (diff)
parent: 00567d9ccde71eba1aabf8fa0d547897f6870b28 (diff)
download: wallabag-97c678a0b26d748f9829d732910d5c402c609dc2.tar.gz
wallabag-97c678a0b26d748f9829d732910d5c402c609dc2.tar.zst
wallabag-97c678a0b26d748f9829d732910d5c402c609dc2.zip
4 files changed, 131 insertions, 3 deletions
diff --git a/app/DoctrineMigrations/Version20190806130304.php b/app/DoctrineMigrations/Version20190806130304.php
new file mode 100644
index 00000000..8b0271b1
--- /dev/null
+++ b/app/DoctrineMigrations/Version20190806130304.php
@@ -0,0 +1,116 @@
+<?php
+namespace Application\Migrations;
+use Doctrine\DBAL\Schema\Schema;
+use Wallabag\CoreBundle\Doctrine\WallabagMigration;
+/**
+ * Adding more index to kill some slow queries:
+ *     - user_language
+ *     - user_archived
+ *     - user_created
+ *     - user_starred
+ *     - tag_label
+ *     - config_feed_token.
+ */
+final class Version20190806130304 extends WallabagMigration
+{
+    public function up(Schema $schema): void
+    {
+        switch ($this->connection->getDatabasePlatform()->getName()) {
+            case 'sqlite':
+                $this->addSql('DROP INDEX uid');
+                $this->addSql('DROP INDEX created_at');
+                $this->addSql('DROP INDEX hashed_url_user_id');
+                $this->addSql('DROP INDEX IDX_F4D18282A76ED395');
+                $this->addSql('DROP INDEX hashed_given_url_user_id');
+                $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));
+                $this->addSql('DROP TABLE ' . $this->getTable('entry', true));
+                $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)
+                , headers CLOB DEFAULT NULL COLLATE BINARY --(DC2Type:array)
+                , 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)');
+                $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');
+                $this->addSql('DROP TABLE __temp__wallabag_entry');
+                $this->addSql('CREATE INDEX uid ON ' . $this->getTable('entry', true) . ' (uid)');
+                $this->addSql('CREATE INDEX created_at ON ' . $this->getTable('entry', true) . ' (created_at)');
+                $this->addSql('CREATE INDEX hashed_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_url)');
+                $this->addSql('CREATE INDEX IDX_F4D18282A76ED395 ON ' . $this->getTable('entry', true) . ' (user_id)');
+                $this->addSql('CREATE INDEX hashed_given_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_given_url)');
+                $this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry', true) . ' (language, user_id)');
+                $this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry', true) . ' (user_id, is_archived, archived_at)');
+                $this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry', true) . ' (user_id, created_at)');
+                $this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry', true) . ' (user_id, is_starred, starred_at)');
+                $this->addSql('CREATE INDEX tag_label ON ' . $this->getTable('tag', true) . ' (label)');
+                $this->addSql('CREATE INDEX config_feed_token ON ' . $this->getTable('config', true) . ' (feed_token)');
+                break;
+            case 'mysql':
+                $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' MODIFY language VARCHAR(20) DEFAULT NULL');
+                $this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry') . ' (language, user_id)');
+                $this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry') . ' (user_id, is_archived, archived_at)');
+                $this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry') . ' (user_id, created_at)');
+                $this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry') . ' (user_id, is_starred, starred_at)');
+                $this->addSql('CREATE INDEX tag_label ON ' . $this->getTable('tag') . ' (label (255))');
+                $this->addSql('CREATE INDEX config_feed_token ON ' . $this->getTable('config') . ' (feed_token (255))');
+                break;
+            case 'postgresql':
+                $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' ALTER language TYPE VARCHAR(20)');
+                $this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry') . ' (language, user_id)');
+                $this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry') . ' (user_id, is_archived, archived_at)');
+                $this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry') . ' (user_id, created_at)');
+                $this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry') . ' (user_id, is_starred, starred_at)');
+                $this->addSql('CREATE INDEX tag_label ON ' . $this->getTable('tag') . ' (label)');
+                $this->addSql('CREATE INDEX config_feed_token ON ' . $this->getTable('config') . ' (feed_token)');
+                break;
+        }
+    }
+    public function down(Schema $schema): void
+    {
+        switch ($this->connection->getDatabasePlatform()->getName()) {
+            case 'sqlite':
+                $this->addSql('DROP INDEX IDX_F4D18282A76ED395');
+                $this->addSql('DROP INDEX created_at');
+                $this->addSql('DROP INDEX uid');
+                $this->addSql('DROP INDEX hashed_url_user_id');
+                $this->addSql('DROP INDEX hashed_given_url_user_id');
+                $this->addSql('DROP INDEX user_language');
+                $this->addSql('DROP INDEX user_archived');
+                $this->addSql('DROP INDEX user_created');
+                $this->addSql('DROP INDEX user_starred');
+                $this->addSql('DROP INDEX tag_label');
+                $this->addSql('DROP INDEX config_feed_token');
+                $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));
+                $this->addSql('DROP TABLE ' . $this->getTable('entry', true));
+                $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)
+                , 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)
+                , language CLOB DEFAULT NULL COLLATE BINARY)');
+                $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');
+                $this->addSql('DROP TABLE __temp__wallabag_entry');
+                $this->addSql('CREATE INDEX IDX_F4D18282A76ED395 ON ' . $this->getTable('entry', true) . ' (user_id)');
+                $this->addSql('CREATE INDEX created_at ON ' . $this->getTable('entry', true) . ' (created_at)');
+                $this->addSql('CREATE INDEX uid ON ' . $this->getTable('entry', true) . ' (uid)');
+                $this->addSql('CREATE INDEX hashed_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_url)');
+                $this->addSql('CREATE INDEX hashed_given_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_given_url)');
+                break;
+            case 'mysql':
+                $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' MODIFY language LONGTEXT DEFAULT NULL');
+                $this->addSql('DROP INDEX user_language ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX user_archived ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX user_created ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX user_starred ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX tag_label ON ' . $this->getTable('tag'));
+                $this->addSql('DROP INDEX config_feed_token ON ' . $this->getTable('config'));
+                break;
+            case 'postgresql':
+                $this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' ALTER language TYPE TEXT');
+                $this->addSql('DROP INDEX user_language ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX user_archived ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX user_created ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX user_starred ON ' . $this->getTable('entry'));
+                $this->addSql('DROP INDEX tag_label ON ' . $this->getTable('tag'));
+                $this->addSql('DROP INDEX config_feed_token ON ' . $this->getTable('config'));
+                break;
+        }
+    }
+}
diff --git a/src/Wallabag/CoreBundle/Entity/Config.php b/src/Wallabag/CoreBundle/Entity/Config.php
index c6e65d66..5181d91d 100644
--- a/src/Wallabag/CoreBundle/Entity/Config.php
+++ b/src/Wallabag/CoreBundle/Entity/Config.php
@@ -11,7 +11,12 @@ use Wallabag\UserBundle\Entity\User;
 * Config.
 *
 * @ORM\Entity(repositoryClass="Wallabag\CoreBundle\Repository\ConfigRepository")
- * @ORM\Table(name="`config`")
+ * @ORM\Table(
+ *     name="`config`",
+ *     indexes={
+ *         @ORM\Index(name="config_feed_token", columns={"feed_token"}, options={"lengths"={255}}),
+ *     }
+ * )
 * @ORM\Entity
 */
 class Config
diff --git a/src/Wallabag/CoreBundle/Entity/Entry.php b/src/Wallabag/CoreBundle/Entity/Entry.php
index 4d5e6fc9..4acec261 100644
--- a/src/Wallabag/CoreBundle/Entity/Entry.php
+++ b/src/Wallabag/CoreBundle/Entity/Entry.php
@@ -28,7 +28,11 @@ use Wallabag\UserBundle\Entity\User;
 *         @ORM\Index(name="created_at", columns={"created_at"}),
 *         @ORM\Index(name="uid", columns={"uid"}),
 *         @ORM\Index(name="hashed_url_user_id", columns={"user_id", "hashed_url"}, options={"lengths"={null, 40}}),
- *         @ORM\Index(name="hashed_given_url_user_id", columns={"user_id", "hashed_given_url"}, options={"lengths"={null, 40}})
+ *         @ORM\Index(name="hashed_given_url_user_id", columns={"user_id", "hashed_given_url"}, options={"lengths"={null, 40}}),
+ *         @ORM\Index(name="user_language", columns={"language", "user_id"}),
+ *         @ORM\Index(name="user_archived", columns={"user_id", "is_archived", "archived_at"}),
+ *         @ORM\Index(name="user_created", columns={"user_id", "created_at"}),
+ *         @ORM\Index(name="user_starred", columns={"user_id", "is_starred", "starred_at"})
 *     }
 * )
 * @ORM\HasLifecycleCallbacks()
@@ -221,7 +225,7 @@ class Entry
    /**
     * @var string
     *
-     * @ORM\Column(name="language", type="text", nullable=true)
+     * @ORM\Column(name="language", type="string", length=20, nullable=true)
     *
     * @Groups({"entries_for_user", "export_all"})
     */
diff --git a/src/Wallabag/CoreBundle/Entity/Tag.php b/src/Wallabag/CoreBundle/Entity/Tag.php
index 95c47bbd..3ccb20a5 100644
--- a/src/Wallabag/CoreBundle/Entity/Tag.php
+++ b/src/Wallabag/CoreBundle/Entity/Tag.php
@@ -16,6 +16,9 @@ use JMS\Serializer\Annotation\XmlRoot;
 * @ORM\Table(
 *     name="`tag`",
 *     options={"collate"="utf8mb4_bin", "charset"="utf8mb4"},
+ *     indexes={
+ *         @ORM\Index(name="tag_label", columns={"label"}, options={"lengths"={255}}),
+ *     }
 * )
 * @ORM\Entity(repositoryClass="Wallabag\CoreBundle\Repository\TagRepository")
 * @ExclusionPolicy("all")
author	Jérémy Benoist <j0k3r@users.noreply.github.com>	2019-08-08 15:27:49 +0200
committer	GitHub <noreply@github.com>	2019-08-08 15:27:49 +0200
commit	97c678a0b26d748f9829d732910d5c402c609dc2 (patch)
tree	e82f95e192cd54e1094227c3987ae2af5215d2b4
parent	1a6980dbd16fd75f7be2420feeb9cc72b46884ef (diff)
parent	00567d9ccde71eba1aabf8fa0d547897f6870b28 (diff)
download	wallabag-97c678a0b26d748f9829d732910d5c402c609dc2.tar.gz wallabag-97c678a0b26d748f9829d732910d5c402c609dc2.tar.zst wallabag-97c678a0b26d748f9829d732910d5c402c609dc2.zip

diff --git a/app/DoctrineMigrations/Version20190806130304.php b/app/DoctrineMigrations/Version20190806130304.php new file mode 100644 index 00000000..8b0271b1 --- /dev/null +++ b/app/DoctrineMigrations/Version20190806130304.php
@@ -0,0 +1,116 @@
		1	<?php
		2
		3	namespace Application\Migrations;
		4
		5	use Doctrine\DBAL\Schema\Schema;
		6	use Wallabag\CoreBundle\Doctrine\WallabagMigration;
		7
		8	/**
		9	* Adding more index to kill some slow queries:
		10	* - user_language
		11	* - user_archived
		12	* - user_created
		13	* - user_starred
		14	* - tag_label
		15	* - config_feed_token.
		16	*/
		17	final class Version20190806130304 extends WallabagMigration
		18	{
		19	public function up(Schema $schema): void
		20	{
		21	switch ($this->connection->getDatabasePlatform()->getName()) {
		22	case 'sqlite':
		23	$this->addSql('DROP INDEX uid');
		24	$this->addSql('DROP INDEX created_at');
		25	$this->addSql('DROP INDEX hashed_url_user_id');
		26	$this->addSql('DROP INDEX IDX_F4D18282A76ED395');
		27	$this->addSql('DROP INDEX hashed_given_url_user_id');
		28	$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));
		29	$this->addSql('DROP TABLE ' . $this->getTable('entry', true));
		30	$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)
		31	, headers CLOB DEFAULT NULL COLLATE BINARY --(DC2Type:array)
		32	, 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)');
		33	$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');
		34	$this->addSql('DROP TABLE __temp__wallabag_entry');
		35	$this->addSql('CREATE INDEX uid ON ' . $this->getTable('entry', true) . ' (uid)');
		36	$this->addSql('CREATE INDEX created_at ON ' . $this->getTable('entry', true) . ' (created_at)');
		37	$this->addSql('CREATE INDEX hashed_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_url)');
		38	$this->addSql('CREATE INDEX IDX_F4D18282A76ED395 ON ' . $this->getTable('entry', true) . ' (user_id)');
		39	$this->addSql('CREATE INDEX hashed_given_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_given_url)');
		40	$this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry', true) . ' (language, user_id)');
		41	$this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry', true) . ' (user_id, is_archived, archived_at)');
		42	$this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry', true) . ' (user_id, created_at)');
		43	$this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry', true) . ' (user_id, is_starred, starred_at)');
		44	$this->addSql('CREATE INDEX tag_label ON ' . $this->getTable('tag', true) . ' (label)');
		45	$this->addSql('CREATE INDEX config_feed_token ON ' . $this->getTable('config', true) . ' (feed_token)');
		46	break;
		47	case 'mysql':
		48	$this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' MODIFY language VARCHAR(20) DEFAULT NULL');
		49	$this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry') . ' (language, user_id)');
		50	$this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry') . ' (user_id, is_archived, archived_at)');
		51	$this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry') . ' (user_id, created_at)');
		52	$this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry') . ' (user_id, is_starred, starred_at)');
		53	$this->addSql('CREATE INDEX tag_label ON ' . $this->getTable('tag') . ' (label (255))');
		54	$this->addSql('CREATE INDEX config_feed_token ON ' . $this->getTable('config') . ' (feed_token (255))');
		55	break;
		56	case 'postgresql':
		57	$this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' ALTER language TYPE VARCHAR(20)');
		58	$this->addSql('CREATE INDEX user_language ON ' . $this->getTable('entry') . ' (language, user_id)');
		59	$this->addSql('CREATE INDEX user_archived ON ' . $this->getTable('entry') . ' (user_id, is_archived, archived_at)');
		60	$this->addSql('CREATE INDEX user_created ON ' . $this->getTable('entry') . ' (user_id, created_at)');
		61	$this->addSql('CREATE INDEX user_starred ON ' . $this->getTable('entry') . ' (user_id, is_starred, starred_at)');
		62	$this->addSql('CREATE INDEX tag_label ON ' . $this->getTable('tag') . ' (label)');
		63	$this->addSql('CREATE INDEX config_feed_token ON ' . $this->getTable('config') . ' (feed_token)');
		64	break;
		65	}
		66	}
		67
		68	public function down(Schema $schema): void
		69	{
		70	switch ($this->connection->getDatabasePlatform()->getName()) {
		71	case 'sqlite':
		72	$this->addSql('DROP INDEX IDX_F4D18282A76ED395');
		73	$this->addSql('DROP INDEX created_at');
		74	$this->addSql('DROP INDEX uid');
		75	$this->addSql('DROP INDEX hashed_url_user_id');
		76	$this->addSql('DROP INDEX hashed_given_url_user_id');
		77	$this->addSql('DROP INDEX user_language');
		78	$this->addSql('DROP INDEX user_archived');
		79	$this->addSql('DROP INDEX user_created');
		80	$this->addSql('DROP INDEX user_starred');
		81	$this->addSql('DROP INDEX tag_label');
		82	$this->addSql('DROP INDEX config_feed_token');
		83	$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));
		84	$this->addSql('DROP TABLE ' . $this->getTable('entry', true));
		85	$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)
		86	, 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)
		87	, language CLOB DEFAULT NULL COLLATE BINARY)');
		88	$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');
		89	$this->addSql('DROP TABLE __temp__wallabag_entry');
		90	$this->addSql('CREATE INDEX IDX_F4D18282A76ED395 ON ' . $this->getTable('entry', true) . ' (user_id)');
		91	$this->addSql('CREATE INDEX created_at ON ' . $this->getTable('entry', true) . ' (created_at)');
		92	$this->addSql('CREATE INDEX uid ON ' . $this->getTable('entry', true) . ' (uid)');
		93	$this->addSql('CREATE INDEX hashed_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_url)');
		94	$this->addSql('CREATE INDEX hashed_given_url_user_id ON ' . $this->getTable('entry', true) . ' (user_id, hashed_given_url)');
		95	break;
		96	case 'mysql':
		97	$this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' MODIFY language LONGTEXT DEFAULT NULL');
		98	$this->addSql('DROP INDEX user_language ON ' . $this->getTable('entry'));
		99	$this->addSql('DROP INDEX user_archived ON ' . $this->getTable('entry'));
		100	$this->addSql('DROP INDEX user_created ON ' . $this->getTable('entry'));
		101	$this->addSql('DROP INDEX user_starred ON ' . $this->getTable('entry'));
		102	$this->addSql('DROP INDEX tag_label ON ' . $this->getTable('tag'));
		103	$this->addSql('DROP INDEX config_feed_token ON ' . $this->getTable('config'));
		104	break;
		105	case 'postgresql':
		106	$this->addSql('ALTER TABLE ' . $this->getTable('entry') . ' ALTER language TYPE TEXT');
		107	$this->addSql('DROP INDEX user_language ON ' . $this->getTable('entry'));
		108	$this->addSql('DROP INDEX user_archived ON ' . $this->getTable('entry'));
		109	$this->addSql('DROP INDEX user_created ON ' . $this->getTable('entry'));
		110	$this->addSql('DROP INDEX user_starred ON ' . $this->getTable('entry'));
		111	$this->addSql('DROP INDEX tag_label ON ' . $this->getTable('tag'));
		112	$this->addSql('DROP INDEX config_feed_token ON ' . $this->getTable('config'));
		113	break;
		114	}
		115	}
		116	}


diff --git a/src/Wallabag/CoreBundle/Entity/Config.php b/src/Wallabag/CoreBundle/Entity/Config.php index c6e65d66..5181d91d 100644 --- a/src/Wallabag/CoreBundle/Entity/Config.php +++ b/src/Wallabag/CoreBundle/Entity/Config.php
@@ -11,7 +11,12 @@ use Wallabag\UserBundle\Entity\User;
11	* Config.	11	* Config.
12	*	12	*
13	* @ORM\Entity(repositoryClass="Wallabag\CoreBundle\Repository\ConfigRepository")	13	* @ORM\Entity(repositoryClass="Wallabag\CoreBundle\Repository\ConfigRepository")
14	* @ORM\Table(name="`config`")	14	* @ORM\Table(
		15	* name="`config`",
		16	* indexes={
		17	* @ORM\Index(name="config_feed_token", columns={"feed_token"}, options={"lengths"={255}}),
		18	* }
		19	* )
15	* @ORM\Entity	20	* @ORM\Entity
16	*/	21	*/
17	class Config	22	class Config


diff --git a/src/Wallabag/CoreBundle/Entity/Entry.php b/src/Wallabag/CoreBundle/Entity/Entry.php index 4d5e6fc9..4acec261 100644 --- a/src/Wallabag/CoreBundle/Entity/Entry.php +++ b/src/Wallabag/CoreBundle/Entity/Entry.php
@@ -28,7 +28,11 @@ use Wallabag\UserBundle\Entity\User;
28	* @ORM\Index(name="created_at", columns={"created_at"}),	28	* @ORM\Index(name="created_at", columns={"created_at"}),
29	* @ORM\Index(name="uid", columns={"uid"}),	29	* @ORM\Index(name="uid", columns={"uid"}),
30	* @ORM\Index(name="hashed_url_user_id", columns={"user_id", "hashed_url"}, options={"lengths"={null, 40}}),	30	* @ORM\Index(name="hashed_url_user_id", columns={"user_id", "hashed_url"}, options={"lengths"={null, 40}}),
31	* @ORM\Index(name="hashed_given_url_user_id", columns={"user_id", "hashed_given_url"}, options={"lengths"={null, 40}})	31	* @ORM\Index(name="hashed_given_url_user_id", columns={"user_id", "hashed_given_url"}, options={"lengths"={null, 40}}),
		32	* @ORM\Index(name="user_language", columns={"language", "user_id"}),
		33	* @ORM\Index(name="user_archived", columns={"user_id", "is_archived", "archived_at"}),
		34	* @ORM\Index(name="user_created", columns={"user_id", "created_at"}),
		35	* @ORM\Index(name="user_starred", columns={"user_id", "is_starred", "starred_at"})
32	* }	36	* }
33	* )	37	* )
34	* @ORM\HasLifecycleCallbacks()	38	* @ORM\HasLifecycleCallbacks()
@@ -221,7 +225,7 @@ class Entry
221	/**	225	/**
222	* @var string	226	* @var string
223	*	227	*
224	* @ORM\Column(name="language", type="text", nullable=true)	228	* @ORM\Column(name="language", type="string", length=20, nullable=true)
225	*	229	*
226	* @Groups({"entries_for_user", "export_all"})	230	* @Groups({"entries_for_user", "export_all"})
227	*/	231	*/


diff --git a/src/Wallabag/CoreBundle/Entity/Tag.php b/src/Wallabag/CoreBundle/Entity/Tag.php index 95c47bbd..3ccb20a5 100644 --- a/src/Wallabag/CoreBundle/Entity/Tag.php +++ b/src/Wallabag/CoreBundle/Entity/Tag.php
@@ -16,6 +16,9 @@ use JMS\Serializer\Annotation\XmlRoot;
16	* @ORM\Table(	16	* @ORM\Table(
17	* name="`tag`",	17	* name="`tag`",
18	* options={"collate"="utf8mb4_bin", "charset"="utf8mb4"},	18	* options={"collate"="utf8mb4_bin", "charset"="utf8mb4"},
		19	* indexes={
		20	* @ORM\Index(name="tag_label", columns={"label"}, options={"lengths"={255}}),
		21	* }
19	* )	22	* )
20	* @ORM\Entity(repositoryClass="Wallabag\CoreBundle\Repository\TagRepository")	23	* @ORM\Entity(repositoryClass="Wallabag\CoreBundle\Repository\TagRepository")
21	* @ExclusionPolicy("all")	24	* @ExclusionPolicy("all")