Full text search

[sergiorgiraldo]

I made a full-text search in addition to url/title/description.

1. Change the database:
   ALTER TABLE links ADD COLUMN check_content TINYINT DEFAULT 0;

CREATE TABLE link_text ( link_id int(11) NOT NULL, content longtext CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, FULLTEXT KEY index_content (content))

2.Create a script to download the contents (page-source.php) and schedule as cron job:

<?php

header('Content-Type: text/plain');

$servername = "host";
$username = "user";
$password = "pwd";
$dbname = "db";

$batch_size = isset($_GET['size'])?$_GET['size']:500;

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$query = "SELECT id, url FROM links WHERE check_content = 0 ORDER BY id";
$result = $conn->query($query);
$processed = 0;

while ($row = $result->fetch_assoc()) {
    if ($processed == $batch_size){
        break;
    }

    $linkId = $row["id"];
    $url = $row["url"];

    echo $linkId;
    echo "\n";
    echo $url;
    echo "\n";

    $checkQuery = "SELECT lt.link_id FROM link_text lt inner join links l on l.id = lt.link_id and l.url = '$url'";
    $checkResult = $conn->query($checkQuery);

    if ($checkResult) {
        if ($checkResult->num_rows == 0) {
            $htmlSource = downloadHtmlSource($url);
            if ($htmlSource != "n/a"){
                $stmt = $conn->prepare("INSERT INTO link_text (link_id, content) VALUES (?, ?)");
                $stmt->bind_param("is", $linkId, $htmlSource);
                $stmt->execute();

                echo "Saved\n";

                $processed += 1;
            }
            else{
                echo "Ignored\n";
            }
        }
        else {
            echo "Skipped\n";
        }
    }
    else {
        echo "Error: " . $checkQuery . "\n" . $conn->error;
    }

    $updateQuery = "UPDATE links SET check_content = 1 WHERE id = '$linkId'";
    $conn->query($updateQuery);
    echo "Updated\n\n";
}

$conn->close();

function filterUrls($url) {
    $urlsToIgnore = array("bing", "google", "duckduck", "localhost", "127.0.0.1");
    foreach ($urlsToIgnore as $item) {
        $pattern = "/" . $item . "/i";
        if (preg_match($pattern, $url)) {
            return true;
        }
    }
    return (!filter_var($url, FILTER_VALIDATE_URL));
  }
  
function downloadHtmlSource($url) {
    $text = "";

    if (filterUrls($url)) {
        return "n/a";
    }

    try{
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $text = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);

        if ($httpCode == 401 || $httpCode == 403) {
            $text = "n/a";
        }
    }
    catch(Throwable $e){
        $text = "n/a";
    }
    
    return $text;
}
?>

3. Change files to allow searching for full-text (off by default). Patch attached
   fulltextSearch.patch