From 83ce6c025432de2392972245bde4eaf7dadfce42 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 21 Mar 2024 10:16:28 +0000 Subject: [PATCH] Bug 36120: Ensure pickup_library_id is NOT NULL This patch updates the database schema to ensure bookings.pickup_library_id is NOT NULL Signed-off-by: Nick Clemens Signed-off-by: Katrin Fischer --- api/v1/swagger/definitions/booking.yaml | 8 +++--- .../data/mysql/atomicupdate/bug_36120.pl | 25 ++++++++++++++----- installer/data/mysql/kohastructure.sql | 2 +- 3 files changed, 24 insertions(+), 11 deletions(-) diff --git a/api/v1/swagger/definitions/booking.yaml b/api/v1/swagger/definitions/booking.yaml index aed1e31b3f..5a7b8cc77b 100644 --- a/api/v1/swagger/definitions/booking.yaml +++ b/api/v1/swagger/definitions/booking.yaml @@ -18,12 +18,12 @@ properties: description: Internal item identifier type: - integer - - "null" + - 'null' item: description: Embedable item representation type: - object - - "null" + - 'null' patron_id: description: Internal patron identifier type: integer @@ -31,7 +31,7 @@ properties: description: Embedable patron representation type: - object - - "null" + - 'null' pickup_library_id: description: Internal pickup_library identifier type: string @@ -39,7 +39,7 @@ properties: description: Embedable pickup_library representation type: - object - - "null" + - 'null' start_date: description: Start date and time of this booking format: date-time diff --git a/installer/data/mysql/atomicupdate/bug_36120.pl b/installer/data/mysql/atomicupdate/bug_36120.pl index 02d8186363..9ed97b8ec4 100755 --- a/installer/data/mysql/atomicupdate/bug_36120.pl +++ b/installer/data/mysql/atomicupdate/bug_36120.pl @@ -8,17 +8,30 @@ return { my ( $dbh, $out ) = @$args{qw(dbh out)}; unless ( column_exists( 'bookings', 'pickup_library_id' ) ) { - $dbh->do(q{ + $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'"; + say $out "Added column 'bookings.pickup_library_id'"; - $dbh->do( - q{UPDATE bookings JOIN items ON bookings.item_id = items.itemnumber SET bookings.pickup_library_id = items.homebranch } - ); + $dbh->do( + q{UPDATE bookings JOIN items ON bookings.item_id = items.itemnumber SET bookings.pickup_library_id = items.homebranch } + ); + + say $out "Set existing bookings pickup location to item homebranch"; + + $dbh->do( + q{ + ALTER TABLE bookings + MODIFY pickup_library_id varchar(10) NOT NULL COMMENT 'Identifier for booking pickup library' + } + ); + + say $out "Set pickup_library_id to NOT NULL"; } }, }; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 6922c9cff7..54f739f6c9 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1225,7 +1225,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', + `pickup_library_id` varchar(10) NOT 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`), -- 2.39.5