-- this table pluralized only because user is already taken by postgres
CREATE TABLE IF NOT EXISTS users
(
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT,
password TEXT,
salt TEXT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE(username),
UNIQUE(email)
);
CREATE TABLE IF NOT EXISTS session
(
id INTEGER PRIMARY KEY,
users_id INTEGER NOT NULL REFERENCES users(id),
token TEXT NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
expires TIMESTAMP NOT NULL
);
CREATE TABLE IF NOT EXISTS role (
id INTEGER PRIMARY KEY,
role VARCHAR(256) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE(role)
);
-- e.g. everyone has anonymous role, even those without an account
INSERT INTO role (role) VALUES ('ANONYMOUS') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('VIEWER') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('BORROWER') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('LENDER') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('SERVER_ADMIN') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('ADD_BOOKS') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('EDIT_BOOKS') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('DELETE_BOOKS') ON CONFLICT DO NOTHING;
INSERT INTO role (role) VALUES ('ROLE_GRANTER') ON CONFLICT DO NOTHING;
CREATE TABLE IF NOT EXISTS users_role (
users_id INTEGER NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
role_id INTEGER NOT NULL REFERENCES role(id) ON UPDATE CASCADE ON DELETE CASCADE,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- grantor_id is a role(id), but the information is not key to integrity,
-- i.e. do not want to impact record if grantor is deleted
grantor_id INTEGER,
UNIQUE(users_id, role_id)
);
CREATE TABLE IF NOT EXISTS users_role_request (
users_id INTEGER NOT NULL REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
role_id INTEGER NOT NULL REFERENCES role(id) ON UPDATE CASCADE ON DELETE CASCADE,
reason TEXT,
denied BOOL,
deny_reason TEXT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE(users_id, role_id)
);