From fb3fda97369b9ad3afc773838c7da99843347855 Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Mon, 22 Apr 2019 09:58:39 +0200
Subject: More Mojo::Pg migrations

---
 lib/Travelynx.pm                    | 364 ++++++++++++++----------------------
 lib/Travelynx/Controller/Account.pm |   8 +-
 2 files changed, 143 insertions(+), 229 deletions(-)

diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index caa4cba..da2d76b 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -148,89 +148,6 @@ sub startup {
 		}
 	);
 
-	$self->attr(
-		add_station_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			insert into stations (ds100, name) values (?, ?)
-		}
-			);
-		}
-	);
-	$self->attr(
-		add_user_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			insert into users (
-				name, status, public_level, email, token, password,
-				registered_at, last_login
-			) values (?, 0, 0, ?, ?, ?, to_timestamp(?), to_timestamp(?));
-		}
-			);
-		}
-	);
-	$self->attr(
-		set_email_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-				update users set email = ?, token = ? where id = ?;
-			}
-			);
-		}
-	);
-	$self->attr(
-		set_password_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-				update users set password = ? where id = ?;
-			}
-			);
-		}
-	);
-	$self->attr(
-		add_mail_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-				insert into pending_mails (
-					email, num_tries, last_try
-				) values (?, ?, to_timestamp(?));
-			}
-			);
-		}
-	);
-	$self->attr(
-		mark_for_deletion_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-				update users set deletion_requested = to_timestamp(?) where id = ?;
-			}
-			);
-		}
-	);
-	$self->attr(
-		get_stats_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-					select data from journey_stats
-					where user_id = ? and year = ? and month = ?
-				}
-			);
-		}
-	);
 	$self->attr(
 		dbh => sub {
 			my ($self) = @_;
@@ -331,45 +248,6 @@ sub startup {
 			);
 		}
 	);
-	$self->attr(
-		get_userid_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{select id from users where name = ?});
-		}
-	);
-	$self->attr(
-		get_pending_mails_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{select id from users where email = ? and status = 0;});
-		}
-	);
-	$self->attr(
-		get_listed_mails_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-qq{select * from pending_mails where email = ? and num_tries > 1;}
-			);
-		}
-	);
-	$self->attr(
-		get_user_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			select
-				id, name, status, public_level, email,
-				extract(epoch from registered_at), extract(epoch from last_login), extract(epoch from deletion_requested)
-			from users where id = ?
-		}
-			);
-		}
-	);
 	$self->attr(
 		get_api_tokens_query => sub {
 			my ($self) = @_;
@@ -423,35 +301,6 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 			);
 		}
 	);
