soc.server.database
Class SOCDBHelper

java.lang.Object
  soc.server.database.SOCDBHelper

public class SOCDBHelper
extends java.lang.Object

This class contains methods for connecting to a database and for manipulating the data stored there.

Based on jdbc code found at www.javaworld.com

This code assumes that you're using mySQL as your database, but allows you to use other database types. The default URL is "jdbc:mysql://localhost/socdata". The default driver is "com.mysql.jdbc.Driver". These can be changed by supplying properties to initialize(String, String, Properties) for PROP_JSETTLERS_DB_URL and PROP_JSETTLERS_DB_DRIVER.

It uses a database created with the following commands:
(See src/bin/sql/jsettlers-tables.sql)
CREATE DATABASE socdata; USE socdata; CREATE TABLE users (nickname VARCHAR(20), host VARCHAR(50), password VARCHAR(20), email VARCHAR(50), lastlogin DATE); CREATE TABLE logins (nickname VARCHAR(20), host VARCHAR(50), lastlogin DATE); CREATE TABLE games (gamename VARCHAR(20), player1 VARCHAR(20), player2 VARCHAR(20), player3 VARCHAR(20), player4 VARCHAR(20), score1 TINYINT, score2 TINYINT, score3 TINYINT, score4 TINYINT, starttime TIMESTAMP); CREATE TABLE robotparams (robotname VARCHAR(20), maxgamelength INT, maxeta INT, etabonusfactor FLOAT, adversarialfactor FLOAT, leaderadversarialfactor FLOAT, devcardmultiplier FLOAT, threatmultiplier FLOAT, strategytype INT, starttime TIMESTAMP, endtime TIMESTAMP, gameswon INT, gameslost INT, tradeFlag BOOL);

Author:

Robert S. Thomas

Field Summary

private static java.sql.Connection	connection db connection, or null if never initialized or if cleaned up for shutdown.
private static java.lang.String	CREATE_ACCOUNT_COMMAND
private static java.sql.PreparedStatement	createAccountCommand Create a new account in users: CREATE_ACCOUNT_COMMAND
private static java.lang.String	dbURL Retain the URL (default, or passed via props to initialize(String, String, Properties)).
private static java.lang.String	driverclass The db driver used, or null if none.
private static java.sql.Driver	driverinstance The db driver instance, if we dynamically loaded its JAR.
private static boolean	errorCondition This flag indicates that the connection should be valid, yet the last operation failed.
private static java.lang.String	HOST_QUERY
private static java.sql.PreparedStatement	hostQuery
private static boolean	initialized True if we successfully completed initialize(String, String, Properties) without throwing an exception.
private static java.lang.String	LASTLOGIN_UPDATE
private static java.sql.PreparedStatement	lastloginUpdate
private static java.lang.String	password Cached password used when reconnecting on error
private static java.lang.String	PASSWORD_UPDATE
private static java.sql.PreparedStatement	passwordUpdate User password update in users: PASSWORD_UPDATE
static java.lang.String	PROP_IMPL_JSETTLERS_PW_RESET Internal property name used to hold the --pw-reset command line argument's username.
static java.lang.String	PROP_JSETTLERS_DB_DRIVER Property jsettlers.db.driver to specify the server's JDBC driver class.
static java.lang.String	PROP_JSETTLERS_DB_JAR Property jsettlers.db.jar to specify the JAR filename for the server's JDBC driver.
static java.lang.String	PROP_JSETTLERS_DB_PASS Property jsettlers.db.pass to specify the server's SQL database password.
static java.lang.String	PROP_JSETTLERS_DB_SAVE_GAMES Property jsettlers.db.save.games to ask to save all game results in the database.
static java.lang.String	PROP_JSETTLERS_DB_SCRIPT_SETUP Property jsettlers.db.script.setup to run a SQL setup script at server startup, then exit.
static java.lang.String	PROP_JSETTLERS_DB_URL Property jsettlers.db.url to specify the server's URL.
static java.lang.String	PROP_JSETTLERS_DB_USER Property jsettlers.db.user to specify the server's SQL database username.
private static java.lang.String	RECORD_LOGIN_COMMAND
private static java.sql.PreparedStatement	recordLoginCommand
private static java.lang.String	ROBOT_PARAMS_QUERY
private static java.sql.PreparedStatement	robotParamsQuery Query all robot parameters for a bot name; ROBOT_PARAMS_QUERY.
private static java.lang.String	SAVE_GAME_COMMAND
private static java.sql.PreparedStatement	saveGameCommand Completed-game info insert into games: SAVE_GAME_COMMAND
private static java.lang.String	USER_COUNT_QUERY
private static java.lang.String	USER_EXISTS_QUERY
private static java.lang.String	USER_PASSWORD_QUERY
private static java.sql.PreparedStatement	userCountQuery Query how many users, if any, exist in the users table: USER_COUNT_QUERY.
private static java.sql.PreparedStatement	userExistsQuery Query whether a user nickname exists in users: USER_EXISTS_QUERY
private static java.lang.String	userName Cached username used when reconnecting on error
private static java.sql.PreparedStatement	userPasswordQuery

