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, 109 insertions, 13 deletions

diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index dd49b542d..1b8717e83 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -4,15 +4,81 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 
 	const DEFAULTCATEGORYID = 1;
 
+	protected function addColumn($name) {
+		Minz_Log::warning(__method__ . ': ' . $name);
+		try {
+			if ('attributes' === $name) {	//v1.15.0
+				$ok = $this->pdo->exec('ALTER TABLE `_category` ADD COLUMN attributes TEXT') !== false;
+
+				$stm = $this->pdo->query('SELECT * FROM `_feed`');
+				$feeds = $stm->fetchAll(PDO::FETCH_ASSOC);
+
+				$stm = $this->pdo->prepare('UPDATE `_feed` SET attributes = :attributes WHERE id = :id');
+				foreach ($feeds as $feed) {
+					if (empty($feed['keep_history']) || empty($feed['id'])) {
+						continue;
+					}
+					$keepHistory = $feed['keep_history'];
+					$attributes = empty($feed['attributes']) ? [] : json_decode($feed['attributes'], true);
+					if (is_string($attributes)) {	//Legacy risk of double-encoding
+						$attributes = json_decode($attributes, true);
+					}
+					if (!is_array($attributes)) {
+						$attributes = [];
+					}
+					if ($keepHistory > 0) {
+						$attributes['archiving']['keep_min'] = intval($keepHistory);
+					} elseif ($keepHistory == -1) {	//Infinite
+						$attributes['archiving']['keep_period'] = false;
+						$attributes['archiving']['keep_max'] = false;
+						$attributes['archiving']['keep_min'] = false;
+					} else {
+						continue;
+					}
+					$stm->bindValue(':id', $feed['id'], PDO::PARAM_INT);
+					$stm->bindValue(':attributes', json_encode($attributes, JSON_UNESCAPED_SLASHES));
+					$stm->execute();
+				}
+
+				if ($this->pdo->dbType() !== 'sqlite') {	//SQLite does not support DROP COLUMN
+					$this->pdo->exec('ALTER TABLE `_feed` DROP COLUMN keep_history');
+				} else {
+					$this->pdo->exec('DROP INDEX IF EXISTS feed_keep_history_index');	//SQLite at least drop index
+				}
+				return $ok;
+			}
+		} catch (Exception $e) {
+			Minz_Log::error(__method__ . ': ' . $e->getMessage());
+		}
+		return false;
+	}
+
+	protected function autoUpdateDb($errorInfo) {
+		if (isset($errorInfo[0])) {
+			if ($errorInfo[0] === FreshRSS_DatabaseDAO::ER_BAD_FIELD_ERROR || $errorInfo[0] === FreshRSS_DatabaseDAOPGSQL::UNDEFINED_COLUMN) {
+				foreach (['attributes'] as $column) {
+					if (stripos($errorInfo[2], $column) !== false) {
+						return $this->addColumn($column);
+					}
+				}
+			}
+		}
+		return false;
+	}
+
 	public function addCategory($valuesTmp) {
-		$sql = 'INSERT INTO `_category`(name) '
-		     . 'SELECT * FROM (SELECT TRIM(?)) c2 '	//TRIM() to provide a type hint as text for PostgreSQL
+		$sql = 'INSERT INTO `_category`(name, attributes) '
+		     . 'SELECT * FROM (SELECT TRIM(?), ?) c2 '	//TRIM() to provide a type hint as text for PostgreSQL
 		     . 'WHERE NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = TRIM(?))';	//No tag of the same name
 		$stm = $this->pdo->prepare($sql);
 
 		$valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8');
+		if (!isset($valuesTmp['attributes'])) {
+			$valuesTmp['attributes'] = [];
+		}
 		$values = array(
 			$valuesTmp['name'],
+			is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] : json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES),
 			$valuesTmp['name'],
 		);
 
@@ -20,7 +86,10 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 			return $this->pdo->lastInsertId('`_category_id_seq`');
 		} else {
 			$info = $stm == null ? $this->pdo->errorInfo() : $stm->errorInfo();
-			Minz_Log::error('SQL error addCategory: ' . $info[2]);
+			if ($this->autoUpdateDb($info)) {
+				return $this->addCategory($valuesTmp);
+			}
+			Minz_Log::error('SQL error addCategory: ' . json_encode($info));
 			return false;
 		}
 	}
