Add indexes to link table and use cr32 hashes to increase speed of lookups

4 files changed, 69 insertions, 1 deletions

diff --git a/app/Factories/LinkFactory.php b/app/Factories/LinkFactory.php
index cb13d3d..0e468e2 100644
--- a/app/Factories/LinkFactory.php
+++ b/app/Factories/LinkFactory.php
@@ -7,6 +7,8 @@ use App\Helpers\LinkHelper;
 
 
 class LinkFactory {
+    const MAXIMUM_LINK_LENGTH = 65535;
+
     private static function formatLink($link_ending, $secret_ending=false) {
         /**
         * Given a link ending and a boolean indicating whether a secret ending is needed,
@@ -39,6 +41,13 @@ class LinkFactory {
         * @return string $formatted_link
         */
 
+        if (strlen($long_url) > self::MAXIMUM_LINK_LENGTH) {
+            // If $long_url is longer than the maximum length, then
+            // throw an Exception
+            throw new \Exception('Sorry, but your link is longer than the
+                maximum length allowed.');
+        }
+
         $is_already_short = LinkHelper::checkIfAlreadyShortened($long_url);
 
         if ($is_already_short) {
diff --git a/app/Helpers/LinkHelper.php b/app/Helpers/LinkHelper.php
index ba9733c..b876d77 100644
--- a/app/Helpers/LinkHelper.php
+++ b/app/Helpers/LinkHelper.php
@@ -57,7 +57,7 @@ class LinkHelper {
          * check whether the link is in the DB.
          * @return boolean
          */
-        $link = Link::where('long_url', $long_url)
+        $link = Link::longUrl($long_url)
             ->where('is_custom', 0)
             ->where('secret_key', '')
             ->first();
diff --git a/app/Models/Link.php b/app/Models/Link.php
index 98ae8c6..9792c94 100644
--- a/app/Models/Link.php
+++ b/app/Models/Link.php
@@ -4,4 +4,27 @@ use Illuminate\Database\Eloquent\Model;
 
 class Link extends Model {
     protected $table = 'links';
+
+    public function setLongUrlAttribute($long_url) {
+        // Set crc32 hash and long_url
+        // whenever long_url is set on a Link instance
+
+        // Generate 32-bit unsigned integer crc32 value
+        // Use sprintf to prevent compatibility issues with 32-bit systems
+        // http://php.net/manual/en/function.crc32.php
+        $crc32_hash = sprintf('%u', crc32($long_url));
+
+        $this->attributes['long_url'] = $long_url;
+        $this->attributes['long_url_hash'] = $crc32_hash;
+    }
+
+    public function scopeLongUrl($query, $long_url) {
+        // Allow quick lookups with Link::longUrl that make use
+        // of the indexed crc32 hash to quickly fetch link
+        $crc32_hash = sprintf('%u', crc32($long_url));
+
+        return $query
+            ->where('long_url_hash', $crc32_hash)
+            ->where('long_url', $long_url);
+    }
 }
diff --git a/database/migrations/2017_02_04_025727_add_link_table_indexes.php b/database/migrations/2017_02_04_025727_add_link_table_indexes.php
new file mode 100644
index 0000000..8db19b7
--- /dev/null
+++ b/database/migrations/2017_02_04_025727_add_link_table_indexes.php
@@ -0,0 +1,36 @@
+<?php
+
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Database\Migrations\Migration;
+
+class AddLinkTableIndexes extends Migration
+{
+    /**
+     * Run the migrations.
+     *
+     * @return void
+     */
+    public function up()
+    {
+        Schema::table('links', function (Blueprint $table)
+        {
+            // Add long_url hashes
+            $table->unique('short_url');
+            $table->string('long_url_hash', 10)->nullable();
+            $table->index('long_url_hash', 'links_long_url_index');
+        });
+
+        DB::query("UPDATE links SET long_url_hash = crc32(long_url)");
+    }
+
+    public function down()
+    {
+        Schema::table('links', function (Blueprint $table)
+        {
+            $table->longtext('long_url')->change();
+            $table->dropUnique('links_short_url_unique');
+            $table->dropIndex('links_long_url_index');
+            $table->dropColumn('long_url_hash');
+        });
+    }
+}