From c41b83cfaba76edee847d8897a40c83bd16b57eb Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Wed, 15 Sep 2021 16:29:43 +0100 Subject: [PATCH] Bug 29002: Add bookings table This patch adds a new bookings table to store booking details. Test plan * Confirm that kohastructure and the atomicupdate match such that an install or upgrade result in the same table being present on the system. Sponsored-by: PTFS-Europe Signed-off-by: Martin Renvoize Signed-off-by: Janet McGowan Signed-off-by: Caroline Cyr La Rose Signed-off-by: Laurence Rault Signed-off-by: Kyle M Hall Signed-off-by: Tomas Cohen Arazi --- .../data/mysql/atomicupdate/bug_29002.pl | 29 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 24 +++++++++++++++ 2 files changed, 53 insertions(+) create mode 100755 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 100755 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 c51df73a48..1e7a663e5b 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1195,6 +1195,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.39.5