[MODEXPW-46] Get matching records based on provided query Created: 19/Sep/21  Updated: 17/Dec/21  Resolved: 17/Dec/21

Status: Closed
Project: mod-data-export-worker
Components: None
Affects versions: None
Fix versions: 1.3.0

Type: Story Priority: P3
Reporter: Illia Daliek Assignee: Siarhei Charniak
Resolution: Done Votes: 0
Labels: bulk-edit-pilot
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Attachments: File 2021-12-11-Matched-Records-users_staff.csv     File BULK_EDIT_QUERY_2021-12-17_03-56-36_2354925b-84c7-4c47-aa0b-cfa53f6320e1.csv     PNG File image-2021-12-15-19-15-19-506.png     File users_staff.cql    
Issue links:
Defines
defines UXPROD-3229 Identify user records for bulk edit Closed
Gantt End to Start
has to be done before MODEXPW-51 Records processing errors handling Closed
has to be done after MODEXPS-43 Add new export type Closed
has to be done after MODEXPW-45 Get matching records based on provide... Closed
Sprint: Firebird Sprint 129
Story Points: 5
Development Team: Firebird

 Description   

Purpose/Overview:
The user should be able to send a query to get the matched records CSV file. The behavior is similar to uploading the UUIDs CSV file but with the query used instead. The UI will need to display a preview of the records (top 10).

Requirements/Scope:
Note that in the scope of the Pilot project only query search by user groups will be supported.

  1. The "query" param is supported for generating the matched records CSV file
  2. The user flow should be the same as uploading the UUIDs file flow

Approach:
1) Extend the JobCommand export type filed with the new value for the bulk edit export that supports the CQL query. (UI should send this information - query, export type)
2) Implement a new Job for CQL support.
3) For CQL the new Partitioner should be implemented which will split the query into parts of limit and offset parameters that cover the entire related data set. So these parts will be processed asynchronously.

Acceptance criteria:

  • Response contains all fields from the user record
  • Keys are replaced with corresponding values
  • Query parameter is supported
  • The flow with query parameter for the user is the same as uploading the UUIDs file.
  • The response contains all fields from the user record
  • Keys are replaced with corresponding values
  • Unit and API tests are written where applicable


 Comments   
Comment by Magda Zacharska [ 19/Nov/21 ]

Illia Daliek please do not make changes to the description of the jira after it was estimated by the team. Also, please do not make changes to the acceptance criteria without letting PO know. Any required changes should be discussed and documented in the comments to the ticket.

Comment by Siarhei Charniak [ 11/Dec/21 ]

Magda Zacharska
Verified on folio-snapshot environment.

1. Create a new job

curl --location --request POST 'https://folio-snapshot-okapi.dev.folio.org:443/data-export-spring/jobs' \
--header 'x-okapi-tenant: diku' \
--header "x-okapi-token: $TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{
  "type": "BULK_EDIT_QUERY",
  "exportTypeSpecificParameters": {},
  "identifierType": "BARCODE",
  "entityType": "USER"
}'
{
    "id": "24e7179f-b715-4a41-b5df-a4b7826738d2",
    "name": "000003",
    "source": "diku_admin",
    "isSystemSource": false,
    "type": "BULK_EDIT_QUERY",
    "exportTypeSpecificParameters": {},
    "status": "SCHEDULED",
    "metadata": {
        "createdDate": 1639190773905,
        "createdByUserId": "e904b7a3-fec2-5dc2-be76-70fae47f74da",
        "createdByUsername": "diku_admin",
        "updatedDate": 1639190773905,
        "updatedByUserId": "e904b7a3-fec2-5dc2-be76-70fae47f74da",
        "updatedByUsername": "diku_admin"
    },
    "identifierType": "BARCODE",
    "entityType": "USER"
}

2. Upload file attached users_staff.cql with CQL query

curl --location --request POST 'https://folio-snapshot-okapi.dev.folio.org:443/bulk-edit/24e7179f-b715-4a41-b5df-a4b7826738d2/upload' \
--header 'x-okapi-tenant: diku' \
--header "x-okapi-token: $TOKEN" \
--header 'Content-Type: multipart/form-data' \
--form 'file=@"/Users/siarhei/Downloads/users_staff.cql"'
200 OK

3. Check job status - SUCCESSFUL

