From 60a6cd450b7a3b5c9273d737b27adfff32189bd4 Mon Sep 17 00:00:00 2001 From: Magnus Enger Date: Wed, 1 Nov 2017 15:19:27 +0000 Subject: [PATCH] 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: /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 --- Koha/OAI/Server/ListBase.pm | 6 +++--- .../bug19555-biblio_metadata-timestamp.perl | 17 +++++++++++++++++ installer/data/mysql/kohastructure.sql | 2 ++ t/db_dependent/OAI/Server.t | 4 ++-- 4 files changed, 24 insertions(+), 5 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug19555-biblio_metadata-timestamp.perl diff --git a/Koha/OAI/Server/ListBase.pm b/Koha/OAI/Server/ListBase.pm index 0c7c56a..92af1c3 100644 --- a/Koha/OAI/Server/ListBase.pm +++ b/Koha/OAI/Server/ListBase.pm @@ -58,7 +58,7 @@ sub GetRecords { # first deleted records ($deleted == 1), then normal records ($deleted == 0) STAGELOOP: for ( ; $deleted >= 0; $deleted-- ) { - my $table = $deleted ? 'deletedbiblioitems' : 'biblioitems'; + my $table = $deleted ? 'deletedbiblio_metadata' : 'biblio_metadata'; my $sql = " SELECT biblionumber FROM $table @@ -103,7 +103,7 @@ sub GetRecords { $sql = " SELECT MAX(timestamp) FROM ( - SELECT timestamp FROM deletedbiblioitems WHERE biblionumber = ? + SELECT timestamp FROM deletedbiblio_metadata WHERE biblionumber = ? UNION SELECT timestamp FROM deleteditems WHERE biblionumber = ? ) bis @@ -112,7 +112,7 @@ sub GetRecords { $sql = " SELECT MAX(timestamp) FROM ( - SELECT timestamp FROM biblioitems WHERE biblionumber = ? + SELECT timestamp FROM biblio_metadata WHERE biblionumber = ? UNION SELECT timestamp FROM deleteditems WHERE biblionumber = ? UNION diff --git a/installer/data/mysql/atomicupdate/bug19555-biblio_metadata-timestamp.perl b/installer/data/mysql/atomicupdate/bug19555-biblio_metadata-timestamp.perl new file mode 100644 index 0000000..f9c9aa7 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug19555-biblio_metadata-timestamp.perl @@ -0,0 +1,17 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # or perform some test and warn + unless( column_exists( 'biblio_metadata', 'timestamp' ) ) { + $dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" ); + } + + # or perform some test and warn + unless( column_exists( 'deletedbiblio_metadata', 'timestamp' ) ) { + $dbh->do( "ALTER TABLE deletedbiblio_metadata ADD COLUMN timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" ); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 19555 - Add timestamps to biblio_metadata and deletedbiblio_metadata)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e547168..13ee734 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3980,6 +3980,7 @@ CREATE TABLE biblio_metadata ( `format` VARCHAR(16) NOT NULL, `marcflavour` VARCHAR(16) NOT NULL, `metadata` LONGTEXT NOT NULL, + timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched PRIMARY KEY(id), UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE @@ -3996,6 +3997,7 @@ CREATE TABLE deletedbiblio_metadata ( `format` VARCHAR(16) NOT NULL, `marcflavour` VARCHAR(16) NOT NULL, `metadata` LONGTEXT NOT NULL, + timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched PRIMARY KEY(id), UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE diff --git a/t/db_dependent/OAI/Server.t b/t/db_dependent/OAI/Server.t index 951d7b0..d55b706 100644 --- a/t/db_dependent/OAI/Server.t +++ b/t/db_dependent/OAI/Server.t @@ -69,7 +69,7 @@ $dbh->do('DELETE FROM oai_sets'); my $date_added = DateTime->now() . 'Z'; my $date_to = substr($date_added, 0, 10) . 'T23:59:59Z'; my (@header, @marcxml, @oaidc); -my $sth = $dbh->prepare('SELECT timestamp FROM biblioitems WHERE biblionumber=?'); +my $sth = $dbh->prepare('SELECT timestamp FROM biblio_metadata WHERE biblionumber=?'); # Add biblio records foreach my $index ( 0 .. NUMBER_OF_MARC_RECORDS - 1 ) { @@ -344,7 +344,7 @@ sub manipulate_timestamp { return $timestamp if $timestamp !~ /\d{2}Z/; my $secs = sprintf( "%02d", $index ); $timestamp =~ s/\d{2}Z/${secs}Z/; - $dbh->do("UPDATE biblioitems SET timestamp=? WHERE biblionumber=?", undef, + $dbh->do("UPDATE biblio_metadata SET timestamp=? WHERE biblionumber=?", undef, ( $timestamp, $bibno )); return $timestamp; } -- 2.1.4