From 00774f5a0bf2eacbb1825ccbf07e3fbc7b114b4d Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sun, 2 Mar 2014 11:54:52 +0100
Subject: API : SQL optimisation WHERE ... IN, and better compatibility EasyRSS

https://github.com/marienfressinaud/FreshRSS/issues/13
---
 app/Models/EntryDAO.php | 73 ++++++++++++++++++++++++++++++++++++++++---------
 app/Models/FeedDAO.php  |  9 ++++--
 2 files changed, 66 insertions(+), 16 deletions(-)

(limited to 'app/Models')

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index e90b9a7fe..cc52ea120 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -54,19 +54,66 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 		}
 	}
 
-	public function markRead ($id, $is_read = true) {
-		$sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id '
-		     . 'SET e.is_read = ?,'
-		     . 'f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 '
-		     . 'WHERE e.id=?';
-		$values = array ($is_read ? 1 : 0, $id);
-		$stm = $this->bd->prepare ($sql);
-		if ($stm && $stm->execute ($values)) {
-			return $stm->rowCount();
+	public function markRead($ids, $is_read = true) {
+		if (is_array($ids)) {
+			if (count($ids) < 6) {	//Speed heuristics
+				$affected = 0;
+				foreach ($ids as $id) {
+					$affected += $this->markRead($id, $is_read);
+				}
+				return $affected;
+			}
+
+			$this->bd->beginTransaction();
+			$sql = 'UPDATE `' . $this->prefix . 'entry` e '
+				 . 'SET e.is_read = ? '
+				 . 'WHERE e.id IN (' . str_repeat('?,', count($ids) - 1). '?)';
+			$values = array($is_read ? 1 : 0);
+			$values = array_merge($values, $ids);
+			$stm = $this->bd->prepare($sql);
+			if (!($stm && $stm->execute($values))) {
+				$info = $stm->errorInfo();
+				Minz_Log::record('SQL error : ' . $info[2], Minz_Log::ERROR);
+				$this->bd->rollBack();
+				return false;
+			}
+			$affected = $stm->rowCount();
+
+			if ($affected > 0) {
+				$sql = 'UPDATE `' . $this->prefix . 'feed` f '
+				     . 'INNER JOIN ('
+				     .	'SELECT e.id_feed, '
+				     .	'COUNT(CASE WHEN e.is_read = 0 THEN 1 END) AS nbUnreads, '
+				     .	'COUNT(e.id) AS nbEntries '
+				     .	'FROM `' . $this->prefix . 'entry` e '
+				     .	'GROUP BY e.id_feed'
+				     . ') x ON x.id_feed=f.id '
+				     . 'SET f.cache_nbEntries=x.nbEntries, f.cache_nbUnreads=x.nbUnreads';
+				$stm = $this->bd->prepare($sql);
+				if (!($stm && $stm->execute())) {
+					$info = $stm->errorInfo();
+					Minz_Log::record('SQL error : ' . $info[2], Minz_Log::ERROR);
+					$this->bd->rollBack();
+					return false;
+				}
+			}
+
+			$this->bd->commit();
+			return $affected;
 		} else {
-			$info = $stm->errorInfo();
-			Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR);
-			return false;
+			$sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id '
+				 . 'SET e.is_read = ?,'
+				 . 'f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 '
+				 . 'WHERE e.id=?';
+			$values = array($is_read ? 1 : 0, $ids);
+			$stm = $this->bd->prepare($sql);
+			if ($stm && $stm->execute($values)) {
+				return $stm->rowCount();
+			} else {
+				$info = $stm->errorInfo();
+				Minz_Log::record('SQL error : ' . $info[2], Minz_Log::ERROR);
+				return false;
+			}
 		}
 	}
 
@@ -463,7 +510,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 		return self::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC));
 	}
 
-	public function listIdsWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) {
+	public function listIdsWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) {	//For API
 		list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filter, $date_min, $keepHistoryDefault);
 
 		$stm = $this->bd->prepare($sql);
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index f9e1ad9e8..ca25c3aeb 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -198,15 +198,18 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 		return self::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC));
 	}
 
-	public function arrayCategoryNames() {
-		$sql = 'SELECT f.id, c.name as c_name FROM `' . $this->prefix . 'feed` f '
+	public function arrayFeedCategoryNames() {	//For API
+		$sql = 'SELECT f.id, f.name, c.name as c_name FROM `' . $this->prefix . 'feed` f '
 		     . 'INNER JOIN `' . $this->prefix . 'category` c ON c.id = f.category';
 		$stm = $this->bd->prepare ($sql);
 		$stm->execute ();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 		$feedCategoryNames = array();
 		foreach ($res as $line) {
-			$feedCategoryNames[$line['id']] = $line['c_name'];
+			$feedCategoryNames[$line['id']] = array(
+				'name' => $line['name'],
+				'c_name' => $line['c_name'],
+			);
 		}
 		return $feedCategoryNames;
 	}
-- 
cgit v1.2.3