Posts Tagged ‘mysql’

Unity project – what to do next, part 1

August 31, 2014

A while ago, I had a bit of a think about what I should do next, subsequent to completion of my initial foray into using mysql and a smartfoxserver extension to handle persistence in a unity game world.  So in this post I will outline these ideas, however, this is not what I intend to focus on next in my project based on my current thinking.  That will instead be the focus of part 2.

Database considerations – inventory, equipment, etc.

Certainly it would be good to implement an inventory table in the database and an inventory manipulation interface in the game client.  Then, once this was done, I would like to create an “equipped” table, and allow a player to equip items, and for the state of what is equipped and what is not to persist.  Furthermore, I would like the appearance of various items to be displayed on the character, and for this to be transmitted to other players in the game via the smartfoxserver extension, so that the appearance of the player to themselves, and to others, was consistent with their current equipped / non equipped status.I think this would be a good next step to take this game project, albeit a rather conventional one.  Doubtless later on I will be able to do certain things that are a bit more surreal and slightly off the beaten track.

In terms of obtaining models, etc, to use as inventory, I learned from this video that there is a site, turbosquid, that specialises in 3D models, and many of the resources there are free.  So this is an additional resource of items in addition to the Unity asset store that I could use.

Character customisation / appearance of models

In terms of character customisation, there is already a certain amount of information and discussion on the web regarding how to modify the mesh of a character and swap in / out various items / armours etc, rather than clipping them all on the same model and merely hiding the ones not in use.  It is possible to do this, of course, but is not recommended – in an environment where one has say even a moderate amount of players running around, a game world will take a considerable performance hit.

In particular, there are these discussion threads, all on the Unity forum message boards:

In particular, the last thread points to an out of the box character customisation system for Unity, albeit for Unity Pro, which is by no means a cheap or ready to hand solution.  But nonetheless interesting that the solution exists – maybe in a year or two, Unity free might support something like this?

In terms of criticality of getting something like this working, though, although fascinating and definitely somewhere I’d like to get to eventually, this is not really what I’d like to target next.

Interface and gameplay

The third subfocus I had was to start working on the user interface, as well as gameplay, in particular:

  • health bars
  • skill bars / skill & spell books
  • equipment and character equipment slots
  • icons for skills and equipment, and empty character equipment slots
  • a skill system for skills, spells, etc
  • an action system for combat / player movement
  • better avatars / monsters
  • a minimap
  • gameplay
  • access to local events only on the client – though the server should keep track of all events (albeit, as representative data, of course)

