From 00774f5a0bf2eacbb1825ccbf07e3fbc7b114b4d Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite 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 +++-- p/api/greader.php | 94 ++++++++++++++++++++++++++++--------------------- 3 files changed, 120 insertions(+), 56 deletions(-) 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; } diff --git a/p/api/greader.php b/p/api/greader.php index d1846fdaf..f26217279 100644 --- a/p/api/greader.php +++ b/p/api/greader.php @@ -294,7 +294,7 @@ function streamContents($path, $include_target, $start_time, $count, $order, $ex header('Content-Type: application/json; charset=UTF-8'); $feedDAO = new FreshRSS_FeedDAO(); - $arrayFeedCategoryNames = $feedDAO->arrayCategoryNames(); + $arrayFeedCategoryNames = $feedDAO->arrayFeedCategoryNames(); switch ($path) { case 'reading-list': @@ -332,10 +332,17 @@ function streamContents($path, $include_target, $start_time, $count, $order, $ex $items = array(); foreach ($entries as $entry) { $f_id = $entry->feed(); - $c_name = isset($arrayFeedCategoryNames[$f_id]) ? $arrayFeedCategoryNames[$f_id] : '_'; + if (isset($arrayFeedCategoryNames[$f_id])) { + $c_name = $arrayFeedCategoryNames[$f_id]['c_name']; + $f_name = $arrayFeedCategoryNames[$f_id]['name']; + } else { + $c_name = '_'; + $f_name = '_'; + } $item = array( 'id' => /*'tag:google.com,2005:reader/item/' .*/ dec2hex($entry->id()), //64-bit hexa http://code.google.com/p/google-reader-api/wiki/ItemId 'crawlTimeMsec' => substr($entry->id(), 0, -3), + 'timestampUsec' => $entry->id(), //EasyRSS 'published' => $entry->date(true), 'title' => $entry->title(), 'summary' => array('content' => $entry->content()), @@ -348,7 +355,7 @@ function streamContents($path, $include_target, $start_time, $count, $order, $ex ), 'origin' => array( 'streamId' => 'feed/' . $f_id, - //'title' => $line['f_name'], + 'title' => $f_name, //EasyRSS //'htmlUrl' => $line['f_website'], ), ); @@ -420,32 +427,34 @@ function streamContentsItemsIds($streamId, $start_time, $count, $order, $exclude function editTag($e_ids, $a, $r) { logMe("editTag()\n"); + + foreach ($e_ids as $i => $e_id) { + $e_ids[$i] = hex2dec(basename($e_id)); //Strip prefix 'tag:google.com,2005:reader/item/' + } + $entryDAO = new FreshRSS_EntryDAO(); - foreach ($e_ids as $e_id) { //TODO: User WHERE...IN - $e_id = hex2dec(basename($e_id)); //Strip prefix 'tag:google.com,2005:reader/item/' - switch ($a) { - case 'user/-/state/com.google/read': - $entryDAO->markRead($e_id, true); - break; - case 'user/-/state/com.google/starred': - $entryDAO->markFavorite($e_id, true); - break; - /*case 'user/-/state/com.google/tracking-kept-unread': - break; - case 'user/-/state/com.google/like': - break; - case 'user/-/state/com.google/broadcast': - break;*/ - } - switch ($r) { - case 'user/-/state/com.google/read': - $entryDAO->markRead($e_id, false); - break; - case 'user/-/state/com.google/starred': - $entryDAO->markFavorite($e_id, false); - break; - } + switch ($a) { + case 'user/-/state/com.google/read': + $entryDAO->markRead($e_ids, true); + break; + case 'user/-/state/com.google/starred': + $entryDAO->markFavorite($e_ids, true); + break; + /*case 'user/-/state/com.google/tracking-kept-unread': + break; + case 'user/-/state/com.google/like': + break; + case 'user/-/state/com.google/broadcast': + break;*/ + } + switch ($r) { + case 'user/-/state/com.google/read': + $entryDAO->markRead($e_ids, false); + break; + case 'user/-/state/com.google/starred': + $entryDAO->markFavorite($e_ids, false); + break; } echo 'OK'; @@ -511,22 +520,27 @@ elseif ($pathInfos[1] === 'reader' && $pathInfos[2] === 'api' && isset($pathInfo $count = isset($_GET['n']) ? intval($_GET['n']) : 20; //n=[integer] : The maximum number of results to return. $order = isset($_GET['r']) ? $_GET['r'] : 'd'; //r=[d|n|o] : Sort order of item results. d or n gives items in descending date order, o in ascending order. $start_time = isset($_GET['ot']) ? intval($_GET['ot']) : 0; //ot=[unix timestamp] : The time from which you want to retrieve items. Only items that have been crawled by Google Reader after this time will be returned. - if (isset($pathInfos[5]) && $pathInfos[5] === 'contents' && isset($pathInfos[6]) && isset($pathInfos[7])) { - if ($pathInfos[6] === 'feed') { - $include_target = $pathInfos[7]; - StreamContents($pathInfos[6], $include_target, $start_time, $count, $order, $exclude_target); - } elseif ($pathInfos[6] === 'user' && isset($pathInfos[8]) && isset($pathInfos[9])) { - if ($pathInfos[8] === 'state') { - if ($pathInfos[9] === 'com.google' && isset($pathInfos[10])) { - if ($pathInfos[10] === 'reading-list' || $pathInfos[10] === 'starred') { - $include_target = ''; - streamContents($pathInfos[10], $include_target, $start_time, $count, $order, $exclude_target); + if (isset($pathInfos[5]) && $pathInfos[5] === 'contents' && isset($pathInfos[6])) { + if (isset($pathInfos[7])) { + if ($pathInfos[6] === 'feed') { + $include_target = $pathInfos[7]; + StreamContents($pathInfos[6], $include_target, $start_time, $count, $order, $exclude_target); + } elseif ($pathInfos[6] === 'user' && isset($pathInfos[8]) && isset($pathInfos[9])) { + if ($pathInfos[8] === 'state') { + if ($pathInfos[9] === 'com.google' && isset($pathInfos[10])) { + if ($pathInfos[10] === 'reading-list' || $pathInfos[10] === 'starred') { + $include_target = ''; + streamContents($pathInfos[10], $include_target, $start_time, $count, $order, $exclude_target); + } } + } elseif ($pathInfos[8] === 'label') { + $include_target = $pathInfos[9]; + streamContents($pathInfos[8], $include_target, $start_time, $count, $order, $exclude_target); } - } elseif ($pathInfos[8] === 'label') { - $include_target = $pathInfos[9]; - streamContents($pathInfos[8], $include_target, $start_time, $count, $order, $exclude_target); } + } else { //EasyRSS + $include_target = ''; + streamContents('reading-list', $include_target, $start_time, $count, $order, $exclude_target); } } elseif ($pathInfos[5] === 'items') { if ($pathInfos[6] === 'ids' && isset($_GET['s'])) { -- cgit v1.2.3