diff options
Diffstat (limited to 'lib')
| -rwxr-xr-x | lib/Travelynx.pm | 801 | ||||
| -rw-r--r-- | lib/Travelynx/Command/database.pm | 208 | ||||
| -rw-r--r-- | lib/Travelynx/Controller/Account.pm | 47 | ||||
| -rwxr-xr-x | lib/Travelynx/Controller/Api.pm | 16 | ||||
| -rw-r--r-- | lib/Travelynx/Controller/Static.pm | 3 | ||||
| -rwxr-xr-x | lib/Travelynx/Controller/Traveling.pm | 101 |
6 files changed, 656 insertions, 520 deletions
diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm index fd59cc0..da10a9a 100755 --- a/lib/Travelynx.pm +++ b/lib/Travelynx.pm @@ -191,9 +191,10 @@ sub startup { $self->helper( 'get_departures' => sub { - my ( $self, $station, $lookbehind ) = @_; + my ( $self, $station, $lookbehind, $lookahead ) = @_; $lookbehind //= 180; + $lookahead //= 30; my @station_matches = Travel::Status::DE::IRIS::Stations::get_station($station); @@ -207,7 +208,7 @@ sub startup { lookbehind => 20, datetime => DateTime->now( time_zone => 'Europe/Berlin' ) ->subtract( minutes => $lookbehind ), - lookahead => $lookbehind + 10, + lookahead => $lookbehind + $lookahead, ); return { results => [ $status->results ], @@ -241,6 +242,10 @@ sub startup { 'add_journey' => sub { my ( $self, %opt ) = @_; + $self->app->log->error( + "add_journey is not implemented at the moment"); + return ( undef, undef, 'not implemented' ); + my $user_status = $self->get_user_status; if ( $user_status->{checked_in} or $user_status->{cancelled} ) { @@ -326,11 +331,9 @@ sub startup { $self->helper( 'checkin' => sub { - my ( $self, $station, $train_id, $action_id ) = @_; - - $action_id //= $self->app->action_type->{checkin}; + my ( $self, $station, $train_id ) = @_; - my $status = $self->get_departures($station); + my $status = $self->get_departures( $station, 140, 30 ); if ( $status->{errstr} ) { return ( undef, $status->{errstr} ); } @@ -343,40 +346,35 @@ sub startup { else { my $user = $self->get_user_status; - if ( $user->{checked_in} ) { + if ( $user->{checked_in} or $user->{cancelled} ) { # If a user is already checked in, we assume that they forgot to # check out and do it for them. $self->checkout( $station, 1 ); } - elsif ( $user->{cancelled} ) { - - # Same - $self->checkout( $station, 1, - $self->app->action_type->{cancelled_to} ); - } eval { $self->pg->db->insert( - 'user_actions', + 'in_transit', { - user_id => $self->current_user->{id}, - action_id => $action_id, - station_id => $self->get_station_id( + user_id => $self->current_user->{id}, + cancelled => $train->departure_is_cancelled + ? 1 + : 0, + checkin_station_id => $self->get_station_id( ds100 => $status->{station_ds100}, name => $status->{station_name} ), - action_time => + checkin_time => DateTime->now( time_zone => 'Europe/Berlin' ), - edited => 0, - train_type => $train->type, - train_line => $train->line_no, - train_no => $train->train_no, - train_id => $train->train_id, - sched_time => $train->sched_departure, - real_time => $train->departure, - route => join( '|', $train->route ), - messages => join( + train_type => $train->type, + train_line => $train->line_no, + train_no => $train->train_no, + train_id => $train->train_id, + sched_departure => $train->sched_departure, + real_departure => $train->departure, + route => join( '|', $train->route ), + messages => join( '|', map { ( $_->[0] ? $_->[0]->epoch : q{} ) . ':' @@ -389,7 +387,7 @@ sub startup { if ($@) { my $uid = $self->current_user->{id}; $self->app->log->error( - "Checkin($uid, $action_id): INSERT failed: $@"); + "Checkin($uid): INSERT failed: $@"); return ( undef, 'INSERT failed: ' . $@ ); } return ( $train, undef ); @@ -400,43 +398,86 @@ sub startup { $self->helper( 'undo' => sub { - my ( $self, $action_id ) = @_; - - my $status = $self->get_user_status; + my ( $self, $journey_id ) = @_; + my $uid = $self->current_user->{id}; - if ( $action_id < 1 or $status->{action_id} != $action_id ) { - return -"Invalid action ID: $action_id != $status->{action_id}. Note that you can only undo your latest action."; + if ( $journey_id eq 'in_transit' ) { + eval { + $self->pg->db->delete( 'in_transit', { user_id => $uid } ); + }; + if ($@) { + $self->app->log->error("Undo($uid, $journey_id): $@"); + return "Undo($journey_id): $@"; + } + return undef; + } + if ( $journey_id !~ m{ ^ \d+ $ }x ) { + return 'Invalid Journey ID'; } eval { - $self->pg->db->delete( 'user_actions', { id => $action_id } ); + my $db = $self->pg->db; + my $tx = $db->begin; + + my $journey = $db->select( + 'journeys', + '*', + { + user_id => $uid, + id => $journey_id + } + )->hash; + $db->delete( + 'journeys', + { + user_id => $uid, + id => $journey_id + } + ); + + if ( $journey->{edited} ) { + die( +"Cannot undo a journey which has already been edited. Please delete manually.\n" + ); + } + + delete $journey->{edited}; + delete $journey->{id}; + + $db->insert( 'in_transit', $journey ); + + my $cache_ts = DateTime->now( time_zone => 'Europe/Berlin' ); + if ( $journey->{real_departure} + =~ m{ ^ (?<year> \d{4} ) - (?<month> \d{2} ) }x ) + { + $cache_ts->set( + year => $+{year}, + month => $+{month} + ); + } + + $self->invalidate_stats_cache( $cache_ts, $db ); + + $tx->commit; }; if ($@) { - my $uid = $self->current_user->{id}; - $self->app->log->error( - "Undo($uid, $action_id): DELETE failed: $@"); - return 'DELETE failed: ' . $@; + $self->app->log->error("Undo($uid, $journey_id): $@"); + return "Undo($journey_id): $@"; } - return; + return undef; } ); + # Statistics are partitioned by real_departure, which must be provided + # when calling this function e.g. after journey deletion or editing. + # If a joureny's real_departure has been edited, this function must be + # called twice: once with the old and once with the new value. $self->helper( 'invalidate_stats_cache' => sub { - my ( $self, $ts ) = @_; + my ( $self, $ts, $db ) = @_; my $uid = $self->current_user->{id}; - $ts //= DateTime->now( time_zone => 'Europe/Berlin' ); - - # ts is the checkout timestamp or (for manual entries) the - # time of arrival. As the journey may span a month or year boundary, - # there is a total of five cache entries we need to invalidate: - # * year, month - # * year - # * (year, month) - 1 month (with wraparound) - # * (year) - 1 year - # * total stats + $db //= $self->pg->db; $self->pg->db->delete( 'journey_stats', @@ -454,196 +495,207 @@ sub startup { month => 0, } ); - $ts->subtract( months => 1 ); - $self->pg->db->delete( - 'journey_stats', - { - user_id => $uid, - year => $ts->year, - month => $ts->month, - } - ); - $ts->subtract( months => 11 ); - $self->pg->db->delete( - 'journey_stats', - { - user_id => $uid, - year => $ts->year, - month => 0, - } - ); - $self->pg->db->delete( - 'journey_stats', - { - user_id => $uid, - year => 0, - month => 0, - } - ); } ); $self->helper( 'checkout' => sub { - my ( $self, $station, $force, $action_id ) = @_; - - $action_id //= $self->app->action_type->{checkout}; + my ( $self, $station, $force ) = @_; - my $status = $self->get_departures( $station, 180 ); + my $db = $self->pg->db; + my $uid = $self->current_user->{id}; + my $status = $self->get_departures( $station, 120, 120 ); my $user = $self->get_user_status; my $train_id = $user->{train_id}; if ( not $user->{checked_in} and not $user->{cancelled} ) { - return 'You are not checked into any train'; + return ( 0, 'You are not checked into any train' ); } if ( $status->{errstr} and not $force ) { - return $status->{errstr}; + return ( 1, $status->{errstr} ); } my $now = DateTime->now( time_zone => 'Europe/Berlin' ); + my $journey + = $db->select( 'in_transit', '*', { user_id => $uid } )->hash; my ($train) = first { $_->train_id eq $train_id } @{ $status->{results} }; - if ( not defined $train ) { - if ($force) { - eval { - $self->pg->db->insert( - 'user_actions', - { - user_id => $self->current_user->{id}, - action_id => $action_id, - station_id => $self->get_station_id( - ds100 => $status->{station_ds100}, - name => $status->{station_name} - ), - action_time => $now, - edited => 0 - } - ); - }; - if ($@) { - my $uid = $self->current_user->{id}; - $self->app->log->error( -"Force checkout($uid, $action_id): INSERT failed: $@" - ); - return 'INSERT failed: ' . $@; - } - $self->invalidate_stats_cache; - return; - } - else { - return "Train ${train_id} not found"; - } + + # Store the intended checkout station regardless of this operation's + # success. + my $new_checkout_station_id = $self->get_station_id( + ds100 => $status->{station_ds100}, + name => $status->{station_name} + ); + $db->update( + 'in_transit', + { + checkout_station_id => $new_checkout_station_id, + }, + { user_id => $uid } + ); + + # If in_transit already contains arrival data for another estimated + # destination, we must invalidate it. + if ( defined $journey->{checkout_station_id} + and $journey->{checkout_station_id} + != $new_checkout_station_id ) + { + $db->update( + 'in_transit', + { + checkout_time => undef, + sched_arrival => undef, + real_arrival => undef, + }, + { user_id => $uid } + ); } - else { - eval { - $self->pg->db->insert( - 'user_actions', + + if ( not( defined $train or $force ) ) { + return ( 1, undef ); + } + + my $has_arrived = 0; + + eval { + + my $tx = $db->begin; + + if ( defined $train ) { + $has_arrived = $train->arrival->epoch < $now->epoch ? 1 : 0; + $db->update( + 'in_transit', { - user_id => $self->current_user->{id}, - action_id => $action_id, - station_id => $self->get_station_id( - ds100 => $status->{station_ds100}, - name => $status->{station_name} - ), - action_time => $now, - edited => 0, - train_type => $train->type, - train_line => $train->line_no, - train_no => $train->train_no, - train_id => $train->train_id, - sched_time => $train->sched_arrival, - real_time => $train->arrival, - route => join( '|', $train->route ), - messages => join( + checkout_time => $now, + sched_arrival => $train->sched_arrival, + real_arrival => $train->arrival, + cancelled => $train->arrival_is_cancelled ? 1 : 0, + route => join( '|', $train->route ), + messages => join( '|', map { ( $_->[0] ? $_->[0]->epoch : q{} ) . ':' . $_->[1] } $train->messages - ) - } + ), + }, + { user_id => $uid } ); - }; - if ($@) { - my $uid = $self->current_user->{id}; - $self->app->log->error( - "Checkout($uid, $action_id): INSERT failed: $@"); - return 'INSERT failed: ' . $@; } - $self->invalidate_stats_cache; - return; + + $journey + = $db->select( 'in_transit', '*', { user_id => $uid } )->hash; + + if ( $has_arrived or $force ) { + $journey->{edited} = 0; + $journey->{checkout_time} = $now; + $db->insert( 'journeys', $journey ); + $db->delete( 'in_transit', { user_id => $uid } ); + + my $cache_ts = $now->clone; + if ( $journey->{real_departure} + =~ m{ ^ (?<year> \d{4} ) - (?<month> \d{2} ) }x ) + { + $cache_ts->set( + year => $+{year}, + month => $+{month} + ); + } + $self->invalidate_stats_cache( $cache_ts, $db ); + } + + $tx->commit; + }; + + if ($@) { + $self->app->log->error("Checkout($uid): $@"); + return ( 1, 'Checkout error: ' . $@ ); } + + if ( $has_arrived or $force ) { + return ( 0, undef ); + } + return ( 1, undef ); } ); $self->helper( 'update_journey_part' => sub { - my ( $self, $db, $checkin_id, $checkout_id, $key, $value ) = @_; + my ( $self, $db, $journey_id, $key, $value ) = @_; my $rows; + my $journey = $self->get_journey( + db => $db, + journey_id => $journey_id, + ); + eval { if ( $key eq 'sched_departure' ) { $rows = $db->update( - 'user_actions', + 'journeys', { - sched_time => $value, + sched_departure => $value, + edited => $journey->{edited} | 0x0001, }, { - id => $checkin_id, - action_id => $self->app->action_type->{checkin}, + id => $journey_id, } )->rows; } elsif ( $key eq 'rt_departure' ) { $rows = $db->update( - 'user_actions', + 'journeys', { - real_time => $value, + real_departure => $value, + edited => $journey->{edited} | 0x0002, }, { - id => $checkin_id, - action_id => $self->app->action_type->{checkin}, + id => $journey_id, } )->rows; + + # stats are partitioned by rt_departure -> both the cache for + # the old value (see bottom of this function) and the new value + # (here) must be invalidated. + $self->invalidate_stats_cache( $value, $db ); } elsif ( $key eq 'sched_arrival' ) { $rows = $db->update( - 'user_actions', + 'journeys', { - sched_time => $value, + sched_arrival => $value, + edited => $journey->{edited} | 0x0100, }, { - id => $checkout_id, - action_id => $self->app->action_type->{checkout}, + id => $journey_id, } )->rows; } elsif ( $key eq 'rt_arrival' ) { $rows = $db->update( - 'user_actions', + 'journeys', { - real_time => $value, + real_arrival => $value, + edited => $journey->{edited} | 0x0200, }, { - id => $checkout_id, - action_id => $self->app->action_type->{checkout}, + id => $journey_id, } )->rows; } else { - $self->app->log->error( -"update_journey_part($checkin_id, $checkout_id): Invalid key $key" - ); + die("Invalid key $key\n"); } }; if ($@) { $self->app->log->error( -"update_journey_part($checkin_id, $checkout_id): UPDATE failed: $@" - ); - return 'UPDATE failed: ' . $@; + "update_journey_part($journey_id, $key): $@"); + return "update_journey_part($key): $@"; } if ( $rows == 1 ) { + $self->invalidate_stats_cache( $journey->{rt_departure}, $db ); return undef; } return 'UPDATE failed: did not match any journey part'; @@ -930,14 +982,12 @@ sub startup { $self->helper( 'delete_journey' => sub { - my ( $self, $checkin_id, $checkout_id, $checkin_epoch, - $checkout_epoch ) - = @_; + my ( $self, $journey_id, $checkin_epoch, $checkout_epoch ) = @_; my $uid = $self->current_user->{id}; my @journeys = $self->get_user_travels( - uid => $uid, - checkout_id => $checkout_id + uid => $uid, + journey_id => $journey_id ); if ( @journeys == 0 ) { return 'Journey not found'; @@ -947,8 +997,7 @@ sub startup { # Double-check (comparing both ID and action epoch) to make sure we # are really deleting the right journey and the user isn't just # playing around with POST requests. - if ( $journey->{ids}[0] != $checkin_id - or $journey->{ids}[1] != $checkout_id + if ( $journey->{id} != $journey_id or $journey->{checkin}->epoch != $checkin_epoch or $journey->{checkout}->epoch != $checkout_epoch ) { @@ -958,26 +1007,24 @@ sub startup { my $rows; eval { $rows = $self->pg->db->delete( - 'user_actions', + 'journeys', { user_id => $uid, - id => [ $checkin_id, $checkout_id ] + id => $journey_id, } )->rows; }; if ($@) { - $self->app->log->error( - "Delete($uid, $checkin_id, $checkout_id): DELETE failed: $@" - ); + $self->app->log->error("Delete($uid, $journey_id): $@"); return 'DELETE failed: ' . $@; } - if ( $rows == 2 ) { - $self->invalidate_stats_cache( $journey->{checkout} ); + if ( $rows == 1 ) { + $self->invalidate_stats_cache( $journey->{rt_departure} ); return undef; } - return sprintf( 'Deleted %d rows, expected 2', $rows ); + return sprintf( 'Deleted %d rows, expected 1', $rows ); } ); @@ -1075,203 +1122,103 @@ sub startup { # Otherwise, we grab a fresh one. my $db = $opt{db} // $self->pg->db; - my $selection = qq{ - user_actions.id as action_log_id, action_id, - extract(epoch from action_time) as action_time_ts, - stations.ds100 as ds100, stations.name as name, - train_type, train_line, train_no, train_id, - extract(epoch from sched_time) as sched_time_ts, - extract(epoch from real_time) as real_time_ts, - route, messages, edited - }; - $selection =~ tr{\n}{}d; - my %where = ( user_id => $uid ); + my %where = ( + user_id => $uid, + cancelled => 0 + ); my %order = ( order_by => { - -desc => 'action_time', + -desc => 'real_dep_ts', } ); - if ( $opt{limit} ) { - $order{limit} = 10; + if ( $opt{cancelled} ) { + $where{cancelled} = 1; } - if ( $opt{checkout_id} ) { - $where{'user_actions.id'} = { '<=', $opt{checkout_id} }; - $order{limit} = 2; + if ( $opt{limit} ) { + $order{limit} = $opt{limit}; } - elsif ( $opt{after} and $opt{before} ) { - # Each journey consists of exactly two database entries: one for - # checkin, one for checkout. A simple query using e.g. - # after = YYYY-01-01T00:00:00 and before YYYY-02-01T00:00:00 - # will miss journeys where checkin and checkout take place in - # different months. - # We therefore add one day to the before timestamp and filter out - # journeys whose checkin lies outside the originally requested - # time range afterwards. - # For an additional twist, get_interval_actions_query filters based - # on the action time, not actual departure, as force - # checkout actions lack sched_time and real_time data. By - # subtracting one day from "after" (i.e., moving it one day into - # the past), we make sure not to miss journeys where the real departure - # time falls into the interval, but the checkin time does not. - # Again, this is addressed in postprocessing at the bottom of this - # helper. - # This works under the assumption that there are no DB trains whose - # journey takes more than 24 hours. If this no longer holds, - # please adjust the intervals accordingly. - $where{action_time} = { - -between => [ - $opt{after}->clone->subtract( days => 1 ), - $opt{before}->clone->add( days => 1 ) - ] - }; + if ( $opt{journey_id} ) { + $where{journey_id} = $opt{journey_id}; + delete $where{cancelled}; } - - my @match_actions = ( - $self->app->action_type->{checkout}, - $self->app->action_type->{checkin} - ); - if ( $opt{cancelled} ) { - @match_actions = ( - $self->app->action_type->{cancelled_to}, - $self->app->action_type->{cancelled_from} - ); + elsif ( $opt{after} and $opt{before} ) { + $where{real_dep_ts} = { + -between => [ $opt{after}->epoch, $opt{before}->epoch, ] }; } my @travels; - my $prev_action = 0; - my $res = $db->select( - [ - 'user_actions', - [ - -left => 'stations', - id => 'station_id' - ] - ], - $selection, - \%where, - \%order - ); + my $res = $db->select( 'journeys_str', '*', \%where, \%order ); for my $entry ( $res->hashes->each ) { - if ( $entry->{action_id} == $match_actions[0] - or ( $opt{checkout_id} and not @travels ) ) - { - push( - @travels, - { - ids => [ undef, $entry->{action_log_id} ], - to_name => $entry->{name}, - sched_arrival => - epoch_to_dt( $entry->{sched_time_ts} ), - rt_arrival => epoch_to_dt( $entry->{real_time_ts} ), - checkout => epoch_to_dt( $entry->{action_time_ts} ), - type => $entry->{train_type}, - line => $entry->{train_line}, - no => $entry->{train_no}, - messages => $entry->{messages} - ? [ split( qr{[|]}, $entry->{messages} ) ] - : undef, - route => $entry->{route} - ? [ split( qr{[|]}, $entry->{route} ) ] - : undef, - completed => 0, - edited => $entry->{edited} << 8, - } - ); - } - elsif ( - ( - $entry->{action_id} == $match_actions[1] - and $prev_action == $match_actions[0] - ) - or $opt{checkout_id} - ) - { - my $ref = $travels[-1]; - $ref->{ids}->[0] = $entry->{action_log_id}; - $ref->{from_name} = $entry->{name}; - $ref->{completed} = 1; - $ref->{sched_departure} - = epoch_to_dt( $entry->{sched_time_ts} ); - $ref->{rt_departure} - = epoch_to_dt( $entry->{real_time_ts} ); - $ref->{checkin} = epoch_to_dt( $entry->{action_time_ts} ); - $ref->{type} //= $entry->{train_type}; - $ref->{line} //= $entry->{train_line}; - $ref->{no} //= $entry->{train_no}; - $ref->{messages} - //= [ split( qr{[|]}, $entry->{messages} ) ]; - $ref->{route} //= [ split( qr{[|]}, $entry->{route} ) ]; - $ref->{edited} |= $entry->{edited}; - - if ( $opt{verbose} ) { - my @parsed_messages; - for my $message ( @{ $ref->{messages} // [] } ) { - my ( $ts, $msg ) = split( qr{:}, $message ); - push( @parsed_messages, - [ epoch_to_dt($ts), $msg ] ); - } - $ref->{messages} = [ reverse @parsed_messages ]; - $ref->{sched_duration} - = $ref->{sched_arrival} - ? $ref->{sched_arrival}->epoch - - $ref->{sched_departure}->epoch - : undef; - $ref->{rt_duration} - = $ref->{rt_arrival} - ? $ref->{rt_arrival}->epoch - - $ref->{rt_departure}->epoch - : undef; - my ( $km, $skip ) - = $self->get_travel_distance( $ref->{from_name}, - $ref->{to_name}, $ref->{route} ); - $ref->{km_route} = $km; - $ref->{skip_route} = $skip; - ( $km, $skip ) - = $self->get_travel_distance( $ref->{from_name}, - $ref->{to_name}, - [ $ref->{from_name}, $ref->{to_name} ] ); - $ref->{km_beeline} = $km; - $ref->{skip_beeline} = $skip; - my $kmh_divisor - = ( $ref->{rt_duration} // $ref->{sched_duration} - // 999999 ) / 3600; - $ref->{kmh_route} - = $kmh_divisor ? $ref->{km_route} / $kmh_divisor : -1; - $ref->{kmh_beeline} - = $kmh_divisor - ? $ref->{km_beeline} / $kmh_divisor - : -1; - } - if ( $opt{checkout_id} - and $entry->{action_id} - == $self->app->action_type->{cancelled_from} ) - { - $ref->{cancelled} = 1; + my $ref = { + id => $entry->{journey_id}, + type => $entry->{train_type}, + line => $entry->{train_line}, + no => $entry->{train_no}, + from_name => $entry->{dep_name}, + checkin => epoch_to_dt( $entry->{checkin_ts} ), + sched_departure => epoch_to_dt( $entry->{sched_dep_ts} ), + rt_departure => epoch_to_dt( $entry->{real_dep_ts} ), + to_name => $entry->{arr_name}, + checkout => epoch_to_dt( $entry->{checkout_ts} ), + sched_arrival => epoch_to_dt( $entry->{sched_arr_ts} ), + rt_arrival => epoch_to_dt( $entry->{real_arr_ts} ), + messages => $entry->{messages} + ? [ split( qr{[|]}, $entry->{messages} ) ] + : undef, + route => $entry->{route} + ? [ split( qr{[|]}, $entry->{route} ) ] + : undef, + edited => $entry->{edited}, + }; + + if ( $opt{verbose} ) { + $ref->{cancelled} = $entry->{cancelled}; + my @parsed_messages; + for my $message ( @{ $ref->{messages} // [] } ) { + my ( $ts, $msg ) = split( qr{:}, $message ); + push( @parsed_messages, [ epoch_to_dt($ts), $msg ] ); } + $ref->{messages} = [ reverse @parsed_messages ]; + $ref->{sched_duration} + = $ref->{sched_arrival} + ? $ref->{sched_arrival}->epoch + - $ref->{sched_departure}->epoch + : undef; + $ref->{rt_duration} + = $ref->{rt_arrival} + ? $ref->{rt_arrival}->epoch - $ref->{rt_departure}->epoch + : undef; + my ( $km, $skip ) + = $self->get_travel_distance( $ref->{from_name}, + $ref->{to_name}, $ref->{route} ); + $ref->{km_route} = $km; + $ref->{skip_route} = $skip; + ( $km, $skip ) + = $self->get_travel_distance( $ref->{from_name}, + $ref->{to_name}, + [ $ref->{from_name}, $ref->{to_name} ] ); + $ref->{km_beeline} = $km; + $ref->{skip_beeline} = $skip; + my $kmh_divisor + = ( $ref->{rt_duration} // $ref->{sched_duration} + // 999999 ) / 3600; + $ref->{kmh_route} + = $kmh_divisor ? $ref->{km_route} / $kmh_divisor : -1; + $ref->{kmh_beeline} + = $kmh_divisor + ? $ref->{km_beeline} / $kmh_divisor + : -1; } - $prev_action = $entry->{action_id}; - } - if ( $opt{before} and $opt{after} ) { - @travels = grep { - $_->{rt_departure} >= $opt{after} - and $_->{rt_departure} < $opt{before} - } @travels; + push( @travels, $ref ); } - # user_actions are sorted by action_time. As users are allowed to check - # into trains in arbitrary order, action_time does not always - # correspond to departure/arrival time, so we ensure a proper sort - # order here. - @travels - = sort { $b->{rt_departure} <=> $a->{rt_departure} } @travels; - return @travels; } ); @@ -1280,11 +1227,9 @@ sub startup { 'get_journey' => sub { my ( $self, %opt ) = @_; + $opt{cancelled} = 'any'; my @journeys = $self->get_user_travels(%opt); - if ( @journeys == 0 - or not $journeys[0]{completed} - or $journeys[0]{ids}[1] != $opt{checkout_id} ) - { + if ( @journeys == 0 ) { return undef; } @@ -1298,86 +1243,96 @@ sub startup { $uid //= $self->current_user->{id}; - my $selection = qq{ - user_actions.id as action_log_id, action_id, - extract(epoch from action_time) as action_time_ts, - stations.ds100 as ds100, stations.name as name, - train_type, train_line, train_no, train_id, - extract(epoch from sched_time) as sched_time_ts, - extract(epoch from real_time) as real_time_ts, - route - }; - $selection =~ tr{\n}{}d; + my $db = $self->pg->db; + my $now = DateTime->now( time_zone => 'Europe/Berlin' ); - my $res = $self->pg->db->select( - [ - 'user_actions', - [ - -left => 'stations', - id => 'station_id' - ] - ], - $selection, - { - user_id => $uid, - }, - { - order_by => { - -desc => 'action_time', - }, - limit => 1, - } - ); - my $status = $res->hash; + my $in_transit + = $db->select( 'in_transit_str', '*', { user_id => $uid } )->hash; - if ($status) { - my $now = DateTime->now( time_zone => 'Europe/Berlin' ); + if ($in_transit) { - my $action_ts = epoch_to_dt( $status->{action_time_ts} ); - my $sched_ts = epoch_to_dt( $status->{sched_time_ts} ); - my $real_ts = epoch_to_dt( $status->{real_time_ts} ); - my $checkin_station_name = $status->{name}; - my @route = split( qr{[|]}, $status->{route} // q{} ); + my @route = split( qr{[|]}, $in_transit->{route} // q{} ); my @route_after; my $is_after = 0; for my $station (@route) { - if ( $station eq $checkin_station_name ) { + if ( $station eq $in_transit->{dep_name} ) { $is_after = 1; } if ($is_after) { push( @route_after, $station ); } } + + my $ts = $in_transit->{checkout_ts} + // $in_transit->{checkin_ts}; + my $action_time = epoch_to_dt($ts); + return { - checked_in => ( - $status->{action_id} - == $self->app->action_type->{checkin} - ), - cancelled => ( - $status->{action_id} - == $self->app->action_type->{cancelled_from} - ), - timestamp => $action_ts, - timestamp_delta => $now->epoch - $action_ts->epoch, - action_id => $status->{action_log_id}, - sched_ts => $sched_ts, - real_ts => $real_ts, - station_ds100 => $status->{ds100}, - station_name => $checkin_station_name, - train_type => $status->{train_type}, - train_line => $status->{train_line}, - train_no => $status->{train_no}, - train_id => $status->{train_id}, - route => \@route, - route_after => \@route_after, + checked_in => !$in_transit->{cancelled}, + cancelled => $in_transit->{cancelled}, + timestamp => $action_time, + timestamp_delta => $now->epoch - $action_time->epoch, + train_type => $in_transit->{train_type}, + train_line => $in_transit->{train_line}, + train_no => $in_transit->{train_no}, + train_id => $in_transit->{train_id}, + sched_departure => + epoch_to_dt( $in_transit->{sched_dep_ts} ), + real_departure => epoch_to_dt( $in_transit->{real_dep_ts} ), + dep_ds100 => $in_transit->{dep_ds100}, + dep_name => $in_transit->{dep_name}, + sched_arrival => epoch_to_dt( $in_transit->{sched_arr_ts} ), + real_arrival => epoch_to_dt( $in_transit->{real_arr_ts} ), + arr_ds100 => $in_transit->{arr_ds100}, + arr_name => $in_transit->{arr_name}, + route_after => \@route_after, }; } + + my $latest = $db->select( + 'journeys_str', + '*', + { + user_id => $uid, + cancelled => 0 + }, + { + order_by => { -desc => 'journey_id' }, + limit => 1 + } + )->hash; + + if ($latest) { + my $ts = $latest->{checkout_ts}; + my $action_time = epoch_to_dt($ts); + return { + checked_in => 0, + cancelled => 0, + journey_id => $latest->{journey_id}, + timestamp => $action_time, + timestamp_delta => $now->epoch - $action_time->epoch, + train_type => $latest->{train_type}, + train_line => $latest->{train_line}, + train_no => $latest->{train_no}, + train_id => $latest->{train_id}, + sched_departure => epoch_to_dt( $latest->{sched_dep_ts} ), + real_departure => epoch_to_dt( $latest->{real_dep_ts} ), + dep_ds100 => $latest->{dep_ds100}, + dep_name => $latest->{dep_name}, + sched_arrival => epoch_to_dt( $latest->{sched_arr_ts} ), + real_arrival => epoch_to_dt( $latest->{real_arr_ts} ), + arr_ds100 => $latest->{arr_ds100}, + arr_name => $latest->{arr_name}, + }; + } + return { - checked_in => 0, - timestamp => epoch_to_dt(0), - sched_ts => epoch_to_dt(0), - real_ts => epoch_to_dt(0), + checked_in => 0, + cancelled => 0, + no_journeys_yet => 1, + timestamp => epoch_to_dt(0), + timestamp_delta => $now->epoch, }; } ); diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm index b270262..b5e8cf5 100644 --- a/lib/Travelynx/Command/database.pm +++ b/lib/Travelynx/Command/database.pm @@ -12,9 +12,11 @@ sub get_schema_version { my $version; eval { - $version = $db->select( 'schema_version', ['version'] )->hash->{version}; + $version + = $db->select( 'schema_version', ['version'] )->hash->{version}; }; if ($@) { + # If it failed, the version table does not exist -> run setup first. return undef; } @@ -124,6 +126,210 @@ my @migrations = ( } ); }, + + # v3 -> v4 + # Introduces "journeys", containing one row for each complete + # journey, and "in_transit", containing the journey which is currently + # in progress (if any). "user_actions" is no longer used, but still kept + # as a backup for now. + sub { + my ($db) = @_; + + $db->query( + qq{ + create table journeys ( + id serial not null primary key, + user_id integer not null references users (id), + train_type varchar(16) not null, + train_line varchar(16), + train_no varchar(16) not null, + train_id varchar(128) not null, + checkin_station_id integer not null references stations (id), + checkin_time timestamptz not null, + sched_departure timestamptz not null, + real_departure timestamptz not null, + checkout_station_id integer not null references stations (id), + checkout_time timestamptz not null, + sched_arrival timestamptz, + real_arrival timestamptz, + cancelled boolean not null, + edited smallint not null, + route text, + messages text + ); + create table in_transit ( + user_id integer not null references users (id) primary key, + train_type varchar(16) not null, + train_line varchar(16), + train_no varchar(16) not null, + train_id varchar(128) not null, + checkin_station_id integer not null references stations (id), + checkin_time timestamptz not null, + sched_departure timestamptz not null, + real_departure timestamptz not null, + checkout_station_id int references stations (id), + checkout_time timestamptz, + sched_arrival timestamptz, + real_arrival timestamptz, + cancelled boolean not null, + route text, + messages text + ); + create view journeys_str as select + journeys.id as journey_id, user_id, + train_type, train_line, train_no, train_id, + extract(epoch from checkin_time) as checkin_ts, + extract(epoch from sched_departure) as sched_dep_ts, + extract(epoch from real_departure) as real_dep_ts, + dep_stations.ds100 as dep_ds100, + dep_stations.name as dep_name, + extract(epoch from checkout_time) as checkout_ts, + extract(epoch from sched_arrival) as sched_arr_ts, + extract(epoch from real_arrival) as real_arr_ts, + arr_stations.ds100 as arr_ds100, + arr_stations.name as arr_name, + cancelled, edited, route, messages + from journeys + join stations as dep_stations on dep_stations.id = checkin_station_id + join stations as arr_stations on arr_stations.id = checkout_station_id + ; + create view in_transit_str as select + user_id, + train_type, train_line, train_no, train_id, + extract(epoch from checkin_time) as checkin_ts, + extract(epoch from sched_departure) as sched_dep_ts, + extract(epoch from real_departure) as real_dep_ts, + dep_stations.ds100 as dep_ds100, + dep_stations.name as dep_name, + extract(epoch from checkout_time) as checkout_ts, + extract(epoch from sched_arrival) as sched_arr_ts, + extract(epoch from real_arrival) as real_arr_ts, + arr_stations.ds100 as arr_ds100, + arr_stations.name as arr_name, + cancelled, route, messages + from in_transit + join stations as dep_stations on dep_stations.id = checkin_station_id + left join stations as arr_stations on arr_stations.id = checkout_station_id + ; + } + ); + + my @uids + = $db->select( 'users', ['id'] )->hashes->map( sub { shift->{id} } ) + ->each; + my $count = 0; + + for my $uid (@uids) { + my %cache; + my $prev_action_type = 0; + my $actions = $db->select( + 'user_actions', '*', + { user_id => $uid }, + { order_by => { -asc => 'id' } } + ); + for my $action ( $actions->hashes->each ) { + my $action_type = $action->{action_id}; + my $id = $action->{id}; + + if ( $action_type == 2 and $prev_action_type != 1 ) { + die( +"Inconsistent data at uid ${uid} action ${id}: Illegal transition $prev_action_type -> $action_type.\n" + ); + } + + if ( $action_type == 5 and $prev_action_type != 4 ) { + die( +"Inconsistent data at uid ${uid} action ${id}: Illegal transition $prev_action_type -> $action_type.\n" + ); + } + + if ( $action_type == 1 or $action_type == 4 ) { + %cache = ( + train_type => $action->{train_type}, + train_line => $action->{train_line}, + train_no => $action->{train_no}, + train_id => $action->{train_id}, + checkin_station_id => $action->{station_id}, + checkin_time => $action->{action_time}, + sched_departure => $action->{sched_time}, + real_departure => $action->{real_time}, + route => $action->{route}, + messages => $action->{messages}, + cancelled => $action->{action_id} == 4 ? 1 : 0, + edited => $action->{edited}, + ); + } + elsif ( $action_type == 2 or $action_type == 5 ) { + $cache{checkout_station_id} = $action->{station_id}; + $cache{checkout_time} = $action->{action_time}; + $cache{sched_arrival} = $action->{sched_time}; + $cache{real_arrival} = $action->{real_time}; + $cache{edited} |= $action->{edited} << 8; + if ( $action->{route} ) { + $cache{route} = $action->{route}; + } + if ( $action->{messages} ) { + $cache{messages} = $action->{messages}; + } + + $db->insert( + 'journeys', + { + user_id => $uid, + train_type => $cache{train_type}, + train_line => $cache{train_line}, + train_no => $cache{train_no}, + train_id => $cache{train_id}, + checkin_station_id => $cache{checkin_station_id}, + checkin_time => $cache{checkin_time}, + sched_departure => $cache{sched_departure}, + real_departure => $cache{real_departure}, + checkout_station_id => $cache{checkout_station_id}, + checkout_time => $cache{checkout_time}, + sched_arrival => $cache{sched_arrival}, + real_arrival => $cache{real_arrival}, + cancelled => $cache{cancelled}, + edited => $cache{edited}, + route => $cache{route}, + messages => $cache{messages} + } + ); + + %cache = (); + + } + + $prev_action_type = $action_type; + } + + if (%cache) { + + # user is currently in transit + $db->insert( + 'in_transit', + { + user_id => $uid, + train_type => $cache{train_type}, + train_line => $cache{train_line}, + train_no => $cache{train_no}, + train_id => $cache{train_id}, + checkin_station_id => $cache{checkin_station_id}, + checkin_time => $cache{checkin_time}, + sched_departure => $cache{sched_departure}, + real_departure => $cache{real_departure}, + cancelled => $cache{cancelled}, + route => $cache{route}, + messages => $cache{messages} + } + ); + } + + $count++; + printf( " journey storage migration: %3.0f%% complete\n", + $count * 100 / @uids ); + } + $db->update( 'schema_version', { version => 4 } ); + }, ); sub setup_db { diff --git a/lib/Travelynx/Controller/Account.pm b/lib/Travelynx/Controller/Account.pm index 0037e16..e60f1d3 100644 --- a/lib/Travelynx/Controller/Account.pm +++ b/lib/Travelynx/Controller/Account.pm @@ -286,45 +286,18 @@ sub account { sub json_export { my ($self) = @_; - my $uid = $self->current_user->{id}; - my $query = $self->app->get_all_actions_query; - - $query->execute($uid); - - my @entries; - - while ( my @row = $query->fetchrow_array ) { - my ( - $action_id, $action, $raw_ts, $ds100, - $name, $train_type, $train_line, $train_no, - $train_id, $raw_sched_ts, $raw_real_ts, $raw_route, - $raw_messages - ) = @row; - - push( - @entries, - { - action => $self->app->action_types->[ $action - 1 ], - action_ts => $raw_ts, - station_ds100 => $ds100, - station_name => $name, - train_type => $train_type, - train_line => $train_line, - train_no => $train_no, - train_id => $train_id, - scheduled_ts => $raw_sched_ts, - realtime_ts => $raw_real_ts, - messages => $raw_messages - ? [ map { [ split(qr{:}) ] } split( qr{[|]}, $raw_messages ) ] - : undef, - route => $raw_route ? [ split( qr{[|]}, $raw_route ) ] - : undef, - } - ); - } + my $uid = $self->current_user->{id}; + + my $db = $self->pg->db; $self->render( - json => [@entries], + json => { + account => $db->select( 'users', '*', { id => $uid } )->hash, + journeys => [ + $db->select( 'journeys', '*', { user_id => $uid } ) + ->hashes->each + ], + } ); } diff --git a/lib/Travelynx/Controller/Api.pm b/lib/Travelynx/Controller/Api.pm index a9500f1..8e72374 100755 --- a/lib/Travelynx/Controller/Api.pm +++ b/lib/Travelynx/Controller/Api.pm @@ -48,10 +48,10 @@ sub get_v0 { my $station_lon = undef; my $station_lat = undef; - if ( $status->{station_ds100} ) { + if ( $status->{arr_ds100} // $status->{dep_ds100} ) { @station_descriptions = Travel::Status::DE::IRIS::Stations::get_station( - $status->{station_ds100} ); + $status->{arr_ds100} // $status->{dep_ds100} ); } if ( @station_descriptions == 1 ) { ( undef, undef, $station_eva, $station_lon, $station_lat ) @@ -59,14 +59,14 @@ sub get_v0 { } $self->render( json => { - deprecated => \0, + deprecated => \1, checked_in => ( $status->{checked_in} or $status->{cancelled} ) ? \1 : \0, station => { - ds100 => $status->{station_ds100}, - name => $status->{station_name}, + ds100 => $status->{arr_ds100} // $status->{dep_ds100}, + name => $status->{arr_ds100} // $status->{dep_ds100}, uic => $station_eva, longitude => $station_lon, latitude => $station_lat, @@ -77,8 +77,10 @@ sub get_v0 { no => $status->{train_no}, }, actionTime => $status->{timestamp}->epoch, - scheduledTime => $status->{sched_ts}->epoch, - realTime => $status->{real_ts}->epoch, + scheduledTime => $status->{sched_arrival}->epoch + || $status->{sched_departure}->epoch, + realTime => $status->{real_arrival}->epoch + || $status->{real_departure}->epoch, }, ); } diff --git a/lib/Travelynx/Controller/Static.pm b/lib/Travelynx/Controller/Static.pm index 0144d83..09d7f51 100644 --- a/lib/Travelynx/Controller/Static.pm +++ b/lib/Travelynx/Controller/Static.pm @@ -6,7 +6,8 @@ my $travelynx_version = qx{git describe --dirty} || 'experimental'; sub about { my ($self) = @_; - $self->render( 'about', version => $self->app->config->{version} // 'UNKNOWN' ); + $self->render( 'about', + version => $self->app->config->{version} // 'UNKNOWN' ); } sub imprint { diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm index b43c891..d8e5e03 100755 --- a/lib/Travelynx/Controller/Traveling.pm +++ b/lib/Travelynx/Controller/Traveling.pm @@ -97,13 +97,16 @@ sub log_action { else { $self->render( json => { - success => 1, + success => 1, + redirect_to => '/', }, ); } } elsif ( $params->{action} eq 'checkout' ) { - my $error = $self->checkout( $params->{station}, $params->{force} ); + my ( $still_checked_in, $error ) + = $self->checkout( $params->{station}, $params->{force} ); + my $station_link = '/s/' . $params->{station}; if ($error) { $self->render( @@ -116,7 +119,8 @@ sub log_action { else { $self->render( json => { - success => 1, + success => 1, + redirect_to => $still_checked_in ? '/' : $station_link, }, ); } @@ -134,7 +138,8 @@ sub log_action { else { $self->render( json => { - success => 1, + success => 1, + redirect_to => '/', }, ); } @@ -155,13 +160,15 @@ sub log_action { else { $self->render( json => { - success => 1, + success => 1, + redirect_to => '/', }, ); } } elsif ( $params->{action} eq 'cancelled_to' ) { - my $error = $self->checkout( $params->{station}, 1, + my ( undef, $error ) + = $self->checkout( $params->{station}, 1, $self->app->action_type->{cancelled_to} ); if ($error) { @@ -175,14 +182,14 @@ sub log_action { else { $self->render( json => { - success => 1, + success => 1, + redirect_to => '/', }, ); } } elsif ( $params->{action} eq 'delete' ) { - my ( $from, $to ) = split( qr{,}, $params->{ids} ); - my $error = $self->delete_journey( $from, $to, $params->{checkin}, + my $error = $self->delete_journey( $params->{id}, $params->{checkin}, $params->{checkout} ); if ($error) { $self->render( @@ -195,7 +202,8 @@ sub log_action { else { $self->render( json => { - success => 1, + success => 1, + redirect_to => '/history', }, ); } @@ -215,7 +223,7 @@ sub station { my $station = $self->stash('station'); my $train = $self->param('train'); - my $status = $self->get_departures($station); + my $status = $self->get_departures( $station, 120, 30 ); if ( $status->{errstr} ) { $self->render( @@ -382,11 +390,13 @@ sub monthly_history { sub journey_details { my ($self) = @_; - my ( $uid, $checkout_id ) = split( qr{-}, $self->stash('id') ); + my $journey_id = $self->stash('id'); + + my $uid = $self->current_user->{id}; - $self->param( journey_id => $checkout_id ); + $self->param( journey_id => $journey_id ); - if ( not( $uid == $self->current_user->{id} and $checkout_id ) ) { + if ( not($journey_id) ) { $self->render( 'journey', error => 'notfound', @@ -395,36 +405,35 @@ sub journey_details { return; } - my @journeys = $self->get_user_travels( - uid => $uid, - checkout_id => $checkout_id, - verbose => 1, + my $journey = $self->get_journey( + uid => $uid, + journey_id => $journey_id, + verbose => 1, ); - if ( @journeys == 0 - or not $journeys[0]{completed} - or $journeys[0]{ids}[1] != $checkout_id ) - { + + if ($journey) { + $self->render( + 'journey', + error => undef, + journey => $journey, + ); + } + else { $self->render( 'journey', error => 'notfound', journey => {} ); - return; } - $self->render( - 'journey', - error => undef, - journey => $journeys[0] - ); } sub edit_journey { - my ($self) = @_; - my $checkout_id = $self->param('journey_id'); - my $uid = $self->current_user->{id}; + my ($self) = @_; + my $journey_id = $self->param('journey_id'); + my $uid = $self->current_user->{id}; - if ( not( $uid == $self->current_user->{id} and $checkout_id ) ) { + if ( not( $journey_id =~ m{ ^ \d+ $ }x ) ) { $self->render( 'edit_journey', error => 'notfound', @@ -434,8 +443,8 @@ sub edit_journey { } my $journey = $self->get_journey( - uid => $uid, - checkout_id => $checkout_id + uid => $uid, + journey_id => $journey_id ); if ( not $journey ) { @@ -449,11 +458,6 @@ sub edit_journey { my $error = undef; - if ( $self->param('action') and $self->param('action') eq 'cancel' ) { - $self->redirect_to("/journey/${uid}-${checkout_id}"); - return; - } - if ( $self->param('action') and $self->param('action') eq 'save' ) { my $parser = DateTime::Format::Strptime->new( pattern => '%d.%m.%Y %H:%M', @@ -468,12 +472,8 @@ sub edit_journey { { my $datetime = $parser->parse_datetime( $self->param($key) ); if ( $datetime and $datetime->epoch ne $journey->{$key}->epoch ) { - $error = $self->update_journey_part( - $db, - $journey->{ids}[0], - $journey->{ids}[1], - $key, $datetime - ); + $error = $self->update_journey_part( $db, $journey->{id}, + $key, $datetime ); if ($error) { last; } @@ -482,17 +482,16 @@ sub edit_journey { if ( not $error ) { $journey = $self->get_journey( - uid => $uid, - db => $db, - checkout_id => $checkout_id, - verbose => 1 + uid => $uid, + db => $db, + journey_id => $journey_id, + verbose => 1 ); $error = $self->journey_sanity_check($journey); } if ( not $error ) { $tx->commit; - $self->redirect_to("/journey/${uid}-${checkout_id}"); - $self->invalidate_stats_cache( $journey->{checkout} ); + $self->redirect_to("/journey/${journey_id}"); return; } } |