For all of these, save the last two (the second last which is contingent on sensible synthesis of many of the rest), I identified assets on the Unity Asset store I could (and can) use / adapt to my purposes.  I’ve also found another site that sells quite interesting character and monster models (including a dragon!), which has also piqued my interest .  This will probably be a good next step.  Furthermore, I could in particular work to tie in of all these features to

  • a sensible database schema and messaging system, both in the local game client and on the server ( I already have solutions that exist for this and work quite well – C# messenger extended locally and smartfox for the client server messaging)
  • full multiplayer functionality

Abstract / surreal environments and game logic

The fourth idea that I thought would be interesting to focus on was incorporating a heavy surreal element into what I am trying to build.  This is something that I think is good to aim for in games, since it is in such that there is this tremendous opportunity to suspend the laws of reality and physics as we understand them, and flip them on their head.

Gameplay is also quite important, so I also had in mind looking into combat (magic & melee).

And then, consequent to this step, and prior to doing advanced project work on meshes / customisation of characters, I thought it would probably also be good to focus more on multiplayer functionality, eg, grouping, chat.  And maybe minigames, as well as some sort of quest system.  This is probably where I’d like to start diverging from the standard, and start exploring a somewhat more abstract and idiosyncratic game world and game logic – like Another World, or Abe’s Oddysee / Oddworld, or even, more prosaically, the Link games / Super Mario World universe.  Something a bit unusual.  I’d also like the gameplay to allow for a certain degree of freedom in terms of actions / animations, eg, dodge and roll, etc.

Artificial intelligence frameworks and embodiment

The final discarded item in the string of ideas I thought would be interesting to consider (and which I may eventually return to) is artificial intelligence.

In particular, I’d be very interested at some point starting to look into using OpenCog for embodiment of intelligent agents in a Unity game world, and somehow also incorporating that into the game.  This would be an extremely interesting thing to do – not least of which is that it seems that the community team behind that project seems to be starting to get some traction with their ideas.  Certainly at the very least I’m interested to see how things progress on that front over the next few years – as, if their current roadmap is even to be half believed, there could well be some fairly interesting behaviours one could instantiate in agents within a game world circa 2015+ or so, through use of the OpenCog framework.   Something that would be worth doing by someone on that project in particular might be to write tests; certainly, if I had unlimited amounts of time and energy I would do so myself, but I consider this extremely unlikely at the moment given my current priorities.

Experiences in deployment of my Unity application to Amazon EC2

June 22, 2014

In this post I will document my experiences of attempting to deploy my Unity application to the cloud.

First of all, I decided that I would use RightScale, and Amazon EC2, as I had succeeded in using this combination in the past to deploy smartfoxserver2x-Unity prototypes.  Of course, in this instance, there was a new dimension – interaction with the database.

In the end, I adapted the smartfoxserver template by adding a couple of my own rightscripts.  In particular, I was interested in doing the following things on boot of the server in addition to installation of the SmartFoxServer2X instance:

  • download extension jar files from dropbox
  • download a shell script from dropbox
  • download a mysql representation of my database from dropbox
  • install mysql server 5.5 and start the server
  • load the database file into an appropriate table
  • editing the log4j.properties file for smartfoxserver to allow trace logging (via log4j)
  • editing the xml document for the appropriate room to reflect desired configuration options for the extension jar file (optional, see below)

And finally

  • an operational rightscript that would redownload the database and the extension jar file to the correct location
  • an operational rightscript that would copy the server logs to my dropbox (wput)

Admittedly, the first three are not hyper-secure in terms of data-security, but as I am primarily interested in just getting things to work, and relatively cheaply, this seemed like a good compromise – particularly as for a t1.micro server (that I eventually plan to use, in lieu of an m1.small) – the only way to configure the thing is on boot.  I eventually opted for a RightImage that was an Ubuntu 12.04 machine.

Additional things I needed to do involved:

  • configuration of the smartfoxserver admin to enable the extension jar file
  • management of the appropriate security groups to include ports for MySQL

Eventually I managed to get all of this to work (after much in the way of abortive attempts with seeking to jump straight past MySQL 5.5 to upgrade to MySQL 5.6 which I did not need)*, but still was not able to validate / log into the application. This led me to investigate using log4j within my extension jars to capture / trace behaviour at that end – however it turns out that even this was not necessary, since the server should still have been logging what was going on with the program.

*As a minor aside, MySQL is quite easy to install – indeed, I found that the following was sufficient for my purposes:

export DEBIAN_FRONTEND=noninteractive
apt-get -q -y install mysql-server

At this point, somewhat dissuaded by an apparent lack of progress, I decided to focus on other things for a number of months.  Finally I returned to the problem of cloud deployment of this project relatively recently, and finished addressing the remaining roadblocks.  Essentially the key things still left unfinished were:

  • The fact that the smartfoxserver version was out of date.  I fixed this by altering the rightscale scripts that initially performed a wget on a file in Amazon S3 to a zip file in my dropbox, which I then instructed the server on boot to unpack.
  • UDP configuration in SFS2X/config/server.xml.  It was necessary to provide the server with access not only to TCP but also to the UDP protocol.  I had previously opened up the ports with an appropriate security group but for some reason I hadn’t arranged for the server.xml file to be configured correctly.
  • Basicexamples.zone.xml.  Running locally, I was able to configure this without hazard from the admin page, but due to permissions on the virtual machine this didn’t work properly in the cloud.  I managed to get around this by editing the file manually from the terminal (sudo pico).  In particular what needed to be modified was:
    • Setting the custom login flag to true
    • Adding a reference to the appropriate extension jar class
  • Capitalisation in one of the scripts for creating a room leading to the server not being able to recognise the room extension – I think this was either an operating system thing (less brittle on Mac than Ubuntu), or it might have been rendered less fragile in SFS2X 2.8 (the one I can run locally) but in 2.7 I needed to be more careful.  Regardless, changing the name of the configuration parameter from lower to upper case fixed this issue.

Fixing all these items had the net consequence that, for one thing, I was finally able to see my print / debug statements from my jar files in SFS2X/logs/smartfox.log while the server was running.

More importantly, the end result of all this was that I was able to log in into the world and obtain similar location persistence as I did here.  In other words, I was able to move in the world, log out, and then log back into the world at the same spot, but rather than things running locally, I was connecting from my client to a server and database running on Amazon EC2.  Neat.

__________

So that’s my report as to my experiences with Amazon EC2.  It took a few months, but I got there in the end.

In terms of next steps:

I’ve been giving some thought to how to keep track of the movements of creatures (eg, mobs and npcs) as well as persistent world events (such as weather, day / night cycle) in the application.  This is an issue because although I have managed to obtain persistence of player characteristics (eg, potentially level, stats, equipment, friends list, and, of course, what I have implemented so far, namely position and rotation information) if two players log in and run all the creatures / weather /etc on their clients, then they will have different views of the environment than each other.

To get around this, one would need to have I suppose what might be aptly named as an instance of the game running on the server, with say a vector of transforms / spawn points representing creatures (as opposed to a singleton transform & spawn point for each player), and timers for the day / night cycle, etc, all sent to the server program, which in turn would synchronise with the database and any other player clients connected.  There is really no getting around the need for a separate client running on the server, since creature movements would be too complex to model properly if merely say updated in a java program, for instance.

However, more in terms of a more concrete roadmap and action plan, I am fairly interested in refactoring this project in preparation for Unity5, as then perhaps I might be able to take advantage of Unet, a perhaps more sensible networking framework to use than SmartFox, if I am going to be developing in Unity.  It is also important that I find a good way to simplify my project layout – although I finally have the thing under version control, the project structure is a mess and contains a great deal of assets that I don’t need or am not using, or are not in a particularly sensible structure.  One of the things that I’d like to do, for instance, which sadly is not the case now, is to have a clear separation between source assets and project files, in particular, scripts.

Furthermore, I have been thinking a bit more in terms of a general roadmap going forward.  I’ve decided that aiming to empower players to create dungeons, etc, in a way that is easy to put something together rapidly, might be a good way to go.  I know that Neverwinter Foundry already does this sort of thing, but the angle that I had in mind was to go for a more of a 3D version of Roll20 – where players could have tools / templates to be empowered to create worlds, and run dungeons and dragons sessions in such mockups.  Then the emphasis / focus would be more on providing tools for a good dungeons and dragons experience, rather than so much as building a complicated combat, leveling, and itemisation system.  In this way, I would seek a slightly different path than the one trodden by many of the more formulaic hack’n’slash MMORPGs that are all over the place these days.  Less action, more emphasis on old school role playing.

Anyway, I guess it is something to think about.

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

January 24, 2014

(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]”.

MySQL, Unity, and SmartFoxServer – implementing the functionality

January 9, 2014

(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.

MySQL, Unity, and SmartFoxServer – further deliberations

December 6, 2013

(In this first of a four part series of posts I will document my endeavours in getting Unity and MySQL to speak to each other through a SFS2X extension.)

Hi folks,

So, finally after much ado, I’ve got around to applying source control to my Unity project, for both the game client and also the game server (using SFS2X) as per this tutorial here.  I also discovered that I hadn’t yet installed MySQL yet, only the workbench.  That was easy to fix though, following this tutorial here.

I was quite excited to gather that SFS2X version 2.8 was recently released, and it now has not only better support for MMO type games, but also better support for writing data from Unity to a database – both goals that I am interested in achieving with my project.  One of the new features is the introduction of an area of interest (AoI), wherein the server will only update objects / send messages about the game world to a client that are within that client’s AoI.  See here.  There is also the concept of “MMOItems” that is introduced, which seems like a neat way of handling changes to the world state triggered by players that are outside a client’s AoI.

To recap my general first “hello MySQL-Unity-SFS2X” objective, I wish to store a single row of data for a given user into the database, with columns Username, Password, x, y, z position, and x, y, z rotation – so eight pieces of information in total.  I then would like this to be updated every time the user logs out, and read every time the user logs in.  In this way I should have game functionality that allows a user to re-enter the world in the same position they left it – quite a handy feature to have.

Naturally, this would allow me to springboard into more complex features later on in the development process.

So upon completion of the first part of the MySQL Server installation tutorial above, I linked MySQL Workbench to my database, and created a schema “rpg_mmo_smartfox” in same.  Consequent to this I created a table with the following columns (note: I changed VARCHAR(45) to DECIMAL(10,0) subsequent to this screenshot):

SmartFoxServer RPG MMO first table

I then inserted the following row into said table:

Naturally the x, y, and z position is the most important aspect of the program to get right.  On examination of the code on the server, I obtained the correct starting location / spawn point:

private static Transform[] getSpawnPointsRPG() {
Transform[] spawnPoints = new Transform[3];
spawnPoints[0] = new Transform(850, 10, 1054, 0, 0, 0);
return spawnPoints;
}

So the location of the initial spawn point in the game world is 850, 10, 1054, with rotation information 0, 0, 0. Therefore it seems logical to use that as my initial row. A simple modification of my table, and I am now ready to start trying to learn how to connect to the mysql database ingame and update rows on logout, and read rows on login.

I suppose more generally it would be better to have 6 extra columns, too, for the player spawn location (eg, the concept of “hearthstone point” in a popular mmo that some might be familiar with).  So on some further reflection, I decided ultimately to have 12 columns in total for position information: player last seen location, and current spawn location.  I set the new columns to have the same set of values as the existing columns: I would like on creation, a player to have the same last seen position as their spawn location.

So then the idea would be that the spawn location would have to be changed by a player deliberation, whereas the x,y,z position and rotation information would be more dynamic.  And the spawn x, y, z location and rotation information would be used by the game if the player needs to be respawned.

Huzzah! In this era of big data, I have now done my part to make that data just a little bit bigger.

Ideas as to things I should probably look into doing next

May 27, 2013

Hi folks,

In addition to some of the things mentioned in the last post, I’ve had a bit of a further play, and have identified to a better degree as to what I would like to try to implement next.

Namely, I have been having a look into MecAnim to a greater or lesser extent – in particular finding this tutorial quite useful.  I’ve since accumulated a bit of a library of basic animations, and a few models, old and new.  In addition to this, I’ve been looking into patrol pathing for the creatures in question, sandboxing this work within a new scene.

Essentially what I would like to achieve is to have a group of friendlies – guards, on a set patrol path, and a group of enemies – skeletons, also on a set patrol path (inspired by this resource). When the two come within sight radius of each other, I want them to close for melee combat.  Upon attack, I would like the other creature to sense this and also join combat.  Creatures will have to face in the correct direction and be within a certain range for combat to be permissible.  In addition, I would like each creature to have a set number of hit points.  When this reaches zero, I would like an appropriate animation to play – then a moderate delay prior to destroying the prefab.  Then I would like a replacement enemy / guard to spawn and start walking along the original patrol path until it sees an enemy / guard, then charges to initiate combat, etc.

In terms of animations, I would like a walking animation to play while the creatures are walking, maybe a pause and idle animation at each waypoint, or certain specific waypoints, and if an enemy comes into sight radius / arc I would like the model to charge until within melee distance – at which point it is to stop and play an attack animation.  For a more convincing experience, I might also like to build in a “morale” stat, which determines at what health % the enemy / guard is likely to break and run in a random direction, at reduced speed.

I would also like the player to be able to join combat on one side or the other, most likely in favour of the guards.

For a bonus feature, I would like to have a button on the player HUD that allowed the following effect to occur – random spawning of large boulders falling from the sky, at say 5 or 6 (or maybe 10?) fixed and predetermined spawn points for the purpose.  ie so that at every predetermined time interval (allowing for delta time so that the implementation is hardware agnostic), a random number (say 1 to 3) spawn points are randomly selected.  Prior to impact I would like there to be an obvious red glowing marker at the location that the boulder will fall.  Then I would like the boulder to spawn and fall at some predetermined speed, and autocrush any avatars beneath, forcing respawn.  Subsequent to impact I would like the boulder to decay after some period of time, maybe with a lifetime of 1 minute  (In the intervening time I would like it to be treated as a wall for pathing purposes, as according to the particular AI script I will be using).

I would then be interested in implementing a dive and roll animation into the player controls, and possibly introduce a stat “reactions”/”reflex” to guards / skeletons, which make it more likely that they will detect imminent impact and also dive out of the way, even if they are in combat.  I would also like to be able to switch this effect off from the player HUD at will.

Later, time and resources permitting, ideally I would like to introduce missile combat animations (bow and arrow, and/or crossbow, sling, etc), and magic combat animations, to allow for guard / skeleton variants that can engage in combat at range.

So that’s my animation plans.  For the time being, as mentioned above, I intend to sandbox this activity, since converting the work to something that is synchronised in a SmartFoxServer might make things harder to build initially; I can do the legwork for that later.

The second thing I am interested in doing, and will probably take much longer to figure out, is to determine how to integrate a MySQL database with a SmartFoxServer application.  The ultimate goal of this will be to implement an architecture wherein I can store player specific login names, passwords (so, for instance if this was a licensed game, they would need to transact, or at least register to obtain a name and password), together with a table containing the set of all that player’s characters and their names, together with a table that, for each character name (uniquely specified for the server (for chat considerations)), contains the information as to health, stats, and inventory.  Inventory would be a string that contained a list of words, eg, potion, wand, leather armour, broadsword, that would presumably be read by the SFS extension jar file and allow the server to transmit to the client the data that relates to the items they have.  That sort of idea, anyhow – so that the data, related to persistence of character state, for the collection of characters each player has, is stored on the server.

So that’s the goal.  I do not expect implementation to be easy, particularly since documentation seems relatively scarce regarding such.  However, this post looks like a good place to start on said matter.  In particular, it is indicated how one might set up a custom SmartFoxServer via the RightScale platform if one wanted to deploy the server in the cloud, say on Amazon EC2, via the 12th point of the recipe (one can directly modify files while booting up/setting up a virtual machine on RightScale).