Constructor Summary

SOCDBHelper()

Method Summary

private static boolean	checkConnection() Checks if connection is supposed to be present and attempts to reconnect if there was previously an error.
static void	cleanup(boolean isForShutdown) Close out and shut down the database connection.
private static boolean	connect(java.lang.String user, java.lang.String pswd, java.lang.String setupScriptPath) Opens a new connection and initializes the prepared statements.
static int	countUsers() Count the number of users, if any, currently in the users table.
static boolean	createAccount(java.lang.String userName, java.lang.String host, java.lang.String password, java.lang.String email, long time) Attempt to create a new account with a unique userName in the users table.
(package private) static void	dispResultSet(java.sql.ResultSet rs)
static boolean	doesTableColumnExist(java.lang.String tabname, java.lang.String colname) Query to see if a column exists in a table.
static boolean	doesUserExist(java.lang.String userName) Does this user (nickname) exist in the database?
static java.lang.String	getUserFromHost(java.lang.String host) DOCUMENT ME!
static java.lang.String	getUserPassword(java.lang.String sUserName) Verify that this user exists, and retrieve their password from the database.
static void	initialize(java.lang.String user, java.lang.String pswd, java.util.Properties props) This makes a connection to the database and initializes the prepared statements.
static boolean	isInitialized() Were we able to initialize(String, String, Properties) and connect to the database? True if db is connected and available; false if never initialized, or if cleanup(boolean) was called.
static boolean	recordLogin(java.lang.String userName, java.lang.String host, long time) Record this user's login host and time.
static SOCRobotParameters	retrieveRobotParams(java.lang.String robotName) Get this robot's specialized parameters from the database, if it has an entry there.
private static void	runSetupScript(java.lang.String setupScriptPath) Load and run a SQL script.
private static void	saveGameScores_fit6pInto4(SOCGame ga, java.lang.String[] names, short[] scores) Try and fit names and scores of player 5 and/or player 6 into the 4 db slots, for backwards-compatibility.
static boolean	saveGameScores(SOCGame ga, long gameLengthSeconds) Record this game's time, players, and scores in the database.
static boolean	updateLastlogin(java.lang.String userName, long time) DOCUMENT ME!
static boolean	updateUserPassword(java.lang.String userName, java.lang.String newPassword) Update a user's password if the user is in the database.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

PROP_JSETTLERS_DB_USER

public static final java.lang.String PROP_JSETTLERS_DB_USER

Property jsettlers.db.user to specify the server's SQL database username. Default is "socuser".

Since:

1.1.09

See Also:

Constant Field Values

PROP_JSETTLERS_DB_PASS

public static final java.lang.String PROP_JSETTLERS_DB_PASS

Property jsettlers.db.pass to specify the server's SQL database password. Default is "socpass". v1.1.20 and higher allow a blank password ("").

Since:

1.1.09

See Also:

