From The Mana World
(Directly link to the source code for database specification) |
(Added current table specifications) |
||
Line 1: | Line 1: | ||
{{Category_programming}} | |||
== SQL table specifications == | == SQL table specifications == | ||
=== User accounts === | |||
{|border=1 cellpadding=5 cellspacing=0 | |||
|+ tmw_accounts | |||
|-bgcolor=lightgrey | |||
! 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 === | |||
{|border=1 cellpadding=5 cellspacing=0 | |||
|+ tmw_characters | |||
|-bgcolor=lightgrey | |||
! Column name !! Datatype !! Nullable !! References !! Description | |||
|- | |||
|} | |||
=== Character Inventory === | |||
{|border=1 cellpadding=5 cellspacing=0 | |||
|+ tmw_inventories | |||
|-bgcolor=lightgrey | |||
! Column name !! Datatype !! Nullable !! References !! Description | |||
|- | |||
|} | |||
=== Guilds === | |||
{|border=1 cellpadding=5 cellspacing=0 | |||
|+ tmw_guilds | |||
|-bgcolor=lightgrey | |||
! Column name !! Datatype !! Nullable !! References !! Description | |||
|- | |||
|} | |||
=== Guild memberships === | |||
{|border=1 cellpadding=5 cellspacing=0 | |||
|+ tmw_guild_members | |||
|-bgcolor=lightgrey | |||
! Column name !! Datatype !! Nullable !! References !! Description | |||
|- | |||
|} | |||
=== Quest states === | |||
{|border=1 cellpadding=5 cellspacing=0 | |||
|+ tmw_quests | |||
|-bgcolor=lightgrey | |||
! 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! | 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! |
Revision as of 08:43, 12 September 2008
This article contains information for Programmers working or interested in working for The Mana World
SQL table specifications
User 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) | |
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
Column name | Datatype | Nullable | References | Description |
---|
Character Inventory
Column name | Datatype | Nullable | References | Description |
---|
Guilds
Column name | Datatype | Nullable | References | Description |
---|
Guild memberships
Column name | Datatype | Nullable | References | Description |
---|
Quest states
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!
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';