This article contains information for Programmers working or interested in working for The Mana World
Current version of database structure in Git repository: 5
SQL table specifications
The current database model.
User accounts
| tmw_accounts
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of an account }}
|
| username
|
TEXT
|
NOT NULL UNIQUE
|
|
username / loginname of an user
|
| password
|
TEXT
|
NOT NULL
|
|
password of an user (sha256 encoded)
|
| email
|
TEXT
|
NOT NULL
|
|
email (sha256 encoded, only for pwd recovery)
|
| level
|
INTEGER
|
NOT NULL
|
|
numeric level of the user account
|
| banned
|
INTEGER
|
NOT NULL
|
|
unixtimestamp until when the account is banned
|
| registration
|
INTEGER
|
NOT NULL
|
|
unixtimestamp of account registration
|
| lastlogin
|
INTEGER
|
NOT NULL
|
|
unixtimestamp of last login with client
|
| authorization
|
TEXT
|
|
|
secret key used for password recovery
|
| expiration
|
INTEGER
|
|
|
unixtimestamp defining expiration of secret key
|
|
Details
- email
- The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
- level
- describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)
- authorization and expiration
- these fields are used by TMWWEB to store a secret key and the time of expiration if the user has requested to reset its password. The secret key is sent via mail to the user.
Characters
| tmw_characters
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of the character
|
| user_id
|
INTEGER
|
NOT NULL
|
tmw_accounts.id
|
id of the owner
|
| name
|
TEXT
|
NOT NULL UNIQUE
|
|
name of the character
|
| gender
|
INTEGER
|
NOT NULL
|
|
gender of the character (0/1)
|
| hair_style
|
INTEGER
|
NOT NULL
|
|
id of the hair sprite
|
| hair_color
|
INTEGER
|
NOT NULL
|
|
id of the hair color
|
| level
|
INTEGER
|
NOT NULL
|
|
experience level of the character
|
| char_pts
|
INTEGER
|
NOT NULL
|
|
available points to raise attributes
|
| correct_pts
|
INTEGER
|
NOT NULL
|
|
available points to lower attributes and regain char_pts
|
| money
|
INTEGER
|
NOT NULL
|
|
GP of the character
|
| x
|
INTEGER
|
NOT NULL
|
|
x position of the character on the map
|
| y
|
INTEGER
|
NOT NULL
|
|
y position of the character on the map
|
| map_id
|
INTEGER
|
NOT NULL
|
tmw_maps.id
|
id of the current map the character is located
|
| str
|
INTEGER
|
NOT NULL
|
|
strength of the character
|
| agi
|
INTEGER
|
NOT NULL
|
|
agility of the character
|
| dex
|
INTEGER
|
NOT NULL
|
|
dexternity of the character
|
| vit
|
INTEGER
|
NOT NULL
|
|
vitality of the character
|
| int
|
INTEGER
|
NOT NULL
|
|
intelligence of the character
|
| will
|
INTEGER
|
NOT NULL
|
|
willpower of the character
|
|
Concerns
- The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The
skill_id should point to a skills.xml file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --Bjørn 18:09, 12 September 2008 (CEST)
- I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --kess 19:54, 12 September 2008 (CEST)
- I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --Exceptionfault 15:50, 14 September 2008 (CEST)
- Thanks for taking out the skills from this table. However, I disagree about generalizing stuff on the level of attributes, unless there would be any plan of putting infrastructure in place to make this possible. For now we can't even finish a server for The Mana World within years, so please don't try to build a server that supports any online RPG just yet. I only suggested we take out the skills because it would be completely unmanageable as part of this table, but the list of attribute is not expected to change for now. --Bjørn 15:55, 22 September 2008 (CEST)
Character attributes
This table is intended to store skills and experiences of each character.
| tmw_char_skills
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| char_id
|
INTEGER
|
NOT NULL
|
tmw_characters.id
|
reference to the character
|
| skill_id
|
INTEGER
|
NOT NULL
|
(skills.xml)
|
reference to the skill
|
| exp
|
INTEGER
|
NOT NULL
|
|
current experience of the char in this skill
|
|
Concerns
- I'm not sure, if an INTEGER does the job for column exp? --Exceptionfault 14:40, 19 September 2008 (CEST)
- What is the range? I would expect it to be enough. --Bjørn 15:56, 22 September 2008 (CEST)
- Here’s some basic info:
- MySQL: INTEGER (INT) may be signed or unsigned and is 4 bytes large, MySQL also have 1, 2, 3 and 8 byte integer variants (TINYINT, SMALLINT, MEDIUMINT and BIGINT) [1]
- PostgreSQL: INTEGER (INT, INT4) is signed and 4 bytes large, PostgreSQL also have 2 and 8 byte integer variants (SMALLINT and BIGINT) [2]
- SQLite: INTEGER is signed and size is either 1, 2, 3, 4, 6 or 8 bytes depending on the size of the value [3]
- So, the largest integer type supported should be of 8 byte size (signed): from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, while the “standard†(of the SQL variants above) signed 4 byte integer ranges from -2,147,483,648 to +2,147,483,647. --kess 21:18, 22 September 2008 (CEST)
- Although we plan to have no fixed level cap I considered a skill level of about 100 to be the highest reasonable value a player can archieve. Level 100 requires an exp sum of 10 million with the current exp formula (level³ * 10) which fits into a 4 byte integer easily. We won't get in trouble with 32bit signed integers until the players get near skill level 600 (2.16 billion). The server and the netcode also use 32 bit integers internally.
Character Inventory
| tmw_inventories
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of the item
|
| owner_id
|
INTEGER
|
NOT NULL
|
tmw_characters.id
|
id of the owning character
|
| slot
|
INTEGER
|
NOT NULL
|
|
inventory slot where the item is equipped
|
| class_id
|
INTEGER
|
NOT NULL
|
|
type of the item, see items.xml file of tmwserv
|
| amount
|
INTEGER
|
NOT NULL
|
|
amount of items per slot
|
|
- While I haven’t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as Bjørn noted above for the tmw_characters table). I think it would be nicer to have something like {owner_id FK, slot, item_id FK, amount, PK owner_id + slot}, where class_id shouldn’t directly be needed in this table. --kess 19:49, 12 September 2008 (CEST)
- Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --Exceptionfault 16:02, 14 September 2008 (CEST)
- We want all equipment to be completely individual with completely unique stats. --Crush2 16:13, 14 September 2008 (CEST)
- Good to know, so I will extend the DAL improvement task in mantis. --Exceptionfault 16:20, 14 September 2008 (CEST)
Guilds
| tmw_guilds
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of the guid
|
| name
|
TEXT
|
NOT NULL UNIQUE
|
|
unique name of the guild
|
|
Guild memberships
The table tmw_guild_members stores informations which character is member in which guild and which rights does he has.
| tmw_guild_members
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| guild_id
|
INTEGER
|
NOT NULL
|
tmw_guilds.id
|
reference to the guild
|
| member_id
|
INTEGER
|
NOT NULL
|
tmw_characters.id
|
reference to the characters
|
| rights
|
INTEGER
|
NOT NULL
|
|
id showing the rights a character has in this guild (member, admin...)
|
|
World state
The table tmw_world_state is used to store persistent informations about the world or individual maps.
| tmw_world_state
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| state_name
|
TEXT
|
PRIMARY KEY
|
|
unique name of a state variable
|
| map_id
|
INTEGER
|
NULL
|
tmw_maps.id
|
reference to a map, see details
|
| value
|
TEXT
|
NULL
|
|
string value of the state
|
| moddate
|
INTEGER
|
NOT NULL
|
|
date and time of the last modification
|
|
Details
- The column state_name forms the primary key of the table. If map_id is null, the variable is used globally in the world, otherwise the variable is specific for a map.
- moddate is for informational use only as it contains the unixtimestamp of the last modification.
- It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple state_names with different map_ids. So we set a state_name to unique and use map_id as notifier if this variable is globally used or just locally for a specific map. --Exceptionfault 11:05, 15 September 2008 (CEST)
- This table is ready for usage, commited as rev. 4629 --Exceptionfault 13:51, 15 September 2008 (CEST)
Quest states
This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...
| tmw_quests
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| owner_id
|
INTEGER
|
NOT NULL
|
tmw_characters.id
|
reference to the character
|
| name
|
TEXT
|
NOT NULL
|
|
name of the quest variable
|
| value
|
TEXT
|
NOT NULL
|
|
current value of the quest variable
|
|
- Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for characters. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --Bjørn 18:16, 12 September 2008 (CEST)
- We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --Bjørn 18:16, 12 September 2008 (CEST)
- I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --Crush2 23:28, 12 September 2008 (CEST)
- I agree with Bjørn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --Exceptionfault 16:20, 14 September 2008 (CEST)
- I am not sure I understand everything which have been said above, so I will just sketch how I would do it. The table would have one owner_id (mandatory unique integer: a character; possibly either a character, an account, a guild etc.) and a number of variables (mandatory non-uniqe datatype: holding a number of quest states). The variables would simply be named variable1 and so on, and added as necessary.
- In the Lua quest scripts the bit masks would be hidden and the quest states themselves would be assigned and checked through general functions.
- An XML file
quests.xml or otherfashionly named would hold the general information regarding the quests and their states so that there could be content updates (of a stripped down version of the server file). By requesting the quest states from the server the clients would show the info with a nice GUI.
- What I do not understand above is the need to split the quest variable into name and value... But I’m merely a beginner in database designs and you surely have a clever reason why far beyond my own grasp of the matter.
- ✎ Kess☽ 15:25, 23 May 2009 (UTC)
Auctions
Inspired by the ManaBay auction system developed by Qoal, it is planned to build a trading platform for the whole community of players using item auctions.
| tmw_auctions
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| auction_id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of an auction
|
| auction_state
|
INTEGER
|
NOT NULL
|
|
current state of the auction
|
| char_id
|
INTEGER
|
NOT NULL
|
tmw_characters (id)
|
owner of the auction
|
| itemclass_id
|
INTEGER
|
NOT NULL
|
items.xml
|
id of the item
|
| amount
|
INTEGER
|
NOT NULL
|
|
amount of items to trade
|
| start_time
|
INTEGER
|
NOT NULL
|
|
date of creation or start of auction
|
| end_time
|
INTEGER
|
NOT NULL
|
|
end of auction (unixtimestamp)
|
| start_price
|
INTEGER
|
NOT NULL
|
|
minimum price for the first bid
|
| min_price
|
INTEGER
|
NULL
|
|
minimum price for the final bid to succeed
|
| buyout_price
|
INTEGER
|
NULL
|
|
price for direct buy of bidded item
|
| description
|
TEXT
|
NULL
|
|
optional description of an auction
|
|
Annotations
- auction_state can have the following values:
- 0 = The auction is published and ready for bidders
- 1 = The auction has finished and closed
- start_time contains the creation date of the auction. Format: Unixtimestamp
Bids on Auctions
This table stores bids made by characters to a specific auction.
| tmw_auctions
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| bid_id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of a bid
|
| auction_id
|
INTEGER
|
NOT NULL
|
tmw_auctions
|
unique id of an auction
|
| char_id
|
INTEGER
|
NOT NULL
|
tmw_characters
|
owner of the bid
|
| bid_time
|
INTEGER
|
NOT NULL
|
|
time of the bid, used for sorting bids
|
| bid_price
|
INTEGER
|
NOT NULL
|
|
price bidded by the character
|
|
Annotations
- bid_time holds the date and time of the bid as Unixtimestamp. Regarding 2 bids can be placed at the same second, the higher bid_price is relevant. If the bid_price is also equal, the smallest bid_id counts, as this is the record first inserted into the database.
Postal system
Letters
| tmw_post
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| letter_id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of the letter
|
| sender_id
|
INTEGER
|
NOT NULL
|
tmw_characters
|
sending character of the letter
|
| receiver_id
|
INTEGER
|
NOT NULL
|
tmw_characters
|
receiving character of the letter
|
| letter_type
|
INTEGER
|
NOT NULL
|
|
type of the letter (unused atm)
|
| expiration_date
|
INTEGER
|
NOT NULL
|
|
date and time of expiration
|
| sending_date
|
INTEGER
|
NOT NULL
|
|
date and time of sending
|
| letter_text
|
TEXT
|
NULL
|
|
text
|
|
Item attachments
| tmw_post_attachments
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| attachment_id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of the attachment
|
| letter_id
|
INTEGER
|
NOT NULL
|
tmw_post
|
reference to the letter
|
| item_id
|
INTEGER
|
NOT NULL
|
tmw_item_instances
|
attached itema
|
|
Miscellaneous
Transactions
Each transaction like creating a new character or picking up an item is logged in the table twm_transactions.
| tmw_transactions
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of the transaction
|
| char_id
|
INTEGER
|
NOT NULL
|
tmw_characters
|
reference to the character
|
| action
|
INTEGER
|
NOT NULL
|
tmw_transaction_codes
|
reference to the action
|
| message
|
TEXT
|
|
|
an informational text about the action
|
| time
|
INTEGER
|
NOT NULL
|
|
unixtimestamp / transaction time
|
|
Transaction codes
The transactions from the table tmw_transactions use a numeric code to define the action that has happened. This
table contains a description of all available actions.
| tmw_transaction_codes
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| id
|
INTEGER
|
PRIMARY KEY
|
|
unique id of the transaction code
|
| description
|
TEXT
|
NOT NULL
|
|
a short description of the action
|
| category
|
TEXT
|
NOT NULL
|
|
a short categorization of the action
|
|
List of online users
TMWSERV maintains a list of online users. You can select this table directly to find out who is online, or use the services provided by TMWWEB.
| tmw_online_list
|
| Column name |
Datatype |
Nullable |
References |
Description
|
| char_id
|
INTEGER
|
PRIMARY KEY
|
tmw_characters
|
unique id of the character
|
| login_date
|
INTEGER
|
NOT NULL
|
|
Unix time-stamp of login
|
|
