summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/Makefile.am2
-rw-r--r--src/account-server/dalstorage.cpp2
-rw-r--r--src/sql/sqlite/createTables.sql167
-rw-r--r--src/sql/sqlite/updates/update_3_to_4.sql11
-rw-r--r--src/sql/sqlite/updates/update_4_to_5.sql78
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';
+
generated by cgit v1.2.3-70-g09d2 (git 2.46.0) at 2025-04-03 17:07:50 +0000