From 3965c222ec138163346a4e9e596f3ac15b3ead60 Mon Sep 17 00:00:00 2001 From: Runxi Yu Date: Mon, 13 Jan 2025 08:48:34 +0800 Subject: Add addresses --- schema.sql | 22 ++++++++++++++-------- 1 file changed, 14 insertions(+), 8 deletions(-) diff --git a/schema.sql b/schema.sql index 5faeb74..26f366c 100644 --- a/schema.sql +++ b/schema.sql @@ -1,20 +1,26 @@ CREATE TABLE accounts ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - username TEXT, - password TEXT + username TEXT NOT NULL, + password TEXT -- NULL for locked accounts ); CREATE TABLE mailboxes ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - account INTEGER NOT NULL, - FOREIGN KEY(account) REFERENCES accounts(id), - name TEXT, - UNIQUE(account, name) + account_id INTEGER NOT NULL, + FOREIGN KEY(account_id) REFERENCES accounts(id), + name TEXT NOT NULL, + UNIQUE(account_id, name) +); + +CREATE TABLE addresses ( + address TEXT PRIMARY KEY NOT NULL, + mailbox_id INTEGER NOT NULL, + FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id) ); CREATE TABLE mail ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - mailbox INTEGER NOT NULL, - FOREIGN KEY(mailbox) REFERENCES mailboxes(id), + mailbox_id INTEGER NOT NULL, + FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id), data bytea ); -- cgit v1.2.3