The Mana World - User contributions [en]

Setting up tmwAthena on Linux

2009-10-17T10:13:52Z

Exceptionfault: /* Cloning and compiling */ added troubleshooting when trying to compile under ubuntu x64

This article describes setting up eAthena on a Linux system. For how to set up eAthena on Windows refer to [[Building and running eAthena on Windows]].

== Cloning and compiling ==

Start with cloning eAthena from our [[Git Repository|Git repository]] and compiling it, then compile the eathena-monitor utility. If you are compiling for FreeBSD, use gmake instead of make. Also note that you will need bison and flex installed.

$ git clone git://gitorious.org/tmw-eathena/mainline.git eathena
$ cd eathena
$ make
$ gcc -o eathena-monitor eathena-monitor.c

There's probably lots of warnings while compiling the server executables, but you can just ignore that. If you are having problems cloning eAthena, there are other URLs in the [[Git Repository|Git repository]] article (the push URL will only work if you are a TMW developer). Once this step is successful, let's grab the eAthena data from Git as well. This is where we keep all the server side configuration, scripts and other data.

We'll check it out to a <code>eathena-data</code> directory, but note that using <code>$HOME/tmwserver</code> requires less editing of configuration files later, since this is the default.

$ cd ..
$ git clone git://gitorious.org/tmw-eathena-data/mainline.git eathena-data
$ cd eathena-data

If you are having problems cloning eathena-data, there are other URLs in the [[Git Repository|Git repository]] article.

Copy over the server executables and monitor:

$ cp -iv ../eathena/*-server .
$ cp -iv ../eathena/eathena-monitor .

Optional step. If you did not check out the <code>server-data</code> in <code>$HOME/tmwserver</code> you can add a symbolic link, so that it looks like you did check it out there:

$ cd $HOME
$ ln -s '''''path/to/were/you/put/eathena-data/''''' tmwserver

=== Troubleshooting ===

When having trouble compiling eathena under Ubuntu x86_64 and receiving an error like "Error: gnu/stubs-32.h: No such file or directory", just install the following lib:
sudo apt-get install libc6-dev-i386

== Mandatory configuration ==
Now we need to edit some configuration specific to our setup.

=== Logging ===
Create a directory where server log files will be stored, usually just do <code>mkdir log</code> in the directory where your servers are located.

=== General configuration ===
First go to the <code>eathena-data/conf</code> directory and make a copy of each file ending in <code>.example</code>, removing that extension. These files are generally server-specific and we don't want changes made in the Git repository to unexpectedly affect your local configuration.

If you are just going to use the server yourself you can enter <code>127.0.0.1</code> as the value for all IP addresses. Also, if you have no clue about what ports are or which you will be using, leave the default values as they are.

The local configurations needed for the three servers are as follow:

<code>login_local.conf</code>
* Set a password for the <code>ladmin</code> tool at <code>admin_pass</code>, or disable remote administration at <code>admin_state</code>.
** You should configure the file <code>ladmin_local.conf</code> accordingly, if you choose to leave this tool enabled.
* Also don't forget to set a game master password at <code>gm_pass</code>.

<code>char_local.conf</code>
* Set a username and password to be used for inter-server communication at <code>userid</code> and <code>passwd</code>.
* You may optionally want to customize your server name at <code>server_name</code>.
* Specify the IP address and, if you changed it, the port of the login server as seen from the host on which you run the character server at <code>login_ip</code> and <code>login_port</code> (if they are on the same machine, use 127.0.0.1).
* Specify the IP address of the character server as seen from the clients connecting to your server at <code>char_ip</code>. This should usually be your internet IP address.

<code>map_local.conf</code>
* Set the same inter-server communication username and password as you did in <code>char_local.conf</code>.
* Specify the IP address and, if you changed, the port of the character server as seen from the host on which you run the map server at <code>char_ip</code> and <code>char_port</code> (if they are on the same machine, use 127.0.0.1).
* Specify the IP address of the map server as seen from the clients connecting to your server at <code>map_ip</code>.

Lastly, go to <code>save/</code> and copy the account.txt.example file (as account.txt). If you changed the inter-server passwords, you need to change the username <code style="color:darkgreen;">'''''s1'''''</code> and the password <code style="color:darkgreen;">'''''p1'''''</code> of account 0. Don't change anything else about it.

Now make sure everything works by doing a test run of the eAthena server before you make any optional changes.

=== Path to server data ===
Please note, if you have checked out out the <code>eathena-data</code> to <code>$HOME/tmwserver</code> or have a symbolic link pointing here, you can skip these steps.

* Edit eathena.sh and set <code>SRVHOMEDIR</code> to the location of your server-data checkout. Probably something like <code>$HOME/.../eathena-data</code>.
* Edit <code>conf/eathena-monitor.conf</code> and adjust the paths according to the <code>SRVHOMEDIR</code> you previously set in <code>eathena.sh</code>

== Optional configuration ==
Here are some pointers if you want to configure your server in other ways than the official TMW server. Feel free to browse any of the other configuration files to see what else you may want to change.

* The maps and NPCs that should be available are set in <code>map_athena.conf</code>.
* The starting location and the starting equipment for the player are set in <code>char_athena.conf</code>.
* Access levels to GM commands are set in <code>atcommand_local.conf</code>.
* â€œThe message of todayâ€ text shown at login can be changed in <code>motd.txt</code>.
* The command help texts can be changed in <code>help.txt</code>.

=== Specifying your own update host ===
When you want to provide your players with a different world from the official server, you need to set up your own update host. You can specify a different update host in <code>conf/login_local.conf</code> at <code>update_host</code>. The client stores updates downloaded from the update host in an update-host specific directory, in order to avoid filename collisions.

TODO: Provide more information about the files that an update host needs to server.

== Running your server ==
We have two convenient scripts that allow us to start and stop the server with single commands. The <code>eathena-monitor</code> launches all servers and restarts them when they crash. The <code>eathena.sh</code> shell script is used to start/stop the <code>eathena-monitor</code>.

$ ./eathena.sh start
$ ./eathena.sh restart
$ ./eathena.sh stop

Note that there are also GM commands that allow you to restart for example the map server. This is preferred over using <code>./eathena.sh restart</code>. Also be sure to announce the restart to your players, since this doesn't happen automatically.

Connect with your client and create your first account to test if everything works fine.

If you manually run the servers, the char server will segfault the first time it's able to connect with the login server. This is fine. Just restart it. The monitor will handle this automatically.

=== Troubleshooting ===
If your server fails to run and/or you cannot connect to it with your client, it is likely to be caused by one or several of the below points. But before you do anything else, check the [[#Logging|logfile]] as it may say what is the cause.

* Forgotten or mismatching usernames, passwords, ip addresses and ports.
*# Check that you have followed all [[#Mandatory configuration|mandatory configuration steps]].
* Unavailable files.
*# Check where you did download the server-data. If you did not put in the default directory, please make sure that you have either [[#Checking out and compiling|created a symlink]] that points to the default directory or that you have [[#Path to server data|changed]] the needed configuration files.
*# Check whether you have [[#Checking out and compiling|created symlinks]] to the three server executables.

=== Making yourself a GM ===
Specify your account ID as found in <code>save/account.txt</code> (usually 2000000 for the first account) in <code>conf/gm_account.txt</code>. The level 99 should be the highest. You can set up the GM commands in <code>conf/atcommand_athena.conf</code>.

User:Exceptionfault/tmwencyclopedia

2009-09-11T17:37:32Z

Exceptionfault: /* Todo */ finished ncps and map warps

= TMW Encyclopedia =

I've started a small project based on tmwweb (aka [[Account Manager]]) called TMWEncyclopedia.
It's intention is to provide a mostly dynamically generated encyclopedia of all known
maps, monsters, items and npcs.

You can have a look at an early preview under http://tmwweb-test.mericet.de
Just click on "items" in the navigation pane, search for a common icon like
"Bug Leg" and click on its name. Welcome to the Encyclopedia!

TMWE consists of two components. The data gathering module and the data visualization
module.

== Data gathering ==

The data currently available in the encyclopedia comes from the following files:

* tmwserv-data\items.xml
* tmwdata\monsters.xml
* tmwdata\maps\*.tmx

To have a faster access to this data, the files get scanned by a ruby script
and stored in a relational sqlite database. This ruby script has to be executed
whenever the base data has changed, e.g. if new items were added.

Find the code of the ruby script here http://gitorious.org/~Exceptionfault/tmwweb/excfs-sandbox/trees/master/utils/TMWEnzyklopedia via [[git]]

== Data visualization ==

The frontend of the encyclopedia will be tightly integrated in the [[Account Manager]].
Its an optional extension which could be enabled in the configuration files.

== Todo ==

* Show warps between maps
** <s>Data gathering and storage</s>
** <s>Frontend / Visualization</s>
* Show NPCs on maps
** <s>Data gathering and storage</s>
** <s>Frontend / Visualization</s>
* Show more informations on items
** Data gathering and storage
** Frontend / Visualization
* Show more informations on monsters
** Data gathering and storage
** Frontend / Visualization
* Create mechanism to be able to extend the dynamic content with manual written informations

User:Exceptionfault

2009-09-10T11:05:55Z

Exceptionfault: /* Contributions */

== About me==

This text is stolen from my personal blog and translated by myself for you all, so enjoy...

I'm born in 1981 in Heidelberg, Germany. Since 2003 I work as a software- and database developer with focus on Oracle databases and Microsoft .NET technologies at the headquarter of one of the biggest german food discounter. Advantaged by my well-founded education as DBA, I form the ''interface'' between our DBA's and our developers; sometimes with more or less understanding of their individual problems with each other ;-) .

== Proposals ==

All of these proposals apply to the development branch 0.1.* as I think this should take up the most effort in implementing new features.

* [[Individual item attributes]]
* ''advanced database layout with better relational approach''

== Contributions ==

I've contributed to the following projects, articles or efforts in The Mana World:

* [[Account_Manager|Account Manager]] aka tmwweb
* [[User:Exceptionfault/tmwencyclopedia|TMW Encyclopedia]]

User:Exceptionfault/tmwenclopedia

2009-09-10T11:05:29Z

Exceptionfault: User:Exceptionfault/tmwenclopedia moved to User:Exceptionfault/tmwencyclopedia: misspelled

#REDIRECT [[User:Exceptionfault/tmwencyclopedia]]

User:Exceptionfault/tmwencyclopedia

2009-09-10T11:05:29Z

Exceptionfault: User:Exceptionfault/tmwenclopedia moved to User:Exceptionfault/tmwencyclopedia: misspelled

= TMW Encyclopedia =

I've started a small project based on tmwweb (aka [[Account Manager]]) called TMWEncyclopedia.
It's intention is to provide a mostly dynamically generated encyclopedia of all known
maps, monsters, items and npcs.

You can have a look at an early preview under http://tmwweb-test.mericet.de
Just click on "items" in the navigation pane, search for a common icon like
"Bug Leg" and click on its name. Welcome to the Encyclopedia!

TMWE consists of two components. The data gathering module and the data visualization
module.

== Data gathering ==

The data currently available in the encyclopedia comes from the following files:

* tmwserv-data\items.xml
* tmwdata\monsters.xml
* tmwdata\maps\*.tmx

To have a faster access to this data, the files get scanned by a ruby script
and stored in a relational sqlite database. This ruby script has to be executed
whenever the base data has changed, e.g. if new items were added.

Find the code of the ruby script here http://gitorious.org/~Exceptionfault/tmwweb/excfs-sandbox/trees/master/utils/TMWEnzyklopedia via [[git]]

== Data visualization ==

The frontend of the encyclopedia will be tightly integrated in the [[Account Manager]].
Its an optional extension which could be enabled in the configuration files.

== Todo ==

* Show warps between maps
** <s>Data gathering and storage</s>
** Frontend / Visualization
* Show NPCs on maps
** <s>Data gathering and storage</s>
** Frontend / Visualization
* Show more informations on items
** Data gathering and storage
** Frontend / Visualization
* Show more informations on monsters
** Data gathering and storage
** Frontend / Visualization
* Create mechanism to be able to extend the dynamic content with manual written informations

User:Exceptionfault

2009-09-10T08:07:43Z

