From 8a6ad05ebacb6bf6c0f6afd0afe54a29a0a18ee9 Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Wed, 22 Oct 2014 18:33:46 +0200 Subject: Remove STATE_STRICT See https://github.com/marienfressinaud/FreshRSS/issues/634 --- app/Models/Entry.php | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'app/Models/Entry.php') diff --git a/app/Models/Entry.php b/app/Models/Entry.php index ee94d1110..346c98a92 100644 --- a/app/Models/Entry.php +++ b/app/Models/Entry.php @@ -1,12 +1,11 @@ Date: Sat, 4 Apr 2015 22:39:31 +0200 Subject: SQL: detection of updates, and preparation for better burge https://github.com/FreshRSS/FreshRSS/issues/798 https://github.com/FreshRSS/FreshRSS/issues/493 SQLite not yet tested. Only MySQL tested so far. --- app/Controllers/feedController.php | 98 ++++++++------ app/Controllers/importExportController.php | 3 +- app/Models/Entry.php | 16 +++ app/Models/EntryDAO.php | 198 +++++++++++++++++++++-------- app/Models/Feed.php | 1 + app/SQL/install.sql.mysql.php | 7 +- app/SQL/install.sql.sqlite.php | 7 +- lib/lib_rss.php | 2 +- 8 files changed, 231 insertions(+), 101 deletions(-) (limited to 'app/Models/Entry.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 6f544d834..08a0257a2 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -145,7 +145,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // Call the extension hook $name = $feed->name(); $feed = Minz_ExtensionManager::callHook('feed_before_insert', $feed); - if (is_null($feed)) { + if ($feed === null) { Minz_Request::bad(_t('feed_not_added', $name), $url_redirect); } @@ -181,7 +181,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // Use a shared statement and a transaction to improve a LOT the // performances. - $prepared_statement = $entryDAO->addEntryPrepare(); $feedDAO->beginTransaction(); foreach ($entries as $entry) { // Entries are added without any verification. @@ -190,13 +189,13 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $entry->_isRead($is_read); $entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry); - if (is_null($entry)) { + if ($entry === null) { // An extension has returned a null value, there is nothing to insert. continue; } $values = $entry->toArray(); - $entryDAO->addEntry($values, $prepared_statement); + $entryDAO->addEntry($values); } $feedDAO->updateLastUpdate($feed->id()); $feedDAO->commit(); @@ -307,7 +306,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $feed->load(false); } catch (FreshRSS_Feed_Exception $e) { Minz_Log::notice($e->getMessage()); - $feedDAO->updateLastUpdate($feed->id(), 1); + $feedDAO->updateLastUpdate($feed->id(), true); $feed->unlock(); continue; } @@ -323,50 +322,69 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // We want chronological order and SimplePie uses reverse order. $entries = array_reverse($feed->entries()); if (count($entries) > 0) { - // For this feed, check last n entry GUIDs already in database. - $existing_guids = array_fill_keys($entryDAO->listLastGuidsByFeed( - $feed->id(), count($entries) + 10 - ), 1); - $use_declared_date = empty($existing_guids); + $newGuids = array(); + foreach ($entries as $entry) { + $newGuids[] = $entry->guid(); + } + // 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. - $prepared_statement = $entryDAO->addEntryPrepare(); - $feedDAO->beginTransaction(); foreach ($entries as $entry) { $entry_date = $entry->date(true); - if (isset($existing_guids[$entry->guid()]) || - ($feed_history == 0 && $entry_date < $date_min)) { - // This entry already exists in DB or should not be added - // considering configuration and date. - continue; - } - - $id = uTimeString(); - if ($use_declared_date || $entry_date < $date_min) { - // Use declared date at first import. - $id = min(time(), $entry_date) . uSecString(); + if (isset($existingHashForGuids[$entry->guid()])) { + $existingHash = $existingHashForGuids[$entry->guid()]; + if (strcasecmp($existingHash, $entry->hash()) === 0 || $existingHash === '00000000000000000000000000000000') { + //This entry already exists and is unchanged. TODO: Remove the test with the zero'ed hash in FreshRSS v1.3 + $oldGuids[] = $entry->guid(); + } else { //This entry already exists but has been updated + Minz_Log::debug('Entry with GUID `' . $entry->guid() . '` updated in feed ' . $feed->id() . + ', old hash ' . $existingHash . ', new hash ' . $entry->hash()); + $entry->_isRead($is_read); //Reset is_read + if (!$entryDAO->hasTransaction()) { + $entryDAO->beginTransaction(); + } + $entryDAO->updateEntry($entry->toArray()); + } + } elseif ($feed_history == 0 && $entry_date < $date_min) { + // 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. + $id = min(time(), $entry_date) . uSecString(); + } + + $entry->_id($id); + $entry->_isRead($is_read); + + $entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry); + if ($entry === null) { + // An extension has returned a null value, there is nothing to insert. + continue; + } + + if (!$entryDAO->hasTransaction()) { + $entryDAO->beginTransaction(); + } + $entryDAO->addEntry($entry->toArray()); } - - $entry->_id($id); - $entry->_isRead($is_read); - - $entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry); - if (is_null($entry)) { - // An extension has returned a null value, there is nothing to insert. - continue; - } - - $values = $entry->toArray(); - $entryDAO->addEntry($values, $prepared_statement); } + $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) // Remove old entries once in 30. - if (!$feedDAO->hasTransaction()) { - $feedDAO->beginTransaction(); + 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, @@ -377,9 +395,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } } - $feedDAO->updateLastUpdate($feed->id(), 0, $feedDAO->hasTransaction()); - if ($feedDAO->hasTransaction()) { - $feedDAO->commit(); + $feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->hasTransaction()); + if ($entryDAO->hasTransaction()) { + $entryDAO->commit(); } if ($feed->url() !== $url) { diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index 589777b2a..26b163e43 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -361,7 +361,6 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { } // Then, articles are imported. - $prepared_statement = $this->entryDAO->addEntryPrepare(); $this->entryDAO->beginTransaction(); foreach ($article_object['items'] as $item) { if (!isset($article_to_feed[$item['id']])) { @@ -396,7 +395,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { } $values = $entry->toArray(); - $id = $this->entryDAO->addEntry($values, $prepared_statement); + $id = $this->entryDAO->addEntry($values); if (!$error && ($id === false)) { $error = true; diff --git a/app/Models/Entry.php b/app/Models/Entry.php index 346c98a92..6931c9f25 100644 --- a/app/Models/Entry.php +++ b/app/Models/Entry.php @@ -14,6 +14,7 @@ class FreshRSS_Entry extends Minz_Model { private $content; private $link; private $date; + private $hash = null; private $is_read; private $is_favorite; private $feed; @@ -88,6 +89,14 @@ class FreshRSS_Entry extends Minz_Model { } } + public function hash() { + if ($this->hash === null) { + //Do not include $this->date because it may be automatically generated when lacking + $this->hash = md5($this->link . $this->title . $this->author . $this->content . $this->tags(true)); + } + return $this->hash; + } + public function _id($value) { $this->id = $value; } @@ -95,18 +104,23 @@ class FreshRSS_Entry extends Minz_Model { $this->guid = $value; } public function _title($value) { + $this->hash = null; $this->title = $value; } public function _author($value) { + $this->hash = null; $this->author = $value; } public function _content($value) { + $this->hash = null; $this->content = $value; } public function _link($value) { + $this->hash = null; $this->link = $value; } public function _date($value) { + $this->hash = null; $value = intval($value); $this->date = $value > 1 ? $value : time(); } @@ -120,6 +134,7 @@ class FreshRSS_Entry extends Minz_Model { $this->feed = $value; } public function _tags($value) { + $this->hash = null; if (!is_array($value)) { $value = array($value); } @@ -182,6 +197,7 @@ class FreshRSS_Entry extends Minz_Model { 'content' => $this->content(), 'link' => $this->link(), 'date' => $this->date(true), + 'hash' => $this->hash(), 'is_read' => $this->isRead(), 'is_favorite' => $this->isFavorite(), 'id_feed' => $this->feed(), diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 9736d5cd3..5b4b85547 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -6,20 +6,57 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return parent::$sharedDbType !== 'sqlite'; } - public function addEntryPrepare() { - $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(?)' : '?') - . ', ?, ?, ?, ?, ?, ?)'; - return $this->bd->prepare($sql); + 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; + } + } + if ($hasTransaction) { + $this->bd->rollBack(); + } + } 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(); + } + } catch (Exception $e) { + Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage()); + if ($hasTransaction) { + $this->bd->rollBack(); + } + } + } + } + return false; } - public function addEntry($valuesTmp, $preparedStatement = null) { - $stm = $preparedStatement === null ? - FreshRSS_EntryDAO::addEntryPrepare() : - $preparedStatement; + private $addEntryPrepared = null; + + public function addEntry($valuesTmp) { + 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) ' + . 'VALUES(?, ?, ?, ?, ' + . ($this->isCompressed() ? 'COMPRESS(?)' : '?') + . ', ?, ?, ?, X?, ?, ?, ?, ?)'; + $this->addEntryPrepared = $this->bd->prepare($sql); + } $values = array( $valuesTmp['id'], @@ -29,55 +66,65 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $valuesTmp['content'], substr($valuesTmp['link'], 0, 1023), $valuesTmp['date'], + time(), + $valuesTmp['hash'], $valuesTmp['is_read'] ? 1 : 0, $valuesTmp['is_favorite'] ? 1 : 0, $valuesTmp['id_feed'], substr($valuesTmp['tags'], 0, 1023), ); - if ($stm && $stm->execute($values)) { + if ($this->addEntryPrepared && $this->addEntryPrepared->execute($values)) { return $this->bd->lastInsertId(); } else { - $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 + $info = $this->addEntryPrepared == null ? array(2 => 'syntax error') : $this->addEntryPrepared->errorInfo(); + if ($this->autoAddColumn($info)) { + 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']); - } /*else { - Minz_Log::debug('SQL error ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] - . ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']); - }*/ + } return false; } } - 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); + private $updateEntryPrepared = null; - $eDate = $entry->date(true); - - if ($feedHistory == -2) { - $feedHistory = $conf->keep_history_default; + public function updateEntry($valuesTmp) { + if ($this->updateEntryPrepared === null) { + $sql = 'UPDATE `' . $this->prefix . 'entry` ' + . 'SET title=?, author=?, ' + . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?') + . ', link=?, date=?, lastSeen=?, hash=X?, is_read=?, tags=? ' + . 'WHERE id_feed=? AND guid=?'; + $this->updateEntryPrepared = $this->bd->prepare($sql); } - if (!isset($existingGuids[$entry->guid()]) && - ($feedHistory != 0 || $eDate >= $date_min || $entry->isFavorite())) { - $values = $entry->toArray(); - - $useDeclaredDate = empty($existingGuids); - $values['id'] = ($useDeclaredDate || $eDate < $date_min) ? - min(time(), $eDate) . uSecString() : - uTimeString(); + $values = array( + substr($valuesTmp['title'], 0, 255), + substr($valuesTmp['author'], 0, 255), + $valuesTmp['content'], + substr($valuesTmp['link'], 0, 1023), + $valuesTmp['date'], + time(), + $valuesTmp['hash'], + $valuesTmp['is_read'] ? 1 : 0, + substr($valuesTmp['tags'], 0, 1023), + $valuesTmp['id_feed'], + substr($valuesTmp['guid'], 0, 760), + ); - return $this->addEntry($values); + if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) { + return $this->bd->lastInsertId(); + } else { + $info = $this->updateEntryPrepared == null ? array(2 => 'syntax error') : $this->updateEntryPrepared->errorInfo(); + if ($this->autoAddColumn($info)) { + return $this->updateEntry($valuesTmp); + } + Minz_Log::error('SQL error updateEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] + . ' while updating entry with GUID ' . $valuesTmp['guid'] . ' in feed ' . $valuesTmp['id_feed']); + return false; } - - // We don't return Entry object to avoid a research in DB - return -1; } /** @@ -94,6 +141,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (!is_array($ids)) { $ids = array($ids); } + if (count($ids) < 1) { + return 0; + } $sql = 'UPDATE `' . $this->prefix . 'entry` ' . 'SET is_favorite=? ' . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?)'; @@ -296,11 +346,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { * * If $idMax equals 0, a deprecated debug message is logged * - * @param integer $id feed ID + * @param integer $id_feed feed ID * @param integer $idMax fail safe article ID * @return integer affected rows */ - public function markReadFeed($id, $idMax = 0) { + public function markReadFeed($id_feed, $idMax = 0) { if ($idMax == 0) { $idMax = time() . '000000'; Minz_Log::debug('Calling markReadFeed(0) is deprecated!'); @@ -310,7 +360,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'UPDATE `' . $this->prefix . 'entry` ' . 'SET is_read=1 ' . 'WHERE id_feed=? AND is_read=0 AND id <= ?'; - $values = array($id, $idMax); + $values = array($id_feed, $idMax); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -324,7 +374,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'UPDATE `' . $this->prefix . 'feed` ' . 'SET cache_nbUnreads=cache_nbUnreads-' . $affected . ' WHERE id=?'; - $values = array($id); + $values = array($id_feed); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -338,7 +388,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $affected; } - public function searchByGuid($feed_id, $id) { + public function searchByGuid($id_feed, $guid) { // un guid est unique pour un flux donné $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') @@ -347,8 +397,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $stm = $this->bd->prepare($sql); $values = array( - $feed_id, - $id + $id_feed, + $guid, ); $stm->execute($values); @@ -519,12 +569,52 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { 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); + public function listHashForFeedGuids($id_feed, $guids) { + if (count($guids) < 1) { + return array(); + } + $sql = 'SELECT guid, hex(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); - $stm->execute($values); - return $stm->fetchAll(PDO::FETCH_COLUMN, 0); + $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(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 updateLastSeen($id_feed, $guids) { + 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); + $values = array_merge($values, $guids); + if ($stm && $stm->execute($values)) { + return $stm->rowCount(); + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoAddColumn($info)) { + return $this->updateLastSeen($id_feed, $guids); + } + Minz_Log::error('SQL error updateLastSeen: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] + . ' while updating feed ' . $id_feed); + return false; + } } public function countUnreadRead() { diff --git a/app/Models/Feed.php b/app/Models/Feed.php index 5ce03be5d..27c83ffd5 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -255,6 +255,7 @@ 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/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index cf0159199..afdd821b2 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `name` varchar(255) NOT NULL, `website` varchar(255) CHARACTER SET latin1, `description` text, - `lastUpdate` int(11) DEFAULT 0, + `lastUpdate` int(11) DEFAULT 0, -- Until year 2038 `priority` tinyint(2) NOT NULL DEFAULT 10, `pathEntries` varchar(511) DEFAULT NULL, `httpAuth` varchar(511) DEFAULT NULL, @@ -40,7 +40,9 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( `author` varchar(255), `content_bin` blob, -- v0.7 `link` varchar(1023) CHARACTER SET latin1 NOT NULL, - `date` int(11), + `date` int(11), -- Until year 2038 + `lastSeen` INT(11) NOT NULL, -- v1.2, Until year 2038 + `hash` BINARY(16), -- v1.2 `is_read` boolean NOT NULL DEFAULT 0, `is_favorite` boolean NOT NULL DEFAULT 0, `id_feed` SMALLINT, -- v0.7 @@ -50,6 +52,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( UNIQUE KEY (`id_feed`,`guid`), -- v0.7 INDEX (`is_favorite`), -- v0.7 INDEX (`is_read`) -- v0.7 + INDEX entry_lastSeen_index (`lastSeen`) -- v1.2 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB; diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 30bca2810..7517ead45 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -14,7 +14,7 @@ $SQL_CREATE_TABLES = array( `name` varchar(255) NOT NULL, `website` varchar(255), `description` text, - `lastUpdate` int(11) DEFAULT 0, + `lastUpdate` int(11) DEFAULT 0, -- Until year 2038 `priority` tinyint(2) NOT NULL DEFAULT 10, `pathEntries` varchar(511) DEFAULT NULL, `httpAuth` varchar(511) DEFAULT NULL, @@ -38,7 +38,9 @@ $SQL_CREATE_TABLES = array( `author` varchar(255), `content` text, `link` varchar(1023) NOT NULL, - `date` int(11), + `date` int(11), -- Until year 2038 + `lastSeen` INT(11) NOT NULL, -- v1.2, Until year 2038 + `hash` BINARY(16), -- v1.2 `is_read` boolean NOT NULL DEFAULT 0, `is_favorite` boolean NOT NULL DEFAULT 0, `id_feed` SMALLINT, @@ -50,6 +52,7 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `%1$sentry`(`is_favorite`);', 'CREATE INDEX IF NOT EXISTS entry_is_read_index ON `%1$sentry`(`is_read`);', +'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);', //v1.2 'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");', ); diff --git a/lib/lib_rss.php b/lib/lib_rss.php index e5fe73041..c6bdfde0e 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -38,7 +38,7 @@ function classAutoloader($class) { include(APP_PATH . '/Models/' . $components[1] . '.php'); return; case 3: //Controllers, Exceptions - @include(APP_PATH . '/' . $components[2] . 's/' . $components[1] . $components[2] . '.php'); + include(APP_PATH . '/' . $components[2] . 's/' . $components[1] . $components[2] . '.php'); return; } } elseif (strpos($class, 'Minz') === 0) { -- cgit v1.2.3 From 7f7de31c1dcb6599be5c5713f36b4bde1d03d47a Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 9 May 2015 13:07:54 +0200 Subject: SQL: update request for updated articles https://github.com/FreshRSS/FreshRSS/issues/798 --- app/Controllers/feedController.php | 2 +- app/Models/Entry.php | 4 ++-- app/Models/EntryDAO.php | 16 +++++++++++++--- 3 files changed, 16 insertions(+), 6 deletions(-) (limited to 'app/Models/Entry.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 08a0257a2..59c9174fb 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -343,7 +343,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } else { //This entry already exists but has been updated Minz_Log::debug('Entry with GUID `' . $entry->guid() . '` updated in feed ' . $feed->id() . ', old hash ' . $existingHash . ', new hash ' . $entry->hash()); - $entry->_isRead($is_read); //Reset is_read + $entry->_isRead(null); //Change is_read according to policy. //TODO: Implement option if (!$entryDAO->hasTransaction()) { $entryDAO->beginTransaction(); } diff --git a/app/Models/Entry.php b/app/Models/Entry.php index 6931c9f25..a562a963a 100644 --- a/app/Models/Entry.php +++ b/app/Models/Entry.php @@ -15,7 +15,7 @@ class FreshRSS_Entry extends Minz_Model { private $link; private $date; private $hash = null; - private $is_read; + private $is_read; //Nullable boolean private $is_favorite; private $feed; private $tags; @@ -125,7 +125,7 @@ class FreshRSS_Entry extends Minz_Model { $this->date = $value > 1 ? $value : time(); } public function _isRead($value) { - $this->is_read = $value; + $this->is_read = $value === null ? null : (bool)$value; } public function _isFavorite($value) { $this->is_favorite = $value; diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 5b4b85547..543b61573 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -91,11 +91,17 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { private $updateEntryPrepared = null; public function updateEntry($valuesTmp) { + if (!isset($valuesTmp['is_read'])) { + $valuesTmp['is_read'] = null; + } + if ($this->updateEntryPrepared === null) { $sql = 'UPDATE `' . $this->prefix . 'entry` ' . 'SET title=?, author=?, ' . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?') - . ', link=?, date=?, lastSeen=?, hash=X?, is_read=?, tags=? ' + . ', link=?, date=?, lastSeen=?, hash=X?, ' + . ($valuesTmp['is_read'] === null ? '' : 'is_read=?, ') + . 'tags=? ' . 'WHERE id_feed=? AND guid=?'; $this->updateEntryPrepared = $this->bd->prepare($sql); } @@ -108,11 +114,15 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $valuesTmp['date'], time(), $valuesTmp['hash'], - $valuesTmp['is_read'] ? 1 : 0, + ); + if ($valuesTmp['is_read'] !== null) { + $values[] = $valuesTmp['is_read'] ? 1 : 0; + } + $values = array_merge($values, array( substr($valuesTmp['tags'], 0, 1023), $valuesTmp['id_feed'], substr($valuesTmp['guid'], 0, 760), - ); + )); if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) { return $this->bd->lastInsertId(); -- cgit v1.2.3