SPIKE: Store all statuses in holdings_status table?
Priority
P3Labels
Environment
Template
Description
Development Team
Release
Story Points
Sprint
relates to
Checklist
hideTestRail: Results
Activity
Sobha Duvvuri October 14, 2019 at 5:30 PM
Sounds good. Thanks
Andrii Paias October 8, 2019 at 10:24 AM
Added user story for approach 2 : MODKBEKBJ-320
Andrii Paias October 8, 2019 at 9:53 AMEdited
There are two approaches for storing history:
1) Allow adding multiple rows to holdings_status table and when status needs to be updated execute "INSERT ..." statement with new update date.
This approach will complicate current implementation because we will need to rewrite most sql queries for holdings_status table, this can be difficult because some queries are complex:
https://github.com/folio-org/mod-kb-ebsco-java/blob/fea6fea4f93ed8ea626abedb163e8a26ebf78122/src/main/java/org/folio/repository/holdings/status/HoldingsStatusTableConstants.java#L14
Additionally we would need to rewrite following trigger for updating status:
https://github.com/folio-org/mod-kb-ebsco-java/blob/35cc0a7e5141e46843b67c161c2bed37df52c4b3/src/main/resources/templates/db_scripts/update_started_date_before_insertion.sql#L10
2)Add a separate table with status history:
We will need to add trigger that will run before holdings_status table is updated. The trigger will save new version of status to a separate holdings_status_history table.
We also need to periodically clean status history, we can do it by deleting all status records that are older than set amount of days(e.g. 30 days). This operation can run before POST /loadHoldings request, that way we won't have to configure another periodic job
While investigating issues related to holdings process, we noticed that it is difficult to trace logs for statuses -
Will it make sense to store all statuses in holdings_status table for each load and give the latest entry when requested from the GET endpoint? Will we clear out the table as soon as a process starts and populate fresh statuses for each load?
Outcome
Determine approach
Create a story IF the work can be estimated at 1 or 2 pts. Otherwise write up spike findings and we will consider in the future if more thant 2 pts estimated.
Timebox: 4 hours