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 descriptions

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

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

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

tmw_guild_members

Column name	Datatype	Nullable	References	Description

Quest states

tmw_quests

Column name	Datatype	Nullable	References	Description

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)

Example queries

• Count total number of in-game objects

select count(*) from tmw_items;

• Count total number of item types

select count(*) from tmw_base_items;

• List player inventory

select distinct tmw_items.id, tmw_items.name
from tmw_items, tmw_inventory
where tmw_items.id = tmw_inventory.id
and tmw_inventory.name = 'nym the great';

• Find weight of player inventory

select sum(tmw_base_items.weight)
from tmw_base_items, tmw_items, tmw_inventory
where tmw_base_items.name = tmw_items.name
and tmw_items.id = tmw_inventory.id
and tmw_inventory.name = 'nym the great';