diff options
| -rwxr-xr-x | index.pl | 43 | ||||
| -rwxr-xr-x | migrate.pl | 89 | ||||
| -rw-r--r-- | templates/account.html.ep | 7 |
3 files changed, 111 insertions, 28 deletions
@@ -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) = @_; @@ -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> |