Add basic CRUD operations for the new download list

Description

This task includes the following APIs:

  • Add files to list

  • Remove files from list

  • Clear list

  • Get the files available for download

  • Get the AccessRestrictions for unavailable files from the download list

This task does not include any of the asynchronous workers that are part of this feature.

Environment

None

Activity

Show:
John Hill
March 5, 2021, 1:50 AM

We tried the following changes on the large query:

  • remove the comment

  • create a single line query

  • change the acl joins to use (RA.ID = RAT.ID_OID) instead of (RA.OWNER_ID = RAT.OWNER_ID)

None of these changes seemed to make a difference when 'compression protocols' are disabled.

John Hill
March 4, 2021, 9:20 PM
Edited

Note: discovered that when you run the above query with a connection that 'use compression protocols' the same query returns in less than 100 MS. This indicates that the 349 MS query from above might not be related to the query and instead on how the query is sent to the server.

John Hill
March 4, 2021, 1:59 AM

This feature needs to be able to list the files that are available for download and those that are unavailable.

This created a challenge as our existing authorization code can only answer available/unavailable questions one entity at a time. In addition, a single availability check for a single entity would require over a dozen separate database calls. With the existing authorization code, if a user had 1K files on their download list it would take 12K database calls to determine what they can and cannot download.

In order to make this feature scale, we needed a way to answer authorization availability in batches. We were able to formulate two batch queries that gather all of the information needed to make an authorization decision.
Gathers basic entity information and the user's permissions for each entity in a batch:

On prod-346 this query executed in 349 MS with the following query plan:

The second query gathers all of the access restriction information needed to make a can-download decision:

The second query was run on prod-346 in 94 MS with the following explain plan:

Assignee

John Hill

Reporter

John Hill

Validator

Bruce Hoff

Priority

Major

Labels

None

Development Area

Portals

Sprint

Fix versions

None

Release Version History

None

Story Points

None

Epic Link

Slack Channel

None