From b01a94f4a0b203505230954561a5a2671e569653 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 6 Jun 2019 12:40:11 +0100 Subject: [PATCH] Bug 22421: Add missing constraints DB update to add a new old_issue_id field to accountlines and set foreign key constraints for both the new field and the existing issue_id field. Signed-off-by: Martin Renvoize --- .../data/mysql/atomicupdate/bug_22421.perl | 55 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 5 ++ 2 files changed, 60 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_22421.perl diff --git a/installer/data/mysql/atomicupdate/bug_22421.perl b/installer/data/mysql/atomicupdate/bug_22421.perl new file mode 100644 index 0000000000..35b0cc810b --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22421.perl @@ -0,0 +1,55 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if ( CheckVersion($DBversion) ) { + + # Add old_issue_id field + unless ( column_exists( 'accountlines', 'old_issue_id' ) ) { + $dbh->do(q{ + ALTER TABLE accountlines + ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id + }); + $dbh->do(q{ + ALTER TABLE accountlines + ADD CONSTRAINT `accountlines_ibfk_old_issues` + FOREIGN KEY (`old_issue_id`) + REFERENCES `old_issues` (`issue_id`) + ON DELETE SET NULL + ON UPDATE CASCADE + }); + $dbh->do(q{ + UPDATE + accountlines a + LEFT JOIN old_issues o ON (a.issue_id = o.issue_id) + SET + a.old_issue_id = o.issue_id, + a.issue_id = NULL + WHERE + o.issue_id IS NOT NULL + }); + + # Add constraint for issue_id + unless ( + foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) ) + { + $dbh->do(q{ + UPDATE + accountlines a + LEFT JOIN issues i ON (a.issue_id = i.issue_id) + SET + a.issue_id = NULL + WHERE + i.issue_id IS NULL + }); + $dbh->do(q{ + ALTER TABLE accountlines + ADD CONSTRAINT `accountlines_ibfk_issues` + FOREIGN KEY (`issue_id`) + REFERENCES `issues` (`issue_id`) + ON DELETE SET NULL + ON UPDATE CASCADE + }); + } + } + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 22421 - Add old_issue_id to accountlines and setup appropriate constraints)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index da3ff83f37..40af9a975a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2672,6 +2672,7 @@ DROP TABLE IF EXISTS `accountlines`; CREATE TABLE `accountlines` ( `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, `issue_id` int(11) NULL DEFAULT NULL, + `old_issue_id` int(11) NULL DEFAULT NULL, `borrowernumber` int(11) DEFAULT NULL, `itemnumber` int(11) default NULL, `date` date default NULL, @@ -2691,9 +2692,13 @@ CREATE TABLE `accountlines` ( KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), KEY `branchcode` (`branchcode`), + KEY `issue_id` (`issue_id`), + KEY `old_issue_id` (`old_issue_id`), KEY `manager_id` (`manager_id`), CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.20.1