Add and use 'Db::now_comparison_qpart()'.

This introduces a helper to build a query part comparing a field against a past datetime (determined by '$now - $some_interval'), eliminating certain boilerplate code.

6 files changed, 50 insertions, 119 deletions

diff --git a/classes/Db.php b/classes/Db.php
index 2d8511258..8e0e7047b 100644
--- a/classes/Db.php
+++ b/classes/Db.php
@@ -96,4 +96,26 @@ class Db {
 		return "RANDOM()";
 	}
 
+	/**
+	 * Helper to build a query part comparing a field against a past datetime (determined by "$now - $some_interval")
+	 * 
+	 * The example below could be read as "last_digest_sent is older than 1 day ago".
+	 * ```php
+	 * Db::past_comparison_qpart('last_digest_sent', '<', 1, 'day');
+	 * ```
+	 *
+	 * @todo validate value of $unit and fail if invalid (or massage if practical)?
+	 * @link https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
+	 * @link https://dev.mysql.com/doc/refman/9.2/en/expressions.html#temporal-intervals
+	 * @param string $field the table field being checked
+	 * @param '<'|'>'|'<='|'>='|'=' $operator the comparison operator
+	 * @param positive-int $quantity the amount of $unit
+	 * @param 'year'|'month'|'week'|'day'|'hour'|'minute'|'second' $unit the unit of time for $quantity (see links for more info)
+	 * @return string the query part string
+	 */
+	static function past_comparison_qpart(string $field, string $operator, int $quantity, string $unit): string {
+		if (Config::get(Config::DB_TYPE) == 'pgsql')
+			return "$field $operator NOW() - INTERVAL '$quantity $unit' ";
+		return "$field $operator DATE_SUB(NOW(), INTERVAL $quantity $unit) ";
+	}
 }
diff --git a/classes/Digest.php b/classes/Digest.php
index 3ce2693f3..05b735510 100644
--- a/classes/Digest.php
+++ b/classes/Digest.php
@@ -7,11 +7,7 @@ class Digest
 
 		Debug::log("Sending digests, batch of max $user_limit users, headline limit = $limit");
 
-		if (Config::get(Config::DB_TYPE) == "pgsql") {
-			$interval_qpart = "last_digest_sent < NOW() - INTERVAL '1 days'";
-		} else /* if (Config::get(Config::DB_TYPE) == "mysql") */ {
-			$interval_qpart = "last_digest_sent < DATE_SUB(NOW(), INTERVAL 1 DAY)";
-		}
+		$interval_qpart = Db::past_comparison_qpart('last_digest_sent', '<', 1, 'day');
 
 		$pdo = Db::pdo();
 
@@ -107,11 +103,7 @@ class Digest
 
 		$affected_ids = array();
 
-		if (Config::get(Config::DB_TYPE) == "pgsql") {
-			$interval_qpart = "ttrss_entries.date_updated > NOW() - INTERVAL '$days days'";
-		} else /* if (Config::get(Config::DB_TYPE) == "mysql") */ {
-			$interval_qpart = "ttrss_entries.date_updated > DATE_SUB(NOW(), INTERVAL $days DAY)";
-		}
+		$interval_qpart = Db::past_comparison_qpart('ttrss_entries.date_updated', '>', $days, 'day');
 
 		$pdo = Db::pdo();
 
diff --git a/classes/Feeds.php b/classes/Feeds.php
index 3eab4e9f8..25df28dab 100644
--- a/classes/Feeds.php
+++ b/classes/Feeds.php
@@ -756,33 +756,10 @@ class Feeds extends Handler_Protected {
 			$search_qpart = "true";
 		}
 
