MySQL, Unity, and SmartFoxServer – location persistence in the game world

(In this penultimate of a four part series of posts I will complete my sketch on how I built a working implementation of simple database persistence in a Unity MMO type game, leaving the final post for afterthoughts and other meanderings.)

Greetings,

In this post I continue from the previous two posts:

I will first describe my attempts to read a login location for a player from the database I am using, and then I will describe my attempts to write a new login location when the player exits the game / the smartfoxserver instance is terminated.

First of all, I found that I was able to modify the standard extension jar (not the login extension jar from Part Two) with the following scripts:

package com.spinningcat.mysql.access;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.List;
import java.util.ArrayList;

public class TransformMySQL {
private Connection connect = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;

public List<Float> getTransformFromDataBase(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 x_position, y_position, z_position," +
"                x_rotation, y_rotation, z_rotation" +
" from rpg_mmo_smartfox.player where name ='" + username + "';");
resultSet.next();
String x_pos = resultSet.getString("x_position");
String y_pos = resultSet.getString("y_position");
String z_pos = resultSet.getString("z_position");
String x_rot = resultSet.getString("x_rotation");
String y_rot = resultSet.getString("y_rotation");
String z_rot = resultSet.getString("z_rotation");

float x_position = Float.parseFloat(x_pos);
float y_position = Float.parseFloat(y_pos);
float z_position = Float.parseFloat(z_pos);
float x_rotation = Float.parseFloat(x_rot);
float y_rotation = Float.parseFloat(y_rot);
float z_rotation = Float.parseFloat(z_rot);

List<Float> result = new ArrayList<Float>();

result.add(x_position);
result.add(y_position);
result.add(z_position);
result.add(x_rotation);
result.add(y_rotation);
result.add(z_rotation);

return result;

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

}
}

And, in the main class called on instantiation of the player in the game world:

package dk.spinningcat.fps.simulation;

import java.util.List;

import com.smartfoxserver.v2.entities.User;
import com.smartfoxserver.v2.entities.data.ISFSObject;
import com.smartfoxserver.v2.entities.data.SFSObject;
import com.spinningcat.mysql.access.*;

// Player class representing an individual soldier in the world simulation
public class CombatPlayer {

public CombatPlayer(User sfsUser) {
this.sfsUser = sfsUser;

//used to determine where to spawn the player based on their location in the database
String username = sfsUser.getName();

//extracts the player position, based on their username, from the database
TransformMySQL mysql = new TransformMySQL();
List<Float> result = null;
try{ result = mysql.getTransformFromDataBase(username);}
catch (Exception e) { e.printStackTrace();}

//sets the value of combatplayer to the corresponding position rotation information from the
//database, so that the player is spawned at said position and rotation when instantiated
this.transform = new Transform(result.get(0), result.get(1), result.get(2),
result.get(3), result.get(4), result.get(5));

//this.transform = new Transform(850, 10, 1054, 0, 0, 0);
/*this.transform = Transform.randomRPG();*/
/*this.collider = new Collider(0, 1, 0, 0.5, 2);*/
}

}

Initially, I tested this general functionality by checking that I could access passwords in the database when I fed the program a username string “bob”.  Next I checked that I could access bob’s transform and rotation information.  Finally I made a minor tweak to the above script so that the program used as username the player’s username on login.

Ultimately, these actions were successful.  The end result of these efforts is documented in the following clip, where I demonstrate for two users, “bob” and “charlie”, both with password “alice”, that their spawn locations are dependent on their transform information in the database.

This leaves as a final step in this exercise to determine how to update information in the database on logout.  Consequently the game world should then experience true persistence of player location information.

I was not able to implement this on exiting play mode of the client application, since the username information was no longer accessible to the extension and therefore the correct row in the database could not be written to.  As a quick fix / kludge, however, I determined that writing to the database each frame would do the trick for the time being.  However this is ultimately unwieldy, particularly if there were, say, 100 players accessing the server concurrently – which is possible under the permissions of the free SmartFoxServer 2X license I am using.

Consequently ultimately I will need to do one of two things – either implement a quit button, or to modify the on player exit event, to write the transform of the player (stored as a temporary variable in the CombatPlayer class on the server) to the database – this will be in part 4 of this series.

In the mean time, though, you can see for yourself the successful implementation of my original goal for this mini series in the following video:

The code required to get this to work was, on the client side:

    /// <summary>
/// Send local transform to the server on logout
public void SendTransformDatabase(NetworkTransform ntransform) {
Room room = smartFox.LastJoinedRoom;
ISFSObject data = new SFSObject();
ntransform.ToSFSObject(data);
ExtensionRequest request = new ExtensionRequest("sendTransformDatabase", data, room, true); // True flag = UDP
smartFox.Send(request);
}

and on the server side:

