From a5097b47c4d2ae63cf282515076ef98d8cfe21f2 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>
---
 .../data/mysql/atomicupdate/bug_22421.perl    | 55 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  5 ++
 2 files changed, 60 insertions(+)
 create mode 100644 installer/data/mysql/atomicupdate/bug_22421.perl

diff --git a/installer/data/mysql/atomicupdate/bug_22421.perl b/installer/data/mysql/atomicupdate/bug_22421.perl
new file mode 100644
index 0000000000..4bce6f0c3f
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_22421.perl
@@ -0,0 +1,55 @@
+$DBversion = 'XXX';    # will be replaced by the RM
+if ( CheckVersion($DBversion) ) {
+
+    # 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
+        });
+        $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
+        });
+        $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
+        });
+
+        # 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
+            });
+            $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
+            });
+        }
+    }
+
+    SetVersion($DBversion);
+    print "Upgrade to $DBversion done (Bug 22421 - 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 da3ff83f37..40af9a975a 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2672,6 +2672,7 @@ DROP TABLE IF EXISTS `accountlines`;
 CREATE TABLE `accountlines` (
   `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
   `issue_id` int(11) NULL DEFAULT NULL,
+  `old_issue_id` int(11) NULL DEFAULT NULL,
   `borrowernumber` int(11) DEFAULT NULL,
   `itemnumber` int(11) default NULL,
   `date` date default NULL,
@@ -2691,9 +2692,13 @@ CREATE TABLE `accountlines` (
   KEY `timeidx` (`timestamp`),
   KEY `itemnumber` (`itemnumber`),
   KEY `branchcode` (`branchcode`),
+  KEY `issue_id` (`issue_id`),
+  KEY `old_issue_id` (`old_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_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_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_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 (Apple Git-117)