Skip to content

Filter runners by project

Marcos Rocha requested to merge mc_rocha-add-project-fullpath-argument-runners-query-345430 into master

What does this MR do and why?

We are working on a new feature in which we should query the tag_list of all available runners of a given project.

We have a REST API endpoint to filter the runners of a given project. However, to get the tag list of all runners available to a project, we would need to get a list of runners firsts and for each runner to get the tags.

This MR adds the projectFullPath argument to the Runners query to avoid multiple requests to the rest API.

This MR is part of issue #345430 (closed).

New Queries

[4] pry(main)> Ci::Runner.owned_or_instance_wide(1)
  Project Load (2.3ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 

 ProjectCiCdSetting Load (0.5ms)  SELECT "project_ci_cd_settings".* FROM "project_ci_cd_settings" WHERE "project_ci_cd_settings"."project_id" = 1 LIMIT 1 

 Feature::FlipperGate Load (0.4ms)  SELECT "feature_gates".* FROM "feature_gates" WHERE "feature_gates"."feature_key" = 'use_traversal_ids_for_ancestor_scopes' 

   (3.0ms)  WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "projects"."id" = 1)
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id" FROM "base_and_ancestors" AS "namespaces"
  
Ci::Runner Load (1.7ms)  SELECT "ci_runners".* FROM ((SELECT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id" WHERE "ci_runner_projects"."project_id" = 1)
UNION ALL
(SELECT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id" WHERE "ci_runner_namespaces"."namespace_id" = 22)
UNION ALL
(SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."runner_type" = 1)) ci_runners
SELECT
    project_ci_cd_settings.*
FROM
    project_ci_cd_settings
WHERE
    project_ci_cd_settings.project_id = 278964
LIMIT 1;

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13378/commands/46985

WITH RECURSIVE "base_and_ancestors" AS ((
        SELECT
            "namespaces"."id",
            "namespaces"."name",
            "namespaces"."path",
            "namespaces"."owner_id",
            "namespaces"."created_at",
            "namespaces"."updated_at",
            "namespaces"."type",
            "namespaces"."description",
            "namespaces"."avatar",
            "namespaces"."membership_lock",
            "namespaces"."share_with_group_lock",
            "namespaces"."visibility_level",
            "namespaces"."request_access_enabled",
            "namespaces"."ldap_sync_status",
            "namespaces"."ldap_sync_error",
            "namespaces"."ldap_sync_last_update_at",
            "namespaces"."ldap_sync_last_successful_update_at",
            "namespaces"."ldap_sync_last_sync_at",
            "namespaces"."description_html",
            "namespaces"."lfs_enabled",
            "namespaces"."parent_id",
            "namespaces"."shared_runners_minutes_limit",
            "namespaces"."repository_size_limit",
            "namespaces"."require_two_factor_authentication",
            "namespaces"."two_factor_grace_period",
            "namespaces"."cached_markdown_version",
            "namespaces"."project_creation_level",
            "namespaces"."runners_token",
            "namespaces"."file_template_project_id",
            "namespaces"."saml_discovery_token",
            "namespaces"."runners_token_encrypted",
            "namespaces"."custom_project_templates_group_id",
            "namespaces"."auto_devops_enabled",
            "namespaces"."extra_shared_runners_minutes_limit",
            "namespaces"."last_ci_minutes_notification_at",
            "namespaces"."last_ci_minutes_usage_notification_level",
            "namespaces"."subgroup_creation_level",
            "namespaces"."emails_disabled",
            "namespaces"."max_pages_size",
            "namespaces"."max_artifacts_size",
            "namespaces"."mentions_disabled",
            "namespaces"."default_branch_protection",
            "namespaces"."unlock_membership_to_ldap",
            "namespaces"."max_personal_access_token_lifetime",
            "namespaces"."push_rule_id",
            "namespaces"."shared_runners_enabled",
            "namespaces"."allow_descendants_override_disabled_shared_runners",
            "namespaces"."traversal_ids"
        FROM
            "namespaces"
            INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
        WHERE
            "namespaces"."type" = 'Group'
            AND "projects"."id" = 278964)
    UNION (
        SELECT
            "namespaces"."id",
            "namespaces"."name",
            "namespaces"."path",
            "namespaces"."owner_id",
            "namespaces"."created_at",
            "namespaces"."updated_at",
            "namespaces"."type",
            "namespaces"."description",
            "namespaces"."avatar",
            "namespaces"."membership_lock",
            "namespaces"."share_with_group_lock",
            "namespaces"."visibility_level",
            "namespaces"."request_access_enabled",
            "namespaces"."ldap_sync_status",
            "namespaces"."ldap_sync_error",
            "namespaces"."ldap_sync_last_update_at",
            "namespaces"."ldap_sync_last_successful_update_at",
            "namespaces"."ldap_sync_last_sync_at",
            "namespaces"."description_html",
            "namespaces"."lfs_enabled",
            "namespaces"."parent_id",
            "namespaces"."shared_runners_minutes_limit",
            "namespaces"."repository_size_limit",
            "namespaces"."require_two_factor_authentication",
            "namespaces"."two_factor_grace_period",
            "namespaces"."cached_markdown_version",
            "namespaces"."project_creation_level",
            "namespaces"."runners_token",
            "namespaces"."file_template_project_id",
            "namespaces"."saml_discovery_token",
            "namespaces"."runners_token_encrypted",
            "namespaces"."custom_project_templates_group_id",
            "namespaces"."auto_devops_enabled",
            "namespaces"."extra_shared_runners_minutes_limit",
            "namespaces"."last_ci_minutes_notification_at",
            "namespaces"."last_ci_minutes_usage_notification_level",
            "namespaces"."subgroup_creation_level",
            "namespaces"."emails_disabled",
            "namespaces"."max_pages_size",
            "namespaces"."max_artifacts_size",
            "namespaces"."mentions_disabled",
            "namespaces"."default_branch_protection",
            "namespaces"."unlock_membership_to_ldap",
            "namespaces"."max_personal_access_token_lifetime",
            "namespaces"."push_rule_id",
            "namespaces"."shared_runners_enabled",
            "namespaces"."allow_descendants_override_disabled_shared_runners",
            "namespaces"."traversal_ids"
        FROM
            "namespaces",
            "base_and_ancestors"
        WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
    "id"
FROM
    "base_and_ancestors" AS "namespaces"

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13378/commands/46987

SELECT
    "ci_runners".*
FROM ((
        SELECT
            "ci_runners".*
        FROM
            "ci_runners"
            INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
        WHERE
            "ci_runner_projects"."project_id" = 278964)
    UNION ALL (
        SELECT
            "ci_runners".*
        FROM
            "ci_runners"
            INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
        WHERE
            "ci_runner_namespaces"."namespace_id" = 22)
    UNION ALL (
        SELECT
            "ci_runners".*
        FROM
            "ci_runners"
        WHERE
            "ci_runners"."runner_type" = 1)) ci_runners

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13378/commands/46989

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

  1. Start the rails console
rails c
  1. Enable the on_demand_scans_runner_tags feature flag
Feature.enable(:on_demand_scans_runner_tags)
  1. Create a specific runner for one project as described here

Screenshot_2022-11-15_at_7.15.18_PM

  1. Create a project runner for another project as described here

Screenshot_2022-11-15_at_7.17.02_PM

  1. Go to /-/graphql-explorer and enter the query below, replacing the projectFullPath for one of the projects you used in the steps above.
{
  project(fullPath: "root/apiscantest") {
    runners {
      nodes {
        status
        tagList
      }
    }
  }
}
  1. Check the response. It should contain the specific runner to the project you used as an argument, and it shouldn't include the other's project specific runner.
{
  "data": {
    "project": {
      "runners": {
        "nodes": [
          {
            "status": "ONLINE",
            "tagList": [
              "ruby",
              "rails"
            ]
          },
          {
            "status": "ONLINE",
            "tagList": [
              "test"
            ]
          },
          {
            "status": "NEVER_CONTACTED",
            "tagList": [
              "test",
              "dast",
              "on-demand"
            ]
          },
          {
            "status": "NEVER_CONTACTED",
            "tagList": [
              "test"
            ]
          }
        ]
      }
    }
  }
}

MR acceptance checklist

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

Edited by Marcos Rocha

Merge request reports