summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/Travelynx/Command/database.pm73
1 files changed, 66 insertions, 7 deletions
diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index 880dfb4..f2b79f3 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -7,6 +7,7 @@ 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;
@@ -2401,6 +2402,65 @@ my @migrations = (
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
@@ -2732,15 +2792,15 @@ sub sync_stations {
sub sync_backends {
my ($db) = @_;
- for my $service ( Travel::Status::DE::HAFAS::get_services()) {
+ for my $service ( Travel::Status::DE::HAFAS::get_services() ) {
$db->insert(
'backends',
{
- iris => 0,
+ iris => 0,
hafas => 1,
- efa => 0,
- ris => 0,
- name => $service->{shortname},
+ efa => 0,
+ ris => 0,
+ name => $service->{shortname},
},
{ on_conflict => undef }
);
@@ -2843,12 +2903,11 @@ sub migrate_db {
say 'Backend table is up-to-date';
}
else {
- say
+ say
"Synchronizing with Travel::Status::DE::HAFAS $Travel::Status::DE::HAFAS::VERSION";
sync_backends($db);
}
-
$db->update( 'schema_version',
{ travelynx => $self->app->config->{version} } );