From 34101af2f323713ac88dc4af78a8bc5900e2beb9 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 Signed-off-by: Kyle M Hall Signed-off-by: Victor Grousset/tuxayo --- .../data/mysql/atomicupdate/bug_22421.pl | 79 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 ++ 2 files changed, 85 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_22421.pl diff --git a/installer/data/mysql/atomicupdate/bug_22421.pl b/installer/data/mysql/atomicupdate/bug_22421.pl new file mode 100755 index 0000000000..766a6273a8 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22421.pl @@ -0,0 +1,79 @@ +use Modern::Perl; + +return { + bug_number => "22421", + description => "Add issue constraints to accountlines", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + # 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 + } + ); + say $out "Added column 'accountlines.old_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 + } + ); + say $out "Added constraint 'accountlines.old_issues'"; + + $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 + } + ); + say $out "Updated 'accountlines.old_issue_id' from 'old_issues'"; + + # 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 + } + ); + say $out "Fix accountlines.issue_id where missing from issues"; + + $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 + } + ); + say $out "Added constraint 'accountlines.issues'"; + } + } + + say $out "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 16310f0f44..d82343aecd 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -119,6 +119,7 @@ DROP TABLE IF EXISTS `accountlines`; CREATE TABLE `accountlines` ( `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, `issue_id` int(11) DEFAULT NULL, + `old_issue_id` int(11) DEFAULT NULL, `borrowernumber` int(11) DEFAULT NULL, `itemnumber` int(11) DEFAULT NULL, `date` timestamp NULL DEFAULT NULL, @@ -143,6 +144,8 @@ CREATE TABLE `accountlines` ( KEY `debit_type_code` (`debit_type_code`), KEY `itemnumber` (`itemnumber`), KEY `branchcode` (`branchcode`), + KEY `issue_id` (`issue_id`), + KEY `old_issue_id` (`old_issue_id`), KEY `manager_id` (`manager_id`), KEY `accountlines_ibfk_registers` (`register_id`), CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, @@ -150,11 +153,14 @@ CREATE TABLE `accountlines` ( CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) 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_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) 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_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `action_logs` -- -- 2.43.2