This article contains information for Programmers working or interested in working for The Mana World
SQL table specifications
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
|
|
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)
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
|
| unarmed_exp
|
INTEGER
|
NOT NULL
|
|
Experience: unarmed
|
| knife_exp
|
INTEGER
|
NOT NULL
|
|
Experience: knifes
|
| sword_exp
|
INTEGER
|
NOT NULL
|
|
Experience: swords
|
| polearm_exp
|
INTEGER
|
NOT NULL
|
|
Experience: polearms
|
| staff_exp
|
INTEGER
|
NOT NULL
|
|
Experience: staffs
|
| whip_exp
|
INTEGER
|
NOT NULL
|
|
Experience: whips
|
| bow_exp
|
INTEGER
|
NOT NULL
|
|
Experience: bows
|
| shoot_exp
|
INTEGER
|
NOT NULL
|
|
Experience: shooting
|
| mace_exp
|
INTEGER
|
NOT NULL
|
|
Experience: maces
|
| axe_exp
|
INTEGER
|
NOT NULL
|
|
Experience: axes
|
| thrown_exp
|
INTEGER
|
NOT NULL
|
|
Experience: throwing
|
|
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)
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.
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...)
|
|
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)
Reference
Since the database is changing relatively often while we're still developing 0.1.0 and nobody likes to keep this page up to date, here is the link to the source code that specifies creation of the database tables. The source code is always right!
