diff options
Diffstat (limited to 'lib/Db')
-rw-r--r-- | lib/Db/FeedMapperV2.php | 24 | ||||
-rw-r--r-- | lib/Db/FeedType.php | 5 | ||||
-rw-r--r-- | lib/Db/FolderMapperV2.php | 26 | ||||
-rw-r--r-- | lib/Db/ItemMapper.php | 586 | ||||
-rw-r--r-- | lib/Db/ItemMapperV2.php | 366 | ||||
-rw-r--r-- | lib/Db/MapperFactory.php | 54 | ||||
-rw-r--r-- | lib/Db/Mysql/ItemMapper.php | 98 |
7 files changed, 408 insertions, 751 deletions
diff --git a/lib/Db/FeedMapperV2.php b/lib/Db/FeedMapperV2.php index b3d8879f3..5e346732c 100644 --- a/lib/Db/FeedMapperV2.php +++ b/lib/Db/FeedMapperV2.php @@ -153,4 +153,28 @@ class FeedMapperV2 extends NewsMapperV2 return $this->findEntities($builder); } + + /** + * @param string $userId + * @param int $id + * @param int|null $maxItemID + */ + public function read(string $userId, int $id, ?int $maxItemID = null): void + { + $builder = $this->db->getQueryBuilder(); + $builder->update(ItemMapperV2::TABLE_NAME, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->setValue('unread', 0) + ->andWhere('feeds.user_id = :userId') + ->andWhere('feeds.id = :feedId') + ->setParameter('userId', $userId) + ->setParameter('feedId', $id); + + if ($maxItemID !== null) { + $builder->andWhere('items.id =< :maxItemId') + ->setParameter('maxItemId', $maxItemID); + } + + $this->db->executeUpdate($builder->getSQL()); + } } diff --git a/lib/Db/FeedType.php b/lib/Db/FeedType.php index bf487992c..1ccd592a8 100644 --- a/lib/Db/FeedType.php +++ b/lib/Db/FeedType.php @@ -13,6 +13,11 @@ namespace OCA\News\Db; +/** + * Enum FeedType + * + * @package OCA\News\Db + */ class FeedType { const FEED = 0; diff --git a/lib/Db/FolderMapperV2.php b/lib/Db/FolderMapperV2.php index 85e07c07f..12fa26887 100644 --- a/lib/Db/FolderMapperV2.php +++ b/lib/Db/FolderMapperV2.php @@ -95,4 +95,30 @@ class FolderMapperV2 extends NewsMapperV2 return $this->findEntity($builder); } + + /** + * @param string $userId + * @param int $id + * @param int|null $maxItemID + * + * @return void + */ + public function read(string $userId, int $id, ?int $maxItemID = null): void + { + $builder = $this->db->getQueryBuilder(); + $builder->update(ItemMapperV2::TABLE_NAME, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->setValue('unread', 0) + ->andWhere('feeds.user_id = :userId') + ->andWhere('feeds.folder_id = :folderId') + ->setParameter('userId', $userId) + ->setParameter('folderId', $id); + + if ($maxItemID !== null) { + $builder->andWhere('items.id =< :maxItemId') + ->setParameter('maxItemId', $maxItemID); + } + + $this->db->executeUpdate($builder->getSQL()); + } } diff --git a/lib/Db/ItemMapper.php b/lib/Db/ItemMapper.php deleted file mode 100644 index 17f6638de..000000000 --- a/lib/Db/ItemMapper.php +++ /dev/null @@ -1,586 +0,0 @@ -<?php -/** - * Nextcloud - News - * - * This file is licensed under the Affero General Public License version 3 or - * later. See the COPYING file. - * - * @author Alessandro Cosentino <cosenal@gmail.com> - * @author Bernhard Posselt <dev@bernhard-posselt.com> - * @copyright 2012 Alessandro Cosentino - * @copyright 2012-2014 Bernhard Posselt - */ - -namespace OCA\News\Db; - -use OCA\News\Utility\Time; -use OCP\AppFramework\Db\DoesNotExistException; -use OCP\AppFramework\Db\Entity; -use OCP\AppFramework\Db\Mapper; -use OCP\AppFramework\Db\MultipleObjectsReturnedException; -use OCP\DB\QueryBuilder\IQueryBuilder; -use OCP\IDBConnection; - -/** - * Class LegacyItemMapper - * - * @package OCA\News\Db - * @deprecated use ItemMapper - */ -class ItemMapper extends Mapper -{ - - const TABLE_NAME = 'news_items'; - /** - * @var Time - */ - private $time; - - /** - * NewsMapper constructor. - * - * @param IDBConnection $db Database connection - * @param Time $time Time class - */ - public function __construct(IDBConnection $db, Time $time) - { - parent::__construct($db, static::TABLE_NAME, Item::class); - $this->time = $time; - } - - private function makeSelectQuery( - string $prependTo = '', - bool $oldestFirst = false, - bool $distinctFingerprint = false - ): string { - if ($oldestFirst) { - $ordering = 'ASC'; - } else { - $ordering = 'DESC'; - } - - return 'SELECT `items`.* FROM `*PREFIX*news_items` `items` ' . - 'JOIN `*PREFIX*news_feeds` `feeds` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - 'AND `feeds`.`deleted_at` = 0 ' . - 'AND `feeds`.`user_id` = ? ' . - $prependTo . - 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' . - 'ON `folders`.`id` = `feeds`.`folder_id` ' . - 'WHERE `feeds`.`folder_id` IS NULL ' . - 'OR `folders`.`deleted_at` = 0 ' . - 'ORDER BY `items`.`id` ' . $ordering; - } - - /** - * check if type is feed or all items should be shown - * - * @param bool $showAll - * @param int|null $type - * @return string - */ - private function buildStatusQueryPart($showAll, $type = null) - { - $sql = ''; - - if (isset($type) && $type === FeedType::STARRED) { - $sql = 'AND `items`.`starred` = '; - $sql .= $this->db->quote(true, IQueryBuilder::PARAM_BOOL) . ' '; - } elseif (!$showAll || $type === FeedType::UNREAD) { - $sql .= 'AND `items`.`unread` = '; - $sql .= $this->db->quote(true, IQueryBuilder::PARAM_BOOL) . ' '; - } - - return $sql; - } - - private function buildSearchQueryPart(array $search = []): string - { - return str_repeat('AND `items`.`search_index` LIKE ? ', count($search)); - } - - /** - * wrap and escape search parameters in a like statement - * - * @param string[] $search an array of strings that should be searched - * @return array with like parameters - */ - private function buildLikeParameters($search = []) - { - return array_map( - function ($param) { - $param = addcslashes($param, '\\_%'); - return '%' . mb_strtolower($param, 'UTF-8') . '%'; - }, - $search - ); - } - - /** - * @param int $id - * @param string $userId - * @return \OCA\News\Db\Item|Entity - */ - public function find(string $userId, int $id) - { - $sql = $this->makeSelectQuery('AND `items`.`id` = ? '); - return $this->findEntity($sql, [$userId, $id]); - } - - public function starredCount(string $userId): int - { - $sql = 'SELECT COUNT(*) AS size FROM `*PREFIX*news_items` `items` ' . - 'JOIN `*PREFIX*news_feeds` `feeds` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - 'AND `feeds`.`deleted_at` = 0 ' . - 'AND `feeds`.`user_id` = ? ' . - 'AND `items`.`starred` = ? ' . - 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' . - 'ON `folders`.`id` = `feeds`.`folder_id` ' . - 'WHERE `feeds`.`folder_id` IS NULL ' . - 'OR `folders`.`deleted_at` = 0'; - - $params = [$userId, true]; - - $result = $this->execute($sql, $params)->fetch(); - - return (int)$result['size']; - } - - - public function readAll(int $highestItemId, string $time, string $userId): void - { - $sql = 'UPDATE `*PREFIX*news_items` ' . - 'SET unread = ? ' . - ', `last_modified` = ? ' . - 'WHERE `feed_id` IN (' . - 'SELECT `id` FROM `*PREFIX*news_feeds` ' . - 'WHERE `user_id` = ? ' . - ') ' . - 'AND `id` <= ?'; - $params = [false, $time, $userId, $highestItemId]; - $this->execute($sql, $params); - } - - - public function readFolder(?int $folderId, int $highestItemId, string $time, string $userId): void - { - $folderWhere = is_null($folderId) ? 'IS' : '='; - $sql = 'UPDATE `*PREFIX*news_items` ' . - 'SET unread = ? ' . - ', `last_modified` = ? ' . - 'WHERE `feed_id` IN (' . - 'SELECT `id` FROM `*PREFIX*news_feeds` ' . - "WHERE `folder_id` ${folderWhere} ? " . - 'AND `user_id` = ? ' . - ') ' . - 'AND `id` <= ?'; - $params = [false, $time, $folderId, $userId, - $highestItemId]; - $this->execute($sql, $params); - } - - - public function readFeed(int $feedId, int $highestItemId, string $time, string $userId): void - { - $sql = 'UPDATE `*PREFIX*news_items` ' . - 'SET unread = ? ' . - ', `last_modified` = ? ' . - 'WHERE `feed_id` = ? ' . - 'AND `id` <= ? ' . - 'AND EXISTS (' . - 'SELECT * FROM `*PREFIX*news_feeds` ' . - 'WHERE `user_id` = ? ' . - 'AND `id` = ? ) '; - $params = [false, $time, $feedId, $highestItemId, - $userId, $feedId]; - - $this->execute($sql, $params); - } - - - private function getOperator(bool $oldestFirst): string - { - if ($oldestFirst) { - return '>'; - } else { - return '<'; - } - } - - - public function findAllNew(int $updatedSince, int $type, bool $showAll, string $userId): array - { - $sql = $this->buildStatusQueryPart($showAll, $type); - - $sql .= 'AND `items`.`last_modified` >= ? '; - $sql = $this->makeSelectQuery($sql); - $params = [$userId, $updatedSince]; - return $this->findEntities($sql, $params); - } - - - public function findAllNewFolder(?int $id, int $updatedSince, bool $showAll, string $userId): array - { - $sql = $this->buildStatusQueryPart($showAll); - - $folderWhere = is_null($id) ? 'IS' : '='; - $sql .= "AND `feeds`.`folder_id` ${folderWhere} ? " . - 'AND `items`.`last_modified` >= ? '; - $sql = $this->makeSelectQuery($sql); - $params = [$userId, $id, $updatedSince]; - return $this->findEntities($sql, $params); - } - - - public function findAllNewFeed(?int $id, int $updatedSince, bool $showAll, string $userId): array - { - $sql = $this->buildStatusQueryPart($showAll); - - $sql .= 'AND `items`.`feed_id` = ? ' . - 'AND `items`.`last_modified` >= ? '; - $sql = $this->makeSelectQuery($sql); - $params = [$userId, $id, $updatedSince]; - return $this->findEntities($sql, $params); - } - - - /** - * @param (int|mixed|null)[] $params - */ - private function findEntitiesIgnoringNegativeLimit(string $sql, array $params, int $limit): array - { - // ignore limit if negative to offer a way to return all feeds - if ($limit >= 0) { - return $this->findEntities($sql, $params, $limit); - } else { - return $this->findEntities($sql, $params); - } - } - - - public function findAllFeed( - ?int $id, - int $limit, - int $offset, - bool $showAll, - bool $oldestFirst, - string $userId, - array $search = [] - ): array { - $params = [$userId]; - $params = array_merge($params, $this->buildLikeParameters($search)); - $params[] = $id; - - $sql = $this->buildStatusQueryPart($showAll); - $sql .= $this->buildSearchQueryPart($search); - - $sql .= 'AND `items`.`feed_id` = ? '; - if ($offset !== 0) { - $sql .= 'AND `items`.`id` ' . - $this->getOperator($oldestFirst) . ' ? '; - $params[] = $offset; - } - $sql = $this->makeSelectQuery($sql, $oldestFirst); - return $this->findEntitiesIgnoringNegativeLimit($sql, $params, $limit); - } - - - public function findAllFolder( - ?int $id, - int $limit, - int $offset, - bool $showAll, - bool $oldestFirst, - string $userId, - array $search = [] - ): array { - $params = [$userId]; - $params = array_merge($params, $this->buildLikeParameters($search)); - $params[] = $id; - - $sql = $this->buildStatusQueryPart($showAll); - $sql .= $this->buildSearchQueryPart($search); - - $folderWhere = is_null($id) ? 'IS' : '='; - $sql .= "AND `feeds`.`folder_id` ${folderWhere} ? "; - if ($offset !== 0) { - $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; - $params[] = $offset; - } - $sql = $this->makeSelectQuery($sql, $oldestFirst); - return $this->findEntitiesIgnoringNegativeLimit($sql, $params, $limit); - } - - - /** - * @param string[] $search - */ - public function findAllItems( - int $limit, - int $offset, - int $type, - bool $showAll, - bool $oldestFirst, - string $userId, - array $search = [] - ): array { - $params = [$userId]; - $params = array_merge($params, $this->buildLikeParameters($search)); - $sql = $this->buildStatusQueryPart($showAll, $type); - $sql .= $this->buildSearchQueryPart($search); - - if ($offset !== 0) { - $sql .= 'AND `items`.`id` ' . - $this->getOperator($oldestFirst) . ' ? '; - $params[] = $offset; - } - - $sql = $this->makeSelectQuery($sql, $oldestFirst); - - return $this->findEntitiesIgnoringNegativeLimit($sql, $params, $limit); - } - - - public function findAllUnreadOrStarred(string $userId): array - { - $params = [$userId, true, true]; - $sql = 'AND (`items`.`unread` = ? OR `items`.`starred` = ?) '; - $sql = $this->makeSelectQuery($sql); - return $this->findEntities($sql, $params); - } - - /** - * @param $guidHash - * @param $feedId - * @param $userId - * - * @return Entity|Item - * @throws DoesNotExistException - * @throws MultipleObjectsReturnedException - */ - public function findByGuidHash($guidHash, $feedId, $userId) - { - $sql = $this->makeSelectQuery( - 'AND `items`.`guid_hash` = ? ' . - 'AND `feeds`.`id` = ? ' - ); - - return $this->findEntity($sql, [$userId, $guidHash, $feedId]); - } - - - /** - * Delete all items for feeds that have over $threshold unread and not - * starred items - * - * @param int $threshold the number of items that should be deleted - * - * @return void - */ - public function deleteReadOlderThanThreshold($threshold) - { - $params = [false, false, $threshold]; - - $sql = 'SELECT (COUNT(*) - `feeds`.`articles_per_update`) AS `size`, ' . - '`feeds`.`id` AS `feed_id`, `feeds`.`articles_per_update` ' . - 'FROM `*PREFIX*news_items` `items` ' . - 'JOIN `*PREFIX*news_feeds` `feeds` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - 'AND `items`.`unread` = ? ' . - 'AND `items`.`starred` = ? ' . - 'GROUP BY `feeds`.`id`, `feeds`.`articles_per_update` ' . - 'HAVING COUNT(*) > ?'; - - $result = $this->execute($sql, $params); - - while ($row = $result->fetch()) { - $size = (int)$row['size']; - $limit = $size - $threshold; - $feed_id = $row['feed_id']; - - if ($limit > 0) { - $params = [false, false, $feed_id, $limit]; - $sql = 'SELECT `id` FROM `*PREFIX*news_items` ' . - 'WHERE `unread` = ? ' . - 'AND `starred` = ? ' . - 'AND `feed_id` = ? ' . - 'ORDER BY `id` ASC ' . - 'LIMIT 1 ' . - 'OFFSET ? '; - } - $limit_result = $this->execute($sql, $params); - if ($limit_row = $limit_result->fetch()) { - $limit_id = (int)$limit_row['id']; - $params = [false, false, $feed_id, $limit_id]; - $sql = 'DELETE FROM `*PREFIX*news_items` ' . - 'WHERE `unread` = ? ' . - 'AND `starred` = ? ' . - 'AND `feed_id` = ? ' . - 'AND `id` < ? '; - $this->execute($sql, $params); - } - } - } - - - public function getNewestItemId(string $userId): int - { - $sql = 'SELECT MAX(`items`.`id`) AS `max_id` ' . - 'FROM `*PREFIX*news_items` `items` ' . - 'JOIN `*PREFIX*news_feeds` `feeds` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - 'AND `feeds`.`user_id` = ?'; - $params = [$userId]; - - $result = $this->findOneQuery($sql, $params); - - return (int)$result['max_id']; - } - - - /** - * Returns a list of ids and userid of all items - * - * @param int|null $limit - * @param int|null $offset - * - * @return array|false - */ - public function findAllIds(?int $limit = null, ?int $offset = null) - { - $sql = 'SELECT `id` FROM `*PREFIX*news_items`'; - return $this->execute($sql, [], $limit, $offset)->fetchAll(); - } - - /** - * Update search indices of all items - * - * @return void - */ - public function updateSearchIndices(): void - { - // update indices in steps to prevent memory issues on larger systems - $step = 1000; // update 1000 items at a time - $itemCount = 1; - $offset = 0; - - // stop condition if there are no previously fetched items - while ($itemCount > 0) { - $items = $this->findAllIds($step, $offset); - $itemCount = count($items); - $this->updateSearchIndex($items); - $offset += $step; - } - } - - private function updateSearchIndex(array $items = []): void - { - foreach ($items as $row) { - $sql = 'SELECT * FROM `*PREFIX*news_items` WHERE `id` = ?'; - $params = [$row['id']]; - $item = $this->findEntity($sql, $params); - $item->generateSearchIndex(); - $this->update($item); - } - } - - /** - * @return void - */ - public function readItem(int $itemId, bool $isRead, string $lastModified, string $userId) - { - $item = $this->find($userId, $itemId); - - // reading an item should set all of the same items as read, whereas - // marking an item as unread should only mark the selected instance - // as unread - if ($isRead) { - $sql = 'UPDATE `*PREFIX*news_items` - SET `unread` = ?, - `last_modified` = ? - WHERE `fingerprint` = ? - AND `feed_id` IN ( - SELECT `f`.`id` FROM `*PREFIX*news_feeds` AS `f` - WHERE `f`.`user_id` = ? - )'; - $params = [false, $lastModified, $item->getFingerprint(), $userId]; - $this->execute($sql, $params); - } else { - $item->setLastModified($lastModified); - $item->setUnread(true); - $this->update($item); - } - } - - public function update(Entity $entity): Entity - { - $entity->setLastModified($this->time->getMicroTime()); - return parent::update($entity); - } - - public function insert(Entity $entity): Entity - { - $entity->setLastModified($this->time->getMicroTime()); - return parent::insert($entity); - } - - /** - * Remove deleted items. - * - * @return void - */ - public function purgeDeleted(): void - { - $builder = $this->db->getQueryBuilder(); - $builder->delete($this->tableName) - ->where('deleted_at != 0') - ->execute(); - } - /** - * Performs a SELECT query with all arguments appended to the WHERE clause - * The SELECT will be performed on the current table and takes the entity - * that is related for transforming the properties into column names - * - * Important: This method does not filter marked as deleted rows! - * - * @param array $search an assoc array from property to filter value - * @param int|null $limit Output limit - * @param int|null $offset Output offset - * - * @depreacted Legacy function - * - * @return Entity[] - */ - public function where(array $search = [], ?int $limit = null, ?int $offset = null) - { - $entity = new $this->entityClass(); - - // turn keys into sql query filter, e.g. feedId -> feed_id = :feedId - $filter = array_map( - function ($property) use ($entity) { - // check if the property actually exists on the entity to prevent - // accidental Sql injection - if (!property_exists($entity, $property)) { - $msg = 'Property ' . $property . ' does not exist on ' - . $this->entityClass; - throw new \BadFunctionCallException($msg); - } - - $column = $entity->propertyToColumn($property); - return $column . ' = :' . $property; - }, - array_keys($search) - ); - - $andStatement = implode(' AND ', $filter); - - $sql = 'SELECT * FROM `' . $this->getTableName() . '`'; - - if (count($search) > 0) { - $sql .= 'WHERE ' . $andStatement; - } - - return $this->findEntities($sql, $search, $limit, $offset); - } -} diff --git a/lib/Db/ItemMapperV2.php b/lib/Db/ItemMapperV2.php index f51b7af4d..8cdf9c430 100644 --- a/lib/Db/ItemMapperV2.php +++ b/lib/Db/ItemMapperV2.php @@ -12,6 +12,7 @@ namespace OCA\News\Db; +use OCA\News\Service\Exceptions\ServiceValidationException; use Doctrine\DBAL\FetchMode; use OCA\News\Utility\Time; use OCP\AppFramework\Db\DoesNotExistException; @@ -55,12 +56,11 @@ class ItemMapperV2 extends NewsMapperV2 ->from($this->tableName, 'items') ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') ->where('feeds.user_id = :user_id') - ->andWhere('deleted_at = 0') + ->andWhere('feeds.deleted_at = 0') ->setParameter('user_id', $userId, IQueryBuilder::PARAM_STR); foreach ($params as $key => $value) { - $builder->andWhere("${key} = :${key}") - ->setParameter($key, $value); + $builder->andWhere("${key} = " . $builder->createNamedParameter($value)); } return $this->findEntities($builder); @@ -74,13 +74,17 @@ class ItemMapperV2 extends NewsMapperV2 public function findAll(): array { $builder = $this->db->getQueryBuilder(); - $builder->addSelect('*') + $builder->select('*') ->from($this->tableName) - ->andWhere('deleted_at = 0'); + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->andWhere('feeds.deleted_at = 0'); return $this->findEntities($builder); } + /** + * @inheritDoc + */ public function findFromUser(string $userId, int $id): Entity { $builder = $this->db->getQueryBuilder(); @@ -89,9 +93,9 @@ class ItemMapperV2 extends NewsMapperV2 ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') ->where('feeds.user_id = :user_id') ->andWhere('items.id = :item_id') - ->andWhere('deleted_at = 0') + ->andWhere('feeds.deleted_at = 0') ->setParameter('user_id', $userId, IQueryBuilder::PARAM_STR) - ->setParameter('item_id', $id, IQueryBuilder::PARAM_STR); + ->setParameter('item_id', $id, IQueryBuilder::PARAM_INT); return $this->findEntity($builder); } @@ -102,15 +106,15 @@ class ItemMapperV2 extends NewsMapperV2 * @param int $feedId ID of the feed * @param string $guidHash hash to find with * - * @return Item + * @return Item|Entity * * @throws DoesNotExistException * @throws MultipleObjectsReturnedException */ - public function findByGuidHash(int $feedId, string $guidHash): Item + public function findByGuidHash(int $feedId, string $guidHash): Entity { $builder = $this->db->getQueryBuilder(); - $builder->addSelect('*') + $builder->select('*') ->from($this->tableName) ->andWhere('feed_id = :feed_id') ->andWhere('guid_hash = :guid_hash') @@ -121,6 +125,34 @@ class ItemMapperV2 extends NewsMapperV2 } /** + * Find a user item by a GUID hash. + * + * @param string $userId + * @param int $feedId ID of the feed + * @param string $guidHash hash to find with + * + * @return Item|Entity + * + * @throws DoesNotExistException + * @throws MultipleObjectsReturnedException + */ + public function findForUserByGuidHash(string $userId, int $feedId, string $guidHash): Item + { + $builder = $this->db->getQueryBuilder(); + $builder->select('items.*') + ->from($this->tableName, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->andWhere('feeds.user_id = :user_id') + ->andWhere('feeds.id = :feed_id') + ->andWhere('items.guid_hash = :guid_hash') + ->setParameter('user_id', $userId, IQueryBuilder::PARAM_STR) + ->setParameter('feed_id', $feedId, IQueryBuilder::PARAM_INT) + ->setParameter('guid_hash', $guidHash, IQueryBuilder::PARAM_STR); + + return $this->findEntity($builder); + } + + /** * @param int $feedId * * @return array @@ -128,10 +160,10 @@ class ItemMapperV2 extends NewsMapperV2 public function findAllForFeed(int $feedId): array { $builder = $this->db->getQueryBuilder(); - $builder->addSelect('*') + $builder->select('*') ->from($this->tableName) - ->andWhere('feed_id = :feed_id') - ->setParameter('feed_id', $feedId, IQueryBuilder::PARAM_INT); + ->where('feed_id = :feed_identifier') + ->setParameter('feed_identifier', $feedId, IQueryBuilder::PARAM_INT); return $this->findEntities($builder); } @@ -214,4 +246,312 @@ class ItemMapperV2 extends NewsMapperV2 { //NO-OP } + + + /** + * @param string $userId + * @param int $maxItemId + * + * @TODO: Update this for NC 21 + */ + public function readAll(string $userId, int $maxItemId): void + { + $builder = $this->db->getQueryBuilder(); + + $builder->update($this->tableName, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->setValue('unread', 0) + ->andWhere('items.id =< :maxItemId') + ->andWhere('feeds.user_id = :userId') + ->setParameter('maxItemId', $maxItemId) + ->setParameter('userId', $userId); + + $this->db->executeUpdate($builder->getSQL()); + } + + /** + * @param string $userId + * + * @return Entity|Item + * + * @throws DoesNotExistException The item is not found + * @throws MultipleObjectsReturnedException Multiple items found + */ + public function newest(string $userId): Entity + { + $builder = $this->db->getQueryBuilder(); + + $builder->select('items.*') + ->from($this->tableName, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->where('feeds.user_id = :userId') + ->setParameter('userId', $userId) + ->orderBy('items.updated_date', 'DESC') + ->addOrderBy('items.id', 'DESC') + ->setMaxResults(1); + + return $this->findEntity($builder); + } + + /** + * @param string $userId + * @param int $feedId + * @param int $updatedSince + * @param bool $hideRead + * + * @return Item[] + */ + public function findAllInFeedAfter( + string $userId, + int $feedId, + int $updatedSince, + bool $hideRead + ): array { + $builder = $this->db->getQueryBuilder(); + + $builder->select('items.*') + ->from($this->tableName, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->andWhere('items.updated_date >= :updatedSince') + ->andWhere('feeds.user_id = :userId') + ->andWhere('feeds.id = :feedId') + ->setParameters([ + 'updatedSince' => $updatedSince, + 'feedId' => $feedId, + 'userId'=> $userId, + ]) + ->orderBy('items.updated_date', 'DESC') + ->addOrderBy('items.id', 'DESC'); + + if ($hideRead === true) { + $builder->andWhere('items.unread = 1'); + } + + return $this->findEntities($builder); + } + + /** + * @param string $userId + * @param int|null $folderId + * @param int $updatedSince + * @param bool $hideRead + * + * @return Item[] + */ + public function findAllInFolderAfter( + string $userId, + ?int $folderId, + int $updatedSince, + bool $hideRead + ): array { + $builder = $this->db->getQueryBuilder(); + + $builder->select('items.*') + ->from($this->tableName, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->innerJoin('feeds', FolderMapperV2::TABLE_NAME, 'folders', 'feeds.folder_id = folders.id') + ->andWhere('items.updated_date >= :updatedSince') + ->andWhere('feeds.user_id = :userId') + ->andWhere('folders.id = :folderId') + ->setParameters(['updatedSince' => $updatedSince, 'folderId' => $folderId, 'userId' => $userId]) + ->orderBy('items.updated_date', 'DESC') + ->addOrderBy('items.id', 'DESC'); + + if ($hideRead === true) { + $builder->andWhere('items.unread = 1'); + } + + return $this->findEntities($builder); + } + + /** + * @param string $userId + * @param int $updatedSince + * @param int $feedType + * + * @return Item[]|Entity[] + * @throws ServiceValidationException + */ + public function findAllAfter(string $userId, int $feedType, int $updatedSince): array + { + $builder = $this->db->getQueryBuilder(); + + $builder->select('items.*') + ->from($this->tableName, 'items') + ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id') + ->andWhere('items.updated_date >= :updatedSince') + ->andWhere('feeds.user_id = :userId') + ->setParameters(['updatedSince' => $updatedSince, 'userId' => $userId]) + ->orderBy('items.updated_date', 'DESC') + ->addOrderBy('items.id', 'DESC'); + + switch ($feedType) { + case FeedType::STARRED: + $builder->andWhere('items.starred = 1'); + break; + case FeedType::UNREAD: + $builder->andWhere('items.unread = 1'); + break; + default: + throw new ServiceValidati |