NuGet case-insensitive version search (!98382) · Merge requests · GitLab.org / GitLab

Steve Abrams requested to merge 273664-nuget-version-case into master Sep 19, 2022

What does this MR do and why?

NuGet packages can be published and installed from the package registry. When a user installs a package, they can specify the version. Currently, we search for an exact version match, but the NuGet docs specify that the version can be case-insensitive:

NuGetVersion uses case insenstive string comparisons for pre-release components. This means that 1.0.0-alpha and 1.0.0-Alpha are equal.

This MR updates the Finder logic to search for the version in a case-insensitive manner.

Database

-- Old query
SELECT
    "packages_packages".*
FROM
    "packages_packages"
WHERE
    "packages_packages"."project_id" = 1
    AND "packages_packages"."status" IN (0, 1)
    AND "packages_packages"."package_type" = 4
    AND "packages_packages"."version" IS NOT NULL
    AND "packages_packages"."name" ILIKE 'foo'
    AND version = '1.0.0-abc' -- this is the line that changes
ORDER BY
    "packages_packages"."created_at" DESC
LIMIT 300;

Prior to this change, here is the query and explain plan we would be using to fetch a package 19ms: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12210/commands/43384

-- New query
SELECT
    "packages_packages".*
FROM
    "packages_packages"
WHERE
    "packages_packages"."project_id" = 1
    AND "packages_packages"."status" IN (0, 1)
    AND "packages_packages"."package_type" = 4
    AND "packages_packages"."version" IS NOT NULL
    AND "packages_packages"."name" ILIKE 'foo'
    AND (LOWER(version) = '1.0.0-abc') -- this is the line that changes
ORDER BY
    "packages_packages"."created_at" DESC
LIMIT 300;

When we update the query to search with LOWER(version), the index on package_id, version is no longer used and underperforms 399ms: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12210/commands/43383

We add a new index in this MR on package_id, LOWER(version) to keep the query performant 7ms: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12210/commands/43389

Screenshots or screen recordings

Using a package named HelloWorld with version 1.3.0.17-aBc.

Installing a package specifying the version with different cases:

Output

nuget install HelloWorld -OutputDirectory . -Version 1.3.0.17-abc -Source localhost
Feeds used:
  /Users/steveabrams/.nuget/packages/
  http://gdk.test:3000/api/v4/projects/33/packages/nuget/index.json



Attempting to gather dependency information for package 'HelloWorld.1.3.0.17-abc' with respect to project '/Users/steveabrams/workspace/gdk/gitlab', targeting 'Any,Version=v0.0'
Gathering dependency information took 351 ms
Attempting to resolve dependencies for package 'HelloWorld.1.3.0.17-abc' with DependencyBehavior 'Lowest'
Resolving dependency information took 0 ms
Resolving actions to install package 'HelloWorld.1.3.0.17-abc'
Resolved actions to install package 'HelloWorld.1.3.0.17-abc'
Retrieving package 'HelloWorld 1.3.0.17-abc' from '/Users/steveabrams/.nuget/packages/'.
Adding package 'HelloWorld.1.3.0.17' to folder '/Users/steveabrams/workspace/gdk/gitlab'
Added package 'HelloWorld.1.3.0.17' to folder '/Users/steveabrams/workspace/gdk/gitlab'
Successfully installed 'HelloWorld 1.3.0.17-abc' to /Users/steveabrams/workspace/gdk/gitlab
Executing nuget actions took 188 ms

nuget install HelloWorld -OutputDirectory . -Version 1.3.0.17-abc -Source localhost
Feeds used:
  /Users/steveabrams/.nuget/packages/
  http://gdk.test:3000/api/v4/projects/33/packages/nuget/index.json



Attempting to gather dependency information for package 'HelloWorld.1.3.0.17-abc' with respect to project '/Users/steveabrams/workspace/gdk/gitlab', targeting 'Any,Version=v0.0'
Gathering dependency information took 312 ms
Attempting to resolve dependencies for package 'HelloWorld.1.3.0.17-abc' with DependencyBehavior 'Lowest'
Resolving dependency information took 0 ms
Resolving actions to install package 'HelloWorld.1.3.0.17-abc'
Resolved actions to install package 'HelloWorld.1.3.0.17-abc'
Retrieving package 'HelloWorld 1.3.0.17-aBc' from 'localhost'.
  GET http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-aBc/helloworld.1.3.0.17.nupkg
  OK http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-aBc/helloworld.1.3.0.17.nupkg 692ms
