From fe18d12551f626c56257f825caf8f97a4a5461ce Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 30 Jul 2016 18:45:34 +0200
Subject: Update MySQL to utf8mb4 (full unicode) 🔥
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

* Requires MySQL 5.5.3+ (drop support for MySQL 5.0)
* Requires PHP 5.3.3+ (drop support for PHP 5.3.0)
https://github.com/FreshRSS/FreshRSS/issues/789#issuecomment-73878076
---
 app/Models/EntryDAO.php | 60 +++++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 51 insertions(+), 9 deletions(-)

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

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index f74055835..fa5d87b55 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -11,7 +11,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	}
 
 	protected function addColumn($name) {
-		Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn: ' . $name);
+		Minz_Log::warning('FreshRSS_EntryDAO::addColumn: ' . $name);
 		$hasTransaction = false;
 		try {
 			$stm = null;
@@ -38,7 +38,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 				return $stm && $stm->execute();
 			}
 		} catch (Exception $e) {
-			Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage());
+			Minz_Log::error('FreshRSS_EntryDAO::addColumn error: ' . $e->getMessage());
 			if ($hasTransaction) {
 				$this->bd->rollBack();
 			}
@@ -46,9 +46,44 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return false;
 	}
 
-	protected function autoAddColumn($errorInfo) {
+	private $triedUpdateToUtf8mb4 = false;
+
+	protected function updateToUtf8mb4() {
+		if ($this->triedUpdateToUtf8mb4) {
+			return false;
+		}
+		$this->triedUpdateToUtf8mb4 = true;
+		Minz_Log::warning('Updating MySQL to UTF8MB4...');
+		$db = FreshRSS_Context::$system_conf->db;
+		if ($db['type'] === 'mysql') {
+			include_once(APP_PATH . '/SQL/install.sql.mysql.php');
+			if (defined('SQL_UPDATE_UTF8MB4')) {
+				$hadTransaction = $this->bd->inTransaction();
+				if ($hadTransaction) {
+					$this->bd->commit();
+				}
+				$ok = false;
+				try {
+					$sql = sprintf(SQL_UPDATE_UTF8MB4, $this->prefix, $db['base']);
+					$stm = $this->bd->prepare($sql);
+					$ok = $stm->execute();
+				} catch (Exception $e) {
+					Minz_Log::error('FreshRSS_EntryDAO::updateToUtf8mb4 error: ' . $e->getMessage());
+				}
+				if ($hadTransaction) {
+					$this->bd->beginTransaction();
+					//NB: Transaction not starting. Why? (tested on PHP 7.0.8-0ubuntu and MySQL 5.7.13-0ubuntu)
+				}
+				return $ok;
+			}
+		}
+		return false;
+	}
+
+	protected function autoUpdateDb($errorInfo) {
 		if (isset($errorInfo[0])) {
-			if ($errorInfo[0] == '42S22') {	//ER_BAD_FIELD_ERROR
+			if ($errorInfo[0] === '42S22') {	//ER_BAD_FIELD_ERROR
+				//autoAddColumn
 				foreach (array('lastSeen', 'hash') as $column) {
 					if (stripos($errorInfo[2], $column) !== false) {
 						return $this->addColumn($column);
@@ -56,6 +91,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 				}
 			}
 		}
+		if (isset($errorInfo[1])) {
+			if ($errorInfo[1] == '1366') {	//ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
+				return $this->updateToUtf8mb4();
+			}
+		}
 		return false;
 	}
 
@@ -94,7 +134,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return $this->bd->lastInsertId();
 		} else {
 			$info = $this->addEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->addEntryPrepared->errorInfo();
-			if ($this->autoAddColumn($info)) {
+			if ($this->autoUpdateDb($info)) {
 				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]
@@ -145,7 +185,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return $this->bd->lastInsertId();
 		} else {
 			$info = $this->updateEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->updateEntryPrepared->errorInfo();
-			if ($this->autoAddColumn($info)) {
+			if ($this->autoUpdateDb($info)) {
 				return $this->updateEntry($valuesTmp);
 			}
 			Minz_Log::error('SQL error updateEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
@@ -615,7 +655,7 @@ 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)) {
+			if ($this->autoUpdateDb($info)) {
 				return $this->listHashForFeedGuids($id_feed, $guids);
 			}
 			Minz_Log::error('SQL error listHashForFeedGuids: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
@@ -636,7 +676,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return $stm->rowCount();
 		} else {
 			$info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo();
-			if ($this->autoAddColumn($info)) {
+			if ($this->autoUpdateDb($info)) {
 				return $this->updateLastSeen($id_feed, $guids);
 			}
 			Minz_Log::error('SQL error updateLastSeen: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
@@ -692,7 +732,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	public function optimizeTable() {
 		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`';	//MySQL
 		$stm = $this->bd->prepare($sql);
-		$stm->execute();
+		if ($stm) {
+			return $stm->execute();
+		}
 	}
 
 	public function size($all = false) {
-- 
cgit v1.2.3


From 2859eff94de87523a94b5a6ed84eaa94844f3fe9 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Mon, 1 Aug 2016 17:52:21 +0200
Subject: MySQL UTF8MB4 minor details

---
 app/Models/EntryDAO.php       | 2 +-
 app/SQL/install.sql.mysql.php | 4 ++--
 2 files changed, 3 insertions(+), 3 deletions(-)

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

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index fa5d87b55..c9e6f9742 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -53,11 +53,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return false;
 		}
 		$this->triedUpdateToUtf8mb4 = true;
-		Minz_Log::warning('Updating MySQL to UTF8MB4...');
 		$db = FreshRSS_Context::$system_conf->db;
 		if ($db['type'] === 'mysql') {
 			include_once(APP_PATH . '/SQL/install.sql.mysql.php');
 			if (defined('SQL_UPDATE_UTF8MB4')) {
+				Minz_Log::warning('Updating MySQL to UTF8MB4...');
 				$hadTransaction = $this->bd->inTransaction();
 				if ($hadTransaction) {
 					$this->bd->commit();
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index c8755c1ad..c78839ef7 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -67,12 +67,12 @@ define('SQL_UPDATE_UTF8MB4', '
 ALTER DATABASE `%2$s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 ALTER TABLE `%1$scategory` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-UPDATE `%1$scategory` SET name=SUBSTRING(name,1,190) where LENGTH(name) > 191;
+UPDATE `%1$scategory` SET name=SUBSTRING(name,1,190) WHERE LENGTH(name) > 191;
 ALTER TABLE `%1$scategory` MODIFY `name` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
 OPTIMIZE TABLE `%1$scategory`;
 
 ALTER TABLE `%1$sfeed` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-UPDATE `%1$sfeed` SET name=SUBSTRING(name,1,190) where LENGTH(name) > 191;
+UPDATE `%1$sfeed` SET name=SUBSTRING(name,1,190) WHERE LENGTH(name) > 191;
 ALTER TABLE `%1$sfeed` MODIFY `name` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
 ALTER TABLE `%1$sfeed` MODIFY `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 OPTIMIZE TABLE `%1$sfeed`;
-- 
cgit v1.2.3


From 32c734ef622f4ed83d212d3eb1d15d83d406bd86 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Thu, 18 Aug 2016 00:00:08 +0200
Subject: SQL mark search as read

https://github.com/FreshRSS/FreshRSS/issues/608
---
 app/Controllers/entryController.php |  19 ++++-
 app/Models/EntryDAO.php             | 150 ++++++++++++++++++++----------------
 app/layout/nav_menu.phtml           |   2 +
 app/views/helpers/pagination.phtml  |   2 +
 4 files changed, 102 insertions(+), 71 deletions(-)

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

diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php
index bff1073ef..c40588105 100755
--- a/app/Controllers/entryController.php
+++ b/app/Controllers/entryController.php
@@ -40,6 +40,17 @@ class FreshRSS_entry_Controller extends Minz_ActionController {
 		$get = Minz_Request::param('get');
 		$next_get = Minz_Request::param('nextGet', $get);
 		$id_max = Minz_Request::param('idMax', 0);
+		FreshRSS_Context::$search = new FreshRSS_Search(Minz_Request::param('search', ''));
+
+		FreshRSS_Context::$state = Minz_Request::param('state', 0);
+		if (FreshRSS_Context::isStateEnabled(FreshRSS_Entry::STATE_FAVORITE)) {
+			FreshRSS_Context::$state = FreshRSS_Entry::STATE_FAVORITE;
+		} elseif (FreshRSS_Context::isStateEnabled(FreshRSS_Entry::STATE_NOT_FAVORITE)) {
+			FreshRSS_Context::$state = FreshRSS_Entry::STATE_NOT_FAVORITE;
+		} else {
+			FreshRSS_Context::$state = 0;
+		}
+
 		$params = array();
 
 		$entryDAO = FreshRSS_Factory::createEntryDao();
@@ -58,16 +69,16 @@ class FreshRSS_entry_Controller extends Minz_ActionController {
 				$get = substr($get, 2);
 				switch($type_get) {
 				case 'c':
-					$entryDAO->markReadCat($get, $id_max);
+					$entryDAO->markReadCat($get, $id_max, FreshRSS_Context::$search, FreshRSS_Context::$state);
 					break;
 				case 'f':
-					$entryDAO->markReadFeed($get, $id_max);
+					$entryDAO->markReadFeed($get, $id_max, FreshRSS_Context::$search, FreshRSS_Context::$state);
 					break;
 				case 's':
-					$entryDAO->markReadEntries($id_max, true);
+					$entryDAO->markReadEntries($id_max, true, 0, FreshRSS_Context::$search);
 					break;
 				case 'a':
-					$entryDAO->markReadEntries($id_max);
+					$entryDAO->markReadEntries($id_max, false, 0, FreshRSS_Context::$search, FreshRSS_Context::$state);
 					break;
 				}
 
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index c9e6f9742..3bb8a720c 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -344,7 +344,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	 * @param integer $priorityMin
 	 * @return integer affected rows
 	 */
-	public function markReadEntries($idMax = 0, $onlyFavorites = false, $priorityMin = 0) {
+	public function markReadEntries($idMax = 0, $onlyFavorites = false, $priorityMin = 0, $filter = null, $state = 0) {
 		if ($idMax == 0) {
 			$idMax = time() . '000000';
 			Minz_Log::debug('Calling markReadEntries(0) is deprecated!');
@@ -359,8 +359,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			$sql .= ' AND f.priority > ' . intval($priorityMin);
 		}
 		$values = array($idMax);
-		$stm = $this->bd->prepare($sql);
-		if (!($stm && $stm->execute($values))) {
+
+		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+
+		$stm = $this->bd->prepare($sql . $search);
+		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error markReadEntries: ' . $info[2]);
 			return false;
@@ -383,7 +386,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	 * @param integer $idMax fail safe article ID
 	 * @return integer affected rows
 	 */
-	public function markReadCat($id, $idMax = 0) {
+	public function markReadCat($id, $idMax = 0, $filter = null, $state = 0) {
 		if ($idMax == 0) {
 			$idMax = time() . '000000';
 			Minz_Log::debug('Calling markReadCat(0) is deprecated!');
@@ -393,8 +396,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			 . 'SET e.is_read=1 '
 			 . 'WHERE f.category=? AND e.is_read=0 AND e.id <= ?';
 		$values = array($id, $idMax);
-		$stm = $this->bd->prepare($sql);
-		if (!($stm && $stm->execute($values))) {
+
+		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+
+		$stm = $this->bd->prepare($sql . $search);
+		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error markReadCat: ' . $info[2]);
 			return false;
@@ -417,19 +423,22 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	 * @param integer $idMax fail safe article ID
 	 * @return integer affected rows
 	 */
-	public function markReadFeed($id_feed, $idMax = 0) {
+	public function markReadFeed($id_feed, $idMax = 0, $filter = null, $state = 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=1 '
-			 . 'WHERE id_feed=? AND is_read=0 AND id <= ?';
+		$sql = 'UPDATE `' . $this->prefix . 'entry` e '
+			 . 'SET e.is_read=1 '
+			 . 'WHERE e.id_feed=? AND e.is_read=0 AND e.id <= ?';
 		$values = array($id_feed, $idMax);
-		$stm = $this->bd->prepare($sql);
-		if (!($stm && $stm->execute($values))) {
+
+		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+
+		$stm = $this->bd->prepare($sql . $search);
+		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error markReadFeed: ' . $info[2]);
 			$this->bd->rollBack();
@@ -493,52 +502,24 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return 'CONCAT(' . $s1 . ',' . $s2 . ')';	//MySQL
 	}
 
-	private function sqlListWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0) {
-		if (!$state) {
-			$state = FreshRSS_Entry::STATE_ALL;
-		}
-		$where = '';
-		$joinFeed = false;
+	private function sqlListEntriesWhere($filter = null, $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $firstId = '', $date_min = 0) {
+		$search = ' ';
 		$values = array();
-		switch ($type) {
-		case 'a':
-			$where .= 'f.priority > 0 ';
-			$joinFeed = true;
-			break;
-		case 's':	//Deprecated: use $state instead
-			$where .= 'e1.is_favorite=1 ';
-			break;
-		case 'c':
-			$where .= 'f.category=? ';
-			$values[] = intval($id);
-			$joinFeed = true;
-			break;
-		case 'f':
-			$where .= 'e1.id_feed=? ';
-			$values[] = intval($id);
-			break;
-		case 'A':
-			$where .= '1 ';
-			break;
-		default:
-			throw new FreshRSS_EntriesGetter_Exception('Bad type in Entry->listByType: [' . $type . ']!');
-		}
-
 		if ($state & FreshRSS_Entry::STATE_NOT_READ) {
 			if (!($state & FreshRSS_Entry::STATE_READ)) {
-				$where .= 'AND e1.is_read=0 ';
+				$search .= 'AND e.is_read=0 ';
 			}
 		}
 		elseif ($state & FreshRSS_Entry::STATE_READ) {
-			$where .= 'AND e1.is_read=1 ';
+			$search .= 'AND e.is_read=1 ';
 		}
 		if ($state & FreshRSS_Entry::STATE_FAVORITE) {
 			if (!($state & FreshRSS_Entry::STATE_NOT_FAVORITE)) {
-				$where .= 'AND e1.is_favorite=1 ';
+				$search .= 'AND e.is_favorite=1 ';
 			}
 		}
 		elseif ($state & FreshRSS_Entry::STATE_NOT_FAVORITE) {
-			$where .= 'AND e1.is_favorite=0 ';
+			$search .= 'AND e.is_favorite=0 ';
 		}
 
 		switch ($order) {
@@ -552,76 +533,111 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			$firstId = $order === 'DESC' ? '9000000000'. '000000' : '0';	//MySQL optimization. TODO: check if this is needed again, after the filtering for old articles has been removed in 0.9-dev
 		}*/
 		if ($firstId !== '') {
-			$where .= 'AND e1.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' ';
+			$search .= 'AND e.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' ';
 		}
 		if ($date_min > 0) {
-			$where .= 'AND e1.id >= ' . $date_min . '000000 ';
+			$search .= 'AND e.id >= ' . $date_min . '000000 ';
 		}
-		$search = '';
 		if ($filter) {
 			if ($filter->getIntitle()) {
-				$search .= 'AND e1.title LIKE ? ';
+				$search .= 'AND e.title LIKE ? ';
 				$values[] = "%{$filter->getIntitle()}%";
 			}
 			if ($filter->getInurl()) {
-				$search .= 'AND CONCAT(e1.link, e1.guid) LIKE ? ';
+				$search .= 'AND CONCAT(e.link, e.guid) LIKE ? ';
 				$values[] = "%{$filter->getInurl()}%";
 			}
 			if ($filter->getAuthor()) {
-				$search .= 'AND e1.author LIKE ? ';
+				$search .= 'AND e.author LIKE ? ';
 				$values[] = "%{$filter->getAuthor()}%";
 			}
 			if ($filter->getMinDate()) {
-				$search .= 'AND e1.id >= ? ';
+				$search .= 'AND e.id >= ? ';
 				$values[] = "{$filter->getMinDate()}000000";
 			}
 			if ($filter->getMaxDate()) {
-				$search .= 'AND e1.id <= ? ';
+				$search .= 'AND e.id <= ? ';
 				$values[] = "{$filter->getMaxDate()}000000";
 			}
 			if ($filter->getMinPubdate()) {
-				$search .= 'AND e1.date >= ? ';
+				$search .= 'AND e.date >= ? ';
 				$values[] = $filter->getMinPubdate();
 			}
 			if ($filter->getMaxPubdate()) {
-				$search .= 'AND e1.date <= ? ';
+				$search .= 'AND e.date <= ? ';
 				$values[] = $filter->getMaxPubdate();
 			}
 			if ($filter->getTags()) {
 				$tags = $filter->getTags();
 				foreach ($tags as $tag) {
-					$search .= 'AND e1.tags LIKE ? ';
+					$search .= 'AND e.tags LIKE ? ';
 					$values[] = "%{$tag}%";
 				}
 			}
 			if ($filter->getSearch()) {
 				$search_values = $filter->getSearch();
 				foreach ($search_values as $search_value) {
-					$search .= 'AND ' . $this->sqlconcat('e1.title', $this->isCompressed() ? 'UNCOMPRESS(content_bin)' : 'content') . ' LIKE ? ';
+					$search .= 'AND ' . $this->sqlconcat('e.title', $this->isCompressed() ? 'UNCOMPRESS(content_bin)' : 'content') . ' LIKE ? ';
 					$values[] = "%{$search_value}%";
 				}
 			}
 		}
-		return array($values,
-			'SELECT e1.id FROM `' . $this->prefix . 'entry` e1 '
-			. ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e1.id_feed=f.id ' : '')
+		return array($values, $search);
+	}
+
+	private function sqlListWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0) {
+		if (!$state) {
+			$state = FreshRSS_Entry::STATE_ALL;
+		}
+		$where = '';
+		$joinFeed = false;
+		$values = array();
+		switch ($type) {
+		case 'a':
+			$where .= 'f.priority > 0 ';
+			$joinFeed = true;
+			break;
+		case 's':	//Deprecated: use $state instead
+			$where .= 'e.is_favorite=1 ';
+			break;
+		case 'c':
+			$where .= 'f.category=? ';
+			$values[] = intval($id);
+			$joinFeed = true;
+			break;
+		case 'f':
+			$where .= 'e.id_feed=? ';
+			$values[] = intval($id);
+			break;
+		case 'A':
+			$where .= '1 ';
+			break;
+		default:
+			throw new FreshRSS_EntriesGetter_Exception('Bad type in Entry->listByType: [' . $type . ']!');
+		}
+
+		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state, $order, $firstId, $date_min);
+
+		return array(array_merge($values, $searchValues),
+			'SELECT e.id FROM `' . $this->prefix . 'entry` e '
+			. ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id ' : '')
 			. 'WHERE ' . $where
 			. $search
-			. 'ORDER BY e1.id ' . $order
+			. 'ORDER BY e.id ' . $order
 			. ($limit > 0 ? ' LIMIT ' . $limit : ''));	//TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
 	}
 
 	public function listWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0) {
 		list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filter, $date_min);
 
-		$sql = 'SELECT e.id, e.guid, e.title, e.author, '
+		$sql = 'SELECT e0.id, e0.guid, e0.title, e0.author, '
 		     . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content')
-		     . ', e.link, e.date, e.is_read, e.is_favorite, e.id_feed, e.tags '
-		     . 'FROM `' . $this->prefix . 'entry` e '
+		     . ', e0.link, e0.date, e0.is_read, e0.is_favorite, e0.id_feed, e0.tags '
+		     . 'FROM `' . $this->prefix . 'entry` e0 '
 		     . 'INNER JOIN ('
 		     . $sql
-		     . ') e2 ON e2.id=e.id '
-		     . 'ORDER BY e.id ' . $order;
+		     . ') e2 ON e2.id=e0.id '
+		     . 'ORDER BY e0.id ' . $order;
 
 		$stm = $this->bd->prepare($sql);
 		$stm->execute($values);
diff --git a/app/layout/nav_menu.phtml b/app/layout/nav_menu.phtml
index 488c6fdef..23255f04f 100644
--- a/app/layout/nav_menu.phtml
+++ b/app/layout/nav_menu.phtml
@@ -75,6 +75,8 @@
 				'get' => $get,
 				'nextGet' => FreshRSS_Context::$next_get,
 				'idMax' => FreshRSS_Context::$id_max,
+				'search' => FreshRSS_Context::$search,
+				'state' => FreshRSS_Context::$state,
 			)
 		);
 	?>
diff --git a/app/views/helpers/pagination.phtml b/app/views/helpers/pagination.phtml
index 23c45114d..20957fc67 100755
--- a/app/views/helpers/pagination.phtml
+++ b/app/views/helpers/pagination.phtml
@@ -10,6 +10,8 @@
 			'get' => FreshRSS_Context::currentGet(),
 			'nextGet' => FreshRSS_Context::$next_get,
 			'idMax' => FreshRSS_Context::$id_max,
+			'search' => FreshRSS_Context::$search,
+			'state' => FreshRSS_Context::$state,
 		)
 	);
 ?>
-- 
cgit v1.2.3


From 6afa36e7e101236b4ac08f8c267f4e2bd78f4ea6 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre.alapetite@alexandra.dk>
Date: Fri, 19 Aug 2016 11:05:24 +0200
Subject: SQLite fix for mark search as read

https://github.com/FreshRSS/FreshRSS/issues/1220
https://github.com/FreshRSS/FreshRSS/pull/1218
https://github.com/FreshRSS/FreshRSS/issues/608
---
 app/Models/EntryDAO.php       |  2 +-
 app/Models/EntryDAOSQLite.php | 20 +++++++++++++-------
 2 files changed, 14 insertions(+), 8 deletions(-)

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

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 3bb8a720c..769d2ae6c 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -502,7 +502,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return 'CONCAT(' . $s1 . ',' . $s2 . ')';	//MySQL
 	}
 
-	private function sqlListEntriesWhere($filter = null, $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $firstId = '', $date_min = 0) {
+	protected function sqlListEntriesWhere($filter = null, $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $firstId = '', $date_min = 0) {
 		$search = ' ';
 		$values = array();
 		if ($state & FreshRSS_Entry::STATE_NOT_READ) {
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index 19b97fd3a..de02616d6 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -119,7 +119,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 	 * @param integer $priorityMin
 	 * @return integer affected rows
 	 */
-	public function markReadEntries($idMax = 0, $onlyFavorites = false, $priorityMin = 0) {
+	public function markReadEntries($idMax = 0, $onlyFavorites = false, $priorityMin = 0, $filter = null, $state = 0) {
 		if ($idMax == 0) {
 			$idMax = time() . '000000';
 			Minz_Log::debug('Calling markReadEntries(0) is deprecated!');
@@ -132,8 +132,11 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 			$sql .= ' AND id_feed IN (SELECT f.id FROM `' . $this->prefix . 'feed` f WHERE f.priority > ' . intval($priorityMin) . ')';
 		}
 		$values = array($idMax);
-		$stm = $this->bd->prepare($sql);
-		if (!($stm && $stm->execute($values))) {
+
+		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+
+		$stm = $this->bd->prepare($sql . $search);
+		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error markReadEntries: ' . $info[2]);
 			return false;
@@ -156,7 +159,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 	 * @param integer $idMax fail safe article ID
 	 * @return integer affected rows
 	 */
-	public function markReadCat($id, $idMax = 0) {
+	public function markReadCat($id, $idMax = 0, $filter = null, $state = 0) {
 		if ($idMax == 0) {
 			$idMax = time() . '000000';
 			Minz_Log::debug('Calling markReadCat(0) is deprecated!');
@@ -166,9 +169,12 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 			 . 'SET is_read=1 '
 			 . 'WHERE is_read=0 AND id <= ? AND '
 			 . 'id_feed IN (SELECT f.id FROM `' . $this->prefix . 'feed` f WHERE f.category=?)';
-		$values = array($idMax, $id);
-		$stm = $this->bd->prepare($sql);
-		if (!($stm && $stm->execute($values))) {
+		$values = array($id, $idMax);
+
+		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+
+		$stm = $this->bd->prepare($sql . $search);
+		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error markReadCat: ' . $info[2]);
 			return false;
-- 
cgit v1.2.3


From edd4516178c49f966248d832e63776f5f2f90236 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre.alapetite@alexandra.dk>
Date: Fri, 19 Aug 2016 14:33:08 +0200
Subject: More SQLite fix for mark search as read

---
 app/Models/EntryDAO.php       | 40 ++++++++++++++++++++--------------------
 app/Models/EntryDAOSQLite.php |  6 +++---
 2 files changed, 23 insertions(+), 23 deletions(-)

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

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 769d2ae6c..8f64098e5 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -360,7 +360,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		}
 		$values = array($idMax);
 
-		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+		list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filter, $state);
 
 		$stm = $this->bd->prepare($sql . $search);
 		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
@@ -397,7 +397,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			 . 'WHERE f.category=? AND e.is_read=0 AND e.id <= ?';
 		$values = array($id, $idMax);
 
-		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+		list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filter, $state);
 
 		$stm = $this->bd->prepare($sql . $search);
 		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
@@ -435,7 +435,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			 . 'WHERE e.id_feed=? AND e.is_read=0 AND e.id <= ?';
 		$values = array($id_feed, $idMax);
 
-		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+		list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filter, $state);
 
 		$stm = $this->bd->prepare($sql . $search);
 		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
@@ -502,24 +502,24 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return 'CONCAT(' . $s1 . ',' . $s2 . ')';	//MySQL
 	}
 
-	protected function sqlListEntriesWhere($filter = null, $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $firstId = '', $date_min = 0) {
+	protected function sqlListEntriesWhere($alias = '', $filter = null, $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $firstId = '', $date_min = 0) {
 		$search = ' ';
 		$values = array();
 		if ($state & FreshRSS_Entry::STATE_NOT_READ) {
 			if (!($state & FreshRSS_Entry::STATE_READ)) {
-				$search .= 'AND e.is_read=0 ';
+				$search .= 'AND ' . $alias . 'is_read=0 ';
 			}
 		}
 		elseif ($state & FreshRSS_Entry::STATE_READ) {
-			$search .= 'AND e.is_read=1 ';
+			$search .= 'AND ' . $alias . 'is_read=1 ';
 		}
 		if ($state & FreshRSS_Entry::STATE_FAVORITE) {
 			if (!($state & FreshRSS_Entry::STATE_NOT_FAVORITE)) {
-				$search .= 'AND e.is_favorite=1 ';
+				$search .= 'AND ' . $alias . 'is_favorite=1 ';
 			}
 		}
 		elseif ($state & FreshRSS_Entry::STATE_NOT_FAVORITE) {
-			$search .= 'AND e.is_favorite=0 ';
+			$search .= 'AND ' . $alias . 'is_favorite=0 ';
 		}
 
 		switch ($order) {
@@ -533,51 +533,51 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			$firstId = $order === 'DESC' ? '9000000000'. '000000' : '0';	//MySQL optimization. TODO: check if this is needed again, after the filtering for old articles has been removed in 0.9-dev
 		}*/
 		if ($firstId !== '') {
-			$search .= 'AND e.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' ';
+			$search .= 'AND ' . $alias . 'id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' ';
 		}
 		if ($date_min > 0) {
-			$search .= 'AND e.id >= ' . $date_min . '000000 ';
+			$search .= 'AND ' . $alias . 'id >= ' . $date_min . '000000 ';
 		}
 		if ($filter) {
 			if ($filter->getIntitle()) {
-				$search .= 'AND e.title LIKE ? ';
+				$search .= 'AND ' . $alias . 'title LIKE ? ';
 				$values[] = "%{$filter->getIntitle()}%";
 			}
 			if ($filter->getInurl()) {
-				$search .= 'AND CONCAT(e.link, e.guid) LIKE ? ';
+				$search .= 'AND CONCAT(' . $alias . 'link, ' . $alias . 'guid) LIKE ? ';
 				$values[] = "%{$filter->getInurl()}%";
 			}
 			if ($filter->getAuthor()) {
-				$search .= 'AND e.author LIKE ? ';
+				$search .= 'AND ' . $alias . 'author LIKE ? ';
 				$values[] = "%{$filter->getAuthor()}%";
 			}
 			if ($filter->getMinDate()) {
-				$search .= 'AND e.id >= ? ';
+				$search .= 'AND ' . $alias . 'id >= ? ';
 				$values[] = "{$filter->getMinDate()}000000";
 			}
 			if ($filter->getMaxDate()) {
-				$search .= 'AND e.id <= ? ';
+				$search .= 'AND ' . $alias . 'id <= ? ';
 				$values[] = "{$filter->getMaxDate()}000000";
 			}
 			if ($filter->getMinPubdate()) {
-				$search .= 'AND e.date >= ? ';
+				$search .= 'AND ' . $alias . 'date >= ? ';
 				$values[] = $filter->getMinPubdate();
 			}
 			if ($filter->getMaxPubdate()) {
-				$search .= 'AND e.date <= ? ';
+				$search .= 'AND ' . $alias . 'date <= ? ';
 				$values[] = $filter->getMaxPubdate();
 			}
 			if ($filter->getTags()) {
 				$tags = $filter->getTags();
 				foreach ($tags as $tag) {
-					$search .= 'AND e.tags LIKE ? ';
+					$search .= 'AND ' . $alias . 'tags LIKE ? ';
 					$values[] = "%{$tag}%";
 				}
 			}
 			if ($filter->getSearch()) {
 				$search_values = $filter->getSearch();
 				foreach ($search_values as $search_value) {
-					$search .= 'AND ' . $this->sqlconcat('e.title', $this->isCompressed() ? 'UNCOMPRESS(content_bin)' : 'content') . ' LIKE ? ';
+					$search .= 'AND ' . $this->sqlconcat($alias . 'title', $this->isCompressed() ? 'UNCOMPRESS(' . $alias . 'content_bin)' : '' . $alias . 'content') . ' LIKE ? ';
 					$values[] = "%{$search_value}%";
 				}
 			}
@@ -616,7 +616,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			throw new FreshRSS_EntriesGetter_Exception('Bad type in Entry->listByType: [' . $type . ']!');
 		}
 
-		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state, $order, $firstId, $date_min);
+		list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filter, $state, $order, $firstId, $date_min);
 
 		return array(array_merge($values, $searchValues),
 			'SELECT e.id FROM `' . $this->prefix . 'entry` e '
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index de02616d6..dad34a93d 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -133,7 +133,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 		}
 		$values = array($idMax);
 
-		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+		list($searchValues, $search) = $this->sqlListEntriesWhere('', $filter, $state);
 
 		$stm = $this->bd->prepare($sql . $search);
 		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
@@ -169,9 +169,9 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 			 . 'SET is_read=1 '
 			 . 'WHERE is_read=0 AND id <= ? AND '
 			 . 'id_feed IN (SELECT f.id FROM `' . $this->prefix . 'feed` f WHERE f.category=?)';
-		$values = array($id, $idMax);
+		$values = array($idMax, $id);
 
-		list($searchValues, $search) = $this->sqlListEntriesWhere($filter, $state);
+		list($searchValues, $search) = $this->sqlListEntriesWhere('', $filter, $state);
 
 		$stm = $this->bd->prepare($sql . $search);
 		if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
-- 
cgit v1.2.3