mirror of
https://github.com/micromdm/micromdm/
synced 2026-06-25 15:35:46 +08:00
39 lines
1.0 KiB
SQL
39 lines
1.0 KiB
SQL
PRAGMA auto_vacuum = INCREMENTAL;
|
|
PRAGMA foreign_keys = ON;
|
|
|
|
DROP TABLE IF EXISTS users;
|
|
DROP TABLE IF EXISTS sessions;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id text PRIMARY KEY,
|
|
username text NOT NULL DEFAULT '',
|
|
email text NOT NULL DEFAULT '',
|
|
password TEXT NOT NULL,
|
|
salt text NOT NULL,
|
|
confirmation_hash text,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT chk_username_not_empty CHECK (username != ''),
|
|
CONSTRAINT chk_email_not_empty CHECK (email != ''),
|
|
UNIQUE (email),
|
|
UNIQUE (username)
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS tg_users_updated_at
|
|
AFTER UPDATE ON users
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE users SET updated_at = CURRENT_TIMESTAMP
|
|
WHERE
|
|
id = old.id;
|
|
END;
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id text PRIMARY KEY NOT NULL,
|
|
user_id text REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
accessed_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS session_users_idx ON sessions(user_id);
|