Permissions: Updated generation querying to be more efficient

Query of existing entity permissions during view permission generation
could cause timeouts or SQL placeholder limits due to massive whereOr
query generation, where an "or where" clause would be created for each
entity type/id combo involved, which could be all within 20 books.

This updates the query handling to use a query per type involved, with
no "or where"s, and to be chunked at large entity counts.

Also tweaked role-specific permission regen to chunk books at
half-previous rate to prevent such a large scope being involved on each
chunk.

For #4695

diff --git a/app/Permissions/EntityPermissionEvaluator.php b/app/Permissions/EntityPermissionEvaluator.php
index 06f0126ad290908ffdc86a2d3c1b73583cc3f89e..98ec0330649f5374d0add8cd718b1e5290342cf6 100644 (file)
--- a/app/Permissions/EntityPermissionEvaluator.php
+++ b/app/Permissions/EntityPermissionEvaluator.php
@@ -9,11 +9,9 @@ use Illuminate\Database\Eloquent\Builder;
 
 class EntityPermissionEvaluator
 {
-    protected string $action;
-
-    public function __construct(string $action)
-    {
-        $this->action = $action;
+    public function __construct(
+        protected string $action
+    ) {
     }
 
     public function evaluateEntityForUser(Entity $entity, array $userRoleIds): ?bool
@@ -82,23 +80,25 @@ class EntityPermissionEvaluator
      */
     protected function getPermissionsMapByTypeId(array $typeIdChain, array $filterRoleIds): array
     {
-        $query = EntityPermission::query()->where(function (Builder $query) use ($typeIdChain) {
-            foreach ($typeIdChain as $typeId) {
-                $query->orWhere(function (Builder $query) use ($typeId) {
-                    [$type, $id] = explode(':', $typeId);
-                    $query->where('entity_type', '=', $type)
-                        ->where('entity_id', '=', $id);
-                });
+        $idsByType = [];
+        foreach ($typeIdChain as $typeId) {
+            [$type, $id] = explode(':', $typeId);
+            if (!isset($idsByType[$type])) {
+                $idsByType[$type] = [];
             }
-        });
 
-        if (!empty($filterRoleIds)) {
-            $query->where(function (Builder $query) use ($filterRoleIds) {
-                $query->whereIn('role_id', [...$filterRoleIds, 0]);
-            });
+            $idsByType[$type][] = $id;
         }
 
-        $relevantPermissions = $query->get(['entity_id', 'entity_type', 'role_id', $this->action])->all();
+        $relevantPermissions = [];
+
+        foreach ($idsByType as $type => $ids) {
+            $idsChunked = array_chunk($ids, 10000);
+            foreach ($idsChunked as $idChunk) {
+                $permissions = $this->getPermissionsForEntityIdsOfType($type, $idChunk, $filterRoleIds);
+                array_push($relevantPermissions, ...$permissions);
+            }
+        }
 
         $map = [];
         foreach ($relevantPermissions as $permission) {
@@ -113,6 +113,26 @@ class EntityPermissionEvaluator
         return $map;
     }
 
+    /**
+     * @param string[] $ids
+     * @param int[] $filterRoleIds
+     * @return EntityPermission[]
+     */
+    protected function getPermissionsForEntityIdsOfType(string $type, array $ids, array $filterRoleIds): array
+    {
+        $query = EntityPermission::query()
+            ->where('entity_type', '=', $type)
+            ->whereIn('entity_id', $ids);
+
+        if (!empty($filterRoleIds)) {
+            $query->where(function (Builder $query) use ($filterRoleIds) {
+                $query->whereIn('role_id', [...$filterRoleIds, 0]);
+            });
+        }
+
+        return $query->get(['entity_id', 'entity_type', 'role_id', $this->action])->all();
+    }
+
     /**
      * @return string[]
      */

diff --git a/app/Permissions/JointPermissionBuilder.php b/app/Permissions/JointPermissionBuilder.php
index 945909631b7312934fb8f00ec3c91e0eae82aa91..8c961fb13061bc7f4169419fd13841c1ad363c79 100644 (file)
--- a/app/Permissions/JointPermissionBuilder.php
+++ b/app/Permissions/JointPermissionBuilder.php
@@ -83,13 +83,13 @@ class JointPermissionBuilder
         $role->load('permissions');
 
         // Chunk through all books
-        $this->bookFetchQuery()->chunk(20, function ($books) use ($roles) {
+        $this->bookFetchQuery()->chunk(10, function ($books) use ($roles) {
             $this->buildJointPermissionsForBooks($books, $roles);
         });
 
         // Chunk through all bookshelves
         Bookshelf::query()->select(['id', 'owned_by'])
-            ->chunk(50, function ($shelves) use ($roles) {
+            ->chunk(100, function ($shelves) use ($roles) {
                 $this->createManyJointPermissions($shelves->all(), $roles);
             });
     }

diff --git a/database/seeders/LargeContentSeeder.php b/database/seeders/LargeContentSeeder.php
index bb9b087d2951201e871de2f84a14a24bfdc1cde8..ac551dd933abdc11ddecd06695eb6beeff29ef85 100644 (file)
--- a/database/seeders/LargeContentSeeder.php
+++ b/database/seeders/LargeContentSeeder.php
@@ -28,12 +28,18 @@ class LargeContentSeeder extends Seeder
 
         /** @var Book $largeBook */
         $largeBook = Book::factory()->create(['name' => 'Large book' . Str::random(10), 'created_by' => $editorUser->id, 'updated_by' => $editorUser->id]);
-        $pages = Page::factory()->count(200)->make(['created_by' => $editorUser->id, 'updated_by' => $editorUser->id]);
         $chapters = Chapter::factory()->count(50)->make(['created_by' => $editorUser->id, 'updated_by' => $editorUser->id]);
-
-        $largeBook->pages()->saveMany($pages);
         $largeBook->chapters()->saveMany($chapters);
-        $all = array_merge([$largeBook], array_values($pages->all()), array_values($chapters->all()));
+
+        $allPages = [];
+
+        foreach ($chapters as $chapter) {
+            $pages = Page::factory()->count(100)->make(['created_by' => $editorUser->id, 'updated_by' => $editorUser->id, 'chapter_id' => $chapter->id]);
+            $largeBook->pages()->saveMany($pages);
+            array_push($allPages, ...$pages->all());
+        }
+
+        $all = array_merge([$largeBook], $allPages, array_values($chapters->all()));
 
         app()->make(JointPermissionBuilder::class)->rebuildForEntity($largeBook);
         app()->make(SearchIndex::class)->indexEntities($all);