From cc0db9af4f980829faa4bf0960617807b32fb4fa Mon Sep 17 00:00:00 2001 From: Alexis Degrugillier Date: Wed, 23 Oct 2019 00:52:15 +0200 Subject: Feature/new archiving (#2335) * Change archiving config page layout I've changed some wording and moved actions into a maintenance section. * Update purge action Now we have more control on the purge action. The configuration allows us to choose what to keep and what to discard in a more precise way. At the moment, the configuration applies for all feeds. * Add purge configuration on feed level Now the extend purge configuration is available on feed level. It is stored as attributes and will be used in the purge action. * Update purge action Now the purge action uses the feed configuration if it exists and defaults on user configuration if not. * Add empty option in period list * Fix configuration warnings * Add archiving configuration on categories See #2369 * Add user info back * Add explanations in UI * Fixes for SQLite + error + misc. * Fix invalid feed reference * Short array syntax Only for new code, so far * Fix prefix error * Query performance, default values Work in progress * Fix default values and confirm before leaving Form cancel and confirm changes before leaving were broken. And start taking advantage of the short echo syntax `` as we have moved to PHP 5.4+ * More work * Tuning SQL * Fix MariaDB + performance issue * SQL performance * Fix SQLite bug * Fix some attributes JSON encoding bugs Especially for SQLite export/import * More uniform, fix bugs More uniform between global, category, feed settings * Drop special cases for old articles during refresh Instead will use lastSeen date with the new archiving logic. This was generating problems anyway https://github.com/FreshRSS/FreshRSS/issues/2154 * Draft drop index keep_history Not needed anymore * MySQL typo Now properly tested with MySQL, PostgreSQL, SQLite * More work for legacy values Important to avoid overriding user's preference and risking deleting data erroneously * Fix PHP 7.3 / 7.4 warnings @aledeg "Trying to use values of type null, bool, int, float or resource as an array (such as $null["key"]) will now generate a notice. " https://php.net/migration74.incompatible * Reintroduce min articles and take care of legacy parameters * A few changes forgotten * Draft of migration + DROP of feed.keep_history * Fix several errors And give up using const for SQL to allow multiple database types (and we cannot redefine a const) * Add keep_min to categories + factorise archiving logic * Legacy fix * Fix bug yield from * Minor: Use JSON_UNESCAPED_SLASHE for attributes And make more uniform * Fix sign and missing variable * Fine tune the logic --- app/Models/EntryDAO.php | 65 ++++++++++++++++++++++++++++++++++--------------- 1 file changed, 45 insertions(+), 20 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 5ff3a5b70..6a8a25b3e 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -26,9 +26,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->pdo->commit(); } try { - require_once(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php'); + require(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php'); Minz_Log::warning('SQL CREATE TABLE entrytmp...'); - $ok = $this->pdo->exec(SQL_CREATE_TABLE_ENTRYTMP . SQL_CREATE_INDEX_ENTRY_1) !== false; + $ok = $this->pdo->exec($SQL_CREATE_TABLE_ENTRYTMP . $SQL_CREATE_INDEX_ENTRY_1) !== false; } catch (Exception $ex) { Minz_Log::error(__method__ . ' error: ' . $ex->getMessage()); } @@ -544,32 +544,57 @@ SQL; return $affected; } - public function cleanOldEntries($id_feed, $date_min, $keep = 15) { //Remember to call updateCachedValue($id_feed) or updateCachedValues() just after - $sql = 'DELETE FROM `_entry` ' - . 'WHERE id_feed=:id_feed1 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 `_entry` e3 WHERE e3.id_feed=:id_feed2) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance - . 'AND id NOT IN (SELECT id_entry FROM `_entrytag`) ' //Do not purge tagged entries - . 'AND id NOT IN (SELECT id FROM (SELECT e2.id FROM `_entry` e2 WHERE e2.id_feed=:id_feed3 ORDER BY id DESC LIMIT :keep) keep)'; //Double select: MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery' - $stm = $this->pdo->prepare($sql); + public function cleanOldEntries($id_feed, $options = []) { //Remember to call updateCachedValue($id_feed) or updateCachedValues() just after + $sql = 'DELETE FROM `_entry` WHERE id_feed = :id_feed1'; //No alias for MySQL / MariaDB + $params = []; + $params[':id_feed1'] = $id_feed; - if ($stm) { - $id_max = intval($date_min) . '000000'; - $stm->bindParam(':id_max', $id_max, PDO::PARAM_STR); - $stm->bindParam(':id_feed1', $id_feed, PDO::PARAM_INT); - $stm->bindParam(':id_feed2', $id_feed, PDO::PARAM_INT); - $stm->bindParam(':id_feed3', $id_feed, PDO::PARAM_INT); - $stm->bindParam(':keep', $keep, PDO::PARAM_INT); + //==Exclusions== + if (!empty($options['keep_favourites'])) { + $sql .= ' AND is_favorite = 0'; + } + if (!empty($options['keep_unreads'])) { + $sql .= ' AND is_read = 1'; + } + if (!empty($options['keep_labels'])) { + $sql .= ' AND NOT EXISTS (SELECT 1 FROM `_entrytag` WHERE id_entry = id)'; + } + if (!empty($options['keep_min']) && $options['keep_min'] > 0) { + $sql .= ' AND `lastSeen` < (SELECT e2.`lastSeen` FROM `_entry` e2 WHERE e2.id_feed = :id_feed2' + . ' ORDER BY e2.`lastSeen` DESC LIMIT 1 OFFSET :keep_min)'; + $params[':id_feed2'] = $id_feed; + $params[':keep_min'] = (int)$options['keep_min']; } + //Keep at least the articles seen at the last refresh + $sql .= ' AND `lastSeen` < (SELECT MAX(e3.`lastSeen`) FROM `_entry` e3 WHERE e3.id_feed = :id_feed3)'; + $params[':id_feed3'] = $id_feed; + + //==Inclusions== + $sql .= ' AND (1=0'; + if (!empty($options['keep_period'])) { + $sql .= ' OR `lastSeen` < :max_last_seen'; + $now = new DateTime('now'); + $now->sub(new DateInterval($options['keep_period'])); + $params[':max_last_seen'] = $now->format('U'); + } + if (!empty($options['keep_max']) && $options['keep_max'] > 0) { + $sql .= ' OR `lastSeen` <= (SELECT e4.`lastSeen` FROM `_entry` e4 WHERE e4.id_feed = :id_feed4' + . ' ORDER BY e4.`lastSeen` DESC LIMIT 1 OFFSET :keep_max)'; + $params[':id_feed4'] = $id_feed; + $params[':keep_max'] = (int)$options['keep_max']; + } + $sql .= ')'; + + $stm = $this->pdo->prepare($sql); - if ($stm && $stm->execute()) { + if ($stm && $stm->execute($params)) { return $stm->rowCount(); } else { $info = $stm == null ? $this->pdo->errorInfo() : $stm->errorInfo(); if ($this->autoUpdateDb($info)) { - return $this->cleanOldEntries($id_feed, $date_min, $keep); + return $this->cleanOldEntries($id_feed, $options); } - Minz_Log::error('SQL error cleanOldEntries: ' . $info[2]); + Minz_Log::error(__method__ . ' error:' . json_encode($info)); return false; } } -- cgit v1.2.3