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;