diff options
Diffstat (limited to 'src/sql')
| -rw-r--r-- | src/sql/sqlite/createTables.sql | 4 | ||||
| -rw-r--r-- | src/sql/sqlite/updates/update_26_to_27.sql | 61 |
2 files changed, 63 insertions, 2 deletions
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql index 437a0546..9616245a 100644 --- a/src/sql/sqlite/createTables.sql +++ b/src/sql/sqlite/createTables.sql @@ -29,7 +29,7 @@ CREATE TABLE mana_accounts id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE, password TEXT NOT NULL, - email TEXT NOT NULL, + email TEXT NULL, level INTEGER NOT NULL, banned INTEGER NOT NULL, registration INTEGER NOT NULL, @@ -411,7 +411,7 @@ AS INSERT INTO mana_world_states VALUES('accountserver_startup',-1,'0', strftime('%s','now')); INSERT INTO mana_world_states VALUES('accountserver_version',-1,'0', strftime('%s','now')); -INSERT INTO mana_world_states VALUES('database_version', -1,'26', strftime('%s','now')); +INSERT INTO mana_world_states VALUES('database_version', -1,'27', strftime('%s','now')); -- all known transaction codes diff --git a/src/sql/sqlite/updates/update_26_to_27.sql b/src/sql/sqlite/updates/update_26_to_27.sql new file mode 100644 index 00000000..3d0889ef --- /dev/null +++ b/src/sql/sqlite/updates/update_26_to_27.sql @@ -0,0 +1,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; |