trunk/data/db.pl

#!/usr/bin/perl -w

use strict;
use parse_trivia;
use DBI;

# score for words in title
my $title_rank = 3;

open(my $t, "gzip -cd trivia.list.gz |") || die "can't open trivia.list.gz: $!";

my $dsn = 'dbi:Pg:dbname=trivia';
my ($user,$password);

my $dbh = DBI->connect($dsn, $user, $password,
        { RaiseError => 1, AutoCommit => 0 });

my $nr = 1;

my $sql = {
        insert_movie => qq{
                insert into films (title, year) values (?,?)
        },
        insert_trivia => qq{
                insert into trivias (film_id, trivia) values (?,?)
        },
        insert_quote => qq{
                insert into quotes (trivia_id, quote) values (?,?)
        },
};

my $sth;

foreach my $op ( keys %$sql ) {
        my $q = $sql->{$op};
        warn "# prepare $op: $q\n";
        $sth->{$op} = $dbh->prepare( $q );
}

my $film_id_by_title;

parse_trivia($t, sub {

                my $a = {@_};

                my $k = $a->{title};
                $k .= $a->{year} if ($a->{year});

                my $film_id = $film_id_by_title->{ $k };
                if (! $film_id ) {
                        $sth->{insert_movie}->execute( $a->{title}, $a->{year} );
                        $film_id = $dbh->last_insert_id(undef,undef,"films",undef);
                }

                $film_id_by_title->{ $k } = $film_id;

                $sth->{insert_trivia}->execute( $film_id, $a->{trivia} );
                my $trivia_id = $dbh->last_insert_id(undef,undef,"trivias",undef);

                foreach my $q ( @{ $a->{qv} } ) {
                        $sth->{insert_quote}->execute( $trivia_id, $q );
                }
});

$dbh->commit;
1	#!/usr/bin/perl -w
2
3	use strict;
4	use parse_trivia;
5	use DBI;
6
7	# score for words in title
8	my $title_rank = 3;
9
10	open(my $t, "gzip -cd trivia.list.gz \|") \|\| die "can't open trivia.list.gz: $!";
11
12	my $dsn = 'dbi:Pg:dbname=trivia';
13	my ($user,$password);
14
15	my $dbh = DBI->connect($dsn, $user, $password,
16	{ RaiseError => 1, AutoCommit => 0 });
17
18	my $nr = 1;
19
20	my $sql = {
21	insert_movie => qq{
22	insert into films (title, year) values (?,?)
23	},
24	insert_trivia => qq{
25	insert into trivias (film_id, trivia) values (?,?)
26	},
27	insert_quote => qq{
28	insert into quotes (trivia_id, quote) values (?,?)
29	},
30	};
31
32	my $sth;
33
34	foreach my $op ( keys %$sql ) {
35	my $q = $sql->{$op};
36	warn "# prepare $op: $q\n";
37	$sth->{$op} = $dbh->prepare( $q );
38	}
39
40	my $film_id_by_title;
41
42	parse_trivia($t, sub {
43
44	my $a = {@_};
45
46	my $k = $a->{title};
47	$k .= $a->{year} if ($a->{year});
48
49	my $film_id = $film_id_by_title->{ $k };
50	if (! $film_id ) {
51	$sth->{insert_movie}->execute( $a->{title}, $a->{year} );
52	$film_id = $dbh->last_insert_id(undef,undef,"films",undef);
53	}
54
55	$film_id_by_title->{ $k } = $film_id;
56
57	$sth->{insert_trivia}->execute( $film_id, $a->{trivia} );
58	my $trivia_id = $dbh->last_insert_id(undef,undef,"trivias",undef);
59
60	foreach my $q ( @{ $a->{qv} } ) {
61	$sth->{insert_quote}->execute( $trivia_id, $q );
62	}
63	});
64
65	$dbh->commit;