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

1 files changed, 45 insertions, 20 deletions

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;
 		}
 	}