summaryrefslogtreecommitdiff
path: root/maintenance.pl
diff options
context:
space:
mode:
authorDaniel Friesel <derf@finalrewind.org>2019-03-11 17:09:35 +0100
committerDaniel Friesel <derf@finalrewind.org>2019-03-11 17:09:35 +0100
commitd6bdc0ae105fc3799094fc1bb12499243b65fa91 (patch)
treef36e1110644bb3054ae3554a6870d7e6e1e96fb4 /maintenance.pl
parent1c074c2b9a5960fee6d3e637edaff12f858e0eed (diff)
add cron DB maintenance script (prune unverified accounts)
Does not cover account deletion yet
Diffstat (limited to 'maintenance.pl')
-rwxr-xr-xmaintenance.pl54
1 files changed, 54 insertions, 0 deletions
diff --git a/maintenance.pl b/maintenance.pl
new file mode 100755
index 0000000..fe7a220
--- /dev/null
+++ b/maintenance.pl
@@ -0,0 +1,54 @@
+#!/usr/bin/env perl
+
+use strict;
+use warnings;
+use 5.020;
+
+use DateTime;
+use DBI;
+
+my $dbname = $ENV{TRAVELYNX_DB_FILE} // 'run/travelynx.sqlite';
+my $dbh = DBI->connect( "dbi:SQLite:dbname=${dbname}", q{}, q{} );
+
+my $get_unverified_query
+ = $dbh->prepare(
+qq{select email, registered_at from users where status = 0 and registered_at < ?;}
+ );
+my $get_pending_query
+ = $dbh->prepare(qq{select num_tries from pending_mails where email = ?;});
+my $set_pending_query = $dbh->prepare(
+ qq{update pending_mails set num_tries = ?, last_try = ? where email = ?;});
+my $add_pending_query
+ = $dbh->prepare(
+ qq{insert into pending_mails (email, num_tries, last_try) values (?, ?, ?);}
+ );
+my $drop_unverified_query = $dbh->prepare(
+ qq{delete from users where status = 0 and registered_at < ?;});
+
+my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+my $verification_deadline = $now->subtract( hours => 48 )->epoch;
+
+$dbh->begin_work;
+$get_unverified_query->execute($verification_deadline);
+while ( my @row = $get_unverified_query->fetchrow_array ) {
+ my ( $mail, $reg_date ) = @row;
+
+ if ($mail) {
+ $get_pending_query->execute($mail);
+ my $rows = $get_pending_query->fetchall_arrayref;
+
+ if ( @{$rows} ) {
+ my $num_tries = $rows->[0][0];
+ $set_pending_query->execute( $num_tries + 1, $reg_date, $mail );
+ }
+ else {
+ $add_pending_query->execute( $mail, 1, $reg_date );
+ }
+ }
+}
+$drop_unverified_query->execute($verification_deadline);
+printf( "Pruned %d unverified accounts from database\n",
+ $drop_unverified_query->rows );
+$dbh->commit;
+
+$dbh->disconnect;