From b60671cff3664d59fabdf0799f1c4a19077bab83 Mon Sep 17 00:00:00 2001
From: Tomas Cohen Arazi <tomascohen@theke.io>
Date: Mon, 5 Jun 2023 13:50:00 -0300
Subject: [PATCH] Bug 21983: DB update

This patch:
- Adds a illrequests.deleted_biblio_id column
- Adjusts existing db_rev so people upgrading have the biblio_id value
  moved to the also created column, before attempting to add the FK
  constraint on the biblios table.
- Adds a new db_rev for those who unfortunately already updated, it
  should be idempotent.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
(cherry picked from commit f1346c20257038e523be0405e9d84fe762d502db)
---
 .../mysql/atomicupdate/bug_21983_ill_fk.pl    | 40 +++++++++++++++++++
 installer/data/mysql/db_revs/221105004.pl     | 20 ++++++++++
 installer/data/mysql/kohastructure.sql        |  1 +
 3 files changed, 61 insertions(+)
 create mode 100755 installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl

diff --git a/installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl b/installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl
new file mode 100755
index 0000000000..2453185c68
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl
@@ -0,0 +1,40 @@
+use Modern::Perl;
+
+return {
+    bug_number  => "21983",
+    description => "Deleted biblio handling on ILL",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+
+        unless ( column_exists( 'illrequests', 'deleted_biblio_id' ) ) {
+            $dbh->do(q{
+                ALTER TABLE illrequests
+                    ADD COLUMN `deleted_biblio_id` int(11) DEFAULT NULL COMMENT 'Deleted bib linked to request'
+                        AFTER `biblio_id`;
+            });
+            say $out "Added column 'illrequests.deleted_biblio_id'";
+        }
+
+        # Move deleted biblio_id to deleted_biblio_id before setting the FK
+        $dbh->do(q{
+            UPDATE    illrequests
+            LEFT JOIN biblio
+            ON illrequests.biblio_id=biblio.biblionumber
+            SET illrequests.biblio_id=NULL,
+                illrequests.deleted_biblio_id=illrequests.biblio_id
+            WHERE     biblio.biblionumber IS NULL
+                  AND illrequests.biblio_id IS NOT NULL
+        });
+
+        unless ( foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) ) {
+            $dbh->do(q{
+                ALTER TABLE illrequests
+                    ADD KEY `illrequests_bibfk` (`biblio_id`),
+                    ADD FOREIGN KEY illrequests_bibfk (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE;
+            });
+
+            say $out "Added foreign key constraint 'illrequests.illrequests_bibfk'";
+        }
+    },
+};
diff --git a/installer/data/mysql/db_revs/221105004.pl b/installer/data/mysql/db_revs/221105004.pl
index 0f5148b968..7b840e51da 100755
--- a/installer/data/mysql/db_revs/221105004.pl
+++ b/installer/data/mysql/db_revs/221105004.pl
@@ -7,6 +7,26 @@ return {
         my ($args) = @_;
         my ($dbh, $out) = @$args{qw(dbh out)};
 
+        unless ( column_exists( 'illrequests', 'deleted_biblio_id' ) ) {
+            $dbh->do(q{
+                ALTER TABLE illrequests
+                    ADD COLUMN `deleted_biblio_id` int(11) DEFAULT NULL COMMENT 'Deleted bib linked to request'
+                        AFTER `biblio_id`;
+            });
+            say $out "Added column 'illrequests.deleted_biblio_id'";
+        }
+
+        # Move deleted biblio_id to deleted_biblio_id before setting the FK
+        $dbh->do(q{
+            UPDATE    illrequests
+            LEFT JOIN biblio
+            ON illrequests.biblio_id=biblio.biblionumber
+            SET illrequests.biblio_id=NULL,
+                illrequests.deleted_biblio_id=illrequests.biblio_id
+            WHERE     biblio.biblionumber IS NULL
+                  AND illrequests.biblio_id IS NOT NULL
+        });
+
         unless ( foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) ) {
             $dbh->do(q{
                 ALTER TABLE illrequests
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index e87116644e..b2c23bfdfc 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -3247,6 +3247,7 @@ CREATE TABLE `illrequests` (
   `illrequest_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ILL request number',
   `borrowernumber` int(11) DEFAULT NULL COMMENT 'Patron associated with request',
   `biblio_id` int(11) DEFAULT NULL COMMENT 'Potential bib linked to request',
+  `deleted_biblio_id` int(11) DEFAULT NULL COMMENT 'Deleted bib linked to request',
   `due_date` datetime DEFAULT NULL COMMENT 'Custom date due specified by backend, leave NULL for default date_due calculation',
   `branchcode` varchar(50) NOT NULL COMMENT 'The branch associated with the request',
   `status` varchar(50) DEFAULT NULL COMMENT 'Current Koha status of request',
-- 
2.39.5