Exceptionfault: /* Contributions */ added link to tmwencyclopedia

User:Exceptionfault/tmwencyclopedia

2009-09-10T08:06:38Z

Exceptionfault: some words and ideas to TMWEncyclopedia

= TMW Encyclopedia =

I've started a small project based on tmwweb (aka [[Account Manager]]) called TMWEncyclopedia.
It's intention is to provide a mostly dynamically generated encyclopedia of all known
maps, monsters, items and npcs.

You can have a look at an early preview under http://tmwweb-test.mericet.de
Just click on "items" in the navigation pane, search for a common icon like
"Bug Leg" and click on its name. Welcome to the Encyclopedia!

TMWE consists of two components. The data gathering module and the data visualization
module.

== Data gathering ==

The data currently available in the encyclopedia comes from the following files:

* tmwserv-data\items.xml
* tmwdata\monsters.xml
* tmwdata\maps\*.tmx

To have a faster access to this data, the files get scanned by a ruby script
and stored in a relational sqlite database. This ruby script has to be executed
whenever the base data has changed, e.g. if new items were added.

Find the code of the ruby script here http://gitorious.org/~Exceptionfault/tmwweb/excfs-sandbox/trees/master/utils/TMWEnzyklopedia via [[git]]

== Data visualization ==

The frontend of the encyclopedia will be tightly integrated in the [[Account Manager]].
Its an optional extension which could be enabled in the configuration files.

== Todo ==

* Show warps between maps
** <s>Data gathering and storage</s>
** Frontend / Visualization
* Show NPCs on maps
** <s>Data gathering and storage</s>
** Frontend / Visualization
* Show more informations on items
** Data gathering and storage
** Frontend / Visualization
* Show more informations on monsters
** Data gathering and storage
** Frontend / Visualization
* Create mechanism to be able to extend the dynamic content with manual written informations

Archive:Database specifications

2009-08-23T11:30:29Z

Exceptionfault: Added table tmw_transactions and tmw_transaction_code

{{Category_programming}}

Current version of database structure in [[git]]: 5

== SQL table specifications ==

[[Image:Database specification proposal.png|thumb|200px|The current database model.]]

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{sqltablerow|authorization | TEXT | | | secret key used for password recovery }}
{{sqltablerow|expiration | INTEGER | | | unixtimestamp defining expiration of secret key }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)
* authorization and expiration
** these fields are used by TMWWEB to store a secret key and the time of expiration if the user has requested to reset its password. The secret key is sent via mail to the user.

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)
*** Thanks for taking out the skills from this table. However, I disagree about generalizing stuff on the level of attributes, unless there would be any plan of putting infrastructure in place to make this possible. For now we can't even finish a server for The Mana World within years, so please don't try to build a server that supports any online RPG just yet. I only suggested we take out the skills because it would be completely unmanageable as part of this table, but the list of attribute is not expected to change for now. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:55, 22 September 2008 (CEST)

=== Character attributes ===

This table is intended to store skills and experiences of each character.

{{beginsqltable|tmw_char_skills}}
{{sqltablerow|char_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|skill_id | INTEGER | NOT NULL | (skills.xml) | reference to the skill }}
{{sqltablerow|exp | INTEGER | NOT NULL | | current experience of the char in this skill }}
{{endsqltable}}

==== Concerns ====

* I'm not sure, if an INTEGER does the job for column exp? --[[User:Exceptionfault|Exceptionfault]] 14:40, 19 September 2008 (CEST)
** What is the range? I would expect it to be enough. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:56, 22 September 2008 (CEST)
*** Hereâ€™s some basic info:
***: '''MySQL:''' INTEGER (INT) may be signed or unsigned and is 4 bytes large, MySQL also have 1, 2, 3 and 8 byte integer variants (TINYINT, SMALLINT, MEDIUMINT and BIGINT) [http://dev.mysql.com/doc/refman/6.0/en/numeric-types.html]
***: '''PostgreSQL:''' INTEGER (INT, INT4) is signed and 4 bytes large, PostgreSQL also have 2 and 8 byte integer variants (SMALLINT and BIGINT) [http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html]
***: '''SQLite:''' INTEGER is signed and size is either 1, 2, 3, 4, 6 or 8 bytes depending on the size of the value [http://sqlite.org/datatype3.html]
***: So, the largest integer type supported should be of 8 byte size (signed): from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, while the â€œstandardâ€ (of the SQL variants above) signed 4 byte integer ranges from -2,147,483,648 to +2,147,483,647. --[[User:Kess|kess]] 21:18, 22 September 2008 (CEST)
*** Although we plan to have no fixed level cap I considered a skill level of about 100 to be the highest reasonable value a player can archieve. Level 100 requires an exp sum of 10 million with the current exp formula (levelÂ³ * 10) which fits into a 4 byte integer easily. We won't get in trouble with 32bit signed integers until the players get near skill level 600 (2.16 billion). The server and the netcode also use 32 bit integers internally.

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

==== Guild memberships ====

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== World state ===

The table ''tmw_world_state'' is used to store persistent informations about the world or individual maps.

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| value | TEXT | NULL | | string value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====

* The column ''state_name'' forms the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable is specific for a map.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

==== Comments ====

* It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple ''state_name''s with different ''map_id''s. So we set a ''state_name'' to unique and use ''map_id'' as notifier if this variable is globally used or just locally for a specific map. --[[User:Exceptionfault|Exceptionfault]] 11:05, 15 September 2008 (CEST)
** This table is ready for usage, commited as rev. 4629 --[[User:Exceptionfault|Exceptionfault]] 13:51, 15 September 2008 (CEST)

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

* I am not sure I understand everything which have been said above, so I will just sketch how I would do it. The table would have one ''owner_id'' (mandatory unique integer: a character; possibly either a character, an account, a guild etc.) and a number of ''variable''s (mandatory non-uniqe datatype: holding a number of quest states). The variables would simply be named ''variable1'' and so on, and added as necessary.
** In the Lua quest scripts the bit masks would be hidden and the quest states themselves would be assigned and checked through general functions.
** An XML file <code>quests.xml</code> or otherfashionly named would hold the general information regarding the quests and their states so that there could be content updates (of a stripped down version of the server file). By requesting the quest states from the server the clients would show the info with a nice GUI.
: What I do not understand above is the need to split the quest variable into name and value... But Iâ€™m merely a beginner in database designs and you surely have a clever reason why far beyond my own grasp of the matter.
: âœŽâ€ˆ[[User:Kess|Kess]][[User talk:Kess|â˜½]] 15:25, 23 May 2009 (UTC)

=== Auctions ===

Inspired by the ManaBay auction system developed by Qoal, it is planned to build a trading platform for the whole community of players using item auctions.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| auction_id | INTEGER | PRIMARY KEY | | unique id of an auction }}
{{sqltablerow| auction_state | INTEGER | NOT NULL | | current state of the auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters (id) | owner of the auction }}
{{sqltablerow| itemclass_id | INTEGER | NOT NULL | items.xml | id of the item }}
{{sqltablerow| amount | INTEGER | NOT NULL | | amount of items to trade }}
{{sqltablerow| start_time | INTEGER | NOT NULL | | date of creation or start of auction }}
{{sqltablerow| end_time | INTEGER | NOT NULL | | end of auction (unixtimestamp) }}
{{sqltablerow| start_price | INTEGER | NOT NULL | | minimum price for the first bid }}
{{sqltablerow| min_price | INTEGER | NULL | | minimum price for the final bid to succeed }}
{{sqltablerow| buyout_price | INTEGER | NULL | | price for direct buy of bidded item }}
{{sqltablerow| description | TEXT | NULL | | optional description of an auction }}
{{endsqltable}}

==== Annotations ====

* auction_state can have the following values:
** 0 = The auction is published and ready for bidders
** 1 = The auction has finished and closed
* start_time contains the creation date of the auction. Format: Unixtimestamp

==== Bids on Auctions ====

This table stores bids made by characters to a specific auction.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| bid_id | INTEGER | PRIMARY KEY | | unique id of a bid }}
{{sqltablerow| auction_id | INTEGER | NOT NULL | tmw_auctions | unique id of an auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters | owner of the bid }}
{{sqltablerow| bid_time | INTEGER | NOT NULL | | time of the bid, used for sorting bids }}
{{sqltablerow| bid_price | INTEGER | NOT NULL | | price bidded by the character }}
{{endsqltable}}

===== Annotations =====

* bid_time holds the date and time of the bid as Unixtimestamp. Regarding 2 bids can be placed at the same second, the higher bid_price is relevant. If the bid_price is also equal, the smallest bid_id counts, as this is the record first inserted into the database.

=== Postal system ===

==== Letters ====

{{beginsqltable|tmw_post}}
{{sqltablerow| letter_id | INTEGER | PRIMARY KEY | | unique id of the letter }}
{{sqltablerow| sender_id | INTEGER | NOT NULL | tmw_characters | sending character of the letter }}
{{sqltablerow| receiver_id | INTEGER | NOT NULL | tmw_characters | receiving character of the letter }}
{{sqltablerow| letter_type | INTEGER | NOT NULL | | type of the letter (unused atm) }}
{{sqltablerow| expiration_date | INTEGER | NOT NULL | | date and time of expiration }}
{{sqltablerow| sending_date | INTEGER | NOT NULL | | date and time of sending }}
{{sqltablerow| letter_text | TEXT | NULL | | text }}
{{endsqltable}}

==== Item attachments ====

{{beginsqltable|tmw_post_attachments}}
{{sqltablerow| attachment_id | INTEGER | PRIMARY KEY | | unique id of the attachment }}
{{sqltablerow| letter_id | INTEGER | NOT NULL | tmw_post | reference to the letter }}
{{sqltablerow| item_id | INTEGER | NOT NULL | tmw_item_instances | attached itema }}
{{endsqltable}}

=== Miscellaneous ===

==== Transactions ====

Each transaction like creating a new character or picking up an item is logged in the table twm_transactions.

{{beginsqltable|tmw_transactions}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of the transaction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters | reference to the character }}
{{sqltablerow| action | INTEGER | NOT NULL | tmw_transaction_codes | reference to the action }}
{{sqltablerow| message | TEXT | | | an informational text about the action }}
{{sqltablerow| time | INTEGER | NOT NULL | | unixtimestamp / transaction time }}
{{endsqltable}}

===== Transaction codes =====

The transactions from the table tmw_transactions use a numeric code to define the action that has happened. This
table contains a description of all available actions.

{{beginsqltable|tmw_transaction_codes}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of the transaction code }}
{{sqltablerow| description | TEXT | NOT NULL | | a short description of the action }}
{{sqltablerow| category | TEXT | NOT NULL | | a short categorization of the action }}
{{endsqltable}}

==== List of online users ====

TMWSERV maintains a list of online users. You can select this table directly to find out who is online, or use the services provided by TMWWEB.

{{beginsqltable|tmw_online_list}}
{{sqltablerow| char_id | INTEGER | PRIMARY KEY | tmw_characters | unique id of the character }}
{{sqltablerow| login_date | INTEGER | NOT NULL | | Unix time-stamp of login }}
{{endsqltable}}

Archive:Database specifications

2009-08-23T11:17:41Z

Exceptionfault: /* User accounts */ added columns authorization and expiration

{{Category_programming}}

== SQL table specifications ==

[[Image:Database specification proposal.png|thumb|200px|The current database model.]]

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{sqltablerow|authorization | TEXT | | | secret key used for password recovery }}
{{sqltablerow|expiration | INTEGER | | | unixtimestamp defining expiration of secret key }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)
* authorization and expiration
** these fields are used by TMWWEB to store a secret key and the time of expiration if the user has requested to reset its password. The secret key is sent via mail to the user.

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)
*** Thanks for taking out the skills from this table. However, I disagree about generalizing stuff on the level of attributes, unless there would be any plan of putting infrastructure in place to make this possible. For now we can't even finish a server for The Mana World within years, so please don't try to build a server that supports any online RPG just yet. I only suggested we take out the skills because it would be completely unmanageable as part of this table, but the list of attribute is not expected to change for now. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:55, 22 September 2008 (CEST)

=== Character attributes ===

This table is intended to store skills and experiences of each character.