Constant Field Values

PROP_JSETTLERS_DB_JAR

public static final java.lang.String PROP_JSETTLERS_DB_JAR

Property jsettlers.db.jar to specify the JAR filename for the server's JDBC driver. This is required when running a JAR file, since JVM will ignore CLASSPATH.

Default is blank (no driver jar file), since the filename varies when used.

Since:

1.1.15

See Also:

Constant Field Values

PROP_JSETTLERS_DB_DRIVER

public static final java.lang.String PROP_JSETTLERS_DB_DRIVER

Property jsettlers.db.driver to specify the server's JDBC driver class. The default driver is "com.mysql.jdbc.Driver". If the URL begins with "jdbc:postgresql:", the driver will be "org.postgresql.Driver". If the URL begins with "jdbc:sqlite:", the driver will be "org.sqlite.JDBC".

Since:

1.1.09

See Also:

Constant Field Values

PROP_JSETTLERS_DB_URL

public static final java.lang.String PROP_JSETTLERS_DB_URL

Property jsettlers.db.url to specify the server's URL. The default URL is "jdbc:mysql://localhost/socdata".

Since:

1.1.09

See Also:

Constant Field Values

PROP_JSETTLERS_DB_SCRIPT_SETUP

public static final java.lang.String PROP_JSETTLERS_DB_SCRIPT_SETUP

Property jsettlers.db.script.setup to run a SQL setup script at server startup, then exit. Used to create tables when setting up a server. To activate this feature, set this to the SQL script's full path or relative path.

To implement this, the SOCServer constructor connects to the db and runs the setup script, then signals success by throwing an EOFException which is caught by main(..). Errors throw SQLException instead.

Since:

1.1.15

See Also:

Constant Field Values

PROP_JSETTLERS_DB_SAVE_GAMES

public static final java.lang.String PROP_JSETTLERS_DB_SAVE_GAMES

Property jsettlers.db.save.games to ask to save all game results in the database. Set this to 1 or Y to activate this feature.

Since:

1.1.15

See Also:

Constant Field Values

PROP_IMPL_JSETTLERS_PW_RESET

public static final java.lang.String PROP_IMPL_JSETTLERS_PW_RESET

Internal property name used to hold the --pw-reset command line argument's username. When present at server startup, the server will prompt and reset the password if the user exists, then exit.

This is a Utility Mode parameter; not for use in property files, because the program always exits after trying to change the password.

Since:

1.1.20

See Also:

Constant Field Values

driverclass

private static java.lang.String driverclass

The db driver used, or null if none. If driverinstance != null, use that to connect instead of driverclass; we still need to remember driverclass to detect various db-specific behaviors. Set in initialize(String, String, Properties).

Since:

1.1.14

driverinstance

private static java.sql.Driver driverinstance

The db driver instance, if we dynamically loaded its JAR. Otherwise null, use dbURL to connect instead.

Used because DriverManager.registerDriver(Driver) won't work if the classloader is different, which it will be for dynamic loading.

Set in initialize(String, String, Properties). Used in connect(String, String, String).

Since:

1.1.15

connection

private static java.sql.Connection connection

db connection, or null if never initialized or if cleaned up for shutdown. If this is non-null but closed, most queries will try to recreate it via checkConnection(). Set in connect(String, String, String), based on the dbURL from initialize(String, String, Properties). Cleared in cleanup(true).

dbURL

private static java.lang.String dbURL

Retain the URL (default, or passed via props to initialize(String, String, Properties)). Used in connect(String, String, String).

If driverinstance != null, go through it to connect to dbURL.

Since:

1.1.09

errorCondition

private static boolean errorCondition

This flag indicates that the connection should be valid, yet the last operation failed. Methods will attempt to reconnect prior to their operation if this is set.

initialized

private static boolean initialized

True if we successfully completed initialize(String, String, Properties) without throwing an exception. Set false in cleanup(boolean).

userName

private static java.lang.String userName

Cached username used when reconnecting on error

password

private static java.lang.String password

