Archive:Database specifications

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

Comments

• 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)

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

Comments

• 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)

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!

• View dalstoragesql.hpp
• Download dalstoragesql.hpp (plain text)