Dynamic OPML (#4407)

* Dynamic OPML draft
#fix https://github.com/FreshRSS/FreshRSS/issues/4191

* Export dynamic OPML
http://opml.org/spec2.opml#1629043127000

* Restart with simpler approach

* Minor revert

* Export dynamic OPML also for single feeds

* Special category type for importing dynamic OPML

* Parameter for excludeMutedFeeds

* Details

* More draft

* i18n

* Fix update

* Draft manual import working

* Working manual refresh

* Draft automatic update

* Working Web refresh + fixes

* Import/export dynamic OPML settings

* Annoying numerous lines in SQL logs

* Fix minor JavaScript error

* Fix auto adding new columns

* Add require

* Add missing 🗲

* Missing space

* Disable adding new feeds to dynamic categories

* Link from import

* i18n typo

* Improve theme icon function

* Fix pink-dark

1 files changed, 87 insertions, 16 deletions

diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index 18747c906..cef8e6d63 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -17,7 +17,13 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 	protected function addColumn($name) {
 		Minz_Log::warning(__method__ . ': ' . $name);
 		try {
-			if ('attributes' === $name) {	//v1.15.0
+			if ($name === 'kind') {	//v1.20.0
+				return $this->pdo->exec('ALTER TABLE `_category` ADD COLUMN kind SMALLINT DEFAULT 0') !== false;
+			} elseif ($name === 'lastUpdate') {	//v1.20.0
+				return $this->pdo->exec('ALTER TABLE `_category` ADD COLUMN `lastUpdate` BIGINT DEFAULT 0') !== false;
+			} elseif ($name === 'error') {	//v1.20.0
+				return $this->pdo->exec('ALTER TABLE `_category` ADD COLUMN error SMALLINT DEFAULT 0') !== false;
+			} elseif ('attributes' === $name) {	//v1.15.0
 				$ok = $this->pdo->exec('ALTER TABLE `_category` ADD COLUMN attributes TEXT') !== false;
 
 				$stm = $this->pdo->query('SELECT * FROM `_feed`');
@@ -69,8 +75,9 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 	protected function autoUpdateDb(array $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) {
+				$errorLines = explode("\n", $errorInfo[2], 2);	// The relevant column name is on the first line, other lines are noise
+				foreach (['kind', 'lastUpdate', 'error', 'attributes'] as $column) {
+					if (stripos($errorLines[0], $column) !== false) {
 						return $this->addColumn($column);
 					}
 				}
@@ -79,12 +86,13 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		return false;
 	}
 
+	/** @return int|false */
 	public function addCategory($valuesTmp) {
 		// TRIM() to provide a type hint as text
 		// No tag of the same name
 		$sql = <<<'SQL'
-INSERT INTO `_category`(name, attributes)
-SELECT * FROM (SELECT TRIM(?) AS name, TRIM(?) AS attributes) c2
+INSERT INTO `_category`(kind, name, attributes)
+SELECT * FROM (SELECT ABS(?) AS kind, TRIM(?) AS name, TRIM(?) AS attributes) c2
 WHERE NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = TRIM(?))
 SQL;
 		$stm = $this->pdo->prepare($sql);
@@ -94,6 +102,7 @@ SQL;
 			$valuesTmp['attributes'] = [];
 		}
 		$values = array(
+			$valuesTmp['kind'] ?? FreshRSS_Category::KIND_NORMAL,
 			$valuesTmp['name'],
 			is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] : json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES),
 			$valuesTmp['name'],
@@ -111,13 +120,18 @@ SQL;
 		}
 	}
 
+	/**
+	 * @param FreshRSS_Category $category
+	 * @return int|false
+	 */
 	public function addCategoryObject($category) {
 		$cat = $this->searchByName($category->name());
 		if (!$cat) {
-			// Category does not exist yet in DB so we add it before continue
-			$values = array(
+			$values = [
+				'kind' => $category->kind(),
 				'name' => $category->name(),
-			);
+				'attributes' => $category->attributes(),
+			];
 			return $this->addCategory($values);
 		}
 
@@ -127,7 +141,7 @@ SQL;
 	public function updateCategory($id, $valuesTmp) {
 		// No tag of the same name
 		$sql = <<<'SQL'
-UPDATE `_category` SET name=?, attributes=? WHERE id=?
+UPDATE `_category` SET name=?, kind=?, attributes=? WHERE id=?
 AND NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = ?)
 SQL;
 		$stm = $this->pdo->prepare($sql);
@@ -138,6 +152,7 @@ SQL;
 		}
 		$values = array(
 			$valuesTmp['name'],
+			$valuesTmp['kind'] ?? FreshRSS_Category::KIND_NORMAL,
 			is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] : json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES),
 			$id,
 			$valuesTmp['name'],
@@ -155,6 +170,24 @@ SQL;
 		}
 	}
 
