From b552abb3327f09baa1c0f4e821dc9f6bd6ef738e Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 1 May 2018 17:02:11 +0200 Subject: JSON column for feeds (#1838) * Draft of JSON column for feeds https://github.com/FreshRSS/FreshRSS/issues/1654 * Add some per-feed options * Feed cURL timeout * Mark updated articles as read https://github.com/FreshRSS/FreshRSS/issues/891 * Mark as read upon reception https://github.com/FreshRSS/FreshRSS/issues/1702 * Ignore SSL (unsafe) https://github.com/FreshRSS/FreshRSS/issues/1811 * Try PHPCS workaround While waiting for a better syntax support --- app/Models/DatabaseDAO.php | 2 +- app/Models/Factory.php | 8 ++++- app/Models/Feed.php | 26 +++++++++++++++- app/Models/FeedDAO.php | 73 +++++++++++++++++++++++++++++++++++++++----- app/Models/FeedDAOSQLite.php | 17 +++++++++++ 5 files changed, 115 insertions(+), 11 deletions(-) create mode 100644 app/Models/FeedDAOSQLite.php (limited to 'app/Models') diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index f5469f2b7..b8e5577e4 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -50,7 +50,7 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { public function feedIsCorrect() { return $this->checkTable('feed', array( 'id', 'url', 'category', 'name', 'website', 'description', 'lastUpdate', - 'priority', 'pathEntries', 'httpAuth', 'error', 'keep_history', 'ttl', + 'priority', 'pathEntries', 'httpAuth', 'error', 'keep_history', 'ttl', 'attributes', 'cache_nbEntries', 'cache_nbUnreads' )); } diff --git a/app/Models/Factory.php b/app/Models/Factory.php index dfccc883e..764987c46 100644 --- a/app/Models/Factory.php +++ b/app/Models/Factory.php @@ -3,7 +3,13 @@ class FreshRSS_Factory { public static function createFeedDao($username = null) { - return new FreshRSS_FeedDAO($username); + $conf = Minz_Configuration::get('system'); + switch ($conf->db['type']) { + case 'sqlite': + return new FreshRSS_FeedDAOSQLite($username); + default: + return new FreshRSS_FeedDAO($username); + } } public static function createEntryDao($username = null) { diff --git a/app/Models/Feed.php b/app/Models/Feed.php index 196d94931..04101c10d 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -26,6 +26,7 @@ class FreshRSS_Feed extends Minz_Model { private $error = false; private $keep_history = self::KEEP_HISTORY_DEFAULT; private $ttl = self::TTL_DEFAULT; + private $attributes = array(); private $mute = false; private $hash = null; private $lockPath = ''; @@ -114,6 +115,13 @@ class FreshRSS_Feed extends Minz_Model { public function ttl() { return $this->ttl; } + public function attributes($key = '') { + if ($key == '') { + return $this->attributes; + } else { + return isset($this->attributes[$key]) ? $this->attributes[$key] : null; + } + } public function mute() { return $this->mute; } @@ -234,6 +242,22 @@ class FreshRSS_Feed extends Minz_Model { $this->ttl = abs($value); $this->mute = $value < self::TTL_DEFAULT; } + + public function _attributes($key, $value) { + if ($key == '') { + if (is_string($value)) { + $value = json_decode($value, true); + } + if (is_array($value)) { + $this->attributes = $value; + } + } elseif ($value === null) { + unset($this->attributes[$key]); + } else { + $this->attributes[$key] = $value; + } + } + public function _nbNotRead($value) { $this->nbNotRead = intval($value); } @@ -253,7 +277,7 @@ class FreshRSS_Feed extends Minz_Model { if ($this->httpAuth != '') { $url = preg_replace('#((.+)://)(.+)#', '${1}' . $this->httpAuth . '@${3}', $url); } - $feed = customSimplePie(); + $feed = customSimplePie($this->attributes()); if (substr($url, -11) === '#force_feed') { $feed->force_feed(true); $url = substr($url, 0, -11); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 5c6e613d3..f968ae98b 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -1,6 +1,33 @@ bd->prepare('ALTER TABLE `' . $this->prefix . 'feed` ADD COLUMN attributes TEXT'); + return $stm && $stm->execute(); + } + } catch (Exception $e) { + Minz_Log::error('FreshRSS_FeedDAO::addColumn error: ' . $e->getMessage()); + } + return false; + } + + protected function autoUpdateDb($errorInfo) { + if (isset($errorInfo[0])) { + if ($errorInfo[0] === '42S22' || $errorInfo[0] === '42703') { //ER_BAD_FIELD_ERROR (Mysql), undefined_column (PostgreSQL) + foreach (array('attributes') as $column) { + if (stripos($errorInfo[2], $column) !== false) { + return $this->addColumn($column); + } + } + } + } + return false; + } + public function addFeed($valuesTmp) { $sql = ' INSERT INTO `' . $this->prefix . 'feed` @@ -15,10 +42,11 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { `httpAuth`, error, keep_history, - ttl + ttl, + attributes ) VALUES - (?, ?, ?, ?, ?, ?, 10, ?, 0, ?, ?)'; + (?, ?, ?, ?, ?, ?, 10, ?, 0, ?, ?, ?)'; $stm = $this->bd->prepare($sql); $valuesTmp['url'] = safe_ascii($valuesTmp['url']); @@ -34,12 +62,16 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { base64_encode($valuesTmp['httpAuth']), FreshRSS_Feed::KEEP_HISTORY_DEFAULT, FreshRSS_Feed::TTL_DEFAULT, + isset($valuesTmp['attributes']) ? json_encode($valuesTmp['attributes']) : '', ); if ($stm && $stm->execute($values)) { return $this->bd->lastInsertId('"' . $this->prefix . 'feed_id_seq"'); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoUpdateDb($info)) { + return $this->addFeed($valuesTmp); + } Minz_Log::error('SQL error addFeed: ' . $info[2]); return false; } @@ -60,7 +92,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { 'website' => $feed->website(), 'description' => $feed->description(), 'lastUpdate' => 0, - 'httpAuth' => $feed->httpAuth() + 'httpAuth' => $feed->httpAuth(), + 'attributes' => $feed->attributes(), ); $id = $this->addFeed($values); @@ -87,8 +120,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { foreach ($valuesTmp as $key => $v) { $set .= '`' . $key . '`=?, '; - if ($key == 'httpAuth') { + if ($key === 'httpAuth') { $valuesTmp[$key] = base64_encode($v); + } elseif ($key === 'attributes') { + $valuesTmp[$key] = json_encode($v); } } $set = substr($set, 0, -2); @@ -105,11 +140,25 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoUpdateDb($info)) { + return $this->updateFeed($id, $valuesTmp); + } Minz_Log::error('SQL error updateFeed: ' . $info[2] . ' for feed ' . $id); return false; } } + public function updateFeedAttribute($feed, $key, $value) { + if ($feed instanceof FreshRSS_Feed) { + $feed->_attributes($key, $value); + return $this->updateFeed( + $feed->id(), + array('attributes' => $feed->attributes()) + ); + } + return false; + } + public function updateLastUpdate($id, $inError = false, $mtime = 0) { //See also updateCachedValue() $sql = 'UPDATE `' . $this->prefix . 'feed` ' . 'SET `lastUpdate`=?, error=? ' @@ -252,15 +301,22 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { */ public function listFeedsOrderUpdate($defaultCacheDuration = 3600) { $this->updateTTL(); - $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl ' + $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl, attributes ' . 'FROM `' . $this->prefix . 'feed` ' . ($defaultCacheDuration < 0 ? '' : 'WHERE ttl >= ' . FreshRSS_Feed::TTL_DEFAULT . ' AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=' . FreshRSS_Feed::TTL_DEFAULT . ' THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ') . 'ORDER BY `lastUpdate`'; $stm = $this->bd->prepare($sql); - $stm->execute(); - - return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); + if ($stm && $stm->execute()) { + return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); + } else { + $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoUpdateDb($info)) { + return $this->listFeedsOrderUpdate($defaultCacheDuration); + } + Minz_Log::error('SQL error listFeedsOrderUpdate: ' . $info[2]); + return array(); + } } public function listByCategory($cat) { @@ -385,6 +441,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $myFeed->_error(isset($dao['error']) ? $dao['error'] : 0); $myFeed->_keepHistory(isset($dao['keep_history']) ? $dao['keep_history'] : FreshRSS_Feed::KEEP_HISTORY_DEFAULT); $myFeed->_ttl(isset($dao['ttl']) ? $dao['ttl'] : FreshRSS_Feed::TTL_DEFAULT); + $myFeed->_attributes('', isset($dao['attributes']) ? $dao['attributes'] : ''); $myFeed->_nbNotRead(isset($dao['cache_nbUnreads']) ? $dao['cache_nbUnreads'] : 0); $myFeed->_nbEntries(isset($dao['cache_nbEntries']) ? $dao['cache_nbEntries'] : 0); if (isset($dao['id'])) { diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php new file mode 100644 index 000000000..3c203b378 --- /dev/null +++ b/app/Models/FeedDAOSQLite.php @@ -0,0 +1,17 @@ +bd->query("PRAGMA table_info('feed')")) { + $columns = $tableInfo->fetchAll(PDO::FETCH_COLUMN, 1); + foreach (array('attributes') as $column) { + if (!in_array($column, $columns)) { + return $this->addColumn($column); + } + } + } + return false; + } + +} -- cgit v1.2.3