From 249e6222994318415ce8aae77a7c6409eee695b9 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Thu, 31 Mar 2022 15:38:07 +0200 Subject: [PATCH] Bug 27253: Fix definition of updated_on in borrowers and deletedborrowers Content-Type: text/plain; charset=utf-8 Test plan: 1. First you have to be in a state where updated_on is NULL-able. You can do that by either: a) do a fresh install of Koha 16.05 and update to master, or b) execute the following SQL queries: ALTER TABLE borrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' ALTER TABLE deletedborrowers MODIFY updated_on timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time of last change could be useful for synchronization with external systems (among others)' 2. Create two borrowers (let's name them X and Y) 3. Delete borrower Y 4. Set the column updated_on to NULL for both borrowers by executing the following SQL query: UPDATE borrowers SET updated_on = NULL WHERE borrowernumber = UPDATE deletedborrowers SET updated_on = NULL WHERE borrowernumber = 5. Apply patch and run updatedatabase 6. Verify that borrowers.updated_on and deletedborrowers.updated_on are not NULL-able. Verify that updated_on for X and Y have taken the value of dateenrolled. 7. Repeat step 2 to 6 but this time renew the patron and/or log in with its account in order to set the columns borrowers.date_renewed and borrowers.lastseen before executing updatedatabase borrowers.updated_on should take the greatest value among dateenrolled, date_renewed, and lastseen Signed-off-by: Owen Leonard Signed-off-by: Marcel de Rooy --- .../data/mysql/atomicupdate/bug-27253.pl | 46 +++++++++++++++++++ 1 file changed, 46 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug-27253.pl diff --git a/installer/data/mysql/atomicupdate/bug-27253.pl b/installer/data/mysql/atomicupdate/bug-27253.pl new file mode 100755 index 0000000000..d5c7d7136d --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug-27253.pl @@ -0,0 +1,46 @@ +use Modern::Perl; + +return { + bug_number => "27253", + description => "Fix definition of borrowers.updated_on and deletedborrowers.updated_on", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + + my $rv = $dbh->do(q{ + UPDATE borrowers + SET updated_on = GREATEST( + COALESCE(date_renewed, FROM_UNIXTIME(0)), + COALESCE(dateenrolled, FROM_UNIXTIME(0)), + COALESCE(lastseen, FROM_UNIXTIME(0)) + ) + WHERE updated_on IS NULL + }); + say $out sprintf('Updated all NULL values of borrowers.updated_on to GREATEST(date_renewed, dateenrolled, lastseen): %d rows updated', $rv); + + $rv = $dbh->do(q{ + UPDATE deletedborrowers + SET updated_on = GREATEST( + COALESCE(date_renewed, FROM_UNIXTIME(0)), + COALESCE(dateenrolled, FROM_UNIXTIME(0)), + COALESCE(lastseen, FROM_UNIXTIME(0)) + ) + WHERE updated_on IS NULL + }); + say $out sprintf('Updated all NULL values of borrowers.updated_on to GREATEST(date_renewed, dateenrolled, lastseen): %d rows updated', $rv); + + $dbh->do(q{ + ALTER TABLE borrowers + MODIFY updated_on timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() + COMMENT 'time of last change could be useful for synchronization with external systems (among others)' + }); + say $out 'Fixed definition of borrowers.updated_on'; + + $dbh->do(q{ + ALTER TABLE deletedborrowers + MODIFY updated_on timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() + COMMENT 'time of last change could be useful for synchronization with external systems (among others)' + }); + say $out 'Fixed definition of deletedborrowers.updated_on'; + }, +}; -- 2.20.1