Merge branch 'main' into efa-support

1 files changed, 381 insertions, 5 deletions

diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index f9a2f1e..b328040 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -1,6 +1,7 @@
 package Travelynx::Command::database;
 
 # Copyright (C) 2020-2023 Birte Kristina Friesel
+# Copyright (C) 2025 networkException <git@nwex.de>
 #
 # SPDX-License-Identifier: AGPL-3.0-or-later
 use Mojo::Base 'Mojolicious::Command';
@@ -12,6 +13,7 @@ use JSON;
 use Travel::Status::DE::EFA;
 use Travel::Status::DE::HAFAS;
 use Travel::Status::DE::IRIS::Stations;
+use Travel::Status::MOTIS;
 
 has description => 'Initialize or upgrade database layout';
 
@@ -2692,6 +2694,338 @@ qq{select distinct checkout_station_id from in_transit where backend_id = 0;}
 	},
 
 	# v58 -> v59
+	# DB HAFAS is dead. Default to DB IRIS for now.
+	sub {
+		my ($db) = @_;
+		$db->query(
+			qq{
+				alter table users alter column backend_id set default 0;
+				update schema_version set version = 59;
+			}
+		);
+	},
+
+	# v59 -> v60
+	# Add bahn.de / DBRIS backend
+	sub {
+		my ($db) = @_;
+		$db->insert(
+			'backends',
+			{
+				iris  => 0,
+				hafas => 0,
+				efa   => 0,
+				ris   => 1,
+				name  => 'bahn.de',
+			},
+		);
+		$db->query(
+			qq{
+				update schema_version set version = 60;
+			}
+		);
+	},
+
+	# v60 -> v61
+	# Rename "ris" / "is_ris" to "dbris" / "is_dbris", as it is DB-specific
+	sub {
+		my ($db) = @_;
+		$db->query(
+			qq{
+				drop view in_transit_str;
+				drop view journeys_str;
+				drop view users_with_backend;
+				drop view follows_in_transit;
+				alter table backends rename column ris to dbris;
+				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.dbris as is_dbris,
+					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.dbris as is_dbris,
+					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 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, dbris, backend.name as backend_name
+					from users
+					left join backends as backend on users.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,
+					backend.iris as is_iris, backend.hafas as is_hafas,
+					backend.efa as is_efa, backend.dbris as is_dbris,
+					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 = 61;
+			}
+		);
+	},
+
+	# v61 -> v62
+	# Add MOTIS backend type, add RNV and transitous MOTIS backends
+	sub {
+		my ($db) = @_;
+		$db->query(
+			qq{
+				alter table backends add column motis bool default false;
+				alter table schema_version add column motis varchar(12);
+
+				create table stations_external_ids (
+					eva serial not null primary key,
+					backend_id smallint not null,
+					external_id text not null,
+
+					unique (backend_id, external_id),
+					foreign key (eva, backend_id) references stations (eva, source)
+				);
+
+				create view stations_with_external_ids as select
+					stations.*, stations_external_ids.external_id
+					from stations
+					left join stations_external_ids on
+						stations.eva = stations_external_ids.eva and
+						stations.source = stations_external_ids.backend_id
+					;
+
+				drop view in_transit_str;
+				drop view journeys_str;
+				drop view users_with_backend;
+				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.dbris as is_dbris,
+					backend.motis as is_motis,
+					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,
+					dep_station_external_id.external_id as dep_external_id,
+					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,
+					arr_station_external_id.external_id as arr_external_id,
+					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 stations_external_ids as dep_station_external_id on checkin_station_id = dep_station_external_id.eva and in_transit.backend_id = dep_station_external_id.backend_id
+					left join stations_external_ids as arr_station_external_id on checkout_station_id = arr_station_external_id.eva and in_transit.backend_id = arr_station_external_id.backend_id
+					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.dbris as is_dbris,
+					backend.motis as is_motis,
+					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,
+					dep_station_external_id.external_id as dep_external_id,
+					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,
+					arr_station_external_id.external_id as arr_external_id,
+					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 stations_external_ids as dep_station_external_id on checkin_station_id = dep_station_external_id.eva and journeys.backend_id = dep_station_external_id.backend_id
+					left join stations_external_ids as arr_station_external_id on checkout_station_id = arr_station_external_id.eva and journeys.backend_id = arr_station_external_id.backend_id
+					left join backends as backend on journeys.backend_id = backend.id
+					;
+				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, dbris, motis, backend.name as backend_name
+					from users
+					left join backends as backend on users.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,
+					backend.iris as is_iris, backend.hafas as is_hafas,
+					backend.efa as is_efa, backend.dbris as is_dbris,
+					backend.motis as is_motis,
+					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
+					;
+			}
+		);
+		$db->query(
+			qq{
+				update schema_version set version = 62;
+			}
+		);
+	},
+
+	# v62 -> v63
 	# Add EFA backend support
 	sub {
 		my ($db) = @_;
@@ -2893,7 +3227,7 @@ sub sync_stations {
 	}
 }
 
