Marauroa Database Structure: Difference between revisions
Jump to navigation
Jump to search
imported>MartinFuchs |
imported>MartinFuchs |
(No difference)
| |
Revision as of 20:02, 29 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 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. <br>
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 the configuration file '''server.ini''' ''(or whatever you choose to name it)'' and edit the next fields
<pre>
marauroa_DATABASE=JDBCPlayerDatabase
jdbc_class=com.mysql.jdbc.Driver
jdbc_url=jdbc:mysql://localhost/marauroa
jdbc_user=marauroa_dbuser
jdbc_pwd=marauroa_dbpwd
- 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.
Now, simply save the changes and your configuration file is ready.
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_dbuser@localhost identified by 'stendhal_dbpwd';
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.
This decision is made in RPManager. It calculates how often an object has to be stored.
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?