From 8bf291df365510204ec1b03d7dc88a9056c47c9a Mon Sep 17 00:00:00 2001 From: Paul Derscheid Date: Mon, 25 Nov 2024 12:00:18 +0000 Subject: [PATCH] Bug 37592: (QA follow-up) Ensure consistent NOT NULL placement and make implicit behavior explicit - Explicitly set `NOT NULL` constraints for `creation_date` and `modification_date` in `kohastructure.sql` to clarify the implicit behavior of `DEFAULT CURRENT_TIMESTAMP`. - Adjusted the `modification_date` column definition in the atomic update file to place `NOT NULL` before `ON UPDATE` for consistency. --- installer/data/mysql/db_revs/240600029.pl | 2 +- installer/data/mysql/kohastructure.sql | 4 ++-- 2 files changed, 3 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/db_revs/240600029.pl b/installer/data/mysql/db_revs/240600029.pl index 760561001f..863f9bf293 100644 --- a/installer/data/mysql/db_revs/240600029.pl +++ b/installer/data/mysql/db_revs/240600029.pl @@ -28,7 +28,7 @@ return { ALTER TABLE bookings ADD COLUMN creation_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'the datetime for when a bookings was created' SQL my $modification_date_statement = <<~'SQL'; - ALTER TABLE bookings ADD COLUMN modification_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT 'the datetime for when a booking has been updated' + ALTER TABLE bookings ADD COLUMN modification_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'the datetime for when a booking has been updated' SQL if ( @{$existing_columns} == 0 ) { $dbh->do("$creation_date_statement AFTER `end_date`"); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index d368659571..f55c06ca35 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1223,8 +1223,8 @@ CREATE TABLE `bookings` ( `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', - `creation_date` timestamp DEFAULT current_timestamp() COMMENT 'the timestamp for when a booking was created', - `modification_date` timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'the timestamp for when a booking has been updated', + `creation_date` timestamp DEFAULT current_timestamp() NOT NULL COMMENT 'the timestamp for when a booking was created', + `modification_date` timestamp DEFAULT current_timestamp() NOT NULL ON UPDATE current_timestamp() COMMENT 'the timestamp for when a booking has been updated', `status` enum('new', 'cancelled', 'completed') NOT NULL DEFAULT 'new' COMMENT 'current status of the booking', `cancellation_reason` varchar(80) DEFAULT NULL COMMENT 'optional authorised value BOOKING_CANCELLATION', PRIMARY KEY (`booking_id`), -- 2.39.5