From 5298ace329a86a824704ddc1318c1c9fa0251108 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 7 Mar 2024 17:06:52 +0000 Subject: [PATCH] Bug 36120: Add pickup_library_id to bookings table Sponsored-by: Cuyahoga County Public Library Signed-off-by: Lisette Scheer Signed-off-by: Kristi Krueger --- .../data/mysql/atomicupdate/bug_36120.pl | 20 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 +++- 2 files changed, 23 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_36120.pl diff --git a/installer/data/mysql/atomicupdate/bug_36120.pl b/installer/data/mysql/atomicupdate/bug_36120.pl new file mode 100755 index 00000000000..fd66b2dae81 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_36120.pl @@ -0,0 +1,20 @@ +use Modern::Perl; + +return { + bug_number => "36120", + description => "Add pickup location to bookings", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + unless ( column_exists( 'bookings', 'pickup_library_id' ) ) { + $dbh->do(q{ + ALTER TABLE bookings + ADD COLUMN `pickup_library_id` varchar(10) DEFAULT NULL COMMENT 'Identifier for booking pickup library' AFTER `item_id`, + ADD CONSTRAINT `bookings_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE + }); + + say $out "Added column 'bookings.pickup_library_id'"; + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 16310f0f449..1181bfb2457 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1208,6 +1208,7 @@ CREATE TABLE `bookings` ( `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', + `pickup_library_id` varchar(10) DEFAULT NULL COMMENT 'Identifier for booking pickup library', `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`), @@ -1216,7 +1217,8 @@ CREATE TABLE `bookings` ( 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 + CONSTRAINT `bookings_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `bookings_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.44.0