Attachment 69457 Details for Bug 19724 – Bug 19724: Add timestamp to biblio_metadata and deletedbiblio_metadata

[patch] Bug 19724: Add timestamp to biblio_metadata and deletedbiblio_metadata

Bug-19724-Add-timestamp-to-bibliometadata-and-dele.patch (text/plain), 4.26 KB, created by David Bourgault on 2017-12-01 20:23:43 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: David Bourgault

Created: 2017-12-01 20:23:43 UTC

Size: 4.26 KB

patch

obsolete

>From 818432c70c638ddeb6905b70f04fc754abac3563 Mon Sep 17 00:00:00 2001
>From: Julian Maurice <julian.maurice@biblibre.com>
>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 <david.bourgault@inlibro.com>
>---
> 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
>
>diff --git a/installer/data/mysql/atomicupdate/bug_19724.perl b/installer/data/mysql/atomicupdate/bug_19724.perl
>new file mode 100644
>index 0000000..968f998
>--- /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 4f2bd98..05af2ce 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.7.4

Actions: View | Diff | Splinter Review

Attachments on bug 19724: 69431 | 69457 | 69478