From 04ebe945e98e397192272d1e3f5e6b91ebd3d795 Mon Sep 17 00:00:00 2001 From: Andreas Habel Date: Mon, 17 Aug 2009 19:18:36 +0200 Subject: Update table tmw_accounts. Added columns to store secret key and expiration time for password recovery via tmwweb/email. --- src/account-server/dalstorage.cpp | 2 +- src/sql/sqlite/createTables.sql | 20 +++++++++++--------- src/sql/sqlite/updates/update_3_to_4.sql | 13 +++++++++++++ 3 files changed, 25 insertions(+), 10 deletions(-) create mode 100644 src/sql/sqlite/updates/update_3_to_4.sql (limited to 'src') diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp index d27413d9..ec7158e5 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 "4" /** diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql index 1831c45a..682a94d4 100644 --- a/src/sql/sqlite/createTables.sql +++ b/src/sql/sqlite/createTables.sql @@ -1,13 +1,15 @@ 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 ); @@ -142,7 +144,7 @@ CREATE TABLE tmw_world_states 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')); +INSERT INTO tmw_world_states VALUES('database_version', NULL,'4', strftime('%s','now')); CREATE TABLE tmw_auctions ( 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..94d2edd4 --- /dev/null +++ b/src/sql/sqlite/updates/update_3_to_4.sql @@ -0,0 +1,13 @@ + +-- add two columns to table tmw_accounts +ALTER TABLE tmw_accounts ADD + authorization TEXT NULL, + 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'; + -- cgit v1.2.3-70-g09d2 From c32f2a14736733b94aab1d1b0bd8e6d74396009d Mon Sep 17 00:00:00 2001 From: Jared Adams Date: Thu, 20 Aug 2009 17:31:06 -0600 Subject: Remove attackzone from Makefile --- src/Makefile.am | 2 -- 1 file changed, 2 deletions(-) (limited to 'src') 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 \ -- cgit v1.2.3-70-g09d2 From 6b5bbbd0a5789bb920fde958a7778c16b6c1bcc6 Mon Sep 17 00:00:00 2001 From: Andreas Habel Date: Sun, 23 Aug 2009 13:02:34 +0200 Subject: 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. --- src/account-server/dalstorage.cpp | 2 +- src/sql/sqlite/createTables.sql | 149 ++++++++++++++++++++++++++++++- src/sql/sqlite/updates/update_3_to_4.sql | 6 +- src/sql/sqlite/updates/update_4_to_5.sql | 78 ++++++++++++++++ 4 files changed, 226 insertions(+), 9 deletions(-) create mode 100644 src/sql/sqlite/updates/update_4_to_5.sql (limited to 'src') diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp index ec7158e5..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 "4" +#define SUPPORTED_DB_VERSION "5" /** diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql index 682a94d4..152d16fc 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 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'; + -- cgit v1.2.3-70-g09d2