public class RpgExtension extends SFSExtension {

@Override
public void init() {
// Subscribing the request handlers
addRequestHandler("sendTransformDatabase", SendTransformDatabaseHandler.class);

And the handler:

package dk.spinningcat.fps.handlers;

import com.smartfoxserver.v2.entities.Room;
import com.smartfoxserver.v2.entities.User;
import com.smartfoxserver.v2.entities.data.ISFSObject;
import com.smartfoxserver.v2.entities.data.SFSObject;
import com.smartfoxserver.v2.extensions.BaseClientRequestHandler;

import dk.spinningcat.fps.simulation.Transform;
import dk.spinningcat.fps.simulation.World;
import dk.spinningcat.fps.utils.RoomHelper;
import dk.spinningcat.fps.utils.UserHelper;

import com.spinningcat.mysql.access.OnLogout;

import java.util.Date;
import java.util.List;

public class SendTransformDatabaseHandler extends BaseClientRequestHandler {

	@Override
	public void handleClientRequest(User u, ISFSObject data) {
		// The transform we received from player
		Transform receivedTransform = Transform.fromSFSObject(data);
		String username = u.getName();
		OnLogout onLogout = new OnLogout();
		try{ onLogout.updatePlayerPositionInDatabase(receivedTransform, username); }
		catch (Exception e) { System.out.println("Username and password combination incorrect.");
				//e.printStackTrace();
		}

	}

}

And the java mysql code:

package com.spinningcat.mysql.access;

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

import dk.spinningcat.fps.simulation.Transform;

public class OnLogout {

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

public void updatePlayerPositionInDatabase(Transform transform, 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();

Double x_pos = Math.floor(transform.getX());
Double y_pos = Math.floor(transform.getY());
Double z_pos = Math.floor(transform.getZ());
//System.out.println(transform.getRotx() + "," + transform.getRoty() + "," + transform.getRotz());
Double x_rot = Math.floor(transform.getRotx());
Double y_rot = Math.floor(transform.getRoty());
Double z_rot = Math.floor(transform.getRotz());

// PreparedStatements can use variables and are more efficient
preparedStatement = connect
.prepareStatement("UPDATE rpg_mmo_smartfox.player SET x_position = ?," +
"y_position = ?, z_position = ?," +
"x_rotation = ?, y_rotation = ?, z_rotation = ?" +
"WHERE name = '" + username + "';");

preparedStatement.setDouble(1, x_pos);
preparedStatement.setDouble(2, y_pos + 0.5);
preparedStatement.setDouble(3, z_pos);
preparedStatement.setDouble(4, x_rot);
preparedStatement.setDouble(5, y_rot);
preparedStatement.setDouble(6, z_rot);
preparedStatement.executeUpdate();

} 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) {

}
}
}

Finally, some more code on the client side to instantiate the player rotation properly:

using System;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;

// Spawns player and items objects, stores them in collections and provides access to them
public class PlayerManager : MonoBehaviour {

public GameObject playerPrefab;

private GameObject playerObj;

private static PlayerManager instance;
public static PlayerManager Instance {
get {
return instance;
}
}

public GameObject GetPlayerObject() {
return playerObj;
}

public void SpawnPlayer(NetworkTransform ntransform, string name, int score) {
//instantiate the player and move the camera
playerObj = GameObject.Instantiate(playerPrefab) as GameObject;

//adjust the camera to point in the right direction
Camera.main.transform.eulerAngles = ntransform.AngleRotation;

//edit the player position and euler angle coordinates
playerObj.transform.position = ntransform.Position;
playerObj.transform.eulerAngles = ntransform.AngleRotation;

playerObj.SendMessage("StartSendTransform");
}

There were another couple of client side fixes required.  One was to do with the third person controller not functioning correctly – essentially it did not register on spawn that the player was always falling slightly (I needed to delta the y coordinate that player position was stored / persisted at so at respawn the player would not fall through the world).  Anyway, this led to a puzzling difficulty in persisting euler angles of rotation for the player; finally, after debugging by force setting the height of the player to 400 on spawn, I figured out that it was something to do with the OnGrounded() function call in the third person controller.  Long story short, it was an easy one line code fix job, and it would serve no real educational purpose to provide the code itself.

Hence this concludes the bulk of this series of posts.  In the fourth and final post, I will perform some minor fixes.  One will be to delete the waypoints that the guard uses to patrol; for some reason these spheres are not disappearing in my mini multiplayer rpg, though they did disappear in my guards vs skeletons example!  So some investigation is required.

The second fix, mentioned already, will be to either implement a quit feature, or alter the OnUserGoneHandler to write the last known transform of the player to the database; the latter is simpler to implement and therefore is the preferred solution, if possible.    In particular, I will only be writing to the database once on exiting the game, rather than writing certain information to the database constantly – something which, I am sure, would put severe load on the database were there a large number of users accessing the server.  Furthermore, since, ultimately, I am potentially interested in running this server on a small cloud instance – whether that be Amazon EC2 or whatever else – it is unlikely that such is likely to have huge amounts of computational resources to spare.

Finally, I am interested in investigating stored procedures in MySQL, and calling these from the server when certain events occur.  Stored procedures offer various advantages, many of which are documented here.  The main ones of interest to me, however, are security and performance.  Security, since stored procedures allow one to more finely control who has access to do what in the database.  Performance, since a stored procedure lives in the database, and hundreds of lines of SQL code can be called by a short command of the form “Run [stored procedure name] with [specified variable values]”.

Advertisements

Tags: , , ,

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: