Marauroa Database Structure
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.
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.
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.
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.
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.)