Marauroa Database Structure: Difference between revisions

From Arianne
Jump to navigation Jump to search
← Older edit
Content deleted Content added
imported>MartinFuchs
imported>Kymara
m →The whole picture: close brackets
 
(136 intermediate revisions by 3 users not shown)
Line 1: Line 1:
{{Navigation for Marauroa Top|Internals}}
= Basic idea behind Database storage =
{{Navigation for Marauroa Developers}}
Arianne uses a database to store game state information so that the games can be truly persistent.


==Database Tables==


{{Database Access}}
This are the tables of the Stendhal database schema:
__TOC__
<pre>
Table account (InnoDB)
+----------+------------------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | MUL | NULL | auto_increment |
| username | varchar(32) | NO | PRI | | |
| password | varchar(255) | NO | | | |
| email | varchar(64) | NO | | | |
| timedate | timestamp | NO | | CURRENT_TIMESTAMP | |
| status | enum('active','inactive','banned') | NO | | active | |
+----------+------------------------------------+------+-----+-------------------+----------------+


Table banlist (InnoDB)
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| address | varchar(15) | YES | | NULL | |
| mask | varchar(15) | YES | | NULL | |
| reason | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+


This article describes the table structure of the Marauroa database. You might want to have a look at [[High Level Database Access]] which explains the high level API to access the database from your program code. The article [[Low Level Database Access]] describes how Marauroa accesses the database internally and how you can add support for your own tables.
Table characters (InnoDB)
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| player_id | int(11) | NO | | | |
| charname | varchar(32) | NO | PRI | | |
| object_id | int(11) | NO | | | |
+-----------+-------------+------+-----+---------+-------+


Table character_stats (MyISAM)
+----------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+-------+
| name | varchar(32) | NO | PRI | | |
| online | tinyint(1) | YES | | NULL | |
| admin | int(11) | YES | | 0 | |
| sentence | varchar(256) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| level | int(11) | YES | | NULL | |
| outfit | varchar(32) | YES | | NULL | |
| xp | int(11) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| married | varchar(32) | YES | | NULL | |
| atk | int(11) | YES | | NULL | |
| def | int(11) | YES | | NULL | |
| hp | int(11) | YES | | NULL | |
| karma | int(11) | YES | | NULL | |
| head | varchar(32) | YES | | NULL | |
| armor | varchar(32) | YES | | NULL | |
| lhand | varchar(32) | YES | | NULL | |
| rhand | varchar(32) | YES | | NULL | |
| legs | varchar(32) | YES | | NULL | |
| feet | varchar(32) | YES | | NULL | |
| cloak | varchar(32) | YES | | NULL | |
| timedate | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------+--------------+------+-----+-------------------+-------+


== Accounts ==
Table gameevents (InnoDB)
[[Image:Database-account-logs.png‎]]
+----------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| timedate | timestamp | NO | | CURRENT_TIMESTAMP | |
| source | varchar(64) | YES | | NULL | |
| event | varchar(64) | YES | | NULL | |
| param1 | varchar(128) | YES | | NULL | |
| param2 | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+-------------------+----------------+


Authentication information is stored in the table account. It consists of the username, the password hash, email address, and the timestamp of the account creation. Please note that for historic reasons foreign key columns pointing to the account table are not named account_id but player_id.
Table halloffame (MyISAM)
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| charname | varchar(32) | NO | | | |
| fametype | char(1) | NO | | | |
| points | int(11) | NO | | | |
+----------+-------------+------+-----+---------+----------------+


For security reason every login (successful or not) is logged in the table loginEvent with the ip-address, timestamp, and a success flag. The column service is used to tell logins from a game and a website apart. The optional column seed stores a preauthentication seed. Marauroa automatically prevents logins for some time after too many failed tries.
Table itemid (MyISAM)
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| last_id | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+


Password changes are logged in a similar way. In addition to normal loginEvents the old password hash is stored as well. This is a precaution to restore hacked accounts back to their original owner.
Table itemlog (MyISAM)
+----------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| timedate | timestamp | NO | | CURRENT_TIMESTAMP | |
| itemid | int(11) | YES | | NULL | |
| source | varchar(64) | YES | | NULL | |
| event | varchar(64) | YES | | NULL | |
| param1 | varchar(64) | YES | | NULL | |
| param2 | varchar(64) | YES | | NULL | |
| param3 | varchar(64) | YES | | NULL | |
| param4 | varchar(64) | YES | | NULL | |
+----------+-------------+------+-----+-------------------+----------------+


