function renameLabel() { $caption = db_escape_string($_REQUEST["caption"]); $label_id = feed_to_label_id((int) db_escape_string($_REQUEST["label_id"])); if ($label_id != "" && $caption != "") { $this->dbh->query("UPDATE ttrss_labels2 SET caption = '{$caption}' WHERE id = '{$label_id}' AND owner_uid = " . $_SESSION["uid"]); return array(API::STATUS_OK); } else { return array(API::STATUS_ERR, array("error" => 'INCORRECT_USAGE')); } }
static function remove_feed($id, $owner_uid) { if ($id > 0) { /* save starred articles in Archived feed */ db_query("BEGIN"); /* prepare feed if necessary */ $result = db_query("SELECT feed_url FROM ttrss_feeds WHERE id = {$id}\n\t\t\t\tAND owner_uid = {$owner_uid}"); $feed_url = db_escape_string(db_fetch_result($result, 0, "feed_url")); $result = db_query("SELECT id FROM ttrss_archived_feeds\n\t\t\t\tWHERE feed_url = '{$feed_url}' AND owner_uid = {$owner_uid}"); if (db_num_rows($result) == 0) { $result = db_query("SELECT MAX(id) AS id FROM ttrss_archived_feeds"); $new_feed_id = (int) db_fetch_result($result, 0, "id") + 1; db_query("INSERT INTO ttrss_archived_feeds\n\t\t\t\t\t(id, owner_uid, title, feed_url, site_url)\n\t\t\t\tSELECT {$new_feed_id}, owner_uid, title, feed_url, site_url from ttrss_feeds\n\t\t\t\tWHERE id = '{$id}'"); $archive_id = $new_feed_id; } else { $archive_id = db_fetch_result($result, 0, "id"); } db_query("UPDATE ttrss_user_entries SET feed_id = NULL,\n\t\t\t\torig_feed_id = '{$archive_id}' WHERE feed_id = '{$id}' AND\n\t\t\t\t\tmarked = true AND owner_uid = {$owner_uid}"); /* Remove access key for the feed */ db_query("DELETE FROM ttrss_access_keys WHERE\n\t\t\t\tfeed_id = '{$id}' AND owner_uid = {$owner_uid}"); /* remove the feed */ db_query("DELETE FROM ttrss_feeds\n\t\t\t\t\tWHERE id = '{$id}' AND owner_uid = {$owner_uid}"); db_query("COMMIT"); if (file_exists(ICONS_DIR . "/{$id}.ico")) { unlink(ICONS_DIR . "/{$id}.ico"); } ccache_remove($id, $owner_uid); } else { label_remove(feed_to_label_id($id), $owner_uid); //ccache_remove($id, $owner_uid); don't think labels are cached } }
function setArticleLabel() { $article_ids = array_filter(explode(",", $this->dbh->escape_string($_REQUEST["article_ids"])), is_numeric); $label_id = (int) $this->dbh->escape_string($_REQUEST['label_id']); $assign = (bool) $this->dbh->escape_string($_REQUEST['assign']) == "true"; $label = $this->dbh->escape_string(label_find_caption(feed_to_label_id($label_id), $_SESSION["uid"])); $num_updated = 0; if ($label) { foreach ($article_ids as $id) { if ($assign) { label_add_article($id, $label, $_SESSION["uid"]); } else { label_remove_article($id, $label, $_SESSION["uid"]); } ++$num_updated; } } $this->wrap(self::STATUS_OK, array("status" => "OK", "updated" => $num_updated)); }
function getFeedTitle($id, $cat = false) { if ($cat) { return getCategoryTitle($id); } else { if ($id == -1) { return __("Starred articles"); } else { if ($id == -2) { return __("Published articles"); } else { if ($id == -3) { return __("Fresh articles"); } else { if ($id == -4) { return __("All articles"); } else { if ($id === 0 || $id === "0") { return __("Archived articles"); } else { if ($id == -6) { return __("Recently read"); } else { if ($id < LABEL_BASE_INDEX) { $label_id = feed_to_label_id($id); $result = db_query("SELECT caption FROM ttrss_labels2 WHERE id = '{$label_id}'"); if (db_num_rows($result) == 1) { return db_fetch_result($result, 0, "caption"); } else { return "Unknown label ({$label_id})"; } } else { if (is_numeric($id) && $id > 0) { $result = db_query("SELECT title FROM ttrss_feeds WHERE id = '{$id}'"); if (db_num_rows($result) == 1) { return db_fetch_result($result, 0, "title"); } else { return "Unknown feed ({$id})"; } } else { return $id; } } } } } } } } } }
function view() { $timing_info = microtime(true); $reply = array(); if ($_REQUEST["debug"]) { $timing_info = print_checkpoint("0", $timing_info); } $feed = $this->dbh->escape_string($_REQUEST["feed"]); $method = $this->dbh->escape_string($_REQUEST["m"]); $view_mode = $this->dbh->escape_string($_REQUEST["view_mode"]); $limit = 30; @($cat_view = $_REQUEST["cat"] == "true"); @($next_unread_feed = $this->dbh->escape_string($_REQUEST["nuf"])); @($offset = $this->dbh->escape_string($_REQUEST["skip"])); @($vgroup_last_feed = $this->dbh->escape_string($_REQUEST["vgrlf"])); $order_by = $this->dbh->escape_string($_REQUEST["order_by"]); if (is_numeric($feed)) { $feed = (int) $feed; } /* Feed -5 is a special case: it is used to display auxiliary information * when there's nothing to load - e.g. no stuff in fresh feed */ if ($feed == -5) { print json_encode($this->generate_dashboard_feed()); return; } $result = false; if ($feed < LABEL_BASE_INDEX) { $label_feed = feed_to_label_id($feed); $result = $this->dbh->query("SELECT id FROM ttrss_labels2 WHERE\n\t\t\t\t\t\t\tid = '{$label_feed}' AND owner_uid = " . $_SESSION['uid']); } else { if (!$cat_view && is_numeric($feed) && $feed > 0) { $result = $this->dbh->query("SELECT id FROM ttrss_feeds WHERE\n\t\t\t\t\t\t\tid = '{$feed}' AND owner_uid = " . $_SESSION['uid']); } else { if ($cat_view && is_numeric($feed) && $feed > 0) { $result = $this->dbh->query("SELECT id FROM ttrss_feed_categories WHERE\n\t\t\t\t\t\t\tid = '{$feed}' AND owner_uid = " . $_SESSION['uid']); } } } if ($result && $this->dbh->num_rows($result) == 0) { print json_encode($this->generate_error_feed(__("Feed not found."))); return; } /* Updating a label ccache means recalculating all of the caches * so for performance reasons we don't do that here */ if ($feed >= 0) { ccache_update($feed, $_SESSION["uid"], $cat_view); } set_pref("_DEFAULT_VIEW_MODE", $view_mode); set_pref("_DEFAULT_VIEW_ORDER_BY", $order_by); /* bump login timestamp if needed */ if (time() - $_SESSION["last_login_update"] > 3600) { $this->dbh->query("UPDATE ttrss_users SET last_login = NOW() WHERE id = " . $_SESSION["uid"]); $_SESSION["last_login_update"] = time(); } if (!$cat_view && is_numeric($feed) && $feed > 0) { $this->dbh->query("UPDATE ttrss_feeds SET last_viewed = NOW()\n\t\t\t\t\t\t\tWHERE id = '{$feed}' AND owner_uid = " . $_SESSION["uid"]); } $reply['headlines'] = array(); if (!$next_unread_feed) { $reply['headlines']['id'] = $feed; } else { $reply['headlines']['id'] = $next_unread_feed; } $reply['headlines']['is_cat'] = (bool) $cat_view; $override_order = false; switch ($order_by) { case "title": $override_order = "ttrss_entries.title"; break; case "date_reverse": $override_order = "score DESC, date_entered, updated"; break; case "feed_dates": $override_order = "updated DESC"; break; } if ($_REQUEST["debug"]) { $timing_info = print_checkpoint("04", $timing_info); } $ret = $this->format_headlines_list($feed, $method, $view_mode, $limit, $cat_view, $next_unread_feed, $offset, $vgroup_last_feed, $override_order, true); //$topmost_article_ids = $ret[0]; $headlines_count = $ret[1]; /* $returned_feed = $ret[2]; */ $disable_cache = $ret[3]; $vgroup_last_feed = $ret[4]; $reply['headlines']['content'] =& $ret[5]['content']; $reply['headlines']['toolbar'] =& $ret[5]['toolbar']; if ($_REQUEST["debug"]) { $timing_info = print_checkpoint("05", $timing_info); } $reply['headlines-info'] = array("count" => (int) $headlines_count, "vgroup_last_feed" => $vgroup_last_feed, "disable_cache" => (bool) $disable_cache); if ($_REQUEST["debug"]) { $timing_info = print_checkpoint("30", $timing_info); } $reply['runtime-info'] = make_runtime_info(); print json_encode($reply); }
/** * Private function which executes the SQL query. Mapped on the same-named function in * the official TT-RSS API, removing un-needed parts. */ function queryFeedHeadlines($feed, $limit, $view_mode, $offset = 0, $since_id = 0) { $owner_uid = $_SESSION["uid"]; $ext_tables_part = ""; $search_query_part = ""; $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") { if ($feed != -1) { $unread = getFeedUnread($feed, false); if ($unread > 0) { $view_query_part = " unread = true AND "; } } } else { if ($view_mode == "marked") { $view_query_part = " marked = true AND "; } else { if ($view_mode == "has_note") { $view_query_part = " (note IS NOT NULL AND note != '') AND "; } else { if ($view_mode == "published") { $view_query_part = " published = true AND "; } else { if ($view_mode == "unread" && $feed != -6) { $view_query_part = " unread = true AND "; } } } } } if ($limit > 0) { $limit_query_part = "LIMIT " . $limit; } $allow_archived = false; // override query strategy and enable feed display when searching globally if (!is_numeric($feed)) { $query_strategy_part = "true"; } else { if ($feed > 0) { $query_strategy_part = "feed_id = '{$feed}'"; } else { if ($feed == 0) { // archive virtual feed $query_strategy_part = "feed_id IS NULL"; $allow_archived = true; } else { if ($feed == -1) { // starred virtual feed $query_strategy_part = "marked = true"; $allow_archived = true; $override_order = "last_marked DESC, date_entered DESC, updated DESC"; } else { if ($feed == -2) { // published virtual feed OR labels category $query_strategy_part = "published = true"; $allow_archived = true; $override_order = "last_published DESC, date_entered DESC, updated DESC"; } else { if ($feed == -6) { // recently read $query_strategy_part = "unread = false AND last_read IS NOT NULL"; $allow_archived = true; $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) "; } } else { if ($feed == -4) { // all articles virtual feed $allow_archived = true; $query_strategy_part = "true"; } 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\tttrss_labels2.id = ttrss_user_labels2.label_id AND\n\t\t\t\tttrss_user_labels2.article_id = ref_id"; $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; } $content_query_part = "content, content AS content_preview, "; if (is_numeric($feed)) { if ($limit_query_part) { $offset_query_part = "OFFSET {$offset}"; } 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\tLEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)"; } $query = "SELECT DISTINCT\n\t\t\t\t\tdate_entered,\n\t\t\t\t\tguid,\n\t\t\t\t\tttrss_entries.id,\n\t\t\t\t\tupdated,\n\t\t\t\t\tint_id,\n\t\t\t\t\tuuid,\n\t\t\t\t\tunread,marked,published,link,last_read,\n\t\t\t\t\tlast_marked, last_published,\n\t\t\t\t\tscore\n\t\t\t\tFROM\n\t\t\t\t\t{$from_qpart}\n\t\t\t\tWHERE\n\t\t\t\t{$feed_check_qpart}\n\t\t\t\tttrss_user_entries.ref_id = ttrss_entries.id AND\n\t\t\t\tttrss_user_entries.owner_uid = '{$owner_uid}' AND\n\t\t\t\t{$search_query_part}\n\t\t\t\t{$filter_query_part}\n\t\t\t\t{$view_query_part}\n\t\t\t\t{$since_id_part}\n\t\t\t\t{$query_strategy_part} ORDER BY {$order_by}\n\t\t\t\t{$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," . "ttrss_entries.id as id," . "updated," . "unread," . "marked," . "uuid," . "last_read," . "(SELECT hide_images FROM ttrss_feeds WHERE id = feed_id) AS hide_images," . "last_marked, last_published, " . $since_id_part . "score "; $all_tags = explode(",", $feed); $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); $result = db_query($select_qpart . $from_qpart . $where_qpart); } return array($result); }
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) { 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($search); $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($filter, $owner_uid); // Try to check if SQL regexp implementation chokes on a valid regexp $result = db_query("SELECT true AS true_val FROM ttrss_entries,\n\t\t\t\t\tttrss_user_entries, ttrss_feeds\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") { 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 "; } } } } 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 "; } 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($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($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"; $vfeed_query_part = "ttrss_feeds.title AS feed_title,"; $allow_archived = true; if (!$override_order) { $override_order = "last_read DESC"; } /* } else if ($feed == -7) { // shared $query_strategy_part = "uuid != ''"; $vfeed_query_part = "ttrss_feeds.title AS feed_title,"; $allow_archived = true; */ } 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 ($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); } 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, content AS content_preview, "; 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\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,"; } $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\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.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($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," . "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); }
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); }