Add migration with foreign keys

Closes #829

Signed-off-by: Sean Molenaar <sean@seanmolenaar.eu>

6 files changed, 35 insertions, 24 deletions

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);