More uniform SQL search and PHP search (#8329)

* More uniform SQL search and PHP search
The behaviour depends though on the database.
Improve https://github.com/FreshRSS/FreshRSS/discussions/8265#discussioncomment-15278980

* Try to use transliterator_transliterate function instead

4 files changed, 63 insertions, 10 deletions

diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php
index 9fa950c16..3d8389c87 100644
--- a/app/Models/DatabaseDAO.php
+++ b/app/Models/DatabaseDAO.php
@@ -482,4 +482,33 @@ SQL;
 
 		return true;
 	}
+
+	/**
+	 * Remove accents from characters and lowercase. Relevant for emulating MySQL utf8mb4_unicode_ci collation.
+	 * Example: `Café` becomes `cafe`.
+	 */
+	private static function removeAccentsLower(string $str): string {
+		if (function_exists('transliterator_transliterate')) {
+			// https://unicode-org.github.io/icu/userguide/transforms/general/#overview
+			$transliterated = transliterator_transliterate('NFD; [:Nonspacing Mark:] Remove; NFC; Lower', $str);
+			if ($transliterated !== false) {
+				return $transliterated;
+			}
+		}
+		return strtolower(strtr($str,
+			'ÀÁÂÃÄÅàáâãäåÒÓÔÕÖØòóôõöøÈÉÊËèéêëÇçÌÍÎÏìíîïÙÚÛÜùúûüÿÑñ',
+			'AAAAAAaaaaaaOOOOOOooooooEEEEeeeeCcIIIIiiiiUUUUuuuuyNn'
+		));
+	}
+
+	/**
+	 * PHP emulation of the SQL ILIKE operation of the selected database.
+	 * Note that it depends on the database collation settings and Unicode extensions.
+	 */
+	public static function strilike(string $haystack, string $needle): bool {
+		// Implementation approximating MySQL/MariaDB `LIKE` with `utf8mb4_unicode_ci` collation.
+		$haystack = self::removeAccentsLower($haystack);
+		$needle = self::removeAccentsLower($needle);
+		return str_contains($haystack, $needle);
+	}
 }
diff --git a/app/Models/DatabaseDAOPGSQL.php b/app/Models/DatabaseDAOPGSQL.php
index 3fa70cd88..116f8816e 100644
--- a/app/Models/DatabaseDAOPGSQL.php
+++ b/app/Models/DatabaseDAOPGSQL.php
@@ -99,4 +99,19 @@ SQL;
 		}
 		return $ok;
 	}
+
+	#[\Override]
+	public static function strilike(string $haystack, string $needle): bool {
+		if (function_exists('mb_stripos')) {
+			return mb_stripos($haystack, $needle, 0, 'UTF-8') !== false;
+		}
+		if (function_exists('transliterator_transliterate')) {
+			$haystack_ = transliterator_transliterate('Lower', $haystack);
+			$needle_ = transliterator_transliterate('Lower', $needle);
+			if ($haystack_ !== false && $needle_ !== false) {
+				return str_contains($haystack_, $needle_);
+			}
+		}
+		return stripos($haystack, $needle) !== false;
+	}
 }
diff --git a/app/Models/DatabaseDAOSQLite.php b/app/Models/DatabaseDAOSQLite.php
index 13c73a2d7..9a4842c39 100644
--- a/app/Models/DatabaseDAOSQLite.php
+++ b/app/Models/DatabaseDAOSQLite.php
@@ -99,4 +99,9 @@ class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO {
 		}
 		return $ok;
 	}
+
+	#[\Override]
+	public static function strilike(string $haystack, string $needle): bool {
+		return stripos($haystack, $needle) !== false;
+	}
 }
