From 677cd7453f4c531aa8e1cb8c6ed68ea81864ca73 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Mon, 5 May 2025 21:55:38 +0300 Subject: drop some pointless queries now that we can use RETURNING for inserts --- classes/RSSUtils.php | 90 +++++++++++++++++++++++++++------------------------- 1 file changed, 46 insertions(+), 44 deletions(-) (limited to 'classes') diff --git a/classes/RSSUtils.php b/classes/RSSUtils.php index fec9568da..254750c07 100644 --- a/classes/RSSUtils.php +++ b/classes/RSSUtils.php @@ -990,12 +990,14 @@ class RSSUtils { WHERE guid IN (?, ?, ?)"); $csth->execute([$entry_guid, $entry_guid_hashed, $entry_guid_hashed_compat]); - if (!$csth->fetch()) { + if ($row = $csth->fetch()) { + Debug::log("select returned RID: " . $row['id'], Debug::LOG_VERBOSE); + $base_record_created = false; + } else { Debug::log("base guid [$entry_guid or $entry_guid_hashed] not found, creating...", Debug::LOG_VERBOSE); // base post entry does not exist, create it - $usth = $pdo->prepare( "INSERT INTO ttrss_entries (title, @@ -1016,7 +1018,7 @@ class RSSUtils { (?, ?, ?, ?, ?, ?, false, NOW(), - ?, ?, ?, ?, ?, ?)"); + ?, ?, ?, ?, ?, ?) RETURNING id"); $usth->execute([$entry_title, $entry_guid_hashed, @@ -1031,16 +1033,18 @@ class RSSUtils { "$entry_language", "$entry_author"]); - } + $row = $usth->fetch(); - $csth->execute([$entry_guid, $entry_guid_hashed, $entry_guid_hashed_compat]); + Debug::log("insert returned RID: " . $row['id'], Debug::LOG_VERBOSE); + $base_record_created = true; + } $entry_ref_id = 0; $entry_int_id = 0; - if ($row = $csth->fetch()) { + if ($row['id']) { - Debug::log("base guid found, checking for user record", Debug::LOG_VERBOSE); + Debug::log("base record with RID: " . $row['id'] . " found, checking for user record", Debug::LOG_VERBOSE); $ref_id = $row['id']; $entry_ref_id = $ref_id; @@ -1100,56 +1104,54 @@ class RSSUtils { (ref_id, owner_uid, feed_id, unread, last_read, marked, published, score, tag_cache, label_cache, uuid, last_marked, last_published) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, '', '', '', ".$last_marked.", ".$last_published.")"); + VALUES (?, ?, ?, ?, ?, ?, ?, ?, '', '', '', ".$last_marked.", ".$last_published.") + RETURNING int_id"); $sth->execute([$ref_id, $feed_obj->owner_uid, $feed, $unread, $last_read_qpart, $marked, $published, $score]); + if ($row = $sth->fetch()) + $entry_int_id = $row['int_id']; + if ($marked) PluginHost::getInstance()->run_hooks(PluginHost::HOOK_ARTICLES_MARK_TOGGLED, [$ref_id]); if ($published) PluginHost::getInstance()->run_hooks(PluginHost::HOOK_ARTICLES_PUBLISH_TOGGLED, [$ref_id]); - - $sth = $pdo->prepare("SELECT int_id FROM ttrss_user_entries WHERE - ref_id = ? AND owner_uid = ? AND - feed_id = ? LIMIT 1"); - - $sth->execute([$ref_id, $feed_obj->owner_uid, $feed]); - - if ($row = $sth->fetch()) - $entry_int_id = $row['int_id']; } Debug::log("resulting RID: $entry_ref_id, IID: $entry_int_id", Debug::LOG_VERBOSE); - $sth = $pdo->prepare("UPDATE ttrss_entries - SET title = :title, - tsvector_combined = to_tsvector(:ts_lang, :ts_content), - content = :content, - content_hash = :content_hash, - updated = :updated, - date_updated = NOW(), - num_comments = :num_comments, - plugin_data = :plugin_data, - author = :author, - lang = :lang - WHERE id = :id"); - - $params = [":title" => $entry_title, - ":content" => "$entry_content", - ":content_hash" => $entry_current_hash, - ":updated" => $entry_timestamp_fmt, - ":num_comments" => (int)$num_comments, - ":plugin_data" => $entry_plugin_data, - ":author" => "$entry_author", - ":lang" => $entry_language, - ":id" => $ref_id, - ":ts_lang" => $feed_language, - ":ts_content" => mb_substr(strip_tags($entry_title) . " " . \Soundasleep\Html2Text::convert($entry_content), 0, 900000) - ]; - - $sth->execute($params); + // it's pointless to update base record we've just created + if (!$base_record_created) { + $sth = $pdo->prepare("UPDATE ttrss_entries + SET title = :title, + tsvector_combined = to_tsvector(:ts_lang, :ts_content), + content = :content, + content_hash = :content_hash, + updated = :updated, + date_updated = NOW(), + num_comments = :num_comments, + plugin_data = :plugin_data, + author = :author, + lang = :lang + WHERE id = :id"); + + $params = [":title" => $entry_title, + ":content" => "$entry_content", + ":content_hash" => $entry_current_hash, + ":updated" => $entry_timestamp_fmt, + ":num_comments" => (int)$num_comments, + ":plugin_data" => $entry_plugin_data, + ":author" => "$entry_author", + ":lang" => $entry_language, + ":id" => $ref_id, + ":ts_lang" => $feed_language, + ":ts_content" => mb_substr(strip_tags($entry_title) . " " . \Soundasleep\Html2Text::convert($entry_content), 0, 900000) + ]; + + $sth->execute($params); + } // update aux data $sth = $pdo->prepare("UPDATE ttrss_user_entries -- cgit v1.2.3-54-g00ecf From ea6f42dc612d6af0178d4451a06f98c550614db9 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Mon, 5 May 2025 22:08:01 +0300 Subject: switch insert query for base article record to named parameters and add previously missing ts_content stuff --- classes/RSSUtils.php | 50 ++++++++++++++++++++++++++++++++------------------ 1 file changed, 32 insertions(+), 18 deletions(-) (limited to 'classes') diff --git a/classes/RSSUtils.php b/classes/RSSUtils.php index 254750c07..5354ae7ba 100644 --- a/classes/RSSUtils.php +++ b/classes/RSSUtils.php @@ -998,9 +998,10 @@ class RSSUtils { Debug::log("base guid [$entry_guid or $entry_guid_hashed] not found, creating...", Debug::LOG_VERBOSE); // base post entry does not exist, create it - $usth = $pdo->prepare( + $isth = $pdo->prepare( "INSERT INTO ttrss_entries (title, + tsvector_combined, guid, link, updated, @@ -1015,25 +1016,38 @@ class RSSUtils { lang, author) VALUES - (?, ?, ?, ?, ?, ?, + (:title, + to_tsvector(:ts_lang, :ts_content), + :guid, + :link, + :updated, + :content, + :content_hash, false, NOW(), - ?, ?, ?, ?, ?, ?) RETURNING id"); - - $usth->execute([$entry_title, - $entry_guid_hashed, - $entry_link, - $entry_timestamp_fmt, - "$entry_content", - $entry_current_hash, - $date_feed_processed, - $entry_comments, - (int)$num_comments, - $entry_plugin_data, - "$entry_language", - "$entry_author"]); - - $row = $usth->fetch(); + :date_entered, + :comments, + :num_comments, + :plugin_data, + :lang, + :author) RETURNING id"); + + $isth->execute([":title" => $entry_title, + ":ts_lang" => $feed_language, + ":ts_content" => mb_substr(strip_tags($entry_title) . " " . \Soundasleep\Html2Text::convert($entry_content), 0, 900000), + ":guid" => $entry_guid_hashed, + ":link" => $entry_link, + ":updated" => $entry_timestamp_fmt, + ":content" => $entry_content, + ":content_hash" => $entry_current_hash, + ":date_entered" => $date_feed_processed, + ":comments" => $entry_comments, + ":num_comments" => (int)$num_comments, + ":plugin_data" => $entry_plugin_data, + ":lang" => $entry_language, + ":author" => $entry_author]); + + $row = $isth->fetch(); Debug::log("insert returned RID: " . $row['id'], Debug::LOG_VERBOSE); $base_record_created = true; -- cgit v1.2.3-54-g00ecf From 9735ff83cc822cb61bc40ddfc8a433eea92cd5ea Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Tue, 6 May 2025 05:01:23 +0300 Subject: use RETURNING syntax when creating base filter record --- classes/Pref_Filters.php | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'classes') diff --git a/classes/Pref_Filters.php b/classes/Pref_Filters.php index a6063d898..40fc99600 100644 --- a/classes/Pref_Filters.php +++ b/classes/Pref_Filters.php @@ -689,14 +689,10 @@ class Pref_Filters extends Handler_Protected { $sth = $this->pdo->prepare("INSERT INTO ttrss_filters2 (owner_uid, match_any_rule, enabled, title, inverse) VALUES - (?, ?, ?, ?, ?)"); + (?, ?, ?, ?, ?) RETURNING id"); $sth->execute([$_SESSION['uid'], $match_any_rule, $enabled, $title, $inverse]); - $sth = $this->pdo->prepare("SELECT MAX(id) AS id FROM ttrss_filters2 - WHERE owner_uid = ?"); - $sth->execute([$_SESSION['uid']]); - if ($row = $sth->fetch()) { $filter_id = $row['id']; -- cgit v1.2.3-54-g00ecf From e91c49b747f5a643d27b664c32bfa4d13be18346 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Tue, 6 May 2025 05:04:47 +0300 Subject: use RETURNING syntax when creating article record in share anything --- classes/Article.php | 9 +++------ 1 file changed, 3 insertions(+), 6 deletions(-) (limited to 'classes') diff --git a/classes/Article.php b/classes/Article.php index c0d77123c..e939298bc 100644 --- a/classes/Article.php +++ b/classes/Article.php @@ -124,18 +124,15 @@ class Article extends Handler_Protected { $sth = $pdo->prepare("INSERT INTO ttrss_entries (title, guid, link, updated, content, content_hash, date_entered, date_updated) VALUES - (?, ?, ?, NOW(), ?, ?, NOW(), NOW())"); + (?, ?, ?, NOW(), ?, ?, NOW(), NOW()) RETURNING id"); $sth->execute([$title, $guid, $url, $content, $content_hash]); - $sth = $pdo->prepare("SELECT id FROM ttrss_entries WHERE guid = ?"); - $sth->execute([$guid]); - if ($row = $sth->fetch()) { $ref_id = $row["id"]; $sth = $pdo->prepare("UPDATE ttrss_entries - SET tsvector_combined = to_tsvector( :ts_content) - WHERE id = :id"); + SET tsvector_combined = to_tsvector( :ts_content) + WHERE id = :id"); $params = [ ":ts_content" => mb_substr(\Soundasleep\Html2Text::convert($content), 0, 900000), ":id" => $ref_id]; -- cgit v1.2.3-54-g00ecf From 77e5deb9dda0e718227390993346994cf6cc1737 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Tue, 6 May 2025 05:06:17 +0300 Subject: use RETURNING syntax when creating base filter record during OPML import --- classes/OPML.php | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'classes') diff --git a/classes/OPML.php b/classes/OPML.php index 1debc8623..bae00b8bc 100644 --- a/classes/OPML.php +++ b/classes/OPML.php @@ -397,14 +397,10 @@ class OPML extends Handler_Protected { //print "F: $title, $inverse, $enabled, $match_any_rule"; $sth = $this->pdo->prepare("INSERT INTO ttrss_filters2 (match_any_rule,enabled,inverse,title,owner_uid) - VALUES (?, ?, ?, ?, ?)"); + VALUES (?, ?, ?, ?, ?) RETURNING id"); $sth->execute([$match_any_rule, $enabled, $inverse, $title, $owner_uid]); - $sth = $this->pdo->prepare("SELECT MAX(id) AS id FROM ttrss_filters2 WHERE - owner_uid = ?"); - $sth->execute([$owner_uid]); - $row = $sth->fetch(); $filter_id = $row['id']; -- cgit v1.2.3-54-g00ecf