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