Attachment 171017 Details for Bug 18705 – Bug 18705: collections_tracking itemnumber fk

[patch] Bug 18705: collections_tracking itemnumber fk

Bug-18705-collectionstracking-itemnumber-fk.patch (text/plain), 2.77 KB, created by Andreas Jonsson on 2024-09-04 11:00:18 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Andreas Jonsson

Created: 2024-09-04 11:00:18 UTC

Size: 2.77 KB

patch

obsolete

>From 224f6bf57d29d42a1d44ef2756caf2e31b61c2d2 Mon Sep 17 00:00:00 2001
>From: Andreas Jonsson <andreas.jonsson@kreablo.se>
>Date: Wed, 4 Sep 2024 10:43:51 +0000
>Subject: [PATCH] Bug 18705: collections_tracking itemnumber fk
>
>Add index an foreign key to the itemnumber in
>the collections_tracking table for maintaining
>database consistency and for scalability and
>performance of
>C4::RotatingCollections::isItemInAnyCollection
>---
> ...8705-foreign-key-on-collections_tracking.pl | 18 ++++++++++++++++++
> installer/data/mysql/kohastructure.sql         |  4 +++-
> 2 files changed, 21 insertions(+), 1 deletion(-)
> create mode 100755 installer/data/mysql/atomicupdate/bug-18705-foreign-key-on-collections_tracking.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug-18705-foreign-key-on-collections_tracking.pl b/installer/data/mysql/atomicupdate/bug-18705-foreign-key-on-collections_tracking.pl
>new file mode 100755
>index 0000000000..6b300e0ad9
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug-18705-foreign-key-on-collections_tracking.pl
>@@ -0,0 +1,18 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number  => "18705",
>+    description => "Add foreign key on column itemnumber in collections_tracking table",
>+    up          => sub {
>+        my ($args) = @_;
>+        my ( $dbh, $out ) = @$args{qw(dbh out)};
>+
>+        $dbh->do(
>+            q{DELETE FROM collections_tracking WHERE itemnumber NOT IN (SELECT itemnumber FROM items WHERE items.itemnumber = collections_tracking.itemnumber)}
>+        );
>+        $dbh->do(q{ALTER TABLE collections_tracking ADD KEY `collectionst_ibfk_2` (`itemnumber`)});
>+        $dbh->do(
>+            q{ALTER TABLE collections_tracking ADD CONSTRAINT FOREIGN KEY `collectionst_ibfk_2` (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE}
>+        );
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index f3dd950328..2748e5437a 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -2178,7 +2178,9 @@ CREATE TABLE `collections_tracking` (
>   `itemnumber` int(11) NOT NULL DEFAULT 0 COMMENT 'items.itemnumber',
>   PRIMARY KEY (`collections_tracking_id`),
>   KEY `collectionst_ibfk_1` (`colId`),
>-  CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE
>+  KEY `collectionst_ibfk_2` (`itemnumber`),
>+  CONSTRAINT `collectionst_ibfk_1` FOREIGN KEY (`colId`) REFERENCES `collections` (`colId`) ON DELETE CASCADE ON UPDATE CASCADE,
>+  CONSTRAINT `collectionst_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
> 
>-- 
>2.39.2

Actions: View | Diff | Splinter Review

Attachments on bug 18705: 63859 | 63861 | 171017 | 172579