Marauroa Database Structure: Difference between revisions
imported>Hendrik Brummermann No edit summary |
imported>Hendrik Brummermann |
||
| Line 173: | Line 173: | ||
This decision is made in RPManager. It calculates how often an object has to be stored. |
This decision is made in RPManager. It calculates how often an object has to be stored. |
||
Marauroa knows two types of objects which can be |
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. |
||
Revision as of 20:35, 31 December 2007
Basic idea behind Database storage
Arianne uses a database to store game state information so that the games can be truly persistent.
Database Tables
This are the tables of the Stendhal database schema:
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 | |
+---------+--------------+------+-----+---------+----------------+
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 | |
+----------+--------------+------+-----+-------------------+-------+
Table gameevents (InnoDB)
+----------+--------------+------+-----+-------------------+----------------+
| 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 | |
+----------+--------------+------+-----+-------------------+----------------+
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 | | | |
+----------+-------------+------+-----+---------+----------------+
Table itemid (MyISAM)
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| last_id | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
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 | |
+----------+-------------+------+-----+-------------------+----------------+
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 | |
+-----------+-------------+------+-----+-------------------+-------+
Table rpobject (InnoDB)
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| object_id | int(11) | NO | PRI | NULL | auto_increment |
| data | blob | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
Table rpzone (InnoDB)
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| zone_id | varchar(32) | NO | PRI | | |
| data | blob | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
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 | |
+-----------------+-----------+------+-----+-------------------+-------+
Important tables which store original data, use the InnoDB engine. Tables with redundant data for easy access and logging use the MyISAM table type.
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 relational databases. Unfortunatally it does not hide vendor specific stuff. Marauroa and Stendhal currently only work with MySQL because of that. Adding support for other database software would be very easy as the database specific code is concentrated in the classes JDBCDatabase (Marauroa) and StendhalPlayerDatabase (Stendhal). We have no need for that, so it was not done, yet. We will, however, accept patches for multi database system support, so if you need it, go ahead.
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, run the appropriate GenerateINI program. For Stendhal this is games.stendhal.server.core.engine.GenerateINI.
Before using the application with the database, you need to create the database itself. So, with MySQL just run MySQL and enter:
create database stendhal; grant all on stendhal.* to stendhal_user@localhost identified by 'stendhal_passwd';
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 an expansive operation, so it is only done every 10 minutes or on special events (like logout).
This decision is made in RPManager. It calculates how often an object has to be stored.
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.