From 6b787c7408ecad4271c19e7c92edbad3f8df4330 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 23 Apr 2016 23:07:12 +0200
Subject: Error message when accessing entry by GET

---
 app/Controllers/entryController.php | 1 +
 1 file changed, 1 insertion(+)

(limited to 'app/Controllers/entryController.php')

diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php
index 1d9989f40..bff1073ef 100755
--- a/app/Controllers/entryController.php
+++ b/app/Controllers/entryController.php
@@ -46,6 +46,7 @@ class FreshRSS_entry_Controller extends Minz_ActionController {
 		if ($id === false) {
 			// id is false? It MUST be a POST request!
 			if (!Minz_Request::isPost()) {
+				Minz_Request::bad(_t('feedback.access.not_found'), array('c' => 'index', 'a' => 'index'));
 				return;
 			}
 
-- 
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/Controllers/entryController.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 f632a346269100d6a93bef318ffa66c97f16f6fa Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Thu, 12 Oct 2017 20:11:06 +0200
Subject: CLI optimize database (#1663)

CLI optimize database https://github.com/FreshRSS/FreshRSS/issues/1583
And VACUUM in SQLite https://github.com/FreshRSS/FreshRSS/issues/918
Add VACUUM for PostgreSQL (Not tested yet)
---
 CHANGELOG.md                            |  4 ++++
 app/Controllers/configureController.php |  6 +++--
 app/Controllers/entryController.php     |  4 ++--
 app/Controllers/userController.php      |  4 +++-
 app/Models/DatabaseDAO.php              | 41 +++++++++++++++++++++++++++++++++
 app/Models/DatabaseDAOPGSQL.php         | 37 +++++++++++++++++++++++++++++
 app/Models/DatabaseDAOSQLite.php        | 13 +++++++++++
 app/Models/EntryDAO.php                 | 22 ------------------
 app/Models/EntryDAOPGSQL.php            | 11 ---------
 app/Models/EntryDAOSQLite.php           |  8 -------
 cli/README.md                           |  3 +++
 cli/db-optimize.php                     | 20 ++++++++++++++++
 cli/user-info.php                       |  5 ++--
 13 files changed, 130 insertions(+), 48 deletions(-)
 create mode 100755 cli/db-optimize.php

(limited to 'app/Controllers/entryController.php')

diff --git a/CHANGELOG.md b/CHANGELOG.md
index 051b04a3e..97fcdcbed 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -8,6 +8,10 @@
 	* Remove "SimplePie" name from HTTP User-Agent string [#1656](https://github.com/FreshRSS/FreshRSS/pull/1656)
 * Bug fixing
 	* Work-around for `CURLOPT_FOLLOWLOCATION` `open_basedir` bug in favicons and PubSubHubbub [#1655](https://github.com/FreshRSS/FreshRSS/issues/1655)
+* CLI
+	* New command `./cli/db-optimize.php` for database optimisation [#1583](https://github.com/FreshRSS/FreshRSS/issues/1583)
+* SQL
+	* Perform `VACUUM` on SQLite and PostgreSQL databases when optimisation is requested [#918](https://github.com/FreshRSS/FreshRSS/issues/918)
 * Misc.
 	* Translation validation tool [#1653](https://github.com/FreshRSS/FreshRSS/pull/1653)
 	* Translation manipulation tool [#1658](https://github.com/FreshRSS/FreshRSS/pull/1658)
diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php
index 155221d19..9d2ee450c 100755
--- a/app/Controllers/configureController.php
+++ b/app/Controllers/configureController.php
@@ -225,10 +225,12 @@ class FreshRSS_configure_Controller extends Minz_ActionController {
 
 		$entryDAO = FreshRSS_Factory::createEntryDao();
 		$this->view->nb_total = $entryDAO->count();
-		$this->view->size_user = $entryDAO->size();
+
+		$databaseDAO = FreshRSS_Factory::createDatabaseDAO();
+		$this->view->size_user = $databaseDAO->size();
 
 		if (FreshRSS_Auth::hasAccess('admin')) {
-			$this->view->size_total = $entryDAO->size(true);
+			$this->view->size_total = $databaseDAO->size(true);
 		}
 	}
 
diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php
index c40588105..bd8b65b2b 100755
--- a/app/Controllers/entryController.php
+++ b/app/Controllers/entryController.php
@@ -147,8 +147,8 @@ class FreshRSS_entry_Controller extends Minz_ActionController {
 
 		@set_time_limit(300);
 
-		$entryDAO = FreshRSS_Factory::createEntryDao();
-		$entryDAO->optimizeTable();
+		$databaseDAO = FreshRSS_Factory::createDatabaseDAO();
+		$databaseDAO->optimize();
 
 		$feedDAO = FreshRSS_Factory::createFeedDao();
 		$feedDAO->updateCachedValues();
diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php
index a58501186..2a1d43d9e 100644
--- a/app/Controllers/userController.php
+++ b/app/Controllers/userController.php
@@ -120,7 +120,9 @@ class FreshRSS_user_Controller extends Minz_ActionController {
 		// Get information about the current user.
 		$entryDAO = FreshRSS_Factory::createEntryDao($this->view->current_user);
 		$this->view->nb_articles = $entryDAO->count();
-		$this->view->size_user = $entryDAO->size();
+
+		$databaseDAO = FreshRSS_Factory::createDatabaseDAO();
+		$this->view->size_user = $databaseDAO->size();
 	}
 
 	public static function createUser($new_user_name, $passwordPlain, $apiPasswordPlain, $userConfig = array(), $insertDefaultFeeds = true) {
diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php
index 6ba5bca3e..f5469f2b7 100644
--- a/app/Models/DatabaseDAO.php
+++ b/app/Models/DatabaseDAO.php
@@ -80,4 +80,45 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo {
 
 		return $list;
 	}
+
+	public function size($all = false) {
+		$db = FreshRSS_Context::$system_conf->db;
+		$sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema=?';	//MySQL
+		$values = array($db['base']);
+		if (!$all) {
+			$sql .= ' AND table_name LIKE ?';
+			$values[] = $this->prefix . '%';
+		}
+		$stm = $this->bd->prepare($sql);
+		$stm->execute($values);
+		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+		return $res[0];
+	}
+
+	public function optimize() {
+		$ok = true;
+
+		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`';	//MySQL
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'feed`';	//MySQL
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'category`';	//MySQL
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		return $ok;
+	}
 }
diff --git a/app/Models/DatabaseDAOPGSQL.php b/app/Models/DatabaseDAOPGSQL.php
index 2a18db970..1b3f7408d 100644
--- a/app/Models/DatabaseDAOPGSQL.php
+++ b/app/Models/DatabaseDAOPGSQL.php
@@ -40,4 +40,41 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAO {
 			'default' => $dao['default'],
 		);
 	}
+
+	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];
+	}
+
+	public function optimize() {
+		$ok = true;
+
+		$sql = 'VACUUM `' . $this->prefix . 'entry`';
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'VACUUM `' . $this->prefix . 'feed`';
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'VACUUM `' . $this->prefix . 'category`';
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		return $ok;
+	}
 }
diff --git a/app/Models/DatabaseDAOSQLite.php b/app/Models/DatabaseDAOSQLite.php
index 2e1df132e..d3aedb3c0 100644
--- a/app/Models/DatabaseDAOSQLite.php
+++ b/app/Models/DatabaseDAOSQLite.php
@@ -45,4 +45,17 @@ class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO {
 			'default' => $dao['dflt_value'],
 		);
 	}
+
+	public function size($all = false) {
+		return @filesize(join_path(DATA_PATH, 'users', $this->current_user, 'db.sqlite'));
+	}
+
+	public function optimize() {
+		$sql = 'VACUUM';
+		$stm = $this->bd->prepare($sql);
+		if ($stm) {
+			return $stm->execute();
+		}
+		return false;
+	}
 }
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index bebafe500..e8b6dcdae 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -885,28 +885,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return array('all' => $all, 'unread' => $unread, 'read' => $all - $unread);
 	}
 
-	public function optimizeTable() {
-		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`';	//MySQL
-		$stm = $this->bd->prepare($sql);
-		if ($stm) {
-			return $stm->execute();
-		}
-	}
-
-	public function size($all = false) {
-		$db = FreshRSS_Context::$system_conf->db;
-		$sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema=?';	//MySQL
-		$values = array($db['base']);
-		if (!$all) {
-			$sql .= ' AND table_name LIKE ?';
-			$values[] = $this->prefix . '%';
-		}
-		$stm = $this->bd->prepare($sql);
-		$stm->execute($values);
-		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
-		return $res[0];
-	}
-
 	public static function daoToEntry($dao) {
 		$entry = new FreshRSS_Entry(
 				$dao['id_feed'],
diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php
index 405774abf..f09fe8e75 100644
--- a/app/Models/EntryDAOPGSQL.php
+++ b/app/Models/EntryDAOPGSQL.php
@@ -46,15 +46,4 @@ END $$;';
 		}
 		return $result;
 	}
-
-	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/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index 8dad54322..0f57dc1ba 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -261,12 +261,4 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 		}
 		return $affected;
 	}
-
-	public function optimizeTable() {
-		//TODO: Search for an equivalent in SQLite
-	}
-
-	public function size($all = false) {
-		return @filesize(join_path(DATA_PATH, 'users', $this->current_user, 'db.sqlite'));
-	}
 }
diff --git a/cli/README.md b/cli/README.md
index ce1be10a7..6f907566c 100644
--- a/cli/README.md
+++ b/cli/README.md
@@ -69,6 +69,9 @@ cd /usr/share/FreshRSS
 # Returns: 1) a * iff the user is admin, 2) the name of the user,
 #  3) the date/time of last user action, 4) the size occupied,
 #  and the number of: 5) categories, 6) feeds, 7) read articles, 8) unread articles, and 9) favourites
+
+./cli/db-optimize.php --user username
+# Optimize database (reduces the size) for a given user (perform `OPTIMIZE TABLE` in MySQL, `VACUUM` in SQLite)
 ```
 
 
diff --git a/cli/db-optimize.php b/cli/db-optimize.php
new file mode 100755
index 000000000..83123a669
--- /dev/null
+++ b/cli/db-optimize.php
@@ -0,0 +1,20 @@
+#!/usr/bin/php
+<?php
+require('_cli.php');
+
+$options = getopt('', array(
+		'user:',
+	));
+
+if (empty($options['user'])) {
+	fail('Usage: ' . basename(__FILE__) . " --user username");
+}
+
+$username = cliInitUser($options['user']);
+
+echo 'FreshRSS optimizing database for user “', $username, "”…\n";
+
+$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username);
+$ok = $databaseDAO->optimize();
+
+done($ok);
diff --git a/cli/user-info.php b/cli/user-info.php
index aa3e239b8..41b95cbf3 100755
--- a/cli/user-info.php
+++ b/cli/user-info.php
@@ -19,6 +19,7 @@ foreach ($users as $username) {
 	$catDAO = new FreshRSS_CategoryDAO();
 	$feedDAO = FreshRSS_Factory::createFeedDao($username);
 	$entryDAO = FreshRSS_Factory::createEntryDao($username);
+	$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username);
 
 	$nbEntries = $entryDAO->countUnreadRead();
 	$nbFavorites = $entryDAO->countUnreadReadFavorites();
@@ -27,7 +28,7 @@ foreach ($users as $username) {
 		echo
 			$username, "\t",
 			date('c', FreshRSS_UserDAO::mtime($username)), "\t",
-			format_bytes($entryDAO->size()), "\t",
+			format_bytes($databaseDAO->size()), "\t",
 			$catDAO->count(), " categories\t",
 			count($feedDAO->listFeedsIds()), " feeds\t",
 			$nbEntries['read'], " reads\t",
@@ -38,7 +39,7 @@ foreach ($users as $username) {
 		echo
 			$username, "\t",
 			FreshRSS_UserDAO::mtime($username), "\t",
-			$entryDAO->size(), "\t",
+			$databaseDAO->size(), "\t",
 			$catDAO->count(), "\t",
 			count($feedDAO->listFeedsIds()), "\t",
 			$nbEntries['read'], "\t",
-- 
cgit v1.2.3