From 6dffb8706f096acdbcda367210d0b7ad41937174 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 27 Feb 2014 22:48:11 +0100 Subject: Alpha version of Google Reader compatible API https://github.com/marienfressinaud/FreshRSS/issues/13 Hardcoded passwords, no possibility to add/delete feeds or edit categories yet. --- app/Models/FeedDAO.php | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 7ebe68d2b..79d8cff90 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -198,6 +198,19 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return self::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC)); } + public function listCategoryNames() { + $sql = 'SELECT f.id, 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']; + } + return $feedCategoryNames; + } + public function listFeedsOrderUpdate ($cacheDuration = 1500) { $sql = 'SELECT id, name, url, lastUpdate, pathEntries, httpAuth, keep_history ' . 'FROM `' . $this->prefix . 'feed` ' -- cgit v1.2.3 From f44683b5671b323ba96f0c4cd47ba9458e934679 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Fri, 28 Feb 2014 20:22:50 +0100 Subject: API streamContents for categories and feeds https://github.com/marienfressinaud/FreshRSS/issues/13 --- app/Controllers/configureController.php | 2 +- app/Models/CategoryDAO.php | 6 +++--- app/Models/EntryDAO.php | 4 ++-- app/Models/FeedDAO.php | 6 +++--- lib/lib_opml.php | 2 +- p/api/greader.php | 22 ++++++++++++++++------ 6 files changed, 26 insertions(+), 16 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php index f831f25d0..41a7920f0 100755 --- a/app/Controllers/configureController.php +++ b/app/Controllers/configureController.php @@ -44,7 +44,7 @@ class FreshRSS_configure_Controller extends Minz_ActionController { 'name' => $cat->name (), ); - if ($catDAO->searchByName ($newCat) == false) { + if ($catDAO->searchByName ($newCat) == null) { $catDAO->addCategory ($values); } } diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 5355228a5..f3c02e3e4 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -64,7 +64,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { if (isset ($cat[0])) { return $cat[0]; } else { - return false; + return null; } } public function searchByName ($name) { @@ -80,7 +80,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { if (isset ($cat[0])) { return $cat[0]; } else { - return false; + return null; } } @@ -120,7 +120,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { public function checkDefault () { $def_cat = $this->searchById (1); - if ($def_cat === false) { + if ($def_cat == null) { $cat = new FreshRSS_Category (Minz_Translate::t ('default_category')); $cat->_id (1); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index b25ae444b..3fc7a05ef 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -307,7 +307,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $stm->execute ($values); $res = $stm->fetchAll (PDO::FETCH_ASSOC); $entries = self::daoToEntry ($res); - return isset ($entries[0]) ? $entries[0] : false; + return isset ($entries[0]) ? $entries[0] : null; } public function searchById ($id) { @@ -320,7 +320,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $stm->execute ($values); $res = $stm->fetchAll (PDO::FETCH_ASSOC); $entries = self::daoToEntry ($res); - return isset ($entries[0]) ? $entries[0] : false; + return isset ($entries[0]) ? $entries[0] : null; } public function listWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) { diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 79d8cff90..fb4a847a0 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -170,7 +170,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if (isset ($feed[$id])) { return $feed[$id]; } else { - return false; + return null; } } public function searchByUrl ($url) { @@ -186,7 +186,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if (isset ($feed)) { return $feed; } else { - return false; + return null; } } @@ -198,7 +198,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return self::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC)); } - public function listCategoryNames() { + public function arrayCategoryNames() { $sql = 'SELECT f.id, 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); diff --git a/lib/lib_opml.php b/lib/lib_opml.php index 9feb12ae0..05e54d85e 100644 --- a/lib/lib_opml.php +++ b/lib/lib_opml.php @@ -58,7 +58,7 @@ function opml_import ($xml) { $title = htmlspecialchars($title, ENT_COMPAT, 'UTF-8'); $catDAO = new FreshRSS_CategoryDAO (); $cat = $catDAO->searchByName ($title); - if ($cat === false) { + if ($cat == null) { $cat = new FreshRSS_Category ($title); $values = array ( 'name' => $cat->name () diff --git a/p/api/greader.php b/p/api/greader.php index 291bcdf1f..e99e1c0c8 100644 --- a/p/api/greader.php +++ b/p/api/greader.php @@ -266,7 +266,7 @@ function streamContents($path, $include_target, $start_time, $count, $order, $ex header('Content-Type: application/json; charset=UTF-8'); $feedDAO = new FreshRSS_FeedDAO(); - $feedCategoryNames = $feedDAO->listCategoryNames(); + $arrayFeedCategoryNames = $feedDAO->arrayCategoryNames(); switch ($path) { case 'reading-list': @@ -275,8 +275,14 @@ function streamContents($path, $include_target, $start_time, $count, $order, $ex case 'starred': $type = 's'; break; + case 'feed': + $type = 'f'; + break; case 'label': $type = 'c'; + $categoryDAO = new FreshRSS_CategoryDAO(); + $cat = $categoryDAO->searchByName($include_target); + $include_target = $cat == null ? -1 : $cat->id(); break; default: $type = 'A'; @@ -298,7 +304,7 @@ function streamContents($path, $include_target, $start_time, $count, $order, $ex $items = array(); foreach ($entries as $entry) { $f_id = $entry->feed(); - $c_name = isset($feedCategoryNames[$f_id]) ? $feedCategoryNames[$f_id] : '_'; + $c_name = isset($arrayFeedCategoryNames[$f_id]) ? $arrayFeedCategoryNames[$f_id] : '_'; $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), @@ -352,9 +358,11 @@ function streamContentsItemsIds($streamId, $start_time, $count, $order, $exclude $type = 'f'; $id = basename($streamId); } elseif (strpos($streamId, 'user/-/label/') === 0) { - $type = 'C'; + $type = 'c'; $c_name = basename($streamId); - notImplemented(); //TODO + $categoryDAO = new FreshRSS_CategoryDAO(); + $cat = $categoryDAO->searchByName($c_name); + $id = $cat == null ? -1 : $cat->id(); } switch ($exclude_target) { @@ -441,7 +449,7 @@ function markAllAsRead($streamId, $olderThanId) { logMe('----------------------------------------------------------------'."\n"); logMe(print_r($debugInfo, true)); -$pathInfo = empty($_SERVER['PATH_INFO']) ? '/Error' : $_SERVER['PATH_INFO']; +$pathInfo = empty($_SERVER['PATH_INFO']) ? '/Error' : urldecode($_SERVER['PATH_INFO']); $pathInfos = explode('/', $pathInfo); logMe('pathInfos => ' . print_r($pathInfos, true)); @@ -471,7 +479,9 @@ if ($user != null) { Minz_Session::_param('currentUser', $user); -if (count($pathInfos)<3) badRequest(); +if (count($pathInfos) < 3) { + badRequest(); +} elseif ($pathInfos[1] === 'accounts') { if (($pathInfos[2] === 'ClientLogin') && isset($_REQUEST['Email']) && isset($_REQUEST['Passwd'])) clientLogin($_REQUEST['Email'], $_REQUEST['Passwd']); -- cgit v1.2.3 From 71f7ce1be5833b54b0f4e1f37e6557425c364725 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 1 Mar 2014 15:47:15 +0100 Subject: API: SQL optimisation https://github.com/marienfressinaud/FreshRSS/issues/13 --- app/Controllers/entryController.php | 4 +++- app/Models/EntryDAO.php | 43 ++++++++++++++++++++++++++++--------- app/Models/FeedDAO.php | 6 +++--- p/api/greader.php | 11 +++------- 4 files changed, 42 insertions(+), 22 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php index 1756c91e5..ca7122a7c 100755 --- a/app/Controllers/entryController.php +++ b/app/Controllers/entryController.php @@ -137,11 +137,13 @@ class FreshRSS_entry_Controller extends Minz_ActionController { if ($nb > 0) { $nbTotal += $nb; Minz_Log::record($nb . ' old entries cleaned in feed [' . $feed->url() . ']', Minz_Log::DEBUG); - $feedDAO->updateLastUpdate($feed->id()); + //$feedDAO->updateLastUpdate($feed->id()); } } } + $feedDAO->updateCachedValues(); + invalidateHttpCache(); $notif = array( diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 3fc7a05ef..e90b9a7fe 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -35,11 +35,15 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { } } - public function markFavorite ($id, $is_favorite = true) { + public function markFavorite($ids, $is_favorite = true) { + if (!is_array($ids)) { + $ids = array($ids); + } $sql = 'UPDATE `' . $this->prefix . 'entry` e ' . 'SET e.is_favorite = ? ' - . 'WHERE e.id=?'; - $values = array ($is_favorite ? 1 : 0, $id); + . 'WHERE e.id IN (' . str_repeat('?,', count($ids) - 1). '?)'; + $values = array ($is_favorite ? 1 : 0); + $values = array_merge($values, $ids); $stm = $this->bd->prepare ($sql); if ($stm && $stm->execute ($values)) { return $stm->rowCount(); @@ -49,6 +53,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { return false; } } + 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 = ?,' @@ -64,6 +69,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { return false; } } + public function markReadEntries ($idMax = 0, $onlyFavorites = false, $priorityMin = 0) { if ($idMax == 0) { $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' @@ -323,7 +329,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { return isset ($entries[0]) ? $entries[0] : null; } - public function listWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) { + private function sqlListWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) { $where = ''; $joinFeed = false; $values = array(); @@ -432,14 +438,22 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { } } + return array($values, + 'SELECT e1.id FROM `' . $this->prefix . 'entry` e1 ' + . ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e1.id_feed = f.id ' : '') + . 'WHERE ' . $where + . $search + . 'ORDER BY e1.id ' . $order + . ($limit > 0 ? ' LIMIT ' . $limit : '')); //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ + } + + public function listWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) { + list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filter, $date_min, $keepHistoryDefault); + $sql = 'SELECT e.id, e.guid, e.title, e.author, UNCOMPRESS(e.content_bin) AS content, e.link, e.date, e.is_read, e.is_favorite, e.id_feed, e.tags ' . 'FROM `' . $this->prefix . 'entry` e ' - . 'INNER JOIN (SELECT e1.id FROM `' . $this->prefix . 'entry` e1 ' - . ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e1.id_feed = f.id ' : '') - . 'WHERE ' . $where - . $search - . 'ORDER BY e1.id ' . $order - . ($limit > 0 ? ' LIMIT ' . $limit : '') //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ + . 'INNER JOIN (' + . $sql . ') e2 ON e2.id = e.id ' . 'ORDER BY e.id ' . $order; @@ -449,6 +463,15 @@ 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) { + list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filter, $date_min, $keepHistoryDefault); + + $stm = $this->bd->prepare($sql); + $stm->execute($values); + + 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); $stm = $this->bd->prepare ($sql); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index fb4a847a0..f9e1ad9e8 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -242,6 +242,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return $res[0]['count']; } + public function countNotRead ($id) { $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND is_read=0'; $stm = $this->bd->prepare ($sql); @@ -251,6 +252,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return $res[0]['count']; } + public function updateCachedValues () { //For one single feed, call updateLastUpdate($id) $sql = 'UPDATE `' . $this->prefix . 'feed` f ' . 'INNER JOIN (' @@ -263,9 +265,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { . 'SET f.cache_nbEntries=x.nbEntries, f.cache_nbUnreads=x.nbUnreads'; $stm = $this->bd->prepare ($sql); - $values = array ($feed_id); - - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute()) { return $stm->rowCount(); } else { $info = $stm->errorInfo(); diff --git a/p/api/greader.php b/p/api/greader.php index 035a031dd..d1846fdaf 100644 --- a/p/api/greader.php +++ b/p/api/greader.php @@ -403,17 +403,12 @@ function streamContentsItemsIds($streamId, $start_time, $count, $order, $exclude } $entryDAO = new FreshRSS_EntryDAO(); - $entries = $entryDAO->listWhere($type, $id, $state, $order === 'o' ? 'ASC' : 'DESC', $count, '', '', $start_time); + $ids = $entryDAO->listIdsWhere($type, $id, $state, $order === 'o' ? 'ASC' : 'DESC', $count, '', '', $start_time); $itemRefs = array(); - foreach ($entries as $entry) { - $f_id = $entry->feed(); + foreach ($ids as $id) { $itemRefs[] = array( - 'id' => $entry->id(), //64-bit decimal - //'timestampUsec' => $entry->dateAdded(true), - /*'directStreamIds' => array( - 'feed/' . $entry->feed() - ),*/ + 'id' => $id, //64-bit decimal ); } -- cgit v1.2.3 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(-) (limited to 'app/Models/FeedDAO.php') 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 From 9ea3819402746d8425d4a608f2d5f3c0f5bc29fb Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Sat, 29 Mar 2014 20:18:57 +0100 Subject: Better OPML import / export - use a new OPML library (https://github.com/marienfressinaud/lib_opml) - import has been completely rewritten (far better!) - introduce addFeedObject and addCategoryObject (in DAO for the moment). Permit to add easily feeds and categories (check if they already exist in DB) - introduce html_chars_utf8 (wrap htmlspecialchars for UTF-8) --- app/Controllers/importExportController.php | 124 ++++++++----- app/Exceptions/OpmlException.php | 6 - app/Models/CategoryDAO.php | 12 ++ app/Models/FeedDAO.php | 29 +++ app/views/helpers/export/opml.phtml | 43 +++-- lib/lib_opml.php | 277 ++++++++++++++++++++--------- lib/lib_rss.php | 4 + 7 files changed, 345 insertions(+), 150 deletions(-) delete mode 100644 app/Exceptions/OpmlException.php (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index cbadeb6ca..b6b4d0fed 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -129,71 +129,101 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { } private function import_opml($opml_file) { - $categories = array(); - $feeds = array(); + $opml_array = array(); try { - list($categories, $feeds) = opml_import($opml_file); - } catch (FreshRSS_Opml_Exception $e) { + $opml_array = libopml_parse_string($opml_file); + } catch (LibOPML_Exception $e) { Minz_Log::warning($e->getMessage()); return true; } $this->catDAO->checkDefault(); - // on ajoute les catégories en masse dans une fonction à part - $this->addCategories($categories); - - // on calcule la date des articles les plus anciens qu'on accepte - $nb_month_old = $this->view->conf->old_entries; - $date_min = time() - (3600 * 24 * 30 * $nb_month_old); + return $this->addOpmlElements($opml_array['body']); + } - // la variable $error permet de savoir si une erreur est survenue - // Le but est de ne pas arrêter l'import même en cas d'erreur - // L'utilisateur sera mis au courant s'il y a eu des erreurs, mais - // ne connaîtra pas les détails. Ceux-ci seront toutefois logguées + private function addOpmlElements($opml_elements, $parent_cat = null) { $error = false; - foreach ($feeds as $feed) { - try { - $values = array( - 'id' => $feed->id(), - 'url' => $feed->url(), - 'category' => $feed->category(), - 'name' => $feed->name(), - 'website' => $feed->website(), - 'description' => $feed->description(), - 'lastUpdate' => 0, - 'httpAuth' => $feed->httpAuth() - ); + foreach ($opml_elements as $elt) { + $res = false; + if (isset($elt['xmlUrl'])) { + $res = $this->addFeedOpml($elt, $parent_cat); + } else { + $res = $this->addCategoryOpml($elt, $parent_cat); + } - // ajout du flux que s'il n'est pas déjà en BDD - if (!$this->feedDAO->searchByUrl($values['url'])) { - $id = $this->feedDAO->addFeed($values); - if ($id) { - $feed->_id($id); - $feed->faviconPrepare(); - } else { - $error = true; - } - } - } catch (FreshRSS_Feed_Exception $e) { - $error = true; - Minz_Log::record($e->getMessage(), Minz_Log::WARNING); + if (!$error && $res) { + // oops: there is at least one error! + $error = $res; } } return $error; } - private function addCategories($categories) { - foreach ($categories as $cat) { - if (!$this->catDAO->searchByName($cat->name())) { - $values = array( - 'id' => $cat->id(), - 'name' => $cat->name(), - ); - $this->catDAO->addCategory($values); + private function addFeedOpml($feed_elt, $parent_cat) { + if (is_null($parent_cat)) { + // This feed has no parent category so we get the default one + $parent_cat = $catDAO->getDefault()->name(); + } + + $cat = $this->catDAO->searchByName($parent_cat); + + if (!$cat) { + return true; + } + + // We get different useful information + $url = html_chars_utf8($feed_elt['xmlUrl']); + $name = html_chars_utf8($feed_elt['text']); + $website = ''; + if (isset($feed_elt['htmlUrl'])) { + $website = html_chars_utf8($feed_elt['htmlUrl']); + } + $description = ''; + if (isset($feed_elt['description'])) { + $description = html_chars_utf8($feed_elt['description']); + } + + $error = false; + try { + // Create a Feed object and add it in DB + $feed = new FreshRSS_Feed($url); + $feed->_category($cat->id()); + $feed->_name($name); + $feed->_website($website); + $feed->_description($description); + + // addFeedObject checks if feed is already in DB so nothing else to + // check here + $id = $this->feedDAO->addFeedObject($feed); + $error = ($id === false); + } catch (FreshRSS_Feed_Exception $e) { + Minz_Log::record($e->getMessage(), Minz_Log::WARNING); + $error = true; + } + + return $error; + } + + private function addCategoryOpml($cat_elt, $parent_cat) { + // Create a new Category object + $cat = new FreshRSS_Category(html_chars_utf8($cat_elt['text'])); + + $id = $this->catDAO->addCategoryObject($cat); + $error = ($id === false); + + if (isset($cat_elt['@outlines'])) { + // Our cat_elt contains more categories or more feeds, so we + // add them recursively. + // Note: FreshRSS does not support yet category arborescence + $res = $this->addOpmlElements($cat_elt['@outlines'], $cat->name()); + if (!$error && $res) { + $error = true; } } + + return $error; } private function import_articles($article_file, $starred = false) { diff --git a/app/Exceptions/OpmlException.php b/app/Exceptions/OpmlException.php deleted file mode 100644 index e0ea3e493..000000000 --- a/app/Exceptions/OpmlException.php +++ /dev/null @@ -1,6 +0,0 @@ -searchByName($category->name())) { + // Category does not exist yet in DB so we add it before continue + $values = array( + 'name' => $category->name(), + ); + return $this->addCategory($values); + } + + return false; + } + public function updateCategory ($id, $valuesTmp) { $sql = 'UPDATE `' . $this->prefix . 'category` SET name=? WHERE id=?'; $stm = $this->bd->prepare ($sql); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index ca25c3aeb..eac21df7e 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -24,6 +24,35 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } } + public function addFeedObject($feed) { + // TODO: not sure if we should write this method in DAO since DAO + // should not be aware about feed class + + // Add feed only if we don't find it in DB + if (!$this->searchByUrl($feed->url())) { + $values = array( + 'id' => $feed->id(), + 'url' => $feed->url(), + 'category' => $feed->category(), + 'name' => $feed->name(), + 'website' => $feed->website(), + 'description' => $feed->description(), + 'lastUpdate' => 0, + 'httpAuth' => $feed->httpAuth() + ); + + $id = $this->addFeed($values); + if ($id) { + $feed->_id($id); + $feed->faviconPrepare(); + } + + return $id; + } + + return false; + } + public function updateFeed ($id, $valuesTmp) { $set = ''; foreach ($valuesTmp as $key => $v) { diff --git a/app/views/helpers/export/opml.phtml b/app/views/helpers/export/opml.phtml index 2e66e5054..adbac904d 100644 --- a/app/views/helpers/export/opml.phtml +++ b/app/views/helpers/export/opml.phtml @@ -1,15 +1,30 @@ '; -?> - - - - <?php echo Minz_Configuration::title (); ?> OPML Feed - - - -categories); ?> - - + +$opml_array = array( + 'head' => array( + 'title' => Minz_Configuration::title(), + 'dateCreated' => date('D, d M Y H:i:s') + ), + 'body' => array() +); + +foreach ($this->categories as $key => $cat) { + $opml_array['body'][$key] = array( + 'text' => $cat['name'], + '@outlines' => array() + ); + + foreach ($cat['feeds'] as $feed) { + $opml_array['body'][$key]['@outlines'][] = array( + 'text' => $feed->name(), + 'type' => 'rss', + 'xmlUrl' => $feed->url(), + 'htmlUrl' => $feed->website(), + 'description' => htmlspecialchars( + $feed->description(), ENT_COMPAT, 'UTF-8' + ) + ); + } +} + +echo libopml_render($opml_array); diff --git a/lib/lib_opml.php b/lib/lib_opml.php index 05e54d85e..16a9921ea 100644 --- a/lib/lib_opml.php +++ b/lib/lib_opml.php @@ -1,23 +1,86 @@ ' . "\n"; - - foreach ($cat['feeds'] as $feed) { - $txt .= "\t" . '' . "\n"; +/* * + * lib_opml is a free library to manage OPML format in PHP. + * It takes in consideration only version 2.0 (http://dev.opml.org/spec2.html). + * Basically it means "text" attribute for outline elements is required. + * + * lib_opml requires SimpleXML (http://php.net/manual/en/book.simplexml.php) + * + * Usages: + * > include('lib_opml.php'); + * > $filename = 'my_opml_file.xml'; + * > $opml_array = libopml_parse_file($filename); + * > print_r($opml_array); + * + * > $opml_string = [...]; + * > $opml_array = libopml_parse_string($opml_string); + * > print_r($opml_array); + * + * > $opml_array = [...]; + * > $opml_string = libopml_render($opml_array); + * > $opml_object = libopml_render($opml_array, true); + * > echo $opml_string; + * > print_r($opml_object); + * + * If parsing fails for any reason (e.g. not an XML string, does not match with + * the specifications), a LibOPML_Exception is raised. + * + * Author: Marien Fressinaud + * Url: https://github.com/marienfressinaud/lib_opml + * Version: 0.1 + * Date: 2014-03-29 + * License: public domain + * + * */ + +class LibOPML_Exception extends Exception {} + + +// These elements are optional +define('HEAD_ELEMENTS', serialize(array( + 'title', 'dateCreated', 'dateModified', 'ownerName', 'ownerEmail', + 'ownerId', 'docs', 'expansionState', 'vertScrollState', 'windowTop', + 'windowLeft', 'windowBottom', 'windowRight' +))); + + +function libopml_parse_outline($outline_xml) { + $outline = array(); + + // An outline may contain any kind of attributes but "text" attribute is + // required ! + $text_is_present = false; + foreach ($outline_xml->attributes() as $key => $value) { + $outline[$key] = (string)$value; + + if ($key === 'text') { + $text_is_present = true; } + } - $txt .= '' . "\n"; + if (!$text_is_present) { + throw new LibOPML_Exception( + 'Outline does not contain any text attribute' + ); } - return $txt; + foreach ($outline_xml->children() as $key => $value) { + // An outline may contain any number of outline children + if ($key === 'outline') { + $outline['@outlines'][] = libopml_parse_outline($value); + } else { + throw new LibOPML_Exception( + 'Body can contain only outline elements' + ); + } + } + + return $outline; } -function opml_import ($xml) { - $xml = html_only_entity_decode($xml); //!\ Assume UTF-8 +function libopml_parse_string($xml) { $dom = new DOMDocument(); $dom->recover = true; $dom->strictErrorChecking = false; @@ -27,94 +90,142 @@ function opml_import ($xml) { $opml = simplexml_import_dom($dom); if (!$opml) { - throw new FreshRSS_Opml_Exception (); + throw new LibOPML_Exception(); } - $catDAO = new FreshRSS_CategoryDAO(); - $catDAO->checkDefault(); - $defCat = $catDAO->getDefault(); + $array = array( + 'version' => (string)$opml['version'], + 'head' => array(), + 'body' => array() + ); + + // First, we get all "head" elements. Head is required but its sub-elements + // are optional. + foreach ($opml->head->children() as $key => $value) { + if (in_array($key, unserialize(HEAD_ELEMENTS), true)) { + $array['head'][$key] = (string)$value; + } else { + throw new LibOPML_Exception( + $key . 'is not part of OPML format' + ); + } + } - $categories = array (); - $feeds = array (); + // Then, we get body oulines. Body must contain at least one outline + // element. + $at_least_one_outline = false; + foreach ($opml->body->children() as $key => $value) { + if ($key === 'outline') { + $at_least_one_outline = true; + $array['body'][] = libopml_parse_outline($value); + } else { + throw new LibOPML_Exception( + 'Body can contain only outline elements' + ); + } + } + + if (!$at_least_one_outline) { + throw new LibOPML_Exception( + 'Body must contain at least one outline element' + ); + } - foreach ($opml->body->outline as $outline) { - if (!isset ($outline['xmlUrl'])) { - // Catégorie - $title = ''; + return $array; +} - if (isset ($outline['text'])) { - $title = (string) $outline['text']; - } elseif (isset ($outline['title'])) { - $title = (string) $outline['title']; - } - if ($title) { - // Permet d'éviter les soucis au niveau des id : - // ceux-ci sont générés en fonction de la date, - // un flux pourrait être dans une catégorie X avec l'id Y - // alors qu'il existe déjà la catégorie X mais avec l'id Z - // Y ne sera pas ajouté et le flux non plus vu que l'id - // de sa catégorie n'exisera pas - $title = htmlspecialchars($title, ENT_COMPAT, 'UTF-8'); - $catDAO = new FreshRSS_CategoryDAO (); - $cat = $catDAO->searchByName ($title); - if ($cat == null) { - $cat = new FreshRSS_Category ($title); - $values = array ( - 'name' => $cat->name () - ); - $cat->_id ($catDAO->addCategory ($values)); - } - - $feeds = array_merge ($feeds, getFeedsOutline ($outline, $cat->id ())); +function libopml_parse_file($filename) { + $file_content = file_get_contents($filename); + + if ($file_content === false) { + throw new LibOPML_Exception( + $filename . ' cannot be found' + ); + } + + return libopml_parse_string($file_content); +} + + +function libopml_render_outline($parent_elt, $outline) { + // Outline MUST be an array! + if (!is_array($outline)) { + throw new LibOPML_Exception( + 'Outline element must be defined as array' + ); + } + + $outline_elt = $parent_elt->addChild('outline'); + $text_is_present = false; + foreach ($outline as $key => $value) { + // Only outlines can be an array and so we consider children are also + // outline elements. + if ($key === '@outlines' && is_array($value)) { + foreach ($value as $outline_child) { + libopml_render_outline($outline_elt, $outline_child); } + } elseif (is_array($value)) { + throw new LibOPML_Exception( + 'Type of outline elements cannot be array: ' . $key + ); } else { - // Flux rss sans catégorie, on récupère l'ajoute dans la catégorie par défaut - $feeds[] = getFeed ($outline, $defCat->id()); + // Detect text attribute is present, that's good :) + if ($key === 'text') { + $text_is_present = true; + } + + $outline_elt->addAttribute($key, $value); } } - return array ($categories, $feeds); + if (!$text_is_present) { + throw new LibOPML_Exception( + 'You must define at least a text element for all outlines' + ); + } } -/** - * import all feeds of a given outline tag - */ -function getFeedsOutline ($outline, $cat_id) { - $feeds = array (); - foreach ($outline->children () as $child) { - if (isset ($child['xmlUrl'])) { - $feeds[] = getFeed ($child, $cat_id); - } else { - $feeds = array_merge( - $feeds, - getFeedsOutline ($child, $cat_id) - ); +function libopml_render($array, $as_xml_object = false) { + $opml = new SimpleXMLElement(''); + + // Create head element. $array['head'] is optional but head element will + // exist in the final XML object. + $head = $opml->addChild('head'); + if (isset($array['head'])) { + foreach ($array['head'] as $key => $value) { + if (in_array($key, unserialize(HEAD_ELEMENTS), true)) { + $head->addChild($key, $value); + } } } - return $feeds; -} + // Check body is set and contains at least one element + if (!isset($array['body'])) { + throw new LibOPML_Exception( + '$array must contain a body element' + ); + } + if (count($array['body']) <= 0) { + throw new LibOPML_Exception( + 'Body element must contain at least one element (array)' + ); + } -function getFeed ($outline, $cat_id) { - $url = (string) $outline['xmlUrl']; - $url = htmlspecialchars($url, ENT_COMPAT, 'UTF-8'); - $title = ''; - if (isset ($outline['text'])) { - $title = (string) $outline['text']; - } elseif (isset ($outline['title'])) { - $title = (string) $outline['title']; - } - $title = htmlspecialchars($title, ENT_COMPAT, 'UTF-8'); - $feed = new FreshRSS_Feed ($url); - $feed->_category ($cat_id); - $feed->_name ($title); - if (isset($outline['htmlUrl'])) { - $feed->_website(htmlspecialchars((string)$outline['htmlUrl'], ENT_COMPAT, 'UTF-8')); - } - if (isset($outline['description'])) { - $feed->_description(sanitizeHTML((string)$outline['description'])); - } - return $feed; + // Create outline elements + $body = $opml->addChild('body'); + foreach ($array['body'] as $outline) { + libopml_render_outline($body, $outline); + } + + // And return the final result + if ($as_xml_object) { + return $opml; + } else { + $dom = dom_import_simplexml($opml)->ownerDocument; + $dom->formatOutput = true; + $dom->encoding = 'UTF-8'; + return $dom->saveXML(); + } } diff --git a/lib/lib_rss.php b/lib/lib_rss.php index 2077fe63f..0f8161129 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -244,3 +244,7 @@ function cryptAvailable() { } return false; } + +function html_chars_utf8($str) { + return htmlspecialchars($str, ENT_COMPAT, 'UTF-8'); +} -- cgit v1.2.3 From 779afe9c4ee00f8099a423e1fceee40197a90cfa Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Sun, 30 Mar 2014 14:28:13 +0200 Subject: Import of articles is implemented! - Remove massiveImportAction and addCategories from FeedController - Fix typo for some methods (camelCase) - addCategoryObject and addFeedObject return id if corresponding object already exists in DB - introduce addEntryObject. Return -1 if Entry already exist (in order to keep quite good performances) - Complete importArticles method Need some more tests + better performance --- app/Controllers/feedController.php | 88 ---------------------------- app/Controllers/importExportController.php | 92 +++++++++++++++++++++--------- app/Models/CategoryDAO.php | 5 +- app/Models/EntryDAO.php | 30 ++++++++++ app/Models/FeedDAO.php | 5 +- 5 files changed, 100 insertions(+), 120 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 43435d99d..95c9c1e9c 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -327,82 +327,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } } - public function massiveImportAction () { - # TODO: this function has moved to importExportController.php - # I keep it for the moment but should be deleted in a near future. - @set_time_limit(300); - - $this->catDAO = new FreshRSS_CategoryDAO (); - $this->catDAO->checkDefault (); - - $entryDAO = new FreshRSS_EntryDAO (); - $feedDAO = new FreshRSS_FeedDAO (); - - $categories = Minz_Request::param ('categories', array (), true); - $feeds = Minz_Request::param ('feeds', array (), true); - - // on ajoute les catégories en masse dans une fonction à part - $this->addCategories ($categories); - - // on calcule la date des articles les plus anciens qu'on accepte - $nb_month_old = $this->view->conf->old_entries; - $date_min = time () - (3600 * 24 * 30 * $nb_month_old); - - // la variable $error permet de savoir si une erreur est survenue - // Le but est de ne pas arrêter l'import même en cas d'erreur - // L'utilisateur sera mis au courant s'il y a eu des erreurs, mais - // ne connaîtra pas les détails. Ceux-ci seront toutefois logguées - $error = false; - $i = 0; - foreach ($feeds as $feed) { - try { - $values = array ( - 'id' => $feed->id (), - 'url' => $feed->url (), - 'category' => $feed->category (), - 'name' => $feed->name (), - 'website' => $feed->website (), - 'description' => $feed->description (), - 'lastUpdate' => 0, - 'httpAuth' => $feed->httpAuth () - ); - - // ajout du flux que s'il n'est pas déjà en BDD - if (!$feedDAO->searchByUrl ($values['url'])) { - $id = $feedDAO->addFeed ($values); - if ($id) { - $feed->_id ($id); - $feed->faviconPrepare(); - } else { - $error = true; - } - } - } catch (FreshRSS_Feed_Exception $e) { - $error = true; - Minz_Log::record ($e->getMessage (), Minz_Log::WARNING); - } - } - - if ($error) { - $res = Minz_Translate::t ('feeds_imported_with_errors'); - } else { - $res = Minz_Translate::t ('feeds_imported'); - } - - $notif = array ( - 'type' => 'good', - 'content' => $res - ); - Minz_Session::_param ('notification', $notif); - Minz_Session::_param ('actualize_feeds', true); - - // et on redirige vers la page d'accueil - Minz_Request::forward (array ( - 'c' => 'index', - 'a' => 'index' - ), true); - } - public function deleteAction () { if (Minz_Request::isPost ()) { $type = Minz_Request::param ('type', 'feed'); @@ -446,16 +370,4 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } } } - - private function addCategories ($categories) { - foreach ($categories as $cat) { - if (!$this->catDAO->searchByName ($cat->name ())) { - $values = array ( - 'id' => $cat->id (), - 'name' => $cat->name (), - ); - $catDAO->addCategory ($values); - } - } - } } diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index b6b4d0fed..3b6bf2c5c 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -31,7 +31,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { @set_time_limit(300); $file = $_FILES['file']; - $type_file = $this->guess_file_type($file['name']); + $type_file = $this->guessFileType($file['name']); $list_files = array( 'opml' => array(), @@ -46,7 +46,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $zip = zip_open($file['tmp_name']); while (($zipfile = zip_read($zip)) !== false) { - $type_zipfile = $this->guess_file_type(zip_entry_name($zipfile)); + $type_zipfile = $this->guessFileType(zip_entry_name($zipfile)); if ($type_file !== 'unknown') { $list_files[$type_zipfile][] = zip_entry_read( @@ -67,13 +67,13 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { // And finally all other files. $error = false; foreach ($list_files['opml'] as $opml_file) { - $error = $this->import_opml($opml_file); + $error = $this->importOpml($opml_file); } foreach ($list_files['json_starred'] as $article_file) { - $error = $this->import_articles($article_file, true); + $error = $this->importArticles($article_file, true); } foreach ($list_files['json_feed'] as $article_file) { - $error = $this->import_articles($article_file); + $error = $this->importArticles($article_file); } // And finally, we get import status and redirect to the home page @@ -107,7 +107,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { )); } - private function guess_file_type($filename) { + private function guessFileType($filename) { // A *very* basic guess file type function. Only based on filename // That's could be improved but should be enough, at least for a first // implementation. @@ -128,7 +128,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { } } - private function import_opml($opml_file) { + private function importOpml($opml_file) { $opml_array = array(); try { $opml_array = libopml_parse_string($opml_file); @@ -164,7 +164,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { private function addFeedOpml($feed_elt, $parent_cat) { if (is_null($parent_cat)) { // This feed has no parent category so we get the default one - $parent_cat = $catDAO->getDefault()->name(); + $parent_cat = $this->catDAO->getDefault()->name(); } $cat = $this->catDAO->searchByName($parent_cat); @@ -199,7 +199,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $id = $this->feedDAO->addFeedObject($feed); $error = ($id === false); } catch (FreshRSS_Feed_Exception $e) { - Minz_Log::record($e->getMessage(), Minz_Log::WARNING); + Minz_Log::warning($e->getMessage()); $error = true; } @@ -226,28 +226,23 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { return $error; } - private function import_articles($article_file, $starred = false) { + private function importArticles($article_file, $starred = false) { $article_object = json_decode($article_file, true); if (is_null($article_object)) { Minz_Log::warning('Try to import a non-JSON file'); return true; } + $is_read = $this->view->conf->mark_when['reception'] ? 1 : 0; + $google_compliant = (strpos($article_object['id'], 'com.google') !== false); + $error = false; foreach ($article_object['items'] as $item) { - $key = $google_compliant ? 'htmlUrl' : 'feedUrl'; - $feed = $this->feedDAO->searchByUrl($item['origin'][$key]); + $feed = $this->addFeedArticles($item['origin'], $google_compliant); if (is_null($feed)) { - $feed = new FreshRSS_Feed($item['origin'][$key]); - $feed->_name ($item['origin']['title']); - $feed->_website ($item['origin']['htmlUrl']); - - $error = $this->addFeed($feed); // TODO - - if ($error) { - continue; - } + $error = true; + continue; } $author = isset($item['author']) ? $item['author'] : ''; @@ -258,14 +253,55 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { return strpos($var, '/state/com.google') === false; }); } + $entry = new FreshRSS_Entry( $feed->id(), $item['id'], $item['title'], $author, $item[$key_content]['content'], $item['alternate'][0]['href'], - $item['published'], false, $starred, $tags + $item['published'], $is_read, $starred ); + $entry->_tags($tags); - Minz_Log::debug(print_r($entry, true)); // TODO + $id = $this->entryDAO->addEntryObject( + $entry, $this->view->conf, $feed->keepHistory() + ); + + if (!$error && ($id === false)) { + $error = true; + } } + + return $error; + } + + private function addFeedArticles($origin, $google_compliant) { + $default_cat = $this->catDAO->getDefault(); + + $return = null; + $key = $google_compliant ? 'htmlUrl' : 'feedUrl'; + $url = $origin[$key]; + $name = $origin['title']; + $website = $origin['htmlUrl']; + $error = false; + try { + // Create a Feed object and add it in DB + $feed = new FreshRSS_Feed($url); + $feed->_category($default_cat->id()); + $feed->_name($name); + $feed->_website($website); + + // addFeedObject checks if feed is already in DB so nothing else to + // check here + $id = $this->feedDAO->addFeedObject($feed); + + if ($id !== false) { + $feed->_id($id); + $return = $feed; + } + } catch (FreshRSS_Feed_Exception $e) { + Minz_Log::warning($e->getMessage()); + } + + return $return; } public function exportAction() { @@ -283,16 +319,16 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { // Stuff with content if ($export_opml) { - $zip->addFromString('feeds.opml', $this->generate_opml()); + $zip->addFromString('feeds.opml', $this->generateOpml()); } if ($export_starred) { - $zip->addFromString('starred.json', $this->generate_articles('starred')); + $zip->addFromString('starred.json', $this->generateArticles('starred')); } foreach ($export_feeds as $feed_id) { $feed = $this->feedDAO->searchById($feed_id); $zip->addFromString( 'feed_' . $feed->category() . '_' . $feed->id() . '.json', - $this->generate_articles('feed', $feed) + $this->generateArticles('feed', $feed) ); } @@ -306,7 +342,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { } } - private function generate_opml() { + private function generateOpml() { $list = array(); foreach ($this->catDAO->listCategories() as $key => $cat) { $list[$key]['name'] = $cat->name(); @@ -317,7 +353,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { return $this->view->helperToString('export/opml'); } - private function generate_articles($type, $feed = NULL) { + private function generateArticles($type, $feed = NULL) { $this->view->categories = $this->catDAO->listCategories(); if ($type == 'starred') { diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 8be732b98..6a9b839b9 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -19,7 +19,8 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { } public function addCategoryObject($category) { - if (!$this->searchByName($category->name())) { + $cat = $this->searchByName($category->name()); + if (!$cat) { // Category does not exist yet in DB so we add it before continue $values = array( 'name' => $category->name(), @@ -27,7 +28,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { return $this->addCategory($values); } - return false; + return $cat->id(); } public function updateCategory ($id, $valuesTmp) { diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index e4cf128ea..6d00967fc 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -35,6 +35,36 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { } } + 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); + + $eDate = $entry->date(true); + + if ($feedHistory == -2) { + $feedHistory = $conf->keep_history_default; + } + + if (!isset($existingGuids[$entry->guid()]) && + ($feedHistory != 0 || $eDate >= $date_min)) { + $values = $entry->toArray(); + + $useDeclaredDate = empty($existingGuids); + $values['id'] = ($useDeclaredDate || $eDate < $date_min) ? + min(time(), $eDate) . uSecString() : + uTimeString(); + + return $this->addEntry($values); + } + + // We don't return Entry object to avoid a research in DB + return -1; + } + public function markFavorite($ids, $is_favorite = true) { if (!is_array($ids)) { $ids = array($ids); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index eac21df7e..b65ff4af0 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -29,7 +29,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { // should not be aware about feed class // Add feed only if we don't find it in DB - if (!$this->searchByUrl($feed->url())) { + $feed_search = $this->searchByUrl($feed->url()); + if (!$feed_search) { $values = array( 'id' => $feed->id(), 'url' => $feed->url(), @@ -50,7 +51,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return $id; } - return false; + return $feed_search->id(); } public function updateFeed ($id, $valuesTmp) { -- cgit v1.2.3 From af9e5a408a9384399cb6a15bbe33cc4baa1a1d7e Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 3 Jul 2014 01:28:56 +0200 Subject: Small correction favicon Ensure to try the website favicon before trying the feed URL favicon (which might be on e.g. FeedBurner with an anonymous favicon). --- app/Models/FeedDAO.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index b65ff4af0..6cb49a5be 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -245,7 +245,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } public function listFeedsOrderUpdate ($cacheDuration = 1500) { - $sql = 'SELECT id, name, url, lastUpdate, pathEntries, httpAuth, keep_history ' + $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history ' . 'FROM `' . $this->prefix . 'feed` ' . 'WHERE lastUpdate < ' . (time() - intval($cacheDuration)) . ' ORDER BY lastUpdate'; -- cgit v1.2.3 From d6f414108667f32fe2b480adeb7ec9c218db2f4a Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 3 Jul 2014 21:26:30 +0200 Subject: Preparation for SQLite https://github.com/marienfressinaud/FreshRSS/issues/100 --- app/Controllers/configureController.php | 2 +- app/Controllers/entryController.php | 6 +- app/Controllers/feedController.php | 4 +- app/Controllers/importExportController.php | 2 +- app/Controllers/indexController.php | 2 +- app/Controllers/usersController.php | 6 +- app/Models/CategoryDAO.php | 12 ++-- app/Models/Entry.php | 2 +- app/Models/EntryDAO.php | 101 +++++++++++++++++------------ app/Models/EntryDAO_SQLite.php | 42 ++++++++++++ app/Models/Factory.php | 13 ++++ app/Models/FeedDAO.php | 46 ++++++------- app/Models/UserDAO.php | 31 ++++++--- app/SQL/install.sql.mysql.php | 60 +++++++++++++++++ app/SQL/install.sql.sqlite.php | 57 ++++++++++++++++ app/sql.php | 58 ----------------- lib/Minz/Configuration.php | 80 ++++++++++++++--------- lib/Minz/ModelPdo.php | 52 +++++++++------ 18 files changed, 375 insertions(+), 201 deletions(-) create mode 100644 app/Models/EntryDAO_SQLite.php create mode 100644 app/Models/Factory.php create mode 100644 app/SQL/install.sql.mysql.php create mode 100644 app/SQL/install.sql.sqlite.php delete mode 100644 app/sql.php (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php index 89130cae4..cb8e6528f 100755 --- a/app/Controllers/configureController.php +++ b/app/Controllers/configureController.php @@ -291,7 +291,7 @@ class FreshRSS_configure_Controller extends Minz_ActionController { Minz_View::prependTitle(Minz_Translate::t('archiving_configuration') . ' · '); - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); $this->view->nb_total = $entryDAO->count(); $this->view->size_user = $entryDAO->size(); diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php index bbcb990f5..c2d897cf3 100755 --- a/app/Controllers/entryController.php +++ b/app/Controllers/entryController.php @@ -43,7 +43,7 @@ class FreshRSS_entry_Controller extends Minz_ActionController { $nextGet = Minz_Request::param ('nextGet', $get); $idMax = Minz_Request::param ('idMax', 0); - $entryDAO = new FreshRSS_EntryDAO (); + $entryDAO = FreshRSS_Factory::createEntryDao(); if ($id == false) { if (!$get) { $entryDAO->markReadEntries ($idMax); @@ -85,7 +85,7 @@ class FreshRSS_entry_Controller extends Minz_ActionController { $id = Minz_Request::param ('id'); if ($id) { - $entryDAO = new FreshRSS_EntryDAO (); + $entryDAO = FreshRSS_Factory::createEntryDao(); $entryDAO->markFavorite ($id, (bool)(Minz_Request::param ('is_favorite', true))); } } @@ -97,7 +97,7 @@ class FreshRSS_entry_Controller extends Minz_ActionController { // La table des entrées a tendance à grossir énormément // Cette action permet d'optimiser cette table permettant de grapiller un peu de place // Cette fonctionnalité n'est à appeler qu'occasionnellement - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); $entryDAO->optimizeTable(); $feedDAO = new FreshRSS_FeedDAO(); diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 5f5a40bc7..149557a48 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -102,7 +102,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $is_read = $this->view->conf->mark_when['reception'] ? 1 : 0; - $entryDAO = new FreshRSS_EntryDAO (); + $entryDAO = FreshRSS_Factory::createEntryDao(); $entries = array_reverse($feed->entries()); //We want chronological order and SimplePie uses reverse order // on calcule la date des articles les plus anciens qu'on accepte @@ -217,7 +217,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { @set_time_limit(300); $feedDAO = new FreshRSS_FeedDAO (); - $entryDAO = new FreshRSS_EntryDAO (); + $entryDAO = FreshRSS_Factory::createEntryDao(); Minz_Session::_param('actualize_feeds', false); $id = Minz_Request::param ('id'); diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index 3cd791781..12154d9b6 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -12,7 +12,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { require_once(LIB_PATH . '/lib_opml.php'); $this->catDAO = new FreshRSS_CategoryDAO(); - $this->entryDAO = new FreshRSS_EntryDAO(); + $this->entryDAO = FreshRSS_Factory::createEntryDao(); $this->feedDAO = new FreshRSS_FeedDAO(); } diff --git a/app/Controllers/indexController.php b/app/Controllers/indexController.php index c3f39fbe5..46c9518c9 100755 --- a/app/Controllers/indexController.php +++ b/app/Controllers/indexController.php @@ -45,7 +45,7 @@ class FreshRSS_index_Controller extends Minz_ActionController { } $catDAO = new FreshRSS_CategoryDAO(); - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); $this->view->cat_aside = $catDAO->listCategories (); $this->view->nb_favorites = $entryDAO->countUnreadReadFavorites (); diff --git a/app/Controllers/usersController.php b/app/Controllers/usersController.php index 38b8f829b..35fa3675f 100644 --- a/app/Controllers/usersController.php +++ b/app/Controllers/usersController.php @@ -99,7 +99,8 @@ class FreshRSS_users_Controller extends Minz_ActionController { public function createAction() { if (Minz_Request::isPost() && Minz_Configuration::isAdmin(Minz_Session::param('currentUser', '_'))) { - require_once(APP_PATH . '/sql.php'); + $db = Minz_Configuration::dataBase(); + require_once(APP_PATH . '/SQL/sql.' . $db['type'] . '.php'); $new_user_language = Minz_Request::param('new_user_language', $this->view->conf->language); if (!in_array($new_user_language, $this->view->conf->availableLanguages())) { @@ -170,7 +171,8 @@ class FreshRSS_users_Controller extends Minz_ActionController { public function deleteAction() { if (Minz_Request::isPost() && Minz_Configuration::isAdmin(Minz_Session::param('currentUser', '_'))) { - require_once(APP_PATH . '/sql.php'); + $db = Minz_Configuration::dataBase(); + require_once(APP_PATH . '/SQL/sql.' . $db['type'] . '.php'); $username = Minz_Request::param('username'); $ok = ctype_alnum($username); diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 6a9b839b9..3003dea0d 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -12,8 +12,8 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $this->bd->lastInsertId(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error addCategory: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -43,8 +43,8 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error updateCategory: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -58,8 +58,8 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error deleteCategory: ' . $info[2], Minz_Log::ERROR); return false; } } diff --git a/app/Models/Entry.php b/app/Models/Entry.php index fa9066d5b..a24a902d5 100644 --- a/app/Models/Entry.php +++ b/app/Models/Entry.php @@ -154,7 +154,7 @@ class FreshRSS_Entry extends Minz_Model { // Gestion du contenu // On cherche à récupérer les articles en entier... même si le flux ne le propose pas if ($pathEntries) { - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); $entry = $entryDAO->searchByGuid($this->feed, $this->guid); if($entry) { diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 4e24541dc..9ba0d2128 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -1,9 +1,18 @@ prefix . 'entry`(id, guid, title, author, content_bin, link, date, is_read, is_favorite, id_feed, tags) ' - . 'VALUES(?, ?, ?, ?, COMPRESS(?), ?, ?, ?, ?, ?, ?)'; + $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(?)' : '?') + . ', ?, ?, ?, ?, ?, ?)'; $stm = $this->bd->prepare ($sql); $values = array ( @@ -23,9 +32,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $this->bd->lastInsertId(); } else { - $info = $stm->errorInfo(); + $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 - Minz_Log::record ('SQL error ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] + Minz_Log::record('SQL error addEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] . ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title'], Minz_Log::ERROR); } /*else { Minz_Log::record ('SQL error ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] @@ -78,14 +87,14 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markFavorite: ' . $info[2], Minz_Log::ERROR); return false; } } public function markRead($ids, $is_read = true) { - if (is_array($ids)) { + if (is_array($ids)) { //Many IDs at once if (count($ids) < 6) { //Speed heuristics $affected = 0; foreach ($ids as $id) { @@ -102,8 +111,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $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); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack(); return false; } @@ -121,8 +130,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { . '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); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack(); return false; } @@ -134,14 +143,14 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $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); + . 'WHERE e.id=? AND e.is_read<>?'; + $values = array($is_read ? 1 : 0, $ids, $is_read ? 1 : 0); $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); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -161,8 +170,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ()) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadEntries: ' . $info[2], Minz_Log::ERROR); return false; } } else { @@ -179,8 +188,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $values = array ($idMax); $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadEntries: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } @@ -198,8 +207,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0)'; $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ())) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadEntries: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } @@ -220,8 +229,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); return false; } } else { @@ -233,8 +242,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $values = array ($id, $idMax); $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } @@ -254,8 +263,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $values = array ($id); $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } @@ -278,8 +287,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCatName: ' . $info[2], Minz_Log::ERROR); return false; } } else { @@ -293,8 +302,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $values = array($name, $idMax); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { - $info = $stm->errorInfo(); - Minz_Log::record('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCatName: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack(); return false; } @@ -315,8 +324,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $values = array($name); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { - $info = $stm->errorInfo(); - Minz_Log::record('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCatName: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack(); return false; } @@ -337,8 +346,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); return false; } } else { @@ -350,8 +359,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $values = array ($id, $idMax); $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } @@ -364,8 +373,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $values = array ($id); $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } @@ -378,7 +387,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { public function searchByGuid ($feed_id, $id) { // un guid est unique pour un flux donné - $sql = 'SELECT id, guid, title, author, UNCOMPRESS(content_bin) AS content, link, date, is_read, is_favorite, id_feed, tags ' + $sql = 'SELECT id, guid, title, author, ' + . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') + . ', link, date, is_read, is_favorite, id_feed, tags ' . 'FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid=?'; $stm = $this->bd->prepare ($sql); @@ -394,7 +405,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { } public function searchById ($id) { - $sql = 'SELECT id, guid, title, author, UNCOMPRESS(content_bin) AS content, link, date, is_read, is_favorite, id_feed, tags ' + $sql = 'SELECT id, guid, title, author, ' + . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') + . ', link, date, is_read, is_favorite, id_feed, tags ' . 'FROM `' . $this->prefix . 'entry` WHERE id=?'; $stm = $this->bd->prepare ($sql); @@ -520,7 +533,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $search .= 'AND e1.tags LIKE ? '; $values[] = '%' . $word .'%'; } else { - $search .= 'AND CONCAT(e1.title, UNCOMPRESS(e1.content_bin)) LIKE ? '; + $search .= 'AND CONCAT(e1.title, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin)' : 'content') . ') LIKE ? '; $values[] = '%' . $word .'%'; } } @@ -539,7 +552,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { public function listWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $showOlderUnreadsorFavorites = false, $keepHistoryDefault = 0) { list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filter, $date_min, $showOlderUnreadsorFavorites, $keepHistoryDefault); - $sql = 'SELECT e.id, e.guid, e.title, e.author, UNCOMPRESS(e.content_bin) AS content, e.link, e.date, e.is_read, e.is_favorite, e.id_feed, e.tags ' + $sql = 'SELECT e.id, e.guid, e.title, e.author, ' + . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') + . ', e.link, e.date, e.is_read, e.is_favorite, e.id_feed, e.tags ' . 'FROM `' . $this->prefix . 'entry` e ' . 'INNER JOIN (' . $sql diff --git a/app/Models/EntryDAO_SQLite.php b/app/Models/EntryDAO_SQLite.php new file mode 100644 index 000000000..f148f3c63 --- /dev/null +++ b/app/Models/EntryDAO_SQLite.php @@ -0,0 +1,42 @@ +markRead($id, $is_read); + } + return $affected; + } + } else { + $this->bd->beginTransaction(); + $sql = 'UPDATE `' . $this->prefix . 'entry` e SET e.is_read = ? WHERE e.id=? AND e.is_read<>?'; + $values = array($is_read ? 1 : 0, $ids, $is_read ? 1 : 0); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute ($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack (); + return false; + } + $affected = $stm->rowCount(); + if ($affected > 0) { + $sql = 'UPDATE `' . $this->prefix . 'feed` f SET f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' + . 'WHERE f.id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; + $values = array($ids); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute ($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack (); + return false; + } + } + $this->bd->commit(); + return $affected; + } + } +} diff --git a/app/Models/Factory.php b/app/Models/Factory.php new file mode 100644 index 000000000..bea89c114 --- /dev/null +++ b/app/Models/Factory.php @@ -0,0 +1,13 @@ +execute ($values)) { return $this->bd->lastInsertId(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error addFeed: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -76,8 +76,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error updateFeed: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -106,8 +106,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error updateLastUpdate: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -130,8 +130,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error changeCategory: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -155,8 +155,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error deleteFeed: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -181,8 +181,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if ($stm && $stm->execute ($values)) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error deleteFeedByCategory: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -301,8 +301,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if ($stm && $stm->execute()) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error updateCachedValues: ' . $info[2], Minz_Log::ERROR); return false; } } @@ -313,11 +313,11 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { $values = array($id); $this->bd->beginTransaction (); if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error truncate: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack (); + return false; + } $affected = $stm->rowCount(); $sql = 'UPDATE `' . $this->prefix . 'feed` f ' @@ -325,8 +325,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { $values = array ($id); $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error truncate: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } @@ -350,8 +350,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { if ($stm && $stm->execute ()) { return $stm->rowCount(); } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error cleanOldEntries: ' . $info[2], Minz_Log::ERROR); return false; } } diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php index a25b57f89..dcf847a62 100644 --- a/app/Models/UserDAO.php +++ b/app/Models/UserDAO.php @@ -2,33 +2,44 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { public function createUser($username) { - require_once(APP_PATH . '/sql.php'); $db = Minz_Configuration::dataBase(); + require_once(APP_PATH . '/SQL/sql.' . $db['type'] . '.php'); + + if (defined('SQL_CREATE_TABLES')) { + $sql = sprintf(SQL_CREATE_TABLES, $db['prefix'] . $username . '_', Minz_Translate::t('default_category')); + $stm = $c->prepare($sql); + $ok = $stm && $stm->execute(); + } else { + global $SQL_CREATE_TABLES; + if (is_array($SQL_CREATE_TABLES)) { + $ok = true; + foreach ($SQL_CREATE_TABLES as $instruction) { + $sql = sprintf($instruction, '', Minz_Translate::t('default_category')); + $stm = $c->prepare($sql); + $ok &= ($stm && $stm->execute()); + } + } + } - $sql = sprintf(SQL_CREATE_TABLES, $db['prefix'] . $username . '_'); - $stm = $this->bd->prepare($sql, array(PDO::ATTR_EMULATE_PREPARES => true)); - $values = array( - 'catName' => Minz_Translate::t('default_category'), - ); - if ($stm && $stm->execute($values)) { + if ($ok) { return true; } else { - $info = $stm->errorInfo(); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); return false; } } public function deleteUser($username) { - require_once(APP_PATH . '/sql.php'); $db = Minz_Configuration::dataBase(); + require_once(APP_PATH . '/SQL/sql.' . $db['type'] . '.php'); $sql = sprintf(SQL_DROP_TABLES, $db['prefix'] . $username . '_'); $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { return true; } else { - $info = $stm->errorInfo(); + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); return false; } 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 @@ +bd = self::$sharedBd; $this->prefix = self::$sharedPrefix; return; } - $db = Minz_Configuration::dataBase (); - $driver_options = null; + $db = Minz_Configuration::dataBase(); try { $type = $db['type']; - if($type == 'mysql') { - $string = $type - . ':host=' . $db['host'] + if ($type === 'mysql') { + $string = 'mysql:host=' . $db['host'] . ';dbname=' . $db['base'] . ';charset=utf8'; $driver_options = array( - PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' + PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', + ); + $this->prefix = $db['prefix'] . Minz_Session::param('currentUser', '_') . '_'; + } elseif ($type === 'sqlite') { + $string = 'sqlite:' . DATA_PATH . '/' . Minz_Session::param('currentUser', '_') . '.sqlite'; + $driver_options = array( + //PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, + ); + $this->prefix = ''; + } else { + throw new Minz_PDOConnectionException( + 'Invalid database type!', + $db['user'], Minz_Exception::ERROR ); - } elseif($type == 'sqlite') { - $string = $type . ':/' . DATA_PATH . $db['base'] . '.sqlite'; //TODO: DEBUG UTF-8 http://www.siteduzero.com/forum/sujet/sqlite-connexion-utf-8-18797 } + self::$sharedDbType = $type; + self::$sharedPrefix = $this->prefix; - $this->bd = new FreshPDO ( + $this->bd = new FreshPDO( $string, $db['user'], $db['password'], $driver_options ); self::$sharedBd = $this->bd; - - $this->prefix = $db['prefix'] . Minz_Session::param('currentUser', '_') . '_'; - self::$sharedPrefix = $this->prefix; } catch (Exception $e) { - throw new Minz_PDOConnectionException ( + throw new Minz_PDOConnectionException( $string, $db['user'], Minz_Exception::ERROR ); @@ -81,15 +93,15 @@ class Minz_ModelPdo { } public function size($all = false) { - $db = Minz_Configuration::dataBase (); + $db = Minz_Configuration::dataBase(); $sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema = ?'; - $values = array ($db['base']); + $values = array($db['base']); if (!$all) { $sql .= ' AND table_name LIKE ?'; $values[] = $this->prefix . '%'; } - $stm = $this->bd->prepare ($sql); - $stm->execute ($values); + $stm = $this->bd->prepare($sql); + $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; } @@ -107,12 +119,12 @@ class FreshPDO extends PDO { } } - public function prepare ($statement, $driver_options = array()) { + public function prepare($statement, $driver_options = array()) { FreshPDO::check($statement); return parent::prepare($statement, $driver_options); } - public function exec ($statement) { + public function exec($statement) { FreshPDO::check($statement); return parent::exec($statement); } -- cgit v1.2.3 From b34f59e85aa851b82210fdc50074918034f960db Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 3 Jul 2014 22:48:29 +0200 Subject: Preparation #3 for SQLite https://github.com/marienfressinaud/FreshRSS/issues/100 --- app/Controllers/configureController.php | 6 +++--- app/Controllers/entryController.php | 4 ++-- app/Controllers/feedController.php | 8 ++++---- app/Controllers/importExportController.php | 2 +- app/Controllers/indexController.php | 4 ++-- app/Controllers/javascriptController.php | 2 +- app/Models/Category.php | 2 +- app/Models/Entry.php | 2 +- app/Models/EntryDAOSQLite.php | 10 +++++----- app/Models/Factory.php | 9 +++++++++ app/Models/Feed.php | 4 ++-- app/Models/FeedDAO.php | 16 ++++++++-------- app/Models/FeedDAOSQLite.php | 5 +++++ p/api/greader.php | 2 +- 14 files changed, 45 insertions(+), 31 deletions(-) create mode 100644 app/Models/FeedDAOSQLite.php (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php index cb8e6528f..a608df162 100755 --- a/app/Controllers/configureController.php +++ b/app/Controllers/configureController.php @@ -14,7 +14,7 @@ class FreshRSS_configure_Controller extends Minz_ActionController { } public function categorizeAction () { - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $catDAO = new FreshRSS_CategoryDAO (); $defaultCategory = $catDAO->getDefault (); $defaultId = $defaultCategory->id (); @@ -70,7 +70,7 @@ class FreshRSS_configure_Controller extends Minz_ActionController { $catDAO = new FreshRSS_CategoryDAO (); $this->view->categories = $catDAO->listCategories (false); - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $this->view->feeds = $feedDAO->listFeeds (); $id = Minz_Request::param ('id'); @@ -336,7 +336,7 @@ class FreshRSS_configure_Controller extends Minz_ActionController { ); break; case 'f': - $dao = new FreshRSS_FeedDAO(); + $dao = FreshRSS_Factory::createFeedDao(); $feed = $dao->searchById(substr($query['get'], 2)); $this->view->query_get[$key] = array( 'type' => 'feed', diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php index c2d897cf3..2d7fa718a 100755 --- a/app/Controllers/entryController.php +++ b/app/Controllers/entryController.php @@ -100,7 +100,7 @@ class FreshRSS_entry_Controller extends Minz_ActionController { $entryDAO = FreshRSS_Factory::createEntryDao(); $entryDAO->optimizeTable(); - $feedDAO = new FreshRSS_FeedDAO(); + $feedDAO = FreshRSS_Factory::createFeedDao(); $feedDAO->updateCachedValues(); invalidateHttpCache(); @@ -124,7 +124,7 @@ class FreshRSS_entry_Controller extends Minz_ActionController { $nb_month_old = max($this->view->conf->old_entries, 1); $date_min = time() - (3600 * 24 * 30 * $nb_month_old); - $feedDAO = new FreshRSS_FeedDAO(); + $feedDAO = FreshRSS_Factory::createFeedDao(); $feeds = $feedDAO->listFeedsOrderUpdate(); $nbTotal = 0; diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 149557a48..d30b60877 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -31,7 +31,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { ), true); } - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $this->catDAO = new FreshRSS_CategoryDAO (); $this->catDAO->checkDefault (); @@ -201,7 +201,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { public function truncateAction () { if (Minz_Request::isPost ()) { $id = Minz_Request::param ('id'); - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $n = $feedDAO->truncate($id); $notif = array( 'type' => $n === false ? 'bad' : 'good', @@ -216,7 +216,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { public function actualizeAction () { @set_time_limit(300); - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $entryDAO = FreshRSS_Factory::createEntryDao(); Minz_Session::_param('actualize_feeds', false); @@ -375,7 +375,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $type = Minz_Request::param ('type', 'feed'); $id = Minz_Request::param ('id'); - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); if ($type == 'category') { if ($feedDAO->deleteFeedByCategory ($id)) { $notif = array ( diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index 12154d9b6..6b4c3e81a 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -13,7 +13,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $this->catDAO = new FreshRSS_CategoryDAO(); $this->entryDAO = FreshRSS_Factory::createEntryDao(); - $this->feedDAO = new FreshRSS_FeedDAO(); + $this->feedDAO = FreshRSS_Factory::createFeedDao(); } public function indexAction() { diff --git a/app/Controllers/indexController.php b/app/Controllers/indexController.php index 46c9518c9..4340865ec 100755 --- a/app/Controllers/indexController.php +++ b/app/Controllers/indexController.php @@ -126,7 +126,7 @@ class FreshRSS_index_Controller extends Minz_ActionController { // on essaye de récupérer tous les articles if ($state === FreshRSS_Entry::STATE_NOT_READ && empty($entries) && ($state_param === null)) { Minz_Log::record ('Conflicting information about nbNotRead!', Minz_Log::DEBUG); - $feedDAO = new FreshRSS_FeedDAO(); + $feedDAO = FreshRSS_Factory::createFeedDao(); try { $feedDAO->updateCachedValues(); } catch (Exception $ex) { @@ -187,7 +187,7 @@ class FreshRSS_index_Controller extends Minz_ActionController { case 'f': $feed = FreshRSS_CategoryDAO::findFeed($this->view->cat_aside, $getId); if (empty($feed)) { - $feedDAO = new FreshRSS_FeedDAO(); + $feedDAO = FreshRSS_Factory::createFeedDao(); $feed = $feedDAO->searchById($getId); } if ($feed) { diff --git a/app/Controllers/javascriptController.php b/app/Controllers/javascriptController.php index 3d741e298..737908c91 100755 --- a/app/Controllers/javascriptController.php +++ b/app/Controllers/javascriptController.php @@ -7,7 +7,7 @@ class FreshRSS_javascript_Controller extends Minz_ActionController { public function actualizeAction () { header('Content-Type: text/javascript; charset=UTF-8'); - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $this->view->feeds = $feedDAO->listFeedsOrderUpdate(); } diff --git a/app/Models/Category.php b/app/Models/Category.php index 328bae799..0a0dbd3ca 100644 --- a/app/Models/Category.php +++ b/app/Models/Category.php @@ -44,7 +44,7 @@ class FreshRSS_Category extends Minz_Model { } public function feeds () { if ($this->feeds === null) { - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $this->feeds = $feedDAO->listByCategory ($this->id ()); $this->nbFeed = 0; $this->nbNotRead = 0; diff --git a/app/Models/Entry.php b/app/Models/Entry.php index a24a902d5..0bf1f2616 100644 --- a/app/Models/Entry.php +++ b/app/Models/Entry.php @@ -74,7 +74,7 @@ class FreshRSS_Entry extends Minz_Model { } public function feed ($object = false) { if ($object) { - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); return $feedDAO->searchById ($this->feed); } else { return $this->feed; diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index 45d3a3ea9..0a837b3aa 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -13,24 +13,24 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { } } else { $this->bd->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` e SET e.is_read = ? WHERE e.id=? AND e.is_read<>?'; + $sql = 'UPDATE `' . $this->prefix . 'entry` SET is_read=? WHERE id=? AND is_read<>?'; $values = array($is_read ? 1 : 0, $ids, $is_read ? 1 : 0); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute ($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); + Minz_Log::record('SQL error markRead 1: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } $affected = $stm->rowCount(); if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f SET f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' - . 'WHERE f.id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; + $sql = 'UPDATE `' . $this->prefix . 'feed` SET cache_nbUnreads=cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' + . 'WHERE id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; $values = array($ids); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute ($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); + Minz_Log::record('SQL error markRead 2: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack (); return false; } diff --git a/app/Models/Factory.php b/app/Models/Factory.php index 3ef68c41d..95d21a277 100644 --- a/app/Models/Factory.php +++ b/app/Models/Factory.php @@ -2,6 +2,15 @@ class FreshRSS_Factory { + public static function createFeedDao() { + $db = Minz_Configuration::dataBase(); + if ($db['type'] === 'sqlite') { + return new FreshRSS_FeedDAOSQLite(); + } else { + return new FreshRSS_FeedDAO(); + } + } + public static function createEntryDao() { $db = Minz_Configuration::dataBase(); if ($db['type'] === 'sqlite') { diff --git a/app/Models/Feed.php b/app/Models/Feed.php index ba142c8c8..8093b2bfd 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -87,7 +87,7 @@ class FreshRSS_Feed extends Minz_Model { } public function nbEntries () { if ($this->nbEntries < 0) { - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $this->nbEntries = $feedDAO->countEntries ($this->id ()); } @@ -95,7 +95,7 @@ class FreshRSS_Feed extends Minz_Model { } public function nbNotRead () { if ($this->nbNotRead < 0) { - $feedDAO = new FreshRSS_FeedDAO (); + $feedDAO = FreshRSS_Factory::createFeedDao(); $this->nbNotRead = $feedDAO->countNotRead ($this->id ()); } diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 3f3847aa4..9534d61bd 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -84,15 +84,15 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { public function updateLastUpdate ($id, $inError = 0, $updateCache = true) { if ($updateCache) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE - . 'SET f.cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=f.id),' - . 'f.cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=f.id AND e2.is_read=0),' + $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE + . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=feed.id),' + . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=feed.id AND e2.is_read=0),' . 'lastUpdate=?, error=? ' - . 'WHERE f.id=?'; + . 'WHERE id=?'; } else { - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' + $sql = 'UPDATE `' . $this->prefix . 'feed` ' . 'SET lastUpdate=?, error=? ' - . 'WHERE f.id=?'; + . 'WHERE id=?'; } $values = array ( @@ -320,8 +320,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } $affected = $stm->rowCount(); - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' - . 'SET f.cache_nbEntries=0, f.cache_nbUnreads=0 WHERE f.id=?'; + $sql = 'UPDATE `' . $this->prefix . 'feed` ' + . 'SET cache_nbEntries=0, cache_nbUnreads=0 WHERE id=?'; $values = array ($id); $stm = $this->bd->prepare ($sql); if (!($stm && $stm->execute ($values))) { diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php new file mode 100644 index 000000000..f3c92149e --- /dev/null +++ b/app/Models/FeedDAOSQLite.php @@ -0,0 +1,5 @@ +arrayFeedCategoryNames(); switch ($path) { -- cgit v1.2.3 From 805c91da98c2f582e279f3c853fba9e43f572419 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 5 Jul 2014 01:52:41 +0200 Subject: Add support for SQLite https://github.com/marienfressinaud/FreshRSS/issues/100 Warning: MySQL has been changed too, so bugs may have been introduced --- CHANGELOG | 2 + README.md | 2 +- app/Models/CategoryDAO.php | 4 +- app/Models/EntryDAO.php | 526 +++++++++++++++++------------------------- app/Models/EntryDAOSQLite.php | 111 ++++++++- app/Models/FeedDAO.php | 218 ++++++++--------- app/Models/FeedDAOSQLite.php | 14 ++ lib/Minz/ModelPdo.php | 14 -- p/api/greader.php | 12 +- 9 files changed, 441 insertions(+), 462 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/CHANGELOG b/CHANGELOG index 9325ee724..4ca0199e1 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -2,6 +2,8 @@ ## 2014-xx-xx FreshRSS 0.7.x +* SQL + * Add support for SQLite (beta) in addition to MySQL * SimplePie * Complies with HTTP "301 Moved Permanently" responses by automatically updating the URL of feeds that have changed address. diff --git a/README.md b/README.md index ce3c8ef32..fff08472b 100644 --- a/README.md +++ b/README.md @@ -35,7 +35,7 @@ Privilégiez pour cela des demandes sur GitHub * PHP 5.2.1+ (PHP 5.3.7+ recommandé) * Requis : [PDO_MySQL](http://php.net/pdo-mysql), [cURL](http://php.net/curl), [LibXML](http://php.net/xml), [PCRE](http://php.net/pcre), [ctype](http://php.net/ctype) * Recommandés : [JSON](http://php.net/json), [zlib](http://php.net/zlib), [mbstring](http://php.net/mbstring), [iconv](http://php.net/iconv), [Zip](http://php.net/zip) -* MySQL 5.0.3+ (ou SQLite 3.7.4+ à venir) +* MySQL 5.0.3+ (recommandé) ou SQLite 3.7.4+ (en bêta) * Un navigateur Web récent tel Firefox 4+, Chrome, Opera, Safari, Internet Explorer 9+ * Fonctionne aussi sur mobile diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 3003dea0d..f11f87f47 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -102,7 +102,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { $sql = 'SELECT c.id AS c_id, c.name AS c_name, ' . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.cache_nbEntries, f.cache_nbUnreads ') . 'FROM `' . $this->prefix . 'category` c ' - . 'LEFT OUTER JOIN `' . $this->prefix . 'feed` f ON f.category = c.id ' + . 'LEFT OUTER JOIN `' . $this->prefix . 'feed` f ON f.category=c.id ' . 'GROUP BY f.id ' . 'ORDER BY c.name, f.name'; $stm = $this->bd->prepare ($sql); @@ -166,7 +166,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo { } public function countNotRead ($id) { - $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE category=? AND e.is_read=0'; + $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE category=? AND e.is_read=0'; $stm = $this->bd->prepare ($sql); $values = array ($id); $stm->execute ($values); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 9ba0d2128..1775af63c 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -6,16 +6,16 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { return parent::$sharedDbType !== 'sqlite'; } - public function addEntry ($valuesTmp) { + public function addEntry($valuesTmp) { $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(?)' : '?') . ', ?, ?, ?, ?, ?, ?)'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ( + $values = array( $valuesTmp['id'], substr($valuesTmp['guid'], 0, 760), substr($valuesTmp['title'], 0, 255), @@ -29,7 +29,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { substr($valuesTmp['tags'], 0, 1023), ); - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute($values)) { return $this->bd->lastInsertId(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -78,13 +78,13 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if (!is_array($ids)) { $ids = array($ids); } - $sql = 'UPDATE `' . $this->prefix . 'entry` e ' - . 'SET e.is_favorite = ? ' - . 'WHERE e.id IN (' . str_repeat('?,', count($ids) - 1). '?)'; - $values = array ($is_favorite ? 1 : 0); + $sql = 'UPDATE `' . $this->prefix . 'entry` ' + . 'SET is_favorite=? ' + . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?)'; + $values = array($is_favorite ? 1 : 0); $values = array_merge($values, $ids); - $stm = $this->bd->prepare ($sql); - if ($stm && $stm->execute ($values)) { + $stm = $this->bd->prepare($sql); + if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -93,8 +93,38 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { } } + protected function updateCacheUnreads($catId = false, $feedId = false) { + $sql = 'UPDATE `' . $this->prefix . 'feed` f ' + . 'LEFT OUTER JOIN (' + . 'SELECT e.id_feed, ' + . 'COUNT(*) AS nbUnreads ' + . 'FROM `' . $this->prefix . 'entry` e ' + . 'WHERE e.is_read=0 ' + . 'GROUP BY e.id_feed' + . ') x ON x.id_feed=f.id ' + . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0) ' + . 'WHERE 1'; + $values = array(); + if ($feedId !== false) { + $sql .= ' AND f.id=?'; + $values[] = $id; + } + if ($catId !== false) { + $sql .= ' AND f.category=?'; + $values[] = $catId; + } + $stm = $this->bd->prepare($sql); + if ($stm && $stm->execute($values)) { + return true; + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error updateCacheUnreads: ' . $info[2], Minz_Log::ERROR); + return false; + } + } + public function markRead($ids, $is_read = true) { - if (is_array($ids)) { //Many IDs at once + if (is_array($ids)) { //Many IDs at once (used by API) if (count($ids) < 6) { //Speed heuristics $affected = 0; foreach ($ids as $id) { @@ -104,9 +134,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { } $this->bd->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` e ' - . 'SET e.is_read = ? ' - . 'WHERE e.id IN (' . str_repeat('?,', count($ids) - 1). '?)'; + $sql = 'UPDATE `' . $this->prefix . 'entry` ' + . 'SET is_read=? ' + . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?)'; $values = array($is_read ? 1 : 0); $values = array_merge($values, $ids); $stm = $this->bd->prepare($sql); @@ -117,34 +147,18 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { 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 == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack(); - return false; - } + if (($affected > 0) && (!$this->updateCacheUnreads(false, false))) { + $this->bd->rollBack(); + return false; } - $this->bd->commit(); return $affected; } else { - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'SET e.is_read = ?,' + $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=? AND e.is_read<>?'; - $values = array($is_read ? 1 : 0, $ids, $is_read ? 1 : 0); + . 'WHERE e.id=? AND e.is_read=?'; + $values = array($is_read ? 1 : 0, $ids, $is_read ? 0 : 1); $stm = $this->bd->prepare($sql); if ($stm && $stm->execute($values)) { return $stm->rowCount(); @@ -156,267 +170,139 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { } } - public function markReadEntries ($idMax = 0, $onlyFavorites = false, $priorityMin = 0) { + public function markReadEntries($idMax = 0, $onlyFavorites = false, $priorityMin = 0) { if ($idMax == 0) { - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'SET e.is_read = 1, f.cache_nbUnreads=0 ' - . 'WHERE e.is_read = 0'; - if ($onlyFavorites) { - $sql .= ' AND e.is_favorite = 1'; - } elseif ($priorityMin >= 0) { - $sql .= ' AND f.priority > ' . intval($priorityMin); - } - $stm = $this->bd->prepare ($sql); - if ($stm && $stm->execute ()) { - return $stm->rowCount(); - } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadEntries: ' . $info[2], Minz_Log::ERROR); - return false; - } - } else { - $this->bd->beginTransaction (); - - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'SET e.is_read = 1 ' - . 'WHERE e.is_read = 0 AND e.id <= ?'; - if ($onlyFavorites) { - $sql .= ' AND e.is_favorite = 1'; - } elseif ($priorityMin >= 0) { - $sql .= ' AND f.priority > ' . intval($priorityMin); - } - $values = array ($idMax); - $stm = $this->bd->prepare ($sql); - if (!($stm && $stm->execute ($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadEntries: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - $affected = $stm->rowCount(); - - if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' - . 'LEFT OUTER JOIN (' - . 'SELECT e.id_feed, ' - . 'COUNT(*) AS nbUnreads ' - . 'FROM `' . $this->prefix . 'entry` e ' - . 'WHERE e.is_read = 0 ' - . 'GROUP BY e.id_feed' - . ') x ON x.id_feed=f.id ' - . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0)'; - $stm = $this->bd->prepare ($sql); - if (!($stm && $stm->execute ())) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadEntries: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - } - - $this->bd->commit (); - return $affected; + $idMax = time() . '000000'; + Minz_Log::record($nb . 'Calling markReadEntries(0) is deprecated!', Minz_Log::DEBUG); + } + $this->bd->beginTransaction(); + + $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id ' + . 'SET e.is_read=1 ' + . 'WHERE e.is_read=0 AND e.id <= ?'; + if ($onlyFavorites) { + $sql .= ' AND e.is_favorite=1'; + } elseif ($priorityMin >= 0) { + $sql .= ' AND f.priority > ' . intval($priorityMin); + } + $values = array($idMax); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadEntries: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack(); + return false; + } + $affected = $stm->rowCount(); + if (($affected > 0) && (!$this->updateCacheUnreads(false, false))) { + $this->bd->rollBack(); + return false; } + $this->bd->commit(); + return $affected; } - public function markReadCat ($id, $idMax = 0) { + public function markReadCat($id, $idMax = 0) { if ($idMax == 0) { - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'SET e.is_read = 1, f.cache_nbUnreads=0 ' - . 'WHERE f.category = ? AND e.is_read = 0'; - $values = array ($id); - $stm = $this->bd->prepare ($sql); - if ($stm && $stm->execute ($values)) { - return $stm->rowCount(); - } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); - return false; - } - } else { - $this->bd->beginTransaction (); - - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'SET e.is_read = 1 ' - . 'WHERE f.category = ? AND e.is_read = 0 AND e.id <= ?'; - $values = array ($id, $idMax); - $stm = $this->bd->prepare ($sql); - if (!($stm && $stm->execute ($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - $affected = $stm->rowCount(); - - if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' - . 'LEFT OUTER JOIN (' - . 'SELECT e.id_feed, ' - . 'COUNT(*) AS nbUnreads ' - . 'FROM `' . $this->prefix . 'entry` e ' - . 'WHERE e.is_read = 0 ' - . 'GROUP BY e.id_feed' - . ') x ON x.id_feed=f.id ' - . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0) ' - . 'WHERE f.category = ?'; - $values = array ($id); - $stm = $this->bd->prepare ($sql); - if (!($stm && $stm->execute ($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - } + $idMax = time() . '000000'; + Minz_Log::record($nb . 'Calling markReadCat(0) is deprecated!', Minz_Log::DEBUG); + } + $this->bd->beginTransaction(); - $this->bd->commit (); - return $affected; + $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id ' + . 'SET e.is_read=1 ' + . 'WHERE f.category=? AND e.is_read=0 AND e.id <= ?'; + $values = array($id, $idMax); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack(); + return false; } + $affected = $stm->rowCount(); + if (($affected > 0) && (!$this->updateCacheUnreads($id, false))) { + $this->bd->rollBack(); + return false; + } + $this->bd->commit(); + return $affected; } - public function markReadCatName($name, $idMax = 0) { + public function markReadFeed($id, $idMax = 0) { if ($idMax == 0) { - $sql = 'UPDATE `' . $this->prefix . 'entry` e ' - . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'INNER JOIN `' . $this->prefix . 'category` c ON c.id = f.category ' - . 'SET e.is_read = 1, f.cache_nbUnreads=0 ' - . 'WHERE c.name = ?'; - $values = array($name); - $stm = $this->bd->prepare($sql); - if ($stm && $stm->execute($values)) { - return $stm->rowCount(); - } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadCatName: ' . $info[2], Minz_Log::ERROR); - return false; - } - } else { - $this->bd->beginTransaction(); + $idMax = time() . '000000'; + Minz_Log::record($nb . 'Calling markReadFeed(0) is deprecated!', Minz_Log::DEBUG); + } + $this->bd->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` e ' - . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'INNER JOIN `' . $this->prefix . 'category` c ON c.id = f.category ' - . 'SET e.is_read = 1 ' - . 'WHERE c.name = ? AND e.id <= ?'; - $values = array($name, $idMax); + $sql = 'UPDATE `' . $this->prefix . 'entry` ' + . 'SET is_read=1 ' + . 'WHERE id_feed=? AND is_read=0 AND id <= ?'; + $values = array($id, $idMax); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack(); + return false; + } + $affected = $stm->rowCount(); + + if ($affected > 0) { + $sql = 'UPDATE `' . $this->prefix . 'feed` ' + . 'SET cache_nbUnreads=cache_nbUnreads-' . $affected + . ' WHERE id=?'; + $values = array($id); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadCatName: ' . $info[2], Minz_Log::ERROR); + Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); $this->bd->rollBack(); return false; } - $affected = $stm->rowCount(); - - if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' - . 'LEFT OUTER JOIN (' - . 'SELECT e.id_feed, ' - . 'COUNT(*) AS nbUnreads ' - . 'FROM `' . $this->prefix . 'entry` e ' - . 'WHERE e.is_read = 0 ' - . 'GROUP BY e.id_feed' - . ') x ON x.id_feed=f.id ' - . 'INNER JOIN `' . $this->prefix . 'category` c ON c.id = f.category ' - . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0) ' - . 'WHERE c.name = ?'; - $values = array($name); - $stm = $this->bd->prepare($sql); - if (!($stm && $stm->execute($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadCatName: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack(); - return false; - } - } - - $this->bd->commit(); - return $affected; } - } - public function markReadFeed ($id, $idMax = 0) { - if ($idMax == 0) { - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'SET e.is_read = 1, f.cache_nbUnreads=0 ' - . 'WHERE f.id=? AND e.is_read = 0'; - $values = array ($id); - $stm = $this->bd->prepare ($sql); - if ($stm && $stm->execute ($values)) { - return $stm->rowCount(); - } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); - return false; - } - } else { - $this->bd->beginTransaction (); - - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'SET e.is_read = 1 ' - . 'WHERE f.id=? AND e.is_read = 0 AND e.id <= ?'; - $values = array ($id, $idMax); - $stm = $this->bd->prepare ($sql); - if (!($stm && $stm->execute ($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - $affected = $stm->rowCount(); - - if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' - . 'SET f.cache_nbUnreads=f.cache_nbUnreads-' . $affected - . ' WHERE f.id=?'; - $values = array ($id); - $stm = $this->bd->prepare ($sql); - if (!($stm && $stm->execute ($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markReadFeed: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - } - - $this->bd->commit (); - return $affected; - } + $this->bd->commit(); + return $affected; } - public function searchByGuid ($feed_id, $id) { + public function searchByGuid($feed_id, $id) { // un guid est unique pour un flux donné $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', link, date, is_read, is_favorite, id_feed, tags ' . 'FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ( + $values = array( $feed_id, $id ); - $stm->execute ($values); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - $entries = self::daoToEntry ($res); - return isset ($entries[0]) ? $entries[0] : null; + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $entries = self::daoToEntry($res); + return isset($entries[0]) ? $entries[0] : null; } - public function searchById ($id) { + public function searchById($id) { $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', link, date, is_read, is_favorite, id_feed, tags ' . 'FROM `' . $this->prefix . 'entry` WHERE id=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ($id); + $values = array($id); - $stm->execute ($values); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - $entries = self::daoToEntry ($res); - return isset ($entries[0]) ? $entries[0] : null; + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $entries = self::daoToEntry($res); + return isset($entries[0]) ? $entries[0] : null; + } + + protected function sqlConcat($s1, $s2) { + return 'CONCAT(' . $s1 . ',' . $s2 . ')'; //MySQL } private function sqlListWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $showOlderUnreadsorFavorites = false, $keepHistoryDefault = 0) { @@ -432,39 +318,39 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $joinFeed = true; break; case 's': //Deprecated: use $state instead - $where .= 'e1.is_favorite = 1 '; + $where .= 'e1.is_favorite=1 '; break; case 'c': - $where .= 'f.category = ? '; + $where .= 'f.category=? '; $values[] = intval($id); $joinFeed = true; break; case 'f': - $where .= 'e1.id_feed = ? '; + $where .= 'e1.id_feed=? '; $values[] = intval($id); break; case 'A': $where .= '1 '; break; default: - throw new FreshRSS_EntriesGetter_Exception ('Bad type in Entry->listByType: [' . $type . ']!'); + throw new FreshRSS_EntriesGetter_Exception('Bad type in Entry->listByType: [' . $type . ']!'); } if ($state & FreshRSS_Entry::STATE_NOT_READ) { if (!($state & FreshRSS_Entry::STATE_READ)) { - $where .= 'AND e1.is_read = 0 '; + $where .= 'AND e1.is_read=0 '; } } elseif ($state & FreshRSS_Entry::STATE_READ) { - $where .= 'AND e1.is_read = 1 '; + $where .= 'AND e1.is_read=1 '; } if ($state & FreshRSS_Entry::STATE_FAVORITE) { if (!($state & FreshRSS_Entry::STATE_NOT_FAVORITE)) { - $where .= 'AND e1.is_favorite = 1 '; + $where .= 'AND e1.is_favorite=1 '; } } elseif ($state & FreshRSS_Entry::STATE_NOT_FAVORITE) { - $where .= 'AND e1.is_favorite = 0 '; + $where .= 'AND e1.is_favorite=0 '; } switch ($order) { @@ -472,7 +358,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { case 'ASC': break; default: - throw new FreshRSS_EntriesGetter_Exception ('Bad order in Entry->listByType: [' . $order . ']!'); + throw new FreshRSS_EntriesGetter_Exception('Bad order in Entry->listByType: [' . $order . ']!'); } if ($firstId !== '') { $where .= 'AND e1.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' '; @@ -480,7 +366,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { if (($date_min > 0) && ($type !== 's')) { $where .= 'AND (e1.id >= ' . $date_min . '000000'; if ($showOlderUnreadsorFavorites) { //Lax date constraint - $where .= ' OR e1.is_read = 0 OR e1.is_favorite = 1 OR (f.keep_history <> 0'; + $where .= ' OR e1.is_read=0 OR e1.is_favorite=1 OR (f.keep_history <> 0'; if (intval($keepHistoryDefault) === 0) { $where .= ' AND f.keep_history <> -2'; //default } @@ -533,7 +419,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $search .= 'AND e1.tags LIKE ? '; $values[] = '%' . $word .'%'; } else { - $search .= 'AND CONCAT(e1.title, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin)' : 'content') . ') LIKE ? '; + $search .= 'AND ' . $this->sqlconcat('e1.title', $this->isCompressed() ? 'UNCOMPRESS(content_bin)' : 'content') . ' LIKE ? '; $values[] = '%' . $word .'%'; } } @@ -542,7 +428,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { return array($values, 'SELECT e1.id FROM `' . $this->prefix . 'entry` e1 ' - . ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e1.id_feed = f.id ' : '') + . ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e1.id_feed=f.id ' : '') . 'WHERE ' . $where . $search . 'ORDER BY e1.id ' . $order @@ -558,13 +444,13 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { . 'FROM `' . $this->prefix . 'entry` e ' . 'INNER JOIN (' . $sql - . ') e2 ON e2.id = e.id ' + . ') e2 ON e2.id=e.id ' . 'ORDER BY e.id ' . $order; - $stm = $this->bd->prepare ($sql); - $stm->execute ($values); + $stm = $this->bd->prepare($sql); + $stm->execute($values); - return self::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC)); + return self::daoToEntry($stm->fetchAll(PDO::FETCH_ASSOC)); } public function listIdsWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $showOlderUnreadsorFavorites = false, $keepHistoryDefault = 0) { //For API @@ -578,69 +464,85 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { public function listLastGuidsByFeed($id, $n) { $sql = 'SELECT guid FROM `' . $this->prefix . 'entry` WHERE id_feed=? ORDER BY id DESC LIMIT ' . intval($n); - $stm = $this->bd->prepare ($sql); - $values = array ($id); - $stm->execute ($values); - return $stm->fetchAll (PDO::FETCH_COLUMN, 0); + $stm = $this->bd->prepare($sql); + $values = array($id); + $stm->execute($values); + return $stm->fetchAll(PDO::FETCH_COLUMN, 0); } - public function countUnreadRead () { - $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE priority > 0' - . ' UNION SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE priority > 0 AND is_read = 0'; - $stm = $this->bd->prepare ($sql); - $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + public function countUnreadRead() { + $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE priority > 0' + . ' UNION SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE priority > 0 AND is_read=0'; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); $all = empty($res[0]) ? 0 : $res[0]; $unread = empty($res[1]) ? 0 : $res[1]; return array('all' => $all, 'unread' => $unread, 'read' => $all - $unread); } - public function count ($minPriority = null) { - $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id'; + public function count($minPriority = null) { + $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id'; if ($minPriority !== null) { $sql = ' WHERE priority > ' . intval($minPriority); } - $stm = $this->bd->prepare ($sql); - $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; } - public function countNotRead ($minPriority = null) { - $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE is_read = 0'; + public function countNotRead($minPriority = null) { + $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE is_read=0'; if ($minPriority !== null) { $sql = ' AND priority > ' . intval($minPriority); } - $stm = $this->bd->prepare ($sql); - $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; } - public function countUnreadReadFavorites () { - $sql = 'SELECT COUNT(id) FROM `' . $this->prefix . 'entry` WHERE is_favorite=1' - . ' UNION SELECT COUNT(id) FROM `' . $this->prefix . 'entry` WHERE is_favorite=1 AND is_read = 0'; - $stm = $this->bd->prepare ($sql); - $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + public function countUnreadReadFavorites() { + $sql = 'SELECT c FROM (' + . 'SELECT COUNT(id) AS c, 1 as o FROM `' . $this->prefix . 'entry` WHERE is_favorite=1 ' + . 'UNION SELECT COUNT(id) AS c, 2 AS o FROM `' . $this->prefix . 'entry` WHERE is_favorite=1 AND is_read=0' + . ') u ORDER BY o'; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); $all = empty($res[0]) ? 0 : $res[0]; $unread = empty($res[1]) ? 0 : $res[1]; return array('all' => $all, 'unread' => $unread, 'read' => $all - $unread); } public function optimizeTable() { - $sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`'; - $stm = $this->bd->prepare ($sql); - $stm->execute (); + $sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`'; //MySQL + $stm = $this->bd->prepare($sql); + $stm->execute(); + } + + public function size($all = false) { + $db = Minz_Configuration::dataBase(); + $sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema=?'; //MySQL + $values = array($db['base']); + if (!$all) { + $sql .= ' AND table_name LIKE ?'; + $values[] = $this->prefix . '%'; + } + $stm = $this->bd->prepare($sql); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); + return $res[0]; } - public static function daoToEntry ($listDAO) { - $list = array (); + public static function daoToEntry($listDAO) { + $list = array(); - if (!is_array ($listDAO)) { - $listDAO = array ($listDAO); + if (!is_array($listDAO)) { + $listDAO = array($listDAO); } foreach ($listDAO as $key => $dao) { - $entry = new FreshRSS_Entry ( + $entry = new FreshRSS_Entry( $dao['id_feed'], $dao['guid'], $dao['title'], @@ -652,13 +554,13 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo { $dao['is_favorite'], $dao['tags'] ); - if (isset ($dao['id'])) { - $entry->_id ($dao['id']); + if (isset($dao['id'])) { + $entry->_id($dao['id']); } $list[] = $entry; } - unset ($listDAO); + unset($listDAO); return $list; } diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index 0a837b3aa..4680c3eaa 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -2,9 +2,38 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { + protected function sqlConcat($s1, $s2) { + return $s1 . '||' . $s2; + } + + protected function updateCacheUnreads($catId = false, $feedId = false) { + $sql = 'UPDATE `' . $this->prefix . 'feed` ' + . 'SET cache_nbUnreads=(' + . 'SELECT COUNT(*) AS nbUnreads FROM `' . $this->prefix . 'entry` e ' + . 'WHERE e.id_feed=feed.id AND e.is_read=0) ' + . 'WHERE 1'; + $values = array(); + if ($feedId !== false) { + $sql .= ' AND id=?'; + $values[] = $feedId; + } + if ($catId !== false) { + $sql .= ' AND category=?'; + $values[] = $catId; + } + $stm = $this->bd->prepare($sql); + if ($stm && $stm->execute($values)) { + return true; + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error updateCacheUnreads: ' . $info[2], Minz_Log::ERROR); + return false; + } + } + public function markRead($ids, $is_read = true) { - if (is_array($ids)) { //Many IDs at once - if (true) { //Not supported yet in SQLite, so always call IDs one by one + if (is_array($ids)) { //Many IDs at once (used by API) + if (true) { //Speed heuristics //TODO: Not implemented yet for SQLite (so always call IDs one by one) $affected = 0; foreach ($ids as $id) { $affected += $this->markRead($id, $is_read); @@ -13,13 +42,13 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { } } else { $this->bd->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` SET is_read=? WHERE id=? AND is_read<>?'; - $values = array($is_read ? 1 : 0, $ids, $is_read ? 1 : 0); + $sql = 'UPDATE `' . $this->prefix . 'entry` SET is_read=? WHERE id=? AND is_read=?'; + $values = array($is_read ? 1 : 0, $ids, $is_read ? 0 : 1); $stm = $this->bd->prepare($sql); - if (!($stm && $stm->execute ($values))) { + if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::record('SQL error markRead 1: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); + $this->bd->rollBack(); return false; } $affected = $stm->rowCount(); @@ -28,10 +57,10 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { . 'WHERE id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; $values = array($ids); $stm = $this->bd->prepare($sql); - if (!($stm && $stm->execute ($values))) { + if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::record('SQL error markRead 2: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); + $this->bd->rollBack(); return false; } } @@ -39,4 +68,70 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { return $affected; } } + + public function markReadEntries($idMax = 0, $onlyFavorites = false, $priorityMin = 0) { + if ($idMax == 0) { + $idMax = time() . '000000'; + Minz_Log::record($nb . 'Calling markReadEntries(0) is deprecated!', Minz_Log::DEBUG); + } + + $this->bd->beginTransaction(); + $sql = 'UPDATE `' . $this->prefix . 'entry` SET is_read=1 WHERE is_read=0 AND id <= ?'; + if ($onlyFavorites) { + $sql .= ' AND is_favorite=1'; + } elseif ($priorityMin >= 0) { + $sql .= ' AND id_feed IN (SELECT f.id FROM `' . $this->prefix . 'feed` f WHERE f.priority > ' . intval($priorityMin) . ')'; + } + $values = array($idMax); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadEntries 1: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack(); + return false; + } + $affected = $stm->rowCount(); + if (($affected > 0) && (!$this->updateCacheUnreads(false, false))) { + $this->bd->rollBack(); + return false; + } + $this->bd->commit(); + return $affected; + } + + public function markReadCat($id, $idMax = 0) { + if ($idMax == 0) { + $idMax = time() . '000000'; + Minz_Log::record($nb . 'Calling markReadCat(0) is deprecated!', Minz_Log::DEBUG); + } + $this->bd->beginTransaction(); + + $sql = 'UPDATE `' . $this->prefix . 'entry` ' + . 'SET is_read=1 ' + . 'WHERE is_read=0 AND id <= ? AND ' + . 'id_feed IN (SELECT f.id FROM `' . $this->prefix . 'feed` f WHERE f.category=?)'; + $values = array($idMax, $id); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markReadCat: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack(); + return false; + } + $affected = $stm->rowCount(); + if (($affected > 0) && (!$this->updateCacheUnreads($id, false))) { + $this->bd->rollBack(); + return false; + } + $this->bd->commit(); + return $affected; + } + + public function optimizeTable() { + //TODO: Search for an equivalent in SQLite + } + + public function size($all = false) { + return @filesize(DATA_PATH . '/' . Minz_Session::param('currentUser', '_') . '.sqlite'); + } } diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 9534d61bd..5281b371d 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -1,21 +1,21 @@ prefix . 'feed` (url, category, name, website, description, lastUpdate, priority, httpAuth, error, keep_history) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2)'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ( + $values = array( substr($valuesTmp['url'], 0, 511), $valuesTmp['category'], substr($valuesTmp['name'], 0, 255), substr($valuesTmp['website'], 0, 255), substr($valuesTmp['description'], 0, 1023), $valuesTmp['lastUpdate'], - base64_encode ($valuesTmp['httpAuth']), + base64_encode($valuesTmp['httpAuth']), ); - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute($values)) { return $this->bd->lastInsertId(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -54,26 +54,26 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return $feed_search->id(); } - public function updateFeed ($id, $valuesTmp) { + public function updateFeed($id, $valuesTmp) { $set = ''; foreach ($valuesTmp as $key => $v) { $set .= $key . '=?, '; if ($key == 'httpAuth') { - $valuesTmp[$key] = base64_encode ($v); + $valuesTmp[$key] = base64_encode($v); } } - $set = substr ($set, 0, -2); + $set = substr($set, 0, -2); $sql = 'UPDATE `' . $this->prefix . 'feed` SET ' . $set . ' WHERE id=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); foreach ($valuesTmp as $v) { $values[] = $v; } $values[] = $id; - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -82,11 +82,11 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } } - public function updateLastUpdate ($id, $inError = 0, $updateCache = true) { + public function updateLastUpdate($id, $inError = 0, $updateCache = true) { if ($updateCache) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE - . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=feed.id),' - . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=feed.id AND e2.is_read=0),' + . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' + . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),' . 'lastUpdate=?, error=? ' . 'WHERE id=?'; } else { @@ -95,15 +95,15 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { . 'WHERE id=?'; } - $values = array ( + $values = array( time(), $inError, $id, ); - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -112,22 +112,22 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } } - public function changeCategory ($idOldCat, $idNewCat) { - $catDAO = new FreshRSS_CategoryDAO (); - $newCat = $catDAO->searchById ($idNewCat); + public function changeCategory($idOldCat, $idNewCat) { + $catDAO = new FreshRSS_CategoryDAO(); + $newCat = $catDAO->searchById($idNewCat); if (!$newCat) { - $newCat = $catDAO->getDefault (); + $newCat = $catDAO->getDefault(); } $sql = 'UPDATE `' . $this->prefix . 'feed` SET category=? WHERE category=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ( - $newCat->id (), + $values = array( + $newCat->id(), $idOldCat ); - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -136,23 +136,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } } - public function deleteFeed ($id) { - /*//For MYISAM (MySQL 5.5-) without FOREIGN KEY - $sql = 'DELETE FROM `' . $this->prefix . 'entry` WHERE id_feed=?'; - $stm = $this->bd->prepare ($sql); - $values = array ($id); - if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); - return false; - }*/ - + public function deleteFeed($id) { $sql = 'DELETE FROM `' . $this->prefix . 'feed` WHERE id=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ($id); + $values = array($id); - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -160,25 +150,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return false; } } - public function deleteFeedByCategory ($id) { - /*//For MYISAM (MySQL 5.5-) without FOREIGN KEY - $sql = 'DELETE FROM `' . $this->prefix . 'entry` e ' - . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . 'WHERE f.category=?'; - $stm = $this->bd->prepare ($sql); - $values = array ($id); - if (!($stm && $stm->execute ($values))) { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); - return false; - }*/ - + public function deleteFeedByCategory($id) { $sql = 'DELETE FROM `' . $this->prefix . 'feed` WHERE category=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ($id); + $values = array($id); - if ($stm && $stm->execute ($values)) { + if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -187,52 +165,52 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } } - public function searchById ($id) { + public function searchById($id) { $sql = 'SELECT * FROM `' . $this->prefix . 'feed` WHERE id=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ($id); + $values = array($id); - $stm->execute ($values); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - $feed = self::daoToFeed ($res); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $feed = self::daoToFeed($res); - if (isset ($feed[$id])) { + if (isset($feed[$id])) { return $feed[$id]; } else { return null; } } - public function searchByUrl ($url) { + public function searchByUrl($url) { $sql = 'SELECT * FROM `' . $this->prefix . 'feed` WHERE url=?'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ($url); + $values = array($url); - $stm->execute ($values); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - $feed = current (self::daoToFeed ($res)); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $feed = current(self::daoToFeed($res)); - if (isset ($feed)) { + if (isset($feed)) { return $feed; } else { return null; } } - public function listFeeds () { + public function listFeeds() { $sql = 'SELECT * FROM `' . $this->prefix . 'feed` ORDER BY name'; - $stm = $this->bd->prepare ($sql); - $stm->execute (); + $stm = $this->bd->prepare($sql); + $stm->execute(); - return self::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC)); + return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); } 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 (); + $stm = $this->bd->prepare($sql); + $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $feedCategoryNames = array(); foreach ($res as $line) { @@ -244,49 +222,49 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return $feedCategoryNames; } - public function listFeedsOrderUpdate ($cacheDuration = 1500) { + public function listFeedsOrderUpdate($cacheDuration = 1500) { $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history ' . 'FROM `' . $this->prefix . 'feed` ' . 'WHERE lastUpdate < ' . (time() - intval($cacheDuration)) . ' ORDER BY lastUpdate'; - $stm = $this->bd->prepare ($sql); - $stm->execute (); + $stm = $this->bd->prepare($sql); + $stm->execute(); - return self::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC)); + return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); } - public function listByCategory ($cat) { + public function listByCategory($cat) { $sql = 'SELECT * FROM `' . $this->prefix . 'feed` WHERE category=? ORDER BY name'; - $stm = $this->bd->prepare ($sql); + $stm = $this->bd->prepare($sql); - $values = array ($cat); + $values = array($cat); - $stm->execute ($values); + $stm->execute($values); - return self::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC)); + return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); } - public function countEntries ($id) { + public function countEntries($id) { $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` WHERE id_feed=?'; - $stm = $this->bd->prepare ($sql); - $values = array ($id); - $stm->execute ($values); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); + $stm = $this->bd->prepare($sql); + $values = array($id); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); return $res[0]['count']; } - public function countNotRead ($id) { + public function countNotRead($id) { $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND is_read=0'; - $stm = $this->bd->prepare ($sql); - $values = array ($id); - $stm->execute ($values); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); + $stm = $this->bd->prepare($sql); + $values = array($id); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); return $res[0]['count']; } - public function updateCachedValues () { //For one single feed, call updateLastUpdate($id) + public function updateCachedValues() { //For one single feed, call updateLastUpdate($id) $sql = 'UPDATE `' . $this->prefix . 'feed` f ' . 'INNER JOIN (' . 'SELECT e.id_feed, ' @@ -296,7 +274,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { . '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); + $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { return $stm->rowCount(); @@ -307,39 +285,39 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } } - public function truncate ($id) { - $sql = 'DELETE e.* FROM `' . $this->prefix . 'entry` e WHERE e.id_feed=?'; + public function truncate($id) { + $sql = 'DELETE FROM `' . $this->prefix . 'entry` WHERE id_feed=?'; $stm = $this->bd->prepare($sql); $values = array($id); - $this->bd->beginTransaction (); - if (!($stm && $stm->execute ($values))) { + $this->bd->beginTransaction(); + if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::record('SQL error truncate: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); + $this->bd->rollBack(); return false; } $affected = $stm->rowCount(); $sql = 'UPDATE `' . $this->prefix . 'feed` ' . 'SET cache_nbEntries=0, cache_nbUnreads=0 WHERE id=?'; - $values = array ($id); - $stm = $this->bd->prepare ($sql); - if (!($stm && $stm->execute ($values))) { + $values = array($id); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::record('SQL error truncate: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); + $this->bd->rollBack(); return false; } - $this->bd->commit (); + $this->bd->commit(); return $affected; } - public function cleanOldEntries ($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) just after - $sql = 'DELETE e.* FROM `' . $this->prefix . 'entry` e ' - . 'WHERE e.id_feed = :id_feed AND e.id <= :id_max AND e.is_favorite = 0 AND e.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 because of: MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - $stm = $this->bd->prepare ($sql); + public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) just after + $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' + . 'WHERE id_feed = :id_feed AND id <= :id_max AND is_favorite=0 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 yet support 'LIMIT & IN/ALL/ANY/SOME subquery' + $stm = $this->bd->prepare($sql); $id_max = intval($date_min) . '000000'; @@ -347,7 +325,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { $stm->bindParam(':id_max', $id_max, PDO::PARAM_INT); $stm->bindParam(':keep', $keep, PDO::PARAM_INT); - if ($stm && $stm->execute ()) { + if ($stm && $stm->execute()) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -356,18 +334,18 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } } - public static function daoToFeed ($listDAO, $catID = null) { - $list = array (); + public static function daoToFeed($listDAO, $catID = null) { + $list = array(); - if (!is_array ($listDAO)) { - $listDAO = array ($listDAO); + if (!is_array($listDAO)) { + $listDAO = array($listDAO); } foreach ($listDAO as $key => $dao) { - if (!isset ($dao['name'])) { + if (!isset($dao['name'])) { continue; } - if (isset ($dao['id'])) { + if (isset($dao['id'])) { $key = $dao['id']; } if ($catID === null) { @@ -384,13 +362,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { $myFeed->_lastUpdate(isset($dao['lastUpdate']) ? $dao['lastUpdate'] : 0); $myFeed->_priority(isset($dao['priority']) ? $dao['priority'] : 10); $myFeed->_pathEntries(isset($dao['pathEntries']) ? $dao['pathEntries'] : ''); - $myFeed->_httpAuth(isset($dao['httpAuth']) ? base64_decode ($dao['httpAuth']) : ''); + $myFeed->_httpAuth(isset($dao['httpAuth']) ? base64_decode($dao['httpAuth']) : ''); $myFeed->_error(isset($dao['error']) ? $dao['error'] : 0); $myFeed->_keepHistory(isset($dao['keep_history']) ? $dao['keep_history'] : -2); $myFeed->_nbNotRead(isset($dao['cache_nbUnreads']) ? $dao['cache_nbUnreads'] : 0); $myFeed->_nbEntries(isset($dao['cache_nbEntries']) ? $dao['cache_nbEntries'] : 0); - if (isset ($dao['id'])) { - $myFeed->_id ($dao['id']); + if (isset($dao['id'])) { + $myFeed->_id($dao['id']); } $list[$key] = $myFeed; } diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php index f3c92149e..bb75c860b 100644 --- a/app/Models/FeedDAOSQLite.php +++ b/app/Models/FeedDAOSQLite.php @@ -2,4 +2,18 @@ class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAO { + public function updateCachedValues() { //For one single feed, call updateLastUpdate($id) + $sql = 'UPDATE `' . $this->prefix . 'feed` ' + . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=feed.id),' + . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=feed.id AND e2.is_read=0)'; + $stm = $this->bd->prepare($sql); + if ($stm && $stm->execute()) { + return $stm->rowCount(); + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error updateCachedValues: ' . $info[2], Minz_Log::ERROR); + return false; + } + } + } diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php index cbbc0bebc..1f56f09c2 100644 --- a/lib/Minz/ModelPdo.php +++ b/lib/Minz/ModelPdo.php @@ -92,20 +92,6 @@ class Minz_ModelPdo { $this->bd->rollBack(); } - public function size($all = false) { - $db = Minz_Configuration::dataBase(); - $sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema = ?'; - $values = array($db['base']); - if (!$all) { - $sql .= ' AND table_name LIKE ?'; - $values[] = $this->prefix . '%'; - } - $stm = $this->bd->prepare($sql); - $stm->execute($values); - $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); - return $res[0]; - } - public static function clean() { self::$sharedBd = null; self::$sharedPrefix = ''; diff --git a/p/api/greader.php b/p/api/greader.php index 843cd93c4..7a961225f 100644 --- a/p/api/greader.php +++ b/p/api/greader.php @@ -364,7 +364,7 @@ function streamContents($path, $include_target, $start_time, $count, $order, $ex $count++; //Shift by one element } - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); $entries = $entryDAO->listWhere($type, $include_target, $state, $order === 'o' ? 'ASC' : 'DESC', $count, $continuation, '', $start_time); $items = array(); @@ -458,7 +458,7 @@ function streamContentsItemsIds($streamId, $start_time, $count, $order, $exclude break; } - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); $ids = $entryDAO->listIdsWhere($type, $id, $state, $order === 'o' ? 'ASC' : 'DESC', $count, '', '', $start_time); $itemRefs = array(); @@ -481,7 +481,7 @@ function editTag($e_ids, $a, $r) { $e_ids[$i] = hex2dec(basename($e_id)); //Strip prefix 'tag:google.com,2005:reader/item/' } - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); switch ($a) { case 'user/-/state/com.google/read': @@ -512,13 +512,15 @@ function editTag($e_ids, $a, $r) { function markAllAsRead($streamId, $olderThanId) { logMe("markAllAsRead($streamId, $olderThanId)\n"); - $entryDAO = new FreshRSS_EntryDAO(); + $entryDAO = FreshRSS_Factory::createEntryDao(); if (strpos($streamId, 'feed/') === 0) { $f_id = basename($streamId); $entryDAO->markReadFeed($f_id, $olderThanId); } elseif (strpos($streamId, 'user/-/label/') === 0) { $c_name = basename($streamId); - $entryDAO->markReadCatName($c_name, $olderThanId); + $categoryDAO = new FreshRSS_CategoryDAO(); + $cat = $categoryDAO->searchByName($c_name); + $entryDAO->markReadCat($cat === null ? -1 : $cat->id(), $olderThanId); } elseif ($streamId === 'user/-/state/com.google/reading-list') { $entryDAO->markReadEntries($olderThanId, false, -1); } -- cgit v1.2.3 From bc8eb560afd50290745ea6a500c0f930df2559eb Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 6 Jul 2014 00:09:31 +0200 Subject: Add TTL to control feed freshness https://github.com/marienfressinaud/FreshRSS/issues/250 --- app/Controllers/configureController.php | 3 + app/Controllers/entryController.php | 2 +- app/Controllers/feedController.php | 2 +- app/Controllers/javascriptController.php | 2 +- app/Models/Configuration.php | 5 ++ app/Models/Feed.php | 150 ++++++++++++++++--------------- app/Models/FeedDAO.php | 19 ++-- app/i18n/en.php | 3 +- app/i18n/fr.php | 1 + app/views/configure/archiving.phtml | 21 +++++ app/views/configure/feed.phtml | 21 +++++ lib/lib_rss.php | 2 +- 12 files changed, 150 insertions(+), 81 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php index a608df162..5fd09a263 100755 --- a/app/Controllers/configureController.php +++ b/app/Controllers/configureController.php @@ -109,6 +109,7 @@ class FreshRSS_configure_Controller extends Minz_ActionController { 'priority' => intval(Minz_Request::param ('priority', 0)), 'httpAuth' => $httpAuth, 'keep_history' => intval(Minz_Request::param ('keep_history', -2)), + 'ttl' => intval(Minz_Request::param('ttl', -2)), ); if ($feedDAO->updateFeed ($id, $values)) { @@ -274,9 +275,11 @@ class FreshRSS_configure_Controller extends Minz_ActionController { if (Minz_Request::isPost()) { $old = Minz_Request::param('old_entries', 3); $keepHistoryDefault = Minz_Request::param('keep_history_default', 0); + $ttlDefault = Minz_Request::param('ttl_default', -2); $this->view->conf->_old_entries($old); $this->view->conf->_keep_history_default($keepHistoryDefault); + $this->view->conf->_ttl_default($ttlDefault); $this->view->conf->save(); invalidateHttpCache(); diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php index 2d7fa718a..ac43587ea 100755 --- a/app/Controllers/entryController.php +++ b/app/Controllers/entryController.php @@ -125,7 +125,7 @@ class FreshRSS_entry_Controller extends Minz_ActionController { $date_min = time() - (3600 * 24 * 30 * $nb_month_old); $feedDAO = FreshRSS_Factory::createFeedDao(); - $feeds = $feedDAO->listFeedsOrderUpdate(); + $feeds = $feedDAO->listFeeds(); $nbTotal = 0; invalidateHttpCache(); diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index d30b60877..36425ca9b 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -233,7 +233,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $feeds = array ($feed); } } else { - $feeds = $feedDAO->listFeedsOrderUpdate (); + $feeds = $feedDAO->listFeedsOrderUpdate($this->view->conf->ttl_default); } // on calcule la date des articles les plus anciens qu'on accepte diff --git a/app/Controllers/javascriptController.php b/app/Controllers/javascriptController.php index 737908c91..67148350f 100755 --- a/app/Controllers/javascriptController.php +++ b/app/Controllers/javascriptController.php @@ -8,7 +8,7 @@ class FreshRSS_javascript_Controller extends Minz_ActionController { public function actualizeAction () { header('Content-Type: text/javascript; charset=UTF-8'); $feedDAO = FreshRSS_Factory::createFeedDao(); - $this->view->feeds = $feedDAO->listFeedsOrderUpdate(); + $this->view->feeds = $feedDAO->listFeedsOrderUpdate($this->view->conf->ttl_default); } public function nbUnreadsPerFeedAction() { diff --git a/app/Models/Configuration.php b/app/Models/Configuration.php index ffd20deca..f2084b833 100644 --- a/app/Models/Configuration.php +++ b/app/Models/Configuration.php @@ -7,6 +7,7 @@ class FreshRSS_Configuration { 'language' => 'en', 'old_entries' => 3, 'keep_history_default' => 0, + 'ttl_default' => 3600, 'mail_login' => '', 'token' => '', 'passwordHash' => '', //CRYPT_BLOWFISH @@ -159,6 +160,10 @@ class FreshRSS_Configuration { $value = intval($value); $this->data['keep_history_default'] = $value >= -1 ? $value : 0; } + public function _ttl_default($value) { + $value = intval($value); + $this->data['ttl_default'] = $value >= -1 ? $value : 3600; + } public function _shortcuts ($values) { foreach ($values as $key => $value) { if (isset($this->data['shortcuts'][$key])) { diff --git a/app/Models/Feed.php b/app/Models/Feed.php index c9f1230ad..576f37760 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -16,18 +16,19 @@ class FreshRSS_Feed extends Minz_Model { private $httpAuth = ''; private $error = false; private $keep_history = -2; + private $ttl = -2; private $hash = null; private $lockPath = ''; - public function __construct ($url, $validate=true) { + public function __construct($url, $validate=true) { if ($validate) { - $this->_url ($url); + $this->_url($url); } else { $this->url = $url; } } - public function id () { + public function id() { return $this->id; } @@ -38,72 +39,75 @@ class FreshRSS_Feed extends Minz_Model { return $this->hash; } - public function url () { + public function url() { return $this->url; } - public function category () { + public function category() { return $this->category; } - public function entries () { + public function entries() { return $this->entries === null ? array() : $this->entries; } - public function name () { + public function name() { return $this->name; } - public function website () { + public function website() { return $this->website; } - public function description () { + public function description() { return $this->description; } - public function lastUpdate () { + public function lastUpdate() { return $this->lastUpdate; } - public function priority () { + public function priority() { return $this->priority; } - public function pathEntries () { + public function pathEntries() { return $this->pathEntries; } - public function httpAuth ($raw = true) { + public function httpAuth($raw = true) { if ($raw) { return $this->httpAuth; } else { - $pos_colon = strpos ($this->httpAuth, ':'); - $user = substr ($this->httpAuth, 0, $pos_colon); - $pass = substr ($this->httpAuth, $pos_colon + 1); + $pos_colon = strpos($this->httpAuth, ':'); + $user = substr($this->httpAuth, 0, $pos_colon); + $pass = substr($this->httpAuth, $pos_colon + 1); - return array ( + return array( 'username' => $user, 'password' => $pass ); } } - public function inError () { + public function inError() { return $this->error; } - public function keepHistory () { + public function keepHistory() { return $this->keep_history; } - public function nbEntries () { + public function ttl() { + return $this->ttl; + } + public function nbEntries() { if ($this->nbEntries < 0) { $feedDAO = FreshRSS_Factory::createFeedDao(); - $this->nbEntries = $feedDAO->countEntries ($this->id ()); + $this->nbEntries = $feedDAO->countEntries($this->id()); } return $this->nbEntries; } - public function nbNotRead () { + public function nbNotRead() { if ($this->nbNotRead < 0) { $feedDAO = FreshRSS_Factory::createFeedDao(); - $this->nbNotRead = $feedDAO->countNotRead ($this->id ()); + $this->nbNotRead = $feedDAO->countNotRead($this->id()); } return $this->nbNotRead; } public function faviconPrepare() { $file = DATA_PATH . '/favicons/' . $this->hash() . '.txt'; - if (!file_exists ($file)) { + if (!file_exists($file)) { $t = $this->website; if ($t == '') { $t = $this->url; @@ -116,92 +120,98 @@ class FreshRSS_Feed extends Minz_Model { @unlink($path . '.ico'); @unlink($path . '.txt'); } - public function favicon () { - return Minz_Url::display ('/f.php?' . $this->hash()); + public function favicon() { + return Minz_Url::display('/f.php?' . $this->hash()); } - public function _id ($value) { + public function _id($value) { $this->id = $value; } - public function _url ($value, $validate=true) { + public function _url($value, $validate=true) { $this->hash = null; if ($validate) { $value = checkUrl($value); } - if (empty ($value)) { - throw new FreshRSS_BadUrl_Exception ($value); + if (empty($value)) { + throw new FreshRSS_BadUrl_Exception($value); } $this->url = $value; } - public function _category ($value) { + public function _category($value) { $value = intval($value); $this->category = $value >= 0 ? $value : 0; } - public function _name ($value) { + public function _name($value) { $this->name = $value === null ? '' : $value; } - public function _website ($value, $validate=true) { + public function _website($value, $validate=true) { if ($validate) { $value = checkUrl($value); } - if (empty ($value)) { + if (empty($value)) { $value = ''; } $this->website = $value; } - public function _description ($value) { + public function _description($value) { $this->description = $value === null ? '' : $value; } - public function _lastUpdate ($value) { + public function _lastUpdate($value) { $this->lastUpdate = $value; } - public function _priority ($value) { + public function _priority($value) { $value = intval($value); $this->priority = $value >= 0 ? $value : 10; } - public function _pathEntries ($value) { + public function _pathEntries($value) { $this->pathEntries = $value; } - public function _httpAuth ($value) { + public function _httpAuth($value) { $this->httpAuth = $value; } - public function _error ($value) { + public function _error($value) { $this->error = (bool)$value; } - public function _keepHistory ($value) { + public function _keepHistory($value) { $value = intval($value); $value = min($value, 1000000); $value = max($value, -2); $this->keep_history = $value; } - public function _nbNotRead ($value) { + public function _ttl($value) { + $value = intval($value); + $value = min($value, 100000000); + $value = max($value, -2); + $this->ttl = $value; + } + public function _nbNotRead($value) { $this->nbNotRead = intval($value); } - public function _nbEntries ($value) { + public function _nbEntries($value) { $this->nbEntries = intval($value); } - public function load ($loadDetails = false) { + public function load($loadDetails = false) { if ($this->url !== null) { if (CACHE_PATH === false) { - throw new Minz_FileNotExistException ( + throw new Minz_FileNotExistException( 'CACHE_PATH', Minz_Exception::ERROR ); } else { - $url = htmlspecialchars_decode ($this->url, ENT_QUOTES); + $url = htmlspecialchars_decode($this->url, ENT_QUOTES); if ($this->httpAuth != '') { - $url = preg_replace ('#((.+)://)(.+)#', '${1}' . $this->httpAuth . '@${3}', $url); + $url = preg_replace('#((.+)://)(.+)#', '${1}' . $this->httpAuth . '@${3}', $url); } $feed = customSimplePie(); - $feed->set_feed_url ($url); + $feed->set_feed_url($url); if (!$loadDetails) { //Only activates auto-discovery when adding a new feed $feed->set_autodiscovery_level(SIMPLEPIE_LOCATOR_NONE); } $mtime = $feed->init(); if ((!$mtime) || $feed->error()) { - throw new FreshRSS_Feed_Exception ($feed->error() . ' [' . $url . ']'); + throw new FreshRSS_Feed_Exception($feed->error() . ' [' . $url . ']'); } if ($loadDetails) { @@ -209,7 +219,7 @@ class FreshRSS_Feed extends Minz_Model { $subscribe_url = $feed->subscribe_url(false); $title = strtr(html_only_entity_decode($feed->get_title()), array('<' => '<', '>' => '>', '"' => '"')); //HTML to HTML-PRE //ENT_COMPAT except & - $this->_name ($title == '' ? $this->url : $title); + $this->_name($title == '' ? $this->url : $title); $this->_website(html_only_entity_decode($feed->get_link())); $this->_description(html_only_entity_decode($feed->get_description())); @@ -221,12 +231,12 @@ class FreshRSS_Feed extends Minz_Model { if ($subscribe_url !== null && $subscribe_url !== $this->url) { if ($this->httpAuth != '') { // on enlève les id si authentification HTTP - $subscribe_url = preg_replace ('#((.+)://)((.+)@)(.+)#', '${1}${5}', $subscribe_url); + $subscribe_url = preg_replace('#((.+)://)((.+)@)(.+)#', '${1}${5}', $subscribe_url); } - $this->_url ($subscribe_url); + $this->_url($subscribe_url); } - if (($mtime === true) || ($mtime > $this->lastUpdate)) { + if (($mtime === true) ||($mtime > $this->lastUpdate)) { syslog(LOG_DEBUG, 'FreshRSS no cache ' . $mtime . ' > ' . $this->lastUpdate . ' for ' . $subscribe_url); $this->loadEntries($feed); // et on charge les articles du flux } else { @@ -240,25 +250,25 @@ class FreshRSS_Feed extends Minz_Model { } } - private function loadEntries ($feed) { - $entries = array (); + private function loadEntries($feed) { + $entries = array(); - foreach ($feed->get_items () as $item) { - $title = html_only_entity_decode (strip_tags ($item->get_title ())); - $author = $item->get_author (); - $link = $item->get_permalink (); - $date = @strtotime ($item->get_date ()); + foreach ($feed->get_items() as $item) { + $title = html_only_entity_decode(strip_tags($item->get_title())); + $author = $item->get_author(); + $link = $item->get_permalink(); + $date = @strtotime($item->get_date()); // gestion des tags (catégorie == tag) - $tags_tmp = $item->get_categories (); - $tags = array (); + $tags_tmp = $item->get_categories(); + $tags = array(); if ($tags_tmp !== null) { foreach ($tags_tmp as $tag) { - $tags[] = html_only_entity_decode ($tag->get_label ()); + $tags[] = html_only_entity_decode($tag->get_label()); } } - $content = html_only_entity_decode ($item->get_content ()); + $content = html_only_entity_decode($item->get_content()); $elinks = array(); foreach ($item->get_enclosures() as $enclosure) { @@ -276,16 +286,16 @@ class FreshRSS_Feed extends Minz_Model { } } - $entry = new FreshRSS_Entry ( - $this->id (), - $item->get_id (), + $entry = new FreshRSS_Entry( + $this->id(), + $item->get_id(), $title === null ? '' : $title, - $author === null ? '' : html_only_entity_decode ($author->name), + $author === null ? '' : html_only_entity_decode($author->name), $content === null ? '' : $content, $link === null ? '' : $link, - $date ? $date : time () + $date ? $date : time() ); - $entry->_tags ($tags); + $entry->_tags($tags); // permet de récupérer le contenu des flux tronqués $entry->loadCompleteContent($this->pathEntries()); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 5281b371d..83f3a6231 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -2,7 +2,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { public function addFeed($valuesTmp) { - $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, lastUpdate, priority, httpAuth, error, keep_history) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2)'; + $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, lastUpdate, priority, httpAuth, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); $values = array( @@ -222,13 +222,19 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { return $feedCategoryNames; } - public function listFeedsOrderUpdate($cacheDuration = 1500) { - $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history ' + public function listFeedsOrderUpdate($defaultCacheDuration = 3600) { + $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history, ttl ' . 'FROM `' . $this->prefix . 'feed` ' - . 'WHERE lastUpdate < ' . (time() - intval($cacheDuration)) - . ' ORDER BY lastUpdate'; + . 'WHERE ttl <> -1 AND lastUpdate < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' + . 'ORDER BY lastUpdate'; $stm = $this->bd->prepare($sql); - $stm->execute(); + if (!($stm && $stm->execute())) { + $sql2 = 'ALTER TABLE `' . $this->prefix . 'feed` ADD COLUMN ttl INT NOT NULL DEFAULT -2'; //v0.7.3 + $stm = $this->bd->prepare($sql2); + $stm->execute(); + $stm = $this->bd->prepare($sql); + $stm->execute(); + } return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); } @@ -365,6 +371,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { $myFeed->_httpAuth(isset($dao['httpAuth']) ? base64_decode($dao['httpAuth']) : ''); $myFeed->_error(isset($dao['error']) ? $dao['error'] : 0); $myFeed->_keepHistory(isset($dao['keep_history']) ? $dao['keep_history'] : -2); + $myFeed->_ttl(isset($dao['ttl']) ? $dao['ttl'] : -2); $myFeed->_nbNotRead(isset($dao['cache_nbUnreads']) ? $dao['cache_nbUnreads'] : 0); $myFeed->_nbEntries(isset($dao['cache_nbEntries']) ? $dao['cache_nbEntries'] : 0); if (isset($dao['id'])) { diff --git a/app/i18n/en.php b/app/i18n/en.php index 19cf4a06d..569903782 100644 --- a/app/i18n/en.php +++ b/app/i18n/en.php @@ -41,7 +41,7 @@ return array ( 'query_state_15' => 'Display all articles', 'query_number' => 'Query n°%d', 'add_query' => 'Add a query', - 'no_query' => 'You haven’t created user queries yet.', + 'no_query' => 'You haven’t created any user query yet.', 'query_filter' => 'Filter applied:', 'no_query_filter' => 'No filter', 'about' => 'About', @@ -197,6 +197,7 @@ return array ( 'by_feed' => 'by feed', 'by_default' => 'By default', 'keep_history' => 'Minimum number of articles to keep', + 'ttl' => 'Do not automatically refresh more often than', 'categorize' => 'Store in a category', 'truncate' => 'Delete all articles', 'advanced' => 'Advanced', diff --git a/app/i18n/fr.php b/app/i18n/fr.php index 54fe55ea0..5fc60bd15 100644 --- a/app/i18n/fr.php +++ b/app/i18n/fr.php @@ -197,6 +197,7 @@ return array ( 'by_feed' => 'par flux', 'by_default' => 'Par défaut', 'keep_history' => 'Nombre minimum d’articles à conserver', + 'ttl' => 'Ne pas automatiquement rafraîchir plus souvent que', 'categorize' => 'Ranger dans une catégorie', 'truncate' => 'Supprimer tous les articles', 'advanced' => 'Avancé', diff --git a/app/views/configure/archiving.phtml b/app/views/configure/archiving.phtml index e144d0f45..04fa19b0d 100644 --- a/app/views/configure/archiving.phtml +++ b/app/views/configure/archiving.phtml @@ -24,6 +24,27 @@ ?> () +
+ +
+ () +
+
diff --git a/app/views/configure/feed.phtml b/app/views/configure/feed.phtml index 27b0990ff..a8dd9a8cb 100644 --- a/app/views/configure/feed.phtml +++ b/app/views/configure/feed.phtml @@ -103,6 +103,27 @@ ?>
+
+ +
+ +
+
diff --git a/lib/lib_rss.php b/lib/lib_rss.php index 1b1e4b021..7ca611b04 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -109,7 +109,7 @@ function customSimplePie() { $simplePie = new SimplePie(); $simplePie->set_useragent(Minz_Translate::t('freshrss') . '/' . FRESHRSS_VERSION . ' (' . PHP_OS . '; ' . FRESHRSS_WEBSITE . ') ' . SIMPLEPIE_NAME . '/' . SIMPLEPIE_VERSION); $simplePie->set_cache_location(CACHE_PATH); - $simplePie->set_cache_duration(1500); + $simplePie->set_cache_duration(800); $simplePie->strip_htmltags(array( 'base', 'blink', 'body', 'doctype', 'embed', 'font', 'form', 'frame', 'frameset', 'html', -- cgit v1.2.3 From 3d3b33ea2e3f00a3de154086341c1fc2abcadc3e Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 6 Jul 2014 10:12:37 +0200 Subject: Bug global TTL https://github.com/marienfressinaud/FreshRSS/issues/250 --- app/Models/FeedDAO.php | 3 +++ app/views/configure/archiving.phtml | 2 +- 2 files changed, 4 insertions(+), 1 deletion(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 83f3a6231..756b1f008 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -223,6 +223,9 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { } public function listFeedsOrderUpdate($defaultCacheDuration = 3600) { + if ($defaultCacheDuration < 0) { + $defaultCacheDuration = 2147483647; + } $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history, ttl ' . 'FROM `' . $this->prefix . 'feed` ' . 'WHERE ttl <> -1 AND lastUpdate < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' diff --git a/app/views/configure/archiving.phtml b/app/views/configure/archiving.phtml index 04fa19b0d..c9cc7fe02 100644 --- a/app/views/configure/archiving.phtml +++ b/app/views/configure/archiving.phtml @@ -25,7 +25,7 @@
- +
"/> - "/> - "/> - "/> + + + + +
- + @@ -35,23 +36,11 @@
- query_get[$key]) && - $this->query_get[$key]['deprecated']); - ?> - - + hasParameters()) { ?>
- + isDeprecated()) { ?>
@@ -60,20 +49,20 @@
    - -
    • + hasSearch()) { ?> +
  • getSearch()->getRawInput()); ?>
    • - -
    • + getState()) { ?> +
  • getState()); ?>
    • - -
    • + getOrder()) { ?> +
  • getOrder())); ?>
    • - -
  • query_get[$key]['type'], $this->query_get[$key]['name']); ?>
    • + getGet()) { ?> +
  • getGetType(), $query->getGetName()); ?>
