Bugzilla – Attachment 133342 Details for
Bug 27253
borrowers.updated_on cannot be null on fresh install, but can be null with upgrade
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 27253: Fix definition of updated_on in borrowers and deletedborrowers
Bug-27253-Fix-definition-of-updatedon-in-borrowers.patch (text/plain), 4.25 KB, created by
Marcel de Rooy
on 2022-04-15 07:39:56 UTC
(
hide
)
Description:
Bug 27253: Fix definition of updated_on in borrowers and deletedborrowers
Filename:
MIME Type:
Creator:
Marcel de Rooy
Created:
2022-04-15 07:39:56 UTC
Size:
4.25 KB
patch
obsolete
>From 249e6222994318415ce8aae77a7c6409eee695b9 Mon Sep 17 00:00:00 2001 >From: Julian Maurice <julian.maurice@biblibre.com> >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 = > <borrowernumber of X> > UPDATE deletedborrowers SET updated_on = NULL WHERE borrowernumber = > <borrowernumber of Y> >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 <oleonard@myacpl.org> > >Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >--- > .../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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 27253
:
132819
|
132934
| 133342 |
133735