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 (

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