From 1ee1fcce91d64c8cee6b8621aaedbdeae0c9c330 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Tue, 2 Feb 2021 00:03:59 +0100
Subject: INSERT ... ON CONFLIT DO NOTHING for addEntry (#3409)

#fix https://github.com/FreshRSS/FreshRSS/issues/3402
Explicit `INSERT OR IGNORE` / `ON CONFLICT DO NOTHING` for the
`addEntry()` method, which does expect some duplicates.
---
 app/Models/EntryDAO.php       | 9 +++++++--
 app/Models/EntryDAOPGSQL.php  | 5 +++++
 app/Models/EntryDAOSQLite.php | 4 ++++
 3 files changed, 16 insertions(+), 2 deletions(-)

(limited to 'app/Models')

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 28a9b8bde..6f7a4b48f 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -18,6 +18,10 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return 'hex(' . $x . ')';
 	}
 
+	public function sqlIgnoreConflict($sql) {
+		return str_replace('INSERT INTO ', 'INSERT IGNORE INTO ', $sql);
+	}
+
 	//TODO: Move the database auto-updates to DatabaseDAO
 	protected function createEntryTempTable() {
 		$ok = false;
@@ -83,14 +87,15 @@ SQL;
 
 	public function addEntry($valuesTmp, $useTmpTable = true) {
 		if ($this->addEntryPrepared == null) {
-			$sql = 'INSERT INTO `_' . ($useTmpTable ? 'entrytmp' : 'entry') . '` (id, guid, title, author, '
+			$sql = $this->sqlIgnoreConflict(
+				'INSERT INTO `_' . ($useTmpTable ? 'entrytmp' : 'entry') . '` (id, guid, title, author, '
 				. ($this->isCompressed() ? 'content_bin' : 'content')
 				. ', link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) '
 				. 'VALUES(:id, :guid, :title, :author, '
 				. ($this->isCompressed() ? 'COMPRESS(:content)' : ':content')
 				. ', :link, :date, :last_seen, '
 				. $this->sqlHexDecode(':hash')
-				. ', :is_read, :is_favorite, :id_feed, :tags)';
+				. ', :is_read, :is_favorite, :id_feed, :tags)');
 			$this->addEntryPrepared = $this->pdo->prepare($sql);
 		}
 		if ($this->addEntryPrepared) {
diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php
index 9afea279f..a9304bbaa 100644
--- a/app/Models/EntryDAOPGSQL.php
+++ b/app/Models/EntryDAOPGSQL.php
@@ -14,6 +14,10 @@ class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite {
 		return 'encode(' . $x . ", 'hex')";
 	}
 
+	public function sqlIgnoreConflict($sql) {
+		return rtrim($sql, ' ;') . ' ON CONFLICT DO NOTHING';
+	}
+
 	protected function autoUpdateDb($errorInfo) {
 		if (isset($errorInfo[0])) {
 			if ($errorInfo[0] === FreshRSS_DatabaseDAOPGSQL::UNDEFINED_TABLE) {
@@ -33,6 +37,7 @@ class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite {
 	}
 
 	public function commitNewEntries() {
+		//TODO: Update to PostgreSQL 9.5+ syntax with ON CONFLICT DO NOTHING
 		$sql = 'DO $$
 DECLARE
 maxrank bigint := (SELECT MAX(id) FROM `_entrytmp`);
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index 12e8f27e1..e53583654 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -14,6 +14,10 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 		return $x;
 	}
 
+	public function sqlIgnoreConflict($sql) {
+		return str_replace('INSERT INTO ', 'INSERT OR IGNORE INTO ', $sql);
+	}
+
 	protected function autoUpdateDb($errorInfo) {
 		if ($tableInfo = $this->pdo->query("SELECT sql FROM sqlite_master where name='tag'")) {
 			$showCreate = $tableInfo->fetchColumn();
-- 
cgit v1.2.3