From 55d1139ad8676cc988f2e6c111aeaafaec9cc89b 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 Content-Type: text/plain; charset=utf-8 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-transactions DAYS will remove entries from pseudonymized_transactions older than DAYS day * --pseudo-transactions can be used without the parameter DAYS but with -- pseudo-transactions-from and/or --pseudo-transactions-to instead, to provide a range of date 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/ Signed-off-by: Signed-off-by: Sonia Bouis Signed-off-by: Marcel de Rooy --- Koha/Objects.pm | 36 +++++++++++++-- misc/cronjobs/cleanup_database.pl | 25 ++++++++++- t/db_dependent/Koha/Objects.t | 95 ++++++++++++++++++++++++++++++++++++++- 3 files changed, 148 insertions(+), 8 deletions(-) diff --git a/Koha/Objects.pm b/Koha/Objects.pm index b1877884a1..3ea4727048 100644 --- a/Koha/Objects.pm +++ b/Koha/Objects.pm @@ -25,6 +25,7 @@ use Class::Inspector; use Koha::Database; use Koha::Exceptions::Object; +use Koha::DateUtils qw( dt_from_string ); =head1 NAME @@ -252,15 +253,42 @@ sub update { return $self->_resultset->update($fields); } +=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_from_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 a86c07986e..c78a4027e0 100755 --- a/misc/cronjobs/cleanup_database.pl +++ b/misc/cronjobs/cleanup_database.pl @@ -49,7 +49,7 @@ use Koha::Old::Checkouts; use Koha::Old::Holds; use Koha::Old::Patrons; use Koha::Item::Transfers; - +use Koha::PseudonymizedTransactions; sub usage { print STDERR < \$help, @@ -160,7 +164,10 @@ GetOptions( 'deleted-patrons:i' => \$pDeletedPatrons, 'old-issues:i' => \$pOldIssues, 'old-reserves:i' => \$pOldReserves, - 'transfers:i' => \$pTransfers, + 'transfers:i' => \$pTransfers, + 'pseudo-transactions:i' => \$pPseudoTransactions, + 'pseudo-transactions-from:s' => \$pPseudoTransactionsFrom, + 'pseudo-transactions-to:s' => \$pPseudoTransactionsTo, ) || usage(1); # Use default values @@ -201,6 +208,7 @@ unless ( $sessions || $pOldIssues || $pOldReserves || $pTransfers + || defined $pPseudoTransactions ) { print "You did not specify any cleanup work for the script to do.\n\n"; usage(1); @@ -448,6 +456,19 @@ if ($pTransfers) { print "Done with purging transfers.\n" if $verbose; } +if (defined $pPseudoTransactions) { + print "Purging pseudonymized transactions older than $pPseudoTransactions days.\n" if $verbose; + Koha::PseudonymizedTransactions->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 0f67e123dd..5c35546667 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 => 23; +use Test::More tests => 24; use Test::Exception; use Test::MockModule; use Test::Warn; @@ -31,6 +31,7 @@ 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; @@ -1036,6 +1037,96 @@ subtest "attributes_from_api() 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; }; subtest "from_api_mapping() tests" => sub { -- 2.11.0