Attachment 97719 Details for Bug 23916 – Bug 23916: Add issues.issuer column and FK

[patch] Bug 23916: Add issues.issuer column and FK

Bug-23916-Add-issuesissuer-column-and-FK.patch (text/plain), 6.68 KB, created by Andrew Isherwood on 2020-01-22 13:44:07 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Andrew Isherwood

Created: 2020-01-22 13:44:07 UTC

Size: 6.68 KB

patch

obsolete

>From 21b7f83a61f228ca63fed950ca7c63c3d6eb95b7 Mon Sep 17 00:00:00 2001
>From: Andrew Isherwood <andrew.isherwood@ptfs-europe.com>
>Date: Thu, 31 Oct 2019 10:08:42 +0000
>Subject: [PATCH] Bug 23916: Add issues.issuer column and FK
>
>This patch adds the issues.issuer column which is a foreign key to
>borrowers.borrowernumber
>
>Signed-off-by: Ben Veasey <B.T.Veasey@lboro.ac.uk>
>---
> .../atomicupdate/bug_23916_add_issues_issuer.perl  | 50 ++++++++++++++++++++++
> installer/data/mysql/kohastructure.sql             |  9 +++-
> 2 files changed, 57 insertions(+), 2 deletions(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_23916_add_issues_issuer.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_23916_add_issues_issuer.perl b/installer/data/mysql/atomicupdate/bug_23916_add_issues_issuer.perl
>new file mode 100644
>index 0000000000..68df8a0447
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_23916_add_issues_issuer.perl
>@@ -0,0 +1,50 @@
>+$DBversion = 'XXX'; # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+    if( !column_exists( 'issues', 'issuer' ) ) {
>+        $dbh->do( q| ALTER TABLE issues ADD issuer INT(11) AFTER borrowernumber | );
>+    }
>+    if (!foreign_key_exists( 'issues', 'issues_ibfk_borrowers_borrowernumber' )) {
>+        $dbh->do( q| ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE | );
>+    }
>+    if( !column_exists( 'old_issues', 'issuer' ) ) {
>+        $dbh->do( q| ALTER TABLE old_issues ADD issuer INT(11) AFTER borrowernumber | );
>+    }
>+    if (!foreign_key_exists( 'old_issues', 'old_issues_ibfk_borrowers_borrowernumber' )) {
>+        $dbh->do( q| ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE | );
>+    }
>+
>+    # Now attempt to migrate previously action logged issues into issues.issuer
>+    # We assume that an item issued to a borrower on the same day is the same issue
>+    #
>+    # Get existing action logs
>+    # - user = issuer
>+    # - object = borrowernumber
>+    # - info = itemnumber
>+    my $action_logs = $dbh->selectall_arrayref("SELECT DATE(timestamp) AS dt, user, object, info FROM action_logs WHERE module='CIRCULATION' and action='ISSUE'", { Slice => {} });
>+
>+    foreach my $log( @{$action_logs} ) {
>+        # Look for an issue of this item, to this borrower, on this day
>+        # We're doing DATE comparison in the database to avoid invoking
>+        # DateTime and it's performance sapping ways...
>+        #
>+        # If we're dealing with an actual borrower
>+        if ($log->{user} != 0) {
>+            my $done_issue = $dbh->do(
>+                "UPDATE issues SET issuer = ? WHERE DATE(timestamp) = ? AND borrowernumber = ? AND itemnumber = ?",
>+                undef,
>+                ( $log->{user}, $log->{dt}, $log->{object}, $log->{info} )
>+            );
>+            # If we didn't find the issue in 'issues', look in 'old_issues'
>+            if (!$done_issue) {
>+                $dbh->do(
>+                    "UPDATE old_issues SET issuer = ? WHERE DATE(timestamp) = ? AND borrowernumber = ? AND itemnumber = ?",
>+                    undef,
>+                    ( $log->{user}, $log->{dt}, $log->{object}, $log->{info} )
>+                );
>+            }
>+        }
>+    }
>+
>+    SetVersion( $DBversion );
>+    print "Upgrade to $DBversion done (Bug 23916 - Add issues.issuer)\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 56d5d27d5a..31ebdd6d00 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1662,6 +1662,7 @@ DROP TABLE IF EXISTS `issues`;
> CREATE TABLE `issues` ( -- information related to check outs or issues
>   `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
>   `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
>+  `issuer` int(11), -- foreign key, linking this to the borrowers table for the user who checked out this item
>   `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
>   `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
>   `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
>@@ -1683,7 +1684,8 @@ CREATE TABLE `issues` ( -- information related to check outs or issues
>   KEY `branchcode_idx` (`branchcode`),
>   KEY `bordate` (`borrowernumber`,`timestamp`),
>   CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
>-  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
>+  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
>+  CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
>@@ -1694,6 +1696,7 @@ DROP TABLE IF EXISTS `old_issues`;
> CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
>   `issue_id` int(11) NOT NULL, -- primary key for issues table
>   `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
>+  `issuer` int(11) default NULL, -- foreign key, linking this to the borrowers table for the user who checked out this item
>   `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
>   `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
>   `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
>@@ -1716,7 +1719,9 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r
>   CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
>     ON DELETE SET NULL ON UPDATE SET NULL,
>   CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
>-    ON DELETE SET NULL ON UPDATE SET NULL
>+    ON DELETE SET NULL ON UPDATE SET NULL,
>+  CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`)
>+    ON DELETE SET NULL ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
>-- 
>2.11.0

Actions: View | Diff | Splinter Review

Attachments on bug 23916: 94914 | 94915 | 94916 | 94917 | 94918 | 95503 | 95504 | 95505 | 95506 | 95507 | 95508 | 95509 | 97719 | 97720 | 97721 | 97722 | 97723 | 97724 | 98586 | 98587 | 98588 | 98589 | 98590 | 98591 | 98594 | 99886 | 99887 | 99888 | 99889 | 99890 | 99891 | 110649 | 110650 | 110651 | 110652 | 110653 | 110654 | 110655 | 111485 | 111486 | 111487 | 111488 | 111489 | 111490 | 111491 | 111492 | 111966 | 111967 | 111968 | 111969 | 111970 | 111971 | 111972 | 111973 | 111974 | 112248 | 112249 | 112250 | 112251 | 112252 | 112253 | 112254 | 112255 | 112256 | 112257 | 112363 | 112364 | 112365 | 112366 | 112367 | 112368 | 112369 | 112370 | 112371 | 112372 | 112373 | 113035 | 113036 | 113037 | 113038 | 113039 | 113040 | 113041 | 113042 | 113043 | 113044 | 113045 | 113316 | 113317 | 113318 | 113319 | 113320 | 113321 | 113322 | 113323 | 113324 | 113325 | 113326 | 113327 | 113367 | 113368 | 113369 | 113370 | 113371 | 113372 | 113373 | 113374 | 113375 | 113376 | 113377 | 113378 | 113379 | 113380 | 113381 | 113382 | 113396