From The Mana World
(Outdated database reference)
(Directly link to the source code for database specification)
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;

Revision as of 07:47, 22 April 2008

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;
  • 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';
Retrieved from "https://oldwiki.themanaworld.org/index.php?title=Archive:Database_specifications&oldid=9193"