Use hash given url to avoid duplicate

Using hashed url we can ensure an index on them to ensure it's fast.

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 @@
-<?php
-
-namespace Application\Migrations;
-
-use Doctrine\DBAL\Schema\Schema;
-use Wallabag\CoreBundle\Doctrine\WallabagMigration;
-
-/**
- * Added `given_url` field in entry table.
- */
-class Version20170710125843 extends WallabagMigration
-{
-    /**
-     * @param Schema $schema
-     */
-    public function up(Schema $schema)
-    {
-        $entryTable = $schema->getTable($this->getTable('entry'));
-
-        $this->skipIf($entryTable->hasColumn('given_url'), 'It seems that you already played this migration.');
-
-        $entryTable->addColumn('given_url', 'text', [
-            'notnull' => false,
-        ]);
-    }
-
-    /**
-     * @param Schema $schema
-     */
-    public function down(Schema $schema)
-    {
-        $entryTable = $schema->getTable($this->getTable('entry'));
-
-        $this->skipIf(!$entryTable->hasColumn('given_url'), 'It seems that you already played this migration.');
-
-        $entryTable->dropColumn('given_url');
-    }
-}
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 @@
-<?php
-
-namespace Application\Migrations;
-
-use Doctrine\DBAL\Schema\Schema;
-use Wallabag\CoreBundle\Doctrine\WallabagMigration;
-
-/**
- * Added indexes on wallabag_entry.url and wallabag_entry.given_url and wallabag_entry.user_id.
- */
-class Version20171218135243 extends WallabagMigration
-{
-    private $indexGivenUrl = 'IDX_entry_given_url';
-
-    /**
-     * @param Schema $schema
-     */
-    public function up(Schema $schema)
-    {
-        $entryTable = $schema->getTable($this->getTable('entry'));
-        $this->skipIf($entryTable->hasIndex($this->indexGivenUrl), 'It seems that you already played this migration.');
-
-        switch ($this->connection->getDatabasePlatform()->getName()) {
-            case 'sqlite':
-                $sql = 'CREATE UNIQUE INDEX ' . $this->indexGivenUrl . ' ON ' . $this->getTable('entry') . ' (url, given_url, user_id);';
-                break;
-            case 'mysql':
-                $sql = 'CREATE UNIQUE INDEX ' . $this->indexGivenUrl . ' ON ' . $this->getTable('entry') . ' (url (255), given_url (255), user_id);';
-                break;
-            case 'postgresql':
-                $sql = 'CREATE UNIQUE INDEX ' . $this->indexGivenUrl . ' ON ' . $this->getTable('entry') . ' (url, given_url, user_id);';
-                break;
-        }
-
-        $this->addSql($sql);
-    }
-
-    /**
-     * @param Schema $schema
-     */
-    public function down(Schema $schema)
-    {
-        $entryTable = $schema->getTable($this->getTable('entry'));
-        $this->skipIf(false === $entryTable->hasIndex($this->indexGivenUrl), 'It seems that you already played this migration.');
-
-        $entryTable->dropIndex($this->indexGivenUrl);
-    }
-}
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 @@
+<?php
+
+namespace Application\Migrations;
+
+use Doctrine\DBAL\Schema\Schema;
+use Wallabag\CoreBundle\Doctrine\WallabagMigration;
+
+/**
+ * Added `given_url` & `hashed_given_url` field in entry table.
+ */
+class Version20190601125843 extends WallabagMigration
+{
+    /**
+     * @param Schema $schema
+     */
+    public function up(Schema $schema)
+    {
+        $entryTable = $schema->getTable($this->getTable('entry'));
+
+        if (!$entryTable->hasColumn('given_url')) {
+            $entryTable->addColumn('given_url', 'text', [
+                'notnull' => false,
+            ]);
+        }
+
+        if (!$entryTable->hasColumn('hashed_given_url')) {
+            $entryTable->addColumn('hashed_given_url', 'text', [
+                'length' => 40,
+                'notnull' => false,
+            ]);
+        }
+
+        $entryTable->dropIndex('hashed_url_user_id');
+        $entryTable->addIndex(
+            [
+                'user_id',
+                'hashed_url',
+                'hashed_given_url',
+            ],
+            'hashed_urls_user_id',
+            [],
+            [
+                // specify length for index which is required by MySQL on text field
+                'lengths' => [
+                    // user_id
+                    null,
+                    // hashed_url
+                    40,
+                    // hashed_given_url
+                    40,
+                ],
+            ]
+        );
+    }
+
+    /**
+     * @param Schema $schema
+     */
+    public function down(Schema $schema)
+    {
+        $entryTable = $schema->getTable($this->getTable('entry'));
+
+        if ($entryTable->hasColumn('given_url')) {
+            $entryTable->dropColumn('given_url');
+        }
+
+        if ($entryTable->hasColumn('hashed_given_url')) {
+            $entryTable->dropColumn('hashed_given_url');
+        }
+
+        $entryTable->dropIndex('hashed_urls_user_id');
+        $entryTable->addIndex(['user_id', 'hashed_url'], 'hashed_url_user_id', [], ['lengths' => [null, 40]]);
+    }
+}