#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbfile = "turk.db";
unlink($dbfile) if (-f $dbfile);
die "Couldn't remove $dbfile." if (-f $dbfile);

my $dbh = DBI->connect("dbi:SQLite2:dbname=${dbfile}","","", {
    RaiseError => 1,
    AutoCommit => 0
});

$dbh->do(qq{
    CREATE TABLE cities (
        id INTEGER PRIMARY KEY,
        city VARCHAR(30) NOT NULL,
        state VARCHAR(2) NOT NULL
    )
});

$dbh->do(qq{
    CREATE TABLE hits (
        hitid VARCHAR(100) PRIMARY KEY,
        hittypeid VARCHAR(100) NOT NULL,
        cityid INTEGER NOT NULL,
        question TEXT
    )
});

my @cities = (
    ['Seattle', 'WA'],
    ['NewYork', 'NY'],
    ['Phoenix', 'AZ']
);

my $sth = $dbh->prepare(qq{
    INSERT INTO cities (id,city,state)
    VALUES (?,?,?)
});

my $id = 0;
foreach my $city (@cities) {
    $id++;
    $sth->execute($id, $city->[0], $city->[1]);
}

print "Inserted $id rows.";

$dbh->commit;
$dbh->disconnect;