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.

5 files changed, 106 insertions, 33 deletions

diff --git a/src/account-server/accounthandler.cpp b/src/account-server/accounthandler.cpp
index 4a4c73cf..e8161fcd 100644
--- a/src/account-server/accounthandler.cpp
+++ b/src/account-server/accounthandler.cpp
@@ -591,23 +591,28 @@ void AccountHandler::handleRegisterMessage(AccountClient &client,
     {
         reply.writeInt8(REGISTER_EXISTS_USERNAME);
     }
-    else if (storage->doesEmailAddressExist(sha256(email)))
-    {
-        reply.writeInt8(REGISTER_EXISTS_EMAIL);
-    }
-    else if (!checkCaptcha(client, captcha))
-    {
-        reply.writeInt8(REGISTER_CAPTCHA_WRONG);
-    }
     else
     {
         // We hash email server-side for additional privacy. We ask for it again
         // when we need it and verify it through comparing it with the hash.
-        client.setAccount(createAccount(username, sha256(password), sha256(email)));
-        client.status = CLIENT_CONNECTED;
+        const std::string emailHash = email.empty() ? std::string() : sha256(email);
 
-        reply.writeInt8(ERRMSG_OK);
-        addServerInfo(reply);
+        if (storage->doesEmailAddressExist(emailHash))
+        {
+            reply.writeInt8(REGISTER_EXISTS_EMAIL);
+        }
+        else if (!checkCaptcha(client, captcha))
+        {
+            reply.writeInt8(REGISTER_CAPTCHA_WRONG);
+        }
+        else
+        {
+            client.setAccount(createAccount(username, sha256(password), emailHash));
+            client.status = CLIENT_CONNECTED;
+
+            reply.writeInt8(ERRMSG_OK);
+            addServerInfo(reply);
+        }
     }
 
     client.send(reply);
@@ -692,27 +697,29 @@ void AccountHandler::handleEmailChangeMessage(AccountClient &client,
     }
 
     const std::string email = msg.readString();
-    const std::string emailHash = sha256(email);
 
-    if (!stringFilter->isEmailValid(email))
-    {
-        reply.writeInt8(ERRMSG_INVALID_ARGUMENT);
-    }
-    else if (stringFilter->findDoubleQuotes(email))
+    if (!stringFilter->isEmailValid(email)
+        || stringFilter->findDoubleQuotes(email))
     {
         reply.writeInt8(ERRMSG_INVALID_ARGUMENT);
     }
-    else if (storage->doesEmailAddressExist(emailHash))
-    {
-        reply.writeInt8(ERRMSG_EMAIL_ALREADY_EXISTS);
-    }
     else
     {
-        acc->setEmail(emailHash);
-        // Keep the database up to date otherwise we will go out of sync
-        storage->flush(*acc);
-        reply.writeInt8(ERRMSG_OK);
+        const std::string emailHash = email.empty() ? std::string() : sha256(email);
+
+        if (storage->doesEmailAddressExist(emailHash))
+        {
+            reply.writeInt8(ERRMSG_EMAIL_ALREADY_EXISTS);
+        }
+        else
+        {
+            acc->setEmail(emailHash);
+            // Keep the database up to date otherwise we will go out of sync
+            storage->flush(*acc);
+            reply.writeInt8(ERRMSG_OK);
+        }
     }
+
     client.send(reply);
 }
 
@@ -996,7 +1003,7 @@ void AccountHandler::handleCharacterDeleteMessage(AccountClient &client,
     }
 
     const std::string &characterName = chars[slot]->getName();
-    LOG_INFO("Character deleted:" << characterName);
+    LOG_INFO("Character deleted: " << characterName);
 
     // Log transaction
     Transaction trans;
@@ -1110,7 +1117,7 @@ void AccountHandler::handleStellarLogin(const std::string &token, const std::str
     }
     else
     {
-        // On-demand account creation for public keys
+        // On-demand account creation, using the public key as username.
         acc = createAccount(pubKey, std::string(), std::string());
 
         LOG_INFO("Stellar login: Created account for public key " << pubKey << ", ID " << acc->getID());
diff --git a/src/account-server/storage.cpp b/src/account-server/storage.cpp
index a1270dca..38c37935 100644
--- a/src/account-server/storage.cpp
+++ b/src/account-server/storage.cpp
@@ -610,6 +610,9 @@ bool Storage::doesUserNameExist(const std::string &name)
 
 bool Storage::doesEmailAddressExist(const std::string &email)
 {
+    if (email.empty())
+        return false;
+
     try
     {
         std::ostringstream sql;
@@ -910,7 +913,8 @@ void Storage::addAccount(Account &account)
         {
             mDb->bindValue(1, account.getName());
             mDb->bindValue(2, account.getPassword());
-            mDb->bindValue(3, account.getEmail());
+            if (!account.getEmail().empty())
+                mDb->bindValue(3, account.getEmail());
 
             mDb->processSql();
             account.setID(mDb->getLastId());
@@ -948,7 +952,8 @@ void Storage::flush(Account &account)
         {
             mDb->bindValue(1, account.getName());
             mDb->bindValue(2, account.getPassword());
-            mDb->bindValue(3, account.getEmail());
+            if (!account.getEmail().empty())
+                mDb->bindValue(3, account.getEmail());
             mDb->bindValue(4, account.getLevel());
             mDb->bindValue(5, account.getLastLogin());
             mDb->bindValue(6, account.getID());
diff --git a/src/common/manaserv_protocol.h b/src/common/manaserv_protocol.h
index 7d5c694f..9bb50337 100644
--- a/src/common/manaserv_protocol.h
+++ b/src/common/manaserv_protocol.h
@@ -31,7 +31,7 @@ namespace ManaServ {
 enum {
     PROTOCOL_VERSION = 10,
     MIN_PROTOCOL_VERSION = 9,
-    SUPPORTED_DB_VERSION = 26
+    SUPPORTED_DB_VERSION = 27
 };
 
 /**
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;