diff --git a/app/Models/Entry.php b/app/Models/Entry.php
index 9d9f880fd..0fb82a203 100644
--- a/app/Models/Entry.php
+++ b/app/Models/Entry.php
@@ -625,6 +625,10 @@ HTML;
 	}
 
 	public function matches(FreshRSS_BooleanSearch $booleanSearch): bool {
+		static $databaseDao = null;
+		if (!($databaseDao instanceof FreshRSS_DatabaseDAO)) {
+			$databaseDao = FreshRSS_Factory::createDatabaseDAO();
+		}
 		$ok = true;
 		foreach ($booleanSearch->searches() as $filter) {
 			if ($filter instanceof FreshRSS_BooleanSearch) {
@@ -695,7 +699,7 @@ HTML;
 				}
 				if ($ok && $filter->getAuthor() !== null) {
 					foreach ($filter->getAuthor() as $author) {
-						$ok &= stripos(implode(';', $this->authors), $author) !== false;
+						$ok &= $databaseDao::strilike(implode(';', $this->authors), $author);
 					}
 				}
 				if ($ok && $filter->getAuthorRegex() !== null) {
@@ -705,7 +709,7 @@ HTML;
 				}
 				if ($ok && $filter->getNotAuthor() !== null) {
 					foreach ($filter->getNotAuthor() as $author) {
-						$ok &= stripos(implode(';', $this->authors), $author) === false;
+						$ok &= !$databaseDao::strilike(implode(';', $this->authors), $author);
 					}
 				}
 				if ($ok && $filter->getNotAuthorRegex() !== null) {
@@ -715,7 +719,7 @@ HTML;
 				}
 				if ($ok && $filter->getIntitle() !== null) {
 					foreach ($filter->getIntitle() as $title) {
-						$ok &= stripos($this->title, $title) !== false;
+						$ok &= $databaseDao::strilike($this->title, $title);
 					}
 				}
 				if ($ok && $filter->getIntitleRegex() !== null) {
@@ -725,7 +729,7 @@ HTML;
 				}
 				if ($ok && $filter->getNotIntitle() !== null) {
 					foreach ($filter->getNotIntitle() as $title) {
-						$ok &= stripos($this->title, $title) === false;
+						$ok &= !$databaseDao::strilike($this->title, $title);
 					}
 				}
 				if ($ok && $filter->getNotIntitleRegex() !== null) {
@@ -735,7 +739,7 @@ HTML;
 				}
 				if ($ok && $filter->getIntext() !== null) {
 					foreach ($filter->getIntext() as $content) {
-						$ok &= stripos($this->content, $content) !== false;
+						$ok &= $databaseDao::strilike($this->content, $content);
 					}
 				}
 				if ($ok && $filter->getIntextRegex() !== null) {
@@ -745,7 +749,7 @@ HTML;
 				}
 				if ($ok && $filter->getNotIntext() !== null) {
 					foreach ($filter->getNotIntext() as $content) {
-						$ok &= stripos($this->content, $content) === false;
+						$ok &= !$databaseDao::strilike($this->content, $content);
 					}
 				}
 				if ($ok && $filter->getNotIntextRegex() !== null) {
@@ -758,7 +762,7 @@ HTML;
 						$found = false;
 						foreach ($this->tags as $tag1) {
 							$tag1 = ltrim($tag1, '#');
-							if (strcasecmp($tag1, $tag2) === 0) {
+							if ($databaseDao::strilike($tag1, $tag2)) {
 								$found = true;
 								break;
 							}
@@ -784,7 +788,7 @@ HTML;
 						$found = false;
 						foreach ($this->tags as $tag1) {
 							$tag1 = ltrim($tag1, '#');
-							if (strcasecmp($tag1, $tag2) === 0) {
+							if ($databaseDao::strilike($tag1, $tag2)) {
 								$found = true;
 								break;
 							}
@@ -827,12 +831,12 @@ HTML;
 				}
 				if ($ok && $filter->getSearch() !== null) {
 					foreach ($filter->getSearch() as $needle) {
-						$ok &= (stripos($this->title, $needle) !== false || stripos($this->content, $needle) !== false);
+						$ok &= ($databaseDao::strilike($this->title, $needle) || $databaseDao::strilike($this->content, $needle));
 					}
 				}
 				if ($ok && $filter->getNotSearch() !== null) {
 					foreach ($filter->getNotSearch() as $needle) {
-						$ok &= (stripos($this->title, $needle) === false && stripos($this->content, $needle) === false);
+						$ok &= (!$databaseDao::strilike($this->title, $needle) && !$databaseDao::strilike($this->content, $needle));
 					}
 				}
 				if ($ok && $filter->getSearchRegex() !== null) {