diff options
author | Bernhard Posselt <dev@bernhard-posselt.com> | 2016-04-28 17:08:25 +0200 |
---|---|---|
committer | Bernhard Posselt <dev@bernhard-posselt.com> | 2016-04-28 17:08:39 +0200 |
commit | 41851a17be5c9a42e8beffcfa07ed3a59d8a9f8a (patch) | |
tree | fd81a0d9264bd22905321b2060d5526e49dbbd7b /db/itemmapper.php | |
parent | ed9710700c09fbf63eef5c0876d13c7672922746 (diff) |
Rerun fingerprint migration
Diffstat (limited to 'db/itemmapper.php')
-rw-r--r-- | db/itemmapper.php | 186 |
1 files changed, 91 insertions, 95 deletions
diff --git a/db/itemmapper.php b/db/itemmapper.php index bbb4ca95d..220b4f153 100644 --- a/db/itemmapper.php +++ b/db/itemmapper.php @@ -19,36 +19,36 @@ use OCP\IDBConnection; class ItemMapper extends NewsMapper { - public function __construct(IDBConnection $db){ + public function __construct(IDBConnection $db) { parent::__construct($db, 'news_items', Item::class); } - private function makeSelectQuery($prependTo='', $oldestFirst=false, - $distinctFingerprint=false){ - if($oldestFirst) { + private function makeSelectQuery($prependTo = '', $oldestFirst = false, + $distinctFingerprint = false) { + 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` = 0 ' . - 'OR `folders`.`deleted_at` = 0 ' . - 'ORDER BY `items`.`id` ' . $ordering; + 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` = 0 ' . + 'OR `folders`.`deleted_at` = 0 ' . + 'ORDER BY `items`.`id` ' . $ordering; } private function makeSelectQueryStatus($prependTo, $status, - $oldestFirst=false, $search=[], - $distinctFingerprint=false) { - $status = (int) $status; + $oldestFirst = false, $search = [], + $distinctFingerprint = false) { + $status = (int)$status; $count = count($search); // WARNING: Potential SQL injection if you change this carelessly @@ -59,13 +59,13 @@ class ItemMapper extends NewsMapper { return $this->makeSelectQuery($sql, $oldestFirst, $distinctFingerprint); } - /** - * 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=[]) { + /** + * 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') . '%'; @@ -77,29 +77,29 @@ class ItemMapper extends NewsMapper { * @param string $userId * @return \OCA\News\Db\Item */ - public function find($id, $userId){ + public function find($id, $userId) { $sql = $this->makeSelectQuery('AND `items`.`id` = ? '); return $this->findEntity($sql, [$userId, $id]); } - public function starredCount($userId){ - $sql = 'SELECT COUNT(*) AS size FROM `*PREFIX*news_items` `items` '. + public function starredCount($userId) { + $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`.`status` & ' . StatusFlag::STARRED . ') = ' . - StatusFlag::STARRED . ')' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + 'AND `feeds`.`deleted_at` = 0 ' . + 'AND `feeds`.`user_id` = ? ' . + 'AND ((`items`.`status` & ' . StatusFlag::STARRED . ') = ' . + StatusFlag::STARRED . ')' . 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' . - 'ON `folders`.`id` = `feeds`.`folder_id` ' . + 'ON `folders`.`id` = `feeds`.`folder_id` ' . 'WHERE `feeds`.`folder_id` = 0 ' . - 'OR `folders`.`deleted_at` = 0'; + 'OR `folders`.`deleted_at` = 0'; $params = [$userId]; $result = $this->execute($sql, $params)->fetch(); - return (int) $result['size']; + return (int)$result['size']; } @@ -108,9 +108,9 @@ class ItemMapper extends NewsMapper { 'SET `status` = `status` & ? ' . ', `last_modified` = ? ' . 'WHERE `feed_id` IN (' . - 'SELECT `id` FROM `*PREFIX*news_feeds` ' . - 'WHERE `user_id` = ? ' . - ') '. + 'SELECT `id` FROM `*PREFIX*news_feeds` ' . + 'WHERE `user_id` = ? ' . + ') ' . 'AND `id` <= ?'; $params = [~StatusFlag::UNREAD, $time, $userId, $highestItemId]; $this->execute($sql, $params); @@ -122,10 +122,10 @@ class ItemMapper extends NewsMapper { 'SET `status` = `status` & ? ' . ', `last_modified` = ? ' . 'WHERE `feed_id` IN (' . - 'SELECT `id` FROM `*PREFIX*news_feeds` ' . - 'WHERE `folder_id` = ? ' . - 'AND `user_id` = ? ' . - ') '. + 'SELECT `id` FROM `*PREFIX*news_feeds` ' . + 'WHERE `folder_id` = ? ' . + 'AND `user_id` = ? ' . + ') ' . 'AND `id` <= ?'; $params = [~StatusFlag::UNREAD, $time, $folderId, $userId, $highestItemId]; @@ -133,16 +133,16 @@ class ItemMapper extends NewsMapper { } - public function readFeed($feedId, $highestItemId, $time, $userId){ + public function readFeed($feedId, $highestItemId, $time, $userId) { $sql = 'UPDATE `*PREFIX*news_items` ' . 'SET `status` = `status` & ? ' . ', `last_modified` = ? ' . - 'WHERE `feed_id` = ? ' . - 'AND `id` <= ? ' . - 'AND EXISTS (' . - 'SELECT * FROM `*PREFIX*news_feeds` ' . - 'WHERE `user_id` = ? ' . - 'AND `id` = ? ) '; + 'WHERE `feed_id` = ? ' . + 'AND `id` <= ? ' . + 'AND EXISTS (' . + 'SELECT * FROM `*PREFIX*news_feeds` ' . + 'WHERE `user_id` = ? ' . + 'AND `id` = ? ) '; $params = [~StatusFlag::UNREAD, $time, $feedId, $highestItemId, $userId, $feedId]; @@ -151,7 +151,7 @@ class ItemMapper extends NewsMapper { private function getOperator($oldestFirst) { - if($oldestFirst) { + if ($oldestFirst) { return '>'; } else { return '<'; @@ -159,7 +159,7 @@ class ItemMapper extends NewsMapper { } - public function findAllNew($updatedSince, $status, $userId){ + public function findAllNew($updatedSince, $status, $userId) { $sql = $this->makeSelectQueryStatus( 'AND `items`.`last_modified` >= ? ', $status); $params = [$userId, $updatedSince]; @@ -167,18 +167,18 @@ class ItemMapper extends NewsMapper { } - public function findAllNewFolder($id, $updatedSince, $status, $userId){ + public function findAllNewFolder($id, $updatedSince, $status, $userId) { $sql = 'AND `feeds`.`folder_id` = ? ' . - 'AND `items`.`last_modified` >= ? '; + 'AND `items`.`last_modified` >= ? '; $sql = $this->makeSelectQueryStatus($sql, $status); $params = [$userId, $id, $updatedSince]; return $this->findEntities($sql, $params); } - public function findAllNewFeed($id, $updatedSince, $status, $userId){ + public function findAllNewFeed($id, $updatedSince, $status, $userId) { $sql = 'AND `items`.`feed_id` = ? ' . - 'AND `items`.`last_modified` >= ? '; + 'AND `items`.`last_modified` >= ? '; $sql = $this->makeSelectQueryStatus($sql, $status); $params = [$userId, $id, $updatedSince]; return $this->findEntities($sql, $params); @@ -196,53 +196,53 @@ class ItemMapper extends NewsMapper { public function findAllFeed($id, $limit, $offset, $status, $oldestFirst, - $userId, $search=[]){ + $userId, $search = []) { $params = [$userId]; $params = array_merge($params, $this->buildLikeParameters($search)); $params[] = $id; $sql = 'AND `items`.`feed_id` = ? '; - if($offset !== 0){ + if ($offset !== 0) { $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; $params[] = $offset; } $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst, - $search); + $search); return $this->findEntitiesIgnoringNegativeLimit($sql, $params, $limit); } public function findAllFolder($id, $limit, $offset, $status, $oldestFirst, - $userId, $search=[]){ + $userId, $search = []) { $params = [$userId]; $params = array_merge($params, $this->buildLikeParameters($search)); $params[] = $id; $sql = 'AND `feeds`.`folder_id` = ? '; - if($offset !== 0){ + if ($offset !== 0) { $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; $params[] = $offset; } $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst, - $search); + $search); return $this->findEntitiesIgnoringNegativeLimit($sql, $params, $limit); } public function findAll($limit, $offset, $status, $oldestFirst, $userId, - $search=[]){ + $search = []) { $params = [$userId]; $params = array_merge($params, $this->buildLikeParameters($search)); $sql = ''; - if($offset !== 0){ + if ($offset !== 0) { $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; $params[] = $offset; } $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst, - $search); + $search); return $this->findEntitiesIgnoringNegativeLimit($sql, $params, $limit); } @@ -257,7 +257,7 @@ class ItemMapper extends NewsMapper { } - public function findByGuidHash($guidHash, $feedId, $userId){ + public function findByGuidHash($guidHash, $feedId, $userId) { $sql = $this->makeSelectQuery( 'AND `items`.`guid_hash` = ? ' . 'AND `feeds`.`id` = ? '); @@ -269,39 +269,39 @@ class ItemMapper extends NewsMapper { /** * 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 + * @param int $threshold the number of items that should be deleted */ - public function deleteReadOlderThanThreshold($threshold){ + public function deleteReadOlderThanThreshold($threshold) { $status = StatusFlag::STARRED | StatusFlag::UNREAD; $params = [$status, $threshold]; $sql = 'SELECT (COUNT(*) - `feeds`.`articles_per_update`) AS `size`, ' . - '`feeds`.`id` AS `feed_id`, `feeds`.`articles_per_update` ' . + '`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 NOT ((`items`.`status` & ?) > 0) ' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + 'AND NOT ((`items`.`status` & ?) > 0) ' . 'GROUP BY `feeds`.`id`, `feeds`.`articles_per_update` ' . 'HAVING COUNT(*) > ?'; $result = $this->execute($sql, $params); - while($row = $result->fetch()) { + while ($row = $result->fetch()) { - $size = (int) $row['size']; + $size = (int)$row['size']; $limit = $size - $threshold; - if($limit > 0) { + if ($limit > 0) { $params = [$status, $row['feed_id'], $limit]; $sql = 'DELETE FROM `*PREFIX*news_items` ' . - 'WHERE `id` IN (' . + 'WHERE `id` IN (' . 'SELECT `id` FROM `*PREFIX*news_items` ' . 'WHERE NOT ((`status` & ?) > 0) ' . 'AND `feed_id` = ? ' . 'ORDER BY `id` ASC ' . 'LIMIT ?' . - ')'; + ')'; $this->execute($sql, $params); } @@ -312,15 +312,15 @@ class ItemMapper extends NewsMapper { public function getNewestItemId($userId) { $sql = 'SELECT MAX(`items`.`id`) AS `max_id` ' . - 'FROM `*PREFIX*news_items` `items` '. + 'FROM `*PREFIX*news_items` `items` ' . 'JOIN `*PREFIX*news_feeds` `feeds` ' . - 'ON `feeds`.`id` = `items`.`feed_id` '. - 'AND `feeds`.`user_id` = ?'; + 'ON `feeds`.`id` = `items`.`feed_id` ' . + 'AND `feeds`.`user_id` = ?'; $params = [$userId]; $result = $this->findOneQuery($sql, $params); - return (int) $result['max_id']; + return (int)$result['max_id']; } @@ -331,9 +331,9 @@ class ItemMapper extends NewsMapper { public function deleteUser($userId) { $sql = 'DELETE FROM `*PREFIX*news_items` ' . 'WHERE `feed_id` IN (' . - 'SELECT `feeds`.`id` FROM `*PREFIX*news_feeds` `feeds` ' . - 'WHERE `feeds`.`user_id` = ?' . - ')'; + 'SELECT `feeds`.`id` FROM `*PREFIX*news_feeds` `feeds` ' . + 'WHERE `feeds`.`user_id` = ?' . + ')'; $this->execute($sql, [$userId]); } @@ -342,7 +342,7 @@ class ItemMapper extends NewsMapper { /** * Returns a list of ids and userid of all items */ - public function findAllIds($limit=null, $offset=null) { + public function findAllIds($limit = null, $offset = null) { $sql = 'SELECT `id` FROM `*PREFIX*news_items`'; return $this->execute($sql, [], $limit, $offset)->fetchAll(); } @@ -365,17 +365,13 @@ class ItemMapper extends NewsMapper { } } - private function updateSearchIndex(array $items=[]) { + private function updateSearchIndex(array $items = []) { foreach ($items as $row) { - try { - $sql = 'SELECT * FROM `*PREFIX*news_items` WHERE `id` = ?'; - $params = [$row['id']]; - $item = $this->findEntity($sql, $params); - $item->generateSearchIndex(); - $this->update($item); - } catch (Exception $e) { - continue; - } + $sql = 'SELECT * FROM `*PREFIX*news_items` WHERE `id` = ?'; + $params = [$row['id']]; + $item = $this->findEntity($sql, $params); + $item->generateSearchIndex(); + $this->update($item); } } @@ -395,7 +391,7 @@ class ItemMapper extends NewsMapper { WHERE `f`.`user_id` = ? )'; $params = [~StatusFlag::UNREAD, $lastModified, - $item->getFingerprint(), $userId]; + $item->getFingerprint(), $userId]; $this->execute($sql, $params); } else { $item->setLastModified($lastModified); |