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	dpavlin	83	#!/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;