Scope board to iteration cadence (!69030) · Merge requests · GitLab.org / GitLab

Alexandru Croitor requested to merge scope-board-to-iteration-cadence into master Aug 25, 2021

What does this MR do?

This MR introduces auto-scoping of boards to iteration cadence. This is to provide compatibility with introducing multiple cadences and at the same time backfilling the cadences data for boards that are already scoped to existing "scope to current iteration" functionality on boards.

This MR adds iteration_cadence_id column to boards table
Adds a data migration to backfill the iteration_cadence_id column with first cadence that it finds in the group hierarchy
Removes the scoped to iteration if no cadence is found in the hierarchy. This is an existing bug, where a board can be scoped to current iteration even if there are no iterations at all.
Adds the capability to scope a board to an iteration and a cadence
If an actual iteration is provided it is used to also scope the board to corresponding the cadence id
If a predefined iteration wildcard is provided, i.e. Any, None, Current we try to find a cadence within the group hierarchy and scope the board to that. We'll need to change this to require the cadence id to be provided explicitly when we make supporting multiple cadences GA.
- If we cannot find a cadence within the hierarchy we show an error. I guess a better UI/UX would be to not have the scope to current iteration option if there are no iterations/cadences within group hierarchy.

re #323653 (closed)

Database Queries

group boards that are scoped to current iteration:

select count(*) from boards WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."group_id" IS NOT NULL;
 count
-------
   668

fetch group boards that are scoped to current iteration

