Recherche côté SQL avec LIKE

Premier essai de recherche côté base de données (à améliorer)
https://github.com/marienfressinaud/FreshRSS/issues/204

Pour l'instant fait avec du LIKE et pas d'indexation texte complet.

* Suppression de EntriesGetter car le code est devenu plus simple grâce
au filtrage côté SQL
* Uniformisation de get_c à une lettre ('all' devient 'a','favoris'
devient 's' - pour "starred") pour simplifier le code
* low_to_high par DESC, high_to_low par ASC
* Réduction du nombre de créations de *DAO dans indexController
* Refactorisation de checkAndProcessType()

Pas encore trop testé...

3 files changed, 83 insertions, 286 deletions

diff --git a/app/models/EntriesGetter.php b/app/models/EntriesGetter.php
deleted file mode 100644
index ce026f252..000000000
--- a/app/models/EntriesGetter.php
+++ /dev/null
@@ -1,148 +0,0 @@
-<?php
-
-class EntriesGetter {
-	private $type = array (
-		'type' => 'all',
-		'id' => 'all'
-	);
-	private $state = 'all';
-	private $filter = array (
-		'words' => array (),
-		'tags' => array (),
-	);
-	private $order = 'high_to_low';
-	private $entries = array ();
-
-	private $nb = 1;
-	private $first = '';
-	private $next = '';
-
-	public function __construct ($type, $state, $filter, $order, $nb, $first = '') {
-		$this->_type ($type);
-		$this->_state ($state);
-		$this->_filter ($filter);
-		$this->_order ($order);
-		$this->nb = $nb;
-		$this->first = $first;
-	}
-
-	public function type () {
-		return $this->type;
-	}
-	public function state () {
-		return $this->state;
-	}
-	public function filter () {
-		return $this->filter;
-	}
-	public function order () {
-		return $this->order;
-	}
-	public function entries () {
-		return $this->entries;
-	}
-
-	public function _type ($value) {
-		if (!is_array ($value) ||
-		    !isset ($value['type']) ||
-		    !isset ($value['id'])) {
-			throw new EntriesGetterException ('Bad type line ' . __LINE__ . ' in file ' . __FILE__);
-		}
-
-		$type = $value['type'];
-		$id = $value['id'];
-
-		if ($type != 'all' && $type != 'favoris' && $type != 'public' && $type != 'c' && $type != 'f') {
-			throw new EntriesGetterException ('Bad type line ' . __LINE__ . ' in file ' . __FILE__);
-		}
-
-		if (($type == 'all' || $type == 'favoris' || $type == 'public') &&
-		    ($type != $id)) {
-			throw new EntriesGetterException ('Bad type line ' . __LINE__ . ' in file ' . __FILE__);
-		}
-
-		$this->type = $value;
-	}
-	public function _state ($value) {
-		if ($value != 'all' && $value != 'not_read' && $value != 'read') {
-			throw new EntriesGetterException ('Bad state line ' . __LINE__ . ' in file ' . __FILE__);
-		}
-
-		$this->state = $value;
-	}
-	public function _filter ($value) {
-		$value = trim ($value);
-		$terms = explode (' ', $value);
-
-		foreach ($terms as $word) {
-			if (!empty ($word) && $word[0] == '#' && isset ($word[1])) {
-				$tag = substr ($word, 1);
-				$this->filter['tags'][$tag] = $tag;
-			} elseif (!empty ($word)) {
-				$this->filter['words'][$word] = $word;
-			}
-		}
-	}
-	public function _order ($value) {
-		if ($value != 'high_to_low' && $value != 'low_to_high') {
-			throw new EntriesGetterException ('Bad order line ' . __LINE__ . ' in file ' . __FILE__);
-		}
-
-		$this->order = $value;
-	}
-
-	public function execute () {
-		$entryDAO = new EntryDAO ();
-
-		HelperEntry::$nb = $this->nb;	//TODO: Update: Now done in SQL
-		HelperEntry::$first = $this->first;	//TODO: Update: Now done in SQL
-		HelperEntry::$filter = $this->filter;
-
-		$sqlLimit = (empty ($this->filter['words']) && empty ($this->filter['tags'])) ? $this->nb : '';	//Disable SQL LIMIT optimisation during search	//TODO: Do better!
-
-		switch ($this->type['type']) {
-		case 'all':
-			list ($this->entries, $this->next) = $entryDAO->listEntries (
-				$this->state,
-				$this->order,
-				$this->first,
-				$sqlLimit
-			);
-			break;
-		case 'favoris':
-			list ($this->entries, $this->next) = $entryDAO->listFavorites (
-				$this->state,
-				$this->order,
-				$this->first,
-				$sqlLimit
-			);
-			break;
-		case 'c':
-			list ($this->entries, $this->next) = $entryDAO->listByCategory (
-				$this->type['id'],
-				$this->state,
-				$this->order,
-				$this->first,
-				$sqlLimit
-			);
-			break;
-		case 'f':
-			list ($this->entries, $this->next) = $entryDAO->listByFeed (
-				$this->type['id'],
-				$this->state,
-				$this->order,
-				$this->first,
-				$sqlLimit
-			);
-			break;
-		default:
-			throw new EntriesGetterException ('Bad type line ' . __LINE__ . ' in file ' . __FILE__);
-		}
-	}
-
-	public function getPaginator () {
-		$paginator = new RSSPaginator ($this->entries, $this->next);
-
-		return $paginator;
-	}
-}
diff --git a/app/models/Entry.php b/app/models/Entry.php
index 894985ece..915fbccc8 100755
--- a/app/models/Entry.php
+++ b/app/models/Entry.php
@@ -15,7 +15,7 @@ class Entry extends Model {
 	private $tags;
 
 	public function __construct ($feed = '', $guid = '', $title = '', $author = '', $content = '',
-	                             $link = '', $pubdate = 0, $is_read = false, $is_favorite = false) {
+	                             $link = '', $pubdate = 0, $is_read = false, $is_favorite = false, $tags = '') {
 		$this->_guid ($guid);
 		$this->_title ($title);
 		$this->_author ($author);
@@ -25,7 +25,7 @@ class Entry extends Model {
 		$this->_isRead ($is_read);
 		$this->_isFavorite ($is_favorite);
 		$this->_feed ($feed);
-		$this->_tags (array ());
+		$this->_tags (preg_split('/[\s#]/', $tags));
 	}
 
 	public function id () {
@@ -81,11 +81,7 @@ class Entry extends Model {
 	}
 	public function tags ($inString = false) {
 		if ($inString) {
-			if (!empty ($this->tags)) {
-				return '#' . implode(' #', $this->tags);
-			} else {
-				return '';
-			}
+			return empty ($this->tags) ? '' : '#' . implode(' #', $this->tags);
 		} else {
 			return $this->tags;
 		}
@@ -110,8 +106,8 @@ class Entry extends Model {
 		$this->link = $value;
 	}
 	public function _date ($value) {
-		if (is_int (intval ($value))) {
-			$this->date = $value;
+		if (is_int ($value)) {
+			$this->date = intval ($value);
 		} else {
 			$this->date = time ();
 		}
@@ -448,13 +444,8 @@ class EntryDAO extends Model_pdo {
 
 		$stm->execute ($values);
 		$res = $stm->fetchAll (PDO::FETCH_ASSOC);
-		list ($entry, $next) = HelperEntry::daoToEntry ($res);
-
-		if (isset ($entry[0])) {
-			return $entry[0];
-		} else {
-			return false;
-		}
+		$entries = HelperEntry::daoToEntry ($res);
+		return isset ($entries[0]) ? $entries[0] : false;
 	}
 
 	public function searchById ($id) {
@@ -466,60 +457,79 @@ class EntryDAO extends Model_pdo {
 
 		$stm->execute ($values);
 		$res = $stm->fetchAll (PDO::FETCH_ASSOC);
-		list ($entry, $next) = HelperEntry::daoToEntry ($res);
-
-		if (isset ($entry[0])) {
-			return $entry[0];
-		} else {
-			return false;
+		$entries = HelperEntry::daoToEntry ($res);
+		return isset ($entries[0]) ? $entries[0] : false;
+	}
+
+	public function listWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = -1, $filter = '') {
+		$where = '';
+		$values = array();
+		switch ($type) {
+			case 'a':
+				$where .= 'priority > 0 ';
+				break;
+			case 's':
+				$where .= 'is_favorite = 1 ';
+				break;
+			case 'c':
+				$where .= 'category = ? ';
+				$values[] = intval($id);
+				break;
+			case 'f':
+				$where .= 'id_feed = ? ';
+				$values[] = intval($id);
+				break;
+			default:
+				throw new EntriesGetterException ('Bad type in Entry->listByType: [' . $type . ']!');
 		}
-	}
-
-	private function listWhere ($where, $state, $order, $limitFromId = '', $limitCount = '', $values = array ()) {
-		if ($state === 'not_read') {
-			$where .= ' AND is_read = 0';
-		} elseif ($state === 'read') {
-			$where .= ' AND is_read = 1';
+		switch ($state) {
+			case 'all':
+				break;
+			case 'not_read':
+				$where .= 'AND is_read = 0 ';
+				break;
+			case 'read':
+				$where .= 'AND is_read = 1 ';
+				break;
+			default:
+				throw new EntriesGetterException ('Bad state in Entry->listByType: [' . $state . ']!');
 		}
-		if (!empty($limitFromId)) {
-			$where .= ' AND e.id ' . ($order === 'low_to_high' ? '<=' : '>=') . $limitFromId;
+		switch ($order) {
+			case 'DESC':
+			case 'ASC':
+				break;
+			default:
+				throw new EntriesGetterException ('Bad order in Entry->listByType: [' . $order . ']!');
 		}
-
-		if ($order === 'low_to_high') {
-			$order = ' DESC';
-		} else {
-			$order = '';
+		if ($firstId > 0) {
+			$where .= 'AND e.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' ';
+		}
+		$terms = explode(' ', trim($filter));
+		sort($terms);	//Put #tags first
+		foreach ($terms as $word) {
+			if (!empty($word)) {
+				if ($word[0] === '#' && isset($word[1])) {
+					$where .= 'AND tags LIKE "%' . $word . '%" ';
+				} elseif (!empty($word)) {
+					$where .= 'AND (e.title LIKE "%' . $word . '%" OR UNCOMPRESS(e.content_bin) LIKE "%' . $word . '%") ';
+				}
+			}
 		}
 
 		$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;
+		     . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE ' . $where
+		     . 'ORDER BY e.id ' . $order;
 
-		if (empty($limitCount)) {
-			$limitCount = 20000;	//TODO: FIXME: Hack temporaire en attendant la recherche côté base-de-données
+		if ($limit > 0) {
+			$sql .= ' LIMIT ' . $limit;	//TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
 		}
-		//if (!empty($limitCount)) {
-			$sql .= ' LIMIT ' . ($limitCount + 2);	//TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
-		//}
 
 		$stm = $this->bd->prepare ($sql);
 		$stm->execute ($values);
 
 		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);
-	}
-	public function listFavorites ($state, $order = 'high_to_low', $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));
-	}
-	public function listByFeed ($feed, $state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') {
-		return $this->listWhere ('WHERE id_feed = ?', $state, $order, $limitFromId, $limitCount, array ($feed));
-	}
 
 	public function listLastGuidsByFeed($id, $n) {
 		$sql = 'SELECT guid FROM `' . $this->prefix . 'entry` WHERE id_feed=? ORDER BY id DESC LIMIT ' . intval($n);
@@ -579,14 +589,6 @@ class EntryDAO extends Model_pdo {
 }
 
 class HelperEntry {
-	public static $nb = 1;
-	public static $first = '';
-
-	public static $filter = array (
-		'words' => array (),
-		'tags' => array (),
-	);
-
 	public static function daoToEntry ($listDAO) {
 		$list = array ();
 
@@ -594,80 +596,27 @@ class HelperEntry {
 			$listDAO = array ($listDAO);
 		}
 
-		$count = 0;
-		$first_is_found = false;
-		$break_after = false;
-		$next = '';
 		foreach ($listDAO as $key => $dao) {
-			$dao['tags'] = preg_split('/[\s#]/', $dao['tags']);
-
-			if (self::tagsMatchEntry ($dao) &&
-			    self::searchMatchEntry ($dao)) {
-				if ($break_after) {
-					$next = $dao['id'];
-					break;
-				}
-				if ($first_is_found || $dao['id'] == self::$first || self::$first == '') {
-					$list[$key] = self::createEntry ($dao);
-
-					$count++;
-					$first_is_found = true;	//TODO: Update: Now done in SQL
-				}
-				if ($count >= self::$nb) {
-					$break_after = true;
-				}
+			$entry = new Entry (
+				$dao['id_feed'],
+				$dao['guid'],
+				$dao['title'],
+				$dao['author'],
+				$dao['content'],
+				$dao['link'],
+				$dao['date'],
+				$dao['is_read'],
+				$dao['is_favorite'],
+				$dao['tags']
+			);
+			if (isset ($dao['id'])) {
+				$entry->_id ($dao['id']);
 			}
+			$list[] = $entry;
 		}
 
 		unset ($listDAO);
 
-		return array ($list, $next);
-	}
-
-	private static function createEntry ($dao) {
-		$entry = new Entry (
-			$dao['id_feed'],
-			$dao['guid'],
-			$dao['title'],
-			$dao['author'],
-			$dao['content'],
-			$dao['link'],
-			$dao['date'],
-			$dao['is_read'],
-			$dao['is_favorite']
-		);
-
-		$entry->_tags ($dao['tags']);
-
-		if (isset ($dao['id'])) {
-			$entry->_id ($dao['id']);
-		}
-
-		return $entry;
-	}
-
-	private static function tagsMatchEntry ($dao) {
-		$tags = self::$filter['tags'];
-		foreach ($tags as $tag) {
-			if (!in_array ($tag, $dao['tags'])) {
-				return false;
-			}
-		}
-
-		return true;
-	}
-	private static function searchMatchEntry ($dao) {
-		$words = self::$filter['words'];
-
-		foreach ($words as $word) {
-			$word = strtolower ($word);
-			if (strpos (strtolower ($dao['title']), $word) === false &&
-			    strpos (strtolower ($dao['content']), $word) === false &&
-			    strpos (strtolower ($dao['link']), $word) === false) {
-				return false;
-			}
-		}
-
-		return true;
+		return $list;
 	}
 }
diff --git a/app/models/RSSConfiguration.php b/app/models/RSSConfiguration.php
index 37f26b1dd..007c1c0f5 100755
--- a/app/models/RSSConfiguration.php
+++ b/app/models/RSSConfiguration.php
@@ -213,11 +213,7 @@ class RSSConfiguration extends Model {
 		}
 	}
 	public function _sortOrder ($value) {
-		if ($value == 'high_to_low') {
-			$this->sort_order = 'high_to_low';
-		} else {
-			$this->sort_order = 'low_to_high';
-		}
+		$this->sort_order = $value === 'ASC' ? 'ASC' : 'DESC';
 	}
 	public function _oldEntries ($value) {
 		if (is_int (intval ($value)) && $value > 0) {
@@ -334,7 +330,7 @@ class RSSConfigurationDAO extends Model_array {
 	public $display_posts = 'no';
 	public $onread_jump_next = 'yes';
 	public $lazyload = 'yes';
-	public $sort_order = 'low_to_high';
+	public $sort_order = 'DESC';
 	public $old_entries = 3;
 	public $shortcuts = array (
 		'mark_read' => 'r',