static function api_get_feeds($cat_id, $unread_only, $limit, $offset, $include_nested = false)
{
$feeds = array();
/* Labels */
if ($cat_id == -4 || $cat_id == -2) {
$counters = getLabelCounters(true);
foreach (array_values($counters) as $cv) {
$unread = $cv["counter"];
if ($unread || !$unread_only) {
$row = array("id" => (int) $cv["id"], "title" => $cv["description"], "unread" => $cv["counter"], "cat_id" => -2);
array_push($feeds, $row);
}
}
}
/* Virtual feeds */
if ($cat_id == -4 || $cat_id == -1) {
foreach (array(-1, -2, -3, -4, -6, 0) as $i) {
$unread = getFeedUnread($i);
if ($unread || !$unread_only) {
$title = getFeedTitle($i);
$row = array("id" => $i, "title" => $title, "unread" => $unread, "cat_id" => -1);
array_push($feeds, $row);
}
}
}
/* Child cats */
if ($include_nested && $cat_id) {
$result = db_query("SELECT\n\t\t\t\t\tid, title FROM ttrss_feed_categories\n\t\t\t\t\tWHERE parent_cat = '{$cat_id}' AND owner_uid = " . $_SESSION["uid"] . " ORDER BY id, title");
while ($line = db_fetch_assoc($result)) {
$unread = getFeedUnread($line["id"], true) + getCategoryChildrenUnread($line["id"]);
if ($unread || !$unread_only) {
$row = array("id" => (int) $line["id"], "title" => $line["title"], "unread" => $unread, "is_cat" => true);
array_push($feeds, $row);
}
}
}
/* Real feeds */
if ($limit) {
$limit_qpart = "LIMIT {$limit} OFFSET {$offset}";
} else {
$limit_qpart = "";
}
if ($cat_id == -4 || $cat_id == -3) {
$result = db_query("SELECT\n\t\t\t\t\tid, feed_url, cat_id, title, order_id, " . SUBSTRING_FOR_DATE . "(last_updated,1,19) AS last_updated\n\t\t\t\t\t\tFROM ttrss_feeds WHERE owner_uid = " . $_SESSION["uid"] . " ORDER BY cat_id, title " . $limit_qpart);
} else {
if ($cat_id) {
$cat_qpart = "cat_id = '{$cat_id}'";
} else {
$cat_qpart = "cat_id IS NULL";
}
$result = db_query("SELECT\n\t\t\t\t\tid, feed_url, cat_id, title, order_id, " . SUBSTRING_FOR_DATE . "(last_updated,1,19) AS last_updated\n\t\t\t\t\t\tFROM ttrss_feeds WHERE\n\t\t\t\t\t\t{$cat_qpart} AND owner_uid = " . $_SESSION["uid"] . " ORDER BY cat_id, title " . $limit_qpart);
}
while ($line = db_fetch_assoc($result)) {
$unread = getFeedUnread($line["id"]);
$has_icon = feed_has_icon($line['id']);
if ($unread || !$unread_only) {
$row = array("feed_url" => $line["feed_url"], "title" => $line["title"], "id" => (int) $line["id"], "unread" => (int) $unread, "has_icon" => $has_icon, "cat_id" => (int) $line["cat_id"], "last_updated" => (int) strtotime($line["last_updated"]), "order_id" => (int) $line["order_id"]);
array_push($feeds, $row);
}
}
return $feeds;
}
function getCategoryChildrenUnread($cat, $owner_uid = false)
{
if (!$owner_uid) {
$owner_uid = $_SESSION["uid"];
}
$result = db_query("SELECT id FROM ttrss_feed_categories WHERE parent_cat = '{$cat}'\n\t\t\t\tAND owner_uid = {$owner_uid}");
$unread = 0;
while ($line = db_fetch_assoc($result)) {
$unread += getCategoryUnread($line["id"], $owner_uid);
$unread += getCategoryChildrenUnread($line["id"], $owner_uid);
}
return $unread;
}
function queryFeedHeadlines($link, $feed, $limit, $view_mode, $cat_view, $search, $search_mode, $match_on, $override_order = false, $offset = 0, $owner_uid = 0, $filter = false, $since_id = 0, $include_children = false, $ignore_vfeed_group = false)
{
if (!$owner_uid) {
$owner_uid = $_SESSION["uid"];
}
$ext_tables_part = "";
if ($search) {
if (SPHINX_ENABLED) {
$ids = join(",", @sphinx_search($search, 0, 500));
if ($ids) {
$search_query_part = "ref_id IN ({$ids}) AND ";
} else {
$search_query_part = "ref_id = -1 AND ";
}
} else {
$search_query_part = search_to_sql($link, $search, $match_on);
$search_query_part .= " AND ";
}
} else {
$search_query_part = "";
}
if ($filter) {
if (DB_TYPE == "pgsql") {
$query_strategy_part .= " AND updated > NOW() - INTERVAL '14 days' ";
} else {
$query_strategy_part .= " AND updated > DATE_SUB(NOW(), INTERVAL 14 DAY) ";
}
$override_order = "updated DESC";
$filter_query_part = filter_to_sql($link, $filter, $owner_uid);
// Try to check if SQL regexp implementation chokes on a valid regexp
$result = db_query($link, "SELECT true AS true_val FROM ttrss_entries,\n\t\t\t\t\tttrss_user_entries, ttrss_feeds, ttrss_feed_categories\n\t\t\t\t\tWHERE {$filter_query_part} LIMIT 1", false);
if ($result) {
$test = db_fetch_result($result, 0, "true_val");
if (!$test) {
$filter_query_part = "false AND";
} else {
$filter_query_part .= " AND";
}
} else {
$filter_query_part = "false AND";
}
} else {
$filter_query_part = "";
}
if ($since_id) {
$since_id_part = "ttrss_entries.id > {$since_id} AND ";
} else {
$since_id_part = "";
}
$view_query_part = "";
if ($view_mode == "adaptive" || $view_query_part == "noscores") {
if ($search) {
$view_query_part = " ";
} else {
if ($feed != -1) {
$unread = getFeedUnread($link, $feed, $cat_view);
if ($cat_view && $feed > 0 && $include_children) {
$unread += getCategoryChildrenUnread($link, $feed);
}
if ($unread > 0) {
$view_query_part = " unread = true AND ";
}
}
}
}
if ($view_mode == "marked") {
$view_query_part = " marked = true AND ";
}
if ($view_mode == "published") {
$view_query_part = " published = true AND ";
}
if ($view_mode == "unread") {
$view_query_part = " unread = true AND ";
}
if ($view_mode == "updated") {
$view_query_part = " (last_read is null and unread = false) AND ";
}
if ($limit > 0) {
$limit_query_part = "LIMIT " . $limit;
}
$allow_archived = false;
$vfeed_query_part = "";
// override query strategy and enable feed display when searching globally
if ($search && $search_mode == "all_feeds") {
$query_strategy_part = "true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
/* tags */
} else {
if (!is_numeric($feed)) {
$query_strategy_part = "true";
$vfeed_query_part = "(SELECT title FROM ttrss_feeds WHERE\n\t\t\t\t\tid = feed_id) as feed_title,";
} else {
if ($search && $search_mode == "this_cat") {
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
if ($feed > 0) {
if ($include_children) {
$subcats = getChildCategories($link, $feed, $owner_uid);
array_push($subcats, $feed);
$cats_qpart = join(",", $subcats);
} else {
$cats_qpart = $feed;
}
$query_strategy_part = "ttrss_feeds.cat_id IN ({$cats_qpart})";
} else {
$query_strategy_part = "ttrss_feeds.cat_id IS NULL";
}
} else {
if ($feed > 0) {
if ($cat_view) {
if ($feed > 0) {
if ($include_children) {
# sub-cats
$subcats = getChildCategories($link, $feed, $owner_uid);
array_push($subcats, $feed);
$query_strategy_part = "cat_id IN (" . implode(",", $subcats) . ")";
} else {
$query_strategy_part = "cat_id = '{$feed}'";
}
} else {
$query_strategy_part = "cat_id IS NULL";
}
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
$query_strategy_part = "feed_id = '{$feed}'";
}
} else {
if ($feed == 0 && !$cat_view) {
// archive virtual feed
$query_strategy_part = "feed_id IS NULL";
$allow_archived = true;
} else {
if ($feed == 0 && $cat_view) {
// uncategorized
$query_strategy_part = "cat_id IS NULL AND feed_id IS NOT NULL";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
if ($feed == -1) {
// starred virtual feed
$query_strategy_part = "marked = true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
} else {
if ($feed == -2) {
// published virtual feed OR labels category
if (!$cat_view) {
$query_strategy_part = "published = true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
if (!$override_order) {
$override_order = "last_read DESC, updated DESC";
}
} else {
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$ext_tables_part = ",ttrss_labels2,ttrss_user_labels2";
$query_strategy_part = "ttrss_labels2.id = ttrss_user_labels2.label_id AND\n\t\t\t\t\t\tttrss_user_labels2.article_id = ref_id";
}
} else {
if ($feed == -6) {
// recently read
$query_strategy_part = "unread = false AND last_read IS NOT NULL";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
if (!$override_order) {
$override_order = "last_read DESC";
}
} else {
if ($feed == -3) {
// fresh virtual feed
$query_strategy_part = "unread = true AND score >= 0";
$intl = get_pref($link, "FRESH_ARTICLE_MAX_AGE", $owner_uid);
if (DB_TYPE == "pgsql") {
$query_strategy_part .= " AND updated > NOW() - INTERVAL '{$intl} hour' ";
} else {
$query_strategy_part .= " AND updated > DATE_SUB(NOW(), INTERVAL {$intl} HOUR) ";
}
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
if ($feed == -4) {
// all articles virtual feed
$query_strategy_part = "true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
if ($feed <= -10) {
// labels
$label_id = -$feed - 11;
$query_strategy_part = "label_id = '{$label_id}' AND\n\t\t\t\t\tttrss_labels2.id = ttrss_user_labels2.label_id AND\n\t\t\t\t\tttrss_user_labels2.article_id = ref_id";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$ext_tables_part = ",ttrss_labels2,ttrss_user_labels2";
$allow_archived = true;
} else {
$query_strategy_part = "true";
}
}
}
}
}
}
}
}
}
}
}
}
if (get_pref($link, "SORT_HEADLINES_BY_FEED_DATE", $owner_uid)) {
$date_sort_field = "updated";
} else {
$date_sort_field = "date_entered";
}
if (get_pref($link, 'REVERSE_HEADLINES', $owner_uid)) {
$order_by = "{$date_sort_field}";
} else {
$order_by = "{$date_sort_field} DESC";
}
if ($view_mode != "noscores") {
$order_by = "score DESC, {$order_by}";
}
if ($override_order) {
$order_by = $override_order;
}
$feed_title = "";
if ($search) {
$feed_title = T_sprintf("Search results: %s", $search);
} else {
if ($cat_view) {
$feed_title = getCategoryTitle($link, $feed);
} else {
if (is_numeric($feed) && $feed > 0) {
$result = db_query($link, "SELECT title,site_url,last_error\n\t\t\t\t\t\t\tFROM ttrss_feeds WHERE id = '{$feed}' AND owner_uid = {$owner_uid}");
$feed_title = db_fetch_result($result, 0, "title");
$feed_site_url = db_fetch_result($result, 0, "site_url");
$last_error = db_fetch_result($result, 0, "last_error");
} else {
$feed_title = getFeedTitle($link, $feed);
}
}
}
$content_query_part = "content as content_preview, cached_content, ";
if (is_numeric($feed)) {
if ($feed >= 0) {
$feed_kind = "Feeds";
} else {
$feed_kind = "Labels";
}
if ($limit_query_part) {
$offset_query_part = "OFFSET {$offset}";
}
// proper override_order applied above
if ($vfeed_query_part && !$ignore_vfeed_group && get_pref($link, 'VFEED_GROUP_BY_FEED', $owner_uid)) {
if (!$override_order) {
$order_by = "ttrss_feeds.title, {$order_by}";
} else {
$order_by = "ttrss_feeds.title, {$override_order}";
}
}
if (!$allow_archived) {
$from_qpart = "ttrss_entries,ttrss_user_entries,ttrss_feeds{$ext_tables_part}";
$feed_check_qpart = "ttrss_user_entries.feed_id = ttrss_feeds.id AND";
} else {
$from_qpart = "ttrss_entries{$ext_tables_part},ttrss_user_entries\n\t\t\t\t\t\tLEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)";
}
$query = "SELECT DISTINCT\n\t\t\t\t\t\tdate_entered,\n\t\t\t\t\t\tguid,\n\t\t\t\t\t\tttrss_entries.id,ttrss_entries.title,\n\t\t\t\t\t\tupdated,\n\t\t\t\t\t\tlabel_cache,\n\t\t\t\t\t\ttag_cache,\n\t\t\t\t\t\talways_display_enclosures,\n\t\t\t\t\t\tsite_url,\n\t\t\t\t\t\tnote,\n\t\t\t\t\t\tnum_comments,\n\t\t\t\t\t\tcomments,\n\t\t\t\t\t\tint_id,\n\t\t\t\t\t\tunread,feed_id,marked,published,link,last_read,orig_feed_id,\n\t\t\t\t\t\t" . SUBSTRING_FOR_DATE . "(last_read,1,19) as last_read_noms,\n\t\t\t\t\t\t{$vfeed_query_part}\n\t\t\t\t\t\t{$content_query_part}\n\t\t\t\t\t\t" . SUBSTRING_FOR_DATE . "(updated,1,19) as updated_noms,\n\t\t\t\t\t\tauthor,score\n\t\t\t\t\tFROM\n\t\t\t\t\t\t{$from_qpart}\n\t\t\t\t\tWHERE\n\t\t\t\t\t{$feed_check_qpart}\n\t\t\t\t\tttrss_user_entries.ref_id = ttrss_entries.id AND\n\t\t\t\t\tttrss_user_entries.owner_uid = '{$owner_uid}' AND\n\t\t\t\t\t{$search_query_part}\n\t\t\t\t\t{$filter_query_part}\n\t\t\t\t\t{$view_query_part}\n\t\t\t\t\t{$since_id_part}\n\t\t\t\t\t{$query_strategy_part} ORDER BY {$order_by}\n\t\t\t\t\t{$limit_query_part} {$offset_query_part}";
if ($_REQUEST["debug"]) {
print $query;
}
$result = db_query($link, $query);
} else {
// browsing by tag
$select_qpart = "SELECT DISTINCT " . "date_entered," . "guid," . "note," . "ttrss_entries.id as id," . "title," . "updated," . "unread," . "feed_id," . "orig_feed_id," . "marked," . "num_comments, " . "comments, " . "tag_cache," . "label_cache," . "link," . "last_read," . SUBSTRING_FOR_DATE . "(last_read,1,19) as last_read_noms," . $since_id_part . $vfeed_query_part . $content_query_part . SUBSTRING_FOR_DATE . "(updated,1,19) as updated_noms," . "score ";
$feed_kind = "Tags";
$all_tags = explode(",", $feed);
if ($search_mode == 'any') {
$tag_sql = "tag_name in (" . implode(", ", array_map("db_quote", $all_tags)) . ")";
$from_qpart = " FROM ttrss_entries,ttrss_user_entries,ttrss_tags ";
$where_qpart = " WHERE " . "ref_id = ttrss_entries.id AND " . "ttrss_user_entries.owner_uid = {$owner_uid} AND " . "post_int_id = int_id AND {$tag_sql} AND " . $view_query_part . $search_query_part . $query_strategy_part . " ORDER BY {$order_by} " . $limit_query_part;
} else {
$i = 1;
$sub_selects = array();
$sub_ands = array();
foreach ($all_tags as $term) {
array_push($sub_selects, "(SELECT post_int_id from ttrss_tags WHERE tag_name = " . db_quote($term) . " AND owner_uid = {$owner_uid}) as A{$i}");
$i++;
}
if ($i > 2) {
$x = 1;
$y = 2;
do {
array_push($sub_ands, "A{$x}.post_int_id = A{$y}.post_int_id");
$x++;
$y++;
} while ($y < $i);
}
array_push($sub_ands, "A1.post_int_id = ttrss_user_entries.int_id and ttrss_user_entries.owner_uid = {$owner_uid}");
array_push($sub_ands, "ttrss_user_entries.ref_id = ttrss_entries.id");
$from_qpart = " FROM " . implode(", ", $sub_selects) . ", ttrss_user_entries, ttrss_entries";
$where_qpart = " WHERE " . implode(" AND ", $sub_ands);
}
// error_log("TAG SQL: " . $tag_sql);
// $tag_sql = "tag_name = '$feed'"; DEFAULT way
// error_log("[". $select_qpart . "][" . $from_qpart . "][" .$where_qpart . "]");
$result = db_query($link, $select_qpart . $from_qpart . $where_qpart);
}
return array($result, $feed_title, $feed_site_url, $last_error);
}
function getCategories()
{
$unread_only = (bool) db_escape_string($_REQUEST["unread_only"]);
$enable_nested = (bool) db_escape_string($_REQUEST["enable_nested"]);
// TODO do not return empty categories, return Uncategorized and standard virtual cats
if ($enable_nested) {
$nested_qpart = "parent_cat IS NULL";
} else {
$nested_qpart = "true";
}
$result = db_query($this->link, "SELECT\n\t\t\t\tid, title, order_id FROM ttrss_feed_categories\n\t\t\tWHERE {$nested_qpart} AND owner_uid = " . $_SESSION["uid"]);
$cats = array();
while ($line = db_fetch_assoc($result)) {
$unread = getFeedUnread($this->link, $line["id"], true);
if ($enable_nested) {
$unread += getCategoryChildrenUnread($this->link, $line["id"]);
}
if ($unread || !$unread_only) {
array_push($cats, array("id" => $line["id"], "title" => $line["title"], "unread" => $unread, "order_id" => (int) $line["order_id"]));
}
}
foreach (array(-2, -1, 0) as $cat_id) {
$unread = getFeedUnread($this->link, $cat_id, true);
if ($unread || !$unread_only) {
array_push($cats, array("id" => $cat_id, "title" => getCategoryTitle($this->link, $cat_id), "unread" => $unread));
}
}
print $this->wrap(self::STATUS_OK, $cats);
}
function queryFeedHeadlines($params)
{
$feed = $params["feed"];
$limit = isset($params["limit"]) ? $params["limit"] : 30;
$view_mode = $params["view_mode"];
$cat_view = isset($params["cat_view"]) ? $params["cat_view"] : false;
$search = isset($params["search"]) ? $params["search"] : false;
$search_language = isset($params["search_language"]) ? $params["search_language"] : "";
$override_order = isset($params["override_order"]) ? $params["override_order"] : false;
$offset = isset($params["offset"]) ? $params["offset"] : 0;
$owner_uid = isset($params["owner_uid"]) ? $params["owner_uid"] : $_SESSION["uid"];
$since_id = isset($params["since_id"]) ? $params["since_id"] : 0;
$include_children = isset($params["include_children"]) ? $params["include_children"] : false;
$ignore_vfeed_group = isset($params["ignore_vfeed_group"]) ? $params["ignore_vfeed_group"] : false;
$override_strategy = isset($params["override_strategy"]) ? $params["override_strategy"] : false;
$override_vfeed = isset($params["override_vfeed"]) ? $params["override_vfeed"] : false;
$start_ts = isset($params["start_ts"]) ? $params["start_ts"] : false;
$check_first_id = isset($params["check_first_id"]) ? $params["check_first_id"] : false;
$api_request = isset($params["api_request"]) ? $params["api_request"] : false;
$ext_tables_part = "";
$query_strategy_part = "";
$search_words = array();
if ($search) {
foreach (PluginHost::getInstance()->get_hooks(PluginHost::HOOK_SEARCH) as $plugin) {
list($search_query_part, $search_words) = $plugin->hook_search($search);
break;
}
// fall back in case of no plugins
if (!$search_query_part) {
list($search_query_part, $search_words) = search_to_sql($search, $search_language);
}
$search_query_part .= " AND ";
} else {
$search_query_part = "";
}
if ($since_id) {
$since_id_part = "ttrss_entries.id > {$since_id} AND ";
} else {
$since_id_part = "";
}
$view_query_part = "";
$disable_offsets = false;
if ($view_mode == "adaptive") {
if ($search) {
$view_query_part = " ";
} else {
if ($feed != -1) {
$unread = getFeedUnread($feed, $cat_view);
if ($cat_view && $feed > 0 && $include_children) {
$unread += getCategoryChildrenUnread($feed);
}
if ($unread > 0) {
$view_query_part = " unread = true AND ";
$disable_offsets = !$api_request && get_pref("CDM_AUTO_CATCHUP") && get_pref("CDM_EXPANDED");
}
}
}
}
if ($view_mode == "marked") {
$view_query_part = " marked = true AND ";
}
if ($view_mode == "has_note") {
$view_query_part = " (note IS NOT NULL AND note != '') AND ";
}
if ($view_mode == "published") {
$view_query_part = " published = true AND ";
}
if ($view_mode == "unread" && $feed != -6) {
$view_query_part = " unread = true AND ";
$disable_offsets = !$api_request && get_pref("CDM_AUTO_CATCHUP") && get_pref("CDM_EXPANDED");
}
if ($limit > 0) {
$limit_query_part = "LIMIT " . $limit;
}
$allow_archived = false;
$vfeed_query_part = "";
/* tags */
if (!is_numeric($feed)) {
$query_strategy_part = "true";
$vfeed_query_part = "(SELECT title FROM ttrss_feeds WHERE\n\t\t\t\t\tid = feed_id) as feed_title,";
} else {
if ($feed > 0) {
if ($cat_view) {
if ($feed > 0) {
if ($include_children) {
# sub-cats
$subcats = getChildCategories($feed, $owner_uid);
array_push($subcats, $feed);
$query_strategy_part = "cat_id IN (" . implode(",", $subcats) . ")";
} else {
$query_strategy_part = "cat_id = '{$feed}'";
}
} else {
$query_strategy_part = "cat_id IS NULL";
}
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
$query_strategy_part = "feed_id = '{$feed}'";
}
} else {
if ($feed == 0 && !$cat_view) {
// archive virtual feed
$query_strategy_part = "feed_id IS NULL";
$allow_archived = true;
} else {
if ($feed == 0 && $cat_view) {
// uncategorized
$query_strategy_part = "cat_id IS NULL AND feed_id IS NOT NULL";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
if ($feed == -1) {
// starred virtual feed
$query_strategy_part = "marked = true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
if (!$override_order) {
$override_order = "last_marked DESC, date_entered DESC, updated DESC";
}
} else {
if ($feed == -2) {
// published virtual feed OR labels category
if (!$cat_view) {
$query_strategy_part = "published = true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
if (!$override_order) {
$override_order = "last_published DESC, date_entered DESC, updated DESC";
}
} else {
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$ext_tables_part = "ttrss_labels2,ttrss_user_labels2,";
$query_strategy_part = "ttrss_labels2.id = ttrss_user_labels2.label_id AND\n\t\t\t\t\t\tttrss_user_labels2.article_id = ref_id";
}
} else {
if ($feed == -6) {
// recently read
$query_strategy_part = "unread = false AND last_read IS NOT NULL";
if (DB_TYPE == "pgsql") {
$query_strategy_part .= " AND date_entered > NOW() - INTERVAL '1 DAY' ";
} else {
$query_strategy_part .= " AND date_entered > DATE_SUB(NOW(), INTERVAL 1 DAY) ";
}
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
$ignore_vfeed_group = true;
if (!$override_order) {
$override_order = "last_read DESC";
}
} else {
if ($feed == -3) {
// fresh virtual feed
$query_strategy_part = "unread = true AND score >= 0";
$intl = get_pref("FRESH_ARTICLE_MAX_AGE", $owner_uid);
if (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) ";
}
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
if ($feed == -4) {
// all articles virtual feed
$allow_archived = true;
$query_strategy_part = "true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} else {
if ($feed <= LABEL_BASE_INDEX) {
// labels
$label_id = feed_to_label_id($feed);
$query_strategy_part = "label_id = '{$label_id}' AND\n\t\t\t\t\tttrss_labels2.id = ttrss_user_labels2.label_id AND\n\t\t\t\t\tttrss_user_labels2.article_id = ref_id";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$ext_tables_part = "ttrss_labels2,ttrss_user_labels2,";
$allow_archived = true;
} else {
$query_strategy_part = "true";
}
}
}
}
}
}
}
}
}
}
$order_by = "score DESC, date_entered DESC, updated DESC";
if ($override_order) {
$order_by = $override_order;
}
if ($override_strategy) {
$query_strategy_part = $override_strategy;
}
if ($override_vfeed) {
$vfeed_query_part = $override_vfeed;
}
$feed_title = "";
if ($search) {
$feed_title = T_sprintf("Search results: %s", $search);
} else {
if ($cat_view) {
$feed_title = getCategoryTitle($feed);
} else {
if (is_numeric($feed) && $feed > 0) {
$result = db_query("SELECT title,site_url,last_error,last_updated\n\t\t\t\t\t\t\tFROM ttrss_feeds WHERE id = '{$feed}' AND owner_uid = {$owner_uid}");
$feed_title = db_fetch_result($result, 0, "title");
$feed_site_url = db_fetch_result($result, 0, "site_url");
$last_error = db_fetch_result($result, 0, "last_error");
$last_updated = db_fetch_result($result, 0, "last_updated");
} else {
$feed_title = getFeedTitle($feed);
}
}
}
$content_query_part = "content, ";
if ($limit_query_part) {
$offset_query_part = "OFFSET {$offset}";
} else {
$offset_query_part = "";
}
if (is_numeric($feed)) {
// proper override_order applied above
if ($vfeed_query_part && !$ignore_vfeed_group && get_pref('VFEED_GROUP_BY_FEED', $owner_uid)) {
if (!$override_order) {
$order_by = "ttrss_feeds.title, {$order_by}";
} else {
$order_by = "ttrss_feeds.title, {$override_order}";
}
}
if (!$allow_archived) {
$from_qpart = "{$ext_tables_part}ttrss_entries LEFT JOIN ttrss_user_entries ON (ref_id = ttrss_entries.id),ttrss_feeds";
$feed_check_qpart = "ttrss_user_entries.feed_id = ttrss_feeds.id AND";
} else {
$from_qpart = "{$ext_tables_part}ttrss_entries LEFT JOIN ttrss_user_entries ON (ref_id = ttrss_entries.id)\n\t\t\t\t\t\tLEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)";
}
if ($vfeed_query_part) {
$vfeed_query_part .= "favicon_avg_color,";
}
if ($start_ts) {
$start_ts_formatted = date("Y/m/d H:i:s", strtotime($start_ts));
$start_ts_query_part = "date_entered >= '{$start_ts_formatted}' AND";
} else {
$start_ts_query_part = "";
}
$first_id = 0;
$first_id_query_strategy_part = $query_strategy_part;
if ($feed == -3) {
$first_id_query_strategy_part = "true";
}
if (DB_TYPE == "pgsql") {
$sanity_interval_qpart = "date_entered >= NOW() - INTERVAL '1 hour' AND";
} else {
$sanity_interval_qpart = "date_entered >= DATE_SUB(NOW(), INTERVAL 1 hour) AND";
}
if (!$search && !$disable_offsets) {
// if previous topmost article id changed that means our current pagination is no longer valid
$query = "SELECT DISTINCT\n\t\t\t\t\t\t\tttrss_feeds.title,\n\t\t\t\t\t\t\tdate_entered,\n\t\t\t\t\t\t\tguid,\n\t\t\t\t\t\t\tttrss_entries.id,\n\t\t\t\t\t\t\tttrss_entries.title,\n\t\t\t\t\t\t\tupdated,\n\t\t\t\t\t\t\tscore,\n\t\t\t\t\t\t\tmarked,\n\t\t\t\t\t\t\tpublished,\n\t\t\t\t\t\t\tlast_marked,\n\t\t\t\t\t\t\tlast_published,\n\t\t\t\t\t\t\tlast_read\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t{$from_qpart}\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t{$feed_check_qpart}\n\t\t\t\t\t\tttrss_user_entries.owner_uid = '{$owner_uid}' AND\n\t\t\t\t\t\t{$search_query_part}\n\t\t\t\t\t\t{$start_ts_query_part}\n\t\t\t\t\t\t{$since_id_part}\n\t\t\t\t\t\t{$sanity_interval_qpart}\n\t\t\t\t\t\t{$first_id_query_strategy_part} ORDER BY {$order_by} LIMIT 1";
if ($_REQUEST["debug"]) {
print $query;
}
$result = db_query($query);
if ($result && db_num_rows($result) > 0) {
$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);
}
}
}
if ($disable_offsets) {
$offset_query_part = "";
}
$query = "SELECT DISTINCT\n\t\t\t\t\t\tdate_entered,\n\t\t\t\t\t\tguid,\n\t\t\t\t\t\tttrss_entries.id,ttrss_entries.title,\n\t\t\t\t\t\tupdated,\n\t\t\t\t\t\tlabel_cache,\n\t\t\t\t\t\ttag_cache,\n\t\t\t\t\t\talways_display_enclosures,\n\t\t\t\t\t\tsite_url,\n\t\t\t\t\t\tnote,\n\t\t\t\t\t\tnum_comments,\n\t\t\t\t\t\tcomments,\n\t\t\t\t\t\tint_id,\n\t\t\t\t\t\tuuid,\n\t\t\t\t\t\tlang,\n\t\t\t\t\t\thide_images,\n\t\t\t\t\t\tunread,feed_id,marked,published,link,last_read,orig_feed_id,\n\t\t\t\t\t\tlast_marked, last_published,\n\t\t\t\t\t\t{$vfeed_query_part}\n\t\t\t\t\t\t{$content_query_part}\n\t\t\t\t\t\tauthor,score\n\t\t\t\t\tFROM\n\t\t\t\t\t\t{$from_qpart}\n\t\t\t\t\tWHERE\n\t\t\t\t\t{$feed_check_qpart}\n\t\t\t\t\tttrss_user_entries.owner_uid = '{$owner_uid}' AND\n\t\t\t\t\t{$search_query_part}\n\t\t\t\t\t{$start_ts_query_part}\n\t\t\t\t\t{$view_query_part}\n\t\t\t\t\t{$since_id_part}\n\t\t\t\t\t{$query_strategy_part} ORDER BY {$order_by}\n\t\t\t\t\t{$limit_query_part} {$offset_query_part}";
if ($_REQUEST["debug"]) {
print $query;
}
$result = db_query($query);
} else {
// browsing by tag
$query = "SELECT DISTINCT\n\t\t\t\t\t\t\tdate_entered,\n\t\t\t\t\t\t\tguid,\n\t\t\t\t\t\t\tnote,\n\t\t\t\t\t\t\tttrss_entries.id as id,\n\t\t\t\t\t\t\ttitle,\n\t\t\t\t\t\t\tupdated,\n\t\t\t\t\t\t\tunread,\n\t\t\t\t\t\t\tfeed_id,\n\t\t\t\t\t\t\torig_feed_id,\n\t\t\t\t\t\t\tmarked,\n\t\t\t\t\t\t\tnum_comments,\n\t\t\t\t\t\t\tcomments,\n\t\t\t\t\t\t\ttag_cache,\n\t\t\t\t\t\t\tlabel_cache,\n\t\t\t\t\t\t\tlink,\n\t\t\t\t\t\t\tlang,\n\t\t\t\t\t\t\tuuid,\n\t\t\t\t\t\t\tlast_read,\n\t\t\t\t\t\t\t(SELECT hide_images FROM ttrss_feeds WHERE id = feed_id) AS hide_images,\n\t\t\t\t\t\t\tlast_marked, last_published,\n\t\t\t\t\t\t\t{$since_id_part}\n\t\t\t\t\t\t\t{$vfeed_query_part}\n\t\t\t\t\t\t\t{$content_query_part}\n\t\t\t\t\t\t\tauthor, score\n\t\t\t\t\t\tFROM ttrss_entries, ttrss_user_entries, ttrss_tags\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tref_id = ttrss_entries.id AND\n\t\t\t\t\t\t\tttrss_user_entries.owner_uid = {$owner_uid} AND\n\t\t\t\t\t\t\tpost_int_id = int_id AND\n\t\t\t\t\t\t\ttag_name = '{$feed}' AND\n\t\t\t\t\t\t\t{$view_query_part}\n\t\t\t\t\t\t\t{$search_query_part}\n\t\t\t\t\t\t\t{$query_strategy_part} ORDER BY {$order_by}\n\t\t\t\t\t\t\t{$limit_query_part} {$offset_query_part}";
if ($_REQUEST["debug"]) {
print $query;
}
$result = db_query($query);
}
return array($result, $feed_title, $feed_site_url, $last_error, $last_updated, $search_words, $first_id);
}
function queryFeedHeadlines($feed, $limit, $view_mode, $cat_view, $search, $search_mode, $override_order = false, $offset = 0, $owner_uid = 0, $filter = false, $since_id = 0, $include_children = false, $ignore_vfeed_group = false, $override_strategy = false, $override_vfeed = false, $start_ts = false)
{
if (!$owner_uid) {
$owner_uid = $_SESSION["uid"];
}
$ext_tables_part = "";
$search_words = array();
$search_query_part = "";
if ($search) {
// adjust $search_query_part
foreach (PluginHost::getInstance()->get_hooks(PluginHost::HOOK_SEARCH) as $plugin) {
list($search_query_part, $search_words) = $plugin->hook_search($search);
break;
}
// fall back in case of no plugins
if (!$search_query_part) {
list($search_query_part, $search_words) = search_to_sql($search);
}
$search_query_part .= " AND ";
}
$filter_query_part = "";
if ($filter) {
// adjust $filter_query_part
if (DB_TYPE == "pgsql") {
$query_strategy_part .= " AND updated > NOW() - INTERVAL '14 days' ";
} else {
$query_strategy_part .= " AND updated > DATE_SUB(NOW(), INTERVAL 14 DAY) ";
}
$override_order = "updated DESC";
$filter_query_part = filter_to_sql($filter, $owner_uid);
// Try to check if SQL regexp implementation chokes on a valid regexp
$result = db_query("SELECT true AS true_val\n FROM ttrss_entries\n JOIN ttrss_user_entries ON ttrss_entries.id = ttrss_user_entries.ref_id\n JOIN ttrss_feeds ON ttrss_feeds.id = ttrss_user_entries.feed_id\n WHERE {$filter_query_part} LIMIT 1", false);
if ($result) {
$test = db_fetch_result($result, 0, "true_val");
if (!$test) {
$filter_query_part = "false AND";
} else {
$filter_query_part .= " AND";
}
} else {
$filter_query_part = "false AND";
}
}
$since_id_part = "";
if ($since_id) {
// adjust $since_id_part
$since_id_part = "ttrss_entries.id > {$since_id} AND ";
}
$view_query_part = "";
if ($view_mode == "adaptive") {
// adjust $view_query_part
if ($search) {
$view_query_part = " ";
} elseif ($feed != -1) {
$unread = getFeedUnread($feed, $cat_view);
if ($cat_view && $feed > 0 && $include_children) {
$unread += getCategoryChildrenUnread($feed);
}
if ($unread > 0) {
$view_query_part = " unread = true AND ";
}
}
} elseif ($view_mode == "marked") {
$view_query_part = " marked = true AND ";
} elseif ($view_mode == "has_note") {
$view_query_part = " (note IS NOT NULL AND note != '') AND ";
} elseif ($view_mode == "published") {
$view_query_part = " published = true AND ";
} elseif ($view_mode == "unread" && $feed != -6) {
$view_query_part = " unread = true AND ";
}
if ($limit > 0) {
$limit_query_part = "LIMIT " . $limit;
}
$allow_archived = false;
$vfeed_query_part = "";
// override query strategy and enable feed display when searching globally
if ($search && $search_mode == "all_feeds") {
$query_strategy_part = "true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
/* tags */
} elseif (!is_numeric($feed)) {
$query_strategy_part = "true";
$vfeed_query_part = "(SELECT title FROM ttrss_feeds WHERE\n id = feed_id) as feed_title,";
} elseif ($search && $search_mode == "this_cat") {
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
if ($feed > 0) {
if ($include_children) {
$subcats = getChildCategories($feed, $owner_uid);
array_push($subcats, $feed);
$cats_qpart = join(",", $subcats);
} else {
$cats_qpart = $feed;
}
$query_strategy_part = "ttrss_feeds.cat_id IN ({$cats_qpart})";
} else {
$query_strategy_part = "ttrss_feeds.cat_id IS NULL";
}
} elseif ($feed > 0) {
if ($cat_view) {
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
if ($include_children) {
# sub-cats
$subcats = getChildCategories($feed, $owner_uid);
array_push($subcats, $feed);
$query_strategy_part = "cat_id IN (" . implode(",", $subcats) . ")";
} else {
$query_strategy_part = "cat_id = '{$feed}'";
}
} else {
$query_strategy_part = "feed_id = '{$feed}'";
}
} elseif ($feed == 0 && !$cat_view) {
// archive virtual feed
$query_strategy_part = "feed_id IS NULL";
$allow_archived = true;
} elseif ($feed == 0 && $cat_view) {
// uncategorized
$query_strategy_part = "cat_id IS NULL AND feed_id IS NOT NULL";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} elseif ($feed == -1) {
// starred virtual feed
$query_strategy_part = "marked = true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
if (!$override_order) {
$override_order = "last_marked DESC, date_entered DESC, updated DESC";
}
} elseif ($feed == -2) {
// published virtual feed OR labels category
if (!$cat_view) {
$query_strategy_part = "published = true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
if (!$override_order) {
$override_order = "last_published DESC, date_entered DESC, updated DESC";
}
} else {
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$ext_tables_part = ",ttrss_labels2,ttrss_user_labels2";
$query_strategy_part = "ttrss_labels2.id = ttrss_user_labels2.label_id AND\n ttrss_user_labels2.article_id = ref_id";
}
} elseif ($feed == -6) {
// recently read
$query_strategy_part = "unread = false AND last_read IS NOT NULL";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true;
$ignore_vfeed_group = true;
if (!$override_order) {
$override_order = "last_read DESC";
}
/*} elseif ($feed == -7) {
// shared
$query_strategy_part = "uuid != ''";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$allow_archived = true; */
} elseif ($feed == -3) {
// fresh virtual feed
$query_strategy_part = "unread = true AND score >= 0";
$intl = get_pref("FRESH_ARTICLE_MAX_AGE", $owner_uid);
if (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) ";
}
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} elseif ($feed == -4) {
// all articles virtual feed
$allow_archived = true;
$query_strategy_part = "true";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
} elseif ($feed <= LABEL_BASE_INDEX) {
// labels
$label_id = feed_to_label_id($feed);
$query_strategy_part = "label_id = '{$label_id}' AND\n ttrss_labels2.id = ttrss_user_labels2.label_id AND\n ttrss_user_labels2.article_id = ref_id";
$vfeed_query_part = "ttrss_feeds.title AS feed_title,";
$ext_tables_part = ",ttrss_labels2,ttrss_user_labels2";
$allow_archived = true;
} else {
$query_strategy_part = "true";
}
$order_by = "score DESC, date_entered DESC, updated DESC";
if ($view_mode == "unread_first") {
$order_by = "unread DESC, {$order_by}";
}
if ($override_order) {
$order_by = $override_order;
}
if ($override_strategy) {
$query_strategy_part = $override_strategy;
}
if ($override_vfeed) {
$vfeed_query_part = $override_vfeed;
}
$feed_title = "";
if ($search) {
$feed_title = T_sprintf("Search results: %s", $search);
} elseif ($cat_view) {
$feed_title = getCategoryTitle($feed);
} elseif (is_numeric($feed) && $feed > 0) {
$result = db_query("SELECT title,site_url,last_error,last_updated\n FROM ttrss_feeds WHERE id = '{$feed}' AND owner_uid = {$owner_uid}");
$feed_title = db_fetch_result($result, 0, "title");
$feed_site_url = db_fetch_result($result, 0, "site_url");
$last_error = db_fetch_result($result, 0, "last_error");
$last_updated = db_fetch_result($result, 0, "last_updated");
} else {
$feed_title = getFeedTitle($feed);
}
$content_query_part = "content, ";
if (is_numeric($feed)) {
if ($feed >= 0) {
$feed_kind = "Feeds";
} else {
$feed_kind = "Labels";
}
if ($limit_query_part) {
$offset_query_part = "OFFSET {$offset}";
}
// proper override_order applied above
if ($vfeed_query_part && !$ignore_vfeed_group && get_pref('VFEED_GROUP_BY_FEED', $owner_uid)) {
if (!$override_order) {
$order_by = "ttrss_feeds.title, {$order_by}";
} else {
$order_by = "ttrss_feeds.title, {$override_order}";
}
}
if (!$allow_archived) {
$from_qpart = "ttrss_entries,ttrss_user_entries,ttrss_feeds{$ext_tables_part}";
$feed_check_qpart = "ttrss_user_entries.feed_id = ttrss_feeds.id AND";
} else {
$from_qpart = "ttrss_entries{$ext_tables_part},ttrss_user_entries\n LEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)";
}
if ($vfeed_query_part) {
$vfeed_query_part .= "favicon_avg_color,";
}
if ($start_ts) {
$start_ts_formatted = date("Y/m/d H:i:s", strtotime($start_ts));
$start_ts_query_part = "date_entered >= '{$start_ts_formatted}' AND";
} else {
$start_ts_query_part = "";
}
$query = "SELECT DISTINCT\n date_entered,\n guid,\n ttrss_entries.id,ttrss_entries.title,\n updated,\n label_cache,\n tag_cache,\n always_display_enclosures,\n site_url,\n note,\n num_comments,\n comments,\n int_id,\n uuid,\n lang,\n hide_images,\n unread,feed_id,marked,published,link,last_read,orig_feed_id,\n last_marked, last_published,\n {$vfeed_query_part}\n {$content_query_part}\n author,score\n FROM\n {$from_qpart}\n WHERE\n {$feed_check_qpart}\n ttrss_user_entries.ref_id = ttrss_entries.id AND\n ttrss_user_entries.owner_uid = '{$owner_uid}' AND\n {$search_query_part}\n {$start_ts_query_part}\n {$filter_query_part}\n {$view_query_part}\n {$since_id_part}\n {$query_strategy_part} ORDER BY {$order_by}\n {$limit_query_part} {$offset_query_part}";
if ($_REQUEST["debug"]) {
print $query;
}
$result = db_query($query);
} else {
// browsing by tag
$select_qpart = "SELECT DISTINCT " . "date_entered," . "guid," . "note," . "ttrss_entries.id as id," . "title," . "updated," . "unread," . "feed_id," . "orig_feed_id," . "marked," . "num_comments, " . "comments, " . "tag_cache," . "label_cache," . "link," . "lang," . "uuid," . "last_read," . "(SELECT hide_images FROM ttrss_feeds WHERE id = feed_id) AS hide_images," . "last_marked, last_published, " . $since_id_part . $vfeed_query_part . $content_query_part . "score ";
$feed_kind = "Tags";
$all_tags = explode(",", $feed);
if ($search_mode == 'any') {
$tag_sql = "tag_name in (" . implode(", ", array_map("db_quote", $all_tags)) . ")";
$from_qpart = " FROM ttrss_entries,ttrss_user_entries,ttrss_tags ";
$where_qpart = " WHERE " . "ref_id = ttrss_entries.id AND " . "ttrss_user_entries.owner_uid = {$owner_uid} AND " . "post_int_id = int_id AND {$tag_sql} AND " . $view_query_part . $search_query_part . $query_strategy_part . " ORDER BY {$order_by} " . $limit_query_part;
} else {
$i = 1;
$sub_selects = array();
$sub_ands = array();
foreach ($all_tags as $term) {
array_push($sub_selects, "(SELECT post_int_id from ttrss_tags WHERE tag_name = " . db_quote($term) . " AND owner_uid = {$owner_uid}) as A{$i}");
$i++;
}
if ($i > 2) {
$x = 1;
$y = 2;
do {
array_push($sub_ands, "A{$x}.post_int_id = A{$y}.post_int_id");
$x++;
$y++;
} while ($y < $i);
}
array_push($sub_ands, "A1.post_int_id = ttrss_user_entries.int_id and ttrss_user_entries.owner_uid = {$owner_uid}");
array_push($sub_ands, "ttrss_user_entries.ref_id = ttrss_entries.id");
$from_qpart = " FROM " . implode(", ", $sub_selects) . ", ttrss_user_entries, ttrss_entries";
$where_qpart = " WHERE " . implode(" AND ", $sub_ands);
}
// error_log("TAG SQL: " . $tag_sql);
// $tag_sql = "tag_name = '$feed'"; DEFAULT way
// error_log("[". $select_qpart . "][" . $from_qpart . "][" .$where_qpart . "]");
$result = db_query($select_qpart . $from_qpart . $where_qpart);
}
return array($result, $feed_title, $feed_site_url, $last_error, $last_updated, $search_words);
}