john
05e173b293
Replace the WebView shell with SwiftUI screens, add account-scoped Wiki and TTS APIs with adaptive review and photo scan support, and keep web/iOS pages usable while data loads asynchronously. Co-authored-by: Cursor <cursoragent@cursor.com>
285 lines
12 KiB
Python
285 lines
12 KiB
Python
"""启动时补齐新增列（已有库无需手工迁移）。"""
|
|
|
|
from sqlalchemy import inspect, text
|
|
|
|
from database import engine
|
|
|
|
|
|
def run_migrations() -> None:
|
|
insp = inspect(engine)
|
|
with engine.begin() as conn:
|
|
if insp.has_table("words"):
|
|
cols = {c["name"] for c in insp.get_columns("words")}
|
|
if "total_train_seconds" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE words ADD COLUMN total_train_seconds "
|
|
"INTEGER NOT NULL DEFAULT 0"
|
|
)
|
|
)
|
|
if "train_count" not in cols:
|
|
conn.execute(
|
|
text("ALTER TABLE words ADD COLUMN train_count INTEGER NOT NULL DEFAULT 0")
|
|
)
|
|
if "difficulty" not in cols:
|
|
conn.execute(
|
|
text("ALTER TABLE words ADD COLUMN difficulty DOUBLE NOT NULL DEFAULT 5.0")
|
|
)
|
|
if "stability_hours" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE words ADD COLUMN stability_hours "
|
|
"DOUBLE NOT NULL DEFAULT 24.0"
|
|
)
|
|
)
|
|
if "next_review_at" not in cols:
|
|
conn.execute(text("ALTER TABLE words ADD COLUMN next_review_at VARCHAR(32)"))
|
|
if "error_bank_member" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE words ADD COLUMN error_bank_member "
|
|
"INTEGER NOT NULL DEFAULT 0"
|
|
)
|
|
)
|
|
if "error_reinforce_streak" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE words ADD COLUMN error_reinforce_streak "
|
|
"INTEGER NOT NULL DEFAULT 0"
|
|
)
|
|
)
|
|
if "error_bank_entered_at" not in cols:
|
|
conn.execute(
|
|
text("ALTER TABLE words ADD COLUMN error_bank_entered_at VARCHAR(32)")
|
|
)
|
|
conn.execute(
|
|
text(
|
|
"UPDATE words SET error_bank_member = 1 "
|
|
"WHERE wrong_count > 0 AND error_bank_member = 0"
|
|
)
|
|
)
|
|
conn.execute(
|
|
text(
|
|
"UPDATE words SET error_bank_member = 1 "
|
|
"WHERE error_reinforce_streak >= 2 AND error_bank_member = 0"
|
|
)
|
|
)
|
|
|
|
if insp.has_table("quiz_records"):
|
|
cols = {c["name"] for c in insp.get_columns("quiz_records")}
|
|
if "duration_seconds" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE quiz_records ADD COLUMN duration_seconds "
|
|
"INTEGER NOT NULL DEFAULT 0"
|
|
)
|
|
)
|
|
|
|
if insp.has_table("user_settings"):
|
|
cols = {c["name"] for c in insp.get_columns("user_settings")}
|
|
if "active_book_id" not in cols:
|
|
conn.execute(text("ALTER TABLE user_settings ADD COLUMN active_book_id INTEGER"))
|
|
if "ai_wiki_enabled" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE user_settings ADD COLUMN ai_wiki_enabled "
|
|
"INTEGER NOT NULL DEFAULT 0"
|
|
)
|
|
)
|
|
if "ai_wiki_auto_organize" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE user_settings ADD COLUMN ai_wiki_auto_organize "
|
|
"INTEGER NOT NULL DEFAULT 0"
|
|
)
|
|
)
|
|
if "error_clear_correct_count" not in cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE user_settings ADD COLUMN error_clear_correct_count "
|
|
"INTEGER NOT NULL DEFAULT 2"
|
|
)
|
|
)
|
|
|
|
if insp.has_table("user_book_settings"):
|
|
ubs_cols = {c["name"] for c in insp.get_columns("user_book_settings")}
|
|
if "error_clear_correct_count" not in ubs_cols:
|
|
conn.execute(
|
|
text(
|
|
"ALTER TABLE user_book_settings ADD COLUMN error_clear_correct_count "
|
|
"INTEGER NOT NULL DEFAULT 2"
|
|
)
|
|
)
|
|
|
|
if not insp.has_table("wiki_sources"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE wiki_sources (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
word_id INTEGER NULL,
|
|
source_key VARCHAR(96) NOT NULL,
|
|
source_type VARCHAR(32) NOT NULL,
|
|
payload TEXT NOT NULL,
|
|
created_at VARCHAR(32) NOT NULL,
|
|
CONSTRAINT uq_wiki_source_user_key UNIQUE (user_id, source_key),
|
|
FOREIGN KEY(user_id) REFERENCES users (id),
|
|
FOREIGN KEY(word_id) REFERENCES words (id)
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
conn.execute(text("CREATE INDEX ix_wiki_sources_user_id ON wiki_sources (user_id)"))
|
|
conn.execute(text("CREATE INDEX ix_wiki_sources_word_id ON wiki_sources (word_id)"))
|
|
|
|
if not insp.has_table("wiki_pages"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE wiki_pages (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
word_id INTEGER NULL,
|
|
slug VARCHAR(160) NOT NULL,
|
|
title VARCHAR(500) NOT NULL,
|
|
category VARCHAR(32) NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
source_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at VARCHAR(32) NOT NULL,
|
|
updated_at VARCHAR(32) NOT NULL,
|
|
CONSTRAINT uq_wiki_page_user_slug UNIQUE (user_id, slug),
|
|
FOREIGN KEY(user_id) REFERENCES users (id),
|
|
FOREIGN KEY(word_id) REFERENCES words (id)
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
conn.execute(text("CREATE INDEX ix_wiki_pages_user_id ON wiki_pages (user_id)"))
|
|
conn.execute(text("CREATE INDEX ix_wiki_pages_word_id ON wiki_pages (word_id)"))
|
|
|
|
if not insp.has_table("wiki_links"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE wiki_links (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
source_page_id INTEGER NOT NULL,
|
|
target_page_id INTEGER NOT NULL,
|
|
relation VARCHAR(32) NOT NULL,
|
|
created_at VARCHAR(32) NOT NULL,
|
|
CONSTRAINT uq_wiki_link_edge UNIQUE (
|
|
user_id, source_page_id, target_page_id, relation
|
|
),
|
|
FOREIGN KEY(user_id) REFERENCES users (id),
|
|
FOREIGN KEY(source_page_id) REFERENCES wiki_pages (id),
|
|
FOREIGN KEY(target_page_id) REFERENCES wiki_pages (id)
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
conn.execute(text("CREATE INDEX ix_wiki_links_user_id ON wiki_links (user_id)"))
|
|
conn.execute(
|
|
text("CREATE INDEX ix_wiki_links_source_page_id ON wiki_links (source_page_id)")
|
|
)
|
|
conn.execute(
|
|
text("CREATE INDEX ix_wiki_links_target_page_id ON wiki_links (target_page_id)")
|
|
)
|
|
|
|
if not insp.has_table("wiki_logs"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE wiki_logs (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
action VARCHAR(32) NOT NULL,
|
|
page_slug VARCHAR(160) NULL,
|
|
detail TEXT NOT NULL,
|
|
created_at VARCHAR(32) NOT NULL,
|
|
FOREIGN KEY(user_id) REFERENCES users (id)
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
conn.execute(text("CREATE INDEX ix_wiki_logs_user_id ON wiki_logs (user_id)"))
|
|
|
|
if not insp.has_table("wiki_llm_usage"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE wiki_llm_usage (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
usage_date VARCHAR(10) NOT NULL,
|
|
call_count INTEGER NOT NULL DEFAULT 0,
|
|
CONSTRAINT uq_wiki_llm_usage_day UNIQUE (user_id, usage_date),
|
|
FOREIGN KEY(user_id) REFERENCES users (id)
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
conn.execute(text("CREATE INDEX ix_wiki_llm_usage_user_id ON wiki_llm_usage (user_id)"))
|
|
|
|
if insp.has_table("wiki_pages"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
UPDATE wiki_pages SET
|
|
title = REPLACE(title, 'WordLoop LLM Wiki', 'WordLoop Wiki'),
|
|
content = REPLACE(content, 'WordLoop LLM Wiki', 'WordLoop Wiki')
|
|
WHERE title LIKE '%LLM Wiki%' OR content LIKE '%LLM Wiki%'
|
|
"""
|
|
)
|
|
)
|
|
|
|
if not insp.has_table("user_book_settings"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE user_book_settings (
|
|
id INTEGER PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
book_id INTEGER NOT NULL,
|
|
daily_target INTEGER NOT NULL DEFAULT 12,
|
|
master_required_count INTEGER NOT NULL DEFAULT 3,
|
|
weak_wrong_threshold INTEGER NOT NULL DEFAULT 2,
|
|
CONSTRAINT uq_user_book_settings UNIQUE (user_id, book_id),
|
|
FOREIGN KEY(user_id) REFERENCES users (id),
|
|
FOREIGN KEY(book_id) REFERENCES word_books (id)
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
|
|
if insp.has_table("words"):
|
|
cols = {c["name"] for c in insp.get_columns("words")}
|
|
if "book_id" not in cols:
|
|
conn.execute(
|
|
text("ALTER TABLE words ADD COLUMN book_id INTEGER NOT NULL DEFAULT 0")
|
|
)
|
|
conn.execute(text("CREATE INDEX ix_words_book_id ON words (book_id)"))
|
|
if "book_entry_id" not in cols:
|
|
conn.execute(text("ALTER TABLE words ADD COLUMN book_entry_id INTEGER"))
|
|
|
|
if insp.has_table("words") and insp.has_table("quiz_records"):
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
UPDATE words w SET
|
|
train_count = (
|
|
SELECT COUNT(*) FROM quiz_records q WHERE q.word_id = w.id
|
|
),
|
|
total_train_seconds = (
|
|
SELECT COALESCE(SUM(duration_seconds), 0)
|
|
FROM quiz_records q WHERE q.word_id = w.id
|
|
)
|
|
WHERE train_count = 0 AND EXISTS (
|
|
SELECT 1 FROM quiz_records q WHERE q.word_id = w.id
|
|
)
|
|
"""
|
|
)
|
|
)
|