|
|
| Line 1: |
Line 1: |
| {{Status_outdated}}
| |
|
| |
| == SQL table specifications == | | == SQL table specifications == |
|
| |
|
| ==== Account Information Table ====
| | 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! |
|
| |
|
| {{beginsqltable|tmw_accounts}}
| | * [http://themanaworld.svn.sourceforge.net/viewvc/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?view=markup View dalstoragesql.hpp] |
| {{sqltablerow|id |integer unsigned primary key auto_increment|Unique account ID}}
| | * [http://themanaworld.svn.sourceforge.net/viewvc/*checkout*/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?revision=4024 Download dalstoragesql.hpp (plain text)] |
| {{sqltablerow|username|varchar(32) not null unique|User login name}}
| |
| {{sqltablerow|password|varchar(32) not null|Password hash}}
| |
| {{sqltablerow|email |varchar(64)|User email}}
| |
| {{sqltablerow|level |tinyint unsigned not null|User level (restricted, normal, moderator, admin)}}
| |
| {{sqltablerow|banned |mediumint unsigned not null|UNIX time when ban is over}}
| |
| {{endsqltable}}
| |
|
| |
|
| ==== Character/Player Information Table ==== | | == Example queries == |
|
| |
|
| <pre>
| | {{Status_outdated}} |
| create table tmw_characters (
| |
| -- [Unique] character name
| |
| name varchar(32) unique primary key not null,
| |
| -- Username
| |
| user varchar(32) not null,
| |
| -- Player information
| |
| gender int not null,
| |
| level int not null,
| |
| money int not null,
| |
| -- Coordinates & map
| |
| x int not null,
| |
| y int not null,
| |
| map text not null,
| |
| -- Statistics
| |
| strength int not null,
| |
| agility int not null,
| |
| vitality int not null,
| |
| intelligence int not null,
| |
| dexterity int not null,
| |
| luck int not null,
| |
| -- Player equipment
| |
| --inventory blob not null,
| |
| --equipment blob not null,
| |
| -- Table relationship
| |
| foreign key(user) references tmw_accounts(user)
| |
| );
| |
| </pre>
| |
|
| |
|
| ==== Item type table ====
| |
|
| |
| <pre>
| |
| create table tmw_base_items (
| |
| -- Name of item
| |
| name varchar(32) unique primary key not null,
| |
| -- Item attributes here
| |
| weight int not null
| |
| );
| |
| </pre>
| |
|
| |
| ==== In-game object table ====
| |
|
| |
| <pre>
| |
| create table tmw_items (
| |
| id int unique primary key not null,
| |
| name varchar(32) not null,
| |
| -- Special (unique) item attribues
| |
| -- Table relationship
| |
| foreign key (name) references tmw_base_items(name)
| |
| );
| |
| </pre>
| |
|
| |
| ==== Character inventory table ====
| |
|
| |
| <pre>
| |
| create table tmw_inventory (
| |
| -- Item
| |
| id int not null,
| |
| -- Character who has the item
| |
| name varchar(32) not null,
| |
| -- Table relationship
| |
| foreign key(id) references tmw_items(id),
| |
| foreign key(name) references tmw_characters(name)
| |
| );
| |
| </pre>
| |
|
| |
| ==== Character equipment table (items which are equipped) ====
| |
|
| |
| <pre>
| |
| create table tmw_equipment (
| |
| -- Item
| |
| id int not null,
| |
| -- Character who has item
| |
| name varchar(32) not null,
| |
| -- Item slot equipped on
| |
| slot int not null,
| |
| -- Table relationship
| |
| foreign key(id) references tmw_items(id),
| |
| foreign key(name) references tmw_characters(name)
| |
| );
| |
| </pre>
| |
|
| |
| == Example queries ==
| |
| * Count total number of in-game objects | | * Count total number of in-game objects |
| select count(*) from tmw_items; | | select count(*) from tmw_items; |
SQL table specifications
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
Template:Status outdated
- Count total number of in-game objects
select count(*) from tmw_items;
- Count total number of item types
select count(*) from tmw_base_items;
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';
