/** Creates tables if they do not exist. */
private void initializeTables() {
Connection conn = null;
Statement statement = null;
try {
conn = retrieveConnection();
statement = conn.createStatement();
statement.setQueryTimeout(30);
// One table holding the playername id relation
String playerQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT, %s STRING)",
playerTable, "name");
statement.executeUpdate(playerQuery);
// One column for every message
StringBuilder columns = new StringBuilder();
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (node.getColumnName() != null
&& (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME)) {
columns.append(',');
columns.append(node.getColumnName());
}
}
String msgQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY UNIQUE %s)",
msgTable, columns);
statement.executeUpdate(msgQuery);
// Check if all columns are present
DatabaseMetaData dmd = conn.getMetaData();
// Add missing columns
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME) {
ResultSet set = dmd.getColumns(null, null, msgTable, node.getColumnName());
if (!set.next()) {
String updateQuery =
String.format("ALTER TABLE %s ADD COLUMN %s", msgTable, node.getColumnName());
statement.executeUpdate(updateQuery);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Get how often a message has been displayed
*
* @param node to get the count for
* @param playerId id of the player to get the count for
* @return how often this message has been displayed
*/
private int getCountFor(MessageNode node, int playerId) {
Connection conn = null;
Statement statement = null;
ResultSet result = null;
int value = 0;
try {
conn = retrieveConnection();
statement = conn.createStatement();
String select = String.format("SELECT * FROM %s WHERE %s = %s", msgTable, "id", playerId);
result = statement.executeQuery(select);
if (result.next()) value = result.getInt(node.getColumnName());
else // create the missing row
{
String newPlayerDataQuery =
String.format( // empty row in messages
"INSERT INTO %s (%s) VALUES (%s)", msgTable, "id", playerId);
conn.createStatement().executeUpdate(newPlayerDataQuery);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
if (result != null) result.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return value;
}
/**
* Set the count of a certain message to a certain value
*
* @param node node to set the count for
* @param playerId player for whom we are tracking the count
* @param value value to set
*/
private void set(MessageNode node, int playerId, int value) {
Validate.isTrue(value >= 0, "Count has to be positive");
Connection conn = null;
Statement statement = null;
try {
conn = retrieveConnection();
statement = conn.createStatement();
// Set the count to the provided value
String setQuery =
String.format(
"UPDATE %s SET %s = %s WHERE id = %s",
msgTable, node.getColumnName(), value, playerId);
statement.execute(setQuery);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Resets all counts for a given player
*
* @param playerName player to reset the stats for
*/
public void resetAll(String playerName) {
int playerId = getPlayerId(playerName);
for (MessageNode node : MessageNode.values()) {
if (node.getColumnName() != null) {
set(node, playerId, 0);
}
}
}
