Marauroa Database Structure: Difference between revisions

Jump to navigation Jump to search
← Older editNewer edit →
Content deleted Content added
imported>MiguelAngelBlanchLardin
No edit summary
imported>MartinFuchs
update to the current Stendhal database schema
Line 2: Line 2:
Arianne uses a database to store game state information so that the games can be truly persistent.
Arianne uses a database to store game state information so that the games can be truly persistent.


==Database Tables and Relationships ==
==Database Tables==


This are the tables of the Stendhal database schema:
The database table relationship schema is:
<pre>
<pre>
Table PLAYER
Table account
+----------+------------------------------------+------+-----+-------------------+----------------+
{
| Field | Type | Null | Key | Default | Extra |
PK(username)
+----------+------------------------------------+------+-----+-------------------+----------------+
password
| 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 CHARACTERS
Table banlist
+---------+--------------+------+-----+---------+----------------+
{
| Field | Type | Null | Key | Default | Extra |
PK(character)
+---------+--------------+------+-----+---------+----------------+
content
| id | int(11) | NO | PRI | NULL | auto_increment |
}
| address | varchar(15) | YES | | NULL | |
| mask | varchar(15) | YES | | NULL | |
| reason | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+


Table LOGIN_EVENT
Table characters
+-----------+-------------+------+-----+---------+-------+
{
| Field | Type | Null | Key | Default | Extra |
PK(id)
+-----------+-------------+------+-----+---------+-------+
address
| player_id | int(11) | NO | | | |
timedate
| charname | varchar(32) | NO | PRI | | |
result
| object_id | int(11) | NO | | | |
}
+-----------+-------------+------+-----+---------+-------+


Table STATISTICS
Table character_stats
+----------+--------------+------+-----+-------------------+-------+
(
| Field | Type | Null | Key | Default | Extra |
PK(timedate)
+----------+--------------+------+-----+-------------------+-------+
| 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
bytes_send
+----------+--------------+------+-----+-------------------+----------------+
bytes_recv
| 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 itemid
players_login
+---------+---------+------+-----+---------+-------+
players_logout
| Field | Type | Null | Key | Default | Extra |
players_timeout
+---------+---------+------+-----+---------+-------+
players_online
| last_id | int(11) | YES | | NULL | |
)
+---------+---------+------+-----+---------+-------+


Table RPOBJECT
Table itemlog
+----------+-------------+------+-----+-------------------+----------------+
(
| Field | Type | Null | Key | Default | Extra |
PK(id)
+----------+-------------+------+-----+-------------------+----------------+
slot_id
| 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 RPATTRIBUTE
Table rpobject
+-----------+---------+------+-----+---------+----------------+
(
| Field | Type | Null | Key | Default | Extra |
PK(object_id)
+-----------+---------+------+-----+---------+----------------+
PK(name)
| object_id | int(11) | NO | PRI | NULL | auto_increment |
value
| data | blob | YES | | NULL | |
)
+-----------+---------+------+-----+---------+----------------+


Table RPSLOT
Table statistics
+-----------------+-----------+------+-----+-------------------+-------+
(
| Field | Type | Null | Key | Default | Extra |
object_id
+-----------------+-----------+------+-----+-------------------+-------+
name
| timedate | timestamp | NO | | CURRENT_TIMESTAMP | |
PK(slot_id)
| bytes_send | int(11) | YES | | NULL | |
)
| bytes_recv | int(11) | YES | | NULL | |
</pre>
| players_login | int(11) | YES | | NULL | |
| players_logout | int(11) | YES | | NULL | |
| players_timeout | int(11) | YES | | NULL | |
| players_online | int(11) | YES | | NULL | |
+-----------------+-----------+------+-----+-------------------+-------+


Table rpzone
Relationships:
+---------+-------------+------+-----+---------+-------+
<pre>
| Field | Type | Null | Key | Default | Extra |
Relationship PLAYER_CHARACTERS
+---------+-------------+------+-----+---------+-------+
{
| zone_id | varchar(32) | NO | PRI | | |
PK(player_username)
| data | blob | YES | | NULL | |
PK(characters_character)
+---------+-------------+------+-----+---------+-------+
}


Important tables which store original data, use the InnoDB engine. Tables with redundant data for easy access and logging use the MyISAM table type.
Relationship PLAYER_LOGIN_EVENT
{
PK(player_username)
PK(login_event_id)
}
</pre>
Translate this to SQL easily and you have the SQL schema of Marauroa




Line 85: Line 139:
http://www.mysql.com/downloads/api-jdbc-stable.html
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.
To configure Marauroa to work with a JDBC source, we need to modify the configuration of the JDBC Connection.


So open the configuration file '''marauroad.ini''' ''(or whatever you choose to name it)'' and edit the next fields
So open the configuration file '''server.ini''' ''(or whatever you choose to name it)'' and edit the next fields
<pre>
<pre>
marauroa_DATABASE=JDBCPlayerDatabase
marauroa_DATABASE=JDBCPlayerDatabase
Line 107: Line 161:
Before using the application with the database, you need to create the database itself. So, with MySQL just run MySQL and enter:
Before using the application with the database, you need to create the database itself. So, with MySQL just run MySQL and enter:
<pre>
<pre>
create database marauroa;
create database stendhal;
grant all on marauroa.* to marauroa_dbuser@localhost identified by 'marauroa_dbpwd';
grant all on stendhal.* to stendhal_dbuser@localhost identified by 'stendhal_dbpwd';
</pre>
</pre>


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