8 my $dbh = DBI->connect("dbi:Pg:dbname=foosball;host=127.0.0.1", "foosball", "cleanrun", {AutoCommit => 0});
9 $dbh->{RaiseError} = 1;
13 sub find_single_rating {
14 my ($dbh, $username, $limit) = @_;
15 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',
17 $rd = apply_aging($rd, $age / 86400.0);
18 return ($rating, $rd);
21 sub find_double_rating {
22 my ($dbh, $username, $limit) = @_;
23 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',
25 $rd = apply_aging($rd, $age / 86400.0);
26 return ($rating, $rd);
29 sub create_user_if_not_exists {
30 my ($dbh, $username) = @_;
31 my $count = $dbh->selectrow_array('SELECT count(*) FROM users WHERE username=?',
33 return if ($count > 0);
34 $dbh->do('INSERT INTO users (username) VALUES (?)',
36 $dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,1500.0,350.0)',
38 $dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,1500.0,350.0)',
43 # c=8 => RD=50 moves to RD=350 over approx. five years
48 $rd = sqrt($rd*$rd + $c * $c * ($age / 86400.0));
49 $rd = 350.0 if ($rd > 350.0);
54 my ($rating1, $rd1, $rating2, $rd2, $score1, $score2) = @_;
55 my $result = `/srv/foosball.sesse.net/foorank $rating1 $rd1 $rating2 $rd2 $score1 $score2`;
57 my ($newr1, $newrd1) = split / /, $result;
59 $newrd1 = 30.0 if ($newrd1 < 30.0);
61 return ($newr1, $newrd1);