From d0b6b7e0523cbee6c5902b53e993cfecc7cb5843 Mon Sep 17 00:00:00 2001 From: Daniel Friesel Date: Sun, 7 Apr 2019 16:55:35 +0200 Subject: Cache journey stats --- lib/Travelynx.pm | 106 ++++++++++++++++++++++++++++++++++ lib/Travelynx/Command/database.pm | 29 ++++++++-- lib/Travelynx/Controller/Traveling.pm | 15 +++-- 3 files changed, 140 insertions(+), 10 deletions(-) (limited to 'lib') diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm index b9132f3..df27684 100755 --- a/lib/Travelynx.pm +++ b/lib/Travelynx.pm @@ -8,6 +8,7 @@ use DateTime; use DBI; use Encode qw(decode encode); use Geo::Distance; +use JSON; use List::Util qw(first); use List::MoreUtils qw(after_incl before_incl); use Travel::Status::DE::IRIS; @@ -227,6 +228,44 @@ sub startup { ); } ); + $self->attr( + get_stats_query => sub { + my ($self) = @_; + + return $self->app->dbh->prepare( + qq{ + select data from journey_stats + where user_id = ? and year = ? and month = ? + } + ); + } + ); + $self->attr( + add_stats_query => sub { + my ($self) = @_; + + return $self->app->dbh->prepare( + qq{ + insert into journey_stats + (user_id, year, month, data) + values + (?, ?, ?, ?) + } + ); + } + ); + $self->attr( + drop_stats_query => sub { + my ($self) = @_; + + return $self->app->dbh->prepare( + qq{ + delete from journey_stats + where user_id = ? and year = ? and month = ? + } + ); + } + ); $self->attr( action_query => sub { my ($self) = @_; @@ -958,6 +997,73 @@ qq{select * from pending_mails where email = ? and num_tries > 1;} } ); + $self->helper( + 'get_journey_stats' => sub { + my ( $self, %opt ) = @_; + + if ( $opt{cancelled} ) { + $self->app->log->warning( +'get_journey_stats called with illegal option cancelled => 1' + ); + return {}; + } + + my $uid = $self->current_user->{id}; + my $year = $opt{year} // 0; + my $month = $opt{month} // 0; + + # Assumption: If the stats cache contains an entry it is up-to-date. + # -> Cache entries must be explicitly invalidated whenever the user + # checks out of a train or manually edits/adds a journey. + + $self->app->get_stats_query->execute( $uid, $year, $month ); + my $rows = $self->app->get_stats_query->fetchall_arrayref; + + if ( @{$rows} == 1 ) { + return JSON->new->decode( $rows->[0][0] ); + } + + my $interval_start = DateTime->new( + time_zone => 'Europe/Berlin', + year => 2000, + month => 1, + day => 1, + hour => 0, + minute => 0, + second => 0, + ); + + # I wonder if people will still be traveling by train in the year 3000 + my $interval_end = $interval_start->clone->add( years => 1000 ); + + if ( $opt{year} and $opt{month} ) { + $interval_start->set( + year => $opt{year}, + month => $opt{month} + ); + $interval_end = $interval_start->clone->add( months => 1 ); + } + elsif ( $opt{year} ) { + $interval_start->set( year => $opt{year} ); + $interval_end = $interval_start->clone->add( years => 1 ); + } + + my @journeys = $self->get_user_travels( + cancelled => $opt{cancelled} ? 1 : 0, + verbose => 1, + after => $interval_start, + before => $interval_end + ); + my $stats = $self->compute_journey_stats(@journeys); + + $self->app->drop_stats_query->execute( $uid, $year, $month ); + $self->app->add_stats_query->execute( $uid, $year, $month, + JSON->new->encode($stats) ); + + return $stats; + } + ); + $self->helper( 'get_user_travels' => sub { my ( $self, %opt ) = @_; diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm index 7a714b1..4175d58 100644 --- a/lib/Travelynx/Command/database.pm +++ b/lib/Travelynx/Command/database.pm @@ -71,7 +71,28 @@ sub initialize_db { ); } -my @migrations = (); +my @migrations = ( + + # v0 -> v1 + sub { + my ($dbh) = @_; + return $dbh->do( + qq{ + alter table user_actions + add column edited smallint; + drop table if exists monthly_stats; + create table journey_stats ( + user_id integer not null references users (id), + year smallint not null, + month smallint not null, + data jsonb not null, + primary key (user_id, year, month) + ); + update schema_version set version = 1; + } + ); + }, +); sub run { my ( $self, $command ) = @_; @@ -96,18 +117,18 @@ sub run { } for my $i ( $schema_version .. $#migrations ) { printf( "Updating to v%d ...\n", $i + 1 ); - if ( not $migrations[$i]() ) { + if ( not $migrations[$i]($dbh) ) { say "Aborting migration; rollback to v${schema_version}"; $dbh->rollback; last; } } - if ( get_schema_version($dbh) == $#migrations ) { + if ( get_schema_version($dbh) == @migrations ) { $dbh->commit; } } elsif ( $command eq 'has-current-schema' ) { - if ( get_schema_version($dbh) == $#migrations ) { + if ( get_schema_version($dbh) == @migrations ) { say "yes"; } else { diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm index a83adb0..dee69a6 100755 --- a/lib/Travelynx/Controller/Traveling.pm +++ b/lib/Travelynx/Controller/Traveling.pm @@ -285,7 +285,9 @@ sub monthly_history { qw(Januar Februar März April Mai Juni Juli August September Oktober November Dezember) ); - if ( not( $year =~ m{ ^ [0-9]{4} $ }x and $month =~ m{ ^ [0-9]{1,2} $ }x ) ) + if ( $cancelled + or + not( $year =~ m{ ^ [0-9]{4} $ }x and $month =~ m{ ^ [0-9]{1,2} $ }x ) ) { @journeys = $self->get_user_travels( cancelled => $cancelled ); } @@ -301,12 +303,13 @@ sub monthly_history { ); my $interval_end = $interval_start->clone->add( months => 1 ); @journeys = $self->get_user_travels( - cancelled => $cancelled, - verbose => 1, - after => $interval_start, - before => $interval_end + after => $interval_start, + before => $interval_end + ); + $stats = $self->get_journey_stats( + year => $year, + month => $month ); - $stats = $self->compute_journey_stats(@journeys); } $self->respond_to( -- cgit v1.2.3