diff --git a/tests/app/Models/UserQueryTest.php b/tests/app/Models/UserQueryTest.php new file mode 100644 index 000000000..2234be6e1 --- /dev/null +++ b/tests/app/Models/UserQueryTest.php @@ -0,0 +1,229 @@ + 'a'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertEquals('all', $user_query->getGetName()); + $this->assertEquals('all', $user_query->getGetType()); + } + + public function test__construct_whenFavoriteQuery_storesFavoriteParameters() { + $query = array('get' => 's'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertEquals('favorite', $user_query->getGetName()); + $this->assertEquals('favorite', $user_query->getGetType()); + } + + /** + * @expectedException Exceptions/FreshRSS_DAOException + * @expectedExceptionMessage Category DAO is not loaded in UserQuery + */ + public function test__construct_whenCategoryQueryAndNoDao_throwsException() { + $this->markTestIncomplete('There is a problem with the exception autoloading. We need to make a better autoloading process'); + $query = array('get' => 'c_1'); + new FreshRSS_UserQuery($query); + } + + public function test__construct_whenCategoryQuery_storesCategoryParameters() { + $category_name = 'some category name'; + $cat = $this->getMock('FreshRSS_Category'); + $cat->expects($this->atLeastOnce()) + ->method('name') + ->withAnyParameters() + ->willReturn($category_name); + $cat_dao = $this->getMock('FreshRSS_Searchable'); + $cat_dao->expects($this->atLeastOnce()) + ->method('searchById') + ->withAnyParameters() + ->willReturn($cat); + $query = array('get' => 'c_1'); + $user_query = new FreshRSS_UserQuery($query, null, $cat_dao); + $this->assertEquals($category_name, $user_query->getGetName()); + $this->assertEquals('category', $user_query->getGetType()); + } + + /** + * @expectedException Exceptions/FreshRSS_DAOException + * @expectedExceptionMessage Feed DAO is not loaded in UserQuery + */ + public function test__construct_whenFeedQueryAndNoDao_throwsException() { + $this->markTestIncomplete('There is a problem with the exception autoloading. We need to make a better autoloading process'); + $query = array('get' => 'c_1'); + new FreshRSS_UserQuery($query); + } + + public function test__construct_whenFeedQuery_storesFeedParameters() { + $feed_name = 'some feed name'; + $feed = $this->getMock('FreshRSS_Feed', array(), array('', false)); + $feed->expects($this->atLeastOnce()) + ->method('name') + ->withAnyParameters() + ->willReturn($feed_name); + $feed_dao = $this->getMock('FreshRSS_Searchable'); + $feed_dao->expects($this->atLeastOnce()) + ->method('searchById') + ->withAnyParameters() + ->willReturn($feed); + $query = array('get' => 'f_1'); + $user_query = new FreshRSS_UserQuery($query, $feed_dao, null); + $this->assertEquals($feed_name, $user_query->getGetName()); + $this->assertEquals('feed', $user_query->getGetType()); + } + + public function test__construct_whenUnknownQuery_doesStoreParameters() { + $query = array('get' => 'q'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertNull($user_query->getGetName()); + $this->assertNull($user_query->getGetType()); + } + + public function test__construct_whenName_storesName() { + $name = 'some name'; + $query = array('name' => $name); + $user_query = new FreshRSS_UserQuery($query); + $this->assertEquals($name, $user_query->getName()); + } + + public function test__construct_whenOrder_storesOrder() { + $order = 'some order'; + $query = array('order' => $order); + $user_query = new FreshRSS_UserQuery($query); + $this->assertEquals($order, $user_query->getOrder()); + } + + public function test__construct_whenState_storesState() { + $state = 'some state'; + $query = array('state' => $state); + $user_query = new FreshRSS_UserQuery($query); + $this->assertEquals($state, $user_query->getState()); + } + + public function test__construct_whenUrl_storesUrl() { + $url = 'some url'; + $query = array('url' => $url); + $user_query = new FreshRSS_UserQuery($query); + $this->assertEquals($url, $user_query->getUrl()); + } + + public function testToArray_whenNoData_returnsEmptyArray() { + $user_query = new FreshRSS_UserQuery(array()); + $this->assertInternalType('array', $user_query->toArray()); + $this->assertCount(0, $user_query->toArray()); + } + + public function testToArray_whenData_returnsArray() { + $query = array( + 'get' => 's', + 'name' => 'some name', + 'order' => 'some order', + 'search' => 'some search', + 'state' => 'some state', + 'url' => 'some url', + ); + $user_query = new FreshRSS_UserQuery($query); + $this->assertInternalType('array', $user_query->toArray()); + $this->assertCount(6, $user_query->toArray()); + $this->assertEquals($query, $user_query->toArray()); + } + + public function testHasSearch_whenSearch_returnsTrue() { + $query = array( + 'search' => 'some search', + ); + $user_query = new FreshRSS_UserQuery($query); + $this->assertTrue($user_query->hasSearch()); + } + + public function testHasSearch_whenNoSearch_returnsFalse() { + $user_query = new FreshRSS_UserQuery(array()); + $this->assertFalse($user_query->hasSearch()); + } + + public function testHasParameters_whenAllQuery_returnsFalse() { + $query = array('get' => 'a'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertFalse($user_query->hasParameters()); + } + + public function testHasParameters_whenNoParameter_returnsFalse() { + $query = array(); + $user_query = new FreshRSS_UserQuery($query); + $this->assertFalse($user_query->hasParameters()); + } + + public function testHasParameters_whenParameter_returnTrue() { + $query = array('get' => 's'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertTrue($user_query->hasParameters()); + } + + public function testIsDeprecated_whenCategoryExists_returnFalse() { + $cat = $this->getMock('FreshRSS_Category'); + $cat_dao = $this->getMock('FreshRSS_Searchable'); + $cat_dao->expects($this->atLeastOnce()) + ->method('searchById') + ->withAnyParameters() + ->willReturn($cat); + $query = array('get' => 'c_1'); + $user_query = new FreshRSS_UserQuery($query, null, $cat_dao); + $this->assertFalse($user_query->isDeprecated()); + } + + public function testIsDeprecated_whenCategoryDoesNotExist_returnTrue() { + $cat_dao = $this->getMock('FreshRSS_Searchable'); + $cat_dao->expects($this->atLeastOnce()) + ->method('searchById') + ->withAnyParameters() + ->willReturn(null); + $query = array('get' => 'c_1'); + $user_query = new FreshRSS_UserQuery($query, null, $cat_dao); + $this->assertTrue($user_query->isDeprecated()); + } + + public function testIsDeprecated_whenFeedExists_returnFalse() { + $feed = $this->getMock('FreshRSS_Feed', array(), array('', false)); + $feed_dao = $this->getMock('FreshRSS_Searchable'); + $feed_dao->expects($this->atLeastOnce()) + ->method('searchById') + ->withAnyParameters() + ->willReturn($feed); + $query = array('get' => 'f_1'); + $user_query = new FreshRSS_UserQuery($query, $feed_dao, null); + $this->assertFalse($user_query->isDeprecated()); + } + + public function testIsDeprecated_whenFeedDoesNotExist_returnTrue() { + $feed_dao = $this->getMock('FreshRSS_Searchable'); + $feed_dao->expects($this->atLeastOnce()) + ->method('searchById') + ->withAnyParameters() + ->willReturn(null); + $query = array('get' => 'f_1'); + $user_query = new FreshRSS_UserQuery($query, $feed_dao, null); + $this->assertTrue($user_query->isDeprecated()); + } + + public function testIsDeprecated_whenAllQuery_returnFalse() { + $query = array('get' => 'a'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertFalse($user_query->isDeprecated()); + } + + public function testIsDeprecated_whenFavoriteQuery_returnFalse() { + $query = array('get' => 's'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertFalse($user_query->isDeprecated()); + } + + public function testIsDeprecated_whenUnknownQuery_returnFalse() { + $query = array('get' => 'q'); + $user_query = new FreshRSS_UserQuery($query); + $this->assertFalse($user_query->isDeprecated()); + } + +} -- cgit v1.2.3 From 24f6c1eabb4cea941e40307c2f732c0ca384ffd2 Mon Sep 17 00:00:00 2001 From: Alexis Degrugillier Date: Thu, 5 Mar 2015 06:47:13 -0500 Subject: Fix spacing --- app/Models/CategoryDAO.php | 2 +- app/Models/EntryDAO.php | 2 +- app/Models/FeedDAO.php | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 4eee226ba..189a5f0e4 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -1,6 +1,6 @@ prefix . 'category`(name) VALUES(?)'; $stm = $this->bd->prepare($sql); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index b8a1a43b0..9736d5cd3 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -1,6 +1,6 @@ prefix . 'feed` (url, category, name, website, description, lastUpdate, priority, httpAuth, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); -- cgit v1.2.3 From 993466844405bd9854d890d6d5ebf763ed8b78cb Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 9 May 2015 23:37:56 +0200 Subject: SQL: more robust purge https://github.com/FreshRSS/FreshRSS/issues/798 https://github.com/FreshRSS/FreshRSS/issues/493 --- app/Controllers/feedController.php | 13 +++++-------- app/Models/Feed.php | 3 +-- app/Models/FeedDAO.php | 8 +++++--- 3 files changed, 11 insertions(+), 13 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 59c9174fb..5657d4a88 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -329,7 +329,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // 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. @@ -353,14 +352,14 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // 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. + if ($entry_date < $date_min) { $id = min(time(), $entry_date) . uSecString(); + $entry->_isRead(true); //Old article that was not in database. Probably an error, so mark as read + } else { + $id = uTimeString(); + $entry->_isRead($is_read); } - $entry->_id($id); - $entry->_isRead($is_read); $entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry); if ($entry === null) { @@ -376,7 +375,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } $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) @@ -384,7 +382,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController { 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, diff --git a/app/Models/Feed.php b/app/Models/Feed.php index 27c83ffd5..5d377de9a 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -245,7 +245,7 @@ class FreshRSS_Feed extends Minz_Model { $this->_url($clean_url); } - if (($mtime === true) ||($mtime > $this->lastUpdate)) { + if (($mtime === true) || ($mtime > $this->lastUpdate)) { Minz_Log::notice('FreshRSS no cache ' . $mtime . ' > ' . $this->lastUpdate . ' for ' . $clean_url); $this->loadEntries($feed); // et on charge les articles du flux } else { @@ -255,7 +255,6 @@ 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/Models/FeedDAO.php b/app/Models/FeedDAO.php index f48beee6e..c13e2b008 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -322,10 +322,12 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $affected; } - public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) just after + public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) or updateCachedValues() just after $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' - . 'WHERE id_feed = :id_feed AND id <= :id_max AND is_favorite=0 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 yet support 'LIMIT & IN/ALL/ANY/SOME subquery' + . 'WHERE id_feed = :id_feed AND id <= :id_max ' + . 'AND is_favorite=0 ' //Do not remove favourites + . 'AND lastSeen < (SELECT maxLastSeen FROM (SELECT (MAX(e3.lastSeen) - 99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed = :id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance + . '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'; -- cgit v1.2.3 From a7bc54bb996a87a66127101be548d181dc8dd935 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 9 May 2015 23:45:52 +0200 Subject: Minor spaces --- app/Models/FeedDAO.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index c13e2b008..76025ff53 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -324,10 +324,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) or updateCachedValues() just after $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' - . 'WHERE id_feed = :id_feed AND id <= :id_max ' + . 'WHERE id_feed=:id_feed AND id<=:id_max ' . 'AND is_favorite=0 ' //Do not remove favourites - . 'AND lastSeen < (SELECT maxLastSeen FROM (SELECT (MAX(e3.lastSeen) - 99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed = :id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance - . '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' + . 'AND lastSeen < (SELECT maxLastSeen FROM (SELECT (MAX(e3.lastSeen)-99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance + . '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'; -- cgit v1.2.3 From 217c191a1ba3ac03b847d261a32e19975380fcad Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite 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/Models/FeedDAO.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 7c1b5e322cca0134f57b3a436129985ba9170b9f Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite 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/Models/FeedDAO.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 @@ +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 = <<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 @@ +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 = <<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 = <<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 = <<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 = <<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 @@ $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 + + +
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 e315192c4b3df89dddb1ac37c6c7a01531d7952f Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 14 Aug 2016 11:06:31 +0200 Subject: Bug static --- app/Models/CategoryDAO.php | 2 +- app/Models/FeedDAO.php | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index a44edb0f6..3b1519c20 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('"' . parent::prefix . 'category_id_seq"'); + return $this->bd->lastInsertId('"' . $this->prefix . 'category_id_seq"'); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error addCategory: ' . $info[2]); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index f29dac9c0..ce65ed8b1 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('"' . parent::prefix . 'feed_id_seq"'); + return $this->bd->lastInsertId('"' . $this->prefix . 'feed_id_seq"'); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error addFeed: ' . $info[2]); -- cgit v1.2.3 From f66be86e41d89214688a28243b412ffa43ce500d Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 31 Aug 2016 21:47:12 +0200 Subject: Quoted upper-cases instead of string replace --- app/Models/CategoryDAO.php | 2 +- app/Models/EntryDAO.php | 14 +++++++------- app/Models/EntryDAOSQLite.php | 4 ++-- app/Models/FeedDAO.php | 22 +++++++++++----------- app/Models/FeedDAOSQLite.php | 4 ++-- lib/Minz/ModelPdo.php | 5 +---- 6 files changed, 24 insertions(+), 27 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 3b1519c20..1dab60ea9 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -100,7 +100,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable public function listCategories($prePopulateFeeds = true, $details = false) { if ($prePopulateFeeds) { $sql = 'SELECT c.id AS c_id, c.name AS c_name, ' - . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.cache_nbEntries, f.cache_nbUnreads ') + . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.`cache_nbEntries`, f.`cache_nbUnreads` ') . 'FROM `' . $this->prefix . 'category` c ' . 'LEFT OUTER JOIN `' . $this->prefix . 'feed` f ON f.category=c.id ' . 'GROUP BY f.id, c_id ' diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 8d136cd6c..d39f0237c 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -28,7 +28,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->bd->beginTransaction(); $hasTransaction = true; } - $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN lastSeen INT(11) DEFAULT 0'); + $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()) { @@ -113,7 +113,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { 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) ' + . ', link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) ' . 'VALUES(:id, :guid, :title, :author, ' . ($this->isCompressed() ? 'COMPRESS(:content)' : ':content') . ', :link, :date, :last_seen, ' @@ -174,7 +174,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'UPDATE `' . $this->prefix . 'entry` ' . 'SET title=?, author=?, ' . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?') - . ', link=?, date=?, lastSeen=?, hash=' + . ', link=?, date=?, `lastSeen`=?, hash=' . ($this->hasNativeHex() ? 'X?' : '?') //TODO PostgreSQL . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=?, ') . 'tags=? ' @@ -265,7 +265,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'WHERE e.is_read=0 ' . 'GROUP BY e.id_feed' . ') x ON x.id_feed=f.id ' - . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0) ' + . 'SET f.`cache_nbUnreads`=COALESCE(x.nbUnreads, 0) ' . 'WHERE 1'; $values = array(); if ($feedId !== false) { @@ -328,7 +328,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } else { $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 ' + . 'f.`cache_nbUnreads`=f.`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 ' . 'WHERE e.id=? AND e.is_read=?'; $values = array($is_read ? 1 : 0, $ids, $is_read ? 0 : 1); $stm = $this->bd->prepare($sql); @@ -467,7 +467,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($affected > 0) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbUnreads=cache_nbUnreads-' . $affected + . 'SET `cache_nbUnreads`=`cache_nbUnreads`-' . $affected . ' WHERE id=?'; $values = array($id_feed); $stm = $this->bd->prepare($sql); @@ -703,7 +703,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (count($guids) < 1) { return 0; } - $sql = 'UPDATE `' . $this->prefix . 'entry` SET lastSeen=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $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); diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index 80dbcca6b..fd5d25bf6 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -28,7 +28,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { protected function updateCacheUnreads($catId = false, $feedId = false) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbUnreads=(' + . 'SET `cache_nbUnreads`=(' . 'SELECT COUNT(*) AS nbUnreads FROM `' . $this->prefix . 'entry` e ' . 'WHERE e.id_feed=`' . $this->prefix . 'feed`.id AND e.is_read=0) ' . 'WHERE 1'; @@ -86,7 +86,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { } $affected = $stm->rowCount(); if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` SET cache_nbUnreads=cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' + $sql = 'UPDATE `' . $this->prefix . 'feed` SET `cache_nbUnreads`=`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 ' . 'WHERE id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; $values = array($ids); $stm = $this->bd->prepare($sql); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index ce65ed8b1..6e6d8857b 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -2,7 +2,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function addFeed($valuesTmp) { - $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, lastUpdate, priority, httpAuth, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; + $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, `lastUpdate`, priority, `httpAuth`, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); $values = array( @@ -85,13 +85,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function updateLastUpdate($id, $inError = 0, $updateCache = true) { if ($updateCache) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE - . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),' - . 'lastUpdate=?, error=? ' + . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' + . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),' + . '`lastUpdate`=?, error=? ' . 'WHERE id=?'; } else { $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET lastUpdate=?, error=? ' + . 'SET `lastUpdate`=?, error=? ' . 'WHERE id=?'; } @@ -226,10 +226,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($defaultCacheDuration < 0) { $defaultCacheDuration = 2147483647; } - $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history, ttl ' + $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl ' . 'FROM `' . $this->prefix . 'feed` ' - . 'WHERE ttl <> -1 AND lastUpdate < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' - . 'ORDER BY lastUpdate'; + . 'WHERE ttl <> -1 AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' + . 'ORDER BY `lastUpdate`'; $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute())) { $sql2 = 'ALTER TABLE `' . $this->prefix . 'feed` ADD COLUMN ttl INT NOT NULL DEFAULT -2'; //v0.7.3 @@ -282,7 +282,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . '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'; + . 'SET f.`cache_nbEntries`=x.nbEntries, f.`cache_nbUnreads`=x.nbUnreads'; $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { @@ -308,7 +308,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $affected = $stm->rowCount(); $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbEntries=0, cache_nbUnreads=0 WHERE id=?'; + . 'SET `cache_nbEntries`=0, `cache_nbUnreads`=0 WHERE id=?'; $values = array($id); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { @@ -326,7 +326,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' . 'WHERE id_feed=:id_feed AND id<=:id_max ' . 'AND is_favorite=0 ' //Do not remove favourites - . 'AND lastSeen < (SELECT maxLastSeen FROM (SELECT (MAX(e3.lastSeen)-99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance + . 'AND `lastSeen` < (SELECT maxLastSeen FROM (SELECT (MAX(e3.`lastSeen`)-99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance . '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); diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php index 7599fda53..440ae74da 100644 --- a/app/Models/FeedDAOSQLite.php +++ b/app/Models/FeedDAOSQLite.php @@ -4,8 +4,8 @@ class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAO { public function updateCachedValues() { //For one single feed, call updateLastUpdate($id) $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)'; + . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' + . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)'; $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { return $stm->rowCount(); diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php index 93a22fc3d..78b44ea7f 100644 --- a/lib/Minz/ModelPdo.php +++ b/lib/Minz/ModelPdo.php @@ -156,9 +156,6 @@ class MinzPDOMSQLite extends MinzPDO { class MinzPDOPGSQL extends MinzPDO { protected function compatibility($statement) { - return str_replace( - array('`', 'lastUpdate', 'pathEntries', 'httpAuth', 'cache_nbEntries', 'cache_nbUnreads', 'lastSeen'), - array('"', '"lastUpdate"', '"pathEntries"', '"httpAuth"', '"cache_nbEntries"', '"cache_nbUnreads"', '"lastSeen"'), - $statement); + return str_replace('`', '"', $statement); } } -- cgit v1.2.3 From 05cabe99ae48a187a77e0246dfffc60f2434b5e5 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 5 Oct 2016 00:39:54 +0200 Subject: Take better advantage of other users refresh --- app/Controllers/feedController.php | 18 ++++++++++++++++-- app/Models/EntryDAO.php | 7 +++++-- app/Models/Feed.php | 4 ++++ app/Models/FeedDAO.php | 8 ++++---- 4 files changed, 29 insertions(+), 8 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index c2a22aeb3..15f373833 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -242,7 +242,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $feeds[] = $feed; } } else { - $feeds = $feedDAO->listFeedsOrderUpdate(FreshRSS_Context::$user_conf->ttl_default); + $feeds = $feedDAO->listFeedsOrderUpdate(-1); } // Calculate date of oldest entries we accept in DB. @@ -266,6 +266,20 @@ class FreshRSS_feed_Controller extends Minz_ActionController { continue; //When PubSubHubbub is used, do not pull refresh so often } + $mtime = 0; + $ttl = $feed->ttl(); + if ($ttl == -1) { + continue; //Feed refresh is disabled + } + if (feed->lastUpdate() < time() - ($ttl == -2 ? FreshRSS_Context::$user_conf->ttl_default : $ttl)) { + //Too early to refresh from source, but check if the feed was already updated by another user + $mtime = feed->cacheModifiedTime(); + if (feed->lastUpdate() >= $mtime + 10) { + continue; //Nothing newer from other users + } + Minz_Log::debug($feed->url() . ' was recently updated by another user'); + } + if (!$feed->lock()) { Minz_Log::notice('Feed already being actualized: ' . $feed->url()); continue; @@ -358,7 +372,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $entryDAO->addEntry($entry->toArray()); } } - $entryDAO->updateLastSeen($feed->id(), $oldGuids); + $entryDAO->updateLastSeen($feed->id(), $oldGuids, $mtime); } if ($feed_history >= 0 && rand(0, 30) === 1) { diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 63565e73a..43ac21f9e 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -707,13 +707,16 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } - public function updateLastSeen($id_feed, $guids) { + public function updateLastSeen($id_feed, $guids, $mtime = 0) { 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); + if ($mtime <= 0) { + $mtime = time(); + } + $values = array($mtime, $id_feed); $values = array_merge($values, $guids); if ($stm && $stm->execute($values)) { return $stm->rowCount(); diff --git a/app/Models/Feed.php b/app/Models/Feed.php index f2f345662..f435620c8 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -340,6 +340,10 @@ class FreshRSS_Feed extends Minz_Model { $this->entries = $entries; } + function cacheModifiedTime() { + return @filemtime(CACHE_PATH . '/' . md5($this->url) . '.spc'); + } + function lock() { $this->lockPath = TMP_PATH . '/' . $this->hash() . '.freshrss.lock'; if (file_exists($this->lockPath) && ((time() - @filemtime($this->lockPath)) > 3600)) { diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 6e6d8857b..ad3cb0c2e 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -222,13 +222,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $feedCategoryNames; } + /** + * Use $defaultCacheDuration == -1 to return all feeds, without filtering them by TTL. + */ public function listFeedsOrderUpdate($defaultCacheDuration = 3600) { - if ($defaultCacheDuration < 0) { - $defaultCacheDuration = 2147483647; - } $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl ' . 'FROM `' . $this->prefix . 'feed` ' - . 'WHERE ttl <> -1 AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ' + . ($defaultCacheDuration < 0 ? '' : 'WHERE ttl <> -1 AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ') . 'ORDER BY `lastUpdate`'; $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute())) { -- cgit v1.2.3 From fe65eec5bbdaee37177e3673e31e241b1f1b938d Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 5 Oct 2016 17:48:24 +0200 Subject: Better multiuser update --- app/Controllers/feedController.php | 9 +++++---- app/Models/FeedDAO.php | 10 +++++++--- 2 files changed, 12 insertions(+), 7 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index d85a53446..dadc8aa83 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -271,13 +271,14 @@ class FreshRSS_feed_Controller extends Minz_ActionController { if ($ttl == -1) { continue; //Feed refresh is disabled } - if ($feed->lastUpdate() < time() + 60 - ($ttl == -2 ? FreshRSS_Context::$user_conf->ttl_default : $ttl)) { + if ($feed->lastUpdate() + 10 >= time() - ($ttl == -2 ? FreshRSS_Context::$user_conf->ttl_default : $ttl)) { //Too early to refresh from source, but check whether the feed was updated by another user $mtime = $feed->cacheModifiedTime(); - if ($feed->lastUpdate() >= $mtime + 10) { + if ($feed->lastUpdate() + 10 >= $mtime) { continue; //Nothing newer from other users } - Minz_Log::debug($feed->url() . ' was recently updated by another user'); + //Minz_Log::debug($feed->url() . ' was updated at ' . date('c', $mtime) . ' by another user'); + //Will take advantage of the newer cache } if (!$feed->lock()) { @@ -391,7 +392,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } } - $feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->inTransaction()); + $feedDAO->updateLastUpdate($feed->id(), false, $entryDAO->inTransaction(), $mtime); if ($entryDAO->inTransaction()) { $entryDAO->commit(); } diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index ad3cb0c2e..c680d270c 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -82,7 +82,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } - public function updateLastUpdate($id, $inError = 0, $updateCache = true) { + public function updateLastUpdate($id, $inError = false, $updateCache = true, $mtime = 0) { if ($updateCache) { $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' @@ -95,9 +95,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'WHERE id=?'; } + if ($mtime <= 0) { + $mtime = time(); + } + $values = array( - time(), - $inError, + $mtime, + $inError ? 1 : 0, $id, ); -- cgit v1.2.3 From 1893fc61e0e576519f878267fd877247445d1055 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 20 Oct 2016 01:19:59 +0200 Subject: guid and urls should not contain low/high characters It looks like SimplePie does not always filter everything Having a character not in latin1 would create MySQL collate errors --- app/Controllers/feedController.php | 4 +++- app/Controllers/importExportController.php | 4 +++- app/Models/EntryDAO.php | 4 ++++ app/Models/FeedDAO.php | 10 ++++++++++ 4 files changed, 20 insertions(+), 2 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 8751d2fff..d4a6c9955 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -314,7 +314,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController { if (count($entries) > 0) { $newGuids = array(); foreach ($entries as $entry) { - $newGuids[] = $entry->guid(); + $guid = $entry->guid(); + $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $newGuids[] = $guid; } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index d36c57deb..e380323c4 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -362,7 +362,9 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $newGuids = array(); foreach ($article_object['items'] as $item) { - $newGuids[] = $item['id']; + $guid = $item['id']; + $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $newGuids[] = $guid; } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $this->entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 3959cb191..466e6f5a3 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -123,6 +123,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); + $valuesTmp['guid'] = filter_var($valuesTmp['guid'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); @@ -130,6 +131,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); @@ -190,6 +192,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->updateEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->updateEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); $this->updateEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->updateEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); @@ -689,6 +692,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (count($guids) < 1) { return array(); } + $guids = array_unique($guids); $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hex_hash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; $stm = $this->bd->prepare($sql); $values = array($id_feed); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index c680d270c..33e19d750 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -5,6 +5,9 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, `lastUpdate`, priority, `httpAuth`, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); + $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $values = array( substr($valuesTmp['url'], 0, 511), $valuesTmp['category'], @@ -55,6 +58,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function updateFeed($id, $valuesTmp) { + if (isset($valuesTmp['url'])) { + $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + } + if (isset($valuesTmp['website'])) { + $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + } + $set = ''; foreach ($valuesTmp as $key => $v) { $set .= $key . '=?, '; -- cgit v1.2.3 From 7f2b0439ec4158ee7d78571d60e9bcc995e87cac Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 20 Oct 2016 01:38:23 +0200 Subject: Extract function safe_ascii() --- app/Controllers/feedController.php | 4 +--- app/Controllers/importExportController.php | 4 +--- app/Models/EntryDAO.php | 6 +++--- app/Models/FeedDAO.php | 8 ++++---- lib/lib_rss.php | 3 +++ 5 files changed, 12 insertions(+), 13 deletions(-) (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index d4a6c9955..ed3229687 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -314,9 +314,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { if (count($entries) > 0) { $newGuids = array(); foreach ($entries as $entry) { - $guid = $entry->guid(); - $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); - $newGuids[] = $guid; + $newGuids[] = safe_ascii($entry->guid()); } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index e380323c4..a1f789805 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -362,9 +362,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $newGuids = array(); foreach ($article_object['items'] as $item) { - $guid = $item['id']; - $guid = filter_var($guid, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); - $newGuids[] = $guid; + $newGuids[] = safe_ascii($item['id']); } // For this feed, check existing GUIDs already in database. $existingHashForGuids = $this->entryDAO->listHashForFeedGuids($feed->id(), $newGuids); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 466e6f5a3..4c6a9ea20 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -123,7 +123,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); - $valuesTmp['guid'] = filter_var($valuesTmp['guid'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['guid'] = safe_ascii($valuesTmp['guid']); $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); @@ -131,7 +131,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); - $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['link'] = safe_ascii($valuesTmp['link']); $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); @@ -192,7 +192,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->updateEntryPrepared->bindParam(':author', $valuesTmp['author']); $this->updateEntryPrepared->bindParam(':content', $valuesTmp['content']); $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); - $valuesTmp['link'] = filter_var($valuesTmp['link'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['link'] = safe_ascii($valuesTmp['link']); $this->updateEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->updateEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); $valuesTmp['lastSeen'] = time(); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 33e19d750..b21f19b66 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -5,8 +5,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, `lastUpdate`, priority, `httpAuth`, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)'; $stm = $this->bd->prepare($sql); - $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); - $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['url'] = safe_ascii($valuesTmp['url']); + $valuesTmp['website'] = safe_ascii($valuesTmp['website']); $values = array( substr($valuesTmp['url'], 0, 511), @@ -59,10 +59,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function updateFeed($id, $valuesTmp) { if (isset($valuesTmp['url'])) { - $valuesTmp['url'] = filter_var($valuesTmp['url'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['url'] = safe_ascii($valuesTmp['url']); } if (isset($valuesTmp['website'])) { - $valuesTmp['website'] = filter_var($valuesTmp['website'], FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); + $valuesTmp['website'] = safe_ascii($valuesTmp['website']); } $set = ''; diff --git a/lib/lib_rss.php b/lib/lib_rss.php index b18512484..75046fd54 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -83,6 +83,9 @@ function checkUrl($url) { } } +function safe_ascii($text) { + return filter_var($text, FILTER_DEFAULT, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH); +} /** * Test if a given server address is publicly accessible. -- cgit v1.2.3 From fcb9280fc87c159539f5832ab35f174cd515654e Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 23 Oct 2016 13:37:48 +0200 Subject: CLI export ZIP export, OPML export. Corrected height of feeds select in Pafat theme. https://github.com/FreshRSS/FreshRSS/pull/1338 https://github.com/FreshRSS/FreshRSS/issues/1039 https://github.com/FreshRSS/FreshRSS/issues/1277 --- app/Controllers/importExportController.php | 84 +++++++++++++++++++----------- app/Models/FeedDAO.php | 7 +++ app/views/importExport/index.phtml | 2 +- cli/_cli.php | 4 +- cli/create-user.php | 14 ++--- cli/delete-user.php | 2 +- cli/export-opml-for-user.php | 24 +++++++++ cli/export-zip-for-user.php | 30 +++++++++++ cli/import-for-user.php | 3 +- p/themes/Pafat/pafat.css | 3 -- 10 files changed, 128 insertions(+), 45 deletions(-) create mode 100644 cli/export-opml-for-user.php create mode 100644 cli/export-zip-for-user.php (limited to 'app/Models/FeedDAO.php') diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index fbebb2a78..bb22ac739 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -522,26 +522,21 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { return $return; } - /** - * This action handles export action. - * - * This action must be reached by a POST request. - * - * Parameters are: - * - export_opml (default: false) - * - export_starred (default: false) - * - export_feeds (default: array()) a list of feed ids - */ - public function exportAction() { - if (!Minz_Request::isPost()) { - Minz_Request::forward(array('c' => 'importExport', 'a' => 'index'), true); - } + public function exportFile($export_opml = true, $export_starred = false, $export_feeds = array(), $maxFeedEntries = 50, $username = null) { + require_once(LIB_PATH . '/lib_opml.php'); - $this->view->_useLayout(false); + $this->catDAO = new FreshRSS_CategoryDAO($username); + $this->entryDAO = FreshRSS_Factory::createEntryDao($username); + $this->feedDAO = FreshRSS_Factory::createFeedDao($username); + + if ($export_feeds === true) { + //All feeds + $export_feeds = $this->feedDAO->listFeedsIds(); + } + if (!is_array($export_feeds)) { + $export_feeds = array(); + } - $export_opml = Minz_Request::param('export_opml', false); - $export_starred = Minz_Request::param('export_starred', false); - $export_feeds = Minz_Request::param('export_feeds', array()); $day = date('Y-m-d'); $export_files = array(); @@ -558,7 +553,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { if ($feed) { $filename = "feed_${day}_" . $feed->category() . '_' . $feed->id() . '.json'; - $export_files[$filename] = $this->generateEntries('feed', $feed); + $export_files[$filename] = $this->generateEntries('feed', $feed, $maxFeedEntries); } } @@ -566,18 +561,49 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { if ($nb_files > 1) { // If there are more than 1 file to export, we need a ZIP archive. try { - $this->exportZip($export_files); + $this->sendZip($export_files); } catch (Exception $e) { - # Oops, there is no ZIP extension! - Minz_Request::bad(_t('feedback.import_export.export_no_zip_extension'), - array('c' => 'importExport', 'a' => 'index')); + throw new FreshRSS_ZipMissing_Exception($e); } } elseif ($nb_files === 1) { // Only one file? Guess its type and export it. $filename = key($export_files); $type = self::guessFileType($filename); - $this->exportFile('freshrss_' . $filename, $export_files[$filename], $type); - } else { + $this->sendFile('freshrss_' . $filename, $export_files[$filename], $type); + } + return $nb_files; + } + + /** + * This action handles export action. + * + * This action must be reached by a POST request. + * + * Parameters are: + * - export_opml (default: false) + * - export_starred (default: false) + * - export_feeds (default: array()) a list of feed ids + */ + public function exportAction() { + if (!Minz_Request::isPost()) { + Minz_Request::forward(array('c' => 'importExport', 'a' => 'index'), true); + } + $this->view->_useLayout(false); + + $nb_files = 0; + try { + $nb_files = $this->exportFile( + Minz_Request::param('export_opml', false), + Minz_Request::param('export_starred', false), + Minz_Request::param('export_feeds', array()) + ); + } catch (FreshRSS_ZipMissing_Exception $zme) { + # Oops, there is no ZIP extension! + Minz_Request::bad(_t('feedback.import_export.export_no_zip_extension'), + array('c' => 'importExport', 'a' => 'index')); + } + + if ($nb_files < 1) { // Nothing to do... Minz_Request::forward(array('c' => 'importExport', 'a' => 'index'), true); } @@ -606,7 +632,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { * @param FreshRSS_Feed $feed feed of which we want to get entries. * @return string the JSON file content. */ - private function generateEntries($type, $feed = NULL) { + private function generateEntries($type, $feed = NULL, $maxFeedEntries = 50) { $this->view->categories = $this->catDAO->listCategories(); if ($type == 'starred') { @@ -621,7 +647,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $this->view->type = 'feed/' . $feed->id(); $this->view->entries = $this->entryDAO->listWhere( 'f', $feed->id(), FreshRSS_Entry::STATE_ALL, 'ASC', - FreshRSS_Context::$user_conf->posts_per_page + $maxFeedEntries ); $this->view->feed = $feed; } @@ -635,7 +661,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { * @param array $files list of files where key is filename and value the content. * @throws Exception if Zip extension is not loaded. */ - private function exportZip($files) { + private function sendZip($files) { if (!extension_loaded('zip')) { throw new Exception(); } @@ -667,7 +693,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { * @param string $type the file type (opml, json_feed or json_starred). * If equals to unknown, nothing happens. */ - private function exportFile($filename, $content, $type) { + private function sendFile($filename, $content, $type) { if ($type === 'unknown') { return; } diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index b21f19b66..68398efd5 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -212,6 +212,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } + public function listFeedsIds() { + $sql = 'SELECT id FROM `' . $this->prefix . 'feed`'; + $stm = $this->bd->prepare($sql); + $stm->execute(); + return $stm->fetchAll(PDO::FETCH_COLUMN, 0); + } + public function listFeeds() { $sql = 'SELECT * FROM `' . $this->prefix . 'feed` ORDER BY name'; $stm = $this->bd->prepare($sql); diff --git a/app/views/importExport/index.phtml b/app/views/importExport/index.phtml index c0bc412c3..c5049e3ea 100644 --- a/app/views/importExport/index.phtml +++ b/app/views/importExport/index.phtml @@ -44,7 +44,7 @@ $select_args = ' size="' . min(10, count($this->feeds)) .'" multiple="multiple"'; } ?> - size="10"> '; ?> feeds as $feed) { ?> diff --git a/cli/_cli.php b/cli/_cli.php index d81d83d66..66506f07a 100644 --- a/cli/_cli.php +++ b/cli/_cli.php @@ -10,7 +10,7 @@ Minz_Configuration::register('system', DATA_PATH . '/config.php', DATA_PATH . '/config.default.php'); FreshRSS_Context::$system_conf = Minz_Configuration::get('system'); -Minz_Translate::init(); +Minz_Translate::init('en'); FreshRSS_Context::$isCli = true; @@ -39,6 +39,6 @@ function cliInitUser($username) { } function done($ok) { - echo 'Result: ', ($ok ? 'success' : 'fail'), ".\n"; + fwrite(STDERR, 'Result: ' . ($ok ? 'success' : 'fail') . "\n"); exit($ok ? 0 : 1); } diff --git a/cli/create-user.php b/cli/create-user.php index 387b503b6..243e65a35 100755 --- a/cli/create-user.php +++ b/cli/create-user.php @@ -4,16 +4,16 @@ require('_cli.php'); $options = getopt('', array( 'user:', - 'password::', - 'api-password::', - 'language::', - 'email::', - 'token::', + 'password:', + 'api-password:', + 'language:', + 'email:', + 'token:', )); if (empty($options['user'])) { - fail('Usage: ' . basename(__FILE__) . " --user=username --password='password' --api-password='api_password'" . - " --language=en --email=user@example.net --token='longRandomString'"); + fail('Usage: ' . basename(__FILE__) . " --user username --password 'password' --api-password 'api_password'" . + " --language en --email user@example.net --token 'longRandomString'"); } $username = $options['user']; if (!ctype_alnum($username)) { diff --git a/cli/delete-user.php b/cli/delete-user.php index da48103f7..6f0e86e17 100755 --- a/cli/delete-user.php +++ b/cli/delete-user.php @@ -7,7 +7,7 @@ $options = getopt('', array( )); if (empty($options['user'])) { - fail('Usage: ' . basename(__FILE__) . " --user=username"); + fail('Usage: ' . basename(__FILE__) . " --user username"); } $username = $options['user']; if (!ctype_alnum($username)) { diff --git a/cli/export-opml-for-user.php b/cli/export-opml-for-user.php new file mode 100644 index 000000000..bce4efd63 --- /dev/null +++ b/cli/export-opml-for-user.php @@ -0,0 +1,24 @@ +#!/usr/bin/php + /path/to/file.opml.xml'"); +} + +$username = cliInitUser($options['user']); + +fwrite(STDERR, 'FreshRSS exporting OPML for user “' . $username . "”…\n"); + +$importController = new FreshRSS_importExport_Controller(); + +$ok = false; +$ok = $importController->exportFile(true, false, array(), 0, $username); + +invalidateHttpCache($username); + +done($ok); diff --git a/cli/export-zip-for-user.php b/cli/export-zip-for-user.php new file mode 100644 index 000000000..f8d24238b --- /dev/null +++ b/cli/export-zip-for-user.php @@ -0,0 +1,30 @@ +#!/usr/bin/php + /path/to/file.zip'"); +} + +$username = cliInitUser($options['user']); + +fwrite(STDERR, 'FreshRSS exporting ZIP for user “' . $username . "”…\n"); + +$importController = new FreshRSS_importExport_Controller(); + +$ok = false; +try { + $ok = $importController->exportFile(true, true, true, + empty($options['max-feed-entries']) ? 100 : intval($options['max-feed-entries']), + $username); +} catch (FreshRSS_ZipMissing_Exception $zme) { + fail('FreshRSS error: Lacking php-zip extension!'); +} +invalidateHttpCache($username); + +done($ok); diff --git a/cli/import-for-user.php b/cli/import-for-user.php index 308786599..29084f062 100755 --- a/cli/import-for-user.php +++ b/cli/import-for-user.php @@ -5,11 +5,10 @@ require('_cli.php'); $options = getopt('', array( 'user:', 'filename:', - 'clear-existing', )); if (empty($options['user']) || empty($options['filename'])) { - fail('Usage: ' . basename(__FILE__) . " --user=username --filename='/path/to/file.ext' --clear-existing"); + fail('Usage: ' . basename(__FILE__) . " --user username --filename /path/to/file.ext"); } $username = cliInitUser($options['user']); diff --git a/p/themes/Pafat/pafat.css b/p/themes/Pafat/pafat.css index 5e46a63ca..abe808eab 100644 --- a/p/themes/Pafat/pafat.css +++ b/p/themes/Pafat/pafat.css @@ -48,9 +48,6 @@ input, select, textarea { vertical-align: middle; } -select{ - height:29px; -} option { padding: 0 .5em; } -- cgit v1.2.3