From a4f1ddcfb27fb30197bd99d667a68010d5afc108 Mon Sep 17 00:00:00 2001
From: Nicolas Legrand <nicolas.legrand@bulac.fr>
Date: Mon, 13 Jan 2020 17:55:46 +0100
Subject: [PATCH] Bug 24412: kohastructure and atomic update
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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

https://bugs.koha-community.org/show_bug.cgi?id=26779
Signed-off-by: Séverine QUEUNE <severine.queune@bulac.fr>
---
 .../bug_24412_Attach_waiting_reserve_to_desk.perl        | 16 ++++++++++++++++
 installer/data/mysql/kohastructure.sql                   |  6 +++++-
 2 files changed, 21 insertions(+), 1 deletion(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_24412_Attach_waiting_reserve_to_desk.perl

diff --git a/installer/data/mysql/atomicupdate/bug_24412_Attach_waiting_reserve_to_desk.perl b/installer/data/mysql/atomicupdate/bug_24412_Attach_waiting_reserve_to_desk.perl
new file mode 100644
index 0000000..c743eab
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_24412_Attach_waiting_reserve_to_desk.perl
@@ -0,0 +1,16 @@
+$DBversion = 'XXX';  # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+    # you can use $dbh here like:
+    $dbh->do(q{
+ALTER TABLE reserves ADD COLUMN desk_id INT(11) DEFAULT NULL AFTER branchcode,
+  ADD KEY desk_id (`desk_id`),
+  ADD CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE CASCADE ON UPDATE CASCADE ;
+             });
+    $dbh->do(q{
+ALTER TABLE old_reserves ADD COLUMN desk_id INT(11) DEFAULT NULL AFTER branchcode,
+  ADD KEY `old_desk_id` (`desk_id`);
+             });
+
+    SetVersion( $DBversion );
+    print "Upgrade to $DBversion done (Bug XXXXX - Attach waiting reserve to desk)\n";
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index fc9953b..8229015 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1761,6 +1761,7 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
   `reservedate` date default NULL, -- the date the hold was placed
   `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
   `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at
+  `desk_id` int(11) default NULL, -- foreign key from the desks table defining which desk the patron should pick this hold up at
   `notificationdate` date default NULL, -- currently unused
   `reminderdate` date default NULL, -- currently unused
   `cancellationdate` date default NULL, -- the date this hold was cancelled
@@ -1784,12 +1785,14 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
   KEY `biblionumber` (`biblionumber`),
   KEY `itemnumber` (`itemnumber`),
   KEY `branchcode` (`branchcode`),
+  KEY `desk_id` (`desk_id`),
   KEY `itemtype` (`itemtype`),
   CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 --
@@ -1803,6 +1806,7 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b
   `reservedate` date default NULL, -- the date the hold was places
   `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
   `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at
+  `desk_id` int(11) default NULL, -- foreign key from the desks table defining which desk the patron should pick this hold up at
   `notificationdate` date default NULL, -- currently unused
   `reminderdate` date default NULL, -- currently unused
   `cancellationdate` date default NULL, -- the date this hold was cancelled
-- 
2.1.4