From b877916a1e4fccbdb8daef19263804855508da41 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 18 Dec 2019 14:35:50 +0000 Subject: [PATCH] Bug 22273: Database revision for table article_requests Column created_on should not be updated. It is filled once by the store method of Koha::ArticleRequest. Column updated_on should be a timestamp, updated by the database. Note: Although higher versions of MySQL and MariaDB support two timestamps (NOT NULL) in one table, I kept on the safe side by allowing NULL on the created_on column. @RM: Don't forget to run DBIx update. Test plan: Run new install or upgrade. Signed-off-by: Bernardo Gonzalez Kriegel Tested running update. Signed-off-by: Katrin Fischer --- installer/data/mysql/atomicupdate/bug22273.perl | 8 ++++++++ installer/data/mysql/kohastructure.sql | 4 ++-- 2 files changed, 10 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug22273.perl diff --git a/installer/data/mysql/atomicupdate/bug22273.perl b/installer/data/mysql/atomicupdate/bug22273.perl new file mode 100644 index 0000000000..82d114b7b1 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug22273.perl @@ -0,0 +1,8 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + $dbh->do( q| +ALTER TABLE article_requests MODIFY COLUMN created_on timestamp NULL, MODIFY COLUMN updated_on timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() + |); + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 22273: Column article_requests.created_on should not be updated)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 8ec1791c66..388cf828aa 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3944,8 +3944,8 @@ CREATE TABLE `article_requests` ( `patron_notes` MEDIUMTEXT, `status` enum('PENDING','PROCESSING','COMPLETED','CANCELED') NOT NULL DEFAULT 'PENDING', `notes` MEDIUMTEXT, - `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `updated_on` timestamp NULL DEFAULT NULL, + `created_on` timestamp NULL DEFAULT NULL, -- Be careful with two timestamps in one table not allowing NULL + `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `borrowernumber` (`borrowernumber`), KEY `biblionumber` (`biblionumber`), -- 2.11.0