diff options
Diffstat (limited to 'app/Models/EntryDAOPGSQL.php')
| -rw-r--r-- | app/Models/EntryDAOPGSQL.php | 34 |
1 files changed, 18 insertions, 16 deletions
diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php index 91068919d..05850632f 100644 --- a/app/Models/EntryDAOPGSQL.php +++ b/app/Models/EntryDAOPGSQL.php @@ -90,22 +90,24 @@ class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite { #[\Override] public function commitNewEntries(): bool { //TODO: Update to PostgreSQL 9.5+ syntax with ON CONFLICT DO NOTHING - $sql = 'DO $$ -DECLARE -maxrank bigint := (SELECT MAX(id) FROM `_entrytmp`); -rank bigint := (SELECT maxrank - COUNT(*) FROM `_entrytmp`); -BEGIN - INSERT INTO `_entry` - (id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes) - (SELECT rank + row_number() OVER(ORDER BY date, id) AS id, guid, title, author, content, - link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes - FROM `_entrytmp` AS etmp - WHERE NOT EXISTS ( - SELECT 1 FROM `_entry` AS ereal - WHERE (etmp.id = ereal.id) OR (etmp.id_feed = ereal.id_feed AND etmp.guid = ereal.guid)) - ORDER BY date, id); - DELETE FROM `_entrytmp` WHERE id <= maxrank; -END $$;'; + $sql = <<<'SQL' + DO $$ + DECLARE + maxrank bigint := (SELECT MAX(id) FROM `_entrytmp`); + rank bigint := (SELECT maxrank - COUNT(*) FROM `_entrytmp`); + BEGIN + INSERT INTO `_entry` + (id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes) + (SELECT rank + row_number() OVER(ORDER BY etmp.date, etmp.id) AS id, guid, title, author, content, + link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes + FROM `_entrytmp` AS etmp + WHERE NOT EXISTS ( + SELECT 1 FROM `_entry` AS ereal + WHERE (etmp.id = ereal.id) OR (etmp.id_feed = ereal.id_feed AND etmp.guid = ereal.guid)) + ORDER BY etmp.date, etmp.id); + DELETE FROM `_entrytmp` WHERE id <= maxrank; + END $$; + SQL; $hadTransaction = $this->pdo->inTransaction(); if (!$hadTransaction) { $this->pdo->beginTransaction(); |