1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
-- rename tables to new prefix
ALTER TABLE tmw_accounts RENAME TO mana_accounts;
ALTER TABLE tmw_characters RENAME TO mana_characters;
ALTER TABLE tmw_char_skills RENAME TO mana_char_skills;
ALTER TABLE tmw_char_status_effects RENAME TO mana_char_status_effects;
ALTER TABLE tmw_items RENAME TO mana_items;
ALTER TABLE tmw_item_instances RENAME TO mana_item_instances;
ALTER TABLE tmw_item_attributes RENAME TO mana_item_attributes;
ALTER TABLE tmw_inventories RENAME TO mana_inventories;
ALTER TABLE tmw_guilds RENAME TO mana_guilds;
ALTER TABLE tmw_guild_members RENAME TO mana_guild_members;
ALTER TABLE tmw_quests RENAME TO mana_quests;
ALTER TABLE tmw_world_states RENAME TO mana_world_states;
ALTER TABLE tmw_auctions RENAME TO mana_auctions;
ALTER TABLE tmw_auction_bids RENAME TO mana_auction_bids;
ALTER TABLE tmw_post RENAME TO mana_post;
ALTER TABLE tmw_post_attachments RENAME TO mana_post_attachments;
ALTER TABLE tmw_transaction_codes RENAME TO mana_transaction_codes;
ALTER TABLE tmw_transactions RENAME TO mana_transactions;
ALTER TABLE tmw_online_list RENAME TO mana_online_list;
-- rename indexes (apparently have to drop and recreate)
DROP INDEX tmw_accounts_username;
DROP INDEX tmw_accounts_email;
DROP INDEX tmw_characters_user;
DROP INDEX tmw_characters_name;
DROP INDEX tmw_char_skills_char;
DROP INDEX tmw_char_status_char;
DROP INDEX tmw_items_type;
DROP INDEX tmw_item_instances_typ;
DROP INDEX tmw_item_attributes_item;
DROP INDEX tmw_inventories_owner;
DROP INDEX tmw_guild_members_g;
DROP INDEX tmw_guild_members_m;
DROP INDEX tmw_auctions_owner;
DROP INDEX tmw_auctions_state;
DROP INDEX tmw_auctions_item;
DROP INDEX tmw_auction_bids_auction;
DROP INDEX tmw_auction_bids_owner;
DROP INDEX tmw_post_sender;
DROP INDEX tmw_post_receiver;
DROP INDEX tmw_post_attachments_ltr;
DROP INDEX tmw_post_attachments_itm;
DROP INDEX tmw_transaction_codes_cat;
DROP INDEX tmw_transactions_char;
DROP INDEX tmw_transactions_action;
DROP INDEX tmw_transactions_time;
CREATE UNIQUE INDEX mana_accounts_username ON mana_accounts ( username );
CREATE UNIQUE INDEX mana_accounts_email ON mana_accounts ( email );
CREATE INDEX mana_characters_user ON mana_characters ( user_id );
CREATE UNIQUE INDEX mana_characters_name ON mana_characters ( name );
CREATE INDEX mana_char_skills_char ON mana_char_skills ( char_id );
CREATE INDEX mana_char_status_char on mana_char_status_effects ( char_id );
CREATE INDEX mana_items_type ON mana_items (itemtype);
CREATE INDEX mana_item_instances_typ ON mana_item_instances ( itemclass_id );
CREATE INDEX mana_item_attributes_item ON mana_item_attributes ( item_id );
CREATE INDEX mana_inventories_owner ON mana_inventories ( owner_id );
CREATE INDEX mana_guild_members_g ON mana_guild_members ( guild_id );
CREATE INDEX mana_guild_members_m ON mana_guild_members ( member_id );
CREATE INDEX mana_auctions_owner ON mana_auctions ( char_id );
CREATE INDEX mana_auctions_state ON mana_auctions ( auction_state );
CREATE INDEX mana_auctions_item ON mana_auctions ( itemclass_id );
CREATE INDEX mana_auction_bids_auction ON mana_auction_bids ( auction_id );
CREATE INDEX mana_auction_bids_owner ON mana_auction_bids ( char_id );
CREATE INDEX mana_post_sender ON mana_post ( sender_id );
CREATE INDEX mana_post_receiver ON mana_post ( receiver_id );
CREATE INDEX mana_post_attachments_ltr ON mana_post_attachments ( letter_id );
CREATE INDEX mana_post_attachments_itm ON mana_post_attachments ( item_id );
CREATE INDEX mana_transaction_codes_cat ON mana_transaction_codes ( category );
CREATE INDEX mana_transactions_char ON mana_transactions ( char_id );
CREATE INDEX mana_transactions_action ON mana_transactions ( action );
CREATE INDEX mana_transactions_time ON mana_transactions ( time );
-- rename views (have to drop and recreate as well)
DROP VIEW tmw_v_online_chars;
DROP VIEW tmw_v_transactions;
CREATE VIEW mana_v_online_chars
AS
SELECT l.char_id as char_id,
l.login_date as login_date,
c.user_id as user_id,
c.name as name,
c.gender as gender,
c.level as level,
c.map_id as map_id
FROM mana_online_list l
JOIN mana_characters c
ON l.char_id = c.id;
CREATE VIEW mana_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 mana_transactions t
JOIN mana_characters c
ON t.char_id = c.id
JOIN mana_accounts a
ON c.user_id = a.id
JOIN mana_transaction_codes tc
ON t.action = tc.id;
-- update the database version, and set date of update
UPDATE mana_world_states
SET value = '7',
moddate = strftime('%s','now')
WHERE state_name = 'database_version';
|
