diff options
Diffstat (limited to 'lib/Travelynx/Command')
-rw-r--r-- | lib/Travelynx/Command/database.pm | 574 | ||||
-rw-r--r-- | lib/Travelynx/Command/dumpconfig.pm | 1 | ||||
-rw-r--r-- | lib/Travelynx/Command/dumpstops.pm | 5 | ||||
-rw-r--r-- | lib/Travelynx/Command/influxdb.pm | 101 | ||||
-rw-r--r-- | lib/Travelynx/Command/integritycheck.pm | 201 | ||||
-rw-r--r-- | lib/Travelynx/Command/maintenance.pm | 16 | ||||
-rw-r--r-- | lib/Travelynx/Command/work.pm | 212 |
7 files changed, 884 insertions, 226 deletions
diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm index fb5ee80..a7d13a8 100644 --- a/lib/Travelynx/Command/database.pm +++ b/lib/Travelynx/Command/database.pm @@ -7,7 +7,9 @@ use Mojo::Base 'Mojolicious::Command'; use DateTime; use File::Slurp qw(read_file); +use List::Util qw(); use JSON; +use Travel::Status::DE::HAFAS; use Travel::Status::DE::IRIS::Stations; has description => 'Initialize or upgrade database layout'; @@ -1918,7 +1920,7 @@ my @migrations = ( # v49 -> v50 # travelynx 2.0 introduced proper HAFAS support, so there is no need for - # the 'FYI, here is some hAFAS data' kludge anymore. + # the 'FYI, here is some HAFAS data' kludge anymore. sub { my ($db) = @_; $db->query( @@ -1948,7 +1950,7 @@ my @migrations = ( }, # v51 -> v52 - # Explicitly encode backend type; preparation for multiple hAFAS backends + # Explicitly encode backend type; preparation for multiple HAFAS backends sub { my ($db) = @_; $db->query( @@ -2050,6 +2052,9 @@ my @migrations = ( } ); }, + + # v52 -> v53 + # Extend train_id to be compatible with more recent HAFAS versions sub { my ($db) = @_; $db->query( @@ -2166,6 +2171,524 @@ my @migrations = ( } ); }, + + # v53 -> v54 + # Retrofit lat/lon data onto routes logged before v2.7.8; ensure + # consistent name and eva entries as well. + sub { + my ($db) = @_; + + say +'Adding lat/lon to routes of journeys logged before v2.7.8 and improving consistency of name/eva data in very old route entries.'; + say 'This may take a while ...'; + + my %legacy_to_new; + if ( -r 'share/old_station_names.json' ) { + %legacy_to_new = %{ JSON->new->utf8->decode( + scalar read_file('share/old_station_names.json') + ) + }; + } + + my %latlon_by_eva; + my %latlon_by_name; + my $res = $db->select( 'stations', [ 'name', 'eva', 'lat', 'lon' ] ); + while ( my $row = $res->hash ) { + $latlon_by_eva{ $row->{eva} } = $row; + $latlon_by_name{ $row->{name} } = $row; + } + + my $total + = $db->select( 'journeys', 'count(*) as count' )->hash->{count}; + my $count = 0; + my $total_no_eva = 0; + my $total_no_latlon = 0; + + my $json = JSON->new; + + $res = $db->select( 'journeys_str', [ 'route', 'journey_id' ] ); + while ( my $row = $res->expand->hash ) { + my $no_eva = 0; + my $no_latlon = 0; + my $changed = 0; + my @route = @{ $row->{route} }; + for my $stop (@route) { + my $name = $stop->[0]; + my $eva = $stop->[1]; + + if ( not $eva and $stop->[2]{eva} ) { + $eva = $stop->[1] = 0 + $stop->[2]{eva}; + } + + if ( $stop->[2]{eva} and $eva and $eva == $stop->[2]{eva} ) { + delete $stop->[2]{eva}; + } + + if ( $stop->[2]{name} and $name eq $stop->[2]{name} ) { + delete $stop->[2]{name}; + } + + if ( not $eva ) { + if ( $latlon_by_name{$name} ) { + $eva = $stop->[1] = $latlon_by_name{$name}{eva}; + $changed = 1; + } + elsif ( $legacy_to_new{$name} + and $latlon_by_name{ $legacy_to_new{$name} } ) + { + $eva = $stop->[1] + = $latlon_by_name{ $legacy_to_new{$name} }{eva}; + $stop->[2]{lat} + = $latlon_by_name{ $legacy_to_new{$name} }{lat}; + $stop->[2]{lon} + = $latlon_by_name{ $legacy_to_new{$name} }{lon}; + $changed = 1; + } + else { + $no_eva = 1; + } + } + + if ( $stop->[2]{lat} and $stop->[2]{lon} ) { + next; + } + + if ( $eva and $latlon_by_eva{$eva} ) { + $stop->[2]{lat} = $latlon_by_eva{$eva}{lat}; + $stop->[2]{lon} = $latlon_by_eva{$eva}{lon}; + $changed = 1; + } + elsif ( $latlon_by_name{$name} ) { + $stop->[2]{lat} = $latlon_by_name{$name}{lat}; + $stop->[2]{lon} = $latlon_by_name{$name}{lon}; + $changed = 1; + } + elsif ( $legacy_to_new{$name} + and $latlon_by_name{ $legacy_to_new{$name} } ) + { + $stop->[2]{lat} + = $latlon_by_name{ $legacy_to_new{$name} }{lat}; + $stop->[2]{lon} + = $latlon_by_name{ $legacy_to_new{$name} }{lon}; + $changed = 1; + } + else { + $no_latlon = 1; + } + } + if ($no_eva) { + $total_no_eva += 1; + } + if ($no_latlon) { + $total_no_latlon += 1; + } + if ($changed) { + $db->update( + 'journeys', + { + route => $json->encode( \@route ), + }, + { id => $row->{journey_id} } + ); + } + if ( $count++ % 10000 == 0 ) { + printf( " %2.0f%% complete\n", $count * 100 / $total ); + } + } + say ' done'; + if ($total_no_eva) { + printf( " (%d of %d routes still lack some EVA IDs)\n", + $total_no_eva, $total ); + } + if ($total_no_latlon) { + printf( " (%d of %d routes still lack some lat/lon data)\n", + $total_no_latlon, $total ); + } + + $db->query( + qq{ + update schema_version set version = 54; + } + ); + }, + + # v54 -> v55 + # do not share stations between backends + sub { + my ($db) = @_; + $db->query( + qq{ + alter table schema_version add column hafas varchar(12); + alter table users drop column external_services; + alter table users add column backend_id smallint references backends (id) default 1; + alter table stations drop constraint stations_pkey; + alter table stations add unique (eva, source); + create index eva_by_source on stations (eva, source); + create index eva on stations (eva); + alter table related_stations drop constraint related_stations_eva_meta_key; + drop index rel_eva; + alter table related_stations add column backend_id smallint; + update related_stations set backend_id = 1; + alter table related_stations alter column backend_id set not null; + alter table related_stations add constraint backend_fk foreign key (backend_id) references backends (id); + alter table related_stations add unique (eva, meta, backend_id); + create index related_stations_eva_backend_key on related_stations (eva, backend_id); + } + ); + + # up until now, IRIS and DB HAFAS shared stations, with IRIS taking + # preference. As of v2.7, this is no longer the case. However, old DB + # HAFAS journeys may still reference IRIS-specific stations. So, we + # make all IRIS stations available as DB HAFAS stations as well. + my $total + = $db->select( 'stations', 'count(*) as count', { source => 0 } ) + ->hash->{count}; + my $count = 0; + + # Caveat: If this is a fresh installation, there are no IRIS stations + # in the database yet. So we have to populate it first. + if ( not $total ) { + say +'Preparing to untangle IRIS / HAFAS stations, this may take a while ...'; + $total = scalar Travel::Status::DE::IRIS::Stations::get_stations(); + for my $s ( Travel::Status::DE::IRIS::Stations::get_stations() ) { + my ( $ds100, $name, $eva, $lon, $lat ) = @{$s}; + if ( $ENV{__TRAVELYNX_TEST_MINI_IRIS} + and ( $eva < 8000000 or $eva > 8000100 ) ) + { + next; + } + $db->insert( + 'stations', + { + eva => $eva, + ds100 => $ds100, + name => $name, + lat => $lat, + lon => $lon, + source => 0, + archived => 0 + }, + ); + if ( $count++ % 1000 == 0 ) { + printf( " %2.0f%% complete\n", $count * 100 / $total ); + } + } + $count = 0; + } + + say 'Untangling IRIS / HAFAS stations, this may take a while ...'; + my $res = $db->query( + qq{ + select eva, ds100, name, lat, lon, archived + from stations + where source = 0; + } + ); + while ( my $row = $res->hash ) { + $db->insert( + 'stations', + { + eva => $row->{eva}, + ds100 => $row->{ds100}, + name => $row->{name}, + lat => $row->{lat}, + lon => $row->{lon}, + archived => $row->{archived}, + source => 1, + } + ); + if ( $count++ % 1000 == 0 ) { + printf( " %2.0f%% complete\n", $count * 100 / $total ); + } + } + + # Occasionally, IRIS checkins refer to stations that are not part of + # the Travel::Status::DE::IRIS database. Add those as HAFAS stops to + # satisfy the upcoming foreign key constraints. + + my %iris_has_eva; + $res = $db->query(qq{select eva from stations where source = 0;}); + while ( my $row = $res->hash ) { + $iris_has_eva{ $row->{eva} } = 1; + } + + my %hafas_by_eva; + $res = $db->query(qq{select * from stations where source = 1;}); + while ( my $row = $res->hash ) { + $hafas_by_eva{ $row->{eva} } = $row; + } + + my @iris_ref_stations; + $res + = $db->query( +qq{select distinct checkin_station_id from journeys where backend_id = 0;} + ); + while ( my $row = $res->hash ) { + push( @iris_ref_stations, $row->{checkin_station_id} ); + } + $res + = $db->query( +qq{select distinct checkout_station_id from journeys where backend_id = 0;} + ); + while ( my $row = $res->hash ) { + push( @iris_ref_stations, $row->{checkout_station_id} ); + } + $res + = $db->query( +qq{select distinct checkin_station_id from in_transit where backend_id = 0;} + ); + while ( my $row = $res->hash ) { + push( @iris_ref_stations, $row->{checkin_station_id} ); + } + $res + = $db->query( +qq{select distinct checkout_station_id from in_transit where backend_id = 0;} + ); + while ( my $row = $res->hash ) { + if ( $row->{checkout_station_id} ) { + push( @iris_ref_stations, $row->{checkout_station_id} ); + } + } + + @iris_ref_stations = List::Util::uniq @iris_ref_stations; + + for my $station (@iris_ref_stations) { + if ( not $iris_has_eva{$station} ) { + $hafas_by_eva{$station}{source} = 0; + $hafas_by_eva{$station}{archived} = 1; + $db->insert( 'stations', $hafas_by_eva{$station} ); + } + } + + $db->query( + qq{ + alter table in_transit add constraint in_transit_checkin_eva_fk + foreign key (checkin_station_id, backend_id) + references stations (eva, source); + alter table in_transit add constraint in_transit_checkout_eva_fk + foreign key (checkout_station_id, backend_id) + references stations (eva, source); + alter table journeys add constraint journeys_checkin_eva_fk + foreign key (checkin_station_id, backend_id) + references stations (eva, source); + alter table journeys add constraint journeys_checkout_eva_fk + foreign key (checkout_station_id, backend_id) + references stations (eva, source); + drop view in_transit_str; + drop view journeys_str; + drop view follows_in_transit; + create view in_transit_str as select + user_id, + backend.iris as is_iris, backend.hafas as is_hafas, + backend.efa as is_efa, backend.ris as is_ris, + backend.name as backend_name, in_transit.backend_id as backend_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, + checkin_station_id as dep_eva, + dep_station.ds100 as dep_ds100, + dep_station.name as dep_name, + dep_station.lat as dep_lat, + dep_station.lon as dep_lon, + 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, + checkout_station_id as arr_eva, + arr_station.ds100 as arr_ds100, + arr_station.name as arr_name, + arr_station.lat as arr_lat, + arr_station.lon as arr_lon, + polyline_id, + polylines.polyline as polyline, + visibility, + coalesce(visibility, users.public_level & 127) as effective_visibility, + cancelled, route, messages, user_data, + dep_platform, arr_platform, data + from in_transit + left join polylines on polylines.id = polyline_id + left join users on users.id = user_id + left join stations as dep_station on checkin_station_id = dep_station.eva and in_transit.backend_id = dep_station.source + left join stations as arr_station on checkout_station_id = arr_station.eva and in_transit.backend_id = arr_station.source + left join backends as backend on in_transit.backend_id = backend.id + ; + create view journeys_str as select + journeys.id as journey_id, user_id, + backend.iris as is_iris, backend.hafas as is_hafas, + backend.efa as is_efa, backend.ris as is_ris, + backend.name as backend_name, journeys.backend_id as backend_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, + checkin_station_id as dep_eva, + dep_station.ds100 as dep_ds100, + dep_station.name as dep_name, + dep_station.lat as dep_lat, + dep_station.lon as dep_lon, + 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, + checkout_station_id as arr_eva, + arr_station.ds100 as arr_ds100, + arr_station.name as arr_name, + arr_station.lat as arr_lat, + arr_station.lon as arr_lon, + polylines.polyline as polyline, + visibility, + coalesce(visibility, users.public_level & 127) as effective_visibility, + cancelled, edited, route, messages, user_data, + dep_platform, arr_platform + from journeys + left join polylines on polylines.id = polyline_id + left join users on users.id = user_id + left join stations as dep_station on checkin_station_id = dep_station.eva and journeys.backend_id = dep_station.source + left join stations as arr_station on checkout_station_id = arr_station.eva and journeys.backend_id = arr_station.source + left join backends as backend on journeys.backend_id = backend.id + ; + create view follows_in_transit as select + r1.subject_id as follower_id, user_id as followee_id, + users.name as followee_name, + train_type, train_line, train_no, train_id, + in_transit.backend_id as backend_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, + checkin_station_id as dep_eva, + dep_station.ds100 as dep_ds100, + dep_station.name as dep_name, + dep_station.lat as dep_lat, + dep_station.lon as dep_lon, + 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, + checkout_station_id as arr_eva, + arr_station.ds100 as arr_ds100, + arr_station.name as arr_name, + arr_station.lat as arr_lat, + arr_station.lon as arr_lon, + polyline_id, + polylines.polyline as polyline, + visibility, + coalesce(visibility, users.public_level & 127) as effective_visibility, + cancelled, route, messages, user_data, + dep_platform, arr_platform, data + from in_transit + left join polylines on polylines.id = polyline_id + left join users on users.id = user_id + left join relations as r1 on r1.predicate = 1 and r1.object_id = user_id + left join stations as dep_station on checkin_station_id = dep_station.eva and in_transit.backend_id = dep_station.source + left join stations as arr_station on checkout_station_id = arr_station.eva and in_transit.backend_id = arr_station.source + order by checkin_time desc + ; + create view users_with_backend as select + users.id as id, users.name as name, status, public_level, + email, password, registered_at, last_seen, + deletion_requested, deletion_notified, use_history, + accept_follows, notifications, profile, backend_id, iris, + hafas, efa, ris, backend.name as backend_name + from users + left join backends as backend on users.backend_id = backend.id + ; + update schema_version set version = 55; + update schema_version set hafas = '0'; + } + ); + say + 'This travelynx instance now has support for non-DB HAFAS backends.'; + say +'If the migration fails due to a deadlock, re-run it after stopping all background workers'; + }, + + # v55 -> v56 + # include backend data in dumpstops command + sub { + my ($db) = @_; + $db->query( + qq{ + create view stations_str as + select stations.name as name, + eva, lat, lon, + backends.name as backend, + iris as is_iris, + hafas as is_hafas, + efa as is_efa, + ris as is_ris + from stations + left join backends + on source = backends.id; + update schema_version set version = 56; + } + ); + }, + + # v56 -> v57 + # Berlin Hbf used to be divided between "Berlin Hbf" (8011160) and "Berlin + # Hbf (tief)" (8098160). Since 2024, both are called "Berlin Hbf". + # As there are some places in the IRIS backend where station names are + # mapped to EVA IDs, this is not good. As of 2.8.21, travelynx deals with + # this IRIS edge case (and probably similar edge cases in Karlsruhe). + # Rebuild stats to ensure no bogus data is in there. + sub { + my ($db) = @_; + $db->query( + qq{ + truncate journey_stats; + update schema_version set version = 57; + } + ); + }, + + # v57 -> v58 + # Add backend data to follows_in_transit + sub { + my ($db) = @_; + $db->query( + qq{ + drop view follows_in_transit; + create view follows_in_transit as select + r1.subject_id as follower_id, user_id as followee_id, + users.name as followee_name, + train_type, train_line, train_no, train_id, + backend.iris as is_iris, backend.hafas as is_hafas, + backend.efa as is_efa, backend.ris as is_ris, + backend.name as backend_name, in_transit.backend_id as backend_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, + checkin_station_id as dep_eva, + dep_station.ds100 as dep_ds100, + dep_station.name as dep_name, + dep_station.lat as dep_lat, + dep_station.lon as dep_lon, + 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, + checkout_station_id as arr_eva, + arr_station.ds100 as arr_ds100, + arr_station.name as arr_name, + arr_station.lat as arr_lat, + arr_station.lon as arr_lon, + polyline_id, + polylines.polyline as polyline, + visibility, + coalesce(visibility, users.public_level & 127) as effective_visibility, + cancelled, route, messages, user_data, + dep_platform, arr_platform, data + from in_transit + left join polylines on polylines.id = polyline_id + left join users on users.id = user_id + left join relations as r1 on r1.predicate = 1 and r1.object_id = user_id + left join stations as dep_station on checkin_station_id = dep_station.eva and in_transit.backend_id = dep_station.source + left join stations as arr_station on checkout_station_id = arr_station.eva and in_transit.backend_id = arr_station.source + left join backends as backend on in_transit.backend_id = backend.id + order by checkin_time desc + ; + update schema_version set version = 58; + } + ); + }, ); sub sync_stations { @@ -2197,7 +2720,7 @@ sub sync_stations { }, { on_conflict => \ -'(eva) do update set archived = false, source = 0, ds100 = EXCLUDED.ds100, name=EXCLUDED.name, lat=EXCLUDED.lat, lon=EXCLUDED.lon' +'(eva, source) do update set archived = false, source = 0, ds100 = EXCLUDED.ds100, name=EXCLUDED.name, lat=EXCLUDED.lat, lon=EXCLUDED.lon' } ); if ( $count++ % 1000 == 0 ) { @@ -2356,6 +2879,36 @@ sub sync_stations { } } +sub sync_backends { + my ($db) = @_; + for my $service ( Travel::Status::DE::HAFAS::get_services() ) { + my $present = $db->select( + 'backends', + 'count(*) as count', + { + hafas => 1, + name => $service->{shortname} + } + )->hash->{count}; + if ( not $present ) { + $db->insert( + 'backends', + { + iris => 0, + hafas => 1, + efa => 0, + ris => 0, + name => $service->{shortname}, + }, + { on_conflict => undef } + ); + } + } + + $db->update( 'schema_version', + { hafas => $Travel::Status::DE::HAFAS::VERSION } ); +} + sub setup_db { my ($db) = @_; my $tx = $db->begin; @@ -2422,9 +2975,9 @@ sub migrate_db { } my $iris_version = get_schema_version( $db, 'iris' ); - say "Found IRIS station database v${iris_version}"; + say "Found IRIS station table v${iris_version}"; if ( $iris_version eq $Travel::Status::DE::IRIS::Stations::VERSION ) { - say 'Station database is up-to-date'; + say 'Station table is up-to-date'; } else { eval { @@ -2443,6 +2996,17 @@ sub migrate_db { } } + my $hafas_version = get_schema_version( $db, 'hafas' ); + say "Found backend table for HAFAS v${hafas_version}"; + if ( $hafas_version eq $Travel::Status::DE::HAFAS::VERSION ) { + say 'Backend table is up-to-date'; + } + else { + say +"Synchronizing with Travel::Status::DE::HAFAS $Travel::Status::DE::HAFAS::VERSION"; + sync_backends($db); + } + $db->update( 'schema_version', { travelynx => $self->app->config->{version} } ); diff --git a/lib/Travelynx/Command/dumpconfig.pm b/lib/Travelynx/Command/dumpconfig.pm index 600ffb0..2c308c9 100644 --- a/lib/Travelynx/Command/dumpconfig.pm +++ b/lib/Travelynx/Command/dumpconfig.pm @@ -1,4 +1,5 @@ package Travelynx::Command::dumpconfig; + # Copyright (C) 2020-2023 Birte Kristina Friesel # # SPDX-License-Identifier: AGPL-3.0-or-later diff --git a/lib/Travelynx/Command/dumpstops.pm b/lib/Travelynx/Command/dumpstops.pm index c8ecd7a..4d20bbd 100644 --- a/lib/Travelynx/Command/dumpstops.pm +++ b/lib/Travelynx/Command/dumpstops.pm @@ -24,12 +24,13 @@ sub run { or die("open($filename): $!\n"); my $csv = Text::CSV->new( { eol => "\r\n" } ); - $csv->combine(qw(name eva lat lon source archived)); + $csv->combine(qw(name eva lat lon backend is_iris is_hafas)); print $fh $csv->string; my $iter = $self->app->stations->get_db_iterator; while ( my $row = $iter->hash ) { - $csv->combine( @{$row}{qw{name eva lat lon source archived}} ); + $csv->combine( + @{$row}{qw{name eva lat lon backend is_iris is_hafas}} ); print $fh $csv->string; } close($fh); diff --git a/lib/Travelynx/Command/influxdb.pm b/lib/Travelynx/Command/influxdb.pm index f3fc3de..4b779a2 100644 --- a/lib/Travelynx/Command/influxdb.pm +++ b/lib/Travelynx/Command/influxdb.pm @@ -29,7 +29,7 @@ sub run { my $active = $now->clone->subtract( months => 1 ); my @stats; - my @stations; + my @backend_stats; my @traewelling; push( @@ -85,50 +85,31 @@ sub run { ) ); - push( - @stations, - query_to_influx( - 'iris', - $db->select( - 'stations', - 'count(*) as count', - { - source => 0, - archived => 0 - } - )->hash->{count} - ) - ); - push( - @stations, - query_to_influx( - 'hafas', - $db->select( - 'stations', - 'count(*) as count', - { - source => 1, - archived => 0 - } - )->hash->{count} - ) - ); - push( - @stations, - query_to_influx( - 'archived', - $db->select( 'stations', 'count(*) as count', { archived => 1 } ) - ->hash->{count} - ) - ); - push( - @stations, - query_to_influx( - 'meta', - $db->select( 'related_stations', 'count(*) as count' ) - ->hash->{count} - ) - ); + my @backends = $self->app->stations->get_backends; + + for my $backend (@backends) { + push( + @backend_stats, + [ + $backend->{iris} ? 'IRIS' : $backend->{name}, + $db->select( + 'stations', + 'count(*) as count', + { + source => $backend->{id}, + archived => 0 + } + )->hash->{count}, + $db->select( + 'related_stations', + 'count(*) as count', + { + backend_id => $backend->{id}, + } + )->hash->{count} + ] + ); + } push( @traewelling, @@ -167,10 +148,18 @@ sub run { . $self->app->config->{influxdb}->{url} . ' stats ' . join( ',', @stats ) ); - $self->app->log->debug( 'POST ' - . $self->app->config->{influxdb}->{url} - . ' stations ' - . join( ',', @stations ) ); + for my $backend_entry (@backend_stats) { + $self->app->log->debug( + 'POST ' + . $self->app->config->{influxdb}->{url} + . ' stations,backend=' + . $backend_entry->[0] + . sprintf( + ' count=%d,meta=%d', + $backend_entry->[1], $backend_entry->[2] + ) + ); + } $self->app->log->debug( 'POST ' . $self->app->config->{influxdb}->{url} . ' traewelling ' @@ -181,10 +170,16 @@ sub run { $self->app->config->{influxdb}->{url}, 'stats ' . join( ',', @stats ) )->wait; - $self->app->ua->post_p( - $self->app->config->{influxdb}->{url}, - 'stations ' . join( ',', @stations ) - )->wait; + my $buf = q{}; + for my $backend_entry (@backend_stats) { + $buf + .= "\nstations,backend=" + . $backend_entry->[0] + . sprintf( ' count=%d,meta=%d', + $backend_entry->[1], $backend_entry->[2] ); + } + $self->app->ua->post_p( $self->app->config->{influxdb}->{url}, $buf ) + ->wait; $self->app->ua->post_p( $self->app->config->{influxdb}->{url}, 'traewelling ' . join( ',', @traewelling ) diff --git a/lib/Travelynx/Command/integritycheck.pm b/lib/Travelynx/Command/integritycheck.pm index 4894c3d..be5fe71 100644 --- a/lib/Travelynx/Command/integritycheck.pm +++ b/lib/Travelynx/Command/integritycheck.pm @@ -9,58 +9,60 @@ use List::Util qw(); use Travel::Status::DE::IRIS::Stations; sub run { - my ($self) = @_; - my $found = 0; - my $db = $self->app->pg->db; - - my $res1 = $db->query( - qq{ - select checkin_station_id - from journeys - left join stations on journeys.checkin_station_id = stations.eva - where stations.eva is null; - } - ); - - my $res2 = $db->query( - qq{ - select checkout_station_id - from journeys - left join stations on journeys.checkout_station_id = stations.eva - where stations.eva is null; - } - ); - - my %notified; - while ( my $row = $res1->hash ) { - my $eva = $row->{checkin_station_id}; - if ( not $found ) { - $found = 1; - say + my ( $self, $mode ) = @_; + my $found = 0; + my $db = $self->app->pg->db; + + if ( $mode eq 'all' or $mode eq 'unknown-evas' ) { + + my %notified; + my $res1 = $db->query( + qq{ + select checkin_station_id + from journeys + left join stations on journeys.checkin_station_id = stations.eva + where stations.eva is null; + } + ); + my $res2 = $db->query( + qq{ + select checkout_station_id + from journeys + left join stations on journeys.checkout_station_id = stations.eva + where stations.eva is null; + } + ); + + while ( my $row = $res1->hash ) { + my $eva = $row->{checkin_station_id}; + if ( not $found ) { + $found = 1; + say 'Journeys in the travelynx database contain the following unknown EVA IDs.'; - say '------------8<----------'; - say 'Travel::Status::DE::IRIS v' - . $Travel::Status::DE::IRIS::Stations::VERSION; - } - if ( not $notified{$eva} ) { - say $eva; - $notified{$eva} = 1; + say '------------8<----------'; + say 'Travel::Status::DE::IRIS v' + . $Travel::Status::DE::IRIS::Stations::VERSION; + } + if ( not $notified{$eva} ) { + say $eva; + $notified{$eva} = 1; + } } - } - while ( my $row = $res2->hash ) { - my $eva = $row->{checkout_station_id}; - if ( not $found ) { - $found = 1; - say + while ( my $row = $res2->hash ) { + my $eva = $row->{checkout_station_id}; + if ( not $found ) { + $found = 1; + say 'Journeys in the travelynx database contain the following unknown EVA IDs.'; - say '------------8<----------'; - say 'Travel::Status::DE::IRIS v' - . $Travel::Status::DE::IRIS::Stations::VERSION; - } - if ( not $notified{$eva} ) { - say $eva; - $notified{$eva} = 1; + say '------------8<----------'; + say 'Travel::Status::DE::IRIS v' + . $Travel::Status::DE::IRIS::Stations::VERSION; + } + if ( not $notified{$eva} ) { + say $eva; + $notified{$eva} = 1; + } } } @@ -70,42 +72,101 @@ sub run { $found = 0; } - my $rename = $self->app->renamed_station; + if ( $mode eq 'all' or $mode eq 'unknown-route-entries' ) { - my $res = $db->select( 'journeys', [ 'route', 'edited' ] )->expand; - while ( my $j = $res->hash ) { - if ( $j->{edited} & 0x0010 ) { - next; - } - my @stops = @{ $j->{route} // [] }; - for my $stop (@stops) { - my $stop_name = $stop->[0]; - if ( $rename->{ $stop->[0] } ) { - $stop->[0] = $rename->{ $stop->[0] }; + my %notified; + my $rename = $self->app->renamed_station; + my $res = $db->select( 'journeys', [ 'route', 'edited' ] )->expand; + + while ( my $j = $res->hash ) { + if ( $j->{edited} & 0x0010 ) { + next; + } + my @stops = @{ $j->{route} // [] }; + for my $stop (@stops) { + my $stop_name = $stop->[0]; + if ( $rename->{ $stop->[0] } ) { + $stop->[0] = $rename->{ $stop->[0] }; + } + } + my @unknown + = $self->app->stations->grep_unknown( map { $_->[0] } @stops ); + for my $stop_name (@unknown) { + if ( not $notified{$stop_name} ) { + if ( not $found ) { + say +'Journeys in the travelynx database contain the following unknown route entries.'; + say + 'Note that this check ignores manual route entries.'; + say +'All reports refer to routes obtained via HAFAS/IRIS.'; + say '------------8<----------'; + say 'Travel::Status::DE::IRIS v' + . $Travel::Status::DE::IRIS::Stations::VERSION; + $found = 1; + } + say $stop_name; + $notified{$stop_name} = 1; + } } } - my @unknown - = $self->app->stations->grep_unknown( map { $_->[0] } @stops ); - for my $stop_name (@unknown) { - if ( not $notified{$stop_name} ) { + } + + if ($found) { + say '------------8<----------'; + say ''; + $found = 0; + } + + if ( $mode eq 'all' or $mode eq 'checkout-eva-vs-route-eva' ) { + + my $res = $db->select( + 'journeys_str', + [ 'journey_id', 'sched_arr_ts', 'route', 'arr_name', 'arr_eva' ], + { backend_id => 0 } + )->expand; + + journey: while ( my $j = $res->hash ) { + my $found_in_route; + my $found_arr; + for my $stop ( @{ $j->{route} // [] } ) { + if ( not $stop->[1] ) { + next journey; + } + if ( $stop->[1] == $j->{arr_eva} ) { + $found_in_route = 1; + last; + } + if ( $stop->[2]{sched_arr} + and $j->{sched_arr_ts} + and $stop->[2]{sched_arr} == int( $j->{sched_arr_ts} ) ) + { + $found_arr = $stop; + } + } + if ( $found_arr and not $found_in_route ) { if ( not $found ) { + say q{}; say -'Journeys in the travelynx database contain the following unknown route entries.'; - say 'Note that this check ignores manual route entries.'; - say 'All reports refer to routes obtained via HAFAS/IRIS.'; +'The following journeys have route entries which do not agree with checkout EVA ID.'; + say +'checkout station ID (left) vs route entry with matching checkout time (right)'; say '------------8<----------'; - say 'Travel::Status::DE::IRIS v' - . $Travel::Status::DE::IRIS::Stations::VERSION; $found = 1; } - say $stop_name; - $notified{$stop_name} = 1; + printf( + "%7d %d (%s) vs %d (%s)\n", + $j->{journey_id}, $j->{arr_eva}, $j->{arr_name}, + $found_arr->[1], $found_arr->[0] + ); } } } + if ($found) { say '------------8<----------'; say ''; + $found = 0; } } diff --git a/lib/Travelynx/Command/maintenance.pm b/lib/Travelynx/Command/maintenance.pm index c9c7ed6..7baf762 100644 --- a/lib/Travelynx/Command/maintenance.pm +++ b/lib/Travelynx/Command/maintenance.pm @@ -153,22 +153,6 @@ sub run { } $tx->commit; - - # Computing stats may take a while, but we've got all time in the - # world here. This means users won't have to wait when loading their - # own journey log. - say 'Generating missing stats ...'; - for - my $user ( $db->select( 'users', ['id'], { status => 1 } )->hashes->each ) - { - $tx = $db->begin; - $self->app->journeys->generate_missing_stats( uid => $user->{id} ); - $self->app->journeys->get_stats( - uid => $user->{id}, - year => $now->year - ); - $tx->commit; - } } 1; diff --git a/lib/Travelynx/Command/work.pm b/lib/Travelynx/Command/work.pm index 129ff45..98f478a 100644 --- a/lib/Travelynx/Command/work.pm +++ b/lib/Travelynx/Command/work.pm @@ -47,94 +47,131 @@ sub run { my $arr = $entry->{arr_eva}; my $train_id = $entry->{train_id}; - if ( $train_id =~ m{[|]} ) { + if ( $entry->{is_hafas} ) { - $self->app->hafas->get_journey_p( trip_id => $train_id )->then( - sub { - my ($journey) = @_; + eval { - my $found_dep; - my $found_arr; - for my $stop ( $journey->route ) { - if ( $stop->loc->eva == $dep ) { - $found_dep = $stop; + $self->app->hafas->get_journey_p( + trip_id => $train_id, + service => $entry->{backend_name} + )->then( + sub { + my ($journey) = @_; + + my $found_dep; + my $found_arr; + for my $stop ( $journey->route ) { + if ( $stop->loc->eva == $dep ) { + $found_dep = $stop; + } + if ( $arr and $stop->loc->eva == $arr ) { + $found_arr = $stop; + last; + } } - if ( $arr and $stop->loc->eva == $arr ) { - $found_arr = $stop; - last; + if ( not $found_dep ) { + $self->app->log->debug( + "Did not find $dep within journey $train_id"); + return; } - } - if ( not $found_dep ) { - $self->app->log->debug( - "Did not find $dep within journey $train_id"); - return; - } - if ( $found_dep->rt_dep ) { - $self->app->in_transit->update_departure_hafas( - uid => $uid, - journey => $journey, - stop => $found_dep, - dep_eva => $dep, - arr_eva => $arr - ); - if ( $journey->class <= 16 - and $found_dep->rt_dep->epoch > $now->epoch ) - { - $self->app->add_wagonorder( $uid, 1, $train_id, - $found_dep->sched_dep, $journey->number ); - $self->app->add_stationinfo( $uid, 1, $journey->id, - $found_dep->loc->eva ); + if ( $found_dep->rt_dep ) { + $self->app->in_transit->update_departure_hafas( + uid => $uid, + journey => $journey, + stop => $found_dep, + dep_eva => $dep, + arr_eva => $arr + ); + if ( $entry->{backend_id} <= 1 + and $journey->class <= 16 + and $found_dep->rt_dep->epoch > $now->epoch ) + { + $self->app->add_wagonorder( + uid => $uid, + train_id => $journey->id, + is_departure => 1, + eva => $dep, + datetime => $found_dep->sched_dep, + train_type => $journey->type, + train_no => $journey->number, + ); + $self->app->add_stationinfo( $uid, 1, + $journey->id, $found_dep->loc->eva ); + } } - } - if ( $found_arr and $found_arr->rt_arr ) { - $self->app->in_transit->update_arrival_hafas( - uid => $uid, - journey => $journey, - stop => $found_arr, - dep_eva => $dep, - arr_eva => $arr - ); - if ( $journey->class <= 16 - and $found_arr->rt_arr->epoch - $now->epoch < 600 ) - { - $self->app->add_wagonorder( $uid, 0, $train_id, - $found_dep->sched_dep, $journey->number ); - $self->app->add_stationinfo( $uid, 0, $journey->id, - $found_dep->loc->eva, $found_arr->loc->eva ); + if ( $found_arr and $found_arr->rt_arr ) { + $self->app->in_transit->update_arrival_hafas( + uid => $uid, + journey => $journey, + stop => $found_arr, + dep_eva => $dep, + arr_eva => $arr + ); + if ( $entry->{backend_id} <= 1 + and $journey->class <= 16 + and $found_arr->rt_arr->epoch - $now->epoch + < 600 ) + { + $self->app->add_wagonorder( + uid => $uid, + train_id => $journey->id, + is_arrival => 1, + eva => $arr, + datetime => $found_arr->sched_dep, + train_type => $journey->type, + train_no => $journey->number, + ); + $self->app->add_stationinfo( $uid, 0, + $journey->id, $found_dep->loc->eva, + $found_arr->loc->eva ); + } } } - } - )->catch( - sub { - my ($err) = @_; - if ( $err =~ m{svcResL\[0\][.]err is (?:FAIL|PARAMETER)$} ) - { - # HAFAS do be weird. These are not actionable. - $self->app->log->debug("work($uid)/journey: $err"); - } - else { - $self->app->log->error("work($uid)/journey: $err"); + )->catch( + sub { + my ($err) = @_; + if ( $err + =~ m{svcResL\[0\][.]err is (?:FAIL|PARAMETER)$} ) + { + # HAFAS do be weird. These are not actionable. + $self->app->log->debug( +"work($uid) @ HAFAS $entry->{backend_name}: journey: $err" + ); + } + else { + $self->app->log->error( +"work($uid) @ HAFAS $entry->{backend_name}: journey: $err" + ); + } } - } - )->wait; - - if ( $arr - and $entry->{real_arr_ts} - and $now->epoch - $entry->{real_arr_ts} > 600 ) - { - $self->app->checkout_p( - station => $arr, - force => 2, - dep_eva => $dep, - arr_eva => $arr, - uid => $uid )->wait; + + if ( $arr + and $entry->{real_arr_ts} + and $now->epoch - $entry->{real_arr_ts} > 600 ) + { + $self->app->checkout_p( + station => $arr, + force => 2, + dep_eva => $dep, + arr_eva => $arr, + uid => $uid + )->wait; + } + }; + if ($@) { + $errors += 1; + $self->app->log->error( + "work($uid) @ HAFAS $entry->{backend_name}: $@"); } next; } + # TODO irgendwo ist hier ne race condition wo ein neuer checkin (in HAFAS) mit IRIS-Daten überschrieben wird. + # Die ganzen updates brauchen wirklich mal sanity checks mit train id ... + # Note: IRIS data is not always updated in real-time. Both departure and # arrival delays may take several minutes to appear, especially in case # of large-scale disturbances. We work around this by continuing to @@ -197,8 +234,15 @@ sub run { } else { $self->app->add_route_timestamps( $uid, $train, 1 ); - $self->app->add_wagonorder( $uid, 1, $train->train_id, - $train->sched_departure, $train->train_no ); + $self->app->add_wagonorder( + uid => $uid, + train_id => $train->train_id, + is_departure => 1, + eva => $dep, + datetime => $train->sched_departure, + train_type => $train->type, + train_no => $train->train_no + ); $self->app->add_stationinfo( $uid, 1, $train->train_id, $dep, $arr ); } @@ -206,7 +250,7 @@ sub run { }; if ($@) { $errors += 1; - $self->app->log->error("work($uid)/departure: $@"); + $self->app->log->error("work($uid) @ IRIS: departure: $@"); } eval { @@ -273,8 +317,15 @@ sub run { and $now->epoch > $entry->{real_arr_ts} ) ? 1 : 0 ); - $self->app->add_wagonorder( $uid, 0, $train->train_id, - $train->sched_departure, $train->train_no ); + $self->app->add_wagonorder( + uid => $uid, + train_id => $train->train_id, + is_arrival => 1, + eva => $arr, + datetime => $train->sched_departure, + train_type => $train->type, + train_no => $train->train_no + ); $self->app->add_stationinfo( $uid, 0, $train->train_id, $dep, $arr ); } @@ -289,14 +340,15 @@ sub run { )->catch( sub { my ($error) = @_; - $self->app->log->error("work($uid)/arrival: $error"); + $self->app->log->error( + "work($uid) @ IRIS: arrival: $error"); $errors += 1; } )->wait; } }; if ($@) { - $self->app->log->error("work($uid)/arrival: $@"); + $self->app->log->error("work($uid) @ IRIS: arrival: $@"); $errors += 1; } |