From ee2c1a8c7888ef16d76a0c03ff2040aaa8a11a94 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 28 Nov 2013 01:42:06 +0100 Subject: Classement par date d'ajout e.id (expérimentation) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Expérimentation : classement par date d'ajout dans la base plutôt que selon la date déclarée par le flux (qui est parfois fausse dans le passé, dans le futur, ou absente). Quelques conséquences : * Les flux avec des dates erronées ne sont plus un problème * Lorsqu'on fait "marquer tout comme lu", les articles arrivés pendant la lecture ne sont plus indûment marqués comme lus * Les articles ont tendance à être plus regroupés par flux lorsqu'on les affiche par catégorie * Si un utilisateur n'utilise pas de cron et n'utilise pas FreshRSS pendant plusieurs jours, lors du rafraîchissement, les nouveaux articles seront dans "Aujourd'hui" (à interpréter donc comme les articles reçus aujourd'hui, et non comme déclarés comme étant publiés aujourd'hui) * La pagination est plus efficace Termine l'implémentation de https://github.com/marienfressinaud/FreshRSS/issues/202 --- app/models/RSSPaginator.php | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'app/models/RSSPaginator.php') diff --git a/app/models/RSSPaginator.php b/app/models/RSSPaginator.php index 86b4b5cac..60d2a06ac 100644 --- a/app/models/RSSPaginator.php +++ b/app/models/RSSPaginator.php @@ -23,6 +23,10 @@ class RSSPaginator { return $this->next; } + public function peek () { + return empty($this->items) ? null : $this->items[0]; + } + public function render ($view, $getteur) { $view = APP_PATH . '/views/helpers/'.$view; -- cgit v1.2.3 From e98b7ab13ec414d1c5c3c3d1d6a7c9995ebf4fea Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 30 Nov 2013 13:15:54 +0100 Subject: SQL : compression côté base de données (attention, perte de compatibilité) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Ça y est, j'ai tout cassé... Contribue à https://github.com/marienfressinaud/FreshRSS/issues/204 Compatible MySQL 5.0. Commentaires souhaités avant l'implémentation de la recherche côté base de données. Pour l'instant, je n'ai pas fait de script de mise à jour, car la manière précédente `base64_encode(gzdeflate(serialize($content)))` est difficile à traiter côté MySQL et nécessite une boucle en PHP. Avec la nouvelle approche de ce patch, nous pourrons plus facilement changer d'avis sans perte de compatibilité. --- README.md | 2 +- app/models/Entry.php | 82 +++++++++------------------------------------ app/models/Feed.php | 22 ++++++++++++ app/models/RSSPaginator.php | 2 +- public/install.php | 13 ++++--- 5 files changed, 48 insertions(+), 73 deletions(-) (limited to 'app/models/RSSPaginator.php') diff --git a/README.md b/README.md index fb0b9e61e..fb4793e2d 100644 --- a/README.md +++ b/README.md @@ -22,7 +22,7 @@ Privilégiez pour cela des demandes sur GitHub * PHP 5.2+ (PHP 5.3.3+ recommandé) * Requis : [libxml](http://php.net/xml), [cURL](http://php.net/curl), [PDO_MySQL](http://php.net/pdo-mysql) * Recommandés : [Zlib](http://php.net/zlib), [mbstring](http://php.net/mbstring), [iconv](http://php.net/iconv) -* MySQL 5.0.3+ (SQLite à venir) +* MySQL 5.0.3+ (ou SQLite 3.7.4+ à venir) * Un navigateur Web récent tel Firefox, Chrome, Opera, Safari, Internet Explorer 9+ * Fonctionne aussi sur mobile diff --git a/app/models/Entry.php b/app/models/Entry.php index 739c2a582..894985ece 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -199,7 +199,8 @@ class Entry extends Model { class EntryDAO extends Model_pdo { public function addEntry ($valuesTmp) { - $sql = 'INSERT INTO `' . $this->prefix . 'entry`(id, guid, title, author, content, link, date, is_read, is_favorite, id_feed, tags) VALUES(CAST(? * 1000000 AS SIGNED INTEGER), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'; + $sql = 'INSERT INTO `' . $this->prefix . 'entry`(id, guid, title, author, content_bin, link, date, is_read, is_favorite, id_feed, tags) ' + . 'VALUES(CAST(? * 1000000 AS SIGNED INTEGER), ?, ?, ?, COMPRESS(?), ?, ?, ?, ?, ?, ?)'; $stm = $this->bd->prepare ($sql); $values = array ( @@ -207,7 +208,7 @@ class EntryDAO extends Model_pdo { substr($valuesTmp['guid'], 0, 760), substr($valuesTmp['title'], 0, 255), substr($valuesTmp['author'], 0, 255), - base64_encode (gzdeflate (serialize ($valuesTmp['content']))), + $valuesTmp['content'], substr($valuesTmp['link'], 0, 1023), $valuesTmp['date'], $valuesTmp['is_read'], @@ -231,33 +232,6 @@ class EntryDAO extends Model_pdo { } } - /*public function updateEntry ($id, $valuesTmp) { - if (isset ($valuesTmp['content'])) { - $valuesTmp['content'] = base64_encode (gzdeflate (serialize ($valuesTmp['content']))); - } - - $set = ''; - foreach ($valuesTmp as $key => $v) { - $set .= $key . '=?, '; - } - $set = substr ($set, 0, -2); - - $sql = 'UPDATE `' . $this->prefix . 'entry` SET ' . $set . ' WHERE id=?'; - $stm = $this->bd->prepare ($sql); - - foreach ($valuesTmp as $v) { - $values[] = $v; - } - $values[] = $id; - - if ($stm && $stm->execute ($values)) { - return $stm->rowCount(); - } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); - return false; - } - }*/ public function markFavorite ($id, $is_favorite = true) { $sql = 'UPDATE `' . $this->prefix . 'entry` e ' . 'SET e.is_favorite = ? ' @@ -443,35 +417,9 @@ class EntryDAO extends Model_pdo { } } - /*public function updateEntries ($valuesTmp) { - if (isset ($valuesTmp['content'])) { - $valuesTmp['content'] = base64_encode (gzdeflate (serialize ($valuesTmp['content']))); - } - - $set = ''; - foreach ($valuesTmp as $key => $v) { - $set .= $key . '=?, '; - } - $set = substr ($set, 0, -2); - - $sql = 'UPDATE `' . $this->prefix . 'entry` SET ' . $set; - $stm = $this->bd->prepare ($sql); - - foreach ($valuesTmp as $v) { - $values[] = $v; - } - - if ($stm && $stm->execute ($values)) { - return $stm->rowCount(); - } else { - $info = $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); - return false; - } - }*/ - public function cleanOldEntries ($date_min) { - $sql = 'DELETE e.* FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE e.id <= ? AND e.is_favorite = 0 AND f.keep_history = 0'; + $sql = 'DELETE e.* FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' + . 'WHERE e.id <= ? AND e.is_favorite = 0 AND f.keep_history = 0'; $stm = $this->bd->prepare ($sql); $values = array ( @@ -489,7 +437,8 @@ class EntryDAO extends Model_pdo { public function searchByGuid ($feed_id, $id) { // un guid est unique pour un flux donné - $sql = 'SELECT * FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid=?'; + $sql = 'SELECT id, guid, title, author, UNCOMPRESS(content_bin) AS content, link, date, is_read, is_favorite, id_feed, tags ' + . 'FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid=?'; $stm = $this->bd->prepare ($sql); $values = array ( @@ -509,7 +458,8 @@ class EntryDAO extends Model_pdo { } public function searchById ($id) { - $sql = 'SELECT * FROM `' . $this->prefix . 'entry` WHERE id=?'; + $sql = 'SELECT id, guid, title, author, UNCOMPRESS(content_bin) AS content, link, date, is_read, is_favorite, id_feed, tags ' + . 'FROM `' . $this->prefix . 'entry` WHERE id=?'; $stm = $this->bd->prepare ($sql); $values = array ($id); @@ -541,8 +491,9 @@ class EntryDAO extends Model_pdo { $order = ''; } - $sql = 'SELECT e.* FROM `' . $this->prefix . 'entry` e' - . ' INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id' . $where + $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 `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' . $where . ' ORDER BY e.id' . $order; if (empty($limitCount)) { @@ -558,16 +509,16 @@ class EntryDAO extends Model_pdo { return HelperEntry::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC)); } public function listEntries ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE priority > 0', $state, $order, $limitFromId, $limitCount); + return $this->listWhere ('WHERE priority > 0', $state, $order, $limitFromId, $limitCount); } public function listFavorites ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE is_favorite = 1', $state, $order, $limitFromId, $limitCount); + return $this->listWhere ('WHERE is_favorite = 1', $state, $order, $limitFromId, $limitCount); } public function listByCategory ($cat, $state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE category = ?', $state, $order, $limitFromId, $limitCount, array ($cat)); + return $this->listWhere ('WHERE category = ?', $state, $order, $limitFromId, $limitCount, array ($cat)); } public function listByFeed ($feed, $state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE id_feed = ?', $state, $order, $limitFromId, $limitCount, array ($feed)); + return $this->listWhere ('WHERE id_feed = ?', $state, $order, $limitFromId, $limitCount, array ($feed)); } public function listLastGuidsByFeed($id, $n) { @@ -648,7 +599,6 @@ class HelperEntry { $break_after = false; $next = ''; foreach ($listDAO as $key => $dao) { - $dao['content'] = unserialize (gzinflate (base64_decode ($dao['content']))); $dao['tags'] = preg_split('/[\s#]/', $dao['tags']); if (self::tagsMatchEntry ($dao) && diff --git a/app/models/Feed.php b/app/models/Feed.php index 2260f6fdf..954d1d45c 100644 --- a/app/models/Feed.php +++ b/app/models/Feed.php @@ -408,6 +408,16 @@ class FeedDAO extends Model_pdo { } 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; + }*/ + $sql = 'DELETE FROM `' . $this->prefix . 'feed` WHERE id=?'; $stm = $this->bd->prepare ($sql); @@ -422,6 +432,18 @@ class FeedDAO extends Model_pdo { } } 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; + }*/ + $sql = 'DELETE FROM `' . $this->prefix . 'feed` WHERE category=?'; $stm = $this->bd->prepare ($sql); diff --git a/app/models/RSSPaginator.php b/app/models/RSSPaginator.php index 60d2a06ac..39146f1ba 100644 --- a/app/models/RSSPaginator.php +++ b/app/models/RSSPaginator.php @@ -24,7 +24,7 @@ class RSSPaginator { } public function peek () { - return empty($this->items) ? null : $this->items[0]; + return isset($this->items[0]) ? $this->items[0] : null; } public function render ($view, $getteur) { diff --git a/public/install.php b/public/install.php index 764d152f5..0701c8c1f 100644 --- a/public/install.php +++ b/public/install.php @@ -18,7 +18,8 @@ define ('SQL_REQ_CAT', 'CREATE TABLE IF NOT EXISTS `%scategory` ( `color` char(7) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY (`name`) -- v0.7 -) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); +) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci +ENGINE = INNODB;'); define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( `id` SMALLINT NOT NULL AUTO_INCREMENT, -- v0.7 @@ -41,14 +42,15 @@ define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( INDEX (`name`), -- v0.7 INDEX (`priority`), -- v0.7 INDEX (`keep_history`) -- v0.7 -) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); +) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci +ENGINE = INNODB;'); define ('SQL_REQ_ENTRY', 'CREATE TABLE IF NOT EXISTS `%sentry` ( `id` bigint NOT NULL, -- v0.7 `guid` varchar(760) CHARACTER SET latin1 NOT NULL, -- Maximum for UNIQUE is 767B `title` varchar(255) NOT NULL, `author` varchar(255) NOT NULL, - `content` text NOT NULL, + `content_bin` blob NOT NULL, -- v0.7 `link` varchar(1023) CHARACTER SET latin1 NOT NULL, `date` int(11) NOT NULL, `is_read` boolean NOT NULL DEFAULT 0, @@ -59,8 +61,9 @@ define ('SQL_REQ_ENTRY', 'CREATE TABLE IF NOT EXISTS `%sentry` ( FOREIGN KEY (`id_feed`) REFERENCES `%sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY (`id_feed`,`guid`), -- v0.7 INDEX (`is_favorite`), -- v0.7 - INDEX (`is_read`) -- v0.7 -) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); + INDEX (`is_read`), -- v0.7 +) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci +ENGINE = INNODB;'); function writeLine ($f, $line) { -- cgit v1.2.3 From 1e077160fca3306a273ecae5a366fd756c32baee Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 30 Nov 2013 22:47:48 +0100 Subject: Optimisation recherche et pagination MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * Optimisation recherche SQL avec utilisation de HAVING plutôt que WHERE * Simplification et amélioration des performances en supprimant de RSSPaginator qui n'aidait plus vraiment et nécessitait plus de code et des copies de données. * Correction d'un bug dans le titre de la page introduit récemment, et simplification --- app/App_FrontController.php | 1 - app/controllers/indexController.php | 33 +++++++++++++--------------- app/layout/layout.phtml | 5 ++--- app/layout/nav_menu.phtml | 4 ++-- app/models/Entry.php | 11 +++++++--- app/models/RSSPaginator.php | 37 -------------------------------- app/views/helpers/pagination.phtml | 4 ++-- app/views/helpers/view/normal_view.phtml | 8 +++---- app/views/helpers/view/reader_view.phtml | 7 +++--- app/views/helpers/view/rss_view.phtml | 3 +-- 10 files changed, 36 insertions(+), 77 deletions(-) delete mode 100644 app/models/RSSPaginator.php (limited to 'app/models/RSSPaginator.php') diff --git a/app/App_FrontController.php b/app/App_FrontController.php index d701e2944..176677781 100644 --- a/app/App_FrontController.php +++ b/app/App_FrontController.php @@ -33,7 +33,6 @@ class App_FrontController extends FrontController { include (APP_PATH . '/models/Category.php'); include (APP_PATH . '/models/Feed.php'); include (APP_PATH . '/models/Entry.php'); - include (APP_PATH . '/models/RSSPaginator.php'); include (APP_PATH . '/models/Log_Model.php'); } diff --git a/app/controllers/indexController.php b/app/controllers/indexController.php index cf0e5024a..b3ae40847 100755 --- a/app/controllers/indexController.php +++ b/app/controllers/indexController.php @@ -46,8 +46,6 @@ class indexController extends ActionController { 'params' => $params ); - $this->view->rss_title = View::title(); - if ($output === 'rss') { // no layout for RSS output $this->view->_useLayout (false); @@ -67,19 +65,6 @@ class indexController extends ActionController { $this->view->get_c = ''; $this->view->get_f = ''; - // mise à jour des titres - $this->view->nb_not_read = HelperCategory::CountUnreads($this->view->cat_aside, 1); - if ($this->view->nb_not_read > 0) { - View::appendTitle (' (' . $this->view->nb_not_read . ')'); - } - View::prependTitle (' - '); - - $this->view->rss_title = $this->view->currentName . ' - ' . $this->view->rss_title; - View::prependTitle ( - $this->view->currentName . - ($this->nb_not_read_cat > 0 ? ' (' . $this->nb_not_read_cat . ')' : '') - ); - $get = Request::param ('get', 'a'); $getType = $get[0]; $getId = substr ($get, 2); @@ -92,6 +77,18 @@ class indexController extends ActionController { return; } + $this->view->nb_not_read = HelperCategory::CountUnreads($this->view->cat_aside, 1); + + // mise à jour des titres + if ($this->view->nb_not_read > 0) { + View::appendTitle (' (' . $this->view->nb_not_read . ')'); + } + View::prependTitle ( + $this->view->currentName . + ($this->nb_not_read_cat > 0 ? ' (' . $this->nb_not_read_cat . ')' : '') . + ' - ' + ); + // On récupère les différents éléments de filtrage $this->view->state = $state = Request::param ('state', $this->view->conf->defaultView ()); $filter = Request::param ('search', ''); @@ -138,13 +135,13 @@ class indexController extends ActionController { } if (count($entries) <= $nb) { - $next = ''; + $this->view->nextId = ''; } else { //We have more elements for pagination $lastEntry = array_pop($entries); - $next = $lastEntry->id(); + $this->view->nextId = $lastEntry->id(); } - $this->view->entryPaginator = new RSSPaginator ($entries, $next); + $this->view->entries = $entries; } catch (EntriesGetterException $e) { Minz_Log::record ($e->getMessage (), Minz_Log::NOTICE); Error::error ( diff --git a/app/layout/layout.phtml b/app/layout/layout.phtml index 9b502275c..6fc5c1cf0 100644 --- a/app/layout/layout.phtml +++ b/app/layout/layout.phtml @@ -10,10 +10,9 @@ renderHelper ('javascript_vars'); ?> //]]> entryPaginator) ? $this->entryPaginator->next() : ''; - if (!empty($next)) { + if (!empty($this->nextId)) { $params = Request::params (); - $params['next'] = $next; + $params['next'] = $this->nextId; ?> diff --git a/app/layout/nav_menu.phtml b/app/layout/nav_menu.phtml index 289fe6542..4b4945108 100644 --- a/app/layout/nav_menu.phtml +++ b/app/layout/nav_menu.phtml @@ -17,8 +17,6 @@ $string_mark = Translate::t ('mark_cat_read'); } $nextGet = $get; - $p = $this->entryPaginator->peek(); - $idMax = $p === null ? '0' : $p->id(); if (($this->conf->onread_jump_next () === 'yes') && (strlen ($get) > 2)) { $anotherUnreadId = ''; $foundCurrent = false; @@ -54,6 +52,8 @@ break; } } + $p = isset($this->entries[0]) ? $this->entries[0] : null; + $idMax = $p === null ? '0' : $p->id(); $markReadUrl = _url ('entry', 'read', 'is_read', 1, 'get', $get, 'nextGet', $nextGet, 'idMax', $idMax); Session::_param ('markReadUrl', $markReadUrl); ?> diff --git a/app/models/Entry.php b/app/models/Entry.php index 915fbccc8..ae8facf68 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -504,14 +504,18 @@ class EntryDAO extends Model_pdo { if ($firstId > 0) { $where .= 'AND e.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' '; } - $terms = explode(' ', trim($filter)); + $terms = array_unique(explode(' ', trim($filter))); sort($terms); //Put #tags first + $having = ''; foreach ($terms as $word) { if (!empty($word)) { if ($word[0] === '#' && isset($word[1])) { - $where .= 'AND tags LIKE "%' . $word . '%" '; + $having .= 'AND tags LIKE ? '; + $values[] = '%' . $word .'%'; } elseif (!empty($word)) { - $where .= 'AND (e.title LIKE "%' . $word . '%" OR UNCOMPRESS(e.content_bin) LIKE "%' . $word . '%") '; + $having .= 'AND (e.title LIKE ? OR content LIKE ?) '; + $values[] = '%' . $word .'%'; + $values[] = '%' . $word .'%'; } } } @@ -519,6 +523,7 @@ class EntryDAO extends Model_pdo { $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 `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE ' . $where + . (empty($having) ? '' : 'HAVING' . substr($having, 3)) . 'ORDER BY e.id ' . $order; if ($limit > 0) { diff --git a/app/models/RSSPaginator.php b/app/models/RSSPaginator.php deleted file mode 100644 index 39146f1ba..000000000 --- a/app/models/RSSPaginator.php +++ /dev/null @@ -1,37 +0,0 @@ -items = $items; - $this->next = $next; - } - - public function isEmpty () { - return empty ($this->items); - } - - public function items () { - return $this->items; - } - - public function next () { - return $this->next; - } - - public function peek () { - return isset($this->items[0]) ? $this->items[0] : null; - } - - public function render ($view, $getteur) { - $view = APP_PATH . '/views/helpers/'.$view; - - if (file_exists ($view)) { - include ($view); - } - } -} diff --git a/app/views/helpers/pagination.phtml b/app/views/helpers/pagination.phtml index 0b0d2f5ba..408cfca1b 100755 --- a/app/views/helpers/pagination.phtml +++ b/app/views/helpers/pagination.phtml @@ -8,8 +8,8 @@