From a57f543944ce21714056c166547779f6d2aef939 Mon Sep 17 00:00:00 2001
From: Aleisha Amohia <aleishaamohia@hotmail.com>
Date: Mon, 1 Mar 2021 15:25:13 +1300
Subject: [PATCH] Bug 15516: Database and installer updates

Signed-off-by: Michal Denar <black23@gmail.com>
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>

Signed-off-by: Michal Denar <black23@gmail.com>
---
 .../bug_15516_-_hold_group_table.pl           | 27 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        | 20 ++++++++++++--
 2 files changed, 45 insertions(+), 2 deletions(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl

diff --git a/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl
new file mode 100644
index 0000000000..e4690b5f8d
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl
@@ -0,0 +1,27 @@
+use Modern::Perl;
+
+return {
+    bug_number => "15516",
+    description => "Add new table hold_groups",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+
+        unless( TableExists( 'hold_groups' ) ) {
+            $dbh->do(q{CREATE TABLE hold_groups (
+                hold_group_id int unsigned NOT NULL AUTO_INCREMENT,
+                PRIMARY KEY (hold_group_id)
+            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci});
+        }
+
+        unless( column_exists( 'reserves', 'hold_group_id' ) ) {
+            $dbh->do(q{ALTER TABLE reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL AFTER non_priority});
+            $dbh->do(q{ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_7 FOREIGN KEY (hold_group_id) REFERENCES hold_groups (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE});
+        }
+
+        unless( column_exists( 'old_reserves', 'hold_group_id') ) {
+            $dbh->do(q{ALTER TABLE old_reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL AFTER non_priority});
+            $dbh->do(q{ALTER TABLE old_reserves ADD CONSTRAINT old_reserves_ibfk_5 FOREIGN KEY (hold_group_id) REFERENCES hold_groups (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE});
+        }
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 3f524aec6a..5130679e3f 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -4072,6 +4072,18 @@ CREATE TABLE `old_issues` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
+--
+-- Table structure for table `hold_groups`
+--
+DROP TABLE IF EXISTS `hold_groups`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE hold_groups (
+    hold_group_id int unsigned NOT NULL AUTO_INCREMENT,
+    PRIMARY KEY (hold_group_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
 --
 -- Table structure for table `old_reserves`
 --
@@ -4104,6 +4116,7 @@ CREATE TABLE `old_reserves` (
   `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting',
   `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level',
   `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold',
+  `hold_group_id` int unsigned NULL DEFAULT NULL COMMENT 'The id of a group of titles reservations fulfilled when one title is picked',
   PRIMARY KEY (`reserve_id`),
   KEY `old_reserves_borrowernumber` (`borrowernumber`),
   KEY `old_reserves_biblionumber` (`biblionumber`),
@@ -4113,7 +4126,8 @@ CREATE TABLE `old_reserves` (
   CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
   CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL,
   CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
-  CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL
+  CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL,
+  CONSTRAINT `old_reserves_ibfk_5` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_groups` (`hold_group_id`) ON DELETE SET NULL ON UPDATE SET NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
@@ -4550,6 +4564,7 @@ CREATE TABLE `reserves` (
   `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting',
   `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level',
   `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold',
+  `hold_group_id` int unsigned NULL DEFAULT NULL COMMENT 'The id of a group of titles reservations fulfilled when one title is picked',
   PRIMARY KEY (`reserve_id`),
   KEY `priorityfoundidx` (`priority`,`found`),
   KEY `borrowernumber` (`borrowernumber`),
@@ -4563,7 +4578,8 @@ CREATE TABLE `reserves` (
   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_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE
+  CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE,
+  CONSTRAINT `reserves_ibfk_7` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_groups` (`hold_group_id`) ON DELETE SET NULL ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
-- 
2.30.2