Attachment 90366 Details for Bug 22421 – Bug 22421: Add missing constraints

[patch] Bug 22421: Add missing constraints

Bug-22421-Add-missing-constraints.patch (text/plain), 4.51 KB, created by Kyle M Hall (khall) on 2019-06-06 13:09:23 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2019-06-06 13:09:23 UTC

Size: 4.51 KB

patch

obsolete

>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)

Actions: View | Diff | Splinter Review

Attachments on bug 22421: 85773 | 85774 | 85775 | 90352 | 90353 | 90354 | 90359 | 90360 | 90361 | 90362 | 90365 | 90366 | 90367 | 90368 | 161919 | 161920 | 161921 | 161922 | 161923 | 162244 | 162245 | 162246 | 162247 | 162248 | 166631 | 173287 | 173288 | 173289 | 173305 | 173790 | 173791 | 173792 | 173793 | 173794 | 173795 | 173796 | 173797 | 173798 | 173799 | 174281