Expose timelogs in GraphQL query type and add user/project filter (!67185) · Merge requests · GitLab.org / GitLab

Lee Tickett requested to merge 3918-graphql-expose-timelogs-at-root into master Jul 29, 2021

What does this MR do?

We recently introduced the first Time Tracking report for issuables !60161 (merged)

We need to deliver on further reporting requirements by allowing Time Tracking reports at project and user levels.

This MR improves the GraphQL querying capabilities by exposing timelogs against the query type and allowing any combination of filtering on:

date range
group
project
user

Screenshots or Screencasts (strongly suggested)

How to setup and validate locally (strongly suggested)

Enter some timelogs- for example:
- http://10.10.10.59:3000/flightjs/Flight/-/issues/27 - /spend 60m 2021-01-01 and /spend 90m 2021-02-01
- http://10.10.10.59:3000/gitlab-org/gitlab-test/-/issues/40 - /spend 30m 2021-02-01
- http://10.10.10.59:3000/gitlab-org/gitlab-shell/-/issues/1 - /spend 120m 2021-03-01
Impersonate another user- for example: http://10.10.10.59:3000/admin/users/rosenda_watsica
- and add some timelogs- for example
- http://10.10.10.59:3000/flightjs/Flight/-/issues/27 - /spend 60m 2021-01-01 and /spend 90m 2021-02-01

GraphQL Queries:

