summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDaniel Friesel <derf@finalrewind.org>2019-03-04 18:17:03 +0100
committerDaniel Friesel <derf@finalrewind.org>2019-03-04 18:17:03 +0100
commit8adca327fd976b117ccfa4d69903ee24e4f9bca4 (patch)
treedda13d7cf370ab4bdc9e45029f6f391d81741b23
parent3096091f1130f36df61d0845f4e026cd970ce66c (diff)
prepare DB schema for public registration0.04
-rwxr-xr-xindex.pl43
-rwxr-xr-xmigrate.pl89
-rw-r--r--templates/account.html.ep7
3 files changed, 111 insertions, 28 deletions
diff --git a/index.pl b/index.pl
index 71ecd92..513cf29 100755
--- a/index.pl
+++ b/index.pl
@@ -178,6 +178,20 @@ app->attr(
}
);
app->attr(
+ get_user_query => sub {
+ my ($self) = @_;
+
+ return $self->app->dbh->prepare(
+ qq{
+ select
+ id, name, status, is_public, email,
+ registered_at, last_login, deletion_requested
+ from users where id = ?
+ }
+ );
+ }
+);
+app->attr(
get_stationid_by_ds100_query => sub {
my ($self) = @_;
@@ -445,6 +459,35 @@ helper 'get_station_id' => sub {
}
};
+helper 'get_user_data' => sub {
+ my ($self) = @_;
+
+ my $uid = $self->get_user_id;
+ my $query = $self->app->get_user_query;
+ $query->execute($uid);
+ my $rows = $query->fetchall_arrayref;
+ if ( @{$rows} ) {
+ my @row = @{ $rows->[0] };
+ return {
+ id => $row[0],
+ name => $row[1],
+ status => $row[2],
+ is_public => $row[3],
+ email => $row[4],
+ registered_at => DateTime->from_epoch(
+ epoch => $row[5],
+ time_zone => 'Europe/Berlin'
+ ),
+ last_seen => DateTime->from_epoch(
+ epoch => $row[6],
+ time_zone => 'Europe/Berlin'
+ ),
+ deletion_requested => $row[7]
+ };
+ }
+ return;
+};
+
helper 'get_user_name' => sub {
my ($self) = @_;
diff --git a/migrate.pl b/migrate.pl
index 84964bb..722eea4 100755
--- a/migrate.pl
+++ b/migrate.pl
@@ -4,26 +4,31 @@ use strict;
use warnings;
use 5.020;
+use DateTime;
use DBI;
my $dbname = $ENV{TRAVELYNX_DB_FILE} // 'travelynx.sqlite';
my $dbh = DBI->connect( "dbi:SQLite:dbname=${dbname}", q{}, q{} );
-my $has_version_query = $dbh->prepare(qq{
+my $has_version_query = $dbh->prepare(
+ qq{
select name from sqlite_master
where type = 'table' and name = 'schema_version';
-});
+}
+);
sub get_schema_version {
$has_version_query->execute();
my $rows = $has_version_query->fetchall_arrayref;
- if (@{$rows} == 1) {
- my $get_version_query = $dbh->prepare(qq{
+ if ( @{$rows} == 1 ) {
+ my $get_version_query = $dbh->prepare(
+ qq{
select version from schema_version;
- });
+ }
+ );
$get_version_query->execute();
my $rows = $get_version_query->fetchall_arrayref;
- if (@{$rows} == 0) {
+ if ( @{$rows} == 0 ) {
return -1;
}
return $rows->[0][0];
@@ -32,36 +37,68 @@ sub get_schema_version {
}
my @migrations = (
+
# v0 -> v1
sub {
- $dbh->do(qq{
- begin transaction;
- });
- $dbh->do(qq{
+ $dbh->begin_work;
+ $dbh->do(
+ qq{
create table schema_version (
version integer primary key
);
- });
- $dbh->do(qq{
+ }
+ );
+ $dbh->do(
+ qq{
insert into schema_version (version) values (1);
- });
- $dbh->do(qq{
+ }
+ );
+ $dbh->do(
+ qq{
create table new_users (
id integer primary key,
name char(64) not null unique,
status int not null,
+ is_public bool not null,
email char(256),
password text,
- registered_at datetime,
- last_login datetime
+ registered_at datetime not null,
+ last_login datetime not null,
+ deletion_requested datetime
);
- });
- my $get_users_query = $dbh->prepare(qq{
+ }
+ );
+ my $get_users_query = $dbh->prepare(
+ qq{
select * from users;
- });
- $dbh->do(qq{
- commit;
- });
+ }
+ );
+ my $add_user_query = $dbh->prepare(
+ qq{
+ insert into new_users
+ (id, name, status, is_public, registered_at, last_login)
+ values
+ (?, ?, ?, ?, ?, ?)
+ }
+ );
+ $get_users_query->execute;
+
+ while ( my @row = $get_users_query->fetchrow_array ) {
+ my ( $id, $name ) = @row;
+ my $now = DateTime->now( time_zone => 'Europe/Berlin' )->epoch;
+ $add_user_query->execute( $id, $name, 0, 0, $now, $now );
+ }
+ $dbh->do(
+ qq{
+ drop table users;
+ }
+ );
+ $dbh->do(
+ qq{
+ alter table new_users rename to users;
+ }
+ );
+ $dbh->commit;
},
);
@@ -69,11 +106,13 @@ my $schema_version = get_schema_version();
say "Found travelynx schema v${schema_version}";
-if ($schema_version == @migrations) {
+if ( $schema_version == @migrations ) {
say "Database schema is up-to-date";
}
-for my $i ($schema_version .. $#migrations) {
- printf("Updating to v%d\n", $i + 1);
+for my $i ( $schema_version .. $#migrations ) {
+ printf( "Updating to v%d\n", $i + 1 );
$migrations[$i]();
}
+
+$dbh->disconnect;
diff --git a/templates/account.html.ep b/templates/account.html.ep
index e734ee0..9c3a435 100644
--- a/templates/account.html.ep
+++ b/templates/account.html.ep
@@ -1,17 +1,18 @@
<h1>Account</h1>
+% my $acc = get_user_data();
<div class="row">
<table class="striped">
<tr>
<th scope="row">Name</th>
- <td><%= get_user_name() %></td>
+ <td><%= $acc->{name} %></td>
</tr>
<tr>
<th scope="row">Mail</th>
- <td>fnord@example.org</td>
+ <td><%= $acc->{email} %></td>
</tr>
<tr>
<th scope="row">Registriert am</th>
- <td>01.01.1970 00:00</td>
+ <td><%= $acc->{registered_at}->strftime('%d.%m.%Y %H:%M') %></td>
</tr>
</table>
</div>