1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
#!/usr/bin/env perl
use strict;
use warnings;
use 5.010;
use DBI;
use List::Util qw(first);
use Travel::Status::DE::IRIS;
our $VERSION = '0.00';
my $station = shift or die("Usage: $0 <station> <database>\n");
my $dbname = shift or die("Usage: $0 <station> <database>\n");
my $first_run = not -e $dbname;
my $dbh = DBI->connect( "dbi:SQLite:dbname=$dbname", q{}, q{} );
if ($first_run) {
my $query = qq{
create table departures (
train_id int not null,
station char(10) not null,
raw_id char(64) not null unique,
scheduled_time int not null,
delay int,
is_canceled boolean,
destination char(64) not null,
train_type char(6) not null,
train_no int not null,
line_no int,
platform char(16),
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(
station => $station,
lookahead => 60
);
my @fields = (
qw(train_id station raw_id scheduled_time delay
is_canceled destination train_type train_no line_no platform)
);
my $fieldlist = join( ', ', @fields );
my $field_placeholders = join( ', ', ('?') x @fields );
my $insert_query = qq{
insert or replace into departures ( $fieldlist ) values ( $field_placeholders )
};
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 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
);
for my $msg ( 1 .. 99 ) {
if ( $msgtypes[$msg] ) {
$msg_sth[$msg]->execute( $r->train_id, $r->datetime->epoch );
}
}
}
__END__
=head1 NAME
=head1 SYNOPSIS
=head1 VERSION
=head1 DESCRIPTION
=head1 OPTIONS
=over
=back
=head1 EXIT STATUS
=head1 CONFIGURATION
None.
=head1 DEPENDENCIES
=over
=back
=head1 BUGS AND LIMITATIONS
=head1 AUTHOR
Copyright (C) 2015 by Daniel Friesel E<lt>derf@finalrewind.orgE<gt>
=head1 LICENSE
0. You just DO WHAT THE FUCK YOU WANT TO.
|
