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
|
BEGIN;
-- The email column is now allowed to be null. To alter the table in this way,
-- we have to:
--
-- * create the new table
-- * copy the data over
-- * drop the views using the old table and then drop the old table itself
-- * rename the new table
-- * recreate the indexes and views
--
CREATE TABLE mana_accounts_new
(
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
email TEXT NULL,
level INTEGER NOT NULL,
banned INTEGER NOT NULL,
registration INTEGER NOT NULL,
lastlogin INTEGER NOT NULL,
authorization TEXT NULL,
expiration INTEGER NULL
);
INSERT INTO mana_accounts_new SELECT * FROM mana_accounts;
DROP VIEW mana_v_transactions;
DROP TABLE mana_accounts;
ALTER TABLE mana_accounts_new RENAME TO mana_accounts;
CREATE UNIQUE INDEX mana_accounts_username ON mana_accounts ( username );
CREATE UNIQUE INDEX mana_accounts_email ON mana_accounts ( email );
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 = '27',
moddate = strftime('%s','now')
WHERE state_name = 'database_version';
END;
|
