Sinccon / app /db.py
Shaptoli's picture
Update: Interactive Web solver, SQLite WAL mode, geometry drawer and bugfixes
ac94664
Raw History Blame Contribute Delete
14.9 kB
"""
Sincc.on: Foydalanuvchilar va So'rovlar Statistikasi (SQLite).
Yuqori yuklama uchun WAL rejimi, xavfsiz timeout va normallashtirilgan kesh bilan.
"""
import sqlite3
import pathlib
import datetime
import hashlib
import json
import re
DB_PATH = pathlib.Path(__file__).resolve().parent.parent / "data" / "bot_stats.db"
def get_db_connection() -> sqlite3.Connection:
"""Ko'p oqimli rejimda blokirovkaga tushmasligi uchun 25 soniyalik timeout bilan ulanish beradi."""
conn = sqlite3.connect(DB_PATH, timeout=25.0)
conn.execute("PRAGMA busy_timeout = 25000;")
return conn
def init_db():
DB_PATH.parent.mkdir(parents=True, exist_ok=True)
with get_db_connection() as conn:
cursor = conn.cursor()
# Yuqori yuklamada blokirovkalar bo'lmasligi uchun WAL rejimi
cursor.execute("PRAGMA journal_mode=WAL;")
cursor.execute("PRAGMA synchronous=NORMAL;")
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
user_id INTEGER PRIMARY KEY,
username TEXT,
first_name TEXT,
joined_at TEXT,
last_active TEXT,
total_queries INTEGER DEFAULT 0,
is_admin INTEGER DEFAULT 0
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS queries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
query_type TEXT,
question_preview TEXT,
timestamp TEXT,
verified INTEGER DEFAULT 0
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS solution_cache (
query_hash TEXT PRIMARY KEY,
question_text TEXT,
solution_json TEXT,
created_at TEXT,
hits INTEGER DEFAULT 1
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS feedbacks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
username TEXT,
first_name TEXT,
message TEXT,
reply_context TEXT,
timestamp TEXT
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS chat_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
role TEXT,
content TEXT,
timestamp TEXT
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS banned_users (
user_id INTEGER PRIMARY KEY,
username TEXT,
first_name TEXT,
reason TEXT,
banned_at TEXT,
banned_by INTEGER
)
""")
conn.commit()
def record_user_activity(user_id: int, username: str = None, first_name: str = None):
now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT user_id, total_queries FROM users WHERE user_id = ?", (user_id,))
row = cursor.fetchone()
if row is None:
cursor.execute(
"INSERT INTO users (user_id, username, first_name, joined_at, last_active, total_queries) VALUES (?, ?, ?, ?, ?, ?)",
(user_id, username or "", first_name or "", now_str, now_str, 0)
)
else:
cursor.execute(
"UPDATE users SET username = ?, first_name = ?, last_active = ? WHERE user_id = ?",
(username or "", first_name or "", now_str, user_id)
)
conn.commit()
def log_query(user_id: int, query_type: str, question: str, verified: bool = False):
now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
preview = (question[:80] + "...") if len(question) > 80 else question
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute(
"INSERT INTO queries (user_id, query_type, question_preview, timestamp, verified) VALUES (?, ?, ?, ?, ?)",
(user_id, query_type, preview, now_str, 1 if verified else 0)
)
cursor.execute(
"UPDATE users SET total_queries = total_queries + 1, last_active = ? WHERE user_id = ?",
(now_str, user_id)
)
conn.commit()
def log_feedback(user_id: int, username: str, first_name: str, message: str, reply_context: str = ""):
now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
INSERT INTO feedbacks (user_id, username, first_name, message, reply_context, timestamp)
VALUES (?, ?, ?, ?, ?, ?)
""", (user_id, username or "", first_name or "", message, reply_context or "", now_str))
conn.commit()
def get_recent_feedbacks(limit: int = 5):
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT id, user_id, username, first_name, message, reply_context, timestamp
FROM feedbacks
ORDER BY id DESC
LIMIT ?
""", (limit,))
return cursor.fetchall()
def set_user_admin(user_id: int, is_admin: bool = True):
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("UPDATE users SET is_admin = ? WHERE user_id = ?", (1 if is_admin else 0, user_id))
conn.commit()
def is_user_admin(user_id: int) -> bool:
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT is_admin FROM users WHERE user_id = ?", (user_id,))
row = cursor.fetchone()
return bool(row and row[0] == 1)
def ban_user(user_id: int, username: str = "", first_name: str = "", reason: str = "Xavfsizlik qoidabuzarligi", banned_by: int = 0):
"""Foydalanuvchini bloklanganlar ro'yxatiga qo'shadi."""
now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
INSERT OR REPLACE INTO banned_users (user_id, username, first_name, reason, banned_at, banned_by)
VALUES (?, ?, ?, ?, ?, ?)
""", (user_id, username or "", first_name or "", reason, now_str, banned_by))
conn.commit()
def unban_user(user_id: int) -> bool:
"""Foydalanuvchini blokdan chiqaradi."""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM banned_users WHERE user_id = ?", (user_id,))
changes = conn.total_changes
conn.commit()
return changes > 0
def is_user_banned(user_id: int) -> tuple[bool, str]:
"""Foydalanuvchi bloklanganligini tekshiradi."""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT reason, banned_at FROM banned_users WHERE user_id = ?", (user_id,))
row = cursor.fetchone()
if row:
return True, row[0] or "Xavfsizlik qoidabuzarligi"
return False, ""
def get_banned_users(limit: int = 20) -> list[dict]:
"""Bloklangan foydalanuvchilar ro'yxatini qaytaradi."""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT user_id, username, first_name, reason, banned_at, banned_by
FROM banned_users
ORDER BY banned_at DESC
LIMIT ?
""", (limit,))
return [
{
"user_id": r[0],
"username": r[1],
"first_name": r[2],
"reason": r[3],
"banned_at": r[4],
"banned_by": r[5]
}
for r in cursor.fetchall()
]
def get_statistics():
today_str = datetime.datetime.now().strftime("%Y-%m-%d")
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
total_users = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM users WHERE last_active LIKE ?", (f"{today_str}%",))
active_today = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM queries")
total_queries = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM queries WHERE query_type = 'photo'")
photo_queries = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM queries WHERE query_type = 'voice'")
voice_queries = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM queries WHERE query_type = 'text'")
text_queries = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM feedbacks")
total_feedbacks = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM banned_users")
total_banned = cursor.fetchone()[0]
cursor.execute("""
SELECT user_id, username, first_name, total_queries, last_active
FROM users
ORDER BY last_active DESC
LIMIT 10
""")
recent_users = cursor.fetchall()
return {
"total_users": total_users,
"active_today": active_today,
"total_queries": total_queries,
"photo_queries": photo_queries,
"voice_queries": voice_queries,
"text_queries": text_queries,
"total_feedbacks": total_feedbacks,
"total_banned": total_banned,
"recent_users": recent_users
}
def get_all_user_ids():
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT user_id FROM users")
return [row[0] for row in cursor.fetchall()]
_NORM_NOISE_PHRASES = (
"tenglamaning ildizlarini toping", "tenglamani yeching", "tengsizlikni yeching",
"ifodani soddalashtiring", "qiymatini hisoblang", "ildizlarini toping",
"ni yeching", "ni hisoblang", "ni toping", "yeching", "hisoblang",
"тенгламани ечинг", "ечинг", "илдизларини топинг", "ҳисобланг",
)
def hash_query(text: str) -> str:
"""Matematik masalalarni semantik jihatdan tozalab keshlaydi."""
s = text.lower().strip()
for phrase in _NORM_NOISE_PHRASES:
s = s.replace(phrase, " ")
s = re.sub(r"[\s\?\!\.\,]+", " ", s).strip()
cleaned = "".join(s.split())
if not cleaned:
cleaned = "".join(text.lower().split())
return hashlib.md5(cleaned.encode("utf-8")).hexdigest()
def add_chat_message(user_id: int, role: str, content: str):
"""Foydalanuvchi bilan suhbat tarixini saqlaydi (xotira uchun)."""
if not content:
return
now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute(
"INSERT INTO chat_history (user_id, role, content, timestamp) VALUES (?, ?, ?, ?)",
(user_id, role, content[:2500], now_str)
)
cursor.execute("""
DELETE FROM chat_history
WHERE user_id = ? AND id NOT IN (
SELECT id FROM chat_history WHERE user_id = ? ORDER BY id DESC LIMIT 20
)
""", (user_id, user_id))
conn.commit()
def get_recent_chat_history(user_id: int, limit: int = 8) -> list[dict]:
"""Foydalanuvchining oxirgi suhbat xabarlarini AI ga uzatish uchun qaytaradi."""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT role, content FROM (
SELECT id, role, content FROM chat_history
WHERE user_id = ?
ORDER BY id DESC
LIMIT ?
) ORDER BY id ASC
""", (user_id, limit))
return [{"role": r[0], "content": r[1]} for r in cursor.fetchall()]
def clear_chat_history(user_id: int):
"""Foydalanuvchi suhbat xotirasini tozalaydi."""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM chat_history WHERE user_id = ?", (user_id,))
conn.commit()
def get_cached_solution(text: str) -> dict | None:
"""Agar masala avval SymPy bilan tasdiqlanib yechilgan bo'lsa, keshdan tayyor javobni oladi."""
q_hash = hash_query(text)
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT solution_json, hits FROM solution_cache WHERE query_hash = ?", (q_hash,))
row = cursor.fetchone()
if row:
try:
data = json.loads(row[0])
if not data.get("verified") or data.get("intent") in ("chat", "incomplete", "theory"):
return None
cursor.execute("UPDATE solution_cache SET hits = hits + 1 WHERE query_hash = ?", (q_hash,))
conn.commit()
data["from_cache"] = True
return data
except Exception:
return None
return None
def save_cached_solution(text: str, solution: dict):
"""Faqat SymPy bilan tasdiqlangan aniq matematik yechimlarni keshga saqlaydi."""
if not solution or not solution.get("verified") or not solution.get("final_answer"):
return
if solution.get("intent", solution.get("type")) in ("chat", "incomplete", "theory"):
return
q_hash = hash_query(text)
now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
clean_sol = dict(solution)
clean_sol.pop("from_cache", None)
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
INSERT OR REPLACE INTO solution_cache (query_hash, question_text, solution_json, created_at, hits)
VALUES (?, ?, ?, ?, COALESCE((SELECT hits FROM solution_cache WHERE query_hash = ?), 1))
""", (q_hash, text[:150], json.dumps(clean_sol, ensure_ascii=False), now_str, q_hash))
conn.commit()
def purge_bad_cache():
"""Eski noto'g'ri yoki tasdiqlanmagan kesh yozuvlarini tozalaydi."""
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT query_hash, solution_json FROM solution_cache")
rows = cursor.fetchall()
for qh, sj in rows:
try:
d = json.loads(sj)
if not d.get("verified") or d.get("type") in ("incomplete", "theory") or any(
"[Konsensus" in str(s) or "Identify coefficients" in str(s) for s in d.get("steps", [])
):
cursor.execute("DELETE FROM solution_cache WHERE query_hash = ?", (qh,))
except Exception:
cursor.execute("DELETE FROM solution_cache WHERE query_hash = ?", (qh,))
conn.commit()
init_db()
purge_bad_cache()