Should get all 6 back...
query {
  timelogs { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 2 back from root (30m/120m)...
query {
  timelogs(groupId: "gid://gitlab/Group/22") { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 4 back, 2 from root, 2 from rosenda_watsica...
query {
  timelogs(projectId: "gid://gitlab/Project/6") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 2 back from rosenda_wasica...
query {
  timelogs(username: "rosenda_watsica") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 5 back (all except the 120m)...
query {
  timelogs(startDate: "2021-01-01", endDate: "2021-02-28") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

Database

query {
  timelogs { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=0.43..5.79 rows=100 width=84) (actual time=0.031..0.240 rows=100 loops=1)
   Buffers: shared hit=101
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using timelogs_pkey on public.timelogs  (cost=0.43..173800.35 rows=3245325 width=84) (actual time=0.029..0.223 rows=100 loops=1)
         Buffers: shared hit=101
         I/O Timings: read=0.000 write=0.000

Time: 0.417 ms
  - planning: 0.144 ms
  - execution: 0.273 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 101 (~808.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(groupId: "gid://gitlab/Group/22") { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" INNER JOIN "projects" ON "projects"."id" = "timelogs"."project_id" WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 22)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT id FROM "base_and_descendants" AS "namespaces") ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=13935.07..13935.32 rows=100 width=84) (actual time=2.743..2.745 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=2.598 write=0.000
   ->  Sort  (cost=13935.07..13936.71 rows=656 width=84) (actual time=2.741..2.744 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=3
         I/O Timings: read=2.598 write=0.000
         ->  Nested Loop  (cost=1592.55..13910.00 rows=656 width=84) (actual time=2.694..2.697 rows=0 loops=1)
               Buffers: shared read=3
               I/O Timings: read=2.598 write=0.000
               ->  Nested Loop  (cost=1592.12..2129.71 rows=4087 width=4) (actual time=2.693..2.696 rows=0 loops=1)
                     Buffers: shared read=3
                     I/O Timings: read=2.598 write=0.000
                     ->  HashAggregate  (cost=1591.69..1593.69 rows=200 width=4) (actual time=2.693..2.695 rows=0 loops=1)
                           Group Key: namespaces.id
                           Buffers: shared read=3
                           I/O Timings: read=2.598 write=0.000
                           ->  CTE Scan on base_and_descendants namespaces  (cost=1585.15..1589.17 rows=201 width=4) (actual time=2.658..2.660 rows=0 loops=1)
                                 Buffers: shared read=3
                                 I/O Timings: read=2.598 write=0.000
                                 CTE base_and_descendants
                                   ->  Recursive Union  (cost=0.43..1585.15 rows=201 width=356) (actual time=2.657..2.658 rows=0 loops=1)
                                         Buffers: shared read=3
                                         I/O Timings: read=2.598 write=0.000
                                         ->  Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1  (cost=0.43..3.45 rows=1 width=356) (actual time=2.652..2.653 rows=0 loops=1)
                                               Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 22))
                                               Buffers: shared read=3
                                               I/O Timings: read=2.598 write=0.000
                                         ->  Nested Loop  (cost=0.56..157.77 rows=20 width=356) (actual time=0.003..0.004 rows=0 loops=1)
                                               I/O Timings: read=0.000 write=0.000
                                               ->  WorkTable Scan on base_and_descendants  (cost=0.00..0.20 rows=10 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                     I/O Timings: read=0.000 write=0.000
                                               ->  Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2  (cost=0.56..15.74 rows=2 width=356) (actual time=0.000..0.000 rows=0 loops=0)
                                                     Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
                                                     Filter: ((namespaces_2.type)::text = 'Group'::text)
                                                     Rows Removed by Filter: 0
                                                     I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using index_projects_on_namespace_id_and_id on public.projects  (cost=0.44..2.48 rows=20 width=8) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (projects.namespace_id = namespaces.id)
                           Heap Fetches: 0
                           I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_timelogs_on_project_id_and_spent_at on public.timelogs  (cost=0.43..1.99 rows=89 width=84) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (timelogs.project_id = projects.id)
                     I/O Timings: read=0.000 write=0.000

Time: 8.966 ms
  - planning: 5.925 ms
  - execution: 3.041 ms
    - I/O read: 2.598 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(projectId: "gid://gitlab/Project/6") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" WHERE "timelogs"."project_id" = 6 ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=66.37..66.48 rows=45 width=84) (actual time=4.496..4.498 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=4.397 write=0.000
   ->  Sort  (cost=66.37..66.48 rows=45 width=84) (actual time=4.494..4.495 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=3
         I/O Timings: read=4.397 write=0.000
         ->  Index Scan using index_timelogs_on_project_id_and_spent_at on public.timelogs  (cost=0.43..65.13 rows=45 width=84) (actual time=4.475..4.475 rows=0 loops=1)
               Index Cond: (timelogs.project_id = 6)
               Buffers: shared read=3
               I/O Timings: read=4.397 write=0.000

Time: 4.743 ms
  - planning: 0.213 ms
  - execution: 4.530 ms
    - I/O read: 4.397 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(username: "rosenda_watsica") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" WHERE "timelogs"."user_id" = 6 ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=80.01..80.15 rows=56 width=84) (actual time=2.913..2.914 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=2.743 write=0.000
   ->  Sort  (cost=80.01..80.15 rows=56 width=84) (actual time=2.910..2.911 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=3
         I/O Timings: read=2.743 write=0.000
         ->  Index Scan using index_timelogs_on_user_id on public.timelogs  (cost=0.43..78.39 rows=56 width=84) (actual time=2.822..2.822 rows=0 loops=1)
               Index Cond: (timelogs.user_id = 6)
               Buffers: shared read=3
               I/O Timings: read=2.743 write=0.000

Time: 3.140 ms
  - planning: 0.193 ms
  - execution: 2.947 ms
    - I/O read: 2.743 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(startDate: "2021-01-01", endDate: "2021-02-28") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

 SELECT "timelogs".* FROM "timelogs" WHERE (spent_at >= '2021-01-01 00:00:00') AND (spent_at <= '2021-02-28 23:59:59.999999') ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=0.43..135.42 rows=100 width=84) (actual time=9.099..40.421 rows=100 loops=1)
   Buffers: shared hit=61648
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using timelogs_pkey on public.timelogs  (cost=0.43..190026.98 rows=140767 width=84) (actual time=9.097..40.400 rows=100 loops=1)
         Filter: ((timelogs.spent_at >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (timelogs.spent_at <= '2021-02-28 23:59:59.999999+00'::timestamp with time zone))
         Rows Removed by Filter: 63277
         Buffers: shared hit=61648
         I/O Timings: read=0.000 write=0.000

Time: 40.712 ms
  - planning: 0.231 ms
  - execution: 40.481 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 61648 (~481.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(projectId: "gid://gitlab/Project/6", username: "rosenda_watsica", startDate: "2021-01-01", endDate: "2021-02-28") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" WHERE "timelogs"."project_id" = 6 AND "timelogs"."user_id" = 6 AND (spent_at >= '2021-01-01 00:00:00') AND (spent_at <= '2021-02-28 23:59:59.999999') ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=4.87..4.88 rows=1 width=84) (actual time=0.059..0.060 rows=0 loops=1)
   Buffers: shared hit=6
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=4.87..4.88 rows=1 width=84) (actual time=0.058..0.058 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=6
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_timelogs_on_project_id_and_spent_at on public.timelogs  (cost=0.43..4.86 rows=1 width=84) (actual time=0.036..0.036 rows=0 loops=1)
               Index Cond: ((timelogs.project_id = 6) AND (timelogs.spent_at >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (timelogs.spent_at <= '2021-02-28 23:59:59.999999+00'::timestamp with time zone))
               Filter: (timelogs.user_id = 6)
               Rows Removed by Filter: 0
               Buffers: shared hit=3
               I/O Timings: read=0.000 write=0.000

Time: 0.466 ms
  - planning: 0.376 ms
  - execution: 0.090 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 6 (~48.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

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 04, 2021 by Lee Tickett

Expose timelogs in GraphQL query type and add user/project filter