From 993466844405bd9854d890d6d5ebf763ed8b78cb Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 9 May 2015 23:37:56 +0200 Subject: SQL: more robust purge https://github.com/FreshRSS/FreshRSS/issues/798 https://github.com/FreshRSS/FreshRSS/issues/493 --- app/Models/FeedDAO.php | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index f48beee6e..c13e2b008 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -322,10 +322,12 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $affected; } - public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) just after + public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) or updateCachedValues() just after $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' - . 'WHERE id_feed = :id_feed AND id <= :id_max AND is_favorite=0 AND id NOT IN ' - . '(SELECT id FROM (SELECT e2.id FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed = :id_feed ORDER BY id DESC LIMIT :keep) keep)'; //Double select MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' + . 'WHERE id_feed = :id_feed AND id <= :id_max ' + . 'AND is_favorite=0 ' //Do not remove favourites + . 'AND lastSeen < (SELECT maxLastSeen FROM (SELECT (MAX(e3.lastSeen) - 99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed = :id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance + . 'AND id NOT IN (SELECT id FROM (SELECT e2.id FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed = :id_feed ORDER BY id DESC LIMIT :keep) keep)'; //Double select: MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery' $stm = $this->bd->prepare($sql); $id_max = intval($date_min) . '000000'; -- cgit v1.2.3