50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- Users 表创建脚本
|
||
-- 数据库类型: SQLite
|
||
|
||
CREATE TABLE IF NOT EXISTS Users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username TEXT UNIQUE NOT NULL,
|
||
password_hash TEXT NOT NULL,
|
||
create_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
last_login DATETIME,
|
||
is_active BOOLEAN DEFAULT 1
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS Notes (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
pid INTEGER NOT NULL,
|
||
owner VARCHAR(128) DEFAULT 'Unknow',
|
||
title VARCHAR(128) NOT NULL,
|
||
content TEXT NOT NULL,
|
||
views INTEGER NOT NULL DEFAULT 0,
|
||
created_at DATETIME,
|
||
updated_at DATETIME
|
||
);
|
||
|
||
-- 旧版增量
|
||
-- ALTER TABLE Notes ADD COLUMN owner VARCHAR(128) DEFAULT 'Unknow';
|
||
-- ALTER TABLE Notes ADD COLUMN views INTEGER NOT NULL DEFAULT 0; -- patch_001 观看人数列
|
||
-- ALTER TABLE Notes ADD COLUMN created_at DATETIME; -- patch_002 创建时间
|
||
-- ALTER TABLE Notes ADD COLUMN updated_at DATETIME; -- patch_002 更新时间
|
||
|
||
-- Notes 索引：get_notes 按 pid 查子节点、所有权校验按 owner（patch_001 并入）
|
||
CREATE INDEX IF NOT EXISTS idx_notes_pid ON Notes(pid);
|
||
CREATE INDEX IF NOT EXISTS idx_notes_owner ON Notes(owner);
|
||
|
||
-- ── 日志表（db 内置日志模块）─────────────────────────────────────────
|
||
|
||
CREATE TABLE IF NOT EXISTS Logs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
ts TEXT NOT NULL,
|
||
service TEXT NOT NULL,
|
||
level TEXT NOT NULL,
|
||
message TEXT NOT NULL,
|
||
trace_id TEXT,
|
||
extra TEXT
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_logs_service_ts ON Logs(service, ts);
|
||
CREATE INDEX IF NOT EXISTS idx_logs_level_ts ON Logs(level, ts);
|
||
CREATE INDEX IF NOT EXISTS idx_logs_ts ON Logs(ts);
|
||
CREATE INDEX IF NOT EXISTS idx_logs_trace ON Logs(trace_id);
|