Cached password used when reconnecting on error

CREATE_ACCOUNT_COMMAND

private static java.lang.String CREATE_ACCOUNT_COMMAND

RECORD_LOGIN_COMMAND

private static java.lang.String RECORD_LOGIN_COMMAND

USER_PASSWORD_QUERY

private static java.lang.String USER_PASSWORD_QUERY

HOST_QUERY

private static java.lang.String HOST_QUERY

LASTLOGIN_UPDATE

private static java.lang.String LASTLOGIN_UPDATE

PASSWORD_UPDATE

private static final java.lang.String PASSWORD_UPDATE

See Also:

Constant Field Values

SAVE_GAME_COMMAND

private static java.lang.String SAVE_GAME_COMMAND

ROBOT_PARAMS_QUERY

private static java.lang.String ROBOT_PARAMS_QUERY

USER_COUNT_QUERY

private static final java.lang.String USER_COUNT_QUERY

See Also:

Constant Field Values

USER_EXISTS_QUERY

private static final java.lang.String USER_EXISTS_QUERY

See Also:

Constant Field Values

createAccountCommand

private static java.sql.PreparedStatement createAccountCommand

Create a new account in users: CREATE_ACCOUNT_COMMAND

recordLoginCommand

private static java.sql.PreparedStatement recordLoginCommand

userExistsQuery

private static java.sql.PreparedStatement userExistsQuery

Query whether a user nickname exists in users: USER_EXISTS_QUERY

userPasswordQuery

private static java.sql.PreparedStatement userPasswordQuery

hostQuery

private static java.sql.PreparedStatement hostQuery

lastloginUpdate

private static java.sql.PreparedStatement lastloginUpdate

passwordUpdate

private static java.sql.PreparedStatement passwordUpdate

User password update in users: PASSWORD_UPDATE

saveGameCommand

private static java.sql.PreparedStatement saveGameCommand

Completed-game info insert into games: SAVE_GAME_COMMAND

robotParamsQuery

private static java.sql.PreparedStatement robotParamsQuery

Query all robot parameters for a bot name; ROBOT_PARAMS_QUERY. Used in retrieveRobotParams(String).

userCountQuery

private static java.sql.PreparedStatement userCountQuery

Query how many users, if any, exist in the users table: USER_COUNT_QUERY.

Since:

1.1.19

Constructor Detail

SOCDBHelper

public SOCDBHelper()

Method Detail

initialize

public static void initialize(java.lang.String user,
                              java.lang.String pswd,
                              java.util.Properties props)
                       throws java.sql.SQLException,
                              java.io.IOException

This makes a connection to the database and initializes the prepared statements. (If props includes PROP_JSETTLERS_DB_SCRIPT_SETUP, runs that script before the prepared statements.) Sets isInitialized().

The default URL is "jdbc:mysql://localhost/socdata". The default driver is "com.mysql.jdbc.Driver". These can be changed by supplying props.

Parameters:

user - the user name for accessing the database

pswd - the password for the user, or ""

props - null, or properties containing PROP_JSETTLERS_DB_DRIVER, PROP_JSETTLERS_DB_URL, and any other desired properties. Ignores PROP_JSETTLERS_DB_USER and PROP_JSETTLERS_DB_PASS if present, uses the user and pswd parameters instead.

Throws:

java.sql.SQLException - if an SQL command fails, or the db couldn't be initialized; or if the PROP_JSETTLERS_DB_DRIVER property is not mysql, not sqlite, not postgres, but the PROP_JSETTLERS_DB_URL property is not provided.

java.io.IOException - if props includes PROP_JSETTLERS_DB_SCRIPT_SETUP but the SQL file wasn't found, or if any other IO error occurs reading the script

isInitialized

public static boolean isInitialized()

Were we able to initialize(String, String, Properties) and connect to the database? True if db is connected and available; false if never initialized, or if cleanup(boolean) was called.

Returns:

True if available

Since:

1.1.14

checkConnection

private static boolean checkConnection()
                                throws java.sql.SQLException

