Merge branch 'pg-optimize' into 'master'

drop some pointless queries now that we can use RETURNING for inserts

See merge request tt-rss/tt-rss!130

4 files changed, 82 insertions, 77 deletions

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];
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'];
 
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'];
 
diff --git a/classes/RSSUtils.php b/classes/RSSUtils.php
index fec9568da..5354ae7ba 100644
--- a/classes/RSSUtils.php
+++ b/classes/RSSUtils.php
@@ -990,15 +990,18 @@ 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(
+					$isth = $pdo->prepare(
 						"INSERT INTO ttrss_entries
 							(title,
+							tsvector_combined,
 							guid,
 							link,
 							updated,
@@ -1013,34 +1016,49 @@ class RSSUtils {
 							lang,
 							author)
 						VALUES
-							(?, ?, ?, ?, ?, ?,
+							(:title,
+							to_tsvector(:ts_lang, :ts_content),
+							:guid,
+							:link,
+							:updated,
+							:content,
+							:content_hash,
 							false,
 							NOW(),
-							?, ?, ?, ?,	?, ?)");
-
-						$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"]);
-
-				}
-
-				$csth->execute([$entry_guid, $entry_guid_hashed, $entry_guid_hashed_compat]);
+							: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;
+					}
 
 				$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 +1118,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