@@ -39,13 +108,17 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 	}
 
 	public function updateCategory($id, $valuesTmp) {
-		$sql = 'UPDATE `_category` SET name=? WHERE id=? '
+		$sql = 'UPDATE `_category` SET name=?, attributes=? WHERE id=? '
 		     . 'AND NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = ?)';	//No tag of the same name
 		$stm = $this->pdo->prepare($sql);
 
 		$valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8');
+		if (!isset($valuesTmp['attributes'])) {
+			$valuesTmp['attributes'] = [];
+		}
 		$values = array(
 			$valuesTmp['name'],
+			is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] : json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES),
 			$id,
 			$valuesTmp['name'],
 		);
@@ -54,7 +127,10 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 			return $stm->rowCount();
 		} else {
 			$info = $stm == null ? $this->pdo->errorInfo() : $stm->errorInfo();
-			Minz_Log::error('SQL error updateCategory: ' . $info[2]);
+			if ($this->autoUpdateDb($info)) {
+				return $this->updateCategory($valuesTmp);
+			}
+			Minz_Log::error('SQL error updateCategory: ' . json_encode($info));
 			return false;
 		}
 	}
@@ -70,16 +146,27 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 			return $stm->rowCount();
 		} else {
 			$info = $stm == null ? $this->pdo->errorInfo() : $stm->errorInfo();
-			Minz_Log::error('SQL error deleteCategory: ' . $info[2]);
+			Minz_Log::error('SQL error deleteCategory: ' . json_encode($info));
 			return false;
 		}
 	}
 
 	public function selectAll() {
-		$sql = 'SELECT id, name FROM `_category`';
+		$sql = 'SELECT id, name, attributes FROM `_category`';
 		$stm = $this->pdo->query($sql);
-		while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {
-			yield $row;
+		if ($stm != false) {
+			while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {
+				yield $row;
+			}
+		} else {
+			$info = $this->pdo->errorInfo();
+			if ($this->autoUpdateDb($info)) {
+				foreach ($this->selectAll() as $category) {	// `yield from` requires PHP 7+
+					yield $category;
+				}
+			}
+			Minz_Log::error(__method__ . ' error: ' . json_encode($info));
+			return false;
 		}
 	}
 
@@ -116,7 +203,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 
 	public function listCategories($prePopulateFeeds = true, $details = false) {
 		if ($prePopulateFeeds) {
-			$sql = 'SELECT c.id AS c_id, c.name AS c_name, '
+			$sql = 'SELECT c.id AS c_id, c.name AS c_name, c.attributes AS c_attributes, '
 			     . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.`cache_nbEntries`, f.`cache_nbUnreads`, f.ttl ')
 			     . 'FROM `_category` c '
 			     . 'LEFT OUTER JOIN `_feed` f ON f.category=c.id '
@@ -124,9 +211,17 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 			     . 'GROUP BY f.id, c_id '
 			     . 'ORDER BY c.name, f.name';
 			$stm = $this->pdo->prepare($sql);
-			$stm->bindValue(':priority_normal', FreshRSS_Feed::PRIORITY_NORMAL, PDO::PARAM_INT);
-			$stm->execute();
-			return self::daoToCategoryPrepopulated($stm->fetchAll(PDO::FETCH_ASSOC));
+			$values = [ ':priority_normal' => FreshRSS_Feed::PRIORITY_NORMAL ];
+			if ($stm && $stm->execute($values)) {
+				return self::daoToCategoryPrepopulated($stm->fetchAll(PDO::FETCH_ASSOC));
+			} else {
+				$info = $stm == null ? $this->pdo->errorInfo() : $stm->errorInfo();
+				if ($this->autoUpdateDb($info)) {
+					return $this->listCategories($prePopulateFeeds, $details);
+				}
+				Minz_Log::error('SQL error listCategories: ' . json_encode($info));
+				return false;
+			}
 		} else {
 			$sql = 'SELECT * FROM `_category` ORDER BY name';
 			$stm = $this->pdo->query($sql);
@@ -282,6 +377,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 				$dao['name']
 			);
 			$cat->_id($dao['id']);
+			$cat->_attributes('', isset($dao['attributes']) ? $dao['attributes'] : '');
 			$cat->_isDefault(static::DEFAULTCATEGORYID === intval($dao['id']));
 			$list[$key] = $cat;
 		}