3 # Copyright 2009 PTFS, Inc.
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
22 use constant DEFAULT_ZEBRAQ_PURGEDAYS => 30;
23 use constant DEFAULT_MAIL_PURGEDAYS => 30;
24 use constant DEFAULT_IMPORT_PURGEDAYS => 60;
25 use constant DEFAULT_LOGS_PURGEDAYS => 180;
26 use constant DEFAULT_SEARCHHISTORY_PURGEDAYS => 30;
27 use constant DEFAULT_SHARE_INVITATION_EXPIRY_DAYS => 14;
28 use constant DEFAULT_DEBARMENTS_PURGEDAYS => 30;
31 # find Koha's Perl modules
32 # test carefully before changing this
34 eval { require "$FindBin::Bin/../kohalib.pl" };
37 use Koha::Script -cron;
40 use C4::Search::History;
44 use Koha::UploadedFiles;
45 use Koha::Old::Biblios;
47 use Koha::Old::Biblioitems;
48 use Koha::Old::Checkouts;
50 use Koha::Old::Patrons;
51 use Koha::Item::Transfers;
52 use Koha::PseudonymizedTransactions;
56 Usage: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueue DAYS] [-m|--mail] [--merged] [--import DAYS] [--logs DAYS] [--searchhistory DAYS] [--restrictions DAYS] [--all-restrictions] [--fees DAYS] [--temp-uploads] [--temp-uploads-days DAYS] [--uploads-missing 0|1 ] [--statistics DAYS] [--deleted-catalog DAYS] [--deleted-patrons DAYS] [--old-issues DAYS] [--old-reserves DAYS] [--transfers DAYS]
58 -h --help prints this help message, and exits, ignoring all
60 --sessions purge the sessions table. If you use this while users
61 are logged into Koha, they will have to reconnect.
62 --sessdays DAYS purge only sessions older than DAYS days.
63 -v --verbose will cause the script to give you a bit more information
65 --zebraqueue DAYS purge completed zebraqueue entries older than DAYS days.
66 Defaults to 30 days if no days specified.
67 -m --mail DAYS purge items from the mail queue that are older than DAYS days.
68 Defaults to 30 days if no days specified.
69 --merged purged completed entries from need_merge_authorities.
70 --import DAYS purge records from import tables older than DAYS days.
71 Defaults to 60 days if no days specified.
72 --z3950 purge records from import tables that are the result
74 --fees DAYS purge entries accountlines older than DAYS days, where
75 amountoutstanding is 0 or NULL.
76 In the case of --fees, DAYS must be greater than
78 --logs DAYS purge entries from action_logs older than DAYS days.
79 Defaults to 180 days if no days specified.
80 --searchhistory DAYS purge entries from search_history older than DAYS days.
81 Defaults to 30 days if no days specified
82 --list-invites DAYS purge (unaccepted) list share invites older than DAYS
83 days. Defaults to 14 days if no days specified.
84 --restrictions DAYS purge patrons restrictions expired since more than DAYS days.
85 Defaults to 30 days if no days specified.
86 --all-restrictions purge all expired patrons restrictions.
87 --del-exp-selfreg Delete expired self registration accounts
88 --del-unv-selfreg DAYS Delete unverified self registrations older than DAYS
89 --unique-holidays DAYS Delete all unique holidays older than DAYS
90 --temp-uploads Delete temporary uploads.
91 --temp-uploads-days DAYS Override the corresponding preference value.
92 --uploads-missing FLAG Delete upload records for missing files when FLAG is true, count them otherwise
93 --oauth-tokens Delete expired OAuth2 tokens
94 --statistics DAYS Purge statistics entries more than DAYS days old.
95 This table is used to build reports, make sure you are aware of the consequences of this before using it!
96 --deleted-catalog DAYS Purge catalog records deleted more then DAYS days ago
97 (from tables deleteditems, deletedbiblioitems, deletedbiblio_metadata and deletedbiblio).
98 --deleted-patrons DAYS Purge patrons deleted more than DAYS days ago.
99 --old-issues DAYS Purge checkouts (old_issues) returned more than DAYS days ago.
100 --old-reserves DAYS Purge reserves (old_reserves) more than DAYS old.
101 --transfers DAYS Purge transfers completed more than DAYS day ago.
102 --pseudo-transactions DAYS Purge the pseudonymized transactions that have been originally created more than DAYS days ago
103 DAYS is optional and can be replaced by:
104 --pseudo-transactions-from YYYY-MM-DD and/or --pseudo-transactions-to YYYY-MM-DD
120 my $pListShareInvites;
126 my $special_holidays_days;
128 my $temp_uploads_days;
137 my ( $pPseudoTransactions, $pPseudoTransactionsFrom, $pPseudoTransactionsTo );
141 'sessions' => \$sessions,
142 'sessdays:i' => \$sess_days,
143 'v|verbose' => \$verbose,
144 'm|mail:i' => \$mail,
145 'zebraqueue:i' => \$zebraqueue_days,
146 'merged' => \$purge_merged,
147 'import:i' => \$pImport,
150 'fees:i' => \$fees_days,
151 'searchhistory:i' => \$pSearchhistory,
152 'list-invites:i' => \$pListShareInvites,
153 'restrictions:i' => \$pDebarments,
154 'all-restrictions' => \$allDebarments,
155 'del-exp-selfreg' => \$pExpSelfReg,
156 'del-unv-selfreg' => \$pUnvSelfReg,
157 'unique-holidays:i' => \$special_holidays_days,
158 'temp-uploads' => \$temp_uploads,
159 'temp-uploads-days:i' => \$temp_uploads_days,
160 'uploads-missing:i' => \$uploads_missing,
161 'oauth-tokens' => \$oauth_tokens,
162 'statistics:i' => \$pStatistics,
163 'deleted-catalog:i' => \$pDeletedCatalog,
164 'deleted-patrons:i' => \$pDeletedPatrons,
165 'old-issues:i' => \$pOldIssues,
166 'old-reserves:i' => \$pOldReserves,
167 'transfers:i' => \$pTransfers,
168 'pseudo-transactions:i' => \$pPseudoTransactions,
169 'pseudo-transactions-from:s' => \$pPseudoTransactionsFrom,
170 'pseudo-transactions-to:s' => \$pPseudoTransactionsTo,
174 $sessions = 1 if $sess_days && $sess_days > 0;
175 $pImport = DEFAULT_IMPORT_PURGEDAYS if defined($pImport) && $pImport == 0;
176 $pLogs = DEFAULT_LOGS_PURGEDAYS if defined($pLogs) && $pLogs == 0;
177 $zebraqueue_days = DEFAULT_ZEBRAQ_PURGEDAYS if defined($zebraqueue_days) && $zebraqueue_days == 0;
178 $mail = DEFAULT_MAIL_PURGEDAYS if defined($mail) && $mail == 0;
179 $pSearchhistory = DEFAULT_SEARCHHISTORY_PURGEDAYS if defined($pSearchhistory) && $pSearchhistory == 0;
180 $pListShareInvites = DEFAULT_SHARE_INVITATION_EXPIRY_DAYS if defined($pListShareInvites) && $pListShareInvites == 0;
181 $pDebarments = DEFAULT_DEBARMENTS_PURGEDAYS if defined($pDebarments) && $pDebarments == 0;
196 || $pListShareInvites
201 || $special_holidays_days
203 || defined $uploads_missing
211 || defined $pPseudoTransactions
212 || $pPseudoTransactionsFrom
213 || $pPseudoTransactionsTo
215 print "You did not specify any cleanup work for the script to do.\n\n";
219 if ($pDebarments && $allDebarments) {
220 print "You can not specify both --restrictions and --all-restrictions.\n\n";
226 my $dbh = C4::Context->dbh();
231 if ( $sessions && !$sess_days ) {
233 print "Session purge triggered.\n";
234 $sth = $dbh->prepare(q{ SELECT COUNT(*) FROM sessions });
235 $sth->execute() or die $dbh->errstr;
236 my @count_arr = $sth->fetchrow_array;
237 print "$count_arr[0] entries will be deleted.\n";
239 $sth = $dbh->prepare(q{ TRUNCATE sessions });
240 $sth->execute() or die $dbh->errstr;
242 print "Done with session purge.\n";
245 elsif ( $sessions && $sess_days > 0 ) {
246 print "Session purge triggered with days>$sess_days.\n" if $verbose;
248 print "Done with session purge with days>$sess_days.\n" if $verbose;
251 if ($zebraqueue_days) {
253 print "Zebraqueue purge triggered for $zebraqueue_days days.\n" if $verbose;
254 $sth = $dbh->prepare(
256 SELECT id,biblio_auth_number,server,time
258 WHERE done=1 AND time < date_sub(curdate(), INTERVAL ? DAY)
261 $sth->execute($zebraqueue_days) or die $dbh->errstr;
262 $sth2 = $dbh->prepare(q{ DELETE FROM zebraqueue WHERE id=? });
263 while ( my $record = $sth->fetchrow_hashref ) {
264 $sth2->execute( $record->{id} ) or die $dbh->errstr;
267 print "$count records were deleted.\nDone with zebraqueue purge.\n" if $verbose;
271 print "Mail queue purge triggered for $mail days.\n" if $verbose;
272 $sth = $dbh->prepare(
274 DELETE FROM message_queue
275 WHERE time_queued < date_sub(curdate(), INTERVAL ? DAY)
278 $sth->execute($mail) or die $dbh->errstr;
281 print "$count messages were deleted from the mail queue.\nDone with message_queue purge.\n" if $verbose;
285 print "Purging completed entries from need_merge_authorities.\n" if $verbose;
286 $sth = $dbh->prepare(q{ DELETE FROM need_merge_authorities WHERE done=1 });
287 $sth->execute() or die $dbh->errstr;
288 print "Done with purging need_merge_authorities.\n" if $verbose;
292 print "Purging records from import tables.\n" if $verbose;
294 print "Done with purging import tables.\n" if $verbose;
298 print "Purging Z39.50 records from import tables.\n" if $verbose;
300 print "Done with purging Z39.50 records from import tables.\n" if $verbose;
304 print "Purging records from action_logs.\n" if $verbose;
305 $sth = $dbh->prepare(
307 DELETE FROM action_logs
308 WHERE timestamp < date_sub(curdate(), INTERVAL ? DAY)
311 $sth->execute($pLogs) or die $dbh->errstr;
312 print "Done with purging action_logs.\n" if $verbose;
316 print "Purging records from accountlines.\n" if $verbose;
317 purge_zero_balance_fees( $fees_days );
318 print "Done purging records from accountlines.\n" if $verbose;
321 if ($pSearchhistory) {
322 print "Purging records older than $pSearchhistory from search_history.\n" if $verbose;
323 C4::Search::History::delete({ interval => $pSearchhistory });
324 print "Done with purging search_history.\n" if $verbose;
327 if ($pListShareInvites) {
328 print "Purging unaccepted list share invites older than $pListShareInvites days.\n" if $verbose;
329 $sth = $dbh->prepare(
331 DELETE FROM virtualshelfshares
332 WHERE invitekey IS NOT NULL
333 AND (sharedate + INTERVAL ? DAY) < NOW()
336 $sth->execute($pListShareInvites);
337 print "Done with purging unaccepted list share invites.\n" if $verbose;
341 print "Expired patrons restrictions purge triggered for $pDebarments days.\n" if $verbose;
342 $count = PurgeDebarments($pDebarments);
343 print "$count restrictions were deleted.\nDone with restrictions purge.\n" if $verbose;
347 print "All expired patrons restrictions purge triggered.\n" if $verbose;
348 $count = PurgeDebarments(0);
349 print "$count restrictions were deleted.\nDone with all restrictions purge.\n" if $verbose;
352 # Handle unsubscribe requests from GDPR consent form, depends on UnsubscribeReflectionDelay preference
353 my $unsubscribed_patrons = Koha::Patrons->search_unsubscribed;
354 $count = $unsubscribed_patrons->count;
355 $unsubscribed_patrons->lock( { expire => 1, remove => 1 } );
356 say sprintf "Locked %d patrons", $count if $verbose;
358 # Anonymize patron data, depending on PatronAnonymizeDelay
359 my $anonymize_candidates = Koha::Patrons->search_anonymize_candidates( { locked => 1 } );
360 $count = $anonymize_candidates->count;
361 $anonymize_candidates->anonymize;
362 say sprintf "Anonymized %s patrons", $count if $verbose;
364 # Remove patron data, depending on PatronRemovalDelay (will raise an exception if problem encountered
365 my $anonymized_patrons = Koha::Patrons->search_anonymized;
366 $count = $anonymized_patrons->count;
367 $anonymized_patrons->delete( { move => 1 } );
372 say sprintf "Deleted %d patrons", $count;
376 DeleteExpiredSelfRegs();
379 DeleteUnverifiedSelfRegs( $pUnvSelfReg );
382 if ($special_holidays_days) {
383 DeleteSpecialHolidays( abs($special_holidays_days) );
386 if( $temp_uploads ) {
387 # Delete temporary uploads, governed by a pref (unless you override)
388 print "Purging temporary uploads.\n" if $verbose;
389 Koha::UploadedFiles->delete_temporary({
390 defined($temp_uploads_days)
391 ? ( override_pref => $temp_uploads_days )
394 print "Done purging temporary uploads.\n" if $verbose;
397 if( defined $uploads_missing ) {
398 print "Looking for missing uploads\n" if $verbose;
399 my $keep = $uploads_missing == 1 ? 0 : 1;
400 my $count = Koha::UploadedFiles->delete_missing({ keep_record => $keep });
402 print "Counted $count missing uploaded files\n";
404 print "Removed $count records for missing uploads\n";
409 require Koha::OAuthAccessTokens;
411 my $count = int Koha::OAuthAccessTokens->search({ expires => { '<=', time } })->delete;
412 say "Removed $count expired OAuth2 tokens" if $verbose;
416 print "Purging statistics older than $pStatistics days.\n" if $verbose;
417 Koha::Statistics->filter_by_last_update(
418 { timestamp_column_name => 'datetime', days => $pStatistics } )->delete;
419 print "Done with purging statistics.\n" if $verbose;
422 if ($pDeletedCatalog) {
423 print "Purging deleted catalog older than $pDeletedCatalog days.\n" if $verbose;
424 Koha::Old::Items ->filter_by_last_update( { days => $pDeletedCatalog } )->delete;
425 Koha::Old::Biblioitems->filter_by_last_update( { days => $pDeletedCatalog } )->delete;
426 Koha::Old::Biblios ->filter_by_last_update( { days => $pDeletedCatalog } )->delete;
427 print "Done with purging deleted catalog.\n" if $verbose;
430 if ($pDeletedPatrons) {
431 print "Purging deleted patrons older than $pDeletedPatrons days.\n" if $verbose;
432 Koha::Old::Patrons->filter_by_last_update(
433 { timestamp_column_name => 'updated_on', days => $pDeletedPatrons } )
435 print "Done with purging deleted patrons.\n" if $verbose;
439 print "Purging old checkouts older than $pOldIssues days.\n" if $verbose;
440 Koha::Old::Checkouts->filter_by_last_update( { days => $pOldIssues } )->delete;
441 print "Done with purging old issues.\n" if $verbose;
445 print "Purging old reserves older than $pOldReserves days.\n" if $verbose;
446 Koha::Old::Holds->filter_by_last_update( { days => $pOldReserves } )->delete;
447 print "Done with purging old reserves.\n" if $verbose;
451 print "Purging arrived item transfers older than $pTransfers days.\n" if $verbose;
452 Koha::Item::Transfers->filter_by_last_update(
454 timestamp_column_name => 'datearrived',
458 print "Done with purging transfers.\n" if $verbose;
461 if (defined $pPseudoTransactions or $pPseudoTransactionsFrom or $pPseudoTransactionsTo ) {
462 print "Purging pseudonymized transactions\n" if $verbose;
463 Koha::PseudonymizedTransactions->filter_by_last_update(
465 timestamp_column_name => 'datetime',
466 ( defined $pPseudoTransactions ? ( days => $pPseudoTransactions ) : () ),
467 ( $pPseudoTransactionsFrom ? ( from => $pPseudoTransactionsFrom ) : () ),
468 ( $pPseudoTransactionsTo ? ( to => $pPseudoTransactionsTo ) : () ),
471 print "Done with purging pseudonymized transactions.\n" if $verbose;
476 sub RemoveOldSessions {
477 my ( $id, $a_session, $limit, $lasttime );
478 $limit = time() - 24 * 3600 * $sess_days;
480 $sth = $dbh->prepare(q{ SELECT id, a_session FROM sessions });
481 $sth->execute or die $dbh->errstr;
482 $sth->bind_columns( \$id, \$a_session );
483 $sth2 = $dbh->prepare(q{ DELETE FROM sessions WHERE id=? });
486 while ( $sth->fetch ) {
488 if ( $a_session =~ /lasttime:\s+'?(\d+)/ ) {
491 elsif ( $a_session =~ /(ATIME|CTIME):\s+'?(\d+)/ ) {
494 if ( $lasttime && $lasttime < $limit ) {
495 $sth2->execute($id) or die $dbh->errstr;
500 print "$count sessions were deleted.\n";
504 sub PurgeImportTables {
506 #First purge import_records
507 #Delete cascades to import_biblios, import_items and import_record_matches
508 $sth = $dbh->prepare(
510 DELETE FROM import_records
511 WHERE upload_timestamp < date_sub(curdate(), INTERVAL ? DAY)
514 $sth->execute($pImport) or die $dbh->errstr;
516 # Now purge import_batches
517 # Timestamp cannot be used here without care, because records are added
518 # continuously to batches without updating timestamp (Z39.50 search).
519 # So we only delete older empty batches.
520 # This delete will therefore not have a cascading effect.
521 $sth = $dbh->prepare(
524 FROM import_batches ba
525 LEFT JOIN import_records re ON re.import_batch_id=ba.import_batch_id
526 WHERE re.import_record_id IS NULL AND
527 ba.upload_timestamp < date_sub(curdate(), INTERVAL ? DAY)
530 $sth->execute($pImport) or die $dbh->errstr;
534 $sth = $dbh->prepare(
536 DELETE FROM import_batches
537 WHERE batch_type = 'z3950'
540 $sth->execute() or die $dbh->errstr;
543 sub PurgeDebarments {
544 require Koha::Patron::Debarments;
547 $sth = $dbh->prepare(
549 SELECT borrower_debarment_id
550 FROM borrower_debarments
551 WHERE expiration < date_sub(curdate(), INTERVAL ? DAY)
554 $sth->execute($days) or die $dbh->errstr;
555 while ( my ($borrower_debarment_id) = $sth->fetchrow_array ) {
556 Koha::Patron::Debarments::DelDebarment($borrower_debarment_id);
562 sub DeleteExpiredSelfRegs {
563 my $cnt= C4::Members::DeleteExpiredOpacRegistrations();
564 print "Removed $cnt expired self-registered borrowers\n" if $verbose;
567 sub DeleteUnverifiedSelfRegs {
568 my $cnt= C4::Members::DeleteUnverifiedOpacRegistrations( $_[0] );
569 print "Removed $cnt unverified self-registrations\n" if $verbose;
572 sub DeleteSpecialHolidays {
575 my $sth = $dbh->prepare(q{
576 DELETE FROM special_holidays
577 WHERE DATE( CONCAT( year, '-', month, '-', day ) ) < DATE_SUB( CAST(NOW() AS DATE), INTERVAL ? DAY );
579 my $count = $sth->execute( $days ) + 0;
580 print "Removed $count unique holidays\n" if $verbose;