From 42d69a95f3276a2d6089ca68f635c4e2f6aa7a23 Mon Sep 17 00:00:00 2001 From: Bernhard Posselt Date: Tue, 21 Oct 2014 16:45:36 +0200 Subject: convert tabs indention to indention with 4 spaces because of mixing of both variants in code and better readability on github and websites because you cant set the indention width there and 8 spaces will be used for a tab --- db/entityjsonserializer.php | 14 +- db/feed.php | 176 +++++++-------- db/feedmapper.php | 304 ++++++++++++------------- db/feedtype.php | 10 +- db/folder.php | 60 ++--- db/foldermapper.php | 168 +++++++------- db/iapi.php | 2 +- db/item.php | 342 ++++++++++++++-------------- db/itemmapper.php | 538 ++++++++++++++++++++++---------------------- db/mapperfactory.php | 34 +-- db/newsmapper.php | 2 +- db/postgres/itemmapper.php | 88 ++++---- db/statusflag.php | 48 ++-- 13 files changed, 893 insertions(+), 893 deletions(-) (limited to 'db') diff --git a/db/entityjsonserializer.php b/db/entityjsonserializer.php index 7e3357304..c0d946452 100644 --- a/db/entityjsonserializer.php +++ b/db/entityjsonserializer.php @@ -16,13 +16,13 @@ namespace OCA\News\Db; trait EntityJSONSerializer { - public function serializeFields($properties) { - $result = []; - foreach($properties as $property) { - $result[$property] = $this->$property; - } - return $result; - } + public function serializeFields($properties) { + $result = []; + foreach($properties as $property) { + $result[$property] = $this->$property; + } + return $result; + } } \ No newline at end of file diff --git a/db/feed.php b/db/feed.php index c5b76656e..0f5a9ba47 100644 --- a/db/feed.php +++ b/db/feed.php @@ -43,94 +43,94 @@ use \OCP\AppFramework\Db\Entity; */ class Feed extends Entity implements IAPI, \JsonSerializable { - use EntityJSONSerializer; - - protected $userId; - protected $urlHash; - protected $url; - protected $title; - protected $faviconLink; - protected $added; - protected $folderId; - protected $unreadCount; - protected $link; - protected $preventUpdate; - protected $deletedAt; - protected $articlesPerUpdate; - - public function __construct(){ - $this->addType('parentId', 'integer'); - $this->addType('added', 'integer'); - $this->addType('folderId', 'integer'); - $this->addType('unreadCount', 'integer'); - $this->addType('preventUpdate', 'boolean'); - $this->addType('deletedAt', 'integer'); - $this->addType('articlesPerUpdate', 'integer'); - } - - - /** - * Turns entitie attributes into an array - */ - public function jsonSerialize() { - $serialized = $this->serializeFields([ - 'id', - 'userId', - 'urlHash', - 'url', - 'title', - 'faviconLink', - 'added', - 'folderId', - 'unreadCount', - 'link', - 'preventUpdate', - 'deletedAt', - 'articlesPerUpdate', - ]); - - $url = parse_url($this->link)['host']; - - // strip leading www. to avoid css class confusion - if (strpos($url, 'www.') === 0) { - $url = substr($url, 4); - } - - $serialized['cssClass'] = 'custom-' . str_replace('.', '-', $url); - - return $serialized; - } - - - public function toAPI() { - return $this->serializeFields([ - 'id', - 'url', - 'title', - 'faviconLink', - 'added', - 'folderId', - 'unreadCount', - 'link' - ]); - } - - - public function setUrl($url) { - $url = trim($url); - if(strpos($url, 'http') === 0) { - parent::setUrl($url); - $this->setUrlHash(md5($url)); - } - } - - - public function setLink($url) { - $url = trim($url); - if(strpos($url, 'http') === 0) { - parent::setLink($url); - } - } + use EntityJSONSerializer; + + protected $userId; + protected $urlHash; + protected $url; + protected $title; + protected $faviconLink; + protected $added; + protected $folderId; + protected $unreadCount; + protected $link; + protected $preventUpdate; + protected $deletedAt; + protected $articlesPerUpdate; + + public function __construct(){ + $this->addType('parentId', 'integer'); + $this->addType('added', 'integer'); + $this->addType('folderId', 'integer'); + $this->addType('unreadCount', 'integer'); + $this->addType('preventUpdate', 'boolean'); + $this->addType('deletedAt', 'integer'); + $this->addType('articlesPerUpdate', 'integer'); + } + + + /** + * Turns entitie attributes into an array + */ + public function jsonSerialize() { + $serialized = $this->serializeFields([ + 'id', + 'userId', + 'urlHash', + 'url', + 'title', + 'faviconLink', + 'added', + 'folderId', + 'unreadCount', + 'link', + 'preventUpdate', + 'deletedAt', + 'articlesPerUpdate', + ]); + + $url = parse_url($this->link)['host']; + + // strip leading www. to avoid css class confusion + if (strpos($url, 'www.') === 0) { + $url = substr($url, 4); + } + + $serialized['cssClass'] = 'custom-' . str_replace('.', '-', $url); + + return $serialized; + } + + + public function toAPI() { + return $this->serializeFields([ + 'id', + 'url', + 'title', + 'faviconLink', + 'added', + 'folderId', + 'unreadCount', + 'link' + ]); + } + + + public function setUrl($url) { + $url = trim($url); + if(strpos($url, 'http') === 0) { + parent::setUrl($url); + $this->setUrlHash(md5($url)); + } + } + + + public function setLink($url) { + $url = trim($url); + if(strpos($url, 'http') === 0) { + parent::setLink($url); + } + } } \ No newline at end of file diff --git a/db/feedmapper.php b/db/feedmapper.php index b913288a6..ab55c6f1b 100644 --- a/db/feedmapper.php +++ b/db/feedmapper.php @@ -20,158 +20,158 @@ use \OCP\AppFramework\Db\Entity; class FeedMapper extends NewsMapper { - public function __construct(IDb $db) { - parent::__construct($db, 'news_feeds', '\OCA\News\Db\Feed'); - } - - - public function find($id, $userId){ - $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . - 'FROM `*PREFIX*news_feeds` `feeds` ' . - 'LEFT JOIN `*PREFIX*news_items` `items` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - // WARNING: this is a desperate attempt at making this query work - // because prepared statements dont work. This is a possible - // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. - // think twice when changing this - 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . - StatusFlag::UNREAD . ' ' . - 'WHERE `feeds`.`id` = ? ' . - 'AND `feeds`.`user_id` = ? ' . - 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. - '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. - '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. - '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; - $params = [$id, $userId]; - - return $this->findEntity($sql, $params); - } - - - public function findAllFromUser($userId){ - $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . - 'FROM `*PREFIX*news_feeds` `feeds` ' . - 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` '. - 'ON `feeds`.`folder_id` = `folders`.`id` ' . - 'LEFT JOIN `*PREFIX*news_items` `items` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - // WARNING: this is a desperate attempt at making this query work - // because prepared statements dont work. This is a possible - // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. - // think twice when changing this - 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . - StatusFlag::UNREAD . ' ' . - 'WHERE `feeds`.`user_id` = ? ' . - 'AND (`feeds`.`folder_id` = 0 ' . - 'OR `folders`.`deleted_at` = 0' . - ')' . - 'AND `feeds`.`deleted_at` = 0 ' . - 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. - '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. - '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. - '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; - $params = [$userId]; - - return $this->findEntities($sql, $params); - } - - - public function findAll(){ - $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . - 'FROM `*PREFIX*news_feeds` `feeds` ' . - 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` '. - 'ON `feeds`.`folder_id` = `folders`.`id` ' . - 'LEFT JOIN `*PREFIX*news_items` `items` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - // WARNING: this is a desperate attempt at making this query work - // because prepared statements don't work. This is a possible - // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. - // think twice when changing this - 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . - StatusFlag::UNREAD . ' ' . - 'WHERE (`feeds`.`folder_id` = 0 ' . - 'OR `folders`.`deleted_at` = 0' . - ')' . - 'AND `feeds`.`deleted_at` = 0 ' . - 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. - '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. - '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. - '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; - - return $this->findEntities($sql); - } - - - public function findByUrlHash($hash, $userId){ - $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . - 'FROM `*PREFIX*news_feeds` `feeds` ' . - 'LEFT JOIN `*PREFIX*news_items` `items` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - // WARNING: this is a desperate attempt at making this query work - // because prepared statements dont work. This is a possible - // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. - // think twice when changing this - 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . - StatusFlag::UNREAD . ' ' . - 'WHERE `feeds`.`url_hash` = ? ' . - 'AND `feeds`.`user_id` = ? ' . - 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. - '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. - '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. - '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; - $params = [$hash, $userId]; - - return $this->findEntity($sql, $params); - } - - - public function delete(Entity $entity){ - parent::delete($entity); - - // someone please slap me for doing this manually :P - // we needz CASCADE + FKs please - $sql = 'DELETE FROM `*PREFIX*news_items` WHERE `feed_id` = ?'; - $params = [$entity->getId()]; - $this->execute($sql, $params); - } - - - /** - * @param int $deleteOlderThan if given gets all entries with a delete date - * older than that timestamp - * @param string $userId if given returns only entries from the given user - * @return array with the database rows - */ - public function getToDelete($deleteOlderThan=null, $userId=null) { - $sql = 'SELECT * FROM `*PREFIX*news_feeds` ' . - 'WHERE `deleted_at` > 0 '; - $params = []; - - // sometimes we want to delete all entries - if ($deleteOlderThan !== null) { - $sql .= 'AND `deleted_at` < ? '; - $params[] = $deleteOlderThan; - } - - // we need to sometimes only delete feeds of a user - if($userId !== null) { - $sql .= 'AND `user_id` = ?'; - $params[] = $userId; - } - - return $this->findEntities($sql, $params); - } - - - /** - * Deletes all feeds of a user, delete items first since the user_id - * is not defined in there - * @param string $userId the name of the user - */ - public function deleteUser($userId) { - $sql = 'DELETE FROM `*PREFIX*news_feeds` WHERE `user_id` = ?'; - $this->execute($sql, [$userId]); - } + public function __construct(IDb $db) { + parent::__construct($db, 'news_feeds', '\OCA\News\Db\Feed'); + } + + + public function find($id, $userId){ + $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . + 'FROM `*PREFIX*news_feeds` `feeds` ' . + 'LEFT JOIN `*PREFIX*news_items` `items` ' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + // WARNING: this is a desperate attempt at making this query work + // because prepared statements dont work. This is a possible + // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. + // think twice when changing this + 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . + StatusFlag::UNREAD . ' ' . + 'WHERE `feeds`.`id` = ? ' . + 'AND `feeds`.`user_id` = ? ' . + 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. + '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. + '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. + '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; + $params = [$id, $userId]; + + return $this->findEntity($sql, $params); + } + + + public function findAllFromUser($userId){ + $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . + 'FROM `*PREFIX*news_feeds` `feeds` ' . + 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` '. + 'ON `feeds`.`folder_id` = `folders`.`id` ' . + 'LEFT JOIN `*PREFIX*news_items` `items` ' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + // WARNING: this is a desperate attempt at making this query work + // because prepared statements dont work. This is a possible + // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. + // think twice when changing this + 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . + StatusFlag::UNREAD . ' ' . + 'WHERE `feeds`.`user_id` = ? ' . + 'AND (`feeds`.`folder_id` = 0 ' . + 'OR `folders`.`deleted_at` = 0' . + ')' . + 'AND `feeds`.`deleted_at` = 0 ' . + 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. + '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. + '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. + '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; + $params = [$userId]; + + return $this->findEntities($sql, $params); + } + + + public function findAll(){ + $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . + 'FROM `*PREFIX*news_feeds` `feeds` ' . + 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` '. + 'ON `feeds`.`folder_id` = `folders`.`id` ' . + 'LEFT JOIN `*PREFIX*news_items` `items` ' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + // WARNING: this is a desperate attempt at making this query work + // because prepared statements don't work. This is a possible + // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. + // think twice when changing this + 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . + StatusFlag::UNREAD . ' ' . + 'WHERE (`feeds`.`folder_id` = 0 ' . + 'OR `folders`.`deleted_at` = 0' . + ')' . + 'AND `feeds`.`deleted_at` = 0 ' . + 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. + '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. + '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. + '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; + + return $this->findEntities($sql); + } + + + public function findByUrlHash($hash, $userId){ + $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . + 'FROM `*PREFIX*news_feeds` `feeds` ' . + 'LEFT JOIN `*PREFIX*news_items` `items` ' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + // WARNING: this is a desperate attempt at making this query work + // because prepared statements dont work. This is a possible + // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. + // think twice when changing this + 'AND (`items`.`status` & ' . StatusFlag::UNREAD . ') = ' . + StatusFlag::UNREAD . ' ' . + 'WHERE `feeds`.`url_hash` = ? ' . + 'AND `feeds`.`user_id` = ? ' . + 'GROUP BY `feeds`.`id`, `feeds`.`user_id`, `feeds`.`url_hash`,'. + '`feeds`.`url`, `feeds`.`title`, `feeds`.`link`,'. + '`feeds`.`favicon_link`, `feeds`.`added`, `feeds`.`articles_per_update`,'. + '`feeds`.`folder_id`, `feeds`.`prevent_update`, `feeds`.`deleted_at`'; + $params = [$hash, $userId]; + + return $this->findEntity($sql, $params); + } + + + public function delete(Entity $entity){ + parent::delete($entity); + + // someone please slap me for doing this manually :P + // we needz CASCADE + FKs please + $sql = 'DELETE FROM `*PREFIX*news_items` WHERE `feed_id` = ?'; + $params = [$entity->getId()]; + $this->execute($sql, $params); + } + + + /** + * @param int $deleteOlderThan if given gets all entries with a delete date + * older than that timestamp + * @param string $userId if given returns only entries from the given user + * @return array with the database rows + */ + public function getToDelete($deleteOlderThan=null, $userId=null) { + $sql = 'SELECT * FROM `*PREFIX*news_feeds` ' . + 'WHERE `deleted_at` > 0 '; + $params = []; + + // sometimes we want to delete all entries + if ($deleteOlderThan !== null) { + $sql .= 'AND `deleted_at` < ? '; + $params[] = $deleteOlderThan; + } + + // we need to sometimes only delete feeds of a user + if($userId !== null) { + $sql .= 'AND `user_id` = ?'; + $params[] = $userId; + } + + return $this->findEntities($sql, $params); + } + + + /** + * Deletes all feeds of a user, delete items first since the user_id + * is not defined in there + * @param string $userId the name of the user + */ + public function deleteUser($userId) { + $sql = 'DELETE FROM `*PREFIX*news_feeds` WHERE `user_id` = ?'; + $this->execute($sql, [$userId]); + } } diff --git a/db/feedtype.php b/db/feedtype.php index 2539214ed..d43948a3c 100644 --- a/db/feedtype.php +++ b/db/feedtype.php @@ -15,9 +15,9 @@ namespace OCA\News\Db; class FeedType { - const FEED = 0; - const FOLDER = 1; - const STARRED = 2; - const SUBSCRIPTIONS = 3; - const SHARED = 4; + const FEED = 0; + const FOLDER = 1; + const STARRED = 2; + const SUBSCRIPTIONS = 3; + const SHARED = 4; }; \ No newline at end of file diff --git a/db/folder.php b/db/folder.php index d5f50685f..6d4af1aa3 100644 --- a/db/folder.php +++ b/db/folder.php @@ -31,38 +31,38 @@ use \OCP\AppFramework\Db\Entity; */ class Folder extends Entity implements IAPI, \JsonSerializable { - use EntityJSONSerializer; + use EntityJSONSerializer; - protected $parentId; - protected $name; - protected $userId; - protected $opened; - protected $deletedAt; + protected $parentId; + protected $name; + protected $userId; + protected $opened; + protected $deletedAt; - public function __construct(){ - $this->addType('parentId', 'integer'); - $this->addType('opened', 'boolean'); - $this->addType('deletedAt', 'integer'); - } + public function __construct(){ + $this->addType('parentId', 'integer'); + $this->addType('opened', 'boolean'); + $this->addType('deletedAt', 'integer'); + } - /** - * Turns entitie attributes into an array - */ - public function jsonSerialize() { - return $this->serializeFields([ - 'id', - 'parentId', - 'name', - 'userId', - 'opened', - 'deletedAt', - ]); - } + /** + * Turns entitie attributes into an array + */ + public function jsonSerialize() { + return $this->serializeFields([ + 'id', + 'parentId', + 'name', + 'userId', + 'opened', + 'deletedAt', + ]); + } - public function toAPI() { - return $this->serializeFields([ - 'id', - 'name' - ]); - } + public function toAPI() { + return $this->serializeFields([ + 'id', + 'name' + ]); + } } \ No newline at end of file diff --git a/db/foldermapper.php b/db/foldermapper.php index 2c4d2aca5..bce599b45 100644 --- a/db/foldermapper.php +++ b/db/foldermapper.php @@ -18,90 +18,90 @@ use \OCP\AppFramework\Db\Entity; class FolderMapper extends NewsMapper { - public function __construct(IDb $db) { - parent::__construct($db, 'news_folders', '\OCA\News\Db\Folder'); - } - - public function find($id, $userId){ - $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . - 'WHERE `id` = ? ' . - 'AND `user_id` = ?'; - - return $this->findEntity($sql, [$id, $userId]); - } - - - public function findAllFromUser($userId){ - $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . - 'WHERE `user_id` = ? ' . - 'AND `deleted_at` = 0'; - $params = [$userId]; - - return $this->findEntities($sql, $params); - } - - - public function findByName($folderName, $userId){ - $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . - 'WHERE `name` = ? ' . - 'AND `user_id` = ?'; - $params = [$folderName, $userId]; - - return $this->findEntities($sql, $params); - } - - - public function delete(Entity $entity){ - parent::delete($entity); - - // someone please slap me for doing this manually :P - // we needz CASCADE + FKs please - $sql = 'DELETE FROM `*PREFIX*news_feeds` WHERE `folder_id` = ?'; - $params = [$entity->getId()]; - $this->execute($sql, $params); - - $sql = 'DELETE FROM `*PREFIX*news_items` WHERE `feed_id` NOT IN '. - '(SELECT `feeds`.`id` FROM `*PREFIX*news_feeds` `feeds`)'; - - $this->execute($sql); - } - - - /** - * @param int $deleteOlderThan if given gets all entries with a delete date - * older than that timestamp - * @param string $userId if given returns only entries from the given user - * @return array with the database rows - */ - public function getToDelete($deleteOlderThan=null, $userId=null) { - $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . - 'WHERE `deleted_at` > 0 '; - $params = []; - - // sometimes we want to delete all entries - if ($deleteOlderThan !== null) { - $sql .= 'AND `deleted_at` < ? '; - $params[] = $deleteOlderThan; - } - - // we need to sometimes only delete feeds of a user - if($userId !== null) { - $sql .= 'AND `user_id` = ?'; - $params[] = $userId; - } - - return $this->findEntities($sql, $params); - } - - - /** - * Deletes all folders of a user - * @param string $userId the name of the user - */ - public function deleteUser($userId) { - $sql = 'DELETE FROM `*PREFIX*news_folders` WHERE `user_id` = ?'; - $this->execute($sql, [$userId]); - } + public function __construct(IDb $db) { + parent::__construct($db, 'news_folders', '\OCA\News\Db\Folder'); + } + + public function find($id, $userId){ + $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . + 'WHERE `id` = ? ' . + 'AND `user_id` = ?'; + + return $this->findEntity($sql, [$id, $userId]); + } + + + public function findAllFromUser($userId){ + $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . + 'WHERE `user_id` = ? ' . + 'AND `deleted_at` = 0'; + $params = [$userId]; + + return $this->findEntities($sql, $params); + } + + + public function findByName($folderName, $userId){ + $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . + 'WHERE `name` = ? ' . + 'AND `user_id` = ?'; + $params = [$folderName, $userId]; + + return $this->findEntities($sql, $params); + } + + + public function delete(Entity $entity){ + parent::delete($entity); + + // someone please slap me for doing this manually :P + // we needz CASCADE + FKs please + $sql = 'DELETE FROM `*PREFIX*news_feeds` WHERE `folder_id` = ?'; + $params = [$entity->getId()]; + $this->execute($sql, $params); + + $sql = 'DELETE FROM `*PREFIX*news_items` WHERE `feed_id` NOT IN '. + '(SELECT `feeds`.`id` FROM `*PREFIX*news_feeds` `feeds`)'; + + $this->execute($sql); + } + + + /** + * @param int $deleteOlderThan if given gets all entries with a delete date + * older than that timestamp + * @param string $userId if given returns only entries from the given user + * @return array with the database rows + */ + public function getToDelete($deleteOlderThan=null, $userId=null) { + $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . + 'WHERE `deleted_at` > 0 '; + $params = []; + + // sometimes we want to delete all entries + if ($deleteOlderThan !== null) { + $sql .= 'AND `deleted_at` < ? '; + $params[] = $deleteOlderThan; + } + + // we need to sometimes only delete feeds of a user + if($userId !== null) { + $sql .= 'AND `user_id` = ?'; + $params[] = $userId; + } + + return $this->findEntities($sql, $params); + } + + + /** + * Deletes all folders of a user + * @param string $userId the name of the user + */ + public function deleteUser($userId) { + $sql = 'DELETE FROM `*PREFIX*news_folders` WHERE `user_id` = ?'; + $this->execute($sql, [$userId]); + } } \ No newline at end of file diff --git a/db/iapi.php b/db/iapi.php index e5de5bd38..ff9791753 100644 --- a/db/iapi.php +++ b/db/iapi.php @@ -14,5 +14,5 @@ namespace OCA\News\Db; interface IAPI { - public function toAPI(); + public function toAPI(); } diff --git a/db/item.php b/db/item.php index a53c825ff..5c353b058 100644 --- a/db/item.php +++ b/db/item.php @@ -41,177 +41,177 @@ use \OCP\AppFramework\Db\Entity; */ class Item extends Entity implements IAPI, \JsonSerializable { - use EntityJSONSerializer; - - protected $guidHash; - protected $guid; - protected $url; - protected $title; - protected $author; - protected $pubDate; - protected $body; - protected $enclosureMime; - protected $enclosureLink; - protected $feedId; - protected $status = 0; - protected $lastModified; - - public function __construct(){ - $this->addType('pubDate', 'integer'); - $this->addType('feedId', 'integer'); - $this->addType('status', 'integer'); - $this->addType('lastModified', 'integer'); - } - - - public function setRead() { - $this->markFieldUpdated('status'); - $this->status &= ~StatusFlag::UNREAD; - } - - public function isRead() { - return !(($this->status & StatusFlag::UNREAD) === StatusFlag::UNREAD); - } - - public function setUnread() { - $this->markFieldUpdated('status'); - $this->status |= StatusFlag::UNREAD; - } - - public function isUnread() { - return !$this->isRead(); - } - - public function setStarred() { - $this->markFieldUpdated('status'); - $this->status |= StatusFlag::STARRED; - } - - public function isStarred() { - return ($this->status & StatusFlag::STARRED) === StatusFlag::STARRED; - } - - public function setUnstarred() { - $this->markFieldUpdated('status'); - $this->status &= ~StatusFlag::STARRED; - } - - public function isUnstarred() { - return !$this->isStarred(); - } - - /** - * Turns entitie attributes into an array - */ - public function jsonSerialize() { - return [ - 'id' => $this->getId(), - 'guid' => $this->getGuid(), - 'guidHash' => $this->getGuidHash(), - 'url' => $this->getUrl(), - 'title' => $this->getTitle(), - 'author' => $this->getAuthor(), - 'pubDate' => $this->getPubDate(), - 'body' => $this->getBody(), - 'enclosureMime' => $this->getEnclosureMime(), - 'enclosureLink' => $this->getEnclosureLink(), - 'feedId' => $this->getFeedId(), - 'unread' => $this->isUnread(), - 'starred' => $this->isStarred(), - 'lastModified' => $this->getLastModified() - ]; - } - - public function toAPI() { - return [ - 'id' => $this->getId(), - 'guid' => $this->getGuid(), - 'guidHash' => $this->getGuidHash(), - 'url' => $this->getUrl(), - 'title' => $this->getTitle(), - 'author' => $this->getAuthor(), - 'pubDate' => $this->getPubDate(), - 'body' => $this->getBody(), - 'enclosureMime' => $this->getEnclosureMime(), - 'enclosureLink' => $this->getEnclosureLink(), - 'feedId' => $this->getFeedId(), - 'unread' => $this->isUnread(), - 'starred' => $this->isStarred(), - 'lastModified' => $this->getLastModified() - ]; - } - - - public function toExport($feeds) { - return [ - 'guid' => $this->getGuid(), - 'url' => $this->getUrl(), - 'title' => $this->getTitle(), - 'author' => $this->getAuthor(), - 'pubDate' => $this->getPubDate(), - 'body' => $this->getBody(), - 'enclosureMime' => $this->getEnclosureMime(), - 'enclosureLink' => $this->getEnclosureLink(), - 'unread' => $this->isUnread(), - 'starred' => $this->isStarred(), - 'feedLink' => $feeds['feed'. $this->getFeedId()]->getLink() - ]; - } - - - public static function fromImport($import) { - $item = new static(); - $item->setGuid($import['guid']); - $item->setUrl($import['url']); - $item->setTitle($import['title']); - $item->setAuthor($import['author']); - $item->setPubDate($import['pubDate']); - $item->setBody($import['body']); - $item->setEnclosureMime($import['enclosureMime']); - $item->setEnclosureLink($import['enclosureLink']); - if($import['unread']) { - $item->setUnread(); - } else { - $item->setRead(); - } - if($import['starred']) { - $item->setStarred(); - } else { - $item->setUnstarred(); - } - - return $item; - } - - - public function setAuthor($name) { - parent::setAuthor(strip_tags($name)); - } - - - public function setTitle($title) { - parent::setTitle(strip_tags($title)); - } - - - public function setUrl($url) { - $url = trim($url); - if(strpos($url, 'http') === 0 || strpos($url, 'magnet') === 0) { - parent::setUrl($url); - } - } - - - public function setGuid($guid) { - parent::setGuid($guid); - $this->setGuidHash(md5($guid)); - } - - - public function setBody($body) { - // FIXME: this should not happen if the target="_blank" is already on the link - parent::setBody(str_replace('addType('pubDate', 'integer'); + $this->addType('feedId', 'integer'); + $this->addType('status', 'integer'); + $this->addType('lastModified', 'integer'); + } + + + public function setRead() { + $this->markFieldUpdated('status'); + $this->status &= ~StatusFlag::UNREAD; + } + + public function isRead() { + return !(($this->status & StatusFlag::UNREAD) === StatusFlag::UNREAD); + } + + public function setUnread() { + $this->markFieldUpdated('status'); + $this->status |= StatusFlag::UNREAD; + } + + public function isUnread() { + return !$this->isRead(); + } + + public function setStarred() { + $this->markFieldUpdated('status'); + $this->status |= StatusFlag::STARRED; + } + + public function isStarred() { + return ($this->status & StatusFlag::STARRED) === StatusFlag::STARRED; + } + + public function setUnstarred() { + $this->markFieldUpdated('status'); + $this->status &= ~StatusFlag::STARRED; + } + + public function isUnstarred() { + return !$this->isStarred(); + } + + /** + * Turns entitie attributes into an array + */ + public function jsonSerialize() { + return [ + 'id' => $this->getId(), + 'guid' => $this->getGuid(), + 'guidHash' => $this->getGuidHash(), + 'url' => $this->getUrl(), + 'title' => $this->getTitle(), + 'author' => $this->getAuthor(), + 'pubDate' => $this->getPubDate(), + 'body' => $this->getBody(), + 'enclosureMime' => $this->getEnclosureMime(), + 'enclosureLink' => $this->getEnclosureLink(), + 'feedId' => $this->getFeedId(), + 'unread' => $this->isUnread(), + 'starred' => $this->isStarred(), + 'lastModified' => $this->getLastModified() + ]; + } + + public function toAPI() { + return [ + 'id' => $this->getId(), + 'guid' => $this->getGuid(), + 'guidHash' => $this->getGuidHash(), + 'url' => $this->getUrl(), + 'title' => $this->getTitle(), + 'author' => $this->getAuthor(), + 'pubDate' => $this->getPubDate(), + 'body' => $this->getBody(), + 'enclosureMime' => $this->getEnclosureMime(), + 'enclosureLink' => $this->getEnclosureLink(), + 'feedId' => $this->getFeedId(), + 'unread' => $this->isUnread(), + 'starred' => $this->isStarred(), + 'lastModified' => $this->getLastModified() + ]; + } + + + public function toExport($feeds) { + return [ + 'guid' => $this->getGuid(), + 'url' => $this->getUrl(), + 'title' => $this->getTitle(), + 'author' => $this->getAuthor(), + 'pubDate' => $this->getPubDate(), + 'body' => $this->getBody(), + 'enclosureMime' => $this->getEnclosureMime(), + 'enclosureLink' => $this->getEnclosureLink(), + 'unread' => $this->isUnread(), + 'starred' => $this->isStarred(), + 'feedLink' => $feeds['feed'. $this->getFeedId()]->getLink() + ]; + } + + + public static function fromImport($import) { + $item = new static(); + $item->setGuid($import['guid']); + $item->setUrl($import['url']); + $item->setTitle($import['title']); + $item->setAuthor($import['author']); + $item->setPubDate($import['pubDate']); + $item->setBody($import['body']); + $item->setEnclosureMime($import['enclosureMime']); + $item->setEnclosureLink($import['enclosureLink']); + if($import['unread']) { + $item->setUnread(); + } else { + $item->setRead(); + } + if($import['starred']) { + $item->setStarred(); + } else { + $item->setUnstarred(); + } + + return $item; + } + + + public function setAuthor($name) { + parent::setAuthor(strip_tags($name)); + } + + + public function setTitle($title) { + parent::setTitle(strip_tags($title)); + } + + + public function setUrl($url) { + $url = trim($url); + if(strpos($url, 'http') === 0 || strpos($url, 'magnet') === 0) { + parent::setUrl($url); + } + } + + + public function setGuid($guid) { + parent::setGuid($guid); + $this->setGuidHash(md5($guid)); + } + + + public function setBody($body) { + // FIXME: this should not happen if the target="_blank" is already on the link + parent::setBody(str_replace('makeSelectQuery( - - // WARNING: this is a desperate attempt at making this query work - // because prepared statements dont work. This is a possible - // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. - // think twice when changing this - 'AND ((`items`.`status` & ' . $status . ') = ' . $status . ') ' . - $prependTo, $oldestFirst - ); - } - - - 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` '. - '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 . ')' . - 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' . - 'ON `folders`.`id` = `feeds`.`folder_id` ' . - 'WHERE `feeds`.`folder_id` = 0 ' . - 'OR `folders`.`deleted_at` = 0'; - - $params = [$userId]; - - $result = $this->execute($sql, $params)->fetch(); - - return (int) $result['size']; - } - - - public function readAll($highestItemId, $time, $userId) { - $sql = 'UPDATE `*PREFIX*news_items` ' . - 'SET `status` = `status` & ? ' . - ', `last_modified` = ? ' . - 'WHERE `feed_id` IN (' . - 'SELECT `id` FROM `*PREFIX*news_feeds` ' . - 'WHERE `user_id` = ? ' . - ') '. - 'AND `id` <= ?'; - $params = [~StatusFlag::UNREAD, $time, $userId, $highestItemId]; - $this->execute($sql, $params); - } - - - public function readFolder($folderId, $highestItemId, $time, $userId) { - $sql = 'UPDATE `*PREFIX*news_items` ' . - 'SET `status` = `status` & ? ' . - ', `last_modified` = ? ' . - 'WHERE `feed_id` IN (' . - 'SELECT `id` FROM `*PREFIX*news_feeds` ' . - 'WHERE `folder_id` = ? ' . - 'AND `user_id` = ? ' . - ') '. - 'AND `id` <= ?'; - $params = [~StatusFlag::UNREAD, $time, $folderId, $userId, - $highestItemId]; - $this->execute($sql, $params); - } - - - 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` = ? ) '; - $params = [~StatusFlag::UNREAD, $time, $feedId, $highestItemId, - $userId, $feedId]; - - $this->execute($sql, $params); - } - - - private function getOperator($oldestFirst) { - if($oldestFirst) { - return '>'; - } else { - return '<'; - } - } - - - public function findAllNew($updatedSince, $status, $userId){ - $sql = $this->makeSelectQueryStatus( - 'AND `items`.`last_modified` >= ? ', $status); - $params = [$userId, $updatedSince]; - return $this->findEntities($sql, $params); - } - - - public function findAllNewFolder($id, $updatedSince, $status, $userId){ - $sql = 'AND `feeds`.`folder_id` = ? ' . - '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){ - $sql = 'AND `items`.`feed_id` = ? ' . - 'AND `items`.`last_modified` >= ? '; - $sql = $this->makeSelectQueryStatus($sql, $status); - $params = [$userId, $id, $updatedSince]; - return $this->findEntities($sql, $params); - } - - - public function findAllFeed($id, $limit, $offset, $status, $oldestFirst, $userId){ - $params = [$userId, $id]; - $sql = 'AND `items`.`feed_id` = ? '; - if($offset !== 0){ - $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; - $params[] = $offset; - } - $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst); - return $this->findEntities($sql, $params, $limit); - } - - - public function findAllFolder($id, $limit, $offset, $status, $oldestFirst, $userId){ - $params = [$userId, $id]; - $sql = 'AND `feeds`.`folder_id` = ? '; - if($offset !== 0){ - $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; - $params[] = $offset; - } - $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst); - return $this->findEntities($sql, $params, $limit); - } - - - public function findAll($limit, $offset, $status, $oldestFirst, $userId){ - $params = [$userId]; - $sql = ''; - if($offset !== 0){ - $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; - $params[] = $offset; - } - $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst); - return $this->findEntities($sql, $params, $limit); - } - - - public function findAllUnreadOrStarred($userId) { - $params = [$userId]; - $status = StatusFlag::UNREAD | StatusFlag::STARRED; - $sql = 'AND ((`items`.`status` & ' . $status . ') > 0) '; - $sql = $this->makeSelectQuery($sql); - return $this->findEntities($sql, $params); - } - - - 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 - */ - public function deleteReadOlderThanThreshold($threshold){ - $status = StatusFlag::STARRED | StatusFlag::UNREAD; - $sql = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' . - '`items`.`feed_id` AS `feed_id` ' . - 'FROM `*PREFIX*news_items` `items` ' . - 'JOIN `*PREFIX*news_feeds` `feeds` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - 'WHERE NOT ((`items`.`status` & ?) > 0) ' . - 'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' . - 'HAVING COUNT(*) > ?'; - $params = [$status, $threshold]; - $result = $this->execute($sql, $params); - - while($row = $result->fetch()) { - - $size = (int) $row['size']; - $limit = $size - $threshold; - - if($limit > 0) { - $params = [$status, $row['feed_id']]; - - $sql = 'DELETE FROM `*PREFIX*news_items` ' . - 'WHERE NOT ((`status` & ?) > 0) ' . - 'AND `feed_id` = ? ' . - 'ORDER BY `id` ASC'; - - $this->execute($sql, $params, $limit); - } - } - } - - - public function getNewestItemId($userId) { - $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']; - } - - - /** - * Deletes all items of a user - * @param string $userId the name of the user - */ - 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` = ?' . - ')'; - - $this->execute($sql, [$userId]); - } + public function __construct(IDb $db){ + parent::__construct($db, 'news_items', '\OCA\News\Db\Item'); + } + + + private function makeSelectQuery($prependTo, $oldestFirst=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; + } + + private function makeSelectQueryStatus($prependTo, $status, $oldestFirst=false) { + // Hi this is Ray and you're watching Jack Ass + // Now look closely: this is how we adults handle weird bugs in our + // code: we take them variables and we cast the shit out of them + $status = (int) $status; + + // now im gonna slowly stick them in the query, be careful! + return $this->makeSelectQuery( + + // WARNING: this is a desperate attempt at making this query work + // because prepared statements dont work. This is a possible + // SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. + // think twice when changing this + 'AND ((`items`.`status` & ' . $status . ') = ' . $status . ') ' . + $prependTo, $oldestFirst + ); + } + + + 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` '. + '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 . ')' . + 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' . + 'ON `folders`.`id` = `feeds`.`folder_id` ' . + 'WHERE `feeds`.`folder_id` = 0 ' . + 'OR `folders`.`deleted_at` = 0'; + + $params = [$userId]; + + $result = $this->execute($sql, $params)->fetch(); + + return (int) $result['size']; + } + + + public function readAll($highestItemId, $time, $userId) { + $sql = 'UPDATE `*PREFIX*news_items` ' . + 'SET `status` = `status` & ? ' . + ', `last_modified` = ? ' . + 'WHERE `feed_id` IN (' . + 'SELECT `id` FROM `*PREFIX*news_feeds` ' . + 'WHERE `user_id` = ? ' . + ') '. + 'AND `id` <= ?'; + $params = [~StatusFlag::UNREAD, $time, $userId, $highestItemId]; + $this->execute($sql, $params); + } + + + public function readFolder($folderId, $highestItemId, $time, $userId) { + $sql = 'UPDATE `*PREFIX*news_items` ' . + 'SET `status` = `status` & ? ' . + ', `last_modified` = ? ' . + 'WHERE `feed_id` IN (' . + 'SELECT `id` FROM `*PREFIX*news_feeds` ' . + 'WHERE `folder_id` = ? ' . + 'AND `user_id` = ? ' . + ') '. + 'AND `id` <= ?'; + $params = [~StatusFlag::UNREAD, $time, $folderId, $userId, + $highestItemId]; + $this->execute($sql, $params); + } + + + 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` = ? ) '; + $params = [~StatusFlag::UNREAD, $time, $feedId, $highestItemId, + $userId, $feedId]; + + $this->execute($sql, $params); + } + + + private function getOperator($oldestFirst) { + if($oldestFirst) { + return '>'; + } else { + return '<'; + } + } + + + public function findAllNew($updatedSince, $status, $userId){ + $sql = $this->makeSelectQueryStatus( + 'AND `items`.`last_modified` >= ? ', $status); + $params = [$userId, $updatedSince]; + return $this->findEntities($sql, $params); + } + + + public function findAllNewFolder($id, $updatedSince, $status, $userId){ + $sql = 'AND `feeds`.`folder_id` = ? ' . + '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){ + $sql = 'AND `items`.`feed_id` = ? ' . + 'AND `items`.`last_modified` >= ? '; + $sql = $this->makeSelectQueryStatus($sql, $status); + $params = [$userId, $id, $updatedSince]; + return $this->findEntities($sql, $params); + } + + + public function findAllFeed($id, $limit, $offset, $status, $oldestFirst, $userId){ + $params = [$userId, $id]; + $sql = 'AND `items`.`feed_id` = ? '; + if($offset !== 0){ + $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; + $params[] = $offset; + } + $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst); + return $this->findEntities($sql, $params, $limit); + } + + + public function findAllFolder($id, $limit, $offset, $status, $oldestFirst, $userId){ + $params = [$userId, $id]; + $sql = 'AND `feeds`.`folder_id` = ? '; + if($offset !== 0){ + $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; + $params[] = $offset; + } + $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst); + return $this->findEntities($sql, $params, $limit); + } + + + public function findAll($limit, $offset, $status, $oldestFirst, $userId){ + $params = [$userId]; + $sql = ''; + if($offset !== 0){ + $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; + $params[] = $offset; + } + $sql = $this->makeSelectQueryStatus($sql, $status, $oldestFirst); + return $this->findEntities($sql, $params, $limit); + } + + + public function findAllUnreadOrStarred($userId) { + $params = [$userId]; + $status = StatusFlag::UNREAD | StatusFlag::STARRED; + $sql = 'AND ((`items`.`status` & ' . $status . ') > 0) '; + $sql = $this->makeSelectQuery($sql); + return $this->findEntities($sql, $params); + } + + + 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 + */ + public function deleteReadOlderThanThreshold($threshold){ + $status = StatusFlag::STARRED | StatusFlag::UNREAD; + $sql = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' . + '`items`.`feed_id` AS `feed_id` ' . + 'FROM `*PREFIX*news_items` `items` ' . + 'JOIN `*PREFIX*news_feeds` `feeds` ' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + 'WHERE NOT ((`items`.`status` & ?) > 0) ' . + 'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' . + 'HAVING COUNT(*) > ?'; + $params = [$status, $threshold]; + $result = $this->execute($sql, $params); + + while($row = $result->fetch()) { + + $size = (int) $row['size']; + $limit = $size - $threshold; + + if($limit > 0) { + $params = [$status, $row['feed_id']]; + + $sql = 'DELETE FROM `*PREFIX*news_items` ' . + 'WHERE NOT ((`status` & ?) > 0) ' . + 'AND `feed_id` = ? ' . + 'ORDER BY `id` ASC'; + + $this->execute($sql, $params, $limit); + } + } + } + + + public function getNewestItemId($userId) { + $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']; + } + + + /** + * Deletes all items of a user + * @param string $userId the name of the user + */ + 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` = ?' . + ')'; + + $this->execute($sql, [$userId]); + } } diff --git a/db/mapperfactory.php b/db/mapperfactory.php index 4af75cefa..6bc9346ca 100644 --- a/db/mapperfactory.php +++ b/db/mapperfactory.php @@ -18,23 +18,23 @@ use \OCA\News\Db\Postgres\ItemMapper as PostgresItemMapper; class MapperFactory { - private $dbType; - private $db; - - public function __construct($dbType, IDb $db) { - $this->dbType = $dbType; - $this->db = $db; - } - - - public function getItemMapper() { - switch($this->dbType) { - case 'pgsql': - return new PostgresItemMapper($this->db); - default: - return new ItemMapper($this->db); - } - } + private $dbType; + private $db; + + public function __construct($dbType, IDb $db) { + $this->dbType = $dbType; + $this->db = $db; + } + + + public function getItemMapper() { + switch($this->dbType) { + case 'pgsql': + return new PostgresItemMapper($this->db); + default: + return new ItemMapper($this->db); + } + } } \ No newline at end of file diff --git a/db/newsmapper.php b/db/newsmapper.php index 955e3ba00..c483bd329 100644 --- a/db/newsmapper.php +++ b/db/newsmapper.php @@ -28,7 +28,7 @@ abstract class NewsMapper extends Mapper { * @param string $userId the id of the user * @return \OCP\AppFramework\Db\Entity */ - public abstract function find($id, $userId); + public abstract function find($id, $userId); } \ No newline at end of file diff --git a/db/postgres/itemmapper.php b/db/postgres/itemmapper.php index 593a0719a..643055f1a 100644 --- a/db/postgres/itemmapper.php +++ b/db/postgres/itemmapper.php @@ -20,50 +20,50 @@ use \OCA\News\Db\StatusFlag; class ItemMapper extends \OCA\News\Db\ItemMapper { - public function __construct(IDb $db){ - parent::__construct($db); - } - - - /** - * Delete all items for feeds that have over $threshold unread and not - * starred items - */ - public function deleteReadOlderThanThreshold($threshold){ - $status = StatusFlag::STARRED | StatusFlag::UNREAD; - $sql = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' . - '`items`.`feed_id` AS `feed_id` ' . - 'FROM `*PREFIX*news_items` `items` ' . - 'JOIN `*PREFIX*news_feeds` `feeds` ' . - 'ON `feeds`.`id` = `items`.`feed_id` ' . - 'WHERE NOT ((`items`.`status` & ?) > 0) ' . - 'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' . - 'HAVING COUNT(*) > ?'; - $params = [$status, $threshold]; - $result = $this->execute($sql, $params); - - while($row = $result->fetch()) { - - $size = (int) $row['size']; - $limit = $size - $threshold; - - if($limit > 0) { - $params = [$status, $row['feed_id'], $limit]; - - $sql = 'DELETE FROM `*PREFIX*news_items` ' . - '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); - } - } - - } + public function __construct(IDb $db){ + parent::__construct($db); + } + + + /** + * Delete all items for feeds that have over $threshold unread and not + * starred items + */ + public function deleteReadOlderThanThreshold($threshold){ + $status = StatusFlag::STARRED | StatusFlag::UNREAD; + $sql = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' . + '`items`.`feed_id` AS `feed_id` ' . + 'FROM `*PREFIX*news_items` `items` ' . + 'JOIN `*PREFIX*news_feeds` `feeds` ' . + 'ON `feeds`.`id` = `items`.`feed_id` ' . + 'WHERE NOT ((`items`.`status` & ?) > 0) ' . + 'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' . + 'HAVING COUNT(*) > ?'; + $params = [$status, $threshold]; + $result = $this->execute($sql, $params); + + while($row = $result->fetch()) { + + $size = (int) $row['size']; + $limit = $size - $threshold; + + if($limit > 0) { + $params = [$status, $row['feed_id'], $limit]; + + $sql = 'DELETE FROM `*PREFIX*news_items` ' . + '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); + } + } + + } } \ No newline at end of file diff --git a/db/statusflag.php b/db/statusflag.php index d24f81275..a3bfd4b18 100644 --- a/db/statusflag.php +++ b/db/statusflag.php @@ -14,30 +14,30 @@ namespace OCA\News\Db; class StatusFlag { - const UNREAD = 0x02; - const STARRED = 0x04; - const DELETED = 0x08; - const UPDATED = 0x16; - - - /** - * Get status for query - */ - public function typeToStatus($type, $showAll){ - if($type === FeedType::STARRED){ - return self::STARRED; - } else { - $status = 0; - } - - if($showAll){ - $status &= ~self::UNREAD; - } else { - $status |= self::UNREAD; - } - - return $status; - } + const UNREAD = 0x02; + const STARRED = 0x04; + const DELETED = 0x08; + const UPDATED = 0x16; + + + /** + * Get status for query + */ + public function typeToStatus($type, $showAll){ + if($type === FeedType::STARRED){ + return self::STARRED; + } else { + $status = 0; + } + + if($showAll){ + $status &= ~self::UNREAD; + } else { + $status |= self::UNREAD; + } + + return $status; + } } \ No newline at end of file -- cgit v1.2.3