more fts stuff for simple index

2 files changed, 52 insertions, 42 deletions

diff --git a/include/functions2.php b/include/functions2.php
index 206673b7d..a9bb49df8 100644
--- a/include/functions2.php
+++ b/include/functions2.php
@@ -282,17 +282,10 @@
 
 	function search_to_sql($search) {
 
-		/*if (DB_TYPE == "pgsql") {
-			$search_escaped = db_escape_string($search);
-
-			return array("(to_tsvector('english', SUBSTR(ttrss_entries.title, 0, 200) || ' ' || SUBSTR(content, 0, 800))
-				@@ to_tsquery('$search_escaped'))", explode(" ", $search));
-		}*/
-
 		$keywords = str_getcsv($search, " ");
 		$query_keywords = array();
 		$search_words = array();
-		$search_query_leftover = "";
+		$search_query_leftover = array();
 
 		foreach ($keywords as $k) {
 			if (strpos($k, "-") === 0) {
@@ -390,23 +383,27 @@
 
 					array_push($query_keywords, "(".SUBSTRING_FOR_DATE."(updated,1,LENGTH('$k')) $not = '$k')");
 				} else {
-					$search_query_leftover .= $k . " ";
 
-					if (!$not) array_push($search_words, $k);
+					if (DB_TYPE == "pgsql") {
+						$k = mb_strtolower($k);
+						array_push($search_query_leftover, $not ? "!$k" : $k);
+					} else {
+						array_push($query_keywords, "(UPPER(ttrss_entries.title) $not LIKE UPPER('%$k%')
+							OR UPPER(ttrss_entries.content) $not LIKE UPPER('%$k%'))");
+					}
 
-					/*array_push($query_keywords, "(UPPER(ttrss_entries.title) $not LIKE UPPER('%$k%')
-						OR UPPER(ttrss_entries.content) $not LIKE UPPER('%$k%'))");
-					if (!$not) array_push($search_words, $k);*/
+					if (!$not) array_push($search_words, $k);
 				}
 			}
 		}
 
-		if ($search_query_leftover) {
-			$search_query_leftover = db_escape_string($search_query_leftover);
+		if (count($search_query_leftover) > 0) {
+			$search_query_leftover = db_escape_string(implode(" & ", $search_query_leftover));
 
-			array_push($query_keywords,
-				"(to_tsvector('simple', SUBSTR(ttrss_entries.title, 0, 200) || ' ' || SUBSTR(content, 0, 800))
-				@@ to_tsquery('$search_query_leftover'))");
+			if (DB_TYPE == "pgsql") {
+				array_push($query_keywords,
+					"(tsvector_combined @@ '$search_query_leftover'::tsquery)");
+			}
 
 		}
 
@@ -713,29 +710,30 @@
 				if ($feed == -3)
 					$first_id_query_strategy_part = "true";
 
-				// if previous topmost article id changed that means our current pagination is no longer valid
-				$query = "SELECT DISTINCT
-						ttrss_feeds.title,
-						date_entered,
-						guid,
-						ttrss_entries.id,
-						ttrss_entries.title,
-						updated,
-						score,
-						marked,
-						published,
-						last_marked,
-						last_published
-					FROM
-						$from_qpart
-					WHERE
-					$feed_check_qpart
-					ttrss_user_entries.ref_id = ttrss_entries.id AND
-					ttrss_user_entries.owner_uid = '$owner_uid' AND
-					$search_query_part
-					$start_ts_query_part
-					$since_id_part
-					$first_id_query_strategy_part ORDER BY $order_by LIMIT 1";
+				if (!$search) {
+					// if previous topmost article id changed that means our current pagination is no longer valid
+					$query = "SELECT DISTINCT
+							ttrss_feeds.title,
+							date_entered,
+							guid,
+							ttrss_entries.id,
+							ttrss_entries.title,
+							updated,
+							score,
+							marked,
+							published,
+							last_marked,
+							last_published
+						FROM
+							$from_qpart
+						WHERE
+						$feed_check_qpart
+						ttrss_user_entries.ref_id = ttrss_entries.id AND
+						ttrss_user_entries.owner_uid = '$owner_uid' AND
+						$search_query_part
+						$start_ts_query_part
+						$since_id_part
+						$first_id_query_strategy_part ORDER BY $order_by LIMIT 1";
 
 					if ($_REQUEST["debug"]) {
 						print $query;
@@ -743,12 +741,13 @@
 
 					$result = db_query($query);
 					if ($result && db_num_rows($result) > 0) {
-						$first_id = (int) db_fetch_result($result, 0, "id");
+						$first_id = (int)db_fetch_result($result, 0, "id");
 
 						if ($offset > 0 && $first_id && $check_first_id && $first_id != $check_first_id) {
 							return array(-1, $feed_title, $feed_site_url, $last_error, $last_updated, $search_words, $first_id);
 						}
 					}
+				}
 
 				$query = "SELECT DISTINCT
 						date_entered,
diff --git a/include/rssfuncs.php b/include/rssfuncs.php
index 6eb4e6d98..5ebddf9ef 100644
--- a/include/rssfuncs.php
+++ b/include/rssfuncs.php
@@ -984,11 +984,22 @@
 
 					_debug("RID: $entry_ref_id, IID: $entry_int_id", $debug_enabled);
 
+					if (DB_TYPE == "pgsql") {
+						$tsvector_combined = db_escape_string(mb_substr($entry_title . ' ' . strip_tags($entry_content),
+							0, 1000000));
+
+						$tsvector_qpart = "tsvector_combined = to_tsvector('simple', '$tsvector_combined'),";
+
+					} else {
+						$tsvector_qpart = "";
+					}
+
 					db_query("UPDATE ttrss_entries
 						SET title = '$entry_title',
 							content = '$entry_content',
 							content_hash = '$entry_current_hash',
 							updated = '$entry_timestamp_fmt',
+							$tsvector_qpart
 							num_comments = '$num_comments',
 							plugin_data = '$entry_plugin_data',
 							author = '$entry_author',