summaryrefslogtreecommitdiff
path: root/lib/Travelynx/Command/database.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/Travelynx/Command/database.pm')
-rw-r--r--lib/Travelynx/Command/database.pm1179
1 files changed, 1159 insertions, 20 deletions
diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index 4f7c792..d13b2a7 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -1,10 +1,13 @@
package Travelynx::Command::database;
-# Copyright (C) 2020 Daniel Friesel
+
+# Copyright (C) 2020-2023 Birte Kristina Friesel
#
# SPDX-License-Identifier: AGPL-3.0-or-later
use Mojo::Base 'Mojolicious::Command';
use DateTime;
+use File::Slurp qw(read_file);
+use JSON;
use Travel::Status::DE::IRIS::Stations;
has description => 'Initialize or upgrade database layout';
@@ -12,13 +15,12 @@ has description => 'Initialize or upgrade database layout';
has usage => sub { shift->extract_usage };
sub get_schema_version {
- my ($db) = @_;
+ my ( $db, $key ) = @_;
my $version;
- eval {
- $version
- = $db->select( 'schema_version', ['version'] )->hash->{version};
- };
+ $key //= 'version';
+
+ eval { $version = $db->select( 'schema_version', [$key] )->hash->{$key}; };
if ($@) {
# If it failed, the version table does not exist -> run setup first.
@@ -1055,8 +1057,1085 @@ my @migrations = (
}
);
},
+
+ # v23 -> v24
+ # travelynx 1.22 warns about upcoming account deletion due to inactivity
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table users add column deletion_notified timestamptz;
+ comment on column users.deletion_notified is 'Time at which warning about upcoming account deletion due to inactivity was sent';
+ update schema_version set version = 24;
+ }
+ );
+ },
+
+ # v24 -> v25
+ # travelynx 1.23 adds optional links to external services, e.g.
+ # DBF or bahn.expert departure boards
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table users add column external_services smallint;
+ comment on column users.external_services is 'Which external service to use for stationboard or routing links';
+ update schema_version set version = 25;
+ }
+ );
+ },
+
+ # v25 -> v26
+ # travelynx 1.24 adds local transit connections and needs to know targets
+ # for that to work, as local transit does not support checkins yet.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ create table localtransit (
+ user_id integer not null references users (id) primary key,
+ data jsonb
+ );
+ create view user_transit as select
+ id,
+ use_history,
+ localtransit.data as data
+ from users
+ left join localtransit on localtransit.user_id = id
+ ;
+ update schema_version set version = 26;
+ }
+ );
+ },
+
+ # v26 -> v27
+ # add list of stations that are not (or no longer) present in T-S-DE-IRIS
+ # (in this case, stations that were removed up to 1.74)
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table schema_version
+ add column iris varchar(12);
+ create table stations (
+ eva int not null primary key,
+ ds100 varchar(16) not null,
+ name varchar(64) not null,
+ lat real not null,
+ lon real not null,
+ source smallint not null,
+ archived bool not null
+ );
+ update schema_version set version = 27;
+ update schema_version set iris = '0';
+ }
+ );
+ },
+
+ # v27 -> v28
+ # add ds100, name, and lat/lon from stations table to journeys_str / in_transit_str
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view journeys_str;
+ drop view in_transit_str;
+ 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,
+ 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,
+ cancelled, edited, route, messages, user_data,
+ dep_platform, arr_platform
+ from journeys
+ left join polylines on polylines.id = polyline_id
+ left join stations as dep_station on checkin_station_id = dep_station.eva
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ 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,
+ 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,
+ cancelled, route, messages, user_data,
+ dep_platform, arr_platform, data
+ from in_transit
+ left join polylines on polylines.id = polyline_id
+ left join stations as dep_station on checkin_station_id = dep_station.eva
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ update schema_version set version = 28;
+ }
+ );
+ },
+
+ # v28 -> v29
+ # add pre-migration travelynx version. This way, a failed migration can
+ # print a helpful "git checkout" command.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table schema_version
+ add column travelynx varchar(64);
+ update schema_version set version = 29;
+ }
+ );
+ },
+
+ # 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', [ 'route', 'user_id' ] );
+ 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', [ 'route', 'journey_id' ] );
+ while ( my $row = $res->expand->hash ) {
+ 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;
+ }
+ );
+ },
+
+ # v30 -> v31
+ # travelynx v1.29.17 introduces links to conflicting journeys.
+ # These require changes to statistics data.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ truncate journey_stats;
+ update schema_version set version = 31;
+ }
+ );
+ },
+
+ # v31 -> v32
+ # travelynx v1.29.18 improves above-mentioned conflict links.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ truncate journey_stats;
+ update schema_version set version = 32;
+ }
+ );
+ },
+
+ # v32 -> v33
+ # add optional per-status visibility that overrides global visibility
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table journeys add column visibility smallint;
+ alter table in_transit add column visibility smallint;
+ drop view journeys_str;
+ drop view in_transit_str;
+ 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,
+ 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,
+ cancelled, edited, route, messages, user_data,
+ dep_platform, arr_platform
+ from journeys
+ left join polylines on polylines.id = polyline_id
+ left join stations as dep_station on checkin_station_id = dep_station.eva
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ 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,
+ 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,
+ cancelled, route, messages, user_data,
+ dep_platform, arr_platform, data
+ from in_transit
+ left join polylines on polylines.id = polyline_id
+ left join stations as dep_station on checkin_station_id = dep_station.eva
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ }
+ );
+ my $res = $db->select( 'users', [ 'id', 'public_level' ] );
+ while ( my $row = $res->hash ) {
+ my $old_level = $row->{public_level};
+
+ # status default: unlisted
+ my $new_level = 30;
+ if ( $old_level & 0x01 ) {
+
+ # status: account required
+ $new_level = 80;
+ }
+ if ( $old_level & 0x02 ) {
+
+ # status: public
+ $new_level = 100;
+ }
+ if ( $old_level & 0x04 ) {
+
+ # comment public
+ $new_level |= 0x80;
+ }
+ if ( $old_level & 0x10 ) {
+
+ # past: account required
+ $new_level |= 0x100;
+ }
+ if ( $old_level & 0x20 ) {
+
+ # past: public
+ $new_level |= 0x200;
+ }
+ if ( $old_level & 0x40 ) {
+
+ # past: infinite (default is 4 weeks)
+ $new_level |= 0x400;
+ }
+ my $r = $db->update(
+ 'users',
+ { public_level => $new_level },
+ { id => $row->{id} }
+ )->rows;
+ if ( $r != 1 ) {
+ die("oh no");
+ }
+ }
+ $db->update( 'schema_version', { version => 33 } );
+ },
+
+ # v33 -> v34
+ # add polyline_id to in_transit_str
+ # (https://github.com/derf/travelynx/issues/66)
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view in_transit_str;
+ 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,
+ 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,
+ cancelled, route, messages, user_data,
+ dep_platform, arr_platform, data
+ from in_transit
+ left join polylines on polylines.id = polyline_id
+ left join stations as dep_station on checkin_station_id = dep_station.eva
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ update schema_version set version = 34;
+ }
+ );
+ },
+
+ # v34 -> v35
+ sub {
+ my ($db) = @_;
+
+ # 1 : follows
+ # 2 : follow requested
+ # 3 : is blocked by
+ $db->query(
+ qq{
+ create table relations (
+ subject_id integer not null references users (id),
+ predicate smallint not null,
+ object_id integer not null references users (id),
+ primary key (subject_id, object_id)
+ );
+ create view followers as select
+ relations.object_id as self_id,
+ users.id as id,
+ users.name as name
+ from relations
+ join users on relations.subject_id = users.id
+ where predicate = 1;
+ create view followees as select
+ relations.subject_id as self_id,
+ users.id as id,
+ users.name as name
+ from relations
+ join users on relations.object_id = users.id
+ where predicate = 1;
+ create view follow_requests as select
+ relations.object_id as self_id,
+ users.id as id,
+ users.name as name
+ from relations
+ join users on relations.subject_id = users.id
+ where predicate = 2;
+ create view blocked_users as select
+ relations.object_id as self_id,
+ users.id as id,
+ users.name as name
+ from relations
+ join users on relations.subject_id = users.id
+ where predicate = 3;
+ update schema_version set version = 35;
+ }
+ );
+ },
+
+ # v35 -> v36
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table relations
+ add column ts timestamptz not null;
+ alter table users
+ add column accept_follows smallint default 0;
+ update schema_version set version = 36;
+ }
+ );
+ },
+
+ # v36 -> v37
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table users
+ add column notifications smallint default 0,
+ add column profile jsonb;
+ update schema_version set version = 37;
+ }
+ );
+ },
+
+ # v37 -> v38
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view followers;
+ create view followers as select
+ relations.object_id as self_id,
+ users.id as id,
+ users.name as name,
+ users.accept_follows as accept_follows,
+ r2.predicate as inverse_predicate
+ from relations
+ join users on relations.subject_id = users.id
+ left join relations as r2 on relations.subject_id = r2.object_id
+ where relations.predicate = 1;
+ update schema_version set version = 38;
+ }
+ );
+ },
+
+ # v38 -> v39
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view followers;
+ create view followers as select
+ relations.object_id as self_id,
+ users.id as id,
+ users.name as name,
+ users.accept_follows as accept_follows,
+ r2.predicate as inverse_predicate
+ from relations
+ join users on relations.subject_id = users.id
+ left join relations as r2
+ on relations.subject_id = r2.object_id
+ and relations.object_id = r2.subject_id
+ where relations.predicate = 1;
+ update schema_version set version = 39;
+ }
+ );
+ },
+
+ # v39 -> v40
+ # distinguish between public / travelynx / followers / private visibility
+ # for the history page, just like status visibility.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table users alter public_level type integer;
+ }
+ );
+ my $res = $db->select( 'users', [ 'id', 'public_level' ] );
+ while ( my $row = $res->hash ) {
+ my $old_level = $row->{public_level};
+
+ # checkin and comment visibility remain unchanged
+ my $new_level = $old_level & 0x00ff;
+
+ # past: account required
+ if ( $old_level & 0x100 ) {
+ $new_level |= 80 << 8;
+ }
+
+ # past: public
+ elsif ( $old_level & 0x200 ) {
+ $new_level |= 100 << 8;
+ }
+
+ # past: private
+ else {
+ $new_level |= 10 << 8;
+ }
+
+ # past: infinite (default is 4 weeks)
+ if ( $old_level & 0x400 ) {
+ $new_level |= 0x10000;
+ }
+
+ # show past journey on status page
+ if ( $old_level & 0x800 ) {
+ $new_level |= 0x8000;
+ }
+
+ my $r = $db->update(
+ 'users',
+ { public_level => $new_level },
+ { id => $row->{id} }
+ )->rows;
+ if ( $r != 1 ) {
+ die("oh no");
+ }
+ }
+ $db->update( 'schema_version', { version => 40 } );
+ },
+
+ # v40 -> v41
+ # Compute effective visibility in in_transit_str and journeys_str.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view in_transit_str;
+ drop view journeys_str;
+ 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,
+ 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
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ 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,
+ 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
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ update schema_version set version = 41;
+ }
+ );
+ },
+
+ # v41 -> v42
+ # adds current followee checkins
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ 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,
+ 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
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ ;
+ update schema_version set version = 42;
+ }
+ );
+ },
+
+ # v42 -> v43
+ # list sent and received follow requests
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter view follow_requests rename to rx_follow_requests;
+ create view tx_follow_requests as select
+ relations.subject_id as self_id,
+ users.id as id,
+ users.name as name
+ from relations
+ join users on relations.object_id = users.id
+ where predicate = 2;
+ update schema_version set version = 43;
+ }
+ );
+ },
+
+ # v43 -> v44
+ # show inverse relation in followees as well
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view followees;
+ create view followees as select
+ relations.subject_id as self_id,
+ users.id as id,
+ users.name as name,
+ r2.predicate as inverse_predicate
+ from relations
+ join users on relations.object_id = users.id
+ left join relations as r2
+ on relations.subject_id = r2.object_id
+ and relations.object_id = r2.subject_id
+ where relations.predicate = 1;
+ update schema_version set version = 44;
+ }
+ );
+ },
+
+ # v44 -> v45
+ # prepare for HAFAS support: many HAFAS stations do not have DS100 codes
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table stations alter column ds100 drop not null;
+ update schema_version set version = 45;
+ }
+ );
+ },
+
+ # v45 -> v46
+ # Switch to Traewelling OAuth2 authentication.
+ # E-Mail is no longer needed.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view traewelling_str;
+ create view traewelling_str as select
+ user_id, push_sync, pull_sync, errored, token, data,
+ extract(epoch from latest_run) as latest_run_ts
+ from traewelling
+ ;
+ alter table traewelling drop column email;
+ update schema_version set version = 46;
+ }
+ );
+ },
+
+ # v46 -> v47
+ # sort followee checkins by checkin time
+ # (descending / most recent first, like a timeline)
+ 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,
+ 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
+ left join stations as arr_station on checkout_station_id = arr_station.eva
+ order by checkin_time desc
+ ;
+ update schema_version set version = 47;
+ }
+ );
+ },
+
+ # v47 -> v48
+ # Store Traewelling refresh tokens; store expiry as explicit column.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table traewelling
+ add column refresh_token text,
+ add column expiry timestamptz;
+ drop view traewelling_str;
+ create view traewelling_str as select
+ user_id, push_sync, pull_sync, errored,
+ token, refresh_token, data,
+ extract(epoch from latest_run) as latest_run_ts,
+ extract(epoch from expiry) as expiry_ts
+ from traewelling
+ ;
+ update schema_version set version = 48;
+ }
+ );
+ },
+
+ # v48 -> v49
+ # create indexes for common queries
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ create index uid_real_departure_idx on journeys (user_id, real_departure);
+ update schema_version set version = 49;
+ }
+ );
+ },
+
+ # v49 -> v50
+ # travelynx 2.0 introduced proper HAFAS support, so there is no need for
+ # the 'FYI, here is some hAFAS data' kludge anymore.
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ drop view user_transit;
+ drop table localtransit;
+ update schema_version set version = 50;
+ }
+ );
+ },
+
+ # v50 -> v51
+ # store related HAFAS stations
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ create table related_stations (
+ eva integer not null,
+ meta integer not null,
+ unique (eva, meta)
+ );
+ create index rel_eva on related_stations (eva);
+ update schema_version set version = 51;
+ }
+ );
+ },
);
+sub sync_stations {
+ my ( $db, $iris_version ) = @_;
+
+ $db->update( 'schema_version',
+ { iris => $Travel::Status::DE::IRIS::Stations::VERSION } );
+
+ say 'Updating stations table, this may take a while ...';
+ my $total = scalar Travel::Status::DE::IRIS::Stations::get_stations();
+ my $count = 0;
+ 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
+ },
+ {
+ on_conflict => \
+'(eva) do update set archived = false, source = 0, ds100 = EXCLUDED.ds100, name=EXCLUDED.name, lat=EXCLUDED.lat, lon=EXCLUDED.lon'
+ }
+ );
+ if ( $count++ % 1000 == 0 ) {
+ printf( " %2.0f%% complete\n", $count * 100 / $total );
+ }
+ }
+ say ' done';
+
+ 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
+ limit 1;
+ }
+ )->hash;
+
+ 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
+ limit 1;
+ }
+ )->hash;
+
+ if ( $res1 or $res2 ) {
+ say 'Dropping stats cache for archived stations ...';
+ $db->query('truncate journey_stats;');
+ }
+
+ say 'Updating archived stations ...';
+ my $old_stations
+ = JSON->new->utf8->decode( scalar read_file('share/old_stations.json') );
+ if ( $ENV{__TRAVELYNX_TEST_MINI_IRIS} ) {
+ $old_stations = [];
+ }
+ for my $s ( @{$old_stations} ) {
+ $db->insert(
+ 'stations',
+ {
+ eva => $s->{eva},
+ ds100 => $s->{ds100},
+ name => $s->{name},
+ lat => $s->{latlong}[0],
+ lon => $s->{latlong}[1],
+ source => 0,
+ archived => 1
+ },
+ { on_conflict => undef }
+ );
+ }
+
+ if ( $iris_version == 0 ) {
+ say 'Applying EVA ID changes ...';
+ for my $change (
+ [ 721394, 301002, 'RKBP: Kronenplatz (U), Karlsruhe' ],
+ [
+ 721356, 901012,
+ 'RKME: Ettlinger Tor/Staatstheater (U), Karlsruhe'
+ ],
+ )
+ {
+ my ( $old, $new, $desc ) = @{$change};
+ my $rows = $db->update(
+ 'journeys',
+ { checkout_station_id => $new },
+ { checkout_station_id => $old }
+ )->rows;
+ $rows += $db->update(
+ 'journeys',
+ { checkin_station_id => $new },
+ { checkin_station_id => $old }
+ )->rows;
+ if ($rows) {
+ say "$desc ($old -> $new) : $rows rows";
+ }
+ }
+ }
+
+ say 'Checking for unknown EVA IDs ...';
+ my $found = 0;
+
+ $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;
+ }
+ );
+
+ $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 '';
+ 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 '';
+ say '------------8<----------';
+ say 'Travel::Status::DE::IRIS v'
+ . $Travel::Status::DE::IRIS::Stations::VERSION;
+ }
+ if ( not $notified{$eva} ) {
+ say $eva;
+ $notified{$eva} = 1;
+ }
+ }
+
+ if ($found) {
+ say '------------8<----------';
+ say '';
+ say
+'Due to a conceptual flaw in past travelynx releases, your database contains unknown EVA IDs.';
+ say
+'Please file a bug report titled "Missing EVA IDs after DB migration" at https://github.com/derf/travelynx/issues';
+ say 'and include the list shown above in the bug report.';
+ say
+'If you do not have a GitHub account, please send an E-Mail to derf+travelynx@finalrewind.org instead.';
+ say '';
+ say 'This issue does not affect usability or long-term data integrity,';
+ say 'and handling it is not time-critical.';
+ say
+'Past journeys referencing unknown EVA IDs may have inaccurate distance statistics,';
+ say
+'but this will be resolved once a future release handles those EVA IDs.';
+ say 'Note that this issue was already present in previous releases.';
+ }
+ else {
+ say 'None found.';
+ }
+}
+
sub setup_db {
my ($db) = @_;
my $tx = $db->begin;
@@ -1070,31 +2149,86 @@ sub setup_db {
}
}
+sub failure_hints {
+ my ($old_version) = @_;
+ say STDERR 'This travelynx instance has reached an undefined state:';
+ say STDERR
+'The source code is expecting a different schema version than present in the database.';
+ say STDERR
+'Please file a detailed bug report at <https://github.com/derf/travelynx/issues>';
+ say STDERR 'or send an e-mail to derf+travelynx@finalrewind.org.';
+ if ($old_version) {
+ say STDERR '';
+ say STDERR
+ "The last migration was performed with travelynx v${old_version}.";
+ say STDERR
+'You may be able to return to a working state with the following command:';
+ say STDERR "git checkout ${old_version}";
+ say STDERR '';
+ say STDERR 'We apologize for any inconvenience.';
+ }
+}
+
sub migrate_db {
- my ($db) = @_;
+ my ( $self, $db ) = @_;
my $tx = $db->begin;
my $schema_version = get_schema_version($db);
say "Found travelynx schema v${schema_version}";
+ my $old_version;
+
+ if ( $schema_version >= 29 ) {
+ $old_version = get_schema_version( $db, 'travelynx' );
+ }
+
if ( $schema_version == @migrations ) {
- say "Database layout is up-to-date";
+ say 'Database layout is up-to-date';
+ }
+ else {
+ eval {
+ for my $i ( $schema_version .. $#migrations ) {
+ printf( "Updating to v%d ...\n", $i + 1 );
+ $migrations[$i]($db);
+ }
+ say 'Update complete.';
+ };
+ if ($@) {
+ say STDERR "Migration failed: $@";
+ say STDERR "Rolling back to v${schema_version}";
+ failure_hints($old_version);
+ exit(1);
+ }
}
- eval {
- for my $i ( $schema_version .. $#migrations ) {
- printf( "Updating to v%d ...\n", $i + 1 );
- $migrations[$i]($db);
+ my $iris_version = get_schema_version( $db, 'iris' );
+ say "Found IRIS station database v${iris_version}";
+ if ( $iris_version eq $Travel::Status::DE::IRIS::Stations::VERSION ) {
+ say 'Station database is up-to-date';
+ }
+ else {
+ eval {
+ say
+"Synchronizing with Travel::Status::DE::IRIS $Travel::Status::DE::IRIS::Stations::VERSION";
+ sync_stations( $db, $iris_version );
+ say 'Synchronization complete.';
+ };
+ if ($@) {
+ say STDERR "Synchronization failed: $@";
+ if ( $schema_version != @migrations ) {
+ say STDERR "Rolling back to v${schema_version}";
+ failure_hints($old_version);
+ }
+ exit(1);
}
- };
- if ($@) {
- say STDERR "Migration failed: $@";
- say STDERR "Rolling back to v${schema_version}";
- exit(1);
}
+ $db->update( 'schema_version',
+ { travelynx => $self->app->config->{version} } );
+
if ( get_schema_version($db) == @migrations ) {
$tx->commit;
+ say 'Changes committed to database. Have a nice day.';
}
else {
printf STDERR (
@@ -1103,6 +2237,8 @@ sub migrate_db {
get_schema_version($db)
);
say STDERR "Rolling back to v${schema_version}";
+ say STDERR "";
+ failure_hints($old_version);
exit(1);
}
}
@@ -1121,10 +2257,13 @@ sub run {
if ( not defined get_schema_version($db) ) {
setup_db($db);
}
- migrate_db($db);
+ $self->migrate_db($db);
}
elsif ( $command eq 'has-current-schema' ) {
- if ( get_schema_version($db) == @migrations ) {
+ if ( get_schema_version($db) == @migrations
+ and get_schema_version( $db, 'iris' ) eq
+ $Travel::Status::DE::IRIS::Stations::VERSION )
+ {
say "yes";
}
else {
@@ -1149,5 +2288,5 @@ __END__
Recommended workflow:
> systemctl stop travelynx
- > perl index.pl migrate
+ > perl index.pl database migrate
> systemctl start travelynx
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-05 16:22:23 +0000