af_psql_trgm: add support for querying against mariadb FULLTEXT indexes

1 files changed, 64 insertions, 34 deletions

diff --git a/plugins/af_psql_trgm/init.php b/plugins/af_psql_trgm/init.php
index d12f50713..09a9fcd27 100644
--- a/plugins/af_psql_trgm/init.php
+++ b/plugins/af_psql_trgm/init.php
@@ -23,8 +23,6 @@ class Af_Psql_Trgm extends Plugin {
 		$enable_globally = checkbox_to_sql_bool($_POST["enable_globally"] ?? "");
 
 		if ($similarity < 0) $similarity = 0;
-		if ($similarity > 1) $similarity = 1;
-
 		if ($min_title_length < 0) $min_title_length = 0;
 
 		$similarity = sprintf("%.2f", $similarity);
@@ -65,24 +63,43 @@ class Af_Psql_Trgm extends Plugin {
 
 			print "<p>$title</p>";
 
-			$sth = $this->pdo->prepare("SELECT ttrss_entries.id AS id,
-				feed_id,
-				ttrss_entries.title AS title,
-				updated, link,
-				ttrss_feeds.title AS feed_title,
-				SIMILARITY(ttrss_entries.title, ?) AS sm
-			FROM
-				ttrss_entries, ttrss_user_entries LEFT JOIN ttrss_feeds ON (ttrss_feeds.id = feed_id)
-			WHERE
-				ttrss_entries.id = ref_id AND
-				ttrss_user_entries.owner_uid = ? AND
-				ttrss_entries.id != ? AND
-				date_entered >= NOW() - INTERVAL '2 weeks'
-			ORDER BY
-				sm DESC, date_entered DESC
-			LIMIT 10");
-
-			$sth->execute([$title, $owner_uid, $id]);
+			if (Config::get(Config::DB_TYPE) == "pgsql") {
+				$sth = $this->pdo->prepare("SELECT ttrss_entries.id AS id,
+					feed_id,
+					ttrss_entries.title AS title,
+					updated, link,
+					ttrss_feeds.title AS feed_title,
+					SIMILARITY(ttrss_entries.title, :title) AS sm
+				FROM
+					ttrss_entries, ttrss_user_entries LEFT JOIN ttrss_feeds ON (ttrss_feeds.id = feed_id)
+				WHERE
+					ttrss_entries.id = ref_id AND
+					ttrss_user_entries.owner_uid = :owner_uid AND
+					ttrss_entries.id != :id AND
+					date_entered >= NOW() - INTERVAL '2 weeks'
+				ORDER BY
+					sm DESC, date_entered DESC
+				LIMIT 10");
+			} else {
+				$sth = $this->pdo->prepare("SELECT ttrss_entries.id AS id,
+					feed_id,
+					ttrss_entries.title AS title,
+					updated, link,
+					ttrss_feeds.title AS feed_title,
+					MATCH (ttrss_entries.title) AGAINST (:title) AS sm
+				FROM
+					ttrss_entries, ttrss_user_entries LEFT JOIN ttrss_feeds ON (ttrss_feeds.id = feed_id)
+				WHERE
+					ttrss_entries.id = ref_id AND
+					ttrss_user_entries.owner_uid = :owner_uid AND
+					ttrss_entries.id != :id AND
+					date_entered >= DATE_SUB(NOW(), INTERVAL 2 WEEK)
+				ORDER BY
+					sm DESC, date_entered DESC
+				LIMIT 10");
+			}
+
+			$sth->execute(['title' => $title, "owner_uid" => $owner_uid, "id" => $id]);
 
 			print "<ul class='panel panel-scrollable'>";
 
@@ -140,9 +157,7 @@ class Af_Psql_Trgm extends Plugin {
 			title="<i class='material-icons'>extension</i> <?= __('Mark similar articles as read (af_psql_trgm)') ?>">
 
 			<?php
-			if (Config::get(Config::DB_TYPE) != "pgsql") {
-				print_error("Database type not supported.");
-			} else {
+			if (Config::get(Config::DB_TYPE) == "pgsql") {
 				$res = $this->pdo->query("select 'similarity'::regproc");
 
 				if (!$res || !$res->fetch()) {
@@ -175,7 +190,11 @@ class Af_Psql_Trgm extends Plugin {
 						name="similarity" value="<?= htmlspecialchars($similarity) ?>">
 
 					<div dojoType='dijit.Tooltip' connectId='psql_trgm_similarity' position='below'>
-						<?= __("PostgreSQL trigram extension returns string similarity as a floating point number (0-1). Setting it too low might produce false positives, zero disables checking.") ?>
+						<?php if (Config::get(Config::DB_TYPE) == "pgsql") { ?>
+							<?= __("PostgreSQL trigram extension returns string similarity as a floating point number (0-1). Setting it too low might produce false positives, zero disables checking.") ?>
+						<?php } else { ?>
+							<?= __("Setting this value too low might produce false positives, zero disables checking.") ?>
+						<?php } ?>
 					</div>
 				</fieldset>
 
@@ -264,10 +283,10 @@ class Af_Psql_Trgm extends Plugin {
 
 	function hook_article_filter($article) {
 
-		if (Config::get(Config::DB_TYPE) != "pgsql") return $article;
-
-		$res = $this->pdo->query("select 'similarity'::regproc");
-		if (!$res || !$res->fetch()) return $article;
+		if (Config::get(Config::DB_TYPE) == "pgsql") {
+			$res = $this->pdo->query("select 'similarity'::regproc");
+			if (!$res || !$res->fetch()) return $article;
+		}
 
 		$enable_globally = $this->host->get($this, "enable_globally");
 
@@ -317,12 +336,23 @@ class Af_Psql_Trgm extends Plugin {
 			return $article;
 		} */
 
-		$sth = $this->pdo->prepare("SELECT MAX(SIMILARITY(title, ?)) AS ms
-		  FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND
-		  date_entered >= NOW() - interval '1 day' AND
-		  guid != ? AND
-		  owner_uid = ?");
-		$sth->execute([$title_escaped, $entry_guid, $owner_uid]);
+		if (Config::get(Config::DB_TYPE) == "pgsql") {
+			$sth = $this->pdo->prepare("SELECT MAX(SIMILARITY(title, :title)) AS ms
+				FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND
+				date_entered >= NOW() - interval '1 day' AND
+				guid != :guid AND
+				owner_uid = :uid");
+		} else {
+			$sth = $this->pdo->prepare("SELECT MATCH(title) AGAINST (:title) AS ms
+				FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND
+					date_entered >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND
+					guid != :guid AND
+					owner_uid = :uid
+					ORDER BY ms DESC
+					LIMIT 1");
+		}
+
+		$sth->execute(['title' => $title_escaped, 'guid' => $entry_guid, 'uid' => $owner_uid]);
 
 		$row = $sth->fetch();
 		$similarity_result = $row['ms'];