From 1f7c7fa192907d90a0ef6bbfbfe770a1e2fffa08 Mon Sep 17 00:00:00 2001 From: Thorbjørn Lindeijer Date: Tue, 16 May 2023 16:45:39 +0200 Subject: 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. --- src/sql/sqlite/createTables.sql | 4 +- src/sql/sqlite/updates/update_26_to_27.sql | 61 ++++++++++++++++++++++++++++++ 2 files changed, 63 insertions(+), 2 deletions(-) create mode 100644 src/sql/sqlite/updates/update_26_to_27.sql (limited to 'src/sql') 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; -- cgit v1.2.3-70-g09d2