Resolve "BE: Storage project table - "Total" column sorting"
What does this MR do and why?
Adds two options to namespace projects GraphQL resolver to sort by storage ascending and descending.
Also it fixes a math issue in — this was agreed to be reverted, see !111211 (comment 1316052501).STORAGE
sorting option, and renames it to REMAINING_REPO_STORAGE_SIZE_ASC
. This renaming requires a deprecation process, started in this issue: Deprecate GraphQL `STORAGE` enum in NamespacePr... (#396284)
Screenshots or screen recordings
N/A
How to set up and validate locally
In your local http://localhost:3000/-/graphql-explorer try following queries:
query
query getNamespaceStorageStatistics($fullPath: ID!, $searchTerm: String = "", $first: Int, $last: Int, $after: String, $before: String, $sort: NamespaceProjectSort) {
namespace(fullPath: $fullPath) {
id
name
storageSizeLimit
actualRepositorySizeLimit
additionalPurchasedStorageSize
totalRepositorySizeExcess
totalRepositorySize
containsLockedProjects
repositorySizeExcessProjectCount
rootStorageStatistics {
storageSize
repositorySize
lfsObjectsSize
containerRegistrySize
buildArtifactsSize
pipelineArtifactsSize
packagesSize
wikiSize
snippetsSize
uploadsSize
__typename
}
projects(
includeSubgroups: true
search: $searchTerm
first: $first
last: $last
after: $after
before: $before
sort: $sort
) {
nodes {
id
fullPath
nameWithNamespace
avatarUrl
webUrl
name
repositorySizeExcess
actualRepositorySizeLimit
statistics {
commitCount
storageSize
repositorySize
lfsObjectsSize
containerRegistrySize
buildArtifactsSize
packagesSize
wikiSize
snippetsSize
uploadsSize
__typename
}
__typename
}
pageInfo {
...PageInfo
__typename
}
__typename
}
__typename
}
}
fragment PageInfo on PageInfo {
hasNextPage
hasPreviousPage
startCursor
endCursor
__typename
}
Passing different values to sort param, e.g.
{
"searchTerm": "",
"fullPath": "h5bp",
"first": 20,
"sort": "STORAGE_SIZE_DESC"
}
DB Queries
In scope of this task one new query was introduced (with ASC and DESC sorting), and one was modified (all three are ActiveRecord-driven queries)
New query introduced:
SELECT "projects".*, "project_statistics"."storage_size" AS project_statistics_storage_size FROM "projects" INNER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{22}'))) ORDER BY "project_statistics"."storage_size" ASC NULLS FIRST, "projects"."id" DESC LIMIT 21
Query plan with namespace.id = 1701395
: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/16208/commands/55596
Query that was modified:
NOTE: These changes were reverted. See !111211 (comment 1316052501)
Before
SELECT "projects".*, (("project_statistics"."repository_size" + "project_statistics"."lfs_objects_size") - COALESCE("projects"."repository_size_limit", 0, 0)) AS excess_storage, "projects"."id" AS projects_id FROM "projects" INNER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{22}'))) ORDER BY (("project_statistics"."repository_size" + "project_statistics"."lfs_objects_size") - COALESCE("projects"."repository_size_limit", 0, 0)) DESC, "projects"."id" DESC LIMIT 21
After
SELECT "projects".*, (COALESCE("projects"."repository_size_limit", 1152921504606846976) - ("project_statistics"."repository_size" + "project_statistics"."lfs_objects_size")) AS remaining_repo_storage_size, "projects"."id" AS projects_id FROM "projects" INNER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{22}'))) AND (("projects"."path" ILIKE '%project%' OR "projects"."name" ILIKE '%project%') OR "projects"."description" ILIKE '%project%') ORDER BY (COALESCE("projects"."repository_size_limit", 1152921504606846976) - ("project_statistics"."repository_size" + "project_statistics"."lfs_objects_size")) ASC, "projects"."id" DESC LIMIT 21
Query plan with namespace.id = 1701395
: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/16208/commands/55595
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.
Related to #389421