From The Mana World
(Trying a more wiki-like way or writing down an updated accounts table) |
(Numbers are a lot shorter) |
||
Line 3: | Line 3: | ||
==== Account Information Table ==== | ==== Account Information Table ==== | ||
{{beginsqltable| | {{beginsqltable|tmw_accounts}} | ||
{{sqltablerow| | {{sqltablerow|id |integer unsigned primary key auto_increment|Unique account ID}} | ||
{{sqltablerow| | {{sqltablerow|username|varchar(32) not null unique|User login name}} | ||
{{sqltablerow| | {{sqltablerow|password|varchar(32) not null|Password hash}} | ||
{{sqltablerow| | {{sqltablerow|email |varchar(64)|User email}} | ||
{{sqltablerow| | {{sqltablerow|level |tinyint unsigned not null|User level (restricted, normal, moderator, admin)}} | ||
{{sqltablerow| | {{sqltablerow|banned |mediumint unsigned not null|UNIX time when ban is over}} | ||
{{endsqltable}} | {{endsqltable}} | ||
Revision as of 20:19, 24 December 2005
SQL table specifications
Account Information Table
|
Character/Player Information Table
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) );
Item type table
create table tmw_base_items ( -- Name of item name varchar(32) unique primary key not null, -- Item attributes here weight int not null );
In-game object table
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) );
Character inventory table
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) );
Character equipment table (items which are equipped)
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) );
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';