diff options
| -rwxr-xr-x | bin/iris-delay-stats | 40 |
1 files changed, 32 insertions, 8 deletions
diff --git a/bin/iris-delay-stats b/bin/iris-delay-stats index 1c534e1..8534480 100755 --- a/bin/iris-delay-stats +++ b/bin/iris-delay-stats @@ -22,7 +22,7 @@ if ($first_run) { create table departures ( train_id int not null, station char(10) not null, - raw_id char(64) not null primary key, + raw_id char(64) not null unique, scheduled_time int not null, delay int, is_canceled boolean, @@ -31,8 +31,19 @@ if ($first_run) { train_no int not null, line_no int, platform char(16), - } . join( ', ', map { "msg_$_ boolean" } ( 1 .. 99 ) ) . ')'; + primary key (train_id, scheduled_time) + ) + }; $dbh->do($query); + for my $msg ( 1 .. 99 ) { + $dbh->do( + qq{create table msg_$msg ( + train_id int not null, + scheduled_time int not null, + primary key (train_id, scheduled_time) + )} + ); + } } my $status = Travel::Status::DE::IRIS->new( @@ -44,7 +55,6 @@ my @fields = ( qw(train_id station raw_id scheduled_time delay is_canceled destination train_type train_no line_no platform) ); -push( @fields, map { "msg_$_" } ( 1 .. 99 ) ); my $fieldlist = join( ', ', @fields ); my $field_placeholders = join( ', ', ('?') x @fields ); my $insert_query = qq{ @@ -52,19 +62,33 @@ my $insert_query = qq{ }; my $sth = $dbh->prepare($insert_query); +my @msg_sth; +for my $msg ( 1 .. 99 ) { + $msg_sth[$msg] = $dbh->prepare( + qq{insert or replace into msg_$msg + ( train_id, scheduled_time ) values ( ?, ? ) } + ); +} + for my $r ( $status->results ) { - my @msgtypes = (0) x 99; + my @msgtypes = (0) x 100; for my $m ( $r->raw_messages ) { $msgtypes[ $m->[1] ] = 1; } $sth->execute( - $r->train_id, $station, $r->raw_id, - $r->datetime->epoch, $r->delay, $r->is_cancelled, - $r->destination, $r->type, $r->train_no, - $r->line_no, $r->sched_platform, @msgtypes + $r->train_id, $station, $r->raw_id, + $r->datetime->epoch, $r->delay, $r->is_cancelled, + $r->destination, $r->type, $r->train_no, + $r->line_no, $r->sched_platform ); + + for my $msg ( 1 .. 99 ) { + if ( $msgtypes[$msg] ) { + $msg_sth[$msg]->execute( $r->train_id, $r->datetime->epoch ); + } + } } __END__ |