Filter Dast Profiles with Schedule

What does this MR do and why?

problem

frontend needs to render profiles that have an associated schedule when they call Project.dastProfiles.

solution

• add hasDastProfileSchedule argument to project.dastProfiles that filters the results

semantics

• hasDastProfileSchedule = null => all profiles.
• hasDastProfileSchedule = false => profiles without a schedule
• hasDastProfileSchedule = true => profiles with a schedule

This Merge Request is related to issue #342477 (closed)

New queries

SELECT
    "dast_profiles".*
FROM
    "dast_profiles"
    INNER JOIN "dast_profile_schedules" ON "dast_profile_schedules"."dast_profile_id" = "dast_profiles"."id"
WHERE
    "dast_profiles"."project_id" = 278964
ORDER BY
    "dast_profiles"."id" DESC
LIMIT 100
 

visualization

SELECT
    "dast_profiles".*
FROM
    "dast_profiles"
    LEFT OUTER JOIN "dast_profile_schedules" ON "dast_profile_schedules"."dast_profile_id" = "dast_profiles"."id"
WHERE
    "dast_profiles"."project_id" = 278964
    AND "dast_profile_schedules"."id" IS NULL
ORDER BY
    "dast_profiles"."id" DESC
LIMIT 100

visualization

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Create test data:

1. run this database seed to create dast_profiles to every project.
2. create some dast_profile_schedule

rails c
owner = User.first
project = Project.last
dast_profile = Dast::Profile.last

Dast::ProfileSchedule.create!(user_id: owner.id, cron: "*/10 * * * *", next_run_at: Time.zone.now, dast_profile_id: dast_profile.id, project_id: project.id, timezone: "America/New_York", starts_at:Time.zone.now)

Testing in the rails console:

1. run rails c
2. Test the Dast::Profile.with_schedule scope

[22] pry(main)> Dast::Profile.all.count
   (0.2ms)  SELECT COUNT(*) FROM "dast_profiles" /*application:console,db_config_name:main,line:/lib/gitlab/database/load_balancing/connection_proxy.rb:103:in `block in read_using_load_balancer'*/
=> 99
[23] pry(main)> Dast::Profile.with_schedule(true).count
   (0.4ms)  SELECT COUNT(*) FROM "dast_profiles" INNER JOIN "dast_profile_schedules" ON "dast_profile_schedules"."dast_profile_id" = "dast_profiles"."id" /*application:console,db_config_name:main,line:/lib/gitlab/database/load_balancing/connection_proxy.rb:103:in `block in read_using_load_balancer'*/
=> 4
[24] pry(main)> Dast::Profile.with_schedule(false).count
   (0.4ms)  SELECT COUNT(*) FROM "dast_profiles" LEFT OUTER JOIN "dast_profile_schedules" ON "dast_profile_schedules"."dast_profile_id" = "dast_profiles"."id" WHERE "dast_profile_schedules"."id" IS NULL /*application:console,db_config_name:main,line:/lib/gitlab/database/load_balancing/connection_proxy.rb:103:in `block in read_using_load_balancer'*/
=> 95

Testing using the graphql-explorer

1. go to <your_gdk_url>:3000/graphql-explorer
2. Make queries like:

{
  project(fullPath: "gitlab-instance-0c0627c9/Monitoring") {
    dastProfiles(hasDastProfileSchedule: true) {
      nodes { id }
    }
  }
}

And

{
  project(fullPath: "gitlab-instance-0c0627c9/Monitoring") {
    dastProfiles(hasDastProfileSchedule: false) {
      nodes { id }
    }
  }
}

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.