MySQL, Unity, and SmartFoxServer – implementing the functionality

(In this second of a four part series of posts I will aim to continue to develop my remarks on the foundations of simple database persistence in a Unity MMO type game.)

Hello again,

If one recalls where I left off last time, I had placed both client and server side code under subversion, and also created a simple database, with a single row of data.

The next step is to do something like what is mentioned in this forum post, which is targeted to SFS2X and MySQL specifically.  More broadly, the specifics of this post seem also like a good place to start trying to learn how to interact between the SFS2X extension jar and my MySQL database, or, also, perhaps, this post.

Subsequent to completion of this task, in terms of tidying up and improving upon my implementation, I think it might also be good practice to assign another user other than root to access to the database I created; in practice I’d probably not want to have the root user connecting all the time!

First, however, there is the matter of setting up elementary communication between an sfs extension, running on the server, and the client program written in unity.

In such vein, I followed the instructions in the tutorial by Lars Vogel (linked above), namely:

  • I created a new schema “feedback” in my default instance and a table within that schema, “comments” with the specified data structure
  • I copied the code in Lars’ tutorial to a new eclipse project
  • I downloaded the platform independent version of the connector-J driver from the mysql site, and linked it as an extension jar to the project
  • projectstructure
  • I then adapted the code but so that it would work with the location specific information in the table I created in Part I.
public void readDataBase() throws Exception {
try {
// This will load the MySQL driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");
// Setup the connection with the DB
connect = DriverManager
.getConnection("jdbc:mysql://localhost/rpg_mmo_smartfox?"
+ "user=root&password=");

// Statements allow to issue SQL queries to the database
statement = connect.createStatement();
// Result set get the result of the SQL query
resultSet = statement
.executeQuery("select * from rpg_mmo_smartfox.player");
writeResultSet(resultSet);

// PreparedStatements can use variables and are more efficient
preparedStatement = connect
.prepareStatement("UPDATE rpg_mmo_smartfox.player SET x_position = ?," +
"y_position = ?, z_position = ? WHERE idplayer=1;");
// "myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
// Parameters start with 1
//preparedStatement.setString(1, "0");
//preparedStatement.setString(2, "0");
//preparedStatement.setString(3, "0");
preparedStatement.setString(1, "850");
preparedStatement.setString(2, "10");
preparedStatement.setString(3, "1054");
preparedStatement.executeUpdate();

preparedStatement = connect
.prepareStatement("SELECT * from rpg_mmo_smartfox.player");
resultSet = preparedStatement.executeQuery();
writeResultSet(resultSet);

} catch (Exception e) {
throw e;
} finally {
close();
}

}

Everything worked perfectly well; I found that I was able to edit and update information in my database perfectly well from this script.

So, with that done, I saw the next step as to figure out how to get a username and password type communication happening between Unity, SmartFoxServer, and mysql.  There is a post here that I found useful in this regard.  Further information regarding programming client side is here (search for OnExtensionRequest); server side programming is covered here.

First of all, as I discovered, it is necessary to set the room in question on the server for SmartFoxServer to use a custom login – so that the extension handler is used rather than the system handler.

Consequently, there is a need to write a secondary extension jar for handling the custom login – on top of the existing extension jar for the game itself (there may well be a way of having both in one jar file, but this has stumped me for the time being).

To set things up, one needs to add an event listener in Init() for the server extension:

public class RpgLoginExtension extends SFSExtension {

@Override
public void init() {

// Subscribing the request handlers
addEventHandler(SFSEventType.USER_LOGIN, LoginHandler.class);
//might not be necessary but maybe necessary when deploy to server online and
//room does not already exist
//addEventHandler(SFSEventType.ROOM_ADDED, RoomAddHandler.class);

trace("RPG Login extension initialized");
}

}

Next, one needs to write a LoginHandler script.  Note: this discussion proved quite informative regarding security for a custom login.

package dk.spinningcat.rpg.login.handlers;

//import dk.spinningcat.fps.RpgExtension;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Statement;

import com.smartfoxserver.bitswarm.sessions.ISession;
import com.smartfoxserver.v2.entities.data.SFSArray;
import com.smartfoxserver.v2.core.SFSEventParam;
import com.smartfoxserver.v2.core.ISFSEvent;
import com.smartfoxserver.v2.exceptions.SFSException;
import com.smartfoxserver.v2.exceptions.SFSLoginException;
import com.smartfoxserver.v2.exceptions.SFSErrorData;
import com.smartfoxserver.v2.exceptions.SFSErrorCode;
import com.smartfoxserver.v2.extensions.BaseServerEventHandler;
import com.smartfoxserver.v2.extensions.ExtensionLogLevel;

