From 05732aa0b6a95d6d6c5986b28077d7fe28edab0c Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Sat, 19 Nov 2005 09:18:34 +0100 Subject: [PATCH] misc new schema support related work --- backend.php | 17 ++++++++++------- functions.php | 26 ++++++++++++++++---------- schema/ttrss_schema_pgsql.sql | 3 ++- tt-rss.js | 2 +- 4 files changed, 29 insertions(+), 19 deletions(-) diff --git a/backend.php b/backend.php index a3ee30c5..19aebd89 100644 --- a/backend.php +++ b/backend.php @@ -59,11 +59,12 @@ } function getTagCounters($link) { + $result = db_query($link, "SELECT tag_name,count(ttrss_entries.id) AS count FROM ttrss_tags,ttrss_entries,ttrss_user_entries WHERE ttrss_user_entries.ref_id = ttrss_entries.id AND ttrss_tags.owner_uid = ".$_SESSION["uid"]." AND - post_id = ttrss_entries.id AND unread = true GROUP BY tag_name + post_int_id = ttrss_user_entries.int_id AND unread = true GROUP BY tag_name UNION select tag_name,0 as count FROM ttrss_tags WHERE ttrss_tags.owner_uid = ".$_SESSION["uid"]); @@ -272,8 +273,9 @@ // tags $result = db_query($link, "SELECT tag_name,count(ttrss_entries.id) AS count - FROM ttrss_tags,ttrss_entries WHERE - post_id = ttrss_entries.id AND unread = true + FROM ttrss_tags,ttrss_entries,ttrss_user_entries WHERE + post_int_id = ttrss_user_entries.int_id AND + unread = true AND ref_id = ttrss_entries.id AND ttrss_tags.owner_uid = '$owner_uid' GROUP BY tag_name UNION select tag_name,0 as count FROM ttrss_tags WHERE owner_uid = '$owner_uid' @@ -375,7 +377,7 @@ if ($subop == "forceUpdateAllFeeds" || $subop == "updateAllFeeds") { - update_all_feeds($link, true); + update_all_feeds($link, $subop == "forceUpdateAllFeeds"); $omode = $_GET["omode"]; @@ -718,10 +720,11 @@ $vfeed_query_part SUBSTRING(updated,1,19) as updated_noms FROM - ttrss_entries,ttrss_tags + ttrss_entries,ttrss_user_entries,ttrss_tags WHERE - ttrss_entries.owner_uid = '".$_SESSION["uid"]."' AND - post_id = ttrss_entries.id AND tag_name = '$feed' AND + ref_id = ttrss_entries.id AND + ttrss_user_entries.owner_uid = '".$_SESSION["uid"]."' AND + post_int_id = int_id AND tag_name = '$feed' AND $view_query_part $search_query_part $query_strategy_part ORDER BY $order_by diff --git a/functions.php b/functions.php index 3dee285a..36741a19 100644 --- a/functions.php +++ b/functions.php @@ -261,7 +261,8 @@ $entry_guid = db_escape_string($entry_guid); - $result = db_query($link, "SELECT id FROM ttrss_entries WHERE guid = '$entry_guid'"); + $result = db_query($link, "SELECT id FROM ttrss_entries + WHERE guid = '$entry_guid'"); $owner_uid = $_SESSION["uid"]; @@ -305,7 +306,8 @@ // now it should exist, if not - bad luck then - $result = db_query($link, "SELECT id FROM ttrss_entries WHERE guid = '$entry_guid'"); + $result = db_query($link, "SELECT id FROM ttrss_entries WHERE + guid = '$entry_guid'"); if (db_num_rows($result) == 1) { @@ -315,7 +317,8 @@ $result = db_query($link, "SELECT ref_id FROM ttrss_user_entries WHERE - ref_id = '$ref_id' AND owner_uid = '$owner_uid' AND feed_id = '$feed'"); + ref_id = '$ref_id' AND owner_uid = '$owner_uid' AND + feed_id = '$feed'"); // okay it doesn't exist - create user entry @@ -449,7 +452,7 @@ /* taaaags */ // , // -/* $entry_tags = null; + $entry_tags = null; preg_match_all("/([^>]+)<\/a>/i", $entry_content, $entry_tags); @@ -458,9 +461,10 @@ if (count($entry_tags) > 0) { - $result = db_query($link, "SELECT id FROM ttrss_entries + $result = db_query($link, "SELECT id,int_id + FROM ttrss_entries,ttrss_user_entries WHERE guid = '$entry_guid' - AND feed_id = '$feed' + AND feed_id = '$feed' AND ref_id = id AND owner_uid = " . $_SESSION["uid"]); if (!$result || db_num_rows($result) != 1) { @@ -468,6 +472,7 @@ } $entry_id = db_fetch_result($result, 0, "id"); + $entry_int_id = db_fetch_result($result, 0, "int_id"); foreach ($entry_tags as $tag) { $tag = db_escape_string(strtolower($tag)); @@ -475,7 +480,7 @@ $tag = str_replace("technorati tag: ", "", $tag); $result = db_query($link, "SELECT id FROM ttrss_tags - WHERE tag_name = '$tag' AND post_id = '$entry_id' AND + WHERE tag_name = '$tag' AND post_int_id = '$entry_int_id' AND owner_uid = ".$_SESSION["uid"]." LIMIT 1"); // print db_fetch_result($result, 0, "id"); @@ -484,11 +489,12 @@ // print "tagging $entry_id as $tag
"; - db_query($link, "INSERT INTO ttrss_tags (owner_uid,tag_name,post_id) - VALUES ('".$_SESSION["uid"]."','$tag', '$entry_id')"); + db_query($link, "INSERT INTO ttrss_tags + (owner_uid,tag_name,post_int_id) + VALUES ('".$_SESSION["uid"]."','$tag', '$entry_int_id')"); } } - } */ + } } db_query($link, "UPDATE ttrss_feeds diff --git a/schema/ttrss_schema_pgsql.sql b/schema/ttrss_schema_pgsql.sql index d3e691b3..e1d07c42 100644 --- a/schema/ttrss_schema_pgsql.sql +++ b/schema/ttrss_schema_pgsql.sql @@ -53,6 +53,7 @@ create table ttrss_entries (id serial not null primary key, index (guid), index(title)); create table ttrss_user_entries ( + int_id serial not null primary key, ref_id integer not null references ttrss_entries(id) ON DELETE CASCADE, feed_id int references ttrss_feeds(id) ON DELETE CASCADE not null, owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE, @@ -100,7 +101,7 @@ insert into ttrss_labels (owner_uid,sql_exp,description) values (1, create table ttrss_tags (id serial not null primary key, tag_name varchar(250) not null, owner_uid integer not null references ttrss_users(id) on delete cascade, - post_id integer references ttrss_entries(id) ON DELETE CASCADE not null); + post_int_id integer references ttrss_user_entries(int_id) ON DELETE CASCADE not null); create index ttrss_tags_owner_uid_index on ttrss_tags(owner_uid); diff --git a/tt-rss.js b/tt-rss.js index 08178c84..e18d12be 100644 --- a/tt-rss.js +++ b/tt-rss.js @@ -250,7 +250,7 @@ function viewfeed(feed, skip, subop) { } function timeout() { - scheduleFeedUpdate(true); + scheduleFeedUpdate(false); setTimeout("timeout()", 1800*1000); } -- 2.39.5