Attachment 120100 Details for Bug 17202 – Bug 17202: (QA follow-up) Add foreign key constraint

[patch] Bug 17202: (QA follow-up) Add foreign key constraint

Bug-17202-QA-follow-up-Add-foreign-key-constraint.patch (text/plain), 2.10 KB, created by Kyle M Hall (khall) on 2021-04-23 13:36:49 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2021-04-23 13:36:49 UTC

Size: 2.10 KB

patch

obsolete

>From ad3cc49e116f60e76254af06e59e5d4390ec3b1e Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>Date: Fri, 23 Apr 2021 09:36:03 -0400
>Subject: [PATCH] Bug 17202: (QA follow-up) Add foreign key constraint
>
>Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
>---
> installer/data/mysql/atomicupdate/bug_17202.perl | 12 ++++++++++++
> installer/data/mysql/kohastructure.sql           |  3 ++-
> 2 files changed, 14 insertions(+), 1 deletion(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_17202.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_17202.perl b/installer/data/mysql/atomicupdate/bug_17202.perl
>new file mode 100644
>index 0000000000..875675b90f
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_17202.perl
>@@ -0,0 +1,12 @@
>+$DBversion = 'XXX'; # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+    $dbh->do(q{
>+        DELETE FROM collections_tracking WHERE colId NOT IN ( SELECT colId FROM collections )
>+    });
>+    $dbh->do(q{
>+        ALTER TABLE collections_tracking 
>+        ADD CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE
>+    });
>+
>+    NewVersion( $DBversion, 17202, "Add FK constraint for collection to collections_tracking");
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 16d2b6eb0d..aa39f89043 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1980,7 +1980,8 @@ CREATE TABLE `collections_tracking` (
>   `collections_tracking_id` int(11) NOT NULL AUTO_INCREMENT,
>   `colId` int(11) NOT NULL DEFAULT 0 COMMENT 'collections.colId',
>   `itemnumber` int(11) NOT NULL DEFAULT 0 COMMENT 'items.itemnumber',
>-  PRIMARY KEY (`collections_tracking_id`)
>+  PRIMARY KEY (`collections_tracking_id`),
>+  CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
> 
>-- 
>2.24.3 (Apple Git-128)

Actions: View | Diff | Splinter Review

Attachments on bug 17202: 119306 | 119330 | 120099 | 120100 | 120216 | 120217 | 120218 | 120219