Move maintenance script to Mojolicious command

2 files changed, 71 insertions, 54 deletions

diff --git a/lib/Travelynx/Command/maintenance.pm b/lib/Travelynx/Command/maintenance.pm
new file mode 100644
index 0000000..b9f5947
--- /dev/null
+++ b/lib/Travelynx/Command/maintenance.pm
@@ -0,0 +1,71 @@
+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 $dbh = $self->app->dbh;
+
+	my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+	my $verification_deadline = $now->subtract( hours => 48 )->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 $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(?);}
+	  );
+
+	$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;
+}
+
+1;
+
+__END__
+
+=head1 SYNOPSIS
+
+  Usage: index.pl maintenance
+
+  Prunes unverified users.
diff --git a/maintenance.pl b/maintenance.pl
deleted file mode 100755
index fe7a220..0000000
--- a/maintenance.pl
+++ /dev/null
@@ -1,54 +0,0 @@
-#!/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;