Note: It may be a good idea to delete old rows in this table regularly for privacy reasons.
Table loginevent (InnoDB)
+-----------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-------+
| player_id | int(11) | NO | | | |
| address | varchar(64) | YES | | NULL | |
| timedate | timestamp | NO | | CURRENT_TIMESTAMP | |
| result | tinyint(4) | YES | | NULL | |
+-----------+-------------+------+-----+-------------------+-------+


== Bans ==
Table rpobject (InnoDB)
[[Image:Database-account-bans.png‎]]
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| object_id | int(11) | NO | PRI | NULL | auto_increment |
| data | blob | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+


Unfortunately there are some unfriendly people out there that you may need to keep away. The tables accountban and banlist store such bans.
Table rpzone (InnoDB)
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| zone_id | varchar(32) | NO | PRI | | |
| data | blob | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+


The table accountban is on a per account basis. The person trying to login is displayed the reason. Account bans can expire automatically.
Table statistics (InnoDB)
+-----------------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+-------------------+-------+
| timedate | timestamp | NO | | CURRENT_TIMESTAMP | |
| bytes_send | int(11) | YES | | NULL | |
| bytes_recv | int(11) | YES | | NULL | |
| players_login | int(11) | YES | | NULL | |
| players_logout | int(11) | YES | | NULL | |
| players_timeout | int(11) | YES | | NULL | |
| players_online | int(11) | YES | | NULL | |
+-----------------+-----------+------+-----+-------------------+-------+


The table banlist stores bans based on ip-address and ip-address-ranges. The mask 255.255.255.255 donates a single ip-address.
Important tables which store original data, use the InnoDB engine. Tables with redundant data for easy access and logging use the MyISAM table type.


== RPObjects ==
[[Image:Database-rpobjects.png‎]]


Marauroa knows two types of objects which can be stored to the database: players and zones. Both objects can contain other objects: Players have items in their bags, zones can contain objects that are flagged as storable.
== JDBC Database HOWTO==
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.


Player objects are linked to their account using the table characters.
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.


Note: Currently the rpobjects (including owned slots and rpojbects in those slots) are serialized in a Blob field. In the Marauroa Version 1.x it was a relational. Unfortunately that approach was failing performance requirements (MySQL is extremely slow on delete statements). There is a [[Meta object model for relational data storage|concept]] in development that proposes an improved relational structure.
You need to download MySQL Connector/J in order to get it to run. <br>
http://www.mysql.com/downloads/api-jdbc-stable.html


== Game Logging ==
To configure Marauroa to work with a JDBC source, we need to modify the configuration of the JDBC Connection.
[[Image:Database-gamelog.png‎]]


There are two more tables used for logging:
So open the configuration file '''server.ini''' ''(or whatever you choose to name it)'' and edit the next fields
<pre>
marauroa_DATABASE=JDBCPlayerDatabase


gameEvents stores events that occur in the game world (killing monsters, trading, teleporting, etc.) with a time stamp and the player causing the event. The parameters param1 and param2 depend on the event.
jdbc_class=com.mysql.jdbc.Driver
jdbc_url=jdbc:mysql://localhost/marauroa
jdbc_user=marauroa_dbuser
jdbc_pwd=marauroa_dbpwd
</pre>


Unless disabled Marauroa logs statistical information every minute in the table statistics. This includes the network traffic data and number of players online.
* ''jdbc_class'': This field tells the engine what Driver to use for database access. Please refer to your software manual to see the multiple options.
* ''jdbc_url'': This points to the type and source of the information, for MySQL the string must be as follows:
* '''jdbc:mysql://ip:database_name/'''
* ''jdbc_user'': This is the username for the database
* ''jdbc_pwd'': This is the password for that username in the database.


== The whole picture ==
Now, simply save the changes and your configuration file is ready.


