From 57737edd8f822f0c1090d3b715e16ba63877b1ef Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Wed, 27 Feb 2019 12:04:46 +0000 Subject: [PATCH] Bug 22008: Add missing constraints Signed-off-by: Kyle M Hall Signed-off-by: Josef Moravec --- installer/data/mysql/atomicupdate/bug_22008.perl | 19 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 ++++-- 2 files changed, 23 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_22008.perl diff --git a/installer/data/mysql/atomicupdate/bug_22008.perl b/installer/data/mysql/atomicupdate/bug_22008.perl new file mode 100644 index 0000000000..7f6098e65e --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22008.perl @@ -0,0 +1,19 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # Add constraint for manager_id + unless( foreign_key_exists( 'accountlines', 'accountlines_ibfk_borrowers_2' ) ) { + $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL;"); + $dbh->do("UPDATE accountlines a LEFT JOIN borrowers b ON ( a.manager_id = b.borrowernumber) SET a.manager_id = NULL WHERE b.borrowernumber IS NULL; + $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"); + } + + # Rename accountlines_ibfk_2 to accountlines_ibfk_items + if ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_2' ) && !foreign_key_exists( 'accountlines', 'accountlines_ibfk_items' ) ) { + $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2"); + $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE"); + } + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 22008 - Add missing constraints for accountlines.manager_id)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4778e034bc..8e5fde6906 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2684,15 +2684,17 @@ CREATE TABLE `accountlines` ( `lastincrement` decimal(28,6) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `note` MEDIUMTEXT NULL default NULL, - `manager_id` int(11) NULL, + `manager_id` int(11) NULL DEFAULT NULL, `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc. PRIMARY KEY (`accountlines_id`), KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), KEY `branchcode` (`branchcode`), + 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_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) 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.11.0