Bugzilla – Attachment 171977 Details for
Bug 31143
We should attempt to fix/identify all cases where '0000-00-00' may still remain in the database
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 31143: Fix/identify all cases where '0000-00-00' may still remain in the database
Bug-31143-Fixidentify-all-cases-where-0000-00-00-m.patch (text/plain), 5.63 KB, created by
Shi Yao Wang
on 2024-09-26 14:24:15 UTC
(
hide
)
Description:
Bug 31143: Fix/identify all cases where '0000-00-00' may still remain in the database
Filename:
MIME Type:
Creator:
Shi Yao Wang
Created:
2024-09-26 14:24:15 UTC
Size:
5.63 KB
patch
obsolete
>From abd1635ff0becb69bd5221d27a336e7e57578274 Mon Sep 17 00:00:00 2001 >From: wainuiwitikapark <wainuiwitikapark@catalyst.net.nz> >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 <shi-yao.wang@inlibro.com> >--- > 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 <http://www.gnu.org/licenses>. >+ >+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.34.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 31143
:
154299
|
154399
|
155964
|
168636
|
168770
|
168775
|
168776
|
170086
|
170697
|
170698
|
171812
|
171813
|
171814
|
171815
|
171816
|
171817
|
171918
|
171976
|
171977
|
172138
|
172139
|
172140
|
172141
|
172143
|
172144
|
172145
|
172146
|
173900