From 5675dfd20b24f1e0eef06b11d9ec41a894d3a5bf Mon Sep 17 00:00:00 2001 From: salmonik Date: Tue, 28 Apr 2026 20:05:36 +0200 Subject: multitude of changes. but primarly, use db for all data. Signed-off-by: salmonik --- cogs/utils/database.py | 111 ++++++++++++++++++++++++++++++++++++++++++++++ cogs/utils/file_search.py | 18 ++++++-- cogs/utils/settings.py | 69 ++++++++++++++++++---------- 3 files changed, 172 insertions(+), 26 deletions(-) create mode 100644 cogs/utils/database.py (limited to 'cogs/utils') diff --git a/cogs/utils/database.py b/cogs/utils/database.py new file mode 100644 index 0000000..67cbc9f --- /dev/null +++ b/cogs/utils/database.py @@ -0,0 +1,111 @@ +import os +import sqlite3 +import logging + +logger = logging.getLogger(__name__) + +DB_PATH = "data/senator.db" + +_initialized = False + + +def get_connection(): + return sqlite3.connect(get_db_path()) + + +def get_db_path(): + return os.getenv("SENATOR_DB_PATH", DB_PATH) + + +def set_db_path(path): + global DB_PATH + DB_PATH = path + + +def init_database(): + global _initialized + if _initialized: + return + + _initialized = True + + db_path = get_db_path() + db_dir = os.path.dirname(db_path) + if db_dir and not os.path.exists(db_dir): + os.makedirs(db_dir) + + conn = sqlite3.connect(db_path) + try: + c = conn.cursor() + + c.execute("PRAGMA foreign_keys = ON") + + c.execute(""" + CREATE TABLE IF NOT EXISTS settings ( + key TEXT PRIMARY KEY, + value TEXT + )""") + + c.execute(""" + CREATE TABLE IF NOT EXISTS cron_jobs ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + cron_expression TEXT NOT NULL, + type TEXT NOT NULL, + channel_id INTEGER NOT NULL, + active INTEGER DEFAULT 1, + audio_filename TEXT, + message TEXT, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP + )""") + + c.execute(""" + CREATE TABLE IF NOT EXISTS voice_activity ( + user_id TEXT PRIMARY KEY, + join_time REAL, + total_active REAL DEFAULT 0, + total_afk REAL DEFAULT 0, + is_afk INTEGER DEFAULT 0, + afk_start REAL, + active_start REAL, + channel_name TEXT, + last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP + )""") + + c.execute("CREATE INDEX IF NOT EXISTS idx_voice_activity_user_id ON voice_activity(user_id)") + c.execute("CREATE INDEX IF NOT EXISTS idx_cron_jobs_active ON cron_jobs(active)") + + c.execute(""" + CREATE TABLE IF NOT EXISTS triggers ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL UNIQUE, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP + )""") + + c.execute(""" + CREATE TABLE IF NOT EXISTS trigger_keywords ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + trigger_id INTEGER NOT NULL, + keyword TEXT NOT NULL, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + FOREIGN KEY (trigger_id) REFERENCES triggers(id) ON DELETE CASCADE, + UNIQUE(trigger_id, keyword) + )""") + + c.execute(""" + CREATE TABLE IF NOT EXISTS trigger_responses ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + trigger_id INTEGER NOT NULL, + response_text TEXT NOT NULL, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + FOREIGN KEY (trigger_id) REFERENCES triggers(id) ON DELETE CASCADE + )""") + + c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_keywords_trigger_id ON trigger_keywords(trigger_id)") + c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_keywords_keyword ON trigger_keywords(keyword)") + c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_responses_trigger_id ON trigger_responses(trigger_id)") + + conn.commit() + logger.info("Database initialized successfully") + finally: + conn.close() diff --git a/cogs/utils/file_search.py b/cogs/utils/file_search.py index 8111216..788f50c 100644 --- a/cogs/utils/file_search.py +++ b/cogs/utils/file_search.py @@ -34,10 +34,22 @@ class UniversalSearcher: query = query.strip() logger.info(f"Starting copyparty search for query: '{query}'") - # Build the query with optional extension filtering - name_query = f'name like "*{query}*"' + tokens = query.split() + if not tokens: + return [] + + token_queries = [] + for token in tokens: + path_query = f'path like "*{token}*"' + name_query = f'name like "*{token}*"' + token_queries.append(f"({path_query} or {name_query})") + + if len(token_queries) == 1: + name_query = token_queries[0] + else: + name_query = " and ".join(token_queries) + if file_extensions: - # Create extension filters using copyparty's .ext$ syntax ext_filters = [f"name like \"*.{ext.lower()}\"" for ext in file_extensions] ext_query = " or ".join(ext_filters) query = f"{name_query} and ({ext_query})" diff --git a/cogs/utils/settings.py b/cogs/utils/settings.py index d66ce7f..7146b6c 100644 --- a/cogs/utils/settings.py +++ b/cogs/utils/settings.py @@ -3,16 +3,18 @@ # Copyright (C) 2026 Salmonik @ salmonik@tuta.io # https://codeberg.org/salmonik/senator-sperma -import json import logging -import aiofiles +import sqlite3 import os from dataclasses import dataclass, field +from cogs.utils.database import get_connection, init_database + logger = logging.getLogger(__name__) -settings_file = "data/settings.json" -DEFAULT_BASEURL = "http://localhost:8080" +DEFAULT_BASEURL = "http://localhost:3939" + +init_database() @dataclass @@ -28,24 +30,22 @@ class Settings: } try: - if os.path.exists(settings_file): - async with aiofiles.open(settings_file, "r") as f: - content = await f.read() - if content.strip(): - self._data = json.loads(content) - self._sync_from_data() - logger.info("Settings loaded into memory") - else: - self._data = default_settings.copy() - self._sync_from_data() - await self.save() - logger.warning("Empty settings.json, reset to defaults") + conn = get_connection() + c = conn.cursor() + c.execute("SELECT key, value FROM settings") + rows = c.fetchall() + conn.close() + + if rows: + self._data = {row[0]: row[1] for row in rows} + self._sync_from_data() + logger.info("Settings loaded from database") else: self._data = default_settings.copy() self._sync_from_data() await self.save() - logger.info("Created default settings.json") - except json.JSONDecodeError as e: + logger.info("Created default settings in database") + except Exception as e: logger.error(f"Error loading settings: {e}") self._data = default_settings.copy() self._sync_from_data() @@ -53,17 +53,28 @@ class Settings: return self def _sync_from_data(self): - self.volume = self._data.get("volume", 1.0) + try: + self.volume = float(self._data.get("volume", 1.0)) + except (ValueError, TypeError): + self.volume = 1.0 self.baseurl = self._data.get("baseurl", DEFAULT_BASEURL) def _sync_to_data(self): - self._data["volume"] = self.volume + self._data["volume"] = str(self.volume) self._data["baseurl"] = self.baseurl async def save(self): self._sync_to_data() - async with aiofiles.open(settings_file, "w") as f: - await f.write(json.dumps(self._data, indent=2)) + conn = get_connection() + c = conn.cursor() + c.execute("PRAGMA foreign_keys = ON") + for key, value in self._data.items(): + c.execute( + "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)", + (key, value), + ) + conn.commit() + conn.close() _settings_instance: Settings | None = None @@ -90,4 +101,16 @@ def get_volume() -> float: def get_baseurl() -> str: - return _settings_instance.baseurl.rstrip("/") if _settings_instance else DEFAULT_BASEURL + url = os.getenv("COPYPARTY_URL") + if not url: + logger.warning("COPYPARTY_URL not set! Using default http://localhost:3939") + return DEFAULT_BASEURL + return url.rstrip("/") + + +def get_uploadurl() -> str: + url = os.getenv("COPYPARTY_URL_UPLOAD") + if not url: + logger.warning("COPYPARTY_URL_UPLOAD not set! Using default http://localhost:3939") + return DEFAULT_BASEURL + return url.rstrip("/") -- cgit v1.2.3