From 4c99baaa1cff7ff4f61bf7c6d57b8a96ccfbfc91 Mon Sep 17 00:00:00 2001 From: Andrew Isherwood 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 Signed-off-by: Bouzid Fergani --- .../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 fc9953bb2b..b7157bfb48 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1617,6 +1617,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 @@ -1638,7 +1639,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; -- @@ -1649,6 +1651,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 @@ -1671,7 +1674,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.20.1