From 4abc43b72e597f7c441fc2c287ed38255a1f439b Mon Sep 17 00:00:00 2001 From: Daniel Friesel Date: Sun, 15 Jan 2023 16:37:32 +0100 Subject: change route/stop layout to [name, eva, {data}] --- lib/Travelynx/Command/database.pm | 76 +++++++++++++++++++++++++++++++++++++++ lib/Travelynx/Helper/HAFAS.pm | 30 ++++++++-------- lib/Travelynx/Helper/IRIS.pm | 18 +++++++--- lib/Travelynx/Model/Journeys.pm | 11 +++--- 4 files changed, 110 insertions(+), 25 deletions(-) (limited to 'lib/Travelynx') diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm index d6158bf..d8b04d2 100644 --- a/lib/Travelynx/Command/database.pm +++ b/lib/Travelynx/Command/database.pm @@ -1212,6 +1212,82 @@ my @migrations = ( } ); }, + +# v29 -> v30 +# change layout of stops in in_transit and journeys "route" lists. +# Old layout: A mixture of [name, {data}, undef/"additional"/"cancelled"], [name, timestamp, timestamp], and [name] +# New layout: [name, eva, {data including isAdditional/isCancelled}] +# Combined with a maintenance task that adds eva IDs to past stops, this will allow for more resilience against station name changes. +# It will also help increase the performance of distance and map calculation + sub { + my ($db) = @_; + my $json = JSON->new; + + say 'Adjusting route schema, this may take a while ...'; + + my $res = $db->select( 'in_transit_str', '*' ); + while ( my $row = $res->expand->hash ) { + my @new_route; + for my $stop ( @{ $row->{route} } ) { + push( @new_route, [ $stop->[0], undef, {} ] ); + } + $db->update( + 'in_transit', + { route => $json->encode( \@new_route ) }, + { user_id => $row->{user_id} } + ); + } + + my $total + = $db->select( 'journeys', 'count(*) as count' )->hash->{count}; + my $count = 0; + + $res = $db->select( 'journeys_str', '*' ); + while ( my $row = $res->expand->hash ) { + my $id = $row->{journey_id}; + my @new_route; + + for my $stop ( @{ $row->{route} } ) { + if ( @{$stop} == 1 ) { + push( @new_route, [ $stop->[0], undef, {} ] ); + } + elsif ( + ( not defined $stop->[1] or $stop->[1] =~ m{ ^ \d+ $ }x ) + and + ( not defined $stop->[2] or $stop->[2] =~ m{ ^ \d+ $ }x ) + ) + { + push( @new_route, [ $stop->[0], undef, {} ] ); + } + else { + my $attr = $stop->[1] // {}; + if ( $stop->[2] and $stop->[2] eq 'additional' ) { + $attr->{isAdditional} = 1; + } + elsif ( $stop->[2] and $stop->[2] eq 'cancelled' ) { + $attr->{isCancelled} = 1; + } + push( @new_route, [ $stop->[0], undef, $attr ] ); + } + } + + $db->update( + 'journeys', + { route => $json->encode( \@new_route ) }, + { id => $row->{journey_id} } + ); + + if ( $count++ % 10000 == 0 ) { + printf( " %2.0f%% complete\n", $count * 100 / $total ); + } + } + say ' done'; + $db->query( + qq{ + update schema_version set version = 30; + } + ); + }, ); sub sync_stations { diff --git a/lib/Travelynx/Helper/HAFAS.pm b/lib/Travelynx/Helper/HAFAS.pm index 92cf1c2..803c10e 100644 --- a/lib/Travelynx/Helper/HAFAS.pm +++ b/lib/Travelynx/Helper/HAFAS.pm @@ -125,22 +125,22 @@ sub get_route_timestamps_p { for my $stop ( $journey->route ) { my $name = $stop->{name}; $ret->{$name} = $ret->{ $stop->{eva} } = { - name => $stop->{name}, - eva => $stop->{eva}, - sched_arr => _epoch( $stop->{sched_arr} ), - sched_dep => _epoch( $stop->{sched_dep} ), - rt_arr => _epoch( $stop->{rt_arr} ), - rt_dep => _epoch( $stop->{rt_dep} ), - arr_delay => $stop->{arr_delay}, - dep_delay => $stop->{dep_delay}, - eva => $stop->{eva}, - load => $stop->{load}, - isCancelled => ( - ( $stop->{arr_cancelled} or not $stop->{sched_arr} ) - and - ( $stop->{dep_cancelled} or not $stop->{sched_dep} ) - ), + name => $stop->{name}, + eva => $stop->{eva}, + sched_arr => _epoch( $stop->{sched_arr} ), + sched_dep => _epoch( $stop->{sched_dep} ), + rt_arr => _epoch( $stop->{rt_arr} ), + rt_dep => _epoch( $stop->{rt_dep} ), + arr_delay => $stop->{arr_delay}, + dep_delay => $stop->{dep_delay}, + eva => $stop->{eva}, + load => $stop->{load} }; + if ( ( $stop->{arr_cancelled} or not $stop->{sched_arr} ) + and ( $stop->{dep_cancelled} or not $stop->{sched_dep} ) ) + { + $ret->{$name}{isCancelled} = 1; + } if ( $station_is_past and not $ret->{$name}{isCancelled} diff --git a/lib/Travelynx/Helper/IRIS.pm b/lib/Travelynx/Helper/IRIS.pm index 3222dad..eee7b58 100644 --- a/lib/Travelynx/Helper/IRIS.pm +++ b/lib/Travelynx/Helper/IRIS.pm @@ -179,27 +179,35 @@ sub route_diff { while ( $route_idx <= $#route and $sched_idx <= $#sched_route ) { if ( $route[$route_idx] eq $sched_route[$sched_idx] ) { - push( @json_route, [ $route[$route_idx], {}, undef ] ); + push( @json_route, [ $route[$route_idx], undef, {} ] ); $route_idx++; $sched_idx++; } # this branch is inefficient, but won't be taken frequently elsif ( not( grep { $_ eq $route[$route_idx] } @sched_route ) ) { - push( @json_route, [ $route[$route_idx], {}, 'additional' ], ); + push( @json_route, + [ $route[$route_idx], undef, { isAdditional => 1 } ], + ); $route_idx++; } else { - push( @json_route, [ $sched_route[$sched_idx], {}, 'cancelled' ], ); + push( @json_route, + [ $sched_route[$sched_idx], undef, { isCancelled => 1 } ], + ); $sched_idx++; } } while ( $route_idx <= $#route ) { - push( @json_route, [ $route[$route_idx], {}, 'additional' ], ); + push( @json_route, + [ $route[$route_idx], undef, { isAdditional => 1 } ], + ); $route_idx++; } while ( $sched_idx <= $#sched_route ) { - push( @json_route, [ $sched_route[$sched_idx], {}, 'cancelled' ], ); + push( @json_route, + [ $sched_route[$sched_idx], undef, { isCancelled => 1 } ], + ); $sched_idx++; } return @json_route; diff --git a/lib/Travelynx/Model/Journeys.pm b/lib/Travelynx/Model/Journeys.pm index f577236..0d47763 100755 --- a/lib/Travelynx/Model/Journeys.pm +++ b/lib/Travelynx/Model/Journeys.pm @@ -162,7 +162,7 @@ sub add { my @route; if ( not $route_has_start ) { - push( @route, [ $dep_station->{name}, {}, undef ] ); + push( @route, [ $dep_station->{name}, $dep_station->{eva}, {} ] ); } if ( $opt{route} ) { @@ -170,10 +170,11 @@ sub add { for my $station ( @{ $opt{route} } ) { my $station_info = $self->{stations}->search($station); if ($station_info) { - push( @route, [ $station_info->{name}, {}, undef ] ); + push( @route, + [ $station_info->{name}, $station_info->{eva}, {} ] ); } else { - push( @route, [ $station, {}, undef ] ); + push( @route, [ $station, undef, {} ] ); push( @unknown_stations, $station ); } } @@ -192,7 +193,7 @@ sub add { } if ( not $route_has_stop ) { - push( @route, [ $arr_station->{name}, {}, undef ] ); + push( @route, [ $arr_station->{name}, $arr_station->{eva}, {} ] ); } my $entry = { @@ -358,7 +359,7 @@ sub update { )->rows; } if ( exists $opt{route} ) { - my @new_route = map { [ $_, {}, undef ] } @{ $opt{route} }; + my @new_route = map { [ $_, undef, {} ] } @{ $opt{route} }; $rows = $db->update( 'journeys', { -- cgit v1.2.3