Bug 19555 - Some changes to records are not reflected in OAI-PMH
Summary: Some changes to records are not reflected in OAI-PMH
Status: RESOLVED DUPLICATE of bug 19724
Alias: None
Product: Koha
Classification: Unclassified
Component: Web services (show other bugs)
Version: Main
Hardware: All All
: P5 - low major (vote)
Assignee: Magnus Enger
QA Contact: Tomás Cohen Arazi
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-10-31 16:20 UTC by Magnus Enger
Modified: 2017-12-04 08:08 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 19555: Some changes to records are not reflected in OAI-PMH (6.85 KB, patch)
2017-11-02 09:15 UTC, Magnus Enger
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Magnus Enger 2017-10-31 16:20:29 UTC
The OAI-PMH server can take "from" and "until" dates, in which case it should return records that were added or changed in the given date range:

/koha/oai.pl?verb=ListRecords&metadataPrefix=marcxchange&from=2017-10-26&until=2017-10-28

In current master, Koha uses biblioitems.timestamp to figure out which records should be returned for a given timeframe: 
http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=Koha/OAI/Server/ListBase.pm;h=0c7c56a13d7fcc977e5de5ff471059c9e32d7cfb;hb=HEAD#l63

This worked fine when the marcxml column was still in the biblioitems table. Any change to the record would update the timestamp and it would be reflected in OAI-PMH. 

But then we yanked marcxml out of the biblioitems table, and moved it to biblio_metadata.metadata. This means that biblioitems.timestamp only gets updated when one of the MARC-fields that has its own column in biblioitems gets updated. And this means that some changes are not reflected in OAI-PMH. 

To verify: 
- Open a record for editing in Koha
- Look at the row in biblioitems that corresponds to the record
- Change e.g. one of the subjects -> the timestamp is not updated
- Change e.g. the ISBN -> the timestamp IS updated

The solution that comes to my mind first is to:
- add a timestamp to the biblio_metadata table, and
- consider both biblioitems.timestamp and biblio_metadata.timestamp when OAI-PMH decides what records to return

Opinions?
Comment 1 Tomás Cohen Arazi 2017-10-31 17:33:47 UTC
(In reply to Magnus Enger from comment #0)
> The solution that comes to my mind first is to:
> - add a timestamp to the biblio_metadata table, and
> - consider both biblioitems.timestamp and biblio_metadata.timestamp when
> OAI-PMH decides what records to return
> 
> Opinions?

We need to make sure any change on the record (even to non-mapped fields, which is my bet here) updates the timestamp.
Comment 2 Katrin Fischer 2017-11-01 11:36:26 UTC
Hm, when we add the timestamp to the new table, wouldn't looking at that not be enough? I think that would be easiest. 
I think I saw on another bug that changes to items weren't reflected either.
 Is this already fixed or could be fixed in one go with this here?
Comment 3 Tomás Cohen Arazi 2017-11-01 11:57:04 UTC
(In reply to Katrin Fischer from comment #2)
> Hm, when we add the timestamp to the new table, wouldn't looking at that not
> be enough? I think that would be easiest. 
> I think I saw on another bug that changes to items weren't reflected either.
>  Is this already fixed or could be fixed in one go with this here?

biblio and biblioitems are both the same table, just splitted. When a bibliographic record is modified, the timestamp should be changed (no matter in which of the tables is). The problem might be that we are relying on the DB feature of updating the timestamp when the row is changed. Which doesn't happen when no mapping triggers a row change.

Regarding items, the items timestamps should be checked if include_items is set.
Comment 4 Katrin Fischer 2017-11-01 12:02:50 UTC
Yes, we are using the MySQL feature, I think for all of our timestamps actually. That's why Magnus suggested to put one on the metadata table and use that.
Comment 5 Tomás Cohen Arazi 2017-11-01 12:05:00 UTC
(In reply to Katrin Fischer from comment #4)
> Yes, we are using the MySQL feature, I think for all of our timestamps
> actually. That's why Magnus suggested to put one on the metadata table and
> use that.

I don't oppose.
Comment 6 Magnus Enger 2017-11-02 09:15:19 UTC
Created attachment 68888 [details] [review]
Bug 19555: Some changes to records are not reflected in OAI-PMH

When we moved biblioitems.marcxml to biblio_metadata.metadata, this
meant that biblioteitems.timestamp was only updated when there
were changes to MARC fields that have corresponding columns in
the biblioitems table, like ISBN. This patch adds a timestamp
column to biblio_metadata and makes OAI-PMH use that to decide
if a record should be included in a response or not. This should
mean that any change to the records are reflected in OAI-PMH.

To test:
- Before applying the patch:
- Look at at the data in biblioitems for a record, e.g.:
  SELECT * FROM biblioitems WHERE biblionumber = x\G
- Edit the record, and verify that a change to e.g. ISBN updates
  the timestamp, and a change to e.g. a subject does not
- Apply the patch and make sure the atomicupdate is run
- Look at the data in biblio_metadata for the record. e.g.:
  SELECT * FROM biblio_metadata WHERE biblionumber = x\G
- Verify that any change to the record results in a change to
  the timestamp
- Enable OAI-PMH with the OAI-PMH syspref
- Visit this URL in your browser:
  <opac>/cgi-bin/koha/oai.pl?verb=ListRecords&metadataPrefix=marcxml&from=2017-10-26&until=2017-11-02
- Change the from and until dates as well as the
  biblio_metadata.timestamp value (e.g.: UPDATE biblio_metadata
  SET timestamp = '2017-10-24' WHERE biblionumber = x), and verify
  that the record is included or not included in the OAI-PMH
  response as expected
- Run the tests: prove t/db_dependent/OAI/*.t
Comment 7 Magnus Enger 2017-11-02 10:56:57 UTC
My patch just adds timestamp columns to biblio_metadata and deletedbiblio_metadata. This means the timestamps in those columns will be 0000-00-00 00:00:00. 

Possible things to do:

1. One thing we could do is copy over data from biblioitems.timestamp to biblio_metadata.timestamp. That would give us non-zero values, but those values would still be as wrong as the values in biblioitems.timestamp are. 

2. One step further, at least for MARC21, would be to extract the date from 005 and put that in biblio_metadata.timestamp. That should give pretty accurate timestamps, as long as the value in 005 makes sense. 

3. The problem with both 1 and 2 is that some biblio_metadata.timestamps will be way back in time. If a client does a harvest like verb=ListRecords&metadataPrefix=marcxchange&from=2017-10-26&until=2017-11-02 to pick up the most recent changes, those records with old timestamps will still not be picked up. So the best solution is perhaps to set biblio_metadata.timestamp to the value of 005, BUT when that timestamp is more recent than biblioitems.timestamp, set biblio_metadata.timestamp to NOW(), so that a harvest of "recent updates" will catch all these old changes. 

Should we put this in the databaseupdate, or is it too complex? Is it better to have a note in the databaseupdate pointing to a page on the wiki that details these options and lets the user/admin choose which one they want?
Comment 8 Magnus Enger 2017-12-04 08:08:54 UTC

*** This bug has been marked as a duplicate of bug 19724 ***