explain (analyze, buffers)SELECT "boards"."id" FROM "boards" WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."group_id" IS NOT NULL ORDER BY "boards"."id" ASC LIMIT 1;

 Limit  (cost=76.09..76.09 rows=1 width=4) (actual time=2.253..2.256 rows=1 loops=1)
   Buffers: shared hit=1241
   ->  Sort  (cost=76.09..76.09 rows=1 width=4) (actual time=2.251..2.252 rows=1 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=1241
         ->  Index Scan using index_boards_on_iteration_id on boards  (cost=0.43..76.08 rows=1 width=4) (actual time=0.036..2.111 rows=668 loops=1)
               Index Cond: (iteration_id IS NOT NULL)
               Filter: ((iteration_cadence_id IS NULL) AND (group_id IS NOT NULL))
               Rows Removed by Filter: 596
               Buffers: shared hit=1241
 Planning Time: 0.160 ms
 Execution Time: 2.288 ms

explain (analyze, buffers) SELECT "boards"."id" FROM "boards" WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."group_id" IS NOT NULL AND "boards"."id" >= 547029 ORDER BY "boards"."id" ASC LIMIT 1 OFFSET 1000;

 Limit  (cost=79.33..79.33 rows=1 width=4) (actual time=2.354..2.355 rows=0 loops=1)
   Buffers: shared hit=1241
   ->  Sort  (cost=79.32..79.33 rows=1 width=4) (actual time=2.275..2.321 rows=668 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 56kB
         Buffers: shared hit=1241
         ->  Index Scan using index_boards_on_iteration_id on boards  (cost=0.43..79.31 rows=1 width=4) (actual time=0.045..2.091 rows=668 loops=1)
               Index Cond: (iteration_id IS NOT NULL)
               Filter: ((iteration_cadence_id IS NULL) AND (group_id IS NOT NULL) AND (id >= 547029))
               Rows Removed by Filter: 596
               Buffers: shared hit=1241
 Planning Time: 0.177 ms
 Execution Time: 2.385 ms

explain (analyze, buffers) SELECT "boards"."id", "boards"."group_id" FROM "boards" WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."group_id" IS NOT NULL AND "boards"."id" >= 547029;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_boards_on_iteration_id on boards  (cost=0.43..79.31 rows=1 width=8) (actual time=0.035..1.960 rows=668 loops=1)
   Index Cond: (iteration_id IS NOT NULL)
   Filter: ((iteration_cadence_id IS NULL) AND (group_id IS NOT NULL) AND (id >= 547029))
   Rows Removed by Filter: 596
   Buffers: shared hit=1241
 Planning Time: 0.137 ms
 Execution Time: 2.059 ms

project boards that are scoped to current iteration:

select count(*) from boards WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."project_id" IS NOT NULL;
 count
-------
   596
(1 row)

fetch project boars that are scoped to current iteration

explain (analyze, buffers) SELECT "boards"."id" FROM "boards" WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."project_id" IS NOT NULL ORDER BY "boards"."id" ASC LIMIT 1;

 Limit  (cost=76.11..76.11 rows=1 width=4) (actual time=2.292..2.294 rows=1 loops=1)
   Buffers: shared hit=1241
   ->  Sort  (cost=76.11..76.12 rows=6 width=4) (actual time=2.290..2.291 rows=1 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=1241
         ->  Index Scan using index_boards_on_iteration_id on boards  (cost=0.43..76.08 rows=6 width=4) (actual time=0.020..2.200 rows=596 loops=1)
               Index Cond: (iteration_id IS NOT NULL)
               Filter: ((iteration_cadence_id IS NULL) AND (project_id IS NOT NULL))
               Rows Removed by Filter: 668
               Buffers: shared hit=1241
 Planning Time: 0.214 ms
 Execution Time: 2.320 ms
(13 rows)

explain (analyze, buffers) SELECT "boards"."id" FROM "boards" WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."project_id" IS NOT NULL AND "boards"."id" >= 321135 ORDER BY "boards"."id" ASC LIMIT 1 OFFSET 1000;

 Limit  (cost=79.38..79.38 rows=1 width=4) (actual time=2.276..2.278 rows=0 loops=1)
   Buffers: shared hit=1241
   ->  Sort  (cost=79.37..79.38 rows=5 width=4) (actual time=2.196..2.242 rows=596 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 52kB
         Buffers: shared hit=1241
         ->  Index Scan using index_boards_on_iteration_id on boards  (cost=0.43..79.31 rows=5 width=4) (actual time=0.031..1.996 rows=596 loops=1)
               Index Cond: (iteration_id IS NOT NULL)
               Filter: ((iteration_cadence_id IS NULL) AND (project_id IS NOT NULL) AND (id >= 321135))
               Rows Removed by Filter: 668
               Buffers: shared hit=1241
 Planning Time: 0.177 ms
 Execution Time: 2.308 ms

explain (analyze, buffers) SELECT "boards"."id", "boards"."group_id" FROM "boards" WHERE "boards"."iteration_id" IS NOT NULL AND "boards"."iteration_cadence_id" IS NULL AND "boards"."project_id" IS NOT NULL AND "boards"."id" >= 321135;

 Index Scan using index_boards_on_iteration_id on boards  (cost=0.43..79.31 rows=5 width=8) (actual time=0.033..1.990 rows=596 loops=1)
   Index Cond: (iteration_id IS NOT NULL)
   Filter: ((iteration_cadence_id IS NULL) AND (project_id IS NOT NULL) AND (id >= 321135))
   Rows Removed by Filter: 668
   Buffers: shared hit=1241
 Planning Time: 0.148 ms
 Execution Time: 2.078 ms

max boards id is ~3182212, so consequent batches should scan even less data, thus no performance issue should be seen

gitlabhq_dblab=# select max(id) from boards;
   max
---------
 3182212
(1 row)

Screenshots or Screencasts (strongly suggested)

How to setup and validate locally (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
I have added/updated documentation, or it's not needed. (Is documentation required?)
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
I have self-reviewed this MR per code review guidelines.
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
I have followed the style guides.
This change is backwards compatible across updates, or this does not apply.

Availability and Testing

I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
I have tested this MR in all supported browsers, or it's not needed.
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited Aug 30, 2021 by Alexandru Croitor

Scope board to iteration cadence