From 10e8c28feaf6d858948285a291231f651ef74728 Mon Sep 17 00:00:00 2001 From: Sean Molenaar Date: Sun, 4 Oct 2020 20:45:33 +0200 Subject: Add migration with foreign keys Closes #829 Signed-off-by: Sean Molenaar --- lib/Db/Feed.php | 12 +++++++----- lib/Db/FeedMapper.php | 4 ++-- lib/Db/FeedMapperV2.php | 18 ++++++++++++------ lib/Db/Folder.php | 2 +- lib/Db/FolderMapperV2.php | 1 + lib/Db/ItemMapper.php | 22 ++++++++++++---------- 6 files changed, 35 insertions(+), 24 deletions(-) (limited to 'lib/Db') diff --git a/lib/Db/Feed.php b/lib/Db/Feed.php index 852de4c78..473912acc 100644 --- a/lib/Db/Feed.php +++ b/lib/Db/Feed.php @@ -37,7 +37,7 @@ class Feed extends Entity implements IAPI, \JsonSerializable protected $faviconLink = null; /** @var int|null */ protected $added = 0; - /** @var int */ + /** @var int|null */ protected $folderId; /** @var int */ protected $unreadCount; @@ -152,9 +152,9 @@ class Feed extends Entity implements IAPI, \JsonSerializable } /** - * @return int + * @return int|null */ - public function getFolderId(): int + public function getFolderId(): ?int { return $this->folderId; } @@ -416,9 +416,11 @@ class Feed extends Entity implements IAPI, \JsonSerializable } /** - * @param int $folderId + * @param int|null $folderId + * + * @return Feed */ - public function setFolderId(int $folderId): Feed + public function setFolderId(?int $folderId): Feed { if ($this->folderId !== $folderId) { $this->folderId = $folderId; diff --git a/lib/Db/FeedMapper.php b/lib/Db/FeedMapper.php index 867ba982d..193cf1f5d 100644 --- a/lib/Db/FeedMapper.php +++ b/lib/Db/FeedMapper.php @@ -77,7 +77,7 @@ class FeedMapper extends NewsMapper // think twice when changing this 'AND `items`.`unread` = ? ' . 'WHERE `feeds`.`user_id` = ? ' . - 'AND (`feeds`.`folder_id` = 0 ' . + 'AND (`feeds`.`folder_id` IS NULL ' . 'OR `folders`.`deleted_at` = 0 ' . ') ' . 'AND `feeds`.`deleted_at` = 0 ' . @@ -106,7 +106,7 @@ class FeedMapper extends NewsMapper // POSSIBLE SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT. // think twice when changing this 'AND `items`.`unread` = ? ' . - 'WHERE (`feeds`.`folder_id` = 0 ' . + 'WHERE (`feeds`.`folder_id` IS NULL ' . 'OR `folders`.`deleted_at` = 0 ' . ') ' . 'AND `feeds`.`deleted_at` = 0 ' . diff --git a/lib/Db/FeedMapperV2.php b/lib/Db/FeedMapperV2.php index a7edecd88..05b272112 100644 --- a/lib/Db/FeedMapperV2.php +++ b/lib/Db/FeedMapperV2.php @@ -43,6 +43,7 @@ class FeedMapperV2 extends NewsMapperV2 * Find all feeds for a user. * * @param string $userId The user identifier + * @param array $params Filter parameters * * @return Entity[] */ @@ -62,7 +63,7 @@ class FeedMapperV2 extends NewsMapperV2 * Find all feeds for a user. * * @param string $userId The user identifier - * @param int $id The feed identifier + * @param int $id The feed identifier * * @return Entity * @@ -124,17 +125,22 @@ class FeedMapperV2 extends NewsMapperV2 /** * Find all feeds in a folder * - * @param int $id ID of the folder + * @param int|null $id ID of the folder * * @return Feed[] */ - public function findAllFromFolder(int $id): array + public function findAllFromFolder(?int $id): array { $builder = $this->db->getQueryBuilder(); $builder->addSelect('*') - ->from($this->tableName) - ->where('folder_id = :folder_id') - ->setParameter(':folder_id', $id); + ->from($this->tableName); + + if (is_null($id)) { + $builder->where('folder_id IS NULL'); + } else { + $builder->where('folder_id = :folder_id') + ->setParameter(':folder_id', $id); + } return $this->findEntities($builder); } diff --git a/lib/Db/Folder.php b/lib/Db/Folder.php index 2efd65e96..d452bb7d4 100644 --- a/lib/Db/Folder.php +++ b/lib/Db/Folder.php @@ -141,7 +141,7 @@ class Folder extends Entity implements IAPI, \JsonSerializable return $this; } - public function setParentId(int $parentId = 0): self + public function setParentId(?int $parentId = null): self { if ($this->parentId !== $parentId) { $this->parentId = $parentId; diff --git a/lib/Db/FolderMapperV2.php b/lib/Db/FolderMapperV2.php index 7d0536607..913bd9d70 100644 --- a/lib/Db/FolderMapperV2.php +++ b/lib/Db/FolderMapperV2.php @@ -41,6 +41,7 @@ class FolderMapperV2 extends NewsMapperV2 * Find all feeds for a user. * * @param string $userId The user identifier + * @param array $params Filter parameters * * @return Entity[] */ diff --git a/lib/Db/ItemMapper.php b/lib/Db/ItemMapper.php index 270919b44..65c6e0b15 100644 --- a/lib/Db/ItemMapper.php +++ b/lib/Db/ItemMapper.php @@ -55,7 +55,7 @@ class ItemMapper extends NewsMapper $prependTo . 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' . 'ON `folders`.`id` = `feeds`.`folder_id` ' . - 'WHERE `feeds`.`folder_id` = 0 ' . + 'WHERE `feeds`.`folder_id` IS NULL ' . 'OR `folders`.`deleted_at` = 0 ' . 'ORDER BY `items`.`id` ' . $ordering; } @@ -125,7 +125,7 @@ class ItemMapper extends NewsMapper 'AND `items`.`starred` = ? ' . 'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' . 'ON `folders`.`id` = `feeds`.`folder_id` ' . - 'WHERE `feeds`.`folder_id` = 0 ' . + 'WHERE `feeds`.`folder_id` IS NULL ' . 'OR `folders`.`deleted_at` = 0'; $params = [$userId, true]; @@ -151,14 +151,15 @@ class ItemMapper extends NewsMapper } - public function readFolder($folderId, $highestItemId, $time, $userId) + public function readFolder(?int $folderId, $highestItemId, $time, $userId) { + $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` = ? ' . + "WHERE `folder_id` ${folderWhere} ? " . 'AND `user_id` = ? ' . ') ' . 'AND `id` <= ?'; @@ -207,11 +208,12 @@ class ItemMapper extends NewsMapper } - public function findAllNewFolder($id, $updatedSince, $showAll, $userId) + public function findAllNewFolder(?int $id, $updatedSince, $showAll, $userId) { $sql = $this->buildStatusQueryPart($showAll); - $sql .= 'AND `feeds`.`folder_id` = ? ' . + $folderWhere = is_null($id) ? 'IS' : '='; + $sql .= "AND `feeds`.`folder_id` ${$folderWhere} ? " . 'AND `items`.`last_modified` >= ? '; $sql = $this->makeSelectQuery($sql); $params = [$userId, $id, $updatedSince]; @@ -270,7 +272,7 @@ class ItemMapper extends NewsMapper public function findAllFolder( - $id, + ?int $id, $limit, $offset, $showAll, @@ -285,10 +287,10 @@ class ItemMapper extends NewsMapper $sql = $this->buildStatusQueryPart($showAll); $sql .= $this->buildSearchQueryPart($search); - $sql .= 'AND `feeds`.`folder_id` = ? '; + $folderWhere = is_null($id) ? 'IS' : '='; + $sql .= "AND `feeds`.`folder_id` ${folderWhere} ? "; if ($offset !== 0) { - $sql .= 'AND `items`.`id` ' . - $this->getOperator($oldestFirst) . ' ? '; + $sql .= 'AND `items`.`id` ' . $this->getOperator($oldestFirst) . ' ? '; $params[] = $offset; } $sql = $this->makeSelectQuery($sql, $oldestFirst, $search); -- cgit v1.2.3