diff options
| -rw-r--r-- | backend.php | 1 | ||||
| -rw-r--r-- | functions.php | 149 | ||||
| -rw-r--r-- | sanity_check.php | 2 | ||||
| -rw-r--r-- | schema/ttrss_schema_mysql.sql | 12 | ||||
| -rw-r--r-- | schema/ttrss_schema_pgsql.sql | 11 | ||||
| -rw-r--r-- | schema/versions/mysql/50.sql | 19 | ||||
| -rw-r--r-- | schema/versions/pgsql/50.sql | 19 |
7 files changed, 181 insertions, 32 deletions
diff --git a/backend.php b/backend.php index 549661156..109c6e586 100644 --- a/backend.php +++ b/backend.php @@ -225,6 +225,7 @@ outputArticleXML($link, $id, $feed_id, true, true); } else { catchupArticleById($link, $id, 0); + ccache_update($link, $feed_id, $_SESSION["uid"]); } if (!$_SESSION["bw_limit"]) { diff --git a/functions.php b/functions.php index 39704ec0c..3dd43d477 100644 --- a/functions.php +++ b/functions.php @@ -2327,15 +2327,22 @@ $age_qpart = getMaxAgeSubquery(); - $result = db_query($link, "SELECT cat_id,SUM((SELECT COUNT(int_id) +/* $result = db_query($link, "SELECT cat_id,SUM((SELECT COUNT(int_id) FROM ttrss_user_entries, ttrss_entries WHERE feed_id = ttrss_feeds.id AND id = ref_id AND $age_qpart AND unread = true)) AS unread FROM ttrss_feeds WHERE + hidden = false AND owner_uid = ".$_SESSION["uid"]." GROUP BY cat_id"); */ + + $result = db_query($link, "SELECT cat_id FROM ttrss_feeds + WHERE hidden = false AND owner_uid = ".$_SESSION["uid"]." GROUP BY cat_id"); while ($line = db_fetch_assoc($result)) { $line["cat_id"] = sprintf("%d", $line["cat_id"]); + + $line["unread"] = ccache_find($link, $line["cat_id"], $_SESSION["uid"], true); + print "<counter type=\"category\" id=\"".$line["cat_id"]."\" counter=\"". $line["unread"]."\"/>"; } @@ -2522,7 +2529,7 @@ $user_id = $_SESSION["uid"]; } - $age_qpart = getMaxAgeSubquery(); +/* $age_qpart = getMaxAgeSubquery(); $result = db_query($link, "SELECT count(ttrss_entries.id) as c_id FROM ttrss_entries,ttrss_user_entries,ttrss_feeds WHERE unread = true AND @@ -2530,8 +2537,14 @@ ttrss_user_entries.ref_id = ttrss_entries.id AND hidden = false AND $age_qpart AND - ttrss_user_entries.owner_uid = '$user_id'"); - $c_id = db_fetch_result($result, 0, "c_id"); + ttrss_user_entries.owner_uid = '$user_id'"); */ + + + $result = db_query($link, "SELECT SUM(value) AS c_id FROM ttrss_counters_cache + WHERE owner_uid = '$user_id' AND feed_id > 0"); + + $c_id = db_fetch_result($result, 0, "c_id"); + return $c_id; } @@ -2680,9 +2693,11 @@ $label_name = $line["description"]; + $count = ccache_find($link, $id, $_SESSION["uid"]); + error_reporting (0); - $tmp_result = db_query($link, "SELECT count(ttrss_entries.id) as count FROM ttrss_user_entries,ttrss_entries,ttrss_feeds +/* $tmp_result = db_query($link, "SELECT count(ttrss_entries.id) as count FROM ttrss_user_entries,ttrss_entries,ttrss_feeds WHERE (" . $line["sql_exp"] . ") AND unread = true AND ttrss_feeds.hidden = false AND $age_qpart AND @@ -2708,10 +2723,29 @@ $ret_arr[$id]["counter"] = $count; $ret_arr[$id]["description"] = $label_name; } - } + } */ + + if (!$smart_mode || $old_counters[$id] != $count) { + $old_counters[$id] = $count; + $lctrs_modified = true; + if (!$ret_mode) { + + if (get_pref($link, 'EXTENDED_FEEDLIST')) { + $xmsg_part = "xmsg=\"(" . getFeedArticles($link, $id) . " total)\""; + } else { + $xmsg_part = ""; + } + + print "<counter type=\"label\" id=\"$id\" counter=\"$count\" $xmsg_part/>"; + } else { + $ret_arr[$id]["counter"] = $count; + $ret_arr[$id]["description"] = $label_name; + } + } + error_reporting (DEFAULT_ERROR_LEVEL); - } + } if ($smart_mode && $lctrs_modified) { $_SESSION["lctr_last_value"] = $old_counters; @@ -2757,7 +2791,7 @@ FROM ttrss_feeds WHERE owner_uid = ".$_SESSION["uid"] . " AND parent_feed IS NULL"); */ - $query = "SELECT ttrss_feeds.id, +/* $query = "SELECT ttrss_feeds.id, ttrss_feeds.title, ".SUBSTRING_FOR_DATE."(ttrss_feeds.last_updated,1,19) AS last_updated, last_error, @@ -2770,7 +2804,18 @@ $age_qpart) WHERE ttrss_feeds.owner_uid = ".$_SESSION["uid"]." AND parent_feed IS NULL - GROUP BY ttrss_feeds.id, ttrss_feeds.title, ttrss_feeds.last_updated, last_error"; + GROUP BY ttrss_feeds.id, ttrss_feeds.title, ttrss_feeds.last_updated, last_error"; */ + + $query = "SELECT ttrss_feeds.id, + ttrss_feeds.title, + ".SUBSTRING_FOR_DATE."(ttrss_feeds.last_updated,1,19) AS last_updated, + last_error + FROM ttrss_feeds + WHERE ttrss_feeds.owner_uid = ".$_SESSION["uid"]." + AND parent_feed IS NULL + GROUP BY ttrss_feeds.id, ttrss_feeds.title, ttrss_feeds.last_updated, + last_error"; + $result = db_query($link, $query); $fctrs_modified = false; @@ -2780,7 +2825,7 @@ while ($line = db_fetch_assoc($result)) { $id = $line["id"]; - $count = $line["count"]; + $count = ccache_find($link, $line["id"], $_SESSION["uid"]); $last_error = htmlspecialchars($line["last_error"]); if (get_pref($link, 'HEADLINES_SMART_DATE')) { @@ -4332,11 +4377,6 @@ $query = "SELECT ttrss_feeds.*, ".SUBSTRING_FOR_DATE."(last_updated,1,19) AS last_updated_noms, - (SELECT COUNT(id) FROM ttrss_entries,ttrss_user_entries - WHERE feed_id = ttrss_feeds.id AND unread = true - AND $age_qpart - AND ttrss_user_entries.ref_id = ttrss_entries.id - AND owner_uid = '$owner_uid') as unread, cat_id,last_error, ttrss_feed_categories.title AS category, ttrss_feed_categories.collapsed @@ -4371,7 +4411,7 @@ $subop = $_GET["subop"]; - $unread = $line["unread"]; + $unread = ccache_find($link, $feed_id, $_SESSION["uid"]); if (get_pref($link, 'HEADLINES_SMART_DATE')) { $last_updated = smart_date_time(strtotime($line["last_updated_noms"])); @@ -4646,6 +4686,8 @@ $result = db_query($link, "UPDATE ttrss_user_entries SET unread = false,last_read = NOW() WHERE ref_id = '$id' AND owner_uid = " . $_SESSION["uid"]); + + ccache_update($link, $feed_id, $_SESSION["uid"]); } $result = db_query($link, "SELECT title,link,content,feed_id,comments,int_id, @@ -5944,45 +5986,98 @@ } - function ccache_invalidate($link, $feed_id, $owner_uid) { - + function ccache_zero($link, $feed_id, $owner_uid) { db_query($link, "UPDATE ttrss_counters_cache SET value = 0, updated = NOW() WHERE feed_id = '$feed_id' AND owner_uid = '$owner_uid'"); + } + function ccache_invalidate($link, $feed_id, $owner_uid, $is_cat = false) { + + if (!$is_cat) { + $table = "ttrss_counters_cache"; + } else { + $table = "ttrss_cat_counters_cache"; + } + + db_query($link, "DELETE FROM $table + WHERE feed_id = '$feed_id' AND owner_uid = '$owner_uid'"); } - function ccache_find($link, $feed_id, $owner_uid) { - $result = db_query($link, "SELECT value FROM ttrss_counters_cache - WHERE owner_uid = '$owner_uid' AND feed_id = '$feed_id' LIMIT 1"); + function ccache_find($link, $feed_id, $owner_uid, $is_cat = false) { + + if (!$is_cat) { + $table = "ttrss_counters_cache"; + } else { + $table = "ttrss_cat_counters_cache"; + } + + if (DB_TYPE == "pgsql") { + $date_qpart = "updated > NOW() - INTERVAL '15 minutes'"; + } else if (DB_TYPE == "mysql") { + $date_qpart = "updated > DATE_SUB(NOW(), INTERVAL 15 MINUTE)"; + } + + $result = db_query($link, "SELECT value FROM $table + WHERE owner_uid = '$owner_uid' AND feed_id = '$feed_id' + AND $date_qpart LIMIT 1"); if (db_num_rows($result) == 1) { return db_fetch_result($result, 0, "value"); } else { - return -1; + return ccache_update($link, $feed_id, $owner_uid, $is_cat); + } } - function ccache_update($link, $feed_id, $owner_uid) { + function ccache_update($link, $feed_id, $owner_uid, $is_cat = false) { - $unread = (int) getFeedArticles($link, $feed_id, false, true, $owner_uid); + if (!$is_cat) { + $table = "ttrss_counters_cache"; + } else { + $table = "ttrss_cat_counters_cache"; + } + + $unread = (int) getFeedArticles($link, $feed_id, $is_cat, true, $owner_uid); - $result = db_query($link, "SELECT feed_id FROM ttrss_counters_cache + $result = db_query($link, "SELECT feed_id FROM $table WHERE owner_uid = '$owner_uid' AND feed_id = '$feed_id' LIMIT 1"); if (db_num_rows($result) == 1) { - db_query($link, "UPDATE ttrss_counters_cache SET + db_query($link, "UPDATE $table SET value = '$unread', updated = NOW() WHERE feed_id = '$feed_id' AND owner_uid = '$owner_uid'"); } else { - db_query($link, "INSERT INTO ttrss_counters_cache + db_query($link, "INSERT INTO $table (feed_id, value, owner_uid, updated) VALUES ($feed_id, $unread, $owner_uid, NOW())"); } + /* As it is a real feed, we have to update labels, possibly referencing + * this feed; when browsing the category - update counters for underlying + * feeds */ + + if ($feed_id > 0) { + + + if ($is_cat) { + $result = db_query($link, "SELECT id AS feed_id FROM ttrss_feeds + WHERE owner_uid = '$owner_uid' AND cat_id = '$feed_id'"); + } else { + $result = db_query($link, "SELECT feed_id FROM ttrss_counters_cache + WHERE owner_uid = '$owner_uid' AND feed_id < 0"); + } + + while ($line = db_fetch_assoc($result)) { + ccache_update($link, $line["feed_id"], $owner_uid); + + } + } + + return $unread; } ?> diff --git a/sanity_check.php b/sanity_check.php index 93a6b0f57..0ba47b064 100644 --- a/sanity_check.php +++ b/sanity_check.php @@ -2,7 +2,7 @@ require_once "functions.php"; define('EXPECTED_CONFIG_VERSION', 17); - define('SCHEMA_VERSION', 49); + define('SCHEMA_VERSION', 50); if (!file_exists("config.php")) { print __("<b>Fatal Error</b>: You forgot to copy diff --git a/schema/ttrss_schema_mysql.sql b/schema/ttrss_schema_mysql.sql index f4d3b38a9..4d081db0f 100644 --- a/schema/ttrss_schema_mysql.sql +++ b/schema/ttrss_schema_mysql.sql @@ -18,6 +18,7 @@ drop table if exists ttrss_user_entries; drop table if exists ttrss_entries; drop table if exists ttrss_scheduled_updates; drop table if exists ttrss_counters_cache; +drop table if exists ttrss_cat_counters_cache; drop table if exists ttrss_feeds; drop table if exists ttrss_feed_categories; drop table if exists ttrss_users; @@ -64,7 +65,14 @@ create table ttrss_counters_cache ( owner_uid integer not null, value integer not null default 0, updated datetime not null, - foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + updated datetime not null, foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE ); @@ -224,7 +232,7 @@ create table ttrss_tags (id integer primary key auto_increment, create table ttrss_version (schema_version int not null) TYPE=InnoDB; -insert into ttrss_version values (49); +insert into ttrss_version values (50); create table ttrss_enclosures (id serial not null primary key, content_url text not null, diff --git a/schema/ttrss_schema_pgsql.sql b/schema/ttrss_schema_pgsql.sql index 4ea93f97a..319e64211 100644 --- a/schema/ttrss_schema_pgsql.sql +++ b/schema/ttrss_schema_pgsql.sql @@ -15,6 +15,7 @@ drop table ttrss_user_entries; drop table ttrss_entries; drop table ttrss_scheduled_updates; drop table ttrss_counters_cache; +drop table ttrss_cat_counters_cache; drop table ttrss_feeds; drop table ttrss_feed_categories; drop table ttrss_users; @@ -86,7 +87,13 @@ insert into ttrss_feeds (owner_uid, title, feed_url) values (1, 'Tiny Tiny RSS: Forum', 'http://tt-rss.spb.ru/forum/rss.php'); create table ttrss_counters_cache ( - feed_id integer not null references ttrss_feeds(id) ON DELETE CASCADE, + feed_id integer not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + updated timestamp not null, + value integer not null default 0); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, updated timestamp not null, value integer not null default 0); @@ -201,7 +208,7 @@ create index ttrss_tags_owner_uid_index on ttrss_tags(owner_uid); create table ttrss_version (schema_version int not null); -insert into ttrss_version values (49); +insert into ttrss_version values (50); create table ttrss_enclosures (id serial not null primary key, content_url text not null, diff --git a/schema/versions/mysql/50.sql b/schema/versions/mysql/50.sql new file mode 100644 index 000000000..6f49742f4 --- /dev/null +++ b/schema/versions/mysql/50.sql @@ -0,0 +1,19 @@ +drop table if exists ttrss_counters_cache; + +create table ttrss_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + updated datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, + owner_uid integer not null, + value integer not null default 0, + updated datetime not null, + foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE +); + +update ttrss_version set schema_version = 50; diff --git a/schema/versions/pgsql/50.sql b/schema/versions/pgsql/50.sql new file mode 100644 index 000000000..86846242c --- /dev/null +++ b/schema/versions/pgsql/50.sql @@ -0,0 +1,19 @@ +begin; + +drop table ttrss_counters_cache; + +create table ttrss_counters_cache ( + feed_id integer not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + updated timestamp not null, + value integer not null default 0); + +create table ttrss_cat_counters_cache ( + feed_id integer not null, + owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, + updated timestamp not null, + value integer not null default 0); + +update ttrss_version set schema_version = 50; + +commit; |