From: Steinar H. Gunderson <sesse@debian.org>
Date: Wed, 3 Oct 2007 19:59:01 +0000 (+0200)
Subject: Initial checkin.
X-Git-Url: https://git.sesse.net/?p=foosball;a=commitdiff_plain;h=3298937aa329fbeefb64080636bc7873461e75c3

Initial checkin.
---

3298937aa329fbeefb64080636bc7873461e75c3
diff --git a/foorank.cpp b/foorank.cpp
new file mode 100644
index 0000000..659c611
--- /dev/null
+++ b/foorank.cpp
@@ -0,0 +1,339 @@
+#include <stdio.h>
+#include <math.h>
+#include <assert.h>
+
+#include <vector>
+#include <algorithm>
+
+// integration step size
+static const double step_size = 10.0;
+
+using namespace std;
+
+double prob_score(double a, double rd);
+double prob_score_real(double a, double prodai, double rd_norm);
+double prodai(double a);
+
+// probability of match ending 10-a when winnerR - loserR = RD
+//
+//   +inf  
+//     / 
+//    |
+//    | Poisson[lambda1, t](a) * Erlang[lambda2, 10](t) dt
+//    |
+//   /
+// -inf
+//
+// where lambda1 = 1.0, lambda2 = 2^(rd/455)
+//
+// The constant of 455 is chosen carefully so to match with the
+// Glicko/Bradley-Terry assumption that a player rated 400 points over
+// his/her opponent will win with a probability of 10/11 =~ 0.90909. 
+//
+double prob_score(double a, double rd)
+{
+	return prob_score_real(a, prodai(a), rd/455.0);
+}
+
+// Same, but takes in Product(a+i, i=1..9) as an argument in addition to a. Faster
+// if you already have that precomputed, and assumes rd is already divided by 455.
+double prob_score_real(double a, double prodai, double rd_norm)
+{
+	double nom =
+		pow(2.0, -a*rd_norm) * pow(2.0, 10.0*rd_norm) * pow(pow(2.0, -rd_norm) + 1.0, -a)
+		* prodai;
+	double denom = 362880 * pow(1.0 + pow(2.0, rd_norm), 10.0);
+	return nom/denom;
+}
+
+// Calculates Product(a+i, i=1..9) (see above).
+double prodai(double a)
+{
+	return (a+1)*(a+2)*(a+3)*(a+4)*(a+5)*(a+6)*(a+7)*(a+8)*(a+9);
+}
+
+// 
+// Computes the integral
+//
+//   +inf
+//    /
+//    |
+//    | ProbScore[a] (r2-r1) Gaussian[mu2, sigma2] (dr2) dr2
+//    |
+//   /
+// -inf
+//
+// For practical reasons, -inf and +inf are replaced by 0 and 3000, which
+// is reasonable in the this context.
+//
+// The Gaussian is not normalized.
+//
+// Set the last parameter to 1.0 if player 1 won, or -1.0 if player 2 won.
+// In the latter case, ProbScore will be given (r1-r2) instead of (r2-r1).
+//
+double opponent_rating_pdf(double a, double r1, double mu2, double sigma2, double winfac)
+{
+	double sum = 0.0;
+	double prodai_precompute = prodai(a);
+	winfac /= 455.0;
+	for (double r2 = 0.0; r2 < 3000.0; r2 += step_size) {
+		double x = r2 + step_size*0.5;
+		double probscore = prob_score_real(a, prodai_precompute, (r1 - x)*winfac);
+		double z = (x - mu2)/sigma2;
+		double gaussian = exp(-(z*z/2.0));
+		sum += step_size * probscore * gaussian;
+	}
+	return sum;
+}
+
+// normalize the curve so we know that A ~= 1
+void normalize(vector<pair<double, double> > &curve)
+{
+	double peak = 0.0;
+	for (vector<pair<double, double> >::const_iterator i = curve.begin(); i != curve.end(); ++i) {
+		peak = max(peak, i->second);
+	}
+
+	double invpeak = 1.0 / peak;
+	for (vector<pair<double, double> >::iterator i = curve.begin(); i != curve.end(); ++i) {
+		i->second *= invpeak;
+	}
+}
+
+// computes matA * matB
+void mat_mul(double *matA, unsigned ah, unsigned aw,
+             double *matB, unsigned bh, unsigned bw,
+	     double *result)
+{
+	assert(aw == bh);
+	for (unsigned y = 0; y < bw; ++y) {
+		for (unsigned x = 0; x < ah; ++x) {
+			double sum = 0.0;
+			for (unsigned c = 0; c < aw; ++c) {
+				sum += matA[c*ah + x] * matB[y*bh + c];
+			}
+			result[y*bw + x] = sum;
+		}
+	}
+}
+		
+// computes matA^T * matB
+void mat_mul_trans(double *matA, unsigned ah, unsigned aw,
+                   double *matB, unsigned bh, unsigned bw,
+		   double *result)
+{
+	assert(ah == bh);
+	for (unsigned y = 0; y < bw; ++y) {
+		for (unsigned x = 0; x < aw; ++x) {
+			double sum = 0.0;
+			for (unsigned c = 0; c < ah; ++c) {
+				sum += matA[x*ah + c] * matB[y*bh + c];
+			}
+			result[y*bw + x] = sum;
+		}
+	}
+}
+
+void print3x3(double *M)
+{
+	printf("%f %f %f\n", M[0], M[3], M[6]);
+	printf("%f %f %f\n", M[1], M[4], M[7]);
+	printf("%f %f %f\n", M[2], M[5], M[8]);
+}
+
+void print3x1(double *M)
+{
+	printf("%f\n", M[0]);
+	printf("%f\n", M[1]);
+	printf("%f\n", M[2]);
+}
+
+// solves Ax = B by Gauss-Jordan elimination, where A is a 3x3 matrix,
+// x is a column vector of length 3 and B is a row vector of length 3.
+// Destroys its input in the process.
+void solve3x3(double *A, double *x, double *B)
+{
+	// row 1 -= row 0 * (a1/a0)
+	{
+		double f = A[1] / A[0];
+		A[1] = 0.0;
+		A[4] -= A[3] * f;
+		A[7] -= A[6] * f;
+
+		B[1] -= B[0] * f;
+	}
+
+	// row 2 -= row 0 * (a2/a0)
+	{
+		double f = A[2] / A[0];
+		A[2] = 0.0;
+		A[5] -= A[3] * f;
+		A[8] -= A[6] * f;
+
+		B[2] -= B[0] * f;
+	}
+
+	// row 2 -= row 1 * (a5/a4)
+	{
+		double f = A[5] / A[4];
+		A[5] = 0.0;
+		A[8] -= A[7] * f;
+		
+		B[2] -= B[1] * f;
+	}
+
+	// back substitute:
+
+	// row 1 -= row 2 * (a7/a8)
+	{
+		double f = A[7] / A[8];
+		A[7] = 0.0;
+
+		B[1] -= B[2] * f;
+	}
+
+	// row 0 -= row 2 * (a6/a8)
+	{
+		double f = A[6] / A[8];
+		A[6] = 0.0;
+
+		B[0] -= B[2] * f;
+	}
+
+	// row 0 -= row 1 * (a3/a4)
+	{
+		double f = A[3] / A[4];
+		A[3] = 0.0;
+
+		B[0] -= B[1] * f;
+	}
+
+	// normalize
+	x[0] = B[0] / A[0];
+	x[1] = B[1] / A[4];
+	x[2] = B[2] / A[8];
+}
+
+// Give an OK starting estimate for the least squares, by numerical integration
+// of x*f(x) and x^2 * f(x). Somehow seems to underestimate sigma, though.
+void estimate_musigma(vector<pair<double, double> > &curve, double &mu_result, double &sigma_result)
+{
+	double mu = 0.0;
+	double sigma = 0.0;
+	double sum_area = 0.0;
+
+	for (unsigned i = 1; i < curve.size(); ++i) {
+		double x1 = curve[i].first;
+		double x0 = curve[i-1].first;
+		double y1 = curve[i].second;
+		double y0 = curve[i-1].second;
+		double xm = 0.5 * (x0 + x1);
+		double ym = 0.5 * (y0 + y1);
+		sum_area += (x1-x0) * ym;
+		mu += (x1-x0) * xm * ym;
+		sigma += (x1-x0) * xm * xm * ym;
+	}
+
+	mu_result = mu / sum_area;
+	sigma_result = sqrt(sigma) / sum_area;
+}
+	
+// Find best fit of the data in curves to a Gaussian pdf, based on the
+// given initial estimates. Works by nonlinear least squares, iterating
+// until we're below a certain threshold.
+void least_squares(vector<pair<double, double> > &curve, double mu1, double sigma1, double &mu_result, double &sigma_result)
+{
+	double A = 1.0;
+	double mu = mu1;
+	double sigma = sigma1;
+
+	// column-major
+	double matA[curve.size() * 3];  // N x 3
+	double dbeta[curve.size()];     // N x 1
+
+	// A^T * A: 3xN * Nx3 = 3x3
+	double matATA[3*3];
+
+	// A^T * dβ: 3xN * Nx1 = 3x1
+	double matATdb[3];
+
+	double dlambda[3];
+
+	for ( ;; ) {
+		//printf("A=%f mu=%f sigma=%f\n", A, mu, sigma);
+
+		// fill in A (depends only on x_i, A, mu, sigma -- not y_i)
+		for (unsigned i = 0; i < curve.size(); ++i) {
+			double x = curve[i].first;
+
+			// df/dA(x_i)
+			matA[i + 0 * curve.size()] = 
+				exp(-(x-mu)*(x-mu)/(2.0*sigma*sigma));
+
+			// df/dµ(x_i)
+			matA[i + 1 * curve.size()] = 
+				A * (x-mu)/(sigma*sigma) * matA[i + 0 * curve.size()];
+
+			// df/dσ(x_i)
+			matA[i + 2 * curve.size()] = 
+				matA[i + 1 * curve.size()] * (x-mu)/sigma;
+		}
+
+		// find dβ
+		for (unsigned i = 0; i < curve.size(); ++i) {
+			double x = curve[i].first;
+			double y = curve[i].second;
+
+			dbeta[i] = y - A * exp(- (x-mu)*(x-mu)/(2.0*sigma*sigma));
+		}
+
+		// compute a and b
+		mat_mul_trans(matA, curve.size(), 3, matA, curve.size(), 3, matATA);
+		mat_mul_trans(matA, curve.size(), 3, dbeta, curve.size(), 1, matATdb);
+
+		// solve
+		solve3x3(matATA, dlambda, matATdb);
+
+		A += dlambda[0];
+		mu += dlambda[1];
+		sigma += dlambda[2];
+
+		// terminate when we're down to three digits
+		if (fabs(dlambda[0]) <= 1e-3 && fabs(dlambda[1]) <= 1e-3 && fabs(dlambda[2]) <= 1e-3)
+			break;
+	}
+
+	mu_result = mu;
+	sigma_result = sigma;
+}
+
+int main(int argc, char **argv)
+{
+	double mu1 = atof(argv[1]);
+	double sigma1 = atof(argv[2]);
+	double mu2 = atof(argv[3]);
+	double sigma2 = atof(argv[4]);
+	int score1 = atoi(argv[5]);
+	int score2 = atoi(argv[6]);
+	vector<pair<double, double> > curve;
+
+	if (score1 == 10) {
+		for (double r1 = 0.0; r1 < 3000.0; r1 += step_size) {
+			double z = (r1 - mu1) / sigma1;
+			double gaussian = exp(-(z*z/2.0));
+			curve.push_back(make_pair(r1, gaussian * opponent_rating_pdf(score2, r1, mu2, sigma2, 1.0)));
+		}
+	} else {
+		for (double r1 = 0.0; r1 < 3000.0; r1 += step_size) {
+			double z = (r1 - mu1) / sigma1;
+			double gaussian = exp(-(z*z/2.0));
+			curve.push_back(make_pair(r1, gaussian * opponent_rating_pdf(score1, r1, mu2, sigma2, -1.0)));
+		}
+	}
+
+	double mu_est, sigma_est, mu, sigma;
+	normalize(curve);
+	estimate_musigma(curve, mu_est, sigma_est);
+	least_squares(curve, mu_est, sigma_est, mu, sigma);
+	printf("%f %f\n", mu, sigma);
+}
diff --git a/foosball.pm b/foosball.pm
new file mode 100644
index 0000000..fd46aae
--- /dev/null
+++ b/foosball.pm
@@ -0,0 +1,106 @@
+use strict;
+use warnings;
+use DBI;
+
+package foosball;
+
+sub db_connect {
+	my $dbh = DBI->connect("dbi:Pg:dbname=foosball;host=127.0.0.1", "foosball", "cleanrun", {AutoCommit => 0});
+	$dbh->{RaiseError} = 1;
+	return $dbh;
+}
+
+sub find_single_rating {
+	my ($dbh, $username, $limit) = @_;
+	my ($age, $rating, $rd) = $dbh->selectrow_array('SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP-ratetime)), rating, rd FROM single_rating WHERE username=? '.$limit.' ORDER BY ratetime DESC LIMIT 1',
+		undef, $username);
+	$rd = apply_aging($rd, $age / 86400.0);
+	return ($rating, $rd);
+}
+
+sub find_double_rating {
+	my ($dbh, $username, $limit) = @_;
+	my ($age, $rating, $rd) = $dbh->selectrow_array('SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP-ratetime)), rating, rd FROM double_rating WHERE username=? '.$limit.'ORDER BY ratetime DESC LIMIT 1',
+		undef, $username);
+	$rd = apply_aging($rd, $age / 86400.0);
+	return ($rating, $rd);
+}
+
+sub create_user_if_not_exists {
+	my ($dbh, $username) = @_;
+	my $count = $dbh->selectrow_array('SELECT count(*) FROM users WHERE username=?',
+		undef, $username);
+	return if ($count > 0);
+	$dbh->do('INSERT INTO users (username) VALUES (?)',
+		undef, $username);
+	$dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,1500.0,350.0)',
+		undef, $username);
+	$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,1500.0,350.0)',
+		undef, $username);
+	return $dbh;
+}
+
+# 10-9 is 0.60
+# 10-0 is 1.00
+sub find_score {
+	my ($score1, $score2) = @_;
+	if ($score1 == 10) {
+		# yay, a win
+		return 0.60 + 0.40 * (9.0-$score2)/9.0;
+	}
+	if ($score2 == 10) {
+		# a loss
+		return 0.40 - 0.40 * (9.0-$score1)/9.0;
+	}
+	die "Nobody won?";
+}
+
+# c=8 => RD=50 moves to RD=350 over approx. five years
+our $c = 8;
+
+sub apply_aging {
+	my ($rd, $age) = @_;
+	$rd = sqrt($rd*$rd + $c * $c * ($age / 86400.0));
+	$rd = 350.0 if ($rd > 350.0);
+	return $rd;
+}
+
+our $q = log(10)/400.0;
+our $pi = 3.1415926535897932384626433832795;
+
+# computes a^b
+sub pow {
+	my ($a, $b) = @_;
+
+	# a^b = exp(log(a^b)) = exp(b log a)
+	return exp($b * log($a));
+}
+
+sub g {
+	my $rd = shift;
+	return 1.0 / sqrt(1.0 + 3.0 * $q * $q * $rd * $rd / ($pi*$pi));
+}
+
+sub E {
+	my ($r, $rating, $rd) = @_;
+	return 1.0 / (1.0 + pow(10.0, -g($rd) * ($r - $rating) / 400.0));
+}
+
+sub dsq {
+	my ($r, $rating, $rd) = @_;
+	return 1.0 / ($q*$q * g($rd) * g($rd) * E($r, $rating, $rd) * (1 - E($r, $rating, $rd)));
+}
+
+sub calc_rating {
+	my ($rating1, $rd1, $rating2, $rd2, $score1, $score2) = @_;
+	my $s1 = find_score($score1, $score2);
+	my $d1sq = dsq($rating1, $rating2, $rd2);
+	my $newr1 = $rating1 + ($q / (1.0/($rd1*$rd1) + 1.0 / $d1sq)) * g($rd2) * ($s1 - E($rating1, $rating2, $rd2));
+	my $newrd1 = sqrt(1.0 / (1.0 / ($rd1*$rd1) + 1.0 / $d1sq));
+
+	$newrd1 = 30.0 if ($newrd1 < 30.0);
+
+	return ($newr1, $newrd1);
+}
+
+1;
diff --git a/www/add-double-result.pl b/www/add-double-result.pl
new file mode 100755
index 0000000..e0f9974
--- /dev/null
+++ b/www/add-double-result.pl
@@ -0,0 +1,85 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+use DBI;
+use CGI;
+use CGI::Carp qw(fatalsToBrowser);
+require '../foosball.pm';
+
+my $cgi = CGI->new;
+
+my $team1_username1 = $cgi->param('team1_username1');
+$team1_username1 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 1/1";
+$team1_username1 = $1;
+
+my $team1_username2 = $cgi->param('team1_username2');
+$team1_username2 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 1/2";
+$team1_username2 = $1;
+
+my $team2_username1 = $cgi->param('team2_username1');
+$team2_username1 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 2/1";
+$team2_username1 = $1;
+
+my $team2_username2 = $cgi->param('team2_username2');
+$team2_username2 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 2/2";
+$team2_username2 = $1;
+
+my $score1 = $cgi->param('score1');
+$score1 =~ /^([0-9]|10)$/ or die "Invalid score 1";
+$score1 = $1;
+
+my $score2 = $cgi->param('score2');
+$score2 =~ /^([0-9]|10)$/ or die "Invalid score 2";
+$score2 = $1;
+
+my $dbh = foosball::db_connect();
+$dbh->{AutoCommit} = 0;
+
+foosball::create_user_if_not_exists($dbh, $team1_username1);
+foosball::create_user_if_not_exists($dbh, $team1_username2);
+foosball::create_user_if_not_exists($dbh, $team2_username1);
+foosball::create_user_if_not_exists($dbh, $team2_username2);
+$dbh->commit;
+
+# fetch the previous double ratings
+my ($rating1_1, $rd1_1) = foosball::find_double_rating($dbh, $team1_username1);
+my ($rating1_2, $rd1_2) = foosball::find_double_rating($dbh, $team1_username2);
+my ($rating2_1, $rd2_1) = foosball::find_double_rating($dbh, $team2_username1);
+my ($rating2_2, $rd2_2) = foosball::find_double_rating($dbh, $team2_username2);
+
+# make virtual "team players"
+my $rating_team1 = 0.5 * ($rating1_1 + $rating1_2);
+my $rd_team1 = sqrt($rd1_1 * $rd1_1 + $rd1_2 * $rd1_2) / sqrt(2.0);
+my $rating_team2 = 0.5 * ($rating1_2 + $rating2_2);
+my $rd_team2 = sqrt($rd2_1 * $rd2_1 + $rd2_2 * $rd2_2) / sqrt(2.0);
+
+my $q = $foosball::q;
+
+my ($new_t1r, undef) =  foosball::calc_rating($rating_team1, $rd_team1, $rating_team2, $rd_team2, $score1, $score2);
+my ($new_t2r, undef) =  foosball::calc_rating($rating_team2, $rd_team2, $rating_team1, $rd_team1, $score2, $score1);
+my $newr1_1 = $rating1_1 + ($new_t1r - $rating_team1);
+my $newr1_2 = $rating1_2 + ($new_t1r - $rating_team1);
+my $newr2_1 = $rating2_1 + ($new_t2r - $rating_team2);
+my $newr2_2 = $rating2_2 + ($new_t2r - $rating_team2);
+
+my (undef, $newrd1_1) = foosball::calc_rating($rating1_1, $rd1_1, $rating_team2, $rd_team2, $score1, $score2);
+my (undef, $newrd1_2) = foosball::calc_rating($rating1_2, $rd1_2, $rating_team2, $rd_team2, $score1, $score2);
+my (undef, $newrd2_1) = foosball::calc_rating($rating2_1, $rd2_1, $rating_team1, $rd_team1, $score2, $score1);
+my (undef, $newrd2_2) = foosball::calc_rating($rating2_2, $rd2_2, $rating_team1, $rd_team1, $score2, $score1);
+
+$dbh->do('INSERT INTO double_results (gametime,team1_username1,team1_username2,team2_username1,team2_username2,score1,score2) VALUES (CURRENT_TIMESTAMP,?,?,?,?,?,?)',
+	undef, $team1_username1, $team1_username2, $team2_username1, $team2_username2, $score1, $score2);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
+	undef, $team1_username1, $newr1_1, $newrd1_1);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
+	undef, $team1_username2, $newr1_2, $newrd1_2);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
+	undef, $team2_username1, $newr2_1, $newrd2_1);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
+	undef, $team2_username2, $newr2_2, $newrd2_2);
+
+$dbh->commit;
+
+print $cgi->header(-status=>'303 See Other',
+		   -location=>'http://foosball.sesse.net/');
+
diff --git a/www/add-single-result.pl b/www/add-single-result.pl
new file mode 100755
index 0000000..78a989e
--- /dev/null
+++ b/www/add-single-result.pl
@@ -0,0 +1,58 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+use DBI;
+use CGI;
+use CGI::Carp qw(fatalsToBrowser);
+require '../foosball.pm';
+
+my $cgi = CGI->new;
+
+my $username1 = $cgi->param('username1');
+$username1 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 1";
+$username1 = $1;
+
+my $username2 = $cgi->param('username2');
+$username2 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 2";
+$username2 = $1;
+
+my $score1 = $cgi->param('score1');
+$score1 =~ /^([0-9]|10)$/ or die "Invalid score 1";
+$score1 = $1;
+
+my $score2 = $cgi->param('score2');
+$score2 =~ /^([0-9]|10)$/ or die "Invalid score 2";
+$score2 = $1;
+
+my $dbh = foosball::db_connect();
+$dbh->{AutoCommit} = 0;
+
+foosball::create_user_if_not_exists($dbh, $username1);
+foosball::create_user_if_not_exists($dbh, $username2);
+$dbh->commit;
+
+# fetch the previous single ratings
+my ($rating1, $rd1) = foosball::find_single_rating($dbh, $username1);
+my ($rating2, $rd2) = foosball::find_single_rating($dbh, $username2);
+
+my $q = $foosball::q;
+
+my ($newr1, $newrd1) = foosball::calc_rating($rating1, $rd1, $rating2, $rd2, $score1, $score2);
+my ($newr2, $newrd2) = foosball::calc_rating($rating2, $rd2, $rating1, $rd1, $score2, $score1);
+
+my $d2sq = foosball::dsq($rating2, $rating1, $rd1);
+my $newr2 = $rating2 + ($q / (1.0/($rd2*$rd2) + 1.0 / $d2sq)) * foosball::g($rd1) * ($s2 - foosball::E($rating2, $rating1, $rd1));
+my $newrd2 = sqrt(1.0 / (1.0 / ($rd2*$rd2) + 1.0 / $d2sq));
+
+$dbh->do('INSERT INTO single_results (gametime,username1,username2,score1,score2) VALUES (CURRENT_TIMESTAMP,?,?,?,?)',
+	undef, $username1, $username2, $score1, $score2);
+$dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
+	undef, $username1, $newr1, $newrd1);
+$dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
+	undef, $username2, $newr2, $newrd2);
+
+$dbh->commit;
+
+print $cgi->header(-status=>'303 See Other',
+		   -location=>'http://foosball.sesse.net/');
+
diff --git a/www/index.pl b/www/index.pl
new file mode 100755
index 0000000..8e0b8f5
--- /dev/null
+++ b/www/index.pl
@@ -0,0 +1,76 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+use lib qw(/srv/bzr.sesse.net/www/xml-template/perl/);
+use XML::Template;
+use CGI;
+require '../foosball.pm',
+
+my $dbh = foosball::db_connect();
+
+# Single score board (whoa, inefficient)
+my @single_top = ();
+my $q = $dbh->prepare('select username from users');
+$q->execute();
+while (my $ref = $q->fetchrow_hashref) {
+	my $username = $ref->{'username'};
+	my ($rating, $rd) = foosball::find_single_rating($dbh, $username);
+	my ($oldrating) = foosball::find_single_rating($dbh, $username, 'AND ratetime::date < current_date ');
+
+	my $trend = "";
+	if (defined($oldrating)) {
+		$trend = (sprintf "%+d", int($rating-$oldrating+0.5));
+	}
+
+	push @single_top, {
+		'username' => $username,
+		'rating' => int($rating+0.5),
+		'rd' => int($rd+0.5),
+		'lowerbound' => int($rating - 3.0*$rd + 0.5),
+		'trend' => $trend,
+	};
+}
+@single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;
+
+# Double score board
+my @double_top = ();
+$q = $dbh->prepare('select username from users');
+$q->execute();
+while (my $ref = $q->fetchrow_hashref) {
+	my $username = $ref->{'username'};
+	my ($rating, $rd) = foosball::find_double_rating($dbh, $username);
+	my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');
+
+	my $trend = "";
+	if (defined($oldrating)) {
+		$trend = (sprintf "%+d", int($rating-$oldrating+0.5));
+	}
+
+	push @double_top, {
+		'username' => $username,
+		'rating' => int($rating+0.5),
+		'rd' => int($rd+0.5),
+		'lowerbound' => int($rating - 3.0*$rd + 0.5),
+		'trend' => $trend,
+	};
+}
+@double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;
+
+# Last games
+my @last_games = ();
+$q = $dbh->prepare('select * from ( select to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,\'Double\' as type,team1_username1 || \' / \' || team1_username2 as username1, team2_username1 || \' / \' || team2_username2 as username2,score1,score2 from double_results union all select to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,\'Single\' as type,username1,username2,score1,score2 from single_results ) t1 order by gametime desc limit 10');
+$q->execute();
+while (my $ref = $q->fetchrow_hashref) {
+	push @last_games, $ref;
+}
+
+$dbh->disconnect;
+
+print CGI->header(-type=>'application/xhtml+xml');
+
+my $doc = XML::Template::process_file('index.xml', {
+	'#singletop' => \@single_top,
+	'#doubletop' => \@double_top,
+	'#lastgames' => \@last_games,
+});
+print $doc->toString;
diff --git a/www/index.xml b/www/index.xml
new file mode 100644
index 0000000..8409d4b
--- /dev/null
+++ b/www/index.xml
@@ -0,0 +1,167 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE
+  html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
+  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
+<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" xmlns:t="http://template.sesse.net/">
+  <head>
+    <title>Foosball!</title>
+    <link rev="made" href="mailto:sgunderson@bigfoot.com" />
+    <meta name="MSSmartTagsPreventParsing" content="TRUE" />
+  </head>
+  <body>
+    <h1>Foosball!</h1>
+    
+    <h2>Add a singles result</h2>
+
+    <form method="post" action="add-single-result.pl">
+      <table>
+        <tr>
+	  <th>User name 1</th>
+	  <td><input name="username1" value="" size="10" /></td>
+	</tr>
+        <tr>
+	  <th>User name 2</th>
+	  <td><input name="username2" value="" size="10" /></td>
+	</tr>
+	<tr>
+	  <th>Score</th>
+	  <td>
+	    <input name="score1" value="" size="2" /> -
+	    <input name="score2" value="" size="2" />
+	  </td>
+	</tr>
+	<tr>
+	  <td colspan="2"><input type="submit" /></td>
+	</tr>
+      </table>
+    </form>
+    
+    <h2>Add a doubles result</h2>
+
+    <form method="post" action="add-double-result.pl">
+      <table>
+        <tr>
+	  <th>Team 1 (usernames)</th>
+	  <td>
+	    <input name="team1_username1" value="" size="10" /> and 
+	    <input name="team1_username2" value="" size="10" />
+	  </td>
+	</tr>
+        <tr>
+	  <th>Team 2 (usernames)</th>
+	  <td>
+	    <input name="team2_username1" value="" size="10" /> and 
+	    <input name="team2_username2" value="" size="10" />
+	  </td>
+	</tr>
+	<tr>
+	  <th>Score</th>
+	  <td>
+	    <input name="score1" value="" size="2" /> -
+	    <input name="score2" value="" size="2" />
+	  </td>
+	</tr>
+	<tr>
+	  <td colspan="2"><input type="submit" /></td>
+	</tr>
+      </table>
+    </form>
+
+    <h2>Singles score board</h2>
+
+    <table>
+      <thead>
+        <tr>
+          <th>Username</th>
+	  <th>Rating mean</th>
+	  <th>Rating deviation</th>
+	  <th>Conservative estimate</th>
+	  <th>Change since yesterday</th>
+	</tr>
+      </thead>
+      <tbody t:id="singletop">
+        <tr>
+	  <td><t:username /></td>
+	  <td><t:rating /></td>
+	  <td><t:rd /></td>
+	  <td><t:lowerbound /></td>
+	  <td><t:trend /></td>
+	</tr>
+      </tbody>
+    </table>
+
+    <h2>Doubles score board</h2>
+
+    <table>
+      <thead>
+        <tr>
+          <th>Username</th>
+	  <th>Rating mean</th>
+	  <th>Rating deviation</th>
+	  <th>Conservative estimate</th>
+	  <th>Change since yesterday</th>
+	</tr>
+      </thead>
+      <tbody t:id="doubletop">
+        <tr>
+	  <td><t:username /></td>
+	  <td><t:rating /></td>
+	  <td><t:rd /></td>
+	  <td><t:lowerbound /></td>
+	  <td><t:trend /></td>
+	</tr>
+      </tbody>
+    </table>
+
+    <h2>Last ten games</h2>
+
+    <table>
+      <thead>
+        <tr>
+          <th>Registered</th>
+          <th>Type</th>
+          <th colspan="2">Opponents</th>
+	  <th>Score</th>
+	</tr>
+      </thead>
+      <tbody t:id="lastgames">
+        <tr>
+	  <td><t:gametime /></td>
+	  <td><t:type /></td>
+	  <td><t:username1 /></td>
+	  <td><t:username2 /></td>
+	  <td><t:score1 /> &#8211; <t:score2 /></td>
+	</tr>
+      </tbody>
+    </table>
+
+    <h2>About the ratings</h2>
+
+    <p>The rating system is a modified Glicko 1 variant, adjusted for teams
+      (with some ideas from Microsoft's TrueSkill system) and non-binary results.
+      For those not familiar with these ratings, the most important parts are:</p>
+
+    <ul>
+      <li>Your rating is a <em>statistical estimation</em> of your true skill.
+	It has a mean (the point estimate of your skill) and a deviation
+	(measuring the uncertainity of the estimate), called the RD. It is approximately
+	Gaussian (actually logistic).</li>
+      <li>When you win or lose a game, your rating will change accordingly,
+        based on your score and your opponent. <em>You do not get 'points'
+	for winning or losing, the estimate is merely getting more accurate.</em>
+	In the process, the RD gets lower as you play. However, the RD increases
+	with time, opening up for the fact that your true skill can change.
+	(Glicko 2 also supports a volatility measure, which better models change
+	in true skill, but it has not been implemented here.)</li>
+      <li>The score board is sorted by a conservative estimate of your rating
+        (mean - 3 * RD). This makes it non-attractive for people with artifically
+	high ratings (especially newcomers) to avoid playing to stay high up in
+	the score board.</li>
+      <li>The single and double rankings are separate. Even though you play as a
+        team and all four players' rankings and RDs influence the rating adjustment,
+        you are ranked as an individual, as we do not usually play with fixed
+	teams.</li>
+    </ul>
+  </body>
+</html>	
+