Checks if connection is supposed to be present and attempts to reconnect if there was previously an error. Reconnecting closes the current connection, opens a new one, and re-initializes the prepared statements.

Returns:

true if the connection is established upon return

Throws:

java.sql.SQLException

connect

private static boolean connect(java.lang.String user,
                               java.lang.String pswd,
                               java.lang.String setupScriptPath)
                        throws java.sql.SQLException,
                               java.io.IOException

Opens a new connection and initializes the prepared statements. initialize(String, String, Properties) and checkConnection() use this to get ready. Uses dbURL and driverinstance.

If setupScriptPath != null, it will be ran before preparing statements. That way, it can create tables used by the statements.

Parameters:

user - DB username

pswd - DB user password, or ""

setupScriptPath - Full path or relative path to SQL script to run at connect, or null; typically from PROP_JSETTLERS_DB_SCRIPT_SETUP

Returns:

true on success; will never return false, instead will throw a sqlexception

Throws:

java.io.IOException - if setupScriptPath wasn't found, or if any other IO error occurs reading the script

java.sql.SQLException - if any connect error, missing table, or SQL error occurs

runSetupScript

private static void runSetupScript(java.lang.String setupScriptPath)
                            throws java.io.FileNotFoundException,
                                   java.io.IOException,
                                   java.sql.SQLException

Load and run a SQL script. Typically DDL commands to create or alter tables, indexes, etc.

Parameters:

setupScriptPath - Full path or relative path to the SQL script filename

Throws:

java.io.FileNotFoundException - if file not found

java.io.IOException - if any other IO error occurs

java.sql.SQLException - if any unexpected database problem

Since:

1.1.15

doesUserExist

public static boolean doesUserExist(java.lang.String userName)
                             throws java.lang.IllegalArgumentException,
                                    java.sql.SQLException

Does this user (nickname) exist in the database?

Parameters:

userName - User nickname to check

Returns:

True if found in users table, false otherwise or if no database is currently connected

Throws:

java.lang.IllegalArgumentException - if userName is null

java.sql.SQLException - if any unexpected database problem

Since:

1.1.20

See Also:

getUserPassword(String)

getUserPassword

public static java.lang.String getUserPassword(java.lang.String sUserName)
                                        throws java.sql.SQLException

Verify that this user exists, and retrieve their password from the database.

Parameters:

sUserName - Username who needs password

Returns:

null if user account doesn't exist, or if database is not currently connected

Throws:

java.sql.SQLException - if any unexpected database problem

See Also:

doesUserExist(String)

getUserFromHost

public static java.lang.String getUserFromHost(java.lang.String host)
                                        throws java.sql.SQLException

DOCUMENT ME!

Parameters:

host - DOCUMENT ME!

Returns:

null if user is not authenticated

Throws:

java.sql.SQLException - DOCUMENT ME!

createAccount

public static boolean createAccount(java.lang.String userName,
                                    java.lang.String host,
                                    java.lang.String password,
                                    java.lang.String email,
                                    long time)
                             throws java.sql.SQLException

Attempt to create a new account with a unique userName in the users table. Before calling, validate the user doesn't already exist by calling doesUserExist(userName) or getUserPassword(userName). This method doesn't verify that the user is a unique new user before creating the record.

Parameters:

userName - New user name (nickname) to create

host - Client hostname or IP requesting new account

password - New user's initial password

email - Optional email address to contact this user

time - Created-at time, same format as System.currentTimeMillis() and Date.Date(long)

Returns:

true if the DB connection is open and the account was created, false if no database is currently connected

Throws:

java.sql.SQLException - if any unexpected database problem occurs

recordLogin

public static boolean recordLogin(java.lang.String userName,
                                  java.lang.String host,
                                  long time)
                           throws java.sql.SQLException

Record this user's login host and time.

Parameters:

userName - User name (nickname)

host - Login is from this client hostname or IP

time - Login time, same format as System.currentTimeMillis()

Returns:

