Use Mojo::Pg for all remaining Controller and Helper logic

4 files changed, 65 insertions, 94 deletions

diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index 91d2bef..576e715 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -164,59 +164,6 @@ sub startup {
 				$user, $pw, { AutoCommit => 1 } );
 		}
 	);
-	$self->attr(
-		get_api_tokens_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			select
-				type, token
-			from tokens where user_id = ?
-		}
-			);
-		}
-	);
-	$self->attr(
-		get_api_token_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			select
-				token
-			from tokens where user_id = ? and type = ?
-		}
-			);
-		}
-	);
-	$self->attr(
-		drop_api_token_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			delete from tokens where user_id = ? and type = ?
-		}
-			);
-		}
-	);
-	$self->attr(
-		set_api_token_query => sub {
-			my ($self) = @_;
-
-			return $self->app->dbh->prepare(
-				qq{
-			insert into tokens
-				(user_id, type, token)
-			values
-				(?, ?, ?)
-			on conflict (user_id, type)
-			do update set token = EXCLUDED.token
-		}
-			);
-		}
-	);
 
 	$self->helper(
 		sendmail => sub {
@@ -649,11 +596,10 @@ sub startup {
 
 	$self->helper(
 		'update_journey_part' => sub {
-			my ( $self, $checkin_id, $checkout_id, $key, $value ) = @_;
+			my ( $self, $db, $checkin_id, $checkout_id, $key, $value ) = @_;
 			my $rows;
 
 			eval {
-				my $db = $self->pg->db;
 				if ( $key eq 'sched_departure' ) {
 					$rows = $db->update(
 						'user_actions',
@@ -844,13 +790,19 @@ sub startup {
 		'get_api_token' => sub {
 			my ( $self, $uid ) = @_;
 			$uid //= $self->current_user->{id};
-			$self->app->get_api_tokens_query->execute($uid);
-			my $rows  = $self->app->get_api_tokens_query->fetchall_arrayref;
+
 			my $token = {};
-			for my $row ( @{$rows} ) {
-				$token->{ $self->app->token_types->[ $row->[0] - 1 ] }
-				  = $row->[1];
+			my $res   = $self->pg->db->select(
+				'tokens',
+				[ 'type', 'token' ],
+				{ user_id => $uid }
+			);
+
+			for my $entry ( $res->hashes->each ) {
+				$token->{ $self->app->token_types->[ $entry->{type} - 1 ] }
+				  = $entry->{token};
 			}
+
 			return $token;
 		}
 	);
@@ -871,11 +823,17 @@ sub startup {
 
 	$self->helper(
 		'add_user' => sub {
-			my ( $self, $user_name, $email, $token, $password ) = @_;
+			my ( $self, $db, $user_name, $email, $token, $password ) = @_;
+
+          # This helper must be called during a transaction, as user creation
+          # may fail even after the database entry has been generated, e.g.  if
+          # the registration mail cannot be sent. We therefore use $db (the
+          # database handle performing the transaction) instead of $self->pg->db
+          # (which may be a new handle not belonging to the transaction).
 
 			my $now = DateTime->now( time_zone => 'Europe/Berlin' );
 
-			my $res = $self->pg->db->insert(
+			my $res = $db->insert(
 				'users',
 				{
 					name          => $user_name,
@@ -1130,6 +1088,11 @@ sub startup {
 
 			my $uid = $opt{uid} || $self->current_user->{id};
 
+			# If get_user_travels is called from inside a transaction, db
+			# specifies the database handle performing the transaction.
+			# Otherwise, we grab a fresh one.
+			my $db = $opt{db} // $self->pg->db;
+
 			my $selection = qq{
 			user_actions.id as action_log_id, action_id,
 			extract(epoch from action_time) as action_time_ts,
@@ -1152,7 +1115,7 @@ sub startup {
 			}
 
 			if ( $opt{checkout_id} ) {
-				$where{action_log_id} = { '<=', $opt{checkout_id} };
+				$where{'user_actions.id'} = { '<=', $opt{checkout_id} };
 				$order{limit} = 2;
 			}
 			elsif ( $opt{after} and $opt{before} ) {
@@ -1198,7 +1161,7 @@ sub startup {
 			my @travels;
 			my $prev_action = 0;
 
-			my $res = $self->pg->db->select(
+			my $res = $db->select(
 				[
 					'user_actions',
 					[
diff --git a/lib/Travelynx/Controller/Account.pm b/lib/Travelynx/Controller/Account.pm
index 7753493..0037e16 100644
--- a/lib/Travelynx/Controller/Account.pm
+++ b/lib/Travelynx/Controller/Account.pm
@@ -114,10 +114,11 @@ sub register {
 		return;
 	}
 
-	my $token   = make_token();
-	my $pw_hash = hash_password($password);
-	$self->app->dbh->begin_work;
-	my $user_id     = $self->add_user( $user, $email, $token, $pw_hash );
+	my $token       = make_token();
+	my $pw_hash     = hash_password($password);
+	my $db          = $self->pg->db;
+	my $tx          = $db->begin;
+	my $user_id     = $self->add_user( $db, $user, $email, $token, $pw_hash );
 	my $reg_url     = $self->url_for('reg')->to_abs->scheme('https');
 	my $imprint_url = $self->url_for('impressum')->to_abs->scheme('https');
 
@@ -143,11 +144,10 @@ sub register {
 	my $success
 	  = $self->sendmail->custom( $email, 'Registrierung bei travelynx', $body );
 	if ($success) {
-		$self->app->dbh->commit;
+		$tx->commit;
 		$self->render( 'login', from => 'register' );
 	}
 	else {
-		$self->app->dbh->rollback;
 		$self->render( 'register', invalid => 'sendmail' );
 	}
 }
diff --git a/lib/Travelynx/Controller/Api.pm b/lib/Travelynx/Controller/Api.pm
index c3eccb8..a9500f1 100755
--- a/lib/Travelynx/Controller/Api.pm
+++ b/lib/Travelynx/Controller/Api.pm
@@ -106,12 +106,27 @@ sub set_token {
 	}
 
 	if ( $self->param('action') eq 'delete' ) {
-		$self->app->drop_api_token_query->execute( $self->current_user->{id},
-			$token_id );
+		$self->pg->db->delete(
+			'tokens',
+			{
+				user_id => $self->current_user->{id},
+				type    => $token_id
+			}
+		);
 	}
 	else {
-		$self->app->set_api_token_query->execute( $self->current_user->{id},
-			$token_id, $token );
+		$self->pg->db->insert(
+			'tokens',
+			{
+				user_id => $self->current_user->{id},
+				type    => $token_id,
+				token   => $token
+			},
+			{
+				on_conflict => \
+				  '(user_id, type) do update set token = EXCLUDED.token'
+			},
+		);
 	}
 	$self->redirect_to('account');
 }
diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm
index 43dd7f0..b43c891 100755
--- a/lib/Travelynx/Controller/Traveling.pm
+++ b/lib/Travelynx/Controller/Traveling.pm
@@ -461,13 +461,15 @@ sub edit_journey {
 			time_zone => 'Europe/Berlin'
 		);
 
-		$self->app->dbh->begin_work;
+		my $db = $self->pg->db;
+		my $tx = $db->begin;
 
 		for my $key (qw(sched_departure rt_departure sched_arrival rt_arrival))
 		{
 			my $datetime = $parser->parse_datetime( $self->param($key) );
 			if ( $datetime and $datetime->epoch ne $journey->{$key}->epoch ) {
 				$error = $self->update_journey_part(
+					$db,
 					$journey->{ids}[0],
 					$journey->{ids}[1],
 					$key, $datetime
@@ -478,27 +480,21 @@ sub edit_journey {
 			}
 		}
 
-		if ($error) {
-			$self->app->dbh->rollback;
-		}
-		else {
+		if ( not $error ) {
 			$journey = $self->get_journey(
 				uid         => $uid,
+				db          => $db,
 				checkout_id => $checkout_id,
 				verbose     => 1
 			);
 			$error = $self->journey_sanity_check($journey);
-			if ($error) {
-				$self->app->dbh->rollback;
-			}
-			else {
-				$self->invalidate_stats_cache( $journey->{checkout} );
-				$self->app->dbh->commit;
-				$self->redirect_to("/journey/${uid}-${checkout_id}");
-				return;
-			}
 		}
-
+		if ( not $error ) {
+			$tx->commit;
+			$self->redirect_to("/journey/${uid}-${checkout_id}");
+			$self->invalidate_stats_cache( $journey->{checkout} );
+			return;
+		}
 	}
 
 	for my $key (qw(sched_departure rt_departure sched_arrival rt_arrival)) {
@@ -566,15 +562,12 @@ sub add_journey_form {
 			$opt{$key} = $self->param($key);
 		}
 
-		$self->app->dbh->begin_work;
-
-		my ( $checkin_id, $checkout_id, $error ) = $self->add_journey(%opt);
+		#my ( $checkin_id, $checkout_id, $error ) = $self->add_journey(%opt);
 
-		$self->app->dbh->rollback;
 		$self->render(
 			'add_journey',
 			with_autocomplete => 1,
-			error             => $error
+			error             => 'not implemented',
 		);
 		return;
 	}