1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
import os
import sqlite3
import logging
from contextlib import contextmanager
logger = logging.getLogger(__name__)
DB_PATH = "data/senator.db"
_initialized = False
def get_connection():
conn = sqlite3.connect(get_db_path())
return _ConnectionContextManager(conn)
def get_db_path():
return os.getenv("SENATOR_DB_PATH", DB_PATH)
def set_db_path(path):
global DB_PATH
DB_PATH = path
class _ConnectionContextManager:
"""Context manager for sqlite3 connections that auto-closes."""
def __init__(self, conn):
self.conn = conn
def __enter__(self):
return self.conn
def __exit__(self, exc_type, exc_val, exc_tb):
self.conn.close()
return False
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()
|
