Allow account email to be null in the database

When using login with Stellar, the email address remains empty. This was
causing issues since the email is also required to be unique, in effect
only a single Stellar account could exist.

Resolved this by allowing the email column to be null, since the unique
requirement does not cover null values. An empty email will now be
stored as null value.

For non-Stellar logins, an email address is still required.

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;