Download app/db.py from Shaptoli/Sinccon: direct link, hf CLI and curl.
- Browser
- Download file 14.9 kB
-
https://huggingface.co/spaces/Shaptoli/Sinccon/resolve/main/app/db.py
- Command line
-
hf download hf://spaces/Shaptoli/Sinccon/app/db.py
-
curl -L -o db.py https://huggingface.co/spaces/Shaptoli/Sinccon/resolve/main/app/db.py
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() | |