[POC] Handle routes with Project and ProjectNamespace sources
What does this MR do?
This MR adjusts routable to be able to handle routes with both Project and ProjectNamespace sources, i.e. addresses parts of the following issues #337103 (closed) and #337100 (closed).
With this in place, we can gradually migrate entries in routes table from Project source to ProjectNamespace source with the sources actually being marked as source_type='Namespace'. This helps us address the concern expressed here about having to drop the unique constraint on routes#path table.
Short demo: https://gitlab.slack.com/files/UFVQVM8BB/F02DC3E041L/screen_recording_2021-09-06_at_23.39.50.mov
Database Queries
Previous sqls
Query1
Group.find(9970).projects.where_full_path_in(['gitlab-org/gitlab', 'gitlab-org/jobs-second-test'])- query plan: https://explain.depesz.com/s/LSKk#html
SELECT
"projects"."id" AS t0_r0,
"projects"."name" AS t0_r1,
"projects"."path" AS t0_r2,
"projects"."description" AS t0_r3,
"projects"."created_at" AS t0_r4,
"projects"."updated_at" AS t0_r5,
"projects"."creator_id" AS t0_r6,
"projects"."namespace_id" AS t0_r7,
"projects"."last_activity_at" AS t0_r8,
"projects"."import_url" AS t0_r9,
"projects"."visibility_level" AS t0_r10,
"projects"."archived" AS t0_r11,
"projects"."merge_requests_template" AS t0_r12,
"projects"."star_count" AS t0_r13,
"projects"."merge_requests_rebase_enabled" AS t0_r14,
"projects"."import_type" AS t0_r15,
"projects"."import_source" AS t0_r16,
"projects"."avatar" AS t0_r17,
"projects"."approvals_before_merge" AS t0_r18,
"projects"."reset_approvals_on_push" AS t0_r19,
"projects"."merge_requests_ff_only_enabled" AS t0_r20,
"projects"."issues_template" AS t0_r21,
"projects"."mirror" AS t0_r22,
"projects"."mirror_user_id" AS t0_r23,
"projects"."shared_runners_enabled" AS t0_r24,
"projects"."runners_token" AS t0_r25,
"projects"."build_coverage_regex" AS t0_r26,
"projects"."build_allow_git_fetch" AS t0_r27,
"projects"."build_timeout" AS t0_r28,
"projects"."mirror_trigger_builds" AS t0_r29,
"projects"."public_builds" AS t0_r30,
"projects"."pending_delete" AS t0_r31,
"projects"."last_repository_check_failed" AS t0_r32,
"projects"."last_repository_check_at" AS t0_r33,
"projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r34,
"projects"."has_external_issue_tracker" AS t0_r35,
"projects"."repository_storage" AS t0_r36,
"projects"."request_access_enabled" AS t0_r37,
"projects"."has_external_wiki" AS t0_r38,
"projects"."repository_read_only" AS t0_r39,
"projects"."lfs_enabled" AS t0_r40,
"projects"."description_html" AS t0_r41,
"projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r42,
"projects"."repository_size_limit" AS t0_r43,
"projects"."service_desk_enabled" AS t0_r44,
"projects"."printing_merge_request_link_enabled" AS t0_r45,
"projects"."auto_cancel_pending_pipelines" AS t0_r46,
"projects"."cached_markdown_version" AS t0_r47,
"projects"."last_repository_updated_at" AS t0_r48,
"projects"."ci_config_path" AS t0_r49,
"projects"."disable_overriding_approvers_per_merge_request" AS t0_r50,
"projects"."delete_error" AS t0_r51,
"projects"."storage_version" AS t0_r52,
"projects"."resolve_outdated_diff_discussions" AS t0_r53,
"projects"."remote_mirror_available_overridden" AS t0_r54,
"projects"."only_mirror_protected_branches" AS t0_r55,
"projects"."pull_mirror_available_overridden" AS t0_r56,
"projects"."jobs_cache_index" AS t0_r57,
"projects"."external_authorization_classification_label" AS t0_r58,
"projects"."mirror_overwrites_diverged_branches" AS t0_r59,
"projects"."external_webhook_token" AS t0_r60,
"projects"."pages_https_only" AS t0_r61,
"projects"."packages_enabled" AS t0_r62,
"projects"."merge_requests_author_approval" AS t0_r63,
"projects"."pool_repository_id" AS t0_r64,
"projects"."runners_token_encrypted" AS t0_r65,
"projects"."bfg_object_map" AS t0_r66,
"projects"."detected_repository_languages" AS t0_r67,
"projects"."merge_requests_disable_committers_approval" AS t0_r68,
"projects"."require_password_to_approve" AS t0_r69,
"projects"."emails_disabled" AS t0_r70,
"projects"."max_pages_size" AS t0_r71,
"projects"."max_artifacts_size" AS t0_r72,
"projects"."remove_source_branch_after_merge" AS t0_r73,
"projects"."marked_for_deletion_at" AS t0_r74,
"projects"."marked_for_deletion_by_user_id" AS t0_r75,
"projects"."suggestion_commit_message" AS t0_r76,
"projects"."autoclose_referenced_issues" AS t0_r77,
"projects"."project_namespace_id" AS t0_r78,
"routes"."id" AS t1_r0,
"routes"."source_id" AS t1_r1,
"routes"."source_type" AS t1_r2,
"routes"."path" AS t1_r3,
"routes"."created_at" AS t1_r4,
"routes"."updated_at" AS t1_r5,
"routes"."name" AS t1_r6
FROM
"projects"
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project'
AND "routes"."source_id" = "projects"."id"
WHERE
"projects"."namespace_id" = 9970
AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab'))
OR (LOWER(routes.path) = LOWER('gitlab-org/jobs-second-test')))Query2
Group.find(9970).all_projects.where_full_path_in(['gitlab-org/gitlab', 'gitlab-org/jobs-second-test'])- query plan: https://explain.depesz.com/s/yaWm#html
SELECT
"projects"."id" AS t0_r0,
"projects"."name" AS t0_r1,
"projects"."path" AS t0_r2,
"projects"."description" AS t0_r3,
"projects"."created_at" AS t0_r4,
"projects"."updated_at" AS t0_r5,
"projects"."creator_id" AS t0_r6,
"projects"."namespace_id" AS t0_r7,
"projects"."last_activity_at" AS t0_r8,
"projects"."import_url" AS t0_r9,
"projects"."visibility_level" AS t0_r10,
"projects"."archived" AS t0_r11,
"projects"."merge_requests_template" AS t0_r12,
"projects"."star_count" AS t0_r13,
"projects"."merge_requests_rebase_enabled" AS t0_r14,
"projects"."import_type" AS t0_r15,
"projects"."import_source" AS t0_r16,
"projects"."avatar" AS t0_r17,
"projects"."approvals_before_merge" AS t0_r18,
"projects"."reset_approvals_on_push" AS t0_r19,
"projects"."merge_requests_ff_only_enabled" AS t0_r20,
"projects"."issues_template" AS t0_r21,
"projects"."mirror" AS t0_r22,
"projects"."mirror_user_id" AS t0_r23,
"projects"."shared_runners_enabled" AS t0_r24,
"projects"."runners_token" AS t0_r25,
"projects"."build_coverage_regex" AS t0_r26,
"projects"."build_allow_git_fetch" AS t0_r27,
"projects"."build_timeout" AS t0_r28,
"projects"."mirror_trigger_builds" AS t0_r29,
"projects"."public_builds" AS t0_r30,
"projects"."pending_delete" AS t0_r31,
"projects"."last_repository_check_failed" AS t0_r32,
"projects"."last_repository_check_at" AS t0_r33,
"projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r34,
"projects"."has_external_issue_tracker" AS t0_r35,
"projects"."repository_storage" AS t0_r36,
"projects"."request_access_enabled" AS t0_r37,
"projects"."has_external_wiki" AS t0_r38,
"projects"."repository_read_only" AS t0_r39,
"projects"."lfs_enabled" AS t0_r40,
"projects"."description_html" AS t0_r41,
"projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r42,
"projects"."repository_size_limit" AS t0_r43,
"projects"."service_desk_enabled" AS t0_r44,
"projects"."printing_merge_request_link_enabled" AS t0_r45,
"projects"."auto_cancel_pending_pipelines" AS t0_r46,
"projects"."cached_markdown_version" AS t0_r47,
"projects"."last_repository_updated_at" AS t0_r48,
"projects"."ci_config_path" AS t0_r49,
"projects"."disable_overriding_approvers_per_merge_request" AS t0_r50,
"projects"."delete_error" AS t0_r51,
"projects"."storage_version" AS t0_r52,
"projects"."resolve_outdated_diff_discussions" AS t0_r53,
"projects"."remote_mirror_available_overridden" AS t0_r54,
"projects"."only_mirror_protected_branches" AS t0_r55,
"projects"."pull_mirror_available_overridden" AS t0_r56,
"projects"."jobs_cache_index" AS t0_r57,
"projects"."external_authorization_classification_label" AS t0_r58,
"projects"."mirror_overwrites_diverged_branches" AS t0_r59,
"projects"."external_webhook_token" AS t0_r60,
"projects"."pages_https_only" AS t0_r61,
"projects"."packages_enabled" AS t0_r62,
"projects"."merge_requests_author_approval" AS t0_r63,
"projects"."pool_repository_id" AS t0_r64,
"projects"."runners_token_encrypted" AS t0_r65,
"projects"."bfg_object_map" AS t0_r66,
"projects"."detected_repository_languages" AS t0_r67,
"projects"."merge_requests_disable_committers_approval" AS t0_r68,
"projects"."require_password_to_approve" AS t0_r69,
"projects"."emails_disabled" AS t0_r70,
"projects"."max_pages_size" AS t0_r71,
"projects"."max_artifacts_size" AS t0_r72,
"projects"."remove_source_branch_after_merge" AS t0_r73,
"projects"."marked_for_deletion_at" AS t0_r74,
"projects"."marked_for_deletion_by_user_id" AS t0_r75,
"projects"."suggestion_commit_message" AS t0_r76,
"projects"."autoclose_referenced_issues" AS t0_r77,
"projects"."project_namespace_id" AS t0_r78,
"routes"."id" AS t1_r0,
"routes"."source_id" AS t1_r1,
"routes"."source_type" AS t1_r2,
"routes"."path" AS t1_r3,
"routes"."created_at" AS t1_r4,
"routes"."updated_at" AS t1_r5,
"routes"."name" AS t1_r6
FROM
"projects"
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project'
AND "routes"."source_id" = "projects"."id"
WHERE
"projects"."namespace_id" IN (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM
"namespaces"
WHERE (traversal_ids @> ('{9970}')))
AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab'))
OR (LOWER(routes.path) = LOWER('gitlab-org/jobs-second-test')))New Queries
Query1
Group.find(9970).projects.where_full_path_in(['gitlab-org/gitlab', 'gitlab-org/jobs-second-test'])- query plan: https://explain.depesz.com/s/CRVb
SELECT
"projects"."id" AS t0_r0,
"projects"."name" AS t0_r1,
"projects"."path" AS t0_r2,
"projects"."description" AS t0_r3,
"projects"."created_at" AS t0_r4,
"projects"."updated_at" AS t0_r5,
"projects"."creator_id" AS t0_r6,
"projects"."namespace_id" AS t0_r7,
"projects"."last_activity_at" AS t0_r8,
"projects"."import_url" AS t0_r9,
"projects"."visibility_level" AS t0_r10,
"projects"."archived" AS t0_r11,
"projects"."merge_requests_template" AS t0_r12,
"projects"."star_count" AS t0_r13,
"projects"."merge_requests_rebase_enabled" AS t0_r14,
"projects"."import_type" AS t0_r15,
"projects"."import_source" AS t0_r16,
"projects"."avatar" AS t0_r17,
"projects"."approvals_before_merge" AS t0_r18,
"projects"."reset_approvals_on_push" AS t0_r19,
"projects"."merge_requests_ff_only_enabled" AS t0_r20,
"projects"."issues_template" AS t0_r21,
"projects"."mirror" AS t0_r22,
"projects"."mirror_user_id" AS t0_r23,
"projects"."shared_runners_enabled" AS t0_r24,
"projects"."runners_token" AS t0_r25,
"projects"."build_coverage_regex" AS t0_r26,
"projects"."build_allow_git_fetch" AS t0_r27,
"projects"."build_timeout" AS t0_r28,
"projects"."mirror_trigger_builds" AS t0_r29,
"projects"."public_builds" AS t0_r30,
"projects"."pending_delete" AS t0_r31,
"projects"."last_repository_check_failed" AS t0_r32,
"projects"."last_repository_check_at" AS t0_r33,
"projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r34,
"projects"."has_external_issue_tracker" AS t0_r35,
"projects"."repository_storage" AS t0_r36,
"projects"."request_access_enabled" AS t0_r37,
"projects"."has_external_wiki" AS t0_r38,
"projects"."repository_read_only" AS t0_r39,
"projects"."lfs_enabled" AS t0_r40,
"projects"."description_html" AS t0_r41,
"projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r42,
"projects"."repository_size_limit" AS t0_r43,
"projects"."service_desk_enabled" AS t0_r44,
"projects"."printing_merge_request_link_enabled" AS t0_r45,
"projects"."auto_cancel_pending_pipelines" AS t0_r46,
"projects"."cached_markdown_version" AS t0_r47,
"projects"."last_repository_updated_at" AS t0_r48,
"projects"."ci_config_path" AS t0_r49,
"projects"."disable_overriding_approvers_per_merge_request" AS t0_r50,
"projects"."delete_error" AS t0_r51,
"projects"."storage_version" AS t0_r52,
"projects"."resolve_outdated_diff_discussions" AS t0_r53,
"projects"."remote_mirror_available_overridden" AS t0_r54,
"projects"."only_mirror_protected_branches" AS t0_r55,
"projects"."pull_mirror_available_overridden" AS t0_r56,
"projects"."jobs_cache_index" AS t0_r57,
"projects"."external_authorization_classification_label" AS t0_r58,
"projects"."mirror_overwrites_diverged_branches" AS t0_r59,
"projects"."external_webhook_token" AS t0_r60,
"projects"."pages_https_only" AS t0_r61,
"projects"."packages_enabled" AS t0_r62,
"projects"."merge_requests_author_approval" AS t0_r63,
"projects"."pool_repository_id" AS t0_r64,
"projects"."runners_token_encrypted" AS t0_r65,
"projects"."bfg_object_map" AS t0_r66,
"projects"."detected_repository_languages" AS t0_r67,
"projects"."merge_requests_disable_committers_approval" AS t0_r68,
"projects"."require_password_to_approve" AS t0_r69,
"projects"."emails_disabled" AS t0_r70,
"projects"."max_pages_size" AS t0_r71,
"projects"."max_artifacts_size" AS t0_r72,
"projects"."remove_source_branch_after_merge" AS t0_r73,
"projects"."marked_for_deletion_at" AS t0_r74,
"projects"."marked_for_deletion_by_user_id" AS t0_r75,
"projects"."suggestion_commit_message" AS t0_r76,
"projects"."autoclose_referenced_issues" AS t0_r77,
"projects"."project_namespace_id" AS t0_r78,
"routes"."id" AS t1_r0,
"routes"."source_id" AS t1_r1,
"routes"."source_type" AS t1_r2,
"routes"."path" AS t1_r3,
"routes"."created_at" AS t1_r4,
"routes"."updated_at" AS t1_r5,
"routes"."name" AS t1_r6,
"namespaces"."id" AS t2_r0,
"namespaces"."name" AS t2_r1,
"namespaces"."path" AS t2_r2,
"namespaces"."owner_id" AS t2_r3,
"namespaces"."created_at" AS t2_r4,
"namespaces"."updated_at" AS t2_r5,
"namespaces"."type" AS t2_r6,
"namespaces"."description" AS t2_r7,
"namespaces"."avatar" AS t2_r8,
"namespaces"."membership_lock" AS t2_r9,
"namespaces"."share_with_group_lock" AS t2_r10,
"namespaces"."visibility_level" AS t2_r11,
"namespaces"."request_access_enabled" AS t2_r12,
"namespaces"."ldap_sync_status" AS t2_r13,
"namespaces"."ldap_sync_error" AS t2_r14,
"namespaces"."ldap_sync_last_update_at" AS t2_r15,
"namespaces"."ldap_sync_last_successful_update_at" AS t2_r16,
"namespaces"."ldap_sync_last_sync_at" AS t2_r17,
"namespaces"."lfs_enabled" AS t2_r18,
"namespaces"."description_html" AS t2_r19,
"namespaces"."parent_id" AS t2_r20,
"namespaces"."shared_runners_minutes_limit" AS t2_r21,
"namespaces"."repository_size_limit" AS t2_r22,
"namespaces"."require_two_factor_authentication" AS t2_r23,
"namespaces"."two_factor_grace_period" AS t2_r24,
"namespaces"."cached_markdown_version" AS t2_r25,
"namespaces"."project_creation_level" AS t2_r26,
"namespaces"."runners_token" AS t2_r27,
"namespaces"."file_template_project_id" AS t2_r28,
"namespaces"."saml_discovery_token" AS t2_r29,
"namespaces"."runners_token_encrypted" AS t2_r30,
"namespaces"."custom_project_templates_group_id" AS t2_r31,
"namespaces"."auto_devops_enabled" AS t2_r32,
"namespaces"."extra_shared_runners_minutes_limit" AS t2_r33,
"namespaces"."last_ci_minutes_notification_at" AS t2_r34,
"namespaces"."last_ci_minutes_usage_notification_level" AS t2_r35,
"namespaces"."subgroup_creation_level" AS t2_r36,
"namespaces"."emails_disabled" AS t2_r37,
"namespaces"."max_pages_size" AS t2_r38,
"namespaces"."max_artifacts_size" AS t2_r39,
"namespaces"."mentions_disabled" AS t2_r40,
"namespaces"."default_branch_protection" AS t2_r41,
"namespaces"."unlock_membership_to_ldap" AS t2_r42,
"namespaces"."max_personal_access_token_lifetime" AS t2_r43,
"namespaces"."push_rule_id" AS t2_r44,
"namespaces"."shared_runners_enabled" AS t2_r45,
"namespaces"."allow_descendants_override_disabled_shared_runners" AS t2_r46,
"namespaces"."traversal_ids" AS t2_r47,
"routes_namespaces"."id" AS t3_r0,
"routes_namespaces"."source_id" AS t3_r1,
"routes_namespaces"."source_type" AS t3_r2,
"routes_namespaces"."path" AS t3_r3,
"routes_namespaces"."created_at" AS t3_r4,
"routes_namespaces"."updated_at" AS t3_r5,
"routes_namespaces"."name" AS t3_r6
FROM ((
SELECT
projects.*
FROM
"projects"
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project'
AND "routes"."source_id" = "projects"."id"
WHERE
"projects"."namespace_id" = 9970
AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab'))
OR (LOWER(routes.path) = LOWER('gitlab-org/jobs-second-test'))))
UNION (
SELECT
projects.*
FROM
"projects"
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."project_namespace_id"
AND "namespaces"."type" = 'Namespaces::ProjectNamespace'
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Namespace'
AND "routes"."source_id" = "namespaces"."id"
WHERE
"projects"."namespace_id" = 9970
AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab'))
OR (LOWER(routes.path) = LOWER('gitlab-org/jobs-second-test'))))) projects
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project'
AND "routes"."source_id" = "projects"."id"
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."project_namespace_id"
AND "namespaces"."type" = 'Namespaces::ProjectNamespace'
LEFT OUTER JOIN "routes" "routes_namespaces" ON "routes_namespaces"."source_type" = 'Namespace'
AND "routes_namespaces"."source_id" = "namespaces"."id"
WHERE
"projects"."namespace_id" = 9970Query2
Group.find(9970).all_projects.where_full_path_in(['gitlab-org/gitlab', 'gitlab-org/jobs-second-test'])- query plan: https://explain.depesz.com/s/Ckvf#html
SELECT
"projects"."id" AS t0_r0,
"projects"."name" AS t0_r1,
"projects"."path" AS t0_r2,
"projects"."description" AS t0_r3,
"projects"."created_at" AS t0_r4,
"projects"."updated_at" AS t0_r5,
"projects"."creator_id" AS t0_r6,
"projects"."namespace_id" AS t0_r7,
"projects"."last_activity_at" AS t0_r8,
"projects"."import_url" AS t0_r9,
"projects"."visibility_level" AS t0_r10,
"projects"."archived" AS t0_r11,
"projects"."merge_requests_template" AS t0_r12,
"projects"."star_count" AS t0_r13,
"projects"."merge_requests_rebase_enabled" AS t0_r14,
"projects"."import_type" AS t0_r15,
"projects"."import_source" AS t0_r16,
"projects"."avatar" AS t0_r17,
"projects"."approvals_before_merge" AS t0_r18,
"projects"."reset_approvals_on_push" AS t0_r19,
"projects"."merge_requests_ff_only_enabled" AS t0_r20,
"projects"."issues_template" AS t0_r21,
"projects"."mirror" AS t0_r22,
"projects"."mirror_user_id" AS t0_r23,
"projects"."shared_runners_enabled" AS t0_r24,
"projects"."runners_token" AS t0_r25,
"projects"."build_coverage_regex" AS t0_r26,
"projects"."build_allow_git_fetch" AS t0_r27,
"projects"."build_timeout" AS t0_r28,
"projects"."mirror_trigger_builds" AS t0_r29,
"projects"."public_builds" AS t0_r30,
"projects"."pending_delete" AS t0_r31,
"projects"."last_repository_check_failed" AS t0_r32,
"projects"."last_repository_check_at" AS t0_r33,
"projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r34,
"projects"."has_external_issue_tracker" AS t0_r35,
"projects"."repository_storage" AS t0_r36,
"projects"."request_access_enabled" AS t0_r37,
"projects"."has_external_wiki" AS t0_r38,
"projects"."repository_read_only" AS t0_r39,
"projects"."lfs_enabled" AS t0_r40,
"projects"."description_html" AS t0_r41,
"projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r42,
"projects"."repository_size_limit" AS t0_r43,
"projects"."service_desk_enabled" AS t0_r44,
"projects"."printing_merge_request_link_enabled" AS t0_r45,
"projects"."auto_cancel_pending_pipelines" AS t0_r46,
"projects"."cached_markdown_version" AS t0_r47,
"projects"."last_repository_updated_at" AS t0_r48,
"projects"."ci_config_path" AS t0_r49,
"projects"."disable_overriding_approvers_per_merge_request" AS t0_r50,
"projects"."delete_error" AS t0_r51,
"projects"."storage_version" AS t0_r52,
"projects"."resolve_outdated_diff_discussions" AS t0_r53,
"projects"."remote_mirror_available_overridden" AS t0_r54,
"projects"."only_mirror_protected_branches" AS t0_r55,
"projects"."pull_mirror_available_overridden" AS t0_r56,
"projects"."jobs_cache_index" AS t0_r57,
"projects"."external_authorization_classification_label" AS t0_r58,
"projects"."mirror_overwrites_diverged_branches" AS t0_r59,
"projects"."external_webhook_token" AS t0_r60,
"projects"."pages_https_only" AS t0_r61,
"projects"."packages_enabled" AS t0_r62,
"projects"."merge_requests_author_approval" AS t0_r63,
"projects"."pool_repository_id" AS t0_r64,
"projects"."runners_token_encrypted" AS t0_r65,
"projects"."bfg_object_map" AS t0_r66,
"projects"."detected_repository_languages" AS t0_r67,
"projects"."merge_requests_disable_committers_approval" AS t0_r68,
"projects"."require_password_to_approve" AS t0_r69,
"projects"."emails_disabled" AS t0_r70,
"projects"."max_pages_size" AS t0_r71,
"projects"."max_artifacts_size" AS t0_r72,
"projects"."remove_source_branch_after_merge" AS t0_r73,
"projects"."marked_for_deletion_at" AS t0_r74,
"projects"."marked_for_deletion_by_user_id" AS t0_r75,
"projects"."suggestion_commit_message" AS t0_r76,
"projects"."autoclose_referenced_issues" AS t0_r77,
"projects"."project_namespace_id" AS t0_r78,
"routes"."id" AS t1_r0,
"routes"."source_id" AS t1_r1,
"routes"."source_type" AS t1_r2,
"routes"."path" AS t1_r3,
"routes"."created_at" AS t1_r4,
"routes"."updated_at" AS t1_r5,
"routes"."name" AS t1_r6,
"namespaces"."id" AS t2_r0,
"namespaces"."name" AS t2_r1,
"namespaces"."path" AS t2_r2,
"namespaces"."owner_id" AS t2_r3,
"namespaces"."created_at" AS t2_r4,
"namespaces"."updated_at" AS t2_r5,
"namespaces"."type" AS t2_r6,
"namespaces"."description" AS t2_r7,
"namespaces"."avatar" AS t2_r8,
"namespaces"."membership_lock" AS t2_r9,
"namespaces"."share_with_group_lock" AS t2_r10,
"namespaces"."visibility_level" AS t2_r11,
"namespaces"."request_access_enabled" AS t2_r12,
"namespaces"."ldap_sync_status" AS t2_r13,
"namespaces"."ldap_sync_error" AS t2_r14,
"namespaces"."ldap_sync_last_update_at" AS t2_r15,
"namespaces"."ldap_sync_last_successful_update_at" AS t2_r16,
"namespaces"."ldap_sync_last_sync_at" AS t2_r17,
"namespaces"."lfs_enabled" AS t2_r18,
"namespaces"."description_html" AS t2_r19,
"namespaces"."parent_id" AS t2_r20,
"namespaces"."shared_runners_minutes_limit" AS t2_r21,
"namespaces"."repository_size_limit" AS t2_r22,
"namespaces"."require_two_factor_authentication" AS t2_r23,
"namespaces"."two_factor_grace_period" AS t2_r24,
"namespaces"."cached_markdown_version" AS t2_r25,
"namespaces"."project_creation_level" AS t2_r26,
"namespaces"."runners_token" AS t2_r27,
"namespaces"."file_template_project_id" AS t2_r28,
"namespaces"."saml_discovery_token" AS t2_r29,
"namespaces"."runners_token_encrypted" AS t2_r30,
"namespaces"."custom_project_templates_group_id" AS t2_r31,
"namespaces"."auto_devops_enabled" AS t2_r32,
"namespaces"."extra_shared_runners_minutes_limit" AS t2_r33,
"namespaces"."last_ci_minutes_notification_at" AS t2_r34,
"namespaces"."last_ci_minutes_usage_notification_level" AS t2_r35,
"namespaces"."subgroup_creation_level" AS t2_r36,
"namespaces"."emails_disabled" AS t2_r37,
"namespaces"."max_pages_size" AS t2_r38,
"namespaces"."max_artifacts_size" AS t2_r39,
"namespaces"."mentions_disabled" AS t2_r40,
"namespaces"."default_branch_protection" AS t2_r41,
"namespaces"."unlock_membership_to_ldap" AS t2_r42,
"namespaces"."max_personal_access_token_lifetime" AS t2_r43,
"namespaces"."push_rule_id" AS t2_r44,
"namespaces"."shared_runners_enabled" AS t2_r45,
"namespaces"."allow_descendants_override_disabled_shared_runners" AS t2_r46,
"namespaces"."traversal_ids" AS t2_r47,
"routes_namespaces"."id" AS t3_r0,
"routes_namespaces"."source_id" AS t3_r1,
"routes_namespaces"."source_type" AS t3_r2,
"routes_namespaces"."path" AS t3_r3,
"routes_namespaces"."created_at" AS t3_r4,
"routes_namespaces"."updated_at" AS t3_r5,
"routes_namespaces"."name" AS t3_r6
FROM ((
SELECT
projects.*
FROM
"projects"
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project'
AND "routes"."source_id" = "projects"."id"
WHERE
"projects"."namespace_id" IN (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM
"namespaces"
WHERE (traversal_ids @> ('{9970}')))
AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab'))
OR (LOWER(routes.path) = LOWER('gitlab-org/jobs-second-test'))))
UNION (
SELECT
projects.*
FROM
"projects"
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."project_namespace_id"
AND "namespaces"."type" = 'Namespaces::ProjectNamespace'
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Namespace'
AND "routes"."source_id" = "namespaces"."id"
WHERE
"projects"."namespace_id" IN (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM
"namespaces"
WHERE (traversal_ids @> ('{9970}')))
AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab'))
OR (LOWER(routes.path) = LOWER('gitlab-org/jobs-second-test'))))) projects
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project'
AND "routes"."source_id" = "projects"."id"
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."project_namespace_id"
AND "namespaces"."type" = 'Namespaces::ProjectNamespace'
LEFT OUTER JOIN "routes" "routes_namespaces" ON "routes_namespaces"."source_type" = 'Namespace'
AND "routes_namespaces"."source_id" = "namespaces"."id"
WHERE
"projects"."namespace_id" IN (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM
"namespaces"
WHERE (traversal_ids @> ('{9970}')))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 by Alexandru Croitor