Line # Revision Author
1 83 dpavlin #!/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;