@@ -, +, @@ --- installer/data/mysql/atomicupdate/bug_22008.perl | 15 +++++++++++++++ installer/data/mysql/kohastructure.sql | 8 ++++++-- 2 files changed, 21 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_22008.perl --- a/installer/data/mysql/atomicupdate/bug_22008.perl +++ a/installer/data/mysql/atomicupdate/bug_22008.perl @@ -0,0 +1,15 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # Clean up + $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL;"); + $dbh->do("UPDATE accountlines SET issue_id = NULL where issue_id NOT IN (SELECT issue_id FROM issues)"); + $dbh->do("UPDATE accountlines SET manager_id = NULL where manager_id NOT IN (SELECT borrowernumber FROM borrowers)"); + + # Add Constraints + $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"); + $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_borrowers_m` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 22008 - Add missing constraints to accountlines)\n"; +} --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -2717,13 +2717,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, PRIMARY KEY (`accountlines_id`), KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), + KEY `issue_id` (`issue_id`), + KEY `manager_id` (`manager_id`), CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL + CONSTRAINT `accountlines_ibfk_2` 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_m` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --