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.

2 files changed, 80 insertions, 4 deletions

diff --git a/src/sql/sqlite/updates/update_3_to_4.sql b/src/sql/sqlite/updates/update_3_to_4.sql
index 94d2edd4..434b7e80 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 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';
+