-sub sync_efa {
+sub sync_backends_efa {
 	my ($db) = @_;
 	for my $service ( Travel::Status::DE::EFA::get_services() ) {
 		my $present = $db->select(
@@ -2923,7 +3257,7 @@ sub sync_efa {
 		{ efa => $Travel::Status::DE::EFA::VERSION } );
 }
 
-sub sync_hafas {
+sub sync_backends_hafas {
 	my ($db) = @_;
 	for my $service ( Travel::Status::DE::HAFAS::get_services() ) {
 		my $present = $db->select(
@@ -2941,7 +3275,7 @@ sub sync_hafas {
 					iris  => 0,
 					hafas => 1,
 					efa   => 0,
-					ris   => 0,
+					dbris => 0,
 					name  => $service->{shortname},
 				},
 				{ on_conflict => undef }
@@ -2953,6 +3287,37 @@ sub sync_hafas {
 		{ hafas => $Travel::Status::DE::HAFAS::VERSION } );
 }
 
+sub sync_backends_motis {
+	my ($db) = @_;
+	for my $service ( Travel::Status::MOTIS::get_services() ) {
+		my $present = $db->select(
+			'backends',
+			'count(*) as count',
+			{
+				motis => 1,
+				name  => $service->{shortname}
+			}
+		)->hash->{count};
+		if ( not $present ) {
+			$db->insert(
+				'backends',
+				{
+					iris  => 0,
+					hafas => 0,
+					efa   => 0,
+					dbris => 0,
+					motis => 1,
+					name  => $service->{shortname},
+				},
+				{ on_conflict => undef }
+			);
+		}
+	}
+
+	$db->update( 'schema_version',
+		{ motis => $Travel::Status::MOTIS::VERSION } );
+}
+
 sub setup_db {
 	my ($db) = @_;
 	my $tx = $db->begin;
@@ -3048,7 +3413,7 @@ sub migrate_db {
 	else {
 		say
 "Synchronizing with Travel::Status::DE::EFA $Travel::Status::DE::EFA::VERSION";
-		sync_efa($db);
+		sync_backends_efa($db);
 	}
 
 	my $hafas_version = get_schema_version( $db, 'hafas' );
@@ -3059,7 +3424,18 @@ sub migrate_db {
 	else {
 		say
 "Synchronizing with Travel::Status::DE::HAFAS $Travel::Status::DE::HAFAS::VERSION";
-		sync_hafas($db);
+		sync_backends_hafas($db);
+	}
+
+	my $motis_version = get_schema_version( $db, 'motis' ) // '0';
+	say "Found backend table for Motis v${motis_version}";
+	if ( $motis_version eq $Travel::Status::MOTIS::VERSION ) {
+		say 'Backend table is up-to-date';
+	}
+	else {
+		say
+"Synchronizing with Travel::Status::MOTIS $Travel::Status::MOTIS::VERSION";
+		sync_backends_motis($db);
 	}
 
 	$db->update( 'schema_version',