The following diagram shows the all tables used by Marauroa. Games are, however, free to add their own tables if the need arises (JMapacman and Marboard don't need additional tables but Stendhal makes heavy use of [[Stendhal Database Structure|this possibility]].)
Before using the application with the database, you need to create the database itself. So, with MySQL just run MySQL and enter:
<pre>
create database stendhal;
grant all on stendhal.* to stendhal_dbuser@localhost identified by 'stendhal_dbpwd';
</pre>


[[Image:Database-marauroa.png‎]]
The rest of code is handled by the server itself, and it will create the tables if they don't exist.


= Storing objects in the database =
Objects are stored in the database to save their state. This is a heavy (intensive) operation, so we should cache objects and store them only from time to time.


[[Category:Marauroa]]
This decision is made in RPManager. It calculates how often an object has to be stored.
{{#breadcrumbs: [[Marauroa]] | [[Navigation for Marauroa Developers|Internals]] | [[Marauroa Database Structure|Database Structure]] }}

You have to write the code to load all the database objects into RPWorld. This loading must be coded in the RPWorld class inside the onInit and onFinish methods.

== How objects are identified ==
It is important to notice that RPObject.ID is only valid while the object is online. So there is no guarantee that any new object won't have that same RPObject.ID once the previous object has been removed.

To solve this problem we store a hidden attribute in each object called '''#db_id''' which gives a unique id to each object regardless of whether the object is online at a particular point in time or not.

When an object is loaded in to the game again, it will be assigned a new valid RPObject.ID. However, the object will still have a valid and unique persistent #db_id which can be used when we need to store the object again. We can simply update the existing copy already on the database.

== How objects are stored ==
There is a problem right now in Marauroa: storing an object involves two steps, remove the previous stored object and insert the new one.
This is a very complex operation and it could be ''simplified'' to update just the fields that has changed. May you want to do it?

Latest revision as of 19:37, 14 April 2011



Database
Marauroa
Stendhal


This article describes the table structure of the Marauroa database. You might want to have a look at High Level Database Access which explains the high level API to access the database from your program code. The article Low Level Database Access describes how Marauroa accesses the database internally and how you can add support for your own tables.


Accounts

Authentication information is stored in the table account. It consists of the username, the password hash, email address, and the timestamp of the account creation. Please note that for historic reasons foreign key columns pointing to the account table are not named account_id but player_id.

For security reason every login (successful or not) is logged in the table loginEvent with the ip-address, timestamp, and a success flag. The column service is used to tell logins from a game and a website apart. The optional column seed stores a preauthentication seed. Marauroa automatically prevents logins for some time after too many failed tries.

Password changes are logged in a similar way. In addition to normal loginEvents the old password hash is stored as well. This is a precaution to restore hacked accounts back to their original owner.

Note: It may be a good idea to delete old rows in this table regularly for privacy reasons.

Bans

Unfortunately there are some unfriendly people out there that you may need to keep away. The tables accountban and banlist store such bans.

The table accountban is on a per account basis. The person trying to login is displayed the reason. Account bans can expire automatically.

The table banlist stores bans based on ip-address and ip-address-ranges. The mask 255.255.255.255 donates a single ip-address.

RPObjects

Marauroa knows two types of objects which can be stored to the database: players and zones. Both objects can contain other objects: Players have items in their bags, zones can contain objects that are flagged as storable.

Player objects are linked to their account using the table characters.

Note: Currently the rpobjects (including owned slots and rpojbects in those slots) are serialized in a Blob field. In the Marauroa Version 1.x it was a relational. Unfortunately that approach was failing performance requirements (MySQL is extremely slow on delete statements). There is a concept in development that proposes an improved relational structure.

Game Logging

There are two more tables used for logging:

gameEvents stores events that occur in the game world (killing monsters, trading, teleporting, etc.) with a time stamp and the player causing the event. The parameters param1 and param2 depend on the event.

Unless disabled Marauroa logs statistical information every minute in the table statistics. This includes the network traffic data and number of players online.

The whole picture

The following diagram shows the all tables used by Marauroa. Games are, however, free to add their own tables if the need arises (JMapacman and Marboard don't need additional tables but Stendhal makes heavy use of this possibility.)

{{#breadcrumbs: Marauroa | Internals | Database Structure }}

Retrieved from "https://meet.konsthol.eu/w/index.php?title=Marauroa_Database_Structure&oldid=105166"