From d4bfae09778ef9f251b5a55b9446a50d66becd92 Mon Sep 17 00:00:00 2001 From: wainuiwitikapark Date: Tue, 24 Sep 2024 04:53:05 +0000 Subject: [PATCH] Bug 31143: Fix/identify all cases where '0000-00-00' may still remain in the database This patch identifies date fields in the database (timestamp, datetime, date) that are 0000-00-00 and sets them to NULL Test plan - Examples of setting up some 0000-00-00 date fields: e.g. Borrowers table update borrowers set dateexpiry="0000-00-00" where borrowernumber=49; e.g. Items table update items set datelastseen="0000-00-00" where itemnumber=12; To identify the cases: 1) Set up some 0000-00-00 date fields 2) Run the misc/maintenance/search_for_data_inconsistencies.pl script 3) Run fix_invalid_dates.pl -v To fix the cases: 1) Run fix_invalid_dates.pl -c -v 2) Notice the value has changed from 0000-00-00 to NULL e.g. Borrowers table select dateexpiry from borrowers where borrowernumber=49; e.g. Items table select datelastseen from items where itemnumber=12; Signed-off-by: Shi Yao Wang Signed-off-by: Nick Clemens Signed-off-by: Katrin Fischer --- misc/cronjobs/fix_invalid_dates.pl | 66 +++++++++++++++++++ .../search_for_data_inconsistencies.pl | 32 +++++++++ 2 files changed, 98 insertions(+) create mode 100755 misc/cronjobs/fix_invalid_dates.pl diff --git a/misc/cronjobs/fix_invalid_dates.pl b/misc/cronjobs/fix_invalid_dates.pl new file mode 100755 index 0000000000..a7f43b671d --- /dev/null +++ b/misc/cronjobs/fix_invalid_dates.pl @@ -0,0 +1,66 @@ +#!/usr/bin/perl + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use C4::Context; +use C4::Log qw(cronlogaction); +use Getopt::Long qw( GetOptions ); +use Pod::Usage qw( pod2usage ); +use Koha::Logger; +use Koha::DateUtils qw( dt_from_string ); +use Koha::Script -cron; +use Data::Dumper; +use Koha::Database; + +my $verbose = 0; +my $doit = 0; + +GetOptions( + 'v|verbose' => \$verbose, + 'c|confirm' => \$doit, +); + +my $schema = Koha::Database->new->schema; +# Loop over all the DBIx::Class classes +for my $class ( sort values %{$schema->{class_mappings}} ) { + # Retrieve the resultset so we can access the columns info + my $rs = $schema->resultset($class); + my $columns = $rs->result_source->columns_info; + + # Loop over the columns + while ( my ( $column, $info ) = each %$columns ) { + # Next if data type is not date/datetime/timestamp + my $data_type = $info->{data_type}; + next unless grep { $data_type =~ m{^$_$} } qw( timestamp datetime date ); + + # Count the invalid dates + my $invalid_dates = $rs->search({ $column => '0000-00-00' })->count; + + next unless $invalid_dates; + + if ($verbose) { + say sprintf "Column %s.%s contains %s invalid dates", $rs->result_source->name, $column, $invalid_dates; + } + + if ($doit) { + $rs->search({ $column => '0000-00-00' })->update({ $column => undef }); + say sprintf "Column %s.%s contains %s invalid dates that have been fixed", $rs->result_source->name, $column, $invalid_dates; + } + + } +} diff --git a/misc/maintenance/search_for_data_inconsistencies.pl b/misc/maintenance/search_for_data_inconsistencies.pl index 6719cecedc..ed29e4134f 100755 --- a/misc/maintenance/search_for_data_inconsistencies.pl +++ b/misc/maintenance/search_for_data_inconsistencies.pl @@ -326,6 +326,37 @@ use C4::Biblio qw( GetMarcFromKohaField ); } } +{ + use Koha::Database; + my $schema = Koha::Database->new->schema; + + # Loop over all the DBIx::Class classes + for my $class ( sort values %{$schema->{class_mappings}} ) { + # Retrieve the resultset so we can access the columns info + my $rs = $schema->resultset($class); + my $columns = $rs->result_source->columns_info; + + # Loop over the columns + while ( my ( $column, $info ) = each %$columns ) { + # Next if data type is not date/datetime/timestamp + my $data_type = $info->{data_type}; + next unless grep { $data_type =~ m{^$_$} } qw( timestamp datetime date ); + + # Count the invalid dates + my $invalid_dates = $rs->search({ $column => '0000-00-00' })->count; + + next unless $invalid_dates; + + new_section("Column " . $rs->result_source->name . "." . $column . " contains $invalid_dates invalid dates"); + + if ($invalid_dates > 0) { + new_hint("You may change the dates with script: misc/cronjobs/fix_invalid_dates.pl (-c -v)"); + } + + } + } +} + { my @loop_borrowers_relationships; my @guarantor_ids = Koha::Patron::Relationships->_resultset->get_column('guarantor_id')->all(); @@ -450,5 +481,6 @@ Catch data inconsistencies in Koha database * Item types defined in items or biblioitems must be defined in the itemtypes table * Invalid MARCXML in bibliographic records * Patrons with invalid category types due to lower and upper age limits +* Any date fields in the database (timestamp, datetime, date) set to 0000-00-00 =cut -- 2.39.5