import java.util.Date;

public class LoginHandler extends BaseServerEventHandler {

public void handleServerEvent(ISFSEvent event) throws SFSException
{
String username = (String) event.getParameter(SFSEventParam.LOGIN_NAME);
String password = (String) event.getParameter(SFSEventParam.LOGIN_PASSWORD);

ISession session = (ISession)event.getParameter(SFSEventParam.SESSION);

//get crypted password from client login request
String cryptedPass = (String) event.getParameter(SFSEventParam.LOGIN_PASSWORD);

//get plain password from database
//String plainPass = SomeDBManager.getPlainPassword();
String plainPass = null;
try {
plainPass = getPasswordFromDataBase(username);
} catch (Exception e) {
e.printStackTrace();
}

trace("Plainpass is: " + plainPass);

//after compare them via api:
if(!getApi().checkSecurePassword(session, plainPass, cryptedPass))
{
//trace("passwords do not match");
SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
data.addParameter(username);
throw new SFSLoginException("Login failed for user: "  + username, data);
}
else
{
trace("passwords match! hooray!");
}

trace("username and password: " + username + "," + plainPass);
trace("Login successful, joining room!");
}

private Connection connect = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;

public String getPasswordFromDataBase(String username) throws Exception{
try {
// This will load the MySQL driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");
// Setup the connection with the DB
connect = DriverManager
.getConnection("jdbc:mysql://localhost/rpg_mmo_smartfox?"
+ "user=root&password=");

// Statements allow to issue SQL queries to the database
statement = connect.createStatement();
// Result set get the result of the SQL query
resultSet = statement
.executeQuery("select password from rpg_mmo_smartfox.player where name = '" + username + "';");
resultSet.next();
String password = resultSet.getString("password");

return password;

} catch (Exception e) {
throw e;
} finally {
close();
}

}

// You need to close the resultSet
private void close() {
try {
if (resultSet != null) {
resultSet.close();
}

if (statement != null) {
statement.close();
}

if (connect != null) {
connect.close();
}
} catch (Exception e) {

}
}

}

Then, subsequent to compiling the jar (NB: it must have a name of the form [yourfavouriteprefix]Extension.jar) one needs to log in to the server (localhost:8080/admin, user/pass “sfsadmin”), then click on the modules, goto the zone configurator, and make sure that custom login is checked.  Consequent to this, select zone extensions, and select [yourfavouriteprefix] from the allowable extensions.  You will also need to select the main class of this extension (in the above example, RpgLoginExtension).  More on this process can be found here.

selectextension

(An alternative approach to all this might be to directly modify the smartfoxserver xml files, but I’m not sure where to copy files to update the version of the login extension jar that the server sees as the ‘current’ jar).

Also, one needs to copy the mysql connector j jar file to the library folder of the smartfoxserver application (since otherwise the server will not see it).  An alternative approach would be to add this jar to the classpath.

Also, too, every time one recompiles the jar file, it is essential to refresh its instantiation in the sfs administration panel as above.

But after these adventures, one should finally obtain something like the following (evidently with your own custom print statements):

smartfoxlogin

Consequent to this, it is a matter of simplicity to disallow login if the username, password combination provided does not match a user in the database.

This wraps up this post.  To summarise, I have learned how to communicate between Unity, a SmartFoxServer login extension, and a mysql database, in order to validate login information.  Hence the foundation is laid for further work, namely, persisting user location in the game world – as a first step in using databases.To possibly reiterate what I’ve mentioned before, naturally one could do many other things with the techniques I’ve started to cover in this post, and will continue to cover in the next – such as inventory, experience, talents, specialisations, reputation, equipped items, friend lists, etc.  Not to mention databases for monsters, storekeepers (limited amount of items in stock, with limited respawn rate, and things like auction houses, or simply shops that keep track of what players have placed on the listings or sold.

In the penultimate part of this series for this mini-project, I will break the task into two pieces.  The first will be, building on the above, to learn how to set the player’s default starting position to the (x,y,z) coordinates in the database that are assigned to the player’s name.

Secondly, it should then be reasonably straightforward to adapt the code to allow one to persist the player logout position (by writing to the database) and then use the final step of this part to read the player’s position on login.  In this part, I might also try and see if I can set up a different server-side user for the database other than root.

Advertisements

Tags: , , , ,

One Response to “MySQL, Unity, and SmartFoxServer – implementing the functionality”

  1. MySQL, Unity, and SmartFoxServer – location persistence in the game world | Where's my hat?! Says:

    […] Random musings of a demonic entity « MySQL, Unity, and SmartFoxServer – implementing the functionality […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: