From f8aa6ff141ac37aa8655277c102f1b58cfc776ad Mon Sep 17 00:00:00 2001 From: Daniel Friesel Date: Sun, 29 Mar 2015 10:53:45 +0200 Subject: iris-delay-stats: add view containing departures with -all- messages --- bin/iris-delay-stats | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) diff --git a/bin/iris-delay-stats b/bin/iris-delay-stats index f2cc5ab..12cfb80 100755 --- a/bin/iris-delay-stats +++ b/bin/iris-delay-stats @@ -130,6 +130,27 @@ if ( not table_exists('departures') ) { } ); } +if ( not table_exists('departures_with_messages') ) { + + # only for testing, too inefficient for production + # (the current schema saves lots of disk space, but not computation time) + $dbh->do( + qq{ + create view departures_with_messages as + select train_id, station, scheduled_time, delay, is_canceled, + destination, train_type, train_no, line_no, platform, } + . join( ", ", + map { "(msgtable${_}.train_id is not null) as msg${_}" } + ( 1 .. 99 ) ) + . " from departures " + . join( + " ", + map { +"left outer join msg_$_ as msgtable$_ using (scheduled_time, train_id)" + } ( 1 .. 99 ) + ) + ); +} if ( not table_exists('hr_departures') ) { $dbh->do( qq{ -- cgit v1.2.3