$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
+$dbh->do('SET client_min_messages TO WARNING');
+
# Find last completely done season
my $ref = $dbh->selectrow_hashref('SELECT sesong FROM fotballserier GROUP BY sesong HAVING COUNT(*)=COUNT(avgjort=1 OR NULL) ORDER BY sesong DESC LIMIT 1');
my $last_season = $ref->{'sesong'};
-my $tmpnam = POSIX::tmpnam();
-open DATA, ">", $tmpnam
- or die "$tmpnam: $!";
-
-# Fetch name (ID) list
-my $q = $dbh->prepare('SELECT DISTINCT id FROM fotballdeltagere');
-$q->execute();
-my @ids = ();
-while (my $ref = $q->fetchrow_hashref) {
- my $id = $ref->{'id'};
- push @ids, $id;
-}
-
-print DATA scalar @ids, "\n";
-for my $id (@ids) {
- print DATA $id, "\n";
-}
-
# Fetch games
-$q = $dbh->prepare('
+my $q = $dbh->prepare('
SELECT
deltager1.id as p1, deltager2.id as p2, maalfor, maalmot, least(pow(2.0, (sesong - ? + 3) / 3.0), 1.0) AS vekt
FROM
');
$q->execute($last_season);
+my @games = ();
+my %ids = ();
+
while (my $ref = $q->fetchrow_hashref) {
- printf DATA "%d %d %d %d %f\n", $ref->{'p1'}, $ref->{'p2'}, $ref->{'maalfor'}, $ref->{'maalmot'}, $ref->{'vekt'};
+ next if ($ref->{'maalfor'} == 150 && $ref->{'maalmot'} == 0);
+ next if ($ref->{'maalfor'} == 0 && $ref->{'maalmot'} == 150);
+ next if ($ref->{'maalfor'} == 150 && $ref->{'maalmot'} == 150);
+ next if ($ref->{'maalfor'} == 0 && $ref->{'maalmot'} == 0);
+ push @games, { %$ref };
+ $ids{$ref->{'p1'}} = 1;
+ $ids{$ref->{'p2'}} = 1;
}
+# Output to file
+my $tmpnam = POSIX::tmpnam();
+open DATA, ">", $tmpnam
+ or die "$tmpnam: $!";
+
+printf DATA "%d\n", scalar keys %ids;
+for my $id (keys %ids) {
+ printf DATA "%d\n", $id;
+}
+for my $ref (@games) {
+ printf DATA "%d %d %d %d %f\n", $ref->{'p1'}, $ref->{'p2'}, $ref->{'maalfor'}, $ref->{'maalmot'}, $ref->{'vekt'};
+}
close DATA;
-$dbh->do('DELETE FROM ratings');
-my $iq = $dbh->prepare('INSERT INTO ratings ( id, rating ) VALUES (?, ?)');
+my @ratings = ();
+my @covariances = ();
-open RATINGS, "/home/sesse/dev/bayeswf/bayeswf < $tmpnam |"
+open RATINGS, "$config::base_dir/bayeswf < $tmpnam |"
or die "bayeswf: $!";
while (<RATINGS>) {
- /(.*) (.*)/ or next;
- $iq->execute($2, $1);
+ chomp;
+ my @x = split;
+ if ($x[0] eq 'covariance') {
+ push @covariances, (join("\t", @x[1..3]));
+ } else {
+ push @ratings, ($x[2] . "\t" . $x[0] . "\t" . $x[1]);
+ }
}
+$dbh->do('TRUNCATE ratings');
+$dbh->do('COPY ratings ( id, rating, rating_stddev ) FROM STDIN');
+$dbh->pg_putcopydata(join("\n", @ratings));
+$dbh->pg_putcopyend();
+
+$dbh->do('CREATE TABLE new_covariance ( player1 smallint NOT NULL, player2 smallint NOT NULL, cov float NOT NULL )');
+$dbh->do('COPY new_covariance ( player1, player2, cov ) FROM STDIN');
+$dbh->pg_putcopydata(join("\n", @covariances));
+$dbh->pg_putcopyend();
+$dbh->do('ALTER TABLE new_covariance ADD PRIMARY KEY ( player1, player2 );');
+$dbh->do('DROP TABLE IF EXISTS covariance');
+$dbh->do('ALTER TABLE new_covariance RENAME TO covariance');
+
$dbh->commit;
+unlink($tmpnam);