From d6f414108667f32fe2b480adeb7ec9c218db2f4a Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Thu, 3 Jul 2014 21:26:30 +0200
Subject: Preparation for SQLite

https://github.com/marienfressinaud/FreshRSS/issues/100
---
 app/SQL/install.sql.mysql.php | 60 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 60 insertions(+)
 create mode 100644 app/SQL/install.sql.mysql.php

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
new file mode 100644
index 000000000..d509cb4a8
--- /dev/null
+++ b/app/SQL/install.sql.mysql.php
@@ -0,0 +1,60 @@
+<?php
+define('SQL_CREATE_TABLES', '
+CREATE TABLE IF NOT EXISTS `%1$scategory` (
+	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
+	`name` varchar(255) NOT NULL,
+	PRIMARY KEY (`id`),
+	UNIQUE KEY (`name`)	-- v0.7
+) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+ENGINE = INNODB;
+
+CREATE TABLE IF NOT EXISTS `%1$sfeed` (
+	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
+	`url` varchar(511) CHARACTER SET latin1 NOT NULL,
+	`category` SMALLINT DEFAULT 0,	-- v0.7
+	`name` varchar(255) NOT NULL,
+	`website` varchar(255) CHARACTER SET latin1,
+	`description` text,
+	`lastUpdate` int(11) DEFAULT 0,
+	`priority` tinyint(2) NOT NULL DEFAULT 10,
+	`pathEntries` varchar(511) DEFAULT NULL,
+	`httpAuth` varchar(511) DEFAULT NULL,
+	`error` boolean DEFAULT 0,
+	`keep_history` MEDIUMINT NOT NULL DEFAULT -2,	-- v0.7
+	`cache_nbEntries` int DEFAULT 0,	-- v0.7
+	`cache_nbUnreads` int DEFAULT 0,	-- v0.7
+	PRIMARY KEY (`id`),
+	FOREIGN KEY (`category`) REFERENCES `%1$scategory`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
+	UNIQUE KEY (`url`),	-- v0.7
+	INDEX (`name`),	-- v0.7
+	INDEX (`priority`),	-- v0.7
+	INDEX (`keep_history`)	-- v0.7
+) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+ENGINE = INNODB;
+
+CREATE TABLE IF NOT EXISTS `%1$sentry` (
+	`id` bigint NOT NULL,	-- v0.7
+	`guid` varchar(760) CHARACTER SET latin1 NOT NULL,	-- Maximum for UNIQUE is 767B
+	`title` varchar(255) NOT NULL,
+	`author` varchar(255),
+	`content_bin` blob,	-- v0.7
+	`link` varchar(1023) CHARACTER SET latin1 NOT NULL,
+	`date` int(11),
+	`is_read` boolean NOT NULL DEFAULT 0,
+	`is_favorite` boolean NOT NULL DEFAULT 0,
+	`id_feed` SMALLINT,	-- v0.7
+	`tags` varchar(1023),
+	PRIMARY KEY (`id`),
+	FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+	UNIQUE KEY (`id_feed`,`guid`),	-- v0.7
+	INDEX (`is_favorite`),	-- v0.7
+	INDEX (`is_read`)	-- v0.7
+) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+ENGINE = INNODB;
+
+INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");
+');
+
+define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory');
+
+define('SQL_SHOW_TABLES', 'SHOW tables;');
-- 
cgit v1.2.3


From 1b43167fb8206dfd17923c389de1e3bd6b76045e Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 5 Jul 2014 01:20:45 +0200
Subject: SQL: Add f.ttl column to control feed cache duration

Preparation of https://github.com/marienfressinaud/FreshRSS/issues/250
Will also be used to disable automatic update for selected feeds
---
 app/SQL/install.sql.mysql.php  | 1 +
 app/SQL/install.sql.sqlite.php | 1 +
 2 files changed, 2 insertions(+)

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index d509cb4a8..16cb3a3b8 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -21,6 +21,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` (
 	`httpAuth` varchar(511) DEFAULT NULL,
 	`error` boolean DEFAULT 0,
 	`keep_history` MEDIUMINT NOT NULL DEFAULT -2,	-- v0.7
+	`ttl` INT NOT NULL DEFAULT -2,	-- v0.7.3
 	`cache_nbEntries` int DEFAULT 0,	-- v0.7
 	`cache_nbUnreads` int DEFAULT 0,	-- v0.7
 	PRIMARY KEY (`id`),
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 8cdec981f..b90a5ef5e 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -19,6 +19,7 @@ $SQL_CREATE_TABLES = array(
 	`httpAuth` varchar(511) DEFAULT NULL,
 	`error` boolean DEFAULT 0,
 	`keep_history` MEDIUMINT NOT NULL DEFAULT -2,
+	`ttl` INT NOT NULL DEFAULT -2,
 	`cache_nbEntries` int DEFAULT 0,
 	`cache_nbUnreads` int DEFAULT 0,
 	FOREIGN KEY (`%1$scategory`) REFERENCES `%1$scategory`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
-- 
cgit v1.2.3


From 7080a32650ab8b19e917d8add944a75cc98381bc Mon Sep 17 00:00:00 2001
From: Marien Fressinaud <dev@marienfressinaud.fr>
Date: Mon, 20 Oct 2014 11:54:31 +0200
Subject: Add checking installation feature

---
 app/Controllers/updateController.php | 14 +++++-
 app/Models/DatabaseDAO.php           | 83 ++++++++++++++++++++++++++++++++++++
 app/Models/DatabaseDAOSQLite.php     | 48 +++++++++++++++++++++
 app/Models/Factory.php               |  9 ++++
 app/SQL/install.sql.mysql.php        |  2 -
 app/SQL/install.sql.sqlite.php       |  2 -
 app/layout/aside_configure.phtml     |  7 ++-
 app/layout/header.phtml              |  1 +
 app/views/update/checkInstall.phtml  | 30 +++++++++++++
 lib/lib_rss.php                      | 62 +++++++++++++++++++++++++++
 10 files changed, 252 insertions(+), 6 deletions(-)
 create mode 100644 app/Models/DatabaseDAO.php
 create mode 100644 app/Models/DatabaseDAOSQLite.php
 create mode 100644 app/views/update/checkInstall.phtml

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/Controllers/updateController.php b/app/Controllers/updateController.php
index 9d1e1ddf5..4ebb11f51 100644
--- a/app/Controllers/updateController.php
+++ b/app/Controllers/updateController.php
@@ -12,7 +12,6 @@ class FreshRSS_update_Controller extends Minz_ActionController {
 
 		invalidateHttpCache();
 
-		Minz_View::prependTitle(_t('update_system') . ' · ');
 		$this->view->update_to_apply = false;
 		$this->view->last_update_time = 'unknown';
 		$this->view->check_last_hour = false;
@@ -24,6 +23,8 @@ class FreshRSS_update_Controller extends Minz_ActionController {
 	}
 
 	public function indexAction() {
+		Minz_View::prependTitle(_t('update_system') . ' · ');
+
 		if (file_exists(UPDATE_FILENAME) && !is_writable(FRESHRSS_PATH)) {
 			$this->view->message = array(
 				'status' => 'bad',
@@ -126,4 +127,15 @@ class FreshRSS_update_Controller extends Minz_ActionController {
 			}
 		}
 	}
+
+	/**
+	 * This action displays information about installation.
+	 */
+	public function checkInstallAction() {
+		Minz_View::prependTitle(_t('gen.title.check_install') . ' · ');
+
+		$this->view->status_php = check_install_php();
+		$this->view->status_files = check_install_files();
+		$this->view->status_database = check_install_database();
+	}
 }
diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php
new file mode 100644
index 000000000..0d85718e3
--- /dev/null
+++ b/app/Models/DatabaseDAO.php
@@ -0,0 +1,83 @@
+<?php
+
+/**
+ * This class is used to test database is well-constructed.
+ */
+class FreshRSS_DatabaseDAO extends Minz_ModelPdo {
+	public function tablesAreCorrect() {
+		$sql = 'SHOW TABLES';
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+		
+		$tables = array(
+			$this->prefix . 'category' => false,
+			$this->prefix . 'feed' => false,
+			$this->prefix . 'entry' => false,
+		);
+		foreach ($res as $value) {
+			$tables[array_pop($value)] = true;
+		}
+
+		return count(array_keys($tables, true, true)) == count($tables);
+	}
+
+	public function getSchema($table) {
+		$sql = 'DESC ' . $this->prefix . $table;
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+
+		return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC));
+	}
+
+	public function checkTable($table, $schema) {
+		$columns = $this->getSchema($table);
+
+		$ok = (count($columns) == count($schema));
+		foreach ($columns as $c) {
+			$ok &= in_array($c['name'], $schema);
+		}
+
+		return $ok;
+	}
+
+	public function categoryIsCorrect() {
+		return $this->checkTable('category', array(
+			'id', 'name'
+		));
+	}
+
+	public function feedIsCorrect() {
+		return $this->checkTable('feed', array(
+			'id', 'url', 'category', 'name', 'website', 'description', 'lastUpdate',
+			'priority', 'pathEntries', 'httpAuth', 'error', 'keep_history', 'ttl',
+			'cache_nbEntries', 'cache_nbUnreads'
+		));
+	}
+
+	public function entryIsCorrect() {
+		return $this->checkTable('entry', array(
+			'id', 'guid', 'title', 'author', 'content_bin', 'link', 'date', 'is_read',
+			'is_favorite', 'id_feed', 'tags'
+		));
+	}
+
+	public function daoToSchema($dao) {
+		return array(
+			'name' => $dao['Field'],
+			'type' => strtolower($dao['Type']),
+			'notnull' => (bool)$dao['Null'],
+			'default' => $dao['Default'],
+		);
+	}
+
+	public function listDaoToSchema($listDAO) {
+		$list = array();
+
+		foreach ($listDAO as $dao) {
+			$list[] = $this->daoToSchema($dao);
+		}
+
+		return $list;
+	}
+}
diff --git a/app/Models/DatabaseDAOSQLite.php b/app/Models/DatabaseDAOSQLite.php
new file mode 100644
index 000000000..7f53f967d
--- /dev/null
+++ b/app/Models/DatabaseDAOSQLite.php
@@ -0,0 +1,48 @@
+<?php
+
+/**
+ * This class is used to test database is well-constructed (SQLite).
+ */
+class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO {
+	public function tablesAreCorrect() {
+		$sql = 'SELECT name FROM sqlite_master WHERE type="table"';
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
+		
+		$tables = array(
+			'category' => false,
+			'feed' => false,
+			'entry' => false,
+		);
+		foreach ($res as $value) {
+			$tables[$value['name']] = true;
+		}
+
+		return count(array_keys($tables, true, true)) == count($tables);
+	}
+
+	public function getSchema($table) {
+		$sql = 'PRAGMA table_info(' . $table . ')';
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+
+		return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC));
+	}
+
+	public function entryIsCorrect() {
+		return $this->checkTable('entry', array(
+			'id', 'guid', 'title', 'author', 'content', 'link', 'date', 'is_read',
+			'is_favorite', 'id_feed', 'tags'
+		));
+	}
+
+	public function daoToSchema($dao) {
+		return array(
+			'name' => $dao['name'],
+			'type' => strtolower($dao['type']),
+			'notnull' => $dao['notnull'] === '1' ? true : false,
+			'default' => $dao['dflt_value'],
+		);
+	}
+}
diff --git a/app/Models/Factory.php b/app/Models/Factory.php
index 93f4552f7..91cb84998 100644
--- a/app/Models/Factory.php
+++ b/app/Models/Factory.php
@@ -29,4 +29,13 @@ class FreshRSS_Factory {
 		}
 	}
 
+	public static function createDatabaseDAO($username = null) {
+		$db = Minz_Configuration::dataBase();
+		if ($db['type'] === 'sqlite') {
+			return new FreshRSS_DatabaseDAOSQLite($username);
+		} else {
+			return new FreshRSS_DatabaseDAO($username);
+		}
+	}
+
 }
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 16cb3a3b8..cf0159199 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -57,5 +57,3 @@ INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");
 ');
 
 define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory');
-
-define('SQL_SHOW_TABLES', 'SHOW tables;');
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 7988ada04..30bca2810 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -55,5 +55,3 @@ $SQL_CREATE_TABLES = array(
 );
 
 define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory');
-
-define('SQL_SHOW_TABLES', 'SELECT name FROM sqlite_master WHERE type="table"');
diff --git a/app/layout/aside_configure.phtml b/app/layout/aside_configure.phtml
index 20446c877..32dc19a4e 100644
--- a/app/layout/aside_configure.phtml
+++ b/app/layout/aside_configure.phtml
@@ -31,7 +31,12 @@
 	<li class="item<?php echo Minz_Request::controllerName() === 'auth' ? ' active' : ''; ?>">
 		<a href="<?php echo _url('auth', 'index'); ?>"><?php echo _t('gen.menu.authentication'); ?></a>
 	</li>
-	<li class="item<?php echo Minz_Request::controllerName() === 'update' ? ' active' : ''; ?>">
+	<li class="item<?php echo Minz_Request::controllerName() === 'update' &&
+	                          Minz_Request::actionName() === 'checkInstall' ? ' active' : ''; ?>">
+		<a href="<?php echo _url('update', 'checkInstall'); ?>"><?php echo _t('gen.menu.check_install'); ?></a>
+	</li>
+	<li class="item<?php echo Minz_Request::controllerName() === 'update' &&
+	                          Minz_Request::actionName() === 'index' ? ' active' : ''; ?>">
 		<a href="<?php echo _url('update', 'index'); ?>"><?php echo _t('update'); ?></a>
 	</li>
 	<?php } ?>
diff --git a/app/layout/header.phtml b/app/layout/header.phtml
index e848ac4eb..506cec175 100644
--- a/app/layout/header.phtml
+++ b/app/layout/header.phtml
@@ -68,6 +68,7 @@ if (Minz_Configuration::canLogIn()) {
 				<li class="dropdown-header"><?php echo _t('gen.menu.admin'); ?></li>
 				<li class="item"><a href="<?php echo _url('user', 'manage'); ?>"><?php echo _t('gen.menu.manage_users'); ?></a></li>
 				<li class="item"><a href="<?php echo _url('auth', 'index'); ?>"><?php echo _t('gen.menu.authentication'); ?></a></li>
+				<li class="item"><a href="<?php echo _url('update', 'checkInstall'); ?>"><?php echo _t('gen.menu.check_install'); ?></a></li>
 				<li class="item"><a href="<?php echo _url('update', 'index'); ?>"><?php echo _t('update'); ?></a></li>
 				<?php } ?>
 				<li class="separator"></li>
diff --git a/app/views/update/checkInstall.phtml b/app/views/update/checkInstall.phtml
new file mode 100644
index 000000000..32058714e
--- /dev/null
+++ b/app/views/update/checkInstall.phtml
@@ -0,0 +1,30 @@
+<?php $this->partial('aside_configure'); ?>
+
+<div class="post">
+	<a href="<?php echo _url('index', 'index'); ?>"><?php echo _t('back_to_rss_feeds'); ?></a>
+
+	<h2><?php echo _t('admin.check_install.php'); ?></h2>
+
+	<?php foreach ($this->status_php as $key => $status) { ?>
+	<p class="alert <?php echo $status ? 'alert-success' : 'alert-error'; ?>">
+		<?php echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok')); ?>
+	</p>
+	<?php } ?>
+
+	<h2><?php echo _t('admin.check_install.files'); ?></h2>
+
+	<?php foreach ($this->status_files as $key => $status) { ?>
+	<p class="alert <?php echo $status ? 'alert-success' : 'alert-error'; ?>">
+		<?php echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok')); ?>
+	</p>
+	<?php } ?>
+
+	<h2><?php echo _t('admin.check_install.database'); ?></h2>
+
+	<?php foreach ($this->status_database as $key => $status) { ?>
+	<p class="alert <?php echo $status ? 'alert-success' : 'alert-error'; ?>">
+		<?php echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok')); ?>
+	</p>
+	<?php } ?>
+
+</div>
diff --git a/lib/lib_rss.php b/lib/lib_rss.php
index 2f9a2ea45..dbed207d0 100644
--- a/lib/lib_rss.php
+++ b/lib/lib_rss.php
@@ -245,3 +245,65 @@ function is_referer_from_same_domain() {
 	}
 	return (isset($host['port']) ? $host['port'] : 0) === (isset($referer['port']) ? $referer['port'] : 0);
 }
+
+
+/**
+ *
+ */
+function check_install_php() {
+	return array(
+		'php' => version_compare(PHP_VERSION, '5.2.1') >= 0,
+		'minz' => file_exists(LIB_PATH . '/Minz'),
+		'curl' => extension_loaded('curl'),
+		'pdo_mysql' => extension_loaded('pdo_mysql'),
+		'pdo_sqlite' => extension_loaded('pdo_sqlite'),
+		'pdo' => extension_loaded('pdo_mysql') || extension_loaded('pdo_sqlite'),
+		'pcre' => extension_loaded('pcre'),
+		'ctype' => extension_loaded('ctype'),
+		'dom' => class_exists('DOMDocument'),
+		'json' => extension_loaded('json'),
+		'zip' => extension_loaded('zip'),
+	);
+}
+
+
+/**
+ *
+ */
+function check_install_files() {
+	return array(
+		'data' => DATA_PATH && is_writable(DATA_PATH),
+		'cache' => CACHE_PATH && is_writable(CACHE_PATH),
+		'logs' => LOG_PATH && is_writable(LOG_PATH),
+		'favicons' => is_writable(DATA_PATH . '/favicons'),
+		'persona' => is_writable(DATA_PATH . '/persona'),
+		'tokens' => is_writable(DATA_PATH . '/tokens'),
+	);
+}
+
+
+/**
+ *
+ */
+function check_install_database() {
+	$status = array(
+		'connection' => true,
+		'tables' => false,
+		'categories' => false,
+		'feeds' => false,
+		'entries' => false,
+	);
+
+	try {
+		$dbDAO = FreshRSS_Factory::createDatabaseDAO();
+
+		$status['tables'] = $dbDAO->tablesAreCorrect();
+		$status['categories'] = $dbDAO->categoryIsCorrect();
+		$status['feeds'] = $dbDAO->feedIsCorrect();
+		$status['entries'] = $dbDAO->entryIsCorrect();
+	} catch(Minz_PDOConnectionException $e) {
+		$status['connection'] = false;
+	}
+
+	return $status;
+}
-- 
cgit v1.2.3


From 711530a512b370d79b079205ce1f8376174f7f03 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 4 Apr 2015 22:39:31 +0200
Subject: SQL: detection of updates, and preparation for better burge

https://github.com/FreshRSS/FreshRSS/issues/798
https://github.com/FreshRSS/FreshRSS/issues/493
SQLite not yet tested. Only MySQL tested so far.
---
 app/Controllers/feedController.php         |  98 ++++++++------
 app/Controllers/importExportController.php |   3 +-
 app/Models/Entry.php                       |  16 +++
 app/Models/EntryDAO.php                    | 198 +++++++++++++++++++++--------
 app/Models/Feed.php                        |   1 +
 app/SQL/install.sql.mysql.php              |   7 +-
 app/SQL/install.sql.sqlite.php             |   7 +-
 lib/lib_rss.php                            |   2 +-
 8 files changed, 231 insertions(+), 101 deletions(-)

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php
index 6f544d834..08a0257a2 100755
--- a/app/Controllers/feedController.php
+++ b/app/Controllers/feedController.php
@@ -145,7 +145,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 			// Call the extension hook
 			$name = $feed->name();
 			$feed = Minz_ExtensionManager::callHook('feed_before_insert', $feed);
-			if (is_null($feed)) {
+			if ($feed === null) {
 				Minz_Request::bad(_t('feed_not_added', $name), $url_redirect);
 			}
 
@@ -181,7 +181,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 
 			// Use a shared statement and a transaction to improve a LOT the
 			// performances.
-			$prepared_statement = $entryDAO->addEntryPrepare();
 			$feedDAO->beginTransaction();
 			foreach ($entries as $entry) {
 				// Entries are added without any verification.
@@ -190,13 +189,13 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				$entry->_isRead($is_read);
 
 				$entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry);
-				if (is_null($entry)) {
+				if ($entry === null) {
 					// An extension has returned a null value, there is nothing to insert.
 					continue;
 				}
 
 				$values = $entry->toArray();
-				$entryDAO->addEntry($values, $prepared_statement);
+				$entryDAO->addEntry($values);
 			}
 			$feedDAO->updateLastUpdate($feed->id());
 			$feedDAO->commit();
@@ -307,7 +306,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				$feed->load(false);
 			} catch (FreshRSS_Feed_Exception $e) {
 				Minz_Log::notice($e->getMessage());
-				$feedDAO->updateLastUpdate($feed->id(), 1);
+				$feedDAO->updateLastUpdate($feed->id(), true);
 				$feed->unlock();
 				continue;
 			}
@@ -323,50 +322,69 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 			// We want chronological order and SimplePie uses reverse order.
 			$entries = array_reverse($feed->entries());
 			if (count($entries) > 0) {
-				// For this feed, check last n entry GUIDs already in database.
-				$existing_guids = array_fill_keys($entryDAO->listLastGuidsByFeed(
-					$feed->id(), count($entries) + 10
-				), 1);
-				$use_declared_date = empty($existing_guids);
+				$newGuids = array();
+				foreach ($entries as $entry) {
+					$newGuids[] = $entry->guid();
+				}
+				// 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.
-				$prepared_statement = $entryDAO->addEntryPrepare();
-				$feedDAO->beginTransaction();
 				foreach ($entries as $entry) {
 					$entry_date = $entry->date(true);
-					if (isset($existing_guids[$entry->guid()]) ||
-							($feed_history == 0 && $entry_date < $date_min)) {
-						// This entry already exists in DB or should not be added
-						// considering configuration and date.
-						continue;
-					}
-
-					$id = uTimeString();
-					if ($use_declared_date || $entry_date < $date_min) {
-						// Use declared date at first import.
-						$id = min(time(), $entry_date) . uSecString();
+					if (isset($existingHashForGuids[$entry->guid()])) {
+						$existingHash = $existingHashForGuids[$entry->guid()];
+						if (strcasecmp($existingHash, $entry->hash()) === 0 || $existingHash === '00000000000000000000000000000000') {
+							//This entry already exists and is unchanged. TODO: Remove the test with the zero'ed hash in FreshRSS v1.3
+							$oldGuids[] = $entry->guid();
+						} else {	//This entry already exists but has been updated
+							Minz_Log::debug('Entry with GUID `' . $entry->guid() . '` updated in feed ' . $feed->id() .
+								', old hash ' . $existingHash . ', new hash ' . $entry->hash());
+							$entry->_isRead($is_read);	//Reset is_read
+							if (!$entryDAO->hasTransaction()) {
+								$entryDAO->beginTransaction();
+							}
+							$entryDAO->updateEntry($entry->toArray());
+						}
+					} elseif ($feed_history == 0 && $entry_date < $date_min) {
+						// 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.
+							$id = min(time(), $entry_date) . uSecString();
+						}
+
+						$entry->_id($id);
+						$entry->_isRead($is_read);
+
+						$entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry);
+						if ($entry === null) {
+							// An extension has returned a null value, there is nothing to insert.
+							continue;
+						}
+
+						if (!$entryDAO->hasTransaction()) {
+							$entryDAO->beginTransaction();
+						}
+						$entryDAO->addEntry($entry->toArray());
 					}
-
-					$entry->_id($id);
-					$entry->_isRead($is_read);
-
-					$entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry);
-					if (is_null($entry)) {
-						// An extension has returned a null value, there is nothing to insert.
-						continue;
-					}
-
-					$values = $entry->toArray();
-					$entryDAO->addEntry($values, $prepared_statement);
 				}
+				$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)
 				// Remove old entries once in 30.
-				if (!$feedDAO->hasTransaction()) {
-					$feedDAO->beginTransaction();
+				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,
@@ -377,9 +395,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				}
 			}
 
-			$feedDAO->updateLastUpdate($feed->id(), 0, $feedDAO->hasTransaction());
-			if ($feedDAO->hasTransaction()) {
-				$feedDAO->commit();
+			$feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->hasTransaction());
+			if ($entryDAO->hasTransaction()) {
+				$entryDAO->commit();
 			}
 
 			if ($feed->url() !== $url) {
diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php
index 589777b2a..26b163e43 100644
--- a/app/Controllers/importExportController.php
+++ b/app/Controllers/importExportController.php
@@ -361,7 +361,6 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 		}
 
 		// Then, articles are imported.
-		$prepared_statement = $this->entryDAO->addEntryPrepare();
 		$this->entryDAO->beginTransaction();
 		foreach ($article_object['items'] as $item) {
 			if (!isset($article_to_feed[$item['id']])) {
@@ -396,7 +395,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController {
 			}
 
 			$values = $entry->toArray();
-			$id = $this->entryDAO->addEntry($values, $prepared_statement);
+			$id = $this->entryDAO->addEntry($values);
 
 			if (!$error && ($id === false)) {
 				$error = true;
diff --git a/app/Models/Entry.php b/app/Models/Entry.php
index 346c98a92..6931c9f25 100644
--- a/app/Models/Entry.php
+++ b/app/Models/Entry.php
@@ -14,6 +14,7 @@ class FreshRSS_Entry extends Minz_Model {
 	private $content;
 	private $link;
 	private $date;
+	private $hash = null;
 	private $is_read;
 	private $is_favorite;
 	private $feed;
@@ -88,6 +89,14 @@ class FreshRSS_Entry extends Minz_Model {
 		}
 	}
 
+	public function hash() {
+		if ($this->hash === null) {
+			//Do not include $this->date because it may be automatically generated when lacking
+			$this->hash = md5($this->link . $this->title . $this->author . $this->content . $this->tags(true));
+		}
+		return $this->hash;
+	}
+
 	public function _id($value) {
 		$this->id = $value;
 	}
@@ -95,18 +104,23 @@ class FreshRSS_Entry extends Minz_Model {
 		$this->guid = $value;
 	}
 	public function _title($value) {
+		$this->hash = null;
 		$this->title = $value;
 	}
 	public function _author($value) {
+		$this->hash = null;
 		$this->author = $value;
 	}
 	public function _content($value) {
+		$this->hash = null;
 		$this->content = $value;
 	}
 	public function _link($value) {
+		$this->hash = null;
 		$this->link = $value;
 	}
 	public function _date($value) {
+		$this->hash = null;
 		$value = intval($value);
 		$this->date = $value > 1 ? $value : time();
 	}
@@ -120,6 +134,7 @@ class FreshRSS_Entry extends Minz_Model {
 		$this->feed = $value;
 	}
 	public function _tags($value) {
+		$this->hash = null;
 		if (!is_array($value)) {
 			$value = array($value);
 		}
@@ -182,6 +197,7 @@ class FreshRSS_Entry extends Minz_Model {
 			'content' => $this->content(),
 			'link' => $this->link(),
 			'date' => $this->date(true),
+			'hash' => $this->hash(),
 			'is_read' => $this->isRead(),
 			'is_favorite' => $this->isFavorite(),
 			'id_feed' => $this->feed(),
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 9736d5cd3..5b4b85547 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -6,20 +6,57 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return parent::$sharedDbType !== 'sqlite';
 	}
 
-	public function addEntryPrepare() {
-		$sql = 'INSERT INTO `' . $this->prefix . 'entry`(id, guid, title, author, '
-		     . ($this->isCompressed() ? 'content_bin' : 'content')
-		     . ', link, date, is_read, is_favorite, id_feed, tags) '
-		     . 'VALUES(?, ?, ?, ?, '
-		     . ($this->isCompressed() ? 'COMPRESS(?)' : '?')
-		     . ', ?, ?, ?, ?, ?, ?)';
-		return $this->bd->prepare($sql);
+	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;
+							}
+						}
+						if ($hasTransaction) {
+							$this->bd->rollBack();
+						}
+					} 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();
+					}
+				} catch (Exception $e) {
+					Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage());
+					if ($hasTransaction) {
+						$this->bd->rollBack();
+					}
+				}
+			}
+		}
+		return false;
 	}
 
-	public function addEntry($valuesTmp, $preparedStatement = null) {
-		$stm = $preparedStatement === null ?
-				FreshRSS_EntryDAO::addEntryPrepare() :
-				$preparedStatement;
+	private $addEntryPrepared = null;
+
+	public function addEntry($valuesTmp) {
+		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) '
+			     . 'VALUES(?, ?, ?, ?, '
+			     . ($this->isCompressed() ? 'COMPRESS(?)' : '?')
+			     . ', ?, ?, ?, X?, ?, ?, ?, ?)';
+			$this->addEntryPrepared = $this->bd->prepare($sql);
+		}
 
 		$values = array(
 			$valuesTmp['id'],
@@ -29,55 +66,65 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			$valuesTmp['content'],
 			substr($valuesTmp['link'], 0, 1023),
 			$valuesTmp['date'],
+			time(),
+			$valuesTmp['hash'],
 			$valuesTmp['is_read'] ? 1 : 0,
 			$valuesTmp['is_favorite'] ? 1 : 0,
 			$valuesTmp['id_feed'],
 			substr($valuesTmp['tags'], 0, 1023),
 		);
 
-		if ($stm && $stm->execute($values)) {
+		if ($this->addEntryPrepared && $this->addEntryPrepared->execute($values)) {
 			return $this->bd->lastInsertId();
 		} else {
-			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
-			if ((int)($info[0] / 1000) !== 23) {	//Filter out "SQLSTATE Class code 23: Constraint Violation" because of expected duplicate entries
+			$info = $this->addEntryPrepared == null ? array(2 => 'syntax error') : $this->addEntryPrepared->errorInfo();
+			if ($this->autoAddColumn($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]
 				. ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']);
-			} /*else {
-				Minz_Log::debug('SQL error ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
-				. ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']);
-			}*/
+			}
 			return false;
 		}
 	}
 
-	public function addEntryObject($entry, $conf, $feedHistory) {
-		$existingGuids = array_fill_keys(
-			$this->listLastGuidsByFeed($entry->feed(), 20), 1
-		);
-
-		$nb_month_old = max($conf->old_entries, 1);
-		$date_min = time() - (3600 * 24 * 30 * $nb_month_old);
+	private $updateEntryPrepared = null;
 
-		$eDate = $entry->date(true);
-
-		if ($feedHistory == -2) {
-			$feedHistory = $conf->keep_history_default;
+	public function updateEntry($valuesTmp) {
+		if ($this->updateEntryPrepared === null) {
+			$sql = 'UPDATE `' . $this->prefix . 'entry` '
+			     . 'SET title=?, author=?, '
+			     . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?')
+			     . ', link=?, date=?, lastSeen=?, hash=X?, is_read=?, tags=? '
+			     . 'WHERE id_feed=? AND guid=?';
+			$this->updateEntryPrepared = $this->bd->prepare($sql);
 		}
 
-		if (!isset($existingGuids[$entry->guid()]) &&
-				($feedHistory != 0 || $eDate >= $date_min || $entry->isFavorite())) {
-			$values = $entry->toArray();
-
-			$useDeclaredDate = empty($existingGuids);
-			$values['id'] = ($useDeclaredDate || $eDate < $date_min) ?
-				min(time(), $eDate) . uSecString() :
-				uTimeString();
+		$values = array(
+			substr($valuesTmp['title'], 0, 255),
+			substr($valuesTmp['author'], 0, 255),
+			$valuesTmp['content'],
+			substr($valuesTmp['link'], 0, 1023),
+			$valuesTmp['date'],
+			time(),
+			$valuesTmp['hash'],
+			$valuesTmp['is_read'] ? 1 : 0,
+			substr($valuesTmp['tags'], 0, 1023),
+			$valuesTmp['id_feed'],
+			substr($valuesTmp['guid'], 0, 760),
+		);
 
-			return $this->addEntry($values);
+		if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) {
+			return $this->bd->lastInsertId();
+		} else {
+			$info = $this->updateEntryPrepared == null ? array(2 => 'syntax error') : $this->updateEntryPrepared->errorInfo();
+			if ($this->autoAddColumn($info)) {
+				return $this->updateEntry($valuesTmp);
+			}
+			Minz_Log::error('SQL error updateEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
+				. ' while updating entry with GUID ' . $valuesTmp['guid'] . ' in feed ' . $valuesTmp['id_feed']);
+			return false;
 		}
-
-		// We don't return Entry object to avoid a research in DB
-		return -1;
 	}
 
 	/**
@@ -94,6 +141,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		if (!is_array($ids)) {
 			$ids = array($ids);
 		}
+		if (count($ids) < 1) {
+			return 0;
+		}
 		$sql = 'UPDATE `' . $this->prefix . 'entry` '
 		     . 'SET is_favorite=? '
 		     . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?)';
@@ -296,11 +346,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	 *
 	 * If $idMax equals 0, a deprecated debug message is logged
 	 *
-	 * @param integer $id feed ID
+	 * @param integer $id_feed feed ID
 	 * @param integer $idMax fail safe article ID
 	 * @return integer affected rows
 	 */
-	public function markReadFeed($id, $idMax = 0) {
+	public function markReadFeed($id_feed, $idMax = 0) {
 		if ($idMax == 0) {
 			$idMax = time() . '000000';
 			Minz_Log::debug('Calling markReadFeed(0) is deprecated!');
@@ -310,7 +360,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		$sql = 'UPDATE `' . $this->prefix . 'entry` '
 			 . 'SET is_read=1 '
 			 . 'WHERE id_feed=? AND is_read=0 AND id <= ?';
-		$values = array($id, $idMax);
+		$values = array($id_feed, $idMax);
 		$stm = $this->bd->prepare($sql);
 		if (!($stm && $stm->execute($values))) {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
@@ -324,7 +374,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			$sql = 'UPDATE `' . $this->prefix . 'feed` '
 				 . 'SET cache_nbUnreads=cache_nbUnreads-' . $affected
 				 . ' WHERE id=?';
-			$values = array($id);
+			$values = array($id_feed);
 			$stm = $this->bd->prepare($sql);
 			if (!($stm && $stm->execute($values))) {
 				$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
@@ -338,7 +388,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return $affected;
 	}
 
-	public function searchByGuid($feed_id, $id) {
+	public function searchByGuid($id_feed, $guid) {
 		// un guid est unique pour un flux donné
 		$sql = 'SELECT id, guid, title, author, '
 		     . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content')
@@ -347,8 +397,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		$stm = $this->bd->prepare($sql);
 
 		$values = array(
-			$feed_id,
-			$id
+			$id_feed,
+			$guid,
 		);
 
 		$stm->execute($values);
@@ -519,12 +569,52 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return $stm->fetchAll(PDO::FETCH_COLUMN, 0);
 	}
 
-	public function listLastGuidsByFeed($id, $n) {
-		$sql = 'SELECT guid FROM `' . $this->prefix . 'entry` WHERE id_feed=? ORDER BY id DESC LIMIT ' . intval($n);
+	public function listHashForFeedGuids($id_feed, $guids) {
+		if (count($guids) < 1) {
+			return array();
+		}
+		$sql = 'SELECT guid, hex(hash) AS hexHash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)';
 		$stm = $this->bd->prepare($sql);
-		$values = array($id);
-		$stm->execute($values);
-		return $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+		$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(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 updateLastSeen($id_feed, $guids) {
+		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);
+		$values = array_merge($values, $guids);
+		if ($stm && $stm->execute($values)) {
+			return $stm->rowCount();
+		} else {
+			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
+			if ($this->autoAddColumn($info)) {
+				return $this->updateLastSeen($id_feed, $guids);
+			}
+			Minz_Log::error('SQL error updateLastSeen: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
+				. ' while updating feed ' . $id_feed);
+			return false;
+		}
 	}
 
 	public function countUnreadRead() {
diff --git a/app/Models/Feed.php b/app/Models/Feed.php
index 5ce03be5d..27c83ffd5 100644
--- a/app/Models/Feed.php
+++ b/app/Models/Feed.php
@@ -255,6 +255,7 @@ 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/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index cf0159199..afdd821b2 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` (
 	`name` varchar(255) NOT NULL,
 	`website` varchar(255) CHARACTER SET latin1,
 	`description` text,
-	`lastUpdate` int(11) DEFAULT 0,
+	`lastUpdate` int(11) DEFAULT 0,	-- Until year 2038
 	`priority` tinyint(2) NOT NULL DEFAULT 10,
 	`pathEntries` varchar(511) DEFAULT NULL,
 	`httpAuth` varchar(511) DEFAULT NULL,
@@ -40,7 +40,9 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 	`author` varchar(255),
 	`content_bin` blob,	-- v0.7
 	`link` varchar(1023) CHARACTER SET latin1 NOT NULL,
-	`date` int(11),
+	`date` int(11),	-- Until year 2038
+	`lastSeen` INT(11) NOT NULL,	-- v1.2, Until year 2038
+	`hash` BINARY(16),	-- v1.2
 	`is_read` boolean NOT NULL DEFAULT 0,
 	`is_favorite` boolean NOT NULL DEFAULT 0,
 	`id_feed` SMALLINT,	-- v0.7
@@ -50,6 +52,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 	UNIQUE KEY (`id_feed`,`guid`),	-- v0.7
 	INDEX (`is_favorite`),	-- v0.7
 	INDEX (`is_read`)	-- v0.7
+	INDEX entry_lastSeen_index (`lastSeen`)	-- v1.2
 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 ENGINE = INNODB;
 
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 30bca2810..7517ead45 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -14,7 +14,7 @@ $SQL_CREATE_TABLES = array(
 	`name` varchar(255) NOT NULL,
 	`website` varchar(255),
 	`description` text,
-	`lastUpdate` int(11) DEFAULT 0,
+	`lastUpdate` int(11) DEFAULT 0,	-- Until year 2038
 	`priority` tinyint(2) NOT NULL DEFAULT 10,
 	`pathEntries` varchar(511) DEFAULT NULL,
 	`httpAuth` varchar(511) DEFAULT NULL,
@@ -38,7 +38,9 @@ $SQL_CREATE_TABLES = array(
 	`author` varchar(255),
 	`content` text,
 	`link` varchar(1023) NOT NULL,
-	`date` int(11),
+	`date` int(11),	-- Until year 2038
+	`lastSeen` INT(11) NOT NULL,	-- v1.2, Until year 2038
+	`hash` BINARY(16),	-- v1.2
 	`is_read` boolean NOT NULL DEFAULT 0,
 	`is_favorite` boolean NOT NULL DEFAULT 0,
 	`id_feed` SMALLINT,
@@ -50,6 +52,7 @@ $SQL_CREATE_TABLES = array(
 
 'CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `%1$sentry`(`is_favorite`);',
 'CREATE INDEX IF NOT EXISTS entry_is_read_index ON `%1$sentry`(`is_read`);',
+'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);',	//v1.2
 
 'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");',
 );
diff --git a/lib/lib_rss.php b/lib/lib_rss.php
index e5fe73041..c6bdfde0e 100644
--- a/lib/lib_rss.php
+++ b/lib/lib_rss.php
@@ -38,7 +38,7 @@ function classAutoloader($class) {
 				include(APP_PATH . '/Models/' . $components[1] . '.php');
 				return;
 			case 3:	//Controllers, Exceptions
-				@include(APP_PATH . '/' . $components[2] . 's/' . $components[1] . $components[2] . '.php');
+				include(APP_PATH . '/' . $components[2] . 's/' . $components[1] . $components[2] . '.php');
 				return;
 		}
 	} elseif (strpos($class, 'Minz') === 0) {
-- 
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/SQL/install.sql.mysql.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 96ba71e618468f7d28a04c4ebc7c46dd912ccd75 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sun, 31 May 2015 20:22:27 +0200
Subject: MySQL create table bug

https://github.com/FreshRSS/FreshRSS/issues/845
And updated version comments to 1.1.1
---
 app/Models/EntryDAO.php        | 2 +-
 app/SQL/install.sql.mysql.php  | 8 ++++----
 app/SQL/install.sql.sqlite.php | 6 +++---
 3 files changed, 8 insertions(+), 8 deletions(-)

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index f939a0fb3..bd575989d 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -92,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']. ' ' . $this->addEntryPrepared);
+					. ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']);
 			}
 			return false;
 		}
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 9c6af405d..c5787d25b 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -41,8 +41,8 @@ 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) DEFAULT 0,	-- v1.2, Until year 2038
-	`hash` BINARY(16),	-- v1.2
+	`lastSeen` INT(11) DEFAULT 0,	-- v1.1.1, Until year 2038
+	`hash` BINARY(16),	-- v1.1.1
 	`is_read` boolean NOT NULL DEFAULT 0,
 	`is_favorite` boolean NOT NULL DEFAULT 0,
 	`id_feed` SMALLINT,	-- v0.7
@@ -51,8 +51,8 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 	FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 	UNIQUE KEY (`id_feed`,`guid`),	-- v0.7
 	INDEX (`is_favorite`),	-- v0.7
-	INDEX (`is_read`)	-- v0.7
-	INDEX entry_lastSeen_index (`lastSeen`)	-- v1.2
+	INDEX (`is_read`),	-- v0.7
+	INDEX `entry_lastSeen_index` (`lastSeen`)	-- v1.1.1
 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 ENGINE = INNODB;
 
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 77e8e094c..71bad7311 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -39,8 +39,8 @@ $SQL_CREATE_TABLES = array(
 	`content` text,
 	`link` varchar(1023) NOT NULL,
 	`date` int(11),	-- Until year 2038
-	`lastSeen` INT(11) DEFAULT 0,	-- v1.2, Until year 2038
-	`hash` BINARY(16),	-- v1.2
+	`lastSeen` INT(11) DEFAULT 0,	-- v1.1.1, Until year 2038
+	`hash` BINARY(16),	-- v1.1.1
 	`is_read` boolean NOT NULL DEFAULT 0,
 	`is_favorite` boolean NOT NULL DEFAULT 0,
 	`id_feed` SMALLINT,
@@ -52,7 +52,7 @@ $SQL_CREATE_TABLES = array(
 
 'CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `%1$sentry`(`is_favorite`);',
 'CREATE INDEX IF NOT EXISTS entry_is_read_index ON `%1$sentry`(`is_read`);',
-'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);',	//v1.2
+'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);',	//v1.1.1
 
 'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");',
 );
-- 
cgit v1.2.3


From 9f0b8ce70d84182676eab89e1c681e5d8bbf7539 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Fri, 3 Jul 2015 23:29:46 +0200
Subject: Add default feeds

https://github.com/FreshRSS/FreshRSS/issues/859
---
 app/SQL/install.sql.mysql.php  | 2 ++
 app/SQL/install.sql.sqlite.php | 2 ++
 2 files changed, 4 insertions(+)

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index c5787d25b..0f4e04620 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -57,6 +57,8 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 ENGINE = INNODB;
 
 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/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 71bad7311..87d5cf286 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -55,6 +55,8 @@ $SQL_CREATE_TABLES = array(
 'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);',	//v1.1.1
 
 'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");',
+'INSERT OR 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 OR IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS releases", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);',
 );
 
 define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory');
-- 
cgit v1.2.3


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
---
 README.fr.md                        |  4 +--
 README.md                           |  4 +--
 app/Controllers/feedController.php  | 14 +++++----
 app/Models/EntryDAO.php             | 60 +++++++++++++++++++++++++++++++------
 app/SQL/install.sql.mysql.php       | 31 +++++++++++++++----
 app/install.php                     | 10 +++----
 app/views/update/checkInstall.phtml |  2 +-
 lib/Minz/ModelPdo.php               | 12 +++-----
 8 files changed, 99 insertions(+), 38 deletions(-)

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/README.fr.md b/README.fr.md
index fd01a507b..9da60408f 100644
--- a/README.fr.md
+++ b/README.fr.md
@@ -32,11 +32,11 @@ Nous sommes une communauté amicale.
 * Serveur modeste, par exemple sous Linux ou Windows
 	* Fonctionne même sur un Raspberry Pi 1 avec des temps de réponse < 1s (testé sur 150 flux, 22k articles)
 * Serveur Web Apache2 (recommandé), ou nginx, lighttpd (non testé sur les autres)
-* PHP 5.3+ (PHP 5.3.7+ recommandé, et PHP 5.5+ pour les performances, et PHP 7+ pour d’encore meilleures performances)
+* PHP 5.3.3+ (PHP 5.3.7+ recommandé, et PHP 5.5+ pour les performances, et PHP 7+ pour d’encore meilleures performances)
 	* Requis : [PDO_MySQL](http://php.net/pdo-mysql) ou [PDO_SQLite](http://php.net/pdo-sqlite), [cURL](http://php.net/curl), [GMP](http://php.net/gmp) (pour accès API sur plateformes < 64 bits), [IDN](http://php.net/intl.idn) (pour les noms de domaines internationalisés)
 	* Recommandés : [iconv](http://php.net/iconv), [JSON](http://php.net/json), [mbstring](http://php.net/mbstring), [Zip](http://php.net/zip), [zlib](http://php.net/zlib)
 	* Inclus par défaut : [DOM](http://php.net/dom), [XML](http://php.net/xml)…
-* MySQL 5.0.3+ (recommandé) ou SQLite 3.7.4+
+* MySQL 5.5.3+ (recommandé) ou SQLite 3.7.4+
 * Un navigateur Web récent tel Firefox, Chrome, Opera, Safari. [Internet Explorer ne fonctionne plus, mais ce sera corrigé](https://github.com/FreshRSS/FreshRSS/issues/772).
 	* Fonctionne aussi sur mobile
 * L’entête HTTP `Referer` ne doit pas être désactivé pour pouvoir utiliser le formulaire de connexion
diff --git a/README.md b/README.md
index a0d22a75c..db5e8c02d 100644
--- a/README.md
+++ b/README.md
@@ -32,11 +32,11 @@ We are a friendly community.
 * Light server running Linux or Windows
 	* It even works on Raspberry Pi 1 with response time under a second (tested with 150 feeds, 22k articles)
 * A web server: Apache2 (recommended), nginx, lighttpd (not tested on others)
-* PHP 5.3+ (PHP 5.3.7+ recommended, and PHP 5.5+ for performance, and PHP 7 for even higher performance)
+* PHP 5.3.3+ (PHP 5.3.7+ recommended, and PHP 5.5+ for performance, and PHP 7 for even higher performance)
 	* Required extensions: [PDO_MySQL](http://php.net/pdo-mysql) or [PDO_SQLite](http://php.net/pdo-sqlite), [cURL](http://php.net/curl), [GMP](http://php.net/gmp) (for API access on platforms < 64 bits), [IDN](http://php.net/intl.idn) (for Internationalized Domain Names)
 	* Recommended extensions: [iconv](http://php.net/iconv), [JSON](http://php.net/json), [mbstring](http://php.net/mbstring), [Zip](http://php.net/zip), [zlib](http://php.net/zlib)
 	* Enabled by default: [DOM](http://php.net/dom), [XML](http://php.net/xml)…
-* MySQL 5.0.3+ (recommended) or SQLite 3.7.4+
+* MySQL 5.5.3+ (recommended) or SQLite 3.7.4+
 * A recent browser like Firefox, Chrome, Opera, Safari. [Internet Explorer currently not supported, but support will come back](https://github.com/FreshRSS/FreshRSS/issues/772).
 	* Works on mobile
 * The browser HTTP `Referer` header must not be disabled when using the form login method
diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php
index 6a8aa01cf..ffda1450d 100755
--- a/app/Controllers/feedController.php
+++ b/app/Controllers/feedController.php
@@ -200,7 +200,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				$entryDAO->addEntry($values);
 			}
 			$feedDAO->updateLastUpdate($feed->id());
-			$feedDAO->commit();
+			if ($feedDAO->inTransaction()) {
+				$feedDAO->commit();
+			}
 
 			// Entries are in DB, we redirect to feed configuration page.
 			$url_redirect['params']['id'] = $feed->id();
@@ -364,7 +366,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 								//', old hash ' . $existingHash . ', new hash ' . $entry->hash());
 							//TODO: Make an updated/is_read policy by feed, in addition to the global one.
 							$entry->_isRead(FreshRSS_Context::$user_conf->mark_updated_article_unread ? false : null);	//Change is_read according to policy.
-							if (!$entryDAO->hasTransaction()) {
+							if (!$entryDAO->inTransaction()) {
 								$entryDAO->beginTransaction();
 							}
 							$entryDAO->updateEntry($entry->toArray());
@@ -396,7 +398,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 							$feed->pubSubHubbubError(true);
 						}
 
-						if (!$entryDAO->hasTransaction()) {
+						if (!$entryDAO->inTransaction()) {
 							$entryDAO->beginTransaction();
 						}
 						$entryDAO->addEntry($entry->toArray());
@@ -408,7 +410,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 			if ($feed_history >= 0 && rand(0, 30) === 1) {
 				// TODO: move this function in web cron when available (see entry::purge)
 				// Remove old entries once in 30.
-				if (!$entryDAO->hasTransaction()) {
+				if (!$entryDAO->inTransaction()) {
 					$entryDAO->beginTransaction();
 				}
 
@@ -421,8 +423,8 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				}
 			}
 
-			$feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->hasTransaction());
-			if ($entryDAO->hasTransaction()) {
+			$feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->inTransaction());
+			if ($entryDAO->inTransaction()) {
 				$entryDAO->commit();
 			}
 
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) {
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 0f4e04620..c8755c1ad 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -2,17 +2,17 @@
 define('SQL_CREATE_TABLES', '
 CREATE TABLE IF NOT EXISTS `%1$scategory` (
 	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
-	`name` varchar(255) NOT NULL,
+	`name` varchar(191) NOT NULL,
 	PRIMARY KEY (`id`),
 	UNIQUE KEY (`name`)	-- v0.7
-) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ENGINE = INNODB;
 
 CREATE TABLE IF NOT EXISTS `%1$sfeed` (
 	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
 	`url` varchar(511) CHARACTER SET latin1 NOT NULL,
 	`category` SMALLINT DEFAULT 0,	-- v0.7
-	`name` varchar(255) NOT NULL,
+	`name` varchar(191) NOT NULL,
 	`website` varchar(255) CHARACTER SET latin1,
 	`description` text,
 	`lastUpdate` int(11) DEFAULT 0,	-- Until year 2038
@@ -30,7 +30,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` (
 	INDEX (`name`),	-- v0.7
 	INDEX (`priority`),	-- v0.7
 	INDEX (`keep_history`)	-- v0.7
-) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ENGINE = INNODB;
 
 CREATE TABLE IF NOT EXISTS `%1$sentry` (
@@ -53,7 +53,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 	INDEX (`is_favorite`),	-- v0.7
 	INDEX (`is_read`),	-- v0.7
 	INDEX `entry_lastSeen_index` (`lastSeen`)	-- v1.1.1
-) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ENGINE = INNODB;
 
 INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");
@@ -62,3 +62,24 @@ INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) V
 ');
 
 define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory');
+
+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;
+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;
+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`;
+
+ALTER TABLE `%1$sentry` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `%1$sentry` MODIFY `title` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
+ALTER TABLE `%1$sentry` MODIFY `author` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `%1$sentry` MODIFY `tags` VARCHAR(1023) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+OPTIMIZE TABLE `%1$sentry`;
+');
diff --git a/app/install.php b/app/install.php
index 062f66814..df6e1aeec 100644
--- a/app/install.php
+++ b/app/install.php
@@ -19,7 +19,7 @@ if (isset($_GET['step'])) {
 	define('STEP', 0);
 }
 
-define('SQL_CREATE_DB', 'CREATE DATABASE IF NOT EXISTS %1$s DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;');
+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'];
@@ -253,7 +253,7 @@ function newPdo() {
 	case 'mysql':
 		$str = 'mysql:host=' . $_SESSION['bd_host'] . ';dbname=' . $_SESSION['bd_base'];
 		$driver_options = array(
-			PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
+			PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
 		);
 		break;
 	case 'sqlite':
@@ -309,7 +309,7 @@ function checkStep0() {
 }
 
 function checkStep1() {
-	$php = version_compare(PHP_VERSION, '5.3.0') >= 0;
+	$php = version_compare(PHP_VERSION, '5.3.3') >= 0;
 	$minz = file_exists(join_path(LIB_PATH, 'Minz'));
 	$curl = extension_loaded('curl');
 	$pdo_mysql = extension_loaded('pdo_mysql');
@@ -437,7 +437,7 @@ function checkBD() {
 		switch ($_SESSION['bd_type']) {
 		case 'mysql':
 			$driver_options = array(
-				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
+				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
 			);
 
 			try {	// on ouvre une connexion juste pour créer la base si elle n'existe pas
@@ -536,7 +536,7 @@ function printStep1() {
 	<?php if ($res['php'] == 'ok') { ?>
 	<p class="alert alert-success"><span class="alert-head"><?php echo _t('gen.short.ok'); ?></span> <?php echo _t('install.check.php.ok', PHP_VERSION); ?></p>
 	<?php } else { ?>
-	<p class="alert alert-error"><span class="alert-head"><?php echo _t('gen.short.damn'); ?></span> <?php echo _t('install.check.php.nok', PHP_VERSION, '5.3.0'); ?></p>
+	<p class="alert alert-error"><span class="alert-head"><?php echo _t('gen.short.damn'); ?></span> <?php echo _t('install.check.php.nok', PHP_VERSION, '5.3.3'); ?></p>
 	<?php } ?>
 
 	<?php if ($res['minz'] == 'ok') { ?>
diff --git a/app/views/update/checkInstall.phtml b/app/views/update/checkInstall.phtml
index ed3858b56..543ab43de 100644
--- a/app/views/update/checkInstall.phtml
+++ b/app/views/update/checkInstall.phtml
@@ -9,7 +9,7 @@
 	<p class="alert <?php echo $status ? 'alert-success' : 'alert-error'; ?>">
 		<?php
 			if ($key === 'php') {
-				echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok'), PHP_VERSION, '5.3.0');
+				echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok'), PHP_VERSION, '5.3.3');
 			} else {
 				echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok'));
 			}
diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php
index 25999f02b..845aecaae 100644
--- a/lib/Minz/ModelPdo.php
+++ b/lib/Minz/ModelPdo.php
@@ -16,7 +16,6 @@ class Minz_ModelPdo {
 	public static $useSharedBd = true;
 	private static $sharedBd = null;
 	private static $sharedPrefix;
-	private static $has_transaction = false;
 	private static $sharedCurrentUser;
 	protected static $sharedDbType;
 
@@ -60,8 +59,8 @@ class Minz_ModelPdo {
 			if ($type === 'mysql') {
 				$string = 'mysql:host=' . $db['host']
 				        . ';dbname=' . $db['base']
-				        . ';charset=utf8';
-				$driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8';
+				        . ';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');
@@ -96,18 +95,15 @@ class Minz_ModelPdo {
 
 	public function beginTransaction() {
 		$this->bd->beginTransaction();
-		self::$has_transaction = true;
 	}
-	public function hasTransaction() {
-		return self::$has_transaction;
+	public function inTransaction() {
+		return $this->bd->inTransaction();	//requires PHP >= 5.3.3
 	}
 	public function commit() {
 		$this->bd->commit();
-		self::$has_transaction = false;
 	}
 	public function rollBack() {
 		$this->bd->rollBack();
-		self::$has_transaction = false;
 	}
 
 	public static function clean() {
-- 
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/SQL/install.sql.mysql.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 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/SQL/install.sql.mysql.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 34e38e3a97814eae27174839dd04bcf79c3d62b5 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 15 Oct 2016 20:01:48 +0200
Subject: SQL DROP TABLE

https://github.com/FreshRSS/FreshRSS/issues/1320
---
 app/SQL/install.sql.mysql.php  | 2 +-
 app/SQL/install.sql.pgsql.php  | 2 +-
 app/SQL/install.sql.sqlite.php | 2 +-
 3 files changed, 3 insertions(+), 3 deletions(-)

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 92a00aecc..ca181303e 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -63,7 +63,7 @@ INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) V
 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');
+define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `%1$sentry`, `%1$sfeed`, `%1$scategory`');
 
 define('SQL_UPDATE_UTF8MB4', '
 ALTER DATABASE `%2$s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
index 91c5b257d..0b86908cd 100644
--- a/app/SQL/install.sql.pgsql.php
+++ b/app/SQL/install.sql.pgsql.php
@@ -56,4 +56,4 @@ $SQL_CREATE_TABLES = array(
 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');',
 );
 
-define('SQL_DROP_TABLES', 'DROP TABLES "%1$sentry", "%1$sfeed", "%1$scategory"');
+define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS "%1$sentry", "%1$sfeed", "%1$scategory"');
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 9d146d6f0..1d3a5d92f 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -59,4 +59,4 @@ $SQL_CREATE_TABLES = array(
 'INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS releases", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);',
 );
 
-define('SQL_DROP_TABLES', 'DROP TABLES entry, feed, category');
+define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS entry, feed, category');
-- 
cgit v1.2.3


From 1182129ce5f07892afed190ffbb2ea4c7fc28967 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Mon, 24 Oct 2016 20:29:08 +0200
Subject: CLI option no-default-feeds

https://github.com/FreshRSS/FreshRSS/issues/1095
---
 app/Controllers/userController.php |  4 ++--
 app/Models/UserDAO.php             | 18 +++++++++++++++++-
 app/SQL/install.sql.mysql.php      |  3 +++
 app/SQL/install.sql.pgsql.php      |  4 ++++
 app/SQL/install.sql.sqlite.php     |  4 ++++
 app/install.php                    | 15 +++++++++++++++
 cli/create-user.php                |  7 +++++--
 7 files changed, 50 insertions(+), 5 deletions(-)

(limited to 'app/SQL/install.sql.mysql.php')

diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php
index 9dee16e8c..9d6ae18e6 100644
--- a/app/Controllers/userController.php
+++ b/app/Controllers/userController.php
@@ -99,7 +99,7 @@ class FreshRSS_user_Controller extends Minz_ActionController {
 		$this->view->size_user = $entryDAO->size();
 	}
 
-	public static function createUser($new_user_name, $passwordPlain, $apiPasswordPlain, $userConfig = array()) {
+	public static function createUser($new_user_name, $passwordPlain, $apiPasswordPlain, $userConfig = array(), $insertDefaultFeeds = true) {
 		if (!is_array($userConfig)) {
 			$userConfig = array();
 		}
@@ -138,7 +138,7 @@ class FreshRSS_user_Controller extends Minz_ActionController {
 		}
 		if ($ok) {
 			$userDAO = new FreshRSS_UserDAO();
-			$ok &= $userDAO->createUser($new_user_name, $userConfig['language']);
+			$ok &= $userDAO->createUser($new_user_name, $userConfig['language'], $insertDefaultFeeds);
 		}
 		return $ok;
 	}
diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php
index 597182693..a95ee6bc4 100644
--- a/app/Models/UserDAO.php
+++ b/app/Models/UserDAO.php
@@ -1,7 +1,7 @@
 <?php
 
 class FreshRSS_UserDAO extends Minz_ModelPdo {
-	public function createUser($username, $new_user_language) {
+	public function createUser($username, $new_user_language, $insertDefaultFeeds = true) {
 		$db = FreshRSS_Context::$system_conf->db;
 		require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php');
 
@@ -28,6 +28,22 @@ class FreshRSS_UserDAO extends Minz_ModelPdo {
 					}
 				}
 			}
+			if ($insertDefaultFeeds) {
+				if (defined('SQL_INSERT_FEEDS')) {	//E.g. MySQL
+					$sql = sprintf(SQL_INSERT_FEEDS, $bd_prefix_user);
+					$stm = $userPDO->bd->prepare($sql);
+					$ok &= $stm && $stm->execute();
+				} else {	//E.g. SQLite
+					global $SQL_INSERT_FEEDS;
+					if (is_array($SQL_INSERT_FEEDS)) {
+						foreach ($SQL_INSERT_FEEDS as $instruction) {
+							$sql = sprintf($instruction, $bd_prefix_user);
+							$stm = $userPDO->bd->prepare($sql);
+							$ok &= ($stm && $stm->execute());
+						}
+					}
+				}
+			}
 		} catch (Exception $e) {
 			Minz_Log::error('Error while creating user: ' . $e->getMessage());
 		}
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index ca181303e..a454829d5 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -59,6 +59,9 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 ENGINE = INNODB;
 
 INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");
+');
+
+define('SQL_INSERT_FEEDS', '
 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);
 ');
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
index b343bda86..9f4240b98 100644
--- a/app/SQL/install.sql.pgsql.php
+++ b/app/SQL/install.sql.pgsql.php
@@ -52,6 +52,10 @@ $SQL_CREATE_TABLES = array(
 'CREATE INDEX %1$sentry_lastSeen_index ON "%1$sentry" ("lastSeen");',
 
 'INSERT INTO "%1$scategory" (name) SELECT \'%2$s\' WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1);',
+);
+
+global $SQL_INSERT_FEEDS;
+$SQL_INSERT_FEEDS = array(
 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'http://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'http://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'http://freshrss.org/feeds/all.atom.xml\');',
 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');',
 );
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 1d3a5d92f..68d93ba92 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -55,6 +55,10 @@ $SQL_CREATE_TABLES = array(
 'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `entry`(`lastSeen`);',	//v1.1.1
 
 'INSERT OR IGNORE INTO `category` (id, name) VALUES(1, "%2$s");',
+);
+
+global $SQL_INSERT_FEEDS;
+$SQL_INSERT_FEEDS = array(
 'INSERT OR IGNORE INTO `feed` (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 OR IGNORE INTO `feed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS releases", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);',
 );
diff --git a/app/install.php b/app/install.php
index 6956761c7..0daa02b1b 100644
--- a/app/install.php
+++ b/app/install.php
@@ -357,6 +357,21 @@ function checkDbUser(&$dbOptions) {
 				}
 			}
 		}
+
+		if (defined('SQL_INSERT_FEEDS')) {
+			$sql = sprintf(SQL_INSERT_FEEDS, $dbOptions['bd_prefix_user']);
+			$stm = $c->prepare($sql);
+			$ok &= $stm->execute();
+		} else {
+			global $SQL_INSERT_FEEDS;
+			if (is_array($SQL_INSERT_FEEDS)) {
+				foreach ($SQL_INSERT_FEEDS as $instruction) {
+					$sql = sprintf($instruction, $dbOptions['bd_prefix_user']);
+					$stm = $c->prepare($sql);
+					$ok &= $stm->execute();
+				}
+			}
+		}
 	} catch (PDOException $e) {
 		$ok = false;
 		$dbOptions['bd_error'] = $e->getMessage();
diff --git a/cli/create-user.php b/cli/create-user.php
index 5e93d4605..c790acb59 100755
--- a/cli/create-user.php
+++ b/cli/create-user.php
@@ -9,11 +9,12 @@ $options = getopt('', array(
 		'language:',
 		'email:',
 		'token:',
+		'no-default-feeds',
 	));
 
 if (empty($options['user'])) {
 	fail('Usage: ' . basename(__FILE__) . " --user username ( --password 'password' --api-password 'api_password'" .
-		" --language en --email user@example.net --token 'longRandomString' )");
+		" --language en --email user@example.net --token 'longRandomString --no-default-feeds' )");
 }
 $username = $options['user'];
 if (!ctype_alnum($username)) {
@@ -33,7 +34,9 @@ $ok = FreshRSS_user_Controller::createUser($username,
 	array(
 		'language' => empty($options['language']) ? '' : $options['language'],
 		'token' => empty($options['token']) ? '' : $options['token'],
-	));
+	),
+	!isset($options['no-default-feeds'])
+	);
 
 if (!$ok) {
 	fail('FreshRSS could not create user!');
-- 
cgit v1.2.3