{{beginsqltable|tmw_char_skills}}
{{sqltablerow|char_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|skill_id | INTEGER | NOT NULL | (skills.xml) | reference to the skill }}
{{sqltablerow|exp | INTEGER | NOT NULL | | current experience of the char in this skill }}
{{endsqltable}}

==== Concerns ====

* I'm not sure, if an INTEGER does the job for column exp? --[[User:Exceptionfault|Exceptionfault]] 14:40, 19 September 2008 (CEST)
** What is the range? I would expect it to be enough. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:56, 22 September 2008 (CEST)
*** Hereâ€™s some basic info:
***: '''MySQL:''' INTEGER (INT) may be signed or unsigned and is 4 bytes large, MySQL also have 1, 2, 3 and 8 byte integer variants (TINYINT, SMALLINT, MEDIUMINT and BIGINT) [http://dev.mysql.com/doc/refman/6.0/en/numeric-types.html]
***: '''PostgreSQL:''' INTEGER (INT, INT4) is signed and 4 bytes large, PostgreSQL also have 2 and 8 byte integer variants (SMALLINT and BIGINT) [http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html]
***: '''SQLite:''' INTEGER is signed and size is either 1, 2, 3, 4, 6 or 8 bytes depending on the size of the value [http://sqlite.org/datatype3.html]
***: So, the largest integer type supported should be of 8 byte size (signed): from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, while the â€œstandardâ€ (of the SQL variants above) signed 4 byte integer ranges from -2,147,483,648 to +2,147,483,647. --[[User:Kess|kess]] 21:18, 22 September 2008 (CEST)
*** Although we plan to have no fixed level cap I considered a skill level of about 100 to be the highest reasonable value a player can archieve. Level 100 requires an exp sum of 10 million with the current exp formula (levelÂ³ * 10) which fits into a 4 byte integer easily. We won't get in trouble with 32bit signed integers until the players get near skill level 600 (2.16 billion). The server and the netcode also use 32 bit integers internally.

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

=== Guild memberships ===

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== World state ===

The table ''tmw_world_state'' is used to store persistent informations about the world or individual maps.

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| value | TEXT | NULL | | string value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====

* The column ''state_name'' forms the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable is specific for a map.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

==== Comments ====

* It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple ''state_name''s with different ''map_id''s. So we set a ''state_name'' to unique and use ''map_id'' as notifier if this variable is globally used or just locally for a specific map. --[[User:Exceptionfault|Exceptionfault]] 11:05, 15 September 2008 (CEST)
** This table is ready for usage, commited as rev. 4629 --[[User:Exceptionfault|Exceptionfault]] 13:51, 15 September 2008 (CEST)

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

* I am not sure I understand everything which have been said above, so I will just sketch how I would do it. The table would have one ''owner_id'' (mandatory unique integer: a character; possibly either a character, an account, a guild etc.) and a number of ''variable''s (mandatory non-uniqe datatype: holding a number of quest states). The variables would simply be named ''variable1'' and so on, and added as necessary.
** In the Lua quest scripts the bit masks would be hidden and the quest states themselves would be assigned and checked through general functions.
** An XML file <code>quests.xml</code> or otherfashionly named would hold the general information regarding the quests and their states so that there could be content updates (of a stripped down version of the server file). By requesting the quest states from the server the clients would show the info with a nice GUI.
: What I do not understand above is the need to split the quest variable into name and value... But Iâ€™m merely a beginner in database designs and you surely have a clever reason why far beyond my own grasp of the matter.
: âœŽâ€ˆ[[User:Kess|Kess]][[User talk:Kess|â˜½]] 15:25, 23 May 2009 (UTC)

=== Auctions ===

Inspired by the ManaBay auction system developed by Qoal, it is planned to build a trading platform for the whole community of players using item auctions.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| auction_id | INTEGER | PRIMARY KEY | | unique id of an auction }}
{{sqltablerow| auction_state | INTEGER | NOT NULL | | current state of the auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters (id) | owner of the auction }}
{{sqltablerow| itemclass_id | INTEGER | NOT NULL | items.xml | id of the item }}
{{sqltablerow| amount | INTEGER | NOT NULL | | amount of items to trade }}
{{sqltablerow| start_time | INTEGER | NOT NULL | | date of creation or start of auction }}
{{sqltablerow| end_time | INTEGER | NOT NULL | | end of auction (unixtimestamp) }}
{{sqltablerow| start_price | INTEGER | NOT NULL | | minimum price for the first bid }}
{{sqltablerow| min_price | INTEGER | NULL | | minimum price for the final bid to succeed }}
{{sqltablerow| buyout_price | INTEGER | NULL | | price for direct buy of bidded item }}
{{sqltablerow| description | TEXT | NULL | | optional description of an auction }}
{{endsqltable}}

==== Annotations ====

* auction_state can have the following values:
** 0 = The auction is published and ready for bidders
** 1 = The auction has finished and closed
* start_time contains the creation date of the auction. Format: Unixtimestamp

==== Bids on Auctions ====

This table stores bids made by characters to a specific auction.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| bid_id | INTEGER | PRIMARY KEY | | unique id of a bid }}
{{sqltablerow| auction_id | INTEGER | NOT NULL | tmw_auctions | unique id of an auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters | owner of the bid }}
{{sqltablerow| bid_time | INTEGER | NOT NULL | | time of the bid, used for sorting bids }}
{{sqltablerow| bid_price | INTEGER | NOT NULL | | price bidded by the character }}
{{endsqltable}}

===== Annotations =====

* bid_time holds the date and time of the bid as Unixtimestamp. Regarding 2 bids can be placed at the same second, the higher bid_price is relevant. If the bid_price is also equal, the smallest bid_id counts, as this is the record first inserted into the database.

=== Postal system ===

==== Letters ====

{{beginsqltable|tmw_post}}
{{sqltablerow| letter_id | INTEGER | PRIMARY KEY | | unique id of the letter }}
{{sqltablerow| sender_id | INTEGER | NOT NULL | tmw_characters | sending character of the letter }}
{{sqltablerow| receiver_id | INTEGER | NOT NULL | tmw_characters | receiving character of the letter }}
{{sqltablerow| letter_type | INTEGER | NOT NULL | | type of the letter (unused atm) }}
{{sqltablerow| expiration_date | INTEGER | NOT NULL | | date and time of expiration }}
{{sqltablerow| sending_date | INTEGER | NOT NULL | | date and time of sending }}
{{sqltablerow| letter_text | TEXT | NULL | | text }}
{{endsqltable}}

==== Item attachments ====

{{beginsqltable|tmw_post_attachments}}
{{sqltablerow| attachment_id | INTEGER | PRIMARY KEY | | unique id of the attachment }}
{{sqltablerow| letter_id | INTEGER | NOT NULL | tmw_post | reference to the letter }}
{{sqltablerow| item_id | INTEGER | NOT NULL | tmw_item_instances | attached itema }}
{{endsqltable}}

=== Miscellaneous ===

==== List of online users ====

TMWSERV maintains a list of online users. You can select this table directly to find out who is online, or use the services provided by TMWWEB.

{{beginsqltable|tmw_online_list}}
{{sqltablerow| char_id | INTEGER | PRIMARY KEY | tmw_characters | unique id of the character }}
{{sqltablerow| login_date | INTEGER | NOT NULL | | Unix time-stamp of login }}
{{endsqltable}}

Git

2008-12-08T07:32:38Z

Exceptionfault: Make the wiki more searchable

#REDIRECT [[Git Repository]]

Archive:TMWWeb Connector

2008-12-03T12:40:33Z

Exceptionfault: /* List of online users (onlineuser) */ added csv as output format

== What is a TMWWEB Connector? ==

The TMWWEB Connector is a package of methods provided by TMWWEB to allow foreign applications to access data of the running tmwserv server. A common example is a list of online users that are currently connected to the server. TMWWEB is able to deliver the data in different formats to be as open as possible for all kinds of applications, e.g. plain text or xml.

== FAQ ==

=== How can I connect to the Connector? ===

Until today, ther is no public installation of the [[Account Manager]] respective the connector. If you set up an installation of your own, just navigate your browser to the mainpage of tmwweb and add a "index.php/connector/" to the URL. The URL might look like that:

http://localhost/tmwweb/index.php/connector/

This is the root URL of the connector. Now you have to extend the URL with the name of the connector function you wan't to execute, e.g. ''onlineuser'' which will give you a URL like this:

http://localhost/tmwweb/index.php/connector/onlineuser/

Some methods probaly need input parameters or support different output formats. All parameters have to be added with a "/" to the url similar as the method name.

== Method Specification ==

=== List of online users (onlineuser) ===

This will give you a list of all users online and connected with a char to the tmwserv Server.

==== Parameters ====
# Output format:
#* "plain" (default) will return a plain textfile with a character name per line.
#* "xml" will return a xml file with more detailed informations about online characters.
#* "csv" will return a plain textfile with detailed informations about online characters, separated by a ","

'''Plain text example'''

name
name2

'''XML example'''

<users>
<user name="[string]"
gender="[number]"
map="[number]"
login_timestamp="[number]"
login_date="[string]" />
<user ...>
</users>

'''CSV example'''

"name",gender,map,login_timestamp,"login_date"

* ''name'': Name of the character
* ''gender'': 0 = male, 1 = female
* ''map'': ID of the current location (see [[Configuration file:maps.xml]])
* ''login_timestamp'': Unix time-stamp the character logged in
* ''login_date'': Time-stamp of login in ISO 8601 format (e.g. 2004-02-12T15:19:21+00:00)

==== Usage ====
connector/onlineuser/[plain|xml|csv]

Archive:TMWWeb Connector

2008-12-03T11:52:57Z

Exceptionfault:

== What is a TMWWEB Connector? ==

The TMWWEB Connector is a package of methods provided by TMWWEB to allow foreign applications to access data of the running tmwserv server. A common example is a list of online users that are currently connected to the server. TMWWEB is able to deliver the data in different formats to be as open as possible for all kinds of applications, e.g. plain text or xml.

== FAQ ==

=== How can I connect to the Connector? ===

Until today, ther is no public installation of the [[Account Manager]] respective the connector. If you set up an installation of your own, just navigate your browser to the mainpage of tmwweb and add a "index.php/connector/" to the URL. The URL might look like that:

http://localhost/tmwweb/index.php/connector/

This is the root URL of the connector. Now you have to extend the URL with the name of the connector function you wan't to execute, e.g. ''onlineuser'' which will give you a URL like this:

http://localhost/tmwweb/index.php/connector/onlineuser/

Some methods probaly need input parameters or support different output formats. All parameters have to be added with a "/" to the url similar as the method name.

== Method Specification ==

=== List of online users (onlineuser) ===

This will give you a list of all users online and connected with a char to the tmwserv Server.

==== Parameters ====
# Output format:
#* "plain" (default) will return a plain textfile with a character name per line.
#* "xml" will return a xml file with more detailed informations about online characters.

'''XML Spec'''

<users>
<user name="[string]"
gender="[number]"
map="[number]"
login_timestamp="[number]
login_date="[string] />
<user ...>
</users>

Each online user will result in its own ''user'' element. The following list explains the provided attributes per charcter:

* ''name'': Name of the character
* ''gender'': 0 = male, 1 = female
* ''map'': ID of the current location (see [[Configuration file:maps.xml]])
* ''number'': Unix time-stamp the character logged in
* ''login_date'': Time-stamp of login in ISO 8601 format (e.g. 2004-02-12T15:19:21+00:00)

==== Usage ====
connector/onlineuser/[plain|xml]

Archive:Account Manager

2008-12-03T11:15:31Z

Exceptionfault: added hint to tmwweb connector

== The Mana World Account Manager ==

The Account Manager also known as TMWWEB is the web interface for users and administrators to manage their accounts from outside the game. Users can reset their password, see their character stats, and change the email associated with the account.

== Installation ==

To install you need to clone tmwweb from our sourcecode repository. See: [[Git Repository]].

You will need a web server running PHP 5.1 or later. If your accountserver uses SQLite as backend you have to enable pdo_sqlite for php.
On FreeBSD you will need to install ports databases/php5-pdo_sqlite and security/pecl-hash along with lang/php5.

