-- 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';
