diff options
author | Jeremy Benoist <jeremy.benoist@gmail.com> | 2019-05-29 14:18:04 +0200 |
---|---|---|
committer | Jeremy Benoist <jeremy.benoist@gmail.com> | 2019-05-29 15:56:20 +0200 |
commit | f3bfb875e94021a93e24a41fbc0f8d86d4dee378 (patch) | |
tree | 34f7efd0f3dc5326f68364e2fb2544619518c371 /app/DoctrineMigrations | |
parent | b7fa51ae7dd5fef2d9459100c88479413ddd3fb3 (diff) | |
download | wallabag-f3bfb875e94021a93e24a41fbc0f8d86d4dee378.tar.gz wallabag-f3bfb875e94021a93e24a41fbc0f8d86d4dee378.tar.zst wallabag-f3bfb875e94021a93e24a41fbc0f8d86d4dee378.zip |
Use hash given url to avoid duplicate
Using hashed url we can ensure an index on them to ensure it's fast.
Diffstat (limited to 'app/DoctrineMigrations')
-rw-r--r-- | app/DoctrineMigrations/Version20170710125843.php | 38 | ||||
-rw-r--r-- | app/DoctrineMigrations/Version20171218135243.php | 48 | ||||
-rw-r--r-- | app/DoctrineMigrations/Version20190601125843.php | 74 |
3 files changed, 74 insertions, 86 deletions
diff --git a/app/DoctrineMigrations/Version20170710125843.php b/app/DoctrineMigrations/Version20170710125843.php deleted file mode 100644 index 2cf8647a..00000000 --- a/app/DoctrineMigrations/Version20170710125843.php +++ /dev/null | |||
@@ -1,38 +0,0 @@ | |||
1 | <?php | ||
2 | |||
3 | namespace Application\Migrations; | ||
4 | |||
5 | use Doctrine\DBAL\Schema\Schema; | ||
6 | use Wallabag\CoreBundle\Doctrine\WallabagMigration; | ||
7 | |||
8 | /** | ||
9 | * Added `given_url` field in entry table. | ||
10 | */ | ||
11 | class Version20170710125843 extends WallabagMigration | ||
12 | { | ||
13 | /** | ||
14 | * @param Schema $schema | ||
15 | */ | ||
16 | public function up(Schema $schema) | ||
17 | { | ||
18 | $entryTable = $schema->getTable($this->getTable('entry')); | ||
19 | |||
20 | $this->skipIf($entryTable->hasColumn('given_url'), 'It seems that you already played this migration.'); | ||
21 | |||
22 | $entryTable->addColumn('given_url', 'text', [ | ||
23 | 'notnull' => false, | ||
24 | ]); | ||
25 | } | ||
26 | |||
27 | /** | ||
28 | * @param Schema $schema | ||
29 | */ | ||
30 | public function down(Schema $schema) | ||
31 | { | ||
32 | $entryTable = $schema->getTable($this->getTable('entry')); | ||
33 | |||
34 | $this->skipIf(!$entryTable->hasColumn('given_url'), 'It seems that you already played this migration.'); | ||
35 | |||
36 | $entryTable->dropColumn('given_url'); | ||
37 | } | ||
38 | } | ||
diff --git a/app/DoctrineMigrations/Version20171218135243.php b/app/DoctrineMigrations/Version20171218135243.php deleted file mode 100644 index 3060c920..00000000 --- a/app/DoctrineMigrations/Version20171218135243.php +++ /dev/null | |||
@@ -1,48 +0,0 @@ | |||
1 | <?php | ||
2 | |||
3 | namespace Application\Migrations; | ||
4 | |||
5 | use Doctrine\DBAL\Schema\Schema; | ||
6 | use Wallabag\CoreBundle\Doctrine\WallabagMigration; | ||
7 | |||
8 | /** | ||
9 | * Added indexes on wallabag_entry.url and wallabag_entry.given_url and wallabag_entry.user_id. | ||
10 | */ | ||
11 | class Version20171218135243 extends WallabagMigration | ||
12 | { | ||
13 | private $indexGivenUrl = 'IDX_entry_given_url'; | ||
14 | |||
15 | /** | ||
16 | * @param Schema $schema | ||
17 | */ | ||
18 | public function up(Schema $schema) | ||
19 | { | ||
20 | $entryTable = $schema->getTable($this->getTable('entry')); | ||
21 | $this->skipIf($entryTable->hasIndex($this->indexGivenUrl), 'It seems that you already played this migration.'); | ||
22 | |||
23 | switch ($this->connection->getDatabasePlatform()->getName()) { | ||
24 | case 'sqlite': | ||
25 | $sql = 'CREATE UNIQUE INDEX ' . $this->indexGivenUrl . ' ON ' . $this->getTable('entry') . ' (url, given_url, user_id);'; | ||
26 | break; | ||
27 | case 'mysql': | ||
28 | $sql = 'CREATE UNIQUE INDEX ' . $this->indexGivenUrl . ' ON ' . $this->getTable('entry') . ' (url (255), given_url (255), user_id);'; | ||
29 | break; | ||
30 | case 'postgresql': | ||
31 | $sql = 'CREATE UNIQUE INDEX ' . $this->indexGivenUrl . ' ON ' . $this->getTable('entry') . ' (url, given_url, user_id);'; | ||
32 | break; | ||
33 | } | ||
34 | |||
35 | $this->addSql($sql); | ||
36 | } | ||
37 | |||
38 | /** | ||
39 | * @param Schema $schema | ||
40 | */ | ||
41 | public function down(Schema $schema) | ||
42 | { | ||
43 | $entryTable = $schema->getTable($this->getTable('entry')); | ||
44 | $this->skipIf(false === $entryTable->hasIndex($this->indexGivenUrl), 'It seems that you already played this migration.'); | ||
45 | |||
46 | $entryTable->dropIndex($this->indexGivenUrl); | ||
47 | } | ||
48 | } | ||
diff --git a/app/DoctrineMigrations/Version20190601125843.php b/app/DoctrineMigrations/Version20190601125843.php new file mode 100644 index 00000000..341d64dc --- /dev/null +++ b/app/DoctrineMigrations/Version20190601125843.php | |||
@@ -0,0 +1,74 @@ | |||
1 | <?php | ||
2 | |||
3 | namespace Application\Migrations; | ||
4 | |||
5 | use Doctrine\DBAL\Schema\Schema; | ||
6 | use Wallabag\CoreBundle\Doctrine\WallabagMigration; | ||
7 | |||
8 | /** | ||
9 | * Added `given_url` & `hashed_given_url` field in entry table. | ||
10 | */ | ||
11 | class Version20190601125843 extends WallabagMigration | ||
12 | { | ||
13 | /** | ||
14 | * @param Schema $schema | ||
15 | */ | ||
16 | public function up(Schema $schema) | ||
17 | { | ||
18 | $entryTable = $schema->getTable($this->getTable('entry')); | ||
19 | |||
20 | if (!$entryTable->hasColumn('given_url')) { | ||
21 | $entryTable->addColumn('given_url', 'text', [ | ||
22 | 'notnull' => false, | ||
23 | ]); | ||
24 | } | ||
25 | |||
26 | if (!$entryTable->hasColumn('hashed_given_url')) { | ||
27 | $entryTable->addColumn('hashed_given_url', 'text', [ | ||
28 | 'length' => 40, | ||
29 | 'notnull' => false, | ||
30 | ]); | ||
31 | } | ||
32 | |||
33 | $entryTable->dropIndex('hashed_url_user_id'); | ||
34 | $entryTable->addIndex( | ||
35 | [ | ||
36 | 'user_id', | ||
37 | 'hashed_url', | ||
38 | 'hashed_given_url', | ||
39 | ], | ||
40 | 'hashed_urls_user_id', | ||
41 | [], | ||
42 | [ | ||
43 | // specify length for index which is required by MySQL on text field | ||
44 | 'lengths' => [ | ||
45 | // user_id | ||
46 | null, | ||
47 | // hashed_url | ||
48 | 40, | ||
49 | // hashed_given_url | ||
50 | 40, | ||
51 | ], | ||
52 | ] | ||
53 | ); | ||
54 | } | ||
55 | |||
56 | /** | ||
57 | * @param Schema $schema | ||
58 | */ | ||
59 | public function down(Schema $schema) | ||
60 | { | ||
61 | $entryTable = $schema->getTable($this->getTable('entry')); | ||
62 | |||
63 | if ($entryTable->hasColumn('given_url')) { | ||
64 | $entryTable->dropColumn('given_url'); | ||
65 | } | ||
66 | |||
67 | if ($entryTable->hasColumn('hashed_given_url')) { | ||
68 | $entryTable->dropColumn('hashed_given_url'); | ||
69 | } | ||
70 | |||
71 | $entryTable->dropIndex('hashed_urls_user_id'); | ||
72 | $entryTable->addIndex(['user_id', 'hashed_url'], 'hashed_url_user_id', [], ['lengths' => [null, 40]]); | ||
73 | } | ||
74 | } | ||