true if the DB connection is open and the login was recorded, false if connection is closed

Throws:

java.sql.SQLException - if any unexpected database problem

updateLastlogin

public static boolean updateLastlogin(java.lang.String userName,
                                      long time)
                               throws java.sql.SQLException

DOCUMENT ME!

Parameters:

userName - DOCUMENT ME!

time - DOCUMENT ME!

Returns:

true if the save succeeded

Throws:

java.sql.SQLException - if any unexpected database problem

updateUserPassword

public static boolean updateUserPassword(java.lang.String userName,
                                         java.lang.String newPassword)
                                  throws java.lang.IllegalArgumentException,
                                         java.sql.SQLException

Update a user's password if the user is in the database.

Parameters:

userName - Username to update. Does not validate this user exists: Call doesUserExist(String) first to do so.

newPassword - New password (length can be 1 to 20)

Returns:

True if the update command succeeded, false if can't connect to db.
Note: If there is no user with userName, will nonetheless return true.

Throws:

java.lang.IllegalArgumentException - If user or password are null, or password is too short or too long

java.sql.SQLException - if an error occurs

Since:

1.1.20

saveGameScores

public static boolean saveGameScores(SOCGame ga,
                                     long gameLengthSeconds)
                              throws java.sql.SQLException

Record this game's time, players, and scores in the database.

Parameters:

ga - Game that's just completed

gameLengthSeconds - Duration of game

Returns:

true if the save succeeded

Throws:

java.sql.SQLException - DOCUMENT ME!

saveGameScores_fit6pInto4

private static void saveGameScores_fit6pInto4(SOCGame ga,
                                              java.lang.String[] names,
                                              short[] scores)

Try and fit names and scores of player 5 and/or player 6 into the 4 db slots, for backwards-compatibility. Checks ga.isSeatVacant(pn) and ga.getPlayer(pn).isRobot() for the first 4 player numbers, and copies player 5 and 6's data to those positions in names[] and scores[].

v1.1.15: Copy to vacant slots among first 4 players.

v1.1.19: Copy to vacant slots or robot slots among first 4; if human player 5 or 6 won, overwrite the lowest-scoring non-winner slot if necessary.

Parameters:

ga - Game that's over

names - Player names for player number 0-5; contents of 0-3 may be changed

scores - Player scores for player number 0-5; contents of 0-3 may be changed

Since:

1.1.15

retrieveRobotParams

public static SOCRobotParameters retrieveRobotParams(java.lang.String robotName)
                                              throws java.sql.SQLException

Get this robot's specialized parameters from the database, if it has an entry there.

Parameters:

robotName - Name of robot for db lookup

Returns:

null if robotName not in database, or if db is empty and robotparams table doesn't exist

Throws:

java.sql.SQLException - if unexpected problem retrieving the params

countUsers

public static int countUsers()
                      throws java.sql.SQLException

Count the number of users, if any, currently in the users table.

Returns:

User count, or -1 if not connected.

Throws:

java.sql.SQLException - if unexpected problem counting the users

Since:

1.1.19

doesTableColumnExist

public static boolean doesTableColumnExist(java.lang.String tabname,
                                           java.lang.String colname)
                                    throws java.lang.IllegalStateException

Query to see if a column exists in a table. Any exception is caught here and returns false.

Parameters:

tabname - Table name to check colname within; case-sensitive in some db types

colname - Column name to check; case-sensitive in some db types. The jsettlers standard is to always use lowercase names when creating tables and columns.

Returns:

true if column exists in the current connection's database

Throws:

java.lang.IllegalStateException - If not connected and if checkConnection() fails

Since:

1.1.14

cleanup

public static void cleanup(boolean isForShutdown)
                    throws java.sql.SQLException

Close out and shut down the database connection.

Parameters:

isForShutdown - If true, set connection = null so we won't try to reconnect later.

Throws:

java.sql.SQLException

dispResultSet

static void dispResultSet(java.sql.ResultSet rs)
                   throws java.sql.SQLException

Throws:

java.sql.SQLException