From 230c894b5ff15b2325f04b49089409763e1c7f02 Mon Sep 17 00:00:00 2001
From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Date: Wed, 15 Sep 2021 16:29:43 +0100
Subject: [PATCH] Bug 29002: Add bookings table to kohastructure

---
 .../data/mysql/atomicupdate/bug_29002.pl      | 29 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        | 24 +++++++++++++++
 2 files changed, 53 insertions(+)
 create mode 100644 installer/data/mysql/atomicupdate/bug_29002.pl

diff --git a/installer/data/mysql/atomicupdate/bug_29002.pl b/installer/data/mysql/atomicupdate/bug_29002.pl
new file mode 100644
index 0000000000..456e9349bd
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_29002.pl
@@ -0,0 +1,29 @@
+use Modern::Perl;
+
+return {
+    bug_number => "29002",
+    description => "Add bookings table",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+        if( !TableExists( 'bookings' ) ) {
+            $dbh->do(q{
+                CREATE TABLE `bookings` (
+                  `booking_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+                  `patron_id` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the borrowers table defining which patron this booking is for',
+                  `biblio_id` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the biblio table defining which bib record this booking is on',
+                  `item_id` int(11) DEFAULT NULL COMMENT 'foreign key from the items table defining the specific item the patron has placed a booking for',
+                  `start_date` datetime DEFAULT NULL COMMENT 'the start date of the booking',
+                  `end_date` datetime DEFAULT NULL COMMENT 'the end date of the booking',
+                PRIMARY KEY (`booking_id`),
+                KEY `patron_id` (`patron_id`),
+                KEY `biblio_id` (`biblio_id`),
+                KEY `item_id` (`item_id`),
+                CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+                CONSTRAINT `bookings_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+                CONSTRAINT `bookings_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+            });
+        }
+    },
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 9ae8fd76e3..3a252ebae1 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1154,6 +1154,30 @@ CREATE TABLE `biblioitems` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
+--
+-- Table structure for table `bookings`
+--
+
+DROP TABLE IF EXISTS `bookings`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `bookings` (
+  `booking_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+  `patron_id` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the borrowers table defining which patron this booking is for',
+  `biblio_id` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the biblio table defining which bib record this booking is on',
+  `item_id` int(11) DEFAULT NULL COMMENT 'foreign key from the items table defining the specific item the patron has placed a booking for',
+  `start_date` datetime DEFAULT NULL COMMENT 'the start date of the booking',
+  `end_date` datetime DEFAULT NULL COMMENT 'the end date of the booking',
+  PRIMARY KEY (`booking_id`),
+  KEY `patron_id` (`patron_id`),
+  KEY `biblio_id` (`biblio_id`),
+  KEY `item_id` (`item_id`),
+  CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `bookings_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `bookings_ibfk_3` FOREIGN KEY (`item_id`) 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 */;
+
 --
 -- Table structure for table `borrower_attribute_types`
 --
-- 
2.40.1