From 39248dece663b9c66f917da97635cf98b8ad0e08 Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Sat, 14 Feb 2015 22:22:47 +0100
Subject: insertion seems to be working.

---
 bin/iris-delay-stats | 67 ++++++++++++++++++++++++++++++++++++++++++++++------
 1 file changed, 60 insertions(+), 7 deletions(-)

(limited to 'bin')

diff --git a/bin/iris-delay-stats b/bin/iris-delay-stats
index 32c38a8..37cce8a 100755
--- a/bin/iris-delay-stats
+++ b/bin/iris-delay-stats
@@ -4,19 +4,72 @@ use strict;
 use warnings;
 use 5.010;
 
+use DBI;
 use List::Util qw(first);
+use Travel::Status::DE::IRIS;
 
 our $VERSION = '0.00';
 
-sub table_exists {
-	my $db = shift;
-	my $table = shift;
-	my @tables = $db->tables('','','','TABLE');
+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 primary key,
+			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),
+	} . join(', ', map { "msg_$_ boolean" } (1..99)) . ')';
+	$dbh->do($query);
+}
 
-	if (first { $_ eq $table } @tables) {
-		return 1;
+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));
+push(@fields, map { "msg_$_" } (1..99));
+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);
+
+for my $r ($status->results) {
+
+	my @msgtypes = (0) x 99;
+	for my $m ($r->raw_messages) {
+		$msgtypes[ $m->[1] ] = 1;
 	}
-	return 0;
+
+	$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,
+		$r->sched_platform,
+		@msgtypes
+	);
 }
 
 __END__
-- 
cgit v1.2.3