From daf9d619e5932fce9fe108a72a48450228feb5e6 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Fri, 1 Dec 2017 12:47:59 +0000 Subject: [PATCH] Bug 19724: Add timestamp to biblio_metadata and deletedbiblio_metadata Since bug 17196, biblioitems.timestamp is not always updated after a change in the MARC record. We need to know the last modification time of the MARC record for some features (like OAI-PMH parameters 'from' and 'until' for instance) This patch only adds the missing column in biblio_metadata and deletedbiblio_metadata Test plan: 1. Run updatedatabase.pl 2. Verify that both tables have the new column, its value should be the greatest timestamp value from the corresponding biblio and biblioitems table entries You can verify with the following SQL query: SELECT b.biblionumber, b.timestamp as biblio_ts, bi.timestamp as biblioitems_ts, m.timestamp as biblio_metadata_ts FROM biblio_metadata m LEFT JOIN biblioitems bi ON (bi.biblionumber = m.biblionumber) LEFT JOIN biblio b ON (b.biblionumber = m.biblionumber); Signed-off-by: David Bourgault Signed-off-by: Josef Moravec Signed-off-by: Jonathan Druart --- .../data/mysql/atomicupdate/bug_19724.perl | 21 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 8 +++++-- 2 files changed, 27 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_19724.perl diff --git a/installer/data/mysql/atomicupdate/bug_19724.perl b/installer/data/mysql/atomicupdate/bug_19724.perl new file mode 100644 index 0000000000..968f99810e --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_19724.perl @@ -0,0 +1,21 @@ +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + foreach my $table (qw(biblio_metadata deletedbiblio_metadata)) { + if (!column_exists($table, 'timestamp')) { + $dbh->do(qq{ + ALTER TABLE `$table` + ADD COLUMN `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `metadata`, + ADD KEY `timestamp` (`timestamp`) + }); + $dbh->do(qq{ + UPDATE $table metadata + LEFT JOIN biblioitems ON (biblioitems.biblionumber = metadata.biblionumber) + LEFT JOIN biblio ON (biblio.biblionumber = metadata.biblionumber) + SET metadata.timestamp = GREATEST(biblioitems.timestamp, biblio.timestamp); + }); + } + } + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 19724 - Add [deleted]biblio_metadata.timestamp)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4f2bd983b5..05af2cefcb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3981,9 +3981,11 @@ 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, 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 + CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, + KEY `timestamp` (`timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -3997,9 +3999,11 @@ 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, 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 + CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, + KEY `timestamp` (`timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- 2.39.5