From 5e1534f2d8c11767e6e70639563f27956c01b08e Mon Sep 17 00:00:00 2001
From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
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 <martin.renvoize@ptfs-europe.com>
Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
---
 .../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 442f683e02..4194185729 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.39.5