Bug 19555

(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.

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?

(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.

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.

(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.

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

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?

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