+	public function updateLastUpdate(int $id, bool $inError = false, int $mtime = 0) {
+		$sql = 'UPDATE `_category` SET `lastUpdate`=?, error=? WHERE id=?';
+		$values = [
+			$mtime <= 0 ? time() : $mtime,
+			$inError ? 1 : 0,
+			$id,
+		];
+		$stm = $this->pdo->prepare($sql);
+
+		if ($stm && $stm->execute($values)) {
+			return $stm->rowCount();
+		} else {
+			$info = $stm == null ? $this->pdo->errorInfo() : $stm->errorInfo();
+			Minz_Log::warning(__METHOD__ . ' error: ' . $sql . ' : ' . json_encode($info));
+			return false;
+		}
+	}
+
 	public function deleteCategory($id) {
 		if ($id <= self::DEFAULTCATEGORYID) {
 			return false;
@@ -172,7 +205,7 @@ SQL;
 	}
 
 	public function selectAll() {
-		$sql = 'SELECT id, name, attributes FROM `_category`';
+		$sql = 'SELECT id, name, kind, `lastUpdate`, error, attributes FROM `_category`';
 		$stm = $this->pdo->query($sql);
 		if ($stm != false) {
 			while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {
@@ -181,15 +214,14 @@ SQL;
 		} else {
 			$info = $this->pdo->errorInfo();
 			if ($this->autoUpdateDb($info)) {
-				foreach ($this->selectAll() as $category) {	// `yield from` requires PHP 7+
-					yield $category;
-				}
+				yield from $this->selectAll();
 			}
 			Minz_Log::error(__method__ . ' error: ' . json_encode($info));
 			yield false;
 		}
 	}
 
+	/** @return FreshRSS_Category|null */
 	public function searchById($id) {
 		$sql = 'SELECT * FROM `_category` WHERE id=:id';
 		$stm = $this->pdo->prepare($sql);
@@ -204,7 +236,9 @@ SQL;
 			return null;
 		}
 	}
-	public function searchByName($name) {
+
+	/** @return FreshRSS_Category|null|false */
+	public function searchByName(string $name) {
 		$sql = 'SELECT * FROM `_category` WHERE name=:name';
 		$stm = $this->pdo->prepare($sql);
 		if ($stm == false) {
@@ -246,7 +280,7 @@ SQL;
 
 	public function listCategories($prePopulateFeeds = true, $details = false) {
 		if ($prePopulateFeeds) {
-			$sql = 'SELECT c.id AS c_id, c.name AS c_name, c.attributes AS c_attributes, '
+			$sql = 'SELECT c.id AS c_id, c.name AS c_name, c.kind AS c_kind, c.`lastUpdate` AS c_last_update, c.error AS c_error, 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 '
@@ -272,6 +306,27 @@ SQL;
 		}
 	}
 
+	/** @return array<FreshRSS_Category> */
+	public function listCategoriesOrderUpdate(int $defaultCacheDuration = 86400, int $limit = 0) {
+		$sql = 'SELECT * FROM `_category` WHERE kind = :kind AND `lastUpdate` < :lu ORDER BY `lastUpdate`'
+			. ($limit < 1 ? '' : ' LIMIT ' . intval($limit));
+		$stm = $this->pdo->prepare($sql);
+		if ($stm &&
+			$stm->bindValue(':kind', FreshRSS_Category::KIND_DYNAMIC_OPML, PDO::PARAM_INT) &&
+			$stm->bindValue(':lu', time() - $defaultCacheDuration, PDO::PARAM_INT) &&
+			$stm->execute()) {
+			return self::daoToCategory($stm->fetchAll(PDO::FETCH_ASSOC));
+		} else {
+			$info = $stm ? $stm->errorInfo() : $this->pdo->errorInfo();
+			if ($this->autoUpdateDb($info)) {
+				return $this->listCategoriesOrderUpdate($defaultCacheDuration, $limit);
+			}
+			Minz_Log::warning(__METHOD__ . ' error: ' . $sql . ' : ' . json_encode($info));
+			return [];
+		}
+	}
+
+	/** @return FreshRSS_Category|null */
 	public function getDefault() {
 		$sql = 'SELECT * FROM `_category` WHERE id=:id';
 		$stm = $this->pdo->prepare($sql);
@@ -290,6 +345,8 @@ SQL;
 			return null;
 		}
 	}
+
+	/** @return int|bool */
 	public function checkDefault() {
 		$def_cat = $this->searchById(self::DEFAULTCATEGORYID);
 
@@ -345,6 +402,10 @@ SQL;
 		return $res[0]['count'];
 	}
 
+	/**
+	 * @param array<FreshRSS_Category> $categories
+	 * @param int $feed_id
+	 */
 	public static function findFeed($categories, $feed_id) {
 		foreach ($categories as $category) {
 			foreach ($category->feeds() as $feed) {
@@ -356,6 +417,10 @@ SQL;
 		return null;
 	}
 
+	/**
+	 * @param array<FreshRSS_Category> $categories
+	 * @param int $minPriority
+	 */
 	public static function CountUnreads($categories, $minPriority = 0) {
 		$n = 0;
 		foreach ($categories as $category) {
@@ -386,6 +451,7 @@ SQL;
 					$feedDao->daoToFeed($feedsDao, $previousLine['c_id'])
 				);
 				$cat->_id($previousLine['c_id']);
+				$cat->_kind($previousLine['c_kind']);
 				$cat->_attributes('', $previousLine['c_attributes']);
 				$list[$previousLine['c_id']] = $cat;
 
@@ -403,6 +469,9 @@ SQL;
 				$feedDao->daoToFeed($feedsDao, $previousLine['c_id'])
 			);
 			$cat->_id($previousLine['c_id']);
+			$cat->_kind($previousLine['c_kind']);
+			$cat->_lastUpdate($previousLine['c_last_update'] ?? 0);
+			$cat->_error($previousLine['c_error'] ?? false);
 			$cat->_attributes('', $previousLine['c_attributes']);
 			$list[$previousLine['c_id']] = $cat;
 		}
@@ -422,8 +491,10 @@ SQL;
 				$dao['name']
 			);
 			$cat->_id($dao['id']);
+			$cat->_kind($dao['kind']);
+			$cat->_lastUpdate($dao['lastUpdate'] ?? 0);
+			$cat->_error($dao['error'] ?? false);
 			$cat->_attributes('', isset($dao['attributes']) ? $dao['attributes'] : '');
-			$cat->_isDefault(static::DEFAULTCATEGORYID === intval($dao['id']));
 			$list[$key] = $cat;
 		}