Installed HelloWorld 1.3.0.17-aBc from http://gdk.test:3000/api/v4/projects/33/packages/nuget/index.json with content hash 1Pbk5sGihV5JCE5hPLC0DirUypeW8hwSzfhD0x0InqpLRSvTEas7sPCVSylJ/KBzoxbGt2Iapg72WPbEYxLX9g==.
Adding package 'HelloWorld.1.3.0.17' to folder '/Users/steveabrams/workspace/gdk/gitlab'
Added package 'HelloWorld.1.3.0.17' to folder '/Users/steveabrams/workspace/gdk/gitlab'
Successfully installed 'HelloWorld 1.3.0.17-aBc' to /Users/steveabrams/workspace/gdk/gitlab
Executing nuget actions took 1.27 sec

Directly making an API call to the endpoint to prove case-insensitivity:

Before this change:

~ curl --user root:xoCEhDe9zvKMviK2zqzY http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-aBc/helloworld.1.3.0.17.nupkg
Warning: Binary output can mess up your terminal. Use "--output -" to tell
Warning: curl to output it to your terminal anyway, or consider "--output
Warning: <FILE>" to save to a file.

~ curl --user root:xoCEhDe9zvKMviK2zqzY http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-abc/helloworld.1.3.0.17.nupkg
{"message":"404 Not Found"}

~ curl --user root:xoCEhDe9zvKMviK2zqzY http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-ABC/helloworld.1.3.0.17.nupkg
{"message":"404 Not Found"}

After this change:

~ curl --user root:xoCEhDe9zvKMviK2zqzY http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-aBc/helloworld.1.3.0.17.nupkg
Warning: Binary output can mess up your terminal. Use "--output -" to tell
Warning: curl to output it to your terminal anyway, or consider "--output
Warning: <FILE>" to save to a file.

~ curl --user root:xoCEhDe9zvKMviK2zqzY http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-abc/helloworld.1.3.0.17.nupkg
Warning: Binary output can mess up your terminal. Use "--output -" to tell
Warning: curl to output it to your terminal anyway, or consider "--output
Warning: <FILE>" to save to a file.

~ curl --user root:xoCEhDe9zvKMviK2zqzY http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-ABC/helloworld.1.3.0.17.nupkg
Warning: Binary output can mess up your terminal. Use "--output -" to tell
Warning: curl to output it to your terminal anyway, or consider "--output
Warning: <FILE>" to save to a file.

How to set up and validate locally

You will need:

A project ID
A personal access token with API scope for a user with at least developer role for the project used in (1.)
nuget CLI installed on your machine: https://learn.microsoft.com/en-us/nuget/install-nuget-client-tools#macoslinux

To validate the change:

Download this package for testing with: helloworld.1.1.1.1.nupkg.

Add the project as a NuGet source:

nuget source Add -Name localhost -Source "http://gdk.test:3000/api/v4/projects/<project_id>/packages/nuget/index.json" -UserName root -Password <personal_access_token>

Publish the file downloaded in (1.)

nuget push helloworld.1.1.1.1.nupkg -Source localhost

In a rails console, update the version of the package (the package version differs from the file name because I had been messing around with lots of stuff, the version does not come from the filename, but from the metadata stored within the actual package)
```
Packages::Package.last.update(version: "1.3.0.17-aBc")
```
Install the package using a different case for the version. Installation should be successful
```
nuget install HelloWorld -OutputDirectory . -Version 1.3.0.17-abc -Source localhost
```

You can alternatively test by requesting with curl:

curl --user root:xoCEhDe9zvKMviK2zqzY http://gdk.test:3000/api/v4/projects/33/packages/nuget/download/HelloWorld/1.3.0.17-abc/helloworld.1.3.0.17.nupkg --output helloworld.1.3.0.17.nupkg

Cleanup

Remove the folder created from using nuget install: rm -rf HelloWorld.1.3.0.17
Remove the file created from using curl: rm helloworld.1.3.0.17.nupkg
Remove the package from your local nuget cache: rm -rf ~/.nuget/packages/helloworld

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 #273664 (closed)

Edited Sep 20, 2022 by Steve Abrams

NuGet case-insensitive version search