== Configuration ==

To get TMWWEB up and running there are several configuration steps to do. After uploading all scripts to your wwwroot, navigate with your browser to the root of the scripts ending with ''setup.php''. This script will guide you through the setup progress and will tell you what to do next and why. If a task is red, fix it and reload the page until everything is in status green.

=== Single tasks ===

There are several config files that have to be prepared for your specific environment. You can find all config files under ./system/application/config

Tmwweb comes with a default configuration file called "config.default.php" You should use this file as template to set up your individual configuration. Make a copy of this file and name rename it to "config.php". In general you have to modify the following settings:

set the "base_url" to the url of your installation.
define the desired "log_threshold" to your needs

Do the same with the file "database.default.php" and "email.default.php". Rename them to "database.php" and "email.php" configure the options inside for your needs.

The following configuration files are shipped with defaults that may or may not be suitable for you.

menu.php
tmw_config.php

Instead of modifying the files directly you should make a copy of them and rename them to <prefix>.user.php, so "menu.php" gets "menu.user.php". Tmwweb first loads menu.php, afterwards menu.user.php (if it exists) and overrides the default values with your custom settings. The advantage of this method is, that upgrading tmwweb won't break your configuration.

'''Hint: You should only define values in the *.user.php files that differ from the default! Remove every entry from those files that match the default!'''

'''All other files in the config directory shouldn`t get touched by you!'''

After proper configuration of all files you have to ensure that the webserver has write access to the ./system/logs, the ./images/items and ./data directory.

chmod 777 ./system/logs
chmod 777 ./data
chmod 777 ./images/items

Tmwweb tries to store cached data read from other modules like tmwserv in the data directory for faster access. Therefore you should allow the webserver to create files there. The logs directory is used for logging as you can think.

== Troubleshooting ==

After uploading and configuring tmwweb it might happen that every site you call is only white and blank. Lets set loglevel in config.php to 4 and after refreshing your site in the browser have a look at the logfiles under ./system/logs
If there is no file except index.html please recheck your directory permissions. Again, the webserver needs write permissions to ./system/logs. If you cannot find a file that is named "config.php", please go back to the chapter [[#Configuration]] and read it more carefully!

== Translations ==

Tmwweb has a simple multilanguage support integrated. During login into your account you can choose one of the currently translated languages. If your native language is not available yet, feel free to support the dev team and translate tmwweb.

=== Howto translate into your language ===
To start with a new language there are two simple steps to do:

<blockquote>
Copy the directoy <tt>./system/application/language/english</tt> as it is the shipped language with tmwweb and should contain all necessary strings. Also you have to copy ./system/language/english to your new language. This directory contains strings comming from CodeIgniter, the used php framework.

Ensure not to copy the hidden directory .svn if you checked out from subversion. Each php file ending with *_lang.php needs to be translated.
</blockquote>
$lang['character'] = '<put your translation here>';
<blockquote>
Ensure to double quote inside of your strings if necessary.

Lastly you have to add your new language to the config file <tt>tmwweb/system/application/conf/tmw_conf.php</tt>. Just add your language to the array <tt>$_tmw_languages</tt> like shown below. The parameter 'dir' has to be the name of your added directory, e.g. 'german'. The parameter 'name' is the displayed string during login. This should be the native name of the language, like 'deutsch'.

Don't forget a comma to separate language arrays.
</blockquote>
$_tmw_languages = array(
array('dir'=>'english', 'name'=>'english'),
array('dir'=>'german', 'name'=>'deutsch')
);

== Maintenance ==

The intention of tmwweb is to be a slim, administrative frontend for tmwserv. Therfor it is natural to use as much as data, information and configuration we can get from tmwserv. Because tmwserv makes extensive use of xml configuration files, which would be too expensive to parse on every page request, tmwweb uses different methods to cache those informations for faster access.

From time to time, if the data in tmwserv was modified, a admin user has to refresh the caches of tmwweb. Therefor you can find a menuoption "maintenance" when logged in with an administrative account. This page shows you all caches and allows you to refresh them with a single click.

=== items.xml ===

Refreshing the items database is a bit special, thats why it is mentioned here additionally.
To show the inventory of a character, the images of those items have to be available for a webbrowser. Therfore they have to reside somewhere under the DOCUMENT_ROOT of your webserver. To keep all things together, we decided to store them under ./images/items. When refreshing the items database, tmwweb looks in that directory and compares the images located there to all items in the database. If a image is missing, tmwweb tries to copy this image from your tmwdata path, you can configure in tmw_config.php (see chapter 2).

To copy those images, the webserver needs write permission to the images/items directory. If tmwweb can't find the image in the tmwdata directory is is not allowed to write to the ./images/items directory, the refresh procedure will show you a list with all missing images. Then you will have to copy those images yourself.

== TMWWEB Connector ==

The TMWWEB Connector is a package of methods provided by TMWWEB to allow foreign applications to access data of the running tmwserv server. A common example is a list of online users that are currently connected to the server. See [[TMWWEB Connector]] for a detailed description of available methods.

Archive:Account Manager

2008-12-03T11:10:23Z

Exceptionfault: generally updated and fixed for git

Archive:Upgrade Database

2008-12-01T08:39:39Z

Exceptionfault: provide error message if version mismatches between account server and database

From time to time, the database model used by tmwserv will be extended or modified. To prevent inconsistencies between the database model and the account server, the database model has its own version number which has to match the known version of the account server. That means, if you upgrade you database model, you also have to update tmwserv and vice versa. If the version of your database is incompatible with you version of the account server, the account server will print out a message like this on startup:

[FTL] Error opening the database: Database version is not supported. Needed version: '1', current version: '2'

In this case, the version of your database is newer, than the version of the account server. You have to get the latest sources of the account server.

==== Determine the database version ====

To find out which version your current database is of, try the following SELECT statement, which should work well independent of your database backend.

SELECT value FROM tmw_world_states WHERE state_name = 'database_version';

This should give you a numeric value which represents your current database version. When using SQLite try the following statement:

sqlite3 tmw.db "SELECT value FROM tmw_world_states WHERE state_name = 'database_version'";

==== How to upgrade my database ====

According to your chosen database backend, you will find a subdirectory called "updates" under "tmwserv/src/sql/<db_backend>". The update scripts follow a naming scheme like "update_<old_version>_to_<new_version>.sql". So if your current version is "1" and you want to upgrade to "2", find the script called "update_1_to_2.sql".

Steps to execute the script depends on your backend. For SQLite just move to the folder where your current database file "tmw.db" lies and type:

sqlite3 tmw.db < src/sql/sqlite/updates/update_1_to_2.sql

'''Attention: The scripts don't matter what version you are running, so check it before running a script!'''

Archive:Database specifications

2008-12-01T08:25:51Z

Exceptionfault: added table tmw_online_list to store online users

{{Category_programming}}

== SQL table specifications ==

[[Image:Database specification proposal.png|thumb|200px|The current database model.]]

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)
*** Thanks for taking out the skills from this table. However, I disagree about generalizing stuff on the level of attributes, unless there would be any plan of putting infrastructure in place to make this possible. For now we can't even finish a server for The Mana World within years, so please don't try to build a server that supports any online RPG just yet. I only suggested we take out the skills because it would be completely unmanageable as part of this table, but the list of attribute is not expected to change for now. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:55, 22 September 2008 (CEST)

=== Character attributes ===

This table is intended to store skills and experiences of each character.

{{beginsqltable|tmw_char_skills}}
{{sqltablerow|char_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|skill_id | INTEGER | NOT NULL | (skills.xml) | reference to the skill }}
{{sqltablerow|exp | INTEGER | NOT NULL | | current experience of the char in this skill }}
{{endsqltable}}

==== Concerns ====

* I'm not sure, if an INTEGER does the job for column exp? --[[User:Exceptionfault|Exceptionfault]] 14:40, 19 September 2008 (CEST)
** What is the range? I would expect it to be enough. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:56, 22 September 2008 (CEST)
*** Hereâ€™s some basic info:
***: '''MySQL:''' INTEGER (INT) may be signed or unsigned and is 4 bytes large, MySQL also have 1, 2, 3 and 8 byte integer variants (TINYINT, SMALLINT, MEDIUMINT and BIGINT) [http://dev.mysql.com/doc/refman/6.0/en/numeric-types.html]
***: '''PostgreSQL:''' INTEGER (INT, INT4) is signed and 4 bytes large, PostgreSQL also have 2 and 8 byte integer variants (SMALLINT and BIGINT) [http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html]
***: '''SQLite:''' INTEGER is signed and size is either 1, 2, 3, 4, 6 or 8 bytes depending on the size of the value [http://sqlite.org/datatype3.html]
***: So, the largest integer type supported should be of 8 byte size (signed): from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, while the â€œstandardâ€ (of the SQL variants above) signed 4 byte integer ranges from -2,147,483,648 to +2,147,483,647. --[[User:Kess|kess]] 21:18, 22 September 2008 (CEST)
*** Although we plan to have no fixed level cap I considered a skill level of about 100 to be the highest reasonable value a player can archieve. Level 100 requires an exp sum of 10 million with the current exp formula (levelÂ³ * 10) which fits into a 4 byte integer easily. We won't get in trouble with 32bit signed integers until the players get near skill level 600 (2.16 billion). The server and the netcode also use 32 bit integers internally.

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

=== Guild memberships ===

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== World state ===

The table ''tmw_world_state'' is used to store persistent informations about the world or individual maps.

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| value | TEXT | NULL | | string value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====

* The column ''state_name'' forms the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable is specific for a map.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

==== Comments ====

* It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple ''state_name''s with different ''map_id''s. So we set a ''state_name'' to unique and use ''map_id'' as notifier if this variable is globally used or just locally for a specific map. --[[User:Exceptionfault|Exceptionfault]] 11:05, 15 September 2008 (CEST)
** This table is ready for usage, commited as rev. 4629 --[[User:Exceptionfault|Exceptionfault]] 13:51, 15 September 2008 (CEST)

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Auctions ===

Inspired by the ManaBay auction system developed by Qoal, it is planned to build a trading platform for the whole community of players using item auctions.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| auction_id | INTEGER | PRIMARY KEY | | unique id of an auction }}
{{sqltablerow| auction_state | INTEGER | NOT NULL | | current state of the auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters (id) | owner of the auction }}
{{sqltablerow| itemclass_id | INTEGER | NOT NULL | items.xml | id of the item }}
{{sqltablerow| amount | INTEGER | NOT NULL | | amount of items to trade }}
{{sqltablerow| start_time | INTEGER | NOT NULL | | date of creation or start of auction }}
{{sqltablerow| end_time | INTEGER | NOT NULL | | end of auction (unixtimestamp) }}
{{sqltablerow| start_price | INTEGER | NOT NULL | | minimum price for the first bid }}
{{sqltablerow| min_price | INTEGER | NULL | | minimum price for the final bid to succeed }}
{{sqltablerow| buyout_price | INTEGER | NULL | | price for direct buy of bidded item }}
{{sqltablerow| description | TEXT | NULL | | optional description of an auction }}
{{endsqltable}}

==== Annotations ====

* auction_state can have the following values:
** 0 = The auction is published and ready for bidders
** 1 = The auction has finished and closed
* start_time contains the creation date of the auction. Format: Unixtimestamp

==== Bids on Auctions ====

This table stores bids made by characters to a specific auction.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| bid_id | INTEGER | PRIMARY KEY | | unique id of a bid }}
{{sqltablerow| auction_id | INTEGER | NOT NULL | tmw_auctions | unique id of an auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters | owner of the bid }}
{{sqltablerow| bid_time | INTEGER | NOT NULL | | time of the bid, used for sorting bids }}
{{sqltablerow| bid_price | INTEGER | NOT NULL | | price bidded by the character }}
{{endsqltable}}

===== Annotations =====

* bid_time holds the date and time of the bid as Unixtimestamp. Regarding 2 bids can be placed at the same second, the higher bid_price is relevant. If the bid_price is also equal, the smallest bid_id counts, as this is the record first inserted into the database.

=== Postal system ===

==== Letters ====

