From 8adca327fd976b117ccfa4d69903ee24e4f9bca4 Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Mon, 4 Mar 2019 18:17:03 +0100
Subject: prepare DB schema for public registration

---
 index.pl                  | 43 +++++++++++++++++++++++
 migrate.pl                | 89 ++++++++++++++++++++++++++++++++++-------------
 templates/account.html.ep |  7 ++--
 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
@@ -177,6 +177,20 @@ app->attr(
 			qq{select id from users where name = ?});
 	}
 );
+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>
-- 
cgit v1.2.3