From 6080cca9ca1de60a5d4a1ef06d4873eca07d9ef2 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Fri, 24 Jan 2020 14:25:31 +0300 Subject: scrap counter cache system; rework counters to sum() booleans instead --- classes/counters.php | 229 ++++++++++++++++++++++++++++----------------------- 1 file changed, 124 insertions(+), 105 deletions(-) (limited to 'classes/counters.php') diff --git a/classes/counters.php b/classes/counters.php index cee901304..29a7812b6 100644 --- a/classes/counters.php +++ b/classes/counters.php @@ -13,62 +13,129 @@ class Counters { } static function getCategoryCounters() { - $ret_arr = array(); + $ret = []; /* Labels category */ $cv = array("id" => -2, "kind" => "cat", "counter" => Feeds::getCategoryUnread(-2)); - array_push($ret_arr, $cv); + array_push($ret, $cv); $pdo = DB::pdo(); - $sth = $pdo->prepare("SELECT ttrss_feed_categories.id AS cat_id, value AS unread, - (SELECT COUNT(id) FROM ttrss_feed_categories AS c2 - WHERE c2.parent_cat = ttrss_feed_categories.id) AS num_children - FROM ttrss_feed_categories, ttrss_cat_counters_cache - WHERE ttrss_cat_counters_cache.feed_id = ttrss_feed_categories.id AND - ttrss_cat_counters_cache.owner_uid = ttrss_feed_categories.owner_uid AND - ttrss_feed_categories.owner_uid = ?"); - $sth->execute([$_SESSION['uid']]); + $sth = $pdo->prepare("SELECT fc.id, + SUM(CASE WHEN unread THEN 1 ELSE 0 END) AS count, + SUM(CASE WHEN marked THEN 1 ELSE 0 END) AS count_marked, + (SELECT COUNT(id) FROM ttrss_feed_categories fcc + WHERE fcc.parent_cat = fc.id) AS num_children + FROM ttrss_feed_categories fc, ttrss_feeds f, ttrss_user_entries ue + WHERE f.cat_id = fc.id AND + ue.feed_id = f.id AND + ue.owner_uid = :uid + GROUP BY fc.id + UNION + SELECT 0, + SUM(CASE WHEN unread THEN 1 ELSE 0 END) AS count, + SUM(CASE WHEN marked THEN 1 ELSE 0 END) AS count_marked, + 0 + FROM ttrss_feeds f, ttrss_user_entries ue + WHERE f.cat_id IS NULL AND + ue.feed_id = f.id AND + ue.owner_uid = :uid"); + + $sth->execute(["uid" => $_SESSION['uid']]); while ($line = $sth->fetch()) { - $line["cat_id"] = (int) $line["cat_id"]; - if ($line["num_children"] > 0) { $child_counter = Feeds::getCategoryChildrenUnread($line["cat_id"], $_SESSION["uid"]); } else { $child_counter = 0; } - $cv = array("id" => $line["cat_id"], "kind" => "cat", - "counter" => $line["unread"] + $child_counter); + $cv = [ + "id" => (int)$line["id"], + "kind" => "cat", + "markedcounter" => (int) $line["count_marked"], + "counter" => (int) $line["count"] + $child_counter + ]; - array_push($ret_arr, $cv); + array_push($ret, $cv); } - /* Special case: NULL category doesn't actually exist in the DB */ + array_push($ret, $cv); + + return $ret; + } + + + static function getFeedCounters($active_feed = false) { + + $ret = []; + + $pdo = Db::pdo(); + + $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"); - $cv = array("id" => 0, "kind" => "cat", - "counter" => (int) CCache::find(0, $_SESSION["uid"], true)); + $sth->execute([$_SESSION['uid']]); - array_push($ret_arr, $cv); + while ($line = $sth->fetch()) { + + $id = $line["id"]; + $last_error = htmlspecialchars($line["last_error"]); + $last_updated = make_local_datetime($line['last_updated'], false); - return $ret_arr; + if (Feeds::feedHasIcon($id)) { + $has_img = filemtime(Feeds::getIconFile($id)); + } else { + $has_img = false; + } + + if (date('Y') - date('Y', strtotime($line['last_updated'])) > 2) + $last_updated = ''; + + $cv = [ + "id" => $id, + "updated" => $last_updated, + "counter" => (int) $line["count"], + "markedcounter" => (int) $line["count_marked"], + "has_img" => (int) $has_img + ]; + + if ($last_error) + $cv["error"] = $last_error; + + if ($active_feed && $id == $active_feed) + $cv["title"] = truncate_string($line["title"], 30); + + array_push($ret, $cv); + + } + + return $ret; } static function getGlobalCounters($global_unread = -1) { - $ret_arr = array(); + $ret = []; if ($global_unread == -1) { $global_unread = Feeds::getGlobalUnread(); } - $cv = array("id" => "global-unread", - "counter" => (int) $global_unread); + $cv = [ + "id" => "global-unread", + "counter" => (int) $global_unread + ]; - array_push($ret_arr, $cv); + array_push($ret, $cv); $pdo = Db::pdo(); @@ -79,17 +146,19 @@ class Counters { $subscribed_feeds = $row["fn"]; - $cv = array("id" => "subscribed-feeds", - "counter" => (int) $subscribed_feeds); + $cv = [ + "id" => "subscribed-feeds", + "counter" => (int) $subscribed_feeds + ]; - array_push($ret_arr, $cv); + array_push($ret, $cv); - return $ret_arr; + return $ret; } static function getVirtCounters() { - $ret_arr = array(); + $ret = []; for ($i = 0; $i >= -4; $i--) { @@ -100,117 +169,67 @@ class Counters { else $auxctr = 0; - $cv = array("id" => $i, + $cv = [ + "id" => $i, "counter" => (int) $count, - "auxcounter" => (int) $auxctr); + "auxcounter" => (int) $auxctr + ]; -// if (get_pref('EXTENDED_FEEDLIST')) -// $cv["xmsg"] = getFeedArticles($i)." ".__("total"); - - array_push($ret_arr, $cv); + array_push($ret, $cv); } $feeds = PluginHost::getInstance()->get_feeds(-1); if (is_array($feeds)) { foreach ($feeds as $feed) { - $cv = array("id" => PluginHost::pfeed_to_feed_id($feed['id']), - "counter" => $feed['sender']->get_unread($feed['id'])); + $cv = [ + "id" => PluginHost::pfeed_to_feed_id($feed['id']), + "counter" => $feed['sender']->get_unread($feed['id']) + ]; if (method_exists($feed['sender'], 'get_total')) $cv["auxcounter"] = $feed['sender']->get_total($feed['id']); - array_push($ret_arr, $cv); + array_push($ret, $cv); } } - return $ret_arr; + return $ret; } static function getLabelCounters($descriptions = false) { - $ret_arr = array(); + $ret = []; $pdo = Db::pdo(); - $sth = $pdo->prepare("SELECT id,caption,SUM(CASE WHEN u1.unread = true THEN 1 ELSE 0 END) AS unread, COUNT(u1.unread) AS total + $sth = $pdo->prepare("SELECT id, + caption, + SUM(CASE WHEN u1.unread = true THEN 1 ELSE 0 END) AS unread, + COUNT(u1.unread) AS total FROM ttrss_labels2 LEFT JOIN ttrss_user_labels2 ON (ttrss_labels2.id = label_id) - LEFT JOIN ttrss_user_entries AS u1 ON u1.ref_id = article_id - WHERE ttrss_labels2.owner_uid = :uid AND u1.owner_uid = :uid - GROUP BY ttrss_labels2.id, - ttrss_labels2.caption"); + LEFT JOIN ttrss_user_entries AS u1 ON u1.ref_id = article_id + WHERE ttrss_labels2.owner_uid = :uid AND u1.owner_uid = :uid + GROUP BY ttrss_labels2.id, ttrss_labels2.caption"); $sth->execute([":uid" => $_SESSION['uid']]); while ($line = $sth->fetch()) { $id = Labels::label_to_feed_id($line["id"]); - $cv = array("id" => $id, + $cv = [ + "id" => $id, "counter" => (int) $line["unread"], - "auxcounter" => (int) $line["total"]); + "auxcounter" => (int) $line["total"] + ]; if ($descriptions) $cv["description"] = $line["caption"]; - array_push($ret_arr, $cv); + array_push($ret, $cv); } - return $ret_arr; + return $ret; } - - static function getFeedCounters($active_feed = false) { - - $ret_arr = array(); - - $pdo = Db::pdo(); - - $sth = $pdo->prepare("SELECT ttrss_feeds.id, - ttrss_feeds.title, - ".SUBSTRING_FOR_DATE."(ttrss_feeds.last_updated,1,19) AS last_updated, - last_error, value AS count - FROM ttrss_feeds, ttrss_counters_cache - WHERE ttrss_feeds.owner_uid = ? - AND ttrss_counters_cache.owner_uid = ttrss_feeds.owner_uid - AND ttrss_counters_cache.feed_id = ttrss_feeds.id"); - $sth->execute([$_SESSION['uid']]); - - while ($line = $sth->fetch()) { - - $id = $line["id"]; - $count = $line["count"]; - $last_error = htmlspecialchars($line["last_error"]); - - $last_updated = make_local_datetime($line['last_updated'], false); - - if (Feeds::feedHasIcon($id)) { - $has_img = filemtime(Feeds::getIconFile($id)); - } else { - $has_img = false; - } - - if (date('Y') - date('Y', strtotime($line['last_updated'])) > 2) - $last_updated = ''; - - $cv = array("id" => $id, - "updated" => $last_updated, - "counter" => (int) $count, - "has_img" => (int) $has_img); - - if ($last_error) - $cv["error"] = $last_error; - -// if (get_pref('EXTENDED_FEEDLIST')) -// $cv["xmsg"] = getFeedArticles($id)." ".__("total"); - - if ($active_feed && $id == $active_feed) - $cv["title"] = truncate_string($line["title"], 30); - - array_push($ret_arr, $cv); - - } - - return $ret_arr; - } - -} \ No newline at end of file +} -- cgit v1.2.3