{{beginsqltable|tmw_post}}
{{sqltablerow| letter_id | INTEGER | PRIMARY KEY | | unique id of the letter }}
{{sqltablerow| sender_id | INTEGER | NOT NULL | tmw_characters | sending character of the letter }}
{{sqltablerow| receiver_id | INTEGER | NOT NULL | tmw_characters | receiving character of the letter }}
{{sqltablerow| letter_type | INTEGER | NOT NULL | | type of the letter (unused atm) }}
{{sqltablerow| expiration_date | INTEGER | NOT NULL | | date and time of expiration }}
{{sqltablerow| sending_date | INTEGER | NOT NULL | | date and time of sending }}
{{sqltablerow| letter_text | TEXT | NULL | | text }}
{{endsqltable}}

==== Item attachments ====

{{beginsqltable|tmw_post_attachments}}
{{sqltablerow| attachment_id | INTEGER | PRIMARY KEY | | unique id of the attachment }}
{{sqltablerow| letter_id | INTEGER | NOT NULL | tmw_post | reference to the letter }}
{{sqltablerow| item_id | INTEGER | NOT NULL | tmw_item_instances | attached itema }}
{{endsqltable}}

=== Miscellaneous ===

==== List of online users ====

TMWSERV maintains a list of online users. You can select this table directly to find out who is online, or use the services provided by TMWWEB.

{{beginsqltable|tmw_online_list}}
{{sqltablerow| char_id | INTEGER | PRIMARY KEY | tmw_characters | unique id of the character }}
{{sqltablerow| login_date | INTEGER | NOT NULL | tmw_characters | unixtimestamp of login }}
{{endsqltable}}

Archive:Upgrade Database

2008-12-01T08:11:41Z

Exceptionfault: /* Determine the database version */ added sqlite

From time to time, the database model used by tmwserv will be extended or modified. To prevent inconsistencies between the database model and the account server, the database model has its own version number which has to match the known version of the account server. That means, if you upgrade you database model, you also have to update tmwserv and vice versa.

==== Determine the database version ====

To find out which version your current database is of, try the following SELECT statement, which should work well independent of your database backend.

SELECT value FROM tmw_world_states WHERE state_name = 'database_version';

This should give you a numeric value which represents your current database version. When using SQLite try the following statement:

sqlite3 tmw.db "SELECT value FROM tmw_world_states WHERE state_name = 'database_version'";

==== How to upgrade my database ====

According to your chosen database backend, you will find a subdirectory called "updates" under "tmwserv/src/sql/<db_backend>". The update scripts follow a naming scheme like "update_<old_version>_to_<new_version>.sql". So if your current version is "1" and you want to upgrade to "2", find the script called "update_1_to_2.sql".

Steps to execute the script depends on your backend. For SQLite just move to the folder where your current database file "tmw.db" lies and type:

sqlite3 tmw.db < src/sql/sqlite/updates/update_1_to_2.sql

'''Attention: The scripts don't matter what version you are running, so check it before running a script!'''

Archive:Upgrade Database

2008-11-30T12:25:20Z

Exceptionfault: describe process of upgrade before next change in the database model

From time to time, the database model used by tmwserv will be extended or modified. To prevent inconsistencies between the database model and the account server, the database model has its own version number which has to match the known version of the account server. That means, if you upgrade you database model, you also have to update tmwserv and vice versa.

==== Determine the database version ====

To find out which version your current database is of, try the following SELECT statement, which should work well independent of your database backend.

SELECT value FROM tmw_world_states WHERE state_name = 'database_version';

This should give you a numeric value which represents your current database version.

==== How to upgrade my database ====

According to your chosen database backend, you will find a subdirectory called "updates" under "tmwserv/src/sql/<db_backend>". The update scripts follow a naming scheme like "update_<old_version>_to_<new_version>.sql". So if your current version is "1" and you want to upgrade to "2", find the script called "update_1_to_2.sql".

Steps to execute the script depends on your backend. For SQLite just move to the folder where your current database file "tmw.db" lies and type:

sqlite3 tmw.db < src/sql/sqlite/updates/update_1_to_2.sql

'''Attention: The scripts don't matter what version you are running, so check it before running a script!'''

Development:Developers

2008-11-30T11:20:18Z

Exceptionfault: /* Active */ added myself

== Active ==
{| border="0" cellspacing="0" cellpadding="5" width="100%" style="border-bottom: 2px solid #efdead"
! style="background:#efdead;" width="150px" align="left" | Name
! style="background:#efdead;" width="200px" align="left" | Occupation
! style="background:#efdead;" width="150px" align="left" | Location

{{dev2| [[User:Exceptionfault|Andreas Habel (Exceptionfault)]] | Programmer | Germany}}
{{dev1| [[User:Av3nger|Av3nger]] | Pixel art, map creation, translations | Brazil}}
{{dev2| [[User:Bertram|Bertram]] | Programmer | France}}
{{dev1| [[User:BjÃ¸rn|BjÃ¸rn Lindeijer]] | Lead Programmer | The Netherlands}}
{{dev2| [[User:Crush|Philipp Sehmisch (Crush)]] | Pixel artist, mapper, programmer | Germany}}
{{dev1| [[User:Dabe|David Del Re (Dabe)]] | Story and Geography writer | USA}}
{{dev2| [[User:Doener|Doener]] | Programmer | Germany}}
{{dev1| [[User:ElvenProgrammer|Eugenio Favalli (ElvenProgrammer)]] | Project leader, programmer | Italy}}
{{dev2| [[User:Silene|Guillaume Melquiond (Silene)]] | Programmer | France}}
{{dev1| [[User:Jaxad0127|Jared Adams (Jaxad0127)]] | Content Manager Task Force member, mapper, minor programming | USA}}
{{dev2| [[User:Len|L Pabin (len)]] | Pixel artist, concept artist, mapper, and Head of Pixellation| USA}}
{{dev1| [[User:maci|maci]] | User support | Germany}}
{{dev2| [[User:hackgrid|Matt]] | Blabbering idiot | Germany}}
{{dev1| [[User:Modanung|Modanung]] | Pixel artist, concept artist | The Netherlands}}
{{dev2| [[User:Pajarico|Pajarico]] | Art, writer | Spain}}
{{dev1| [[User:the-me|Patrick MatthÃ¤i]] | Debian package maintainer | Germany}}
{{dev2| [[User:Pauan|Pauan]] | Graphics artist (painting, pixel art, interface design, etc.) | USA}}
{{dev1| [[User:Peavey|Dennis Friis (peavey)]] | Programmer | Denmark}}
{{dev2| [[User:Platyna|Platyna]] | Slackware package maintainer | Poland}}
{{dev1| [[User:Avaniel|Rogier Polak (Avaniel)]] | Programmer | The Netherlands}}
{{dev2| [[User:Rotonen|Joni Orponen (Rotonen)]] | Head of art, story and music | Finland}}
{{dev1| [[User:Trapdoor|trapdoor]] | Programmer | England}}
{{dev2| [[User:Tuxtgz|Tuxtgz]] | Web-interface Programmer | Germany}}
{{dev1| [[User:VictorSan|VictorSan]] | Programmer | Spain}}
{{dev2| [[User:Yosuhara|Yosuhara]] | Pixel artist | Slovakia}}
{{dev1| [[User:Zipon|Zipon]] | Mapper | Denmark}}
{{dev2| [[User:Irukard|Krzysztof Daszuta (Irukard)]] | Pixel artist | Poland}}
{{dev1| [[User:Kage|Chuck Miller (Kage)]] | Programmer | USA}}
{{dev2| [[User:Fate|Fate]] | Programmer (client, eAthena), Pixel artist | USA }}
|}

== Retired ==
{| border="0" cellspacing="0" cellpadding="5" width="100%" style="border-bottom: 2px solid #efdead"
! style="background:#efdead;" width="150px" align="left" | Name
! style="background:#efdead;" width="200px" align="left" | Occupation
! style="background:#efdead;" width="150px" align="left" | Location

{{dev1| [[User:Gagaofdeath|Andrej Sinicyn]] | Programmer | Germany}}
{{dev2| [[User:Bear|Bear]] | Scripter | USA}}
{{dev1| [[User:Catfish_Man|Catfish_Man]] | Mac port | USA}}
{{dev2| Chetic | Mapper | }}
{{dev1| [[User:Clef|Clef]] | Pixel artist | }}
{{dev2| [[User:Demon|Demon]] | Pixel artist | }}
{{dev1| [[User:deviexx|deviexx]] | Content manager | }}
{{dev2| [[User:Golgo|Golgo]] | Pixel artist | }}
{{dev1| Gnulia | Concept artist | }}
{{dev2| Igor Morgado (imorgado) | Tester | }}
{{dev1| [[User:Javila|Javila]] | Programmer | Brazil}}
{{dev2| [[User:JoshLangley|JoshLangley]] | Client Programmer | Australia}}
{{dev1| [[User:kindjal|kindjal]] | Programmer | France, currently Italy}}
{{dev2| [[User:Kinetic|Kinetic]] | Pixel artist | USA}}
{{dev1| Alexander Baldeck (kth5/Shura) | Programmer | }}
{{dev2| [[User:Kyokai|Kyokai]] | Game systems designer | }}
{{dev1| [[User:Maester Pixel|Maester Pixel]] | Pixel artist | USA}}
{{dev2| [[User:Magick|Magick]] | Musician | }}
{{dev1| [[User:Mkael|Mkael]] | Concept artist | Finland}}
{{dev2| [[User:Mra|Mra]] | Programmer | Germany}}
{{dev1| [[User:Neko-mon|Neko-mon]] | Pixel artist | Brazil}}
{{dev2| Neorice | Pixel artist | }}
{{dev1| [[User:Nym|nym]] | Programmer | Australia}}
{{dev2| Rodney Dawes (dobey) | Packager (install process) | }}
{{dev1| Romulo Fernandes (razor85) | Artist | }}
{{dev2| Simon Edwardsson (simonedw/simedw) | Packager (Mac) | }}
{{dev1| [[User:Sora|Sora]] | Pixel artist | Poland}}
{{dev2| [[User:Sull|Sull]] | Pixel artist and hosting | Canada}}
{{dev1| [[User:Talaroc|Talaroc]] | Pixel artist | }}
{{dev2| Ti Sing Hao | Musician | }}
{{dev1| Ultramichy | Hosting | }}
{{dev2| [[User:Usiu|Mateusz Kaduk (Usiu)]] | Programmer | Poland}}
{{dev1| Vlady | Artist | }}
{{dev2| zenogais (bitshift2002) | Programmer | }}
{{dev1| [[User:Axltrozz|Alex Argumedo (Axltrozz)]] | Pixel artist | USA, born in El Salvador}}
{{dev2| Quiche_On_A_leash | Content Management |}}
|}

Development:Git repository

2008-11-17T08:59:15Z

Exceptionfault: /* git on Windows */

This page provides some information about our switch to git (which, as of Nov 14th, has been completed). As an initial comforting note, git really isn't so complicated as some people would have you believe. A few months ago I became familiar with distributed version control by using Mercurial, and since a few weeks we're using git at work. I think we'll benefit a lot from switching to it on the long run.

== The primary repository ==

=== Initial setup ===

With Git, we'll have one repository for each project. The central repositories through which we cooperate are hosted on [http://gitorious.org/ gitorious.org]. Gitorious is a friendly website that is also open source. On Gitorious the main repository for each project is called ''mainline''. Once you click to the mainline repository, you can see several ways to clone it (the new <code>svn checkout</code>).

We've categorized all projects related to The Mana World, so you can easily see the complete [http://gitorious.org/search?q=category%3Athe-mana-world list of The Mana World projects] on Gitorious. The projects have different clone URLs for read-only or developer access. The URL for developer access is called the "push URL", since it allows you to push commits into the repository via ssh. The list below is for your convenience.

