Archive:Database specifications

This article contains information for Programmers working or interested in working for The Mana World

Current version of database structure in git: 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

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

Comments

• 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

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)

• 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