Archive:Database specifications

Server Database Specification:

SQL[ite] Table Specifications:

• Account Information Table

create table tmw_accounts ( -- Username user varchar(32) unique primary key not null, -- Password hash password varchar(32) not null, -- Email address email varchar(128) unique not null );

• Character/Player Information Table

create table tmw_characters

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';