From d20b5a127fe95f91b55f3ae9391365c67f96c4cc Mon Sep 17 00:00:00 2001
From: Marien Fressinaud <dev@marienfressinaud.fr>
Date: Wed, 29 Oct 2014 11:47:51 +0100
Subject: Fix limit in import Json files

See https://github.com/marienfressinaud/FreshRSS/issues/680
---
 app/Models/FeedDAO.php | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index 852de6e36..74597c730 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -191,7 +191,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 		$feed = current(self::daoToFeed($res));
 
-		if (isset($feed)) {
+		if (isset($feed) && $feed !== false) {
 			return $feed;
 		} else {
 			return null;
-- 
cgit v1.2.3


From 5b90e1f4a0057aa78fd7d8d4d748b01676ec9073 Mon Sep 17 00:00:00 2001
From: Alexis Degrugillier <github@ainw.org>
Date: Sun, 1 Mar 2015 09:18:06 -0500
Subject: Introduce user queries objects

There is now an object to manipulate user queries. It allows to move logic to handle those from the view and the controller in the model.
Thus making the view and the controller easier to read.
I introduced a new interface to start using dependency injection. There is still some rough edges but we are moving in the right direction.
The new object is fully tested but it still need some improvements, for instance, it is still tied to the search object.
There might be a better way to do that.
---
 app/Controllers/configureController.php |  83 +++---------
 app/Exceptions/DAOException.php         |   5 +
 app/Models/CategoryDAO.php              |   2 +-
 app/Models/ConfigurationSetter.php      |   7 +-
 app/Models/EntryDAO.php                 |   2 +-
 app/Models/FeedDAO.php                  |   2 +-
 app/Models/Searchable.php               |   6 +
 app/Models/UserQuery.php                | 226 +++++++++++++++++++++++++++++++
 app/views/configure/queries.phtml       |  47 +++----
 tests/app/Models/UserQueryTest.php      | 229 ++++++++++++++++++++++++++++++++
 10 files changed, 505 insertions(+), 104 deletions(-)
 create mode 100644 app/Exceptions/DAOException.php
 create mode 100644 app/Models/Searchable.php
 create mode 100644 app/Models/UserQuery.php
 create mode 100644 tests/app/Models/UserQueryTest.php

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php
index 38ccd2b2d..fc92aa0c2 100755
--- a/app/Controllers/configureController.php
+++ b/app/Controllers/configureController.php
@@ -241,13 +241,16 @@ class FreshRSS_configure_Controller extends Minz_ActionController {
 	 * checking if categories and feeds are still in use.
 	 */
 	public function queriesAction() {
+		$category_dao = new FreshRSS_CategoryDAO();
+		$feed_dao = FreshRSS_Factory::createFeedDao();
 		if (Minz_Request::isPost()) {
-			$queries = Minz_Request::param('queries', array());
+			$params = Minz_Request::param('queries', array());
 
-			foreach ($queries as $key => $query) {
+			foreach ($params as $key => $query) {
 				if (!$query['name']) {
 					$query['name'] = _t('conf.query.number', $key + 1);
 				}
+				$queries[] = new FreshRSS_UserQuery($query, $feed_dao, $category_dao);
 			}
 			FreshRSS_Context::$user_conf->queries = $queries;
 			FreshRSS_Context::$user_conf->save();
@@ -255,62 +258,9 @@ class FreshRSS_configure_Controller extends Minz_ActionController {
 			Minz_Request::good(_t('feedback.conf.updated'),
 			                   array('c' => 'configure', 'a' => 'queries'));
 		} else {
-			$this->view->query_get = array();
-			$cat_dao = new FreshRSS_CategoryDAO();
-			$feed_dao = FreshRSS_Factory::createFeedDao();
+			$this->view->queries = array();
 			foreach (FreshRSS_Context::$user_conf->queries as $key => $query) {
-				if (!isset($query['get'])) {
-					continue;
-				}
-
-				switch ($query['get'][0]) {
-				case 'c':
-					$category = $cat_dao->searchById(substr($query['get'], 2));
-
-					$deprecated = true;
-					$cat_name = '';
-					if ($category) {
-						$cat_name = $category->name();
-						$deprecated = false;
-					}
-
-					$this->view->query_get[$key] = array(
-						'type' => 'category',
-						'name' => $cat_name,
-						'deprecated' => $deprecated,
-					);
-					break;
-				case 'f':
-					$feed = $feed_dao->searchById(substr($query['get'], 2));
-
-					$deprecated = true;
-					$feed_name = '';
-					if ($feed) {
-						$feed_name = $feed->name();
-						$deprecated = false;
-					}
-
-					$this->view->query_get[$key] = array(
-						'type' => 'feed',
-						'name' => $feed_name,
-						'deprecated' => $deprecated,
-					);
-					break;
-				case 's':
-					$this->view->query_get[$key] = array(
-						'type' => 'favorite',
-						'name' => 'favorite',
-						'deprecated' => false,
-					);
-					break;
-				case 'a':
-					$this->view->query_get[$key] = array(
-						'type' => 'all',
-						'name' => 'all',
-						'deprecated' => false,
-					);
-					break;
-				}
+				$this->view->queries[$key] = new FreshRSS_UserQuery($query, $feed_dao, $category_dao);
 			}
 		}
 
@@ -325,16 +275,17 @@ class FreshRSS_configure_Controller extends Minz_ActionController {
 	 * lean data.
 	 */
 	public function addQueryAction() {
-		$whitelist = array('get', 'order', 'name', 'search', 'state');
-		$queries = FreshRSS_Context::$user_conf->queries;
-		$query = Minz_Request::params();
-		$query['name'] = _t('conf.query.number', count($queries) + 1);
-		foreach ($query as $key => $value) {
-			if (!in_array($key, $whitelist)) {
-				unset($query[$key]);
-			}
+		$category_dao = new FreshRSS_CategoryDAO();
+		$feed_dao = FreshRSS_Factory::createFeedDao();
+		$queries = array();
+		foreach (FreshRSS_Context::$user_conf->queries as $key => $query) {
+			$queries[$key] = new FreshRSS_UserQuery($query, $feed_dao, $category_dao);
 		}
-		$queries[] = $query;
+		$params = Minz_Request::params();
+		$params['url'] = Minz_Url::display(array('params' => $params));
+		$params['name'] = _t('conf.query.number', count($queries) + 1);
+		$queries[] = new FreshRSS_UserQuery($params, $feed_dao, $category_dao);
+
 		FreshRSS_Context::$user_conf->queries = $queries;
 		FreshRSS_Context::$user_conf->save();
 
diff --git a/app/Exceptions/DAOException.php b/app/Exceptions/DAOException.php
new file mode 100644
index 000000000..6bd8f4ff0
--- /dev/null
+++ b/app/Exceptions/DAOException.php
@@ -0,0 +1,5 @@
+<?php
+
+class FreshRSS_DAOException extends \Exception {
+
+}
diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index 27a558522..4eee226ba 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -1,6 +1,6 @@
 <?php
 
-class FreshRSS_CategoryDAO extends Minz_ModelPdo {
+class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable{
 	public function addCategory($valuesTmp) {
 		$sql = 'INSERT INTO `' . $this->prefix . 'category`(name) VALUES(?)';
 		$stm = $this->bd->prepare($sql);
diff --git a/app/Models/ConfigurationSetter.php b/app/Models/ConfigurationSetter.php
index eeb1f2f4c..d7689752f 100644
--- a/app/Models/ConfigurationSetter.php
+++ b/app/Models/ConfigurationSetter.php
@@ -117,12 +117,7 @@ class FreshRSS_ConfigurationSetter {
 	private function _queries(&$data, $values) {
 		$data['queries'] = array();
 		foreach ($values as $value) {
-			$value = array_filter($value);
-			$params = $value;
-			unset($params['name']);
-			unset($params['url']);
-			$value['url'] = Minz_Url::display(array('params' => $params));
-			$data['queries'][] = $value;
+			$data['queries'][] = $value->toArray();
 		}
 	}
 
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index cf75a02c9..b8a1a43b0 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -1,6 +1,6 @@
 <?php
 
-class FreshRSS_EntryDAO extends Minz_ModelPdo {
+class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable{
 
 	public function isCompressed() {
 		return parent::$sharedDbType !== 'sqlite';
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index 74597c730..e8b07acbd 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -1,6 +1,6 @@
 <?php
 
-class FreshRSS_FeedDAO extends Minz_ModelPdo {
+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)';
 		$stm = $this->bd->prepare($sql);
diff --git a/app/Models/Searchable.php b/app/Models/Searchable.php
new file mode 100644
index 000000000..d5bcea49d
--- /dev/null
+++ b/app/Models/Searchable.php
@@ -0,0 +1,6 @@
+<?php
+
+interface FreshRSS_Searchable {
+
+	public function searchById($id);
+}
diff --git a/app/Models/UserQuery.php b/app/Models/UserQuery.php
new file mode 100644
index 000000000..754b6996b
--- /dev/null
+++ b/app/Models/UserQuery.php
@@ -0,0 +1,226 @@
+<?php
+
+/**
+ * Contains the description of a user query
+ *
+ * It allows to extract the meaningful bits of the query to be manipulated in an
+ * easy way.
+ */
+class FreshRSS_UserQuery {
+
+	private $deprecated = false;
+	private $get;
+	private $get_name;
+	private $get_type;
+	private $name;
+	private $order;
+	private $search;
+	private $state;
+	private $url;
+	private $feed_dao;
+	private $category_dao;
+
+	/**
+	 * @param array $query
+	 * @param FreshRSS_Searchable $feed_dao
+	 * @param FreshRSS_Searchable $category_dao
+	 */
+	public function __construct($query, FreshRSS_Searchable $feed_dao = null, FreshRSS_Searchable $category_dao = null) {
+		$this->category_dao = $category_dao;
+		$this->feed_dao = $feed_dao;
+		if (isset($query['get'])) {
+			$this->parseGet($query['get']);
+		}
+		if (isset($query['name'])) {
+			$this->name = $query['name'];
+		}
+		if (isset($query['order'])) {
+			$this->order = $query['order'];
+		}
+		if (!isset($query['search'])) {
+			$query['search'] = '';
+		}
+		// linked to deeply with the search object, need to use dependency injection
+		$this->search = new FreshRSS_Search($query['search']);
+		if (isset($query['state'])) {
+			$this->state = $query['state'];
+		}
+		if (isset($query['url'])) {
+			$this->url = $query['url'];
+		}
+	}
+
+	/**
+	 * Convert the current object to an array.
+	 *
+	 * @return array
+	 */
+	public function toArray() {
+		return array_filter(array(
+		    'get' => $this->get,
+		    'name' => $this->name,
+		    'order' => $this->order,
+		    'search' => $this->search->__toString(),
+		    'state' => $this->state,
+		    'url' => $this->url,
+		));
+	}
+
+	/**
+	 * Parse the get parameter in the query string to extract its name and
+	 * type
+	 *
+	 * @param string $get
+	 */
+	private function parseGet($get) {
+		$this->get = $get;
+		if (preg_match('/(?P<type>[acfs])(_(?P<id>\d+))?/', $get, $matches)) {
+			switch ($matches['type']) {
+				case 'a':
+					$this->parseAll();
+					break;
+				case 'c':
+					$this->parseCategory($matches['id']);
+					break;
+				case 'f':
+					$this->parseFeed($matches['id']);
+					break;
+				case 's':
+					$this->parseFavorite();
+					break;
+			}
+		}
+	}
+
+	/**
+	 * Parse the query string when it is an "all" query
+	 */
+	private function parseAll() {
+		$this->get_name = 'all';
+		$this->get_type = 'all';
+	}
+
+	/**
+	 * Parse the query string when it is a "category" query
+	 *
+	 * @param integer $id
+	 * @throws FreshRSS_DAOException
+	 */
+	private function parseCategory($id) {
+		if (is_null($this->category_dao)) {
+			throw new FreshRSS_DAOException('Category DAO is not loaded i UserQuery');
+		}
+		$category = $this->category_dao->searchById($id);
+		if ($category) {
+			$this->get_name = $category->name();
+		} else {
+			$this->deprecated = true;
+		}
+		$this->get_type = 'category';
+	}
+
+	/**
+	 * Parse the query string when it is a "feed" query
+	 *
+	 * @param integer $id
+	 * @throws FreshRSS_DAOException
+	 */
+	private function parseFeed($id) {
+		if (is_null($this->feed_dao)) {
+			throw new FreshRSS_DAOException('Feed DAO is not loaded i UserQuery');
+		}
+		$feed = $this->feed_dao->searchById($id);
+		if ($feed) {
+			$this->get_name = $feed->name();
+		} else {
+			$this->deprecated = true;
+		}
+		$this->get_type = 'feed';
+	}
+
+	/**
+	 * Parse the query string when it is a "favorite" query
+	 */
+	private function parseFavorite() {
+		$this->get_name = 'favorite';
+		$this->get_type = 'favorite';
+	}
+
+	/**
+	 * Check if the current user query is deprecated.
+	 * It is deprecated if the category or the feed used in the query are
+	 * not existing.
+	 *
+	 * @return boolean
+	 */
+	public function isDeprecated() {
+		return $this->deprecated;
+	}
+
+	/**
+	 * Check if the user query has parameters.
+	 * If the type is 'all', it is considered equal to no parameters
+	 *
+	 * @return boolean
+	 */
+	public function hasParameters() {
+		if ($this->get_type === 'all') {
+			return false;
+		}
+		if ($this->hasSearch()) {
+			return true;
+		}
+		if ($this->state) {
+			return true;
+		}
+		if ($this->order) {
+			return true;
+		}
+		if ($this->get) {
+			return true;
+		}
+		return false;
+	}
+
+	/**
+	 * Check if there is a search in the search object
+	 *
+	 * @return boolean
+	 */
+	public function hasSearch() {
+		return $this->search->getRawInput() != "";
+	}
+
+	public function getGet() {
+		return $this->get;
+	}
+
+	public function getGetName() {
+		return $this->get_name;
+	}
+
+	public function getGetType() {
+		return $this->get_type;
+	}
+
+	public function getName() {
+		return $this->name;
+	}
+
+	public function getOrder() {
+		return $this->order;
+	}
+
+	public function getSearch() {
+		return $this->search;
+	}
+
+	public function getState() {
+		return $this->state;
+	}
+
+	public function getUrl() {
+		return $this->url;
+	}
+
+}
diff --git a/app/views/configure/queries.phtml b/app/views/configure/queries.phtml
index 5f449deb3..69efcf365 100644
--- a/app/views/configure/queries.phtml
+++ b/app/views/configure/queries.phtml
@@ -6,27 +6,28 @@
 	<form method="post" action="<?php echo _url('configure', 'queries'); ?>">
 		<legend><?php echo _t('conf.query'); ?></legend>
 
-		<?php foreach (FreshRSS_Context::$user_conf->queries as $key => $query) { ?>
+		<?php foreach ($this->queries as $key => $query) { ?>
 		<div class="form-group" id="query-group-<?php echo $key; ?>">
 			<label class="group-name" for="queries_<?php echo $key; ?>_name">
 				<?php echo _t('conf.query.number', $key + 1); ?>
 			</label>
 
 			<div class="group-controls">
-				<input type="hidden" id="queries_<?php echo $key; ?>_search" name="queries[<?php echo $key; ?>][search]" value="<?php echo isset($query['search']) ? $query['search'] : ""; ?>"/>
-				<input type="hidden" id="queries_<?php echo $key; ?>_state" name="queries[<?php echo $key; ?>][state]" value="<?php echo isset($query['state']) ? $query['state'] : ""; ?>"/>
-				<input type="hidden" id="queries_<?php echo $key; ?>_order" name="queries[<?php echo $key; ?>][order]" value="<?php echo isset($query['order']) ? $query['order'] : ""; ?>"/>
-				<input type="hidden" id="queries_<?php echo $key; ?>_get" name="queries[<?php echo $key; ?>][get]" value="<?php echo isset($query['get']) ? $query['get'] : ""; ?>"/>
+				<input type="hidden" id="queries_<?php echo $key; ?>_search" name="queries[<?php echo $key; ?>][url]" value="<?php echo $query->getUrl(); ?>"/>
+				<input type="hidden" id="queries_<?php echo $key; ?>_search" name="queries[<?php echo $key; ?>][search]" value="<?php echo $query->getSearch(); ?>"/>
+				<input type="hidden" id="queries_<?php echo $key; ?>_state" name="queries[<?php echo $key; ?>][state]" value="<?php echo $query->getState(); ?>"/>
+				<input type="hidden" id="queries_<?php echo $key; ?>_order" name="queries[<?php echo $key; ?>][order]" value="<?php echo $query->getOrder(); ?>"/>
+				<input type="hidden" id="queries_<?php echo $key; ?>_get" name="queries[<?php echo $key; ?>][get]" value="<?php echo $query->getGet(); ?>"/>
 
 				<div class="stick">
 					<input class="extend"
 					       type="text"
 					       id="queries_<?php echo $key; ?>_name"
 					       name="queries[<?php echo $key; ?>][name]"
-					       value="<?php echo $query['name']; ?>"
+					       value="<?php echo $query->getName(); ?>"
 					/>
 
-					<a class="btn" href="<?php echo $query['url']; ?>">
+					<a class="btn" href="<?php echo $query->getUrl(); ?>">
 						<?php echo _i('link'); ?>
 					</a>
 
@@ -35,23 +36,11 @@
 					</a>
 				</div>
 
-				<?php
-					$exist = (isset($query['search']) ? 1 : 0)
-						   + (isset($query['state']) ? 1 : 0)
-						   + (isset($query['order']) ? 1 : 0)
-						   + (isset($query['get']) ? 1 : 0);
-					// If the only filter is "all" articles, we consider there is no filter
-					$exist = ($exist === 1 && isset($query['get']) && $query['get'] === 'a') ? 0 : $exist;
-
-					$deprecated = (isset($this->query_get[$key]) &&
-					               $this->query_get[$key]['deprecated']);
-				?>
-
-				<?php if ($exist === 0) { ?>
+				<?php if (!$query->hasParameters()) { ?>
 				<div class="alert alert-warn">
 					<div class="alert-head"><?php echo _t('conf.query.no_filter'); ?></div>
 				</div>
-				<?php } elseif ($deprecated) { ?>
+				<?php } elseif ($query->isDeprecated()) { ?>
 				<div class="alert alert-error">
 					<div class="alert-head"><?php echo _t('conf.query.deprecated'); ?></div>
 				</div>
@@ -60,20 +49,20 @@
 					<div class="alert-head"><?php echo _t('conf.query.filter'); ?></div>
 
 					<ul>
-						<?php if (isset($query['search'])) { ?>
-						<li class="item"><?php echo _t('conf.query.search', $query['search']); ?></li>
+						<?php if ($query->hasSearch()) { ?>
+						<li class="item"><?php echo _t('conf.query.search', $query->getSearch()->getRawInput()); ?></li>
 						<?php } ?>
 
-						<?php if (isset($query['state'])) { ?>
-						<li class="item"><?php echo _t('conf.query.state_' . $query['state']); ?></li>
+						<?php if ($query->getState()) { ?>
+						<li class="item"><?php echo _t('conf.query.state_' . $query->getState()); ?></li>
 						<?php } ?>
 
-						<?php if (isset($query['order'])) { ?>
-						<li class="item"><?php echo _t('conf.query.order_' . strtolower($query['order'])); ?></li>
+						<?php if ($query->getOrder()) { ?>
+						<li class="item"><?php echo _t('conf.query.order_' . strtolower($query->getOrder())); ?></li>
 						<?php } ?>
 
-						<?php if (isset($query['get'])) { ?>
-						<li class="item"><?php echo _t('conf.query.get_' . $this->query_get[$key]['type'], $this->query_get[$key]['name']); ?></li>
+						<?php if ($query->getGet()) { ?>
+						<li class="item"><?php echo _t('conf.query.get_' . $query->getGetType(), $query->getGetName()); ?></li>
 						<?php } ?>
 					</ul>
 				</div>
diff --git a/tests/app/Models/UserQueryTest.php b/tests/app/Models/UserQueryTest.php
new file mode 100644
index 000000000..2234be6e1
--- /dev/null
+++ b/tests/app/Models/UserQueryTest.php
@@ -0,0 +1,229 @@
+<?php
+
+/**
+ * Description of UserQueryTest
+ */
+class UserQueryTest extends \PHPUnit_Framework_TestCase {
+
+	public function test__construct_whenAllQuery_storesAllParameters() {
+		$query = array('get' => 'a');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertEquals('all', $user_query->getGetName());
+		$this->assertEquals('all', $user_query->getGetType());
+	}
+
+	public function test__construct_whenFavoriteQuery_storesFavoriteParameters() {
+		$query = array('get' => 's');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertEquals('favorite', $user_query->getGetName());
+		$this->assertEquals('favorite', $user_query->getGetType());
+	}
+
+	/**
+	 * @expectedException Exceptions/FreshRSS_DAOException
+	 * @expectedExceptionMessage Category DAO is not loaded in UserQuery
+	 */
+	public function test__construct_whenCategoryQueryAndNoDao_throwsException() {
+		$this->markTestIncomplete('There is a problem with the exception autoloading. We need to make a better autoloading process');
+		$query = array('get' => 'c_1');
+		new FreshRSS_UserQuery($query);
+	}
+
+	public function test__construct_whenCategoryQuery_storesCategoryParameters() {
+		$category_name = 'some category name';
+		$cat = $this->getMock('FreshRSS_Category');
+		$cat->expects($this->atLeastOnce())
+		    ->method('name')
+		    ->withAnyParameters()
+		    ->willReturn($category_name);
+		$cat_dao = $this->getMock('FreshRSS_Searchable');
+		$cat_dao->expects($this->atLeastOnce())
+		    ->method('searchById')
+		    ->withAnyParameters()
+		    ->willReturn($cat);
+		$query = array('get' => 'c_1');
+		$user_query = new FreshRSS_UserQuery($query, null, $cat_dao);
+		$this->assertEquals($category_name, $user_query->getGetName());
+		$this->assertEquals('category', $user_query->getGetType());
+	}
+
+	/**
+	 * @expectedException Exceptions/FreshRSS_DAOException
+	 * @expectedExceptionMessage Feed DAO is not loaded in UserQuery
+	 */
+	public function test__construct_whenFeedQueryAndNoDao_throwsException() {
+		$this->markTestIncomplete('There is a problem with the exception autoloading. We need to make a better autoloading process');
+		$query = array('get' => 'c_1');
+		new FreshRSS_UserQuery($query);
+	}
+
+	public function test__construct_whenFeedQuery_storesFeedParameters() {
+		$feed_name = 'some feed name';
+		$feed = $this->getMock('FreshRSS_Feed', array(), array('', false));
+		$feed->expects($this->atLeastOnce())
+		    ->method('name')
+		    ->withAnyParameters()
+		    ->willReturn($feed_name);
+		$feed_dao = $this->getMock('FreshRSS_Searchable');
+		$feed_dao->expects($this->atLeastOnce())
+		    ->method('searchById')
+		    ->withAnyParameters()
+		    ->willReturn($feed);
+		$query = array('get' => 'f_1');
+		$user_query = new FreshRSS_UserQuery($query, $feed_dao, null);
+		$this->assertEquals($feed_name, $user_query->getGetName());
+		$this->assertEquals('feed', $user_query->getGetType());
+	}
+
+	public function test__construct_whenUnknownQuery_doesStoreParameters() {
+		$query = array('get' => 'q');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertNull($user_query->getGetName());
+		$this->assertNull($user_query->getGetType());
+	}
+
+	public function test__construct_whenName_storesName() {
+		$name = 'some name';
+		$query = array('name' => $name);
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertEquals($name, $user_query->getName());
+	}
+
+	public function test__construct_whenOrder_storesOrder() {
+		$order = 'some order';
+		$query = array('order' => $order);
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertEquals($order, $user_query->getOrder());
+	}
+
+	public function test__construct_whenState_storesState() {
+		$state = 'some state';
+		$query = array('state' => $state);
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertEquals($state, $user_query->getState());
+	}
+
+	public function test__construct_whenUrl_storesUrl() {
+		$url = 'some url';
+		$query = array('url' => $url);
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertEquals($url, $user_query->getUrl());
+	}
+
+	public function testToArray_whenNoData_returnsEmptyArray() {
+		$user_query = new FreshRSS_UserQuery(array());
+		$this->assertInternalType('array', $user_query->toArray());
+		$this->assertCount(0, $user_query->toArray());
+	}
+
+	public function testToArray_whenData_returnsArray() {
+		$query = array(
+		    'get' => 's',
+		    'name' => 'some name',
+		    'order' => 'some order',
+		    'search' => 'some search',
+		    'state' => 'some state',
+		    'url' => 'some url',
+		);
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertInternalType('array', $user_query->toArray());
+		$this->assertCount(6, $user_query->toArray());
+		$this->assertEquals($query, $user_query->toArray());
+	}
+
+	public function testHasSearch_whenSearch_returnsTrue() {
+		$query = array(
+		    'search' => 'some search',
+		);
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertTrue($user_query->hasSearch());
+	}
+
+	public function testHasSearch_whenNoSearch_returnsFalse() {
+		$user_query = new FreshRSS_UserQuery(array());
+		$this->assertFalse($user_query->hasSearch());
+	}
+
+	public function testHasParameters_whenAllQuery_returnsFalse() {
+		$query = array('get' => 'a');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertFalse($user_query->hasParameters());
+	}
+
+	public function testHasParameters_whenNoParameter_returnsFalse() {
+		$query = array();
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertFalse($user_query->hasParameters());
+	}
+
+	public function testHasParameters_whenParameter_returnTrue() {
+		$query = array('get' => 's');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertTrue($user_query->hasParameters());
+	}
+
+	public function testIsDeprecated_whenCategoryExists_returnFalse() {
+		$cat = $this->getMock('FreshRSS_Category');
+		$cat_dao = $this->getMock('FreshRSS_Searchable');
+		$cat_dao->expects($this->atLeastOnce())
+		    ->method('searchById')
+		    ->withAnyParameters()
+		    ->willReturn($cat);
+		$query = array('get' => 'c_1');
+		$user_query = new FreshRSS_UserQuery($query, null, $cat_dao);
+		$this->assertFalse($user_query->isDeprecated());
+	}
+
+	public function testIsDeprecated_whenCategoryDoesNotExist_returnTrue() {
+		$cat_dao = $this->getMock('FreshRSS_Searchable');
+		$cat_dao->expects($this->atLeastOnce())
+		    ->method('searchById')
+		    ->withAnyParameters()
+		    ->willReturn(null);
+		$query = array('get' => 'c_1');
+		$user_query = new FreshRSS_UserQuery($query, null, $cat_dao);
+		$this->assertTrue($user_query->isDeprecated());
+	}
+
+	public function testIsDeprecated_whenFeedExists_returnFalse() {
+		$feed = $this->getMock('FreshRSS_Feed', array(), array('', false));
+		$feed_dao = $this->getMock('FreshRSS_Searchable');
+		$feed_dao->expects($this->atLeastOnce())
+		    ->method('searchById')
+		    ->withAnyParameters()
+		    ->willReturn($feed);
+		$query = array('get' => 'f_1');
+		$user_query = new FreshRSS_UserQuery($query, $feed_dao, null);
+		$this->assertFalse($user_query->isDeprecated());
+	}
+
+	public function testIsDeprecated_whenFeedDoesNotExist_returnTrue() {
+		$feed_dao = $this->getMock('FreshRSS_Searchable');
+		$feed_dao->expects($this->atLeastOnce())
+		    ->method('searchById')
+		    ->withAnyParameters()
+		    ->willReturn(null);
+		$query = array('get' => 'f_1');
+		$user_query = new FreshRSS_UserQuery($query, $feed_dao, null);
+		$this->assertTrue($user_query->isDeprecated());
+	}
+
+	public function testIsDeprecated_whenAllQuery_returnFalse() {
+		$query = array('get' => 'a');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertFalse($user_query->isDeprecated());
+	}
+
+	public function testIsDeprecated_whenFavoriteQuery_returnFalse() {
+		$query = array('get' => 's');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertFalse($user_query->isDeprecated());
+	}
+
+	public function testIsDeprecated_whenUnknownQuery_returnFalse() {
+		$query = array('get' => 'q');
+		$user_query = new FreshRSS_UserQuery($query);
+		$this->assertFalse($user_query->isDeprecated());
+	}
+
+}
-- 
cgit v1.2.3


From 24f6c1eabb4cea941e40307c2f732c0ca384ffd2 Mon Sep 17 00:00:00 2001
From: Alexis Degrugillier <github@ainw.org>
Date: Thu, 5 Mar 2015 06:47:13 -0500
Subject: Fix spacing

---
 app/Models/CategoryDAO.php | 2 +-
 app/Models/EntryDAO.php    | 2 +-
 app/Models/FeedDAO.php     | 2 +-
 3 files changed, 3 insertions(+), 3 deletions(-)

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index 4eee226ba..189a5f0e4 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -1,6 +1,6 @@
 <?php
 
-class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable{
+class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	public function addCategory($valuesTmp) {
 		$sql = 'INSERT INTO `' . $this->prefix . 'category`(name) VALUES(?)';
 		$stm = $this->bd->prepare($sql);
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index b8a1a43b0..9736d5cd3 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -1,6 +1,6 @@
 <?php
 
-class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable{
+class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 
 	public function isCompressed() {
 		return parent::$sharedDbType !== 'sqlite';
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index e8b07acbd..f48beee6e 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -1,6 +1,6 @@
 <?php
 
-class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable{
+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)';
 		$stm = $this->bd->prepare($sql);
-- 
cgit v1.2.3


From 993466844405bd9854d890d6d5ebf763ed8b78cb Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 9 May 2015 23:37:56 +0200
Subject: SQL: more robust purge

https://github.com/FreshRSS/FreshRSS/issues/798
https://github.com/FreshRSS/FreshRSS/issues/493
---
 app/Controllers/feedController.php | 13 +++++--------
 app/Models/Feed.php                |  3 +--
 app/Models/FeedDAO.php             |  8 +++++---
 3 files changed, 11 insertions(+), 13 deletions(-)

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php
index 59c9174fb..5657d4a88 100755
--- a/app/Controllers/feedController.php
+++ b/app/Controllers/feedController.php
@@ -329,7 +329,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				// For this feed, check existing GUIDs already in database.
 				$existingHashForGuids = $entryDAO->listHashForFeedGuids($feed->id(), $newGuids);
 				unset($newGuids);
-				$use_declared_date = empty($existingHashForGuids);
 
 				$oldGuids = array();
 				// Add entries in database if possible.
@@ -353,14 +352,14 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 						// This entry should not be added considering configuration and date.
 						$oldGuids[] = $entry->guid();
 					} else {
-						$id = uTimeString();
-						if ($use_declared_date || $entry_date < $date_min) {
-							// Use declared date at first import.
+						if ($entry_date < $date_min) {
 							$id = min(time(), $entry_date) . uSecString();
+							$entry->_isRead(true);	//Old article that was not in database. Probably an error, so mark as read
+						} else {
+							$id = uTimeString();
+							$entry->_isRead($is_read);
 						}
-
 						$entry->_id($id);
-						$entry->_isRead($is_read);
 
 						$entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry);
 						if ($entry === null) {
@@ -376,7 +375,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				}
 				$entryDAO->updateLastSeen($feed->id(), $oldGuids);
 			}
-			//TODO: updateLastSeen old GUIDS once in a while, in the case of caching (i.e. the whole feed content has not changed)
 
 			if ($feed_history >= 0 && rand(0, 30) === 1) {
 				// TODO: move this function in web cron when available (see entry::purge)
@@ -384,7 +382,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				if (!$entryDAO->hasTransaction()) {
 					$entryDAO->beginTransaction();
 				}
-				//TODO: more robust system based on entry.lastSeen to avoid cleaning entries that are still published in the RSS feed.
 
 				$nb = $feedDAO->cleanOldEntries($feed->id(),
 				                                $date_min,
diff --git a/app/Models/Feed.php b/app/Models/Feed.php
index 27c83ffd5..5d377de9a 100644
--- a/app/Models/Feed.php
+++ b/app/Models/Feed.php
@@ -245,7 +245,7 @@ class FreshRSS_Feed extends Minz_Model {
 					$this->_url($clean_url);
 				}
 
-				if (($mtime === true) ||($mtime > $this->lastUpdate)) {
+				if (($mtime === true) || ($mtime > $this->lastUpdate)) {
 					Minz_Log::notice('FreshRSS no cache ' . $mtime . ' > ' . $this->lastUpdate . ' for ' . $clean_url);
 					$this->loadEntries($feed);	// et on charge les articles du flux
 				} else {
@@ -255,7 +255,6 @@ class FreshRSS_Feed extends Minz_Model {
 
 				$feed->__destruct();	//http://simplepie.org/wiki/faq/i_m_getting_memory_leaks
 				unset($feed);
-				//TODO: Return a different information in case of cache/no-cache, and give access to the GUIDs in case of cache
 			}
 		}
 	}
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index f48beee6e..c13e2b008 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -322,10 +322,12 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return $affected;
 	}
 
-	public function cleanOldEntries($id, $date_min, $keep = 15) {	//Remember to call updateLastUpdate($id) just after
+	public function cleanOldEntries($id, $date_min, $keep = 15) {	//Remember to call updateLastUpdate($id) or updateCachedValues() just after
 		$sql = 'DELETE FROM `' . $this->prefix . 'entry` '
-		     . 'WHERE id_feed = :id_feed AND id <= :id_max AND is_favorite=0 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 yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+		     . '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 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);
 
 		$id_max = intval($date_min) . '000000';
-- 
cgit v1.2.3


From a7bc54bb996a87a66127101be548d181dc8dd935 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 9 May 2015 23:45:52 +0200
Subject: Minor spaces

---
 app/Models/FeedDAO.php | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index c13e2b008..76025ff53 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -324,10 +324,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 
 	public function cleanOldEntries($id, $date_min, $keep = 15) {	//Remember to call updateLastUpdate($id) or updateCachedValues() just after
 		$sql = 'DELETE FROM `' . $this->prefix . 'entry` '
-		     . 'WHERE id_feed = :id_feed AND id <= :id_max '
+		     . '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 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'
+		     . '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);
 
 		$id_max = intval($date_min) . '000000';
-- 
cgit v1.2.3


From 217c191a1ba3ac03b847d261a32e19975380fcad Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Mon, 11 May 2015 22:42:41 +0200
Subject: More SQLite compatibility

Additional changes to add compatibility with SQLite for the new
hash/lastSeen mode of updating articles.
---
 app/Models/EntryDAO.php        | 71 ++++++++++++++++++++++++------------------
 app/Models/EntryDAOSQLite.php  | 15 +++++++++
 app/Models/FeedDAO.php         | 11 ++++---
 app/SQL/install.sql.mysql.php  |  2 +-
 app/SQL/install.sql.sqlite.php |  2 +-
 app/install.php                |  2 ++
 lib/Minz/ModelPdo.php          |  5 +++
 7 files changed, 70 insertions(+), 38 deletions(-)

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 172eac897..eae9683ad 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -6,38 +6,48 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return parent::$sharedDbType !== 'sqlite';
 	}
 
-	protected function autoAddColumn($errorInfo) {
-		if (isset($errorInfo[0])) {
-			if ($errorInfo[0] == '42S22') {	//ER_BAD_FIELD_ERROR
-				$hasTransaction = false;
-				try {
-					$stm = null;
-					if (stripos($errorInfo[2], 'lastSeen') !== false) {	//v1.2
-						if (!$this->bd->inTransaction()) {
-							$this->bd->beginTransaction();
-							$hasTransaction = true;
-						}
-						$stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN lastSeen INT(11) NOT NULL');
-						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()) {
-								if ($hasTransaction) {
-									$this->bd->commit();
-								}
-								return true;
-							}
-						}
+	protected function addColumn($name) {
+		Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn: ' . $name);
+		$hasTransaction = false;
+		try {
+			$stm = null;
+			if ($name === 'lastSeen') {	//v1.2
+				if (!$this->bd->inTransaction()) {
+					$this->bd->beginTransaction();
+					$hasTransaction = true;
+				}
+				$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()) {
 						if ($hasTransaction) {
-							$this->bd->rollBack();
+							$this->bd->commit();
 						}
-					} elseif (stripos($errorInfo[2], 'hash') !== false) {	//v1.2
-						$stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN hash BINARY(16) NOT NULL');
-						return $stm && $stm->execute();
+						return true;
 					}
-				} catch (Exception $e) {
-					Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage());
-					if ($hasTransaction) {
-						$this->bd->rollBack();
+				}
+				if ($hasTransaction) {
+					$this->bd->rollBack();
+				}
+			} elseif ($name === 'hash') {	//v1.2
+				$stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN hash BINARY(16)');
+				return $stm && $stm->execute();
+			}
+		} catch (Exception $e) {
+			Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage());
+			if ($hasTransaction) {
+				$this->bd->rollBack();
+			}
+		}
+		return false;
+	}
+
+	protected function autoAddColumn($errorInfo) {
+		if (isset($errorInfo[0])) {
+			if ($errorInfo[0] == '42S22') {	//ER_BAD_FIELD_ERROR
+				foreach (array('lastSeen', 'hash') as $column) {
+					if (stripos($errorInfo[2], $column) !== false) {
+						return $this->addColumn($column);
 					}
 				}
 			}
@@ -82,7 +92,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 				return $this->addEntry($valuesTmp);
 			} elseif ((int)($info[0] / 1000) !== 23) {	//Filter out "SQLSTATE Class code 23: Constraint Violation" because of expected duplicate entries
 				Minz_Log::error('SQL error addEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
-				. ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']);
+				. ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']. ' ' . $this->addEntryPrepared);
 			}
 			return false;
 		}
@@ -597,7 +607,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			}
 			return $result;
 		} else {
-			
 			$info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo();
 			if ($this->autoAddColumn($info)) {
 				return $this->listHashForFeedGuids($id_feed, $guids);
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index ffe0f037c..ff049d813 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -2,6 +2,21 @@
 
 class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 
+	protected function autoAddColumn($errorInfo) {
+		if (empty($errorInfo[0]) || $errorInfo[0] == '42S22') {	//ER_BAD_FIELD_ERROR
+			if ($tableInfo = $this->bd->query("SELECT sql FROM sqlite_master where name='entry'")) {
+				$showCreate = $tableInfo->fetchColumn();
+				Minz_Log::debug('FreshRSS_EntryDAOSQLite::autoAddColumn: ' . $showCreate);
+				foreach (array('lastSeen', 'hash') as $column) {
+					if (stripos($showCreate, $column) === false) {
+						return $this->addColumn($column);
+					}
+				}
+			}
+		}
+		return false;
+	}
+
 	protected function sqlConcat($s1, $s2) {
 		return $s1 . '||' . $s2;
 	}
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index 76025ff53..475d39286 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -330,11 +330,12 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		     . '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);
 
-		$id_max = intval($date_min) . '000000';
-
-		$stm->bindParam(':id_feed', $id, PDO::PARAM_INT);
-		$stm->bindParam(':id_max', $id_max, PDO::PARAM_STR);
-		$stm->bindParam(':keep', $keep, PDO::PARAM_INT);
+		if ($stm) {
+			$id_max = intval($date_min) . '000000';
+			$stm->bindParam(':id_feed', $id, PDO::PARAM_INT);
+			$stm->bindParam(':id_max', $id_max, PDO::PARAM_STR);
+			$stm->bindParam(':keep', $keep, PDO::PARAM_INT);
+		}
 
 		if ($stm && $stm->execute()) {
 			return $stm->rowCount();
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index afdd821b2..9c6af405d 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -41,7 +41,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 	`content_bin` blob,	-- v0.7
 	`link` varchar(1023) CHARACTER SET latin1 NOT NULL,
 	`date` int(11),	-- Until year 2038
-	`lastSeen` INT(11) NOT NULL,	-- v1.2, Until year 2038
+	`lastSeen` INT(11) DEFAULT 0,	-- v1.2, Until year 2038
 	`hash` BINARY(16),	-- v1.2
 	`is_read` boolean NOT NULL DEFAULT 0,
 	`is_favorite` boolean NOT NULL DEFAULT 0,
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 7517ead45..77e8e094c 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -39,7 +39,7 @@ $SQL_CREATE_TABLES = array(
 	`content` text,
 	`link` varchar(1023) NOT NULL,
 	`date` int(11),	-- Until year 2038
-	`lastSeen` INT(11) NOT NULL,	-- v1.2, Until year 2038
+	`lastSeen` INT(11) DEFAULT 0,	-- v1.2, Until year 2038
 	`hash` BINARY(16),	-- v1.2
 	`is_read` boolean NOT NULL DEFAULT 0,
 	`is_favorite` boolean NOT NULL DEFAULT 0,
diff --git a/app/install.php b/app/install.php
index 177173fdb..86afb9318 100644
--- a/app/install.php
+++ b/app/install.php
@@ -168,8 +168,10 @@ function saveStep3() {
 			$_SESSION['bd_prefix_user'] = $_SESSION['bd_prefix'] .(empty($_SESSION['default_user']) ? '' :($_SESSION['default_user'] . '_'));
 		}
 
+		//TODO: load `config.default.php` as default
 		$config_array = array(
 			'environment' => 'production',
+			'simplepie_syslog_enabled' => true,
 			'salt' => $_SESSION['salt'],
 			'title' => $_SESSION['title'],
 			'default_user' => $_SESSION['default_user'],
diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php
index ac7a1bed7..3e8ec1f43 100644
--- a/lib/Minz/ModelPdo.php
+++ b/lib/Minz/ModelPdo.php
@@ -134,4 +134,9 @@ class MinzPDO extends PDO {
 		MinzPDO::check($statement);
 		return parent::exec($statement);
 	}
+
+	public function query($statement) {
+		MinzPDO::check($statement);
+		return parent::query($statement);
+	}
 }
-- 
cgit v1.2.3


From 7c1b5e322cca0134f57b3a436129985ba9170b9f Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
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/CategoryDAO.php         |   7 +-
 app/Models/ConfigurationSetter.php |   1 +
 app/Models/EntryDAO.php            |   2 +-
 app/Models/EntryDAOPGSQL.php       |  92 ++++++++++++++++++
 app/Models/Factory.php             |  42 ++++----
 app/Models/FeedDAO.php             |   2 +-
 app/Models/StatsDAO.php            |   8 +-
 app/Models/StatsDAOPGSQL.php       | 192 +++++++++++++++++++++++++++++++++++++
 app/SQL/install.sql.mysql.php      |   2 +
 app/SQL/install.sql.pgsql.php      |  91 ++++++++++++++++++
 app/install.php                    |  31 +++++-
 lib/Minz/ModelPdo.php              |  77 +++++++++------
 p/scripts/install.js               |   6 +-
 13 files changed, 495 insertions(+), 58 deletions(-)
 create mode 100644 app/Models/EntryDAOPGSQL.php
 create mode 100644 app/Models/StatsDAOPGSQL.php
 create mode 100644 app/SQL/install.sql.pgsql.php

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index fc431553e..a44edb0f6 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -10,7 +10,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		);
 
 		if ($stm && $stm->execute($values)) {
-			return $this->bd->lastInsertId();
+			return $this->bd->lastInsertId('"' . parent::prefix . 'category_id_seq"');
 		} else {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error addCategory: ' . $info[2]);
@@ -207,12 +207,13 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 
 		$previousLine = null;
 		$feedsDao = array();
+		$feedDao = FreshRSS_Factory::createFeedDAO();
 		foreach ($listDAO as $line) {
 			if ($previousLine['c_id'] != null && $line['c_id'] !== $previousLine['c_id']) {
 				// End of the current category, we add it to the $list
 				$cat = new FreshRSS_Category(
 					$previousLine['c_name'],
-					FreshRSS_FeedDAO::daoToFeed($feedsDao, $previousLine['c_id'])
+					$feedDao->daoToFeed($feedsDao, $previousLine['c_id'])
 				);
 				$cat->_id($previousLine['c_id']);
 				$list[$previousLine['c_id']] = $cat;
@@ -228,7 +229,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		if ($previousLine != null) {
 			$cat = new FreshRSS_Category(
 				$previousLine['c_name'],
-				FreshRSS_FeedDAO::daoToFeed($feedsDao, $previousLine['c_id'])
+				$feedDao->daoToFeed($feedsDao, $previousLine['c_id'])
 			);
 			$cat->_id($previousLine['c_id']);
 			$list[$previousLine['c_id']] = $cat;
diff --git a/app/Models/ConfigurationSetter.php b/app/Models/ConfigurationSetter.php
index e472b1e7f..988e83356 100644
--- a/app/Models/ConfigurationSetter.php
+++ b/app/Models/ConfigurationSetter.php
@@ -287,6 +287,7 @@ class FreshRSS_ConfigurationSetter {
 
 		switch ($value['type']) {
 		case 'mysql':
+		case 'pgsql':
 			if (empty($value['host']) ||
 					empty($value['user']) ||
 					empty($value['base']) ||
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() {
diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php
new file mode 100644
index 000000000..95c12ff5d
--- /dev/null
+++ b/app/Models/EntryDAOPGSQL.php
@@ -0,0 +1,92 @@
+<?php
+
+class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAO {
+
+	protected function addColumn($name) {
+		return false;
+	}
+
+	protected function updateCacheUnreads($catId = false, $feedId = false) {
+		return true;	//done via triggers
+	}
+
+	/**
+	 * Mark all the articles in a feed as read.
+	 * There is a fail safe to prevent to mark as read articles that are
+	 * loaded during the mark as read action. Then the cache is updated.
+	 *
+	 * If $idMax equals 0, a deprecated debug message is logged
+	 *
+	 * @param integer $id_feed feed ID
+	 * @param integer $idMax fail safe article ID
+	 * @return integer affected rows
+	 */
+	public function markReadFeed($id_feed, $idMax = 0) {
+		if ($idMax == 0) {
+			$idMax = time() . '000000';
+			Minz_Log::debug('Calling markReadFeed(0) is deprecated!');
+		}
+		$this->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 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;
+		}
+	}
+
+	public function optimizeTable() {
+		return null;
+	}
+
+	public function size($all = true) {
+		$db = FreshRSS_Context::$system_conf->db;
+		$sql = 'SELECT pg_size_pretty(pg_database_size(?))';
+		$values = array($db['base']);
+		$stm = $this->bd->prepare($sql);
+		$stm->execute($values);
+		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+		return $res[0];
+	}
+
+}
diff --git a/app/Models/Factory.php b/app/Models/Factory.php
index db09d155d..764987c46 100644
--- a/app/Models/Factory.php
+++ b/app/Models/Factory.php
@@ -4,37 +4,47 @@ class FreshRSS_Factory {
 
 	public static function createFeedDao($username = null) {
 		$conf = Minz_Configuration::get('system');
-		if ($conf->db['type'] === 'sqlite') {
-			return new FreshRSS_FeedDAOSQLite($username);
-		} else {
-			return new FreshRSS_FeedDAO($username);
+		switch ($conf->db['type']) {
+			case 'sqlite':
+				return new FreshRSS_FeedDAOSQLite($username);
+			default:
+				return new FreshRSS_FeedDAO($username);
 		}
 	}
 
 	public static function createEntryDao($username = null) {
 		$conf = Minz_Configuration::get('system');
-		if ($conf->db['type'] === 'sqlite') {
-			return new FreshRSS_EntryDAOSQLite($username);
-		} else {
-			return new FreshRSS_EntryDAO($username);
+		switch ($conf->db['type']) {
+			case 'sqlite':
+				return new FreshRSS_EntryDAOSQLite($username);
+			case 'pgsql':
+				return new FreshRSS_EntryDAOPGSQL($username);
+			default:
+				return new FreshRSS_EntryDAO($username);
 		}
 	}
 
 	public static function createStatsDAO($username = null) {
 		$conf = Minz_Configuration::get('system');
-		if ($conf->db['type'] === 'sqlite') {
-			return new FreshRSS_StatsDAOSQLite($username);
-		} else {
-			return new FreshRSS_StatsDAO($username);
+		switch ($conf->db['type']) {
+			case 'sqlite':
+				return new FreshRSS_StatsDAOSQLite($username);
+			case 'pgsql':
+				return new FreshRSS_StatsDAOPGSQL($username);
+			default:
+				return new FreshRSS_StatsDAO($username);
 		}
 	}
 
 	public static function createDatabaseDAO($username = null) {
 		$conf = Minz_Configuration::get('system');
-		if ($conf->db['type'] === 'sqlite') {
-			return new FreshRSS_DatabaseDAOSQLite($username);
-		} else {
-			return new FreshRSS_DatabaseDAO($username);
+		switch ($conf->db['type']) {
+			case 'sqlite':
+				return new FreshRSS_DatabaseDAOSQLite($username);
+			case 'pgsql':
+				return new FreshRSS_DatabaseDAOPGSQL($username);
+			default:
+				return new FreshRSS_DatabaseDAO($username);
 		}
 	}
 
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index 475d39286..f29dac9c0 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -16,7 +16,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		);
 
 		if ($stm && $stm->execute($values)) {
-			return $this->bd->lastInsertId();
+			return $this->bd->lastInsertId('"' . parent::prefix . 'feed_id_seq"');
 		} else {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error addFeed: ' . $info[2]);
diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 5ca333396..e18ba4748 100644
--- a/app/Models/StatsDAO.php
+++ b/app/Models/StatsDAO.php
@@ -37,10 +37,10 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo {
 			$filter .= "AND e.id_feed = {$feed}";
 		}
 		$sql = <<<SQL
-SELECT COUNT(1) AS `total`,
-COUNT(1) - SUM(e.is_read) AS `unread`,
-SUM(e.is_read) AS `read`,
-SUM(e.is_favorite) AS `favorite`
+SELECT COUNT(1) AS total,
+COUNT(1) - SUM(e.is_read) AS unread,
+SUM(e.is_read) AS read,
+SUM(e.is_favorite) AS favorite
 FROM {$this->prefix}entry AS e
 , {$this->prefix}feed AS f
 WHERE e.id_feed = f.id
diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php
new file mode 100644
index 000000000..0bde72e58
--- /dev/null
+++ b/app/Models/StatsDAOPGSQL.php
@@ -0,0 +1,192 @@
+<?php
+
+class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO {
+
+	/**
+	 * Calculates entry repartition for the selection.
+	 * The repartition includes:
+	 *   - total entries
+	 *   - read entries
+	 *   - unread entries
+	 *   - favorite entries
+	 *
+	 * @param null|integer $feed feed id
+	 * @param boolean $only_main
+	 * @return array
+	 */
+	public function calculateEntryRepartitionPerFeed($feed = null, $only_main = false) {
+		$filter = '';
+		if ($only_main) {
+			$filter .= 'AND f.priority = 10';
+		}
+		if (!is_null($feed)) {
+			$filter .= "AND e.id_feed = {$feed}";
+		}
+		$sql = <<<SQL
+SELECT COUNT(1) AS total,
+COUNT(1) - SUM(case when e.is_read then 1 else 0 end) AS unread,
+SUM(case when e.is_read then 1 else 0 end) AS read,
+SUM(case when e.is_favorite then 1 else 0 end) AS favorite
+FROM "{$this->prefix}entry" AS e
+, "{$this->prefix}feed" AS f
+WHERE e.id_feed = f.id
+{$filter}
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+
+		return $res[0];
+	}
+
+	/**
+	 * Calculates entry count per day on a 30 days period.
+	 * Returns the result as a JSON string.
+	 *
+	 * @return string
+	 */
+	public function calculateEntryCount() {
+		$count = $this->initEntryCountArray();
+		$period = self::ENTRY_COUNT_PERIOD;
+
+		// Get stats per day for the last 30 days
+		$sql = <<<SQL
+SELECT to_timestamp(e.date) - NOW() AS day,
+COUNT(1) AS count
+FROM "{$this->prefix}entry" AS e
+WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '1 DAY'
+GROUP BY day
+ORDER BY day ASC
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+
+		foreach ($res as $value) {
+			$count[$value['day']] = (int) $value['count'];
+		}
+
+		return $this->convertToSerie($count);
+	}
+
+	/**
+	 * Calculates entry average per day on a 30 days period.
+	 *
+	 * @return integer
+	 */
+	public function calculateEntryAverage() {
+		$period = self::ENTRY_COUNT_PERIOD;
+
+		// Get stats per day for the last 30 days
+		$sql = <<<SQL
+SELECT COUNT(1) / {$period} AS average
+FROM "{$this->prefix}entry" AS e
+WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '1 DAY'
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetch(PDO::FETCH_NAMED);
+
+		return round($res['average'], 2);
+	}
+
+	/**
+	 * Calculates the number of article per hour of the day per feed
+	 *
+	 * @param integer $feed id
+	 * @return string
+	 */
+	public function calculateEntryRepartitionPerFeedPerHour($feed = null) {
+		return $this->calculateEntryRepartitionPerFeedPerPeriod('hour', $feed);
+	}
+
+	/**
+	 * Calculates the number of article per day of week per feed
+	 *
+	 * @param integer $feed id
+	 * @return string
+	 */
+	public function calculateEntryRepartitionPerFeedPerDayOfWeek($feed = null) {
+		return $this->calculateEntryRepartitionPerFeedPerPeriod('day', $feed);
+	}
+
+	/**
+	 * Calculates the number of article per month per feed
+	 *
+	 * @param integer $feed
+	 * @return string
+	 */
+	public function calculateEntryRepartitionPerFeedPerMonth($feed = null) {
+		return $this->calculateEntryRepartitionPerFeedPerPeriod('month', $feed);
+	}
+
+	/**
+	 * Calculates the number of article per period per feed
+	 *
+	 * @param string $period format string to use for grouping
+	 * @param integer $feed id
+	 * @return string
+	 */
+	protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) {
+		$restrict = '';
+		if ($feed) {
+			$restrict = "WHERE e.id_feed = {$feed}";
+		}
+		$sql = <<<SQL
+SELECT extract( {$period} from to_timestamp(e.date)) AS period
+, COUNT(1) AS count
+FROM "{$this->prefix}entry" AS e
+{$restrict}
+GROUP BY period
+ORDER BY period ASC
+SQL;
+
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_NAMED);
+
+		foreach ($res as $value) {
+			$repartition[(int) $value['period']] = (int) $value['count'];
+		}
+
+		return $this->convertToSerie($repartition);
+	}
+
+	/**
+	 * Calculates the average number of article per feed
+	 *
+	 * @param float $period number used to divide the number of day in the period
+	 * @param integer $feed id
+	 * @return integer
+	 */
+	protected function calculateEntryAveragePerFeedPerPeriod($period, $feed = null) {
+		$restrict = '';
+		if ($feed) {
+			$restrict = "WHERE e.id_feed = {$feed}";
+		}
+		$sql = <<<SQL
+SELECT COUNT(1) AS count
+, MIN(date) AS date_min
+, MAX(date) AS date_max
+FROM "{$this->prefix}entry" AS e
+{$restrict}
+SQL;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetch(PDO::FETCH_NAMED);
+		$date_min = new \DateTime();
+		$date_min->setTimestamp($res['date_min']);
+		$date_max = new \DateTime();
+		$date_max->setTimestamp($res['date_max']);
+		$interval = $date_max->diff($date_min, true);
+		$interval_in_days = $interval->format('%a');
+		if ($interval_in_days <= 0) {
+			// Surely only one article.
+			// We will return count / (period/period) == count.
+			$interval_in_days = $period;
+		}
+
+		return $res['count'] / ($interval_in_days / $period);
+	}
+
+}
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index c78839ef7..92a00aecc 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -1,4 +1,6 @@
 <?php
+define('SQL_CREATE_DB', 'CREATE DATABASE IF NOT EXISTS %1$s DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;');
+
 define('SQL_CREATE_TABLES', '
 CREATE TABLE IF NOT EXISTS `%1$scategory` (
 	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
new file mode 100644
index 000000000..0f243bdb5
--- /dev/null
+++ b/app/SQL/install.sql.pgsql.php
@@ -0,0 +1,91 @@
+<?php
+define('SQL_CREATE_TABLES', '
+CREATE TABLE IF NOT EXISTS "%1$scategory" (
+	id SERIAL PRIMARY KEY,
+	name varchar(255) UNIQUE NOT NULL
+)
+
+CREATE TABLE IF NOT EXISTS "%1$sfeed" (
+	id SERIAL PRIMARY KEY,
+	url varchar(511) UNIQUE NOT NULL,
+	category SMALLINT DEFAULT 0,
+	name varchar(255) NOT NULL,
+	website varchar(255),
+	description text,
+	lastUpdate int DEFAULT 0,
+	priority smallint NOT NULL DEFAULT 10,
+	pathEntries varchar(511) DEFAULT NULL,
+	httpAuth varchar(511) DEFAULT NULL,
+	error boolean DEFAULT FALSE,
+	keep_history INT NOT NULL DEFAULT -2,
+	ttl INT NOT NULL DEFAULT -2,
+	cache_nbEntries int DEFAULT 0,
+	cache_nbUnreads int DEFAULT 0,
+	FOREIGN KEY (category) REFERENCES "%1$scategory" (id) ON DELETE SET NULL ON UPDATE CASCADE
+)
+CREATE INDEX name_index ON "%1$sfeed" (name)
+CREATE INDEX priority_index ON "%1$sfeed" (priority)
+CREATE INDEX keep_history_index ON "%1$sfeed" (keep_history)
+
+CREATE TABLE IF NOT EXISTS "%1$sentry" (
+	id bigint NOT NULL PRIMARY KEY,
+	guid varchar(760) UNIQUE NOT NULL,
+	title varchar(255) NOT NULL,
+	author varchar(255),
+	content_bin text,
+	link varchar(1023) NOT NULL,
+	date INT,
+	lastSeen INT DEFAULT 0,
+	hash BYTEA,
+	is_read boolean NOT NULL DEFAULT false,
+	is_favorite boolean NOT NULL DEFAULT false,
+	id_feed SMALLINT,
+	tags varchar(1023),
+	FOREIGN KEY (id_feed) REFERENCES "%1$sfeed" (id) ON DELETE CASCADE ON UPDATE CASCADE
+)
+CREATE INDEX is_favorite_index ON "%1$sentry" (is_favorite)
+CREATE INDEX is_read_index ON "%1$sentry" (is_read)
+CREATE INDEX entry_lastSeen_index ON "%1$sentry" (lastSeen)
+
+CREATE OR REPLACE FUNCTION update_unread_feed() RETURNS TRIGGER AS $$
+	BEGIN
+		UPDATE "%1$sfeed" 
+			SET cache_nbUnreads=(SELECT COUNT(*) FROM "%1$sentry" WHERE id_feed=OLD.id_feed AND NOT is_read)
+			WHERE id=OLD.id_feed;
+		return NULL;
+	end;
+	$$ LANGUAGE PLPGSQL;
+
+CREATE TRIGGER update_unread_feed
+	AFTER UPDATE OF is_read OR DELETE OR INSERT ON "%1$sentry"
+	FOR EACH ROW
+	EXECUTE PROCEDURE update_unread_feed();
+
+CREATE OR REPLACE FUNCTION reset_feed_seq() RETURNS TRIGGER AS $$
+	BEGIN
+		PERFORM 1 FROM "%1$sfeed";
+		IF NOT FOUND THEN
+			ALTER SEQUENCE IF EXISTS "%1$sfeed_id_seq" RESTART;
+		END IF;
+		return NULL;
+	end;
+	$$ LANGUAGE PLPGSQL;
+
+CREATE TRIGGER reset_feed_seq
+	AFTER DELETE ON "%1$sfeed"
+	FOR EACH STATEMENT
+	EXECUTE PROCEDURE reset_feed_seq();
+
+
+CREATE OR REPLACE RULE check_constraints_on_entry AS ON INSERT TO "%1$sentry" WHERE EXISTS(SELECT 1 FROM "%1$sentry" WHERE guid=NEW.guid) DO INSTEAD NOTHING;
+CREATE OR REPLACE RULE check_constraints_on_feed AS ON INSERT TO "%1$sfeed" WHERE EXISTS(SELECT 1 FROM "%1$sfeed" WHERE url=NEW.url) DO INSTEAD NOTHING;
+CREATE OR REPLACE RULE check_constraints_on_category AS ON INSERT TO "%1$scategory" WHERE EXISTS(SELECT 1 FROM "%1$scategory" WHERE name=NEW.name) DO INSTEAD NOTHING;
+CREATE OR REPLACE RULE check_constraints_on_category as on update to "%1$scategory" WHERE EXISTS(SELECT 1 FROM "%1$scategory" WHERE name=NEW.name) DO INSTEAD NOTHING;
+
+
+INSERT IGNORE INTO "%1$scategory" (id, name) VALUES(1, "%2$s");
+INSERT IGNORE INTO "%1$sfeed" (url, category, name, website, description, ttl) VALUES("http://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "http://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400);
+INSERT IGNORE INTO "%1$sfeed" (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS @ GitHub", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);
+');
+
+define('SQL_DROP_TABLES', 'DROP TABLES "%1$sentry", "%1$sfeed", "%1$scategory"');
diff --git a/app/install.php b/app/install.php
index 62695ceb6..ffff9f8e9 100644
--- a/app/install.php
+++ b/app/install.php
@@ -19,8 +19,6 @@ if (isset($_GET['step'])) {
 	define('STEP', 0);
 }
 
-define('SQL_CREATE_DB', 'CREATE DATABASE IF NOT EXISTS %1$s DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;');
-
 if (STEP === 3 && isset($_POST['type'])) {
 	$_SESSION['bd_type'] = $_POST['type'];
 }
@@ -33,6 +31,9 @@ if (isset($_SESSION['bd_type'])) {
 	case 'sqlite':
 		include(APP_PATH . '/SQL/install.sql.sqlite.php');
 		break;
+	case 'pgsql':
+		include(APP_PATH . '/SQL/install.sql.pgsql.php');
+		break;
 	}
 }
 
@@ -301,7 +302,8 @@ function checkStep1() {
 	$curl = extension_loaded('curl');
 	$pdo_mysql = extension_loaded('pdo_mysql');
 	$pdo_sqlite = extension_loaded('pdo_sqlite');
-	$pdo = $pdo_mysql || $pdo_sqlite;
+	$pdo_pgsql = extension_loaded('pdo_pgsql');
+	$pdo = $pdo_mysql || $pdo_sqlite || $pdo_pgsql;
 	$pcre = extension_loaded('pcre');
 	$ctype = extension_loaded('ctype');
 	$dom = class_exists('DOMDocument');
@@ -319,6 +321,7 @@ function checkStep1() {
 		'curl' => $curl ? 'ok' : 'ko',
 		'pdo-mysql' => $pdo_mysql ? 'ok' : 'ko',
 		'pdo-sqlite' => $pdo_sqlite ? 'ok' : 'ko',
+		'pdo-pgsql' => $pdo_pgsql ? 'ok' : 'ko',
 		'pdo' => $pdo ? 'ok' : 'ko',
 		'pcre' => $pcre ? 'ok' : 'ko',
 		'ctype' => $ctype ? 'ok' : 'ko',
@@ -435,6 +438,22 @@ function checkBD() {
 				PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
 			);
 			break;
+		case 'pgsql':
+			$driver_options = array(
+				PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
+			);
+
+			try {	// on ouvre une connexion juste pour créer la base si elle n'existe pas
+				$str = 'pgsql:host=' . $_SESSION['bd_host'] . ';';
+				$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) {
+			}
+
+			// on écrase la précédente connexion en sélectionnant la nouvelle BDD
+			$str = 'pgsql:host=' . $_SESSION['bd_host'] . ';dbname=' . $_SESSION['bd_base'];
+			break;
 		default:
 			return false;
 		}
@@ -708,6 +727,12 @@ function printStep3() {
 					SQLite
 				</option>
 				<?php }?>
+				<?php if (extension_loaded('pdo_pgsql')) {?>
+				<option value="sqlite"
+					<?php echo(isset($_SESSION['bd_type']) && $_SESSION['bd_type'] === 'pgsql') ? 'selected="selected"' : ''; ?>>
+					PostgreSQL
+				</option>
+				<?php }?>
 				</select>
 			</div>
 		</div>
diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php
index 845aecaae..b98a26d06 100644
--- a/lib/Minz/ModelPdo.php
+++ b/lib/Minz/ModelPdo.php
@@ -55,36 +55,36 @@ class Minz_ModelPdo {
 		$driver_options = isset($conf->db['pdo_options']) && is_array($conf->db['pdo_options']) ? $conf->db['pdo_options'] : array();
 
 		try {
-			$type = $db['type'];
-			if ($type === 'mysql') {
-				$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 . '_';
-			} elseif ($type === 'sqlite') {
-				$string = 'sqlite:' . join_path(DATA_PATH, 'users', $currentUser, 'db.sqlite');
-				//$driver_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
-				$this->prefix = '';
-			} else {
-				throw new Minz_PDOConnectionException(
-					'Invalid database type!',
-					$db['user'], Minz_Exception::ERROR
-				);
-			}
-			self::$sharedDbType = $type;
-			self::$sharedPrefix = $this->prefix;
-
-			$this->bd = new MinzPDO(
-				$string,
-				$db['user'],
-				$db['password'],
-				$driver_options
-			);
-			if ($type === 'sqlite') {
-				$this->bd->exec('PRAGMA foreign_keys = ON;');
+			switch ($db['type']) {
+				case 'mysql':
+					$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);
+					//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->exec('PRAGMA foreign_keys = ON;');
+					break;
+				case 'pgsql':
+					$string = 'pgsql:host=' . $db['host'] . ';dbname=' . $db['base'] . ';charset=utf8';
+					$this->prefix = $db['prefix'] . $currentUser . '_';
+					$this->bd = new MinzPDOPGSQL($string, $db['user'], $db['password'], $driver_options);
+					$this->bd->exec("SET NAMES 'UTF8';");
+					break;
+				default:
+					throw new Minz_PDOConnectionException(
+						'Invalid database type!',
+						$db['user'], Minz_Exception::ERROR
+					);
+					break;
 			}
 			self::$sharedBd = $this->bd;
+			self::$sharedDbType = $db['type'];
+			self::$sharedPrefix = $this->prefix;
 		} catch (Exception $e) {
 			throw new Minz_PDOConnectionException(
 				$string,
@@ -119,18 +119,39 @@ class MinzPDO extends PDO {
 		}
 	}
 
+	protected function compatibility($statement) {
+		return $statement;
+	}
+
 	public function prepare($statement, $driver_options = array()) {
 		MinzPDO::check($statement);
+		$statement = MinzPDO::compatibility($statement);
 		return parent::prepare($statement, $driver_options);
 	}
 
 	public function exec($statement) {
 		MinzPDO::check($statement);
+		$statement = MinzPDO::compatibility($statement);
 		return parent::exec($statement);
 	}
 
 	public function query($statement) {
 		MinzPDO::check($statement);
+		$statement = MinzPDO::compatibility($statement);
 		return parent::query($statement);
 	}
+
+	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);
+	}
+
+	public function lastInsertId($name = null) {
+		return parent::lastInsertId($name);
+	}
 }
diff --git a/p/scripts/install.js b/p/scripts/install.js
index 57fc2450a..3e1db57b5 100644
--- a/p/scripts/install.js
+++ b/p/scripts/install.js
@@ -42,13 +42,15 @@ if (auth_type) {
 function mySqlShowHide() {
 	var mysql = document.getElementById('mysql');
 	if (mysql) {
-		mysql.style.display = document.getElementById('type').value === 'mysql' ? 'block' : 'none';
-		if (document.getElementById('type').value !== 'mysql') {
+		if (document.getElementById('type').value === 'sqlite') {
 			document.getElementById('host').value = '';
 			document.getElementById('user').value = '';
 			document.getElementById('pass').value = '';
 			document.getElementById('base').value = '';
 			document.getElementById('prefix').value = '';
+			mysql.style.display = 'none';
+		} else {
+			mysql.style.display = 'block';
 		}
 	}
 }
-- 
cgit v1.2.3


From e315192c4b3df89dddb1ac37c6c7a01531d7952f Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sun, 14 Aug 2016 11:06:31 +0200
Subject: Bug static

---
 app/Models/CategoryDAO.php | 2 +-
 app/Models/FeedDAO.php     | 2 +-
 2 files changed, 2 insertions(+), 2 deletions(-)

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index a44edb0f6..3b1519c20 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -10,7 +10,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		);
 
 		if ($stm && $stm->execute($values)) {
-			return $this->bd->lastInsertId('"' . parent::prefix . 'category_id_seq"');
+			return $this->bd->lastInsertId('"' . $this->prefix . 'category_id_seq"');
 		} else {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error addCategory: ' . $info[2]);
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index f29dac9c0..ce65ed8b1 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -16,7 +16,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		);
 
 		if ($stm && $stm->execute($values)) {
-			return $this->bd->lastInsertId('"' . parent::prefix . 'feed_id_seq"');
+			return $this->bd->lastInsertId('"' . $this->prefix . 'feed_id_seq"');
 		} else {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error addFeed: ' . $info[2]);
-- 
cgit v1.2.3


From f66be86e41d89214688a28243b412ffa43ce500d Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
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/FeedDAO.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 05cabe99ae48a187a77e0246dfffc60f2434b5e5 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
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/FeedDAO.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 fe65eec5bbdaee37177e3673e31e241b1f1b938d Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre.alapetite@alexandra.dk>
Date: Wed, 5 Oct 2016 17:48:24 +0200
Subject: Better multiuser update

---
 app/Controllers/feedController.php |  9 +++++----
 app/Models/FeedDAO.php             | 10 +++++++---
 2 files changed, 12 insertions(+), 7 deletions(-)

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php
index d85a53446..dadc8aa83 100755
--- a/app/Controllers/feedController.php
+++ b/app/Controllers/feedController.php
@@ -271,13 +271,14 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 			if ($ttl == -1) {
 				continue;	//Feed refresh is disabled
 			}
-			if ($feed->lastUpdate() < time() + 60 - ($ttl == -2 ? FreshRSS_Context::$user_conf->ttl_default : $ttl)) {
+			if ($feed->lastUpdate() + 10 >= time() - ($ttl == -2 ? FreshRSS_Context::$user_conf->ttl_default : $ttl)) {
 				//Too early to refresh from source, but check whether the feed was updated by another user
 				$mtime = $feed->cacheModifiedTime();
-				if ($feed->lastUpdate() >= $mtime + 10) {
+				if ($feed->lastUpdate() + 10 >= $mtime) {
 					continue;	//Nothing newer from other users
 				}
-				Minz_Log::debug($feed->url() . ' was recently updated by another user');
+				//Minz_Log::debug($feed->url() . ' was updated at ' . date('c', $mtime) . ' by another user');
+				//Will take advantage of the newer cache
 			}
 
 			if (!$feed->lock()) {
@@ -391,7 +392,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				}
 			}
 
-			$feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->inTransaction());
+			$feedDAO->updateLastUpdate($feed->id(), false, $entryDAO->inTransaction(), $mtime);
 			if ($entryDAO->inTransaction()) {
 				$entryDAO->commit();
 			}
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index ad3cb0c2e..c680d270c 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -82,7 +82,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		}
 	}
 
-	public function updateLastUpdate($id, $inError = 0, $updateCache = true) {
+	public function updateLastUpdate($id, $inError = false, $updateCache = true, $mtime = 0) {
 		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),'
@@ -95,9 +95,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			     . 'WHERE id=?';
 		}
 
+		if ($mtime <= 0) {
+			$mtime = time();
+		}
+
 		$values = array(
-			time(),
-			$inError,
+			$mtime,
+			$inError ? 1 : 0,
 			$id,
 		);
 
-- 
cgit v1.2.3


From 1893fc61e0e576519f878267fd877247445d1055 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
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/FeedDAO.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 <alexandre@alapetite.fr>
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/FeedDAO.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


From fcb9280fc87c159539f5832ab35f174cd515654e Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sun, 23 Oct 2016 13:37:48 +0200
Subject: CLI export

ZIP export, OPML export.
Corrected height of feeds select in Pafat theme.
https://github.com/FreshRSS/FreshRSS/pull/1338
https://github.com/FreshRSS/FreshRSS/issues/1039
https://github.com/FreshRSS/FreshRSS/issues/1277
---
 app/Controllers/importExportController.php | 84 +++++++++++++++++++-----------
 app/Models/FeedDAO.php                     |  7 +++
 app/views/importExport/index.phtml         |  2 +-
 cli/_cli.php                               |  4 +-
 cli/create-user.php                        | 14 ++---
 cli/delete-user.php                        |  2 +-
 cli/export-opml-for-user.php               | 24 +++++++++
 cli/export-zip-for-user.php                | 30 +++++++++++
 cli/import-for-user.php                    |  3 +-
 p/themes/Pafat/pafat.css                   |  3 --
 10 files changed, 128 insertions(+), 45 deletions(-)
 create mode 100644 cli/export-opml-for-user.php
 create mode 100644 cli/export-zip-for-user.php

(limited to 'app/Models/FeedDAO.php')

diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php
index fbebb2a78..bb22ac739 100644
--- a/app/Controllers/importExportController.php
+++ b/app/Controllers/importExportController.php
@@ -522,26 +522,21 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 		return $return;
 	}
 
-	/**
-	 * This action handles export action.
-	 *
-	 * This action must be reached by a POST request.
-	 *
-	 * Parameters are:
-	 *   - export_opml (default: false)
-	 *   - export_starred (default: false)
-	 *   - export_feeds (default: array()) a list of feed ids
-	 */
-	public function exportAction() {
-		if (!Minz_Request::isPost()) {
-			Minz_Request::forward(array('c' => 'importExport', 'a' => 'index'), true);
-		}
+	public function exportFile($export_opml = true, $export_starred = false, $export_feeds = array(), $maxFeedEntries = 50, $username = null) {
+		require_once(LIB_PATH . '/lib_opml.php');
 
-		$this->view->_useLayout(false);
+		$this->catDAO = new FreshRSS_CategoryDAO($username);
+		$this->entryDAO = FreshRSS_Factory::createEntryDao($username);
+		$this->feedDAO = FreshRSS_Factory::createFeedDao($username);
+
+		if ($export_feeds === true) {
+			//All feeds
+			$export_feeds = $this->feedDAO->listFeedsIds();
+		}
+		if (!is_array($export_feeds)) {
+			$export_feeds = array();
+		}
 
-		$export_opml = Minz_Request::param('export_opml', false);
-		$export_starred = Minz_Request::param('export_starred', false);
-		$export_feeds = Minz_Request::param('export_feeds', array());
 		$day = date('Y-m-d');
 
 		$export_files = array();
@@ -558,7 +553,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 			if ($feed) {
 				$filename = "feed_${day}_" . $feed->category() . '_'
 				          . $feed->id() . '.json';
-				$export_files[$filename] = $this->generateEntries('feed', $feed);
+				$export_files[$filename] = $this->generateEntries('feed', $feed, $maxFeedEntries);
 			}
 		}
 
@@ -566,18 +561,49 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 		if ($nb_files > 1) {
 			// If there are more than 1 file to export, we need a ZIP archive.
 			try {
-				$this->exportZip($export_files);
+				$this->sendZip($export_files);
 			} catch (Exception $e) {
-				# Oops, there is no ZIP extension!
-				Minz_Request::bad(_t('feedback.import_export.export_no_zip_extension'),
-				                  array('c' => 'importExport', 'a' => 'index'));
+				throw new FreshRSS_ZipMissing_Exception($e);
 			}
 		} elseif ($nb_files === 1) {
 			// Only one file? Guess its type and export it.
 			$filename = key($export_files);
 			$type = self::guessFileType($filename);
-			$this->exportFile('freshrss_' . $filename, $export_files[$filename], $type);
-		} else {
+			$this->sendFile('freshrss_' . $filename, $export_files[$filename], $type);
+		}
+		return $nb_files;
+	}
+
+	/**
+	 * This action handles export action.
+	 *
+	 * This action must be reached by a POST request.
+	 *
+	 * Parameters are:
+	 *   - export_opml (default: false)
+	 *   - export_starred (default: false)
+	 *   - export_feeds (default: array()) a list of feed ids
+	 */
+	public function exportAction() {
+		if (!Minz_Request::isPost()) {
+			Minz_Request::forward(array('c' => 'importExport', 'a' => 'index'), true);
+		}
+		$this->view->_useLayout(false);
+
+		$nb_files = 0;
+		try {
+			$nb_files = $this->exportFile(
+					Minz_Request::param('export_opml', false),
+					Minz_Request::param('export_starred', false),
+					Minz_Request::param('export_feeds', array())
+				);
+		} catch (FreshRSS_ZipMissing_Exception $zme) {
+			# Oops, there is no ZIP extension!
+			Minz_Request::bad(_t('feedback.import_export.export_no_zip_extension'),
+			                  array('c' => 'importExport', 'a' => 'index'));
+		}
+
+		if ($nb_files < 1) {
 			// Nothing to do...
 			Minz_Request::forward(array('c' => 'importExport', 'a' => 'index'), true);
 		}
@@ -606,7 +632,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 	 * @param FreshRSS_Feed $feed feed of which we want to get entries.
 	 * @return string the JSON file content.
 	 */
-	private function generateEntries($type, $feed = NULL) {
+	private function generateEntries($type, $feed = NULL, $maxFeedEntries = 50) {
 		$this->view->categories = $this->catDAO->listCategories();
 
 		if ($type == 'starred') {
@@ -621,7 +647,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 			$this->view->type = 'feed/' . $feed->id();
 			$this->view->entries = $this->entryDAO->listWhere(
 				'f', $feed->id(), FreshRSS_Entry::STATE_ALL, 'ASC',
-				FreshRSS_Context::$user_conf->posts_per_page
+				$maxFeedEntries
 			);
 			$this->view->feed = $feed;
 		}
@@ -635,7 +661,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 	 * @param array $files list of files where key is filename and value the content.
 	 * @throws Exception if Zip extension is not loaded.
 	 */
-	private function exportZip($files) {
+	private function sendZip($files) {
 		if (!extension_loaded('zip')) {
 			throw new Exception();
 		}
@@ -667,7 +693,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 	 * @param string $type the file type (opml, json_feed or json_starred).
 	 *                     If equals to unknown, nothing happens.
 	 */
-	private function exportFile($filename, $content, $type) {
+	private function sendFile($filename, $content, $type) {
 		if ($type === 'unknown') {
 			return;
 		}
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index b21f19b66..68398efd5 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -212,6 +212,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		}
 	}
 
+	public function listFeedsIds() {
+		$sql = 'SELECT id FROM `' . $this->prefix . 'feed`';
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		return $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+	}
+
 	public function listFeeds() {
 		$sql = 'SELECT * FROM `' . $this->prefix . 'feed` ORDER BY name';
 		$stm = $this->bd->prepare($sql);
diff --git a/app/views/importExport/index.phtml b/app/views/importExport/index.phtml
index c0bc412c3..c5049e3ea 100644
--- a/app/views/importExport/index.phtml
+++ b/app/views/importExport/index.phtml
@@ -44,7 +44,7 @@
 						$select_args = ' size="' . min(10, count($this->feeds)) .'" multiple="multiple"';
 					}
 				?>
-				<select name="export_feeds[]"<?php echo $select_args; ?>>
+				<select name="export_feeds[]"<?php echo $select_args; ?> size="10">
 					<?php echo extension_loaded('zip') ? '' : '<option></option>'; ?>
 					<?php foreach ($this->feeds as $feed) { ?>
 					<option value="<?php echo $feed->id(); ?>"><?php echo $feed->name(); ?></option>
diff --git a/cli/_cli.php b/cli/_cli.php
index d81d83d66..66506f07a 100644
--- a/cli/_cli.php
+++ b/cli/_cli.php
@@ -10,7 +10,7 @@ Minz_Configuration::register('system',
 	DATA_PATH . '/config.php',
 	DATA_PATH . '/config.default.php');
 FreshRSS_Context::$system_conf = Minz_Configuration::get('system');
-Minz_Translate::init();
+Minz_Translate::init('en');
 
 FreshRSS_Context::$isCli = true;
 
@@ -39,6 +39,6 @@ function cliInitUser($username) {
 }
 
 function done($ok) {
-	echo 'Result: ', ($ok ? 'success' : 'fail'), ".\n";
+	fwrite(STDERR, 'Result: ' . ($ok ? 'success' : 'fail') . "\n");
 	exit($ok ? 0 : 1);
 }
diff --git a/cli/create-user.php b/cli/create-user.php
index 387b503b6..243e65a35 100755
--- a/cli/create-user.php
+++ b/cli/create-user.php
@@ -4,16 +4,16 @@ require('_cli.php');
 
 $options = getopt('', array(
 		'user:',
-		'password::',
-		'api-password::',
-		'language::',
-		'email::',
-		'token::',
+		'password:',
+		'api-password:',
+		'language:',
+		'email:',
+		'token:',
 	));
 
 if (empty($options['user'])) {
-	fail('Usage: ' . basename(__FILE__) . " --user=username --password='password' --api-password='api_password'" .
-		" --language=en --email=user@example.net --token='longRandomString'");
+	fail('Usage: ' . basename(__FILE__) . " --user username --password 'password' --api-password 'api_password'" .
+		" --language en --email user@example.net --token 'longRandomString'");
 }
 $username = $options['user'];
 if (!ctype_alnum($username)) {
diff --git a/cli/delete-user.php b/cli/delete-user.php
index da48103f7..6f0e86e17 100755
--- a/cli/delete-user.php
+++ b/cli/delete-user.php
@@ -7,7 +7,7 @@ $options = getopt('', array(
 	));
 
 if (empty($options['user'])) {
-	fail('Usage: ' . basename(__FILE__) . " --user=username");
+	fail('Usage: ' . basename(__FILE__) . " --user username");
 }
 $username = $options['user'];
 if (!ctype_alnum($username)) {
diff --git a/cli/export-opml-for-user.php b/cli/export-opml-for-user.php
new file mode 100644
index 000000000..bce4efd63
--- /dev/null
+++ b/cli/export-opml-for-user.php
@@ -0,0 +1,24 @@
+#!/usr/bin/php
+<?php
+require('_cli.php');
+
+$options = getopt('', array(
+		'user:',
+	));
+
+if (empty($options['user'])) {
+	fail('Usage: ' . basename(__FILE__) . " --user username > /path/to/file.opml.xml'");
+}
+
+$username = cliInitUser($options['user']);
+
+fwrite(STDERR, 'FreshRSS exporting OPML for user “' . $username . "”…\n");
+
+$importController = new FreshRSS_importExport_Controller();
+
+$ok = false;
+$ok = $importController->exportFile(true, false, array(), 0, $username);
+
+invalidateHttpCache($username);
+
+done($ok);
diff --git a/cli/export-zip-for-user.php b/cli/export-zip-for-user.php
new file mode 100644
index 000000000..f8d24238b
--- /dev/null
+++ b/cli/export-zip-for-user.php
@@ -0,0 +1,30 @@
+#!/usr/bin/php
+<?php
+require('_cli.php');
+
+$options = getopt('', array(
+		'user:',
+		'max-feed-entries:',
+	));
+
+if (empty($options['user'])) {
+	fail('Usage: ' . basename(__FILE__) . " --user username --max-feed-entries 100 > /path/to/file.zip'");
+}
+
+$username = cliInitUser($options['user']);
+
+fwrite(STDERR, 'FreshRSS exporting ZIP for user “' . $username . "”…\n");
+
+$importController = new FreshRSS_importExport_Controller();
+
+$ok = false;
+try {
+	$ok = $importController->exportFile(true, true, true,
+	empty($options['max-feed-entries']) ? 100 : intval($options['max-feed-entries']),
+	$username);
+} catch (FreshRSS_ZipMissing_Exception $zme) {
+	fail('FreshRSS error: Lacking php-zip extension!');
+}
+invalidateHttpCache($username);
+
+done($ok);
diff --git a/cli/import-for-user.php b/cli/import-for-user.php
index 308786599..29084f062 100755
--- a/cli/import-for-user.php
+++ b/cli/import-for-user.php
@@ -5,11 +5,10 @@ require('_cli.php');
 $options = getopt('', array(
 		'user:',
 		'filename:',
-		'clear-existing',
 	));
 
 if (empty($options['user']) || empty($options['filename'])) {
-	fail('Usage: ' . basename(__FILE__) . " --user=username --filename='/path/to/file.ext' --clear-existing");
+	fail('Usage: ' . basename(__FILE__) . " --user username --filename /path/to/file.ext");
 }
 
 $username = cliInitUser($options['user']);
diff --git a/p/themes/Pafat/pafat.css b/p/themes/Pafat/pafat.css
index 5e46a63ca..abe808eab 100644
--- a/p/themes/Pafat/pafat.css
+++ b/p/themes/Pafat/pafat.css
@@ -48,9 +48,6 @@ input, select, textarea {
 	vertical-align: middle;
 }
 
-select{
-	height:29px;
-}
 option {
 	padding: 0 .5em;
 }
-- 
cgit v1.2.3