1234567891011121314151617181920212223242526272829303132 |
- CREATE TABLE IF NOT EXISTS user (
- name TEXT NOT NULL UNIQUE,
- display_name TEXT NOT NULL UNIQUE,
- salt TEXT NOT NULL,
- hash TEXT NOT NULL,
- totp_secret TEXT DEFAULT NULL,
- contact_email TEXT DEFAULT NULL
- );
- CREATE TABLE IF NOT EXISTS session (
- id TEXT PRIMARY KEY UNIQUE,
- username TEXT NOT NULL UNIQUE REFERENCES user(name) ON DELETE CASCADE
- );
- CREATE INDEX IF NOT EXISTS username_idx ON user(name);
- CREATE VIEW IF NOT EXISTS sess_user AS SELECT user.name AS name, session.id AS session FROM user JOIN session ON session.username=user.name;
- CREATE TABLE IF NOT EXISTS user_acl (
- username TEXT NOT NULL UNIQUE REFERENCES user(name) ON DELETE CASCADE,
- acl TEXT NOT NULL
- );
- CREATE TABLE IF NOT EXISTS change_request (
- username TEXT NOT NULL REFERENCES user(name) ON DELETE CASCADE,
- type TEXT NOT NULL,
- token TEXT PRIMARY KEY UNIQUE,
- secret TEXT,
- processed NUMERIC DEFAULT 0
- );
- CREATE VIEW IF NOT EXISTS change_request_full AS SELECT cr.username, u.display_name, cr.type, cr.token, cr.secret, cr.processed, u.contact_email from change_request AS cr JOIN user AS u ON u.name=cr.username;
|