-	$self->attr(
-		get_password_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			select
-				id, name, status, password
-			from users where name = ?
-		}
-			);
-		}
-	);
-	$self->attr(
-		get_stationid_by_ds100_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{select id from stations where ds100 = ?});
-		}
-	);
-	$self->attr(
-		get_stationid_by_name_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{select id from stations where name = ?});
-		}
-	);
 
 	$self->helper(
 		sendmail => sub {
@@ -989,21 +838,27 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 		'get_station_id' => sub {
 			my ( $self, %opt ) = @_;
 
-			$self->app->get_stationid_by_ds100_query->execute( $opt{ds100} );
-			my $rows
-			  = $self->app->get_stationid_by_ds100_query->fetchall_arrayref;
-			if ( @{$rows} ) {
-				return $rows->[0][0];
-			}
-			else {
-				$self->app->add_station_query->execute( $opt{ds100},
-					$opt{name} );
-				$self->app->get_stationid_by_ds100_query->execute(
-					$opt{ds100} );
-				my $rows
-				  = $self->app->get_stationid_by_ds100_query->fetchall_arrayref;
-				return $rows->[0][0];
+			my $res = $self->pg->db->select( 'stations', ['id'],
+				{ ds100 => $opt{ds100} } );
+			my $res_h = $res->hash;
+
+			if ($res_h) {
+				$res->finish;
+				return $res_h->{id};
 			}
+
+			$self->pg->db->insert(
+				'stations',
+				{
+					ds100 => $opt{ds100},
+					name  => $opt{name},
+				}
+			);
+			$res = $self->pg->db->select( 'stations', ['id'],
+				{ ds100 => $opt{ds100} } );
+			my $id = $res->hash->{id};
+			$res->finish;
+			return $id;
 		}
 	);
 
@@ -1032,28 +887,34 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 			my ( $self, $uid ) = @_;
 
 			$uid //= $self->current_user->{id};
-			my $query = $self->app->get_user_query;
-			$query->execute($uid);
-			my $rows = $query->fetchall_arrayref;
-			if ( @{$rows} ) {
-				my @row = @{ $rows->[0] };
+
+			my $user_data = $self->pg->db->select(
+				'users',
+				'id, name, status, public_level, email, '
+				  . 'extract(epoch from registered_at) as registered_at_ts, '
+				  . 'extract(epoch from last_login) as last_login_ts, '
+				  . 'extract(epoch from deletion_requested) as deletion_requested_ts',
+				{ id => $uid }
+			)->hash;
+
+			if ($user_data) {
 				return {
-					id            => $row[0],
-					name          => $row[1],
-					status        => $row[2],
-					is_public     => $row[3],
-					email         => $row[4],
+					id            => $user_data->{id},
+					name          => $user_data->{name},
+					status        => $user_data->{status},
+					is_public     => $user_data->{public_level},
+					email         => $user_data->{email},
 					registered_at => DateTime->from_epoch(
-						epoch     => $row[5],
+						epoch     => $user_data->{registered_at_ts},
 						time_zone => 'Europe/Berlin'
 					),
 					last_seen => DateTime->from_epoch(
-						epoch     => $row[6],
+						epoch     => $user_data->{last_login_ts},
 						time_zone => 'Europe/Berlin'
 					),
-					deletion_requested => $row[7]
+					deletion_requested => $user_data->{deletion_requested_ts}
 					? DateTime->from_epoch(
-						epoch     => $row[7],
+						epoch     => $user_data->{deletion_requested_ts},
 						time_zone => 'Europe/Berlin'
 					  )
 					: undef,
@@ -1081,19 +942,14 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 	$self->helper(
 		'get_user_password' => sub {
 			my ( $self, $name ) = @_;
-			my $query = $self->app->get_password_query;
-			$query->execute($name);
-			my $rows = $query->fetchall_arrayref;
-			if ( @{$rows} ) {
-				my @row = @{ $rows->[0] };
-				return {
-					id            => $row[0],
-					name          => $row[1],
-					status        => $row[2],
-					password_hash => $row[3],
-				};
-			}
-			return;
+
+			my $res_h = $self->pg->db->select(
+				'users',
+				'id, name, status, password as password_hash',
+				{ name => $name }
+			)->hash;
+
+			return $res_h;
 		}
 	);
 
@@ -1101,36 +957,68 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 		'add_user' => sub {
 			my ( $self, $user_name, $email, $token, $password ) = @_;
 
-			$self->app->get_userid_query->execute($user_name);
-			my $rows = $self->app->get_userid_query->fetchall_arrayref;
+			my $now = DateTime->now( time_zone => 'Europe/Berlin' );
 
-			if ( @{$rows} ) {
-				my $id = $rows->[0][0];
+			my $res = $self->pg->db->insert(
+				'users',
+				{
+					name          => $user_name,
+					status        => 0,
+					public_level  => 0,
+					email         => $email,
+					token         => $token,
+					password      => $password,
+					registered_at => $now,
+					last_login    => $now,
+				},
+				{ returning => 'id' }
+			);
+
+			return $res->hash->{id};
+		}
+	);
+
+	$self->helper(
+		'flag_user_deletion' => sub {
+			my ( $self, $uid ) = @_;
+
+			my $now = DateTime->now( time_zone => 'Europe/Berlin' );
 
-				# transition code for closed beta account -> normal account
-				if ($email) {
-					$self->app->set_email_query->execute( $email, $token, $id );
+			$self->pg->db->update(
+				'users',
+				{ deletion_requested => $now },
+				{
+					id => $uid,
 				}
-				if ($password) {
-					$self->app->set_password_query->execute( $password, $id );
+			);
+		}
+	);
+
+	$self->helper(
+		'unflag_user_deletion' => sub {
+			my ( $self, $uid ) = @_;
+
+			$self->pg->db->update(
+				'users',
+				{
+					deletion_requested => undef,
+				},
+				{
+					id => $uid,
 				}
-				return $id;
-			}
-			else {
-				my $now = DateTime->now( time_zone => 'Europe/Berlin' )->epoch;
-				$self->app->add_user_query->execute( $user_name, $email, $token,
-					$password, $now, $now );
-				$self->app->get_userid_query->execute($user_name);
-				$rows = $self->app->get_userid_query->fetchall_arrayref;
-				return $rows->[0][0];
-			}
+			);
 		}
 	);
 
 	$self->helper(
 		'set_user_password' => sub {
 			my ( $self, $uid, $password ) = @_;
-			$self->app->set_password_query->execute( $password, $uid );
+
+			$self->pg->db->update(
+				'users',
+				{ password => $password },
+				{ id       => $uid }
+			);
 		}
 	);
 
@@ -1138,10 +1026,13 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 		'check_if_user_name_exists' => sub {
 			my ( $self, $user_name ) = @_;
 
-			$self->app->get_userid_query->execute($user_name);
-			my $rows = $self->app->get_userid_query->fetchall_arrayref;
+			my $count = $self->pg->db->select(
+				'users',
+				'count(*) as count',
+				{ name => $user_name }
+			)->hash->{count};
 
-			if ( @{$rows} ) {
+			if ($count) {
 				return 1;
 			}
 			return 0;
@@ -1152,12 +1043,29 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 		'check_if_mail_is_blacklisted' => sub {
 			my ( $self, $mail ) = @_;
 
-			$self->app->get_pending_mails_query->execute($mail);
-			if ( @{ $self->app->get_pending_mails_query->fetchall_arrayref } ) {
+			my $count = $self->pg->db->select(
+				'users',
+				'count(*) as count',
+				{
+					email  => $mail,
+					status => 0,
+				}
+			)->hash->{count};
+
+			if ($count) {
 				return 1;
 			}
-			$self->app->get_listed_mails_query->execute($mail);
-			if ( @{ $self->app->get_listed_mails_query->fetchall_arrayref } ) {
+
+			$count = $self->pg->db->select(
+				'pending_mails',
+				'count(*) as count',
+				{
+					email     => $mail,
+					num_tries => { '>', 1 },
+				}
+			)->hash->{count};
+
+			if ($count) {
 				return 1;
 			}
 			return 0;
@@ -1236,11 +1144,21 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
 			# -> Cache entries must be explicitly invalidated whenever the user
 			# checks out of a train or manually edits/adds a journey.
 
-			$self->app->get_stats_query->execute( $uid, $year, $month );
-			my $rows = $self->app->get_stats_query->fetchall_arrayref;
+			my $res = $self->pg->db->select(
+				'journey_stats',
+				['data'],
+				{
+					user_id => $uid,
+					year    => $year,
+					month   => $month
+				}
+			);
+
+			my $res_h = $res->expand->hash;
 
-			if ( @{$rows} == 1 ) {
-				return JSON->new->decode( $rows->[0][0] );
+			if ($res_h) {
+				$res->finish;
+				return $res_h->{data};
 			}
 
 			my $interval_start = DateTime->new(
diff --git a/lib/Travelynx/Controller/Account.pm b/lib/Travelynx/Controller/Account.pm
index 8626dc8..7753493 100644
--- a/lib/Travelynx/Controller/Account.pm
+++ b/lib/Travelynx/Controller/Account.pm
@@ -182,8 +182,6 @@ sub delete {
 		return;
 	}
 
-	my $now = DateTime->now( time_zone => 'Europe/Berlin' )->epoch;
-
 	if ( $self->param('action') eq 'delete' ) {
 		if (
 			not $self->authenticate(
@@ -195,12 +193,10 @@ sub delete {
 			$self->render( 'account', invalid => 'password' );
 			return;
 		}
-		$self->app->mark_for_deletion_query->execute( $now,
-			$self->current_user->{id} );
+		$self->flag_user_deletion( $self->current_user->{id} );
 	}
 	else {
-		$self->app->mark_for_deletion_query->execute( undef,
-			$self->current_user->{id} );
+		$self->unflag_user_deletion( $self->current_user->{id} );
 	}
 	$self->redirect_to('account');
 }
-- 
cgit v1.2.3