summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
Diffstat (limited to 'lib')
-rwxr-xr-xlib/Travelynx.pm18
-rw-r--r--lib/Travelynx/Command/maintenance.pm153
2 files changed, 77 insertions, 94 deletions
diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index 576e715..fd59cc0 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -6,7 +6,6 @@ use Mojolicious::Plugin::Authentication;
use Cache::File;
use Crypt::Eksblowfish::Bcrypt qw(bcrypt en_base64);
use DateTime;
-use DBI;
use Encode qw(decode encode);
use Geo::Distance;
use JSON;
@@ -148,23 +147,6 @@ sub startup {
}
);
- $self->attr(
- dbh => sub {
- my ($self) = @_;
- my $config = $self->app->config;
-
- my $dbname = $config->{db}->{database};
- my $host = $config->{db}->{host} // 'localhost';
- my $port = $config->{db}->{port} // 5432;
- my $user = $config->{db}->{user};
- my $pw = $config->{db}->{password};
-
- return DBI->connect(
- "dbi:Pg:dbname=${dbname};host=${host};port=${port}",
- $user, $pw, { AutoCommit => 1 } );
- }
- );
-
$self->helper(
sendmail => sub {
state $sendmail = Travelynx::Helper::Sendmail->new(
diff --git a/lib/Travelynx/Command/maintenance.pm b/lib/Travelynx/Command/maintenance.pm
index 3ff4000..0651a83 100644
--- a/lib/Travelynx/Command/maintenance.pm
+++ b/lib/Travelynx/Command/maintenance.pm
@@ -10,90 +10,91 @@ has usage => sub { shift->extract_usage };
sub run {
my ( $self, $filename ) = @_;
- my $dbh = $self->app->dbh;
-
my $now = DateTime->now( time_zone => 'Europe/Berlin' );
- my $verification_deadline = $now->subtract( hours => 48 )->epoch;
- my $deletion_deadline = $now->subtract( hours => 72 )->epoch;
-
- my $get_unverified_query
- = $dbh->prepare(
-qq{select email, extract(epoch from registered_at) from users where status = 0 and registered_at < to_timestamp(?);}
- );
- my $get_pending_query
- = $dbh->prepare(qq{select num_tries from pending_mails where email = ?;});
- my $get_deleted_query = $dbh->prepare(
- qq{select id from users where deletion_requested < to_timestamp(?);});
- my $set_pending_query
- = $dbh->prepare(
-qq{update pending_mails set num_tries = ?, last_try = to_timestamp(?) where email = ?;}
- );
- my $add_pending_query
- = $dbh->prepare(
-qq{insert into pending_mails (email, num_tries, last_try) values (?, ?, to_timestamp(?));}
- );
- my $drop_unverified_query
- = $dbh->prepare(
-qq{delete from users where status = 0 and registered_at < to_timestamp(?);}
- );
- my $drop_tokens_query
- = $dbh->prepare(qq{delete from tokens where user_id = ?;});
- my $drop_stats_query
- = $dbh->prepare(qq{delete from journey_stats where user_id = ?;});
- my $drop_actions_query
- = $dbh->prepare(qq{delete from user_actions where user_id = ?;});
- my $drop_user_query = $dbh->prepare(qq{delete from users where id = ?;});
-
- $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 );
- }
+ my $verification_deadline = $now->clone->subtract( hours => 48 );
+ my $deletion_deadline = $now->clone->subtract( hours => 72 );
+
+ my $db = $self->app->pg->db;
+ my $tx = $db->begin;
+
+ my $unverified = $db->select(
+ 'users',
+ 'id, email, extract(epoch from registered_at) as registered_ts',
+ {
+ status => 0,
+ registered_at => { '<', $verification_deadline }
}
- }
- $drop_unverified_query->execute($verification_deadline);
- printf( "Pruned %d unverified accounts from database\n",
- $drop_unverified_query->rows );
- $dbh->commit;
-
- $dbh->begin_work;
- $get_deleted_query->execute($deletion_deadline);
- my @uids_to_delete
- = map { $_->[0] } @{ $get_deleted_query->fetchall_arrayref };
-
- if ( @uids_to_delete < 10 ) {
- for my $uid (@uids_to_delete) {
- say "Deleting uid ${uid}...";
- $drop_tokens_query->execute($uid);
- $drop_stats_query->execute($uid);
- $drop_actions_query->execute($uid);
- $drop_user_query->execute($uid);
- printf( " %d tokens, %d monthly stats, %d actions\n",
- $drop_tokens_query->rows, $drop_stats_query->rows,
- $drop_actions_query->rows );
+ );
+
+ for my $user ( $unverified->hashes->each ) {
+ my $mail = $user->{email};
+ my $reg_date = DateTime->from_epoch(
+ epoch => $user->{registered_ts},
+ time_zone => 'Europe/Berlin'
+ );
+
+ my $pending
+ = $db->select( 'pending_mails', ['num_tries'], { email => $mail } );
+ my $pending_h = $pending->hash;
+
+ if ($pending_h) {
+ my $num_tries = $pending_h->{num_tries} + 1;
+ $db->update(
+ 'pending_mails',
+ {
+ num_tries => $num_tries,
+ last_try => $reg_date
+ },
+ { email => $mail }
+ );
+ }
+ else {
+ $db->insert(
+ 'pending_mails',
+ {
+ email => $mail,
+ num_tries => 1,
+ last_try => $reg_date
+ }
+ );
}
+ $db->delete( 'users', { id => $user->{id} } );
+ printf( "Pruned unverified user %d\n", $user->{id} );
}
- else {
- printf(
- "Unusually high number of deletion requests (%d accounts)"
- . " -- skipping automatic deletion, please investigate\n",
+
+ my $to_delete = $db->select( 'users', ['id'],
+ { deletion_requested => { '<', $deletion_deadline } } );
+ my @uids_to_delete = $to_delete->arrays->map( sub { shift->[0] } )->each;
+
+ if ( @uids_to_delete > 10 ) {
+ printf STDERR (
+ "About to delete %d accounts, which is quite a lot.\n",
scalar @uids_to_delete
);
+ say STDERR 'Aborting maintenance. Please investigate.';
+ exit(1);
+ }
+
+ for my $uid (@uids_to_delete) {
+ say "Deleting uid ${uid}...";
+ my $tokens_res = $db->delete( 'tokens', { user_id => $uid } );
+ my $stats_res = $db->delete( 'journey_stats', { user_id => $uid } );
+ my $actions_res = $db->delete( 'user_actions', { user_id => $uid } );
+ my $user_res = $db->delete( 'users', { id => $uid } );
+
+ printf( " %d tokens, %d monthly stats, %d actions\n",
+ $tokens_res->rows, $stats_res->rows, $actions_res->rows );
+
+ if ( $user_res->rows != 1 ) {
+ printf STDERR (
+ "Deleted %d rows from users, expected 1. Rollback and abort.\n",
+ $user_res->rows
+ );
+ exit(1);
+ }
}
- $dbh->commit;
- $dbh->disconnect;
+ $tx->commit;
}
1;