From 7c1b5e322cca0134f57b3a436129985ba9170b9f Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 2 Aug 2016 22:49:35 +0200 Subject: PostgreSQL draft https://github.com/FreshRSS/FreshRSS/issues/416 Based on @Damstre work https://github.com/FreshRSS/FreshRSS/pull/1071 Not tested --- app/Models/EntryDAO.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index c9e6f9742..ba52d3f15 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -3,7 +3,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function isCompressed() { - return parent::$sharedDbType !== 'sqlite'; + return parent::$sharedDbType === 'mysql'; } public function hasNativeHex() { -- cgit v1.2.3 From c25fdbcc0990b637e305665a456e52e1aa3dec0a Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 23 Aug 2016 00:02:54 +0200 Subject: More PostgreSQL --- app/Models/EntryDAO.php | 75 ++++++++++++++--------- app/Models/EntryDAOPGSQL.php | 79 +++--------------------- app/Models/EntryDAOSQLite.php | 4 ++ app/SQL/install.sql.pgsql.php | 136 +++++++++++++++++------------------------- app/install.php | 26 ++------ lib/Minz/ModelPdo.php | 29 +++++---- 6 files changed, 138 insertions(+), 211 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 3a1dc1ba5..8d136cd6c 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -10,6 +10,14 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return parent::$sharedDbType !== 'sqlite'; } + public function sqlHexDecode($x) { + return 'X' . $x; + } + + public function sqlHexEncode($x) { + return 'hex(' . $x . ')'; + } + protected function addColumn($name) { Minz_Log::warning('FreshRSS_EntryDAO::addColumn: ' . $name); $hasTransaction = false; @@ -106,31 +114,42 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'INSERT INTO `' . $this->prefix . 'entry` (id, guid, title, author, ' . ($this->isCompressed() ? 'content_bin' : 'content') . ', link, date, lastSeen, hash, is_read, is_favorite, id_feed, tags) ' - . 'VALUES(?, ?, ?, ?, ' - . ($this->isCompressed() ? 'COMPRESS(?)' : '?') - . ', ?, ?, ?, ' - . ($this->hasNativeHex() ? 'X?' : '?') - . ', ?, ?, ?, ?)'; + . 'VALUES(:id, :guid, :title, :author, ' + . ($this->isCompressed() ? 'COMPRESS(:content)' : ':content') + . ', :link, :date, :last_seen, ' + . $this->sqlHexDecode(':hash') + . ', :is_read, :is_favorite, :id_feed, :tags)'; $this->addEntryPrepared = $this->bd->prepare($sql); } + $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); + $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); + $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); + $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); + $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); + $valuesTmp['author'] = substr($valuesTmp['author'], 0, 255); + $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); + $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); + $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); + $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); + $valuesTmp['lastSeen'] = time(); + $this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT); + $valuesTmp['is_read'] = $valuesTmp['is_read'] ? 1 : 0; + $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT); + $valuesTmp['is_favorite'] = $valuesTmp['is_favorite'] ? 1 : 0; + $this->addEntryPrepared->bindParam(':is_favorite', $valuesTmp['is_favorite'], PDO::PARAM_INT); + $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); + $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023); + $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']); + + if ($this->hasNativeHex()) { + $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hash']); + } else { + $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); + $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); // X'09AF' hexadecimal literals do not work with SQLite/PDO //hex2bin() is PHP5.4+ + } - $values = array( - $valuesTmp['id'], - substr($valuesTmp['guid'], 0, 760), - substr($valuesTmp['title'], 0, 255), - substr($valuesTmp['author'], 0, 255), - $valuesTmp['content'], - substr($valuesTmp['link'], 0, 1023), - $valuesTmp['date'], - time(), - $this->hasNativeHex() ? $valuesTmp['hash'] : pack('H*', $valuesTmp['hash']), // X'09AF' hexadecimal literals do not work with SQLite/PDO //hex2bin() is PHP5.4+ - $valuesTmp['is_read'] ? 1 : 0, - $valuesTmp['is_favorite'] ? 1 : 0, - $valuesTmp['id_feed'], - substr($valuesTmp['tags'], 0, 1023), - ); - - if ($this->addEntryPrepared && $this->addEntryPrepared->execute($values)) { + if ($this->addEntryPrepared && $this->addEntryPrepared->execute()) { return $this->bd->lastInsertId(); } else { $info = $this->addEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->addEntryPrepared->errorInfo(); @@ -156,7 +175,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'SET title=?, author=?, ' . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?') . ', link=?, date=?, lastSeen=?, hash=' - . ($this->hasNativeHex() ? 'X?' : '?') + . ($this->hasNativeHex() ? 'X?' : '?') //TODO PostgreSQL . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=?, ') . 'tags=? ' . 'WHERE id_feed=? AND guid=?'; @@ -430,12 +449,12 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $this->bd->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` e ' - . 'SET e.is_read=1 ' - . 'WHERE e.id_feed=? AND e.is_read=0 AND e.id <= ?'; + $sql = 'UPDATE `' . $this->prefix . 'entry` ' + . 'SET is_read=1 ' + . 'WHERE id_feed=? AND is_read=0 AND id <= ?'; $values = array($id_feed, $idMax); - list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filter, $state); + list($searchValues, $search) = $this->sqlListEntriesWhere('', $filter, $state); $stm = $this->bd->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { @@ -658,7 +677,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (count($guids) < 1) { return array(); } - $sql = 'SELECT guid, hex(hash) AS hexHash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hexHash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; $stm = $this->bd->prepare($sql); $values = array($id_feed); $values = array_merge($values, $guids); diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php index 95c12ff5d..b96a62ebc 100644 --- a/app/Models/EntryDAOPGSQL.php +++ b/app/Models/EntryDAOPGSQL.php @@ -1,82 +1,21 @@ bd->beginTransaction(); - - $sql = 'UPDATE "' . $this->prefix . 'entry" ' - . 'SET is_read=:is_read ' - . 'WHERE id_feed=:id_feed AND NOT is_read AND id <= :idmax'; - $values = array($id_feed, $idMax); - $stm = $this->bd->prepare($sql); - $stm->bindValue(':is_read', true, PDO::PARAM_BOOL); - $stm->bindValue(':id_feed', $id_feed); - $stm->bindValue(':idmax', $idMax); - - if (!($stm && $stm->execute())) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error markReadFeed: ' . $info[2]); - $this->bd->rollBack(); - return false; - } - $affected = $stm->rowCount(); - - $this->bd->commit(); - return $affected; + public function sqlHexEncode($x) { + return 'encode(' . $x . ", 'hex')"; } - public function listHashForFeedGuids($id_feed, $guids) { - if (count($guids) < 1) { - return array(); - } - $sql = 'SELECT guid, hash AS hexHash FROM "' . $this->prefix . 'entry" WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; - $stm = $this->bd->prepare($sql); - $values = array($id_feed); - $values = array_merge($values, $guids); - if ($stm && $stm->execute($values)) { - $result = array(); - $rows = $stm->fetchAll(PDO::FETCH_ASSOC); - foreach ($rows as $row) { - $result[$row['guid']] = $row['hexHash']; - } - return $result; - } else { - $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); - if ($this->autoAddColumn($info)) { - return $this->listHashForFeedGuids($id_feed, $guids); - } - Minz_Log::error('SQL error listHashForFeedGuids: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] - . ' while querying feed ' . $id_feed); - return false; - } + protected function autoUpdateDb($errorInfo) { + return false; } - public function optimizeTable() { - return null; + protected function addColumn($name) { + return false; } public function size($all = true) { diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index dad34a93d..80dbcca6b 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -2,6 +2,10 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { + public function sqlHexDecode($x) { + return $x; + } + protected function autoUpdateDb($errorInfo) { if (empty($errorInfo[0]) || $errorInfo[0] == '42S22') { //ER_BAD_FIELD_ERROR //autoAddColumn diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index 0f243bdb5..04e35af68 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -1,91 +1,65 @@ 'SET NAMES utf8mb4', - ); - break; - case 'sqlite': - $str = 'sqlite:' . join_path(USERS_PATH, $_SESSION['default_user'], 'db.sqlite'); - $driver_options = array( - PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, - ); - break; - default: - return false; - } - return new PDO($str, $_SESSION['bd_user'], $_SESSION['bd_password'], $driver_options); -} - function deleteInstall() { $res = unlink(join_path(DATA_PATH, 'do-install.txt')); @@ -444,11 +427,12 @@ function checkBD() { ); try { // on ouvre une connexion juste pour créer la base si elle n'existe pas - $str = 'pgsql:host=' . $_SESSION['bd_host'] . ';'; + $str = 'pgsql:host=' . $_SESSION['bd_host'] . ';dbname=postgres'; $c = new PDO($str, $_SESSION['bd_user'], $_SESSION['bd_password'], $driver_options); $sql = sprintf(SQL_CREATE_DB, $_SESSION['bd_base']); $res = $c->query($sql); } catch (PDOException $e) { + syslog(LOG_DEBUG, 'pgsql ' . $e->getMessage()); } // on écrase la précédente connexion en sélectionnant la nouvelle BDD diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php index b98a26d06..41a9f60bf 100644 --- a/lib/Minz/ModelPdo.php +++ b/lib/Minz/ModelPdo.php @@ -60,17 +60,17 @@ class Minz_ModelPdo { $string = 'mysql:host=' . $db['host'] . ';dbname=' . $db['base'] . ';charset=utf8mb4'; $driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8mb4'; $this->prefix = $db['prefix'] . $currentUser . '_'; - $this->bd = new MinzPDO($string, $db['user'], $db['password'], $driver_options); + $this->bd = new MinzPDOMySql($string, $db['user'], $db['password'], $driver_options); //TODO Consider: $this->bd->exec("SET SESSION sql_mode = 'ANSI_QUOTES';"); break; case 'sqlite': $string = 'sqlite:' . join_path(DATA_PATH, 'users', $currentUser, 'db.sqlite'); $this->prefix = ''; - $this->bd = new MinzPDO($string, $db['user'], $db['password'], $driver_options); + $this->bd = new MinzPDOMSQLite($string, $db['user'], $db['password'], $driver_options); $this->bd->exec('PRAGMA foreign_keys = ON;'); break; case 'pgsql': - $string = 'pgsql:host=' . $db['host'] . ';dbname=' . $db['base'] . ';charset=utf8'; + $string = 'pgsql:host=' . $db['host'] . ';dbname=' . $db['base']; $this->prefix = $db['prefix'] . $currentUser . '_'; $this->bd = new MinzPDOPGSQL($string, $db['user'], $db['password'], $driver_options); $this->bd->exec("SET NAMES 'UTF8';"); @@ -125,33 +125,40 @@ class MinzPDO extends PDO { public function prepare($statement, $driver_options = array()) { MinzPDO::check($statement); - $statement = MinzPDO::compatibility($statement); + $statement = $this->compatibility($statement); return parent::prepare($statement, $driver_options); } public function exec($statement) { MinzPDO::check($statement); - $statement = MinzPDO::compatibility($statement); + $statement = $this->compatibility($statement); return parent::exec($statement); } public function query($statement) { MinzPDO::check($statement); - $statement = MinzPDO::compatibility($statement); + $statement = $this->compatibility($statement); return parent::query($statement); } +} +class MinzPDOMySql extends PDO { public function lastInsertId($name = null) { return parent::lastInsertId(); //We discard the name, only used by PostgreSQL } } -class MinzPDOPGSQL extends MinzPDO { - protected function compatibility($statement) { - return str_replace(array('`', " X'"), array('"', " E'\\x"), $statement); +class MinzPDOMSQLite extends PDO { + public function lastInsertId($name = null) { + return parent::lastInsertId(); //We discard the name, only used by PostgreSQL } +} - public function lastInsertId($name = null) { - return parent::lastInsertId($name); +class MinzPDOPGSQL extends MinzPDO { + protected function compatibility($statement) { + return str_replace( + array('`', 'lastUpdate', 'pathEntries', 'httpAuth', 'cache_nbEntries', 'cache_nbUnreads', 'lastSeen'), + array('"', '"lastUpdate"', '"pathEntries"', '"httpAuth"', '"cache_nbEntries"', '"cache_nbUnreads"', '"lastSeen"'), + $statement); } } -- cgit v1.2.3 From f66be86e41d89214688a28243b412ffa43ce500d Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 31 Aug 2016 21:47:12 +0200 Subject: Quoted upper-cases instead of string replace --- app/Models/CategoryDAO.php | 2 +- app/Models/EntryDAO.php | 14 +++++++------- app/Models/EntryDAOSQLite.php | 4 ++-- app/Models/FeedDAO.php | 22 +++++++++++----------- app/Models/FeedDAOSQLite.php | 4 ++-- lib/Minz/ModelPdo.php | 5 +---- 6 files changed, 24 insertions(+), 27 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 3b1519c20..1dab60ea9 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -100,7 +100,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, ' - . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.cache_nbEntries, f.cache_nbUnreads ') + . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.`cache_nbEntries`, f.`cache_nbUnreads` ') . 'FROM `' . $this->prefix . 'category` c ' . 'LEFT OUTER JOIN `' . $this->prefix . 'feed` f ON f.category=c.id ' . 'GROUP BY f.id, c_id ' diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 8d136cd6c..d39f0237c 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -28,7 +28,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->bd->beginTransaction(); $hasTransaction = true; } - $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN lastSeen INT(11) DEFAULT 0'); + $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN `lastSeen` INT(11) DEFAULT 0'); if ($stm && $stm->execute()) { $stm = $this->bd->prepare('CREATE INDEX entry_lastSeen_index ON `' . $this->prefix . 'entry`(`lastSeen`);'); //"IF NOT EXISTS" does not exist in MySQL 5.7 if ($stm && $stm->execute()) { @@ -113,7 +113,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($this->addEntryPrepared === null) { $sql = 'INSERT INTO `' . $this->prefix . 'entry` (id, guid, title, author, ' . ($this->isCompressed() ? 'content_bin' : 'content') - . ', link, date, lastSeen, hash, is_read, is_favorite, id_feed, tags) ' + . ', link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) ' . 'VALUES(:id, :guid, :title, :author, ' . ($this->isCompressed() ? 'COMPRESS(:content)' : ':content') . ', :link, :date, :last_seen, ' @@ -174,7 +174,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'UPDATE `' . $this->prefix . 'entry` ' . 'SET title=?, author=?, ' . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?') - . ', link=?, date=?, lastSeen=?, hash=' + . ', link=?, date=?, `lastSeen`=?, hash=' . ($this->hasNativeHex() ? 'X?' : '?') //TODO PostgreSQL . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=?, ') . 'tags=? ' @@ -265,7 +265,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'WHERE e.is_read=0 ' . 'GROUP BY e.id_feed' . ') x ON x.id_feed=f.id ' - . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0) ' + . 'SET f.`cache_nbUnreads`=COALESCE(x.nbUnreads, 0) ' . 'WHERE 1'; $values = array(); if ($feedId !== false) { @@ -328,7 +328,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } else { $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id ' . 'SET e.is_read=?,' - . 'f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' + . 'f.`cache_nbUnreads`=f.`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 ' . 'WHERE e.id=? AND e.is_read=?'; $values = array($is_read ? 1 : 0, $ids, $is_read ? 0 : 1); $stm = $this->bd->prepare($sql); @@ -467,7 +467,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($affected > 0) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbUnreads=cache_nbUnreads-' . $affected + . 'SET `cache_nbUnreads`=`cache_nbUnreads`-' . $affected . ' WHERE id=?'; $values = array($id_feed); $stm = $this->bd->prepare($sql); @@ -703,7 +703,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (count($guids) < 1) { return 0; } - $sql = 'UPDATE `' . $this->prefix . 'entry` SET lastSeen=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $sql = 'UPDATE `' . $this->prefix . 'entry` SET `lastSeen`=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; $stm = $this->bd->prepare($sql); $values = array(time(), $id_feed); $values = array_merge($values, $guids); diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index 80dbcca6b..fd5d25bf6 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -28,7 +28,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { protected function updateCacheUnreads($catId = false, $feedId = false) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbUnreads=(' + . 'SET `cache_nbUnreads`=(' . 'SELECT COUNT(*) AS nbUnreads FROM `' . $this->prefix . 'entry` e ' . 'WHERE e.id_feed=`' . $this->prefix . 'feed`.id AND e.is_read=0) ' . 'WHERE 1'; @@ -86,7 +86,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { } $affected = $stm->rowCount(); if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` SET cache_nbUnreads=cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' + $sql = 'UPDATE `' . $this->prefix . 'feed` SET `cache_nbUnreads`=`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 ' . 'WHERE id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; $values = array($ids); $stm = $this->bd->prepare($sql); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index ce65ed8b1..6e6d8857b 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -2,7 +2,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function addFeed($valuesTmp) { - $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, lastUpdate, priority, httpAuth, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; + $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, `lastUpdate`, priority, `httpAuth`, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); $values = array( @@ -85,13 +85,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function updateLastUpdate($id, $inError = 0, $updateCache = true) { if ($updateCache) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE - . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),' - . 'lastUpdate=?, error=? ' + . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' + . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),' + . '`lastUpdate`=?, error=? ' . 'WHERE id=?'; } else { $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET lastUpdate=?, error=? ' + . 'SET `lastUpdate`=?, error=? ' . 'WHERE id=?'; } @@ -226,10 +226,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($defaultCacheDuration < 0) { $defaultCacheDuration = 2147483647; } - $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history, ttl ' + $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl ' . 'FROM `' . $this->prefix . 'feed` ' - . 'WHERE ttl <> -1 AND lastUpdate < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' - . 'ORDER BY lastUpdate'; + . 'WHERE ttl <> -1 AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' + . 'ORDER BY `lastUpdate`'; $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute())) { $sql2 = 'ALTER TABLE `' . $this->prefix . 'feed` ADD COLUMN ttl INT NOT NULL DEFAULT -2'; //v0.7.3 @@ -282,7 +282,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'FROM `' . $this->prefix . 'entry` e ' . 'GROUP BY e.id_feed' . ') x ON x.id_feed=f.id ' - . 'SET f.cache_nbEntries=x.nbEntries, f.cache_nbUnreads=x.nbUnreads'; + . 'SET f.`cache_nbEntries`=x.nbEntries, f.`cache_nbUnreads`=x.nbUnreads'; $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { @@ -308,7 +308,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $affected = $stm->rowCount(); $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbEntries=0, cache_nbUnreads=0 WHERE id=?'; + . 'SET `cache_nbEntries`=0, `cache_nbUnreads`=0 WHERE id=?'; $values = array($id); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { @@ -326,7 +326,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' . 'WHERE id_feed=:id_feed 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 `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance + . 'AND `lastSeen` < (SELECT maxLastSeen FROM (SELECT (MAX(e3.`lastSeen`)-99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance . 'AND id NOT IN (SELECT id FROM (SELECT e2.id FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=:id_feed ORDER BY id DESC LIMIT :keep) keep)'; //Double select: MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery' $stm = $this->bd->prepare($sql); diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php index 7599fda53..440ae74da 100644 --- a/app/Models/FeedDAOSQLite.php +++ b/app/Models/FeedDAOSQLite.php @@ -4,8 +4,8 @@ class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAO { public function updateCachedValues() { //For one single feed, call updateLastUpdate($id) $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)'; + . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' + . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)'; $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { return $stm->rowCount(); diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php index 93a22fc3d..78b44ea7f 100644 --- a/lib/Minz/ModelPdo.php +++ b/lib/Minz/ModelPdo.php @@ -156,9 +156,6 @@ class MinzPDOMSQLite extends MinzPDO { class MinzPDOPGSQL extends MinzPDO { protected function compatibility($statement) { - return str_replace( - array('`', 'lastUpdate', 'pathEntries', 'httpAuth', 'cache_nbEntries', 'cache_nbUnreads', 'lastSeen'), - array('"', '"lastUpdate"', '"pathEntries"', '"httpAuth"', '"cache_nbEntries"', '"cache_nbUnreads"', '"lastSeen"'), - $statement); + return str_replace('`', '"', $statement); } } -- cgit v1.2.3 From 9f04e2bf3246ec5a65280908f017533d6f622a88 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 6 Sep 2016 09:15:32 +0200 Subject: MySQL use unhex instead of hex litteral X' syntax did not work well with bind values --- app/Models/EntryDAO.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index d39f0237c..159f9673b 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -11,7 +11,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function sqlHexDecode($x) { - return 'X' . $x; + return 'unhex(' . $x . ')'; } public function sqlHexEncode($x) { -- cgit v1.2.3 From 548250620bbf16677f043e23c7134e991506ed76 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 1 Oct 2016 10:09:28 +0200 Subject: PostgreSQL updateEntry() --- app/Models/EntryDAO.php | 57 ++++++++++++++++++++++++++++--------------------- 1 file changed, 33 insertions(+), 24 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 159f9673b..40f67061d 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -145,8 +145,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($this->hasNativeHex()) { $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hash']); } else { - $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); - $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); // X'09AF' hexadecimal literals do not work with SQLite/PDO //hex2bin() is PHP5.4+ + $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); //hex2bin() is PHP5.4+ + $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); } if ($this->addEntryPrepared && $this->addEntryPrepared->execute()) { @@ -172,33 +172,42 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($this->updateEntryPrepared === null) { $sql = 'UPDATE `' . $this->prefix . 'entry` ' - . 'SET title=?, author=?, ' - . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?') - . ', link=?, date=?, `lastSeen`=?, hash=' - . ($this->hasNativeHex() ? 'X?' : '?') //TODO PostgreSQL - . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=?, ') - . 'tags=? ' - . 'WHERE id_feed=? AND guid=?'; + . 'SET title=:title, author=:author, ' + . ($this->isCompressed() ? 'content_bin=COMPRESS(:content)' : 'content=:content') + . ', link=:link, date=:date, `lastSeen`=:last_seen, ' + . 'hash=' . $this->sqlHexDecode(':hash') + . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=:is_read, ') + . 'tags=:tags ' + . 'WHERE id_feed=:id_feed AND guid=:guid'; $this->updateEntryPrepared = $this->bd->prepare($sql); } - $values = array( - substr($valuesTmp['title'], 0, 255), - substr($valuesTmp['author'], 0, 255), - $valuesTmp['content'], - substr($valuesTmp['link'], 0, 1023), - $valuesTmp['date'], - time(), - $this->hasNativeHex() ? $valuesTmp['hash'] : pack('H*', $valuesTmp['hash']), - ); + $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); + $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); + $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); + $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); + $valuesTmp['author'] = substr($valuesTmp['author'], 0, 255); + $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); + $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); + $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); + $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); + $valuesTmp['lastSeen'] = time(); + $this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT); if ($valuesTmp['is_read'] !== null) { - $values[] = $valuesTmp['is_read'] ? 1 : 0; + $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'] ? 1 : 0, PDO::PARAM_INT); + } + $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT); + $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); + $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023); + $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']); + + if ($this->hasNativeHex()) { + $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hash']); + } else { + $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); //hex2bin() is PHP5.4+ + $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); } - $values = array_merge($values, array( - substr($valuesTmp['tags'], 0, 1023), - $valuesTmp['id_feed'], - substr($valuesTmp['guid'], 0, 760), - )); if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) { return $this->bd->lastInsertId(); -- cgit v1.2.3 From 1d8c24c73fd76fa81a53808088f70cbb4fe063a6 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 1 Oct 2016 14:50:17 +0200 Subject: BindParam bug --- app/Models/EntryDAO.php | 1 - 1 file changed, 1 deletion(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 40f67061d..9874bce1c 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -197,7 +197,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($valuesTmp['is_read'] !== null) { $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'] ? 1 : 0, PDO::PARAM_INT); } - $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT); $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023); $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']); -- cgit v1.2.3 From a7b605f2a56c9e5ff5edbd3c94b38e7bb7ad9b81 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 1 Oct 2016 15:14:37 +0200 Subject: Bug updateEntry() --- app/Models/EntryDAO.php | 24 ++++++++++++------------ 1 file changed, 12 insertions(+), 12 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 9874bce1c..956a06e93 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -183,29 +183,29 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); - $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); + $this->updateEntryPrepared->bindParam(':guid', $valuesTmp['guid']); $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); - $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); + $this->updateEntryPrepared->bindParam(':title', $valuesTmp['title']); $valuesTmp['author'] = substr($valuesTmp['author'], 0, 255); - $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); - $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); + $this->updateEntryPrepared->bindParam(':author', $valuesTmp['author']); + $this->updateEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); - $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); - $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); + $this->updateEntryPrepared->bindParam(':link', $valuesTmp['link']); + $this->updateEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); - $this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT); + $this->updateEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT); if ($valuesTmp['is_read'] !== null) { - $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'] ? 1 : 0, PDO::PARAM_INT); + $this->updateEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'] ? 1 : 0, PDO::PARAM_INT); } - $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); + $this->updateEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023); - $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']); + $this->updateEntryPrepared->bindParam(':tags', $valuesTmp['tags']); if ($this->hasNativeHex()) { - $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hash']); + $this->updateEntryPrepared->bindParam(':hash', $valuesTmp['hash']); } else { $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); //hex2bin() is PHP5.4+ - $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); + $this->updateEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); } if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) { -- cgit v1.2.3 From 32ac41439a67e2c5777f9da80324fe0bfbf394ff Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 1 Oct 2016 19:31:43 +0200 Subject: PostgreSQL case-insensitivity bug --- app/Models/EntryDAO.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 956a06e93..63565e73a 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -685,7 +685,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (count($guids) < 1) { return array(); } - $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hexHash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hex_hash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; $stm = $this->bd->prepare($sql); $values = array($id_feed); $values = array_merge($values, $guids); @@ -693,7 +693,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $result = array(); $rows = $stm->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { - $result[$row['guid']] = $row['hexHash']; + $result[$row['guid']] = $row['hex_hash']; } return $result; } else { -- cgit v1.2.3 From 05cabe99ae48a187a77e0246dfffc60f2434b5e5 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 5 Oct 2016 00:39:54 +0200 Subject: Take better advantage of other users refresh --- app/Controllers/feedController.php | 18 ++++++++++++++++-- app/Models/EntryDAO.php | 7 +++++-- app/Models/Feed.php | 4 ++++ app/Models/FeedDAO.php | 8 ++++---- 4 files changed, 29 insertions(+), 8 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index c2a22aeb3..15f373833 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -242,7 +242,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $feeds[] = $feed; } } else { - $feeds = $feedDAO->listFeedsOrderUpdate(FreshRSS_Context::$user_conf->ttl_default); + $feeds = $feedDAO->listFeedsOrderUpdate(-1); } // Calculate date of oldest entries we accept in DB. @@ -266,6 +266,20 @@ class FreshRSS_feed_Controller extends Minz_ActionController { continue; //When PubSubHubbub is used, do not pull refresh so often } + $mtime = 0; + $ttl = $feed->ttl(); + if ($ttl == -1) { + continue; //Feed refresh is disabled + } + if (feed->lastUpdate() < time() - ($ttl == -2 ? FreshRSS_Context::$user_conf->ttl_default : $ttl)) { + //Too early to refresh from source, but check if the feed was already updated by another user + $mtime = feed->cacheModifiedTime(); + if (feed->lastUpdate() >= $mtime + 10) { + continue; //Nothing newer from other users + } + Minz_Log::debug($feed->url() . ' was recently updated by another user'); + } + if (!$feed->lock()) { Minz_Log::notice('Feed already being actualized: ' . $feed->url()); continue; @@ -358,7 +372,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $entryDAO->addEntry($entry->toArray()); } } - $entryDAO->updateLastSeen($feed->id(), $oldGuids); + $entryDAO->updateLastSeen($feed->id(), $oldGuids, $mtime); } if ($feed_history >= 0 && rand(0, 30) === 1) { diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 63565e73a..43ac21f9e 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -707,13 +707,16 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } - public function updateLastSeen($id_feed, $guids) { + public function updateLastSeen($id_feed, $guids, $mtime = 0) { if (count($guids) < 1) { return 0; } $sql = 'UPDATE `' . $this->prefix . 'entry` SET `lastSeen`=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; $stm = $this->bd->prepare($sql); - $values = array(time(), $id_feed); + if ($mtime <= 0) { + $mtime = time(); + } + $values = array($mtime, $id_feed); $values = array_merge($values, $guids); if ($stm && $stm->execute($values)) { return $stm->rowCount(); diff --git a/app/Models/Feed.php b/app/Models/Feed.php index f2f345662..f435620c8 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -340,6 +340,10 @@ class FreshRSS_Feed extends Minz_Model { $this->entries = $entries; } + function cacheModifiedTime() { + return @filemtime(CACHE_PATH . '/' . md5($this->url) . '.spc'); + } + function lock() { $this->lockPath = TMP_PATH . '/' . $this->hash() . '.freshrss.lock'; if (file_exists($this->lockPath) && ((time() - @filemtime($this->lockPath)) > 3600)) { diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 6e6d8857b..ad3cb0c2e 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -222,13 +222,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $feedCategoryNames; } + /** + * Use $defaultCacheDuration == -1 to return all feeds, without filtering them by TTL. + */ public function listFeedsOrderUpdate($defaultCacheDuration = 3600) { - if ($defaultCacheDuration < 0) { - $defaultCacheDuration = 2147483647; - } $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl ' . 'FROM `' . $this->prefix . 'feed` ' - . 'WHERE ttl <> -1 AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' + . ($defaultCacheDuration < 0 ? '' : 'WHERE ttl <> -1 AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ') . 'ORDER BY `lastUpdate`'; $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute())) { -- cgit v1.2.3 From 50827447e3684ad59e0ecb8c2e76f473d07d1748 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 11 Oct 2016 19:34:46 +0200 Subject: Better error message in markReadFeed https://github.com/FreshRSS/FreshRSS/issues/1299 --- app/Models/EntryDAO.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 43ac21f9e..6563b0f93 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -467,7 +467,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $stm = $this->bd->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error markReadFeed: ' . $info[2]); + Minz_Log::error('SQL error markReadFeed: ' . $info[2] . ' with SQL: ' . $sql . $search); $this->bd->rollBack(); return false; } @@ -481,7 +481,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error markReadFeed: ' . $info[2]); + Minz_Log::error('SQL error markReadFeed cache: ' . $info[2]); $this->bd->rollBack(); return false; } -- cgit v1.2.3 From d184478fb4c98e035dca1ebef36a5946d43c6a3a Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Fri, 14 Oct 2016 23:05:05 +0200 Subject: PostgreSQL compatibility boolean https://github.com/FreshRSS/FreshRSS/issues/1311 --- app/Models/EntryDAO.php | 12 ++++++++---- app/Models/EntryDAOSQLite.php | 12 ++++++++---- 2 files changed, 16 insertions(+), 8 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 6563b0f93..938d90886 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -273,15 +273,19 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'WHERE e.is_read=0 ' . 'GROUP BY e.id_feed' . ') x ON x.id_feed=f.id ' - . 'SET f.`cache_nbUnreads`=COALESCE(x.nbUnreads, 0) ' - . 'WHERE 1'; + . 'SET f.`cache_nbUnreads`=COALESCE(x.nbUnreads, 0)'; + $hasWhere = false; $values = array(); if ($feedId !== false) { - $sql .= ' AND f.id=?'; + $sql .= $hasWhere ? ' AND' : ' WHERE'; + $hasWhere = true; + $sql .= ' f.id=?'; $values[] = $id; } if ($catId !== false) { - $sql .= ' AND f.category=?'; + $sql .= $hasWhere ? ' AND' : ' WHERE'; + $hasWhere = true; + $sql .= ' f.category=?'; $values[] = $catId; } $stm = $this->bd->prepare($sql); diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index fd5d25bf6..34e854608 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -30,15 +30,19 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { $sql = 'UPDATE `' . $this->prefix . 'feed` ' . 'SET `cache_nbUnreads`=(' . 'SELECT COUNT(*) AS nbUnreads FROM `' . $this->prefix . 'entry` e ' - . 'WHERE e.id_feed=`' . $this->prefix . 'feed`.id AND e.is_read=0) ' - . 'WHERE 1'; + . 'WHERE e.id_feed=`' . $this->prefix . 'feed`.id AND e.is_read=0)'; + $hasWhere = false; $values = array(); if ($feedId !== false) { - $sql .= ' AND id=?'; + $sql .= $hasWhere ? ' AND' : ' WHERE'; + $hasWhere = true; + $sql .= ' id=?'; $values[] = $feedId; } if ($catId !== false) { - $sql .= ' AND category=?'; + $sql .= $hasWhere ? ' AND' : ' WHERE'; + $hasWhere = true; + $sql .= ' category=?'; $values[] = $catId; } $stm = $this->bd->prepare($sql); -- cgit v1.2.3 From 38289beb66b51ce518cf7dc03faf6abc388eedbd Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 16 Oct 2016 15:19:11 +0200 Subject: Fix updateEntry warning https://github.com/FreshRSS/FreshRSS/issues/1325 --- app/Models/EntryDAO.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 938d90886..3959cb191 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -208,7 +208,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->updateEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); } - if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) { + if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute()) { return $this->bd->lastInsertId(); } else { $info = $this->updateEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->updateEntryPrepared->errorInfo(); -- cgit v1.2.3 From 1893fc61e0e576519f878267fd877247445d1055 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 20 Oct 2016 01:19:59 +0200 Subject: guid and urls should not contain low/high characters It looks like SimplePie does not always filter everything Having a character not in latin1 would create MySQL collate errors --- app/Controllers/feedController.php | 4 +++- app/Controllers/importExportController.php | 4 +++- app/Models/EntryDAO.php | 4 ++++ app/Models/FeedDAO.php | 10 ++++++++++ 4 files changed, 20 insertions(+), 2 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 8751d2fff..d4a6c9955 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -314,7 +314,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController { if (count($entries) > 0) { $newGuids = array(); foreach ($entries as $entry) { - $newGuids[] = $entry->guid(); + $guid = $entry->guid(); + $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $newGuids[] = $guid; } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index d36c57deb..e380323c4 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -362,7 +362,9 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $newGuids = array(); foreach ($article_object['items'] as $item) { - $newGuids[] = $item['id']; + $guid = $item['id']; + $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $newGuids[] = $guid; } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $this->entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 3959cb191..466e6f5a3 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -123,6 +123,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); + $valuesTmp['guid'] = filter_var($valuesTmp['guid'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); @@ -130,6 +131,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); @@ -190,6 +192,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->updateEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->updateEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); $this->updateEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->updateEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); @@ -689,6 +692,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (count($guids) < 1) { return array(); } + $guids = array_unique($guids); $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hex_hash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; $stm = $this->bd->prepare($sql); $values = array($id_feed); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index c680d270c..33e19d750 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -5,6 +5,9 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, `lastUpdate`, priority, `httpAuth`, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); + $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $values = array( substr($valuesTmp['url'], 0, 511), $valuesTmp['category'], @@ -55,6 +58,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function updateFeed($id, $valuesTmp) { + if (isset($valuesTmp['url'])) { + $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + } + if (isset($valuesTmp['website'])) { + $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + } + $set = ''; foreach ($valuesTmp as $key => $v) { $set .= $key . '=?, '; -- cgit v1.2.3 From 7f2b0439ec4158ee7d78571d60e9bcc995e87cac Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 20 Oct 2016 01:38:23 +0200 Subject: Extract function safe_ascii() --- app/Controllers/feedController.php | 4 +--- app/Controllers/importExportController.php | 4 +--- app/Models/EntryDAO.php | 6 +++--- app/Models/FeedDAO.php | 8 ++++---- lib/lib_rss.php | 3 +++ 5 files changed, 12 insertions(+), 13 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index d4a6c9955..ed3229687 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -314,9 +314,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { if (count($entries) > 0) { $newGuids = array(); foreach ($entries as $entry) { - $guid = $entry->guid(); - $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); - $newGuids[] = $guid; + $newGuids[] = safe_ascii($entry->guid()); } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index e380323c4..a1f789805 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -362,9 +362,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $newGuids = array(); foreach ($article_object['items'] as $item) { - $guid = $item['id']; - $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); - $newGuids[] = $guid; + $newGuids[] = safe_ascii($item['id']); } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $this->entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 466e6f5a3..4c6a9ea20 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -123,7 +123,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); - $valuesTmp['guid'] = filter_var($valuesTmp['guid'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['guid'] = safe_ascii($valuesTmp['guid']); $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); @@ -131,7 +131,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); - $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['link'] = safe_ascii($valuesTmp['link']); $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); @@ -192,7 +192,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->updateEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->updateEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); - $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['link'] = safe_ascii($valuesTmp['link']); $this->updateEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->updateEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 33e19d750..b21f19b66 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -5,8 +5,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, `lastUpdate`, priority, `httpAuth`, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); - $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); - $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['url'] = safe_ascii($valuesTmp['url']); + $valuesTmp['website'] = safe_ascii($valuesTmp['website']); $values = array( substr($valuesTmp['url'], 0, 511), @@ -59,10 +59,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function updateFeed($id, $valuesTmp) { if (isset($valuesTmp['url'])) { - $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['url'] = safe_ascii($valuesTmp['url']); } if (isset($valuesTmp['website'])) { - $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['website'] = safe_ascii($valuesTmp['website']); } $set = ''; diff --git a/lib/lib_rss.php b/lib/lib_rss.php index b18512484..75046fd54 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -83,6 +83,9 @@ function checkUrl($url) { } } +function safe_ascii($text) { + return filter_var($text, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); +} /** * Test if a given server address is publicly accessible. -- cgit v1.2.3