@@ -, +, @@ deletedbiblio_metadata 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); --- installer/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 --- a/installer/data/mysql/atomicupdate/bug_19724.perl +++ a/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"; +} --- a/installer/data/mysql/kohastructure.sql +++ a/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; -- --