From The Mana World
m (Formatting) |
(Trying a more wiki-like way or writing down an updated accounts table) |
||
Line 1: | Line 1: | ||
== SQL | == SQL table specifications == | ||
==== Account Information Table ==== | ==== Account Information Table ==== | ||
{{beginsqltable|name=tmw_accounts}} | |||
{{sqltablerow|name=id|type=integer unsigned primary key auto_increment|desc=Unique account ID}} | |||
{{sqltablerow|name=username|type=varchar(32) not null unique|desc=User login name}} | |||
{{sqltablerow|name=password|type=varchar(32) not null|desc=Password hash}} | |||
{{sqltablerow|name=email|type=varchar(64)|desc=User email}} | |||
{{sqltablerow|name=level|type=tinyint unsigned not null|desc=User level (restricted, normal, moderator, admin)}} | |||
{{sqltablerow|name=banned|type=mediumint unsigned not null|desc=UNIX time when ban is over}} | |||
{{endsqltable}} | |||
==== Character/Player Information Table ==== | ==== Character/Player Information Table ==== |
Revision as of 20:14, 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';