From 56c3ed3196e10b445139584cbe1ae20477cae6da Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Mon, 17 Dec 2018 13:29:08 +0000 Subject: [PATCH] Bug 22008: Add missing constraints to accountlines Add constraints for manager_id referencing the borrowers table and issue_id referencing the issues table and update the itemnumber constraint from `ON UPDATE SET NULL` to `ON UPDATE CASCADE` --- .../data/mysql/atomicupdate/bug_22008.perl | 31 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 8 +++-- 2 files changed, 37 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..f6c652e19b --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22008.perl @@ -0,0 +1,31 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # Add constraint for manager_id + my $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_borrowers_2'|); + $sth->execute; + unless ($sth->fetchrow_hashref) { + $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL;"); + $dbh->do("UPDATE accountlines SET manager_id = NULL where manager_id NOT IN (SELECT borrowernumber FROM borrowers)"); + $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"); + } + + # Add constraint for issue_id + $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_issues'|); + $sth->execute; + unless ($sth->fetchrow_hashref) { + $dbh->do("UPDATE accountlines SET issue_id = NULL where issue_id NOT IN (SELECT issue_id FROM issues)"); + $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE"); + } + + # Rename accountlines_ibfk_2 to accountlines_ibfk_items + $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_2'|); + $sth->execute; + if ($sth->fetchrow_hashref) { + $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 to accountlines)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index fa5044e9fe..272ffa2746 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2719,15 +2719,19 @@ 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 `issue_id` (`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_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_issues` FOREIGN KEY (`issue_id`) REFERENCES `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