update item ids in batches

3 files changed, 36 insertions, 13 deletions

diff --git a/appinfo/info.xml b/appinfo/info.xml
index 0f47f335b..85a712760 100644
--- a/appinfo/info.xml
+++ b/appinfo/info.xml
@@ -7,7 +7,7 @@
     <author>Bernhard Posselt, Alessandro Cosentino, Jan-Christoph Borchardt</author>
     <category>multimedia</category>
     <licence>AGPL</licence>
-    <version>6.1.4</version>
+    <version>6.1.5</version>
     <namespace>News</namespace>
 
     <!-- resources -->
diff --git a/db/itemmapper.php b/db/itemmapper.php
index 16d717963..738368f25 100644
--- a/db/itemmapper.php
+++ b/db/itemmapper.php
@@ -23,7 +23,7 @@ class ItemMapper extends NewsMapper {
     }
 
 
-    private function makeSelectQuery($prependTo, $oldestFirst=false,
+    private function makeSelectQuery($prependTo='', $oldestFirst=false,
                                      $distinctFingerprint=false){
         if($oldestFirst) {
             $ordering = 'ASC';
@@ -71,12 +71,16 @@ class ItemMapper extends NewsMapper {
         }, $search);
     }
 
+    /**
+     * @param int $id
+     * @param string $userId
+     * @return \OCA\News\Db\Item
+     */
     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` ' .
@@ -337,12 +341,38 @@ class ItemMapper extends NewsMapper {
     /**
      * Returns a list of ids and userid of all items
      */
-    public function findAllItemIdsAndUsers() {
+    public function findAllItemIdsAndUsers($limit=null, $offset=null) {
         $sql = 'SELECT `items`.`id`, `feeds`.`user_id` ' .
                 'FROM `*PREFIX*news_items` `items` ' .
                 'JOIN `*PREFIX*news_feeds` `feeds` ' .
                     'ON `items`.`feed_id` = `feeds`.`id`';
-        return $this->execute($sql)->fetchAll();
+        return $this->execute($sql, [], $limit, $offset)->fetchAll();
+    }
+
+    /**
+     * Update search indices of all items
+     */
+    public function updateSearchIndices() {
+        // update indices in steps to prevent memory issues on larger systems
+        $step = 1000;  // update 1000 items at a time
+        $itemCount = 1;
+        $offset = 0;
+
+        // stop condition if there are no previously fetched items
+        while ($itemCount > 0) {
+            $items = $this->findAllItemIdsAndUsers($step, $offset);
+            $itemCount = count($items);
+            $this->updateSearchIndex($items);
+            $offset += $step;
+        }
+    }
+
+    private function updateSearchIndex(array $items=[]) {
+        foreach ($items as $row) {
+            $item = $this->find($row['id'], $row['user_id']);
+            $item->generateSearchIndex();
+            $this->update($item);
+        }
     }
 
 
diff --git a/service/itemservice.php b/service/itemservice.php
index ec6bee8bd..95a07d425 100644
--- a/service/itemservice.php
+++ b/service/itemservice.php
@@ -260,14 +260,7 @@ class ItemService extends Service {
      * Regenerates the search index for all items
      */
     public function generateSearchIndices() {
-        $rows = $this->itemMapper->findAllItemIdsAndUsers();
-
-        foreach ($rows as $row) {
-            $item = $this->find($row['id'], $row['user_id']);
-            $item->generateSearchIndex();
-            $this->itemMapper->update($item);
-        }
-
+        $this->itemMapper->updateSearchIndices();
     }
 
 }