1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
package Travelynx::Command::maintenance;
use Mojo::Base 'Mojolicious::Command';
use DateTime;
has description => 'Prune unverified users etc';
has usage => sub { shift->extract_usage };
sub run {
my ( $self, $filename ) = @_;
my $now = DateTime->now( time_zone => 'Europe/Berlin' );
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 }
}
);
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( 'mail_blacklist', ['num_tries'], { email => $mail } );
my $pending_h = $pending->hash;
if ($pending_h) {
my $num_tries = $pending_h->{num_tries} + 1;
$db->update(
'mail_blacklist',
{
num_tries => $num_tries,
last_try => $reg_date
},
{ email => $mail }
);
}
else {
$db->insert(
'mail_blacklist',
{
email => $mail,
num_tries => 1,
last_try => $reg_date
}
);
}
$db->delete( 'users', { id => $user->{id} } );
printf( "Pruned unverified user %d\n", $user->{id} );
}
my $res = $db->delete( 'pending_passwords',
{ requested_at => { '<', $verification_deadline } } );
if ( my $rows = $res->rows ) {
printf( "Pruned %d pending password reset(s)\n", $rows );
}
$res = $db->delete( 'pending_mails',
{ requested_at => { '<', $verification_deadline } } );
if ( my $rows = $res->rows ) {
printf( "Pruned %d pending mail change(s)\n", $rows );
}
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 $journeys_res = $db->delete( 'journeys', { user_id => $uid } );
my $transit_res = $db->delete( 'in_transit', { user_id => $uid } );
my $password_res
= $db->delete( 'pending_passwords', { user_id => $uid } );
my $user_res = $db->delete( 'users', { id => $uid } );
printf( " %d tokens, %d monthly stats, %d journeys\n",
$tokens_res->rows, $stats_res->rows, $journeys_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);
}
}
$tx->commit;
}
1;
__END__
=head1 SYNOPSIS
Usage: index.pl maintenance
Prunes unverified users.
|
