Add basic CRUD operations for the new download list
This task includes the following APIs:
Add files to list
Remove files from 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.
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.
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.
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: