GameDesign

!!! Database Tables and Relationships Last updated: 2003/10/07

The database table relationship schema is: <verbatim> Tables:

---

Table PLAYER

 {
 PK(username)
 password
 }

Table CHARACTERS

 {
 PK(character)
 content
 }

Table LOGIN_EVENT

 {
 PK(id)
 address
 timedate
 result
 }

Table STATISTICS

 (
 PK(timedate)

 bytes_send
 bytes_recv

 players_login
 players_logout
 players_timeout
 players_online
 )

Table RPOBJECT

 (
 PK(id)
 slot_id
 )

Table RPATTRIBUTE

 (
 PK(object_id)
 PK(name)
 value
 )

Table RPSLOT

 (
 object_id
 name
 PK(slot_id)
 )

Relationships:

---

Relationship PLAYER_CHARACTERS

 {
 PK(player_username)
 PK(characters_character)
 }

Relationship PLAYER_LOGIN_EVENT

 {
 PK(player_username)
 PK(login_event_id)
 }

</verbatim> So we can translate this to SQL easily and we should create the following SQL Queries: <verbatim> CREATE TABLE player

 (
 id BIGINT PRIMARY KEY NOT NULL,
 username VARCHAR(30) NOT NULL,
 password VARCHAR(30) NOT NULL
 );

CREATE TABLE characters

 (
 player_id BIGINT NOT NULL,
 charname VARCHAR(30) NOT NULL,
 contents VARCHAR(4096)

 PRIMARY KEY(player_id,character)
 );

CREATE TABLE loginEvent

 (
 player_id BIGINT NOT NULL,
 address VARCHAR(20),
 timedate TIMEDATE,
 result TINYINT
 );

CREATE TABLE statistics

 (
 timedate TIMESTAMP,

 bytes_send INTEGER,
 bytes_recv INTEGER,

 players_login INTEGER,
 players_logout INTEGER,
 players_timeout INTEGER,
 players_online INTEGER,

 PRIMARY KEY(timedate)
 );

CREATE TABLE rpobject

 (
 id INTEGER NOT NULL PRIMARY KEY,
 slot_id INTEGER
 );

CREATE TABLE rpattribute

 (
 object_id INTEGER NOT NULL,
 name VARCHAR(64) NOT NULL,
 value VARCHAR(255),
 PRIMARY KEY(object_id,name)
 );

CREATE TABLE rpslot

 (
 object_id INTEGER NOT NULL,
 name VARCHAR(64) NOT NULL,
 slot_id INTEGER AUTO_INCREMENT NOT NULL,

 PRIMARY KEY(slot_id)
 );

</verbatim>

!!! JDBC Database HOWTO Last updated: 2003/10/23

JDBC technology is an API that lets you access virtually any tabular data source from the Java programming language. It provides cross-DBMS connectivity to a wide range of SQL databases, and now, with the new JDBC API, it also provides access to other tabular data sources, such as spreadsheets or flat files.

JDBCPlayerDatabase is anyway not database independent; on the Player table we are using AUTOINCREMENT that is a unique keyword of MySQL that is not part of the SQL standard.

You need to download MySQL Connector/J in order to get it to run. http://www.mysql.com/downloads/api-jdbc-stable.html.

To configure Marauroa to work with a JDBC source we need to modify the configuration of the JDBC Connection.

So open marauroad.ini file and edit the next fields <verbatim> marauroa_DATABASE=JDBCPlayerDatabase

jdbc_class=com.mysql.jdbc.Driver jdbc_url=jdbc:mysql://localhost/marauroa jdbc_user=marauroa_dbuser jdbc_pwd=marauroa_dbpwd </verbatim> jdbc_class is the field that says what Driver to use. Please refer to your software manual to see the multiple options.

jdbc_url points to the type and source of the information, for MySQL the string must be as follow:

jdbc:mysql://[:]/

jdbc_user is the username for the database and jdbc_pwd is the password for that username in the database.

Then simply save the changes and ready.

Before using the application with the database, you need to create the database itself. So in case of MySQL just open MySQL and write:

<verbatim> create database marauroa; grant all on marauroa.* to marauroa_dbuser@localhost identified by 'marauroa_dbpwd'; </verbatim>

