Added table and view to prepare reporting of transaction log.

Fixed database update "3 to 4".
Added database update "4 to 5".
Added indexes to table tmw_transaction for faster search when it
gets bigger.
Refactored layout of sqlite database script.

3 files changed, 225 insertions, 8 deletions

diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
index 682a94d..152d16f 100644
--- a/src/sql/sqlite/createTables.sql
+++ b/src/sql/sqlite/createTables.sql
@@ -1,3 +1,27 @@
+--
+-- 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,
@@ -15,6 +39,7 @@ CREATE TABLE tmw_accounts
 CREATE UNIQUE INDEX tmw_accounts_username ON tmw_accounts ( username );
 CREATE UNIQUE INDEX tmw_accounts_email    ON tmw_accounts ( email );
 
+-----------------------------------------------------------------------------
 
 CREATE TABLE tmw_characters
 (
@@ -44,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,
@@ -55,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,
@@ -69,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,
@@ -80,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,
@@ -92,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
 (
@@ -106,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,
@@ -125,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,
@@ -134,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,
@@ -142,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,'4',  strftime('%s','now'));
+-----------------------------------------------------------------------------
 
 CREATE TABLE tmw_auctions
 (
@@ -167,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,
@@ -182,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
 (
@@ -200,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
 (
@@ -214,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,
@@ -231,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,
@@ -243,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
index 94d2edd..434b7e8 100644
--- a/src/sql/sqlite/updates/update_3_to_4.sql
+++ b/src/sql/sqlite/updates/update_3_to_4.sql
@@ -1,9 +1,7 @@
 
 -- add two columns to table tmw_accounts
-ALTER TABLE tmw_accounts ADD
-    authorization TEXT            NULL,
-    expiration    INTEGER         NULL
-;
+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
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 0000000..5b290ab
--- /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';
+