Hello, I've been trying to code a TrueSkill calculator using Java that fetches scores and updates them in a MySQL database.
I finished coding it however it appears to have a problem while executing.
This is an example of what I want the output to be like:
connecting to database server
connected
beginning transaction
creating tables
getting unscored games
found 2 unscored games
gameid 1 found
gameid 1 has no players, ignoring
gameid 3 found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
new player [] found
gameid 3 is calculating
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
player [] mu: **old** -> **new**
sigma: **old** -> **new**
copying dota elo scores to scores table
committing transaction
done
Where **old** and **new** are the corresponding calculated ratings.
However this is the output I get:
connecting to database server
connected
beginning transaction
creating tables
getting unscored games
found 2 unscored games
gameid 1 has no players, ignoring
copying dota ts scores to tsscores table
committing transaction
done
It clearly appears to be a problem in my loops after I fetch the number of unscored games and their id's but I can't seem to figure it out.
Here is my code:
package dota;
/*
*
* @author Pavel Yarmak
*
*/
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;
import java.math.*;
import java.text.*;
import jskills.*;
public class Dota {
static ArrayList<String> names = new ArrayList<String>();
static Team team1 = new Team();
static Team team2 = new Team();
static double matchQuality;
public static void main(String[] args) {
NumberFormat nf = NumberFormat.getNumberInstance();
nf.setMaximumFractionDigits(2);
nf.setRoundingMode(RoundingMode.HALF_DOWN);
Connection con = null;
Statement st = null;
PreparedStatement pst = null;
ResultSet rs = null;
Properties props = new Properties();
FileInputStream in = null;
try {
in = new FileInputStream("database.properties");
props.load(in);
} catch (FileNotFoundException ex) {
Logger lgr = Logger.getLogger(Dota.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} catch (IOException ex) {
Logger lgr = Logger.getLogger(Dota.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (in != null) {
in.close();
}
} catch (IOException ex) {
Logger lgr = Logger.getLogger(Dota.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
try {
System.out.println("connecting to database server");
con = DriverManager.getConnection(url, user, passwd);
con.setAutoCommit(false);
System.out.println("connected");
System.out.println("beginning transaction");
System.out.println("creating tables");
st = con.createStatement();
st.execute("CREATE TABLE IF NOT EXISTS dota_ts_scores "
+ "( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(15) NOT NULL, server VARCHAR(100) NOT NULL, mu REAL NOT NULL, sigma REAL NOT NULL )");
st.execute("CREATE TABLE IF NOT EXISTS dota_ts_games_scored "
+ "( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, gameid INT NOT NULL, quality REAL NOT NULL )");
System.out.println("getting unscored games");
pst = con.prepareStatement("SELECT id FROM games WHERE id NOT IN ( SELECT gameid FROM dota_ts_games_scored ) ORDER BY id");
rs = pst.executeQuery();
LinkedList <Integer>UnscoredGames = new LinkedList<Integer>();
while (rs.next()) {
UnscoredGames.add(rs.getInt(1));
/*
Iterator iterator = UnscoredGames.iterator();
while(iterator.hasNext()){
System.out.println(iterator.next()+" ");
}
*
*/
}
System.out.println("found " + UnscoredGames.size() + " unscored games");
while (!UnscoredGames.isEmpty()){
int GameID = UnscoredGames.getFirst();
UnscoredGames.removeFirst();
pst = con.prepareStatement("SELECT dota_ts_scores.id, gameplayers.name, "
+ "spoofedrealm, newcolour, winner, mu, sigma FROM dotaplayers LEFT JOIN "
+ "dotagames ON dotagames.gameid=dotaplayers.gameid LEFT JOIN "
+ "gameplayers ON gameplayers.gameid=dotaplayers.gameid AND "
+ "gameplayers.colour=dotaplayers.colour LEFT JOIN "
+ "dota_ts_scores ON dota_ts_scores.name=gameplayers.name AND "
+ "server=spoofedrealm WHERE dotaplayers.gameid=" + Integer.toString(GameID));
rs = pst.executeQuery();
if (!rs.next()) {
boolean ignore = false;
int rowids[] = new int[10];
String servers[] = new String[10];
boolean exists[] = new boolean[10];
int num_players = 0;
double player_mu[] = new double[10];
double player_sigma[] = new double[10];
int player_teams[] = new int[10];
double team_ratings[] = new double[2];
int team_winners[] = new int[2];
int team_numplayers[] = new int[10];
while (rs.next()) {
System.out.println("gameid " + Integer.toString(GameID) + " found");
team_ratings[0] = 0.0;
team_ratings[1] = 0.0;
team_numplayers[0] = 0;
team_numplayers[1] = 0;
if( num_players >= 10 ) {
System.out.println("gameid " + Integer.toString(GameID) + " has more than 10 players, ignoring");
ignore = true;
break;
}
int Winner = rs.getInt(4);
if (Winner != 1 && Winner != 2) {
System.out.println("gameid " + Integer.toString(GameID) + " has no winner, ignoring");
ignore = true;
break;
}
else if( Winner == 1 ) {
team_winners[0] = 1;
team_winners[1] = 0;
}
else {
team_winners[0] = 0;
team_winners[1] = 1;
}
int id = rs.getInt(1);
if (!rs.wasNull())
rowids[num_players] = id;
else
rowids[num_players] = 0;
names.add(num_players, rs.getString(2));
servers[num_players] = rs.getString(3);
Double mu = rs.getDouble(6);
Double sigma = rs.getDouble(7);
if (!rs.wasNull()) {
exists[num_players] = true;
player_mu[num_players] = mu;
player_sigma[num_players] = sigma;
}
else {
System.out.println("new player [" + rs.getString(2) + "] found");
exists[num_players] = false;
player_mu[num_players] = 100.0;
player_sigma[num_players] = 100.0/3;
}
int Colour = rs.getInt(4);
if( Colour >= 1 && Colour <= 5 ) {
player_teams[num_players] = 0;
team1.addPlayer(new Player(rs.getString(2)),
new Rating(player_mu[num_players],
player_sigma[num_players]));
team_numplayers[0]++;
}
else if( Colour >= 7 && Colour <= 11 ) {
player_teams[num_players] = 1;
team2.addPlayer(new Player(rs.getString(2)),
new Rating(player_mu[num_players],
player_sigma[num_players]));
team_numplayers[1]++;
}
else {
System.out.println("gameid " + Integer.toString(GameID) + " has a player with an invalid newcolour, ignoring");
ignore = true;
break;
}
num_players++;
}
if(!ignore) {
if(num_players == 0)
System.out.println("gameid " + Integer.toString(GameID) + " has no players, ignoring");
else if( team_numplayers[0] == 0 )
System.out.println("gameid " + Integer.toString(GameID) + " has no Sentinel players, ignoring");
else if( team_numplayers[1] == 0 )
System.out.println("gameid " + Integer.toString(GameID) + " has no Scourge players, ignoring");
else {
System.out.println("gameid " + Integer.toString(GameID) + " is calculating");
double old_player_mu[] = new double[player_mu.length];
double old_player_sigma[] = new double[player_sigma.length];
for ( int idx = 0 ; idx <= player_mu.length; ++idx )
System.arraycopy( player_mu, 0, old_player_mu, 0, player_mu.length );
for ( int idx = 0 ; idx <= player_sigma.length; ++idx )
System.arraycopy( player_sigma, 0, old_player_sigma, 0, player_sigma.length );
Collection<ITeam> teams = Team.concat(team1, team2);
team1.clear();
team2.clear();
GameInfo gameinfo = new GameInfo(100.0, // mu
100.0/3.0, // sigma
100.0/6.0, // beta
100.0/300.0, // tau
0.00); // draw probability
Map<IPlayer, Rating> results = TrueSkillCalculator.calculateNewRatings(gameinfo, teams, team_winners);
matchQuality = TrueSkillCalculator.calculateMatchQuality(gameinfo, teams);
System.out.println("match quality: " + matchQuality);
for (ITeam t : teams) {
for (IPlayer p : t.keySet()) {
Rating r = results.get(p);
player_mu[names.indexOf(p)] = r.getMean();
player_sigma[names.indexOf(p)] = r.getStandardDeviation();
}
}
for( int i = 0; i < num_players; i++ ) {
System.out.println("player [" + names.get(i) + "] mu: " + old_player_mu[i] + " -> " + player_mu[i]);
System.out.println("sigma: " + old_player_sigma[i] + " -> " + player_sigma[i]);
if(exists[i]) {
pst = con.prepareStatement("UPDATE dota_ts_scores SET mu="
+ nf.format(player_mu[i]) + ", sigma=" + nf.format(player_sigma[i]) + " WHERE id=" + Integer.toString(rowids[i]));
pst.executeUpdate();
}
else {
pst = con.prepareStatement("INSERT INTO dota_ts_scores ( name, server, mu, sigma ) VALUES ( '"
+ names.get(i) + "', '" + servers[i] + "', " + nf.format(player_mu[i]) + "', '" + nf.format(player_sigma[i]) + " )");
pst.executeUpdate();
}
}
}
names.clear();
}
}
pst = con.prepareStatement("INSERT INTO dota_ts_games_scored ( gameid, quality ) VALUES ( "
+ Integer.toString(GameID) + ", " + matchQuality + " )");
pst.executeUpdate();
}
System.out.println("copying dota ts scores to tsscores table");
st.execute("DELETE FROM tsscores WHERE category='dota_ts'");
st.execute("INSERT INTO tsscores ( category, name, server, mu, sigma ) SELECT 'dota_ts', name, server, mu, sigma FROM dota_ts_scores");
System.out.println("committing transaction");
con.commit();
System.out.println("done");
} catch (SQLException ex) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr = Logger.getLogger(Dota.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
Logger lgr = Logger.getLogger(Dota.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Dota.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}Please let me know if you'd like me to provide any additional information.
Thank you for your time.