{|border="1" cellspacing="0" cellpadding="5"
! Project
! Read-only URL
! Push URL
! Atom feed
|-
| '''[http://gitorious.org/projects/tmw TMW client]'''
| git://gitorious.org/tmw/mainline.git
| git@gitorious.org:tmw/mainline.git
| [http://gitorious.org/projects/tmw/repos/mainline/logs/master/feed.atom Atom]
|-
| '''[http://gitorious.org/projects/tmwdata TMW client data]'''
| git://gitorious.org/tmwdata/mainline.git
| git@gitorious.org:tmwdata/mainline.git
| [http://gitorious.org/projects/tmwdata/repos/mainline/logs/master/feed.atom Atom]
|-
| '''[http://gitorious.org/projects/tmwserv TMW server]'''
| git://gitorious.org/tmwserv/mainline.git
| git@gitorious.org:tmwserv/mainline.git
| [http://gitorious.org/projects/tmwserv/repos/mainline/logs/master/feed.atom Atom]
|-
| '''[http://gitorious.org/projects/tmwweb TMW web frontend]'''
| git://gitorious.org/tmwweb/mainline.git
| git@gitorious.org:tmwweb/mainline.git
| [http://gitorious.org/projects/tmwweb/repos/mainline/logs/master/feed.atom Atom]
|-
| '''[http://gitorious.org/projects/tmw-website TMW website]'''
| git://gitorious.org/tmw-website/mainline.git
| git@gitorious.org:tmw-website/mainline.git
| [http://gitorious.org/projects/tmw-website/repos/mainline/logs/master/feed.atom Atom]
|-
| '''[http://gitorious.org/projects/tmw-eathena eAthena server]'''
| git://gitorious.org/tmw-eathena/mainline.git
| git@gitorious.org:tmw-eathena/mainline.git
| [http://gitorious.org/projects/tmw-eathena/repos/mainline/logs/master/feed.atom Atom]
|-
| '''[http://gitorious.org/projects/tmw-eathena-data eAthena data]'''
| git://gitorious.org/tmw-eathena-data/mainline.git
| git@gitorious.org:tmw-eathena-data/mainline.git
| [http://gitorious.org/projects/tmw-eathena-data/repos/mainline/logs/master/feed.atom Atom]
|}

Git uses ssh's private/public key authentication for identifying committers. For development purposes you should clone the "push url", but this requires that you have:

# Signed up to gitorious.org
# Generated a private/public ssh keypair (if you haven't got this already)
# Filled in your public key in your account details on Gitorious

=== Cloning ===

If you simply <code>git clone</code> the URL without any additional arguments, it will create the repository in a directory called "mainline". This is generally not what you want. Hence, after the clone url, you should pass the name of the directory you want to have created (just like with Subversion):

$ git clone <clone_url> project

If you want to have all projects in one place, you probably want to do something like this:

$ mkdir tmw
$ cd tmw
$ git clone git@gitorious.org:tmwserv/mainline.git tmwserv
$ git clone git@gitorious.org:tmw-eathena-data/mainline.git eathena-data
etc.
or for all of them in one go (after the cd tmw step):
$ for repo in tmw tmwdata tmwserv tmwweb tmw-website tmw-eathena tmw-eathena-data ; do git clone git://gitorious.org/${repo}/mainline.git $repo ; done

The way Gitorious works, we can't have one top-level "tmw" project under which we put all these repositories, since they're really separate projects. Gitorious allows multiple repositories for each project, but they are clones of each other (you can only create new ones by cloning existing ones). This allows anybody (not just the development team) to make clones and start hacking on them. Changes can easily be merged from one one repository to another. So instead of all the inconvenience with TMW forks we had in the past, now comes the time to encourage people to clone!

=== The TMW 0.0.x client ===

The 0.0 client may seem a bit hidden, but it exists as a branch just like with Subversion. To continue working on this client you need to checkout the branch and create your own local branch to work on it. This goes as follows:

$ git clone git@gitorious.org:tmw/mainline.git tmw
$ cd tmw
$ git checkout -b 0.0 origin/0.0

With the last command, a new local branch called 0.0 is created. Since it was based on the 0.0 branch in origin (the repository on Gitorious), it will automatically track this remote branch. This means that when you do <code>git pull</code>, git will attempt to automatically merge the latest changes available from this branch with your local 0.0 branch.

== Working with git ==

=== Commit ===

From now on, a commit is something you do locally. Others won't see your change on Gitorious unless you push it there. You'll notice committing is very fast, and you can commit multiple times before you decide to push. You can also make corrections to your last commit.

Before you start committing, it is important to identify yourself to git, so that it can include the correct authorship information with your commit. You are no longer identified with a username, as was the case with Subversion. You can read exactly how to do this, as well as other useful information geared towards people switching from Subversion, on this page:

* '''Git - SVN Crash Course:''' http://git.or.cz/course/svn.html

=== Pushing ===

Once you have committed some stuff, you can push these to the repository on Gitorious using <code>git push</code>. This works since by default the push command pushes to a ''remote'' called ''origin'', and this remote is automatically set up when you clone. However, the push will fail if there have been new commits on the remote repository. In that case, you'll first have to pull in these changes (just like with Subversion, however Subversion allowed this as long as the same files weren't touched, git doesn't).

=== Pulling ===

When you want to get the latest changes from the repository on Gitorious, you generally use <code>git pull</code>. However, note that this command does not work when you have local changes. Also, when you have local commits, the pull command will generate a merge commit (and before that you may have to resolve some conflicts).

If you don't want to create merge commits, but would rather stack your local commits on top of any incoming commits, you should use <code>git pull --rebase</code>. This ''rebases'' your local commits on top of the incoming ones. You should never do this when you have pushed these commits elsewhere, so only do it when you are sure the commits are only on your machine.

If you have local changes and want to update your checkout, then there are several options:

* You commit your local changes, and do a pull, optionally with --rebase.
* Or you use <code>git stash</code> to place your local changes on a "hidden" stash. Then, after pulling, you apply your changes again with <code>git stash apply</code>.
* Or you create a patch of your local changes that you apply again after the pull. This approach sometimes makes sense, but I would say in general it's the more clumsy way to go. There are git commands that help you with this though.

=== Resolving conflicts ===

Rather similar to Subversion. When there are conflicts, a merge or a rebase will add conflict markers into files. Use <code>git status</code> to see which files remain in conflict and use <code>git add</code> on files to mark them as resolved. When you did a merge and you have resolved all conflicts, you commit. When you were doing a rebase of several commits, you do <code>git rebase --continue</code> instead.

=== Good to know ===

Git has several useful commands to figure out the current state of your repository, your files and what recently changed. Below is a non-exhaustive list of commands that are useful to know:

* '''git branch''': Without any parameters, this command should you your local branches, and indicates which branch you're currently on.
* '''git whatchanged''': This shows something similar to the ChangeLog file. Recent commits on your current branch with a list of the files the touched.
* '''git status''': This shows all kind of things about your current checkout: which files changed, untracked (unknown) files, added or removed files, files that have conflicts (during merge), etc. It also shows the status of your index, which is what git will commit once you do <code>git commit</code>. If you're new to git I would recommend to wait a bit with learning how to use the index, but not to avoid it forever.

There are also additional applications that help you with using git:

* '''gitk''': A simple but effective tool that visualizes the history and some of your current state. Run with <code>--all</code> to have it show all branches, otherwise it will just show stuff relevant to your current branch.
* '''tig''': A textual interface, rather similar to an email reader.
* '''git gui''': A gui tool like gitk which helps you prepare and perform your commits. Also makes it easier to understand the index concept.

=== git on Windows ===

When using git on Windows you might use [http://code.google.com/p/msysgit/ msysgit]. If you notice, that all files seem to have changed after doing a fresh clone, this discussion should help you: [http://code.google.com/p/msysgit/issues/detail?id=83]. Find your global git configuration file, normally somewhere under c:\programs\git\etc\gitconfig, and set the parameter ''autocrlf'' in the ''core'' section to ''false''. Then remove you last clone and do a nwe fresh clone.

Development:Git repository

2008-11-17T08:56:58Z

Exceptionfault: added lineending issue under msysgit

Archive:Database specifications

2008-11-07T07:07:16Z

Exceptionfault: Updated graphical model, added postal system tables

{{Category_programming}}

== SQL table specifications ==

[[Image:Database specification proposal.png|thumb|200px|The current database model.]]

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)
*** Thanks for taking out the skills from this table. However, I disagree about generalizing stuff on the level of attributes, unless there would be any plan of putting infrastructure in place to make this possible. For now we can't even finish a server for The Mana World within years, so please don't try to build a server that supports any online RPG just yet. I only suggested we take out the skills because it would be completely unmanageable as part of this table, but the list of attribute is not expected to change for now. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:55, 22 September 2008 (CEST)

=== Character attributes ===

This table is intended to store skills and experiences of each character.

{{beginsqltable|tmw_char_skills}}
{{sqltablerow|char_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|skill_id | INTEGER | NOT NULL | (skills.xml) | reference to the skill }}
{{sqltablerow|exp | INTEGER | NOT NULL | | current experience of the char in this skill }}
{{endsqltable}}

==== Concerns ====

* I'm not sure, if an INTEGER does the job for column exp? --[[User:Exceptionfault|Exceptionfault]] 14:40, 19 September 2008 (CEST)
** What is the range? I would expect it to be enough. --[[User:BjÃ¸rn|BjÃ¸rn]] 15:56, 22 September 2008 (CEST)
*** Hereâ€™s some basic info:
***: '''MySQL:''' INTEGER (INT) may be signed or unsigned and is 4 bytes large, MySQL also have 1, 2, 3 and 8 byte integer variants (TINYINT, SMALLINT, MEDIUMINT and BIGINT) [http://dev.mysql.com/doc/refman/6.0/en/numeric-types.html]
***: '''PostgreSQL:''' INTEGER (INT, INT4) is signed and 4 bytes large, PostgreSQL also have 2 and 8 byte integer variants (SMALLINT and BIGINT) [http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html]
***: '''SQLite:''' INTEGER is signed and size is either 1, 2, 3, 4, 6 or 8 bytes depending on the size of the value [http://sqlite.org/datatype3.html]
***: So, the largest integer type supported should be of 8 byte size (signed): from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, while the â€œstandardâ€ (of the SQL variants above) signed 4 byte integer ranges from -2,147,483,648 to +2,147,483,647. --[[User:Kess|kess]] 21:18, 22 September 2008 (CEST)
*** Although we plan to have no fixed level cap I considered a skill level of about 100 to be the highest reasonable value a player can archieve. Level 100 requires an exp sum of 10 million with the current exp formula (levelÂ³ * 10) which fits into a 4 byte integer easily. We won't get in trouble with 32bit signed integers until the players get near skill level 600 (2.16 billion). The server and the netcode also use 32 bit integers internally.

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

=== Guild memberships ===

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== World state ===

The table ''tmw_world_state'' is used to store persistent informations about the world or individual maps.

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| value | TEXT | NULL | | string value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====

* The column ''state_name'' forms the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable is specific for a map.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

==== Comments ====

* It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple ''state_name''s with different ''map_id''s. So we set a ''state_name'' to unique and use ''map_id'' as notifier if this variable is globally used or just locally for a specific map. --[[User:Exceptionfault|Exceptionfault]] 11:05, 15 September 2008 (CEST)
** This table is ready for usage, commited as rev. 4629 --[[User:Exceptionfault|Exceptionfault]] 13:51, 15 September 2008 (CEST)

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Auctions ===

Inspired by the ManaBay auction system developed by Qoal, it is planned to build a trading platform for the whole community of players using item auctions.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| auction_id | INTEGER | PRIMARY KEY | | unique id of an auction }}
{{sqltablerow| auction_state | INTEGER | NOT NULL | | current state of the auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters (id) | owner of the auction }}
{{sqltablerow| itemclass_id | INTEGER | NOT NULL | items.xml | id of the item }}
{{sqltablerow| amount | INTEGER | NOT NULL | | amount of items to trade }}
{{sqltablerow| start_time | INTEGER | NOT NULL | | date of creation or start of auction }}
{{sqltablerow| end_time | INTEGER | NOT NULL | | end of auction (unixtimestamp) }}
{{sqltablerow| start_price | INTEGER | NOT NULL | | minimum price for the first bid }}
{{sqltablerow| min_price | INTEGER | NULL | | minimum price for the final bid to succeed }}
{{sqltablerow| buyout_price | INTEGER | NULL | | price for direct buy of bidded item }}
{{sqltablerow| description | TEXT | NULL | | optional description of an auction }}
{{endsqltable}}

==== Annotations ====

* auction_state can have the following values:
** 0 = The auction is published and ready for bidders
** 1 = The auction has finished and closed
* start_time contains the creation date of the auction. Format: Unixtimestamp

==== Bids on Auctions ====

This table stores bids made by characters to a specific auction.

{{beginsqltable|tmw_auctions}}
{{sqltablerow| bid_id | INTEGER | PRIMARY KEY | | unique id of a bid }}
{{sqltablerow| auction_id | INTEGER | NOT NULL | tmw_auctions | unique id of an auction }}
{{sqltablerow| char_id | INTEGER | NOT NULL | tmw_characters | owner of the bid }}
{{sqltablerow| bid_time | INTEGER | NOT NULL | | time of the bid, used for sorting bids }}
{{sqltablerow| bid_price | INTEGER | NOT NULL | | price bidded by the character }}
{{endsqltable}}

===== Annotations =====

* bid_time holds the date and time of the bid as Unixtimestamp. Regarding 2 bids can be placed at the same second, the higher bid_price is relevant. If the bid_price is also equal, the smallest bid_id counts, as this is the record first inserted into the database.

=== Postal system ===

==== Letters ====

{{beginsqltable|tmw_post}}
{{sqltablerow| letter_id | INTEGER | PRIMARY KEY | | unique id of the letter }}
{{sqltablerow| sender_id | INTEGER | NOT NULL | tmw_characters | sending character of the letter }}
{{sqltablerow| receiver_id | INTEGER | NOT NULL | tmw_characters | receiving character of the letter }}
{{sqltablerow| letter_type | INTEGER | NOT NULL | | type of the letter (unused atm) }}
{{sqltablerow| expiration_date | INTEGER | NOT NULL | | date and time of expiration }}
{{sqltablerow| sending_date | INTEGER | NOT NULL | | date and time of sending }}
{{sqltablerow| letter_text | TEXT | NULL | | text }}
{{endsqltable}}

==== Item attachments ====

{{beginsqltable|tmw_post_attachments}}
{{sqltablerow| attachment_id | INTEGER | PRIMARY KEY | | unique id of the attachment }}
{{sqltablerow| letter_id | INTEGER | NOT NULL | tmw_post | reference to the letter }}
{{sqltablerow| item_id | INTEGER | NOT NULL | tmw_item_instances | attached itema }}
{{endsqltable}}

== Reference ==

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!

* [http://themanaworld.svn.sourceforge.net/viewvc/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?view=markup View dalstoragesql.hpp]
* [http://themanaworld.svn.sourceforge.net/viewvc/*checkout*/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?revision=4024 Download dalstoragesql.hpp (plain text)]

2008-09-18T11:03:56Z

Exceptionfault:

Archive:Database Installation

2008-09-17T09:35:22Z

Exceptionfault: /* Settings up SQLite */

Archive:Database Installation

2008-09-17T09:04:22Z

Exceptionfault:

'''This page is currently WIP and should describe how to set up the database for a new installation of tmwserv. This page should be moved to [[Setting up a server]] when finished.'''

----

Before you can set up your own server you have to decide wich database backend you want to use. Currently, the server supports three different types of database backends:
* mySQL
* SQLite
* PostgreSQL

== Settings up mySQL ==

=== Requirements ===

To run tmwserv with mySQL you should use a mySQL version >= 5.0.*. It was tested sucessfully with 5.0.51a.
Also you have to enable the [http://dev.mysql.com/doc/refman/5.0/en/innodb.html InnoDB] storage engine as tmwserv makes use of the relational and transactional features.

=== Creating a database and a user ===

This chapter assumes, that you still have little experience working with mysql, so it does not describe how to install mysql itself. The first step to get tmwserv running under mySQL would be the creation of a database and a user. As you can easily do this with graphical frontends like [http://www.phpmyadmin.net phpMyAdmin], we will give you a handy script for doing that from command line. Don't forget to replace the placeholders with appropriate values for your environment.

For the following commands you have to be logged in as a database administrator, preferable ''root''.

-- create a user called tmw (modify this as you whish)
-- the 'localhost' says, that our new user is only allowed from our local machine
CREATE USER 'tmw'@'localhost' IDENTIFIED BY '<insert your password preferred here>';

-- allow the user to connect to the server without any resource limitations
GRANT USAGE ON * . * TO 'tmw'@'localhost' IDENTIFIED BY '<again your pwd>'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

-- create a new database called 'tmw' but you can name it just as you like
CREATE DATABASE IF NOT EXISTS `tmw` ;

-- give our new user full privileges on our new created database
GRANT ALL PRIVILEGES ON `tmw` . * TO 'tmw'@'localhost';

Summarized, now we have a new database called ''tmw'', a new database user ''tmw'' that has full rights in his database, and a restriction, that he is only able to connect from localhost.

=== Create database tables ===

If our new database user is ready for use, we have to create all necessary tables. The installation of tmwserv provides you with a ready to use database script. You can find this script under ''./src/sql/mysql/createTables.sql'' To run this script connect as user tmw with your database.

mysql --host localhost --user tmw --password --database tmw --no-beep

After typing your password you should be connected to the database. The command ''\.'' runs a database script.

\. <path to the script>/createTables.sql
-- e.g.
\. ~/tmwserv/src/sql/mysql/createTables.sql

=== Configuring tmwserv to use the database ===

'''TODO'''

== Settings up SQLite ==

=== Requirements ===

Tmwserv has been tested with SQLite version 3.5.8

=== Preparation of database file ===

'''TODO'''

=== Configuring tmwserv to use the database ===

== Settings up PostgreSQL ==

'''TODO'''

Archive:Database Installation

2008-09-16T21:29:47Z

Exceptionfault:

Archive:Database specifications

2008-09-15T11:51:54Z

Exceptionfault:

{{Category_programming}}

== SQL table specifications ==

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{sqltablerow|unarmed_exp | INTEGER | NOT NULL | | Experience: unarmed }}
{{sqltablerow|knife_exp | INTEGER | NOT NULL | | Experience: knifes }}
{{sqltablerow|sword_exp | INTEGER | NOT NULL | | Experience: swords }}
{{sqltablerow|polearm_exp | INTEGER | NOT NULL | | Experience: polearms }}
{{sqltablerow|staff_exp | INTEGER | NOT NULL | | Experience: staffs }}
{{sqltablerow|whip_exp | INTEGER | NOT NULL | | Experience: whips }}
{{sqltablerow|bow_exp | INTEGER | NOT NULL | | Experience: bows }}
{{sqltablerow|shoot_exp | INTEGER | NOT NULL | | Experience: shooting }}
{{sqltablerow|mace_exp | INTEGER | NOT NULL | | Experience: maces }}
{{sqltablerow|axe_exp | INTEGER | NOT NULL | | Experience: axes }}
{{sqltablerow|thrown_exp | INTEGER | NOT NULL | | Experience: throwing }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

=== Guild memberships ===

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== World state ===

The table ''tmw_world_state'' is used to store persistent informations about the world or individual maps.

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| value | TEXT | NULL | | string value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====

* The column ''state_name'' forms the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable is specific for a map.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

==== Comments ====

* It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple ''state_name''s with different ''map_id''s. So we set a ''state_name'' to unique and use ''map_id'' as notifier if this variable is globally used or just locally for a specific map. --[[User:Exceptionfault|Exceptionfault]] 11:05, 15 September 2008 (CEST)
** This table is ready for usage, commited as rev. 4629 --[[User:Exceptionfault|Exceptionfault]] 13:51, 15 September 2008 (CEST)

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

== Reference ==

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!

* [http://themanaworld.svn.sourceforge.net/viewvc/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?view=markup View dalstoragesql.hpp]
* [http://themanaworld.svn.sourceforge.net/viewvc/*checkout*/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?revision=4024 Download dalstoragesql.hpp (plain text)]

== Suggestions and Todos ==

=== Character attributes ===

This table is intended to store skills and experiences of each character.

{{beginsqltable|tmw_char_skills}}
{{sqltablerow|char_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|skill_id | INTEGER | NOT NULL | (skills.xml) | reference to the skill }}
{{sqltablerow|exp | INTEGER | NOT NULL | | current experience of the char in this skill }}
{{sqltablerow|lastusage | INTEGER | NOT NULL | | date and time of the last usage of this skill }}
{{endsqltable}}

==== Comments ====

* I'am not sure, if an INTEGER does the job for column exp?
* ''lastusage'' gives us the possibility to implement something like: forget skills. This could be interesting for magical skills that have to be trained constantly to not forget them...
* TODO: remove the skill columns from tmw_characters and let DALStorage use the new table
--[[User:Exceptionfault|Exceptionfault]] 13:51, 15 September 2008 (CEST)

Archive:Database specifications

2008-09-15T09:32:04Z

Exceptionfault: /* World state */ removed different value types as current DAL only supports strings

{{Category_programming}}

== SQL table specifications ==

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{sqltablerow|unarmed_exp | INTEGER | NOT NULL | | Experience: unarmed }}
{{sqltablerow|knife_exp | INTEGER | NOT NULL | | Experience: knifes }}
{{sqltablerow|sword_exp | INTEGER | NOT NULL | | Experience: swords }}
{{sqltablerow|polearm_exp | INTEGER | NOT NULL | | Experience: polearms }}
{{sqltablerow|staff_exp | INTEGER | NOT NULL | | Experience: staffs }}
{{sqltablerow|whip_exp | INTEGER | NOT NULL | | Experience: whips }}
{{sqltablerow|bow_exp | INTEGER | NOT NULL | | Experience: bows }}
{{sqltablerow|shoot_exp | INTEGER | NOT NULL | | Experience: shooting }}
{{sqltablerow|mace_exp | INTEGER | NOT NULL | | Experience: maces }}
{{sqltablerow|axe_exp | INTEGER | NOT NULL | | Experience: axes }}
{{sqltablerow|thrown_exp | INTEGER | NOT NULL | | Experience: throwing }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

=== Guild memberships ===

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

== Reference ==

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!

* [http://themanaworld.svn.sourceforge.net/viewvc/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?view=markup View dalstoragesql.hpp]
* [http://themanaworld.svn.sourceforge.net/viewvc/*checkout*/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?revision=4024 Download dalstoragesql.hpp (plain text)]

== Suggestions and Todos ==

=== World state ===

As noted above, we need a table to store custom world and map state variables. I suggest the following structure:

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| value | TEXT | NULL | | string value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====

* The column ''state_name'' forms the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable is specific for a map.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

==== Comments ====

* It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple ''state_name''s with different ''map_id''s. So we set a ''state_name'' to unique and use ''map_id'' as notifier if this variable is globally used or just locally for a specific map. --[[User:Exceptionfault|Exceptionfault]] 11:05, 15 September 2008 (CEST)

Archive:Database specifications

2008-09-15T09:05:46Z

Exceptionfault: /* World state */ modified constraints of tmw_world_state table

{{Category_programming}}

== SQL table specifications ==

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{sqltablerow|unarmed_exp | INTEGER | NOT NULL | | Experience: unarmed }}
{{sqltablerow|knife_exp | INTEGER | NOT NULL | | Experience: knifes }}
{{sqltablerow|sword_exp | INTEGER | NOT NULL | | Experience: swords }}
{{sqltablerow|polearm_exp | INTEGER | NOT NULL | | Experience: polearms }}
{{sqltablerow|staff_exp | INTEGER | NOT NULL | | Experience: staffs }}
{{sqltablerow|whip_exp | INTEGER | NOT NULL | | Experience: whips }}
{{sqltablerow|bow_exp | INTEGER | NOT NULL | | Experience: bows }}
{{sqltablerow|shoot_exp | INTEGER | NOT NULL | | Experience: shooting }}
{{sqltablerow|mace_exp | INTEGER | NOT NULL | | Experience: maces }}
{{sqltablerow|axe_exp | INTEGER | NOT NULL | | Experience: axes }}
{{sqltablerow|thrown_exp | INTEGER | NOT NULL | | Experience: throwing }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

=== Guild memberships ===

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

== Reference ==

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!

* [http://themanaworld.svn.sourceforge.net/viewvc/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?view=markup View dalstoragesql.hpp]
* [http://themanaworld.svn.sourceforge.net/viewvc/*checkout*/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?revision=4024 Download dalstoragesql.hpp (plain text)]

== Suggestions and Todos ==

=== World state ===

As noted above, we need a table to store custom world and map state variables. I suggest the following structure:

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| string_value | TEXT | NULL | | string value of the state }}
{{sqltablerow| number_value | INTEGER | NULL | | numeric value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====

* The column ''state_name'' forms the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable is specific for a map.
* To be able to store strings and numbers (including unixtimestamps), there are two value columns, each with a correct datatype for optimized storage in the database system.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

==== Comments ====

* It seems that SQLite doesn't support composite primary keys (span PK over multiple columns). Therefore it is not possible to allow multiple ''state_name''s with different ''map_id''s. So we set a ''state_name'' to unique and use ''map_id'' as notifier if this variable is globally used or just locally for a specific map. --[[User:Exceptionfault|Exceptionfault]] 11:05, 15 September 2008 (CEST)

Archive:Database specifications

2008-09-15T07:32:02Z

Exceptionfault: Added Todo: tmw_world_state

{{Category_programming}}

== SQL table specifications ==

=== User accounts ===

{{beginsqltable|tmw_accounts}}
{{sqltablerow| id | INTEGER | PRIMARY KEY | | unique id of an account}} }}
{{sqltablerow|username | TEXT | NOT NULL UNIQUE | | username / loginname of an user }}
{{sqltablerow|password | TEXT | NOT NULL | | password of an user (sha256 encoded) }}
{{sqltablerow|email | TEXT | NOT NULL | | email (sha256 encoded, only for pwd recovery) }}
{{sqltablerow|level | INTEGER | NOT NULL | | numeric level of the user account }}
{{sqltablerow|banned | INTEGER | NOT NULL | | unixtimestamp until when the account is banned }}
{{sqltablerow|registration | INTEGER | NOT NULL | | unixtimestamp of account registration }}
{{sqltablerow|lastlogin | INTEGER | NOT NULL | | unixtimestamp of last login with client }}
{{endsqltable}}

==== Details ====
* email
** The email is stored as a one-way sha256 hash value. This ensures, that the email address a user enters cannot be used to send spam mails. It is only used to validate the mailaddress during password recovery procedure.
* level
** describes the user rights in the game (10 = normal user, 50 = gm, 99 = administrator)

=== Characters ===

{{beginsqltable|tmw_characters}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the character }}
{{sqltablerow|user_id | INTEGER | NOT NULL | tmw_accounts.id | id of the owner }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | name of the character }}
{{sqltablerow|gender | INTEGER | NOT NULL | | gender of the character (0/1) }}
{{sqltablerow|hair_style | INTEGER | NOT NULL | | id of the hair sprite }}
{{sqltablerow|hair_color | INTEGER | NOT NULL | | id of the hair color }}
{{sqltablerow|level | INTEGER | NOT NULL | | experience level of the character }}
{{sqltablerow|char_pts | INTEGER | NOT NULL | | available points to raise attributes }}
{{sqltablerow|correct_pts | INTEGER | NOT NULL | | available points to lower attributes and regain char_pts}}
{{sqltablerow|money | INTEGER | NOT NULL | | GP of the character }}
{{sqltablerow|x | INTEGER | NOT NULL | | x position of the character on the map }}
{{sqltablerow|y | INTEGER | NOT NULL | | y position of the character on the map }}
{{sqltablerow|map_id | INTEGER | NOT NULL | tmw_maps.id | id of the current map the character is located }}
{{sqltablerow|str | INTEGER | NOT NULL | | strength of the character }}
{{sqltablerow|agi | INTEGER | NOT NULL | | agility of the character }}
{{sqltablerow|dex | INTEGER | NOT NULL | | dexternity of the character }}
{{sqltablerow|vit | INTEGER | NOT NULL | | vitality of the character }}
{{sqltablerow|int | INTEGER | NOT NULL | | intelligence of the character }}
{{sqltablerow|will | INTEGER | NOT NULL | | willpower of the character }}
{{sqltablerow|unarmed_exp | INTEGER | NOT NULL | | Experience: unarmed }}
{{sqltablerow|knife_exp | INTEGER | NOT NULL | | Experience: knifes }}
{{sqltablerow|sword_exp | INTEGER | NOT NULL | | Experience: swords }}
{{sqltablerow|polearm_exp | INTEGER | NOT NULL | | Experience: polearms }}
{{sqltablerow|staff_exp | INTEGER | NOT NULL | | Experience: staffs }}
{{sqltablerow|whip_exp | INTEGER | NOT NULL | | Experience: whips }}
{{sqltablerow|bow_exp | INTEGER | NOT NULL | | Experience: bows }}
{{sqltablerow|shoot_exp | INTEGER | NOT NULL | | Experience: shooting }}
{{sqltablerow|mace_exp | INTEGER | NOT NULL | | Experience: maces }}
{{sqltablerow|axe_exp | INTEGER | NOT NULL | | Experience: axes }}
{{sqltablerow|thrown_exp | INTEGER | NOT NULL | | Experience: throwing }}
{{endsqltable}}

==== Concerns ====

* The way experience is part of this table really won't scale and isn't flexible in any way. It's currently already way too many variables in one table row, and these are just the weapon skills. So I think we should really have a separate table for storing skill levels similar to the character inventory table below. So something that has { character_id, skill_id, experience }. The <code>skill_id</code> should point to a <code>skills.xml</code> file which describes (and categorizes) each skill. In that way we'll be able to easily change the set of skills and their names later. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:09, 12 September 2008 (CEST)
** I think the same should be done with the attributes (str .. will). In theory almost every attribute in this table could be handled that way, it might look like a mess, but would be really friendly in customizing the gameplay elements. --[[User:Kess|kess]] 19:54, 12 September 2008 (CEST)
** I agree with that completely as this will give us more flexibility and a much more relational database design. I've extended the "DAL improvements" task in mantis: #424 --[[User:Exceptionfault|Exceptionfault]] 15:50, 14 September 2008 (CEST)

=== Character Inventory ===

{{beginsqltable|tmw_inventories}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the item }}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | id of the owning character }}
{{sqltablerow|slot | INTEGER | NOT NULL | | inventory slot where the item is equipped }}
{{sqltablerow|class_id | INTEGER | NOT NULL | | type of the item, see items.xml file of tmwserv}}
{{sqltablerow|amount | INTEGER | NOT NULL | | amount of items per slot }}
{{endsqltable}}

==== Comments ====
* While I havenâ€™t looked how this table is used (my C++ knowledge is rather basic) it seems quite rigid and unflexible (as BjÃ¸rn noted above for the '''tmw_characters''' table). I think it would be nicer to have something like {''owner_id'' FK, ''slot'', ''item_id'' FK, ''amount'', PK owner_id + slot}, where class_id shouldnâ€™t directly be needed in this table. --[[User:Kess|kess]] 19:49, 12 September 2008 (CEST)
* Concerning new features like houses, bank accounts, chests or similar, i think the design of this table needs some more roundtrips. You will need a column which indicates if the item is carried by the character or stored in a chest or in a house; as it makes no sense to have a table for every possible storage type or location. Another point is, that items should be more individualizable (is this a real word? :)). Think about custom colored shirts. So we will need at least one additional table to store individual attributes of items. --[[User:Exceptionfault|Exceptionfault]] 16:02, 14 September 2008 (CEST)
** We want all equipment to be completely individual with completely unique stats. --[[User:Crush2|Crush2]] 16:13, 14 September 2008 (CEST)
*** Good to know, so I will extend the DAL improvement task in mantis. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

=== Guilds ===

{{beginsqltable|tmw_guilds}}
{{sqltablerow|id | INTEGER | PRIMARY KEY | | unique id of the guid }}
{{sqltablerow|name | TEXT | NOT NULL UNIQUE | | unique name of the guild}}
{{endsqltable}}

=== Guild memberships ===

The table ''tmw_guild_members'' stores informations which character is member in which guild and which rights does he has.

{{beginsqltable|tmw_guild_members}}
{{sqltablerow|guild_id | INTEGER | NOT NULL | tmw_guilds.id | reference to the guild }}
{{sqltablerow|member_id | INTEGER | NOT NULL | tmw_characters.id | reference to the characters }}
{{sqltablerow|rights | INTEGER | NOT NULL | | id showing the rights a character has in this guild (member, admin...)}}
{{endsqltable}}

=== Quest states ===

This table is used to store states of quests per character, e.g. if a character has just finished a quest or is currently at the second part of the long journey...

{{beginsqltable|tmw_quests}}
{{sqltablerow|owner_id | INTEGER | NOT NULL | tmw_characters.id | reference to the character }}
{{sqltablerow|name | TEXT | NOT NULL | | name of the quest variable }}
{{sqltablerow|value | TEXT | NOT NULL | | current value of the quest variable}}
{{endsqltable}}

==== Comments ====

* Unless this table is adapted so that it stores the state of a particular quest, this one should have a name which makes it move obvious that it's storing custom values for ''characters''. I consider that different from quests variables, which I would expect to be scoped to a certain quest (global quest variables) or quest instance (local quest variables). --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* We might want to have a similar table to this to store custom values for item instances, and also one for custom world-state variables. --[[User:BjÃ¸rn|BjÃ¸rn]] 18:16, 12 September 2008 (CEST)

* I always wondered why Silene used the terminology "Quest" for what is basically a system to store/querry persistent character-bound integer variables which can be used for countless purposes, not just quests. I think we should rename this whole system to "character variable" in the database, server source and script bindings. --[[User:Crush2|Crush2]] 23:28, 12 September 2008 (CEST)

* I agree with BjÃ¸rn concerning the additional tables for custom item values and custom world-state values. Although the name "tmw_quests" is a little bit confusing and should be renamed, I suggest keeping a table as such to store only quest states of characters and not to mix up with other persistent states. Instead we should provide a much cleaner documentation about the available quests and their persistent states that are possible. I envision something like a questbook in game, where each player can have a look at his finished quests and currently open quests, maybe with hints on the minimap, a todo list .... Besides that, we should be aware of dividing quests into "per-character", "per-party" or global world quests. --[[User:Exceptionfault|Exceptionfault]] 16:20, 14 September 2008 (CEST)

== Reference ==

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!

* [http://themanaworld.svn.sourceforge.net/viewvc/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?view=markup View dalstoragesql.hpp]
* [http://themanaworld.svn.sourceforge.net/viewvc/*checkout*/themanaworld/tmwserv/trunk/src/account-server/dalstoragesql.hpp?revision=4024 Download dalstoragesql.hpp (plain text)]

== Suggestions and Todos ==

=== World state ===

As noted above, we need a table to store custom world and map state variables. I suggest the following structure:

{{beginsqltable|tmw_world_state}}
{{sqltablerow| state_name | TEXT | PRIMARY KEY | | unique name of a state variable }}
{{sqltablerow| map_id | INTEGER | PRIMARY KEY NULL | tmw_maps.id | reference to a map, see details }}
{{sqltablerow| string_value | TEXT | NULL | | string value of the state }}
{{sqltablerow| number_value | INTEGER | NULL | | numeric value of the state }}
{{sqltablerow| moddate | INTEGER | NOT NULL | | date and time of the last modification }}
{{endsqltable}}

==== Details ====
* The column ''state_name'' and ''map_id'' form the primary key of the table. If ''map_id'' is null, the variable is used globally in the world, otherwise the variable can have a different value per map.
* To be able to store strings and numbers (including unixtimestamps), there are two value columns, each with a correct datatype for optimized storage in the database system.
* ''moddate'' is for informational use only as it contains the unixtimestamp of the last modification.

Archive:Database specifications

2008-09-14T14:20:17Z

Exceptionfault: /* Comments */

Archive:Database specifications

2008-09-14T14:20:05Z

Exceptionfault: /* Comments */

Archive:Database specifications

2008-09-14T14:02:37Z

Exceptionfault: /* Comments */

Archive:Database specifications

2008-09-14T13:50:46Z

Exceptionfault: /* Concerns */

Archive:Database specifications

2008-09-12T13:54:02Z

Exceptionfault: updated tablespecs to use sqltable templates