53 lines
1.1 KiB
SQL
53 lines
1.1 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
email TEXT,
|
|
qq TEXT,
|
|
phone TEXT,
|
|
level INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rooms (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
is_private INTEGER NOT NULL,
|
|
password_hash TEXT,
|
|
allow_anonymous INTEGER NOT NULL,
|
|
created_by TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id TEXT NOT NULL,
|
|
user_id TEXT,
|
|
sender_name TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
content TEXT,
|
|
r2_key TEXT,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messages_room_id_id ON messages(room_id, id);
|
|
|
|
CREATE TABLE IF NOT EXISTS bans (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id TEXT NOT NULL,
|
|
reason TEXT,
|
|
until INTEGER,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS password_resets (
|
|
token TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
expires_at INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|