The rest of code is handled by the server itself, and will create the tables if they don't exits.

!!! Player Container Explained Last updated: 2003/10/23

~PlayerContainer is the data structure that contains all of the needed info about the players to keep the game running.

It consists of a list of ~RuntimePlayerEntry objects, and is heavily linked with the ~PlayerDatabase, so we can hide the complexity to ~GameManager. By making ~PlayerDatabase hidden by ~PlayerContainer we achieve the illusion that managing the runtime behavior we modify automatically the permanent one.

~RuntimePlayerEntry is the structure that contains the information of the player while it is online. ~RuntimePlayerEntry contains: clientid Clientid is the field in charge of indexing players in the server. See the document about clientid generation to understand what they are and how they are generated. source Source is the IPv4 address of the client, so that we can determine if the message is really coming from client or another person trying to impersonate it. timestamp Timestamp is used to determine if a client has timed out and as such, it is only wasting resources on the server. As you know, UDP is not a delivery-guaranteed protocol, so we need to check ourselves for dead clients. Take care that it only indicates that the player timed out, it doesn't apply any kind of measures over them. username Username is filled in at runtime with a Login event so that we are able to use the database from ~PlayerContainer, This way by knowing the clientid we can also know the username. choosenCharacter choosenCharacter is filled in at runtime with a ~ChooseCharacter event so that we are able to use the database from ~PlayerContainer, This way by knowing the clientid we can also know the choosenCharacter. state State is a number expressing the state in which the player is. There are four states: Have to login Login Complete Game begin Logout When we create the entry it is by default Have to login. Once you have logged in correctly, we change state to Login Complete, and once you have chosen a Character we change it to game begin. The logout state is trivial :)

The main idea is that some operations are only allowed in one state, so we can more easily control it with the state property. perception counter Perception counter is used for having a incremental counter of the perceptions send, so that client can see if it gets out of sync. perception Previous RPObject Perception previous RPObject is the RPObject that was sent on the last perception, so we can track changes on our RPObject without disturbing the rest of the system. timestamp of the Last time it was stored Timestamp of the Last time it was stored means the timestamp of the moment in which the object was stored at Database.

As you can see all we need to operate ~PlayerDatabase is a username and choosenCharacter. So using ~PlayerEntryContainer we can fully operate it.

!!! ClientID generation Last updated: 2003/10/23

Each client MUST have a session id to avoid another player to impersonate it. sessionid must be a short or int to make harder for an attacker to guess it.

To make it really fun, clientids are generated randomly for each player with the unique condition that two different players MUST have two different clientids. Home

!!! Synchronization between Game and RP Managers Last updated: 2003/12/06

Why bother with it? Well, imagine that a player logs out when the perception is being built, it will no longer be accessible for the RP Manager, when it really expects the object to be there. Or a removed player that is removed too by RP Manager. That is a really serious problem, as it will make the server fail.

So we need to synchronize game and RP manager.

The idea is that they request to a central mutex access to the ~PlayerEntryContainer, and that mutex is the one that decide how the access is done.

We need to differentiate between the two types of accesses, read access and write access. We can have without problems two readers accessing in parallel, but we can only have one write at the same time modifying the stuff.

Whatever action we choose in ~GameManager they are Write actions, as the modify the state of the ~PlayerContainer, but in RP we have two parts, one that build the perceptions that is read only and one that removes idle players that is write, so we must apply two different locks there. Home

!!! Persistent Objects ER Last updated: ?

use marauroa;

<verbatim> drop table rpobject; drop table rpattribute; drop table rpslot; drop table RPObjectInRPSlot;

CREATE TABLE rpattribute (

 object_id int(11) NOT NULL default '0',
 name varchar(64) NOT NULL default ,
 value varchar(255) default NULL,
 PRIMARY KEY  (object_id,name)

) TYPE=InnoDB;

CREATE TABLE rpslot (

 object_id int(11) NOT NULL default '0',
 name varchar(64) NOT NULL default ,
 slot_id int(11) NOT NULL auto_increment,
 PRIMARY KEY  (slot_id)

) TYPE=InnoDB;

CREATE TABLE rpobject (

 id int(11) NOT NULL default '0',
 slot_id int(11) default NULL,
 PRIMARY KEY  (id)

) TYPE=InnoDB;