curl --location --request GET 'https://folio-snapshot-okapi.dev.folio.org:443/data-export-spring/jobs/24e7179f-b715-4a41-b5df-a4b7826738d2' \
--header 'x-okapi-tenant: diku' \
--header "x-okapi-token: $TOKEN" \
--header 'Content-Type: application/json'
{
    "id": "24e7179f-b715-4a41-b5df-a4b7826738d2",
    "name": "000003",
    "source": "diku_admin",
    "isSystemSource": false,
    "type": "BULK_EDIT_QUERY",
    "exportTypeSpecificParameters": {},
    "status": "SUCCESSFUL",
    "files": [
        "https://folio-snapshot-export.s3.us-east-1.amazonaws.com/2021-12-11-Matched-Records-users_staff.csv?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIA2VGNPE73JV77RBGE%2F20211211%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211211T024729Z&X-Amz-Expires=604800&X-Amz-SignedHeaders=host&X-Amz-Signature=fdb1e0da79ca69318732e30e5d2a77e49938c3263375aab20ecfe78bf6e11990"
    ],
    "startTime": 1639190848933,
    "endTime": 1639190849672,
    "metadata": {
        "createdDate": 1639190773905,
        "createdByUserId": "e904b7a3-fec2-5dc2-be76-70fae47f74da",
        "createdByUsername": "diku_admin",
        "updatedDate": 1639190849682,
        "updatedByUserId": "e904b7a3-fec2-5dc2-be76-70fae47f74da",
        "updatedByUsername": "diku_admin"
    },
    "identifierType": "BARCODE",
    "entityType": "USER"
}

The processing result is in 2021-12-11-Matched-Records-users_staff.csv file (attached).

Comment by Magda Zacharska [ 16/Dec/21 ]

Siarhei Charniak does the current implementation require .cql file?   In bulk edit the cql will be generated in the UI not submitted by the file:

Comment by Siarhei Charniak [ 17/Dec/21 ]

Magda Zacharska
Verified on folio-snapshot environment

1. Create a new job with query

curl --location --request POST 'https://folio-snapshot-okapi.dev.folio.org:443/data-export-spring/jobs' \
--header 'x-okapi-tenant: diku' \
--header "x-okapi-token: $TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{
  "type": "BULK_EDIT_QUERY",
  "exportTypeSpecificParameters": {
      "query": "(patronGroup==\"3684a786-6671-4268-8ed0-9db82ebca60b\") sortby personal.lastName"
  },
  "entityType": "USER"
}'
{
    "id": "2354925b-84c7-4c47-aa0b-cfa53f6320e1",
    "name": "000001",
    "source": "diku_admin",
    "isSystemSource": false,
    "type": "BULK_EDIT_QUERY",
    "exportTypeSpecificParameters": {
        "query": "(patronGroup==\"3684a786-6671-4268-8ed0-9db82ebca60b\") sortby personal.lastName"
    },
    "status": "SCHEDULED",
    "metadata": {
        "createdDate": 1639713396273,
        "createdByUserId": "3a4e9dfe-8779-56df-a1a7-a618ac1326aa",
        "createdByUsername": "diku_admin",
        "updatedDate": 1639713396273,
        "updatedByUserId": "3a4e9dfe-8779-56df-a1a7-a618ac1326aa",
        "updatedByUsername": "diku_admin"
    },
    "entityType": "USER"
}

2. Check job status - SUCCESSFUL

curl --location --request GET 'https://folio-snapshot-okapi.dev.folio.org:443/data-export-spring/jobs/2354925b-84c7-4c47-aa0b-cfa53f6320e1' \
--header 'x-okapi-tenant: diku' \
--header "x-okapi-token: $TOKEN" \
--header 'Content-Type: application/json'
{
    "id": "2354925b-84c7-4c47-aa0b-cfa53f6320e1",
    "name": "000001",
    "source": "diku_admin",
    "isSystemSource": false,
    "type": "BULK_EDIT_QUERY",
    "exportTypeSpecificParameters": {
        "query": "(patronGroup==\"3684a786-6671-4268-8ed0-9db82ebca60b\") sortby personal.lastName"
    },
    "status": "SUCCESSFUL",
    "files": [
        "https://folio-snapshot-export.s3.us-east-1.amazonaws.com/BULK_EDIT_QUERY_2021-12-17_03%3A56%3A36_2354925b-84c7-4c47-aa0b-cfa53f6320e1.csv?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIA2VGNPE73JV77RBGE%2F20211217%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211217T035638Z&X-Amz-Expires=604800&X-Amz-SignedHeaders=host&X-Amz-Signature=073ab16b1ffa43342d3d74bcd30436da33c11c7838b8b4d04ed1ac8057b0a05c"
    ],
    "startTime": 1639713396793,
    "endTime": 1639713398115,
    "metadata": {
        "createdDate": 1639713396273,
        "createdByUserId": "3a4e9dfe-8779-56df-a1a7-a618ac1326aa",
        "createdByUsername": "diku_admin",
        "updatedDate": 1639713398127,
        "updatedByUserId": "3a4e9dfe-8779-56df-a1a7-a618ac1326aa",
        "updatedByUsername": "diku_admin"
    },
    "entityType": "USER"
}

The processing result is in attached file BULK_EDIT_QUERY_2021-12-17_03-56-36_2354925b-84c7-4c47-aa0b-cfa53f6320e1.csv

Generated at Thu Feb 08 22:30:14 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.