Merge branch 'master' of git@gitorious.org:tmwserv/mainline

5 files changed, 245 insertions, 15 deletions

diff --git a/src/Makefile.am b/src/Makefile.am
index 47ce13c8..37a36d38 100644
--- a/src/Makefile.am
+++ b/src/Makefile.am
@@ -90,8 +90,6 @@ tmwserv_game_SOURCES = \
 	game-server/accountconnection.cpp \
 	game-server/actor.hpp \
 	game-server/actor.cpp \
-	game-server/attackzone.hpp \
-	game-server/attackzone.cpp \
 	game-server/being.hpp \
 	game-server/being.cpp \
 	game-server/buysell.hpp \
diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp
index d27413d9..7e27ae50 100644
--- a/src/account-server/dalstorage.cpp
+++ b/src/account-server/dalstorage.cpp
@@ -41,7 +41,7 @@
 
 // defines the supported db version
 #define DB_VERSION_PARAMETER "database_version"
-#define SUPPORTED_DB_VERSION "3"
+#define SUPPORTED_DB_VERSION "5"
 
 
 /**
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
index 1831c45a..152d16fc 100644
--- a/src/sql/sqlite/createTables.sql
+++ b/src/sql/sqlite/createTables.sql
@@ -1,18 +1,45 @@
+--
+-- The Mana World Server
+-- Copyright 2009 The Mana World Development Team
+--
+-- This file is part of The Mana World.
+--
+-- The Mana World  is free software; you can redistribute  it and/or modify it
+-- under the terms of the GNU General  Public License as published by the Free
+-- Software Foundation; either version 2 of the License, or any later version.
+--
+-- The Mana  World is  distributed in  the hope  that it  will be  useful, but
+-- WITHOUT ANY WARRANTY; without even  the implied warranty of MERCHANTABILITY
+-- or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
+-- more details.
+--
+-- You should  have received a  copy of the  GNU General Public  License along
+-- with The Mana  World; if not, write to the  Free Software Foundation, Inc.,
+-- 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+--
+
+-----------------------------------------------------------------------------
+-- Tables
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_accounts
 (
-   id           INTEGER     PRIMARY KEY,
-   username     TEXT        NOT NULL UNIQUE,
-   password     TEXT        NOT NULL,
-   email        TEXT        NOT NULL,
-   level        INTEGER     NOT NULL,
-   banned       INTEGER     NOT NULL,
-   registration INTEGER     NOT NULL,
-   lastlogin    INTEGER     NOT NULL
+   id            INTEGER     PRIMARY KEY,
+   username      TEXT        NOT NULL UNIQUE,
+   password      TEXT        NOT NULL,
+   email         TEXT        NOT NULL,
+   level         INTEGER     NOT NULL,
+   banned        INTEGER     NOT NULL,
+   registration  INTEGER     NOT NULL,
+   lastlogin     INTEGER     NOT NULL,
+   authorization TEXT            NULL,
+   expiration    INTEGER         NULL
 );
 
 CREATE UNIQUE INDEX tmw_accounts_username ON tmw_accounts ( username );
 CREATE UNIQUE INDEX tmw_accounts_email    ON tmw_accounts ( email );
 
+-----------------------------------------------------------------------------
 
 CREATE TABLE tmw_characters
 (
@@ -42,6 +69,8 @@ CREATE TABLE tmw_characters
 CREATE INDEX tmw_characters_user ON tmw_characters ( user_id );
 CREATE UNIQUE INDEX tmw_characters_name ON tmw_characters ( name );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_char_skills
 (
     char_id     INTEGER     NOT NULL,
@@ -53,6 +82,8 @@ CREATE TABLE tmw_char_skills
 
 CREATE INDEX tmw_char_skills_char ON tmw_char_skills ( char_id );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_items
 (
     id           INTEGER    PRIMARY KEY,
@@ -67,6 +98,8 @@ CREATE TABLE tmw_items
 
 CREATE INDEX tmw_items_type ON tmw_items (itemtype);
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_item_instances
 (
     item_id       INTEGER    PRIMARY KEY,
@@ -78,6 +111,8 @@ CREATE TABLE tmw_item_instances
 
 CREATE INDEX tmw_item_instances_typ ON tmw_item_instances ( itemclass_id );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_item_attributes
 (
     attribute_id    INTEGER    PRIMARY KEY,
@@ -90,6 +125,8 @@ CREATE TABLE tmw_item_attributes
 
 CREATE INDEX tmw_item_attributes_item ON tmw_item_attributes ( item_id );
 
+-----------------------------------------------------------------------------
+
 -- todo: remove class_id and amount and reference on tmw_item_instances
 CREATE TABLE tmw_inventories
 (
@@ -104,12 +141,16 @@ CREATE TABLE tmw_inventories
 
 CREATE INDEX tmw_inventories_owner ON tmw_inventories ( owner_id );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_guilds
 (
    id           INTEGER     PRIMARY KEY,
    name 		TEXT        NOT NULL UNIQUE
 );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_guild_members
 (
    guild_id     INTEGER     NOT NULL,
@@ -123,6 +164,8 @@ CREATE TABLE tmw_guild_members
 CREATE INDEX tmw_guild_members_g ON tmw_guild_members ( guild_id );
 CREATE INDEX tmw_guild_members_m ON tmw_guild_members ( member_id );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_quests
 (
    owner_id     INTEGER     NOT NULL,
@@ -132,6 +175,8 @@ CREATE TABLE tmw_quests
    FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
 );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_world_states
 (
    state_name   TEXT        PRIMARY KEY,
@@ -140,9 +185,7 @@ CREATE TABLE tmw_world_states
    moddate      INTEGER     NOT NULL
 );
 
-INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL, strftime('%s','now'));
-INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL, strftime('%s','now'));
-INSERT INTO tmw_world_states VALUES('database_version',     NULL,'3',  strftime('%s','now'));
+-----------------------------------------------------------------------------
 
 CREATE TABLE tmw_auctions
 (
@@ -165,6 +208,8 @@ CREATE INDEX tmw_auctions_owner ON tmw_auctions ( char_id );
 CREATE INDEX tmw_auctions_state ON tmw_auctions ( auction_state );
 CREATE INDEX tmw_auctions_item  ON tmw_auctions ( itemclass_id );
 
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_auction_bids
 (
    bid_id        INTEGER     PRIMARY KEY,
@@ -180,6 +225,7 @@ CREATE TABLE tmw_auction_bids
 CREATE INDEX tmw_auction_bids_auction ON tmw_auction_bids ( auction_id );
 CREATE INDEX tmw_auction_bids_owner   ON tmw_auction_bids ( char_id );
 
+-----------------------------------------------------------------------------
 
 CREATE TABLE tmw_post
 (
@@ -198,6 +244,7 @@ CREATE TABLE tmw_post
 CREATE INDEX tmw_post_sender   ON tmw_post ( sender_id );
 CREATE INDEX tmw_post_receiver ON tmw_post ( receiver_id );
 
+-----------------------------------------------------------------------------
 
 CREATE TABLE tmw_post_attachments
 (
@@ -212,15 +259,37 @@ CREATE TABLE tmw_post_attachments
 CREATE INDEX tmw_post_attachments_ltr ON tmw_post_attachments ( letter_id );
 CREATE INDEX tmw_post_attachments_itm ON tmw_post_attachments ( item_id );
 
+-----------------------------------------------------------------------------
+
+CREATE TABLE tmw_transaction_codes
+(
+   id            INTEGER     PRIMARY KEY,
+   description   TEXT        NOT NULL,
+   category      TEXT        NOT NULL
+);
+
+CREATE INDEX tmw_transaction_codes_cat    ON tmw_transaction_codes ( category );
+
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_transactions
 (
     id          INTEGER     PRIMARY KEY,
     char_id     INTEGER     NOT NULL,
     action      INTEGER     NOT NULL,
     message     TEXT,
-    time        INTEGER     NOT NULL
+    time        INTEGER     NOT NULL,
+    --
+    FOREIGN KEY (char_id) REFERENCES tmw_characters(id),
+    FOREIGN KEY (action)  REFERENCES tmw_transaction_codes(id),
 );
 
+CREATE INDEX tmw_transactions_char    ON tmw_transactions ( char_id );
+CREATE INDEX tmw_transactions_action  ON tmw_transactions ( action );
+CREATE INDEX tmw_transactions_time    ON tmw_transactions ( time );
+
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_online_list
 (
     char_id     INTEGER     PRIMARY KEY,
@@ -229,6 +298,12 @@ CREATE TABLE tmw_online_list
     FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
 );
 
+-----------------------------------------------------------------------------
+-- Views
+-----------------------------------------------------------------------------
+
+-- List all online users
+
 CREATE VIEW tmw_v_online_chars
 AS
    SELECT l.char_id    as char_id,
@@ -241,3 +316,71 @@ AS
      FROM tmw_online_list l
      JOIN tmw_characters c
        ON l.char_id = c.id;
+
+
+-- Show all stored transactions
+
+CREATE VIEW tmw_v_transactions
+AS
+   SELECT t.id           as transaction_id,
+          t.time         as transacition_time,
+          a.id           as user_id,
+          a.username     as username,
+          c.id           as char_id,
+          c.name         as charname,
+          tc.id          as action_id,
+          tc.description as action,
+          tc.category    as category,
+          t.message      as message
+     FROM tmw_transactions t
+     JOIN tmw_characters c
+       ON t.char_id = c.id
+     JOIN tmw_accounts a
+       ON c.user_id = a.id
+     JOIN tmw_transaction_codes tc
+       ON t.action = tc.id;
+
+-----------------------------------------------------------------------------
+-- Initial data
+-----------------------------------------------------------------------------
+
+-- initial world states and database version
+
+INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL, strftime('%s','now'));
+INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL, strftime('%s','now'));
+INSERT INTO tmw_world_states VALUES('database_version',     NULL,'5',  strftime('%s','now'));
+
+-- all known transaction codes
+
+INSERT INTO tmw_transaction_codes VALUES (  1, 'Character created',        'Character' );
+INSERT INTO tmw_transaction_codes VALUES (  2, 'Character selected',       'Character' );
+INSERT INTO tmw_transaction_codes VALUES (  3, 'Character deleted',        'Character' );
+INSERT INTO tmw_transaction_codes VALUES (  4, 'Public message sent',      'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  5, 'Public message annouced',  'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  6, 'Private message sent',     'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  7, 'Channel joined',           'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  8, 'Channel kicked',           'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  9, 'Channel MODE',             'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 10, 'Channel QUIT',             'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 11, 'Channel LIST',             'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 12, 'Channel USERLIST',         'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 13, 'Channel TOPIC',            'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 14, 'Command BAN',              'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 15, 'Command DROP',             'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 16, 'Command ITEM',             'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 17, 'Command MONEY',            'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 18, 'Command SETGROUP',         'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 19, 'Command SPAWN',            'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 20, 'Command WARP',             'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 21, 'Item picked up',           'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 22, 'Item used',                'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 23, 'Item dropped',             'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 24, 'Item moved',               'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 25, 'Target attacked',          'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 26, 'ACTION Changed',           'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 27, 'Trade requested',          'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 28, 'Trade ended',              'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 29, 'Trade money',              'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 30, 'Trade items',              'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 31, 'Attribute increased',      'Character' );
+INSERT INTO tmw_transaction_codes VALUES ( 32, 'Attribute decreased',      'Character' );
diff --git a/src/sql/sqlite/updates/update_3_to_4.sql b/src/sql/sqlite/updates/update_3_to_4.sql
new file mode 100644
index 00000000..434b7e80
--- /dev/null
+++ b/src/sql/sqlite/updates/update_3_to_4.sql
@@ -0,0 +1,11 @@
+
+-- add two columns to table tmw_accounts
+ALTER TABLE tmw_accounts ADD authorization TEXT NULL;
+ALTER TABLE tmw_accounts ADD expiration    INTEGER NULL;
+
+-- update the database version, and set date of update
+UPDATE tmw_world_states
+   SET value      = '4',
+       moddate    = strftime('%s','now')
+ WHERE state_name = 'database_version';
+
diff --git a/src/sql/sqlite/updates/update_4_to_5.sql b/src/sql/sqlite/updates/update_4_to_5.sql
new file mode 100644
index 00000000..5b290aba
--- /dev/null
+++ b/src/sql/sqlite/updates/update_4_to_5.sql
@@ -0,0 +1,78 @@
+
+-- create table tmw_transaction_codes
+CREATE TABLE tmw_transaction_codes
+(
+   id            INTEGER     PRIMARY KEY,
+   description   TEXT        NOT NULL,
+   category      TEXT        NOT NULL
+);
+
+CREATE INDEX tmw_transaction_codes_cat    ON tmw_transaction_codes ( category );
+
+INSERT INTO tmw_transaction_codes VALUES (  1, 'Character created',     'Character' );
+INSERT INTO tmw_transaction_codes VALUES (  2, 'Character selected',    'Character' );
+INSERT INTO tmw_transaction_codes VALUES (  3, 'Character deleted',     'Character' );
+
+INSERT INTO tmw_transaction_codes VALUES (  4, 'Public message sent',      'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  5, 'Public message annouced',  'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  6, 'Private message sent',     'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  7, 'Channel joined',           'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  8, 'Channel kicked',           'Chat' );
+INSERT INTO tmw_transaction_codes VALUES (  9, 'Channel MODE',             'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 10, 'Channel QUIT',             'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 11, 'Channel LIST',             'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 12, 'Channel USERLIST',         'Chat' );
+INSERT INTO tmw_transaction_codes VALUES ( 13, 'Channel TOPIC',            'Chat' );
+
+INSERT INTO tmw_transaction_codes VALUES ( 14, 'Command BAN',              'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 15, 'Command DROP',             'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 16, 'Command ITEM',             'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 17, 'Command MONEY',            'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 18, 'Command SETGROUP',         'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 19, 'Command SPAWN',            'Commands' );
+INSERT INTO tmw_transaction_codes VALUES ( 20, 'Command WARP',             'Commands' );
+
+INSERT INTO tmw_transaction_codes VALUES ( 21, 'Item picked up',           'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 22, 'Item used',                'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 23, 'Item dropped',             'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 24, 'Item moved',               'Actions' );
+
+INSERT INTO tmw_transaction_codes VALUES ( 25, 'Target attacked',          'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 26, 'ACTION Changed',           'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 27, 'Trade requested',          'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 28, 'Trade ended',              'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 29, 'Trade money',              'Actions' );
+INSERT INTO tmw_transaction_codes VALUES ( 30, 'Trade items',              'Actions' );
+
+INSERT INTO tmw_transaction_codes VALUES ( 31, 'Attribute increased',      'Character' );
+INSERT INTO tmw_transaction_codes VALUES ( 32, 'Attribute decreased',      'Character' );
+
+
+-- add view to show transactions
+CREATE VIEW tmw_v_transactions
+AS
+   SELECT t.id           as transaction_id,
+          t.time         as transacition_time,
+          a.id           as user_id,
+          a.username     as username,
+          c.id           as char_id,
+          c.name         as charname,
+          tc.id          as action_id,
+          tc.description as action,
+          tc.category    as category,
+          t.message      as message
+     FROM tmw_transactions t
+     JOIN tmw_characters c
+       ON t.char_id = c.id
+     JOIN tmw_accounts a
+       ON c.user_id = a.id
+     JOIN tmw_transaction_codes tc
+       ON t.action = tc.id;
+
+
+-- update the database version, and set date of update
+UPDATE tmw_world_states
+   SET value      = '5',
+       moddate    = strftime('%s','now')
+ WHERE state_name = 'database_version';
+