diff options
Diffstat (limited to 'lib/Db/ItemMapper.php')
| -rw-r--r-- | lib/Db/ItemMapper.php | 586 |
1 files changed, 0 insertions, 586 deletions
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); - } -} |