From 8ac304c9f54a2e879d44eb029942eb5458563116 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 29 Nov 2019 16:08:16 +0100 Subject: [PATCH] Bug 24152: Add the ability to purge pseudonymized tables anonymized_* tables have been added by bug 24151, this patch adds the usual way to purge data from them. The cleanup_database.pl script has been adjusted to take new parameters that will help to delete pseudonymized data. Test plan: Call the scrip with the new parameter to remove pseudonymized data * --pseudo-patrons DAYS will remove entries from anonymized_borrowers older than DAYS day * --pseudo-patrons can be used without the parameter DAYS but with -- pseudo-patrons-from and/or --pseudo-patrons-to instead, to provide a range of date Same for --pseudo-transactions, see the POD of the script. You can use the patch from bug 24153 to make the tests easier, data will not be deleted if the new --confirm flag is not passed. Sponsored-by: Association KohaLa - https://koha-fr.org/ --- Koha/Objects.pm | 36 +++++++++++++-- misc/cronjobs/cleanup_database.pl | 45 ++++++++++++++++++- t/db_dependent/Koha/Objects.t | 95 ++++++++++++++++++++++++++++++++++++++- 3 files changed, 169 insertions(+), 7 deletions(-) diff --git a/Koha/Objects.pm b/Koha/Objects.pm index c72f87df09..e292fdb8ea 100644 --- a/Koha/Objects.pm +++ b/Koha/Objects.pm @@ -23,6 +23,7 @@ use Carp; use List::MoreUtils qw( none ); use Koha::Database; +use Koha::DateUtils qw( dt_from_string ); =head1 NAME @@ -170,15 +171,42 @@ sub search_related { } } +=head3 filter_by_last_update + +my $filtered_objects = $objects->filter_by_last_update + +days exclusive +from inclusive +to inclusive + +=cut + sub filter_by_last_update { my ( $self, $params ) = @_; my $timestamp_column_name = $params->{timestamp_column_name} || 'timestamp'; + my $conditions; + Koha::Exceptions::MissingParameter->throw( + "Missing mandatory parameter: days or from or to") + unless exists $params->{days} + or exists $params->{from} + or exists $params->{to}; + + my $dtf = Koha::Database->new->schema->storage->datetime_parser; + if ( exists $params->{days} ) { + $conditions->{'<'} = $dtf->format_date( dt_from_string->subtract( days => $params->{days} ) ); + } + if ( exists $params->{from} ) { + my $from = ref($params->{from}) ? $params->{from} : dt_from_string($params->{from}); + $conditions->{'>='} = $dtf->format_date( $from ); + } + if ( exists $params->{to} ) { + my $to = ref($params->{to}) ? $params->{to} : dt_to_string($params->{to}); + $conditions->{'<='} = $dtf->format_date( $to ); + } + return $self->_resultset->search( { - $timestamp_column_name => { - '<' => - [ \'DATE_SUB(CURDATE(), INTERVAL ? DAY)', $params->{days} ] - } + $timestamp_column_name => $conditions } ); } diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index f33bf832c7..718c548acc 100755 --- a/misc/cronjobs/cleanup_database.pl +++ b/misc/cronjobs/cleanup_database.pl @@ -96,6 +96,13 @@ Usage: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueu --old-issues DAYS Purge checkouts (old_issues) returned more than DAYS days ago. --old-reserves DAYS Purge reserves (old_reserves) more than DAYS old. --transfers DAYS Purge transfers completed more than DAYS day ago. + --pseudo-patrons DAYS Purge the pseudonymized patrons that have been originally enrolled more than DAYS days ago + DAYS is optional and can be replaced by + --pseudo-patrons-from YYYY-MM-DD (iso formatted date, or as the 'dateformat' system preference is set + and/or --pseudo-patrons-to YYYY-MM-DD + --pseudo-transactions DAYS Purge the pseudonymized transactions that have been originally created more than DAYS days ago + DAYS is optional and can be replaced by: + --pseudo-transactions-from YYYY-MM-DD and/or --pseudo-patrons-to YYYY-MM-DD USAGE exit $_[0]; } @@ -128,6 +135,8 @@ my $pDeletedPatrons; my $pOldIssues; my $pOldReserves; my $pTransfers; +my ( $pPseudoPatrons, $pPseudoPatronsFrom, $pPseudoPatronsTo ); +my ( $pPseudoTransactions, $pPseudoTransactionsFrom, $pPseudoTransactionsTo ); GetOptions( 'h|help' => \$help, @@ -157,7 +166,13 @@ GetOptions( 'deleted-patrons:i' => \$pDeletedPatrons, 'old-issues:i' => \$pOldIssues, 'old-reserves:i' => \$pOldReserves, - 'transfers:i' => \$pTransfers, + 'transfers:i' => \$pTransfers, + 'pseudo-patrons:i' => \$pPseudoPatrons, + 'pseudo-patrons-from:s' => \$pPseudoPatronsFrom, + 'pseudo-patrons-to:s' => \$pPseudoPatronsTo, + 'pseudo-transactions:i' => \$pPseudoTransactions, + 'pseudo-transactions-from:s' => \$pPseudoTransactionsFrom, + 'pseudo-transactions-to:s' => \$pPseudoTransactionsTo, ) || usage(1); # Use default values @@ -198,6 +213,8 @@ unless ( $sessions || $pOldIssues || $pOldReserves || $pTransfers + || defined $pPseudoPatrons + || defined $pPseudoTransactions ) { print "You did not specify any cleanup work for the script to do.\n\n"; usage(1); @@ -430,6 +447,32 @@ if ($pTransfers) { print "Done with purging transfers.\n" if $verbose; } +if (defined $pPseudoPatrons) { + print "Purging pseudonymized patrons older than $pPseudoPatrons days.\n" if $verbose; + my $anonymized_patrons = Koha::Anonymized::Patrons->filter_by_last_update( + { + timestamp_column_name => 'dateenrolled', + ( $pPseudoPatrons ? ( days => $pPseudoPatrons ) : () ), + ( $pPseudoPatronsFrom ? ( from => $pPseudoPatronsFrom ) : () ), + ( $pPseudoPatronsTo ? ( to => $pPseudoPatronsTo ) : () ), + } + )->delete; + print "Done with purging pseudonymized patrons.\n" if $verbose; +} + +if (defined $pPseudoTransactions) { + print "Purging pseudonymized transactions older than $pPseudoTransactions days.\n" if $verbose; + Koha::Anonymized::Transactions->filter_by_last_update( + { + timestamp_column_name => 'datetime', + ( $pPseudoTransactions ? ( days => $pPseudoTransactions ) : () ), + ( $pPseudoTransactionsFrom ? ( from => $pPseudoTransactionsFrom ) : () ), + ( $pPseudoTransactionsTo ? ( to => $pPseudoTransactionsTo ) : () ), + } + )->delete; + print "Done with purging pseudonymized transactions.\n" if $verbose; +} + exit(0); sub RemoveOldSessions { diff --git a/t/db_dependent/Koha/Objects.t b/t/db_dependent/Koha/Objects.t index 592ab40866..2cdc088549 100644 --- a/t/db_dependent/Koha/Objects.t +++ b/t/db_dependent/Koha/Objects.t @@ -1,6 +1,6 @@ #!/usr/bin/perl -# Copyright 2015 Koha Development team +# Copyright 2019 Koha Development team # # This file is part of Koha # @@ -19,7 +19,7 @@ use Modern::Perl; -use Test::More tests => 17; +use Test::More tests => 18; use Test::Exception; use Test::Warn; @@ -30,8 +30,10 @@ use Koha::Patron::Category; use Koha::Patron::Categories; use Koha::Patrons; use Koha::Database; +use Koha::DateUtils qw( dt_from_string ); use t::lib::TestBuilder; +use t::lib::Mocks; use Try::Tiny; @@ -330,3 +332,92 @@ subtest "TO_JSON() tests" => sub { $schema->storage->txn_rollback; }; + +subtest "filter_by_last_update" => sub { + + $schema->storage->txn_begin; + + my $now = dt_from_string->truncate( to => 'day' ); + my @borrowernumbers; + # Building 6 patrons that have been created today, yesterday, ... 1 per day + for my $i ( 0 .. 5 ) { + push @borrowernumbers, + $builder->build_object( + { + class => 'Koha::Patrons', + value => { updated_on => $now->clone->subtract( days => $i ) } + } + )->borrowernumber; + } + + my $patrons = Koha::Patrons->search( + { borrowernumber => { -in => \@borrowernumbers } } ); + + try { + $patrons->filter_by_last_update( { timestamp_column_name => 'updated_on' } ) + ->count; + } + catch { + ok( + $_->isa('Koha::Exceptions::MissingParameter'), + 'Should raise an exception if no parameter given' + ); + }; + + my $count = $patrons->filter_by_last_update( + { timestamp_column_name => 'updated_on', days => 2 } )->count; + is( $count, 3, '3 patrons have been updated before the last 2 days (exclusive)' ); + + $count = $patrons->filter_by_last_update( + { timestamp_column_name => 'updated_on', days => 1 } )->count; + is( $count, 4, '4 patrons have been updated before yesterday (exclusive)' ); + + $count = $patrons->filter_by_last_update( + { timestamp_column_name => 'updated_on', days => 0 } )->count; + is( $count, 5, '5 patrons have been updated before today (exclusive)' ); + + $count = $patrons->filter_by_last_update( + { timestamp_column_name => 'updated_on', from => $now } )->count; + is( $count, 1, '1 patron has been updated "from today" (inclusive)' ); + + $count = $patrons->filter_by_last_update( + { timestamp_column_name => 'updated_on', to => $now } )->count; + is( $count, 6, '6 patrons have been updated "to today" (inclusive)' ); + + $count = $patrons->filter_by_last_update( + { + timestamp_column_name => 'updated_on', + from => $now->clone->subtract( days => 4 ), + to => $now->clone->subtract( days => 2 ) + } + )->count; + is( $count, 3, '3 patrons have been updated between D-4 and D-2' ); + + t::lib::Mocks::mock_preference( 'dateformat', 'metric' ); + try { + $count = $patrons->filter_by_last_update( + { timestamp_column_name => 'updated_on', from => '1970-12-31' } ) + ->count; + } + catch { + ok( + $_->isa( + 'No exception raised, from and to parameters can take an iso formatted date' + ) + ); + }; + try { + $count = $patrons->filter_by_last_update( + { timestamp_column_name => 'updated_on', from => '31/12/1970' } ) + ->count; + } + catch { + ok( + $_->isa( + 'No exception raised, from and to parameters can take an metric formatted date (depending on dateformat syspref)' + ) + ); + }; + + $schema->storage->txn_rollback; +}; -- 2.11.0