From cb0cdb4c97783566339d080be1a6263bad4caf97 Mon Sep 17 00:00:00 2001 From: Andrew Fuerste-Henry Date: Mon, 20 Jul 2020 15:09:30 +0000 Subject: [PATCH] Bug 15563: add label and card creator batch deletion to cleanup_database To test: For item label batches: 1- Create 3 item label batches of at least 2 items each 2- Perform the following updates via the database 3- In Batch 1, set all items' timestamps to 10+ days prior to today 4- In Batch 2, set 1 item's timestamp to 10+ days prior to today 5- In Batch 3, do not update any timestamps 6- Run cron with --labels 9 7- Confirm batch 1 is deleted, batches 2 and 3 are not Repeat with card creator batches 8- Create 3 card creator batches of at least 2 items each 9- Perform the following updates via the database 10- In Batch 1, set all cards' timestamps to 10+ days prior to today 11- In Batch 2, set 1 card's timestamp to 10+ days prior to today 12- In Batch 3, do not update any timestamps 13- Run cron with --cards 9 14- Confirm batch 1 is deleted, batches 2 and 3 are noti --- misc/cronjobs/cleanup_database.pl | 47 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 47 insertions(+) diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index c2d8e9832c..d59fa3a368 100755 --- a/misc/cronjobs/cleanup_database.pl +++ b/misc/cronjobs/cleanup_database.pl @@ -103,6 +103,9 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose] --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-transactions-to YYYY-MM-DD + --labels DAYS Purge item label batches last added to more than DAYS days ago. + --cards DAY Purge card creator batches last added to more than DAYS days ago. + USAGE exit $_[0]; } @@ -577,6 +580,50 @@ if (defined $pPseudoTransactions or $pPseudoTransactionsFrom or $pPseudoTransact ? sprintf "Done with purging %d pseudonymized transactions.", $count : sprintf "%d pseudonymized transactions would have been purged.", $count; } + $sth = $dbh->prepare( + q{ + DELETE FROM branchtransfers + WHERE datearrived < DATE_SUB(CURDATE(), INTERVAL ? DAY) + } + ); + $sth->execute($pTransfers); + print "Done with purging transfers.\n" if $verbose; +} + +if ($labels) { + print "Purging item label batches last added to more than $labels days ago.\n" if $verbose; + $sth = $dbh->prepare( + q{ + DELETE from creator_batches + WHERE batch_id in + (SELECT batch_id + FROM (SELECT batch_id + FROM creator_batches + WHERE creator='labels' + GROUP BY batch_id + HAVING max(timestamp) < date_sub(curdate(),interval ? day)) a) + } + ); + $sth->execute($labels) or die $dbh->errstr; + print "Done with purging item label batches last added to more than $labels days ago.\n" if $verbose; +} + +if ($cards) { + print "Purging card creator batches last added to more than $cards days ago.\n" if $verbose; + $sth = $dbh->prepare( + q{ + DELETE from creator_batches + WHERE batch_id in + (SELECT batch_id + FROM (SELECT batch_id + FROM creator_batches + WHERE creator='patroncards' + GROUP BY batch_id + HAVING max(timestamp) < date_sub(curdate(),interval ? day)) a); + } + ); + $sth->execute($cards) or die $dbh->errstr; + print "Done with purging card creator batches last added to more than $cards days ago.\n" if $verbose; } exit(0); -- 2.11.0