summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
authorDaniel Friesel <derf@finalrewind.org>2019-03-25 17:04:39 +0100
committerDaniel Friesel <derf@finalrewind.org>2019-03-25 17:04:39 +0100
commit632f7319403a9752600a6aac1824dedcc4b597fc (patch)
tree7050dd4916a2a32bb10b931a835a33bcc95a4a03 /lib
parentd114a6248e8fcb13bdad39e7d89d7f2066bb3f7d (diff)
Migrate to postgresql
Diffstat (limited to 'lib')
-rwxr-xr-xlib/Travelynx.pm43
-rw-r--r--lib/Travelynx/Controller/Account.pm2
2 files changed, 26 insertions, 19 deletions
diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index e9d095b..369dfce 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -165,7 +165,7 @@ sub startup {
insert into users (
name, status, public_level, email, token, password,
registered_at, last_login
- ) values (?, 0, 0, ?, ?, ?, ?, ?);
+ ) values (?, 0, 0, ?, ?, ?, to_timestamp(?), to_timestamp(?));
}
);
}
@@ -200,7 +200,7 @@ sub startup {
qq{
insert into pending_mails (
email, num_tries, last_try
- ) values (?, ?, ?);
+ ) values (?, ?, to_timestamp(?));
}
);
}
@@ -222,7 +222,7 @@ sub startup {
return $self->app->dbh->prepare(
qq{
- update users set deletion_requested = ? where id = ?;
+ update users set deletion_requested = to_timestamp(?) where id = ?;
}
);
}
@@ -239,9 +239,9 @@ sub startup {
sched_time, real_time,
route, messages
) values (
+ ?, ?, ?, to_timestamp(?),
?, ?, ?, ?,
- ?, ?, ?, ?,
- ?, ?,
+ to_timestamp(?), to_timestamp(?),
?, ?
)
}
@@ -252,10 +252,15 @@ sub startup {
dbh => sub {
my ($self) = @_;
- my $dbname = $ENV{TRAVELYNX_DB_FILE} // 'travelynx.sqlite';
+ my $dbname = $ENV{TRAVELYNX_DB_NAME} // 'travelynx_dev';
+ my $host = $ENV{TRAVELYNX_DB_HOST} // 'localhost';
+ my $port = $ENV{TRAVELYNX_DB_PORT} // '5432';
+ my $user = $ENV{TRAVELYNX_DB_USER};
+ my $pw = $ENV{TRAVELYNX_DB_PASSWORD};
- return DBI->connect( "dbi:SQLite:dbname=${dbname}", undef, undef,
- { AutoCommit => 1 } );
+ return DBI->connect(
+ "dbi:Pg:dbname=${dbname};host=${host};port=${port}",
+ $user, $pw, { AutoCommit => 1 } );
}
);
$self->attr(
@@ -264,9 +269,9 @@ sub startup {
return $self->app->dbh->prepare(
qq{
- select action_id, action_time, stations.ds100, stations.name,
+ select action_id, extract(epoch from action_time), stations.ds100, stations.name,
train_type, train_line, train_no, train_id,
- sched_time, real_time,
+ extract(epoch from sched_time), extract(epoch from real_time),
route, messages
from user_actions
left outer join stations on station_id = stations.id
@@ -282,9 +287,9 @@ sub startup {
return $self->app->dbh->prepare(
qq{
- select action_id, action_time, stations.ds100, stations.name,
+ select action_id, extract(epoch from action_time), stations.ds100, stations.name,
train_type, train_line, train_no, train_id,
- sched_time, real_time,
+ extract(epoch from sched_time), extract(epoch from real_time),
route, messages
from user_actions
left outer join stations on station_id = stations.id
@@ -301,14 +306,14 @@ sub startup {
return $self->app->dbh->prepare(
qq{
- select action_id, action_time, stations.ds100, stations.name,
+ select action_id, extract(epoch from action_time), stations.ds100, stations.name,
train_type, train_line, train_no, train_id,
- sched_time, real_time,
+ extract(epoch from sched_time), extract(epoch from real_time),
route, messages
from user_actions
left outer join stations on station_id = stations.id
where user_id = ?
- and (action_time = ? or action_time = ?)
+ and (action_time = to_timestamp(?) or action_time = to_timestamp(?))
order by action_time desc
limit 2
}
@@ -348,7 +353,7 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
qq{
select
id, name, status, public_level, email,
- registered_at, last_login, deletion_requested
+ extract(epoch from registered_at), extract(epoch from last_login), extract(epoch from deletion_requested)
from users where id = ?
}
);
@@ -397,10 +402,12 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
return $self->app->dbh->prepare(
qq{
- insert or replace into tokens
+ insert into tokens
(user_id, type, token)
values
(?, ?, ?)
+ on conflict (user_id, type)
+ do update set token = EXCLUDED.token
}
);
}
@@ -456,7 +463,7 @@ qq{select * from pending_mails where email = ? and num_tries > 1;}
insert into user_actions (
user_id, action_id, action_time
) values (
- ?, $self->app->action_type->{undo}, ?
+ ?, $self->app->action_type->{undo}, to_timestamp(?)
)
}
);
diff --git a/lib/Travelynx/Controller/Account.pm b/lib/Travelynx/Controller/Account.pm
index a832727..8b59bb5 100644
--- a/lib/Travelynx/Controller/Account.pm
+++ b/lib/Travelynx/Controller/Account.pm
@@ -2,7 +2,7 @@ package Travelynx::Controller::Account;
use Mojo::Base 'Mojolicious::Controller';
use Crypt::Eksblowfish::Bcrypt qw(bcrypt en_base64);
-use Encode qw(decode);
+use Encode qw(decode encode);
use Email::Sender::Simple qw(try_to_sendmail);
use Email::Simple;
use UUID::Tiny qw(:std);