From 052217e5e2e98a385100ee0aef6712951a2de5ae Mon Sep 17 00:00:00 2001
From: Pedro Amorim <pedro.amorim@ptfs-europe.com>
Date: Fri, 9 Jun 2023 14:14:05 +0000
Subject: [PATCH] Bug 33970: Update DB

Signed-off-by: Chris Walton <C.J.Walton@lboro.ac.uk>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 .../data/mysql/atomicupdate/bug_33970.pl      | 48 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  3 +-
 2 files changed, 50 insertions(+), 1 deletion(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_33970.pl

diff --git a/installer/data/mysql/atomicupdate/bug_33970.pl b/installer/data/mysql/atomicupdate/bug_33970.pl
new file mode 100755
index 0000000000..b6b8d93d4b
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_33970.pl
@@ -0,0 +1,48 @@
+use Modern::Perl;
+
+return {
+    bug_number  => "33970",
+    description => "Add 'backend' column and to composite primary key in illrequestattributes",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        if ( !column_exists( 'illrequestattributes', 'backend' ) ) {
+
+            say $out "Adding 'backend' column to 'illrequestattributes' table";
+            $dbh->do(
+                q{ ALTER TABLE illrequestattributes ADD COLUMN backend varchar(80) NOT NULL COMMENT 'API ILL backend name' AFTER illrequest_id }
+            );
+
+            say $out "Dropping illrequestattributes_ifk foreign key in 'illrequestattributes' table ";
+            $dbh->do(q{ ALTER TABLE illrequestattributes DROP FOREIGN KEY illrequestattributes_ifk });
+
+            say $out "Dropping primary key in 'illrequestattributes' table ";
+            $dbh->do(q{ ALTER TABLE illrequestattributes DROP PRIMARY KEY });
+
+            say $out "Creating new primary key in 'illrequestattributes' table ";
+            $dbh->do(q{ ALTER TABLE illrequestattributes ADD PRIMARY KEY( illrequest_id, backend, type (191)) });
+
+            say $out "Creating new foreign key constraint in 'illrequestattributes' table ";
+            $dbh->do(
+                q{
+                    ALTER TABLE illrequestattributes ADD CONSTRAINT illrequestattributes_ifk
+                    FOREIGN KEY(illrequest_id)
+                    REFERENCES illrequests(illrequest_id)
+                    ON DELETE CASCADE ON UPDATE CASCADE;
+                }
+            );
+
+            say $out "Updating backend value for all pre-existing illrequestattributes";
+            $dbh->do(
+                q{
+                    UPDATE
+                    illrequestattributes ira,
+                    illrequests ir
+                    SET ira.backend = ir.backend
+                    WHERE ira . illrequest_id = ir.illrequest_id;
+                }
+            );
+        }
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index c4fd812f96..479c3c55dd 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -3315,10 +3315,11 @@ DROP TABLE IF EXISTS `illrequestattributes`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `illrequestattributes` (
   `illrequest_id` bigint(20) unsigned NOT NULL COMMENT 'ILL request number',
+  `backend` varchar(80) NOT NULL COMMENT 'API ILL backend name',
   `type` varchar(200) NOT NULL COMMENT 'API ILL property name',
   `value` mediumtext NOT NULL COMMENT 'API ILL property value',
   `readonly` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Is this attribute read only',
-  PRIMARY KEY (`illrequest_id`,`type`(191)),
+  PRIMARY KEY (`illrequest_id`,`backend`,`type`(191)),
   CONSTRAINT `illrequestattributes_ifk` FOREIGN KEY (`illrequest_id`) REFERENCES `illrequests` (`illrequest_id`) 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