diff options
author | Bernhard Posselt <nukeawhale@gmail.com> | 2013-03-22 16:08:19 +0100 |
---|---|---|
committer | Bernhard Posselt <nukeawhale@gmail.com> | 2013-03-22 16:08:19 +0100 |
commit | 91a327f5c687014fe20b531b9a42b98fe151d017 (patch) | |
tree | a5748f77c612f2b4af6fb36db0b72729c57c52b3 | |
parent | 585667bdf4a06a7bcde1b7d2b9b42059135343dc (diff) |
implemented feedRead
-rw-r--r-- | db/itemmapper.php | 159 | ||||
-rw-r--r-- | tests/db/ItemMapperTest.php | 201 |
2 files changed, 190 insertions, 170 deletions
diff --git a/db/itemmapper.php b/db/itemmapper.php index ee57e81e9..03bc9e1cd 100644 --- a/db/itemmapper.php +++ b/db/itemmapper.php @@ -50,6 +50,87 @@ class ItemMapper extends Mapper implements IMapper { return $items; } + + public function find($id, $userId){ + $sql = 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . + 'JOIN `*dbprefix*news_feeds` ' . + 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . + 'WHERE `*dbprefix*news_items`.`id` = ? ' . + 'AND `*dbprefix*news_feeds`.`user_id` = ? '; + + $row = $this->findQuery($sql, array($id, $userId)); + + $item = new Item(); + $item->fromRow($row); + + return $item; + } + + + public function starredCount($userId){ + $sql = 'SELECT COUNT(*) AS size FROM `*dbprefix*news_feeds` `feeds` ' . + 'JOIN `*dbprefix*news_items` `items` ' . + 'ON `items`.`feed_id` = `feeds`.`id` ' . + 'WHERE `feeds`.`user_id` = ? ' . + 'AND ((`items`.`status` & ?) > 0)'; + + $params = array($userId, StatusFlag::STARRED); + + $result = $this->execute($sql, $params)->fetchRow(); + + return $result['size']; + } + + + public function readFeed($feedId, $userId){ + $sql = 'UPDATE `*dbprefix*news_feeds` `feeds` ' . + 'JOIN `*dbprefix*news_items` `items` ' . + 'ON `items`.`feed_id` = `feeds`.`id` ' . + 'SET `items`.`status` = (`items`.`status` & ?) ' . + 'WHERE `feeds`.`user_id` = ? ' . + 'AND `items`.`id` = ?'; + $params = array(~StatusFlag::UNREAD, $userId, $feedId); + + $this->execute($sql, $params); + } + + +} + /** + * Queries to find all items from a folder that belongs to a user + */ + /*private function makeFindAllFromFolderQuery($custom) { + return 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . + 'JOIN `*dbprefix*news_feeds` ' . + 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . + 'WHERE `*dbprefix*news_feeds`.`user_id` = ? ' . + 'AND `*dbprefix*news_feeds`.`folder_id` = ? ' . + 'AND ((`*dbprefix*news_items`.`status` & ?) > 0) ' . + $custom; + } + + public function findAllFromFolderByOffset($userId, $folderId, $status, $limit=null, $offset=null) { + $sql = $this->makeFindAllFromFolderQuery(''); + $params = array($userId, $folderId, $status); + return $this->findAllRows($sql, $params, $limit, $offset); + } + + public function findAllFromFolderByLastMofified($userId, $folderId, $status, $lastModified) { + $sql = $this->makeFindAllFromFolderQuery(' AND (`*dbprefix*news_items`.`last_modified` >= ?)'); + $params = array($userId, $folderId, $status, $lastModified); + return $this->findAllRows($sql, $params); + } + + + /* + request: get all items of a folder of a user (unread and read) + SELECT * FROM items + JOIN feeds + ON feed.id = feed_id + WHERE user_id = ? AND status = ? AND feed.folder_id = ? + (AND id < ? LIMIT ?) + (AND items.lastmodified >= ?) + */ /** * Queries to find all items that belong to a user */ @@ -84,84 +165,6 @@ class ItemMapper extends Mapper implements IMapper { // $params = array($feedId, $userId, $limit, $offset); // return $this->findAllRows($sql, $params); // } - - /** - * Queries to find all items from a folder that belongs to a user - */ - private function makeFindAllFromFolderQuery($custom) { - return 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . - 'JOIN `*dbprefix*news_feeds` ' . - 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . - 'WHERE `*dbprefix*news_feeds`.`user_id` = ? ' . - 'AND `*dbprefix*news_feeds`.`folder_id` = ? ' . - 'AND ((`*dbprefix*news_items`.`status` & ?) > 0) ' . - $custom; - } - - public function findAllFromFolderByOffset($userId, $folderId, $status, $limit=null, $offset=null) { - $sql = $this->makeFindAllFromFolderQuery(''); - $params = array($userId, $folderId, $status); - return $this->findAllRows($sql, $params, $limit, $offset); - } - - public function findAllFromFolderByLastMofified($userId, $folderId, $status, $lastModified) { - $sql = $this->makeFindAllFromFolderQuery(' AND (`*dbprefix*news_items`.`last_modified` >= ?)'); - $params = array($userId, $folderId, $status, $lastModified); - return $this->findAllRows($sql, $params); - } - - - - /* - request: get all items of a folder of a user (unread and read) - SELECT * FROM items - JOIN feeds - ON feed.id = feed_id - WHERE user_id = ? AND status = ? AND feed.folder_id = ? - (AND id < ? LIMIT ?) - (AND items.lastmodified >= ?) - */ - - - - public function find($id, $userId){ - $sql = 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . - 'JOIN `*dbprefix*news_feeds` ' . - 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . - 'WHERE `*dbprefix*news_items`.`id` = ? ' . - 'AND `*dbprefix*news_feeds`.`user_id` = ? '; - - $row = $this->findQuery($sql, array($id, $userId)); - - $item = new Item(); - $item->fromRow($row); - - return $item; - } - - - public function readFeed($feedId, $userId){ - // TODO - } - - - public function starredCount($userId){ - $sql = 'SELECT COUNT(*) AS size FROM `*dbprefix*news_feeds` `feeds` ' . - 'JOIN `*dbprefix*news_items` `items` ' . - 'ON `items`.`feed_id` = `feeds`.`id`' . - 'WHERE `feeds`.`user_id` = ? ' . - 'AND ((`items`.`status` & ?) > 0)'; - - $params = array($userId, StatusFlag::STARRED); - - $result = $this->execute($sql, $params)->fetchRow(); - - return $result['size']; - } - -} - - /** * This class maps an item to a row of the items table in the database. * It follows the Data Mapper pattern (see http://martinfowler.com/eaaCatalog/dataMapper.html). diff --git a/tests/db/ItemMapperTest.php b/tests/db/ItemMapperTest.php index ea9b15815..9d16d6897 100644 --- a/tests/db/ItemMapperTest.php +++ b/tests/db/ItemMapperTest.php @@ -59,10 +59,23 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility { $this->rows = array( array('id' => $this->items[0]->getId()), array('id' => $this->items[1]->getId()) - ); - + ); + + $this->user = 'john'; } + + private function makeFindAllFromFolderQuery($custom) { + return 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . + 'JOIN `*dbprefix*news_feeds` ' . + 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . + 'WHERE `*dbprefix*news_feeds`.`user_id` = ? ' . + 'AND `*dbprefix*news_feeds`.`folder_id` = ? ' . + 'AND ((`*dbprefix*news_items`.`status` & ?) > 0) ' . + $custom; + } + + public function testFind(){ $sql = 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . 'JOIN `*dbprefix*news_feeds` ' . @@ -77,7 +90,100 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility { } + + public function testGetStarredCount(){ + $userId = 'john'; + $row = array( + array('size' => 9) + ); + $sql = 'SELECT COUNT(*) AS size FROM `*dbprefix*news_feeds` `feeds` ' . + 'JOIN `*dbprefix*news_items` `items` ' . + 'ON `items`.`feed_id` = `feeds`.`id` ' . + 'WHERE `feeds`.`user_id` = ? ' . + 'AND ((`items`.`status` & ?) > 0)'; + + $this->setMapperResult($sql, array($userId, StatusFlag::STARRED), $row); + + $result = $this->mapper->starredCount($userId); + $this->assertEquals($row[0]['size'], $result); + } + + + public function testReadFeed(){ + $sql = 'UPDATE `*dbprefix*news_feeds` `feeds` ' . + 'JOIN `*dbprefix*news_items` `items` ' . + 'ON `items`.`feed_id` = `feeds`.`id` ' . + 'SET `items`.`status` = (`items`.`status` & ?) ' . + 'WHERE `feeds`.`user_id` = ? ' . + 'AND `items`.`id` = ?'; + $this->setMapperResult($sql, array(~StatusFlag::UNREAD, $this->user, 3)); + $this->mapper->readFeed(3, $this->user); + } + +/* + public function testFindAllFromFolder() { + $sql = $this->makeFindAllFromFolderQuery(''); + + $status = 2; + + $params = array($this->userId, $this->folderId, $status); + $this->setMapperResult($sql, $params, $this->rows); + $result = $this->mapper->findAllFromFolderByOffset($this->userId, $this->folderId, $status); + $this->assertEquals($this->items, $result); + + } + + public function testFindAllFromFolderByOffset() { + $sql = $this->makeFindAllFromFolderQuery(''); + + $status = 2; + $limit = 10; + $offset = 10; + + $params = array($this->userId, $this->folderId, $status); + $this->setMapperResult($sql, $params, $this->rows); + $result = $this->mapper->findAllFromFolderByOffset($this->userId, $this->folderId, $status, $limit, $offset); + $this->assertEquals($this->items, $result); + + } + + public function testFindAllFromFolderByLastModified() { + $sql = $this->makeFindAllFromFolderQuery(' AND (`*dbprefix*news_items`.`last_modified` >= ?)'); + + $status = 2; + $lastModified = 100; + + $params = array($this->userId, $this->folderId, $status, $lastModified); + $this->setMapperResult($sql, $params, $this->rows); + $result = $this->mapper->findAllFromFolderByLastMofified($this->userId, $this->folderId, $status, $lastModified); + $this->assertEquals($this->items, $result); + + }*/ + +} + + + + +// TBD +// } +// +// public function testFindAllFromFolderByLastModified() { +// $userId = 'john'; +// $folderId = 3; +// $lastModified = 123; +// +// $sql = 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . +// 'JOIN `*dbprefix*news_feeds` ' . +// 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . +// 'WHERE `*dbprefix*news_feeds`.`user_id` = ? ' . +// 'AND `*dbprefix*news_feeds`.`folder_id` = ? ' . +// 'AND `*dbprefix*news_items`.last_modified >= ? '; +// +// $this->setMapperResult($sql, array($userId, $folderId, $lastModified)); +// $result = $this->mapper->findAllFromFolderByLastMofified($userId, $folderId, $lastModified); +// } // // public function testFindNotFound(){ // $sql = 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . @@ -144,93 +250,4 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility { // $result = $this->mapper->findAllFromFeedByStatus($feedId, $userId, $status); // $this->assertEquals($this->items, $result); // -// } - - private function makeFindAllFromFolderQuery($custom) { - return 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . - 'JOIN `*dbprefix*news_feeds` ' . - 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . - 'WHERE `*dbprefix*news_feeds`.`user_id` = ? ' . - 'AND `*dbprefix*news_feeds`.`folder_id` = ? ' . - 'AND ((`*dbprefix*news_items`.`status` & ?) > 0) ' . - $custom; - } - - public function testFindAllFromFolder() { - $sql = $this->makeFindAllFromFolderQuery(''); - - $status = 2; - - $params = array($this->userId, $this->folderId, $status); - $this->setMapperResult($sql, $params, $this->rows); - $result = $this->mapper->findAllFromFolderByOffset($this->userId, $this->folderId, $status); - $this->assertEquals($this->items, $result); - - } - - public function testFindAllFromFolderByOffset() { - $sql = $this->makeFindAllFromFolderQuery(''); - - $status = 2; - $limit = 10; - $offset = 10; - - $params = array($this->userId, $this->folderId, $status); - $this->setMapperResult($sql, $params, $this->rows); - $result = $this->mapper->findAllFromFolderByOffset($this->userId, $this->folderId, $status, $limit, $offset); - $this->assertEquals($this->items, $result); - - } - - public function testFindAllFromFolderByLastModified() { - $sql = $this->makeFindAllFromFolderQuery(' AND (`*dbprefix*news_items`.`last_modified` >= ?)'); - - $status = 2; - $lastModified = 100; - - $params = array($this->userId, $this->folderId, $status, $lastModified); - $this->setMapperResult($sql, $params, $this->rows); - $result = $this->mapper->findAllFromFolderByLastMofified($this->userId, $this->folderId, $status, $lastModified); - $this->assertEquals($this->items, $result); - - } - -// } -// -// public function testFindAllFromFolderByLastModified() { -// $userId = 'john'; -// $folderId = 3; -// $lastModified = 123; -// -// $sql = 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . -// 'JOIN `*dbprefix*news_feeds` ' . -// 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . -// 'WHERE `*dbprefix*news_feeds`.`user_id` = ? ' . -// 'AND `*dbprefix*news_feeds`.`folder_id` = ? ' . -// 'AND `*dbprefix*news_items`.last_modified >= ? '; -// -// $this->setMapperResult($sql, array($userId, $folderId, $lastModified)); -// $result = $this->mapper->findAllFromFolderByLastMofified($userId, $folderId, $lastModified); -// } - - - - public function testGetStarredCount(){ - $userId = 'john'; - $row = array( - array('size' => 9) - ); - $sql = 'SELECT COUNT(*) AS size FROM `*dbprefix*news_feeds` `feeds` ' . - 'JOIN `*dbprefix*news_items` `items` ' . - 'ON `items`.`feed_id` = `feeds`.`id`' . - 'WHERE `feeds`.`user_id` = ? ' . - 'AND ((`items`.`status` & ?) > 0)'; - - $this->setMapperResult($sql, array($userId, StatusFlag::STARRED), $row); - - $result = $this->mapper->starredCount($userId); - $this->assertEquals($row[0]['size'], $result); - } - - -}
\ No newline at end of file +// }
\ No newline at end of file |