-		// TODO: all this interval stuff needs some generic generator function
-
-		switch ($mode) {
-			case "1day":
-				if (Config::get(Config::DB_TYPE) == "pgsql") {
-					$date_qpart = "date_entered < NOW() - INTERVAL '1 day' ";
-				} else {
-					$date_qpart = "date_entered < DATE_SUB(NOW(), INTERVAL 1 DAY) ";
-				}
-				break;
-			case "1week":
-				if (Config::get(Config::DB_TYPE) == "pgsql") {
-					$date_qpart = "date_entered < NOW() - INTERVAL '1 week' ";
-				} else {
-					$date_qpart = "date_entered < DATE_SUB(NOW(), INTERVAL 1 WEEK) ";
-				}
-				break;
-			case "2week":
-				if (Config::get(Config::DB_TYPE) == "pgsql") {
-					$date_qpart = "date_entered < NOW() - INTERVAL '2 week' ";
-				} else {
-					$date_qpart = "date_entered < DATE_SUB(NOW(), INTERVAL 2 WEEK) ";
-				}
-				break;
-			default:
-				$date_qpart = "true";
-		}
+		$date_qpart = match ($mode) {
+			'1day', '1week', '2week' => Db::past_comparison_qpart('date_entered', '<', (int) substr($mode, 0, 1), substr($mode, 1)),
+			default => 'true',
+		};
 
 		if (is_numeric($feed_id_or_tag_name)) {
 			$feed_id = (int) $feed_id_or_tag_name;
@@ -853,12 +830,7 @@ class Feeds extends Handler_Protected {
 
 					$intl = (int) Prefs::get(Prefs::FRESH_ARTICLE_MAX_AGE, $owner_uid, $profile);
 
-					if (Config::get(Config::DB_TYPE) == "pgsql") {
-						$match_part = "date_entered > NOW() - INTERVAL '$intl hour' ";
-					} else {
-						$match_part = "date_entered > DATE_SUB(NOW(),
-							INTERVAL $intl HOUR) ";
-					}
+					$match_part = Db::past_comparison_qpart('date_entered', '>', $intl, 'hour');
 
 					$sth = $pdo->prepare("UPDATE ttrss_user_entries
 						SET unread = false, last_read = NOW() WHERE ref_id IN
@@ -949,15 +921,8 @@ class Feeds extends Handler_Protected {
 		} else if ($n_feed == Feeds::FEED_PUBLISHED) {
 			$match_part = "published = true";
 		} else if ($n_feed == Feeds::FEED_FRESH) {
-			$match_part = "unread = true AND score >= 0";
-
 			$intl = (int) Prefs::get(Prefs::FRESH_ARTICLE_MAX_AGE, $owner_uid, $profile);
-
-			if (Config::get(Config::DB_TYPE) == "pgsql") {
-				$match_part .= " AND date_entered > NOW() - INTERVAL '$intl hour' ";
-			} else {
-				$match_part .= " AND date_entered > DATE_SUB(NOW(), INTERVAL $intl HOUR) ";
-			}
+			$match_part = 'unread = true AND score >= 0 AND ' . Db::past_comparison_qpart('date_entered', '>', $intl, 'hour');
 
 			$need_entries = true;
 
@@ -1568,13 +1533,8 @@ class Feeds extends Handler_Protected {
 
 			}
 		} else if ($feed == Feeds::FEED_RECENTLY_READ) { // recently read
-			$query_strategy_part = "unread = false AND last_read IS NOT NULL";
-
-			if (Config::get(Config::DB_TYPE) == "pgsql") {
-				$query_strategy_part .= " AND last_read > NOW() - INTERVAL '1 DAY' ";
-			} else {
-				$query_strategy_part .= " AND last_read > DATE_SUB(NOW(), INTERVAL 1 DAY) ";
-			}
+			$query_strategy_part = 'unread = false AND last_read IS NOT NULL AND '
+				. Db::past_comparison_qpart('last_read', '>', 1, 'day');
 
 			$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
 			$allow_archived = true;
@@ -1583,15 +1543,10 @@ class Feeds extends Handler_Protected {
 			if (!$override_order) $override_order = "last_read DESC";
 
 		} else if ($feed == Feeds::FEED_FRESH) { // fresh virtual feed
-			$query_strategy_part = "unread = true AND score >= 0";
-
 			$intl = (int) Prefs::get(Prefs::FRESH_ARTICLE_MAX_AGE, $owner_uid, $profile);
 
-			if (Config::get(Config::DB_TYPE) == "pgsql") {
-				$query_strategy_part .= " AND date_entered > NOW() - INTERVAL '$intl hour' ";
-			} else {
-				$query_strategy_part .= " AND date_entered > DATE_SUB(NOW(), INTERVAL $intl HOUR) ";
-			}
+			$query_strategy_part = 'unread = true AND score >= 0 AND '
+				. Db::past_comparison_qpart('date_entered', '>', $intl, 'hour');
 
 			$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
 		} else if ($feed == Feeds::FEED_ALL) { // all articles virtual feed
@@ -1712,13 +1667,12 @@ class Feeds extends Handler_Protected {
 			if ($feed == Feeds::FEED_FRESH)
 				$first_id_query_strategy_part = "true";
 
-			if (Config::get(Config::DB_TYPE) == "pgsql") {
-				$sanity_interval_qpart = "date_entered >= NOW() - INTERVAL '1 hour' AND";
+			$sanity_interval_qpart = Db::past_comparison_qpart('date_entered', '>=', 1, 'hour') . ' AND ';
 
+			if (Config::get(Config::DB_TYPE) == "pgsql") {
 				$distinct_columns = str_replace("desc", "", strtolower($order_by));
 				$distinct_qpart = "DISTINCT ON (id, $distinct_columns)";
 			} else {
-				$sanity_interval_qpart = "date_entered >= DATE_SUB(NOW(), INTERVAL 1 hour) AND";
 				$distinct_qpart = "DISTINCT"; //fallback
 			}
 
diff --git a/classes/RPC.php b/classes/RPC.php
index 03786beaa..82f85fc11 100644
--- a/classes/RPC.php
+++ b/classes/RPC.php
@@ -281,11 +281,8 @@ class RPC extends Handler_Protected {
 		}
 
 		// Test if feed is currently being updated by another process.
-		if (Config::get(Config::DB_TYPE) == "pgsql") {
-			$updstart_thresh_qpart = "AND (last_update_started IS NULL OR last_update_started < NOW() - INTERVAL '5 minutes')";
-		} else {
-			$updstart_thresh_qpart = "AND (last_update_started IS NULL OR last_update_started < DATE_SUB(NOW(), INTERVAL 5 MINUTE))";
-		}
+		$updstart_thresh_qpart = 'AND (last_update_started IS NULL OR '
+			. Db::past_comparison_qpart('last_update_started', '<', 5, 'minute') . ')';
 
 		$random_qpart = Db::sql_random_function();
 
@@ -528,11 +525,7 @@ class RPC extends Handler_Protected {
 		$data["labels"] = Labels::get_all($_SESSION["uid"]);
 
 		if (Config::get(Config::LOG_DESTINATION) == 'sql' && $_SESSION['access_level'] >= UserHelper::ACCESS_LEVEL_ADMIN) {
-			if (Config::get(Config::DB_TYPE) == 'pgsql') {
-				$log_interval = "created_at > NOW() - interval '1 hour'";
-			} else {
-				$log_interval = "created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)";
-			}
+			$log_interval = Db::past_comparison_qpart('created_at', '>', 1, 'hour');
 
 			$sth = $pdo->prepare("SELECT COUNT(id) AS cid
 				FROM ttrss_error_log
diff --git a/classes/RSSUtils.php b/classes/RSSUtils.php
index b01732586..d014b629e 100644
--- a/classes/RSSUtils.php
+++ b/classes/RSSUtils.php
@@ -103,13 +103,8 @@ class RSSUtils {
 		if (!Config::get(Config::SINGLE_USER_MODE) && Config::get(Config::DAEMON_UPDATE_LOGIN_LIMIT) > 0) {
 			$login_limit = (int) Config::get(Config::DAEMON_UPDATE_LOGIN_LIMIT);
 
-			if (Config::get(Config::DB_TYPE) == "pgsql") {
-				$login_thresh_qpart = "AND last_login >= NOW() - INTERVAL '$login_limit days'";
-				$not_logged_in_users_query = "last_login < NOW() - INTERVAL '$login_limit days'";
-			} else {
-				$login_thresh_qpart = "AND last_login >= DATE_SUB(NOW(), INTERVAL $login_limit DAY)";
-				$not_logged_in_users_query = "last_login < DATE_SUB(NOW(), INTERVAL $login_limit DAY)";
-			}
+			$login_thresh_qpart = 'AND ' . Db::past_comparison_qpart('last_login', '>=', $login_limit, 'day');
+			$not_logged_in_users_query = Db::past_comparison_qpart('last_login', '<', $login_limit, 'day');
 
 			$not_logged_in_users = ORM::for_table('ttrss_users')
 				->where_raw($not_logged_in_users_query)
@@ -157,11 +152,9 @@ class RSSUtils {
 		}
 
 		// Test if feed is currently being updated by another process.
-		if (Config::get(Config::DB_TYPE) == "pgsql") {
-			$updstart_thresh_qpart = "AND (last_update_started IS NULL OR last_update_started < NOW() - INTERVAL '10 minutes')";
-		} else {
-			$updstart_thresh_qpart = "AND (last_update_started IS NULL OR last_update_started < DATE_SUB(NOW(), INTERVAL 10 MINUTE))";
-		}
+		// TODO: Update RPC::updaterandomfeed_real() to also use 10 minutes?
+		$updstart_thresh_qpart = 'AND (last_update_started IS NULL OR '
+			. Db::past_comparison_qpart('last_update_started', '<', 10, 'minute') . ')';
 
 		$query_limit = $limit ? sprintf("LIMIT %d", $limit) : "";
 
@@ -395,11 +388,7 @@ class RSSUtils {
 		/** @var DiskCache $cache */
 		$cache = DiskCache::instance('feeds');
 
-		if (Config::get(Config::DB_TYPE) == "pgsql") {
-			$favicon_interval_qpart = "favicon_last_checked < NOW() - INTERVAL '12 hour'";
-		} else {
-			$favicon_interval_qpart = "favicon_last_checked < DATE_SUB(NOW(), INTERVAL 12 HOUR)";
-		}
+		$favicon_interval_qpart = Db::past_comparison_qpart('favicon_last_checked', '<', 12, 'hour');
 
 		$feed_obj = ORM::for_table('ttrss_feeds')
 				->select_expr("ttrss_feeds.*,
@@ -1462,16 +1451,9 @@ class RSSUtils {
 
 	static function expire_error_log(): void {
 		Debug::log("Removing old error log entries...");
-
 		$pdo = Db::pdo();
-
-		if (Config::get(Config::DB_TYPE) == "pgsql") {
-			$pdo->query("DELETE FROM ttrss_error_log
-				WHERE created_at < NOW() - INTERVAL '7 days'");
-		} else {
-			$pdo->query("DELETE FROM ttrss_error_log
-				WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)");
-		}
+		$pdo->query('DELETE FROM ttrss_error_log
+			WHERE ' . Db::past_comparison_qpart('created_at', '<', 7, 'day'));
 	}
 
 	/**
@@ -1716,13 +1698,8 @@ class RSSUtils {
 
 			$pdo->beginTransaction();
 
-			$days = Config::get(Config::DAEMON_UNSUCCESSFUL_DAYS_LIMIT);
-
-			if (Config::get(Config::DB_TYPE) == "pgsql") {
-				$interval_query = "last_successful_update < NOW() - INTERVAL '$days days' AND last_updated > NOW() - INTERVAL '1 days'";
-			} else /* if (Config::get(Config::DB_TYPE) == "mysql") */ {
-				$interval_query = "last_successful_update < DATE_SUB(NOW(), INTERVAL $days DAY) AND last_updated > DATE_SUB(NOW(), INTERVAL 1 DAY)";
-			}
+			$interval_query = Db::past_comparison_qpart('last_successful_update', '<', Config::get(Config::DAEMON_UNSUCCESSFUL_DAYS_LIMIT), 'day')
+				. ' AND ' . Db::past_comparison_qpart('last_updated', '>', 1, 'day');
 
 			$sth = $pdo->prepare("SELECT id, title, owner_uid
 				FROM ttrss_feeds
diff --git a/update.php b/update.php
index d2a1287d1..471e323c8 100755
--- a/update.php
+++ b/update.php
@@ -33,14 +33,7 @@
 	}
 
 	function cleanup_tags(int $days = 14, int $limit = 1000): int {
-
-		$days = (int) $days;
-
-		if (Config::get(Config::DB_TYPE) == "pgsql") {
-			$interval_query = "e.date_updated < NOW() - INTERVAL '$days days'";
-		} else /*if (Config::get(Config::DB_TYPE) == "mysql") */ {
-			$interval_query = "e.date_updated < DATE_SUB(NOW(), INTERVAL $days DAY)";
-		}
+		$interval_query = Db::past_comparison_qpart('e.date_updated', '<', $days, 'day');
 
 		$tags_deleted = 0;
 		$limit_part = 500;