Merge branch 'feature/orm-and-misc' into 'master'

Use ORM in more places, deprecate const SUBSTRING_FOR_DATE, some minor fixes

See merge request tt-rss/tt-rss!135

9 files changed, 77 insertions, 122 deletions

diff --git a/classes/API.php b/classes/API.php
index 2a554e888..e1d4de214 100644
--- a/classes/API.php
+++ b/classes/API.php
@@ -310,7 +310,7 @@ class API extends Handler {
 				->select_many('e.id', 'e.guid', 'e.title', 'e.link', 'e.author', 'e.content', 'e.lang', 'e.comments',
 					'ue.feed_id', 'ue.int_id', 'ue.marked', 'ue.unread', 'ue.published', 'ue.score', 'ue.note')
 				->select_many_expr([
-					'updated' => SUBSTRING_FOR_DATE.'(updated,1,16)',
+					'updated' => 'SUBSTRING_FOR_DATE(updated,1,16)',
 					'feed_title' => '(SELECT title FROM ttrss_feeds WHERE id = ue.feed_id)',
 					'site_url' => '(SELECT site_url FROM ttrss_feeds WHERE id = ue.feed_id)',
 					'hide_images' => '(SELECT hide_images FROM ttrss_feeds WHERE id = feed_id)',
@@ -619,7 +619,7 @@ class API extends Handler {
 			/* API only: -3 (Feeds::CATEGORY_ALL_EXCEPT_VIRTUAL) All feeds, excluding virtual feeds (e.g. Labels and such) */
 			$feeds_obj = ORM::for_table('ttrss_feeds')
 				->select_many('id', 'feed_url', 'cat_id', 'title', 'order_id', 'last_error', 'update_interval')
-				->select_expr(SUBSTRING_FOR_DATE.'(last_updated,1,19)', 'last_updated')
+				->select_expr('SUBSTRING_FOR_DATE(last_updated,1,19)', 'last_updated')
 				->where('owner_uid', $_SESSION['uid'])
 				->order_by_asc('order_id')
 				->order_by_asc('title');
@@ -670,7 +670,7 @@ class API extends Handler {
 
 				$feed = ORM::for_table('ttrss_feeds')
 					->select_many('id', 'cache_images')
-					->select_expr(SUBSTRING_FOR_DATE.'(last_updated,1,19)', 'last_updated')
+					->select_expr('SUBSTRING_FOR_DATE(last_updated,1,19)', 'last_updated')
 					->find_one($feed_id);
 
 				if ($feed) {
diff --git a/classes/Counters.php b/classes/Counters.php
index e20657947..613210ecc 100644
--- a/classes/Counters.php
+++ b/classes/Counters.php
@@ -147,63 +147,34 @@ class Counters {
 	private static function get_feeds(?array $feed_ids = null): array {
 		$ret = [];
 
-		$pdo = Db::pdo();
-
-		if (is_array($feed_ids)) {
-			if (count($feed_ids) == 0)
-				return [];
-
-			$feed_ids_qmarks = arr_qmarks($feed_ids);
-
-			$sth = $pdo->prepare("SELECT f.id,
-					f.title,
-					".SUBSTRING_FOR_DATE."(f.last_updated,1,19) AS last_updated,
-					f.last_error,
-					SUM(CASE WHEN unread THEN 1 ELSE 0 END) AS count,
-					SUM(CASE WHEN marked THEN 1 ELSE 0 END) AS count_marked
-				FROM ttrss_feeds f, ttrss_user_entries ue
-				WHERE f.id = ue.feed_id AND ue.owner_uid = ? AND f.id IN ($feed_ids_qmarks)
-				GROUP BY f.id");
-
-			$sth->execute([$_SESSION['uid'], ...$feed_ids]);
-		} else {
-			$sth = $pdo->prepare("SELECT f.id,
-					f.title,
-					".SUBSTRING_FOR_DATE."(f.last_updated,1,19) AS last_updated,
-					f.last_error,
-					SUM(CASE WHEN unread THEN 1 ELSE 0 END) AS count,
-					SUM(CASE WHEN marked THEN 1 ELSE 0 END) AS count_marked
-				FROM ttrss_feeds f, ttrss_user_entries ue
-				WHERE f.id = ue.feed_id AND ue.owner_uid = :uid
-				GROUP BY f.id");
-
-			$sth->execute(["uid" => $_SESSION['uid']]);
-		}
-
-		while ($line = $sth->fetch()) {
-
-			$id = $line["id"];
-			$last_updated = TimeHelper::make_local_datetime($line['last_updated']);
-
-			if (Feeds::_has_icon($id)) {
-				$ts = filemtime(Feeds::_get_icon_file($id));
-			} else {
-				$ts = 0;
-			}
-
-			$cv = [
-				"id" => $id,
-				"updated" => $last_updated,
-				"counter" => (int) $line["count"],
-				"markedcounter" => (int) $line["count_marked"],
-				"ts" => (int) $ts
+		if (is_array($feed_ids) && count($feed_ids) === 0)
+			return $ret;
+
+		$feeds = ORM::for_table('ttrss_feeds')
+			->table_alias('f')
+			->select_many('f.id', 'f.title', 'f.last_error')
+			->select_many_expr([
+				'count' => 'SUM(CASE WHEN ue.unread THEN 1 ELSE 0 END)',
+				'count_marked' => 'SUM(CASE WHEN ue.marked THEN 1 ELSE 0 END)',
+				'last_updated' => 'SUBSTRING_FOR_DATE(f.last_updated,1,19)',
+			])
+			->join('ttrss_user_entries', [ 'ue.feed_id', '=', 'f.id'], 'ue')
+			->where('ue.owner_uid', $_SESSION['uid'])
+			->group_by('f.id');
+
+		if (is_array($feed_ids))
+			$feeds->where_in('f.id', $feed_ids);
+
+		foreach ($feeds->find_many() as $feed) {
+			$ret[] = [
+				'id' => $feed->id,
+				'title' => truncate_string($feed->title, 30),
+				'error' => $feed->last_error,
+				'updated' => TimeHelper::make_local_datetime($feed->last_updated),
+				'counter' => (int) $feed->count,
+				'markedcounter' => (int) $feed->count_marked,
+				'ts' => Feeds::_has_icon($feed->id) ? (int) filemtime(Feeds::_get_icon_file($feed->id)) : 0,
 			];
-
-			$cv["error"] = $line["last_error"];
-			$cv["title"] = truncate_string($line["title"], 30);
-
-			array_push($ret, $cv);
-
 		}
 
 		return $ret;
diff --git a/classes/Digest.php b/classes/Digest.php
index f0ffafcbf..36389b09f 100644
--- a/classes/Digest.php
+++ b/classes/Digest.php
@@ -110,7 +110,7 @@ class Digest
 				link,
 				score,
 				content,
-				".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated
+				SUBSTRING_FOR_DATE(last_updated,1,19) AS last_updated
 			FROM
 				ttrss_user_entries,ttrss_entries,ttrss_feeds
 			LEFT JOIN
diff --git a/classes/Feeds.php b/classes/Feeds.php
index 7640185c1..7b0fc60cc 100644
--- a/classes/Feeds.php
+++ b/classes/Feeds.php
@@ -400,8 +400,8 @@ class Feeds extends Handler_Protected {
 
 					$reply['content'] .= "<p><span class=\"text-muted\">";
 
-					$sth = $this->pdo->prepare("SELECT " . SUBSTRING_FOR_DATE . "(MAX(last_updated), 1, 19) AS last_updated FROM ttrss_feeds
-                        WHERE owner_uid = ?");
+					$sth = $this->pdo->prepare("SELECT SUBSTRING_FOR_DATE(MAX(last_updated), 1, 19) AS last_updated
+						FROM ttrss_feeds WHERE owner_uid = ?");
 					$sth->execute([$_SESSION['uid']]);
 					$row = $sth->fetch();
 
@@ -2239,7 +2239,7 @@ class Feeds extends Handler_Protected {
 						$orig_ts = strtotime(substr($k, 1));
 						$k = date("Y-m-d", TimeHelper::convert_timestamp($orig_ts, $user_tz_string, 'UTC'));
 
-						array_push($query_keywords, "(".SUBSTRING_FOR_DATE."(updated,1,LENGTH(".$pdo->quote($k).")) $not = ".$pdo->quote($k).")");
+						array_push($query_keywords, "(SUBSTRING_FOR_DATE(updated,1,LENGTH(".$pdo->quote($k).")) $not = ".$pdo->quote($k).")");
 					} else {
 						// treat as leftover text
 
diff --git a/classes/Pref_Feeds.php b/classes/Pref_Feeds.php
index 75ae053bd..d27b7d750 100644
--- a/classes/Pref_Feeds.php
+++ b/classes/Pref_Feeds.php
@@ -74,7 +74,7 @@ class Pref_Feeds extends Handler_Protected {
 		$feeds_obj = ORM::for_table('ttrss_feeds')
 			->table_alias('f')
 			->select_many('id', 'title', 'last_error', 'update_interval')
-			->select_expr(SUBSTRING_FOR_DATE.'(last_updated,1,19)', 'last_updated')
+			->select_expr('SUBSTRING_FOR_DATE(last_updated,1,19)', 'last_updated')
 			->left_outer_join('ttrss_user_entries', [ 'ue.feed_id', '=', 'f.id'], 'ue')
 			->select_expr('COUNT(ue.int_id) AS num_articles')
 			->where(['cat_id' => $cat_id, 'owner_uid' => $_SESSION['uid']])
@@ -261,7 +261,7 @@ class Pref_Feeds extends Handler_Protected {
 			$feeds_obj = ORM::for_table('ttrss_feeds')
 				->table_alias('f')
 				->select_many('id', 'title', 'last_error', 'update_interval')
-				->select_expr(SUBSTRING_FOR_DATE.'(last_updated,1,19)', 'last_updated')
+				->select_expr('SUBSTRING_FOR_DATE(last_updated,1,19)', 'last_updated')
 				->left_outer_join('ttrss_user_entries', [ 'ue.feed_id', '=', 'f.id'], 'ue')
 				->select_expr('COUNT(ue.int_id) AS num_articles')
 				->where('owner_uid', $_SESSION['uid'])
@@ -305,7 +305,7 @@ class Pref_Feeds extends Handler_Protected {
 			$feeds_obj = ORM::for_table('ttrss_feeds')
 				->table_alias('f')
 				->select_many('id', 'title', 'last_error', 'update_interval')
-				->select_expr(SUBSTRING_FOR_DATE.'(last_updated,1,19)', 'last_updated')
+				->select_expr('SUBSTRING_FOR_DATE(last_updated,1,19)', 'last_updated')
 				->left_outer_join('ttrss_user_entries', [ 'ue.feed_id', '=', 'f.id'], 'ue')
 				->select_expr('COUNT(ue.int_id) AS num_articles')
 				->where('owner_uid', $_SESSION['uid'])
diff --git a/classes/Pref_Users.php b/classes/Pref_Users.php
index ef44dad30..6f220c441 100644
--- a/classes/Pref_Users.php
+++ b/classes/Pref_Users.php
@@ -6,7 +6,7 @@ class Pref_Users extends Handler_Administrative {
 
 		function edit(): void {
 			$user = ORM::for_table('ttrss_users')
-				->select_expr("id,login,access_level,email,full_name,otp_enabled")
+				->select_many('id', 'login', 'access_level', 'email', 'full_name', 'otp_enabled')
 				->find_one((int)$_REQUEST["id"])
 				->as_array();
 
@@ -23,30 +23,25 @@ class Pref_Users extends Handler_Administrative {
 		function userdetails(): void {
 			$id = (int) clean($_REQUEST["id"]);
 
-			$sth = $this->pdo->prepare("SELECT login,
-				".SUBSTRING_FOR_DATE."(last_login,1,16) AS last_login,
-				access_level,
-				(SELECT COUNT(int_id) FROM ttrss_user_entries
-					WHERE owner_uid = id) AS stored_articles,
-				".SUBSTRING_FOR_DATE."(created,1,16) AS created
-				FROM ttrss_users
-				WHERE id = ?");
-			$sth->execute([$id]);
-
-			if ($row = $sth->fetch()) {
-
-				$last_login = TimeHelper::make_local_datetime($row['last_login']);
-
-				$created = TimeHelper::make_local_datetime($row['created']);
-
-				$stored_articles = $row["stored_articles"];
+			$user = ORM::for_table('ttrss_users')
+				->table_alias('u')
+				->select_many('u.login', 'u.access_level')
+				->select_many_expr([
+					'created' => 'SUBSTRING_FOR_DATE(u.created,1,16)',
+					'last_login' => 'SUBSTRING_FOR_DATE(u.last_login,1,16)',
+					'stored_articles' => '(SELECT COUNT(ue.int_id) FROM ttrss_user_entries ue WHERE ue.owner_uid = u.id)',
+				])
+				->find_one($id);
 
-				$sth = $this->pdo->prepare("SELECT COUNT(id) as num_feeds FROM ttrss_feeds
-					WHERE owner_uid = ?");
-				$sth->execute([$id]);
-				$row = $sth->fetch();
+			if ($user) {
+				$created = TimeHelper::make_local_datetime($user->created);
+				$last_login = TimeHelper::make_local_datetime($user->last_login);
 
-				$num_feeds = $row["num_feeds"];
+				$user_owned_feeds = ORM::for_table('ttrss_feeds')
+					->select_many('id', 'title', 'site_url')
+					->where('owner_uid', $id)
+					->order_by_expr('LOWER(title)')
+					->find_many();
 
 				?>
 
@@ -62,31 +57,25 @@ class Pref_Users extends Handler_Administrative {
 
 				<fieldset>
 					<label><?= __('Subscribed feeds') ?>:</label>
-					<?= $num_feeds ?>
+					<?= count($user_owned_feeds) ?>
 				</fieldset>
 
 				<fieldset>
 					<label><?= __('Stored articles') ?>:</label>
-					<?= $stored_articles ?>
+					<?= $user->stored_articles ?>
 				</fieldset>
 
-				<?php
-					$sth = $this->pdo->prepare("SELECT id,title,site_url FROM ttrss_feeds
-						WHERE owner_uid = ? ORDER BY title");
-					$sth->execute([$id]);
-				?>
-
 				<ul class="panel panel-scrollable list list-unstyled">
-					<?php while ($row = $sth->fetch()) { ?>
+					<?php foreach ($user_owned_feeds as $feed) { ?>
 						<li>
 							<?php
-								$icon_url = Feeds::_get_icon_url($row['id'], 'images/blank_icon.gif');
+								$icon_url = Feeds::_get_icon_url($feed->id, 'images/blank_icon.gif');
 							?>
 
 							<img class="icon" src="<?= htmlspecialchars($icon_url) ?>">
 
-							<a target="_blank" href="<?= htmlspecialchars($row["site_url"]) ?>">
-								<?= htmlspecialchars($row["title"]) ?>
+							<a target="_blank" href="<?= htmlspecialchars($feed->site_url) ?>">
+								<?= htmlspecialchars($feed->title) ?>
 							</a>
 						</li>
 					<?php } ?>
@@ -134,14 +123,9 @@ class Pref_Users extends Handler_Administrative {
 
 			foreach ($ids as $id) {
 				if ($id != $_SESSION["uid"] && $id != 1) {
-					$sth = $this->pdo->prepare("DELETE FROM ttrss_tags WHERE owner_uid = ?");
-					$sth->execute([$id]);
-
-					$sth = $this->pdo->prepare("DELETE FROM ttrss_feeds WHERE owner_uid = ?");
-					$sth->execute([$id]);
-
-					$sth = $this->pdo->prepare("DELETE FROM ttrss_users WHERE id = ?");
-					$sth->execute([$id]);
+					ORM::for_table('ttrss_tags')->where('owner_uid', $id)->delete_many();
+					ORM::for_table('ttrss_feeds')->where('owner_uid', $id)->delete_many();
+					ORM::for_table('ttrss_users')->where('id', $id)->delete_many();
 				}
 			}
 		}
diff --git a/classes/RSSUtils.php b/classes/RSSUtils.php
index 9403cebdd..d367d91dc 100644
--- a/classes/RSSUtils.php
+++ b/classes/RSSUtils.php
@@ -344,11 +344,13 @@ class RSSUtils {
 		$cache = DiskCache::instance('feeds');
 
 		$feed_obj = ORM::for_table('ttrss_feeds')
-				->select_expr("ttrss_feeds.*,
-					".SUBSTRING_FOR_DATE."(last_unconditional, 1, 19) AS last_unconditional,
-					(favicon_is_custom IS NOT TRUE AND
-						(favicon_last_checked IS NULL OR favicon_last_checked < NOW() - INTERVAL '12 hour')) AS favicon_needs_check")
-				->find_one($feed);
+			->select('ttrss_feeds.*')
+			->select_many_expr([
+				'last_unconditional' => 'SUBSTRING_FOR_DATE(last_unconditional, 1, 19)',
+				'favicon_needs_check' => "(favicon_is_custom IS NOT TRUE AND
+					(favicon_last_checked IS NULL OR favicon_last_checked < NOW() - INTERVAL '12 hour'))",
+			])
+			->find_one($feed);
 
 		if ($feed_obj) {
 			$feed_obj->last_update_started = Db::NOW();
@@ -677,7 +679,7 @@ class RSSUtils {
 
 			$items = $rss->get_items();
 
-			if (!is_array($items)) {
+			if (count($items) === 0) {
 				Debug::log("no articles found.", Debug::LOG_VERBOSE);
 
 				$feed_obj->set([
diff --git a/include/functions.php b/include/functions.php
index c573042a2..f100e3046 100644
--- a/include/functions.php
+++ b/include/functions.php
@@ -11,11 +11,8 @@
 	if (function_exists("mb_internal_encoding")) mb_internal_encoding("UTF-8");
 
 	date_default_timezone_set('UTC');
-	if (defined('E_DEPRECATED')) {
-		error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
-	} else {
-		error_reporting(E_ALL & ~E_NOTICE);
-	}
+
+	error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
 
 	ini_set('display_errors', "false");
 	ini_set('display_startup_errors', "false");
@@ -26,7 +23,8 @@
 
 	require_once "autoload.php";
 
-	define('SUBSTRING_FOR_DATE', 'SUBSTRING_FOR_DATE');
+	/** @deprecated use the 'SUBSTRING_FOR_DATE' string directly */
+	const SUBSTRING_FOR_DATE = 'SUBSTRING_FOR_DATE';
 
 	/**
 	 * @deprecated by Prefs::get()
diff --git a/plugins/share/init.php b/plugins/share/init.php
index 8e25dc677..0a2e60dcf 100644
--- a/plugins/share/init.php
+++ b/plugins/share/init.php
@@ -123,7 +123,7 @@ class Share extends Plugin {
 		$pdo = Db::pdo();
 
 		$sth = $pdo->prepare("SELECT id,title,link,content,feed_id,comments,int_id,lang,
-			".SUBSTRING_FOR_DATE."(updated,1,16) as updated,
+			SUBSTRING_FOR_DATE(updated,1,16) as updated,
 			(SELECT site_url FROM ttrss_feeds WHERE id = feed_id) as site_url,
 			(SELECT title FROM ttrss_feeds WHERE id = feed_id) as feed_title,
 			(SELECT hide_images FROM ttrss_feeds WHERE id = feed_id) as hide_images,