✨ Sharing: update ownership in sql (sharedwith)

Signed-off-by: Marco Nassabain <marco.nassabain@hotmail.com>

2 files changed, 228 insertions, 15 deletions

diff --git a/lib/Db/FeedMapper.php b/lib/Db/FeedMapper.php
new file mode 100644
index 000000000..45901b8d0
--- /dev/null
+++ b/lib/Db/FeedMapper.php
@@ -0,0 +1,204 @@
+<?php
+/**
+ * Nextcloud - News
+ *
+ * This file is licensed under the Affero General Public License version 3 or
+ * later. See the COPYING file.
+ *
+ * @author    Alessandro Cosentino <cosenal@gmail.com>
+ * @author    Bernhard Posselt <dev@bernhard-posselt.com>
+ * @copyright 2012 Alessandro Cosentino
+ * @copyright 2012-2014 Bernhard Posselt
+ */
+
+namespace OCA\News\Db;
+
+use OCA\News\Utility\Time;
+use OCP\AppFramework\Db\DoesNotExistException;
+use OCP\AppFramework\Db\MultipleObjectsReturnedException;
+use OCP\IDBConnection;
+use OCP\AppFramework\Db\Entity;
+
+/**
+ * Class LegacyFeedMapper
+ *
+ * @package OCA\News\Db
+ * @deprecated use FeedMapper
+ */
+class FeedMapper extends NewsMapper
+{
+    const TABLE_NAME = 'news_feeds';
+
+    public function __construct(IDBConnection $db, Time $time)
+    {
+        parent::__construct($db, $time, Feed::class);
+    }
+
+
+    public function find(string $userId, int $id)
+    {
+        $sql = 'SELECT `feeds`.*, `item_numbers`.`unread_count` ' .
+            'FROM `*PREFIX*news_feeds` `feeds` ' .
+            'JOIN ( ' .
+                'SELECT `feeds`.`id`, 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`.`unread` = ? ' .
+                'WHERE `feeds`.`id` = ? ' .
+                  'AND `feeds`.`user_id` = ? ' .
+                'GROUP BY `feeds`.`id` ' .
+            ') `item_numbers` ' .
+            'ON `item_numbers`.`id` = `feeds`.`id` ';
+        $params = [true, $id, $userId];
+
+        return $this->findEntity($sql, $params);
+    }
+
+
+    public function findAllFromUser(string $userId): array
+    {
+        $sql = 'SELECT `feeds`.*, `item_numbers`.`unread_count` ' .
+            'FROM `*PREFIX*news_feeds` `feeds` ' .
+            'JOIN ( ' .
+                'SELECT `feeds`.`id`, 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`.`unread` = ? ' .
+                'WHERE (`feeds`.`user_id` = ? OR `feeds`.`id` IN (' .
+                    'SELECT `feed_id` FROM `*PREFIX*news_items` WHERE `shared_with` = ?)' .
+                ') ' .
+                'AND (`feeds`.`folder_id` IS NULL ' .
+                'OR `folders`.`deleted_at` = 0 ' .
+                ') ' .
+                'AND `feeds`.`deleted_at` = 0 ' .
+                'GROUP BY `feeds`.`id` ' .
+            ') `item_numbers` ' .
+            'ON `item_numbers`.`id` = `feeds`.`id` ';
+        $params = [true, $userId, $userId];
+
+        return $this->findEntities($sql, $params);
+    }
+
+
+    public function findAll(): array
+    {
+        $sql = 'SELECT `feeds`.*, `item_numbers`.`unread_count` ' .
+            'FROM `*PREFIX*news_feeds` `feeds` ' .
+            'JOIN ( ' .
+                'SELECT `feeds`.`id`, 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`.`unread` = ? ' .
+                'WHERE (`feeds`.`folder_id` IS NULL ' .
+                   'OR `folders`.`deleted_at` = 0 ' .
+                ') ' .
+                'AND `feeds`.`deleted_at` = 0 ' .
+                'GROUP BY `feeds`.`id` ' .
+            ') `item_numbers` ' .
+            'ON `item_numbers`.`id` = `feeds`.`id` ';
+
+        return $this->findEntities($sql, [true]);
+    }
+
+
+    public function findByUrlHash($hash, $userId)
+    {
+        $sql = 'SELECT `feeds`.*, `item_numbers`.`unread_count` ' .
+            'FROM `*PREFIX*news_feeds` `feeds` ' .
+            'JOIN ( ' .
+                'SELECT `feeds`.`id`, 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`.`unread` = ? ' .
+                'WHERE `feeds`.`url_hash` = ? ' .
+                  'AND `feeds`.`user_id` = ? ' .
+                'GROUP BY `feeds`.`id` ' .
+            ') `item_numbers` ' .
+            'ON `item_numbers`.`id` = `feeds`.`id` ';
+        $params = [true, $hash, $userId];
+
+        return $this->findEntity($sql, $params);
+    }
+
+
+    public function delete(Entity $entity): 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);
+
+        return parent::delete($entity);
+    }
+
+
+    /**
+     * @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 findFromUser(string $userId, int $id): Entity
+    {
+        return $this->find($id, $userId);
+    }
+}
diff --git a/lib/Db/ItemMapper.php b/lib/Db/ItemMapper.php
index d5ffc41a5..6a6f44725 100644
--- a/lib/Db/ItemMapper.php
+++ b/lib/Db/ItemMapper.php
@@ -51,7 +51,10 @@ class ItemMapper extends NewsMapper
         'JOIN `*PREFIX*news_feeds` `feeds` ' .
         'ON `feeds`.`id` = `items`.`feed_id` ' .
         'AND `feeds`.`deleted_at` = 0 ' .
-        'AND `feeds`.`user_id` = ? ' .
+        'AND ('.
+            '(`feeds`.`user_id` = ? AND `items`.`shared_by` LIKE \'\')'.
+            ' XOR `items`.`shared_with` = ?' .
+        ') ' .
         $prependTo .
         'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' .
         'ON `folders`.`id` = `feeds`.`folder_id` ' .
@@ -112,7 +115,7 @@ class ItemMapper extends NewsMapper
     public function find(string $userId, int $id)
     {
         $sql = $this->makeSelectQuery('AND `items`.`id` = ? ');
-        return $this->findEntity($sql, [$userId, $id]);
+        return $this->findEntity($sql, [$userId, $userId, $id]);
     }
 
     public function starredCount(string $userId)
@@ -121,14 +124,17 @@ class ItemMapper extends NewsMapper
             'JOIN `*PREFIX*news_feeds` `feeds` ' .
             'ON `feeds`.`id` = `items`.`feed_id` ' .
             'AND `feeds`.`deleted_at` = 0 ' .
-            'AND `feeds`.`user_id` = ? ' .
+            'AND ('.
+                '(`feeds`.`user_id` = ? AND `items`.`shared_by` LIKE \'\')'.
+                ' XOR `items`.`shared_with` = ?' .
+            ') ' .
             'AND `items`.`starred` = ? ' .
             'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' .
             'ON `folders`.`id` = `feeds`.`folder_id` ' .
             'WHERE `feeds`.`folder_id` IS NULL ' .
             'OR `folders`.`deleted_at` = 0';
 
-        $params = [$userId, true];
+        $params = [$userId, $userId, true];
 
         $result = $this->execute($sql, $params)->fetch();
 
@@ -203,7 +209,7 @@ class ItemMapper extends NewsMapper
 
         $sql .= 'AND `items`.`last_modified` >= ? ';
         $sql = $this->makeSelectQuery($sql);
-        $params = [$userId, $updatedSince];
+        $params = [$userId, $userId, $updatedSince];
         return $this->findEntities($sql, $params);
     }
 
@@ -216,7 +222,7 @@ class ItemMapper extends NewsMapper
         $sql .= "AND `feeds`.`folder_id` ${$folderWhere} ? " .
             'AND `items`.`last_modified` >= ? ';
         $sql = $this->makeSelectQuery($sql);
-        $params = [$userId, $id, $updatedSince];
+        $params = [$userId, $userId, $id, $updatedSince];
         return $this->findEntities($sql, $params);
     }
 
@@ -228,7 +234,7 @@ class ItemMapper extends NewsMapper
         $sql .= 'AND `items`.`feed_id` = ? ' .
             'AND `items`.`last_modified` >= ? ';
         $sql = $this->makeSelectQuery($sql);
-        $params = [$userId, $id, $updatedSince];
+        $params = [$userId, $userId, $id, $updatedSince];
         return $this->findEntities($sql, $params);
     }
 
@@ -253,7 +259,7 @@ class ItemMapper extends NewsMapper
         $userId,
         $search = []
     ) {
-        $params = [$userId];
+        $params = [$userId, $userId];
         $params = array_merge($params, $this->buildLikeParameters($search));
         $params[] = $id;
 
@@ -280,7 +286,7 @@ class ItemMapper extends NewsMapper
         $userId,
         $search = []
     ) {
-        $params = [$userId];
+        $params = [$userId, $userId];
         $params = array_merge($params, $this->buildLikeParameters($search));
         $params[] = $id;
 
@@ -307,7 +313,7 @@ class ItemMapper extends NewsMapper
         $userId,
         $search = []
     ): array {
-        $params = [$userId];
+        $params = [$userId, $userId];
         $params = array_merge($params, $this->buildLikeParameters($search));
         $sql = $this->buildStatusQueryPart($showAll, $type);
         $sql .= $this->buildSearchQueryPart($search);
@@ -326,7 +332,7 @@ class ItemMapper extends NewsMapper
 
     public function findAllUnreadOrStarred($userId)
     {
-        $params = [$userId, true, true];
+        $params = [$userId, $userId, true, true];
         $sql = 'AND (`items`.`unread` = ? OR `items`.`starred` = ?) ';
         $sql = $this->makeSelectQuery($sql);
         return $this->findEntities($sql, $params);
@@ -340,7 +346,7 @@ class ItemMapper extends NewsMapper
             'AND `feeds`.`id` = ? '
         );
 
-        return $this->findEntity($sql, [$userId, $guidHash, $feedId]);
+        return $this->findEntity($sql, [$userId, $userId, $guidHash, $feedId]);
     }
 
 
@@ -481,9 +487,12 @@ class ItemMapper extends NewsMapper
                 WHERE `fingerprint` = ?
                     AND `feed_id` IN (
                         SELECT `f`.`id` FROM `*PREFIX*news_feeds` AS `f`
-                            WHERE `f`.`user_id` = ?
-                    )';
-            $params = [false, $lastModified, $item->getFingerprint(), $userId];
+                        WHERE ('.
+                            '(`f`.`user_id` = ? AND `shared_by` LIKE \'\')'.
+                            ' XOR `shared_with` = ?' .
+                        ')' .
+                    ')';
+            $params = [false, $lastModified, $item->getFingerprint(), $userId, $userId];
             $this->execute($sql, $params);
         } else {
             $item->setLastModified($lastModified);