107 lines
3.0 KiB
Python
107 lines
3.0 KiB
Python
import os
|
|
import sqlite3
|
|
from datetime import datetime, timezone
|
|
|
|
from werkzeug.security import generate_password_hash
|
|
|
|
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
|
|
DATA_DIR = os.path.join(BASE_DIR, "server", "data")
|
|
DB_PATH = os.path.join(DATA_DIR, "spanish_diary.db")
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
display_name TEXT NOT NULL,
|
|
is_admin INTEGER NOT NULL DEFAULT 0,
|
|
is_active INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS entries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
date TEXT NOT NULL,
|
|
spanish TEXT NOT NULL,
|
|
english TEXT NOT NULL DEFAULT '',
|
|
chinese TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_entries_user_date ON entries(user_id, date);
|
|
|
|
CREATE TABLE IF NOT EXISTS vocabulary (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
word TEXT NOT NULL,
|
|
english TEXT NOT NULL DEFAULT '',
|
|
chinese TEXT NOT NULL DEFAULT '',
|
|
category TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE(user_id, word)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
translator TEXT NOT NULL DEFAULT 'local',
|
|
api_url TEXT NOT NULL DEFAULT '',
|
|
api_key TEXT NOT NULL DEFAULT '',
|
|
extra_config TEXT NOT NULL DEFAULT '{}',
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS typing_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
mode TEXT NOT NULL,
|
|
category_id TEXT NOT NULL DEFAULT '',
|
|
category_name TEXT NOT NULL DEFAULT '',
|
|
item_count INTEGER NOT NULL DEFAULT 0,
|
|
correct_keys INTEGER NOT NULL DEFAULT 0,
|
|
error_keys INTEGER NOT NULL DEFAULT 0,
|
|
accuracy INTEGER NOT NULL DEFAULT 0,
|
|
duration_seconds INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_typing_logs_user_created
|
|
ON typing_logs(user_id, created_at);
|
|
"""
|
|
|
|
|
|
def now_iso():
|
|
return datetime.now(timezone.utc).isoformat()
|
|
|
|
|
|
def get_db():
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA foreign_keys = ON")
|
|
conn.execute("PRAGMA busy_timeout = 5000")
|
|
return conn
|
|
|
|
|
|
def init_db():
|
|
os.makedirs(DATA_DIR, exist_ok=True)
|
|
conn = get_db()
|
|
try:
|
|
conn.executescript(SCHEMA)
|
|
conn.execute(
|
|
"""
|
|
INSERT OR IGNORE INTO users
|
|
(username, password_hash, display_name, is_admin, is_active, created_at)
|
|
VALUES (?, ?, ?, 1, 1, ?)
|
|
""",
|
|
(
|
|
"admin",
|
|
generate_password_hash("admin123"),
|
|
"管理员",
|
|
now_iso(),
|
|
),
|
|
)
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|