From 9d93bd93b3f265f1f0833f32e4f4780dfffab602 Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Mon, 14 Mar 2022 14:21:14 +0000 Subject: [PATCH] Bug 23486: Set lastseen to default to current timestamp Generally, a patron is not registered without some action from the patron Defaulting the lastseen upon creation seems a reasonable way to set the initial value. This patch also makes the column NOT NULL to avoid any future data issues. To handle any currently NULL, we either set them to dateenrolled, or updated_on - the latter is already NOT NULL so is the bast fallback we have (assuming patron was also seen when they were updated) TO test: Apply patches Update database Create a patron Check their lastseen date --- .../bug_23486_set_lastseen_default.pl | 19 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 2 +- 2 files changed, 20 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_23486_set_lastseen_default.pl diff --git a/installer/data/mysql/atomicupdate/bug_23486_set_lastseen_default.pl b/installer/data/mysql/atomicupdate/bug_23486_set_lastseen_default.pl new file mode 100755 index 0000000000..9b799d8f23 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_23486_set_lastseen_default.pl @@ -0,0 +1,19 @@ +use Modern::Perl; + +return { + bug_number => "23486", + description => "Default lastseen to current timestamp", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + $dbh->do(q{ + UPDATE borrowers SET lastseen = IFNULL(dateenrolled,updated_on) WHERE lastseen IS NULL + }); + say $out "Update any borrowers with a NULL lastseen to their date enrolled, or their updated_on date if date enrolled is null"; + $dbh->do(q{ + ALTER TABLE borrowers CHANGE lastseen + lastseen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'last time a patron has been seen (connected at the OPAC or staff interface)' + }); + say $out "Update lastseen colum to default to current timestamp"; + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index cba889d5e0..f0e0a99bb9 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1472,7 +1472,7 @@ CREATE TABLE `borrowers` ( `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'controls if relatives can see this patron''s checkouts', `checkprevcheckout` varchar(7) NOT NULL DEFAULT 'inherit' COMMENT 'produce a warning for this patron if this item has previously been checked out to this patron if ''yes'', not if ''no'', defer to category setting if ''inherit''.', `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)', - `lastseen` datetime DEFAULT NULL COMMENT 'last time a patron has been seen (connected at the OPAC or staff interface)', + `lastseen` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'last time a patron has been seen (connected at the OPAC or staff interface)', `lang` varchar(25) NOT NULL DEFAULT 'default' COMMENT 'lang to use to send notices to this patron', `login_attempts` int(4) NOT NULL DEFAULT 0 COMMENT 'number of failed login attemps', `overdrive_auth_token` mediumtext DEFAULT NULL COMMENT 'persist OverDrive auth token', -- 2.30.2