1CREATE TABLE users_without_email (
 2    id TEXT PRIMARY KEY CHECK (length(id) = 36),
 3    username TEXT NOT NULL UNIQUE
 4        CHECK (
 5            length(username) BETWEEN 1 AND 32
 6            AND username = lower(username)
 7            AND substr(username, 1, 1) GLOB '[a-z0-9]'
 8            AND username NOT GLOB '*[^a-z0-9._-]*'
 9        ),
10    display_name TEXT NOT NULL
11        CHECK (length(CAST(display_name AS BLOB)) <= 128),
12    password_hash BLOB NOT NULL CHECK (length(password_hash) > 0),
13    disabled INTEGER NOT NULL CHECK (disabled IN (0, 1)),
14    uid_number INTEGER NOT NULL UNIQUE
15        CHECK (uid_number BETWEEN 10000 AND 19999),
16    created_at INTEGER NOT NULL,
17    last_login_at INTEGER,
18    credentials_epoch INTEGER NOT NULL DEFAULT 0
19) STRICT;
20
21INSERT INTO users_without_email (
22    id,
23    username,
24    display_name,
25    password_hash,
26    disabled,
27    uid_number,
28    created_at,
29    last_login_at,
30    credentials_epoch
31)
32SELECT
33    id,
34    username,
35    display_name,
36    password_hash,
37    disabled,
38    uid_number,
39    created_at,
40    last_login_at,
41    credentials_epoch
42FROM users;
43
44DROP TRIGGER pending_confirmations_need_disabled;
45DROP TABLE users;
46ALTER TABLE users_without_email RENAME TO users;
47
48CREATE TRIGGER pending_confirmations_need_disabled
49BEFORE INSERT ON pending_confirmations
50WHEN (SELECT disabled FROM users WHERE id = NEW.user_id) IS NOT 1
51BEGIN
52    SELECT RAISE(ABORT, 'a pending account must be disabled');
53END;
54
55CREATE TRIGGER users_pending_stays_disabled
56BEFORE UPDATE OF disabled ON users
57WHEN NEW.disabled = 0 AND EXISTS (SELECT 1 FROM pending_confirmations WHERE user_id = NEW.id)
58BEGIN
59    SELECT RAISE(ABORT, 'a pending account is activated only by its confirmation');
60END;
61
62ALTER TABLE google_links DROP COLUMN google_email_snapshot;
63
64ALTER TABLE invitations DROP COLUMN email;