1 files changed, 61 insertions, 0 deletions
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;