From 8ee8a573f1f7e9cc45f9b3c46627d15670f14f3a Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 29 Sep 2018 20:47:17 +0200 Subject: Custom labels (#2027) * First draft of custom tags https://github.com/FreshRSS/FreshRSS/issues/928 https://github.com/FreshRSS/FreshRSS/issues/1367 * SMALLINT to BIGINT for id_entry And uppercase SQL types * Fix layout for unreads * Start UI menu * Change menu order * Clean database helpers https://github.com/FreshRSS/FreshRSS/pull/2027#discussion_r217971535 * Travis rules do not understand PostgreSQL constants Grrr * Tag controller + UI * Add column attributes to tags * Use only favicon for now, for label * Fix styling for different themes * Constant for maximum InnoDB index length in Unicode https://github.com/FreshRSS/FreshRSS/pull/2027#discussion_r219052200 (I would have personnally prefered keeping the readability of a real value instead of a constant, in this case of many SQL fields) * Use FreshRSS_Factory::createCategoryDao * Add view of all articles containing any tag * Fix search in tags * Mark as read tags * Partial auto-update unread tags * More auto update tag unreads * Add tag deletion * Do not purge tagged articles * Minor comment * Fix SQLite and UI bug * Google Reader API support for user tags Add SQL check that tag names must be distinct from category names * whitespace * Add missing API for EasyRSS * Compatibility SQLite Problematic parentheses * Add SQL DISTINCT for cases with multiple tags * Fix for PostgreSQL PostgreSQL needs some additional type hint to avoid "could not determine data type of parameter $1" http://www.postgresql-archive.org/Could-not-determine-data-type-of-parameter-1-tp2171092p2171094.html --- app/Models/EntryDAO.php | 101 ++++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 85 insertions(+), 16 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index f0e164995..a86de67d6 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -18,6 +18,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return 'hex(' . $x . ')'; } + //TODO: Move the database auto-updates to DatabaseDAO protected function addColumn($name) { Minz_Log::warning('FreshRSS_EntryDAO::addColumn: ' . $name); $hasTransaction = false; @@ -56,6 +57,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { private $triedUpdateToUtf8mb4 = false; + //TODO: Move the database auto-updates to DatabaseDAO protected function updateToUtf8mb4() { if ($this->triedUpdateToUtf8mb4) { return false; @@ -65,7 +67,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($db['type'] === 'mysql') { include_once(APP_PATH . '/SQL/install.sql.mysql.php'); if (defined('SQL_UPDATE_UTF8MB4')) { - Minz_Log::warning('Updating MySQL to UTF8MB4...'); + Minz_Log::warning('Updating MySQL to UTF8MB4...'); //v1.5.0 $hadTransaction = $this->bd->inTransaction(); if ($hadTransaction) { $this->bd->commit(); @@ -88,6 +90,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return false; } + //TODO: Move the database auto-updates to DatabaseDAO protected function createEntryTempTable() { $ok = false; $hadTransaction = $this->bd->inTransaction(); @@ -120,22 +123,28 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $ok; } + //TODO: Move the database auto-updates to DatabaseDAO protected function autoUpdateDb($errorInfo) { if (isset($errorInfo[0])) { - if ($errorInfo[0] === '42S22') { //ER_BAD_FIELD_ERROR + if ($errorInfo[0] === FreshRSS_DatabaseDAO::ER_BAD_FIELD_ERROR) { //autoAddColumn foreach (array('lastSeen', 'hash') as $column) { if (stripos($errorInfo[2], $column) !== false) { return $this->addColumn($column); } } - } elseif ($errorInfo[0] === '42S02' && stripos($errorInfo[2], 'entrytmp') !== false) { //ER_BAD_TABLE_ERROR - return $this->createEntryTempTable(); //v1.7 + } elseif ($errorInfo[0] === FreshRSS_DatabaseDAO::ER_BAD_TABLE_ERROR) { + if (stripos($errorInfo[2], 'tag') !== false) { + $tagDAO = FreshRSS_Factory::createTagDao(); + return $tagDAO->createTagTable(); //v1.12.0 + } elseif (stripos($errorInfo[2], 'entrytmp') !== false) { + return $this->createEntryTempTable(); //v1.7.0 + } } } if (isset($errorInfo[1])) { - if ($errorInfo[1] == '1366') { //ER_TRUNCATED_WRONG_VALUE_FOR_FIELD - return $this->updateToUtf8mb4(); + if ($errorInfo[1] == FreshRSS_DatabaseDAO::ER_TRUNCATED_WRONG_VALUE_FOR_FIELD) { + return $this->updateToUtf8mb4(); //v1.5.0 } } return false; @@ -560,11 +569,52 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $affected; } + /** + * Mark all the articles in a tag as read. + * @param integer $id tag ID, or empty for targetting any tag + * @param integer $idMax max article ID + * @return integer affected rows + */ + public function markReadTag($id = '', $idMax = 0, $filters = null, $state = 0, $is_read = true) { + FreshRSS_UserDAO::touch(); + if ($idMax == 0) { + $idMax = time() . '000000'; + Minz_Log::debug('Calling markReadTag(0) is deprecated!'); + } + + $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'entrytag` et ON et.id_entry = e.id ' + . 'SET e.is_read = ? ' + . 'WHERE ' + . ($id == '' ? '' : 'et.id_tag = ? AND ') + . 'e.is_read <> ? AND e.id <= ?'; + $values = array($is_read ? 1 : 0); + if ($id != '') { + $values[] = $id; + } + $values[] = $is_read ? 1 : 0; + $values[] = $idMax; + + list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filters, $state); + + $stm = $this->bd->prepare($sql . $search); + if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error markReadTag: ' . $info[2]); + return false; + } + $affected = $stm->rowCount(); + if (($affected > 0) && (!$this->updateCacheUnreads(false, false))) { + return false; + } + return $affected; + } + public function cleanOldEntries($id_feed, $date_min, $keep = 15) { //Remember to call updateCachedValue($id_feed) or updateCachedValues() just after $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 id NOT IN (SELECT id_entry FROM `' . $this->prefix . 'entrytag`) ' //Do not purge tagged entries . '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); @@ -770,24 +820,31 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $joinFeed = false; $values = array(); switch ($type) { - case 'a': + case 'a': //All PRIORITY_MAIN_STREAM $where .= 'f.priority > ' . FreshRSS_Feed::PRIORITY_NORMAL . ' '; break; - case 's': //Deprecated: use $state instead + case 'A': //All except PRIORITY_ARCHIVED + $where .= 'f.priority >= ' . FreshRSS_Feed::PRIORITY_NORMAL . ' '; + break; + case 's': //Starred. Deprecated: use $state instead $where .= 'f.priority >= ' . FreshRSS_Feed::PRIORITY_NORMAL . ' '; $where .= 'AND e.is_favorite=1 '; break; - case 'c': + case 'c': //Category $where .= 'f.priority >= ' . FreshRSS_Feed::PRIORITY_NORMAL . ' '; $where .= 'AND f.category=? '; $values[] = intval($id); break; - case 'f': + case 'f': //Feed $where .= 'e.id_feed=? '; $values[] = intval($id); break; - case 'A': - $where .= 'f.priority >= ' . FreshRSS_Feed::PRIORITY_NORMAL . ' '; + case 't': //Tag + $where .= 'et.id_tag=? '; + $values[] = intval($id); + break; + case 'T': //Any tag + $where .= '1=1 '; break; default: throw new FreshRSS_EntriesGetter_Exception('Bad type in Entry->listByType: [' . $type . ']!'); @@ -796,8 +853,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filters, $state, $order, $firstId, $date_min); return array(array_merge($values, $searchValues), - 'SELECT e.id FROM `' . $this->prefix . 'entry` e ' + 'SELECT ' + . ($type === 'T' ? 'DISTINCT ' : '') + . 'e.id FROM `' . $this->prefix . 'entry` e ' . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' + . ($type === 't' || $type === 'T' ? 'INNER JOIN `' . $this->prefix . 'entrytag` et ON et.id_entry = e.id ' : '') . 'WHERE ' . $where . $search . 'ORDER BY e.id ' . $order @@ -817,13 +877,22 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'ORDER BY e0.id ' . $order; $stm = $this->bd->prepare($sql); - $stm->execute($values); - return $stm; + if ($stm && $stm->execute($values)) { + return $stm; + } else { + $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error listWhereRaw: ' . $info[2]); + return false; + } } public function listWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filters = null, $date_min = 0) { $stm = $this->listWhereRaw($type, $id, $state, $order, $limit, $firstId, $filters, $date_min); - return self::daoToEntries($stm->fetchAll(PDO::FETCH_ASSOC)); + if ($stm) { + return self::daoToEntries($stm->fetchAll(PDO::FETCH_ASSOC)); + } else { + return false; + } } public function listByIds($ids, $order = 'DESC') { -- cgit v1.2.3 From c8b54ae807f583723748b5a8cebf9925fb288f9d Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 14 Oct 2018 13:48:59 +0200 Subject: Fix MySQL create table feeds (#2047) https://github.com/FreshRSS/FreshRSS/issues/2042 --- app/Controllers/userController.php | 2 +- app/Models/EntryDAO.php | 2 +- app/SQL/install.sql.mysql.php | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php index 75a4303d6..2f066e25f 100644 --- a/app/Controllers/userController.php +++ b/app/Controllers/userController.php @@ -166,7 +166,7 @@ class FreshRSS_user_Controller extends Minz_ActionController { $entryDAO = FreshRSS_Factory::createEntryDao($this->view->current_user); $this->view->nb_articles = $entryDAO->count(); - $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + $databaseDAO = FreshRSS_Factory::createDatabaseDAO($this->view->current_user); $this->view->size_user = $databaseDAO->size(); } } diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index a86de67d6..a01c2227b 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -992,7 +992,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $stm = $this->bd->prepare($sql); $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); - return $res[0]; + return isset($res[0]) ? $res[0] : 0; } public function countNotRead($minPriority = null) { $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e'; diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index eb454b1a3..222f7e8a7 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -18,7 +18,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `website` VARCHAR(255) CHARACTER SET latin1, `description` TEXT, `lastUpdate` INT(11) DEFAULT 0, -- Until year 2038 - `priority` TINYNT(2) NOT NULL DEFAULT 10, + `priority` TINYINT(2) NOT NULL DEFAULT 10, `pathEntries` VARCHAR(511) DEFAULT NULL, `httpAuth` VARCHAR(511) DEFAULT NULL, `error` BOOLEAN DEFAULT 0, -- cgit v1.2.3