summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDaniel Friesel <derf@finalrewind.org>2020-07-27 18:53:22 +0200
committerDaniel Friesel <derf@finalrewind.org>2020-07-27 18:53:22 +0200
commitf08bdaca5cafc6840cbf8489d7790656bf38f9e4 (patch)
treee0fa4c3c68f19f600f4febe7a54ede46c25a07d2
parentcdb7469f00258aac6fb96b93b2cea2780a30d06e (diff)
Move user model to a separate module
-rwxr-xr-xlib/Travelynx.pm413
-rw-r--r--lib/Travelynx/Controller/Account.pm112
-rwxr-xr-xlib/Travelynx/Controller/Traveling.pm8
-rw-r--r--lib/Travelynx/Model/Users.pm434
-rw-r--r--templates/account.html.ep2
5 files changed, 534 insertions, 435 deletions
diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index 285cfef..378a2ca 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -19,6 +19,7 @@ use Travel::Status::DE::DBWagenreihung;
use Travel::Status::DE::IRIS;
use Travel::Status::DE::IRIS::Stations;
use Travelynx::Helper::Sendmail;
+use Travelynx::Model::Users;
use XML::LibXML;
sub check_password {
@@ -93,7 +94,8 @@ sub startup {
},
validate_user => sub {
my ( $self, $username, $password, $extradata ) = @_;
- my $user_info = $self->get_user_password($username);
+ my $user_info
+ = $self->users->get_login_data( name => $username );
if ( not $user_info ) {
return undef;
}
@@ -271,6 +273,13 @@ sub startup {
);
$self->helper(
+ users => sub {
+ my ($self) = @_;
+ state $users = Travelynx::Model::Users->new( pg => $self->pg );
+ }
+ );
+
+ $self->helper(
pg => sub {
my ($self) = @_;
my $config = $self->app->config;
@@ -919,18 +928,6 @@ sub startup {
);
$self->helper(
- 'mark_seen' => sub {
- my ( $self, $uid ) = @_;
-
- $self->pg->db->update(
- 'users',
- { last_seen => DateTime->now( time_zone => 'Europe/Berlin' ) },
- { id => $uid }
- );
- }
- );
-
- $self->helper(
'update_in_transit_comment' => sub {
my ( $self, $comment, $uid ) = @_;
$uid //= $self->current_user->{id};
@@ -1151,197 +1148,6 @@ sub startup {
}
);
- $self->helper(
- 'verify_registration_token' => sub {
- my ( $self, $uid, $token ) = @_;
-
- my $db = $self->pg->db;
- my $tx = $db->begin;
-
- my $res = $db->select(
- 'pending_registrations',
- 'count(*) as count',
- {
- user_id => $uid,
- token => $token
- }
- );
-
- if ( $res->hash->{count} ) {
- $db->update( 'users', { status => 1 }, { id => $uid } );
- $db->delete( 'pending_registrations', { user_id => $uid } );
- $tx->commit;
- return 1;
- }
- return;
- }
- );
-
- $self->helper(
- 'get_uid_by_name_and_mail' => sub {
- my ( $self, $name, $email ) = @_;
-
- my $res = $self->pg->db->select(
- 'users',
- ['id'],
- {
- name => $name,
- email => $email,
- status => 1
- }
- );
-
- if ( my $user = $res->hash ) {
- return $user->{id};
- }
- return;
- }
- );
-
- $self->helper(
- 'get_privacy_by_name' => sub {
- my ( $self, $name ) = @_;
-
- my $res = $self->pg->db->select(
- 'users',
- [ 'id', 'public_level' ],
- {
- name => $name,
- status => 1
- }
- );
-
- if ( my $user = $res->hash ) {
- return $user;
- }
- return;
- }
- );
-
- $self->helper(
- 'set_privacy' => sub {
- my ( $self, $uid, $public_level ) = @_;
-
- $self->pg->db->update(
- 'users',
- { public_level => $public_level },
- { id => $uid }
- );
- }
- );
-
- $self->helper(
- 'mark_for_password_reset' => sub {
- my ( $self, $db, $uid, $token ) = @_;
-
- my $res = $db->select(
- 'pending_passwords',
- 'count(*) as count',
- { user_id => $uid }
- );
- if ( $res->hash->{count} ) {
- return 'in progress';
- }
-
- $db->insert(
- 'pending_passwords',
- {
- user_id => $uid,
- token => $token,
- requested_at =>
- DateTime->now( time_zone => 'Europe/Berlin' )
- }
- );
-
- return undef;
- }
- );
-
- $self->helper(
- 'verify_password_token' => sub {
- my ( $self, $uid, $token ) = @_;
-
- my $res = $self->pg->db->select(
- 'pending_passwords',
- 'count(*) as count',
- {
- user_id => $uid,
- token => $token
- }
- );
-
- if ( $res->hash->{count} ) {
- return 1;
- }
- return;
- }
- );
-
- $self->helper(
- 'mark_for_mail_change' => sub {
- my ( $self, $db, $uid, $email, $token ) = @_;
-
- $db->insert(
- 'pending_mails',
- {
- user_id => $uid,
- email => $email,
- token => $token,
- requested_at =>
- DateTime->now( time_zone => 'Europe/Berlin' )
- },
- {
- on_conflict => \
-'(user_id) do update set email = EXCLUDED.email, token = EXCLUDED.token, requested_at = EXCLUDED.requested_at'
- },
- );
- }
- );
-
- $self->helper(
- 'change_mail_with_token' => sub {
- my ( $self, $uid, $token ) = @_;
-
- my $db = $self->pg->db;
- my $tx = $db->begin;
-
- my $res_h = $db->select(
- 'pending_mails',
- ['email'],
- {
- user_id => $uid,
- token => $token
- }
- )->hash;
-
- if ($res_h) {
- $db->update(
- 'users',
- { email => $res_h->{email} },
- { id => $uid }
- );
- $db->delete( 'pending_mails', { user_id => $uid } );
- $tx->commit;
- return 1;
- }
- return;
- }
- );
-
- $self->helper(
- 'remove_password_token' => sub {
- my ( $self, $uid, $token ) = @_;
-
- $self->pg->db->delete(
- 'pending_passwords',
- {
- user_id => $uid,
- token => $token
- }
- );
- }
- );
-
# This helper should only be called directly when also providing a user ID.
# If you don't have one, use current_user() instead (get_user_data will
# delegate to it anyways).
@@ -1351,39 +1157,7 @@ sub startup {
$uid //= $self->current_user->{id};
- 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_seen) as last_seen_ts, '
- . 'extract(epoch from deletion_requested) as deletion_requested_ts',
- { id => $uid }
- )->hash;
-
- if ($user_data) {
- return {
- 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 => $user_data->{registered_at_ts},
- time_zone => 'Europe/Berlin'
- ),
- last_seen => DateTime->from_epoch(
- epoch => $user_data->{last_seen_ts},
- time_zone => 'Europe/Berlin'
- ),
- deletion_requested => $user_data->{deletion_requested_ts}
- ? DateTime->from_epoch(
- epoch => $user_data->{deletion_requested_ts},
- time_zone => 'Europe/Berlin'
- )
- : undef,
- };
- }
- return undef;
+ return $self->users->get_data( uid => $uid );
}
);
@@ -1535,153 +1309,6 @@ sub startup {
);
$self->helper(
- 'get_user_password' => sub {
- my ( $self, $name ) = @_;
-
- my $res_h = $self->pg->db->select(
- 'users',
- 'id, name, status, password as password_hash',
- { name => $name }
- )->hash;
-
- return $res_h;
- }
- );
-
- $self->helper(
- 'add_user' => sub {
- 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 = $db->insert(
- 'users',
- {
- name => $user_name,
- status => 0,
- public_level => 0,
- email => $email,
- password => $password,
- registered_at => $now,
- last_seen => $now,
- },
- { returning => 'id' }
- );
- my $uid = $res->hash->{id};
-
- $db->insert(
- 'pending_registrations',
- {
- user_id => $uid,
- token => $token
- }
- );
-
- return $uid;
- }
- );
-
- $self->helper(
- 'flag_user_deletion' => sub {
- my ( $self, $uid ) = @_;
-
- my $now = DateTime->now( time_zone => 'Europe/Berlin' );
-
- $self->pg->db->update(
- 'users',
- { deletion_requested => $now },
- {
- id => $uid,
- }
- );
- }
- );
-
- $self->helper(
- 'unflag_user_deletion' => sub {
- my ( $self, $uid ) = @_;
-
- $self->pg->db->update(
- 'users',
- {
- deletion_requested => undef,
- },
- {
- id => $uid,
- }
- );
- }
- );
-
- $self->helper(
- 'set_user_password' => sub {
- my ( $self, $uid, $password ) = @_;
-
- $self->pg->db->update(
- 'users',
- { password => $password },
- { id => $uid }
- );
- }
- );
-
- $self->helper(
- 'check_if_user_name_exists' => sub {
- my ( $self, $user_name ) = @_;
-
- my $count = $self->pg->db->select(
- 'users',
- 'count(*) as count',
- { name => $user_name }
- )->hash->{count};
-
- if ($count) {
- return 1;
- }
- return 0;
- }
- );
-
- $self->helper(
- 'check_if_mail_is_blacklisted' => sub {
- my ( $self, $mail ) = @_;
-
- my $count = $self->pg->db->select(
- 'users',
- 'count(*) as count',
- {
- email => $mail,
- status => 0,
- }
- )->hash->{count};
-
- if ($count) {
- return 1;
- }
-
- $count = $self->pg->db->select(
- 'mail_blacklist',
- 'count(*) as count',
- {
- email => $mail,
- num_tries => { '>', 1 },
- }
- )->hash->{count};
-
- if ($count) {
- return 1;
- }
- return 0;
- }
- );
-
- $self->helper(
'delete_journey' => sub {
my ( $self, $journey_id, $checkin_epoch, $checkout_epoch ) = @_;
my $uid = $self->current_user->{id};
@@ -2911,24 +2538,6 @@ sub startup {
);
$self->helper(
- 'account_use_history' => sub {
- my ( $self, $uid, $value ) = @_;
-
- if ($value) {
- $self->pg->db->update(
- 'users',
- { use_history => $value },
- { id => $uid }
- );
- }
- else {
- return $self->pg->db->select( 'users', ['use_history'],
- { id => $uid } )->hash->{use_history};
- }
- }
- );
-
- $self->helper(
'get_user_travels' => sub {
my ( $self, %opt ) = @_;
diff --git a/lib/Travelynx/Controller/Account.pm b/lib/Travelynx/Controller/Account.pm
index 135c52b..10200a6 100644
--- a/lib/Travelynx/Controller/Account.pm
+++ b/lib/Travelynx/Controller/Account.pm
@@ -7,7 +7,7 @@ use UUID::Tiny qw(:std);
sub hash_password {
my ($password) = @_;
my @salt_bytes = map { int( rand(255) ) + 1 } ( 1 .. 16 );
- my $salt = en_base64( pack( 'C[16]', @salt_bytes ) );
+ my $salt = en_base64( pack( 'C[16]', @salt_bytes ) );
return bcrypt( $password, '$2a$12$' . $salt );
}
@@ -38,10 +38,10 @@ sub do_login {
else {
if ( $self->authenticate( $user, $password ) ) {
$self->redirect_to( $self->req->param('redirect_to') // '/' );
- $self->mark_seen( $self->current_user->{id} );
+ $self->users->mark_seen( uid => $self->current_user->{id} );
}
else {
- my $data = $self->get_user_password($user);
+ my $data = $self->users->get_login_data( name => $user );
if ( $data and $data->{status} == 0 ) {
$self->render( 'login', invalid => 'confirmation' );
}
@@ -95,12 +95,12 @@ sub register {
return;
}
- if ( $self->check_if_user_name_exists($user) ) {
+ if ( $self->users->check_if_user_name_exists( name => $user ) ) {
$self->render( 'register', invalid => 'user_collision' );
return;
}
- if ( $self->check_if_mail_is_blacklisted($email) ) {
+ if ( $self->users->check_if_mail_is_blacklisted( email => $email ) ) {
$self->render( 'register', invalid => 'mail_blacklisted' );
return;
}
@@ -115,11 +115,17 @@ sub register {
return;
}
- 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 $token = make_token();
+ my $pw_hash = hash_password($password);
+ my $db = $self->pg->db;
+ my $tx = $db->begin;
+ my $user_id = $self->users->add_user(
+ db => $db,
+ name => $user,
+ email => $email,
+ token => $token,
+ password_hash => $pw_hash
+ );
my $reg_url = $self->url_for('reg')->to_abs->scheme('https');
my $imprint_url = $self->url_for('impressum')->to_abs->scheme('https');
@@ -164,7 +170,13 @@ sub verify {
return;
}
- if ( not $self->verify_registration_token( $id, $token ) ) {
+ if (
+ not $self->users->verify_registration_token(
+ uid => $id,
+ token => $token
+ )
+ )
+ {
$self->render( 'register', invalid => 'token' );
return;
}
@@ -190,10 +202,10 @@ sub delete {
$self->render( 'account', invalid => 'deletion password' );
return;
}
- $self->flag_user_deletion( $self->current_user->{id} );
+ $self->users->flag_deletion( uid => $self->current_user->{id} );
}
else {
- $self->unflag_user_deletion( $self->current_user->{id} );
+ $self->users->unflag_deletion( uid => $self->current_user->{id} );
}
$self->redirect_to('account');
}
@@ -249,7 +261,10 @@ sub privacy {
$public_level &= ~0x30;
}
- $self->set_privacy( $user->{id}, $public_level );
+ $self->users->set_privacy(
+ uid => $user->{id},
+ level => $public_level
+ );
$self->flash( success => 'privacy' );
$self->redirect_to('account');
@@ -274,7 +289,7 @@ sub insight {
my ($self) = @_;
my $user = $self->current_user;
- my $use_history = $self->account_use_history( $user->{id} );
+ my $use_history = $self->users->use_history( uid => $user->{id} );
if ( $self->param('action') and $self->param('action') eq 'save' ) {
if ( $self->param('on_departure') ) {
@@ -291,7 +306,10 @@ sub insight {
$use_history &= ~0x02;
}
- $self->account_use_history( $user->{id}, $use_history );
+ $self->users->use_history(
+ uid => $user->{id},
+ set => $use_history
+ );
$self->flash( success => 'use_history' );
$self->redirect_to('account');
}
@@ -375,8 +393,12 @@ sub change_mail {
my $db = $self->pg->db;
my $tx = $db->begin;
- $self->mark_for_mail_change( $db, $self->current_user->{id},
- $email, $token );
+ $self->users->mark_for_mail_change(
+ db => $db,
+ uid => $self->current_user->{id},
+ email => $email,
+ token => $token
+ );
my $ip = $self->req->headers->header('X-Forwarded-For');
my $ua = $self->req->headers->user_agent;
@@ -459,7 +481,10 @@ sub change_password {
}
my $pw_hash = hash_password($password);
- $self->set_user_password( $self->current_user->{id}, $pw_hash );
+ $self->users->set_password_hash(
+ uid => $self->current_user->{id},
+ password_hash => $pw_hash
+ );
$self->flash( success => 'password' );
$self->redirect_to('account');
@@ -500,7 +525,10 @@ sub request_password_reset {
my $name = $self->param('user');
my $email = $self->param('email');
- my $uid = $self->get_uid_by_name_and_mail( $name, $email );
+ my $uid = $self->users->get_uid_by_name_and_mail(
+ name => $name,
+ email => $email
+ );
if ( not $uid ) {
$self->render( 'recover_password',
@@ -512,7 +540,11 @@ sub request_password_reset {
my $db = $self->pg->db;
my $tx = $db->begin;
- my $error = $self->mark_for_password_reset( $db, $uid, $token );
+ my $error = $self->users->mark_for_password_reset(
+ db => $db,
+ uid => $uid,
+ token => $token
+ );
if ($error) {
$self->render( 'recover_password', invalid => $error );
@@ -570,7 +602,13 @@ sub request_password_reset {
$self->render( 'set_password', invalid => 'csrf' );
return;
}
- if ( not $self->verify_password_token( $id, $token ) ) {
+ if (
+ not $self->users->verify_password_token(
+ uid => $id,
+ token => $token
+ )
+ )
+ {
$self->render( 'recover_password', invalid => 'change token' );
return;
}
@@ -585,7 +623,10 @@ sub request_password_reset {
}
my $pw_hash = hash_password($password);
- $self->set_user_password( $id, $pw_hash );
+ $self->users->set_password_hash(
+ uid => $id,
+ password_hash => $pw_hash
+ );
my $account = $self->get_user_data($id);
@@ -597,7 +638,10 @@ sub request_password_reset {
$self->flash( success => 'password' );
$self->redirect_to('account');
- $self->remove_password_token( $id, $token );
+ $self->users->remove_password_token(
+ uid => $id,
+ token => $token
+ );
my $user = $account->{name};
my $email = $account->{email};
@@ -641,7 +685,13 @@ sub recover_password {
return;
}
- if ( $self->verify_password_token( $id, $token ) ) {
+ if (
+ $self->users->verify_password_token(
+ uid => $id,
+ token => $token
+ )
+ )
+ {
$self->render('set_password');
}
else {
@@ -654,7 +704,13 @@ sub confirm_mail {
my $id = $self->current_user->{id};
my $token = $self->stash('token');
- if ( $self->change_mail_with_token( $id, $token ) ) {
+ if (
+ $self->users->change_mail_with_token(
+ uid => $id,
+ token => $token
+ )
+ )
+ {
$self->flash( success => 'mail' );
$self->redirect_to('account');
}
@@ -667,7 +723,7 @@ sub account {
my ($self) = @_;
$self->render('account');
- $self->mark_seen( $self->current_user->{id} );
+ $self->users->mark_seen( uid => $self->current_user->{id} );
}
sub json_export {
@@ -678,7 +734,7 @@ sub json_export {
$self->render(
json => {
- account => $db->select( 'users', '*', { id => $uid } )->hash,
+ account => $db->select( 'users', '*', { id => $uid } )->hash,
in_transit => [
$db->select( 'in_transit_str', '*', { user_id => $uid } )
->hashes->each
diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm
index 702a89e..05bbccd 100755
--- a/lib/Travelynx/Controller/Traveling.pm
+++ b/lib/Travelynx/Controller/Traveling.pm
@@ -19,7 +19,7 @@ sub homepage {
with_autocomplete => 1,
with_geolocation => 1
);
- $self->mark_seen( $self->current_user->{id} );
+ $self->users->mark_seen( uid => $self->current_user->{id} );
}
else {
$self->render(
@@ -35,7 +35,7 @@ sub user_status {
my $name = $self->stash('name');
my $ts = $self->stash('ts') // 0;
- my $user = $self->get_privacy_by_name($name);
+ my $user = $self->users->get_privacy_by_name( name => $name );
if ( not $user or not $user->{public_level} & 0x03 ) {
$self->render('not_found');
@@ -150,7 +150,7 @@ sub public_status_card {
my ($self) = @_;
my $name = $self->stash('name');
- my $user = $self->get_privacy_by_name($name);
+ my $user = $self->users->get_privacy_by_name( name => $name );
delete $self->stash->{layout};
@@ -457,7 +457,7 @@ sub station {
title => "travelynx: $status->{station_name}",
);
}
- $self->mark_seen( $self->current_user->{id} );
+ $self->users->mark_seen( uid => $self->current_user->{id} );
}
sub redirect_to_station {
diff --git a/lib/Travelynx/Model/Users.pm b/lib/Travelynx/Model/Users.pm
new file mode 100644
index 0000000..830ec63
--- /dev/null
+++ b/lib/Travelynx/Model/Users.pm
@@ -0,0 +1,434 @@
+package Travelynx::Model::Users;
+
+use strict;
+use warnings;
+use 5.020;
+
+use DateTime;
+
+sub new {
+ my ( $class, %opt ) = @_;
+
+ return bless( \%opt, $class );
+}
+
+sub mark_seen {
+ my ($self, %opt) = @_;
+ my $uid = $opt{uid};
+ my $db = $opt{db} // $self->{pg}->db;
+
+ $db->update(
+ 'users',
+ { last_seen => DateTime->now( time_zone => 'Europe/Berlin' ) },
+ { id => $uid }
+ );
+}
+
+sub verify_registration_token {
+ my ( $self, %opt ) = @_;
+ my $uid = $opt{uid};
+ my $token = $opt{token};
+ my $db = $opt{db} // $self->{pg}->db;
+
+ my $tx = $db->begin;
+
+ my $res = $db->select(
+ 'pending_registrations',
+ 'count(*) as count',
+ {
+ user_id => $uid,
+ token => $token
+ }
+ );
+
+ if ( $res->hash->{count} ) {
+ $db->update( 'users', { status => 1 }, { id => $uid } );
+ $db->delete( 'pending_registrations', { user_id => $uid } );
+ $tx->commit;
+ return 1;
+ }
+ return;
+}
+
+sub get_uid_by_name_and_mail {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $name = $opt{name};
+ my $email = $opt{email};
+
+ my $res = $db->select(
+ 'users',
+ ['id'],
+ {
+ name => $name,
+ email => $email,
+ status => 1
+ }
+ );
+
+ if ( my $user = $res->hash ) {
+ return $user->{id};
+ }
+ return;
+}
+
+sub get_privacy_by_name {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $name = $opt{name};
+
+ my $res = $db->select(
+ 'users',
+ [ 'id', 'public_level' ],
+ {
+ name => $name,
+ status => 1
+ }
+ );
+
+ if ( my $user = $res->hash ) {
+ return $user;
+ }
+ return;
+}
+
+sub set_privacy {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $public_level = $opt{level};
+
+ $db->update(
+ 'users',
+ { public_level => $public_level },
+ { id => $uid }
+ );
+}
+
+sub mark_for_password_reset {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $token = $opt{token};
+
+ my $res = $db->select(
+ 'pending_passwords',
+ 'count(*) as count',
+ { user_id => $uid }
+ );
+ if ( $res->hash->{count} ) {
+ return 'in progress';
+ }
+
+ $db->insert(
+ 'pending_passwords',
+ {
+ user_id => $uid,
+ token => $token,
+ requested_at =>
+ DateTime->now( time_zone => 'Europe/Berlin' )
+ }
+ );
+
+ return undef;
+}
+
+sub verify_password_token {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $token = $opt{token};
+
+ my $res = $db->select(
+ 'pending_passwords',
+ 'count(*) as count',
+ {
+ user_id => $uid,
+ token => $token
+ }
+ );
+
+ if ( $res->hash->{count} ) {
+ return 1;
+ }
+ return;
+}
+
+sub mark_for_mail_change {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $email = $opt{email};
+ my $token = $opt{token};
+
+ $db->insert(
+ 'pending_mails',
+ {
+ user_id => $uid,
+ email => $email,
+ token => $token,
+ requested_at =>
+ DateTime->now( time_zone => 'Europe/Berlin' )
+ },
+ {
+ on_conflict => \
+'(user_id) do update set email = EXCLUDED.email, token = EXCLUDED.token, requested_at = EXCLUDED.requested_at'
+ },
+ );
+}
+
+sub change_mail_with_token {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $token = $opt{token};
+
+ my $tx = $db->begin;
+
+ my $res_h = $db->select(
+ 'pending_mails',
+ ['email'],
+ {
+ user_id => $uid,
+ token => $token
+ }
+ )->hash;
+
+ if ($res_h) {
+ $db->update(
+ 'users',
+ { email => $res_h->{email} },
+ { id => $uid }
+ );
+ $db->delete( 'pending_mails', { user_id => $uid } );
+ $tx->commit;
+ return 1;
+ }
+ return;
+}
+
+sub remove_password_token {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $token = $opt{token};
+
+ $db->delete(
+ 'pending_passwords',
+ {
+ user_id => $uid,
+ token => $token
+ }
+ );
+}
+
+sub get_data {
+ my ($self, %opt) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+
+ my $user = $db->select(
+ 'users',
+ 'id, name, status, public_level, email, '
+ . 'extract(epoch from registered_at) as registered_at_ts, '
+ . 'extract(epoch from last_seen) as last_seen_ts, '
+ . 'extract(epoch from deletion_requested) as deletion_requested_ts',
+ { id => $uid }
+ )->hash;
+ if ($user) {
+ return {
+ id => $user->{id},
+ name => $user->{name},
+ status => $user->{status},
+ is_public => $user->{public_level},
+ email => $user->{email},
+ registered_at => DateTime->from_epoch(
+ epoch => $user->{registered_at_ts},
+ time_zone => 'Europe/Berlin'
+ ),
+ last_seen => DateTime->from_epoch(
+ epoch => $user->{last_seen_ts},
+ time_zone => 'Europe/Berlin'
+ ),
+ deletion_requested => $user->{deletion_requested_ts}
+ ? DateTime->from_epoch(
+ epoch => $user->{deletion_requested_ts},
+ time_zone => 'Europe/Berlin'
+ )
+ : undef,
+ };
+ }
+ return undef;
+}
+
+sub get_login_data {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $name = $opt{name};
+
+ my $res_h = $db->select(
+ 'users',
+ 'id, name, status, password as password_hash',
+ { name => $name }
+ )->hash;
+
+ return $res_h;
+}
+
+sub add_user {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $user_name = $opt{name};
+ my $email = $opt{email};
+ my $token = $opt{token};
+ my $password = $opt{password_hash};
+
+ # 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 = $db->insert(
+ 'users',
+ {
+ name => $user_name,
+ status => 0,
+ public_level => 0,
+ email => $email,
+ password => $password,
+ registered_at => $now,
+ last_seen => $now,
+ },
+ { returning => 'id' }
+ );
+ my $uid = $res->hash->{id};
+
+ $db->insert(
+ 'pending_registrations',
+ {
+ user_id => $uid,
+ token => $token
+ }
+ );
+
+ return $uid;
+}
+
+sub flag_deletion {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+
+ my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+
+ $db->update(
+ 'users',
+ { deletion_requested => $now },
+ {
+ id => $uid,
+ }
+ );
+}
+
+sub unflag_deletion {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+
+ $db->update(
+ 'users',
+ {
+ deletion_requested => undef,
+ },
+ {
+ id => $uid,
+ }
+ );
+}
+
+sub set_password_hash {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $password = $opt{password_hash};
+
+ $db->update(
+ 'users',
+ { password => $password },
+ { id => $uid }
+ );
+}
+
+sub check_if_user_name_exists {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $user_name = $opt{name};
+
+ my $count = $db->select(
+ 'users',
+ 'count(*) as count',
+ { name => $user_name }
+ )->hash->{count};
+
+ if ($count) {
+ return 1;
+ }
+ return 0;
+}
+
+sub check_if_mail_is_blacklisted {
+ my ( $self, %opt ) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $mail = $opt{email};
+
+ my $count = $db->select(
+ 'users',
+ 'count(*) as count',
+ {
+ email => $mail,
+ status => 0,
+ }
+ )->hash->{count};
+
+ if ($count) {
+ return 1;
+ }
+
+ $count = $db->select(
+ 'mail_blacklist',
+ 'count(*) as count',
+ {
+ email => $mail,
+ num_tries => { '>', 1 },
+ }
+ )->hash->{count};
+
+ if ($count) {
+ return 1;
+ }
+ return 0;
+}
+
+sub use_history {
+ my ($self, %opt) = @_;
+ my $db = $opt{db} // $self->{pg}->db;
+ my $uid = $opt{uid};
+ my $value = $opt{set};
+
+ if ($value) {
+ $db->update(
+ 'users',
+ { use_history => $value },
+ { id => $uid }
+ );
+ }
+ else {
+ return $db->select( 'users', ['use_history'],
+ { id => $uid } )->hash->{use_history};
+ }
+}
+
+1;
diff --git a/templates/account.html.ep b/templates/account.html.ep
index efe2ee7..8df5008 100644
--- a/templates/account.html.ep
+++ b/templates/account.html.ep
@@ -31,7 +31,7 @@
<h1>Account</h1>
% my $acc = current_user();
% my $hook = get_webhook();
-% my $use_history = account_use_history($acc->{id});
+% my $use_history = users->use_history(uid => $acc->{id});
<div class="row">
<div class="col s12">
<table class="striped">