From ad163fd481d1690dae58612e658f45b0174b3693 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..e4302a5e05
--- /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 IF NOT EXISTS `collectionst_ibfk_2` (`itemnumber`)});
+        $dbh->do(
+            q{ALTER TABLE collections_tracking ADD CONSTRAINT FOREIGN KEY IF NOT EXISTS `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