summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rwxr-xr-xlib/Travelynx.pm81
-rw-r--r--lib/Travelynx/Command/database.pm113
-rw-r--r--lib/Travelynx/Command/work.pm34
-rwxr-xr-xlib/Travelynx/Controller/Traveling.pm4
-rw-r--r--templates/_cancelled.html.ep2
-rw-r--r--templates/_checked_in.html.ep6
-rw-r--r--templates/journey.html.ep8
-rw-r--r--templates/landingpage.html.ep2
8 files changed, 186 insertions, 64 deletions
diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index 981a8e7..209a471 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -289,11 +289,23 @@ sub startup {
checkout_time => $now,
edited => 0x3fff,
cancelled => $opt{cancelled} ? 1 : 0,
- route => $dep_station->[1] . '|' . $arr_station->[1],
+ route => JSON->new->encode(
+ [
+ [
+ $dep_station->[1], undef,
+ $opt{sched_departure}->epoch,
+ ],
+ [
+ $arr_station->[1], $opt{sched_arrival}->epoch,
+ undef
+ ]
+ ]
+ ),
};
if ( $opt{comment} ) {
- $entry->{messages} = '0:' . $opt{comment};
+ $entry->{messages}
+ = JSON->new->encode( [ [ 0, $opt{comment} ] ] );
}
my $journey_id = undef;
@@ -344,6 +356,7 @@ sub startup {
}
eval {
+ my $json = JSON->new;
$self->pg->db->insert(
'in_transit',
{
@@ -364,13 +377,14 @@ sub startup {
train_id => $train->train_id,
sched_departure => $train->sched_departure,
real_departure => $train->departure,
- route => join( '|', $train->route ),
- messages => join(
- '|',
- map {
- ( $_->[0] ? $_->[0]->epoch : q{} ) . ':'
- . $_->[1]
- } $train->messages
+ route => $json->encode(
+ [ map { [$_] } $train->route ]
+ ),
+ messages => $json->encode(
+ [
+ map { [ $_->[0]->epoch, $_->[1] ] }
+ $train->messages
+ ]
)
}
);
@@ -560,6 +574,7 @@ sub startup {
if ( defined $train ) {
$has_arrived = $train->arrival->epoch < $now->epoch ? 1 : 0;
+ my $json = JSON->new;
$db->update(
'in_transit',
{
@@ -568,14 +583,14 @@ sub startup {
sched_arrival => $train->sched_arrival,
real_arrival => $train->arrival,
cancelled => $train->arrival_is_cancelled ? 1 : 0,
- route => join( '|', $train->route ),
- messages => join(
- '|',
- map {
- ( $_->[0] ? $_->[0]->epoch : q{} ) . ':'
- . $_->[1]
- } $train->messages
- ),
+ route =>
+ $json->encode( [ map { [$_] } $train->route ] ),
+ messages => $json->encode(
+ [
+ map { [ $_->[0]->epoch, $_->[1] ] }
+ $train->messages
+ ]
+ )
},
{ user_id => $uid }
);
@@ -1725,7 +1740,7 @@ sub startup {
my $res = $db->select( 'journeys_str', '*', \%where, \%order );
- for my $entry ( $res->hashes->each ) {
+ for my $entry ( $res->expand->hashes->each ) {
my $ref = {
id => $entry->{journey_id},
@@ -1740,20 +1755,16 @@ sub startup {
checkout => epoch_to_dt( $entry->{checkout_ts} ),
sched_arrival => epoch_to_dt( $entry->{sched_arr_ts} ),
rt_arrival => epoch_to_dt( $entry->{real_arr_ts} ),
- messages => $entry->{messages}
- ? [ split( qr{[|]}, $entry->{messages} ) ]
- : undef,
- route => $entry->{route}
- ? [ split( qr{[|]}, $entry->{route} ) ]
- : undef,
- edited => $entry->{edited},
+ messages => $entry->{messages},
+ route => $entry->{route},
+ edited => $entry->{edited},
};
if ( $opt{verbose} ) {
$ref->{cancelled} = $entry->{cancelled};
my @parsed_messages;
for my $message ( @{ $ref->{messages} // [] } ) {
- my ( $ts, $msg ) = split( qr{:}, $message );
+ my ( $ts, $msg ) = @{$message};
push( @parsed_messages, [ epoch_to_dt($ts), $msg ] );
}
$ref->{messages} = [ reverse @parsed_messages ];
@@ -1774,7 +1785,7 @@ sub startup {
( $km, $skip )
= $self->get_travel_distance( $ref->{from_name},
$ref->{to_name},
- [ $ref->{from_name}, $ref->{to_name} ] );
+ [ [ $ref->{from_name} ], [ $ref->{to_name} ] ] );
$ref->{km_beeline} = $km;
$ref->{skip_beeline} = $skip;
my $kmh_divisor
@@ -1819,11 +1830,12 @@ sub startup {
my $now = DateTime->now( time_zone => 'Europe/Berlin' );
my $in_transit
- = $db->select( 'in_transit_str', '*', { user_id => $uid } )->hash;
+ = $db->select( 'in_transit_str', '*', { user_id => $uid } )
+ ->expand->hash;
if ($in_transit) {
- my @route = split( qr{[|]}, $in_transit->{route} // q{} );
+ my @route = @{ $in_transit->{route} // [] };
my @route_after;
my $is_after = 0;
for my $station (@route) {
@@ -1831,7 +1843,7 @@ sub startup {
if ($is_after) {
push( @route_after, $station );
}
- if ( $station eq $in_transit->{dep_name} ) {
+ if ( $station->[0] eq $in_transit->{dep_name} ) {
$is_after = 1;
}
}
@@ -1861,14 +1873,12 @@ sub startup {
arr_name => $in_transit->{arr_name},
arr_platform => $in_transit->{arr_platform},
route_after => \@route_after,
- messages => $in_transit->{messages}
- ? [ split( qr{[|]}, $in_transit->{messages} ) ]
- : undef,
+ messages => $in_transit->{messages},
};
my @parsed_messages;
for my $message ( @{ $ret->{messages} // [] } ) {
- my ( $ts, $msg ) = split( qr{:}, $message );
+ my ( $ts, $msg ) = @{$message};
push( @parsed_messages, [ epoch_to_dt($ts), $msg ] );
}
$ret->{messages} = [ reverse @parsed_messages ];
@@ -2027,7 +2037,8 @@ sub startup {
my $distance = 0;
my $skipped = 0;
my $geo = Geo::Distance->new();
- my @route = after_incl { $_ eq $from } @{$route_ref};
+ my @stations = map { $_->[0] } @{$route_ref};
+ my @route = after_incl { $_ eq $from } @stations;
@route = before_incl { $_ eq $to } @route;
if ( @route < 2 ) {
diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index 99fcc61..fab5fa7 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -546,6 +546,119 @@ my @migrations = (
}
);
},
+
+ # v13 -> v14
+ sub {
+ my ($db) = @_;
+ $db->query(
+ qq{
+ alter table journeys add column route_new jsonb,
+ add column messages_new jsonb;
+ alter table in_transit add column route_new jsonb,
+ add column messages_new jsonb;
+ }
+ );
+ my $res = $db->select( 'journeys', [ 'id', 'messages', 'route' ] );
+ my $json = JSON->new;
+
+ for my $journey ( $res->hashes->each ) {
+ my $id = $journey->{id};
+ my @messages;
+ for my $message ( split( qr{[|]}, $journey->{messages} // '' ) ) {
+ my ( $ts, $msg ) = split( qr{:}, $message );
+ push( @messages, [ $ts, $msg ] );
+ }
+ my @route = map { [$_] }
+ split( qr{[|]}, $journey->{route} // '' );
+
+ $db->update(
+ 'journeys',
+ {
+ messages_new => $json->encode( [@messages] ),
+ route_new => $json->encode( [@route] ),
+ },
+ { id => $id }
+ );
+ }
+
+ $res = $db->select( 'in_transit', [ 'user_id', 'messages', 'route' ] );
+ for my $journey ( $res->hashes->each ) {
+ my $id = $journey->{user_id};
+ my @messages;
+ for my $message ( split( qr{[|]}, $journey->{messages} // '' ) ) {
+ my ( $ts, $msg ) = split( qr{:}, $message );
+ push( @messages, [ $ts, $msg ] );
+ }
+ my @route = map { [$_] }
+ split( qr{[|]}, $journey->{route} // '' );
+
+ $db->update(
+ 'in_transit',
+ {
+ messages_new => $json->encode( [@messages] ),
+ route_new => $json->encode( [@route] ),
+ },
+ { user_id => $id }
+ );
+ }
+
+ $db->query(
+ qq{
+ drop view journeys_str;
+ alter table journeys drop column messages;
+ alter table journeys drop column route;
+ alter table journeys rename column messages_new to messages;
+ alter table journeys rename column route_new to route;
+
+ drop view in_transit_str;
+ alter table in_transit drop column messages;
+ alter table in_transit drop column route;
+ alter table in_transit rename column messages_new to messages;
+ alter table in_transit rename column route_new to route;
+
+ 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,
+ dep_stations.ds100 as dep_ds100,
+ dep_stations.name as dep_name,
+ 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,
+ arr_stations.ds100 as arr_ds100,
+ arr_stations.name as arr_name,
+ cancelled, edited, route, messages,
+ dep_platform, arr_platform
+ from journeys
+ join stations as dep_stations on dep_stations.id = checkin_station_id
+ join stations as arr_stations on arr_stations.id = checkout_station_id
+ ;
+ 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,
+ dep_stations.ds100 as dep_ds100,
+ dep_stations.name as dep_name,
+ 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,
+ arr_stations.ds100 as arr_ds100,
+ arr_stations.name as arr_name,
+ cancelled, route, messages,
+ dep_platform, arr_platform
+ from in_transit
+ join stations as dep_stations on dep_stations.id = checkin_station_id
+ left join stations as arr_stations on arr_stations.id = checkout_station_id
+ ;
+
+ update schema_version set version = 14;
+ }
+ );
+ },
);
sub setup_db {
diff --git a/lib/Travelynx/Command/work.pm b/lib/Travelynx/Command/work.pm
index b24e4ad..46f2213 100644
--- a/lib/Travelynx/Command/work.pm
+++ b/lib/Travelynx/Command/work.pm
@@ -2,6 +2,7 @@ package Travelynx::Command::work;
use Mojo::Base 'Mojolicious::Command';
use DateTime;
+use JSON;
use List::Util qw(first);
has description =>
@@ -13,6 +14,7 @@ sub run {
my ($self) = @_;
my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+ my $json = JSON->new;
my $db = $self->app->pg->db;
@@ -47,14 +49,14 @@ sub run {
{
dep_platform => $train->platform,
real_departure => $train->departure,
- route => join( '|', $train->route ),
- messages => join(
- '|',
- map {
- ( $_->[0] ? $_->[0]->epoch : q{} ) . ':'
- . $_->[1]
- } $train->messages
- )
+ route =>
+ $json->encode( [ map { [$_] } $train->route ] ),
+ messages => $json->encode(
+ [
+ map { [ $_->[0]->epoch, $_->[1] ] }
+ $train->messages
+ ]
+ ),
},
{ user_id => $uid }
);
@@ -99,14 +101,14 @@ sub run {
arr_platform => $train->platform,
sched_arrival => $train->sched_arrival,
real_arrival => $train->arrival,
- route => join( '|', $train->route ),
- messages => join(
- '|',
- map {
- ( $_->[0] ? $_->[0]->epoch : q{} ) . ':'
- . $_->[1]
- } $train->messages
- )
+ route =>
+ $json->encode( [ map { [$_] } $train->route ] ),
+ messages => $json->encode(
+ [
+ map { [ $_->[0]->epoch, $_->[1] ] }
+ $train->messages
+ ]
+ ),
},
{ user_id => $uid }
);
diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm
index 4e4b069..20a7e73 100755
--- a/lib/Travelynx/Controller/Traveling.pm
+++ b/lib/Travelynx/Controller/Traveling.pm
@@ -592,10 +592,6 @@ sub edit_journey {
}
}
- if ( $journey->{route} ) {
- $self->param( route => join( "\n", @{ $journey->{route} } ) );
- }
-
$self->render(
'edit_journey',
error => $error,
diff --git a/templates/_cancelled.html.ep b/templates/_cancelled.html.ep
index 82e1ed7..be3e318 100644
--- a/templates/_cancelled.html.ep
+++ b/templates/_cancelled.html.ep
@@ -14,7 +14,7 @@
<tbody>
% my $is_after = 0;
% for my $station (@{$journey->{route_after}}) {
- <tr><td><a class="action-cancelled-to" data-station="<%= $station %>"><%= $station %></a></td></tr>
+ <tr><td><a class="action-cancelled-to" data-station="<%= $station->[0] %>"><%= $station->[0] %></a></td></tr>
% }
</tbody>
</table>
diff --git a/templates/_checked_in.html.ep b/templates/_checked_in.html.ep
index a50aa3e..efe1eff 100644
--- a/templates/_checked_in.html.ep
+++ b/templates/_checked_in.html.ep
@@ -103,11 +103,11 @@
<tbody>
% my $is_after = 0;
% for my $station (@{$journey->{route_after}}) {
- % if ($journey->{arr_name} and $station eq $journey->{arr_name}) {
- <tr><td><b><a class="action-checkout" data-station="<%= $station %>"><%= $station %></a></b></td></tr>
+ % if ($journey->{arr_name} and $station->[0] eq $journey->{arr_name}) {
+ <tr><td><b><a class="action-checkout" data-station="<%= $station->[0] %>"><%= $station->[0] %></a></b></td></tr>
% }
% else {
- <tr><td><a class="action-checkout" data-station="<%= $station %>"><%= $station %></a></td></tr>
+ <tr><td><a class="action-checkout" data-station="<%= $station->[0] %>"><%= $station->[0] %></a></td></tr>
% }
% }
</tbody>
diff --git a/templates/journey.html.ep b/templates/journey.html.ep
index cd2b04e..f83774a 100644
--- a/templates/journey.html.ep
+++ b/templates/journey.html.ep
@@ -148,20 +148,20 @@
% my $within = 0;
% my $at_startstop = 0;
% for my $station (@{$journey->{route}}) {
- % if ($station eq $journey->{from_name}) {
+ % if ($station->[0] eq $journey->{from_name}) {
% $within = 1; $at_startstop = 1;
% }
- % elsif ($station eq $journey->{to_name}) {
+ % elsif ($station->[0] eq $journey->{to_name}) {
% $within = 0; $at_startstop = 1;
% }
% else {
% $at_startstop = 0;
% }
% if ($at_startstop or $within) {
- <%= $station %>
+ <%= $station->[0] %>
% }
% else {
- <span style="color: #666666;"><%= $station %></span>
+ <span style="color: #666666;"><%= $station->[0] %></span>
% }
<br/>
% }
diff --git a/templates/landingpage.html.ep b/templates/landingpage.html.ep
index 742f684..2c7496d 100644
--- a/templates/landingpage.html.ep
+++ b/templates/landingpage.html.ep
@@ -34,7 +34,7 @@
<tbody>
% my $is_after = 0;
% for my $station (@{$status->{route_after}}) {
- <tr><td><a class="action-cancelled-to" data-station="<%= $station %>"><%= $station %></a></td></tr>
+ <tr><td><a class="action-cancelled-to" data-station="<%= $station->[0] %>"><%= $station->[0] %></a></td></tr>
% }
</tbody>
</table>
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-05 18:09:20 +0000